0
0
SELECT
papf.person_number,
ppnf.display_name,
paaf.assignment_number,
flv.meaning AS aor_type,
org.name AS 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 AS 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 par.assignment_id(+) = paaf.assignment_id
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.