CARİ – Blog of Sem

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 )

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

Tag: CARİ

LOGO CARI RISK SIPARIS KAPAT SQL

LOGO BORÇ TAKİP KONTROLLERİ SQL

LOGO SQL PAYTRANSDAN CARİ BAKİYELERİNİ GÖRME