How to handle Union in a Table based value set in Oracle fusion
I have a requirement where i need to add the following query as my value set definition:
SELECT papf.person_number,
CASE
WHEN ppos.NOTIFIED_TERMINATION_DATE IS NULL THEN 'ACTIVE'
WHEN ppos.NOTIFIED_TERMINATION_DATE + NUMTODSINTERVAL(365, 'DAY') >= SYSDATE THEN 'ACTIVE'ELSE 'INACTIVE'END || ' | ' || ppnf.display_name AS display_status
FROM per_all_people_f papf, per_periods_of_service ppos, per_person_names_f ppnf
WHERE papf.person_id = ppos.person_id
AND papf.person_id = ppnf.person_id
AND ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
Tagged:
0