Search for:
Aug 6 2023
Configuration – Load Announcements file in content server

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:

  1. 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”

5. Choose Simple Folio and Click on “Load Folio”:

6.

7. Under “Folio Elements”, click on Add:

Search for Content ID from step 3:

8. Click Next and Folio should be created:

9. Right Click on image and copy Image address.

Use that address in Annoucments.

Mandeep Gupta 0 Comments
Aug 6 2023
BIP – Queries to find count of workers in HCM

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')
                             )
Mandeep Gupta 0 Comments
Aug 6 2023
BIP – Query to find list of same person having same position multiple times

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
Mandeep Gupta 0 Comments
Aug 6 2023
Configuration – Modify existing system messages

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:

Configuration – Using User Interface Text
Mandeep Gupta 0 Comments
Jul 10 2023
HDL – Person EFF Information loaded using HDL is not visible in UI

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
Mandeep Gupta 0 Comments
Jul 9 2023
BIP – Extract Document Record File Link from Content Server

Below query can be used to extract the document record from content server.

SELECT papf.person_number		"Person Number",
       ppnf.first_name			"First Name",
       ppnf.last_name			"Last Name",
       fdt.file_name 			"Attached File Name",
       fdt.dm_version_number 	"Document Id",
       fdt.dm_document_id 		"UCM Content Id",
       (SELECT 'https://'||external_virtual_host
          FROM fusion.ask_deployed_domains
         WHERE deployed_domain_name = 'FADomain')
	   ||'/cs/idcplg?IdcService=GET_FILE' 
	   || chr(38) 
	   || 'dID='
       || fdt.dm_version_number
       || '&dDocName='
       || fdt.dm_document_id
       || '&allowInterrupt=1' 	"UCM File Link"  
  FROM per_all_people_f papf,
       per_person_names_f ppnf,
       hr_documents_of_record hdor,
       fnd_attached_documents fad,
       fnd_documents_tl fdt
 WHERE 1=1
   AND hdor.person_id = papf.person_id
   AND papf.person_id = ppnf.person_id
   AND hdor.documents_of_record_id = fad.pk1_value
   AND fad.document_id = fdt.document_id
   AND fdt.language = 'US'
   AND fad.entity_name = 'HR_DOCUMENTS_OF_RECORD'
   AND ppnf.name_type = 'GLOBAL'
   AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   AND trunc(sysdate) between ppnf.effective_start_date and ppnf.effective_end_date
 ORDER BY 1
Mandeep Gupta 0 Comments
Jul 9 2023
BIP – Suppress special characters

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

Output:

Mandeep Gupta 0 Comments
Jul 8 2023
Configuration – Hiding Print Me under Settings and Actions

Settings and Actions under user name provides a lot of options. Sometimes, the users request to remove unwanted options from this list:

These options can be removed from this list by activating a sandbox and adding tool – “Page Template Composer”:

Once you activate the Sandbox, click on Edit Global Page Template option under “Settings and Actions” and click on Global Page Template:

Click on the option you want to hide (Print Me) in this case:

Click on Settings and uncheck the visible checkbox:

Once the changes are done and you are happy with the changes, publish the sandbox.

Mandeep Gupta 0 Comments
Jul 8 2023
HDL – Sample file to create Pending Worker Record

Below is a sample file to create Pending Worker record using HDL:

METADATA|Worker|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate|DateOfBirth|CategoryCode|ActionCode|SourceSystemOwner|SourceSystemId
MERGE|Worker|2023/07/01|4712/12/31||2023/07/01|1990/05/12||ADD_PEN_WKR|HDL|TestEmp_123

METADATA|PersonName|EffectiveStartDate|EffectiveEndDate|PersonNumber|LegislationCode|NameType|FirstName|MiddleNames|LastName|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)
MERGE|PersonName|2023/07/01|4712/12/31||GB|GLOBAL|TestEmpFN|U|TestEmp|HDL|TestEmpName_123|TestEmp_123

METADATA|PersonLegislativeData|EffectiveStartDate|EffectiveEndDate|PersonNumber|LegislationCode|Sex|MaritalStatus|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)
MERGE|PersonLegislativeData|2023/07/01|4712/12/31||GB|M|M|HDL|TestEmpLegData_123|TestEmp_123

METADATA|WorkRelationship|PersonNumber|DateStart|WorkerType|ActionCode|LegalEmployerName|LegalEmployerSeniorityDate|EnterpriseSeniorityDate|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|ReadyToConvert
MERGE|WorkRelationship||2023/07/01|P|ADD_PEN_WKR|GB Legal Employer|||HDL|TestEmpWR_123|TestEmp_123|Y

METADATA|WorkTerms|AssignmentNumber|EffectiveStartDate|EffectiveEndDate|PersonNumber|EffectiveLatestChange|EffectiveSequence|LegalEmployerName|WorkerType|DateStart|AssignmentStatusTypeCode|BusinessUnitShortCode|ActionCode|PrimaryWorkTermsFlag|ProposedUserPersonType|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|PeriodOfServiceId(SourceSystemId)
MERGE|WorkTerms||2023/07/01|4712/12/31||Y|1|GB Legal Employer|P|2023/07/01|ACTIVE_PROCESS|GB Business Unit|ADD_PEN_WKR|Y|Member|HDL|TestEmpWT_123|TestEmp_123|TestEmpWR_123


METADATA|Assignment|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|WorkTermsNumber|AssignmentNumber|AssignmentStatusTypeCode|BusinessUnitShortCode|PersonNumber|WorkerType|DateStart|LegalEmployerName|PrimaryAssignmentFlag|ProposedUserPersonType|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|WorkTermsAssignmentId(SourceSystemId)|ProjectedStartDate
MERGE|Assignment|ADD_PEN_WKR|2023/07/01|4712/12/31|1|Y|||ACTIVE_PROCESS|GB Business Unit||P|2023/07/01|GB Legal Employer|Y|Member|HDL|TestEmpASG_123|TestEmp_123|TestEmpWT_123|2023/07/10

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.

Mandeep Gupta 0 Comments
Jul 8 2023
REST API Error –  The specified operation is not supported for the invoked HTTP method

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:

  1. Open Postman
  2. 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:

Mandeep Gupta 0 Comments
Jul 5 2023
HDL – Sample HDL file to create Role Provisioning Rules
METADATA|RoleMapping|RoleMappingId|MappingName|DateFrom|DateTo|LegalEmployerName|SystemPersonType|UserPersonType|AssignmentType|AssignmentStatus|SourceSystemId|SourceSystemOwner
MERGE|RoleMapping||Test HDL|1951/01/01|4712/12/31|Test Legal Employer|Employee|Employee|E|ACTIVE|RoleMapping_123|HRC_SQLLOADER


METADATA|Role|RoleMappingRoleId|RoleMappingId(SourceSystemId)|MappingName|RoleId|RequestableFlag|SelfRequestableFlag|UseForAutoProvisioningFlag|RoleCommonName|SourceSystemId|SourceSystemOwner
MERGE|Role||RoleMapping_123|Test HDL||N|N|Y|TEST_EMP_DATA|Role_123|HRC_SQLLOADER

The rules can be verified from UI, once the HDL load is successful:

Mandeep Gupta 0 Comments
Jul 2 2023
Configuration – Pull configured actions/action reasons

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
Mandeep Gupta 0 Comments
Jul 2 2023
HDL – Sample File to upload location DFF values
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
Mandeep Gupta 0 Comments
Jul 2 2023
Fusion Diagnostics – Important MOS notes related to diagnostics

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:

Diagnostics – Run Diagnostics is missing
Mandeep Gupta 0 Comments
Jul 2 2023
HDL – Loading Delivery Preferences for a Worker

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
Mandeep Gupta 0 Comments
Jul 2 2023
HDL – Loaded Salary Data Extract in HDL Format
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
Mandeep Gupta 0 Comments
Jun 26 2023
BIP – Extract Job Details

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
Mandeep Gupta 0 Comments