Satış Siprarişlerine göre depoda bulunan malzeme durumları

Satış Siprarişlerine göre depoda bulunan malzeme durumları

SELECT TOP (100) PERCENT
	CLCARD.DEFINITION_ AS Müşteri,
	ITEMS.CODE AS [Mamul Kodu],
	ITEMS.NAME AS [Mamul Adı],
	LG_001_02_ORFICHE.FICHENO AS [Evrak No],
	LG_001_02_ORFICHE.DATE_ AS [Sip.Tarihi],
	ORFLINE.AMOUNT AS Miktar,
	ORFLINE.SHIPPEDAMOUNT AS [Sevk.Edilen Mikt.],
	ORFLINE.DUEDATE AS [Sevk Tarihi],
	ORFLINE.CLOSED AS [0(Açık)],
	ORFLINE.PRICE,
	(SELECT
		SUM(DEPO.ONHAND) AS MIKTAR
	FROM LV_001_02_STINVTOT AS DEPO
	INNER JOIN LG_001_ITEMS AS STOK
		ON DEPO.STOCKREF = STOK.LOGICALREF
	WHERE (DEPO.INVENNO = '0')
	AND (STOK.CODE = ITEMS.CODE))
	AS [Depoda Bulunan]
FROM LG_001_SPECODES AS SPECODES
RIGHT OUTER JOIN LG_001_02_ORFLINE AS ORFLINE
	ON SPECODES.SPECODE = ORFLINE.SPECODE
LEFT OUTER JOIN LG_001_ITEMS AS ITEMS
	ON ORFLINE.STOCKREF = ITEMS.LOGICALREF
FULL OUTER JOIN LG_001_CLCARD AS CLCARD
RIGHT OUTER JOIN LG_001_SHIPINFO
RIGHT OUTER JOIN LG_001_02_ORFICHE
	ON LG_001_SHIPINFO.LOGICALREF = LG_001_02_ORFICHE.SHIPINFOREF
	ON CLCARD.LOGICALREF = LG_001_02_ORFICHE.CLIENTREF
	ON ORFLINE.ORDFICHEREF = LG_001_02_ORFICHE.LOGICALREF
WHERE (LG_001_02_ORFICHE.TRCODE = 1)
ORDER BY [Sip.Tarihi] DESC

Logo Tiger Depo Bazında Malzeme/Stok Durumları

Malzemlerin listesini depo bazında almak isterseniz aşağıdaki kodu kullanabilirsiniz.

SELECT     ITEMS.CODE, ITEMS.NAME, STINVTOT.INVENNO AS DEPO, SUM(STINVTOT.ONHAND) AS MIKTAR
FROM         LV_001_02_STINVTOT AS STINVTOT LEFT OUTER JOIN
LG_001_ITEMS AS ITEMS ON STINVTOT.STOCKREF = ITEMS.LOGICALREF
WHERE     (ITEMS.CARDTYPE = 1) AND STINVTOT.INVENNO = -1 /* Tüm Depolar için -1 */
GROUP BY ITEMS.CODE, ITEMS.NAME, STINVTOT.INVENNO, ITEMS.CARDTYPE
HAVING      (SUM(STINVTOT.ONHAND) <> 0)