LOGO SQL FATURA TAHSILAT

On April 20, 2021June 11, 2022 By semt20In LOGO ERP, Sql8 Comments

SELECT * FROM (
SELECT  
INVOICE.LOGICALREF INVOICELOGICALREF, 
(CASE
WHEN INVOICE.TRCODE=1   THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2   THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3   THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4   THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5   THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6   THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7   THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8   THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9   THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10  THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12  THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13  THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14  THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26  THEN 'Müstahsil Makbuzu'
WHEN INVOICE.TRCODE=1   THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2   THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3   THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4   THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5   THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6   THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7   THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8   THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9   THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10  THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12  THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13  THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14  THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26  THEN 'Müstahsil Makbuzu'
END
) as FATURATIPI,
INVOICE.FICHENO FATURANO,
INVOICE.DATE_  as FATURATARIHI,
YEAR(INVOICE.DATE_)  as YIL,
100*(YEAR(INVOICE.DATE_))+MONTH(INVOICE.DATE_)  AS [Ay],
INVOICE.DOCODE AS BELGENO,
INVOICE.SPECODE AS OZELKOD,
CLCARD.CODE as CARIKOD,
CLCARD.DEFINITION_ as CARIAD, 
(SELECT P.CODE FROM  LG_201_PAYPLANS P WHERE P.LOGICALREF=INVOICE.PAYDEFREF) ODEME,
INVOICE.TRADINGGRP CARIGRUBU,
(SELECT S.CODE+'-'+S.DEFINITION_ FROM LG_SLSMAN S WHERE S.FIRMNR=201 AND S.LOGICALREF= INVOICE.SALESMANREF) SATISELEMANI,
INVOICE.CYPHCODE YETKIKODU,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDIV D WHERE D.FIRMNR=201 AND D.NR=INVOICE.BRANCH) ISYERI,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDEPT D WHERE D.FIRMNR=201 AND D.NR=INVOICE.DEPARTMENT) BOLUM,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIFACTORY D WHERE D.FIRMNR=201 AND D.NR=INVOICE.FACTORYNR) FABRIKA,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIWHOUSE D WHERE D.FIRMNR=201 AND D.NR=INVOICE.SOURCEINDEX) AMBAR,
(SELECT LEFT(D.CODE,4) FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJE,
(SELECT D.CODE+'-'+ D.NAME FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJEKODU, 
(SELECT D.NAME FROM LG_201_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) PROJEACIKLAMASI, 
(SELECT COUNT(*) FROM LG_201_01_STLINE S WHERE S.INVOICEREF=INVOICE.LOGICALREF ) SATIR_SAYISI,
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS FISACIKLAMA,
INVOICE.TOTALEXPENSES MASRAF,
INVOICE.TOTALDISCOUNTS INDIRIM,
INVOICE.NETTOTAL-INVOICE.TOTALVAT TOPLAM,
INVOICE.TOTALEXADDTAX EKVERGI,
INVOICE.TOTALVAT KDV,
INVOICE.NETTOTAL NET,
  
CASE CLCARD.CCURRENCY WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END CARIDOVIZ,
CASE INVOICE.TRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END ISLEMDOVIZ ,
ISNULL((SELECT SUM(CASE WHEN P.TRCURR IN (0,160) THEN P.PAID ELSE  P.TOTAL*P.TRRATE END) FROM LG_201_01_PAYTRANS P WHERE P.MODULENR=4 AND P.FICHEREF=INVOICE.LOGICALREF AND P.SIGN=0 AND P.PAID>0.00000001),0) TOPLAMTAHSILATTUTAR,
(
   STUFF(
                             (SELECT ','+dbo.PAYTRANS2ISLEMTURU(PP.TRCODE,PP.MODULENR)+'['+ CAST(DAY(PP.DATE_) AS varchar)+'.'+CAST(MONTH(PP.DATE_) AS varchar) +'.'+CAST(YEAR(PP.DATE_) AS varchar)+'] /'+format((CASE WHEN PP.TRCURR IN (0,160) THEN ROUND(PP.PAID,2) ELSE  ROUND(PP.TOTAL*PP.TRRATE,2) END),N'','tr-TR')  FROM LG_201_01_PAYTRANS PP WHERE PP.LOGICALREF IN (
                                 SELECT DISTINCT
                                     S2.CROSSREF    /*','+CAST(S2.CROSSREF AS VARCHAR)*/
                                 FROM LG_201_01_PAYTRANS S2
                                 WHERE S2.FICHEREF = INVOICE.LOGICALREF
                                       AND S2.MODULENR=4 AND S2.SIGN=0 )
                                 FOR XML PATH(''), TYPE
                             ).value('.', 'NVARCHAR(MAX)'),
                             1,
                             1,
                             ''
                         )
) TAHSILATBILGILERI  
FROM LG_201_01_INVOICE AS INVOICE WITH (NOLOCK)  
LEFT JOIN LG_201_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF   WHERE(INVOICE.TRCODE IN(2,3,7,8,9,10,11,14 /*1,4,5,6,12,13,26,2,3,7,8,9,10,14*/))) AS T

CREATE FUNCTION [dbo].[PAYTRANS2ISLEMTURU](@TRCODE INT,@MODULENR INT ) 
RETURNS VARCHAR (100)
AS
begin
declare @ISLEM VARCHAR (100)
select @ISLEM = ( SELECT
CASE @MODULENR
               WHEN 7 THEN
                   CASE @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 @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 @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 @TRCODE
                       WHEN 1 THEN
                           'Müşteri Çeki'
                       WHEN 3 THEN
                           'Kendi Çekimiz'
                   END
               WHEN 3 THEN
                   CASE @TRCODE
                       WHEN 2 THEN
                           'Satınalama Siparişi'
                       WHEN 1 THEN
                           'Satış Siparişi'
                   END
               WHEN 10 THEN
                   CASE @TRCODE
                       WHEN 1 THEN
                           'Nakit Tahsilat'
                       WHEN 2 THEN
                           'Nakit Ödeme'
                   END
               WHEN 61 THEN
                   CASE @TRCODE
                       WHEN 3 THEN
                           'Borç Dekontu'
                       WHEN 4 THEN
                           'Alacak Dekontu'
                   END
               WHEN 62 THEN
                   CASE @TRCODE
                       WHEN 3 THEN
                           'Borç Dekontu'
                       WHEN 4 THEN
                           'Alacak Dekontu'
                   END
           END AS ISLEM 
 ) 

return @ISLEM
end
GO

LOD CUSTOM TABLE ILE LDATAGRID VE MODULE KULLANIMI

On April 8, 2021April 8, 2021 By semt20In LOD, LOGO ERPLeave a comment

LOD CUSTOM FORMA CUSTOM TABLEDAN UPDATE İŞLEMİ

On April 7, 2021April 7, 2021 By semt20In LOD, LOGO ERP, TOOLSLeave a comment

Not: Custom tableda tek row olacaktır db kullanıcı bilgilerini tutacaktır. Dolayısıyla Insert işlemi olmayacaktır.Sadece update olacaktır

Sub ButtonClick(ctrl as String)
     if ctrl="B_SAVE" then
      DbErr=0
      CustTables[4].Search(2,2,1)'tek row olacağı için sondaki 1 olmalı ilk logref yani
       if DbErr<>0 Then
        CustTables[4].NewRecord() 
       End if 
      GetTextVal("T_SERVER",T_SERVER)
      GetTextVal("T_DB",T_DB)
      GetTextVal("T_SQL",T_SQL)
      GetTextVal("T_PAROLA",T_PAROLA)
      CustTables[4].SetTxtField("SERVER", T_SERVER)
      CustTables[4].SetTxtField("DB", T_DB)
      CustTables[4].SetTxtField("USERNAME", T_SQL)
      CustTables[4].SetTxtField("PASSWORD", T_PAROLA)
      DbErr=0
      CustTables[4].UpdateRecord()
       if DBErr<>0 then
        Warn(DBErr)
       end if
      CloseForm()   
     end if
End Sub 
Sub FormShow()
         DbErr=0
         CustTables[4].Search(2,2,1)'tek row olacağı için sondaki 1 olmalı ilk logref yani
         if DbErr=0 Then
            CustTables[4].GetTxtField("SERVER",T_SERVER)
            CustTables[4].GetTxtField("DB",T_DB)
            CustTables[4].GetTxtField("USERNAME",T_SQL)
            CustTables[4].GetTxtField("PASSWORD",T_PAROLA) 
         end if
         SetTextVal("T_SERVER",T_SERVER)
         SetTextVal("T_DB",T_DB)
         SetTextVal("T_SQL",T_SQL)
         SetTextVal("T_PAROLA",T_PAROLA)
End Sub

LOD CHECKBOX İŞLEMLERİ (CUSTOM TABLE ÜZERİNDEN)

On April 7, 2021April 7, 2021 By semt20In LOD, LOGO ERPLeave a comment

Sub FormShow() 
    AppTables[12].GetIntFieldR(RecHandle,"LOGICALREF", ORDFICHEREF)  'ORFICHE
    'STR(ORDFICHEREF,ORDFICHEREFSTR)   'STRYE CEVRILMESSE WARNDA ACIKLAMA: EKLENEMEZ
    'warn("ORDFICEHREF:"+ORDFICHEREFSTR) 
    'CustTables[3].GetIntField("MUHUR_DURUMU", MUHUR_DURUMU)  'ORFICHE
    MUHUR_DURUMU=0
    DbErr=0
    CustTables[3].Search(2,2,ORDFICHEREF)
    if DbErr=0 Then
     CustTables[3].GetIntField("MUHUR_DURUMU",MUHUR_DURUMU) 
    end if
    if MUHUR_DURUMU>0 Then
      SetNumProp("chkMuhurDurumu", 1, MUHUR_DURUMU)  'SetNumProp(chckgroupboxname,itemid,1=checked/0=unchecked)     
    end if 
    'STR(MUHUR_DURUMU,MUHUR_DURUMUSTR)   'STRYE CEVRILMESSE WARNDA ACIKLAMA: EKLENEMEZ
    'warn("MUHUR_DURUMU:"+MUHUR_DURUMUSTR) 
End 
Sub AfterSave()
         tmp=0
         AppTables[12].GetIntFieldR(RecHandle,"LOGICALREF", ORDFICHEREF)  'ORFICHE
         GetNumProp("chkMuhurDurumu",1, tmp)
         'warn(tmp)
        'DB'ye Yazılıyor
         DbErr=0
         CustTables[3].Search(2,2,ORDFICHEREF)
         if DbErr<>0 Then
            CustTables[3].NewRecord()
            CustTables[3].SetIntField("MUHUR_DURUMU",tmp) 
         End if  
         DBErr=0
         CustTables[3].UpdateRecord()
         if DBErr<>0 then
            Warn(DBErr)
         end if
End Sub

LOD ÜZERİNDEN RAPOR OLUŞTURMA

On April 6, 2021 By semt20In LOD, LOGO ERPLeave a comment

Protected: SQL SERVER BINARY EXTRACT BLOB

On April 2, 2021 By semt20In LOGO ERP, LOGO OBJECTS, Sql

LOGO SQL ÜRETİM MİKTAR İSTASYON GİDER

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

SELECT 
ITEMS.CODE 'Malzeme Kodu',
ITEMS.NAME 'Malzeme Adı',
year(PRODORD.DATE_) 'Yıl',
MONTH(PRODORD.DATE_) 'Ay',
(SELECT CODE FROM LG_212_ROUTING WHERE LOGICALREF=PRODORD.ROUTINGref) 'Rota Kodu',
PRODORD.ACTAMOUNT 'Miktar',WORKSTAT.CODE 'İş İstasyonu Kodu',
WORKSTAT.LOGICALREF 'İş İstasyonu Adı', 

PRODORD.DATE_ 'Üretim Tarihi',PRODORD.FICHENO 'Üretim No',PRODORD.ACTMATERIALCOST 'Malzeme Maliyeti',
PRODORD.ACTOVERHCOST 'Gider Maliyet',PRODORD.ACTTOTALCOST 'Toplam Maliyet',
(SELECT CODE FROM LG_212_OPERTION WHERE LOGICALREF=DISPLINE.OPERATIONREF)  'Operasyon Kodu',
(SELECT NAME FROM LG_212_OPERTION WHERE LOGICALREF=DISPLINE.OPERATIONREF) 'Operasyon Adı',
OVERHEADS.CODE+' '+ OVERHEADS.DEFINITION 'Gider Kodu/Adı',OVHDTRANS.TOTAL 'Gider Tutarı'

--PRODORD.PLNDURATION 'Planlanan Süre'
FROM LG_212_PRODORD PRODORD  
LEFT JOIN LG_212_OVHDTRANS OVHDTRANS ON OVHDTRANS.PRODORDREF=PRODORD.LOGICALREF
LEFT JOIN LG_212_OVRHDCENTERLN OVRHDCENTERLN ON OVHDTRANS.OVHDCLNREF=OVRHDCENTERLN.LOGICALREF
LEFT JOIN LG_212_OVERHEADS OVERHEADS ON OVERHEADS.LOGICALREF = OVRHDCENTERLN.OVERHEADREF
LEFT JOIN LG_212_DISPLINE DISPLINE ON DISPLINE.PRODORDREF = PRODORD.LOGICALREF  
LEFT JOIN LG_212_ITEMS ITEMS ON ITEMS.LOGICALREF = PRODORD.ITEMREF 
LEFT JOIN LG_212_WORKSTAT WORKSTAT ON WORKSTAT.LOGICALREF= DISPLINE.WSREF
/*ESKI HALI
FROM LG_212_OVRHDCENTERLN OVRHDCENTERLN 
INNER JOIN LG_212_OVERHEADS OVERHEADS ON OVERHEADS.LOGICALREF = OVRHDCENTERLN.OVERHEADREF
INNER JOIN LG_212_OVHDTRANS OVHDTRANS ON OVHDTRANS.OVHDCLNREF = OVRHDCENTERLN.LOGICALREF
INNER JOIN LG_212_PRODORD PRODORD ON PRODORD.LOGICALREF = OVHDTRANS.PRODORDREF 
INNER JOIN LG_212_DISPLINE DISPLINE ON DISPLINE.PRODORDREF = PRODORD.LOGICALREF AND DISPLINE.LOGICALREF = OVHDTRANS.WORKORDREF
INNER JOIN LG_212_ITEMS ITEMS ON ITEMS.LOGICALREF = PRODORD.ITEMREF 
INNER JOIN LG_212_WORKSTAT WORKSTAT ON WORKSTAT.LOGICALREF= DISPLINE.WSREF

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)

LOGO SQL DETAYLI FATURA RAPORU

On March 4, 2021March 23, 2022 By semt20In LOGO ERP, SqlLeave a comment

SELECT ROW_NUMBER() OVER( ORDER BY INTLOGICALREF ASC)     AS INTROWID,  * FROM (
SELECT
LINETYPE,
'520' TXTFIRMA,
INVOICE.LOGICALREF INTLOGICALREF,
INVOICE.TRCODE INTFATURATURU,
(CASE
WHEN INVOICE.TRCODE=1   THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2   THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3   THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4   THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5   THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6   THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7   THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8   THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9   THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10  THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12  THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13  THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14  THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26  THEN 'Müstahsil Makbuzu'
WHEN INVOICE.TRCODE=1   THEN 'Satınalma Faturası'
WHEN INVOICE.TRCODE=2   THEN 'Perakende Satış İade Faturası'
WHEN INVOICE.TRCODE=3   THEN 'Toptan Satış İade Faturası'
WHEN INVOICE.TRCODE=4   THEN 'Alınan Hizmet Faturası'
WHEN INVOICE.TRCODE=5   THEN 'Alınan Proforma Faturası'
WHEN INVOICE.TRCODE=6   THEN 'Satınalma İade Faturası'
WHEN INVOICE.TRCODE=7   THEN 'Perakende Satış Faturası'
WHEN INVOICE.TRCODE=8   THEN 'Toptan Satış Faturası'
WHEN INVOICE.TRCODE=9   THEN 'Verilen Hizmet Faturası'
WHEN INVOICE.TRCODE=10  THEN 'Verilen Proforma Faturası'
WHEN INVOICE.TRCODE=12  THEN 'Alınan Vade Farkı Faturası'
WHEN INVOICE.TRCODE=13  THEN 'Satınalma Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=14  THEN 'Satış Fiyat Farkı Faturası'
WHEN INVOICE.TRCODE=26  THEN 'Müstahsil Makbuzu' 
END
) as TXTFATURATIPI,
INVOICE.FICHENO TXTFATURANO,
INVOICE.DATE_  as TRHFATURATARIHI,
INVOICE.DOCODE AS TXTBELGENO,
INVOICE.SPECODE AS TXTOZELKOD,
CLCARD.CODE as LNGMUSTERIKOD,
CLCARD.DEFINITION_ as LNGMUSTERIAD, 
(SELECT P.CODE FROM  LG_520_PAYPLANS P WHERE P.LOGICALREF=INVOICE.PAYDEFREF) TXTODEMELER,
INVOICE.TRADINGGRP TXTTICARIISLEMGRUBU,
(SELECT S.CODE+'-'+S.DEFINITION_ FROM LG_SLSMAN S WHERE S.FIRMNR=520 AND S.LOGICALREF= INVOICE.SALESMANREF) TXTSATISELEMANI,
INVOICE.CYPHCODE TXTYETKIKODU,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDIV D WHERE D.FIRMNR=520 AND D.NR=INVOICE.BRANCH) TXTISYERI,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIDEPT D WHERE D.FIRMNR=520 AND D.NR=INVOICE.DEPARTMENT) TXTBOLUM,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIFACTORY D WHERE D.FIRMNR=520 AND D.NR=INVOICE.FACTORYNR) TXTFABRIKA,
(SELECT CAST(D.NR AS VARCHAR)+'-'+ D.NAME FROM L_CAPIWHOUSE D WHERE D.FIRMNR=520 AND D.NR=INVOICE.SOURCEINDEX) TXTAMBAR,
(SELECT D.CODE+'-'+ D.NAME FROM LG_520_PROJECT D WHERE D.LOGICALREF=INVOICE.PROJECTREF) TXTPROJEKODU, 
RTRIM(INVOICE.GENEXP1+' '+INVOICE.GENEXP2+' '+INVOICE.GENEXP3+' '+INVOICE.GENEXP4) AS TXTACIKLAMAFIS,
INVOICE.TOTALEXPENSES DBLYPBTOPLAMMASRAF,
INVOICE.TOTALDISCOUNTS DBLYPBTOPLAMINDIRIM,
INVOICE.NETTOTAL-INVOICE.TOTALVAT DBLYPBTOPLAM,
INVOICE.TOTALEXADDTAX DBLYPBTOPLAMEKVERGI,
INVOICE.TOTALVAT DBLYPBTOPLAMKDV,
INVOICE.NETTOTAL DBLYPBTOPLAMNET,
 
ROUND(INVOICE.TOTALEXPENSES/NULLIF(INVOICE.TRRATE,0),2)  DBLIDTOPLAMMASRAF,
ROUND(INVOICE.TOTALDISCOUNTS/NULLIF(INVOICE.TRRATE,0),2)  DBLIDTOPLAMINDIRIM,
ROUND((INVOICE.NETTOTAL-INVOICE.TOTALVAT)/NULLIF(INVOICE.TRRATE,0),2)  DBLIDTOPLAM,
ROUND(INVOICE.TOTALEXADDTAX/NULLIF(INVOICE.TRRATE,0),2)  DBLIDTOPLAMEKVERGI,
ROUND(INVOICE.TOTALVAT/NULLIF(INVOICE.TRRATE,0),2)  DBLIDTOPLAMKDV,
ROUND(INVOICE.NETTOTAL/NULLIF(INVOICE.TRRATE,0),2) DBLIDTOPLAMNET,
 
CASE CLCARD.CCURRENCY WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTCARIDOVIZ,
CASE INVOICE.TRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTISLEMDOVIZ,
 
CASE LINETYPE WHEN 0 THEN 'MALZEME' WHEN 1 THEN 'PROMOSYON' WHEN 2 THEN 'INDIRIM' WHEN 3 THEN 'MASRAF' WHEN 4 THEN 'HIZMET' ELSE '' END TXTTUR,
CASE WHEN STLINE.LINETYPE=4 THEN SRV.CODE ELSE INV.CODE END AS TXTKOD,
CASE WHEN STLINE.LINETYPE=4 THEN SRV.DEFINITION_ ELSE INV.NAME END TXTACIKLAMA,
COALESCE(
CASE STLINE.UINFO1
WHEN 0 THEN STLINE.AMOUNT
ELSE STLINE.AMOUNT*STLINE.UINFO2/STLINE.UINFO1
END, STLINE.AMOUNT/1000) DBLMIKTAR,
CASE WHEN LINETYPE=4 THEN UNTS.CODE ELSE UNT.CODE END TXTBIRIM, 
ROUND(STLINE.PRICE,2) DBLBIRIMFIYAT, 
CASE STLINE.PRCURR WHEN 0 THEN 'TL' WHEN 1 THEN 'USD' WHEN 17 THEN 'GBP' WHEN 20 THEN 'EUR' ELSE '' END TXTDOVIZTURU,
ROUND(STLINE.DISCPER,2) DBLYUZDE,
ROUND(STLINE.VAT,2) DBLKDV,
ROUND(STLINE.TOTAL,2) DBLTUTAR, 
ROUND(STLINE.VATAMNT,2) DBLKDVTUTARI,
ROUND(STLINE.VATMATRAH,2) DBLNETTUTAR   
FROM LG_520_01_INVOICE AS INVOICE WITH (NOLOCK)
LEFT JOIN LG_520_01_STLINE AS STLINE ON INVOICE.LOGICALREF = STLINE.INVOICEREF
LEFT JOIN LG_520_ITEMS INV ON INV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_520_SRVCARD SRV ON SRV.LOGICALREF=STLINE.STOCKREF
LEFT JOIN LG_520_CLCARD CLCARD ON CLCARD.LOGICALREF=INVOICE.CLIENTREF
LEFT JOIN LG_520_EMUHACC EMUH ON EMUH.LOGICALREF=STLINE.ACCOUNTREF
LEFT JOIN LG_520_UNITSETL UNT ON UNT.UNITSETREF=INV.UNITSETREF AND LINENR=1 
LEFT JOIN LG_520_UNITSETL UNTS ON UNTS.UNITSETREF=SRV.UNITSETREF AND UNTS.LINENR=1 WHERE(INVOICE.TRCODE IN(1/*,4,5,6,12,13,26,2,3,7,8,9,10,14*/))) AS T

Blog of Sem

LOGO SQL FATURA TAHSILAT

LOD CUSTOM TABLE ILE LDATAGRID VE MODULE KULLANIMI

LOD CUSTOM FORMA CUSTOM TABLEDAN UPDATE İŞLEMİ

LOD CHECKBOX İŞLEMLERİ (CUSTOM TABLE ÜZERİNDEN)

LOD TREEVIEW EKLEME VE CUSTOM FORM BAĞLAMA

LOD ÜZERİNDEN RAPOR OLUŞTURMA

Protected: SQL SERVER BINARY EXTRACT BLOB

LOGO SQL ÜRETİM MİKTAR İSTASYON GİDER

LOGO BORÇ TAKİP KONTROLLERİ SQL

LOGO SQL DETAYLI FATURA RAPORU