159 lines
6.3 KiB
Transact-SQL
159 lines
6.3 KiB
Transact-SQL
/* 10.03.2020 CEG - Synchronisation horizontale */
|
|
/* _D01500 - SYNC - H Synchronize items and addresses */
|
|
/* La correction de la synchronisation Horizontale se fait uniquement sur la central SUN (suncent) dans la base Arizona. */
|
|
|
|
|
|
/*
|
|
Step 1: identify the table id with the issue
|
|
Step 2: un-label rows from the table in step 1
|
|
step 2.1: Check the values of the param @in_prc_code_excluded with the one in the steps of the job, theymight change in time
|
|
step 2.2: Check the value of @in_dest_subsidiary_id (AMA was 2 before Vesta, is 102 after Vesta)
|
|
Step 3: select all the logic between "(Step 3)" in the code to the end of the script and execute it
|
|
Step 4: replace the "NOT IN (table_id_with error)" to a "IN (table_id_with error)" and execute again the script from the "(step 3)" to the end
|
|
Step 5: if errors are showing, correct them and start again step 4, until success
|
|
Step 6: Profit !
|
|
*/
|
|
USE [Arizona]
|
|
GO
|
|
|
|
--#region check how many AMR are impacted
|
|
/* AMR des données de la table en erreur */
|
|
select amr.AMR_horizontal_extraction_TS, amr.AMR_aps_ts, amr.AMR_extraction_timestamp, amr.*
|
|
from aps_monitor_row amr ( nolock)
|
|
join APS_monitor_table amt
|
|
on amt.APS_monitor_table_ID = amr.AMR_APS_monitor_table
|
|
where amr.AMR_APS_TS BETWEEN '2023-11-21' AND '2023-11-21 23:59:59' /* Flag comme extrait */
|
|
and amt.AMT_table_name = 'item_key' /* Table en erreur */
|
|
|
|
/* AMR totaux */
|
|
select amr.AMR_horizontal_extraction_TS, amr.AMR_aps_ts, amr.AMR_extraction_timestamp, amr.*
|
|
from aps_monitor_row amr ( nolock)
|
|
join APS_monitor_table amt
|
|
on amt.APS_monitor_table_ID = amr.AMR_APS_monitor_table
|
|
where amr.AMR_APS_TS BETWEEN '2023-11-21' AND '2023-11-21 23:59:59' /* Flag comme extrait */
|
|
--and AMR_APS_monitor_table = 460 /* Item_Key */
|
|
--#endregion
|
|
|
|
|
|
|
|
|
|
|
|
/* Délabéliser les monitor_row */
|
|
UPDATE aps_monitor_row
|
|
SET AMR_horizontal_extraction_TS = NULL,
|
|
AMR_extraction_timestamp = NULL
|
|
WHERE AMR_APS_TS BETWEEN '2023-06-15' AND '2023-06-15 23:59:59' /* Flag comme extrait */
|
|
AND AMR_APS_monitor_table NOT IN (1817) /* Item_Key = 460*/ /* (334700 rows affected) */
|
|
|
|
/**************************/
|
|
/* Etapes de la synchro H (Step 3)*/
|
|
|
|
/*--- suppression du label si existant ---*/
|
|
DELETE TT_extraction_timestamp
|
|
WHERE TTEXTS_horizontal_timestamp IS NOT NULL
|
|
|
|
/*--- creation du label ---*/
|
|
INSERT TT_extraction_timestamp
|
|
(TTEXTS_horizontal_timestamp)
|
|
VALUES (GETDATE())
|
|
|
|
/* Pour info */
|
|
SELECT * FROM TT_extraction_timestamp
|
|
|
|
/* Pour les tables qui ton une cle étrangere sur la table Item */
|
|
/*------------- Remplissage par la succursale source --------------*/
|
|
INSERT TT_Synchro_Item_List
|
|
(TTSYNCIT_spid,
|
|
TTSYNCIT_item_id,
|
|
TTSYNCIT_item_key_id)
|
|
SELECT @@spid,
|
|
itk.ITK_item,
|
|
itk.Item_Key_id
|
|
FROM item_key itk WITH (NOLOCK)
|
|
WHERE itk.ITK_Type = 1
|
|
AND itk.ITK_subsidiary = 100--@param_source_subsidiary_id
|
|
|
|
/* Step 4 */
|
|
/*-- recuperation du label --*/
|
|
DECLARE @extraction_timestamp DATETIME;
|
|
select top 1 @extraction_timestamp = TTEXTS_horizontal_timestamp
|
|
from TT_extraction_timestamp (nolock)
|
|
where TTEXTS_horizontal_timestamp is not NULL
|
|
|
|
SELECT @extraction_timestamp '@extraction_timestamp'
|
|
|
|
/*-- labelisation --*/
|
|
Update amr
|
|
set AMR_horizontal_extraction_TS = @extraction_timestamp
|
|
from aps_monitor_row amr (nolock)
|
|
join aps_monitor_table (nolock)
|
|
on aps_monitor_table_id = amr_aps_monitor_table
|
|
where AMR_horizontal_extraction_TS is null
|
|
and AMT_horizontal_synchronization = 1
|
|
and isnull(AMT_dynamic_synch_H, 0) = 1
|
|
/** Fin du step 4 *******************************************/
|
|
|
|
/* Pour la SP aps_Sync_H_TT_AMR_Create, mettre a jour la colonne AMR_horizontal_extraction_TS */
|
|
/* avec le extraction_timestamp */
|
|
UPDATE dbo.APS_monitor_row
|
|
SET AMR_horizontal_extraction_TS = @extraction_timestamp
|
|
WHERE APS_monitor_row_ID IN(
|
|
SELECT amr.APS_monitor_row_ID
|
|
from aps_monitor_row amr ( nolock)
|
|
join APS_monitor_table amt
|
|
on amt.APS_monitor_table_ID = amr.AMR_APS_monitor_table
|
|
where amr.AMR_APS_TS BETWEEN '2023-06-15' AND '2023-06-15 23:59:59'
|
|
AND amr.AMR_APS_monitor_table NOT IN (1817)) /* ID des AMR */
|
|
|
|
/* Step 5 */
|
|
/* Passer une par une les differentes synchro de sub */
|
|
/* SYNC - ITEM - H Synchronize items from 000 to CENT */
|
|
exec aps_Sync_H_Item_Master
|
|
@in_source_subsidiary_id = 100,
|
|
@in_dest_subsidiary_id = 1,
|
|
@in_dest_company_id = null,
|
|
@in_AMR_usage = 1,
|
|
@in_prc_code_excluded = 'PSUN; AUTO; CPUB; SPUB; FPUB; PDBR; XPUB; PPUB; ACTI; PROU;',
|
|
@in_crt_code_excluded = 'PLC',
|
|
@in_spid = @@spid,
|
|
@in_extraction_timestamp = @extraction_timestamp,
|
|
@in_debug = 1
|
|
|
|
/* SYNC - ITEM - H Synchronize items from 000 to AMA */
|
|
exec aps_Sync_H_Item_Master
|
|
@in_source_subsidiary_id = 100,
|
|
@in_dest_subsidiary_id = 102,
|
|
@in_dest_company_id = null,
|
|
@in_AMR_usage = 1,
|
|
@in_prc_code_excluded = ' ACTI;AUTO;CACT;CINT;CPUB;GPHA;GPUB;KUKO;LIMA;PACT;PDBR;PEXF;PMED;PPHA;PPUB;PREF;PRIC;RBP2;RBP3;RBP4;VERT;XEXF;XPUB;',
|
|
@in_crt_code_excluded = 'PLC',
|
|
@in_spid = @@spid,
|
|
@in_extraction_timestamp = @extraction_timestamp
|
|
|
|
/* SYNC - ITEM - H Synchronize items from 000 to COOP */
|
|
exec aps_Sync_H_Item_Master
|
|
@in_source_subsidiary_id = 100,
|
|
@in_dest_subsidiary_id = 124,
|
|
@in_dest_company_id = null,
|
|
@in_AMR_usage = 1,
|
|
@in_prc_code_excluded = 'PSUN; AUTO; CPUB; SPUB; FPUB; PDBR; XPUB; PPUB; ACTI; PROU;',
|
|
@in_crt_code_excluded = 'PLC',
|
|
@in_spid = @@spid,
|
|
@in_extraction_timestamp = @extraction_timestamp,
|
|
@in_debug = 9
|
|
|
|
/*-- labelisation --*/
|
|
/* SYNC - ITEM - Update AMR_horizontal_extraction_TS with label - End / STEP 10 */
|
|
Update amr
|
|
set AMR_horizontal_extraction_TS = @extraction_timestamp
|
|
from aps_monitor_row amr (nolock)
|
|
join aps_monitor_table (nolock)
|
|
on aps_monitor_table_id = amr_aps_monitor_table
|
|
where AMR_horizontal_extraction_TS is null
|
|
and amt_horizontal_synchronization = 1
|
|
|
|
/*--- suppression du label si existant ---*/
|
|
delete TT_extraction_timestamp
|
|
where TTEXTS_horizontal_timestamp is not null
|
|
|