164 lines
6.2 KiB
Transact-SQL
164 lines
6.2 KiB
Transact-SQL
use arizona
|
|
|
|
|
|
return
|
|
|
|
/* 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. */
|
|
|
|
/* 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 '2024-08-27' AND '2024-08-27 23:59:59' /* Flag comme extrait */
|
|
and amt.AMT_table_name = 'PH_item_regulation_info' /* 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 '2024-08-27' AND '2024-08-27 23:59:59' /* Flag comme extrait */
|
|
--and AMR_APS_monitor_table = 1817 /* Item_Key */
|
|
|
|
/* 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 '2024-08-27' AND '2024-08-27 23:59:59'
|
|
and AMR_APS_monitor_table in (1817) /* Item_Key */ /* (334700 rows affected) */
|
|
|
|
/**************************/
|
|
|
|
/* Etapes de la synchro H (Step 3)*/
|
|
begin transaction
|
|
set xact_abort on
|
|
|
|
/*--- 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 '2024-08-27' AND '2024-08-27 23:59:59'
|
|
and AMR_APS_monitor_table in (1817)
|
|
)
|
|
|
|
/* Step 5 */
|
|
/* Passer une par une les differentes synchro de sub */
|
|
/* SYNC - ITEM - H Synchronize items from 000 to CENT */
|
|
declare @SubCENT INT,@SubSUN INT, @SubAMA int, @SubCVI int
|
|
|
|
SELECT @SubCENT = Subsidiary_ID FROM Arizona.dbo.Subsidiary WHERE SUB_code = '000'
|
|
SELECT @SubSUN = Subsidiary_ID FROM Arizona.dbo.Subsidiary WHERE SUB_code = 'CENT'
|
|
|
|
exec aps_Sync_H_Item_Master
|
|
@in_source_subsidiary_id = @SubCENT,
|
|
@in_dest_subsidiary_id = @SubSUN,
|
|
@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 = -5
|
|
|
|
|
|
/* SYNC - ITEM - H Synchronize items from 000 to AMA */
|
|
SELECT @SubCENT = Subsidiary_ID FROM Arizona.dbo.Subsidiary WHERE SUB_code = '000'
|
|
SELECT @SubAMA = Subsidiary_ID FROM Arizona.dbo.Subsidiary WHERE SUB_code = 'AMA'
|
|
|
|
exec aps_Sync_H_Item_Master
|
|
@in_source_subsidiary_id = @SubCENT,
|
|
@in_dest_subsidiary_id = @SubAMA,
|
|
@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 = -5
|
|
|
|
/* SYNC - ITEM - H Synchronize items from 000 to COOP */
|
|
SELECT @SubCENT = Subsidiary_ID FROM Arizona.dbo.Subsidiary WHERE SUB_code = '000'
|
|
SELECT @SubCVI = Subsidiary_ID FROM Arizona.dbo.Subsidiary WHERE SUB_code = 'COOP'
|
|
|
|
exec aps_Sync_H_Item_Master
|
|
@in_source_subsidiary_id = @SubCENT,
|
|
@in_dest_subsidiary_id = @SubCVI,
|
|
@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 = -5
|
|
|
|
/*-- 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
|
|
|
|
|
|
--rollback transaction
|
|
commit transaction
|