CREATE VIEW dbo.SIPARIS_SATIRLARI
AS
SELECT ITM.REF, FCH.FICHENO, FCH.DATE_, ORS.DURUM, FLN.AMOUNT, ITM.CODE, ISNULL(ITT.CODE, '') AS CODE2, CASE WHEN ITT.[NAME] IS NULL OR
                  ITT.[NAME] = 'YENI KAYIT' THEN ITM.ITEMNAME ELSE ITT.[NAME] END AS ITEMNAME, ITM.ITEMNAME_EN, CASE WHEN ITT.STGRPCODE IS NULL THEN ITM.GROUPCODE ELSE ITT.STGRPCODE END AS GROUPCODE, 
                  ISNULL(PRD.PRODUCERCODE, '') AS PRODUCERCODE, ISNULL
                      ((SELECT TOP (1) PRODUCERCODE
                        FROM      dbo.MALZEME_URETICI_KOD_LISTESI
                        WHERE   (REF = ITM.REF) AND (SIRA = 1) AND (STS = 2)), '') AS PRODUCERCODE2, ISNULL(CMP.DEFINITION_, '') AS DEFINITION_, FLN.LINEEXP1, FLN.LINEEXP2, ISNULL(PRC.PRICE, 0) AS PRICE, 
                  CASE WHEN PRC.CURRENCY = 1 THEN 'USD' WHEN PRC.CURRENCY = 13 THEN 'CNY' WHEN PRC.CURRENCY = 20 THEN 'EUR' WHEN PRC.CURRENCY = 160 THEN 'TL' ELSE 'USD' END AS CURRENCY, ISNULL(PRC.IHRSAT, 0) AS IHRSAT, 
                  CASE WHEN PRC.IHRCURRENCY = 1 THEN 'USD' WHEN PRC.IHRCURRENCY = 13 THEN 'CNY' WHEN PRC.IHRCURRENCY = 20 THEN 'EUR' WHEN PRC.IHRCURRENCY = 160 THEN 'TL' ELSE 'USD' END AS IHRCURRENCY, 
                  FLN.REF AS FLNREF, ITM.BCOLOR, FLN.KONTEYNERREF, ISNULL(FLN.NOSHIPPING, 0) AS NOSHIPPING, ISNULL(KON.FICHENO, '') AS KONFIS, CASE WHEN FLN.REPORTRATE IS NULL OR
                  FLN.REPORTRATE = 0 THEN 1 ELSE FLN.REPORTRATE END AS FLNRATE, FLN.PROMO, FLN.PROMO + FLN.AMOUNT AS TOPAMOUNT
FROM     dbo.FICHE_LINES AS FLN LEFT OUTER JOIN
                  dbo.FICHES AS FCH ON FCH.REF = FLN.FICHEREF LEFT OUTER JOIN
                  dbo.COMPANIES AS CMP ON CMP.REF = FLN.COMPANYREF LEFT OUTER JOIN
                  dbo.ORDER_STATUS AS ORS ON ORS.REF = FLN.STATUS LEFT OUTER JOIN
                  dbo.ITEMS AS ITM ON ITM.REF = FLN.ITEMREF LEFT OUTER JOIN
                  UNIVERSALDB.dbo.LG_011_ITEMS AS ITT ON ITM.LOGICALREF = ITT.LOGICALREF LEFT OUTER JOIN
                  dbo.MALZEME_URETICI_KOD_LISTESI AS PRD ON PRD.REF = ITM.REF AND PRD.SIRA = 1 AND PRD.STS = 1 LEFT OUTER JOIN
                  dbo.PRCLIST AS PRC ON PRC.REF = FLN.PRICEREF LEFT OUTER JOIN
                  dbo.KONTEYNER AS KON ON KON.REF = FLN.KONTEYNERREF