SQL Sorgu Hammadde İhtiyaç Raporu

Murat OSMA

Yönetici
Site Yöneticisi
Katılım
25 May 2018
Mesajlar
1,247
En iyi yanıt
9
Puanları
113
Konum
İstanbul
Web sitesi
excelarsivi.com
Ad Soyad
Murat OSMA
Logo'da Hammadde İhtiyaç Raporu almak için bu sorguyu kullanabilirsiniz.

[HIDEREPLYTHANKS]
SQL:
SELECT CODE, NAME ,
ISNULL((SELECT MINLEVEL FROM LG_500_INVDEF WHERE ITEMREF=ITM.LOGICALREF AND INVENNO=0 AND VARIANTREF=0),0)ASGARI,
ISNULL((SELECT SUM(ONHAND) FROM LV_500_01_GNTOTST WHERE STOCKREF=ITM.LOGICALREF AND INVENNO=0),0)FIILI,
ISNULL((SELECT SUM(SIPARIS) FROM(
SELECT CASE WHEN AMOUNT>SHIPPEDAMOUNT THEN SUM(AMOUNT-SHIPPEDAMOUNT) ELSE 0 END SIPARIS
FROM LG_500_01_ORFLINE WHERE TRCODE=2 AND CLOSED=0 AND STOCKREF=ITM.LOGICALREF AND CANCELLED=0
GROUP BY AMOUNT,SHIPPEDAMOUNT)B),0) SIPARIS,
ISNULL((SELECT SUM(STL.PLNRSRVPRODOUT) FROM LV_500_01_GNTOTST STL WHERE STL.STOCKREF=ITM.LOGICALREF),0)REZERVE,
ISNULL((SELECT SUM(ONHAND) FROM LV_500_01_GNTOTST WHERE STOCKREF=ITM.LOGICALREF AND INVENNO=0)-
(SELECT SUM(STL.PLNRSRVPRODOUT) FROM LV_500_01_GNTOTST STL WHERE STL.STOCKREF=ITM.LOGICALREF),0) SON,
ISNULL((SELECT SUM(SIPARIS) FROM(
SELECT CASE WHEN AMOUNT>SHIPPEDAMOUNT THEN SUM(AMOUNT-SHIPPEDAMOUNT) ELSE 0 END SIPARIS
FROM LG_500_01_ORFLINE WHERE TRCODE=2 AND STOCKREF=ITM.LOGICALREF AND CANCELLED=0
GROUP BY AMOUNT,SHIPPEDAMOUNT)B)+
((SELECT SUM(ONHAND) FROM LV_500_01_GNTOTST WHERE STOCKREF=ITM.LOGICALREF AND INVENNO=0)-
(SELECT SUM(STL.PLNRSRVPRODOUT) FROM LV_500_01_GNTOTST STL WHERE STL.STOCKREF=ITM.LOGICALREF)),0)IHTIYAC,
ISNULL((SELECT MAXLEVEL FROM LG_500_INVDEF WHERE ITEMREF=ITM.LOGICALREF AND INVENNO=0 AND VARIANTREF=0)-
((SELECT SUM(SIPARIS) FROM(
SELECT CASE WHEN AMOUNT>SHIPPEDAMOUNT THEN SUM(AMOUNT-SHIPPEDAMOUNT) ELSE 0 END SIPARIS
FROM LG_500_01_ORFLINE WHERE TRCODE=2 AND STOCKREF=ITM.LOGICALREF AND CANCELLED=0
GROUP BY AMOUNT,SHIPPEDAMOUNT)B)+
((SELECT SUM(ONHAND) FROM LV_500_01_GNTOTST WHERE STOCKREF=ITM.LOGICALREF AND INVENNO=0)-
(SELECT SUM(STL.PLNRSRVPRODOUT) FROM LV_500_01_GNTOTST STL WHERE STL.STOCKREF=ITM.LOGICALREF))),0)NET_IHTIYAC
FROM LG_500_ITEMS ITM WHERE ITM.CARDTYPE=10 AND ITM.ACTIVE=0 ORDER BY ITM.CODE

SELECT SUM(SIPARIS) FROM(
SELECT CASE WHEN AMOUNT>SHIPPEDAMOUNT THEN SUM(AMOUNT-SHIPPEDAMOUNT) ELSE 0 END SIPARIS
FROM LG_500_01_ORFLINE WHERE TRCODE=2 AND CLOSED=0 AND STOCKREF=(SELECT LOGICALREF FROM LG_500_ITEMS WHERE CODE='1000120LNWH') AND CANCELLED=0
GROUP BY AMOUNT,SHIPPEDAMOUNT)B

SELECT AMOUNT-SHIPPEDAMOUNT ACIKSIPARIS,* FROM LG_500_01_ORFLINE
WHERE TRCODE=2 AND CANCELLED=0 AND STOCKREF=(SELECT LOGICALREF FROM LG_500_ITEMS WHERE CODE='1000120LNWH')
AND CLOSED=0 AND AMOUNT-SHIPPEDAMOUNT>0
[/HIDEREPLYTHANKS]
 
Son düzenleme:
Üst Alt