Logo Cari – Muhasebe Bakiye Kontrol Raporu

SELECT

CASE CL.CCURRENCY WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 20 THEN 'EURO' ELSE 'DİĞER' END 'Cari Döviz Türü'
,CL.CODE 'Cari Hesap Kodu'
,CL.DEFINITION_ 'Unvanı'
,ISNULL((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.AMOUNT ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.AMOUNT ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0   AND CLF.TRCODE IN(14)),0) 'Ticari Bakiye'
,ISNULL((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.AMOUNT ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.AMOUNT ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0  AND CLF.TRCODE IN(14)),0)  'Muhasebe Bakiye'
,ISNULL(((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.AMOUNT ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.AMOUNT ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0  AND CLF.TRCODE IN(14)) -
(SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.AMOUNT ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.AMOUNT ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0  AND CLF.TRCODE IN(14))),0)'TL Fark' 
,ISNULL((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.TRNET ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.TRNET ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0 AND  CLF.TRCURR =1 AND CLF.TRCODE=14  ),0) 'Cari USD Bakiye'
,ISNULL((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.TRNET ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.TRNET ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0 AND  CLF.TRCURR =20  AND CLF.TRCODE=14  ),0) 'Cari EURO Bakiye'
,ISNULL((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.TRNET ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.TRNET ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0 AND  CLF.TRCURR =59  AND CLF.TRCODE=14  ),0) 'Cari AED Bakiye'
,ISNULL((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.TRNET ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.TRNET ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0 AND  CLF.TRCURR =17  AND CLF.TRCODE=14  ),0) 'Cari GBP Bakiye'
,ISNULL((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.TRNET ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.TRNET ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0 AND  CLF.TRCURR =17  AND CLF.TRCODE=14  ),0) 'Cari JPY Bakiye'
,ISNULL((SELECT SUM(CASE EML.SIGN WHEN 0 THEN EML.TRNET ELSE 0 END)-SUM(CASE EML.SIGN WHEN 1 THEN EML.TRNET ELSE 0 END) FROM LG_001_01_EMFLINE EML  , LG_001_CRDACREF CR, LG_001_EMUHACC EMU WHERE EML.TRCURR=1 AND CR.ACCOUNTREF=EMU.LOGICALREF AND CR.CARDREF=CL.LOGICALREF AND EMU.LOGICALREF=EML.ACCOUNTREF AND CR.TRCODE=5 AND TYP=1 AND EML.TRCODE=1 ),0) 'Muhasebe USD Bakiye'
,ISNULL((SELECT SUM(CASE EML.SIGN WHEN 0 THEN EML.TRNET ELSE 0 END)-SUM(CASE EML.SIGN WHEN 1 THEN EML.TRNET ELSE 0 END) FROM LG_001_01_EMFLINE EML  , LG_001_CRDACREF CR, LG_001_EMUHACC EMU WHERE EML.TRCURR=20 AND CR.ACCOUNTREF=EMU.LOGICALREF AND CR.CARDREF=CL.LOGICALREF AND EMU.LOGICALREF=EML.ACCOUNTREF AND CR.TRCODE=5 AND TYP=1AND EML.TRCODE=1  ),0) 'Muhasebe EURO Bakiye'
,ISNULL((SELECT SUM(CASE EML.SIGN WHEN 0 THEN EML.TRNET ELSE 0 END)-SUM(CASE EML.SIGN WHEN 1 THEN EML.TRNET ELSE 0 END) FROM LG_001_01_EMFLINE EML  , LG_001_CRDACREF CR, LG_001_EMUHACC EMU WHERE EML.TRCURR=59 AND CR.ACCOUNTREF=EMU.LOGICALREF AND CR.CARDREF=CL.LOGICALREF AND EMU.LOGICALREF=EML.ACCOUNTREF AND CR.TRCODE=5 AND TYP=1 AND EML.TRCODE=1 ),0) 'Muhasebe AED Bakiye'
,ISNULL((SELECT SUM(CASE EML.SIGN WHEN 0 THEN EML.TRNET ELSE 0 END)-SUM(CASE EML.SIGN WHEN 1 THEN EML.TRNET ELSE 0 END) FROM LG_001_01_EMFLINE EML  , LG_001_CRDACREF CR, LG_001_EMUHACC EMU WHERE EML.TRCURR=17 AND CR.ACCOUNTREF=EMU.LOGICALREF AND CR.CARDREF=CL.LOGICALREF AND EMU.LOGICALREF=EML.ACCOUNTREF AND CR.TRCODE=5 AND TYP=1 AND EML.TRCODE=1 ),0) 'Muhasebe GBP Bakiye'
,ISNULL((SELECT SUM(CASE EML.SIGN WHEN 0 THEN EML.TRNET ELSE 0 END)-SUM(CASE EML.SIGN WHEN 1 THEN EML.TRNET ELSE 0 END) FROM LG_001_01_EMFLINE EML  , LG_001_CRDACREF CR, LG_001_EMUHACC EMU WHERE EML.TRCURR=59 AND CR.ACCOUNTREF=EMU.LOGICALREF AND CR.CARDREF=CL.LOGICALREF AND EMU.LOGICALREF=EML.ACCOUNTREF AND CR.TRCODE=5 AND TYP=1 AND EML.TRCODE=1 ),0) 'Muhasebe JPY Bakiye'
,ISNULL(((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.TRNET ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.TRNET ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0 AND  CLF.TRCURR =1 AND CLF.TRCODE=14   )-
ISNULL((SELECT SUM(CASE EML.SIGN WHEN 0 THEN EML.TRNET ELSE 0 END)-SUM(CASE EML.SIGN WHEN 1 THEN EML.TRNET ELSE 0 END) FROM LG_001_01_EMFLINE EML  , LG_001_CRDACREF CR, LG_001_EMUHACC EMU WHERE EML.TRCURR=1 AND CR.ACCOUNTREF=EMU.LOGICALREF AND CR.CARDREF=CL.LOGICALREF AND EMU.LOGICALREF=EML.ACCOUNTREF AND CR.TRCODE=5 AND TYP=1 AND EML.TRCODE=1  ),0)),0) 'USD Fark'
,ISNULL(((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.TRNET ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.TRNET ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0 AND  CLF.TRCURR =20 AND CLF.TRCODE=14 )-
ISNULL((SELECT SUM(CASE EML.SIGN WHEN 0 THEN EML.TRNET ELSE 0 END)-SUM(CASE EML.SIGN WHEN 1 THEN EML.TRNET ELSE 0 END) FROM LG_001_01_EMFLINE EML  , LG_001_CRDACREF CR, LG_001_EMUHACC EMU WHERE EML.TRCURR=20 AND CR.ACCOUNTREF=EMU.LOGICALREF AND CR.CARDREF=CL.LOGICALREF AND EMU.LOGICALREF=EML.ACCOUNTREF AND CR.TRCODE=5 AND TYP=1 AND EML.TRCODE=1  ),0)),0) 'EURO Fark'
,ISNULL(((SELECT SUM(CASE CLF.SIGN WHEN 0 THEN CLF.TRNET ELSE 0 END)-SUM(CASE CLF.SIGN WHEN 1 THEN CLF.TRNET ELSE 0 END)
FROM LG_001_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND  CLF.CANCELLED=0 AND  CLF.TRCURR =59 AND CLF.TRCODE=14  )-
ISNULL((SELECT SUM(CASE EML.SIGN WHEN 0 THEN EML.TRNET ELSE 0 END)-SUM(CASE EML.SIGN WHEN 1 THEN EML.TRNET ELSE 0 END) FROM LG_001_01_EMFLINE EML  , LG_001_CRDACREF CR, LG_001_EMUHACC EMU WHERE EML.TRCURR=59 AND CR.ACCOUNTREF=EMU.LOGICALREF AND CR.CARDREF=CL.LOGICALREF AND EMU.LOGICALREF=EML.ACCOUNTREF AND CR.TRCODE=5 AND TYP=1 AND EML.TRCODE=1  ),0)),0) 'AED Fark'
FROM LG_001_CLCARD CL
WHERE CL.ACTIVE=0 AND CL.CARDTYPE NOT IN(22)