50 lines
1.7 KiB
Transact-SQL
50 lines
1.7 KiB
Transact-SQL
Use ActivePos_server
|
|
|
|
BEGIN TRANSACTION
|
|
|
|
DECLARE @ou_id INT
|
|
EXEC Arizona.dbo.sp_bmc_Bmc_Applic_Default
|
|
@in_job_type = 3,
|
|
@in_param_int_1 = null, /* Company */
|
|
@in_param_int_2 = null, /* Subsidiary */
|
|
@in_param_varchar_1 = 'cvCurrentOrganizationalUnit',
|
|
@out_default_value = @ou_id output,
|
|
@out_param_int_1 = NULL
|
|
|
|
DECLARE @newVal NVARCHAR(500);
|
|
|
|
|
|
/*Save the current value in [HCITools].[tmp].[TT_medifilm_setting]*/
|
|
IF OBJECT_ID('[HCITools].[tmp].[TT_medifilm_setting]') IS NULL
|
|
BEGIN
|
|
CREATE TABLE [HCITools].[tmp].[TT_medifilm_setting](
|
|
oldValue VARCHAR(MAX) NULL,
|
|
dateChange DATETIME2(2) NOT NULL CONSTRAINT df_dateChange DEFAULT GETDATE()
|
|
)
|
|
END
|
|
|
|
/*dynamic sql to avoid exception because of non existing table while compiling the batch*/
|
|
EXEC('
|
|
INSERT INTO [HCITools].[tmp].[TT_medifilm_setting]([oldValue])
|
|
SELECT [SE].[SettingValue]
|
|
FROM [dbo].[Settings] SE
|
|
where [SE].[SettingId] = ''Values.Global.SalesImportService.MainDirectory''
|
|
')
|
|
|
|
/*Craft the new URI from the OU ofthe pharmacy*/
|
|
SELECT @newVal = REPLACE('\\centralinfra.net\GAL\90_UserData\@ou@\POS\Medifilm', '@ou@', RTRIM(i.[Customer])+LTRIM(o.[OU_code]))
|
|
FROM [Arizona].dbo.[Organizational_unit] [o]
|
|
CROSS JOIN [master].cfg.[Identity] [i]
|
|
WHERE [o].[Organizational_unit_ID] = @ou_id
|
|
|
|
/*Update the setting*/
|
|
UPDATE se SET [SE].[SettingValue]= @newVal
|
|
FROM [dbo].[Settings] SE (nolock)
|
|
where [SE].[SettingId] = 'Values.Global.SalesImportService.MainDirectory'
|
|
|
|
/*restart the service to apply the change*/
|
|
EXEC xp_cmdshell 'net stop arizonaServerService'
|
|
EXEC xp_cmdshell 'net start arizonaServerService'
|
|
|
|
--COMMIT TRANSACTION
|
|
ROLLBACK TRANSACTION |