thierry/sql-scripts
1
0
Fork 0
Code Issues Pull Requests Actions Packages Projects Releases Wiki Activity
Files
main
sql-scripts/phidx masking package.sql
Schork Thierry (Galenica) 5a4f2784bb sync
2026-01-13 08:14:13 +01:00

842 lines
29 KiB
Transact-SQL
Raw Permalink Blame History

/*
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 = '' <html><body><p>R<>sum<75> des erreurs dans le tableau ci-dessous : </p>'' +''
<table border="1" width="400px" height="400px">
<tr>
<th>ErrorNumber</th>
<th>ErrorSeverity</th>
<th>ErrorState</th>
<th>ErrorProcedure</th>
<th>ErrorLine</th>
<th>ErrorMessage</th>
</tr>'' + ''<tr valign="center">
<td>'' + isnull(convert(varchar, ERROR_NUMBER()), '''') + ''</td>
<td>'' + isnull(convert(varchar, ERROR_SEVERITY()), '''') + ''</td>
<td>'' + isnull(convert(varchar, ERROR_STATE()), '''') + ''</td>
<td>'' + isnull(ERROR_PROCEDURE(), '''') + ''</td>
<td>'' + isnull(convert(varchar, ERROR_LINE()), '''') + ''</td>
<td>'' + isnull(ERROR_MESSAGE(), '''') + ''</td>
</tr>''+ ''
</table>
</body>
</html>'';
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<EFBFBD>ture du PEXF sans adresse <20> la date -1 de la start_date du prix PEXF avec adresse
pour les articles LS.
Nous recr<63>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
Reference in New Issue View Git Blame Copy Permalink