Introduction
In one of the previous article (Creating a Parameterised ESS Job in Oracle HCM Cloud Application) we have seen how a parameterized ESS job is created in Oracle HCM Cloud Application, but in that example we only used one single parameter and there were no specific linking between the ESS Job and the BI Report to uniquely identify the parameter. Since we only dealt with one parameter we didn’t faced much problems but in case there are multiple parameters one may have some trouble mapping the ESS Job parameter to the corresponding BI Report parameter. There is no concept of Token (I hope you guys remember this was used in EBS while defining Concurrent program parameter) in Oracle Cloud and the entire mapping is done based on the parameter sequence.
So, we need to arrange the parameters in the same order in BIP Report and the ESS Job. Parameter 1 of BIP Report becomes argument 1 of ESS Job, Parameter 2 of BIP Report becomes argument 2 and so on.
We would demonstrate the same in this post here.
We would need to perform the following steps:
Create a SQL Query Based BIP Data Model which has multiple parameters
Create a BIP Report from the SQL Data Model
Creating a Custom ESS Job for the BIP Report
Running the Custom ESS Job and verifying the results
Create a SQL Query Based BIP Data Model which has multiple parameters
We will create a SQL Query which will fetch person number, hire date, primary flag and worker type. The SQL query is mentioned below:
SQL Query |
select papf.person_number, ppos.date_start hiredate, ppos.period_type, ppos.primary_flag from per_all_people_f papf, per_periods_of_service ppos where papf.person_id = ppos.person_id and ppos.date_start between :p_from_date and :p_to_date and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date and ppos.primary_flag =nvl(:p_flag,ppos.primary_flag) and ppos.period_type = nvl(:p_period_type,ppos.period_type) |
The details of the various parameters used are mentioned below:
P_FROM_DATE |
|
Attribute Name |
Attribute Value |
*Name |
p_from_date |
Data Type |
Date |
Default Value |
|
Parameter Type |
Date |
Row Placement |
1 |
Display Label |
From Date |
Text Field Size |
10 |
Options (Ignore User Timezone) |
Unchecked |
Date Format String |
yyyy-MM-dd |
P_TO_DATE |
|
Attribute Name |
Attribute Value |
*Name |
p_to_date |
Data Type |
Date |
Default Value |
|
Parameter Type |
Date |
Row Placement |
2 |
Display Label |
To Date |
Text Field Size |
10 |
Options (Ignore User Timezone) |
Unchecked |
Date Format String |
yyyy-MM-dd |
P_FLAG |
|
Attribute Name |
Attribute Value |
*Name |
p_flag |
Data Type |
String |
Default Value |
|
Parameter Type |
Text |
Row Placement |
3 |
Display Label |
Primary Flag |
Text Field Size |
1 |
P_PERIOD_TYPE |
|
Attribute Name |
Attribute Value |
*Name |
p_period_type |
Data Type |
String |
Default Value |
|
Parameter Type |
Text |
Row Placement |
4 |
Display Label |
Worker Type |
Text Field Size |
1 |
Create a BIP Report from the SQL Data Model
We will create a BIP Report from the above Data Model, and when we run the report the output will be as shown below:
Creating Custom ESS Job
We would now need to create a custom ESS job. We would need to search for “Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications” task under Setup and Maintenance. Next we need to click on (+) button and populate the following details
ESS Job Details |
|
Attribute Name |
Attribute Value |
*Display Name |
Person Details ESS Job |
Name |
XX_PERSONDETAILS |
Path |
/ah/ |
Application |
Global Human Resources |
Description |
Custom ESS Job created to demonstrate how to run BI Report having multiple parameters |
Retries |
|
Job Category |
|
Timeout Period |
|
*Job Application Name |
EarHcmEss |
Enable Submission from Enterprise Manager |
Unchecked |
Job Type |
BIPJobType |
Bursting Report |
Unchecked |
Class Name |
oracle.xdo.service.client.scheduler.BIPJobExecutable |
Default Output Format |
|
*Report ID |
/Custom/Practice Samples/PersonDetailsReport |
Priority |
|
Allow Multiple Pending Submissions |
False |
Enable submission from Scheduled Processes |
Checked |
Once populated the UI will appear as below:
Now, we will create the parameters using the following details
From Date |
|
Attribute Name |
Attribute Value |
*Parameter Prompt |
From Date |
*Data Type |
Date |
Read Only |
Unchecked |
Page Element |
Date Picker |
Show |
Date Only |
Default Date Format |
yyyy-MM-dd |
Default Value |
|
Tooltip Text |
|
Required |
Checked |
Do not Display |
Unchecked |
To Date |
|
Attribute Name |
Attribute Value |
*Parameter Prompt |
To Date |
*Data Type |
Date |
Read Only |
Unchecked |
Page Element |
Date Picker |
Show |
Date Only |
Default Date Format |
yyyy-MM-dd |
Default Value |
|
Tooltip Text |
|
Required |
Checked |
Do not Display |
Unchecked |
Primary Flag |
|
Attribute Name |
Attribute Value |
*Parameter Prompt |
Primary Flag (Y/N) |
*Data Type |
String |
Read Only |
Unchecked |
Page Element |
Text Box |
Default Value |
Y |
Tooltip Text |
|
Required |
Checked |
Do not Display |
Unchecked |
Worker Type |
|
Attribute Name |
Attribute Value |
*Parameter Prompt |
Worker Type (E/C/N/O) |
*Data Type |
String |
Read Only |
Unchecked |
Page Element |
Text Box |
Default Value |
E |
Tooltip Text |
E-Employee, C-Contingent Worker, N- Non-Worker and O for Others. |
Required |
Checked |
Do not Display |
Unchecked |
Once all these details are filled the parameter section would appear as below:
*Note: One can use the up-down arrow buttons (highlighted in yellow above) to re-order the parameters. One should ensure that the parameter order should exactly be same as that of BIP Report parameter sequencing.
Running the Custom ESS Job and Verifying Results
Now as a last step we will run the ESS Job and try to verify the results. For this we need to navigate to Navigator->Tools->Scheduled Processes-> Schedule New Process ( Search for Person Details ESS Job and submit the same)
And the process when gets completed displays the below information
Summary
So, from the above screenshot we can easily conclude that if we keep the ordering / sequencing of the parameter same in BIP Report and ESS Job then the application automatically maps the parameter with the ESS Job argument and no explicit linking is required.
Comments
Keep up the awesome works guys I've added you guys to blogroll.
web page: http://gavraracing.altervista.org/community/profile/maddisonkoehn6/
RSS feed for comments to this post