Search for:
Jun 14 2022
HCM Extracts – Passing multiple values in a parameter

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)
Mandeep Gupta 0 Comments
Jun 2 2021
BIP – Query to get Position costing details
SELECT hapft.name
      ,pcaa.segment1 
      ,pcaa.segment2 
      ,pcaa.segment3 
      ,pcaa.segment4 
      ,pcaa.segment5 
      ,pcaa.segment6 
      ,pcaa.segment7 
      ,pcaa.segment8 
  FROM PAY_COST_ALLOCATIONS_F pacf
      ,PAY_COST_ALLOC_ACCOUNTS pcaa
	  ,HR_ALL_POSITIONS_F hapf
	  ,HR_ALL_POSITIONS_F_TL hapft
 WHERE pacf.cost_allocation_record_id = pcaa.cost_allocation_record_id 
   AND pacf.source_type = 'POS' 
   AND pacf.source_id = hapf.position_id
   AND hapft.position_id = hapf.position_id
   AND hapft.language = 'US'
   AND TRUNC(SYSDATE) BETWEEN pacf.effective_start_date AND pacf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN hapf.effective_start_date AND hapf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN hapft.effective_start_date AND hapft.effective_end_date
Mandeep Gupta 0 Comments
Jun 2 2021
Extract – Loopback Inbound Extract

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:

  1. Extract the Workterms and assignment data for all workers where the job is populated and employee category is NULL.
  2. 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.
  3. Add a new data set in the globalReportsDataModel and paste your query in the new data set.
  4. Check below post on sample query to extract the data in HCM Data Loader format: https://fusionhcmconsulting.com/2021/01/hdl-updating-an-attribute-on-assignment/
  5. Create an etext template (sample RTF template is available to above link) and a report based on above data model.
  6. Global Data Model will look like (G_3 is new data set):-

Steps to create HCM Extract:

You can import the below sample extract in your environment:

XX_Assignment_Update_IntegrationDownload
  • 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.

Mandeep Gupta 1 Comments
May 24 2021
HCM Extract – Using Multiple values in Parameter

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)
Mandeep Gupta 0 Comments
Dec 30 2020
HCM Extract – Query to get last extract run date

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