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_CERIDIAN_OAB_BENEFIT_V

Source

View Text - Preformatted


SELECT per.employee_number , hou.name , bpg.name , bpt.name , bpl.name , pbo.coverage_type , pbv1.contribution_amount , pbv1.max_annual_amount , decode(pbv1.max_rt_end_dt, greatest(pbv1.max_rt_end_dt, trunc(hr_ceridian.get_cer_extract_date)), 'Y', 'N') , per.business_group_id , per.person_id , 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_ceridian.get_cer_extract_date), nvl(pbv1.max_rt_end_dt, to_date('1900/01/01','YYYY/MM/DD'))), trunc(hr_ceridian.get_cer_extract_date), greatest(nvl(pbv1.max_rt_end_dt, to_date('1900/01/01','YYYY/MM/DD')), nvl(pbv1.oabv_last_update_date, to_date('1900/01/01','YYYY/MM/DD'))), nvl(pbv1.oabv_last_update_date, to_date('1900/01/01','YYYY/MM/DD'))), nvl(pbv1.min_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_ceridian.get_cer_extract_date), nvl(pbv1.max_rt_end_dt, to_date('1900/01/01','YYYY/MM/DD'))), trunc(hr_ceridian.get_cer_extract_date), greatest(nvl(pbv1.max_rt_end_dt, to_date('1900/01/01','YYYY/MM/DD')), nvl(pbv1.oabv_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')), pen.effective_start_date, nvl(ptp.last_update_date, to_date('1900/01/01','YYYY/MM/DD'))), greatest(nvl(pbv1.oabv_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')), pen.effective_start_date, nvl(ptp.last_update_date, to_date('1900/01/01','YYYY/MM/DD')))), nvl(pbv1.min_rt_strt_dt, to_date('1900/01/01','YYYY/MM/DD' ))) FROM ben_prtt_enrt_rslt_f pen , ben_pgm_f bpg , ben_pl_f bpl , ben_pl_typ_f bpt , hr_pay_interface_oab_option_v pbo , hr_pay_interface_oab_value_v pbv1 , per_all_people_f per , per_all_assignments_f assg , hr_soft_coding_keyflex hs , hr_all_organization_units hou , pay_payrolls_x prl , per_time_period_types ptp WHERE pen.effective_start_date <= hr_ceridian.get_cer_extract_date AND pen.prtt_enrt_rslt_stat_cd IS NULL AND pen.pl_id = bpl.pl_id AND bpl.pl_typ_id = bpt.pl_typ_id AND pen.pgm_id = bpg.pgm_id (+) AND pen.oipl_id = pbo.oipl_id (+) AND pen.prtt_enrt_rslt_id = pbv1.prtt_enrt_rslt_id(+) AND pbv1.rt_strt_dt(+) BETWEEN pen.effective_start_date AND pen.effective_end_date AND NVL(pbv1.acty_typ_cd(+),'EEPYC') = 'EEPYC' AND pen.person_id = per.person_id AND per.person_id = assg.person_id AND assg.primary_flag = 'Y' AND assg.assignment_type = 'E' AND hs.soft_coding_keyflex_id = assg.soft_coding_keyflex_id AND hs.segment1 = hou.organization_id AND prl.payroll_id = assg.payroll_id AND prl.period_type = ptp.period_type AND pbv1.prtt_enrt_rslt_id IS NOT NULL 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_ceridian.get_cer_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_ceridian.get_cer_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_ceridian.get_cer_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_ceridian.get_cer_extract_date ) AND assg.effective_start_date = (SELECT MAX(a2.effective_start_date) FROM per_all_assignments_f a2 WHERE a2.person_id = assg.person_id AND a2.primary_flag = 'Y' AND a2.assignment_type = 'E' AND a2.effective_start_date <= hr_ceridian.get_cer_extract_date )

View Text - HTML Formatted

SELECT PER.EMPLOYEE_NUMBER
, HOU.NAME
, BPG.NAME
, BPT.NAME
, BPL.NAME
, PBO.COVERAGE_TYPE
, PBV1.CONTRIBUTION_AMOUNT
, PBV1.MAX_ANNUAL_AMOUNT
, DECODE(PBV1.MAX_RT_END_DT
, GREATEST(PBV1.MAX_RT_END_DT
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE))
, 'Y'
, 'N')
, PER.BUSINESS_GROUP_ID
, PER.PERSON_ID
, 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_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PBV1.MAX_RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PBV1.MAX_RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PBV1.OABV_LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PBV1.OABV_LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(PBV1.MIN_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_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PBV1.MAX_RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PBV1.MAX_RT_END_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PBV1.OABV_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'))
, PEN.EFFECTIVE_START_DATE
, NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST(NVL(PBV1.OABV_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'))
, PEN.EFFECTIVE_START_DATE
, NVL(PTP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, NVL(PBV1.MIN_RT_STRT_DT
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD' )))
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PGM_F BPG
, BEN_PL_F BPL
, BEN_PL_TYP_F BPT
, HR_PAY_INTERFACE_OAB_OPTION_V PBO
, HR_PAY_INTERFACE_OAB_VALUE_V PBV1
, PER_ALL_PEOPLE_F PER
, PER_ALL_ASSIGNMENTS_F ASSG
, HR_SOFT_CODING_KEYFLEX HS
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_PAYROLLS_X PRL
, PER_TIME_PERIOD_TYPES PTP
WHERE PEN.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.PL_ID = BPL.PL_ID
AND BPL.PL_TYP_ID = BPT.PL_TYP_ID
AND PEN.PGM_ID = BPG.PGM_ID (+)
AND PEN.OIPL_ID = PBO.OIPL_ID (+)
AND PEN.PRTT_ENRT_RSLT_ID = PBV1.PRTT_ENRT_RSLT_ID(+)
AND PBV1.RT_STRT_DT(+) BETWEEN PEN.EFFECTIVE_START_DATE
AND PEN.EFFECTIVE_END_DATE
AND NVL(PBV1.ACTY_TYP_CD(+)
, 'EEPYC') = 'EEPYC'
AND PEN.PERSON_ID = PER.PERSON_ID
AND PER.PERSON_ID = ASSG.PERSON_ID
AND ASSG.PRIMARY_FLAG = 'Y'
AND ASSG.ASSIGNMENT_TYPE = 'E'
AND HS.SOFT_CODING_KEYFLEX_ID = ASSG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PRL.PAYROLL_ID = ASSG.PAYROLL_ID
AND PRL.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PBV1.PRTT_ENRT_RSLT_ID IS NOT NULL
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_CERIDIAN.GET_CER_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_CERIDIAN.GET_CER_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_CERIDIAN.GET_CER_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_CERIDIAN.GET_CER_EXTRACT_DATE )
AND ASSG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A2.PERSON_ID = ASSG.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE )