Search for:
Sep 14 2022
OTBI – Areas of Responsibility Subject area

Oracle has released a new subject area for Areas of Responsibility.

Subject Area Name – Workforce Management – Areas of Responsibility

This has simplified the process of extracting AoR data using OTBI.

To get any data in this subject area, you should run “Refresh Representative Data” ESS process. The process should be run post any changes done to AoR’s.

To get data in this SA below duties should be assigned:

FBI_AREAS_OF_RESPONSIBILITY_TRANSACTION_ANALYSIS_DUTY 
ORA_FBI_AREAS_OF_RESPONSIBILITY_TRANSACTION_ANALYSIS_DUTY_HCM

Please note that these duties should be added to Job Role not the Data Role.

Mandeep Gupta 0 Comments
Sep 13 2022
BIP – How to check Termination version on your environment?

You can use per_empl_configurations table to verify the versions of Seniority dates/ Termination dates.

Below is an example to check the version for Termination dates:

select version_code 
  from fusion.per_empl_configurations 
 where configuration_type = 'ORA_TERMINATION'

If the version code value is NULL that means you are still on version V2.

Same table can be used to query Seniority Dates version.

Mandeep Gupta 0 Comments
Sep 8 2022
HDL – Assignment Working Hour Pattern

Oracle has provided a new feature where working hours for each day can be stored against assignment working hours.

The data is stored in PER_WORKING_HOUR_PATTERNS_F table.

Use below query to extract the data:

SELECT papf.person_number, pwhpf.* 
  FROM PER_WORKING_HOUR_PATTERNS_F pwhpf
      ,PER_ALL_ASSIGNMENTS_M paam
	  ,PER_ALL_PEOPLE_F papf
 WHERE pwhpf.OBJECT_ID = paam.ASSIGNMENT_ID
   AND paam.PERSON_ID = papf.PERSON_ID
   AND papf.person_number = '10011'
   AND paam.assignment_type = 'E'
   AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
   AND paam.effective_start_date BETWEEN pwhpf.effective_start_date AND pwhpf.effective_end_date

Query to extract data in HDL format:

SELECT
'METADATA|WorkingHourPattern|WorkingHourPatternId|EffectiveStartDate|EffectiveEndDate|Object|ObjectId|AssignmentNumber|PersonId|ReplaceFirstEffectiveStartDate' as DATA_ROW, 1 ORDERBY From dual
UNION
SELECT 'MERGE|WorkingHourPattern|'
|| pwhpf.WORKING_HOUR_PATTERN_ID
|| '|'
|| TO_CHAR(pwhpf.EFFECTIVE_START_DATE,'YYYY/MM/DD')
|| '|'
|| TO_CHAR(pwhpf.EFFECTIVE_END_DATE,'YYYY/MM/DD')
|| '|ASSIGNMENT|'
|| pwhpf.object_id
|| '|'
|| paam.assignment_number
|| '|'
|| paam.person_id
|| '|'
|| 'Y'
 as DATA_ROW, 2 ORDERBY
FROM PER_WORKING_HOUR_PATTERNS_F pwhpf
    ,PER_ALL_ASSIGNMENTS_M paam
WHERE paam.assignment_id = pwhpf.object_id
  AND paam.assignment_type = 'E'
  AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
  AND paam.effective_start_date BETWEEN pwhpf.effective_start_date AND pwhpf.effective_end_date;
Mandeep Gupta 0 Comments