CREATE VIEW dbo.GENIS_MALZEME_BILGILERI
AS
SELECT 
ITM.CODE AS [MALZEME KODU], 
CONCAT(ITM.[NAME],ITM.[NAME3]) AS [MALZEME AÇIKLAMASI], 
ISNULL(ITM.PRODUCERCODE, '') AS [ÜRETICI KODU 1], 
ISNULL(XT.URETICI_KODU2,'')  AS [ÜRETICI KODU 2], 
ISNULL(ITM.SPECODE,'') AS [RAF],
ISNULL(ITM.EXPCTGNO,'') AS [BARKOD],
ISNULL(ITM.SPECODE2,'') AS [ARAÇ MARKA], 
ISNULL(XT.MODEL_BILGI,'') AS [ARAÇ MODEL], 
ISNULL(SPECODE4,'') AS [MARKA],
ISNULL(TLF.TL,0) AS [LISTE FIYATI],
ISNULL(GNT.ONHAND, 0) AS [STOK MIKTARI], 
CASE WHEN ITM.ACTIVE = 0 THEN 'KULLANIMDA' ELSE 'KULLANIM DISI' END AS [DURUM],
CASE WHEN ITM.ACTIVE=1 OR TLF.TL IS NULL THEN 'YOK' ELSE 'VAR' END AS [B2B]
FROM LG_011_ITEMS AS ITM
LEFT OUTER JOIN LG_XT1001_011 AS XT ON XT.PARLOGREF = ITM.LOGICALREF
LEFT OUTER JOIN dbo.UNI_TL_FIYAT AS TLF ON TLF.CARDREF = ITM.LOGICALREF
LEFT OUTER JOIN LV_011_08_GNTOTST AS GNT ON GNT.STOCKREF=ITM.LOGICALREF AND GNT.INVENNO=-1
WHERE ITM.CARDTYPE=1