VIEW: APPS.HR_CERIDIAN_500_OAB_ER_V

Source

View Text - Preformatted

SELECT per.employee_number , hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'MIN_RT_STRT_DT',pen.effective_start_date,pen.effective_end_date) , decode(TO_DATE('31-12-4712','DD-MM-YYYY'), hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'MAX_RT_END_DT',pen.effective_start_date,pen.effective_end_date), TO_DATE(NULL), hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'MAX_RT_END_DT',pen.effective_start_date,pen.effective_end_date)) effective_end_date , assg.assignment_number , assg.primary_flag , per.national_identifier , hou.name , bpg.name , bpt.name , bpl.name , opt.name option_name , hr_pay_interface_pkg.get_split_erpyc_varchar2(pen.prtt_enrt_rslt_id, 'CONTRIBUTION_AMOUNT',pen.effective_start_date,pen.effective_end_date) er_contribution , hr_pay_interface_pkg.get_split_erpyc_number(pen.prtt_enrt_rslt_id, 'MAX_ANNUAL_AMOUNT',pen.effective_start_date,pen.effective_end_date) er_contribution_limit , assg.assignment_id , per.business_group_id , assg.payroll_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(hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'MAX_RT_END_DT', pen.effective_start_date,pen.effective_end_date), to_date('1900/01/01','YYYY/MM/DD'))), trunc(hr_ceridian.get_cer_extract_date), greatest(nvl(hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'MAX_RT_END_DT', pen.effective_start_date,pen.effective_end_date), to_date('1900/01/01','YYYY/MM/DD')), nvl(hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'OABV_LAST_UPDATE_DATE', pen.effective_start_date,pen.effective_end_date), to_date('1900/01/01','YYYY/MM/DD'))), nvl(hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'OABV_LAST_UPDATE_DATE', pen.effective_start_date, pen.effective_end_date), to_date('1900/01/01','YYYY/MM/DD'))), nvl(hr_pay_interface_pkg.get_split_erpyc_date( pen.prtt_enrt_rslt_id, 'MIN_RT_STRT_DT', pen.effective_start_date, pen.effective_end_date), 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(hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'MAX_RT_END_DT', pen.effective_start_date,pen.effective_end_date), to_date('1900/01/01','YYYY/MM/DD'))), trunc(hr_ceridian.get_cer_extract_date), greatest(nvl(hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'MAX_RT_END_DT', pen.effective_start_date,pen.effective_end_date), to_date('1900/01/01','YYYY/MM/DD')), nvl(hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'OABV_LAST_UPDATE_DATE', pen.effective_start_date, pen.effective_end_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(hr_pay_interface_pkg.get_split_erpyc_date(pen.prtt_enrt_rslt_id, 'OABV_LAST_UPDATE_DATE', pen.effective_start_date, pen.effective_end_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(hr_pay_interface_pkg.get_split_erpyc_date( pen.prtt_enrt_rslt_id, 'MIN_RT_STRT_DT', pen.effective_start_date, pen.effective_end_date), to_date('1900/01/01','YYYY/MM/DD'))) FROM ben_prtt_enrt_rslt_f pen , ben_pl_f bpl , ben_pgm_f bpg , ben_pl_typ_f bpt , ben_opt_f opt , ben_oipl_f cop , 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.sspndd_flag = 'N' AND pen.effective_end_date = trunc(TO_DATE('31-12-4712','DD-MM-YYYY')) AND pen.enrt_cvg_strt_dt <= hr_ceridian.get_cer_extract_date AND pen.prtt_enrt_rslt_stat_cd IS NULL AND pen.enrt_cvg_strt_dt = ( SELECT MAX(pen2.enrt_cvg_strt_dt) FROM ben_prtt_enrt_rslt_f pen2 WHERE pen2.person_id = pen.person_id AND nvl(pen2.pgm_id,-1) = nvl(pen.pgm_id,-1) AND nvl(pen2.pl_id,-1) = nvl(pen.pl_id,-1) AND nvl(pen2.oipl_id,-1) = nvl(pen.oipl_id,-1) AND pen2.prtt_enrt_rslt_stat_cd is null AND pen2.enrt_cvg_strt_dt <= hr_ceridian.get_cer_extract_date ) 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 = cop.oipl_id (+) AND opt.opt_id (+) = cop.opt_id AND (cop.oipl_id IS NULL OR cop.effective_start_date = (SELECT MAX(cop1.effective_start_date) FROM ben_oipl_f cop1 WHERE cop1.oipl_id = cop.oipl_id AND cop1.effective_start_date <= hr_ceridian.get_cer_extract_date)) AND (opt.opt_id IS NULL OR opt.effective_start_date = (SELECT MAX(opt1.effective_start_date) FROM ben_opt_f opt1 WHERE opt1.opt_id = opt.opt_id AND opt1.effective_start_date <= hr_ceridian.get_cer_extract_date)) 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 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) AND EXISTS (SELECT NULL FROM hr_pay_interface_oab_value2_v pi WHERE pi.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id AND pi.acty_typ_cd IN ('ERPYC') AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date)

View Text - HTML Formatted

SELECT PER.EMPLOYEE_NUMBER
, HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, DECODE(TO_DATE('31-12-4712'
, 'DD-MM-YYYY')
, HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE(NULL)
, HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)) EFFECTIVE_END_DATE
, ASSG.ASSIGNMENT_NUMBER
, ASSG.PRIMARY_FLAG
, PER.NATIONAL_IDENTIFIER
, HOU.NAME
, BPG.NAME
, BPT.NAME
, BPL.NAME
, OPT.NAME OPTION_NAME
, HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_VARCHAR2(PEN.PRTT_ENRT_RSLT_ID
, 'CONTRIBUTION_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) ER_CONTRIBUTION
, HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_NUMBER(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_ANNUAL_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) ER_CONTRIBUTION_LIMIT
, ASSG.ASSIGNMENT_ID
, PER.BUSINESS_GROUP_ID
, ASSG.PAYROLL_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(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, NVL(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE( PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, 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(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_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(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE(PEN.PRTT_ENRT_RSLT_ID
, 'OABV_LAST_UPDATE_DATE'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_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(HR_PAY_INTERFACE_PKG.GET_SPLIT_ERPYC_DATE( PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
FROM BEN_PRTT_ENRT_RSLT_F PEN
, BEN_PL_F BPL
, BEN_PGM_F BPG
, BEN_PL_TYP_F BPT
, BEN_OPT_F OPT
, BEN_OIPL_F COP
, 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.SSPNDD_FLAG = 'N'
AND PEN.EFFECTIVE_END_DATE = TRUNC(TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PEN.ENRT_CVG_STRT_DT <= HR_CERIDIAN.GET_CER_EXTRACT_DATE
AND PEN.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN.ENRT_CVG_STRT_DT = ( SELECT MAX(PEN2.ENRT_CVG_STRT_DT)
FROM BEN_PRTT_ENRT_RSLT_F PEN2
WHERE PEN2.PERSON_ID = PEN.PERSON_ID
AND NVL(PEN2.PGM_ID
, -1) = NVL(PEN.PGM_ID
, -1)
AND NVL(PEN2.PL_ID
, -1) = NVL(PEN.PL_ID
, -1)
AND NVL(PEN2.OIPL_ID
, -1) = NVL(PEN.OIPL_ID
, -1)
AND PEN2.PRTT_ENRT_RSLT_STAT_CD IS NULL
AND PEN2.ENRT_CVG_STRT_DT <= HR_CERIDIAN.GET_CER_EXTRACT_DATE )
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 = COP.OIPL_ID (+)
AND OPT.OPT_ID (+) = COP.OPT_ID
AND (COP.OIPL_ID IS NULL OR COP.EFFECTIVE_START_DATE = (SELECT MAX(COP1.EFFECTIVE_START_DATE)
FROM BEN_OIPL_F COP1
WHERE COP1.OIPL_ID = COP.OIPL_ID
AND COP1.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE))
AND (OPT.OPT_ID IS NULL OR OPT.EFFECTIVE_START_DATE = (SELECT MAX(OPT1.EFFECTIVE_START_DATE)
FROM BEN_OPT_F OPT1
WHERE OPT1.OPT_ID = OPT.OPT_ID
AND OPT1.EFFECTIVE_START_DATE <= HR_CERIDIAN.GET_CER_EXTRACT_DATE))
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 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)
AND EXISTS (SELECT NULL
FROM HR_PAY_INTERFACE_OAB_VALUE2_V PI
WHERE PI.PRTT_ENRT_RSLT_ID = PEN.PRTT_ENRT_RSLT_ID
AND PI.ACTY_TYP_CD IN ('ERPYC')
AND PI.RT_STRT_DT <= HR_CERIDIAN.GET_CER_EXTRACT_DATE)