• Merhaba Ziyaretçi,
    10 Aralık'a Kadar VIP Paket %50 İNDİRİMLİ (bir kere öde, ömür boyu kullan)
    İndirimden yararlanmak için resme tıklayın. 👇🏻
  • Sn. Ziyaretçi,
    PEAKUP E-Book & Makale & Videoları yayınlandı.

SQL Sorgu Stok İhtiyaç Raporu

Murat OSMA

Yönetici
Site Yöneticisi
Katılım
25 May 2018
Mesajlar
1,253
En iyi yanıt
9
Puanları
113
Konum
İstanbul
Web sitesi
excelarsivi.com
Ad Soyad
Murat OSMA
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:

Vedat ÖZER

Logo Uzmanı
Geliştirici
Katılım
4 Haz 2018
Mesajlar
246
En iyi yanıt
4
Puanları
43
Yaş
30
Konum
Antalya / Merkez
Ad Soyad
Vedat ÖZER
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ş
23
Konum
MUĞLA
Ad Soyad
Yunus BORU
Bu güzel içerik için teşekkürler. Elinize sağlık
 
Üst Alt