VIEW: APPS.HR_CERIDIAN_500_OAB_BENEFIT_V

Source

View Text - Preformatted

SELECT per.employee_number employee_number, hr_pay_interface_pkg.get_eepyc_date (pen.prtt_enrt_rslt_id, 'MIN_RT_STRT_DT', pen.effective_start_date, pen.effective_end_date) effective_start_date, DECODE (TO_DATE ('31-12-4712', 'DD-MM-YYYY'), hr_pay_interface_pkg.get_eepyc_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_eepyc_date (pen.prtt_enrt_rslt_id, 'MAX_RT_END_DT', pen.effective_start_date, pen.effective_end_date)) effective_end_date, assg.assignment_number assignment_number, assg.primary_flag primary_flag, per.national_identifier social_security_number, hou.name company, bpg.name program, bpt.name plan_type, bpl.name plan_name, opt.name option_name, hr_pay_interface_pkg.get_eepyc_varchar2 (pen.prtt_enrt_rslt_id, 'CONTRIBUTION_AMOUNT', pen.effective_start_date, pen.effective_end_date) ee_contribution, hr_pay_interface_pkg.get_erpyc_varchar2 (pen.prtt_enrt_rslt_id, 'CONTRIBUTION_AMOUNT', pen.effective_start_date, pen.effective_end_date) er_contribution, hr_pay_interface_pkg.get_eepyc_number (pen.prtt_enrt_rslt_id, 'MAX_ANNUAL_AMOUNT', pen.effective_start_date, pen.effective_end_date) ee_contribution_limit, hr_pay_interface_pkg.get_erpyc_number (pen.prtt_enrt_rslt_id, 'MAX_ANNUAL_AMOUNT', pen.effective_start_date, pen.effective_end_date) er_contribution_limit, assg.assignment_id assignment_id, per.business_group_id business_group_id, assg.payroll_id payroll_id, per.person_id person_id, GREATEST (NVL (per.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), per.effective_start_date) per_last_update_date, GREATEST (NVL (assg.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), assg.effective_start_date) asg_last_update_date, GREATEST (DECODE (GREATEST (TRUNC (hr_ceridian.get_cer_extract_date), NVL (hr_pay_interface_pkg.get_eepyc_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_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_eepyc_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_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_eepyc_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_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'))), GREATEST (NVL (hr_pay_interface_pkg.get_eepyc_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_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 (NVL (hr_pay_interface_pkg.get_eepyc_date (pen.prtt_enrt_rslt_id, 'MIN_RT_STRT_DT', pen.effective_start_date, pen.effective_end_date), hr_pay_interface_pkg.get_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'))) ben_last_update_date, GREATEST (NVL (ptp.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), TO_DATE ('1900/01/01', 'YYYY/MM/DD')) tp_last_update_date, GREATEST (DECODE (GREATEST (TRUNC (hr_ceridian.get_cer_extract_date), GREATEST (NVL (hr_pay_interface_pkg.get_eepyc_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_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_eepyc_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_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_eepyc_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_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_eepyc_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_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 (NVL (hr_pay_interface_pkg.get_eepyc_date (pen.prtt_enrt_rslt_id, 'MIN_RT_STRT_DT', pen.effective_start_date, pen.effective_end_date), hr_pay_interface_pkg.get_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_last_update_date 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_value_v pi WHERE pi.prtt_enrt_rslt_id = pen.prtt_enrt_rslt_id AND pi.acty_typ_cd IN ('EEPYC','ERPYC') AND pi.rt_strt_dt <= hr_ceridian.get_cer_extract_date)

View Text - HTML Formatted

SELECT PER.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE (PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) EFFECTIVE_START_DATE
, DECODE (TO_DATE ('31-12-4712'
, 'DD-MM-YYYY')
, HR_PAY_INTERFACE_PKG.GET_EEPYC_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_EEPYC_DATE (PEN.PRTT_ENRT_RSLT_ID
, 'MAX_RT_END_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)) EFFECTIVE_END_DATE
, ASSG.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, ASSG.PRIMARY_FLAG PRIMARY_FLAG
, PER.NATIONAL_IDENTIFIER SOCIAL_SECURITY_NUMBER
, HOU.NAME COMPANY
, BPG.NAME PROGRAM
, BPT.NAME PLAN_TYPE
, BPL.NAME PLAN_NAME
, OPT.NAME OPTION_NAME
, HR_PAY_INTERFACE_PKG.GET_EEPYC_VARCHAR2 (PEN.PRTT_ENRT_RSLT_ID
, 'CONTRIBUTION_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) EE_CONTRIBUTION
, HR_PAY_INTERFACE_PKG.GET_ERPYC_VARCHAR2 (PEN.PRTT_ENRT_RSLT_ID
, 'CONTRIBUTION_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) ER_CONTRIBUTION
, HR_PAY_INTERFACE_PKG.GET_EEPYC_NUMBER (PEN.PRTT_ENRT_RSLT_ID
, 'MAX_ANNUAL_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) EE_CONTRIBUTION_LIMIT
, HR_PAY_INTERFACE_PKG.GET_ERPYC_NUMBER (PEN.PRTT_ENRT_RSLT_ID
, 'MAX_ANNUAL_AMOUNT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE) ER_CONTRIBUTION_LIMIT
, ASSG.ASSIGNMENT_ID ASSIGNMENT_ID
, PER.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASSG.PAYROLL_ID PAYROLL_ID
, PER.PERSON_ID PERSON_ID
, GREATEST (NVL (PER.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PER.EFFECTIVE_START_DATE) PER_LAST_UPDATE_DATE
, GREATEST (NVL (ASSG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, ASSG.EFFECTIVE_START_DATE) ASG_LAST_UPDATE_DATE
, GREATEST (DECODE (GREATEST (TRUNC (HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL (HR_PAY_INTERFACE_PKG.GET_EEPYC_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_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_EEPYC_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_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_EEPYC_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_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')))
, GREATEST (NVL (HR_PAY_INTERFACE_PKG.GET_EEPYC_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_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 (NVL (HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE (PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, HR_PAY_INTERFACE_PKG.GET_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'))) BEN_LAST_UPDATE_DATE
, GREATEST (NVL (PTP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')) TP_LAST_UPDATE_DATE
, GREATEST (DECODE (GREATEST (TRUNC (HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST (NVL (HR_PAY_INTERFACE_PKG.GET_EEPYC_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_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_EEPYC_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_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_EEPYC_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_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_EEPYC_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_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 (NVL (HR_PAY_INTERFACE_PKG.GET_EEPYC_DATE (PEN.PRTT_ENRT_RSLT_ID
, 'MIN_RT_STRT_DT'
, PEN.EFFECTIVE_START_DATE
, PEN.EFFECTIVE_END_DATE)
, HR_PAY_INTERFACE_PKG.GET_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_LAST_UPDATE_DATE
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_VALUE_V PI
WHERE PI.PRTT_ENRT_RSLT_ID = PEN.PRTT_ENRT_RSLT_ID
AND PI.ACTY_TYP_CD IN ('EEPYC'
, 'ERPYC')
AND PI.RT_STRT_DT <= HR_CERIDIAN.GET_CER_EXTRACT_DATE)