1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
| Merge Into Ta_Contrat_Etat_Contrat T1
Using
(
Select
Cod_Contrat,
Dat_Debut,
Dat_Fin_Calculee,
Top_Actuel_Calculee
From
(
Select
A . Cod_Contrat Cod_Contrat ,
Dat_Debut ,
Nvl (( Lag ( A.Dat_Debut , 1 ) Over (Partition By A.Cod_Contrat Order By Dat_Debut Desc))- 1 , '31/12/2999' ) Dat_Fin_Calculee ,
Decode (( Lag ( A.Dat_Debut , 1 ) Over (Partition By A.Cod_Contrat Order By Dat_Debut Desc))- 1 ,null, 1 , 0 ) Top_Actuel_Calculee ,
(Case When ( Max ( Dat_Debut ) Over (Partition By A.Cod_Contrat )) > Dat_Arretee_Prec Then 1 Else 0 End) Top_Contrat_Impacte
From
ta_Contrat_Etat_Contrat A ,
tp_Pilotage_Arrete B
Where
A . Dat_Fin >= B . Dat_Arretee_Prec
) T2
Where Top_Contrat_Impacte = 1
) T3
On
(T1.Dat_Debut = T3.Dat_Debut And T1.Cod_Contrat = T3.Cod_Contrat)
When Matched Then
Update
Set
T1.Dat_Fin = T3.Dat_Fin_Calculee,
T1.Top_Actuel = T3.Top_Actuel_Calculee
When Not Matched Then
Insert (Cod_Contrat) Values (T3.Cod_Contrat)
; |
Partager