TL_LEAVCMP_LGVW

(SQL View)
 Index Back

Leave Comp Lang View

Leave Comp Lang View

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 , L.LANGUAGE_CD , ( SELECT COMP.DESCR FROM PS_TL_COMP_TIME_LG COMP WHERE COMP.COMP_TIME_PLAN = COMPLEAV.COMP_TIME_PLAN AND COMP.LANGUAGE_CD = L.LANGUAGE_CD AND COMP.EFFDT = ( SELECT MAX(ECOMP.EFFDT) FROM PS_TL_COMP_TIME_LG ECOMP WHERE ECOMP.COMP_TIME_PLAN = COMP.COMP_TIME_PLAN AND COMP.LANGUAGE_CD = ECOMP.LANGUAGE_CD AND ECOMP.EFFDT <= %CurrentDateIn)) FROM PS_TL_COMPLEAV_TBL COMPLEAV, PS_TL_RPTD_TIME RPT_TIME ,PSLANGUAGES L 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') AND L.LANGUAGE_CD <> ( SELECT LANGUAGE_CD FROM PSOPTIONS) 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)) ,T.LANGUAGE_CD, ( SELECT L_PLAN.DESCR FROM PS_BENEF_PLAN_LANG L_PLAN WHERE L_PLAN.LANGUAGE_CD = T.LANGUAGE_CD AND L_PLAN.PLAN_TYPE = ( SELECT B_PLAN.PLAN_TYPE FROM PS_leave_plan Leav , PS_BENEF_PLAN_TBL B_PLAN WHERE L_PLAN.BENEFIT_PLAN = B_PLAN.BENEFIT_PLAN AND L_PLAN.EFFDT = B_PLAN.EFFDT AND 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))) FROM PS_TL_RPTD_TIME RPT_TIME, PSLANGUAGES T WHERE RPT_TIME.TRC IN ( SELECT trc.TRC FROM PS_TL_TRC_TBL trc WHERE trc.comp_leav_ind = 'LTKN') AND T.LANGUAGE_CD <> ( SELECT LANGUAGE_CD FROM PSOPTIONS)

  • Related Language Record for TL_LEAV_CMP_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 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    9 PLAN_DESCR Character(40) VARCHAR2(40) NOT NULL Plan Description