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”
Below sample queries can be used to extract count of workers (Active/ Inactive) in HCM:
Query 1:
select count(distinct person_number)
from per_all_people_f papf
where 1=1
and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)
Query 2: Find count workers not having basic assignment details:
select (distinct person_number)
from per_all_people_f papf
where 1=1
and exists (select 1 from per_periods_of_service ppos where ppos.person_id = papf.person_id)
and person_number not in (SELECT papf.person_number
FROM per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_jobs_f_tl pjft,
hr_organization_units_f_tl hauft,
per_periods_of_service ppos
WHERE paam.person_id = ppnf.person_id
AND papf.person_id = ppnf.person_id
AND papf.person_id = paam.person_id
AND paam.job_id = pjft.job_id (+)
AND pjft.language(+) = 'US'
AND hauft.language (+) = 'US'
- -and papf.person_number = '414'
AND hauft.organization_id (+) = paam.organization_id
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 trunc(sysdate) BETWEEN ppnf.effective_start_date AND ppnf.effective_end_date
AND trunc(sysdate) BETWEEN hauft.effective_start_date(+) AND hauft.effective_end_date(+)
AND trunc(sysdate) BETWEEN pjft.effective_start_date(+) AND pjft.effective_end_date(+)
AND paam.effective_latest_change = 'Y'
AND paam.primary_flag = 'Y'
AND ppnf.name_type = 'GLOBAL'
AND paam.period_of_service_id = ppos.period_of_service_id
AND ppos.date_start = (select max(ppos2.date_start) from per_periods_of_service ppos2
where ppos2.person_id = ppos.person_id
and ppos2.primary_flag = 'Y')
)
On the responsive Position UI, the position screens shows the Pending Worker and Worker record as an incumbent and the FTE goes in negative.
Below SQL can be used to identify such persons:
SELECT paam.person_id, hapfv.name
FROM per_all_assignments_m paam
,hr_all_positions_f_vl hapfv
WHERE paam.position_id = hapfv.position_id
AND paam.assignment_type NOT LIKE '%T'
AND paam.assignment_status_type NOT LIKE 'INACTIVE'
AND TRUNC(SYSDATE) BETWEEN hapfv.effective_start_date AND hapfv.effective_end_date
AND TRUNC(SYSDATE) BETWEEN paam.effective_start_date AND paam.effective_end_date
GROUP BY paam.person_id, hapfv.name
HAVING COUNT(*) > 1
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
ReadyToConvert Flag on Work relationship is used to convert the Pending worker record to Employee record. If this flag is set to Y, it will appear in NewPerson Dashboard with Automatic conversion marked as Yes.
Run Convert Pending Workers Automatically Process will pick the pending worker and convert it into Worker.
Many a times while trying to use Post method using REST API, we encounter – ” The specified operation is not supported for the invoked HTTP method. Please check the URL and the headers.”
Cause of this error is incorrect header parameters/ missing header parameters while making a call to REST API.
To fix the issue:
Open Postman
Under Headers:
3. Set below values:
Key – Content-type
Value – application/vnd.oracle.adf.action+json
Once the header values are set, the error will go away:
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
METADATA|Location|SourceSystemOwner|SourceSystemId|LocationCode|LocationName|EffectiveStartDate|EffectiveEndDate|SetCode|ActiveStatus|AddressLine1|Country|Description|AddressLine2|AddressLine3|AddressLine4|Building|FloorNumber|PostalCode|Region1|Region2|Region3|TimezoneCode|TownOrCity|FLEX:PER_LOCATIONS_DF|dffType(PER_LOCATIONS_DF=Global Data Elements)
MERGE|Location|HRC_SQLLOADER|TEST_LOCATION_1|TEST_LOCATION|Test Location|1951/01/01|4712/12/31|COMMON|A|1 Churchill Place|GB|1 Churchill Place||||||||||Europe/London|London|Global Data Elements|Sample DFF value
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:
PersonDeliveryMethod child business object of Worker can be used to upload delivery preferences for a worker.
Below is sample HDL file:
METADATA|PersonDeliveryMethod|DeliveryMethodId|DateStart|DateEnd|PersonId|PersonNumber|PreferredOrder|CommDlvryAddress|CommDlvryMethod|CommDlvryFkId|AddressType|AddressLine1|PhoneType|PhoneNumber|EmailType|EmailAddress|SourceSystemOwner|SourceSystemId
MERGE|PersonDeliveryMethod||2023/01/01|4712/12/31||1234|1||NORMAL||HOME|Address Line 1|||||HRC_SQLLOADER|1234_HOME_Address Line 1
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
