Tag: PAYTRANS
LOGO TAHSİL EDİLMEMİŞ ÇEK RAPORU
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
LOGO CARI RISK SIPARIS KAPAT SQL
SENARYO
- Bir Carinin borcu 50 gün ve daha fazlaysa job ile risk bilgilerinde siparişe kapatılsın güncellenmesi yapılacak (LOGO FIRMA ADI 210).
- Job çalışma süresi her gün sabah saat 7
İŞLEYİŞ
- Önce bu kritere uyan cariler view ile belirlenecek (VIEW ADI: CARIRISKSIPARISKAPAT_210)
- Job ile bu view çağrılıp gerekli güncelleme işlemi yapılacak
CREATE VIEW CARIRISKSIPARISKAPAT_210 AS
SELECT * FROM (
SELECT
CLLOGICALREF= C.LOGICALREF,
[Cari Kodu]= C.CODE,
[Cari Ünvanı]= C.DEFINITION_,
[İşlem]= ISNULL((
SELECT TOP 1
CASE WHEN DATEDIFF(DAY,P.DATE_,GETDATE())>=50 THEN 'Siparişe Kapat' END ISLEM
FROM LG_210_01_PAYTRANS P WITH(NOLOCK)
WHERE
P.MODULENR=4 AND P.TRCODE IN (7,8) AND P.PAID=0 AND P.CARDREF=C.LOGICALREF AND P.CANCELLED=0 ORDER BY P.DATE_ ASC
),''),
[Siparişte]= ISNULL((SELECT
CASE CLR.ORDRISKOVER
WHEN 0 THEN 'İşleme Devam Edilecek'
WHEN 1 THEN 'Kullanıcı Uyarılacak'
WHEN 2 THEN 'İşlem Durdurulacak'
END FROM LG_210_01_CLRNUMS CLR WITH(NOLOCK) WHERE CLR.CLCARDREF=C.LOGICALREF),'')
FROM LG_210_CLCARD C WITH(NOLOCK) WHERE ACTIVE=0
) AS T WHERE T.İşlem<>'' and Siparişte not like '%Durdurulacak'
/*JOB SORGUSU UPDATE İŞLEMİ*/
SET QUOTED_IDENTIFIER ON
UPDATE LG_210_01_CLRNUMS SET ORDRISKOVER = 2
WHERE CLCARDREF IN (SELECT B.CLLOGICALREF FROM CARIRISKSIPARISKAPAT_210 B )
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 BORÇ TAKİP KONTROLLERİ SQL
--BORÇ TAKİPTE OLUP DİĞER MODULLERDE OLMAYAN
SELECT *
FROM LG_001_01_PAYTRANS
WHERE
(
(MODULENR=4 AND TRCODE IN (6,7,8,9,41) AND SIGN=1) OR
(MODULENR=5 AND TRCODE IN (2,3) AND SIGN=1) OR
(MODULENR=6 AND TRCODE IN (3,4) AND SIGN=1)OR
(MODULENR=7 AND TRCODE IN (4) AND SIGN=1)OR
(MODULENR=10 AND TRCODE IN (2) AND SIGN=1)OR
(MODULENR=4 AND TRCODE IN (1,2,3,4,26,42) AND SIGN=0)OR
(MODULENR=5 AND TRCODE IN (1,4) AND SIGN=0)OR
(MODULENR=6 AND TRCODE IN (1,2) AND SIGN=0)OR
(MODULENR=7 AND TRCODE IN (3) AND SIGN=0)OR
(MODULENR=10 AND TRCODE IN (1) AND SIGN=0)
)
OR
(
((MODULENR = 0) AND (TRCODE=0) AND (FICHEREF >= 0))OR
((MODULENR>=0) AND (TRCODE=0)AND(FICHEREF=0)) OR
((MODULENR=0) AND (TRCODE>=0)AND(FICHEREF=0))
)
OR
(
(MODULENR=4 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_INVOICE)) OR
(MODULENR=5 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CLFLINE)) OR
(MODULENR=6 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CSROLL)) OR
(MODULENR=7 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_BNFLINE)) OR
(MODULENR=10 AND FICHEREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_KSLINES))
)
--FİŞİ OLMAYAN CARİ HESAP FİŞLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN(SELECT LOGICALREF FROM LG_001_01_CLFICHE)
--CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN(SELECT LOGICALREF FROM LG_001_CLCARD;
--CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
Merhaba,
Yedek aldıktan sonra işlemleri yapınız.
---------FİŞİ OLMAYAN CARİ HESAP HAREKETLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_CLFICHE)
---------CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
---------CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
***********************
BORÇ TAKİP İLE CARİ HESAP HAREKETLERİNİ KARŞILAŞTIRIYOR
SELECT * FROM LG_208_01_PAYTRANS P WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_CLFLINE L WITH(NOLOCK) ON P.FICHEREF=L.SOURCEFREF AND P.MODULENR=L.MODULENR WHERE P.MODULENR NOT IN (5,61,62,63,64) AND L.LOGICALREF IS NULL UNION
SELECT * FROM LG_208_01_PAYTRANS P WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_CLFLINE L WITH(NOLOCK) ON P.FICHEREF=L.LOGICALREF AND P.MODULENR=L.MODULENR WHERE P.MODULENR IN (5,61,62,63,64) AND L.LOGICALREF IS NULL
*****************
CARİ HESAP HAREKETLERİ İLE BORÇ TAKİP KARŞILAŞTIRIYOR
SELECT * FROM LG_208_01_CLFLINE L WITH(NOLOCK) LEFT OUTER JOIN LG_208_01_PAYTRANS P WITH(NOLOCK) ON L.SOURCEFREF=P.FICHEREF AND L.MODULENR=P.MODULENR WHERE L.MODULENR NOT IN (5,61,62,63,64) AND L.AMOUNT>0
-- AND L.CLIENTREF=140
AND P.LOGICALREF IS NULL
UNION
SELECT
*
FROM
LG_208_01_CLFLINE L WITH(NOLOCK)
LEFT OUTER JOIN LG_208_01_PAYTRANS P WITH(NOLOCK) ON L.LOGICALREF=P.FICHEREF AND L.MODULENR=P.MODULENR
WHERE
L.MODULENR IN (5,61,62,63,64)
AND L.AMOUNT>0
-- AND L.CLIENTREF=140
AND P.LOGICALREF IS NULL
SELECT * FROM LG_208_01_PAYTRANS WHERE MODULENR=10 AND FICHEREF=19372
SELECT * FROM LG_208_01_CLFLINE WHERE MODULENR=10 AND SOURCEFREF=19372
------CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
------CARİ HESAP HAREKET HATALI SATIRLAR
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 AND
SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_INVOICE)
-------FATURASI OLUP CARI HESAP HAREKETİ OLMAYANLAR
SELECT * FROM LG_001_01_INVOICE WHERE TRCODE=8 AND LOGICALREF NOT IN (
SELECT SOURCEFREF FROM LG_001_01_CLFLINE WHERE MODULENR=4 AND TRCODE=38 )
------FİŞİ OLMAYAN CARİ HESAP HAREKETLERİ
SELECT * FROM LG_001_01_CLFLINE
WHERE MODULENR=5 AND SOURCEFREF NOT IN (SELECT LOGICALREF FROM LG_001_01_CLFICHE)
-----CARI KARTI OLMAYAN HAREKETLER
SELECT CLIENTREF FROM LG_001_01_CLFLINE
WHERE CLIENTREF NOT IN (SELECT LOGICALREF FROM LG_001_CLCARD)
----CARİ HESAP HAREKETLERİNDE OLUP CARİ HESAP FİŞİ BULUNMAYAN KAYITLAR İÇİN
SELECT * FROM LG_001_01_CLFLINE WHERE MODULENR=5 AND SOURCEFREF NOT IN
(SELECT LOGICALREF FROM LG_001_01_CLFICHE)
----CARİ HESAP FİŞİ OLAN ANCAK CARİ HAREKETLERDE GÖZÜKMEYEN KAYITLAR İÇİN
SELECT * FROM LG_001_01_CLFICHE WHERE LOGICALREF NOT IN
(SELECT SOURCEFREF FROM LG_001_01_CLFLINE WHERE MODULENR=5)
LOGO BORÇ TAKİP EKRANI TO LOGO
SELECT TOP 100 PERCENT
LGMAIN.TRCURR,LGMAIN.SIGN,LGMAIN.MODULENR, LGMAIN.FICHEREF, LGMAIN.LOGICALREF,
(CASE LGMAIN.SIGN WHEN 0 THEN CASE WHEN LGMAIN.TRCURR IN (0,160) THEN LGMAIN.TOTAL ELSE LGMAIN.TOTAL*LGMAIN.TRRATE END ELSE 0-(CASE WHEN LGMAIN.TRCURR IN (0,160) THEN LGMAIN.TOTAL ELSE LGMAIN.TOTAL*LGMAIN.TRRATE END)END) AS TUTAR,
LGMAIN.DATE_,
LGMAIN.PROCDATE,
LGMAIN.MATCHDATE,
CASE WHEN PAID=0 THEN 'Kapama Yapılmadı' else 'Kapalı' End KAPAMA_DURUMU,
dbo.BM_301_PAYTRANS2ISLEMTURU(LGMAIN.TRCODE,LGMAIN.MODULENR) TUR,
LGMAIN.BRANCH,
LGMAIN.PAID,
LGMAIN.CARDREF
FROM
BM_301_01_PAYTRANS LGMAIN WITH(NOLOCK)
LEFT OUTER JOIN LG_301_01_INVOICE INVFC WITH(NOLOCK) ON (LGMAIN.FICHEREF = INVFC.LOGICALREF)
LEFT OUTER JOIN LG_301_01_CLFLINE CTRNS WITH(NOLOCK) ON (LGMAIN.FICHEREF = CTRNS.LOGICALREF)
LEFT OUTER JOIN LG_301_01_CSROLL RLFIC WITH(NOLOCK) ON (LGMAIN.FICHEREF = RLFIC.LOGICALREF)
LEFT OUTER JOIN LG_301_01_BNFLINE BTRNS WITH(NOLOCK) ON (LGMAIN.FICHEREF = BTRNS.LOGICALREF)
LEFT OUTER JOIN LG_301_01_KSLINES CASHTR WITH(NOLOCK) ON (LGMAIN.FICHEREF = CASHTR.LOGICALREF)
LEFT OUTER JOIN LG_301_01_ORFICHE ORFIC WITH(NOLOCK) ON (LGMAIN.FICHEREF = ORFIC.LOGICALREF)
WHERE
(LGMAIN.CARDREF = 10916) AND
--LGMAIN.CARDREF IN (SELECT CL.LOGICALREF FROM LG_301_CLCARD CL WHERE CL.CODE LIKE '120%') AND
((((LGMAIN.MODULENR=3)
AND(ORFIC.CANCELLED=0))OR((LGMAIN.MODULENR=4)
AND(INVFC.CANCELLED=0))OR((LGMAIN.MODULENR IN (5,61,62))
AND(CTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=6)
AND(RLFIC.CANCELLED=0))OR((LGMAIN.MODULENR=7)
AND(BTRNS.CANCELLED=0))OR((LGMAIN.MODULENR=10)
AND(CASHTR.CANCELLED=0))))
AND LGMAIN.PROCDATE<= '05/01/2020' ORDER BY CARDREF, TRCURR, DATE_, SIGN, MODULENR, FICHEREF, LOGICALREF /*MATCHDATE */
LOGO BORÇ TAKİP YAPILMAMIŞ (KAPATILMAMIŞ) MAKBUZLAR
CREATE VIEW BM_301_AO_RAPOR_BORC_TAKIP_YAPILMAYANLAR AS
SELECT TOP 100 PERCENT * FROM (
SELECT
P.DATE_,
BRANCH ISYERI_KODU,
(select CONVERT(VARCHAR,NR)+', '+NAME from L_CAPIDIV WITH(NOLOCK) WHERE FIRMNR=301 and NR=P.BRANCH) [ISYERI],
CASE P.MODULENR
WHEN 7 THEN
CASE P.TRCODE
WHEN 3 THEN
'Gelen Havale'
WHEN 4 THEN
'Gönderilen Havale'
WHEN 16 THEN
'Banka - Alınan Hizmet Faturası'
WHEN 17 THEN
'Banka - Verilen Hizmet Faturası'
END
WHEN 4 THEN
CASE P.TRCODE
WHEN 1 THEN
'Satınalama Faturası'
WHEN 4 THEN
'Alınan Hizmet Faturası'
WHEN 6 THEN
'Satınalma İade Faturası'
WHEN 13 THEN
'Satınalma Fiyat Farkı Faturası'
WHEN 2 THEN
'Perakende Satış İade Faturası'
WHEN 3 THEN
'Toptan Satış İade Faturası'
WHEN 7 THEN
'Perkande Satış Faturası'
WHEN 8 THEN
'Toptan Satış Faturası'
WHEN 9 THEN
'Verilen Hizmet Faturası'
WHEN 14 THEN
'Satış Fiyat Farkı Faturası'
END
WHEN 5 THEN
CASE P.TRCODE
WHEN 70 THEN
'Kredi Kartı Fişi'
WHEN 2 THEN
'Nakit Ödeme'
WHEN 1 THEN
'Nakit Tahsilat'
WHEN 3 THEN
'Borç Dekontu'
WHEN 4 THEN
'Alacak Dekontu'
WHEN 5 THEN
'Virman Fişi'
WHEN 6 THEN
'Kurfarkı Fişi'
WHEN 14 THEN
'Açılış Fişi'
WHEN 41 THEN
'Verilen Vade Farkı Faturası'
WHEN 42 THEN
'Alınan Vade Farkı Faturası'
WHEN 71 THEN
'Kredi Kartı İade Fişi'
WHEN 72 THEN
'Firma Kredi Kartı Fişi'
WHEN 73 THEN
'Firma Kredi Kartı İade Fişi'
END
WHEN 6 THEN
CASE P.TRCODE
WHEN 1 THEN
'Müşteri Çeki'
WHEN 3 THEN
'Kendi Çekimiz'
END
WHEN 3 THEN
CASE P.TRCODE
WHEN 2 THEN
'Satınalama Siparişi'
WHEN 1 THEN
'Satış Siparişi'
END
WHEN 10 THEN
CASE P.TRCODE
WHEN 1 THEN
'Nakit Tahsilat'
WHEN 2 THEN
'Nakit Ödeme'
END
WHEN 61 THEN
CASE P.TRCODE
WHEN 3 THEN
'Borç Dekontu'
WHEN 4 THEN
'Alacak Dekontu'
END
WHEN 62 THEN
CASE P.TRCODE
WHEN 3 THEN
'Borç Dekontu'
WHEN 4 THEN
'Alacak Dekontu'
END
END AS ISLEM ,
(SELECT CL.CODE FROM LG_301_CLCARD CL WHERE CL.LOGICALREF=CARDREF) CARI_KOD,
(SELECT CL.DEFINITION_ FROM LG_301_CLCARD CL WHERE CL.LOGICALREF=CARDREF) CARI,
(SELECT ROUND(SUM(CASE WHEN C.SIGN=0 THEN C.AMOUNT ELSE 0-C.AMOUNT END),2) BAKIYE FROM LG_301_01_CLFLINE C WITH(NOLOCK) WHERE C.CANCELLED=0 AND C.CLIENTREF=P.CARDREF) AS BAKIYE,
ABS(CASE SIGN WHEN 0 THEN CASE WHEN TRCURR IN (0,160) THEN TOTAL ELSE TOTAL*TRRATE END ELSE 0-(CASE WHEN TRCURR IN (0,160) THEN TOTAL ELSE TOTAL*TRRATE END)END) AS TUTAR
FROM BM_301_01_PAYTRANS P WHERE CANCELLED=0 AND PAID=0 AND SIGN=1 AND P.CARDREF IN (SELECT LOGICALREF FROM LG_301_CLCARD WHERE CODE LIKE '120.%')
) AS T
WHERE BAKIYE>0 ORDER BY CARI_KOD
Blog of Sem 