SELECT
CL.CODE AS 'CARİ KODU',
CL.DEFINITION_ AS 'CARİ ÜNVANI',
IT.CODE AS 'MALZEME KODU',
IT.NAME AS 'MALZEME AÇIKLAMASI',
ORF.DATE_ AS 'SİPARİŞ TARİHİ',
MONTH(ORL.DATE_) AS 'SİPARİŞ AYI',
ORF.FICHENO AS 'SİPARİŞ NUMARASI',
ORL.DUEDATE AS 'TESLİM TARİHİ',
MONTH(ORL.DUEDATE) AS 'TESLİMAT AYI',
DATEPART(WK, ORL.DUEDATE) AS 'TESLİMAT HAFTA',
--DATEPART(WK,(CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 101)))) AS 'GÜNÜN HAFTASI',
ORL.AMOUNT AS 'SİPARİŞ MİKTARI',
ORL.SHIPPEDAMOUNT AS 'SEVK EDİLEN MİKTAR',
(ORL.AMOUNT-ORL.SHIPPEDAMOUNT) AS 'BEKLEYEN MİKTAR',
--(SELECT SUM(AMOUNT-SHIPPEDAMOUNT) FROM LG_607_01_ORFLINE WHERE (STOCKREF=IT.LOGICALREF) AND DUEDATE<CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 101))) AS 'AAA',
CASE WHEN DATEPART(WK, ORL.DUEDATE)<DATEPART(WK,(CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 101)))) THEN (ORL.AMOUNT-ORL.SHIPPEDAMOUNT) END AS 'GECİKMİŞ BEKLEYEN MİKTAR',
SLS.DEFINITION_ AS 'SATIŞ ELEMANI',
(select SUM(ONHAND) FROM LV_607_01_STINVTOT WHERE (INVENNO = - 1) AND (STOCKREF = IT.LOGICALREF)) AS 'FİİLİ STOK',
ORL.DELVRYCODE AS 'SİPARİŞ TÜRÜ',
ORL.SPECODE AS 'ÖLÇÜ',
ORL.SPECODE2 AS 'REV./TEDARİKÇİ',
ORL.LINEEXP AS 'SATIR AÇIKLAMASI',
ORF.CYPHCODE AS 'SİPARİŞ YERİ',
ORF.GENEXP1 AS 'SİPARİŞ AÇIKLAMA-1',
TN.TEXTFLDS5 AS 'SEVK YERİ',
SUBSTRING(SLS.CODE,1,2) AS 'BÖLGE KODU',
CASE SUBSTRING(SLS.CODE,1,2)
WHEN 74 THEN 'BARTIN'
WHEN 34 THEN 'ISTANBUL'
WHEN 99 THEN 'IHRACAT'
END AS 'BÖLGE'
FROM LG_607_01_ORFLINE ORL
LEFT JOIN LG_607_01_ORFICHE ORF ON ORL.ORDFICHEREF=ORF.LOGICALREF
LEFT JOIN LG_607_ITEMS IT ON ORL.STOCKREF=IT.LOGICALREF
LEFT JOIN LG_607_CLCARD CL ON ORL.CLIENTREF=CL.LOGICALREF
LEFT JOIN LG_SLSMAN SLS ON ORL.SALESMANREF=SLS.LOGICALREF
LEFT JOIN LG_607_01_DEFNFLDSTRANV TN ON ORL.ORDFICHEREF=TN.PARENTREF
WHERE ORL.TRCODE=1 AND ORL.STATUS<>2 AND
(ORL.AMOUNT-ORL.SHIPPEDAMOUNT) BETWEEN 1 AND 1000000000
