TL_LEAV_CMP_VW

(SQL View)
 Index Back

Absence data - Benefit & Comp

View used in Absence types (Interactive report) to retrieve Compensatory time and Benefits data.

SELECT RPT_TIME.EMPLID , RPT_TIME.EMPL_RCD , RPT_TIME.DUR , RPT_TIME.SEQ_NBR , RPT_TIME.TRC , COMPLEAV.COMP_LEAV_IND , COMPLEAV.COMP_TIME_PLAN , ( SELECT COMP.DESCR FROM PS_TL_COMP_TIME COMP WHERE COMP.COMP_TIME_PLAN = COMPLEAV.COMP_TIME_PLAN AND COMP.EFFDT = ( SELECT MAX(ECOMP.EFFDT) FROM PS_TL_COMP_TIME ECOMP WHERE ECOMP.COMP_TIME_PLAN = COMP.COMP_TIME_PLAN AND ECOMP.EFFDT <= %CurrentDateIn)) , COMPLEAV.TL_QUANTITY , RPT_TIME.PROJECT_ID FROM PS_TL_COMPLEAV_TBL COMPLEAV, PS_TL_RPTD_TIME RPT_TIME WHERE RPT_TIME.EMPLID = COMPLEAV.EMPLID AND RPT_TIME.EMPL_RCD = COMPLEAV.EMPL_RCD AND RPT_TIME.DUR= COMPLEAV.DUR AND RPT_TIME.TRC = COMPLEAV.TRC AND COMPLEAV.COMP_LEAV_IND IN ('CERN','CTKN') UNION ALL SELECT RPT_TIME.EMPLID , RPT_TIME.EMPL_RCD , RPT_TIME.DUR , RPT_TIME.SEQ_NBR , RPT_TIME.TRC , 'LTKN' , ( SELECT Leav.BENEFIT_PLAN FROM PS_leave_plan Leav WHERE Leav.EMPLID = RPT_TIME.EMPLID AND Leav.EMPL_RCD = RPT_TIME.EMPL_RCD AND Leav.PLAN_TYPE = ( SELECT ERN_TBL.PLAN_TYPE FROM PS_EARNINGS_ACCRL ERN_TBL WHERE ERN_TBL.ERNCD = ( SELECT ERNCD.TL_ERNCD FROM PS_TL_ERNCD_TBL ERNCD WHERE ERNCD.TRC = RPT_TIME.TRC AND ERNCD.PAY_SYSTEM = 'NA' AND ERNCD.EFFDT = ( SELECT MAX(EERNCD.EFFDT) FROM PS_TL_ERNCD_TBL EERNCD WHERE EERNCD.TRC = ERNCD.TRC AND EERNCD.PAY_SYSTEM = ERNCD.PAY_SYSTEM AND EERNCD.EFFDT <= %CurrentDateIn)) AND ERN_TBL.HRS_TAKEN_ADD = 'Y' AND ERN_TBL.EFFDT = ( SELECT MAX(EERN_TBL.EFFDT) FROM PS_EARNINGS_ACCRL EERN_TBL WHERE EERN_TBL.ERNCD = ERN_TBL.ERNCD AND EERN_TBL.HRS_TAKEN_ADD = ERN_TBL.HRS_TAKEN_ADD AND EERN_TBL.EFFDT <= %CurrentDateIn)) AND Leav.BENEFIT_NBR = 0 AND Leav.EFFDT = ( SELECT MAX(ELeav.EFFDT) FROM PS_leave_plan ELeav WHERE ELeav.EMPLID = Leav.EMPLID AND ELeav.EMPL_RCD = Leav.EMPL_RCD AND ELeav.PLAN_TYPE = Leav.PLAN_TYPE AND ELeav.BENEFIT_NBR = Leav.BENEFIT_NBR AND ELeav.EFFDT <= %CurrentDateIn)) ,( SELECT B_PLAN.DESCR FROM PS_leave_plan Leav , PS_BENEF_PLAN_TBL B_PLAN WHERE Leav.EMPLID = RPT_TIME.EMPLID AND Leav.EMPL_RCD = RPT_TIME.EMPL_RCD AND Leav.PLAN_TYPE = ( SELECT ERN_TBL.PLAN_TYPE FROM PS_EARNINGS_ACCRL ERN_TBL WHERE ERN_TBL.ERNCD = ( SELECT ERNCD.TL_ERNCD FROM PS_TL_ERNCD_TBL ERNCD WHERE ERNCD.TRC = RPT_TIME.TRC AND ERNCD.PAY_SYSTEM = 'NA' AND ERNCD.EFFDT = ( SELECT MAX(EERNCD.EFFDT) FROM PS_TL_ERNCD_TBL EERNCD WHERE EERNCD.TRC = ERNCD.TRC AND EERNCD.PAY_SYSTEM = ERNCD.PAY_SYSTEM AND EERNCD.EFFDT <= %CurrentDateIn)) AND ERN_TBL.HRS_TAKEN_ADD = 'Y' AND ERN_TBL.EFFDT = ( SELECT MAX(EERN_TBL.EFFDT) FROM PS_EARNINGS_ACCRL EERN_TBL WHERE EERN_TBL.ERNCD = ERN_TBL.ERNCD AND EERN_TBL.HRS_TAKEN_ADD = ERN_TBL.HRS_TAKEN_ADD AND EERN_TBL.EFFDT <= %CurrentDateIn)) AND Leav.BENEFIT_NBR = 0 AND Leav.EFFDT = ( SELECT MAX(ELeav.EFFDT) FROM PS_leave_plan ELeav WHERE ELeav.EMPLID = Leav.EMPLID AND ELeav.EMPL_RCD = Leav.EMPL_RCD AND ELeav.PLAN_TYPE = Leav.PLAN_TYPE AND ELeav.BENEFIT_NBR = Leav.BENEFIT_NBR AND ELeav.EFFDT <= %CurrentDateIn) AND B_PLAN.PLAN_TYPE = Leav.PLAN_TYPE AND B_PLAN.BENEFIT_PLAN = Leav.BENEFIT_PLAN AND B_PLAN.EFFDT = ( SELECT MAX(EB_PLAN.EFFDT) FROM PS_BENEF_PLAN_TBL EB_PLAN WHERE EB_PLAN.PLAN_TYPE = B_PLAN.PLAN_TYPE AND EB_PLAN.BENEFIT_PLAN = B_PLAN.BENEFIT_PLAN AND EB_PLAN.EFFDT <= %CurrentDateIn)) , RPT_TIME.TL_QUANTITY, RPT_TIME.PROJECT_ID FROM PS_TL_RPTD_TIME RPT_TIME WHERE RPT_TIME.TRC IN ( SELECT trc.TRC FROM PS_TL_TRC_TBL trc WHERE trc.comp_leav_ind = 'LTKN')

  • Related Language Record: TL_LEAVCMP_LGVW

    • # 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 DUR Date(10) DATE Date Under Report associates time being reported, scheduled or paid, to a specific date.
    4 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
    5 TRC Character(5) VARCHAR2(5) NOT NULL Time Reporting Code used to report time in Time and Labor
    6 COMP_LEAV_IND Character(4) VARCHAR2(4) NOT NULL Comp Leave Indiactor indicates whether a TRC has any effect on Leave and Compensatory time Off
    CERN=Comp Time Earned
    CTKN=Comp Time Taken
    LTKN=Leave Taken
    NO=No Comp Time Effect
    RULE=Avoid Replacing by Rules
    7 COMP_TIME_PLAN Character(10) VARCHAR2(10) NOT NULL Compensatory Time Off Plan
    8 PLAN_DESCR Character(40) VARCHAR2(40) NOT NULL Plan Description
    9 TL_QUANTITY Signed Number(20,6) DECIMAL(18,6) NOT NULL This field represents the measure of Work in hrs,amount etc.,
    10 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project/Grant