190 lines
8.1 KiB
Transact-SQL
190 lines
8.1 KiB
Transact-SQL
use arizona
|
||
;
|
||
|
||
|
||
/*=======================================================================
|
||
|
||
Galenica - End Of Year - Tasks in order to force the Invoice
|
||
|
||
STEP 01 - Déclaration des variables
|
||
STEP 02 - Création / suppression de la table PHOU historisée
|
||
STEP 03 - Remplissage de la table PHOU historisée
|
||
STEP 04 - Construction de la table de changements
|
||
STEP 05 - Application des changements
|
||
STEP 06 - Rétablissement des anciennes valeurs
|
||
|
||
=======================================================================*/
|
||
/*=======================================================================
|
||
STEP 01 - Déclaration des variables
|
||
=======================================================================*/
|
||
declare @force_delete int = 08,
|
||
@mode int = 1, /* 1 = Check | 2 = Just do IT !!! */
|
||
@cmdsql varchar(max),
|
||
@today datetime = getdate()
|
||
;
|
||
declare @day_delay table (
|
||
day_from int,
|
||
day_to int,
|
||
delivery_delay int,
|
||
closing_mode int)
|
||
;
|
||
/*=======================================================================
|
||
STEP 02 - Création / suppression de la table PHOU historisée
|
||
=======================================================================*/
|
||
if (EXISTS (select *
|
||
from arizonacust.INFORMATION_SCHEMA.TABLES
|
||
where TABLE_SCHEMA = 'dbo'
|
||
and TABLE_NAME = 'PH_Organizational_Unit_history'))
|
||
begin
|
||
|
||
if @force_delete = 1 begin
|
||
select @cmdsql = 'drop table arizonacust.dbo.PH_Organizational_Unit_history'
|
||
exec (@cmdsql)
|
||
end
|
||
|
||
end else begin
|
||
|
||
create table arizonacust.dbo.PH_Organizational_Unit_history (
|
||
PH_Organizational_Unit_history_id int identity (1,1),
|
||
phouh_year int,
|
||
phouh_PH_Organizational_Unit_guid uniqueidentifier,
|
||
phouh_PHOU_AB_delivery_delay smallint,
|
||
phouh_PHOU_AB_closing_mode smallint
|
||
)
|
||
end
|
||
/*=======================================================================
|
||
STEP 03 - Remplissage de la table PHOU historisée
|
||
=======================================================================*/
|
||
insert into arizonacust.dbo.PH_Organizational_Unit_history (
|
||
phouh_year,
|
||
phouh_PH_Organizational_Unit_guid,
|
||
phouh_PHOU_AB_delivery_delay,
|
||
phouh_PHOU_AB_closing_mode)
|
||
select year(@today),
|
||
PH_Organizational_Unit_guid,
|
||
PHOU_AB_delivery_delay,
|
||
PHOU_AB_closing_mode
|
||
from dbo.Organizational_Unit ou (nolock)
|
||
join PH_Organizational_Unit P (nolock)
|
||
on PHOU_Organizational_Unit = Organizational_Unit_Id
|
||
join dbo.OU_store_history oush (nolock)
|
||
on oush.OU_store_history_GUID = (
|
||
select top 1 oush2.OU_store_history_GUID
|
||
from dbo.OU_store_history oush2 (nolock)
|
||
where isnull(oush2.OUSH_end_date,'2050-01-01') >= @today
|
||
and oush2.OUSH_organizational_unit = ou.Organizational_unit_ID
|
||
order by oush2.OUSH_start_date desc
|
||
)
|
||
join dbo.Address_criteria AC (nolock)
|
||
on AC.ADCR_subsidiary = ou.OU_subsidiary
|
||
and AC.ADCR_address = ou.OU_address
|
||
join dbo.Criteria C (nolock)
|
||
on C.Criteria_ID = AC.ADCR_criteria
|
||
and C.CR_code = 'PROD'
|
||
join dbo.Criteria_Type Ct (nolock)
|
||
on Ct.Criteria_type_ID = C.CR_criteria_type
|
||
and Ct.CRT_code = 'ENV'
|
||
|
||
where not exists (
|
||
select 1
|
||
from arizonacust.dbo.PH_Organizational_Unit_history p (nolock)
|
||
where p.phouh_year = year(@today)
|
||
and p.phouh_PH_Organizational_Unit_guid = PH_Organizational_Unit_guid
|
||
)
|
||
/*=======================================================================
|
||
STEP 04 - Construction de la table de changements
|
||
=======================================================================*/
|
||
/* 1. From December 12th to December 17th (included) set the Delivery_delay to 10 */
|
||
insert into @day_delay (day_from, day_to, delivery_delay,closing_mode)
|
||
select 12, 17, 10, 0
|
||
|
||
/* 2. From December 18th to December 24th (included) set the Delivery_delay to 5 */
|
||
insert into @day_delay (day_from, day_to, delivery_delay,closing_mode)
|
||
select 18, 24, 5, 0
|
||
|
||
/* 3. From December 25th to December 31th (included) set the Delivery_delay to 2 ClosingMode */
|
||
insert into @day_delay (day_from, day_to, delivery_delay,closing_mode)
|
||
select 25, 30, 2, 1
|
||
|
||
/*=======================================================================
|
||
STEP 05 - Application des changements
|
||
=======================================================================*/
|
||
if @mode = 2 begin
|
||
|
||
update phou
|
||
set phou.PHOU_AB_delivery_delay = case when d.delivery_delay <= phou.PHOU_AB_delivery_delay
|
||
then d.delivery_delay
|
||
else PHOU.PHOU_AB_delivery_delay
|
||
end,
|
||
phou.PHOU_AB_closing_mode = case when phou.PHOU_AB_closing_mode = 1
|
||
then PHOU.PHOU_AB_closing_mode
|
||
else d.closing_mode
|
||
end
|
||
from dbo.PH_organizational_unit PHOU (nolock)
|
||
join @day_delay d
|
||
on day(@today) between d.day_from and d.day_to
|
||
and month(@today) = 12
|
||
where PHOU_AB_delivery_delay is not null
|
||
and (PHOU_AB_delivery_delay <> case when d.delivery_delay <= phou.PHOU_AB_delivery_delay
|
||
then d.delivery_delay
|
||
else PHOU.PHOU_AB_delivery_delay
|
||
end
|
||
or phou.PHOU_AB_closing_mode <> case when phou.PHOU_AB_closing_mode = 1
|
||
then PHOU.PHOU_AB_closing_mode
|
||
else d.closing_mode
|
||
end)
|
||
|
||
|
||
end else begin
|
||
|
||
select phou.PH_organizational_unit_GUID,
|
||
phou.PHOU_AB_delivery_delay ,'-->', case when d.delivery_delay <= phou.PHOU_AB_delivery_delay
|
||
then d.delivery_delay
|
||
else PHOU.PHOU_AB_delivery_delay
|
||
end,
|
||
phou.PHOU_AB_closing_mode ,'-->', case when phou.PHOU_AB_closing_mode = 1
|
||
then PHOU.PHOU_AB_closing_mode
|
||
else d.closing_mode
|
||
end
|
||
from dbo.PH_organizational_unit PHOU (nolock)
|
||
join @day_delay d
|
||
on day(@today) between d.day_from and d.day_to
|
||
and month(@today) = 12
|
||
where PHOU_AB_delivery_delay is not null
|
||
and (PHOU_AB_delivery_delay <> case when d.delivery_delay <= phou.PHOU_AB_delivery_delay
|
||
then d.delivery_delay
|
||
else PHOU.PHOU_AB_delivery_delay
|
||
end
|
||
or phou.PHOU_AB_closing_mode <> case when phou.PHOU_AB_closing_mode = 1
|
||
then PHOU.PHOU_AB_closing_mode
|
||
else d.closing_mode
|
||
end )
|
||
|
||
end
|
||
/*=======================================================================
|
||
STEP 06 - Rétablissement des anciennes valeurs
|
||
=======================================================================*/
|
||
if month(@today) = 12 and day(@today) = 31 begin
|
||
|
||
if @mode = 2 begin
|
||
|
||
update phou
|
||
set phou.PHOU_AB_delivery_delay = phouh.phouh_PHOU_AB_delivery_delay,
|
||
phou.PHOU_AB_closing_mode = phouh.phouh_PHOU_AB_closing_mode
|
||
from dbo.PH_organizational_unit phou (nolock)
|
||
join arizonacust.dbo.PH_organizational_unit_history phouh (nolock)
|
||
on phouh.phouh_PH_Organizational_Unit_guid = phou.PH_organizational_unit_GUID
|
||
and phouh.phouh_year = year(@today)
|
||
|
||
end else begin
|
||
|
||
select phou.PHOU_AB_delivery_delay ,'-->', phouh.phouh_PHOU_AB_delivery_delay,
|
||
phou.PHOU_AB_closing_mode ,'-->', phouh.phouh_PHOU_AB_closing_mode
|
||
from dbo.PH_organizational_unit phou (nolock)
|
||
join arizonacust.dbo.PH_organizational_unit_history phouh (nolock)
|
||
on phouh.phouh_PH_Organizational_Unit_guid = phou.PH_organizational_unit_GUID
|
||
and phouh.phouh_year = year(@today)
|
||
|
||
end
|
||
|
||
end |