(case when par.business_unit_id is not null then 'Business Unit'
when par.organization_id is not null then 'Department'
when par.legal_entity_id is not null then 'Legal Entity' else null end ) scope_name,par.status
FROM per_all_people_f papf,
per_all_assignments_f paaf,
per_asg_responsibilities par,
hr_organization_units_f_tl org,
WHERE ppnf.person_id(+) = papf.person_id
AND paaf.person_id = papf.person_id
AND UPPER(TRIM(paaf.effective_latest_change)) = 'Y'
AND paaf.assignment_status_type = 'ACTIVE'
AND UPPER(TRIM(paaf.assignment_type)) = 'E'
AND UPPER(TRIM(ppnf.name_type(+))) = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN TRUNC(
NVL(ppnf.effective_start_date(+), SYSDATE)) AND
TRUNC(NVL(ppnf.effective_end_date(+), SYSDATE))
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(papf.effective_start_date, SYSDATE)) AND
TRUNC(NVL(papf.effective_end_date, SYSDATE))
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(paaf.effective_start_date(+), SYSDATE)) AND
TRUNC(NVL(paaf.effective_end_date(+), SYSDATE))
-- AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(par.start_date(+), SYSDATE)) AND
-- TRUNC(NVL(par.end_date(+), SYSDATE))
-- AND UPPER(TRIM(par.status(+))) = 'ACTIVE' ---to get inactive AOR
AND par.assignment_id(+) = paaf.assignment_id --responsibility not be there
AND par.responsibility_type = flv.lookup_code(+)
and (par.organization_id = org.organization_id
or par.legal_entity_id = org.organization_id
or par.business_unit_id = org.organization_id)
AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ORG.EFFECTIVE_START_DATE(+), SYSDATE)) AND
TRUNC(NVL(ORG.EFFECTIVE_END_DATE(+), SYSDATE))
AND flv.lookup_type(+) = 'PER_RESPONSIBILITY_TYPES'
AND flv.enabled_flag(+) = 'Y'
AND TRUNC(SYSDATE) BETWEEN NVL(flv.start_date_active(+), TRUNC(SYSDATE)) AND
NVL(flv.end_date_active(+), TRUNC(SYSDATE))
I hope this blog post was helpful for you. If you have any questions or feedback, please leave a comment below.