Business Requirement
In any ERP application there always exists some or other information which is of particular interest to an employee. Some common examples being:
-
Vacation Balance
-
Rewards
-
Employee Profile Summary (Curriculum Vitae)
While an employee can always login to the application and visit his/her profile to get such information, many a times individuals prefer to run a specific report and get the data. For all such requirements we need to create ESS (Employee Self Service) Reports.
An admin user however, can get details of all employees using a common consolidated report.
Specific changes needs to be made to the consolidated report to ensure that the new version or rather the ESS version works fine and renders data corresponding to the logged in user.
In this example, we will try to create a ESS Report from a Consolidated Report.
Consolidated Vacation Balance Report
As a pre-requisite we need to have a report created. We have created a custom report which fetches the following details:
-
Person Number
-
UserName
-
Plan Name
-
Plan Balance
We will use the below query to create a data model for the report
|
SQL Query Used |
|
SELECT TABLE1.PERSON_NUMBER PERSONNUMBER, TABLE1.USERNAME, TABLE1.PLANNAME PlanName, TABLE1.END_BAL || ' ' || DECODE(TABLE1.PLANUOM,'D', 'Days','H','Hours',TABLE1.PLANUOM) PlanBalance FROM ( select apae.person_id, papf.person_number, apae.plan_id, (select aapft.name from anc_absence_plans_f_tl aapft where TRUNC(SYSDATE) BETWEEN aapft.effective_start_date and aapft.effective_end_date and language = 'US' and aapft.absence_plan_id = apae.plan_id) planname, (select aapf.plan_uom from anc_absence_plans_f aapf where aapf.absence_plan_id = apae.plan_id and TRUNC(SYSDATE) between aapf.effective_start_date and aapf.effective_end_date ) planuom, apae.accrual_period, apae.end_bal, pu.username from anc_per_accrual_entries apae, per_all_people_f papf, per_users pu where apae.person_id = papf.person_id and TRUNC(SYSDATE) BETWEEN papf.effective_start_date and papf.effective_end_date and TRUNC(apae.accrual_period) = TRUNC(LAST_DAY(:pdate)) and pu.person_id = papf.person_id ) TABLE1 |
The Report Output looks as below:
Creating an Employee Self Service Version
In the above snapshot we found that details for all the employees are displayed but in case of a self service version one needs to ensure that records pertaining to only the logged in employee is displayed.
For this we would need to make some changes to the SQL query by introducing additional where clause to only fetch records for the employee.
This can be accomplished by making use of FND_GLOBAL.USER_GUID profile.
A quick comparison of the SQLs namely ConsolidatedVacationBalanceQuery.sql and MyVacationBalanceQuery.sql reveals that only the clause “and pu.user_guid = fnd_global.user_guid” has been added to convert the Consolidated Report to Employee Self Service one.
Verifying Results
Now we have two versions of report namely ConsolidatedVacationBalanceReport and MyVacationBalanceReport.
|
User Type |
ConsolidatedVacationBalanceReport |
EmployeeVacationBalanceReport |
|
Admin User |
|
|
|
Employee User |
|
|
Inference / Summary
From the above screenshot we can conclude that when we used FND_GLOBAL.USER_GUID to filter out records only applicable to logged in user the Employee version of the report didn’t returned any records when run from an Admin User. ( This Admin User does not has any person record associated with it and so the report fetches no data).
On the other hand when we login to application using a named user ALAN.COOK(user with a valid person record associated with it…) then both versions of the report fetched the same data.
So, this is how we can convert a Normal BI Report to an Employee Self Service Report.
And with this I have come to the end of this post.
Thanks all for your time and have a wonderful day ahead.
Apps2Fusion Articles 