CREATE VIEW dbo.CARI_MALZEME_SATIS_LISTESI_TUM_YILLAR
AS
SELECT ITM.CODE AS [MALZEME KODU], CONCAT(CONCAT(ITM.[NAME], ' '), ITM.[NAME3]) AS [MALZEME AÇIKLAMASI], ITM.SPECODE4 AS MARKA, 
ISNULL(GNT.ONHAND, 0) AS [STOK MIKTARI], ISNULL(PRC.PRICE, 0) AS [BIRIM FIYAT], CASE WHEN PRC.CURRENCY=1 THEN 'USD' ELSE 'TL' END AS [KUR],
CASE WHEN ITM.ACTIVE=0 THEN 'KULLANIMDA' ELSE 'KULLANIM DISI' END AS [MALZEME DURUMU]
FROM LG_011_ITEMS AS ITM LEFT OUTER JOIN
LG_011_PRCLIST AS PRC ON PRC.CARDREF=ITM.LOGICALREF LEFT OUTER JOIN
LV_011_07_GNTOTST AS GNT ON GNT.STOCKREF=ITM.LOGICALREF AND GNT.INVENNO=-1
WHERE 
ITM.LOGICALREF IN 
(SELECT DISTINCT STOCKREF FROM
(
(SELECT DISTINCT STOCKREF FROM LG_011_07_STLINE WHERE TRCODE=8 AND LINETYPE=0 AND CANCELLED=0 AND CLIENTREF IN (1932, 3013))
UNION ALL
(SELECT DISTINCT STOCKREF FROM LG_011_06_STLINE WHERE TRCODE=8 AND LINETYPE=0 AND CANCELLED=0 AND CLIENTREF IN (1932, 3013))
UNION ALL
(SELECT DISTINCT STOCKREF FROM LG_011_05_STLINE WHERE TRCODE=8 AND LINETYPE=0 AND CANCELLED=0 AND CLIENTREF IN (1932, 3013))
UNION ALL
(SELECT DISTINCT STOCKREF FROM LG_011_04_STLINE WHERE TRCODE=8 AND LINETYPE=0 AND CANCELLED=0 AND CLIENTREF IN (1932, 3013))
UNION ALL
(SELECT DISTINCT STOCKREF FROM LG_011_03_STLINE WHERE TRCODE=8 AND LINETYPE=0 AND CANCELLED=0 AND CLIENTREF IN (1932, 3013))
UNION ALL
(SELECT DISTINCT STOCKREF FROM LG_011_02_STLINE WHERE TRCODE=8 AND LINETYPE=0 AND CANCELLED=0 AND CLIENTREF IN (1932, 3013))
UNION ALL
(SELECT DISTINCT STOCKREF FROM LG_011_01_STLINE WHERE TRCODE=8 AND LINETYPE=0 AND CANCELLED=0 AND CLIENTREF IN (1932, 3013))
)QQ)