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 TMP AS (
SELECT *
, row_number() OVER(PARTITION BY IdArt ORDER BY PdsArt DESC, IdFam, OrdreEmpl, D_DLUO, D_ENTREESTOCK, IdPalette) AS rnk
FROM (
SELECT LIGNEPIECEVENTE.N_NUMLGN NoLg
, LIGNEPIECEVENTE.N_IDARTICLE IdArt
, LIGNEPIECEVENTE.N_QTECMDEE QteCmd
, A.S_LBLSIMPLIFIE Designation
, A.N_POIDSARTICLE PdsArt
, EPLSTOCK.N_IDEPLSTOCK IdPalette
, V_CODEEPLSTOCK.S_CODEEPLSTOCK CodeEmplacementPhysique
, dbo.EPLSTOCK.N_QUANTITE QtePal
, ES.N_IDTYPESTOCKAGE IdTEmpl
, case when ES.N_IDTYPESTOCKAGE = 10 then 1
when ES.N_IDTYPESTOCKAGE = 2 then 2
when ES.N_IDTYPESTOCKAGE = 3 then 3
else 4 end OrdreEmpl -- ordre de prélèvement
, A.S_IDFAMILLEARTICLE IdFam
, EPLSTOCK.D_DLUO
, EPLSTOCK.D_ENTREESTOCK
FROM LIGNEPIECEVENTE
JOIN ENTETEPIECEVENTE ON (ENTETEPIECEVENTE.N_IDENTETEPIECEVENTE = LIGNEPIECEVENTE.N_IDENTETEPIECEVENTE AND ENTETEPIECEVENTE.N_IDTYPEPIECE = 1)
JOIN ARTICLE AS A ON A.N_IDARTICLE = LIGNEPIECEVENTE.N_IDARTICLE
JOIN EPLSTOCK ON A.N_IDARTICLE = EPLSTOCK.N_IDARTICLE
JOIN ENTITESTOCKAGE ES ON ES.N_IDENTITESTOCKAGE = EPLSTOCK.N_IDENTITESTOCKAGE
JOIN V_CODEEPLSTOCK ON V_CODEEPLSTOCK.N_IDEPLSTOCK = EPLSTOCK.N_IDEPLSTOCK
WHERE LIGNEPIECEVENTE.N_IDENTETEPIECEVENTE = 688034
AND S_STATUTLIGNE NOT IN ('E', 'A', 'S')
AND A.B_PREPADIRECTEARTICLE <> 1
AND LIGNEPIECEVENTE.B_KIT = 0
AND ES.N_IDSITE = 1
AND ES.S_IDBLOCAGE NOT IN ('SOR', 'ES')
AND EPLSTOCK.N_QUANTITE > 0) AS TOUT
)
SELECT
TMP.NoLg
, TMP.IdArt
, TMP.QteCmd
, TMP.Designation
, TMP.PdsArt
, TMP.IdPalette
, TMP.CodeEmplacementPhysique
, TMP.QtePal
, (SELECT SUM(UM1.N_QUANTITE) -- somme qté disponible sur palette
FROM EPLSTOCK AS UM1
JOIN ENTITESTOCKAGE ES1 ON ES1.N_IDENTITESTOCKAGE = UM1.N_IDENTITESTOCKAGE
WHERE UM1.N_IDARTICLE = TMP.IdArt
AND ES1.N_IDTYPESTOCKAGE = TMP.IdTEmpl
AND ES1.N_IDSITE = 1
AND ES1.S_IDBLOCAGE NOT IN ('SOR', 'ES')) AS QteStT
FROM TMP
WHERE RNK = 1
ORDER BY PdsArt desc, IdFam |
Partager