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 

                         CTRNS.DATE_, CLCARD.CODE AS CLCARD_CODE, CLCARD.DEFINITION_ AS CLCARD_DEFINITION_, CTRNS.AMOUNT, CTRNS.LINEEXP, BANKACC.CODE AS BANKACC_CODE, 

                         BANKACC.DEFINITION_ AS BANKACC_DEFINITION_, CLFICHE.FICHENO

FROM            LG_999_01_CLFLINE AS CTRNS LEFT OUTER JOIN

                             (SELECT        LOGICALREF, FICHENO

                               FROM            LG_999_01_CLFICHE) AS CLFICHE ON CTRNS.SOURCEFREF = CLFICHE.LOGICALREF LEFT OUTER JOIN

                             (SELECT        LOGICALREF, CODE, DEFINITION_

                               FROM            LG_999_BANKACC) AS BANKACC ON CTRNS.BANKACCREF = BANKACC.LOGICALREF LEFT OUTER JOIN

                             (SELECT        LOGICALREF, CODE, DEFINITION_

                               FROM            LG_999_CLCARD) AS CLCARD ON CTRNS.CLIENTREF = CLCARD.LOGICALREF

WHERE        (CTRNS.TRCODE = 70) AND (CTRNS.MODULENR = 5) AND (CTRNS.CANCELLED = 0)

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)