/* 04.11.2024, TSC */ /* 01.11.2024, TSC */ USE [HCITools] GO /****** Object: StoredProcedure [dbo].[phidx_mask_service_providers] Script Date: 01.11.2024 14:47:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* TPDT-742 Anonymizing pharmindex service providers https://galenica.atlassian.net/wiki/spaces/OCART/pages/1202389081/Pharmindex+service+providers+masking Will alter every retired or deleted service providers to mask their name, address, phone, fax and email in the pharmindexTP of a central that is either n+1 n+2 888 27.09.2024, TSC Creation 04.11.2024, TSC When cleaning Arizona, ignoring service providers linked to a Organizational Unit. */ CREATE OR ALTER PROCEDURE [dbo].[phidx_mask_service_providers] @force BIT = 0 AS BEGIN SET XACT_ABORT ON; SET NOCOUNT ON; IF NOT EXISTS( SELECT 1 FROM [sys].[databases] [d] WHERE [d].[name] = 'pharmindexTP' ) AND @force = 0 BEGIN PRINT 'No pharmindexTP database on this instance, exiting.'; RETURN 0; END IF NOT EXISTS( SELECT 1 FROM [master].[cfg].[InstanceContext] WHERE [Business] IN ('TPCENT') ) AND @force = 0 BEGIN PRINT 'This instance is not a central, exiting.'; RETURN 0; END IF NOT EXISTS ( SELECT 1 FROM [master].[cfg].[InstanceContext] WHERE [Type] IN ('DEVE', 'VALI') ) AND @force = 0 BEGIN PRINT 'Non dev environment, exiting.'; RETURN 0; END IF OBJECT_ID('tempdb..#sp') IS NOT NULL BEGIN DROP TABLE [#sp]; END CREATE TABLE [#sp] ( [ServiceProviderId] INT NOT NULL ); --fetch the id's of the data to mask INSERT INTO [#sp] ([ServiceProviderId]) SELECT [sp].[ServiceProviderId] FROM [PharmIndexTP].[dbo].[ServiceProvider] [sp] WHERE ([sp].[Status] = 'R' OR [sp].[Deleted] = 1) AND [sp].[Name1] <> 'Masked' AND [sp].[Name2] NOT LIKE 'Provider%'; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - collected SP to mask. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --rename service providers UPDATE [sp] SET [sp].[Name1] = 'Masked', [sp].[Name2] = 'Provider ' + CAST([sp].[ServiceProviderId] AS VARCHAR(15)), [sp].[Title] = NULL, [sp].[Sex] = NULL FROM [#sp] [s] JOIN [PharmIndexTP].[dbo].[ServiceProvider] [sp] ON [s].[ServiceProviderId] = [sp].[ServiceProviderId]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - renamed SP. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --alter sp address UPDATE [sa] SET [sa].[State] = 'BE', [sa].[ZIP] = 3011, [sa].[City] = 'Bern', [sa].[Address] = 'Bundesplatz', [sa].[AddressNumber] = 3 FROM [#sp] [s] JOIN [PharmIndexTP].[dbo].[ServiceProviderAddress] [sa] ON [s].[ServiceProviderId] = [sa].[ServiceProviderId]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - altered address. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --clean communications (ServiceProviderCommunication and ServiceProviderCommunication2) DELETE [sc2] FROM [#sp] [s] JOIN [PharmIndexTP].[dbo].[ServiceProviderAddress] [sa] ON [s].[ServiceProviderId] = [sa].[ServiceProviderId] JOIN [PharmIndexTP].[dbo].[ServiceProviderCommunication2] [sc2] ON [sc2].[ServiceProviderAddressId] = [sa].[ServiceProviderAddressId]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - cleaned communication2. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; DELETE [sc] FROM [#sp] [s] JOIN [PharmIndexTP].[dbo].[ServiceProviderAddress] [sa] ON [s].[ServiceProviderId] = [sa].[ServiceProviderId] JOIN [PharmIndexTP].[dbo].[ServiceProviderCommunication] [sc] ON [s].[ServiceProviderId] = [sc].[ServiceProviderId]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - cleared communication. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --clean emails (ServiceProviderECommunication and ServiceProviderECommunication2) DELETE [sc2] FROM [#sp] [s] JOIN [PharmIndexTP].[dbo].[ServiceProviderAddress] [sa] ON [s].[ServiceProviderId] = [sa].[ServiceProviderId] JOIN [PharmIndexTP].[dbo].[ServiceProviderECommunication2] [sc2] ON [sc2].[ServiceProviderAddressId] = [sa].[ServiceProviderAddressId]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - cleaned ecommunication2. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; DELETE [sc] FROM [#sp] [s] JOIN [PharmIndexTP].[dbo].[ServiceProviderAddress] [sa] ON [s].[ServiceProviderId] = [sa].[ServiceProviderId] JOIN [PharmIndexTP].[dbo].[ServiceProviderECommunication] [sc] ON [s].[ServiceProviderId] = [sc].[ServiceProviderId]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - cleaned ecommunication. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --insert fake emails in [ServiceProviderECommunication] INSERT INTO [PharmIndexTP].[dbo].[ServiceProviderECommunication] ([ServiceProviderId], [Category], [Type], [Value], [CreationDate], [UpdateDate]) SELECT [sp].[ServiceProviderId], 'B' AS [category], 'MAIL' AS [Type], 'masked_provider_' + CAST([sp].[ServiceProviderId] AS VARCHAR(15)) + '@nomail.com' AS [Value], CURRENT_TIMESTAMP AS [CreationDate], CURRENT_TIMESTAMP AS [UpdateDate] FROM [#sp] [s] JOIN [PharmIndexTP].[dbo].[ServiceProvider] [sp] ON [s].[ServiceProviderId] = [sp].[ServiceProviderId]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - inserted fake emails in ecommunication. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --insert fake emails in [ServiceProviderECommunication2] INSERT INTO [PharmIndexTP].[dbo].[ServiceProviderECommunication2] ([ServiceProviderAddressId], [Category], [Type], [Value], [CreationDate], [UpdateDate]) SELECT [sa].[ServiceProviderAddressId], 'B' AS [category], 'MAIL' AS [Type], 'masked_provider_' + CAST([s].[ServiceProviderId] AS VARCHAR(15)) + '@nomail.com' AS [Value], CURRENT_TIMESTAMP AS [CreationDate], CURRENT_TIMESTAMP AS [UpdateDate] FROM [#sp] [s] JOIN [PharmIndexTP].[dbo].[ServiceProviderAddress] [sa] ON [s].[ServiceProviderId] = [sa].[ServiceProviderId]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - inserted fake emails in ecommunication2. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --initial cleanup of arizona data not present in pharmindex anymore IF OBJECT_ID('tempdb..#cleanup') IS NOT NULL BEGIN DROP TABLE [#cleanup]; END CREATE TABLE [#cleanup] ( [Address_ID] INT NOT NULL ); INSERT INTO [#cleanup]([Address_ID]) SELECT [ad].[Address_ID] FROM [Arizona].[dbo].[address] [ad] JOIN [Arizona].[dbo].[PH_prescriber] [sp] ON [ad].[Address_ID] = [sp].[PHPR_address] WHERE [sp].[PHPR_status] = 2 AND NOT EXISTS( --ignore SP linked to OU SELECT 1 FROM [Arizona].[dbo].[Organizational_unit] ou WHERE [ou].[OU_address] = [ad].[Address_ID] ) AND ( ISNULL([ad].[AD_first_name], '') <> 'Masked' AND ISNULL([ad].[AD_last_name], '') NOT LIKE 'Provider%' ); PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - fetched arizona ph_prescriber in status 2. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --mask address UPDATE [ad] SET [ad].[AD_first_name] = LEFT('Masked', 30), [ad].[AD_last_name] = LEFT('Provider ' + CAST([ad].[Address_ID] AS VARCHAR(10)), 30), [ad].[AD_middle_name] = NULL, [ad].[AD_name] = LEFT('Masked Provider ' + CAST([ad].[Address_ID] AS VARCHAR(10)), 60), [ad].[AD_title] = NULL, [ad].[AD_sex] = CASE WHEN [ad].[Address_ID] % 2 = 0 THEN 1 ELSE 2 END, [ad].[AD_address_1] = 'Bundesplatz 3', [ad].[AD_address_2] = NULL, [ad].[AD_address_3] = NULL, [ad].[AD_address_supplement] = NULL, [ad].[AD_zip_code] = '3011', [ad].[AD_city] = 'Bern', [ad].[AD_state] = 'BE', [ad].[AD_postal_routing_address] = '3011 Bern', [ad].[AD_language] = 1, [ad].[AD_country] = 1 FROM [Arizona].[dbo].[address] [ad] JOIN [#cleanup] [c] ON [c].[Address_ID] = [ad].[Address_ID]; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - masked arizona.address. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; --mask address_key type 5 UPDATE [adk] SET [adk].[ADK_key] = LEFT([ad].[AD_name], 15) FROM [Arizona].[dbo].[address_key] [adk] JOIN [#cleanup] [c] ON [c].[Address_ID] = [adk].[ADK_address] JOIN [Arizona].[dbo].[address] [ad] ON [ad].[Address_ID] = [c].[Address_ID] WHERE [adk].[ADK_type] = 5; PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114) + ' - masked arizona.address_key type 5. ' + REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@ROWCOUNT), 1), ',', ''''), '.00', '') + ' row(s) affected.'; END GO /* Drop existing standard schedule for job */ declare @schedule_id int declare c_schedules cursor local forward_only static for select ss.schedule_id from msdb.dbo.sysjobschedules sjs INNER JOIN msdb.dbo.sysschedules ss ON sjs.schedule_id = ss.schedule_id AND ss.name NOT LIKE '%#SPEC#' INNER JOIN msdb.dbo.sysjobs sj ON sjs.job_id = sj.job_id WHERE sj.name = N'_D03091 - INDEX - Load items CDS and prescribers from PharmIndex to Arizona - Central' open c_schedules FETCH NEXT FROM c_schedules into @schedule_id while @@fetch_status = 0 begin IF ((select COUNT(*) from msdb.dbo.sysjobschedules where schedule_id=@schedule_id) = 1) EXEC msdb.dbo.sp_delete_schedule @schedule_id=@schedule_id, @force_delete = 1 FETCH NEXT FROM c_schedules into @schedule_id end close c_schedules deallocate c_schedules IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'_D03091 - INDEX - Load items CDS and prescribers from PharmIndex to Arizona - Central') EXEC msdb.dbo.sp_delete_job @job_name = N'_D03091 - INDEX - Load items CDS and prescribers from PharmIndex to Arizona - Central', @delete_unused_schedule=0 GO /* Creation Job and Steps*/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'TPH-Business' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'TPH-Business' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END /* Add Job */ DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'_D03091 - INDEX - Load items CDS and prescribers from PharmIndex to Arizona - Central', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'Load items, CDS and prescribers from PharmIndex to Arizona 2017.01.18 SNU/GBA : insert new step 7 for br 20170515 TTP Add step "INDEX - Control if ..." 20181029 GBA D 20200408 SPE Add step Update PEXF 20220317 FLA Change DBA mail 20220330 RTC Update ph_prescriber status 20220429 FLA Remove ph_prescriber status 20230713 FLA Change mailing list in steps, 20230818 SNU Remove step Control if ... 20241030 TSC TPDT-742 Added pharmindexTP service provider masking', @category_name=N'TPH-Business', @start_step_id=1, @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /* Add Step */ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Empty step', @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=3, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'/* Empty step */ ', @database_name=N'master', @output_file_name=NULL, @flags=0, @database_user_name=NULL, @server=NULL, @additional_parameters=NULL, @proxy_id=NULL, @proxy_name=NULL IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /* Add Step */ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check state of Pharmindex download', @step_id=2, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=4, @on_fail_step_id=7, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N' /* Check download from Pharmindex is finished */ declare @today datetime; select @today = convert(datetime, convert(varchar(20), getdate(),102)) if not exists ( select top 1 * from BatchImportHistory bih where bih.Success = 1 and bih.EndImportDate > @today) begin /* Download not OK */ raiserror(''Pharmindex download in process, job not executed'', 16,1) end; ', @database_name=N'PharmIndexTP', @output_file_name=NULL, @flags=0, @database_user_name=NULL, @server=NULL, @additional_parameters=NULL, @proxy_id=NULL, @proxy_name=NULL IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /* Add Step */ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'mask pharmindex service providers', @step_id=3, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=4, @on_fail_step_id=7, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec [dbo].[phidx_mask_service_providers]', @database_name=N'HCITools', @output_file_name=NULL, @flags=0, @database_user_name=NULL, @server=NULL, @additional_parameters=NULL, @proxy_id=NULL, @proxy_name=NULL IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /* Add Step */ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'INDEX - Transfert PharmIndexTP to Arizona items, Prescriber using Checksum', @step_id=4, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=4, @on_fail_step_id=7, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N' begin try declare @out_param_int_1 int execute[dbo].[pdx_loading] @in_job_type = 20 ,@skip_mapping = 0 ,@in_subsidiary = 100 ,@in_table_name = null ,@in_debug = 0 ,@out_param_int_1 = @out_param_int_1 output end try begin catch declare @mail_message nvarchar(2000), @mail_subject nvarchar(255); set @mail_message = ''

Résumé des erreurs dans le tableau ci-dessous :

'' +'' '' + ''''+ ''
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
'' + isnull(convert(varchar, ERROR_NUMBER()), '''') + '' '' + isnull(convert(varchar, ERROR_SEVERITY()), '''') + '' '' + isnull(convert(varchar, ERROR_STATE()), '''') + '' '' + isnull(ERROR_PROCEDURE(), '''') + '' '' + isnull(convert(varchar, ERROR_LINE()), '''') + '' '' + isnull(ERROR_MESSAGE(), '''') + ''
''; set @mail_subject = ''INDEX - Transfert PharmIndex to PHGD_xx for items using checksum (Instance '' + @@SERVERNAME + '') - Job _D01941 - INDEX''; /* envoi du mail */ exec Arizona.dbo.aps_Send_Mail_with_template @in_param_varchar_2 = ''HCI_PharmIndex;HCI_DataManagement'', @in_param_varchar_3 = @mail_message, @in_param_subject = @mail_subject, @in_job_type = 4; end catch ', @database_name=N'PharmIndexTP', @output_file_name=NULL, @flags=0, @database_user_name=NULL, @server=NULL, @additional_parameters=NULL, @proxy_id=NULL, @proxy_name=NULL IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /* Add Step */ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Update PEXF', @step_id=5, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=4, @on_fail_step_id=7, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'declare @cvPHGDPriceCodePEXF int, @SubsidiaryId int, @w_date date select @SubsidiaryId = 1000, @cvPHGDPriceCodePEXF = NULL, @w_date = getdate() exec arizona.dbo.sp_bmc_Bmc_Applic_Default @in_job_type = 3, @in_param_int_1 = NULL, @in_param_int_2 = @SubsidiaryId, @in_param_varchar_1 = ''cvPHGDPriceCodePSL1'', @out_default_value = @cvPHGDPriceCodePEXF output, @out_param_int_1 = null if @cvPHGDPriceCodePEXF is null begin select @SubsidiaryId = 100 exec arizona.dbo.sp_bmc_Bmc_Applic_Default @in_job_type = 3, @in_param_int_1 = NULL, @in_param_int_2 = @SubsidiaryId, @in_param_varchar_1 = ''cvPHGDPriceCodePSL1'', @out_default_value = @cvPHGDPriceCodePEXF output, @out_param_int_1 = null end /*------------- Fermeture des PEXF sans Adresse pour les articles avec insurance code not in (10, 11) ------------------*/ Update top(5000) FP set fp.fp_end_date = DATEADD(d,-1,@w_date) from subsidiary SUB with (nolock) join price_code PRC with (nolock) on prc.prc_subsidiary = sub.subsidiary_id and prc.price_code_id = @cvPHGDPriceCodePEXF join Fixed_price FP with (nolock) on FP.FP_price_code = prc.price_code_id and FP.fp_subsidiary = sub.subsidiary_id and FP.FP_address is null and FP.FP_start_date < GETDATE() and ISNULL(fp.fp_end_date, ''2099-12-31'') > GETDATE() join Item_key ITK with (nolock) on itk.ITK_item = fp.fp_item and ITK.ITK_subsidiary = SUB.Subsidiary_ID and ITK.ITK_type = 1 join PH_item phit with (nolock) on phit.PHIT_item = ITK.ITK_item and phit.PHIT_insurance_code not in (''10'', ''11'') where SUB.subsidiary_id = @SubsidiaryId /*--------------------------------------- Clôture du PEXF sans adresse à la date -1 de la start_date du prix PEXF avec adresse pour les articles LS. Nous recréons un PEXF avec la bonne start_date et le bon prix afin que la facturation aux caisse maladie soit OK --------------*/ Update top(5000) FP1 set fp1.fp_end_date = DATEADD(d,-1,fp.FP_start_date) from subsidiary SUB with (nolock) join price_code PRC with (nolock) on prc.prc_subsidiary = sub.subsidiary_id and prc.price_code_id = @cvPHGDPriceCodePEXF join Fixed_price FP with (nolock) on FP.FP_price_code = prc.price_code_id and FP.fp_subsidiary = sub.subsidiary_id and FP.FP_address is not null and FP.FP_start_date < GETDATE() and ISNULL(fp.fp_end_date, ''2099-12-31'') > GETDATE() and FP.fixed_price_id = (select top 1 fp2.fixed_price_id from Fixed_price FP2 with (nolock) where FP2.FP_item = FP.FP_item and FP2.FP_price_code = fp.FP_price_code and FP2.fp_subsidiary = FP.fp_subsidiary and FP2.FP_address is not null and FP2.FP_start_date < GETDATE() and ISNULL(FP2.fp_end_date, ''2099-12-31'') > GETDATE() order by FP2.FP_start_date desc ) join Item_key ITK with (nolock) on itk.ITK_item = fp.fp_item and ITK.ITK_subsidiary = SUB.Subsidiary_ID and ITK.ITK_type = 1 join PH_item phit with (nolock) on phit.PHIT_item = ITK.ITK_item and phit.PHIT_insurance_code in (''10'', ''11'') join Fixed_price FP1 with (nolock) on FP1.FP_price_code = prc.price_code_id and FP1.fp_subsidiary = sub.subsidiary_id and FP1.FP_address is null and FP1.FP_start_date < GETDATE() and ISNULL(FP1.fp_end_date, ''2099-12-31'') > GETDATE() and FP1.FP_item = FP.FP_item where SUB.subsidiary_id = @SubsidiaryId and isnull(FP.FP_tax_free_price_per_unit,0) <> isnull(FP1.FP_tax_free_price_per_unit,0) /*---------- Creation PEXF sans adresse avec start_date et prix du PEXF avec Adresse pour LS (Le plus récent) ------------*/ declare @New_fixed_price_ID int, @nb_fixed_price int , @fp_subsidiary int, @fp_tariff_type int, @fp_item int, @fp_currency int, @fp_price_code int, @fp_sales_tax_code int, @FP_start_date datetime, @FP_tax_free_price_per_unit dec(14,2) SELECT @nb_fixed_price = 1 /*-- Declaration du curseur c_external_item --*/ declare c_FP cursor local forward_only read_only static for select FP.fp_subsidiary, FP.fp_tariff_type, FP.fp_item, FP.fp_currency, FP.fp_price_code, FP.fp_sales_tax_code, FP.FP_start_date, FP.FP_tax_free_price_per_unit from subsidiary SUB with (nolock) join price_code PRC with (nolock) on prc.prc_subsidiary = sub.subsidiary_id and prc.price_code_id = @cvPHGDPriceCodePEXF join Fixed_price FP with (nolock) on FP.FP_price_code = prc.price_code_id and FP.fp_subsidiary = sub.subsidiary_id and FP.FP_address is not null and FP.FP_start_date < GETDATE() and ISNULL(fp.fp_end_date, ''2099-12-31'') > GETDATE() and FP.fixed_price_id = (select top 1 fp2.fixed_price_id from Fixed_price FP2 with (nolock) where FP2.FP_item = FP.FP_item and FP2.FP_price_code = fp.FP_price_code and FP2.fp_subsidiary = FP.fp_subsidiary and FP2.FP_address is not null and FP2.FP_start_date < GETDATE() and ISNULL(FP2.fp_end_date, ''2099-12-31'') > GETDATE() order by FP2.FP_start_date desc ) join Item_key ITK with (nolock) on itk.ITK_item = fp.fp_item and ITK.ITK_subsidiary = SUB.Subsidiary_ID and ITK.ITK_type = 1 join PH_item phit with (nolock) on phit.PHIT_item = ITK.ITK_item and phit.PHIT_insurance_code in (''10'', ''11'') left outer join Fixed_price FP1 with (nolock) on FP1.FP_price_code = prc.price_code_id and FP1.fp_subsidiary = sub.subsidiary_id and FP1.FP_address is null and FP1.FP_start_date < GETDATE() and ISNULL(FP1.fp_end_date, ''2099-12-31'') > GETDATE() and FP1.FP_item = FP.FP_item where SUB.subsidiary_id = @SubsidiaryId and FP1.Fixed_price_ID is null order by 1 open c_FP; fetch next from c_FP into @fp_subsidiary , @fp_tariff_type , @fp_item , @fp_currency , @fp_price_code , @fp_sales_tax_code , @FP_start_date , @FP_tax_free_price_per_unit while (@@fetch_status <> -1 ) begin if (@@fetch_status <> -2 ) begin /*----- Recherche dernier ID pour Fixed_Price -----*/ select @New_fixed_price_ID = null exec sp_bmc_GetNextID @in_key = ''fixed_price'', @in_id_column = ''fixed_price_id'', @in_nbr_of_record = @nb_fixed_price, @out_id = @New_fixed_price_id output insert fixed_price (fixed_price_id, fp_subsidiary, fp_tariff_type, fp_item, fp_currency, fp_price_code, fp_sales_tax_code, fp_start_date, fp_tax_free_price_per_unit , fp_unit_code, FP_price_per_unit_unit_code, fp_final_discount_possible, FP_discount_level_1_possible, FP_discount_level_2_possible, FP_origin, FP_remark ) select @New_fixed_price_id, @fp_subsidiary , @fp_tariff_type , @fp_item , @fp_currency , @fp_price_code , @fp_sales_tax_code , @FP_start_date , @FP_tax_free_price_per_unit, 1, 1, 1, 1, 1, 1, ''Automatic Insert PEXF '' /*----- fin du curseur -----*/ end /* (@@fetch_status <> -2 ) */ fetch next from c_FP into @fp_subsidiary , @fp_tariff_type , @fp_item , @fp_currency , @fp_price_code , @fp_sales_tax_code , @FP_start_date , @FP_tax_free_price_per_unit end /* (@@fetch_status <> -1 ) */ close c_FP; deallocate c_FP; ', @database_name=N'Arizona', @output_file_name=NULL, @flags=0, @database_user_name=NULL, @server=NULL, @additional_parameters=NULL, @proxy_id=NULL, @proxy_name=NULL IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /* Add Step */ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Email step for success', @step_id=6, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=4, @on_fail_step_id=7, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec Get_Job_Error_Info @in_JobName = ''_D03091 - INDEX - Load items CDS and prescribers from PharmIndex to Arizona - Central'', @in_Recipients = ''DBA_operator;HCI_PharmIndex'' EXEC [mon].[Maj_Jobs_Status] @in_JobID = $(ESCAPE_SQUOTE(JOBID))', @database_name=N'HCITools', @output_file_name=NULL, @flags=0, @database_user_name=NULL, @server=NULL, @additional_parameters=NULL, @proxy_id=NULL, @proxy_name=NULL IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /* Add Step */ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send email KO', @step_id=7, @cmdexec_success_code=0, @on_success_action=2, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'exec Get_Job_Error_Info @in_JobName = ''_D03091 - INDEX - Load items CDS and prescribers from PharmIndex to Arizona - Central'', @in_Recipients = ''DBA_operator;HCI_PharmIndex;HCI_DataManagement'' EXEC [mon].[Maj_Jobs_Status] @in_JobID = $(ESCAPE_SQUOTE(JOBID))', @database_name=N'HCITools', @output_file_name=NULL, @flags=0, @database_user_name=NULL, @server=NULL, @additional_parameters=NULL, @proxy_id=NULL, @proxy_name=NULL IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback /* Attach existing specific schedule for job */ declare @enabled_schedule int, @schedule_name nvarchar(50) declare c_schedules cursor local forward_only static for select enabled, name from msdb.dbo.sysschedules where name LIKE '_D03091%' and name LIKE '%#SPEC#' open c_schedules FETCH NEXT FROM c_schedules into @enabled_schedule, @schedule_name while @@fetch_status = 0 begin EXEC @ReturnCode = msdb.dbo.sp_attach_schedule @job_id = @jobId, @schedule_name=@schedule_name IF(@enabled_schedule = 1) begin SET @schedule_name = SUBSTRING(@schedule_name,0,LEN(@schedule_name)-5) IF EXISTS (select name from msdb.dbo.sysschedules where name = @schedule_name) EXEC @ReturnCode = msdb.dbo.sp_update_schedule @name=@schedule_name, @enabled=0 end IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback FETCH NEXT FROM c_schedules into @enabled_schedule, @schedule_name end close c_schedules deallocate c_schedules EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO