For requirements to add List of values to a DFF segments, one can create independent value set or a table based value set. For example, if you want LOV as your existing departments, then you can use a table based value set.
But sometimes, you need to display one hard coded value “All” along with list of departments. This is in order to specify that all departments are applicable for a DFF segement value.
In, this case you need to use UNION.
Below is how the query will look like:
SELECT substr(pd.name,1,125) dep_name
FROM per_departments pd
SELECT 'All Departments' dep_name
Value Set setup:
(SELECT substr(pd.name,1,125) dep_name FROM per_departments pd UNION SELECT ‘All Departments’ dep_name FROM DUAL) a
Once a worker record is terminated, it is expected that the associated user account will get inactive. However, it depends upon the configuration on Enterprise Level and auto provisioning rules setup. If there is even on role assigned to user name, the user account will not get terminated in HCM.
To help the business to get a list of active user accounts even if the associated worker record is terminated, below query is created:
SELECT DISTINCT papf.person_number
FROM PER_ALL_PEOPLE_F papf
WHERE papf.person_id = ppos.person_id
AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = pu.person_id
AND pu.user_id = pur.user_id
AND ppos.actual_termination_date IS NOT NULL
AND ppos.actual_termination_date < TRUNC(SYSDATE) /*Check only for past dated terminations*/
AND NOT EXISTS (SELECT 1 FROM PER_PERIODS_OF_SERVICE ppos2
WHERE ppos2.person_id = ppos.person_id
AND ppos2.actual_termination_date IS NULL)
ORDER BY 1,2
There are scenarios when we want to delete a future dated row from an object. Please note that for objects like Positions/Locations etc which are date tracked, one can make use of HDL with SET command to delete the future dated row.
Let us take an example, where we have below data on Position:
Position Name – Test Position
Effective Start Date – 01- Jan-2023 – Record creation
Effective Start Date – 01-Oct-2023 – Record updated (let us say Standard working hours).
Now the requirement is to delete the row with effective start date – 01-Oct-2023.
In such cases, below HDL can be used:
SET PURGE_FUTURE_CHANGES Y
MERGE|Position|BU Name|Pos Code|2023/01/01|4712/12/31
Any locations for which the value of the field will be selected as “Yes”, will be only available to be used in Manage Employment screen. This is a great feature to segregate locations which can be used on employee assignments vs locations which are used as training centers/ supplier locations etc.
By default, this field is hidden on Location Details quick action.
One needs to enable a sandbox and then from transaction design studio, make it visible.
From Quick actions under My Client Groups, choose Location Details:
If you still don’t see the attribute, create a new sandbox, add Experience Design Tool in it:
Choose Location Details as Action:
Click on Add,
under Page attributes, you see Employee Location is hidden OOTB:
P.S. – Please note this field can’t be used in classic locations page. Also, the default value is set to No.
In HCM, you can define approval rules for different HCM tasks from Tools -> Transaction Console -> Approval Rules
For example, you want to define an approval rule for Promote action. You can search Promo% in Find box and it will give you all matching approval rules:
You can then click on ‘Configure Rules’ to define the rules.
However, Transaction console has many limitations. You can’t define complex approval rules. You can’t make use of functions etc (Day between) in transaction console. In order to define more complex rules, you need to do it from BPM.
First step is that you need to search for a task from “Task Configuration”:
The issue here is sometimes the Approval Rule names in Transaction Console doesn’t match with Task names in BPM.
In such case, you can make sure of below Query:
SELECT HAPT.NAME "Approval Rule Name"
,DECODE(HAAO.APPROVAL_DISABLED,'true','Bypassed','false','Enabled') "Enabled Status"
FROM FUSION.HRC_ARM_PROCESS_B HRPB
AND HAPT.LANGUAGE = 'US'
AND HRPB.FAMILY = 'HCM'
ORDER BY 1
Oracle HCM provides a functionality to store different kinds of worker documents in Document Records. Over a period of time, a large number of documents get accumulated for different workers. In such cases, there are requirements to know size of attachments in DORs. For, this I have developed a simple query which can be modified as needed:
,TRUNC(SUM(wcc_documents.dFileSize)/1024,2) as "Size in KB"
,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024,2) as "Size in MB"
,TRUNC(SUM(wcc_documents.dFileSize)/1024/1024/1024,2) as "Size in GB"
from hr_documents_of_record hdr,
where to_char(hdr.documents_of_record_id) = fad.pk1_value(+)
and fad.entity_name(+) = 'HR_DOCUMENTS_OF_RECORD'
and fad.document_id = fdv.document_id(+)
and fdv.dm_version_number = wcc_revisions.did
and wcc_revisions.did = wcc_documents.did
and wcc_documents.disprimary = 1
group by fdv.file_name
order by 1
Below SQL query can be used to find part-timers in HCM. Any worker where FTE is less than 1 is normally considered as part-timer:
select paam.assignment_number, pawmf.value
from per_legal_employers ple
where ppos.legal_entity_id = ple.organization_id
and paam.period_of_Service_id = ppos.period_of_Service_id
and TRUNC(SYSDATE) between paam.effective_start_date AND paam.effective_end_date
and TRUNC(SYSDATE) between pawmf.effective_start_date AND pawmf.effective_end_date
and paam.assignment_status_type like 'ACTIVE%'
and ple.name like 'Arup%United%Stat%'
and paam.assignment_id = pawmf.assignment_id
and pawmf.unit = 'FTE'
and pawmf.value <1
and paam.assignment_type ='E'
and ppos.actual_termination_date is null
order by 1
Recently, I faced a scenario for a customer where after go-live, where there was an issue found with absence plan configuration. The absence plan was incorrectly set up. The Balance Frequency Source wasn’t setup to “Repeating Period”, so the accrual was calculated incorrectly. In order to fix this, a new absence plan was created and the absence entries were made against the new plan.
So, the approach taken was to take a backup of all absence entries from PROD, enroll the employees into new plan and reupload the absence entries.
A BIP report was developed to take a backup of existing absence entries in HDL format. Below is the query for same:
Once the data is extracted, you need to make sure that Source System Owner is updated from FUSION to HRC_SQLLOADER. Source System Owner is set to FUSION when an entry is created from UI.
Once the output of the BIP is ready, Change “MERGE” to “DELETE” to delete all the absence entries. Then enroll the workers in new plan, do the required changes in BIP extract and upload the data back in Fusion HCM.
There are requirements where customers want to display Honors in “Display Name” if First Name is blank. This where the conditional name formatting functionality of Oracle HCM helps to achieve desired results.
Let us assume, we want display name in below format:
NVL(First Name, Honors), Name Information 1
Follow below steps to achieve this requirement:
Search for Task “Manage Person Name Formats” from setup and maintenance:
2. Search the name format you want to modify/ or create a new one if you want to have a new name format:
3. Click on edit and Choose Conditional Name Formatting component:
4. Scroll down and supply the values in “Preview Name Format” section:
5. As can be seen, first name is blank so Formatted Name is displayed as – Honors Name_Information_1
6. If you supply a value for first name:
Formatted Name is displayed as – Test Name_Information_1 (Honors is ignored as First Name is present).
Oracle HCM provides lots of pre-defined messages which are triggered/displayed in UI on various actions. For example, if you are trying to hire a new person on a position which doesn’t have the vacant FTE, system will throw below warning message:
However, in this particular scenario, business wanted to see an Error message instead of Warning. This can be achieved easily, using “Manage Messages” task under “Setup and Maintenance”.
However, before using Manage messages task, one should know the message number or message name to search. The message name/ message number can be found by running a quick query on fnd_messages_vl:
select * from fnd_messages_vl
where upper(message_text) like 'THE%POSITION%HAS%FUTURE%INCU%'
This will return you the list of all messages which matches the given text:
One we are looking to modify is – 1532361/PER_EMCOR_NO_HC_NO_FTE_FUTURE.
Note it down and open Manage Messages task. Enter the message number and click on search:
Click on edit icon to edit the message:
On edit screen, you can change message text as well as message type:
Change the type to Error and save the changes:
Please note that there are certain messages on UI which are not available in manage messages. To edit those messages, User Interface Text tool can be utilized:
Many a times, even after loading Person EFF information successfully, using HCM Data loader, the information is not available in UI.
In such cases, please make sure that following fields are passed correctly:
InformationType – Name of extra information type e.g. ‘XYZ Medical History’
EFF_CATEGORY_CODE – EFF Category Context. For example, for person EIT, the value will be PER_EIT
CategoryCode – EFF Category Context. For example, for person EIT, the value will be PER_EIT
PeiInformationCategory – Name of extra information type e.g. ‘XYZ Medical History’
Sample HDL file to load Worker EFF information:
METADATA|WorkerExtraInfo|PersonNumber|PersonId|EffectiveStartDate|EffectiveEndDate|SourceSystemId|SourceSystemOwner|FLEX:PER_PERSON_EIT_EFF|PeiInformationCategory|CategoryCode|InformationType|medicalStatus(PER_PERSON_EIT_EFF=XYZ Medical Informaton))|EFF_CATEGORY_CODE
MERGE|WorkerExtraInfo|998812||2022/04/01|4712/12/31|FUSION_998812_1|HRC_SQLLOADER|XYZ Medical Informaton|XYZ Medical Informaton|PER_EIT|XYZ Medical Informaton|Normal|PER_EIT
Oracle HCM allows special characters to be used in fields like Person Names/ Department Names/ Jobs etc. But in reporting, there may be a requirement to change the special characters (like ý, ě, ž, ů etc) to normal English characters.
CONVERT function can be used for such requirements.
select CONVERT('Politických vězňů ižní Předměstí Plzeň', 'US7ASCII') normal_english from dual
Below query can be used to pull actions and configured actions for these reasons along with action type:
,to_char(aru.start_date, 'yyyy/mm/dd') start_date
,to_char(aru.end_date, 'yyyy/mm/dd') end_date
FROM PER_ACTION_REASON_USAGES aru,
AND aru.action_id = pav.action_id
AND aru.action_reason_id = parv.action_reason_id
AND pav.action_code = 'CHANGE_SALARY'
ORDER BY 1,3,5
Below are some important my oracle support notes related to diagnostic framework:
Self-Service Data Integrity Framework for Employment Flows – Part 2 (Doc ID 2597759.1) Self-Service Data Integrity Framework for Employment Flows – Part 1 (Doc ID 2548287.1) Self-Service Data Integrity Framework for Person Flows (Doc ID 2548789.1) Self-Service Data Integrity Framework for Workforce Structure Flows (Doc ID 2548827.1) Fusion Global HR: Corruption Type in Person Diagnostic Auto Correct Report (Doc ID 2619978.1)
Check below link to see the privilege’s required for run Diagnostics: