Search for:
Sep 12 2021
BIP – Query to extract Actions.dat data

Many a time there is a requirement to update/change the existing actions and actions reasons usages. Doing this manually in UI will become a cumbersome process for multiple actions and may lead to human errors as well.

For this, HCM Data Loader can used to update the changes using Actions.dat file.

Use the below SQL queries to extract Actions and Action Reason Usages data from your Pod in HDL format:

Actions:

Select 'MERGE|Actions|'
||
actb.action_code
||'|'||
actt.action_name
||'|'||
actb.action_type_code
||'|'||
to_char(actb.start_date, 'yyyy/mm/dd')
||'|'||
to_char(actb.end_date, 'yyyy/mm/dd')
||'|'||
map.source_system_id
||'|'||
map.source_system_owner ACTIONS_HDL
from
PER_ACTIONS_B actb,
PER_ACTIONS_TL actt,
hrc_integration_key_map map
where 1=1
and actb.action_id = actt.action_id
and actt.language = USERENV('LANG')
and actb.action_id = map.surrogate_id

Action Reason Usage:

Select 'MERGE|ActionReasonUsage|'
||
aru.action_code
||'|'||
aru.action_reason_code
||'|'||
to_char(aru.start_date, 'yyyy/mm/dd')
||'|'||
to_char(aru.end_date, 'yyyy/mm/dd')
||'|'||
km.source_system_id
||'|'||
km.source_system_owner ARC_HDL
from 
hrc_integration_key_map km,
PER_ACTION_REASON_USAGES aru
where 1=1
and aru.ACTION_REASON_USAGE_ID = km.surrogate_id

Copy and save the data as Actions.dat and do the required changes.

Sample File:

METADATA|Actions|ActionCode|ActionName|ActionTypeCode|StartDate|EndDate|SourceSystemId|SourceSystemOwner
MERGE|Actions|TEST_MANAGE_CONTRACT|Test Manage Contract|EMPL_CONTRACT_EXTN|1951/01/01|4712/12/31|300000072427734|FUSION

METADATA|ActionReasonUsage|ActionCode|ActionReasonCode|StartDate|EndDate|SourceSystemId|SourceSystemOwner
MERGE|ActionReasonUsage|TEST_MANAGE_CONTRACT|CHANGE_CONTRACT|1951/01/01|4712/12/31|300000072427735|FUSION

Link to refresh business objects to get latest attributes of Actions.dat:

https://fusionhcmconsulting.com/2021/01/hdl-refresh-business-objects/

Mandeep Gupta 0 Comments
May 26 2021
HDL – Delete custom Action Reason Usages using HDL

Actions and Action Reasons are very important part of any Cloud HCM implementation. Oracle provides a large number of actions and action reasons out of the box. But if needed additional actions and action reasons can be created from UI as well as using HDL.

Each action is tied with an action type. Please note that action types are seeded and can’t be created. You can create a custom action and attach existing action reasons to it. The details are stored in PER_ACTION_REASON_USAGES table.

During implementation, there is a common requirement to delete some of the unwanted action reason usages which were created initially and are no longer required. In such cases finding each reason and deleting it from action is quite a painful task.

This can be achieved easily using HCM Data Loader.

Run the below query in BIP and save the file as Actions.dat. Zip the file and kick Import and Load HCM Data Loader process. You can modify the extract criteria as per your requirement: 

SELECT data
FROM (
SELECT 'METADATA|ActionReasonUsage|ActionCode|ActionReasonCode|StartDate|EndDate|SourceSystemId|SourceSystemOwner' data, 1 DATA_SEQ
  FROm DUAL
UNION ALL
Select 'DELETE|ActionReasonUsage|'
||
paru.action_code
||'|'||
paru.action_reason_code
||'|'||
to_char(paru.start_date, 'yyyy/mm/dd')
||'|'||
to_char(paru.end_date, 'yyyy/mm/dd')
||'|'||
hikm.source_system_id
||'|'||
hikm.source_system_owner data, 2 DATA_SEQ
from 
hrc_integration_key_map hikm,
PER_ACTION_REASON_USAGES paru
where 1=1
and paru.ACTION_REASON_USAGE_ID = hikm.surrogate_id
and paru.created_by <> 'SEED_DATA_FROM_APPLICATION'
)
ORDER BY DATA_SEQ

Sample File:

METADATA|ActionReasonUsage|ActionCode|ActionReasonCode|EndDate|StartDate|SourceSystemId|SourceSystemOwner
DELETE|ActionReasonUsage|TEST_NEW|CMP_CHG||2002/01/01|HRC_SQLLOADER_TEST_NEW|HRC_SQLLOADER

Query to check action reason codes for an action:

SELECT paru.action_code
      ,paru.action_reason_code
  FROM per_action_reason_usages paru
 WHERE paru.action_code like '%CHANGE_SALARY%'
Mandeep Gupta 0 Comments