eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HR_ADP_OAB_BENEFIT_V

Source

View Text - Preformatted


SELECT /*+ ORDERED */ ref.company_code_equivalent , hou.name , hs.segment1 , assg.organization_id , per.employee_number , per.person_id , per.business_group_id , assg.assignment_id , assg.assignment_number , decode(assg.primary_flag, 'Y',0,assg.assignment_sequence) , assg.primary_flag , decode(ptp.number_per_fiscal_year, 52,'W',24,'S',26,'B', 12,'M', ptp.number_per_fiscal_year) , bpg.name , bpl.name , bpt.name , pev.screen_entry_value , null , ptd_limit.mx_val , null /* 115.31: Added new function call to remove view mergable issues */ , DECODE(pen.oipl_id, NULL, NULL, hr_pay_interface_pkg.get_coverage_type(pen.oipl_id)) , bprv1.rt_strt_dt , decode(to_date('31-12-4712','DD-MM-YYYY'), trunc(bprv2.rt_end_dt), bprv2.rt_end_dt, bprv2.rt_end_dt+1) , greatest(nvl(per.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), per.effective_start_date) , greatest(nvl(assg.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), assg.effective_start_date) , greatest( decode( greatest( trunc(hr_adp.get_adp_extract_date), nvl(bprv2.rt_end_dt, to_date('1900/01/01','YYYY/MM/DD'))), trunc(hr_adp.get_adp_extract_date), greatest(nvl(bprv2.rt_end_dt, to_date('1900/01/01','YYYY/MM/DD')), nvl(bprv.last_update_date, to_date('1900/01/01','YYYY/MM/DD'))), nvl(bprv.last_update_date, to_date('1900/01/01','YYYY/MM/DD'))), nvl(bprv1.rt_strt_dt, to_date('1900/01/01','YYYY/MM/DD'))) , greatest(nvl(ptp.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), to_date('1900/01/01','YYYY/MM/DD')) , greatest( decode( greatest( trunc(hr_adp.get_adp_extract_date), nvl(bprv2.rt_end_dt, to_date('1900/01/01','YYYY/MM/DD'))), trunc(hr_adp.get_adp_extract_date), greatest(nvl(bprv2.rt_end_dt, to_date('1900/01/01','YYYY/MM/DD')), nvl(bprv.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(per.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), per.effective_start_date, nvl(assg.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), assg.effective_start_date, nvl(pen.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(ptp.last_update_date, to_date('1900/01/01','YYYY/MM/DD'))), greatest(nvl(bprv.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(per.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), per.effective_start_date, nvl(assg.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), assg.effective_start_date, nvl(pen.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), nvl(ptp.last_update_date, to_date('1900/01/01','YYYY/MM/DD')))), nvl(bprv1.rt_strt_dt, to_date('1900/01/01','YYYY/MM/DD'))) FROM /* 115.31: modified from order */ ben_prtt_rt_val bprv , ben_prtt_enrt_rslt_f pen , pay_element_entry_values_f pev , pay_element_entries_f pee , hr_adp_emp_ref_v ref , per_all_assignments_f assg , per_time_period_types ptp , ben_pgm_f bpg , ben_pl_f bpl , ben_pl_typ_f bpt , per_all_people_f per , hr_soft_coding_keyflex hs , hr_all_organization_units hou , ben_prtt_rt_val bprv1 , ben_prtt_rt_val bprv2 , (SELECT bpl.mx_val mx_val, bprv.prtt_rt_val_id prtt_rt_val_id FROM ben_ptd_lmt_f bpl , ben_acty_rt_ptd_lmt_f barpl , ben_acty_base_rt_f babr , ben_prtt_rt_val bprv WHERE bprv.acty_base_rt_id = babr.acty_base_rt_id AND bprv.prtt_rt_val_stat_cd IS NULL AND babr.acty_base_rt_id = barpl.acty_base_rt_id AND barpl.ptd_lmt_id = bpl.ptd_lmt_id AND bpl.effective_start_date = (SELECT MAX(bpl2.effective_start_date) FROM ben_ptd_lmt_f bpl2 WHERE bpl.ptd_lmt_id = bpl2.ptd_lmt_id AND bpl2.effective_start_date <= hr_adp.get_adp_extract_date) AND babr.effective_start_date = (SELECT MAX(babr2.effective_start_date) FROM ben_acty_base_rt_f babr2 WHERE babr.acty_base_rt_id = babr2.acty_base_rt_id AND babr2.effective_start_date <= hr_adp.get_adp_extract_date) AND barpl.effective_start_date = (SELECT MAX(barpl2.effective_start_date) FROM ben_acty_rt_ptd_lmt_f barpl2 WHERE barpl.acty_rt_ptd_lmt_id = barpl2.acty_rt_ptd_lmt_id AND barpl2.effective_start_date <= hr_adp.get_adp_extract_date)) ptd_limit /* 115.31: removed the following for view merging issues on hr_pay_interface_oab_option_v pbo */ WHERE REF.PERIOD_TYPE = PTP.PERIOD_TYPE AND REF.PRIMARY_FLAG = 'Y' AND PER.PERSON_ID = assg.PERSON_ID AND assg.ASSIGNMENT_ID = REF.ASSIGNMENT_ID AND assg.rowid = REF.asg_rowid AND HS.SOFT_CODING_KEYFLEX_ID = assg.SOFT_CODING_KEYFLEX_ID AND HS.SEGMENT1 = HOU.ORGANIZATION_ID and bprv.element_entry_value_id = pev.element_entry_value_id AND hr_adp.get_adp_extract_date >= bprv.rt_strt_dt AND bprv.acty_typ_cd = 'EEPYC' AND bprv1.acty_typ_cd = 'EEPYC' AND bprv2.acty_typ_cd = 'EEPYC' AND bprv.prtt_rt_val_stat_cd IS NULL AND bprv1.prtt_rt_val_stat_cd IS NULL AND bprv2.prtt_rt_val_stat_cd IS NULL AND bprv.prtt_enrt_rslt_id = bprv1.prtt_enrt_rslt_id AND bprv.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id AND bprv1.prtt_enrt_rslt_id = bprv2.prtt_enrt_rslt_id AND pev.element_entry_id = pee.element_entry_id AND pee.assignment_id = assg.assignment_id AND pee.effective_start_date = pev.effective_start_date AND pev.effective_end_date = pee.effective_end_date AND ptd_limit.prtt_rt_val_id (+)= bprv.prtt_rt_val_id AND pen.effective_start_date <= hr_adp.get_adp_extract_date AND pen.prtt_enrt_rslt_stat_cd IS NULL AND pen.pgm_id = bpg.pgm_id(+) AND pen.pl_id = bpl.pl_id AND bpl.pl_typ_id = bpt.pl_typ_id /* 115.31: removed for view merging issues AND pen.oipl_id = pbo.oipl_id (+) */ AND pen.person_id = per.person_id AND bprv.prtt_enrt_rslt_id IS NOT NULL /* 115.31: this sub-query isnt required because it is a DT entity the restriction can just be a between AND pen.effective_start_date = (SELECT MAX(pen1.effective_start_date) FROM ben_prtt_enrt_rslt_f pen1 WHERE pen.prtt_enrt_rslt_id = pen1.prtt_enrt_rslt_id AND pen.prtt_enrt_rslt_stat_cd IS NULL AND hr_adp.get_adp_extract_date BETWEEN pen1.effective_start_date and pen1.effective_end_date) */ /* 115.31: here is the replacement for the above sub-query */ AND hr_adp.get_adp_extract_date BETWEEN pen.effective_start_date and pen.effective_end_date /* end */ AND bprv.rt_strt_dt = (SELECT MAX(bprvo.rt_strt_dt) FROM ben_prtt_rt_val bprvo WHERE bprvo.prtt_enrt_rslt_id = bprv.prtt_enrt_rslt_id AND bprvo.prtt_rt_val_stat_cd IS NULL AND bprvo.acty_typ_cd = bprv.acty_typ_cd AND bprvo.rt_strt_dt <= hr_adp.get_adp_extract_date) AND bprv1.rt_strt_dt = (SELECT MIN(bprvs.rt_strt_dt) FROM ben_prtt_rt_val bprvs WHERE bprvs.prtt_enrt_rslt_id = bprv.prtt_enrt_rslt_id AND bprvs.prtt_rt_val_stat_cd IS NULL AND bprvs.acty_typ_cd in ('EEPYC') AND bprvs.rt_strt_dt <= hr_adp.get_adp_extract_date) AND bprv2.rt_end_dt = (SELECT MAX(bprve.rt_end_dt) FROM ben_prtt_rt_val bprve WHERE bprve.prtt_enrt_rslt_id = bprv.prtt_enrt_rslt_id AND bprve.prtt_rt_val_stat_cd IS NULL AND bprve.acty_typ_cd in ('EEPYC')) AND bpl.effective_start_date = (SELECT MAX(bpl2.effective_start_date) FROM ben_pl_f bpl2 WHERE bpl.pl_id = bpl2.pl_id AND bpl2.effective_start_date <= hr_adp.get_adp_extract_date) AND (bpg.pgm_id IS NULL OR bpg.effective_start_date = (SELECT MAX(bpg1.effective_start_date) FROM ben_pgm_f bpg1 WHERE bpg1.pgm_id = bpg.pgm_id AND bpg1.effective_start_date <= hr_adp.get_adp_extract_date)) AND bpt.effective_start_date = (SELECT MAX(bpt2.effective_start_date) FROM ben_pl_typ_f bpt2 WHERE bpt.pl_typ_id = bpt2.pl_typ_id AND bpt2.effective_start_date <= hr_adp.get_adp_extract_date) AND per.effective_start_date = (SELECT MAX(per2.effective_start_date) FROM per_all_people_f per2 WHERE per2.person_id = per.person_id AND per2.effective_start_date <= hr_adp.get_adp_extract_date) /* 115.31: this sub-query restriction is taken care of by REF and therefore not required AND assg.effective_start_date = (SELECT MAX(a2.effective_start_date) FROM per_all_assignments_f a2 WHERE a2.assignment_id = assg.assignment_id AND a2.primary_flag = 'Y' AND a2.assignment_type = 'E' AND a2.effective_start_date <= hr_adp.get_adp_extract_date) */ AND pee.effective_start_date = (SELECT max(ee2.effective_start_date) FROM pay_element_entries_f ee2 WHERE ee2.element_entry_id = pee.element_entry_id AND ee2.effective_start_date <= trunc(hr_adp.get_adp_extract_date) AND ((ee2.effective_end_date > trunc(hr_adp.get_adp_extract_date)) or (ee2.effective_end_date >= hr_adp.get_end_deduction_date))) /* 115.31: added the following predicates for improved filtering */ AND ref.assignment_id = pee.assignment_id AND ref.asg_rowid = assg.rowid AND pev.effective_start_date <= trunc(hr_adp.get_adp_extract_date)

View Text - HTML Formatted

SELECT /*+ ORDERED */ REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, ASSG.ORGANIZATION_ID
, PER.EMPLOYEE_NUMBER
, PER.PERSON_ID
, PER.BUSINESS_GROUP_ID
, ASSG.ASSIGNMENT_ID
, ASSG.ASSIGNMENT_NUMBER
, DECODE(ASSG.PRIMARY_FLAG
, 'Y'
, 0
, ASSG.ASSIGNMENT_SEQUENCE)
, ASSG.PRIMARY_FLAG
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, BPG.NAME
, BPL.NAME
, BPT.NAME
, PEV.SCREEN_ENTRY_VALUE
, NULL
, PTD_LIMIT.MX_VAL
, NULL /* 115.31: ADDED NEW FUNCTION CALL TO REMOVE VIEW MERGABLE ISSUES */
, DECODE(PEN.OIPL_ID
, NULL
, NULL
, HR_PAY_INTERFACE_PKG.GET_COVERAGE_TYPE(PEN.OIPL_ID))
, BPRV1.RT_STRT_DT
, DECODE(TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, TRUNC(BPRV2.RT_END_DT)
, BPRV2.RT_END_DT
, BPRV2.RT_END_DT+1)
, GREATEST(NVL(PER.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PER.EFFECTIVE_START_DATE)
, GREATEST(NVL(ASSG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASSG.EFFECTIVE_START_DATE)
, GREATEST( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(BPRV2.RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL(BPRV2.RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(BPRV.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(BPRV.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(BPRV1.RT_STRT_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, GREATEST( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(BPRV2.RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST(NVL(BPRV2.RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(BPRV.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PER.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PER.EFFECTIVE_START_DATE
, NVL(ASSG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASSG.EFFECTIVE_START_DATE
, NVL(PEN.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(BPRV.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PER.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PER.EFFECTIVE_START_DATE
, NVL(ASSG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASSG.EFFECTIVE_START_DATE
, NVL(PEN.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, NVL(BPRV1.RT_STRT_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
FROM /* 115.31: MODIFIED
FROM ORDER */ BEN_PRTT_RT_VAL BPRV
, BEN_PRTT_ENRT_RSLT_F PEN
, PAY_ELEMENT_ENTRY_VALUES_F PEV
, PAY_ELEMENT_ENTRIES_F PEE
, HR_ADP_EMP_REF_V REF
, PER_ALL_ASSIGNMENTS_F ASSG
, PER_TIME_PERIOD_TYPES PTP
, BEN_PGM_F BPG
, BEN_PL_F BPL
, BEN_PL_TYP_F BPT
, PER_ALL_PEOPLE_F PER
, HR_SOFT_CODING_KEYFLEX HS
, HR_ALL_ORGANIZATION_UNITS HOU
, BEN_PRTT_RT_VAL BPRV1
, BEN_PRTT_RT_VAL BPRV2
, (SELECT BPL.MX_VAL MX_VAL
, BPRV.PRTT_RT_VAL_ID PRTT_RT_VAL_ID
FROM BEN_PTD_LMT_F BPL
, BEN_ACTY_RT_PTD_LMT_F BARPL
, BEN_ACTY_BASE_RT_F BABR
, BEN_PRTT_RT_VAL BPRV
WHERE BPRV.ACTY_BASE_RT_ID = BABR.ACTY_BASE_RT_ID
AND BPRV.PRTT_RT_VAL_STAT_CD IS NULL
AND BABR.ACTY_BASE_RT_ID = BARPL.ACTY_BASE_RT_ID
AND BARPL.PTD_LMT_ID = BPL.PTD_LMT_ID
AND BPL.EFFECTIVE_START_DATE = (SELECT MAX(BPL2.EFFECTIVE_START_DATE)
FROM BEN_PTD_LMT_F BPL2
WHERE BPL.PTD_LMT_ID = BPL2.PTD_LMT_ID
AND BPL2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE)
AND BABR.EFFECTIVE_START_DATE = (SELECT MAX(BABR2.EFFECTIVE_START_DATE)
FROM BEN_ACTY_BASE_RT_F BABR2
WHERE BABR.ACTY_BASE_RT_ID = BABR2.ACTY_BASE_RT_ID
AND BABR2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE)
AND BARPL.EFFECTIVE_START_DATE = (SELECT MAX(BARPL2.EFFECTIVE_START_DATE)
FROM BEN_ACTY_RT_PTD_LMT_F BARPL2
WHERE BARPL.ACTY_RT_PTD_LMT_ID = BARPL2.ACTY_RT_PTD_LMT_ID
AND BARPL2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE)) PTD_LIMIT /* 115.31: REMOVED THE FOLLOWING FOR VIEW MERGING ISSUES ON HR_PAY_INTERFACE_OAB_OPTION_V PBO */
WHERE REF.PERIOD_TYPE = PTP.PERIOD_TYPE
AND REF.PRIMARY_FLAG = 'Y'
AND PER.PERSON_ID = ASSG.PERSON_ID
AND ASSG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND ASSG.ROWID = REF.ASG_ROWID
AND HS.SOFT_CODING_KEYFLEX_ID = ASSG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND BPRV.ELEMENT_ENTRY_VALUE_ID = PEV.ELEMENT_ENTRY_VALUE_ID
AND HR_ADP.GET_ADP_EXTRACT_DATE >= BPRV.RT_STRT_DT
AND BPRV.ACTY_TYP_CD = 'EEPYC'
AND BPRV1.ACTY_TYP_CD = 'EEPYC'
AND BPRV2.ACTY_TYP_CD = 'EEPYC'
AND BPRV.PRTT_RT_VAL_STAT_CD IS NULL
AND BPRV1.PRTT_RT_VAL_STAT_CD IS NULL
AND BPRV2.PRTT_RT_VAL_STAT_CD IS NULL
AND BPRV.PRTT_ENRT_RSLT_ID = BPRV1.PRTT_ENRT_RSLT_ID
AND BPRV.PRTT_ENRT_RSLT_ID = PEN.PRTT_ENRT_RSLT_ID
AND BPRV1.PRTT_ENRT_RSLT_ID = BPRV2.PRTT_ENRT_RSLT_ID
AND PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEE.ASSIGNMENT_ID = ASSG.ASSIGNMENT_ID
AND PEE.EFFECTIVE_START_DATE = PEV.EFFECTIVE_START_DATE
AND PEV.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND PTD_LIMIT.PRTT_RT_VAL_ID (+)= BPRV.PRTT_RT_VAL_ID
AND PEN.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.PGM_ID = BPG.PGM_ID(+)
AND PEN.PL_ID = BPL.PL_ID
AND BPL.PL_TYP_ID = BPT.PL_TYP_ID /* 115.31: REMOVED FOR VIEW MERGING ISSUES
AND PEN.OIPL_ID = PBO.OIPL_ID (+) */
AND PEN.PERSON_ID = PER.PERSON_ID
AND BPRV.PRTT_ENRT_RSLT_ID IS NOT NULL /* 115.31: THIS SUB-QUERY ISNT REQUIRED BECAUSE IT IS A DT ENTITY THE RESTRICTION CAN JUST BE A BETWEEN
AND PEN.EFFECTIVE_START_DATE = (SELECT MAX(PEN1.EFFECTIVE_START_DATE)
FROM BEN_PRTT_ENRT_RSLT_F PEN1
WHERE PEN.PRTT_ENRT_RSLT_ID = PEN1.PRTT_ENRT_RSLT_ID
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PEN1.EFFECTIVE_START_DATE
AND PEN1.EFFECTIVE_END_DATE) */ /* 115.31: HERE IS THE REPLACEMENT FOR THE ABOVE SUB-QUERY */
AND HR_ADP.GET_ADP_EXTRACT_DATE BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE /* END */
AND BPRV.RT_STRT_DT = (SELECT MAX(BPRVO.RT_STRT_DT)
FROM BEN_PRTT_RT_VAL BPRVO
WHERE BPRVO.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVO.PRTT_RT_VAL_STAT_CD IS NULL
AND BPRVO.ACTY_TYP_CD = BPRV.ACTY_TYP_CD
AND BPRVO.RT_STRT_DT <= HR_ADP.GET_ADP_EXTRACT_DATE)
AND BPRV1.RT_STRT_DT = (SELECT MIN(BPRVS.RT_STRT_DT)
FROM BEN_PRTT_RT_VAL BPRVS
WHERE BPRVS.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVS.PRTT_RT_VAL_STAT_CD IS NULL
AND BPRVS.ACTY_TYP_CD IN ('EEPYC')
AND BPRVS.RT_STRT_DT <= HR_ADP.GET_ADP_EXTRACT_DATE)
AND BPRV2.RT_END_DT = (SELECT MAX(BPRVE.RT_END_DT)
FROM BEN_PRTT_RT_VAL BPRVE
WHERE BPRVE.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVE.PRTT_RT_VAL_STAT_CD IS NULL
AND BPRVE.ACTY_TYP_CD IN ('EEPYC'))
AND BPL.EFFECTIVE_START_DATE = (SELECT MAX(BPL2.EFFECTIVE_START_DATE)
FROM BEN_PL_F BPL2
WHERE BPL.PL_ID = BPL2.PL_ID
AND BPL2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE)
AND (BPG.PGM_ID IS NULL OR BPG.EFFECTIVE_START_DATE = (SELECT MAX(BPG1.EFFECTIVE_START_DATE)
FROM BEN_PGM_F BPG1
WHERE BPG1.PGM_ID = BPG.PGM_ID
AND BPG1.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE))
AND BPT.EFFECTIVE_START_DATE = (SELECT MAX(BPT2.EFFECTIVE_START_DATE)
FROM BEN_PL_TYP_F BPT2
WHERE BPT.PL_TYP_ID = BPT2.PL_TYP_ID
AND BPT2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE)
AND PER.EFFECTIVE_START_DATE = (SELECT MAX(PER2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PER2
WHERE PER2.PERSON_ID = PER.PERSON_ID
AND PER2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE) /* 115.31: THIS SUB-QUERY RESTRICTION IS TAKEN CARE OF BY REF
AND THEREFORE NOT REQUIRED
AND ASSG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A2.ASSIGNMENT_ID = ASSG.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y'
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.EFFECTIVE_START_DATE <= HR_ADP.GET_ADP_EXTRACT_DATE) */
AND PEE.EFFECTIVE_START_DATE = (SELECT MAX(EE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F EE2
WHERE EE2.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND EE2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND ((EE2.EFFECTIVE_END_DATE > TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)) OR (EE2.EFFECTIVE_END_DATE >= HR_ADP.GET_END_DEDUCTION_DATE))) /* 115.31: ADDED THE FOLLOWING PREDICATES FOR IMPROVED FILTERING */
AND REF.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
AND REF.ASG_ROWID = ASSG.ROWID
AND PEV.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)