TL_ABSEVT_L_VW

(SQL View)
 Index Back

Absence data fom AM - Lang Ref

View used in Absence type interactive report to retrieve data from Absence Management. Language reference view of TL_ABSEVT_VW

SELECT ABS_EVT.EMPLID , ABS_EVT.EMPL_RCD , ABS_EVT.BGN_DT , ABS_EVT.END_DT , ABS_EVT.PIN_TAKE_NUM , LPIN.LANGUAGE_CD , LPIN.DESCR , ABS_EVT.ABSENCE_REASON , LABS_RSN.DESCR FROM PS_GP_ABS_EVENT ABS_EVT , PS_GP_PIN PIN , PS_GP_PIN_LANG LPIN , PS_GP_ABS_REASON ABS_RSN , PS_GP_ABS_RSN_LANG LABS_RSN WHERE PIN.PIN_NUM = ABS_EVT.PIN_TAKE_NUM AND ABS_RSN.USED_BY = PIN.USED_BY AND ABS_RSN.COUNTRY = PIN.COUNTRY AND ABS_RSN.ABSENCE_REASON = ABS_EVT.ABSENCE_REASON AND ABS_RSN.ABS_TYPE_OPTN = ( SELECT ABS_TAK.ABS_TYPE_OPTN FROM PS_GP_ABS_TAKE ABS_TAK WHERE ABS_TAK.PIN_NUM = ABS_EVT.PIN_TAKE_NUM AND ABS_TAK.EFFDT = ( SELECT MAX(EABS_TAK.EFFDT) FROM PS_GP_ABS_TAKE EABS_TAK WHERE EABS_TAK.PIN_NUM = ABS_TAK.PIN_NUM AND EABS_TAK.EFFDT <= ABS_EVT.BGN_DT)) AND ABS_RSN.EFFDT = ( SELECT MAX(EABS_RSN.EFFDT) FROM PS_GP_ABS_REASON EABS_RSN WHERE EABS_RSN.USED_BY = ABS_RSN.USED_BY AND EABS_RSN.COUNTRY = ABS_RSN.COUNTRY AND EABS_RSN.ABSENCE_REASON = ABS_RSN.ABSENCE_REASON AND EABS_RSN.ABS_TYPE_OPTN = ABS_RSN.ABS_TYPE_OPTN AND EABS_RSN.EFFDT <= ABS_EVT.BGN_DT) AND PIN.PIN_NUM = LPIN.PIN_NUM AND ABS_RSN.USED_BY = LABS_RSN.USED_BY AND ABS_RSN.COUNTRY = LABS_RSN.COUNTRY AND ABS_RSN.ABSENCE_REASON = LABS_RSN.ABSENCE_REASON AND ABS_RSN.ABS_TYPE_OPTN = LABS_RSN.ABS_TYPE_OPTN AND ABS_RSN.EFFDT = LABS_RSN.EFFDT UNION SELECT ABS_EVT.EMPLID , ABS_EVT.EMPL_RCD , ABS_EVT.BGN_DT , ABS_EVT.END_DT , ABS_EVT.PIN_TAKE_NUM , LPIN.LANGUAGE_CD , LPIN.DESCR , ABS_EVT.ABSENCE_REASON , ' ' FROM PS_GP_ABS_EVENT ABS_EVT , PS_GP_PIN PIN , PS_GP_PIN_LANG LPIN WHERE PIN.PIN_NUM = ABS_EVT.PIN_TAKE_NUM AND PIN.PIN_NUM = LPIN.PIN_NUM AND NOT EXISTS ( SELECT ABS_RSN.DESCR FROM PS_GP_ABS_REASON ABS_RSN WHERE ABS_RSN.USED_BY = PIN.USED_BY AND ABS_RSN.COUNTRY = PIN.COUNTRY AND ABS_RSN.ABSENCE_REASON = ABS_EVT.ABSENCE_REASON AND ABS_RSN.ABS_TYPE_OPTN = ( SELECT ABS_TAK.ABS_TYPE_OPTN FROM PS_GP_ABS_TAKE ABS_TAK WHERE ABS_TAK.PIN_NUM = ABS_EVT.PIN_TAKE_NUM AND ABS_TAK.EFFDT = ( SELECT MAX(EABS_TAK.EFFDT) FROM PS_GP_ABS_TAKE EABS_TAK WHERE EABS_TAK.PIN_NUM = ABS_TAK.PIN_NUM AND EABS_TAK.EFFDT <= ABS_EVT.BGN_DT)) AND ABS_RSN.EFFDT = ( SELECT MAX(EABS_RSN.EFFDT) FROM PS_GP_ABS_REASON EABS_RSN WHERE EABS_RSN.USED_BY = ABS_RSN.USED_BY AND EABS_RSN.COUNTRY = ABS_RSN.COUNTRY AND EABS_RSN.ABSENCE_REASON = ABS_RSN.ABSENCE_REASON AND EABS_RSN.ABS_TYPE_OPTN = ABS_RSN.ABS_TYPE_OPTN AND EABS_RSN.EFFDT <= ABS_EVT.BGN_DT))

  • Related Language Record for TL_ABSEVT_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
    3 BGN_DT Date(10) DATE Begin Date
    4 END_DT Date(10) DATE end date
    5 PIN_TAKE_NUM Number(8,0) INTEGER NOT NULL Absence Take
    6 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 ABSENCE_REASON Character(3) VARCHAR2(3) NOT NULL absence reason
    9 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr