Login
Register

Home

Trainings

Fusion Blog

EBS Blog

Authors

CONTACT US

Fusion Blog
  • Register

Oracle Gold Partners, our very popular training packages, training schedule is listed here
Designed by Five Star Rated Oracle Press Authors & Oracle ACE's.

webinar new

Search Courses

Introduction

One of the most important data insight required for any Oracle HCM Cloud Absence Management Implementation or Support project is “Employee Absence Plan Balance” Report.

However, there isn’t a delivered report for the same. Hence we would try to create a custom report and also encapsulate the same within a Custom ESS Job so that end-users can easily run the same.

So, without further ado let’s get started.

In order to complete this activity we have to perform following steps:

  1. Create a Custom Data Model
  2. Create a Custom BI Publisher Report
  3. Embed the Custom BI Publisher Report within a Custom ESS Job
  4. Run the Custom ESS Job
  5. Data Verification

Create a Custom Data Model

We would create a BI Data Model based on SQL query. The SQL query used is given below for ready reference:

SQL Query

SELECT peo.person_number, 

aapft.NAME absence_plan_name,

acc_ent.accrual_period bal_cal_date,

acc_ent.end_bal balance,

:p_threshold_date threshold_date

  FROM per_all_people_f peo,

       per_all_assignments_f asg,

       per_person_names_f per_name,

       per_periods_of_service ser,

       per_person_types_tl per_typ,

       per_legal_employers ple,

       anc_absence_plans_f_tl aapft,

       anc_absence_plans_f aapf,

       anc_per_accrual_entries acc_ent

 WHERE asg.person_id = peo.person_id

   AND ser.person_id = peo.person_id

   AND per_name.person_id = peo.person_id

   AND acc_ent.person_id = peo.person_id

   AND asg.period_of_service_id = ser.period_of_service_id

   AND acc_ent.prd_of_svc_id = asg.period_of_service_id

   AND asg.person_type_id = per_typ.person_type_id

   AND asg.legal_entity_id = ple.organization_id

   AND aapft.absence_plan_id = aapf.absence_plan_id

   AND acc_ent.plan_id = aapf.absence_plan_id

   AND acc_ent.accrual_period =

          (SELECT MAX (acc_ent1.accrual_period)

             FROM anc_per_accrual_entries acc_ent1

            WHERE 1 = 1

            and acc_ent1.accrual_period <= :p_threshold_date

              AND acc_ent1.person_id = acc_ent.person_id

              AND acc_ent1.prd_of_svc_id = acc_ent.prd_of_svc_id

              AND acc_ent1.plan_id = acc_ent.plan_id)

   AND asg.primary_flag = 'Y'

   AND asg.assignment_type IN ('E', 'C', 'N', 'P')

   AND asg.assignment_status_type = 'ACTIVE'

   AND per_name.name_type = 'GLOBAL'

   AND ple.status = 'A'

   AND per_typ.LANGUAGE = USERENV ('LANG')

   AND aapf.plan_status = 'A'

   AND aapft.LANGUAGE = 'US'

   AND TRUNC (SYSDATE) BETWEEN peo.effective_start_date AND peo.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN asg.effective_start_date AND asg.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN per_name.effective_start_date

                           AND per_name.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN ple.effective_start_date AND ple.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN aapf.effective_start_date

                           AND aapf.effective_end_date

   AND TRUNC (SYSDATE) BETWEEN aapft.effective_start_date

                           AND aapft.effective_end_date

   AND aapft.name = nvl(:absence_plan_name,aapft.name)

   AND peo.person_number = nvl(:person_number,peo.person_number)

   order by peo.person_number,aapft.name

 

Create Custom BI Report

We would now create a BI Report based on the above BI Data Model and the output would appear as below:

 

Creating Custom ESS Job

Now we would try to build a custom ESS job and encapsulate this BI Report within the ESS Job.Navigation for creating ESS Job is : 

Setup and Maintenance-> Manage Enterprise Scheduler Job Definitions and Job Sets for Human Capital Management and Related Applications->Manage Job Definitions->Create

Next, we need to populate below details and create ESS Job:

Attribute Name

Attribute Value

*Display Name

Get Employee Absence Plan Balance

Name

GET_EMPLOYEE_ABSENCE_PLAN_BALANCE

Path

absence

Application

Absence Management

Description

Custom ESS Job to fetch Employee Absence Plan Balance

*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

XML

Report ID

/Custom/Practice Samples/EmployeeAbsencePlanBalanceReport.xdo

Priority

 

Allow Multiple Pending Submissions

True

Enable Submission from Scheduled Processes

Checked

 




We would also need to define parameters for this ESS Job

Parameter Prompt

Data Type

Page Element

Default Value

Read Only

Required

Absence Plan Name

String

Text Box

     

Person Number

String

Text Box

     

Threshold Date

DateTime

Date Picker

System date

 

Checked

 

 

Running the ESS Job

We can run the Custom ESS Job from below navigation: Tools->Scheduled Processes->Get Employee Absence Plan Balance

Once the ESS Job completes we will find that a Excel Document gets downloaded and it has following output:

 

Verifying the Results

We would need to check the values from application. Navigation: My Client Groups ->Person Management-> Search for Person Number 100-> Absences-> Absence Records

 

You can download the files from below links:

EmployeeAbsencePlanBalance_dm.xdm.catalog

EmployeeAbsencePlanBalance.Sql

EmployeeAbsencePlanBalanceReport.xdo.catalog


Ashish Harbhajanka

Add comment


Security code
Refresh

About the Author

Ashish Harbhajanka

 

Oracle Fusion HCM Techno Functional Consultant with overall 10 years of Experience in software industry with 5 years in EBS HRMS and rest 5 in Fusion HCM.

My areas of intesrest in Fusion HCM include :

a) Inbound Outbound Integration using FBL/HDL or BIP/HCM Extracts.

b) Fast Formula

c) BIP Reports

d) OTBI Reports

e) RESTFUL API / Web Service Call

f) Functional Setup

g) End to End Testing

h) Regression Testing

i) Preparing COnfiguration Workbooks

j) Creating Speed Solutions

k) Preparing User Guides

l) UPK

........

Search Trainings

Fully verifiable testimonials

Apps2Fusion - Event List

<<  Mar 2024  >>
 Mon  Tue  Wed  Thu  Fri  Sat  Sun 
      1  2  3
  4  5  6  7  8  910
11121314151617
18192021222324
25262728293031

Enquire For Training

Fusion Training Packages

Get Email Updates


Powered by Google FeedBurner