Tag: BORÇ TAKİP
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 