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_PAY_INTERFACE_OAB_VALUE1_V

Source

View Text - Preformatted


SELECT DISTINCT pev.screen_entry_value, bprv.acty_typ_cd, assg.assignment_id, assg.assignment_number, assg.effective_start_date, assg.effective_end_date, bprv.rt_strt_dt, bprv.rt_end_dt, bprv1.rt_strt_dt, bprv2.rt_end_dt, bprv.rt_val, bprv.acty_ref_perd_cd, ptd_limit.mx_val, pee.effective_start_date, pee.effective_end_date, bprv.prtt_enrt_rslt_id, bprv.prtt_rt_val_id, pev.element_entry_value_id, assg.person_id, bprv.per_in_ler_id, bprv.rt_typ_cd, bprv.last_update_date FROM per_all_assignments_f assg, pay_element_entries_f pee, (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 <= TRUNC (hr_pay_interface_pkg.get_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 <= TRUNC (hr_pay_interface_pkg.get_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 <= TRUNC (hr_pay_interface_pkg.get_extract_date))) ptd_limit, pay_element_entry_values_f pev, ben_prtt_rt_val bprv, ben_prtt_rt_val bprv1, ben_prtt_rt_val bprv2 WHERE bprv.element_entry_value_id = pev.element_entry_value_id AND TRUNC (hr_pay_interface_pkg.get_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 bprv1.prtt_enrt_rslt_id = bprv2.prtt_enrt_rslt_id AND bprv.rt_strt_dt = (SELECT MAX (bprvo.rt_strt_dt) FROM ben_prtt_rt_val bprvo WHERE bprvo.prtt_rt_val_stat_cd is null AND bprvo.prtt_enrt_rslt_id = bprv.prtt_enrt_rslt_id AND bprvo.acty_typ_cd = bprv.acty_typ_cd AND bprvo.rt_strt_dt <= TRUNC (hr_pay_interface_pkg.get_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.acty_typ_cd = bprv.acty_typ_cd AND bprvs.prtt_rt_val_stat_cd is null AND bprvs.rt_strt_dt <= TRUNC (hr_pay_interface_pkg.get_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.rt_strt_dt <= TRUNC (hr_pay_interface_pkg.get_extract_date) AND bprve.acty_typ_cd = bprv.acty_typ_cd) AND pev.element_entry_id = pee.element_entry_id AND pev.effective_start_date = pee.effective_start_date AND pev.effective_end_date = pee.effective_end_date AND pee.assignment_id = assg.assignment_id AND assg.primary_flag = 'Y' AND assg.assignment_type = 'E' AND ptd_limit.prtt_rt_val_id(+) = bprv.prtt_rt_val_id 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.effective_start_date <= TRUNC (hr_pay_interface_pkg.get_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_pay_interface_pkg.get_extract_date))

View Text - HTML Formatted

SELECT DISTINCT PEV.SCREEN_ENTRY_VALUE
, BPRV.ACTY_TYP_CD
, ASSG.ASSIGNMENT_ID
, ASSG.ASSIGNMENT_NUMBER
, ASSG.EFFECTIVE_START_DATE
, ASSG.EFFECTIVE_END_DATE
, BPRV.RT_STRT_DT
, BPRV.RT_END_DT
, BPRV1.RT_STRT_DT
, BPRV2.RT_END_DT
, BPRV.RT_VAL
, BPRV.ACTY_REF_PERD_CD
, PTD_LIMIT.MX_VAL
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE
, BPRV.PRTT_ENRT_RSLT_ID
, BPRV.PRTT_RT_VAL_ID
, PEV.ELEMENT_ENTRY_VALUE_ID
, ASSG.PERSON_ID
, BPRV.PER_IN_LER_ID
, BPRV.RT_TYP_CD
, BPRV.LAST_UPDATE_DATE
FROM PER_ALL_ASSIGNMENTS_F ASSG
, PAY_ELEMENT_ENTRIES_F PEE
, (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 <= TRUNC (HR_PAY_INTERFACE_PKG.GET_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 <= TRUNC (HR_PAY_INTERFACE_PKG.GET_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 <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))) PTD_LIMIT
, PAY_ELEMENT_ENTRY_VALUES_F PEV
, BEN_PRTT_RT_VAL BPRV
, BEN_PRTT_RT_VAL BPRV1
, BEN_PRTT_RT_VAL BPRV2
WHERE BPRV.ELEMENT_ENTRY_VALUE_ID = PEV.ELEMENT_ENTRY_VALUE_ID
AND TRUNC (HR_PAY_INTERFACE_PKG.GET_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 BPRV1.PRTT_ENRT_RSLT_ID = BPRV2.PRTT_ENRT_RSLT_ID
AND BPRV.RT_STRT_DT = (SELECT MAX (BPRVO.RT_STRT_DT)
FROM BEN_PRTT_RT_VAL BPRVO
WHERE BPRVO.PRTT_RT_VAL_STAT_CD IS NULL
AND BPRVO.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVO.ACTY_TYP_CD = BPRV.ACTY_TYP_CD
AND BPRVO.RT_STRT_DT <= TRUNC (HR_PAY_INTERFACE_PKG.GET_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.ACTY_TYP_CD = BPRV.ACTY_TYP_CD
AND BPRVS.PRTT_RT_VAL_STAT_CD IS NULL
AND BPRVS.RT_STRT_DT <= TRUNC (HR_PAY_INTERFACE_PKG.GET_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.RT_STRT_DT <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE)
AND BPRVE.ACTY_TYP_CD = BPRV.ACTY_TYP_CD)
AND PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEV.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE
AND PEV.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND PEE.ASSIGNMENT_ID = ASSG.ASSIGNMENT_ID
AND ASSG.PRIMARY_FLAG = 'Y'
AND ASSG.ASSIGNMENT_TYPE = 'E'
AND PTD_LIMIT.PRTT_RT_VAL_ID(+) = BPRV.PRTT_RT_VAL_ID
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.EFFECTIVE_START_DATE <= TRUNC (HR_PAY_INTERFACE_PKG.GET_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_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))