Note: Area of responsibilities is NOT part of security roles (Duty or Job Role) it is separate from Security Profile.
SELECT DISTINCT papfc.person_number,
par.responsibility_type,
par.responsibility_name,
ppnfv.full_name full_name,
per.country
FROM (SELECT papf.person_id,
papf.person_number,
paam.legislation_code,
(SELECT DISTINCT hlaf.country
FROM hr_locations_all_f hlaf
WHERE hlaf.location_id = paam.location_id
AND SYSDATE BETWEEN hlaf.effective_start_date
AND hlaf.effective_end_date)
country,
paam.business_unit_id,
paam.legal_entity_id,
paam.organization_id,
paam.location_id,
paam.position_id,
paam.job_id
FROM per_all_people_f papf, per_all_assignments_m paam
WHERE 1 = 1 AND paam.person_id = papf.person_id
AND SYSDATE BETWEEN paam.effective_start_date
AND paam.effective_end_date
AND SYSDATE BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND paam.effective_latest_change = 'Y'
AND paam.assignment_type IN ('E')) per,
per_person_names_f_v ppnfv,
per_asg_responsibilities par,
per_all_people_f papfc,
per_email_addresses pea
WHERE 1 = 1 AND papfc.person_id = par.person_id
--AND per.person_number=NVL(:P_PERSON,10)
AND SYSDATE BETWEEN ppnfv.effective_start_date
AND ppnfv.effective_end_date
AND ppnfv.person_id = papfc.person_id
AND SYSDATE BETWEEN papfc.effective_start_date
AND papfc.effective_end_date
AND papfc.primary_email_id = pea.email_address_id(+)
AND (NVL (par.country, NVL (per.country, 1)) = NVL (per.country, 1)
OR NVL (par.country, NVL (per.legislation_code, 1)) =
NVL (per.legislation_code, 1))
AND NVL (par.business_unit_id, NVL (per.business_unit_id, 1)) =
NVL (per.business_unit_id, 1)
AND NVL (par.legal_entity_id, NVL (per.legal_entity_id, 1)) =
NVL (per.legal_entity_id, 1)
AND NVL (par.organization_id, NVL (per.organization_id, 1)) =
NVL (per.organization_id, 1)
AND NVL (par.location_id, NVL (per.location_id, 1)) =
NVL (per.location_id, 1)
AND NVL (par.position_id, NVL (per.position_id, 1)) =
NVL (per.position_id, 1)
AND NVL (par.job_id, NVL (per.job_id, 1)) = NVL (per.job_id, 1)
/*AND par.responsibility_type IN ('')*/
ORDER BY par.responsibility_type, papfc.person_number
