How to get the AOR details, which person has assigned which Areas of responsibility using SQL Query

select

papf.person_number,

ppnf.display_name,

paaf.assignment_number,

flv.meaning aor_type,

org.name scope_value,

par.responsibility_name,

(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_person_names_f ppnf,

per_all_assignments_f paaf,

per_asg_responsibilities par,

hr_organization_units_f_tl org,

fnd_lookup_values flv

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.