CREATE VIEW dbo.SEPET_SATIRLARI
AS
SELECT        /* -- ILK KISIM -- */ ISNULL(ITS.CODE, 'KAYIT YOK!') AS YUKLU, ITM.CODE, ITM.PRODUCERCODE AS OEM2, ITS.ACTV, CASE WHEN ITM.[NAME] IS NULL THEN ITS.ITEMNAME ELSE CONCAT(ITM.[NAME], ' ', ITM.[NAME3]) 
                         END AS ITEMNAME, UML.ITEMNAME_EN, CASE WHEN ITM.STGRPCODE IS NULL THEN ITS.GROUPCODE ELSE ITM.STGRPCODE END AS GROUPCODE, ISNULL(PRD.PRODUCERCODE, '') AS PROCODE1, 
                         ISNULL(PRD2.PRODUCERCODE, '') AS PROCODE2, UML.FIRMCODE, UML.ALIMFIYAT, UML.ALIMKUR, UML.LASTPRICE, UML.LASTCURRENCY, UML.SALEPRICE, ITS.LINEEXP1, ITS.LINEEXP2, UML.STOCK, 
                         /* Durum alanini View_Malzeme_Siparis_Gecmisi'nden aliyoruz, yoksa 'BEKLEMEDE'*/ ISNULL(MSG.DURUM, 'BEKLEMEDE') AS DURUM, ISNULL(MSG.MIKTAR, 0) AS MIKTAR, ISNULL(MSG.SCOMP, '') AS SCOMP, 
                         ISNULL(MSG.GENEXP, '') AS GENEXP, ISNULL(MSG.FICHENO, '') AS FICHENO, CASE WHEN ITS.ACTV = 1 THEN 'YOK' ELSE 'VAR' END AS AKTIF, ITM.LOGICALREF, SPT.AMOUNT, KULL.ISIM AS KULL, SPT.ORDERDATE, 
                         MSG.CREATE_DATE, SPT.USERID, ITS.BCOLOR, ITM.UETDSUNDEF, ITS.ALTERNATIF_FIYAT, ITS.ALTERNATIF_FIRMA
FROM            UNIVERSALDB.dbo.STOK_RAPOR_SEPET_TEKIL_SIPARISLAR AS SPT LEFT JOIN
                         UNIVERSALDB.dbo.LG_011_ITEMS AS ITM ON SPT.STOCKREF = ITM.LOGICALREF LEFT JOIN
                         dbo.ITEMS AS ITS ON ITS.LOGICALREF = SPT.STOCKREF /* View_Malzeme_Siparis_Gecmisi'ne rn=1 sartiyla LEFT JOIN*/ LEFT JOIN
                         UNIVERSALDB.dbo.View_Malzeme_Siparis_Gecmisi AS MSG ON MSG.STOCKREF = SPT.STOCKREF AND MSG.USERID = SPT.USERID AND MSG.ORDERDATE = SPT.ORDERDATE AND MSG.rn = 1 LEFT JOIN
                         dbo.[1aUNI_malzeme_listesi] AS UML ON UML.REF = ITS.REF LEFT JOIN
                         dbo.MALZEME_URETICI_KOD_LISTESI AS PRD ON PRD.REF = ITS.REF AND PRD.SIRA = 1 AND PRD.STS = 1 LEFT JOIN
                         dbo.MALZEME_URETICI_KOD_LISTESI AS PRD2 ON PRD2.REF = ITS.REF AND PRD2.SIRA = 1 AND PRD2.STS = 2 LEFT JOIN
                         UNIVERSALDB.dbo.KULLANICILAR AS KULL ON KULL.REF = SPT.USERID
WHERE        (ITS.SIL = 0) AND (ITM.LOGICALREF NOT IN
                             (SELECT        LOGICALREF
                               FROM            UNIVERSALDB.dbo.LG_011_ITEMS
                               WHERE        ACTIVE = 1)) AND (ITM.LOGICALREF NOT IN
                             (SELECT        CARDREF
                               FROM            UNIVERSALDB.dbo.LG_011_PRCLIST
                               WHERE        ACTIVE = 1)) AND (ITM.LOGICALREF IN
                             (SELECT        CARDREF
                               FROM            UNIVERSALDB.dbo.LG_011_PRCLIST AS LG_011_PRCLIST_3
                               WHERE        PTYPE = 2))
UNION ALL
SELECT        'KAYIT YOK!' AS YUKLU, ITM.CODE, ITM.PRODUCERCODE AS OEM2, 1 AS ACTV, ITM.NAME AS ITEMNAME, '' AS ITEMNAME_EN, ITM.STGRPCODE AS GROUPCODE, '' AS PROCODE1, '' AS PROCODE2, '' AS FIRMCODE, 
                         0 AS ALIMFIYAT, '' AS ALIMKUR, 0 AS LASTPRICE, '' AS LASTCURRENCY, 0 AS SALEPRICE, '' AS LINEEXP1, '' AS LINEEXP2, GNT.ONHAND AS STOCK, 'BEKLEMEDE' AS DURUM, 0 AS MIKTAR, '' AS SCOMP, '' AS GENEXP, 
                         '' AS FICHENO, 'VAR' AS AKTIF, ITM.LOGICALREF, SPT.AMOUNT, KULL.ISIM AS KULL, SPT.ORDERDATE, '' AS CREATE_DATE, SPT.USERID, '' AS BCOLOR, ITM.UETDSUNDEF, NULL AS ALTERNATIF_FIYAT, NULL AS ALTERNATIF_FIRMA
FROM            UNIVERSALDB.dbo.STOK_RAPOR_SEPET_TEKIL_SIPARISLAR AS SPT LEFT JOIN
                         UNIVERSALDB.dbo.LG_011_ITEMS AS ITM ON SPT.STOCKREF = ITM.LOGICALREF LEFT JOIN
                         UNIVERSALDB.dbo.LV_011_07_GNTOTST AS GNT ON GNT.STOCKREF = ITM.LOGICALREF AND GNT.INVENNO = - 1 LEFT JOIN
                         UNIVERSALDB.dbo.KULLANICILAR AS KULL ON KULL.REF = SPT.USERID
WHERE        (SPT.STOCKREF NOT IN
                             (SELECT        LOGICALREF
                               FROM            dbo.ITEMS)) AND (ITM.LOGICALREF NOT IN
                             (SELECT        LOGICALREF
                               FROM            UNIVERSALDB.dbo.LG_011_ITEMS AS LG_011_ITEMS_1
                               WHERE        ACTIVE = 1)) AND (ITM.LOGICALREF NOT IN
                             (SELECT        CARDREF
                               FROM            UNIVERSALDB.dbo.LG_011_PRCLIST AS LG_011_PRCLIST_2
                               WHERE        ACTIVE = 1)) AND (ITM.LOGICALREF IN
                             (SELECT        CARDREF
                               FROM            UNIVERSALDB.dbo.LG_011_PRCLIST AS LG_011_PRCLIST_1
                               WHERE        PTYPE = 2));