Notes

select * from (SELECT
MUH.YEVMIYE_NO AS EntryNumberCounter,
'Veriban' AS EnteredBy,
CONVERT(varchar(50),MUH.FIS_TARIHI,104) AS EnteredDate,
MUH.FIS_NO AS EntryNumber,
MUH.FIS_ACIKLAMA AS EntryComment,
MUH.ANA_HESAP_KOD AS AccountMainID,
MUH.ANA_HESAP_ACIKLAMA AS AccountMainDescription,
MUH.HESAP_KODU AS AccountSubID ,
REPLACE(REPLACE(REPLACE(MUH.HESAP_ADI,CHAR(9),''),CHAR(10),''),CHAR(13),'') AS AccountSubDescription,
MUH.Amount,
MUH.DebitCreditCode,
MUH.documenttype as DocumentType,
MUH.DocumentTypeDescription,
MUH.documentnumber as DocumentNumber,
CONVERT(varchar(50),MUH.documentdate,104) as DocumentDate,
MUH.paymentmethod as PaymentMethod,
MUH.FIS_ACIKLAMA AS DetailComment
from (
SELECT

FIS_TURU = CASE WHEN FIS.TRCODE= '1' THEN 'Açılış'
WHEN FIS.TRCODE= '2' THEN 'Tahsil'
WHEN FIS.TRCODE= '3' THEN 'Tediye'
WHEN FIS.TRCODE= '4' THEN 'Mahsup'
WHEN FIS.TRCODE= '7' THEN 'Kapanış'
ELSE 'Diğer' END,

YEVMIYE_NO = FIS.JOURNALNO,
FIS_TARIHI = FIS.DATE_,
FIS_NO = FIS.FICHENO,
ANA_HESAP_KOD = MAX(ACCCode.CODE),
ANA_HESAP_ACIKLAMA = MAX(ACCCode.DEFINITION_),
HESAP_KODU = KRT.CODE,
HESAP_ADI = KRT.DEFINITION_,
BORC = SUM(EMF.DEBIT),
ALACAK = SUM(EMF.CREDIT),
FIS_ACIKLAMA= ISNULL(EMF.LINEEXP, FIS.GENEXP1),

REPLACE(STR(EMF.DEBIT + EMF.CREDIT, 25, 2), ',', '.') AS Amount,
CASE WHEN EMF.SIGN = 0 THEN 'D' WHEN EMF.SIGN = 1 THEN 'C' END AS DebitCreditCode,
case when len(DOC.documenttype)>0 then DOC.documenttype else 'other' end as DocumentType,
CASE WHEN len(DOC.documenttype)>0 THEN DOC.documenttypedesc ELSE 'Muhasebe Fişi' end AS DocumentTypeDescription,
case when len(DOC.documenttype)>0 then DOC.documentnumber else FIS.FICHENO END as DocumentNumber,
CASE WHEN LEN(DOC.documenttype)>0 THEN DOC.documentdate ELSE FIS.DATE_ END as DocumentDate,
DOC.paymentmethod as Paymentmethod


From LG_120_01_EMFLINE EMF

INNER JOIN LG_120_01_EMFICHE FIS ON FIS.LOGICALREF = EMF.ACCFICHEREF


LEFT OUTER JOIN
(


SELECT INV.ACCFICHEREF entryref, 'invoice' documenttype, NULL documenttypedesc, INV.FICHENO documentnumber, SUBSTRING(CONVERT(NVARCHAR, INV.DATE_, 112), 0, 5) + '-' + SUBSTRING(CONVERT(NVARCHAR,
INV.DATE_, 112), 5, 2) + '-' + SUBSTRING(CONVERT(NVARCHAR, INV.DATE_, 112), 7, 2) documentdate, NULL paymentmethod, '$ERPNO' erpno, CONVERT(NVARCHAR, EMF.BRANCH) divisionno
FROM LG_120_01_INVOICE INV INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = INV.ACCFICHEREF AND EMF.MODULENR IN (2, 3) INNER JOIN
(SELECT MIN(INV.LOGICALREF) BNFICHEREF, INV.ACCFICHEREF
FROM LG_120_01_INVOICE INV INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = INV.ACCFICHEREF
WHERE INV.ACCOUNTED = 1
GROUP BY INV.ACCFICHEREF) DRV ON DRV.BNFICHEREF = INV.LOGICALREF AND DRV.ACCFICHEREF = EMF.LOGICALREF
WHERE INV.ACCOUNTED = 1 AND INV.CANCELLED = 0
UNION ALL
SELECT CSR.ACCFICHEREF entryref, 'check' documenttype, NULL documenttypedesc, CSR.ROLLNO documentnumber, SUBSTRING(CONVERT(NVARCHAR, CSR.DATE_, 112), 0, 5) + '-' + SUBSTRING(CONVERT(NVARCHAR,
CSR.DATE_, 112), 5, 2) + '-' + SUBSTRING(CONVERT(NVARCHAR, CSR.DATE_, 112), 7, 2) documentdate, 'Çek' paymentmethod, '$ERPNO' erpno, CONVERT(NVARCHAR, EMF.BRANCH) divisionno
FROM LG_120_01_CSROLL CSR INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = CSR.ACCFICHEREF INNER JOIN
(SELECT MIN(CSR.LOGICALREF) BNFICHEREF, CSR.ACCFICHEREF
FROM LG_120_01_CSROLL CSR INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = CSR.ACCFICHEREF
WHERE CSR.ACCOUNTED = 1 AND CSR.CARDREF<>0
GROUP BY CSR.ACCFICHEREF) DRV ON DRV.BNFICHEREF = CSR.LOGICALREF AND DRV.ACCFICHEREF = EMF.LOGICALREF
WHERE CSR.ACCOUNTED = 1 AND CSR.TRCODE IN (1, 3, 5, 7, 9, 11, 13) AND CSR.CANCELLED = 0

UNION ALL

SELECT CSR.ACCFICHEREF entryref, 'voucher' documenttype, NULL documenttypedesc, CSR.ROLLNO documentnumber, SUBSTRING(CONVERT(NVARCHAR, CSR.DATE_, 112), 0, 5)
+ '-' + SUBSTRING(CONVERT(NVARCHAR, CSR.DATE_, 112), 5, 2) + '-' + SUBSTRING(CONVERT(NVARCHAR, CSR.DATE_, 112), 7, 2) documentdate, 'Senet' paymentmethod, '$ERPNO' erpno, CONVERT(NVARCHAR,
EMF.BRANCH) divisionno
FROM LG_120_01_CSROLL CSR INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = CSR.ACCFICHEREF INNER JOIN
(SELECT MIN(CSR.LOGICALREF) BNFICHEREF, CSR.ACCFICHEREF
FROM LG_120_01_CSROLL CSR INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = CSR.ACCFICHEREF
WHERE CSR.ACCOUNTED = 1 AND CSR.CARDREF<>0
GROUP BY CSR.ACCFICHEREF) DRV ON DRV.BNFICHEREF = CSR.LOGICALREF AND DRV.ACCFICHEREF = EMF.LOGICALREF
WHERE CSR.ACCOUNTED = 1 AND CSR.TRCODE IN (2, 4, 6, 8, 10, 12, 14) AND CSR.CANCELLED = 0
UNION ALL
SELECT CLF.ACCFICHEREF entryref, 'other' documenttype, CASE WHEN CLF.TRCODE = 1 THEN 'Nakit Tahsilat' WHEN CLF.TRCODE = 2 THEN 'Nakit Ödeme' WHEN CLF.TRCODE = 3 THEN 'Borç Dekontu' WHEN CLF.TRCODE = 4 THEN 'Alacak Dekontu'
WHEN CLF.TRCODE = 5 THEN 'Virman Fişi' WHEN CLF.TRCODE = 6 THEN 'Kur Farkı Fişi' WHEN CLF.TRCODE = 14 THEN 'Açılış Fişi' WHEN CLF.TRCODE = 70 THEN 'Kredi Kartı Fişi' WHEN CLF.TRCODE = 71 THEN 'Kredi Kartı İade Fişi'
WHEN CLF.TRCODE = 72 THEN 'Firma Kredi Kartı Fişi' end documenttypedesc, CLF.FICHENO documentnumber, SUBSTRING(CONVERT(NVARCHAR, CLF.DATE_, 112), 0, 5)
+ '-' + SUBSTRING(CONVERT(NVARCHAR, CLF.DATE_, 112), 5, 2) + '-' + SUBSTRING(CONVERT(NVARCHAR, CLF.DATE_, 112), 7, 2) documentdate, null paymentmethod, '$ERPNO' erpno, CONVERT(NVARCHAR,
EMF.BRANCH) divisionno
FROM LG_120_01_CLFICHE CLF INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = CLF.ACCFICHEREF AND EMF.MODULENR = 4 INNER JOIN
(SELECT MIN(CLF.LOGICALREF) BNFICHEREF, CLF.ACCFICHEREF
FROM LG_120_01_CLFICHE CLF INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = CLF.ACCFICHEREF
WHERE CLF.ACCOUNTED = 1
GROUP BY CLF.ACCFICHEREF) DRV ON DRV.BNFICHEREF = CLF.LOGICALREF AND DRV.ACCFICHEREF = EMF.LOGICALREF
WHERE CLF.ACCOUNTED = 1 AND CLF.TRCODE IN (1,2,3,4,5,14,70,71,72) AND CLF.CANCELLED = 0
UNION ALL

SELECT KSL.ACCFICHEREF entryref, 'other' documenttype,
CASE WHEN KSL.TRCODE = 11 THEN 'CH Tahsilat' WHEN KSL.TRCODE = 12 THEN 'CH Ödeme' WHEN KSL.TRCODE = 21 THEN 'Bankaya Yatırılan' WHEN KSL.TRCODE = 22 THEN 'Bankadan Çekilen' WHEN KSL.TRCODE
= 31 THEN 'Mal Alım Faturası' WHEN KSL.TRCODE = 32 THEN 'Perakende Satış İade Faturası' WHEN KSL.TRCODE = 33 THEN 'Toptan Satış İade Faturası' WHEN KSL.TRCODE = 34 THEN 'Alınan Hizmet Faturası'
WHEN KSL.TRCODE = 35 THEN 'Alım İade Faturası' WHEN KSL.TRCODE = 36 THEN 'Perakende Satış Faturası' WHEN KSL.TRCODE = 37 THEN 'Toptan Satış Faturası' WHEN KSL.TRCODE = 38 THEN 'Verilen Hizmet Faturası'
WHEN KSL.TRCODE = 39 THEN 'Müstahsil Makbuzu' WHEN KSL.TRCODE = 61 THEN 'Çek Tahsil' WHEN KSL.TRCODE = 62 THEN 'Senet Tahsil' WHEN KSL.TRCODE = 63 THEN 'Çek Ödemesi' WHEN KSL.TRCODE
= 64 THEN 'Senet Ödemesi' WHEN KSL.TRCODE = 75 THEN 'Gider Pusulası' WHEN KSL.TRCODE = 76 THEN 'Verilen Serbest Meslek Makbuzu' WHEN KSL.TRCODE = 77 THEN 'Alınan Serbest Meslek Makbuzu' ELSE
CAST(KSL.TRCODE AS NVARCHAR) END documenttypedesc, KSL.FICHENO documentnumber, SUBSTRING(CONVERT(NVARCHAR, KSL.DATE_, 112), 0, 5) + '-' + SUBSTRING(CONVERT(NVARCHAR, KSL.DATE_,
112), 5, 2) + '-' + SUBSTRING(CONVERT(NVARCHAR, KSL.DATE_, 112), 7, 2) documentdate, 'Nakit' paymentmethod, '$ERPNO' erpno, CONVERT(NVARCHAR, EMF.BRANCH) divisionno
FROM LG_120_01_KSLINES KSL INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = KSL.ACCFICHEREF AND EMF.MODULENR = 7 INNER JOIN
(SELECT MIN(KSL.LOGICALREF) BNFICHEREF, KSL.ACCFICHEREF
FROM LG_120_01_KSLINES KSL INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = KSL.ACCFICHEREF AND EMF.MODULENR = 7
WHERE KSL.ACCOUNTED = 1
GROUP BY KSL.ACCFICHEREF) DRV ON DRV.BNFICHEREF = KSL.LOGICALREF AND DRV.ACCFICHEREF = EMF.LOGICALREF
WHERE KSL.ACCOUNTED = 1 AND KSL.CANCELLED = 0 AND KSL.TRCODE IN (11, 12, 21, 22, 31, 32, 33, 34, 35, 36, 37, 38, 39, 61, 62, 63, 64, 75, 76, 77)
UNION ALL
SELECT BNF.ACCFICHEREF entryref, 'other' documenttype,
CASE BNF.TRCODE WHEN 1 THEN 'Banka İşlem Fişi' WHEN 2 THEN 'Banka Virman Fişi' WHEN 3 THEN 'Gelen Havaleler' WHEN 4 THEN 'Gönderilen Havaleler' WHEN 6 THEN 'Banka Kur Farkı Fişi' WHEN 16 THEN
'Alınan Hizmet Faturası' WHEN 17 THEN 'Verilen Hizmet Faturası' WHEN 18 THEN 'Bankadan Çek Ödemesi' WHEN 19 THEN 'Bankadan Senet Ödemesi' END documenttypedesc, BNF.FICHENO documentnumber,
SUBSTRING(CONVERT(NVARCHAR, BNF.DATE_, 112), 0, 5) + '-' + SUBSTRING(CONVERT(NVARCHAR, BNF.DATE_, 112), 5, 2) + '-' + SUBSTRING(CONVERT(NVARCHAR, BNF.DATE_, 112), 7, 2) documentdate,
'Banka' paymentmethod, '$ERPNO' erpno, CONVERT(NVARCHAR, EMF.BRANCH) divisionno
FROM LG_120_01_BNFICHE BNF INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = BNF.ACCFICHEREF AND EMF.MODULENR = 6 INNER JOIN
(SELECT MIN(BNF.LOGICALREF) BNFICHEREF, BNF.ACCFICHEREF
FROM LG_120_01_BNFICHE BNF INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = BNF.ACCFICHEREF AND EMF.MODULENR = 6
WHERE BNF.ACCOUNTED = 1 AND BNF.TRCODE IN (1, 2, 3, 4, 6, 16, 17, 18, 19)
GROUP BY BNF.ACCFICHEREF) DRV ON DRV.BNFICHEREF = BNF.LOGICALREF AND DRV.ACCFICHEREF = EMF.LOGICALREF

WHERE BNF.ACCOUNTED = 1 AND BNF.TRCODE IN (1, 2, 3, 4, 6, 16, 17, 18, 19)
UNION ALL
SELECT COL.ACCFICHEREF entryref, 'other' documenttype,
CASE COL.TRCODE WHEN 1 THEN '(01) Teminat Girişi' WHEN 2 THEN '(02) Teminat Çıkışı' WHEN 3 THEN '(03) İşlem Bordrosu Müşteri Teminatı' WHEN 4 THEN '(04) İşlem Bordrosu Kendi Teminatımız' END documenttypedesc,
COL.ROLLNO documentnumber, SUBSTRING(CONVERT(NVARCHAR, COL.DATE_, 112), 0, 5) + '-' + SUBSTRING(CONVERT(NVARCHAR, COL.DATE_, 112), 5, 2) + '-' + SUBSTRING(CONVERT(NVARCHAR, COL.DATE_,
112), 7, 2) documentdate, 'Teminat Mektubu' paymentmethod, '$ERPNO' erpno, CONVERT(NVARCHAR, EMF.BRANCH) divisionno
FROM LG_120_01_COLLATRLROLL COL INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = COL.ACCFICHEREF AND EMF.MODULENR = 160 INNER JOIN
(SELECT MIN(COL.LOGICALREF) BNFICHEREF, COL.ACCFICHEREF
FROM LG_120_01_COLLATRLROLL COL INNER JOIN
LG_120_01_EMFICHE EMF ON EMF.LOGICALREF = COL.ACCFICHEREF AND EMF.MODULENR = 160
WHERE COL.ACCOUNTED = 1
GROUP BY COL.ACCFICHEREF) DRV ON DRV.BNFICHEREF = COL.LOGICALREF AND DRV.ACCFICHEREF = EMF.LOGICALREF
WHERE COL.CANCELLED = 0
)
AS DOC

ON DOC.entryref = FIS.LOGICALREF AND EMF.BRANCH = DOC.divisionno
INNER JOIN LG_120_EMUHACC KRT ON KRT.LOGICALREF = EMF.ACCOUNTREF
INNER JOIN LG_120_EMUHACC ACCCode ON ACCCode.CODE = SUBSTRING(KRT.CODE,1,3)
WHERE
FIS.CANCELLED=0
AND EMF.DATE_ >= {0} AND EMF.DATE_ <= {1}

GROUP BY KRT.CODE,FIS.GENEXP1, FIS.JOURNALNO, FIS.FICHENO,FIS.DATE_,FIS.TRCODE,EMF.KEBIRCODE,EMF.DEBIT,EMF.CREDIT,KRT.DEFINITION_,EMF.LINEEXP
,EMF.SIGN,DOC.documenttype,DOC.documenttypedesc,EMF.LOGICALREF,
DOC.documentnumber,
DOC.documentdate,
DOC.paymentmethod,
EMF.CENTERREF
) MUH
) TBL
ORDER BY TBL.EntryNumberCounter, TBL.EnteredDate