TL_ABSEVT_VW

(SQL View)
 Index Back

Absence data fom AM

View used in Absence type interactive report to retrieve data from Absence Management.

SELECT ABS_EVT.EMPLID , ABS_EVT.EMPL_RCD , ABS_EVT.BGN_DT , ABS_EVT.END_DT , ABS_EVT.PIN_TAKE_NUM , ABS_EVT.CAL_RUN_ID , PIN.DESCR , PIN.COUNTRY , PIN.USED_BY , (%DateDiff( ABS_EVT.BGN_DT, ABS_EVT.END_DT) +1) , ABS_EVT.ABSENCE_REASON , ABS_RSN.DESCR , CASE WHEN ABS_EVT.WF_STATUS = ' ' THEN (CASE WHEN ABS_EVT.MANAGER_APPR_IND = 'Y' THEN 'A' ELSE 'N' END) ELSE ABS_EVT.WF_STATUS END AS WF_STATUS FROM PS_GP_ABS_EVENT ABS_EVT , PS_GP_PIN PIN , PS_GP_ABS_REASON ABS_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) UNION SELECT ABS_EVT.EMPLID , ABS_EVT.EMPL_RCD , ABS_EVT.BGN_DT , ABS_EVT.END_DT , ABS_EVT.PIN_TAKE_NUM , ABS_EVT.CAL_RUN_ID , PIN.DESCR , PIN.COUNTRY , PIN.USED_BY , (%DateDiff( ABS_EVT.BGN_DT, ABS_EVT.END_DT) +1) , ABS_EVT.ABSENCE_REASON , ' ' , CASE WHEN ABS_EVT.WF_STATUS = ' ' THEN (CASE WHEN ABS_EVT.MANAGER_APPR_IND = 'Y' THEN 'A' ELSE 'N' END) ELSE ABS_EVT.WF_STATUS END AS WF_STATUS FROM PS_GP_ABS_EVENT ABS_EVT , PS_GP_PIN PIN WHERE PIN.PIN_NUM = ABS_EVT.PIN_TAKE_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: TL_ABSEVT_L_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 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
    7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    8 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    9 USED_BY Character(1) VARCHAR2(1) NOT NULL USED_BY
    A=All Countries
    C=Specific Country
    10 DAY_COUNT Signed Number(20,6) DECIMAL(18,6) NOT NULL Day Count
    11 ABSENCE_REASON Character(3) VARCHAR2(3) NOT NULL absence reason
    12 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    13 WF_STATUS Character(1) VARCHAR2(1) NOT NULL Workflow Status
    A=Approved
    C=Cancelled
    D=Denied
    E=Error. Contact Administrator.
    F=Awaiting final approval
    I=In Approval Process
    M=Administrator is Processing
    N=Not Available
    P=Rework
    S=Submitted
    V=Data Saved