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
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%'