--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)