SELECT TOP (100) PERCENT LG_300_01_PAYTRANS_1.PROCDATE AS [MAK.T.],
dbo.LG_300_CLCARD.CODE AS [CH KOD],
LEFT(dbo.LG_300_CLCARD.DEFINITION_, 40) AS [CH ACIKLAMA],
LEFT(dbo.LG_300_01_INVOICE.FICHENO, 11) AS [FAT.NO.],
dbo.LG_300_01_PAYTRANS.PROCDATE AS [FAT.T.],
LG_300_01_PAYTRANS_1.DATE_ AS [MAK.V.],
LEFT(dbo.LG_300_01_INVOICE.NETTOTAL, 10) AS [FAT.(TL)],
DATEDIFF(DAY, dbo.LG_300_01_PAYTRANS.PROCDATE, LG_300_01_PAYTRANS_1.DATE_) AS GUN,
MONTH(dbo.LG_300_01_INVOICE.DATE_) AS AY,
CASE LG_300_01_PAYTRANS.TRCURR
WHEN 0
THEN (
CASE LG_300_01_PAYTRANS_1.CROSSTOTAL
WHEN 0
THEN (
CASE LG_300_01_PAYTRANS.TRCURR
WHEN 0
THEN (dbo.LG_300_01_PAYTRANS.TOTAL)
ELSE (dbo.LG_300_01_PAYTRANS.TOTAL * dbo.LG_300_01_PAYTRANS.TRRATE)
END
)
ELSE (LG_300_01_PAYTRANS_1.CROSSTOTAL)
END
)
ELSE (LG_300_01_PAYTRANS_1.CROSSTOTAL * dbo.LG_300_01_PAYTRANS.TRRATE)
END AS [MAK. (TL)],
LEFT(dbo.LG_SLSMAN.DEFINITION_, 15) AS [SATIS ELEMANI],
CASE LG_300_01_PAYTRANS_1.MODULENR
WHEN 5
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 4
THEN 'İNDİRİM'
WHEN 70
THEN 'KREDİ KARTI'
WHEN 5
THEN 'VİRMAN'
WHEN 14
THEN 'AÇILIŞ FİŞİ'
END
)
WHEN 10
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 1
THEN 'NAKİT'
END
)
WHEN 6
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 1
THEN 'ÇEK'
END
)
WHEN 7
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 3
THEN 'HAVALE'
END
)
WHEN 4
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 3
THEN 'İADE FATURASI'
WHEN 4
THEN 'HİZMET FATURASI'
WHEN 1
THEN 'MAL FATURASI'
END
)
END AS [MAK.TİPİ],
dbo.LG_300_01_INVOICE.TRADINGGRP
FROM dbo.LG_SLSMAN
RIGHT OUTER JOIN dbo.LG_300_01_INVOICE
ON dbo.LG_SLSMAN.LOGICALREF = dbo.LG_300_01_INVOICE.SALESMANREF
LEFT OUTER JOIN dbo.LG_300_CLCARD
ON dbo.LG_300_01_INVOICE.CLIENTREF = dbo.LG_300_CLCARD.LOGICALREF
RIGHT OUTER JOIN dbo.LG_300_01_PAYTRANS AS LG_300_01_PAYTRANS_1
LEFT OUTER JOIN dbo.LG_300_BANKACC
ON LG_300_01_PAYTRANS_1.BANKACCREF = dbo.LG_300_BANKACC.LOGICALREF
RIGHT OUTER JOIN dbo.LG_300_01_PAYTRANS
ON LG_300_01_PAYTRANS_1.LOGICALREF = dbo.LG_300_01_PAYTRANS.CROSSREF
ON dbo.LG_300_01_INVOICE.TRCODE = dbo.LG_300_01_PAYTRANS.TRCODE
AND dbo.LG_300_01_INVOICE.LOGICALREF = dbo.LG_300_01_PAYTRANS.FICHEREF WHERE (
dbo.LG_300_01_PAYTRANS.TRCODE = 8
OR dbo.LG_300_01_PAYTRANS.TRCODE = 14
)
AND (
CASE LG_300_01_PAYTRANS_1.MODULENR
WHEN 5
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 4
THEN 'İNDİRİM'
WHEN 70
THEN 'KREDİ KARTI'
WHEN 5
THEN 'VİRMAN'
WHEN 14
THEN 'AÇILIŞ FİŞİ'
END
)
WHEN 10
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 1
THEN 'NAKİT'
END
)
WHEN 6
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 1
THEN 'ÇEK'
END
)
WHEN 7
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 3
THEN 'HAVALE'
END
)
WHEN 4
THEN (
CASE LG_300_01_PAYTRANS_1.TRCODE
WHEN 3
THEN 'İADE FATURASI'
WHEN 4
THEN 'HİZMET FATURASI'
WHEN 1
THEN 'MAL FATURASI'
END
)
END = 'ÇEK'
)
AND (GETDATE() - LG_300_01_PAYTRANS_1.PROCDATE <= 90)
AND (
LG_300_01_PAYTRANS_1.FICHEREF NOT IN (
SELECT CST.ROLLREF
FROM LG_300_01_CSTRANS CST
WHERE CST.CSREF IN (
SELECT CSC.LOGICALREF
FROM LG_300_01_CSCARD CSC
WHERE CSC.CURRSTAT = 8
)
)
)
ORDER BY [CH ACIKLAMA]
GO
Tag: CSTRANS
LOGO PAYTRANS ÇEK SENET DETAY RAPORU
SELECT
T2.TUR,T2.BRANCH ISYERI_NR
,
(select CONVERT(VARCHAR,NR)+', '+NAME from L_CAPIDIV WITH(NOLOCK) WHERE FIRMNR=210 and NR=T2.BRANCH) ISYERI
,
(SELECT C.CODE FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_KOD
,
(SELECT C.DEFINITION_ FROM LG_210_CLCARD C WITH(NOLOCK) WHERE C.LOGICALREF=CARDREF) CARI_AD
,
CEK_NO,ISLEM_TARIHI,VADE_TARIHI,CEK_DOVIZ,TUTAR,KAPAMA_TUTAR,KARSI_ISLEM_BILGILERI
FROM (
SELECT DISTINCT
*,
CASE T.TRCURR WHEN 0 THEN 'TL' WHEN 160 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE CONVERT(VARCHAR,T.TRCURR) END CEK_DOVIZ
,
(SELECT SUM(P.TOTAL) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ) TUTAR
,
ISNULL((SELECT SUM(P.PAID) FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF AND PAID<>0),0) KAPAMA_TUTAR
,
ISNULL((
STUFF(
(SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+'['+ CAST(DAY(PP.PROCDATE) AS varchar)+'.'+CAST(MONTH(PP.PROCDATE) AS varchar) +'.'+CAST(YEAR(PP.PROCDATE) AS varchar)+'] /'+format((ROUND(PP.PAID,2)),N'','tr-TR') FROM LG_210_01_PAYTRANS PP WITH(NOLOCK) WHERE PP.LOGICALREF IN ((SELECT P.CROSSREF FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.BRANCH=T.BRANCH AND P.TRCURR=T.TRCURR AND
P.CARDREF=T.CARDREF AND P.FICHEREF=T.PAYTRANS_FICHEREF AND P.FICHELINEREF=T.PAYTRANS_FICHELINEREF ))
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'),
1,
1,
''
)
),'') KARSI_ISLEM_BILGILERI
FROM (
SELECT
dbo.BM_PAYTRANS2ISLEMTURU(P.TRCODE,P.MODULENR) TUR,
P.BRANCH,
P.TRCURR,
P.CARDREF ,
P.FICHEREF PAYTRANS_FICHEREF, /*CSTRANS.ROLLREF*/
P.FICHELINEREF PAYTRANS_FICHELINEREF , /*CSTRANS.LOGICALREF*/
(SELECT CSCARD.NEWSERINO FROM LG_210_01_CSCARD CSCARD WITH(NOLOCK) WHERE CSCARD.LOGICALREF=
(SELECT CST.CSREF FROM LG_210_01_CSTRANS CST WITH(NOLOCK) WHERE CST.ROLLREF=P.FICHEREF AND CST.LOGICALREF=P.FICHELINEREF)) CEK_NO,
P.PROCDATE ISLEM_TARIHI,
P.DATE_ VADE_TARIHI
FROM BM_210_01_PAYTRANS P WITH(NOLOCK) WHERE P.MODULENR=6/*CEKLER*/ /* AND P.CARDREF=5571 */
/*AND YEAR(DATE_)=2021 AND MONTH(DATE_)=4 AND DAY(DATE_)=8*/
) AS T
) AS T2
Logo Çek Sorgusu
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
Not: https://github.com/ugurozpinar/Logo/blob/master/cekler.sql Revizesidir… Emeğe saygılar…
LOGO BANKA TAHSİLE VERİLEN ÇEKLER SQL
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)
Blog of Sem 