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
| INSERT INTO PROD.dbo.OXY_GAPLBL (IDENT, IDENT_ENTETE, GCPARTIC_IDENT, GCPARTIC_CODE, DESIG, QTE, QTE_SORTIE, PUHT, HT, UNITE_ACHAT_IDENT, UNITE_STOCKAGE_IDENT, UNITE_UTILISATION_IDENT, COEFFICIENT_ACHAT_STOCKAGE, COEFFICIENT_STOCKAGE_UTILISATION_BRUT, COEFFICIENT_STOCKAGE_UTILISATION_NET)
SELECT 'INV20200430' + RIGHT('00000' + CAST((ROW_NUMBER() OVER(ORDER BY Produit.ID ASC)) AS VARCHAR(5)),5), 'INV20200430',
a.IDENT, A.CODE, A.DESIG, Produit.QTE-produit.stock, 0, A.PUAHT, A.PUAHT*Produit.QTE, Produit.Unite_Utilisation_ID, Produit.Unite_Utilisation_ID, Produit.Unite_Utilisation_ID
FROM
(
SELECT ID.Qté_EnUniteUtilisationNette AS QTE, ID.Unite_Utilisation_ID, ID.Produit_Code AS CODE, CAST(ID.Inventaire_ID AS VARCHAR) AS ID, isnull(stock.qte,0) as stock
FROM PROD.dbo.InventaireDetail ID
INNER JOIN PROD.dbo.Inventaire ON ID = Inventaire_ID AND DATE = '20200430' AND ID.Qté_EnUniteUtilisationNette <> 0
LEFT JOIN
(
SELECT L.GCPARTIC_CODE, sum((L.qte-L.QTE_SORTIE)*ISNULL(L.COEFFICIENT_ACHAT_STOCKAGE,1)*ISNULL(L.COEFFICIENT_STOCKAGE_UTILISATION_BRUT,1)) as qte
FROM PROD.dbo.oxy_gaplbl L
where l.qte <> l.QTE_SORTIE
group by L.GCPARTIC_CODE
UNION SELECT A.CODE, SUM(FSL.QTE-FSL.QTE_SORTIE) as QTE
FROM PROD.dbo.SQL_FAB FAB INNER JOIN PROD.dbo.OXY_GCPARTIC A on A.IDENT = FAB.GCPARTIC_IDENT INNER JOIN PROD.dbo.SQL_FAB_SOUS_LOT FSL ON FSL.IDENT = FAB.IDENT
WHERE FSL.QTE <> FSL.QTE_SORTIE
GROUP BY A.CODE
) Stock ON Stock.GCPARTIC_CODE = id.Produit_Code
where isnull(stock.qte,0) < id.Qté_EnUniteUtilisationNette and ID.Qté_EnUniteUtilisationNette-isnull(stock.qte,0) > 0.01
) Produit
INNER JOIN PROD.dbo.OXY_GCPARTIC A ON A.CODE = Produit.Code
LEFT JOIN PROD.dbo.OXY_GAPLBL L ON L.GCPARTIC_CODE = Produit.Code AND DATE_LIVRAISON =
(SELECT MAX(DATE_LIVRAISON) FROM PROD.dbo.OXY_GAPLBL WHERE DATE_LIVRAISON <= '20200430' AND GCPARTIC_CODE = Produit.Code) |
Partager