Selamlar,
Aşağıdaki kodu deneyin.
SELECT
CARIKODU=CL.CODE,
CARIADI=CL.DEFINITION_,
TELEFON1=CL.TELNRS1,
TELEFON2=CL.TELNRS2,
İLGİLİ1=CL.INCHARGE,
İLGİLİ2=CL.INCHARGE2,
İLGİLİ3=CL.INCHARGE3,
BORÇ=ROUND(SUM(CF.DEBIT),2),
ALACAK=ROUND(SUM(CF.CREDIT),2),
BORÇBAKIYE=CASE WHEN ROUND(SUM(CF.DEBIT-CF.CREDIT),2) >0 THEN abs(ROUND(SUM(CF.DEBIT-CF.CREDIT),2)) ELSE '' END,
ALACAKBAKIYE=CASE WHEN ROUND(SUM(CF.DEBIT-CF.CREDIT),2)<0 THEN abs(ROUND(SUM(CF.DEBIT-CF.CREDIT),2)) ELSE '' END,
FATURA_TARİHİ=ISNULL(CONVERT(datetime,(SELECT TOP 1 FAT.DATE_ FROM LG_006_01_CLFLINE AS FAT WHERE CL.LOGICALREF=FAT.CLIENTREF AND FAT.TRCODE IN(37,38) AND FAT.CANCELLED=0 ORDER BY FAT.DATE_ DESC),104),0),
FATURA_TUTARI=ISNULL((SELECT TOP 1 (FAT.TRNET) FROM LG_006_01_CLFLINE AS FAT WHERE CL.LOGICALREF=FAT.CLIENTREF AND FAT.TRCODE IN(37,38) AND FAT.CANCELLED=0 ORDER BY FAT.DATE_ DESC ),0),
TAHSİLAT_TARİHİ=ISNULL((SELECT TOP 1 convert(datetime,FAT.DATE_,104) FROM LG_006_01_CLFLINE AS FAT WHERE CL.LOGICALREF=FAT.CLIENTREF AND FAT.MODULENR IN(5,6,7,10) AND FAT.CANCELLED=0 and FAT.SIGN=1 ORDER BY FAT.DATE_ DESC ),0),
TAHSİLAT_TUTARI=ISNULL((SELECT TOP 1 FAT.TRNET FROM LG_006_01_CLFLINE AS FAT WHERE CL.LOGICALREF=FAT.CLIENTREF AND FAT.MODULENR IN(5,6,7,10) AND FAT.CANCELLED=0 AND FAT.SIGN=1 ORDER BY FAT.DATE_ DESC ),0),
TAHSİLAT_TÜRÜ=ISNULL((SELECT TOP 1 case WHEN FAT.TRCODE=1 THEN 'Nakit Tahsilat'
WHEN FAT.TRCODE=2 THEN 'Nakit Ödeme'
WHEN FAT.TRCODE=3 THEN 'Borç Dekontu'
WHEN FAT.TRCODE=4 THEN 'Alacak Dekontu'
WHEN FAT.TRCODE=5 THEN 'Virman İşlemi'
WHEN FAT.TRCODE=6 THEN 'Kur Farkı İşlemi'
WHEN FAT.TRCODE=12 THEN 'Özel İşlem'
WHEN FAT.TRCODE=14 THEN 'Açılış Fişi'
WHEN FAT.TRCODE=20 THEN 'Gelen Havaleler'
WHEN FAT.TRCODE=21 THEN 'Gönderilen Havaleler'
WHEN FAT.TRCODE=31 THEN 'Mal Alım Faturası'
WHEN FAT.TRCODE=32 THEN 'Perakende Satış İade Faturası'
WHEN FAT.TRCODE=33 THEN 'Toptan Satış İade Faturası'
WHEN FAT.TRCODE=34 THEN 'Alınan Hizmet Faturası'
WHEN FAT.TRCODE=35 THEN 'Alınan Proforma Faturası'
WHEN FAT.TRCODE=36 THEN 'Alım İade Faturası'
WHEN FAT.TRCODE=37 THEN 'Perakende Satış Faturası'
WHEN FAT.TRCODE=38 THEN 'Toptan Satış Faturası'
WHEN FAT.TRCODE=39 THEN 'Verilen Hizmet Faturası'
WHEN FAT.TRCODE=40 THEN 'Verilen Proforma Faturası'
WHEN FAT.TRCODE=41 THEN 'Verilen Vade Farkı Faturası'
WHEN FAT.TRCODE=42 THEN 'Alınan Vade Farkı Faturası'
WHEN FAT.TRCODE=43 THEN 'Alınan Fiyat Farkı Faturası'
WHEN FAT.TRCODE=44 THEN 'Verilen Fiyat Farkı Faturası'
WHEN FAT.TRCODE=45 THEN 'Verilen Serbest Meslek Makbuzu'
WHEN FAT.TRCODE=46 THEN 'Alınan Serbest Meslek Makbuzu'
WHEN FAT.TRCODE=56 THEN 'Müstahsil Makbuzu'
WHEN FAT.TRCODE=70 THEN 'Kredi Kartı Fişi'
WHEN FAT.TRCODE=71 THEN 'Kredi Kartı Fişi İade'
WHEN FAT.TRCODE=72 THEN 'Firma Kredi Kartı Fişi'
WHEN FAT.TRCODE=73 THEN 'Firma Kredi Kartı Fişi İade'
WHEN FAT.TRCODE=61 THEN 'Çek Girişi'
WHEN FAT.TRCODE=62 THEN 'Senet Girişi'
WHEN FAT.TRCODE=63 THEN 'Çek Çıkış Cari Hesaba'
WHEN FAT.TRCODE=64 THEN 'Senet Çıkış Cari Hesaba'
WHEN FAT.TRCODE=75 THEN 'Özel Dekont'
end FROM LG_006_01_CLFLINE AS FAT WHERE CL.LOGICALREF=FAT.CLIENTREF AND FAT.CANCELLED=0 AND FAT.SIGN=1 ORDER BY FAT.DATE_ DESC ),0)
FROM LG_006_CLCARD AS CL LEFT OUTER JOIN LV_006_01_CLTOTFIL AS CF ON CL.LOGICALREF=CF.CARDREF
WHERE CF.TOTTYP=1 AND CL.ACTIVE IN (0,1)
GROUP BY CL.CODE,CL.DEFINITION_,CL.LOGICALREF,CL.TELNRS1,TELNRS2,INCHARGE,INCHARGE2,INCHARGE3
ORDER BY CL.CODE,CL.DEFINITION_
