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
UNION
SELECT 'All Departments' dep_name
FROM DUAL
Value Set setup:
From Clause:
(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
,ppos.actual_termination_date
FROM PER_ALL_PEOPLE_F papf
,PER_PERIODS_OF_SERVICE ppos
,PER_USERS pu
,PER_USER_ROLES pur
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"
,HAPT.DESCRIPTION
,HRPB.TASK_FILE_NAME TaskName
,HRPB.TXN_MODULE_IDENTIFIER
,HAPT.CATEGORY_NAME
,HRPB.CATEGORY_CODE
,HAPT.SUBCATEGORY_NAME
,HRPB.SUBCATEGORY_CODE
,HRPB.FAMILY
,DECODE(HAAO.APPROVAL_DISABLED,'true','Bypassed','false','Enabled') "Enabled Status"
FROM FUSION.HRC_ARM_PROCESS_B HRPB
,FUSION.HRC_ARM_PROCESS_TL HAPT
,FUSION.HRC_ARM_APPROVAL_OPTIONS HAAO
WHERE HRPB.PROCESS_ID=HAAO.PROCESS_ID
AND HRPB.PROCESS_ID=HAPT.PROCESS_ID
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:
select fdv.file_name
,fdv.title
,dm_document_id
,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,
fnd_attached_documents fad,
fnd_documents_vl fdv,
fusion_ocserver11g.revisions wcc_revisions,
fusion_ocserver11g.documents wcc_documents
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
,fdv.title
,dm_document_id
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).
When creating a new announcement (Tools -> Announcements) , user has an option to upload images from local directory or a link can be given to a file uploaded in content server.
From local directory:
For content server link:
One needs to follow below steps in order to upload the image file to content server:
Login into content server. Suffix your pod url with /cs:
https://pod.fa.oracle.com/cs
2. Do a new check-in:
3. Click on “Check In”
4. Once the file is successfully checked-in. Click on “New Folio” Under “Content Management”
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:
SELECT pav.action_type_code
,pav.action_code
,pav.action_name
,parv.action_reason_code
,parv.action_reason
,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,
PER_ACTIONS_VL pav,
PER_ACTION_REASONS_VL parv
WHERE 1=1
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:
Sometimes accessing an HCM environment causes in “Max HTTP Headers, Blocked by WAF” error as shown below:
Resolution:
This issue occurs due to browser cache issue. Clearing the cache or trying to access the environment in incognito mode or another browser should resolve the issue.
There are requirements when the existing legal entity name should be updated to a new name. One should note that the LE name in UI is displayed for a view – HR_ALL_ORGANIZATION_UNITS_F_VL.
Sometimes, even after updating the name in UI, correct name is not reflected on some pages like Manage Employment or Manage Legal Entity Information for HCM.
In such cases, one should verify the value in HR_ALL_ORGANIZATION_UNITS_F_VL view to make sure the updated value is showing here.
If updated value is not available in this view, please follow below steps:
Search for legal entity from ‘Manage Legal Entity for HCM Information’ under My Client Groups -> Workforce Structures
Open the legal entity -> Click on action and choose correct
Submit the changes without doing any change.
This should force the name update to happen in HR_ALL_ORGANIZATION_UNITS_F_VL view.
With latest HCM release in 2022, “Run Diagnostics” option was removed from user profile. A new role is now required in order to give access to this function.
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
Make sure Save is set to Y to purge the records from DB.
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 a.*
FROM
(
SELECT 'ANC_PER_ABS_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ABS_ENTRIES
UNION
SELECT 'ANC_PER_ABS_ENTRY_DTLS' table_name, count(*) rowcount FROM ANC_PER_ABS_ENTRY_DTLS
UNION
SELECT 'ANC_PER_ABS_MATERNITY' table_name, count(*) rowcount FROM ANC_PER_ABS_MATERNITY
UNION
SELECT 'ANC_PER_PLAN_ENROLLMENT' table_name, count(*) rowcount FROM ANC_PER_PLAN_ENROLLMENT
UNION
SELECT 'ANC_PER_ABS_PLAN_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ABS_PLAN_ENTRIES
UNION
SELECT 'ANC_PER_ACCRUAL_ENTRIES' table_name, count(*) rowcount FROM ANC_PER_ACCRUAL_ENTRIES
UNION
SELECT 'ANC_PER_ACRL_ENTRY_DTLS' table_name, count(*) rowcount FROM ANC_PER_ACRL_ENTRY_DTLS
UNION
SELECT 'CMP_SALARY' table_name, count(*) rowcount FROM CMP_SALARY
UNION
SELECT 'PAY_ASSIGNED_PAYROLLS_DN' table_name, count(*) rowcount FROM PAY_ASSIGNED_PAYROLLS_DN
UNION
SELECT 'PAY_ASSIGNED_PAYROLLS_F' table_name, count(*) rowcount FROM PAY_ASSIGNED_PAYROLLS_F
UNION
SELECT 'PER_ADDRESSES_F' table_name, count(*) rowcount FROM PER_ADDRESSES_F
UNION
SELECT 'PER_ALL_ASSIGNMENTS_M' table_name, count(*) rowcount FROM PER_ALL_ASSIGNMENTS_M
UNION
SELECT 'PER_ALL_PEOPLE_F' table_name, count(*) rowcount FROM PER_ALL_PEOPLE_F
UNION
SELECT 'PER_ASSIGN_WORK_MEASURES_F' table_name, count(*) rowcount FROM PER_ASSIGN_WORK_MEASURES_F
UNION
SELECT 'PER_CITIZENSHIPS' table_name, count(*) rowcount FROM PER_CITIZENSHIPS
UNION
SELECT 'PER_DRIVERS_LICENSES' table_name, count(*) rowcount FROM PER_DRIVERS_LICENSES
UNION
SELECT 'PER_EMAIL_ADDRESSES' table_name, count(*) rowcount FROM PER_EMAIL_ADDRESSES
UNION
SELECT 'PER_ETHNICITIES' table_name, count(*) rowcount FROM PER_ETHNICITIES
UNION
SELECT 'PER_NATIONAL_IDENTIFIERS' table_name, count(*) rowcount FROM PER_NATIONAL_IDENTIFIERS
UNION
SELECT 'PER_PASSPORTS' table_name, count(*) rowcount FROM PER_PASSPORTS
UNION
SELECT 'PER_PEOPLE_LEGISLATIVE_F' table_name, count(*) rowcount FROM PER_PEOPLE_LEGISLATIVE_F
UNION
SELECT 'PER_PERIODS_OF_SERVICE' table_name, count(*) rowcount FROM PER_PERIODS_OF_SERVICE
UNION
SELECT 'PER_PERSON_ADDR_USAGES_F' table_name, count(*) rowcount FROM PER_PERSON_ADDR_USAGES_F
UNION
SELECT 'PER_PERSON_NAMES_F' table_name, count(*) rowcount FROM PER_PERSON_NAMES_F
UNION
SELECT 'PER_PERSON_TYPE_USAGES_M' table_name, count(*) rowcount FROM PER_PERSON_TYPE_USAGES_M
UNION
SELECT 'PER_PERSONS' table_name, count(*) rowcount FROM PER_PERSONS
UNION
SELECT 'PER_PHONES' table_name, count(*) rowcount FROM PER_PHONES
UNION
SELECT 'PER_RELIGIONS' table_name, count(*) rowcount FROM PER_RELIGIONS
UNION
SELECT 'PER_VISAS_PERMITS_F' table_name, count(*) rowcount FROM PER_VISAS_PERMITS_F
UNION
SELECT 'PAY_ELEMENT_ENTRIES_F' table_name, count(*) rowcount FROM PAY_ELEMENT_ENTRIES_F
UNION
SELECT 'PAY_ELEMENT_ENTRY_VALUES_F' table_name, count(*) rowcount FROM PAY_ELEMENT_ENTRY_VALUES_F
UNION
SELECT 'PER_WORKING_HOUR_PATTERNS_F' table_name, count(*) rowcount FROM PER_WORKING_HOUR_PATTERNS_F
UNION
SELECT 'PER_ASSIGNMENT_EXTRA_INFO_M' table_name, count(*) rowcount FROM PER_ASSIGNMENT_EXTRA_INFO_M
UNION
SELECT 'PER_ASSIGNMENT_SUPERVISORS_F' table_name, count(*) rowcount FROM PER_ASSIGNMENT_SUPERVISORS_F
UNION
SELECT 'PER_PEOPLE_EXTRA_INFO_F' table_name, count(*) rowcount FROM PER_PEOPLE_EXTRA_INFO_F
UNION
SELECT 'HR_DOCUMENTS_OF_RECORD ' table_name, count(*) rowcount FROM HR_DOCUMENTS_OF_RECORD
UNION
SELECT 'PER_CONTACT_RELATIONSHIPS ' table_name, count(*) rowcount FROM PER_CONTACT_RELATIONSHIPS
) a
where
a.rowcount <> 0
You can add/remove more tables based on data in your environment.
Stats:
Normally the process takes around 5-6 hrs for 16-17K employees. Performance depends upon environment sizing as well among other factors.
