Search for:
BIP – Deliver an encrypted file on UCM server

There is a common requirement to send encrypted output files to UCM server as part of outbound integrations in Fusion HCM. By default, FA_UCM_PROVISIONED is delivered as ‘Content Server’. But the delivered content server, doesn’t have the encryption enabled and there is no option to enable encryption on the delivered content server.

So, the solution here is to create a copy of delivered content server with same parameters with encryption enabled.

Choose the PGP key and click on ‘Test Connection’ button. Your new content server is now ready to use. If you have multiple vendors, needing to pick files from content server and each of them is having their own PGP key, you will need to create an individual content server for each vendor.

BIP – Query to extract worker manager job name

Below SQL query below shows how a job name can be pulled for a worker’s manager. The SQL can be easily modified to include further employment related attributes for worker’s line manager.

Select papfemp.person_number
, ppnfemp.first_name person_first_name
, ppnfemp.last_name person_last_name
, paamemp.assignment_number person_assignment
, to_char(pasf.effective_start_date, 'YYYY-MM-DD') effective_start_date
, to_char(pasf.effective_end_date, 'YYYY-MM-DD') effective_end_date
, papf_mgr.person_number manager_number
, ppnf_mgr.first_name manager_first_name
, ppnf_mgr.last_name manager_last_name
, paam_mgr.assignment_number manager_assignment
, pjft.name mgr_job_name
, pasf.manager_type
From per_all_people_f papfemp
, per_person_names_f ppnfemp
, per_all_assignments_m paamemp
, per_all_people_f papf_mgr
, per_person_names_f ppnf_mgr
, per_all_assignments_m paam_mgr
, per_jobs_f_tl pjft
, per_assignment_supervisors_f pasf
Where ppnfemp.person_id = papfemp.person_id
And ppnfemp.name_type = 'GLOBAL'
And paamemp.person_id = papfemp.person_id
And paamemp.assignment_type In ('E', 'C', 'N')
And paamemp.effective_latest_change = 'Y'
And paamemp.effective_start_date Between papfemp.effective_start_date And papfemp.effective_end_date
And paamemp.effective_start_date Between ppnfemp.effective_start_date And ppnfemp.effective_end_date
And TRUNC(SYSDATE) Between paamemp.effective_start_date And paamemp.effective_end_date
And paamemp.effective_start_date Between pasf.effective_start_date And pasf.effective_end_date
And ppnf_mgr.person_id = papf_mgr.person_id
And ppnf_mgr.name_type = 'GLOBAL'
And paam_mgr.person_id = papf_mgr.person_id
And paam_mgr.assignment_type In ('E', 'C', 'N')
And paam_mgr.effective_latest_change = 'Y'
And paam_mgr.effective_start_date Between papf_mgr.effective_start_date And papf_mgr.effective_end_date
And paam_mgr.effective_start_date Between ppnf_mgr.effective_start_date And ppnf_mgr.effective_end_date
And paamemp.effective_start_date Between paam_mgr.effective_start_date And paam_mgr.effective_end_date

And pasf.person_id = papfemp.person_id
And pasf.assignment_id = paamemp.assignment_id
And pasf.manager_id = papf_mgr.person_id
And pasf.manager_assignment_id = paam_mgr.assignment_id
AND paam_mgr.job_id = pjft.job_id
AND pjft.language = 'US'
AND trunc(sysdate) BETWEEN pjft.effective_start_date AND pjft.effective_end_date
BIP – Find list of message sent from Oracle Alert

Alert composer a very powerful and useful tool to configure and sent notifications to the users. However, it is very important for business to know the list of receipients for each message. I have tried to built a query to get these details.

SELECT hat.name alert_name
      ,harr.sent_to
      ,harr.sent_on
 FROM HRC_ALERTS_TL hat
     ,HRC_ALERT_RUNS har
     ,HRC_ALERT_RUN_MESSAGES harm
     ,HRC_ALERT_RUN_RECIPIENTS harr
WHERE hat.name = 'Learning Assignment Alert'
  AND hat.alert_id = har.alert_id
  AND hat.language = 'US'
  AND har.created_by = '[email protected]'
  AND TRUNC(har.creation_date) = TRUNC(SYSDATE)
  AND har.run_id = harm.run_id
  AND harm.run_id = harr.run_id
  AND harm.run_message_id = harr.run_message_id
  ORDER BY harr.sent_to

From alert history, one can find the emails triggered but it doesn’t give an option to export this. This is where the query is helpful.

HDL – How to encrypt a file locally
  • Navigate to Navigator -> Tools -> Security Console -> Certificates
  • Look for – ‘fusion-key’ and from actions button export the public key.
  • Open command prompt from Downloads folder.
  • Type following command to import fusion-key
gpg –import fusion-key_pub.asc
  • Once the key is imported. Use the command below to encrypt your HDL file (please place the JobFamily_userKeys.zip file in Downloads folder).
gpg –cipher-algo AES-256 -r fusion-key –encrypt JobFamily_userKeys.zip

** You can use any other folder as well. Just you need to point to the right directory.

  • An encrypted file with extension .gpg will be generated in the same folder.

Encrypted File Name – JobFamily_userKeys.zip.gpg

  • HCM Data Loader accepts only .zip files, so the newly created .gpg file should be renamed to .zip again (by removing .gpg extension).
  • Try to open the .zip file by double clicking and you should get an error as windows can’t open encrypted files directly.
  • Navigate to My Client Groups -> Data Exchange -> Import and Load -> choose the file.
  • Before submitting, review the parameters by clicking on Review Parameters.
  • Choose the File Encryption as ‘PGP – Unsigned’ and submit the load.
  • File will be decrypted and processed successfully.
HDL – Convert Contingent Workers to Workers

There are scenario’s where customers want to convert their non permanent staff to permanent staff in bulk or vice versa. In HCM terminology, this causes changes in system person type of a person. Let us take an example where a customer wants to hire all the contingent workers working in the company as permanent staff.

As mentioned earlier, converting a contingent worker into worker/employee person type will change the system person type of the person. This is a two step process:

  1. Terminate all the CWK records.
  2. Rehire them as workers.

Performing these steps manually for 100+ records will be a tough task and is error prone process. So, it is better to use HDL for this.

First, we will terminate CWK records. Below is the sample file for same:

METADATA|WorkRelationship|PeriodOfServiceId|LegalEmployerName|DateStart|PersonId|WorkerType|ActualTerminationDate|ActionCode|TerminateWorkRelationshipFlag

MERGE|WorkRelationship|300012012120031|Test Legal Employer|2023/01/01|30001212121223|C|2024/05/08|TERMINATE_PLACEMENT|Y

Below sample SQL can be used to get the various IDs:

SELECT PERIOD_OF_SERVICE_ID, PERSON_ID,DATE_START, PERIOD_TYPE, LEGAL_ENTITY_ID
  FROM PER_PERIODS_OF_SERVICE

Once the termination file is loaded successully, we need to rehire the persons as Worker. Sample file for REHIRE:

METADATA|WorkRelationship|PersonNumber|LegalEmployerName|PrimaryFlag|DateStart|WorkerType
MERGE|WorkRelationship|1234|Test Legal Employer|Y|2024/05/09|E

METADATA|WorkTerms|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentStatusTypeCode|AssignmentType|BusinessUnitShortCode|PrimaryWorkTermsFlag|LegalEmployerName|PersonNumber|DateStart|WorkerType|AssignmentNumber
MERGE|WorkTerms|REHIRE|2024/05/09|4712/12/31|1|Y|ACTIVE_PROCESS|ET|Test BU|Y|Test Legal Employer|1234|2024/05/09|E|ET1234

METADATA|Assignment|ActionCode|EffectiveStartDate|EffectiveEndDate|EffectiveSequence|EffectiveLatestChange|AssignmentStatusTypeCode|AssignmentType|BusinessUnitShortCode|PrimaryAssignmentFlag|PersonNumber|AssignmentCategory|DateStart|WorkerType|LegalEmployerName|AssignmentNumber|WorkTermsNumber
MERGE|Assignment|REHIRE|2024/05/09|4712/12/31|1|Y|ACTIVE_PROCESS|E|Test BU|Y|1234|FR|2024/05/09|E|Test Legal Employer|E1234|ET1234

Please note for rehire, only Work Relationship, Work Terms and Assignment METADATA are required.

HDL – Sample file to upload Department DFF attributes

Sample file to upload/update/correct DFF values on department using User Keys:

METADATA|Organization|Name|ClassificationName|EffectiveStartDate|EffectiveEndDate|FLEX:PER_ORGANIZATION_UNIT_DFF|testAttribute(PER_ORGANIZATION_UNIT_DFF=Global Data Elements)

MERGE|Organization|Test Organization|Department|1951/01/01|4712/12/31|Global Data Elements|Xyz

METADATA|OrgUnitClassification|OrganizationName|ClassificationName|SetCode|EffectiveStartDate|EffectiveEndDate

MERGE|OrgUnitClassification|Test Organization|Department|COMMON|1951/01/01|4712/12/31
HDL – Update Assignment Attributes with User Keys

Below is an example of updating assignment category using only user keys:

METADATA|WorkTerms|AssignmentNumber|PersonNumber|LegalEmployerName|DateStart|WorkerType|ActionCode|ReasonCode|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence
MERGE|WorkTerms|ET000123|000123|US Inc Legal Employer|2014/04/07|E|LOCATION_CHANGE||2023/04/28|4712/12/31|Y|1

METADATA|Assignment|AssignmentNumber|PersonNumber|LegalEmployerName|DateStart|WorkerType|ActionCode|ReasonCode|WorkTermsNumber|EffectiveStartDate|EffectiveEndDate|EffectiveLatestChange|EffectiveSequence|AssignmentCategory
MERGE|Assignment|E000123|000123|US Inc Legal Employer|2014/04/07|E|LOCATION_CHANGE||ET000123|2023/04/28|4712/12/31|Y|1|FR

If you want to use surrogate IDs/ integration keys, then you can check the below post:

BIP – Query to get Job Profile Details
SELECT pjf.job_code
      ,hpb.profile_code
      ,hpt.description profile_desc
      ,hpt.summary
      ,hpeiv.DESCRIPTION
      ,hpeiv.RESPONSIBILITIES
      ,hpeiv.QUALIFICATIONS
  FROM HRT_PROFILE_ITEMS hpi
      ,HRT_PROFILES_B hpb
      ,HRT_PROFILES_TL hpt
      ,HRT_PROFILE_RELATIONS hpr
      ,HRT_PROFILE_EXTRA_INFO_VL hpeiv
      ,PER_JOBS_F pjf
 WHERE hpi.profile_id = hpb.profile_id
   AND hpb.profile_usage_code = 'M'
   AND hpi.profile_id = hpr.profile_id
   AND hpi.profile_id = hpt.profile_id
   AND hpi.profile_id = hpeiv.profile_id
   AND pjf.job_id = hpr.object_id
   AND trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_Date
   AND pjf.job_code= '1099'
   AND hpt.language = 'US'
BIP – Extract Job details with Job Family and Job Function details
SELECT pjf.job_id
     , pjf.job_code
     , pjft.name
     , pjffv.job_family_name
     , pjf.effective_start_date
     , pjf.job_function_code
     , hikm.source_system_id
     , hikm.source_system_owner
  FROM per_jobs_f pjf
     , per_jobs_f_tl pjft
     , per_job_family_f_vl pjffv
     , hrc_integration_key_map hikm
 WHERE pjf.job_id = hikm.surrogate_id
   AND pjft.job_id = pjf.job_id
   AND trunc(sysdate) between pjf.effective_start_date and pjf.effective_end_date
   AND trunc(sysdate) between pjft.effective_start_date and pjft.effective_end_date
   AND trunc(sysdate) between pjffv.effective_start_date and pjffv.effective_end_date
   AND pjft.language = 'US'
   AND pjffv.job_family_id = pjf.job_family_id

List of all job families not associated with a Job:

SELECT *
  FROM per_job_family_f_vl pjffv
 WHERE 1=1
   AND NOT EXISTS (SELECT 1
                     FROM per_jobs_f pjf
		    WHERE pjffv.job_family_id = pjf.job_family_id)
HDL – Sample file to load Licenses and Certifications to employee profile

Sample file to load licenses and certifications to employee profile:

METADATA|TalentProfile|ProfileCode|PersonNumber|ProfileId|SourceSystemOwner|SourceSystemId
MERGE|TalentProfile|PERS_300000123456789|1234|300000123456789|HRC_SQLLOADER|PERS_300000123456789

METADATA|ProfileItem|ProfileId|ProfileCode|SectionId|ContentTypeId|ContentItem|DateFrom|DateTo|RatingModelCode1|RatingLevelCode1|RatingModelCode2|RatingLevelCode2|RatingModelCode3|RatingLevelCode3|ItemText301|ItemText302|ItemText303|SourceSystemOwner|SourceSystemId
MERGE|ProfileItem|300000123456789|PERS_300000123456789|9989|103|Oracle Global HR|2024/07/01|||||||||||HRC_SQLLOADER|HRC_SQLLOADER_PERS_300000123456789_Oracle Global HR

Sample useful queries:

Query to get source system ID and owner details for existing profiles:

select hrb.profile_id
     , hikm.source_system_id 
	 , hikm.source_system_owner 
  from HRT_PROFILES_ITEMS hrb,
       HRC_INTEGRATION_KEP_MAP hikm
 where hrb.profile_id = hikm.surrogate_id

Query to get profile id for a worker:

select papf.person_number
     , hrb.profile_id  
     , hrb.profile_code 
  from HRT_PROFILES_B hrb,
       PER_ALL_PEOPLE_F papf
 where papf.PERSON_ID =  hrb.PERSON_ID
   and TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE
   and papf.person_number = <>
Security – Deactivating Users in Bulk

With the security concerns regarding access to Oracle HCM applications, I have seen many customers asking for a way to restrict access to a particular DEV/TEST environment having unmasked data. One option in such scenario’s is to keep only the admin user accounts active in the particular environment and deactivate all other user accounts. This way, the user roles data is kept intact and access is restricted to only a set of limited users.

Let us now understand, the kind of users which can exist in Fusion HCM environment. There can be system users (seeded), service accounts, worker accounts (users tied to a person), standalone user accounts (for vendors/ SI partners). So, it is really important to filter the right set of user accounts which should be deactivated. Also, the method of deactivation can vary depending upon the type of user.

Bulk deactivation of users can be performed using either HDL or by using SCIM REST API. While HDL is bulk data upload tool but it has its own set of limitations. HDL can’t be used to deactivate standalone users i.e. the users which don’t have an associated person record. To deactivate standalone users, REST API should be used.

I will discuss both the approaches in details. Let us first find a way to store the admin user accounts which should remain active. My preferred way of doing this is to create a Common Lookup and add the details (user names) in this lookup. This is because lookup values can be updated easily using a spreadsheet loader.

Below is the sample lookup (XX_ACTIVE_USER_ACCOUNTS) which I created to store the admin user names:

Next step is to add the user accounts in the meaning attribute:

Two user accounts – [email protected] and [email protected] have been added. The next steps will be to filter these record from the deactivation steps.

Let us now discuss the first approach which is to deactive user accounts using HDL. Below SQL query can be used to get a list of all required active user accounts in User.dat HDL format:

SELECT 'METADATA|User|UserId|Suspended' datarow
      ,1 seq
  FROM DUAl
 UNION
SELECT 'MERGE|User|'
       || pu.user_id
       || '|Y' datarow
      ,2 seq 
  FROM per_users pu
 WHERE pu.person_id IS NOT NULL
   AND pu.created_by NOT IN ('anonymous')
   AND pu.username NOT LIKE 'FUSION%APPS%'
   AND pu.username NOT IN ('AIACS_AIAPPS_LHR_STAGE_APPID','FAAdmin','FAWService','FAWService_APPID','FIISUSER','HCMSI-98f0f163a79a46c58fa4572e41fac8ed_scim_client_APPID','IDROUser','IDRWUser',						'OCLOUD9_osn_APPID','PSCR_PROXY_USER','PUBLIC','app_monitor1','app_monitor',						  'em_monitoring2','fa_monitor','faoperator','oamAdminUser','puds.pscr.anonymous.user','weblogic_idm','anonymous'
)						   
  AND pu.suspended = 'N'
  AND lower(pu.username) NOT IN (SELECT lower(flv.meaning)
                                    FROM fnd_lookup_values flv
                                   WHERE flv.lookup_type = 'XX_ACTIVE_USER_ACCOUNTS'
                                     AND flv.language = 'US'
                                     AND flv.enabled_flag = 'Y'
                                  )									 
ORDER BY seq

So, the above query will return only those active user accounts which are attached to a person record and don’t exist in the custom lookup XX_ACTIVE_USER_ACCOUNTS.

**Suspended Flag in PER_USERS table indicate if the user is active (N) or inactive (Y).

Next step is to create a BIP data model and a report and save the output data in excel format. From excel, copy the data in a Notepad and save the file as User.dat.

Sample Output in excel format:

Copy the data except for “DATAROW” and paste it in a Notepad. Save the file as User.dat:

zip the User.dat file and upload it in HCM using Data Exchange -> Import and Load.

Once the load is successful, please run – ‘Send Pending LDAP Requests’ ESS job. This should deactivate all the extracted users.

You can run quick queries on per_users to make sure that the user accounts have been deactivated.

Second approach is to use SCIM REST API to bulk deactivate user accounts. I recommend to use this approach only for those users where no person record is attached to the user account.

Please check below MOS note for details on the step by step instructions on SCIM REST API:

Fusion Security: Using SCIM REST API (Doc ID 2346455.1)

Please note that in order to run this REST API, the user should have – IT Security Manager role.

Sample url to bulk deactivate users :-

https://fa-abcdef-dev-saasfaprod1.fa.ocs.oraclecloud.com/hcmRestApi/scim/Bulk
Sample Payload:

{
"Operations":[
{
"method":"PATCH",
"path":"/Users/0453A72EE08D419BE0631078680AA831",
"bulkId":"100000001",
"data":{
"schemas":[
"urn:scim:schemas:core:2.0:User"
],
"active":false
}
},
{
"method":"PATCH",
"path":"/Users/0453A72EE08D419BE0631078612AA832",
"bulkId":"100000001",
"data":{
"schemas":[
"urn:scim:schemas:core:2.0:User"
],
"active":false
}
}
]
}

Please note (taken from above Oracle note):

The bulkId attribute value should be set to UNIQUE value, while creating user accounts in BULK. This is required as per IETF SCIM Specifications while creating new resources using POST method. You may use a common value for the bulkId attribute while using PATCH, DELETE, PUT methods in a Bulk operation.

The main challenge with this approach is to get the correct JSON Payload for multiple users from system. I have created a BIP report for this which will generate the output data in required JSON format. Below is the sample code:

SELECT '{
"Operations":['
 data_row, 1 seq 
  FROM DUAL
UNION
SELECT 
'{
"method":"PATCH",
"path":"/Users/'
||pu.user_guid||
'",
"bulkId":"1000000000001",
"data":{
"schemas":[
"urn:scim:schemas:core:2.0:User"
],
"active":false
}
},' data_row, 2 seq
 FROM per_users pu
 WHERE pu.person_id IS NOT NULL
   AND pu.created_by NOT IN ('anonymous')
   AND pu.username NOT LIKE 'FUSION%APPS%'
   AND pu.username NOT IN ('AIACS_AIAPPS_LHR_STAGE_APPID','FAAdmin','FAWService','FAWService_APPID','FIISUSER',
                           'HCMSI-98f0f163a79a46c58fa4572e41fac8ed_scim_client_APPID','IDROUser','IDRWUser',
						   'OCLOUD9_osn_APPID','PSCR_PROXY_USER','PUBLIC','app_monitor1','app_monitor',
						   'em_monitoring2','fa_monitor','faoperator','oamAdminUser','puds.pscr.anonymous.user',
						   'weblogic_idm','anonymous'
						   )						   
   AND pu.suspended = 'N'
   AND lower(pu.username) NOT IN (SELECT lower(flv.meaning)
                                    FROM fnd_lookup_values flv
                                   WHERE flv.lookup_type = 'XX_ACTIVE_USER_ACCOUNTS'
                                     AND flv.language = 'US'
                                     AND flv.enabled_flag = 'Y'
                                  )									 
UNION
SELECT '
]
}' data_row, 3 seq
  FROM dual
ORDER BY seq

You can create a BIP data model and report to get data from this query. Extract the data in excel format and copy it to a notepad. Then you need to remove the highlighted comma in order for this JSON payload to work.

You can use SOAP UI/Postman to run the REST API and provide the output from Notepad as JSON input. Once the API runs successfully, the suspended flag will get changed to Y in per_users table.

BIP – Query to get Benefits Relationship details

Use below SQL to get benefits relationship name and status for a worker:

SELECT papf.person_number
      ,paam.assignment_number
      ,houft.name legal_entity_name
      ,bbrf.benefit_relation_name
      ,bbrf.status
      ,TO_CHAR(bbrf.effective_start_date,'YYYY/MM/DD') effective_start_date
      ,TO_CHAR(bbrf.effective_end_date,'YYYY/MM/DD') effective_end_date 
  FROM per_all_people_f papf 
      ,per_all_assignments_m paam  
      ,ben_benefit_relations_f bbrf
      ,hr_organization_units_f_tl houft
 WHERE 1 =1 
   AND bbrf.person_id = papf.person_id 
   AND paam.person_id = papf.person_id 
   AND bbrf.rel_prmry_asg_id = paam.assignment_id   
   AND bbrf.legal_entity_id = houft.organization_id 
   AND houft.LANGUAGE = 'US'
   AND paam.assignment_type NOT LIKE '%T'
   AND paam.effective_latest_change = 'Y'
   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
 ORDER BY papf.person_number,paam.assignment_number,bbrf.effective_start_date
BIP – Error Exception happened when delivery document to wcc

Many a times when the reports are delivering the content to UCM server, we get below error:

Document delivery failed
[INSTANCE_ID=bip.bi_server1] [DELIVERY_ID=-1]Exception happened when delivery document to wcc
deliver API call throw ProcessingException

WCC delivery failed with Exception
WCC delivery failed with DeliveryException
oracle.xdo.service.delivery.DeliveryException: oracle.j2ee.ws.client.jaxws.JRFSOAPFaultException: Client received SOAP Fault from server : InvalidSecurity : error in processing the WS-Security security header
oracle.xdo.service.delivery.DeliveryException: oracle.xdo.service.delivery.DeliveryException: oracle.j2ee.ws.client.jaxws.JRFSOAPFaultException: Client received SOAP Fault from server : InvalidSecurity : error in processing the WS-Security security header
at oracle.xdo.service.delivery.impl.DeliveryServiceImpl.deliverToWCC(DeliveryServic

In this case look at the password for account used to connect to UCM server. If the password is reset make sure it is updated at the UCM connection as well.

HDL – Loading Person Profile Items

In order to load person profile items, a person should have a profile code. There are two ways to create the profile code for a person record.

  1. From UI :- When any business user clicks on Talent Profile for a worker, a profile code is automatically generated in the backend. Profile code is not visible in UI and is always stored in the backend.
  2. Using HDL :- Profile codes can be loaded in bulk using TalentProfile.dat business object of HCM Data Loader (HDL).

It is advisable to load profile codes in bulk as part of data migration using HDL. But there are cases where a user will click on Talent Profile of a worker just after migrating the Core data for the worker. In this case, a profile code is generated in HRT_PROFILES_B table. So, when TalentProfile.dat is used, the profile record will fail for this particular worker.

Below SQL can be used to get list of all active workers who don’t have a talent profile code yet:

select * 
  from per_all_people_f papf
     , per_periods_of_service ppos
 where papf.person_id = ppos.person_id
   and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
   and ppos.actual_termination_date is NULL
   and not exists (select 1 from HRT_PROFILES_B hpb where papf.person_id = hpb.person_id )
HDL – Sample file to end date element entry value

All recurring element entires are loaded/ created with an effective end date of 31-Dec-4712. But I have seen scenarios, where the business have requirements to end date an element entry as of a certain date. To achieve this in Bulk, one can use ElementEntry.dat HDL business object.

Let us take an example, where an employee has an element entry with an effective end date of 31-Dec-4712:

The effective end date is set to blank which is equivalent to 31-Dec-4712 in the backend table.

So, let us assume the business has requested to end date this particular element as of 31-Jan-2024 for all employees. To achieve, this we need to pull the existing data from element entries table for this particular element. Below SQL can be used to get the ID values and to verify the results before and after HDL load:

SELECT DISTINCT peevf.element_entry_value_id
       ,peef.element_entry_id
       ,petf.base_element_name
       ,peef.effective_start_date ele_sd
       ,peef.effective_end_date ele_ed
       ,peevf.effective_start_date
       ,peevf.effective_end_date
       ,paam.assignment_number
   FROM per_all_assignments_m paam
       ,pay_element_types_f petf
       ,pay_element_entries_f peef
       ,pay_element_entry_values_f peevf
 WHERE 1=1
   AND paam.person_id = peef.person_id
   AND peef.element_type_id = petf.element_type_id
   AND peef.element_entry_id = peevf.element_entry_id
   AND paam.ASSIGNMENT_TYPE in ('E')
   AND paam.primary_assignment_flag = 'Y'
   AND petf.base_element_name = 'Test XYZ Bonus'
   AND paam.assignment_number = 'E2121212'
   AND trunc(sysdate) between petf.effective_start_date and petf.effective_end_date
   AND trunc(sysdate) between paam.effective_start_date and paam.effective_end_date

A normal HDL file with new effective end date will just create a date split in the data. To avoid this a new attribute called ReplaceLastEffectiveEndDate should be added in the HDL file which will updated the effective end date from 31-Dec-4712 to 31-Jan-2024.

METADATA|ElementEntry|AssignmentNumber|ElementName|EffectiveStartDate|EffectiveEndDate|LegislativeDataGroupName|MultipleEntryCount|EntryType|ReplaceLastEffectiveEndDate

MERGE|ElementEntry|E2121212|Test XYZ Bonus|2012/01/31|2024/01/31|GB Legislative Data Group|1|E|Y

Once this HDL is run successfully, the effective end date will get updted.

BIP – Extract Address Style Format Information

You use “Manage Address Formats” task from Setup and Maintenance to define the address style for a particular country. Oracle provides a lot of address format for various countries out of the box. You have the capaibility to customize the address formats as per customer requirement.

For example, you can search for all available address style for United Kingdom using country filter:

You can see below details:

You can edit the address format to customize the address style. You can make a seeded attribute mandatory/ non-mandatory or can add a new address element and many more properties can be set:

You can save the changes. And the address format become active.

One of the main challenges, the data migration team and the integration teams face is the mapping of layout fields to actual backend table attribute in per_addresses_f table.

There is no direct way to find the UI attribute name and its corresponding attribute name in backend table. Below query has been developed to find the UI prompt with actual table attribute name:

SELECT ftt.TERRITORY_SHORT_NAME
      ,hsfl.TERRITORY_CODE
      ,hsflb.STYLE_FORMAT_CODE
      ,hsflb.VARIATION_NUMBER
      ,hsflt.PROMPT	  
      ,hsflb.ATTRIBUTE_CODE
      ,hsflb.LINE_NUMBER
      ,hsflb.MANDATORY_FLAG
      ,hsflb.USE_INITIAL_FLAG
      ,hsflb.UPPERCASE_FLAG
      ,hsflb.STATUS_FLAG
      ,hsflb.TRANSFORM_FUNCTION
      ,hsflb.DELIMITER_BEFORE
      ,hsflb.DELIMITER_AFTER
      ,hsflb.BLANK_LINES_BEFORE
      ,hsflb.BLANK_LINES_AFTER
      ,hsflb.START_DATE_ACTIVE
      ,hsflb.END_DATE_ACTIVE
      ,hsflb.DISPLAY_SIZE
  FROM HZ_STYLE_FMT_LAYOUTS_B hsflb 
      ,HZ_STYLE_FMT_LAYOUTS_TL hsflt
      ,HZ_STYLE_FMT_LOCALES hsfl
      ,FND_TERRITORIES_TL ftt
 WHERE hsflb.STYLE_FORMAT_CODE = hsfl.STYLE_FORMAT_CODE
   AND hsflb.STYLE_FMT_LAYOUT_ID = hsflt.STYLE_FMT_LAYOUT_ID
   AND ftt.TERRITORY_CODE = hsfl.TERRITORY_CODE 
   AND ftt.LANGUAGE = 'US'
   AND ftt.LANGUAGE = hsflt.LANGUAGE
   AND ftt.TERRITORY_CODE = 'MX'
ORDER BY hsflb.STYLE_FORMAT_CODE, hsflb.LINE_NUMBER

Output after running above SQL:

Please note:

COUNTY – REGION_1

STATE – REGION_2

PROVINCE – REGION_3

HDL – Sample file to update Grade name and Grade Step Name

File for Grade Name change:

METADATA|Grade|GradeId|GradeName|GradeCode|SetCode|EffectiveStartDate|EffectiveEndDate
MERGE|Grade|30001221212|New Grade Name|GRADE_9|COMMON|1951/01/01|

File for Grade Step Name change:

METADATA|GradeStep|GradeStepId|GradeId|GradeStepName|EffectiveStartDate|EffectiveEndDate
MERGE|GradeStep|30001451452|30001221212|New GSN Level 1|1951/01/01|

Save the files as Grade.dat and upload them.

Approvals – Remove Approve/ Reject buttons from Notifications

There are many scenarios where the users don’t want to have Approve/ Reject buttons on the Bell Notifications or the notification received over email.

This can be achieved quite easily using simple settings in BPM.

To remove the Approve/Reject buttons from email:- Disable the “Make Notifications Actionable” option under the appropriate task.

To remove the Approve/Reject buttons from worklist notifications:– Remove task actions from Actions under Access for the particular task:

Make sure to Commit the changes once you make the above suggested changes.

HDL – Sample HDL file to End Date Position Valid Grades

There can multiple grades assigned as Valid grade at Position level or Job level. However, there is no direct way to end date the valid grades in bulk.

You can end date a valid grade from responsive UI. But it will be a lot of manual effort. You can search for Position and then navigate to Grades section. Then update the position and click on small delete icon next to Grade name (which you want to end date). This will end date the valid grade with an effective end date = date of position update – 1.

To do this in bulk using HDL, you can’t use DELETE command. If you use DELETE command, it will completely Purge the valid grade record from Position. To end date the valid grade, use “ReplaceLastEffectiveEndDate” attribute in the file.

Below is the sample file:

METADATA|PositionGrade|BusinessUnitName|PositionCode|EffectiveStartDate|EffectiveEndDate|GradeCode|GradeSetCode|ReplaceLastEffectiveEndDate
MERGE|PositionGrade|Progress US Business Unit|PRGUSPOS032|2018/12/31|2023/12/31|Hourly01|PRGUSGRADESET|Y

EffectiveStartDate – Earliest Grade Start Date

EffectiveEndDate – Date on which you want to end date the grade.

Once the file is loaded successfully, below is how the data will look in the backend:

Below SQL query can be used to extract valid grades data:

SELECT DISTINCT 
	   TO_CHAR (pvgf.effective_start_date, 'DD/MON/YYYY') effective_start_date,
	   TO_CHAR (pvgf.effective_end_date, 'DD/MON/YYYY') effective_end_date,
	   pjfv.POSITION_CODE,
	   pjfv.name job_name,
	   pgfv.grade_code,
	   pgfv.name grade_name,
	   pvgf.valid_grade_id,
	   pgfv.grade_id,
	   pjfv.job_id
  FROM per_valid_grades_f pvgf,
       HR_ALL_POSITIONS_F_VL pjfv,
       per_grades_f_vl pgfv 
 WHERE 1=1
   AND pvgf.position_id = pjfv.position_id
   AND pvgf.grade_id = pgfv.grade_id
   AND pjfv.POSITION_CODE = 'PRGUSPOS032'
   AND pvgf.effective_start_date BETWEEN pjfv.effective_start_date AND pjfv.effective_end_date
   AND pvgf.effective_start_date BETWEEN pgfv.effective_start_date AND pgfv.effective_end_date
ORDER BY POSITION_CODE,grade_code
HDL – Sample HDL files load Content Items

To bulk upload items catalogs in HCM profiles, you can use ContentItem.dat. Each of the template have certain mandatory attributes like Context Name, Value Set Name or Value Set Id:

S0, before you start preparing the file, you need to have below information handy:

  1. Context Name – This is a mandatory attribute. If you don’t pass the value in your HDL file, you will get below error:

The values 3000122xxxxx aren’t valid for ContentItemValueSetId.

You can get the context name from HRT_CONTENT_TYPES_B table:

2. Content Item Value Set Name/Id: This is again a mandatory attribute. You can get the Content Value Set Name/ Id from HRT_CONTENT_TP_VALUESETS_TL table.

Once you have the details, you can prepare ContentItem.dat.

Below are the sample files for different item catalog templates:

For Establishments:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|Indian Institute of Technology, Bombay|EDUCATIONAL_ESTABLISHMENT|Establishment||IIT_B|1951/01/01|||IIT_B|HRC_SQLLOADER 
MERGE|ContentItem|Indian Institute of Management, Ahemdabad|EDUCATIONAL_ESTABLISHMENT|Establishment||IIM_A|1951/01/01|||IIT_B|HRC_SQLLOADER 

For Licenses and Certifications:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner  

MERGE|ContentItem|Oracle Global Human Resources 2023|CERTIFICATION|Licenses and Certifications||O_GHR_2023|1951/01/01|||O_GHR_2023|HRC_SQLLOADER MERGE|ContentItem|Oracle Benefits 2023|CERTIFICATION|Licenses and Certifications||O_BEN_2023|1951/01/01|||O_BEN_2023|HRC_SQLLOADER

For Degrees:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|PhD|DEGREE|Degrees||XX_PhD|1951/01/01|||CI_XX_PhD|HRC_SQLLOADER 
MERGE|ContentItem|Higher National Certificate|DEGREE|Degrees||XX_Higher National Certificate|1951/01/01|||CI_XX_Higher National Certificate|HRC_SQLLOADER 

For Competencies:

METADATA|ContentItem|ContextName|ContentItemValueSetName|Name|ContentItemId|ContentItemCode|DateFrom|DateTo|ItemDescription|RatingModelId|RatingModelCode|CountryGeographyCode|CountryCountryCode|SourceSystemId|SourceSystemOwner

MERGE|ContentItem|COMPETENCY|Competencies|Accounting Standards and Principles||XX_ASAP|1951/01/01||To check knowledge on Accounting Standards and Principles.|5|PROFICIENCY|||XX_ASAP|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assessing Talent||XX_AT|1951/01/01||To check knowledge on Assessing Talent.|5|PROFICIENCY|||XX_AT|HRC_SQLLOADER
MERGE|ContentItem|COMPETENCY|Competencies|Assurance and Reporting||XX_AAR|1951/01/01||To check knowledge on Assurance and Reporting.|5|PROFICIENCY|||XX_AAR|HRC_SQLLOADER

For Languages:

METADATA|ContentItem|Name|ContextName|ContentItemValueSetName|ContentItemValueSetId|ContentItemCode|DateFrom|DateTo|RatingModelCode|SourceSystemId|SourceSystemOwner 

MERGE|ContentItem|Hindi|LANGUAGE|Languages||XX_Hindi|1951/01/01|||CI_Hindi|HRC_SQLLOADER
MERGE|ContentItem|Punjabi|LANGUAGE|Languages||XX_Punjabi|1951/01/01|||CI_Punjabi|HRC_SQLLOADER 

Please note that Rating Mode Id is mandatory for loading competencies. You can find the rating model id and rating model code from hrt_rating_models_b table.

Once the data load is successful, you can run below queries to extract loaded data: