SELECT 
CSROLL.BRANCH ISYERIKOD,
(SELECT D.NAME FROM L_CAPIDIV D WHERE D.FIRMNR={0} AND D.NR=CSROLL.BRANCH) ISYERI,
CSROLL.DEPARTMENT BOLUMKOD,
(SELECT D.NAME FROM L_CAPIDEPT D WHERE D.FIRMNR={0} AND D.NR=CSROLL.DEPARTMENT) BOLUM,
CSROLL.CYPHCODE YETKIKODU,
CSROLL.ROLLNO BORDRONO,
CSCARD.PORTFOYNO,
CSCARD.NEWSERINO CEKNO,
CSCARD.DUEDATE VADE, 
(SELECT B.CODE  FROM LG_{0}_BANKACC B WHERE B.LOGICALREF=CSROLL.CARDREF) BANKAKOD,
(SELECT B.DEFINITION_ FROM LG_{0}_BANKACC B WHERE B.LOGICALREF=CSROLL.CARDREF) BANKA,
(SELECT C.CODE  FROM LG_{0}_CLCARD C WHERE C.LOGICALREF= (SELECT CSROLL2.CARDREF FROM LG_{0}_{1}_CSROLL CSROLL2 WHERE  CSROLL2.TRCODE=1 AND
   CSROLL2.LOGICALREF IN (SELECT CSTRANS2.ROLLREF FROM  LG_{0}_{1}_CSTRANS CSTRANS2 WHERE CSTRANS2.CSREF =CSCARD.LOGICALREF)) ) CARIKOD,
(SELECT  C.DEFINITION_ FROM LG_{0}_CLCARD C WHERE C.LOGICALREF= (SELECT CSROLL2.CARDREF FROM LG_{0}_{1}_CSROLL CSROLL2 WHERE  CSROLL2.TRCODE=1 AND
   CSROLL2.LOGICALREF IN (SELECT CSTRANS2.ROLLREF FROM  LG_{0}_{1}_CSTRANS CSTRANS2 WHERE CSTRANS2.CSREF =CSCARD.LOGICALREF)) ) CARI,
CASE WHEN CSTRANS.TRCODE=5 THEN CSROLL.TOTAL ELSE 0 END TUTAR  
FROM LG_{0}_{1}_CSCARD CSCARD LEFT JOIN LG_{0}_{1}_CSTRANS CSTRANS ON CSTRANS.CSREF=CSCARD.LOGICALREF
LEFT JOIN LG_{0}_{1}_CSROLL CSROLL ON CSROLL.LOGICALREF = CSTRANS.ROLLREF
WHERE CSCARD.PORTFOYNO='PORTFOYD' AND CSTRANS.TRCODE IN (5)