SQL Sorgu Stok İhtiyaç Raporu

Murat OSMA

Yönetici
Site Yöneticisi
Katılım
25 May 2018
Mesajlar
1,316
En iyi yanıt
11
Puanları
113
Konum
İstanbul
Web sitesi
excelarsivi.com
Ad Soyad
Murat OSMA
Office Versiyon
Office 365 TR+EN
Logo'da Stok İhtiyaç Raporu için bu sorguyu kullanabilirsiniz.

[HIDEREPLYTHANKS]
SQL:
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
[/HIDEREPLYTHANKS]
 
Son düzenleme:

WoooM

Yeni Üye
Katılım
3 Haz 2019
Mesajlar
10
En iyi yanıt
0
Puanları
1
Yaş
46
Konum
izmir
Ad Soyad
Muhittin
teşekkürler.
 

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
259
En iyi yanıt
4
Puanları
63
Yaş
30
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
Office Versiyon
2019
Merhaba,

Aşağıdaki alanı sorgudan kaldırıp deneyin.

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

yunussboru

Yeni Üye
Katılım
17 Mar 2020
Mesajlar
8
En iyi yanıt
0
Puanları
1
Yaş
24
Konum
MUĞLA
Ad Soyad
Yunus BORU
Bu güzel içerik için teşekkürler. Elinize sağlık
 
Üst Alt