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 BI Publisher Report. 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 parameterized 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 parameterized BIP Report in this example.
Pre-Requisites
Some of the pre-requisites for this include:
- 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)
- 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”.
- Creating a Report to get HDL File format for PersonAccrualDetail.dat Business Object
We would also need to create a Report which would be a copy of globalReportsDataModel.
We would need to create an additional data set which would fetch all the relevant data fields required in PersonAccrualDetail.dat data file. This additional data set should be linked to the delivered globalReportsDataSet using a Group Link. 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 this case we would use the delivered bursting query provided in globalReportsDataModel. If you are using a different HDL Object then the name should correspond to the HDL Object.
- 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:
|
Data Field
|
Data Source
|
|
KEY
|
:payrollActionId
|
|
PERSONID
|
PER_ALL_PEOPLE_F.PERSON_ID
|
|
ACCRUALTYPE
|
<ACCRUALTYPE.sql>
|
|
PERSONNUMBER
|
PER_ALL_PEOPLE_F.PERSON_NUMBER
|
|
PLANNAME
|
<PLANNAME.sql>
|
|
VALUE
|
<ACCRUALVALUE.sql>
|
|
PROCDDATE
|
<PROCDDATE.sql>
|
|
SOURCESYSTEMOWNER
|
<SOURCESYSTEMOWNER.sql>
|
|
DERIVEDPROCDDATE
|
TO_CHAR(TO_DATE(A.PROCDDATE),'YYYYMMDD')
|
|
SQL1 -> SQL to Get ACCRUALTYPE
|
|
SELECT fpv.flow_param_value
FROM pay_flow_parameters FP
, pay_flow_param_values FPV
, pay_flow_instances PFI
WHERE 1=1
AND FP.base_flow_parameter_id = FPV.base_flow_parameter_id
AND FPV.flow_instance_id = PFI.flow_instance_id
AND PFI.flow_instance_id =
(SELECT PR.flow_instance_id
FROM pay_requests PR
WHERE 1=1
AND PR.pay_request_id IN
(SELECT PPA1.pay_request_id
FROM pay_payroll_actions PPA1
WHERE PPA1.payroll_action_id = :payrollActionId
)
)
AND UPPER(FP.base_flow_parameter_name) = 'ACCRUAL_TYPE'
|
|
SQL2 -> SQL to Get PLANNAME
|
|
SELECT fpv.flow_param_value
FROM pay_flow_parameters FP
, pay_flow_param_values FPV
, pay_flow_instances PFI
WHERE 1=1
AND FP.base_flow_parameter_id = FPV.base_flow_parameter_id
AND FPV.flow_instance_id = PFI.flow_instance_id
AND PFI.flow_instance_id =
(SELECT PR.flow_instance_id
FROM pay_requests PR
WHERE 1=1
AND PR.pay_request_id IN
(SELECT PPA1.pay_request_id
FROM pay_payroll_actions PPA1
WHERE PPA1.payroll_action_id = :payrollActionId
)
)
AND UPPER(FP.base_flow_parameter_name) = 'PLAN_NAME'
|
|
SQL3 -> SQL to Get VALUE
|
|
SELECT fpv.flow_param_value
FROM pay_flow_parameters FP
, pay_flow_param_values FPV
, pay_flow_instances PFI
WHERE 1=1
AND FP.base_flow_parameter_id = FPV.base_flow_parameter_id
AND FPV.flow_instance_id = PFI.flow_instance_id
AND PFI.flow_instance_id =
(SELECT PR.flow_instance_id
FROM pay_requests PR
WHERE 1=1
AND PR.pay_request_id IN
(SELECT PPA1.pay_request_id
FROM pay_payroll_actions PPA1
WHERE PPA1.payroll_action_id = :payrollActionId
)
)
AND UPPER(FP.base_flow_parameter_name) = 'ACCRUAL_VALUE'
|
|
SQL4 -> SQL to Get PROCDDATE
|
|
SELECT fpv.flow_param_value
FROM pay_flow_parameters FP
, pay_flow_param_values FPV
, pay_flow_instances PFI
WHERE 1=1
AND FP.base_flow_parameter_id = FPV.base_flow_parameter_id
AND FPV.flow_instance_id = PFI.flow_instance_id
AND PFI.flow_instance_id =
(SELECT PR.flow_instance_id
FROM pay_requests PR
WHERE 1=1
AND PR.pay_request_id IN
(SELECT PPA1.pay_request_id
FROM pay_payroll_actions PPA1
WHERE PPA1.payroll_action_id = :payrollActionId
)
)
AND UPPER(FP.base_flow_parameter_name) = 'PROCD_DATE'
|
|
SQL5 -> SQL to Get SOURCESYSTEMOWNER
|
|
SELECT fpv.flow_param_value
FROM pay_flow_parameters FP
, pay_flow_param_values FPV
, pay_flow_instances PFI
WHERE 1=1
AND FP.base_flow_parameter_id = FPV.base_flow_parameter_id
AND FPV.flow_instance_id = PFI.flow_instance_id
AND PFI.flow_instance_id =
(SELECT PR.flow_instance_id
FROM pay_requests PR
WHERE 1=1
AND PR.pay_request_id IN
(SELECT PPA1.pay_request_id
FROM pay_payroll_actions PPA1
WHERE PPA1.payroll_action_id = :payrollActionId
)
)
AND UPPER(FP.base_flow_parameter_name) = 'SOURCE_SYSTEM_OWNER'
|
|
PersonAccrualDetailParameterized_ds -> Data Model SQL Query
|
|
|
ii) Attaching New Data Set with Delivered Data Set of globalReportsDataModel
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
|
ATTRIBUTE
|
VALUE
|
|
*Layout Name
|
PersonAccrualDetailParameterized.rtf
|
|
*Template File
|
PersonAccrualDetailParameterized
|
|
Type
|
eText
|
|
Locale
|
English
|
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:
|
Attribute Name
|
Attribute Value
|
|
Extract Name
|
Loading Data Into Oracle HCM Cloud Using Parameterized BI Publisher Report
|
|
Extract Type
|
Inbound Interface
|
|
User Entity
|
PER_EXT_RESET_UE
|
|
Threading Database Item
|
|
|
Threading Action Type
|
|
|
Root Data Group Name
|
DummyDataGroup
|
|
Data Group Filter Criteria
|
|
There is one Record named “DummyExtractRecord” which comprises of 1 data element. Details in table below:
|
Col
|
Name
|
Type
|
Data Source (DS) / Data Value (DV)
|
|
1
|
DummyExtractAttribute
|
String
|
X
|
Once configured the Extract Attributes should appear as below:
Next, we need to add various input parameters
|
Name
|
Tag Name
|
Data Type
|
Default Value
|
Description
|
|
Load Automatically
|
auto_load
|
Text
|
Y
|
Load Automatically
|
|
Effective Date
|
effective_date
|
Date
|
Select sysdate from dual
|
Effective Date
|
|
Source System Owner
|
Source_System_Owner
|
Text
|
HRC_LEGACY
|
Source System Owner
|
|
Plan Name
|
Plan_Name
|
Text
|
Vacation
|
Absence Plan Name
|
|
Accrual Type
|
Accrual_Type
|
Text
|
|
Accrual Type
|
|
Accrual Value
|
Accrual_Value
|
Number
|
|
Accrual Value
|
|
Procd Date
|
Procd_Date
|
Date
|
|
Processed Date
|
Next we need to configure “Extract Delivery Options”
|
Extract Delivery Option : PersonAccrualDetailData
|
|
Attribute Name
|
Attribute Value
|
|
*Delivery Option Name
|
PersonAccrualDetailData
|
|
Output Type
|
Data
|
|
Report
|
|
|
Template Name
|
|
|
*Output Name
|
PersonAccrualDetailData
|
|
*Delivery Type
|
None
|
|
Required
|
Checked
|
|
Extract Delivery Option : PersonAccrualDetailHDL
|
|
Attribute Name
|
Attribute Value
|
|
*Delivery Option Name
|
PersonAccrualDetailHDL
|
|
Output Type
|
Text
|
|
Report
|
/Custom/Practice Samples/PersonAccrualDetailParameterized.xdo
|
|
Template Name
|
PersonAccrualDetailParameterized
|
|
*Output Name
|
PersonAccrualDetail
|
|
*Delivery Type
|
Inbound Interface
|
|
Required
|
Checked
|
|
Encryption Mode
|
None
|
|
Override File Extension
|
.dat
|
|
Run Time File Name
|
PersonAccrualDetail
|
|
Integration Name
|
PADPHDL
|
|
Integration Type
|
Data Loader
|
|
Compressed Delivery Group
|
PersonAccrualDetailParameterizedHDL.zip
|
|
Extract Delivery Option : PersonAccrualDetailWCC
|
|
Attribute Name
|
Attribute Value
|
|
*Delivery Option Name
|
PersonAccrualDetailWCC
|
|
Output Type
|
Text
|
|
Report
|
/Custom/Practice Samples/PersonAccrualDetailParameterized.xdo
|
|
Template Name
|
PersonAccrualDetailParameterized
|
|
*Output Name
|
PersonAccrualDetailWCC
|
|
*Delivery Type
|
WebCenter Content
|
|
Required
|
Checked
|
|
Encryption Mode
|
None
|
|
Run Time File Name
|
PersonAccrualDetail
|
|
Integration Name
|
PADWCC
|
Once configured the Extract Delivery Options will appear as below:
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 “Loading Data Into Oracle HCM Cloud Using Parameterized BI Publisher Report” 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
|
Name
|
Initiate HCM Data Loader
|
|
Description
|
Generate HCM Data Loader File and optionally perform a Data Load
|
|
Task Type
|
Standard Process
|
Once we add the payroll flow task we should click on “Go To Task” and add the following details:
|
Initiate HCM Data Loader Task Definition: Basic Information (Data Loader Archive Action)
|
|
Name
|
Data Loader Archive Action
|
|
Execution Mode
|
Submit
|
|
Data Type
|
Text
|
|
Parameter Basis
|
Bind to Flow Task
|
|
Basis Value
|
Loading Data Into Oracle HCM Cloud Using Parameterized BI Publisher Report, Submit , Payroll Process
|
|
Usage
|
Input Parameter
|
|
Initiate HCM Data Loader Task Definition: Basic Information (Data Loader Configuration)
|
|
Name
|
Data Loader Configuration
|
|
Execution Mode
|
Submit
|
|
Data Type
|
Text
|
|
Parameter Basis
|
Constant Bind
|
|
Basis Value
|
ImportMaximumErrors=100,
LoadMaximumErrors=100,
LoadConcurrentThreads=8,
LoadGroupSize=100
|
|
Usage
|
Input Parameter
|
And with this HCM Extract Setup is complete.
Running the Extract
Now we will try running the extract with following parameter values:
|
C001
|
|
Extract Instance Name
|
C001
|
|
Effective Date
|
5/3/2020
|
|
Load Automatically
|
Y
|
|
Source System Owner
|
HRC_LEGACY
|
|
Plan Name
|
Vacation
|
|
Accrual Type
|
INIT
|
|
Accrual Value
|
20
|
|
Procd Date
|
1/1/2020
|
This run added a initial value to “Vacation” plan as of 01/01/2020.
In the next run we will add Accrual Type of AJOTH (other adjustment)
|
C002
|
|
Extract Instance Name
|
C002
|
|
Effective Date
|
5/3/2020
|
|
Load Automatically
|
Y
|
|
Source System Owner
|
HRC_LEGACY
|
|
Plan Name
|
Vacation
|
|
Accrual Type
|
ADJOTH
|
|
Accrual Value
|
15
|
|
Procd Date
|
2/1/2020
|
And finally in the third run we will add Accrual Type of CSH (Disbursement)
|
C003
|
|
Extract Instance Name
|
C003
|
|
Effective Date
|
5/3/2020
|
|
Load Automatically
|
Y
|
|
Source System Owner
|
HRC_LEGACY
|
|
Plan Name
|
Vacation
|
|
Accrual Type
|
CSH
|
|
Accrual Value
|
5
|
|
Procd Date
|
3/1/2020
|
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_PADPHDL%
We can also quickly check the content server and we should be able to see the files.
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.
- By Running a SQL Query
|
SQL Query
|
|
select papf.person_number,
aapft.name,
apaed.type,
trunc(apaed.procd_date) procd_date,
apaed.value,
apaed.payment_percentage
from per_all_people_f papf,
anc_per_acrl_entry_dtls apaed,
anc_absence_plans_f_tl aapft
where papf.person_id = apaed.person_id
and apaed.source = 'DL'
and apaed.pl_id = aapft.absence_plan_id
and aapft.name = :absence_plan_name
and aapft.language = 'US'
and trunc(sysdate) between aapft.effective_start_date and aapft.effective_end_date
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and papf.person_number IN ('30','31','32','33','34','35')
order by papf.person_number,apaed.procd_date ascorder by papf.person_number asc
|
And we could see the results if we run the PersonAccrualVerificationReport
- From Application UI
We would navigate to Person Management-> Search for Person Number 30-> Absence Records and go to Plan Participation Section, Choose “Vacation” plan and we should be able to see entries for Initial Balance, Adjustment and Disbursement
Similarly, we can check for person# 31,32,33,34 and 35 too.
Summary
So this is how we can use parameterized 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 apply 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 files from this link .
a) "PersonAccrualDetailParameterized.rtf" (RTF Template)
b) "SQLs.zip" (SQLs used)
c) "Loading Data Into Oracle HCM Cloud Using Parameterized BI Publisher Report.xml" (Extract Definition File)
d) "PersonAccrualDetailParameterizedReport.xdo.catalog" (Report used in Extract Delivery Option)
e) "PersonAccrualDetailParameterized_dm.xdm.catalog" (Data Model Associated with the Report used in Extract Delivery Option)
f) "PersonAccrualVerificationReport.xdo.catalog" (Report used for Verification)
g) "PersonAccrualDetailVerification_dm.xdm.catalog" (Data Model used for Verification Report)
Apps2Fusion Articles 