Areas of Responsibility (AOR), Its usage, Data Load and Report

AOR can be accessed from Person Management Screen from the right hand side task Areas of Responsibility

Here, we can also include the flag “Include in Work Contacts” which lists the AORs on the employee contacts page.

AOR information can also be loaded using HCM Data Loader and here is the sample dat file for it.

HDL file for loading AOR Info:

COMMENT Save this as AreasOfResponsibility.dat
METADATA|AreasOfResponsibility|ResponsibilityName|ResponsibilityType|StartDate|Status|PersonNumber|AssignmentNumber|OrganizationName|WorkContactsFlag
MERGE|AreasOfResponsibility|EVP - Finance|EVP|2018/01/01|Active|1232333232|E91232333232|Finance|Y

SQL Query to pull AOR Setup Info:

PER_ASG_RESPONSIBILITIES is the table which stores the AOR information. Here is the query to pull AOR setup information.

SELECT (
		SELECT DISTINCT PERSON_NUMBER
		FROM PER_ALL_PEOPLE_F PER
		WHERE PER.PERSON_ID = AOR.PERSON_ID
		) PERSON_NUMBER,
	(
		SELECT DISTINCT FULL_NAME
		FROM PER_PERSON_NAMES_F PER
		WHERE PER.PERSON_ID = AOR.PERSON_ID
			AND NAME_TYPE = 'GLOBAL'
		) PERSON_NAME,
	AOR.RESPONSIBILITY_NAME,
	TO_CHAR(AOR.START_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') START_DATE,
	TO_CHAR(AOR.END_DATE, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN') END_DATE,
	AOR.RESPONSIBILITY_TYPE,
	AOR.STATUS,
	(
		SELECT NAME
		FROM HR_ALL_ORGANIZATION_UNITS HOU
		WHERE HOU.ORGANIZATION_ID = AOR.BUSINESS_UNIT_ID
		) BUSINESS_UNIT,
	(
		SELECT NAME
		FROM HR_ALL_ORGANIZATION_UNITS HOU
		WHERE HOU.ORGANIZATION_ID = AOR.LEGAL_ENTITY_ID
		) LEGAL_ENTITY,
	(
		SELECT NAME
		FROM HR_ALL_ORGANIZATION_UNITS HOU
		WHERE HOU.ORGANIZATION_ID = AOR.ORGANIZATION_ID
		) DEPARTMENT,
	(
		SELECT LOCATION_NAME
		FROM HR_LOCATIONS HL
		WHERE HL.LOCATION_ID = AOR.LOCATION_ID
		) LOCATION,
	(
		SELECT DISTINCT NAME
		FROM HR_ALL_POSITIONS_F_VL PP
		WHERE PP.POSITION_ID = AOR.POSITION_ID
		) POSITION,
	(
		SELECT DISTINCT NAME
		FROM PER_JOBS_F_TL PJ
		WHERE PJ.JOB_ID = AOR.JOB_ID
		) JOB,
	(
		SELECT DISTINCT NAME
		FROM PER_GRADES_F_TL PG
		WHERE PG.GRADE_ID = AOR.GRADE_ID
		) GRADE,
	AOR.ASSIGNMENT_CATEGORY,
	(
		SELECT PAY.PAYROLL_NAME
		FROM PAY_ALL_PAYROLLS_F PAY
		WHERE PAY.PAYROLL_ID = AOR.PAYROLL_ID
			AND TRUNC(SYSDATE) BETWEEN PAY.EFFECTIVE_START_DATE
				AND PAY.EFFECTIVE_END_DATE
		) PAYROLL,
	(
		SELECT NAME
		FROM PER_LEGISLATIVE_DATA_GROUPS_VL PLD
		WHERE PLD.LEGISLATIVE_DATA_GROUP_ID = AOR.LEGISLATIVE_DATA_GROUP_ID
		) LEGISLATIVE_DATA_GROUP
FROM PER_ASG_RESPONSIBILITIES AOR

SQL Query to pull employee and AOR Information:

SELECT TO_Date(To_CHAR(sysdate, 'DD-MM-YYYY'), 'DD-MM-YYYY') Run_Date,
	ppnf.full_name,
	ppnf.first_name,
	papf.person_number emp_person_number,
	papf.person_id Main_Person_Id,
	paaf.assignment_Id Main_Assignment_Id,
	pj.name Designation,
	haou.name Business,
	hl.location_name location,
	papf_Aor.person_number aor_person_number,
	par.RESPONSIBILITY_TYPE,
	peaaor.email_address aor_email,
	peaMgr.email_address mgr_email,
	pasf.Manager_type
FROM per_all_people_f papf,
	per_all_assignments_f paaf,
	per_person_names_f ppnf,
	per_jobs pj,
	hr_all_organization_units haou,
	hr_locations hl,
	per_asg_responsibilities par,
	per_all_people_f papf_Aor,
	per_email_addresses peaaor,
	per_email_addresses peaMgr,
	per_all_people_F papfMgr,
	per_assignment_supervisors_f pasf
WHERE 1 = 1
	AND trunc(sysdate) BETWEEN papf.effective_start_date
		AND papf.effective_end_date
			--Assignment
	AND papf.person_id = paaf.person_id
	AND trunc(sysdate) BETWEEN paaf.effective_start_date
		AND paaf.effective_end_date
	AND paaf.assignment_type = 'E'
	AND paaf.primary_Flag = 'Y'
	--PersonName
	AND papf.person_id = ppnf.person_id
	AND trunc(sysdate) BETWEEN ppnf.effective_StarT_Date
		AND ppnf.effective_End_Date
	AND ppnf.name_type = 'GLOBAL'
	--Job
	AND paaf.job_id = pj.job_id(+)
	AND trunc(sysdate) BETWEEN pj.effective_StarT_Date
		AND pj.effective_End_Date
			--Organization
	AND paaf.business_unit_id = haou.organization_id
	--location 
	AND paaf.location_id = hl.location_id(+)
	AND trunc(sysdate) BETWEEN hl.effective_StarT_Date
		AND hl.effective_End_Date
			--AOR
	AND papf_Aor.person_id = par.person_id
	AND trunc(sysdate) BETWEEN papf_Aor.effective_start_date
		AND papf_Aor.effective_end_date
	AND (
		paaf.POSITION_ID = par.POSITION_ID
		OR par.POSITION_ID IS NULL
		)
	AND (
		paaf.ORGANIZATION_ID = par.ORGANIZATION_ID
		OR par.ORGANIZATION_ID IS NULL
		)
	AND (
		paaf.BUSINESS_UNIT_ID = par.BUSINESS_UNIT_ID
		OR par.BUSINESS_UNIT_ID IS NULL
		)
	AND (
		paaf.EMPLOYMENT_CATEGORY = par.ASSIGNMENT_CATEGORY
		OR par.ASSIGNMENT_CATEGORY IS NULL
		)
	AND (
		paaf.LEGAL_ENTITY_ID = par.LEGAL_ENTITY_ID
		OR par.LEGAL_ENTITY_ID IS NULL
		)
	AND (
		paaf.LOCATION_ID = par.LOCATION_ID
		OR par.LOCATION_ID IS NULL
		)
	AND (
		paaf.JOB_ID = par.JOB_ID
		OR par.JOB_ID IS NULL
		)
	AND (
		paaf.GRADE_ID = par.GRADE_ID
		OR par.GRADE_ID IS NULL
		)
	AND (
		paaf.legislation_code = par.country
		OR par.country IS NULL
		)
	AND trunc(sysdate) BETWEEN par.start_date
		AND NVL(par.end_date, to_date('31-12-4712', 'DD-MM-YYYY'))
			-- email address aor
	AND papf_Aor.primary_email_id = peaaor.email_address_id(+)
	-- Supervisor
	AND paaf.assignment_id = pasf.assignment_id(+)
	AND paaf.person_id = pasf.person_id(+)
	AND trunc(sysdate) BETWEEN pasf.effective_StarT_Date
		AND pasf.effective_End_Date
	AND pasf.manager_type = 'LINE_MANAGER'
	AND pasf.primary_Flag = 'Y'
	-- supervisor person join
	AND papfMgr.person_id = pasf.manager_id
	AND trunc(sysdate) BETWEEN papfMgr.effective_StarT_Date
		AND papfMgr.effective_End_Date
			-- supervisor Email join
	AND papfMgr.primary_email_id = peaMgr.email_address_id(+)

AOR Functionality is also used in security to provide data access to users by creating a Person Security Profile based on AOR and assigning it to users.

Hope this provides you with an overview of the AOR functionality.