395 lines
16 KiB
Transact-SQL
395 lines
16 KiB
Transact-SQL
USE [Arizona]
|
|
GO
|
|
BEGIN TRANSACTION
|
|
|
|
SET ANSI_NULLS ON
|
|
SET ANSI_WARNINGS ON
|
|
go
|
|
|
|
/*
|
|
Script pour la mise à jour de la table [InsuranceNetworkServiceAdvice]
|
|
25.08.2025 / FLA pour le bug OCTP-9752
|
|
|
|
Modifications
|
|
29.10.2025 TSC TPDT-1391 Removed linked server usage
|
|
11.11.2025 TSC TPDT-1391 Removed dbo.xxx instance in remote code
|
|
-------------------------------------------------------------------------------------------------------------------------------------------*/
|
|
|
|
IF(NOT EXISTS (SELECT 1 FROM [InstanceContext] WHERE [Business] = 'TPCENT'))
|
|
BEGIN
|
|
SET XACT_ABORT ON;
|
|
|
|
/* variables for linked server replacement */
|
|
DECLARE @host_central VARCHAR(200);
|
|
DECLARE @host_local VARCHAR(200);
|
|
DECLARE @query VARCHAR(MAX);
|
|
DECLARE @payload VARCHAR(MAX);
|
|
DECLARE @response NVARCHAR(MAX);
|
|
DECLARE @statusText NVARCHAR(4000);
|
|
DECLARE @statusCode INT;
|
|
DECLARE @azFuncUri VARCHAR(500);
|
|
--for output handling
|
|
DECLARE @format NVARCHAR(MAX);
|
|
DECLARE @data NVARCHAR(MAX);
|
|
DECLARE @tpl NVARCHAR(MAX);
|
|
DECLARE @q NVARCHAR(MAX);
|
|
|
|
SELECT @host_central = hostname
|
|
FROM aps_fn_fetch_central_hostname(DEFAULT);
|
|
|
|
SELECT @host_local = fqdn_escaped
|
|
FROM aps_fn_fetch_local_instance_fqdn();
|
|
|
|
SELECT @azFuncUri = url
|
|
FROM aps_fn_fetch_az_func_url();
|
|
|
|
SET @payload='
|
|
{
|
|
"QueryText": "SELECT 1 AS enabled FROM @dbo@.aps_monitor_table WHERE AMT_table_name = ''Insurance_netw_serv_advice'' AND AMT_vertical_synchronization = 1"
|
|
,"Parameters": {}
|
|
, "TargetServer": @host@
|
|
, "TargetDbName": "arizona"
|
|
, "Credential": "sqlLksrvTpCentAdm"
|
|
}';
|
|
SET @payload = REPLACE(@payload, '@host@', isnull('"'+@host_central+'"', 'null'));
|
|
SET @payload = REPLACE(@payload, '@dbo@','dbo');
|
|
|
|
EXEC [sp_Make_Restful_Call] @url = @azFuncUri,
|
|
@method = 'POST',
|
|
@payload = @payload,
|
|
@headers = DEFAULT,
|
|
@credentialName = NULL,
|
|
@timeoutSeconds = 10,
|
|
@response = @response OUTPUT,
|
|
@statusText = @statusText OUTPUT,
|
|
@statusCode = @statusCode OUTPUT;
|
|
IF @statusCode NOT BETWEEN 200 and 299
|
|
BEGIN
|
|
RAISERROR('Error when calling Azure Function: %s (Status code: %d). %s', 16, 1, @statusText, @statusCode, @payload);
|
|
RETURN;
|
|
END
|
|
SELECT @format = [Value]
|
|
FROM OPENJSON(@response)
|
|
WHERE [Key] = 'schemaFormatted';
|
|
|
|
SELECT @data = [Value]
|
|
FROM OPENJSON(@response)
|
|
WHERE [Key] = 'data';
|
|
|
|
IF OBJECT_ID('tempdb..#vsync_config')IS NOT NULL BEGIN
|
|
DROP TABLE #vsync_config;
|
|
END
|
|
CREATE TABLE #vsync_config([enabled] BIT NOT NULL);
|
|
SET @tpl = N'
|
|
INSERT INTO #vsync_config
|
|
SELECT *
|
|
FROM OPENJSON(''@data@'')
|
|
@format@
|
|
';
|
|
|
|
SET @q = REPLACE(REPLACE(@tpl, '@data@', @data), '@format@', @format);
|
|
|
|
EXEC (@q);
|
|
|
|
IF EXISTS (SELECT 1 FROM [#vsync_config] c WHERE c.[enabled] = 1)
|
|
BEGIN
|
|
|
|
DECLARE @cvCurrentOrganizationalUnit INT,
|
|
@out_default_value VARCHAR(60)
|
|
|
|
IF OBJECT_ID('tempdb..#v_Sync_V_Insurance_netw_serv_advice')IS NOT NULL BEGIN
|
|
DROP TABLE #v_Sync_V_Insurance_netw_serv_advice;
|
|
END
|
|
CREATE TABLE #v_Sync_V_Insurance_netw_serv_advice(
|
|
calc_subsidiary_id INT NULL,
|
|
Insurance_netw_serv_advice_ID INT NULL,
|
|
INSA_Advice_Enum TINYINT NULL,
|
|
INSA_Insurance_Network_ID INT NULL,
|
|
INSA_Item_ID INT NULL
|
|
);
|
|
|
|
EXEC sp_bmc_Bmc_Applic_Default
|
|
@in_job_type = 3,
|
|
@in_param_int_1 = NULL,
|
|
@in_param_int_2 = NULL,
|
|
@in_param_varchar_1 = 'cvCurrentOrganizationalUnit',
|
|
@out_default_value = @out_default_value OUTPUT,
|
|
@out_param_int_1 = NULL;
|
|
|
|
SET @cvCurrentOrganizationalUnit = convert(int,@out_default_value);
|
|
|
|
/* Fetch data from central */
|
|
SET @query = '
|
|
SELECT *
|
|
FROM @dbo@.v_Sync_V_Insurance_netw_serv_advice
|
|
';
|
|
SET @query = REPLACE(@query, '@dbo@','dbo');
|
|
--JSON param must be without carriage return, remove them from the query to execute
|
|
SET @query = REPLACE(REPLACE(@query, CHAR(13), ' '), CHAR(10), ' ');
|
|
|
|
set @payload='
|
|
{
|
|
"QueryText": @query@
|
|
,"Parameters": {}
|
|
, "TargetServer": @host@
|
|
, "TargetDbName": "arizona"
|
|
, "Credential": "sqlLksrvTpCentAdm"
|
|
, "InjectResults": true
|
|
, "InjectServer": @local@
|
|
, "InjectDbName": "arizona"
|
|
, "InjectDbSchema": "bulk"
|
|
}';
|
|
|
|
SET @payload = REPLACE(@payload, '@query@', isnull('"'+@query+'"', 'null'));
|
|
SET @payload = REPLACE(@payload, '@host@', isnull('"'+@host_central+'"', 'null'));
|
|
SET @payload = REPLACE(@payload, '@local@', isnull('"'+@host_local+',1433"', 'null'));
|
|
|
|
EXEC [sp_Make_Restful_Call] @url = @azFuncUri,
|
|
@method = 'POST',
|
|
@payload = @payload,
|
|
@headers = DEFAULT,
|
|
@credentialName = NULL,
|
|
@timeoutSeconds = 10,
|
|
@response = @response OUTPUT,
|
|
@statusText = @statusText OUTPUT,
|
|
@statusCode = @statusCode OUTPUT;
|
|
|
|
IF @statusCode NOT BETWEEN 200 and 299
|
|
BEGIN
|
|
RAISERROR('Error when calling Azure Function: %s (Status code: %d). %s', 16, 1, @statusText, @statusCode, @payload);
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @tbl VARCHAR(255);
|
|
SELECT @tbl = [Value]
|
|
FROM OPENJSON(@response)
|
|
WHERE [Key]='TableName';
|
|
|
|
SET @tpl = N'
|
|
INSERT INTO #v_Sync_V_Insurance_netw_serv_advice([calc_subsidiary_id], [Insurance_netw_serv_advice_ID], [INSA_Advice_Enum], [INSA_Insurance_Network_ID], [INSA_Item_ID])
|
|
SELECT [calc_subsidiary_id], [Insurance_netw_serv_advice_ID], [INSA_Advice_Enum], [INSA_Insurance_Network_ID], [INSA_Item_ID]
|
|
FROM @tbl@;
|
|
|
|
DROP TABLE @tbl@;
|
|
';
|
|
|
|
SET @q = REPLACE(@tpl, '@tbl@', @tbl);
|
|
EXEC (@q);
|
|
|
|
-- region managing insurance_network table
|
|
set @query='
|
|
SELECT
|
|
[Insurance_network_ID], [INN_PH_insurance], [INN_ofac_code], [INN_veka_code], [INN_active], [INN_master_ID],
|
|
[Insurance_network_text_ID], [INNT_language], [INNT_insurance_network], [INNT_text], [INNT_master_ID], [INNT_documentation_url]
|
|
FROM [v_Sync_V_Insurance_network] n
|
|
INNER JOIN [v_Sync_V_Insurance_network_text] nt on nt.[INNT_insurance_network] = n.[Insurance_network_ID] and nt.[calc_subsidiary_id] = n.[calc_subsidiary_id]
|
|
WHERE n.[calc_subsidiary_id]=@sub
|
|
'
|
|
set @query = REPLACE(REPLACE(@query, CHAR(13), ' '), CHAR(10), ' ');
|
|
|
|
SET @payload = '
|
|
{
|
|
"QueryText": @query@
|
|
,"Parameters": {"sub": @sub@}
|
|
, "TargetServer": @host@
|
|
, "TargetDbName": "arizona"
|
|
, "Credential": "sqlLksrvTpCentAdm"
|
|
, "InjectResults": true
|
|
, "InjectServer": @local@
|
|
, "InjectDbName": "arizona"
|
|
, "InjectDbSchema": "bulk"
|
|
}
|
|
';
|
|
SET @payload = REPLACE(@payload, '@host@', isnull('"'+@host_central+'"', 'null'));
|
|
SET @payload = REPLACE(@payload, '@local@', isnull('"'+@host_local+',1433"', 'null'));
|
|
SET @payload = REPLACE(@payload, '@query@', isnull('"'+@query+'"', 'null'));
|
|
set @payload = REPLACE(@payload, '@sub@', convert(varchar(36), (SELECT TOP 1 ou.OU_subsidiary
|
|
FROM Organizational_unit ou with (nolock)
|
|
WHERE ou.Organizational_unit_ID = @cvCurrentOrganizationalUnit)));
|
|
EXEC [sp_Make_Restful_Call] @url = @azFuncUri,
|
|
@method = 'POST',
|
|
@payload = @payload,
|
|
@headers = DEFAULT,
|
|
@credentialName = NULL,
|
|
@timeoutSeconds = 10,
|
|
@response = @response OUTPUT,
|
|
@statusText = @statusText OUTPUT,
|
|
@statusCode = @statusCode OUTPUT;
|
|
IF @statusCode NOT BETWEEN 200 and 299
|
|
BEGIN
|
|
RAISERROR('Error when calling Azure Function: %s (Status code: %d). %s', 16, 1, @statusText, @statusCode, @payload);
|
|
RETURN;
|
|
END
|
|
|
|
DECLARE @tbl_insurance_network VARCHAR(255);
|
|
SELECT @tbl_insurance_network = [Value]
|
|
FROM OPENJSON(@response)
|
|
WHERE [Key]='TableName';
|
|
|
|
IF OBJECT_ID('tempdb..#insurance_network')IS NOT NULL
|
|
BEGIN
|
|
DROP TABLE #insurance_network;
|
|
END
|
|
|
|
CREATE TABLE #insurance_network(
|
|
[Insurance_network_ID] int,
|
|
[INN_PH_insurance] uniqueidentifier,
|
|
[INN_ofac_code] varchar(20),
|
|
[INN_veka_code] varchar(20),
|
|
[INN_active] bit,
|
|
[INN_master_ID] int,
|
|
[Insurance_network_text_ID] int,
|
|
[INNT_language] int,
|
|
[INNT_insurance_network] int,
|
|
[INNT_text] varchar(40),
|
|
[INNT_master_ID] int,
|
|
[INNT_documentation_url] varchar(2048)
|
|
);
|
|
SET @tpl = N'
|
|
INSERT INTO #insurance_network([Insurance_network_ID], [INN_PH_insurance], [INN_ofac_code], [INN_veka_code], [INN_active], [INN_master_ID], [Insurance_network_text_ID], [INNT_language], [INNT_insurance_network], [INNT_text], [INNT_master_ID], [INNT_documentation_url])
|
|
SELECT [Insurance_network_ID], [INN_PH_insurance], [INN_ofac_code], [INN_veka_code], [INN_active], [INN_master_ID], [Insurance_network_text_ID], [INNT_language], [INNT_insurance_network], [INNT_text], [INNT_master_ID], [INNT_documentation_url]
|
|
FROM @tbl@;
|
|
|
|
DROP TABLE @tbl@;
|
|
';
|
|
|
|
SET @q = REPLACE(@tpl, '@tbl@', @tbl_insurance_network);
|
|
EXEC (@q);
|
|
|
|
--SELECT *
|
|
UPDATE t
|
|
SET t.[INN_PH_insurance] = s.[INN_PH_insurance],
|
|
t.[INN_ofac_code] = s.[INN_ofac_code],
|
|
t.[INN_veka_code] = s.[INN_veka_code],
|
|
t.[INN_active] = s.[INN_active],
|
|
t.[INN_master_ID] = s.[INN_master_ID]
|
|
FROM [Insurance_network] t
|
|
JOIN (
|
|
SELECT DISTINCT
|
|
[Insurance_network_ID]
|
|
,[INN_PH_insurance]
|
|
,[INN_ofac_code]
|
|
,[INN_veka_code]
|
|
,[INN_active]
|
|
,[INN_master_ID]
|
|
FROM [#insurance_network]
|
|
)s ON s.[Insurance_network_ID] = t.[Insurance_network_ID]
|
|
WHERE s.[INN_PH_insurance] <> t.[INN_PH_insurance]
|
|
OR s.[INN_ofac_code] <> t.[INN_ofac_code]
|
|
OR s.[INN_veka_code] <> t.[INN_veka_code]
|
|
OR s.[INN_active] <> t.[INN_active]
|
|
OR s.[INN_master_ID] <> t.[INN_master_ID];
|
|
PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Updated values on [Insurance_network]. '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.';
|
|
|
|
INSERT INTO [Insurance_network] ([Insurance_network_ID],
|
|
[INN_PH_insurance],
|
|
[INN_ofac_code],
|
|
[INN_veka_code],
|
|
[INN_active],
|
|
[INN_master_ID])
|
|
SELECT DISTINCT
|
|
[Insurance_network_ID],
|
|
[INN_PH_insurance],
|
|
[INN_ofac_code],
|
|
[INN_veka_code],
|
|
[INN_active],
|
|
[INN_master_ID]
|
|
FROM [#insurance_network] s
|
|
WHERE NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Insurance_network] t
|
|
WHERE s.[Insurance_network_ID] = t.[Insurance_network_ID]
|
|
);
|
|
PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - inserted missing [Insurance_network]. '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.';
|
|
|
|
UPDATE t
|
|
SET t.[INNT_language] = s.[INNT_language]
|
|
, t.[INNT_insurance_network] = s.[INNT_insurance_network]
|
|
, t.[INNT_text] = s.[INNT_text]
|
|
, t.[INNT_master_ID] = s.[INNT_master_ID]
|
|
, t.[INNT_documentation_url] = s.[INNT_documentation_url]
|
|
FROM [Insurance_network_text] t
|
|
JOIN (
|
|
SELECT DISTINCT
|
|
[Insurance_network_text_ID],
|
|
[INNT_language],
|
|
[INNT_insurance_network],
|
|
[INNT_text],
|
|
[INNT_master_ID],
|
|
[INNT_documentation_url]
|
|
FROM [#insurance_network]
|
|
)s ON s.[Insurance_network_text_ID] =t.[Insurance_network_text_ID]
|
|
WHERE s.[INNT_language] <> t.[INNT_language]
|
|
OR s.[INNT_insurance_network] <> t.[INNT_insurance_network]
|
|
OR s.[INNT_text] <> t.[INNT_text]
|
|
OR s.[INNT_master_ID] <> t.[INNT_master_ID]
|
|
OR s.[INNT_documentation_url] <> t.[INNT_documentation_url]
|
|
;
|
|
PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Updated existing values on [Insurance_network_text]. '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.';
|
|
|
|
INSERT INTO [Insurance_network_text] ([Insurance_network_text_ID],
|
|
[INNT_language],
|
|
[INNT_insurance_network],
|
|
[INNT_text],
|
|
[INNT_master_ID],
|
|
[INNT_documentation_url])
|
|
SELECT DISTINCT
|
|
[Insurance_network_text_ID],
|
|
[INNT_language],
|
|
[INNT_insurance_network],
|
|
[INNT_text],
|
|
[INNT_master_ID],
|
|
[INNT_documentation_url]
|
|
FROM [#insurance_network] s
|
|
WHERE NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Insurance_network_text] t
|
|
WHERE t.[Insurance_network_text_ID] = s.[Insurance_network_text_ID]
|
|
);
|
|
PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - Added missing [Insurance_network_text]. '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.';
|
|
|
|
-- endregion
|
|
|
|
--#region update existing [Insurance_netw_serv_advice]
|
|
UPDATE t
|
|
SET t.[INSA_Advice_Enum] = s.[INSA_Advice_Enum],
|
|
t.[INSA_Insurance_Network_ID] = s.[INSA_Insurance_Network_ID],
|
|
t.[INSA_Item_ID] = s.[INSA_Item_ID]
|
|
FROM [Insurance_netw_serv_advice] t
|
|
JOIN [#v_Sync_V_Insurance_netw_serv_advice] s ON s.[Insurance_netw_serv_advice_ID] = t.[Insurance_netw_serv_advice_ID]
|
|
WHERE t.[INSA_Advice_Enum] <> s.[INSA_Advice_Enum]
|
|
OR t.[INSA_Item_ID] <> s.[INSA_Item_ID]
|
|
OR ISNULL(t.[INSA_Insurance_Network_ID],-1) <> ISNULL(s.[INSA_Insurance_Network_ID],-1);
|
|
PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - updated existing [Insurance_netw_serv_advice]. '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.';
|
|
--#endregion update existing [Insurance_netw_serv_advice]
|
|
|
|
--#region update existing advices
|
|
INSERT INTO [Insurance_netw_serv_advice] ([Insurance_netw_serv_advice_ID],
|
|
[INSA_Advice_Enum],
|
|
[INSA_Insurance_Network_ID],
|
|
[INSA_Item_ID])
|
|
SELECT
|
|
[Insurance_netw_serv_advice_ID],
|
|
[INSA_Advice_Enum],
|
|
[INSA_Insurance_Network_ID],
|
|
[INSA_Item_ID]
|
|
FROM [#v_Sync_V_Insurance_netw_serv_advice] s
|
|
WHERE NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Insurance_netw_serv_advice] t
|
|
WHERE t.[Insurance_netw_serv_advice_ID] = s.[Insurance_netw_serv_advice_ID]
|
|
)
|
|
AND NOT EXISTS(
|
|
SELECT 1
|
|
FROM [Insurance_netw_serv_advice] t
|
|
WHERE t.INSA_Advice_Enum = s.INSA_Advice_Enum
|
|
AND ISNULL(t.INSA_Insurance_Network_ID,-1) = ISNULL(s.INSA_Insurance_Network_ID,-1)
|
|
AND t.INSA_Item_ID=s.INSA_Item_ID
|
|
);
|
|
PRINT CONVERT(VARCHAR(20), CURRENT_TIMESTAMP, 114)+' - inserted missing [Insurance_netw_serv_advice]. '+REPLACE(REPLACE(CONVERT(VARCHAR(100), CONVERT(MONEY, @@rowcount), 1),',',''''),'.00','')+' row(s) affected.';
|
|
|
|
--#endregion update existing advices
|
|
END
|
|
END
|
|
ROLLBACK TRANSACTION
|