TL_CALPYESEL_VW

(SQL View)
 Index Back

Calendar Details of Absence

This view brings in the calendar groups along with other details related to Absence calendars

SELECT CGRP.CAL_RUN_ID , CAL.CAL_ID , CAL.GP_PAYGROUP , CAL.CAL_PRD_ID , PRD.PRD_BGN_DT , PRD.PRD_END_DT , CGRP.RUN_OPEN_IND , CGRP.RUN_FINALIZED_IND , ENT.PAY_ENTITY , ENT.COUNTRY , PAYEE.GP_CALC_BGN_DT , CASE WHEN PAYEE.USE_PE_RTO_LIM_IND='Y' THEN CASE WHEN ENT.RTO_LIM_BK_OPTN='M' THEN %Sql(FUNCLIB_HR_DATE_MONTH_ADD,PRD.PRD_BGN_DT, (-1 * ENT.RTO_LIM_BK_MONTHS)) WHEN ENT.RTO_LIM_BK_OPTN='Y' THEN CASE WHEN ENT.RTO_LIM_BK_MTH <> ' ' THEN %Sql(FUNCLIB_HR_DATE_YEAR_ADD,%SQL(FUNCLIB_HR_DATE_BUILD,%SQL(FUNCLIB_HR_DATE_YEAR,PRD.PRD_BGN_DT),ENT.RTO_LIM_BK_MTH,ENT.RTO_LIM_BK_DAY),(-1 * ENT.RTO_LIM_BK_YEARS)) ELSE %Sql(FUNCLIB_HR_DATE_YEAR_ADD,PRD.PRD_BGN_DT,(-1 * ENT.RTO_LIM_BK_YEARS)) END END ELSE CASE WHEN PAYEE.RTO_LIM_BK_OPTN='M' THEN %Sql(FUNCLIB_HR_DATE_MONTH_ADD,PRD.PRD_BGN_DT, (-1 * PAYEE.RTO_LIM_BK_MONTHS)) WHEN PAYEE.RTO_LIM_BK_OPTN='Y' THEN CASE WHEN PAYEE.RTO_LIM_BK_MTH <> ' ' THEN %Sql(FUNCLIB_HR_DATE_YEAR_ADD,%SQL(FUNCLIB_HR_DATE_BUILD,%SQL(FUNCLIB_HR_DATE_YEAR,PRD.PRD_BGN_DT),PAYEE.RTO_LIM_BK_MTH,PAYEE.RTO_LIM_BK_DAY),(-1 * PAYEE.RTO_LIM_BK_YEARS)) ELSE %Sql(FUNCLIB_HR_DATE_YEAR_ADD,PRD.PRD_BGN_DT,(-1 * PAYEE.RTO_LIM_BK_YEARS)) END END END , CGRP.OFF_CYCLE , JOB.EMPLID , JOB.EMPL_RCD , CGRP.PROCESS_STRM_IND , STR.STRM_NUM FROM PS_GP_CALENDAR CAL , PS_GP_RUN_TYPE RT , PS_GP_CAL_PRD PRD , PS_GP_CAL_RUN_DTL DTL , PS_GP_CAL_RUN CGRP , PS_GP_PYGRP PGP , PS_GP_PYENT ENT , PS_TL_ABS_STRM_VW STR , PS_JOB JOB LEFT OUTER JOIN PS_GP_PAYEE_DATA PAYEE ON JOB.EMPLID = PAYEE.EMPLID WHERE JOB.ABSENCE_SYSTEM_CD='AM' AND JOB.PAY_SYSTEM_FLG IN ('NA','PI') AND CAL.RUN_TYPE = RT.RUN_TYPE AND RT.CALC_TYPE = 'A' AND PRD.CAL_PRD_ID = CAL.CAL_PRD_ID AND CAL.CAL_ID = DTL.CAL_ID AND CAL.GP_PAYGROUP = DTL.GP_PAYGROUP AND CGRP.CAL_RUN_ID = DTL.CAL_RUN_ID AND CGRP.RUN_FINALIZED_IND='N' AND CGRP.USE_AS_TMPLT_IND = 'N' AND PGP.GP_PAYGROUP = CAL.GP_PAYGROUP AND PGP.PAY_ENTITY = ENT.PAY_ENTITY AND JOB.GP_PAYGROUP = DTL.GP_PAYGROUP AND ((CGRP.PROCESS_STRM_IND = 'Y' AND STR.PROCESS_STRM_IND = 'Y' AND JOB.EMPLID BETWEEN STR.EMPLID_FROM AND STR.EMPLID_TO) OR (CGRP.PROCESS_STRM_IND = 'N' AND STR.PROCESS_STRM_IND = 'N')) AND (JOB.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB.EMPLID = JOB2.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD AND JOB2.EFFDT <= PRD.PRD_BGN_DT) OR JOB.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB.EMPLID = JOB2.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD AND JOB.GP_PAYGROUP = JOB2.GP_PAYGROUP AND JOB2.EFFDT > PRD.PRD_BGN_DT AND JOB2.EFFDT <= PRD.PRD_END_DT)) AND JOB.EFFSEQ = ( SELECT MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = JOB.EMPLID AND JOB3.EMPL_RCD = JOB.EMPL_RCD AND JOB3.EFFDT = JOB.EFFDT) AND JOB.EMPLID=PAYEE.EMPLID AND( EXISTS( SELECT 'X' FROM PS_GP_CAL_RUN CGRP1 , PS_GP_CAL_RUN_DTL DTL1 , PS_GP_CALENDAR CAL1 , PS_GP_CAL_PRD PRD1 WHERE CGRP.CAL_RUN_ID=CGRP1.CAL_RUN_ID AND DTL1.CAL_RUN_ID=CGRP1.CAL_RUN_ID AND DTL1.GP_PAYGROUP=CAL1.GP_PAYGROUP AND DTL1.CAL_ID=CAL1.CAL_ID AND CAL1.CAL_PRD_ID = PRD1.CAL_PRD_ID AND PRD1.PRD_END_DT >= %CurrentDateIn AND PRD1.PRD_BGN_DT <= %CurrentDateIn) OR (CGRP.RUN_FINALIZED_IND='N' AND PRD.PRD_BGN_DT <= %CurrentDateIn)) UNION SELECT CGRP.CAL_RUN_ID , OFF1.OFF_CYCLE_ID , OFF1.GP_PAYGROUP , OFF1.CAL_PRD_ID , PRD.PRD_BGN_DT , PRD.PRD_END_DT , CGRP.RUN_OPEN_IND , CGRP.RUN_FINALIZED_IND , ENT.PAY_ENTITY , ENT.COUNTRY , PAYEE.GP_CALC_BGN_DT , CASE WHEN PAYEE.USE_PE_RTO_LIM_IND='Y' THEN CASE WHEN ENT.RTO_LIM_BK_OPTN='M' THEN %Sql(FUNCLIB_HR_DATE_MONTH_ADD,PRD.PRD_BGN_DT, (-1 * ENT.RTO_LIM_BK_MONTHS)) WHEN ENT.RTO_LIM_BK_OPTN='Y' THEN CASE WHEN ENT.RTO_LIM_BK_MTH <> ' ' THEN %Sql(FUNCLIB_HR_DATE_YEAR_ADD,%SQL(FUNCLIB_HR_DATE_BUILD,%SQL(FUNCLIB_HR_DATE_YEAR,PRD.PRD_BGN_DT),ENT.RTO_LIM_BK_MTH,ENT.RTO_LIM_BK_DAY),(-1 * ENT.RTO_LIM_BK_YEARS)) ELSE %Sql(FUNCLIB_HR_DATE_YEAR_ADD,PRD.PRD_BGN_DT,(-1 * ENT.RTO_LIM_BK_YEARS)) END END ELSE CASE WHEN PAYEE.RTO_LIM_BK_OPTN='M' THEN %Sql(FUNCLIB_HR_DATE_MONTH_ADD,PRD.PRD_BGN_DT, (-1 * PAYEE.RTO_LIM_BK_MONTHS)) WHEN PAYEE.RTO_LIM_BK_OPTN='Y' THEN CASE WHEN PAYEE.RTO_LIM_BK_MTH <> ' ' THEN %Sql(FUNCLIB_HR_DATE_YEAR_ADD,%SQL(FUNCLIB_HR_DATE_BUILD,%SQL(FUNCLIB_HR_DATE_YEAR,PRD.PRD_BGN_DT),PAYEE.RTO_LIM_BK_MTH,PAYEE.RTO_LIM_BK_DAY),(-1 * PAYEE.RTO_LIM_BK_YEARS)) ELSE %Sql(FUNCLIB_HR_DATE_YEAR_ADD,PRD.PRD_BGN_DT,(-1 * PAYEE.RTO_LIM_BK_YEARS)) END END END , CGRP.OFF_CYCLE , PYE.EMPLID , PYE.EMPL_RCD , CGRP.PROCESS_STRM_IND , STR.STRM_NUM FROM PS_GP_CAL_PRD PRD , PS_GP_CAL_RUN_OFF OFF1 , PS_GP_CAL_RUN CGRP , PS_GP_PYGRP PGP , PS_GP_PYENT ENT , PS_GP_PYE_CAL PYE LEFT OUTER JOIN PS_GP_PAYEE_DATA PAYEE ON PYE.EMPLID = PAYEE.EMPLID , PS_JOB JOB , PS_TL_ABS_STRM_VW STR WHERE PRD.CAL_PRD_ID = OFF1.CAL_PRD_ID AND CGRP.CAL_RUN_ID = OFF1.CAL_RUN_ID AND CGRP.off_cycle = 'Y' AND CGRP.RUN_FINALIZED_IND='N' AND PGP.GP_PAYGROUP = OFF1.GP_PAYGROUP AND PGP.PAY_ENTITY = ENT.PAY_ENTITY AND PYE.GP_PAYGROUP = OFF1.GP_PAYGROUP AND PYE.OFF_CYCLE_ID = OFF1.OFF_CYCLE_ID AND PYE.CAL_PRD_ID = OFF1.CAL_PRD_ID AND ((CGRP.PROCESS_STRM_IND = 'Y' AND STR.PROCESS_STRM_IND = 'Y' AND JOB.EMPLID BETWEEN STR.EMPLID_FROM AND STR.EMPLID_TO) OR (CGRP.PROCESS_STRM_IND = 'N' AND STR.PROCESS_STRM_IND = 'N')) AND PYE.EMPLID=JOB.EMPLID AND PYE.EMPL_RCD=JOB.EMPL_RCD AND JOB.EFFDT = ( SELECT MAX(JOB2.EFFDT) FROM PS_JOB JOB2 WHERE JOB.EMPLID = JOB2.EMPLID AND JOB.EMPL_RCD = JOB2.EMPL_RCD AND JOB2.EFFDT <= PRD.PRD_BGN_DT) AND JOB.EFFSEQ = ( SELECT MAX(JOB3.EFFSEQ) FROM PS_JOB JOB3 WHERE JOB3.EMPLID = JOB.EMPLID AND JOB3.EMPL_RCD = JOB.EMPL_RCD AND JOB3.EFFDT = JOB.EFFDT) AND JOB.ABSENCE_SYSTEM_CD='AM' AND JOB.PAY_SYSTEM_FLG IN ('NA','PI')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
2 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id
3 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
4 CAL_PRD_ID Character(10) VARCHAR2(10) NOT NULL Calendar Period ID
5 PRD_BGN_DT Date(10) DATE Period Begin Date
6 PRD_END_DT Date(10) DATE Pay Period End Date
7 RUN_OPEN_IND Character(1) VARCHAR2(1) NOT NULL Y/N Indicates that processing has been initiated for given run (calendar group).
8 RUN_FINALIZED_IND Character(1) VARCHAR2(1) NOT NULL Finalized Run
9 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity
10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
11 GP_CALC_BGN_DT Date(10) DATE Date representing a backward limit for retroactive processing. Assigned at payee level, typically initiated to the first hire date.
12 TL_FROM_DATE Date(10) DATE Reporting Date Range
13 OFF_CYCLE Character(1) VARCHAR2(1) NOT NULL Off Cycle ?
14 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
15 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
16 PROCESS_STRM_IND Character(1) VARCHAR2(1) NOT NULL Process by stream
17 STRM_NUM Number(4,0) SMALLINT NOT NULL Stream Number