You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

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

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!