VIEW: APPS.HR_PAY_INTERFACE_OAB_OPTION_V

Source

View Text - Preformatted

SELECT opt.name ,cop.pl_id ,cop.oipl_id ,greatest(nvl(cop.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), opt.effective_start_date) ,cop.effective_start_date ,cop.effective_end_date FROM ben_opt_f opt ,ben_oipl_f cop WHERE opt.opt_id = cop.opt_id AND 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_pay_interface_pkg.get_extract_date ) AND 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_pay_interface_pkg.get_extract_date )

View Text - HTML Formatted

SELECT OPT.NAME
, COP.PL_ID
, COP.OIPL_ID
, GREATEST(NVL(COP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, OPT.EFFECTIVE_START_DATE)
, COP.EFFECTIVE_START_DATE
, COP.EFFECTIVE_END_DATE
FROM BEN_OPT_F OPT
, BEN_OIPL_F COP
WHERE OPT.OPT_ID = COP.OPT_ID
AND 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_PAY_INTERFACE_PKG.GET_EXTRACT_DATE )
AND 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_PAY_INTERFACE_PKG.GET_EXTRACT_DATE )