There are times when we just need a quick query to count number of positions created in HCM against a department. Below is a sample SQL which can be handy in such situations:
select *
from hr_all_positions_f hapf
where hapf.organization_id IN (select distinct organization_id
from HR_ORGANIZATION_UNITS_F_TL houft
where (houft.name LIKE 'Test%'
OR houft.name LIKE 'Abc%'
)
)
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:
There are different requirements where one wants to get number of active/inactive work relationships in a legal entity.
Below SQL query can be run to get these counts:
select ple.name, count(ppos.period_of_Service_id) number_of_wrs
from per_legal_employers ple
,per_periods_of_service ppos
where ppos.legal_entity_id = ple.organization_id
group by ple.name
order by 1
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:
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:
Select 'METADATA|Salary|AssignmentNumber|SalaryAmount|DateFrom|DateTo|SalaryBasisId|SalaryId|SourceSystemId|SourceSystemOwner|ActionCode|ActionReasonCode' Header, 1 data_flow_order
from dual
UNION
SELECT 'MERGE|Salary'||'|'||
paam.assignment_number||'|'||
SALARY_AMOUNT||'|'||
TO_CHAR(cs.date_from,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
TO_CHAR(cs.date_to,'RRRR/MM/DD', 'nls_date_language=American')||'|'||
cs.salary_basis_id||'|'||
cs.salary_id||'|'||
hikm.source_system_id||'|'||
source_system_owner||'|'||
pav.action_code||'|'||
'XX_ANNL_REVIEW' data_row,
2 data_flow_order
FROM cmp_salary cs,
per_all_assignments_m paam,
hrc_integration_key_map hikm,
PER_ACTIONS_VL pav,
PER_ACTION_REASONS_VL parv
WHERE cs.assignment_id= paam.assignment_id
AND trunc(sysdate) between paam.effective_start_date AND paam.effective_end_date
AND paam.assignment_type not like '%T'
AND cs.salary_id = hikm.surrogate_id
and cs.action_id = pav.action_id
and cs.action_reason_id = parv.action_reason_id
and parv.action_reason_code = 'CMP_ANNV'
ORDER BY data_flow_order
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.
Once Jobs data is loaded/created, you can use below SQL to extract the setup data for Jobs:
Select pjft.name
,pjf.job_code
,TO_CHAR(pjf.effective_start_date,'YYYY/MM/DD') start_date
,TO_CHAR(pjf.effective_end_date,'YYYY/MM/DD') end_Date
,(select add_set.set_code
from fnd_setid_sets add_set
where add_set.set_id = pjf.set_id
and add_set.language = USERENV('LANG')) set_code
,pjf.active_status
from per_jobs_f pjf,
per_jobs_f_tl pjft
where 1=1
and pjf.job_id = pjft.job_id
and pjft.language = USERENV('LANG')
and trunc(SYSDATE) between pjft.effective_start_date and pjft.effective_end_date
and trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
ORDER BY 2,4
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.
SELECT poga.object_id
,petft.element_name
,poga.include_or_exclude
FROM pay_object_groups pog
,pay_object_group_amends poga
,pay_element_types_tl petft
WHERE poga.object_group_id = pog.object_group_id
AND pog.base_object_group_name='Test Element Set'
AND petft.element_type_id = poga.object_id
AND petft.language = 'US'
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 HCM allows an Organization to be classified as multiple Orgs. However, sometime there is a need to inactivate one org classification while keeping the primary classification as Active.
Below is a sample HDL file which can be used for this purpose:
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;
