SELECT 
C.BRANCH ISYERI_KODU,
CONVERT(VARCHAR,CAPIDIV.NR)+', '+CAPIDIV.NAME  ISYERI,
LG_211_CLCARD_CEKGIRISCARI.LOGICALREF CEK_GIRIS_CARIREF,
LG_211_CLCARD_CEKGIRISCARI.CODE CEK_GIRIS_CARI_KOD,
LG_211_CLCARD_CEKGIRISCARI.DEFINITION_ CEK_GIRIS_CARI,
ISNULL(PAYPLANS.CODE+', '+PAYPLANS.DEFINITION_,'') CEK_GIRIS_CARI_VADE,
LG_211_CLCARD_CEKGIRISCARI.SPECODE CEK_GIRIS_CARI_OZELKOD,
LG_211_CLCARD_CEKGIRISCARI.SPECODE2 CEK_GIRIS_CARI_OZELKOD2,
LG_211_CLCARD_CEKGIRISCARI.SPECODE3 CEK_GIRIS_CARI_OZELKOD3,
LG_211_CLCARD_CEKGIRISCARI.SPECODE4 CEK_GIRIS_CARI_OZELKOD4,
LG_211_CLCARD_CEKGIRISCARI.SPECODE5 CEK_GIRIS_CARI_OZELKOD5,
 ISNULL((
 SELECT   SUM(P.TOTAL) TOPLAM  FROM BM_211_01_PAYTRANS P WITH(NOLOCK) WHERE P.SIGN=0  AND P.PAID=0  AND P.CANCELLED=0 AND P.CARDREF = LG_211_CLCARD_CEKGIRISCARI.LOGICALREF AND P.BRANCH=C.BRANCH AND P.TRCURR=LG_211_01_CSROLL.TRCURR  
 ),0) CEK_GIRIS_CARI_BAKIYE,

CASE LG_211_01_CSROLL.TRCURR 
WHEN 0  THEN 'TL'
WHEN 160 THEN 'TL'
WHEN 1 THEN 'USD'
WHEN 17 THEN 'GBP'
WHEN 20 THEN 'EUR'
ELSE CONVERT(VARCHAR,LG_211_01_CSROLL.TRCURR) END CEK_DOVIZ,
LG_211_CLCARD_2.CODE AS KİMDEN, 
/*C.DOC AS [çek 1 senet 2], */
C.BANKNAME AS BANKA, 
Substring(C.BNBRANCHNO, 6, 12) AS ŞUBE, 
C.NEWSERINO AS [Seri No], 
/*Substring(CONVERT(varchar(10), C.DUEDATE, 103), 7, 4) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 4, 2) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 1, 2) AS vadesi, */
C.AMOUNT AS Tutar, 
CASE CURRSTAT 
	WHEN 1 THEN 'Portföyde' 
	WHEN 2 THEN 'Ciro Edildi' 
	WHEN 3 THEN 'Teminata Verildi' 
	WHEN 4 THEN 'Tahsile Verildi' 
	WHEN 5 THEN 'Protestolu Tahsile Verildi' 
	WHEN 6 THEN 'İade Edildi' 
	WHEN 7 THEN 'Protesto Edildi' 
	WHEN 8 THEN 'Tahsil Edildi' 
	WHEN 9 THEN 'Kendi Çekimiz' 
	WHEN 10 THEN 'Borç Senedimiz' 
	WHEN 11 THEN 'Karşılığı Yok' 
	WHEN 12 THEN 'Tahsil Edilemiyor' 
	ELSE 'Ne Oldugu Belirsiz' 
END 
AS DURUMU, 
C.OWING AS [CİRO EDEN], 
(SELECT TOP 1 ASD.DATE_ FROM LG_211_01_CSTRANS ASD  WITH(NOLOCK) WHERE ASD.CSREF=LG_211_01_CSTRANS_2.CSREF AND ASD.STATUS=1 ORDER BY ASD.LOGICALREF ASC) CEK_GIRIS_TARIHI,
C.SETDATE AS TARIH,
C.DUEDATE AS VADE,  
LG_211_BANKACC.CODE 
BANKA 
FROM   LG_211_01_CSCARD AS C  WITH(NOLOCK) 
INNER JOIN dbo.LG_211_CLCARD AS LG_211_CLCARD_2  WITH(NOLOCK) 
INNER JOIN dbo.LG_211_01_CSROLL  WITH(NOLOCK)  ON LG_211_CLCARD_2.LOGICALREF = dbo.LG_211_01_CSROLL.CARDREF 
INNER JOIN dbo.LG_211_01_CSTRANS AS LG_211_01_CSTRANS_2  WITH(NOLOCK)  ON dbo.LG_211_01_CSROLL.LOGICALREF = LG_211_01_CSTRANS_2.ROLLREF ON C.LOGICALREF = LG_211_01_CSTRANS_2.CSREF 
INNER JOIN LG_211_BANKACC  WITH(NOLOCK)  ON LG_211_BANKACC.LOGICALREF = LG_211_01_CSTRANS_2.CARDREF 
INNER JOIN dbo.LG_211_CLCARD AS LG_211_CLCARD_CEKGIRISCARI  WITH(NOLOCK)  ON LG_211_CLCARD_CEKGIRISCARI.LOGICALREF=(SELECT TOP 1 ASD.CARDREF FROM LG_211_01_CSTRANS ASD  WITH(NOLOCK)  WHERE ASD.CSREF=LG_211_01_CSTRANS_2.CSREF AND ASD.STATUS=1 ORDER BY ASD.LOGICALREF ASC)
INNER JOIN LG_211_PAYPLANS PAYPLANS  WITH(NOLOCK)  ON PAYPLANS.LOGICALREF= LG_211_CLCARD_CEKGIRISCARI.PAYMENTREF
INNER JOIN L_CAPIDIV CAPIDIV WITH(NOLOCK)  ON CAPIDIV.NR=C.BRANCH AND CAPIDIV.FIRMNR=211