There are cases where you need to run HCM Extracts on different schedules which are not available in standard schedule. For example, we need to run an HCM Extracts after 6 hours. In this case, a custom flow schedule fast formula can be created.
Sample FF to run HCM Extracts at 4 AM, 10 AM, 4 PM and 10 PM:
You can pass multiple comma separated values in an HCM Extract parameter and then use below logic to separate the comma separated values into multiple values:
SELECT *
FROM (SELECT trim(regexp_substr(param_person_number,'[^, ]+',1,LEVEL))
FROM (SELECT pay_report_utils.get_parameter_value('PER_NUMBER') param_person_number
FROM DUAL)
CONNECT BY regexp_substr(param_person_number,'[^, ]+', 1,LEVEL)>0)
HCM Extracts with inbound delivery are one of the most powerful technical features of the Fusion HCM cloud. HCM Extracts with inbound delivery enables the technical consultants to do customizations and automations by making use of tools like BIP, HCM Extracts and HCM Data Loader.
You can follow the below post for more details on the topic:
One has to take care a number of things when designing the Inbound HCM Extracts or the Loopback extract, otherwise you may get variety of errors.
One of the most commonly encountered error is:
An assertion failure was detected at location BatchProcArchivePA.getRepMapId:2. error while running
There can be multiple reasons to this error. To resolve this, please check below:
Are you using a BIP report to generate the output? If yes, can you please check if permissions are assigned to the report and data model (to FUSION%APPS%HCM%ESS%APPID)?
Please download the sample output from data model and check if you can generate a sample output using BI Publisher plugin.
Check if report path is correct if you are using.
Also, please check if you are able to deliver the output to UCM.
Check if the extract parameters are correct and the related value sets/ fast formula are present in the environment.
Use the below query to extract list of “Extract Rule Type” fast formulas used in Oracle HCM Extracts:
select pedv.definition_name
,ffv.formula_name
from per_ext_data_elements_vl pedev
,pay_report_records_f prrf
,pay_report_blocks prb
,per_ext_definitions_vl pedv
,ff_formulas_vl ffv
where pedev.report_record_id = prrf.report_record_id
and prrf.report_block_id = prb.report_block_id
and prb.ext_definition_id = pedv.ext_definition_id
and pedv.definition_name LIKE 'Dec%Absence%'
and ffv.formula_id = pedev.rule_id
Often in Cloud HCM, we encounter situation(s) where we need to update some information at worker assignment, post worker creation as this information was not available at the time of hiring an worker. One such example could be Employee category. Let’s take a hypothetical example, employee category should be auto populated based on worker Job. As, there is no direct link between employee category and job, so it becomes a pain to manually search and put the correct employee category while hiring. So, in this case, the worker is hired with Job with no value for employee category.
A DFF is opened at Job level which store the corresponding employee category. So, in this case we design a solution which will:
Read the worker job and then the corresponding employee category from Job.
Generate the data for WorkTerms and Assignments METADATA in HCM Data Loader Format.
HCM Extract to consume the data and trigger HDL Import and Load Process.
Schedule HCM Extract to run daily or depending upon the requirement.
Once, HCM Extract is run, employee category will populated automatically.
Steps to design the integration:
Extract the Workterms and assignment data for all workers where the job is populated and employee category is NULL.
Create a BIP publisher report to organize the data extracted in Step 1 in HCM Data Loader format. Copy the Global Reports Data Model (from path /Shared Folders/Human Capital Management/Payroll/Data Models/globalReportsDataModel) to a folder in /Shared Folders/Custom/HR. This folder can be anything as per your nomenclature specifications.
Add a new data set in the globalReportsDataModel and paste your query in the new data set.
Download the above file. Change the extension to xml.
Open the xml file with Notepad or Notepad++ and remove first two rows (these rows were added to make sure the file is uploaded here).
Navigate to My Client Groups -> Data Exchange -> HCM Extracts -> Extract Definitions:
Click on Import to import the xml file
Provide an Extract name. Uncheck the Changes Only checkbox and click on Ok:
Once the extract Import is complete, Click on pencil icon to edit:
Click on ‘Extract Delivery Option’ in navigation Tree on left side. And on the right side, Under ‘Extract Delivery Options’ click on edit to update the path of your report as created earlier. It should like – /Custom/HR/AssignmentUpdateRPT.xdo
Make sure default value for parameter Auto Load is set “Y”.
Save the details. Click on Extract Execution Tree next and Click All Formula:
Once the formulas are complied, then click on Submit button.
The next step is to refine the extract in order to Submit the Import and Load process:
Navigate to My Client Groups -> Data Exchange -> HCM Extracts -> Refine Extracts. Search the extract and click on edit.
Select and Add – Initiate HCM Data Loader process
Click on Go Task for “Initiate HCM Data Loader” and Click Edit for “ Data Loader Archive Action” and add the relevant parameters:
Parameter Basis – Bind to Flow Task Basis Value – XX Assignment Update Integration, Submit , Payroll Process
Click Edit for “Data Loader Configurations” add relevant parameters
Parameter Basis – Constant Bind Basis Value -ImportMaximumErrors=100,LoadMaximumErrors=100,LoadConcurrentThreads=8,LoadGroupSize=100
Task sequence should look as follows:
Go to Review and click on Submit.
Your extract is now ready for submission. You can submit the extract and test it.
There is a frequent requirement of using a parameter in HCM Extracts which can accept multiple comma separated values. For example, in Worker Extract, we need to have a Parameter called PER_NUMBER which should accept multiple comma separated values.
To split the input values, we can use REGEXP_SUBSTR function.
SELECT *
FROM (SELECT trim(regexp_substr(per_num_param,'[^, ]+',1,LEVEL))
FROM (SELECT pay_report_utils.get_parameter_value('PER_NUMBER') per_num_param
FROM DUAL)
CONNECT BY regexp_substr(per_num_param,'[^, ]+', 1,LEVEL)>0)
While creating an HCM extract based on a BIP report, there is a common requirement where the BIP should retrieve “Changed Only” data. This can be achieved using a BIP report data model which is based on globalReportsDataModel (/shared/Human Capital Management/Payroll/Data Model).
Use the below query in the data set to extract last run date of HCM extract and based on this date create a filter on last_update_date of the standard table from which we are extracting the data.
WITH pLastRunDate AS
(SELECT MAX(pfi.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM pay_flows pf
,pay_flow_instances pfi
WHERE 1=1
AND pf.base_flow_id = pfi.base_flow_id
AND pfi.LAST_UPDATE_DATE < sysdate
AND pf.base_flow_name ='XX Custom Integration Extract' -- - give the name of your HCM extract
AND pfi.status = 'COMPLETED'
)
