Uncategorized

Logo Stok İhtiyaç Raporu SQL

SELECT CODE,NAME,MINIMUMSTOK,STOKMIKTAR,TALEPBEKLEYEN,TALEPSIZSATINALMA+TALEPLISATINALMA SATINALMABEKLEYEN, URETIMBEKLEYEN,
CASE WHEN STOKMIKTAR-MINIMUMSTOK+TALEPSIZSATINALMA+TALEPLISATINALMA-TALEPBEKLEYEN<0 THEN
STOKMIKTAR-MINIMUMSTOK+TALEPSIZSATINALMA+TALEPLISATINALMA-TALEPBEKLEYEN ELSE 0 END IHTIYAC
FROM(
SELECT I.CODE, I.NAME,
(SELECT DEF.MINLEVEL FROM LG_001_INVDEF DEF WHERE DEF.ITEMREF=I.LOGICALREF AND VARIANTREF=0 AND INVENNO=0)MINIMUMSTOK,
ISNULL(ROUND((SELECT SUM(ONHAND) FROM LV_001_03_GNTOTST WHERE STOCKREF=I.LOGICALREF AND INVENNO!=-1),0),0)STOKMIKTAR,

(SELECT CASE WHEN SUM(AMOUNT-MEETAMNT-CANCAMOUNT)>0 THEN SUM(AMOUNT-MEETAMNT-CANCAMOUNT) ELSE 0 END
FROM LG_001_03_DEMANDLINE WHERE MEETTYPE=0 AND ITEMREF=I.LOGICALREF AND YEAR(FICHEDATE)>2020
AND STATUS IN(1,2) AND LINETYPE=0)
+
ISNULL(ROUND((SELECT SUM(AMOUNT-SHIPPEDAMOUNT) FROM LG_001_PURCHOFFERLN WHERE STOCKREF=I.LOGICALREF AND CANCELLED=0
AND ((AMOUNT-SHIPPEDAMOUNT))>0 AND CLOSED=0 AND YEAR(DATE_)>2020 AND LINETYPE=0 AND DEMFICHEREF>0),0),0)
-
ISNULL(ROUND((SELECT SUM(AMOUNT-SHIPPEDAMOUNT) FROM LG_001_PURCHOFFERLN WHERE STOCKREF=I.LOGICALREF AND CANCELLED=0
AND ((AMOUNT-SHIPPEDAMOUNT))>0 AND CLOSED=0 AND YEAR(DATE_)>2020 AND LINETYPE=0 AND OFFTRANSREF>0),0),0)
-
ISNULL(ROUND((SELECT SUM(ORDEREDAMOUNT) FROM LG_001_PURCHOFFERLN WHERE STOCKREF=I.LOGICALREF AND CANCELLED=0
AND ((ORDEREDAMOUNT))>0 AND CLOSED=0 AND YEAR(DATE_)>2020 AND LINETYPE=0 AND DEMFICHEREF>0),0),0)
TALEPBEKLEYEN,

ISNULL(ROUND((SELECT SUM(AMOUNT-SHIPPEDAMOUNT) FROM LG_001_03_ORFLINE WHERE STOCKREF=I.LOGICALREF AND TRCODE=2 AND CANCELLED=0
AND ((AMOUNT-SHIPPEDAMOUNT))>0 AND CLOSED=0 AND YEAR(DATE_)>2020 AND LINETYPE=0 AND OFFTRANSREF=0),0),0)TALEPSIZSATINALMA,

ISNULL(ROUND((SELECT SUM(AMOUNT-SHIPPEDAMOUNT) FROM LG_001_03_ORFLINE WHERE STOCKREF=I.LOGICALREF AND TRCODE=2 AND CANCELLED=0
AND ((AMOUNT-SHIPPEDAMOUNT))>0 AND CLOSED=0 AND YEAR(DATE_)>2020 AND LINETYPE=0 AND OFFTRANSREF>0),0),0)TALEPLISATINALMA,

ISNULL(ROUND((SELECT SUM(AMOUNT-PLNAMOUNT) FROM LG_001_03_STLINE WHERE TRCODE=12 AND LPRODSTAT=1 AND
STOCKREF=I.LOGICALREF AND AMOUNT-PLNAMOUNT>0 AND CANCELLED=0 AND YEAR(DATE_)>2020),0),0)URETIMBEKLEYEN

FROM LG_001_ITEMS I WHERE I.ACTIVE=0
AND (SELECT DEF.MINLEVEL FROM LG_001_INVDEF DEF WHERE DEF.ITEMREF=I.LOGICALREF AND VARIANTREF=0 AND INVENNO=0)>0
) AS TABLO

Bir cevap yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir