CREATE VIEW dbo.MALZEME_SIPARIS_GECMISI
AS
SELECT        FCL.ITEMREF, TPP.AMOUNT AS MIKTAR, OST.DURUM, CPN.DEFINITION_ AS SCOMP, ROW_NUMBER() OVER (PARTITION BY FCL.ITEMREF
ORDER BY FCL.REF DESC) AS ORD, ITM.LOGICALREF, FCH.CREATE_DATE
FROM            dbo.FICHE_LINES AS FCL LEFT OUTER JOIN
                         dbo.FICHES AS FCH ON FCH.REF = FCL.FICHEREF LEFT OUTER JOIN
                         dbo.ORDER_STATUS AS OST ON OST.REF = FCL.[STATUS] LEFT OUTER JOIN
                         dbo.ITEMS AS ITM ON FCL.ITEMREF = ITM.REF LEFT OUTER JOIN
                         dbo.COMPANIES AS CPN ON FCL.COMPANYREF = CPN.REF LEFT OUTER JOIN
                         dbo.TOPLAM_SIPARIS AS TPP ON TPP.ITEMREF = FCL.ITEMREF
WHERE        FCL.[STATUS] NOT IN (1, 2) 
UNION ALL
SELECT   DISTINCT     FCL.ITEMREF, 100 AS MIKTAR, 'YOLDA', CPN.DEFINITION_ AS SCOMP, 1 AS ORD, ITM.LOGICALREF, '2024-09-09'
FROM            dbo.FICHE_LINES AS FCL LEFT OUTER JOIN
                         dbo.FICHES AS FCH ON FCH.REF = FCL.FICHEREF LEFT OUTER JOIN
                         dbo.ORDER_STATUS AS OST ON OST.REF = FCL.[STATUS] LEFT OUTER JOIN
                         dbo.ITEMS AS ITM ON FCL.ITEMREF = ITM.REF LEFT OUTER JOIN
                         dbo.COMPANIES AS CPN ON FCL.COMPANYREF = CPN.REF LEFT OUTER JOIN
                         dbo.TOPLAM_SIPARIS AS TPP ON TPP.ITEMREF = FCL.ITEMREF
WHERE       (ITM.LOGICALREF=67 OR ITM.LOGICALREF=18506)