SELECT '020' AS Firma, '01' AS Dönem, CASE ORFLINE.TRCODE WHEN 1 THEN 'Alinan siparis' WHEN 2 THEN 'Verilen Siparis' ELSE 'Tanımsız' END AS [İşlem Türü], Convert(datetime, ORFICHE.DATE_, 104)
AS [İşlem Tarihi], CONVERT(INT, CONVERT(DATETIME, ORFLINE.DUEDATE, 104)) - CONVERT(INT, CONVERT(DATETIME, ORFICHE.DATE_, 104)) AS Gecikme, ORFICHE.FICHENO AS [Fiş Numarası],
ORFICHE.DOCODE AS [Belge Numarası], ORFICHE.SPECODE AS [Fiş ÖzelKodu], CLCARD.CODE AS [CH Kodu], CLCARD.DEFINITION_ AS [CH Unvanı], CONVERT(VARCHAR(3), ISYERI.NR)
+ ' ~ ' + ISYERI.NAME AS İşyeri, CONVERT(VARCHAR(3), AMBARLAR.NR) + ' ~ ' + AMBARLAR.NAME AS Ambar, dbo.LG_SLSMAN.CODE AS [Satış Elemanı Kodu],
dbo.LG_SLSMAN.DEFINITION_ AS [Satış Elemanı Açıklaması],
CASE ORFLINE.LINETYPE WHEN 0 THEN 'Malzeme' WHEN 1 THEN 'Promosyon' WHEN 2 THEN 'İndirim' WHEN 3 THEN 'Masraf' WHEN 4 THEN 'Hizmet' WHEN 5 THEN 'Depozito' WHEN 6 THEN 'Karma koli satırı'
WHEN 7 THEN 'Karma Koli detayları' WHEN 8 THEN 'Sabit Kıymet' ELSE 'Tanımsız' END AS [Satır Türü],
CASE ORFLINE.LINETYPE WHEN 0 THEN ITEMS.CODE WHEN 4 THEN SRVCARD.CODE END AS [Malzeme Kodu],
CASE ORFLINE.LINETYPE WHEN 0 THEN ITEMS.NAME WHEN 4 THEN SRVCARD.DEFINITION_ END AS [Malzeme Açıklaması], ORFLINE.AMOUNT AS Miktar, ORFLINE.SHIPPEDAMOUNT AS [Sevk Edilen Miktar],
CASE WHEN ORFLINE.SHIPPEDAMOUNT > ORFLINE.AMOUNT THEN 0 ELSE ORFLINE.AMOUNT - ORFLINE.SHIPPEDAMOUNT END AS [Bekleyen Miktar], ORFLINE.AMOUNT * (ORFLINE.UINFO2 / ORFLINE.UINFO1)
AS [Ana Birim Miktar], ORFLINE.SHIPPEDAMOUNT * (ORFLINE.UINFO2 / ORFLINE.UINFO1) AS [Ana Birim Sevk Edilen Miktar], CASE WHEN (ORFLINE.SHIPPEDAMOUNT * (ORFLINE.UINFO2 / ORFLINE.UINFO1))
> (ORFLINE.AMOUNT * (ORFLINE.UINFO2 / ORFLINE.UINFO1)) THEN 0 ELSE (ORFLINE.AMOUNT * (ORFLINE.UINFO2 / ORFLINE.UINFO1)) - (ORFLINE.SHIPPEDAMOUNT * (ORFLINE.UINFO2 / ORFLINE.UINFO1))
END AS [Ana Birim Bekleyen Miktar], UNITSETL.CODE AS [Birim Kodu], ORFLINE.UINFO1 AS [Çevrim Katsayısı 1], ORFLINE.UINFO2 AS [Çevrim Katsayısı 2], ORFLINE.PRICE AS [Birim Fiyat],
ORFLINE.VAT AS KDV, ORFLINE.TOTAL AS [Brüt Tutar], ORFLINE.VATMATRAH AS [KDV Matrahı], ORFLINE.LINENET AS [Net Satır Tutarı], ORFICHE.REPORTRATE AS [RD Kuru],
CASE ORFLINE.REPORTRATE WHEN 0 THEN 0 ELSE ORFLINE.PRICE / ORFLINE.REPORTRATE END AS [RD Birim Fiyat],
CASE ORFLINE.REPORTRATE WHEN 0 THEN 0 ELSE ORFLINE.TOTAL / ORFLINE.REPORTRATE END AS [RD Brüt Tutar],
CASE ORFLINE.REPORTRATE WHEN 0 THEN 0 ELSE ORFLINE.VAT / ORFLINE.REPORTRATE END AS [RD KDV],
CASE ORFLINE.REPORTRATE WHEN 0 THEN 0 ELSE ORFLINE.LINENET / ORFLINE.REPORTRATE END AS [RD Satır Net Tutarı], ORFICHE.UPDTRCURR AS İD,
CASE ORFICHE.TRCURR WHEN 0 THEN 'TL' ELSE L_CURRENCYLIST.CURCODE END AS [İD Kuru], CASE ORFLINE.TRRATE WHEN 0 THEN 0 ELSE ORFLINE.PRICE / ORFLINE.TRRATE END AS [İD Birim Fiyat],
CASE ORFLINE.TRRATE WHEN 0 THEN 0 ELSE ORFLINE.VAT / ORFLINE.TRRATE END AS [İD KDV], CASE ORFLINE.TRRATE WHEN 0 THEN 0 ELSE ORFLINE.TOTAL / ORFLINE.TRRATE END AS [İD Brüt Tutar],
CASE ORFLINE.TRRATE WHEN 0 THEN 0 ELSE ORFLINE.LINENET / ORFLINE.TRRATE END AS [İD Net Satır Tutarı], ORFLINE.SPECODE AS [Hareket Özel Kodu], ORFLINE.DELVRYCODE AS [Satır Teslimat Kodu],
Convert(datetime,STFICHE.DATE_, 104) AS [Teslim Tarihi], ORFLINE.LINEEXP AS [Satır Açıklaması], CASE WHEN ORFLINE.CLOSED LIKE '0' THEN 'ACIK' ELSE 'KAPALI' END AS Statü,
CASE WHEN ORFLINE.DORESERVE LIKE '0' THEN 'REZERVE DEGIL' ELSE 'REZERVE' END AS Rezerve,
CASE ORFLINE.STATUS WHEN 1 THEN 'Oneri' WHEN 2 THEN 'Sevkedilemez' WHEN 4 THEN 'Sevkedilebilir' ELSE 'Ne Oldugu Belirsiz' END AS [Onay Bilgisi],
ORFICHE.CUSTORDNO AS [Müşteri Sipariş Numarası], ORFICHE.DOCTRACKINGNR AS [Döküman İzleme Numarası], SHIPINFO.CODE AS [Sevkiyat Adresi Kodu], SHIPINFO.NAME AS [Sevkiyat Adresi Açıklaması],
ORFICHE.GENEXP1 + ' ' + ORFICHE.GENEXP2 + ' ' + ORFICHE.GENEXP3 + ' ' + ORFICHE.GENEXP4 AS [Genel Açıklama], ORFICHE.SHPTYPCOD AS [Teslim Şekli], ORFICHE.SHPAGNCOD AS [Taşıyıcı Kodu]
FROM dbo.LG_420_01_ORFICHE AS ORFICHE WITH (NOLOCK) LEFT OUTER JOIN
dbo.LG_420_01_ORFLINE AS ORFLINE WITH (NOLOCK) ON ORFICHE.LOGICALREF = ORFLINE.ORDFICHEREF LEFT OUTER JOIN
dbo.LG_420_01_STLINE AS STLINE WITH (NOLOCK) ON ORFLINE.LOGICALREF = STLINE.ORDTRANSREF LEFT OUTER JOIN
dbo.LG_420_01_STFICHE AS STFICHE WITH (NOLOCK) ON STFICHE.LOGICALREF = STLINE.STFICHEREF LEFT OUTER JOIN
dbo.LG_420_CLCARD AS CLCARD WITH (NOLOCK) ON ORFLINE.CLIENTREF = CLCARD.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_ITEMS AS ITEMS WITH (NOLOCK) ON ORFLINE.STOCKREF = ITEMS.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_SRVCARD AS SRVCARD WITH (NOLOCK) ON SRVCARD.LOGICALREF = ORFLINE.STOCKREF AND ORFLINE.LINETYPE = 4 LEFT OUTER JOIN
dbo.LG_420_SPECODES AS STGRP WITH (NOLOCK) ON STGRP.SPECODE = ITEMS.STGRPCODE AND STGRP.CODETYPE = 4 AND STGRP.SPECODETYPE = 0 LEFT OUTER JOIN
dbo.LG_420_UNITSETL AS UNITSETL WITH (NOLOCK) ON ORFLINE.UOMREF = UNITSETL.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_UNITSETF AS UNITSETF WITH (NOLOCK) ON ORFLINE.USREF = UNITSETF.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_PRCARDS AS PRCARDS WITH (NOLOCK) ON ORFLINE.PROMREF = PRCARDS.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_PAYPLANS AS PAYPLANS WITH (NOLOCK) ON ORFICHE.PAYDEFREF = PAYPLANS.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_PAYLINES AS PAYLINES WITH (NOLOCK) ON PAYLINES.PAYPLANREF = PAYPLANS.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_PAYPLANS AS PAYPLANS_2 WITH (NOLOCK) ON ORFLINE.PAYDEFREF = PAYPLANS_2.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_PAYLINES AS PAYLINES_2 WITH (NOLOCK) ON PAYLINES_2.PAYPLANREF = PAYPLANS.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_EMCENTER AS LG_420_EMCENTER WITH (NOLOCK) ON ORFLINE.CENTERREF = LG_420_EMCENTER.LOGICALREF LEFT OUTER JOIN
dbo.LG_420_EMUHACC AS LG_420_EMUHACC WITH (NOLOCK) ON ORFLINE.ACCOUNTREF = LG_420_EMUHACC.LOGICALREF LEFT OUTER JOIN
dbo.L_TRADGRP WITH (NOLOCK) ON dbo.L_TRADGRP.GCODE = ORFICHE.TRADINGGRP LEFT OUTER JOIN
dbo.L_CAPIDIV AS ISYERI WITH (NOLOCK) ON ISYERI.NR = ORFICHE.BRANCH AND ISYERI.FIRMNR = '020' LEFT OUTER JOIN
dbo.L_CAPIDEPT AS BOLUM WITH (NOLOCK) ON BOLUM.NR = ORFICHE.DEPARTMENT AND BOLUM.FIRMNR = '020' LEFT OUTER JOIN
dbo.L_CAPIFACTORY AS FABRIKA WITH (NOLOCK) ON FABRIKA.NR = ORFICHE.FACTORYNR AND FABRIKA.FIRMNR = '020' LEFT OUTER JOIN
dbo.L_CAPIWHOUSE AS AMBARLAR WITH (NOLOCK) ON AMBARLAR.NR = ORFICHE.SOURCEINDEX AND AMBARLAR.FIRMNR = '020' LEFT OUTER JOIN
dbo.LG_SLSMAN WITH (NOLOCK) ON dbo.LG_SLSMAN.LOGICALREF = ORFICHE.SALESMANREF AND dbo.LG_SLSMAN.FIRMNR = '020' LEFT OUTER JOIN
dbo.LG_SLSMAN AS SLSSATIR ON SLSSATIR.LOGICALREF = ORFLINE.SALESMANREF AND SLSSATIR.FIRMNR = '020' LEFT OUTER JOIN
dbo.LG_420_SPECODES AS SPECODESLINE WITH (NOLOCK) ON SPECODESLINE.SPECODE = ORFLINE.SPECODE AND SPECODESLINE.CODETYPE = 1 AND
SPECODESLINE.SPECODETYPE = 16 LEFT OUTER JOIN
dbo.LG_420_SPECODES AS SPECODESFICHE WITH (NOLOCK) ON SPECODESFICHE.SPECODE = ORFLINE.SPECODE AND SPECODESFICHE.CODETYPE = 1 AND
SPECODESFICHE.SPECODETYPE = 14 LEFT OUTER JOIN
dbo.LG_420_SHIPINFO AS SHIPINFO WITH (NOLOCK) ON SHIPINFO.LOGICALREF = ORFICHE.SHIPINFOREF LEFT OUTER JOIN
dbo.LG_420_CLCARD AS CLCARD_2 WITH (NOLOCK) ON ORFICHE.RECVREF = CLCARD_2.LOGICALREF LEFT OUTER JOIN
dbo.L_CURRENCYLIST WITH (NOLOCK) ON dbo.L_CURRENCYLIST.CURTYPE = ORFICHE.TRCURR AND dbo.L_CURRENCYLIST.FIRMNR = '020'
WHERE (ORFICHE.TRCODE = 1) AND (ORFLINE.LINETYPE IN (0, 1))
