Creating an Employee Self Service Report from a Normal BIP Report in Oracle Fusion Cloud

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.