Introduction

I hope most of you have already gone through one of my earlier post where we have seen how to Load Data Into Oracle HCM Cloud Using Inbound Interface Option. If you have not seen the post yet please feel free to refer the same from this link .

In this post we would try to achieve a similar data load mechanism but in this case we will try to use the BI Publisher Report instead. 

Many a times we have to perform Discretionary Disbursement from the various Absence Plans configured in the application. Most of the time this is a manual process but if there is a set of rule to be followed and the number of employees for whom this action needs to be performed is huge it makes sense to automate the process. In this post we would try to achieve the same using a BI Publisher Report.

We would try to load Person Accrual Detail data using a BIP Report in this example.

Pre-Requisites

Some of the pre-requisites for this include:

1. Configuring a Source System Owner 

I have configured a new Source System Owner ( Navigation: Setup and Maintenance -> Manage Common Lookups -> HRC_SOURCE_SYSTEM_OWNER -> Add New Lookup Value (HRC_LEGACY)

1. Identifying a Absence Plan which has “Discretionary Disbursement” enabled

We need to either find or configure a Absence Plan which has discretionary disbursement feature enabled. For this example, we will use “Vacation”

1. Creating a Report to get HDL File format for PersonAccrualDetail.dat Business Object

We would also need to create a Report which should have a custom Data Model attached which should fetch all the relevant data fields required in PersonAccrualDetail.dat data file. We would also need to attach a E-Text Template to the Report to ensure we get data from the application in HDL File Format. In addition to this we would also need to have a Bursting SQL added to the data model which will ensure that we are able to change the file name from “DefaultDocument” to “PersonAccrualDetail” . If you are using a different HDL Object then the name should correspond to the HDL Object.

1. Creating Custom Data Model to get Required Data Fields

We would need to create a Data Model which should have below SQL query as data-source.

We would need to fetch below data-columns:

ii) Creating Bursting Query to Change File Name

We would need a Bursting SQL query to change the file name. The SPLIT BY and DELIVER BY field should hold /DATA_DS/G_3/KEY

iii) Attaching E-Text Template to BI Publisher Report

We would also need to associate a EText Template to the BI Publisher Data Model created above. Details of the E-Text Template are provided below for ready reference

The eText Template would look as below

Worked Example

As all the pre-requisite are in place we can now start with the next steps. We would need to create a HCM Extract of Inbound Interface Type with following details:

There is one Record named “DummyExtractRecord” which comprises of 1 data element. Details in table below:

Once configured the Extract Attributes should appear as below:

Next we need to configure “Extract Delivery Options”

Once configured the Extract Delivery Options will appear as below:

Once all the above changes are done we should Add a New Parameter “Load Automatically” and set “Visible” property to Yes.

Once done we can Validate the Extract and once it is successfully validated we can Save and Close.

In the next step, we would need to Add a Flow Task named “Initiate HCM Data Loader” to “Inbound Interface Using BIP” Flow pattern. We can either navigate to Payroll->Checklist-> Search for Inbound Interface Using BIP  or use Refine Extracts->Search for Inbound Interface Using BIP

Once the search results are retrieved, we need to click on “Edit” (pencil icon) and choose the following task

Once we add the payroll flow task we should click on “Go To Task” and add the following details:

And with this HCM Extract Setup is complete.

Running the Extract

In this step we will try running the Extract.

Once submitted we can monitor the status of the payroll flow instance

From the above screenshot we can see that all the process has been successful and hence a Data Load should have got triggered. We can navigate to Data Exchange->HCM Data Loader -> Import and Load Data page to verify this. (While doing a search make sure you set the Created By filter to Blank else the Data Set would not appear) . We would search for Content ID as DL_PERSONAC_1806418

From the screenshots we can see that the data got loaded successfully. 

In the next step we will verify the same in application. We can perform this verification in two ways.

1. By Running a SQL Query

And we could see the results in BI Data Model

1. From Application UI

We would navigate to Person Management-> Search for Person Number 50-> Absence Records and go to Plan Participation Section, Choose “Vacation” plan and we should be able to see disbursement details

Similarly, we can check for person# 51,52,53,54 and 55 too.

Summary

So this is how we can use BI Publisher SQL based Data Source and load data into Oracle HCM Cloud Application by making use of Dummy HCM Extract of Inbound Interface. One may try to extend this logic and make the solution more flexible by making use of bind parameters and also applying this solution to other use-cases.

With this I have come to the end of this article, hope this was a good read.

Thanks for your time and have a great day ahead.

You can download the below list of files using link here

Ashish Harbhajanka

Add comment