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
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
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:
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:
Oracle provides a process Purge Person Data in Test Environments to remove Person/Contact/Worker records from a test environment of Oracle HCM. Please note that HDL for core worker object doesn’t support DELETE, so there is no other option to purge the Person/Contact/Worker information from Oracle HCM once a record is created. In this case, Purge Person Data in Test Environments is definitely a useful utility which helps to purge person related data from a test environment.
This process can’t be run in Oracle HCM Production.
Before 21D, the process required an additional step to get a key to enable to process in Test environments but from 21D onwards the process is enabled by default in all test environments.
You can find more details related to this process on below link:
Click on schedule new process –> Search for Purge Person Data in Test Environments
Supply the parameters
Sample Person SQL’s:
To remove only Contact Person records:
SELECT DISTINCT papf.person_id FROM per_all_people_f papf WHERE 1=1 AND NOT EXISTS (SELECT 1 FROM per_periods_of_service ppos where ppos.person_id = papf.person_id)
To remove all person records:
SELECT DISTINCT papf.person_id FROM per_all_people_f papf
Use below SQL to monitor the progress of entries in each table:
SELECT 'ANC_PER_ABS_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ABS_ENTRIES
SELECT 'ANC_PER_ABS_ENTRY_DTLS' table_name, count(*) rowcount FROM ANC_PER_ABS_ENTRY_DTLS
SELECT 'ANC_PER_ABS_MATERNITY' table_name, count(*) rowcount FROM ANC_PER_ABS_MATERNITY
SELECT 'ANC_PER_PLAN_ENROLLMENT' table_name, count(*) rowcount FROM ANC_PER_PLAN_ENROLLMENT
SELECT 'ANC_PER_ABS_PLAN_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ABS_PLAN_ENTRIES
SELECT 'ANC_PER_ACCRUAL_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ACCRUAL_ENTRIES
SELECT 'ANC_PER_ACRL_ENTRY_DTLS' table_name, count(*) rowcount FROM ANC_PER_ACRL_ENTRY_DTLS
SELECT 'CMP_SALARY' table_name, count(*) rowcount FROM CMP_SALARY
SELECT 'PAY_ASSIGNED_PAYROLLS_DN' table_name, count(*) rowcount FROM PAY_ASSIGNED_PAYROLLS_DN
SELECT 'PAY_ASSIGNED_PAYROLLS_F' table_name, count(*) rowcount FROM PAY_ASSIGNED_PAYROLLS_F
SELECT 'PER_ADDRESSES_F' table_name, count(*) rowcount FROM PER_ADDRESSES_F
SELECT 'PER_ALL_ASSIGNMENTS_M' table_name, count(*) rowcount FROM PER_ALL_ASSIGNMENTS_M
SELECT 'PER_ALL_PEOPLE_F' table_name, count(*) rowcount FROM PER_ALL_PEOPLE_F
SELECT 'PER_ASSIGN_WORK_MEASURES_F' table_name, count(*) rowcount FROM PER_ASSIGN_WORK_MEASURES_F
SELECT 'PER_CITIZENSHIPS' table_name, count(*) rowcount FROM PER_CITIZENSHIPS
SELECT 'PER_DRIVERS_LICENSES' table_name, count(*) rowcount FROM PER_DRIVERS_LICENSES
SELECT 'PER_EMAIL_ADDRESSES' table_name, count(*) rowcount FROM PER_EMAIL_ADDRESSES
SELECT 'PER_ETHNICITIES' table_name, count(*) rowcount FROM PER_ETHNICITIES
SELECT 'PER_NATIONAL_IDENTIFIERS' table_name, count(*) rowcount FROM PER_NATIONAL_IDENTIFIERS
SELECT 'PER_PASSPORTS' table_name, count(*) rowcount FROM PER_PASSPORTS
SELECT 'PER_PEOPLE_LEGISLATIVE_F' table_name, count(*) rowcount FROM PER_PEOPLE_LEGISLATIVE_F
SELECT 'PER_PERIODS_OF_SERVICE' table_name, count(*) rowcount FROM PER_PERIODS_OF_SERVICE
SELECT 'PER_PERSON_ADDR_USAGES_F' table_name, count(*) rowcount FROM PER_PERSON_ADDR_USAGES_F
SELECT 'PER_PERSON_NAMES_F' table_name, count(*) rowcount FROM PER_PERSON_NAMES_F
SELECT 'PER_PERSON_TYPE_USAGES_M' table_name, count(*) rowcount FROM PER_PERSON_TYPE_USAGES_M
SELECT 'PER_PERSONS' table_name, count(*) rowcount FROM PER_PERSONS
SELECT 'PER_PHONES' table_name, count(*) rowcount FROM PER_PHONES
SELECT 'PER_RELIGIONS' table_name, count(*) rowcount FROM PER_RELIGIONS
SELECT 'PER_VISAS_PERMITS_F' table_name, count(*) rowcount FROM PER_VISAS_PERMITS_F
SELECT 'PAY_ELEMENT_ENTRIES_F' table_name, count(*) rowcount FROM PAY_ELEMENT_ENTRIES_F
SELECT 'PAY_ELEMENT_ENTRY_VALUES_F' table_name, count(*) rowcount FROM PAY_ELEMENT_ENTRY_VALUES_F
SELECT 'PER_WORKING_HOUR_PATTERNS_F' table_name, count(*) rowcount FROM PER_WORKING_HOUR_PATTERNS_F
SELECT 'PER_ASSIGNMENT_EXTRA_INFO_M' table_name, count(*) rowcount FROM PER_ASSIGNMENT_EXTRA_INFO_M
SELECT 'PER_ASSIGNMENT_SUPERVISORS_F' table_name, count(*) rowcount FROM PER_ASSIGNMENT_SUPERVISORS_F
SELECT 'PER_PEOPLE_EXTRA_INFO_F' table_name, count(*) rowcount FROM PER_PEOPLE_EXTRA_INFO_F
SELECT 'HR_DOCUMENTS_OF_RECORD ' table_name, count(*) rowcount FROM HR_DOCUMENTS_OF_RECORD
SELECT 'PER_CONTACT_RELATIONSHIPS ' table_name, count(*) rowcount FROM PER_CONTACT_RELATIONSHIPS
a.rowcount <> 0
You can add/remove more tables based on data in your environment.
Normally the process takes around 5-6 hrs for 16-17K employees. Performance depends upon environment sizing as well among other factors.