Logo Bordro Plus Personel Listesi SQL scripti

Bordro Plus üzerinde aktif ve pasif durumdaki personellere ait bilgilerin listesini aşağıdaki sorguyu kullanarak alabilirsiniz.



SELECT
CASE WHEN LGMAIN.TYP = 1 THEN 'AKTİF ÇALIŞAN' ELSE 'PASİF' END AS PERSONELDURUMU ,
LGMAIN.LREF,
LGMAIN.CODE As SicilNo,
LGMAIN.NAME as Adi,
LGMAIN.SURNAME as Soyadi,
LGMAIN.GROUPINDATE as GrubaGirisTarihi,
LGMAIN.INDATE as IseGirisTarihi,
LGMAIN.FIRMNR as KurumNo,
LGMAIN.UNITNR as BirimNo,
L_CAPIUNIT.NAME AS BirimAdi,
ASSG.TITLE as Gorevi,
LGMAIN.PREFIX AS AkademikUnvani,
CASE LAW.SSKSTATUS
      WHEN  1 THEN '4/a Normal'
      WHEN  2 THEN '4/a Emekli'
      WHEN  3 THEN '4/a Çırak'
      WHEN  4 THEN '4/a Stajyer'
      WHEN  5 THEN '4/a Yabancı'
      WHEN  6 THEN '4/b Bağ-kur(çalışan)'
      WHEN  7 THEN '4/b Bağ-kur(emekli)'
      WHEN  8 THEN 'Diğer'
      else 'Tanımsız'
end  as SosyalGuvenlikStatusu,
ASSG.WAGE_WAGE as Ucreti,
Case ASSG.WAGE_CLCTYPE
     WHEN 1 THEN 'Net'
     WHEN 2 THEN 'Brüt'
     ELSE 'Tanımsız'
END AS HesaplamaSekli,
'' as SozlesmeSekli,
INF.DADDY as BabaAdi,
INF.BIRTHPLACE AS DogumYeri,
INF.BIRTHDATE as DogumTarihi,  
CASE INF.STATUS
   WHEN 1 THEN 'Evli'
   WHEN 2 THEN 'Bekar'
   else 'Tanımsız'
end as MedeniHali,
CASE LGMAIN.SEX
   WHEN 1 THEN 'Erkek'
   WHEN 2 THEN 'Kadın'
   ELSE 'Tanımsız'
END AS Cinsiyeti,
CASE LGMAIN.EDUCATION
   WHEN 1 THEN 'İlkokul' WHEN 2 THEN 'Ortaokul' WHEN 3 THEN 'Lise' WHEN 4 THEN 'Yüksek' WHEN 5 THEN 'Yüksek Lisans' WHEN 6 THEN 'Doktora' ELSE 'Tanımsız'
END AS OgretimDurumu,
FINI.IbanNo as IbanNo,
ISNULL (ADRES.EXP1,' ') + ISNULL (ADRES.EXP2,' ')  AS Adres,
ISNULL (GSMNO.EXP1,' ') + ISNULL (GSMNO.EXP2,' ') AS CepNo,
LGMAIN.OUTDATE as CikisTarihi,
LGMAIN.SPECODE as OzelKod,
 INF.IDTCNO as TcKimlikNo,
 '1' AS CalismaAlani
 FROM
LH_001_PERSON LGMAIN WITH(NOLOCK)
LEFT OUTER JOIN LH_001_ASSIGN ASSG WITH(NOLOCK) ON (LGMAIN.LREF  =  ASSG.PERREF) AND (ASSG.ENDDATE IS NULL)
LEFT OUTER JOIN LH_001_PERFIN FINI WITH(NOLOCK) ON (LGMAIN.LREF  =  FINI.PERREF)
LEFT OUTER JOIN LH_001_FAMILY FAM ON   LGMAIN.LREF =   FAM.PERREF  AND RELATION = 0
LEFT OUTER JOIN LH_001_PERIDINF  INF ON   INF.LREF =   FAM.IDREF
LEFT OUTER JOIN LH_001_LAWCHG LAW ON (LGMAIN.LREF  =  LAW.PERREF)
LEFT OUTER JOIN LH_001_CONTACT ADRES (nolock) ON INF.[LREF] = ADRES.CARDREF AND ADRES.TYP = 1
LEFT OUTER JOIN LH_001_CONTACT GSMNO (nolock) ON INF.[LREF] = GSMNO.CARDREF AND GSMNO.TYP = 3
LEFT OUTER JOIN [dbo].L_CAPIUNIT (nolock) on L_CAPIUNIT.FIRMNR = 2 AND LGMAIN.UNITNR = L_CAPIUNIT.NR

Sonuç :