CREATE VIEW dbo.OEM_ILE_LISTELEME
AS
SELECT        ITM.CODE, ISNULL(ITM.NAME, '') AS LOGONAME, ISNULL(MAL.ITEMNAME, '') AS PRONAME, ISNULL(MAL.ITEMNAME_EN, '') AS ENNAME, '' AS LOGOENNAME, ISNULL(ITM.STGRPCODE, '') AS LOGOGROUP, 
                         ISNULL(MAL.GROUPCODE, '') AS PROGROUP, ISNULL(ITM.PRODUCERCODE, '') AS LOGOPROCCODE, ISNULL(PRD.PRODUCERCODE, '') AS PROPRODCODE, ISNULL(CMP.DEFINITION_, '') AS PROFIRMCODE, 
                         '' AS LOGOFIRMCODE, ISNULL(PRC.LINEEXP1, '') AS PROLXP1CODE, '' AS LOGOLXP1CODE, ISNULL(PRC.LINEEXP2, '') AS PROLXP2CODE, '' AS LOGOLXP2CODE, REPLACE(ISNULL(PRC.PRICE, 0), ',', '.') AS PROALIMFIYAT, 
                         0 AS LOGOALIMFIYAT, 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 PROALIMKUR,
                          '' AS LOGOALIMKUR, REPLACE(ISNULL(PRC.IHRSAT, 0), ',', '.') AS PROIHR, 0 AS LOGOIHR, 
                         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 PROIHRKUR, 
                         '' AS LOGOIHRKUR
FROM            UNIVERSALDB.dbo.LG_011_ITEMS AS ITM LEFT OUTER JOIN
                         dbo.ITEMS AS MAL ON MAL.LOGICALREF = ITM.LOGICALREF LEFT OUTER JOIN
                         dbo.MALZEME_SIRALI_FIYAT AS PRC ON PRC.ITEMREF = MAL.REF LEFT OUTER JOIN
                         dbo.MALZEME_URETICI_KOD_LISTESI AS PRD ON PRD.REF = MAL.REF AND PRD.SIRA = 1 LEFT OUTER JOIN
                         dbo.COMPANIES AS CMP ON CMP.REF = PRD.CMPREF