Logo Kredi Kartı Fiş Listesi

Logo Kredi Kartı fiş listesi
Logoda Cari hesap fiş listesini aldığımızda kredi kartının detayını vermiyor
aşağıdaki sql size kredi fişinin satırlarınıda listeler

SELECT DISTINCT 
                 LGMAIN.LOGICALREF, LGMAIN.TRCODE, LGMAIN.FICHENO, LGMAIN.DOCODE, LGMAIN.DATE_, CLNTC.CODE, CLNTC.DEFINITION_,  CTRNS.AMOUNT, BNACC.CODE AS BANKCODE, BNACC.DEFINITION_ AS BANKDEFINITION_
FROM     LG_001_01_CLFICHE AS LGMAIN LEFT OUTER JOIN
                  LG_001_01_CLFLINE AS CTRNS ON LGMAIN.LOGICALREF = CTRNS.SOURCEFREF AND CTRNS.MODULENR = 5 LEFT OUTER JOIN
                  LG_001_CLCARD AS CLNTC ON CTRNS.CLIENTREF = CLNTC.LOGICALREF LEFT OUTER JOIN
                  LG_001_BANKACC AS BNACC ON LGMAIN.BANKACCREF = BNACC.LOGICALREF
WHERE  (LGMAIN.TRCODE = 70) AND LGMAIN.CANCELLED = 0
ORDER BY LGMAIN.LOGICALREF,LGMAIN.TRCODE, LGMAIN.FICHENO

Logo Cari Hesap Muhasebe Kodları

Cari Hesaplara bağlı muhasebe kodlarını gösteren sql

SELECT     CLCARD.CODE, CLCARD.DEFINITION_, EMUH.MUHCODE, EMUH.MUHDEFINITION_
FROM            LG_006_CLCARD AS CLCARD LEFT OUTER JOIN
                             (SELECT        CRDACREF.CARDREF, EMUHACC.CODE AS MUHCODE, EMUHACC.DEFINITION_ AS MUHDEFINITION_
                               FROM            lg_006_CRDACREF AS CRDACREF FULL OUTER JOIN
                                                         lg_006_EMUHACC AS EMUHACC ON CRDACREF.ACCOUNTREF = EMUHACC.LOGICALREF
                               WHERE        (CRDACREF.TRCODE = 5)) AS EMUH ON CLCARD.LOGICALREF = EMUH.CARDREF

order BY code

Logo Cari hesap Durum Raporu

Cari Hesapların Son alacak ve borç durumları ile son yapılan alacak / borç işlemimin tarihlerini verir
hareketsiz cariler listelenmez

SELECT  
CLCARD.LOGICALREF,
    CLCARD.CODE AS [CARİ KODU],
    CLCARD.DEFINITION_ AS [CARİ ÜNVANI],
(select TOP 1 case when (sum(DEBIT)- sum(CREDIT)) > 0 then ROUND(sum(DEBIT) - sum(CREDIT),2) ELSE 0  END  from LV_120_01_CLEKSTRE 
	where TRCURR = 0 and CLIENTREF = CLCARD.LOGICALREF and CANCELLED = 0)AS [BORÇ BAKİYE],
	(select TOP 1 case when (sum(CREDIT)-sum(DEBIT)) > 0 then ROUND(sum(CREDIT)-sum(DEBIT),2) ELSE 0 END  from LV_120_01_CLEKSTRE 
	where TRCURR = 0 and CLIENTREF = CLCARD.LOGICALREF and CANCELLED = 0)AS [ALACAK BAKİYE],
    (SELECT TOP 1 DATE_ FROM LG_120_01_CLFLINE WHERE  SIGN = 0 AND CLIENTREF=CLCARD.LOGICALREF AND CANCELLED = 0 ORDER BY DATE_ DESC) AS [SON BORC TARIHI],
    (SELECT TOP 1 DATE_ FROM LG_120_01_CLFLINE WHERE  SIGN = 1 AND CLIENTREF=CLCARD.LOGICALREF AND CANCELLED = 0 ORDER BY DATE_ DESC) AS [SON ALACAK TARIHI]
FROM  LG_120_01_CLFLINE AS CLFLINE INNER JOIN LG_120_CLCARD AS CLCARD ON CLFLINE.CLIENTREF = CLCARD.LOGICALREF
WHERE  CANCELLED = 0 
GROUP BY CLCARD.CODE, CLCARD.DEFINITION_,CLCARD.LOGICALREF

Logo Alınan / Verilen Hizmetlerin Muhasebe Kodları

Logo da alınan ve verilen hizmetlerin bağlı muhasebe kodları sql kodları rapor üretici içindir
normal sorgu olarak kullanmak isterseniz LG_dönemno_CRDACREF şeklinde güncelleyebilirsiniz.
Alınan Hizmetler

SELECT     SVRCARD.CODE, SVRCARD.DEFINITION_, SVRCARD.VAT, EMUH.MUHCODE, EMUH.MUHDEFINITION_
FROM            {SRVCARD} AS SVRCARD LEFT OUTER JOIN
                             (SELECT        CRDACREF.CARDREF, EMUHACC.CODE AS MUHCODE, EMUHACC.DEFINITION_ AS MUHDEFINITION_
                               FROM            {CRDACREF} AS CRDACREF FULL OUTER JOIN
                                                         {EMUHACC} AS EMUHACC ON CRDACREF.ACCOUNTREF = EMUHACC.LOGICALREF
                               WHERE        (CRDACREF.TRCODE = 3)) AS EMUH ON SVRCARD.LOGICALREF = EMUH.CARDREF
WHERE        (SVRCARD.CARDTYPE = 1)

Verilen Hizmetler

SELECT     SVRCARD.CODE, SVRCARD.DEFINITION_, SVRCARD.VAT, EMUH.MUHCODE, EMUH.MUHDEFINITION_
FROM            {SRVCARD} AS SVRCARD LEFT OUTER JOIN
                             (SELECT        CRDACREF.CARDREF, EMUHACC.CODE AS MUHCODE, EMUHACC.DEFINITION_ AS MUHDEFINITION_
                               FROM            {CRDACREF} AS CRDACREF FULL OUTER JOIN
                                                         {EMUHACC} AS EMUHACC ON CRDACREF.ACCOUNTREF = EMUHACC.LOGICALREF
                               WHERE        (CRDACREF.TRCODE = 4)) AS EMUH ON SVRCARD.LOGICALREF = EMUH.CARDREF
WHERE        (SVRCARD.CARDTYPE = 2)

Logo Go / Tiger Hareketsiz Carileri Kullanımdışı yapma

Logo Go / Tiger Hareketsiz Carileri Kullanımdışı yapma


with bb as (SELECT LOGICALREF,CODE, DEFINITION_,( select count (CLIENTREF) from dbo.LV_115_01_CLEKSTRE where 

CLIENTREF = aa.LOGICALREF ) as say FROM dbo.LG_115_CLCARD as aa with (nolock) where CARDTYPE = 3 )

update LG_115_CLCARD set ACTIVE = 1 where LOGICALREF in ( select LOGICALREF from bb where say = 0)