SORUN: CARİ – MALZEME – BANKA HESAP HAREKET GÖRMEMESİNE RAĞMEN SİLİNEMEMESİ. (LOGO GO3)
TRACE KONTROLLERİ YAPILARAK İLGİLİ TABLOLARIN REFERANSLARI KONTROL EDİLDİ VE KAYIT YOKDU:
UPDATE LG_122_CLCARD SET USEDINPERIODS='0' WHERE ( LOGICALREF = 575)
UPDATE LG_122_ITEMS SET USEDINPERIODS='0' WHERE ( LOGICALREF = 1999)
UPDATE LG_122_BANKACC SET USEDINPERIODS='0' WHERE ( LOGICALREF = 4)
YUKARDAKİ KODLAR ÇALIŞTIRILIP KONTROLLER SAĞLANDI…
SELECT
T.LOGICALREF,
KOD= CODE,
AD= T.DEFINITION_,
TL= T.BAKIYE,
BA_TL= CASE WHEN T.BAKIYE < 0 THEN 'A' WHEN T.BAKIYE > 0 THEN 'B' ELSE '' END,
DOVIZ= T.BAKIYE_RD,
BA_DOVIZ= CASE WHEN T.BAKIYE < 0 THEN 'A' WHEN T.BAKIYE > 0 THEN 'B' ELSE '' END
FROM
(
SELECT
CLC.LOGICALREF,
CLC.CODE,
CLC.DEFINITION_,
ISNULL(
(
SELECT
CONVERT(
DECIMAL(38, 2),
(
SUM (GNCLTOT.DEBIT)- SUM (GNCLTOT.CREDIT)
)
)
FROM
LV_118_01_GNTOTCL GNCLTOT
WHERE
(GNCLTOT.TOTTYP = 1) AND
GNCLTOT.CARDREF = CLC.LOGICALREF
),
0
) BAKIYE,
'' BA,
ISNULL(
(
SELECT
CONVERT(
DECIMAL(38, 2),
(
SUM (GNCLTOT.DEBIT)- SUM (GNCLTOT.CREDIT)
)
)
FROM
LV_118_01_GNTOTCL GNCLTOT
WHERE
(GNCLTOT.TOTTYP = 2) AND
GNCLTOT.CARDREF = CLC.LOGICALREF
),
0
) BAKIYE_RD,
'' BA_RD
FROM
LG_118_CLCARD CLC
WHERE
CODE <> 'ÿ'
AND CLC.ACTIVE = 0
) AS T
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…
--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)
Blog of Sem 