Logo BordroPlus Borç Kesinti Liste Sorgusu (LH_001_PERLOAN - LH_001_LOANDEF )

        BordroPlus programında borç listesini almak için aşağıdaki sorguyu kullanabilirsiniz, veya view haline de getirebilirsiniz.

LH_001_PERLOAN: borç kayıtlarının tutulduğu tablo

LH_001_LOANDEF : Borç türlerinin tanım tablosu

SELECT DISTINCT

YEAR( PNTLN."PERDBEG") as Yil,

MONTH( PNTLN."PERDBEG") as Ay,

PERSONS.CODE as PersonelSicilKodu,

PERSONS.NAME +  ' ' + PERSONS.SURNAME AS AdiSoyadi,

LH_001_LOANDEF.CODE AS KesintiKodu,

LH_001_LOANDEF.DEFINITION_ AS KesintiAciklama,

PNTLN."PERDBEG" AS "PeriodBegdate",

PNTLN."AMNT" AS "KesintiTutari",

PERSONS.LREF AS PersonelReferans,

PERSONS.LOCINDATE AS IsyerineGirisTarihi

,PNTLN.XREF AS "Loan_Reference",

PNTLN."LREF" AS "Puantaj_Reference",

PNTLN."PREF" AS "Point_Reference",

L_CAPIDIV.NAME as Isyeri

,L_CAPIDIV.NR as IsyNr

,L_CAPIDIV.FIRMNR as KrmNr

FROM

       LH_001_PNTLINE  PNTLN WITH(NOLOCK)

       LEFT OUTER JOIN LH_001_PAYELEM  PAYMENTELEMENTINFO WITH(NOLOCK) ON PAYMENTELEMENTINFO.TYP=PNTLN.TYP AND PAYMENTELEMENTINFO.NR=PNTLN.NR    -- (PAYMENTELEMENTINFO."LREF" = PNTLN."PAYELEMREF")

       LEFT OUTER JOIN LH_001_FIRMPAR FIRMPARINFO WITH(NOLOCK) ON FIRMPARINFO.TYP=PNTLN.TYP AND FIRMPARINFO.NR=PNTLN.NR AND  FIRMPARINFO.FIRMNR=PNTLN.FIRMNR  -- (FIRMPARINFO."LREF" = PNTLN."FIRMPARREF")

       LEFT JOIN LH_001_PERLOAN  WITH(NOLOCK)  ON LH_001_PERLOAN.LREF  =PNTLN.XREF

       LEFT JOIN LH_001_LOANDEF  WITH(NOLOCK)  ON LH_001_LOANDEF.LREF  =LH_001_PERLOAN.CARDREF

       LEFT JOIN LH_001_PERSON  PERSONS WITH(NOLOCK)  ON PERSONS.LREF  =PNTLN."PERREF"

       LEFT JOIN L_CAPIFIRM  WITH(NOLOCK) ON PNTLN.FIRMNR = L_CAPIFIRM.LOGICALREF

       LEFT JOIN L_CAPIDIV WITH(NOLOCK) ON L_CAPIDIV.FIRMNR = PNTLN.FIRMNR AND L_CAPIDIV.NR = PNTLN.LOCNR

WHERE  LH_001_LOANDEF.GROUPNR IS NOT NULL