SQL Sorgu Stok İhtiyaç Raporu

CREATE VIEW SNM_STOK_IHTIYAC_LISTESI_2015 AS
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_213_INVDEF DEF WHERE DEF.ITEMREF=I.LOGICALREF AND VARIANTREF=0 AND INVENNO=0)MINIMUMSTOK,
ISNULL(ROUND((SELECT SUM(ONHAND) FROM LV_213_01_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_213_01_DEMANDLINE WHERE MEETTYPE=0 AND ITEMREF=I.LOGICALREF AND YEAR(FICHEDATE)>2014
AND STATUS IN(1,2) AND LINETYPE=0)
+
ISNULL(ROUND((SELECT SUM(AMOUNT-SHIPPEDAMOUNT) FROM LG_213_PURCHOFFERLN WHERE STOCKREF=I.LOGICALREF AND CANCELLED=0
AND ((AMOUNT-SHIPPEDAMOUNT))>0 AND CLOSED=0 AND YEAR(DATE_)>2014 AND LINETYPE=0 AND DEMFICHEREF>0),0),0)
-
ISNULL(ROUND((SELECT SUM(AMOUNT-SHIPPEDAMOUNT) FROM LG_213_PURCHOFFERLN WHERE STOCKREF=I.LOGICALREF AND CANCELLED=0
AND ((AMOUNT-SHIPPEDAMOUNT))>0 AND CLOSED=0 AND YEAR(DATE_)>2014 AND LINETYPE=0 AND OFFTRANSREF>0),0),0)
-
ISNULL(ROUND((SELECT SUM(ORDEREDAMOUNT) FROM LG_213_PURCHOFFERLN WHERE STOCKREF=I.LOGICALREF AND CANCELLED=0
AND ((ORDEREDAMOUNT))>0 AND CLOSED=0 AND YEAR(DATE_)>2014 AND LINETYPE=0 AND DEMFICHEREF>0),0),0)
TALEPBEKLEYEN,

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

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

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

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

AND (SELECT DEF.MINLEVEL FROM LG_213_INVDEF DEF WHERE DEF.ITEMREF=I.LOGICALREF AND VARIANTREF=0 AND INVENNO=0)>0