banka_hareketleri.sql

--- Banka Hareketleri


SELECT
BNC.CODE + ' – ' + BNC.DEFINITION_ AS [Banka Kodu – Adı],
BNL.DATE_ AS Tarih, BNL.TRANNO AS [İşlem No.],
CASE bnl.TRCODE
WHEN 1 THEN 'Banka İşlem Fişi'                
WHEN 2 THEN 'Banka Virman Fişi'
WHEN 3 THEN 'Gelen Havale'                
WHEN 4 THEN 'Gönderilen Havale'
WHEN 5 THEN 'Banka Açılış Fişi'               
WHEN 6 THEN 'Banka Kur Farkı Fişi'
WHEN 16 THEN 'Banka Alınan Hizmet Faturası'   
WHEN 17 THEN 'Banka Verilen Hizmet Faturası'
WHEN 18 THEN 'Bankadan Çek Ödemesi'           
WHEN 19 THEN 'Bankadan Senet Ödemesi'
END AS [İşlem Tipi],
BACC.CODE AS [Hesap Kodu],
BNL.LINEEXP AS [İşlem Açıklaması],
ISNULL(CASE WHEN BNL.SIGN = 0 AND BNL.TRCURR = 0 THEN BNL.TRNET END, 0) AS [Borç TL],
ISNULL(CASE WHEN BNL.SIGN = 1 AND BNL.TRCURR = 0 THEN BNL.TRNET END, 0) AS [Alacak TL],
ISNULL(CASE WHEN BNL.SIGN = 0 AND BNL.TRCURR = 1 THEN BNL.TRNET END, 0) AS [Borç $],
ISNULL(CASE WHEN BNL.SIGN = 1 AND BNL.TRCURR = 1 THEN BNL.TRNET END, 0) AS [Alacak $],
ISNULL(CASE WHEN BNL.SIGN = 0 AND BNL.TRCURR = 20 THEN BNL.TRNET END, 0) AS [Borç €],
ISNULL(CASE WHEN BNL.SIGN = 1 AND BNL.TRCURR = 20 THEN BNL.TRNET END, 0) AS [Alacak €]
,CA.DEFINITION_ as Cari
FROM
LG_221_01_BNFLINE AS BNL
LEFT JOIN LG_221_01_BNFICHE BNF ON BNF.LOGICALREF = BNL.SOURCEFREF
LEFT JOIN LG_221_BNCARD BNC ON BNC.LOGICALREF=BNL.BANKREF
LEFT JOIN LG_221_CLCARD CA ON CA.LOGICALREF = BNL.CLIENTREF
LEFT JOIN LG_221_BANKACC BACC ON BACC.LOGICALREF = BNL.BNACCREF
WHERE     (BNL.TRCODE IN (1, 2, 3, 4, 5, 6, 16, 17, 18, 19)) AND (BNL.TRANSTYPE NOT IN (3,2)) 
ORDER BY BNF.DATE_