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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55
|
WITH EcheanceWithReliqua AS(
SELECT
E.[VBELN_ECH] AS [N°Commande]
,E.[POSNR_ECH] AS [N° ligne Commande]
,E.[ETENR_ECH] AS [N° echance]
,[WMENG_ECH_ORI] AS [Qt echeance]
,[QTE_LIV] * E.[KPEIN_CDE] AS [QT livree]
,case
When [WMENG_ECH_ORI] >= ([QTE_LIV] * E.[KPEIN_CDE]) then [WMENG_ECH_ORI] - ([QTE_LIV] * E.[KPEIN_CDE])
else 0
end AS qte
,Case
When [WMENG_ECH_ORI] >= ([QTE_LIV] * E.[KPEIN_CDE]) then 0
else ([QTE_LIV] * E.[KPEIN_CDE]) - [WMENG_ECH_ORI]
end AS reliquas
,ROW_NUMBER() Over(Partition BY E.[VBELN_ECH]
,E.[POSNR_ECH] ORDER BY E.[ETENR_ECH]) AS RowNum
FROM FACTECHEANCES E
INNER JOIN DIMECHEANCES_LIVRAISONS L
ON (E.[VBELN_ECH]=L.[VBELN_ECH] AND E.[POSNR_ECH]= L.[POSNR_ECH]))
,
Recursion AS
(
SELECT * FROM EcheanceWithReliqua
WHERE RowNum = 1
union ALL
SELECT e.[N°Commande]
,E.[N° ligne Commande]
,E.[N° echance]
,E.[Qt echeance]
,E.[QT livree]
,case
When E.[Qt echeance] >= R.[reliquas] then E.[Qt echeance] - R.[reliquas]
else 0
end AS qte
,Case
When E.[Qt echeance] >= R.[reliquas] then 0
else R.[reliquas]- E.[Qt echeance]
end AS reliquas
,E.RowNum
FROM Recursion R
INNER JOIN EcheanceWithReliqua E
ON ( E.[N°Commande]=R.[N°Commande] AND E.[N° ligne Commande]= R.[N° ligne Commande] AND E.RowNum = R.RowNum +1 )
)
UPDATE FACTECHEANCES
SET WMENG_ECH_ORI = R.qte
FROM FACTECHEANCES E
INNER JOIN Recursion R
ON (E.VBELN_ECH=R.[N°Commande] AND E.POSNR_ECH= R.[N° ligne Commande] AND E.ETENR_ECH= R.[N° echance])
OPTION (MAXRECURSION 0) |
Partager