Introduction
Before we delve deep into developing a Custom BI Report on Areas of Responsibility Report let-us spend some time trying to understand what is meant by Areas of Responsibility Feature in Oracle HCM Cloud Application.
The literal meaning of Areas of Responsibility as the word suggests means the various work-streams an individual is responsible to take care of.
In context of Oracle HCM Cloud Applications Areas of Responsibility referredto as AOR means:
“You can assign a responsibility to a person and define the scope of the responsibility by identifying the people for whom the person has the responsibility; the person then appears in the Work Contacts list of those people. For example, you can assign a worker as the Human Resources (HR) representative for people in a specific organization or department hierarchy. You manage areas of responsibility using the Manage Areas of Responsibility task in the Person Management work area.
Assigning an area of responsibility doesn't affect the person records the representative can see. Access to records is controlled through security. Your security administrator can set up security profiles using areas of responsibility”
One can refer to Detailed Documentation on AOR from the product document, My Oracle Support Portal or even refer to Application Help.
For now we would try to concentrate on building a custom report to identify the AOR details like Person#, Name, Country, Responsibility Type and Responsibility Name.
Worked Example
We would try to build a custom BI Report and the expected data columns are:
-
Person#
-
Name
-
Country
-
Responsibility Type
-
Responsibility Name
We would need to build a custom SQL query for this which is:
|
SQL Query |
|
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 |
Once done the Final Report should look as:
Apps2Fusion Articles 
Comments
I have joined your rss feed and sit up for in search of
extra of your excellent post. Also, I have shared your site in my social
networks
I checked on the net for additional information about the issue and found most people will go along with your views
on this site.
RSS feed for comments to this post