LOGO CARI HAREKET FATURA BAGLANTISIZLAR

On January 19, 2022 By semt20In LOGO ERP, SqlLeave a comment

CREATE VIEW [dbo].[BV_121_CLFLINE_FATURA_BAGLANTISIZ] AS 
SELECT 
CLFL.LOGICALREF CLFLINE_LOGICALREF,
 CASE ((MODULENR*100)+TRCODE)
            WHEN 381 THEN 'Satış Siparişi'
            WHEN 382 THEN 'Satınalma Siparişi'
            WHEN 431 THEN 'Satın Alma Faturası'
            WHEN 432 THEN 'Perakende Satış İade Faturası'
            WHEN 433 THEN 'Toptan Satış İade Faturası'
            WHEN 434 THEN 'Alınan Hizmet Faturası'
            WHEN 435 THEN 'Alınan Proforma Faturası'
            WHEN 436 THEN 'Alım İade Faturası'
            WHEN 437 THEN 'Perakende Satış Faturası'
            WHEN 438 THEN 'Toptan Satış Faturası'
            WHEN 439 THEN 'Verilen Hizmet Faturası'
            WHEN 440 THEN 'Verilen Proforma Faturası'
            WHEN 441 THEN 'Verilen Vade Farkı Faturası'
            WHEN 442 THEN 'Alınan Vade Farkı Faturası'
            WHEN 443 THEN 'Alınan Fiyat Farkı Faturası'
            WHEN 444 THEN 'Verilen Fiyat Farkı Faturası'
            WHEN 456 THEN 'Müstahsil Makbuzu'
            WHEN 501 THEN 'Nakit Tahsilat'
            WHEN 502 THEN 'Nakit Ödeme'
            WHEN 503 THEN 'Borç Dekontu'
            WHEN 504 THEN 'Alacak Dekontu'
            WHEN 505 THEN 'Virman İşlemi'
            WHEN 506 THEN 'Kur Farkı İşlemi'
            WHEN 512 THEN 'Özel İşlem'
            WHEN 514 THEN 'Açılış Fişi'
            WHEN 570 THEN 'Kredi Kartı Fişi'
            WHEN 661 THEN 'Çek Girişi'
            WHEN 662  THEN 'Senet Girişi'
            WHEN 663 THEN 'Çek Çıkış Cari Hesaba'--CSTRANS.CLACCREF
            WHEN 664 THEN 'Senet Çıkış Cari Hesaba'
            WHEN 720 THEN 'Gelen Havaleler'
            WHEN 721 THEN 'Gönderilen Havaleler'
            WHEN 728 THEN 'Banka Alınan Hizmet'
            WHEN 729 THEN 'Banka Verilen Hizmet'
            WHEN 1001 THEN 'Nakit Tahsilat'
            WHEN 1002 THEN 'Nakit Ödeme'
            WHEN 6103 THEN 'Borç Dekontu (Çek)'
            WHEN 6104 THEN 'Alacak Dekontu (Çek)'
        END [FIS_TURU],
(SELECT C.CODE FROM LG_121_CLCARD C WHERE C.LOGICALREF=CLIENTREF ) CARIKODU,
(SELECT C.DEFINITION_ FROM LG_121_CLCARD C WHERE C.LOGICALREF=CLIENTREF )  CARIADI,
DATE_ TARIH,
TRANNO,
DOCODE BELGENO,
AMOUNT TUTAR FROM LG_121_01_CLFLINE CLFL WHERE MODULENR=4 AND SOURCEFREF NOT IN  (SELECT LOGICALREF FROM LG_121_01_INVOICE)

Silme işlemi:

delete LG_121_01_CLFLINE WHERE LOGICALREF IN (SELECT CLFLINE_LOGICALREF FROM BV_121_CLFLINE_FATURA_BAGLANTISIZ)

LOGO TIGER LV_TRDGRP HATASI ÇÖZÜMÜ

On December 12, 2021May 23, 2022 By semt20In LOGO ERP, SqlLeave a comment

LOGO Tigerda tabloları sürüme göre güncellerken alınan LV_TRDGRP collation hatası(view kontrolü sonrası tespit edilen – sorunu yaratan tablo alanı: LG_XXX_XX_CLFLINE – TIGER3 VERSION 2.75) :

SQL_Latin1_General_CP1254_Ci_AS

Bendeki çözümü: sql serverde collationı değiştirmek oldu

LOGO BORÇ TAKİP KONTROLLERİ SQL

On March 24, 2021 By semt20In LOGO ERP, SqlLeave a comment

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

LOGO CARI HAREKET FATURA BAGLANTISIZLAR

LOGO TIGER LV_TRDGRP HATASI ÇÖZÜMÜ

LOGO BORÇ TAKİP KONTROLLERİ SQL