Extending Oracle Fusion Cloud Applications using Oracle APEX – HCM Use Case 1

This blog is part of the blog series Extending Oracle Fusion Cloud Applications using Oracle APEX. In this blog post we will cover a Human Capital Management (HCM) use case, retrieve data from Oracle Fusion Cloud Applications and display it on Oracle APEX pages using the REST GET method.

Business requirement

A customer has implemented Oracle Fusion Human Resources and Payroll, but not Fusion Time and Labor. They require a simple solution to quickly view and upload hours worked for a small group of part-time employees who do not have access to the core Fusion application.
Local supervisors will:

1. Collate these hours weekly.
2. Enter the data into the application for payroll processing.
3. View only the employees they are responsible for.
4. See control totals for verification.

Prerequisites

1. Access to Oracle Fusion.
2. Access to Oracle APEX environment.
3. Complete the steps mentioned in the Extending Oracle Fusion Cloud Applications using Oracle APEX – Authentication blog.
4. Complete the steps mentioned in the Extending Oracle Fusion Cloud Applications using Oracle APEX – Authorization blog.
5. Complete the steps mentioned in the Extending Oracle Fusion Cloud Applications using Oracle APEX – SaaS Data blog.

High level steps

1. Login to the APEX environment.
2. Create a new Fusion Integrated APEX application or use the APEX application created in the Authentication blog.
3. Create REST data source/s.
4. Create Shared LOV for Worker details.
5. Create Oracle APEX page/s.

Execution flow

1. User (Manager) logs into the Fusion Integrated APEX Extension application.
2. When the page is loading, the below details are fetched (if not fetched already):
   1. Manager details (e.g Manager Person Number) using the REST Data Source HCM_SELF_DETAILS and stored in APEX_COLLECTION named MGR_DS.
   2. Manager Reportees details using the REST Data Source HCM_WORKER_DETAILS and stored in APEX_COLLECTION named MGR_REPORTS_DS.
   3. Assignments details of all the Manager Reportees and stored in APEX_COLLECTION named MGR_REPORTS_ASSIGNMENTS_DS.
3. The above information is stored in Oracle APEX Application Items and Collections, so that it can be extracted once per session and reused throughout the session in multiple pages where it is required.
4. Manager selects a reportee and effective date to see the Element Entries. Person number of selected reportee is shown for reference.
5. Manager selects an Element Entry and can view the Element Entry Values.
6. Verify the extension search results with the data in Oracle Fusion for testing.

Detailed steps

In order to create Rest Data source (Design time), the Oracle APEX application needs to be run (Run time), as it uses the same credentials and fetches the required details of the REST API, else we get the Authentication failed error at the last step of the REST data source creation. Also It is recommended to run the APEX application using a user with elevated roles to perform this step.

Create REST data source/s: Navigate to the Shared Components –> Rest Data Sources and create the below REST Data Sources from scratch using the below details (Replace xxxx with the respective environment details):

REST Data Source Static ID:

As of the time of writing this blog, the Oracle Cloud Applications (SaaS) REST Service REST Data source does not yet support the automatic inclusion of nested array from the REST API response, and so we need to add them manually to the Data Profile as below. This needs to be done only for the nested array columns which we need for the extension use case.

HCM Worker Details – Data Profile – Nested array columns

REST Data Source HCM Worker Details – GET operation parameters

REST Data Source HCM Element Entries – GET operation parameters

REST Data Source HCM Element Entry Values – Parameters

(*this is REST Data Source parameter and not Operation level parameter)

Once the above steps are completed and REST API are discovered successfully, we can verify the Data profile (REST API columns), Methods/Operations, Payload, Parameters and REST API response data.

Create Shared LOV for Worker details: Navigate to Shared Components -> Lists of Values and create the List of Values using the below details (Replace xx with the page number of below created page):

Create an Application Item: Navigate to Shared Components -> Application Items and create the Application Item using the below details:

Create Oracle APEX page/s: Create an Oracle APEX page with the below details:

Create a Static content region named Search Element Entries (Replace XX with the page number).

Create a Static content region named Element Entries and Element Entry Values with below Sub Regions and Page Items.

Dynamic Actions (DA)

Get Loggedin Manager Dynamic Actions:

PL/SQL code for Pre-Rendering –> Before Regions: Get Loggedin Manager

-- Sample reference code
declare
lv_mgr_person_id     varchar2(500);
lv_mgr_person_number varchar2(500);
lv_mgr_user_name     varchar2(500);
lv_mgr_display_name  varchar2(500);
lv_mgr_email_address varchar2(500);
begin
if :MGR_PERSONNUMBER is null
then
xx_hcm_element_entry_extn_pkg.get_emp_details(:APP_USER,lv_mgr_person_id,lv_mgr_person_number,lv_mgr_user_name,lv_mgr_display_name,lv_mgr_email_address);
:MGR_PERSONNUMBER := lv_mgr_person_number;
end if;
:PXX_MGR_PERSONNUMBER := :MGR_PERSONNUMBER;
:PXX_FINDER := 'findReports;PersonNumber='|| :MGR_PERSONNUMBER || ',LineManagerFlag=true,DirectReportsFlag=false';
end;

PXX_REPORTEESLIST Dynamic Actions:

PXX_EFFECTIVEDATE Dynamic Actions:

Element Entries (Interactive Grid) Dynamic Actions:

Below is a sample reference screen which helps query the Element Entry details of the reportees of a Manager.

Most of the PLSQL code is added to a DB Package and the same is called from required places in the Oracle APEX pages. This will help centralize the PLSQL code for better support, maintenance and enhancement activities.

-- Sample reference code
CREATE OR REPLACE PACKAGE xx_hcm_element_entry_extn_pkg 
IS
   -- Sample code for the other procedures is available in HCM Use Case 2 blog
   PROCEDURE get_emp_details (
      p_mgr_app_user      VARCHAR2,
      o_mgr_person_id     OUT VARCHAR2,
      o_mgr_person_number OUT VARCHAR2,
      o_mgr_user_name     OUT VARCHAR2,
      o_mgr_display_name  OUT VARCHAR2,
      o_mgr_email_address OUT VARCHAR2
   );

END xx_hcm_element_entry_extn_pkg;

-- Sample reference code
CREATE OR REPLACE PACKAGE BODY xx_hcm_element_entry_extn_pkg 
IS
   -- Sample code for the other procedures is available in HCM Use Case 2 blog
   PROCEDURE get_emp_details (
      p_mgr_app_user      VARCHAR2,
      o_mgr_person_id     OUT VARCHAR2,
      o_mgr_person_number OUT VARCHAR2,
      o_mgr_user_name     OUT VARCHAR2,
      o_mgr_display_name  OUT VARCHAR2,
      o_mgr_email_address OUT VARCHAR2
   ) IS
      l_rc1                      apex_exec.t_context;
      l_rc2                      apex_exec.t_context;
      l_parameters2              apex_exec.t_parameters;
      lv_reportee_person_id      VARCHAR2(500);
      lv_reportee_person_number  VARCHAR2(500);
      lv_wr_period_of_service_id VARCHAR2(500);
      lv_wr_primary_flag         VARCHAR2(500);
      ln_cnt_input_values        NUMBER;
      ln_cnt_coll                NUMBER;
   BEGIN
   --------------------------------------------------------------------------------------------------------------------------------------------
   --------------------------------------------------------------------------------------------------------------------------------------------
      SELECT
         COUNT(1)
      INTO ln_cnt_coll
      FROM
         xx_mgr_ds_v;
      IF ln_cnt_coll = 0 THEN
         apex_collection.create_or_truncate_collection('MGR_DS');
         apex_collection.create_or_truncate_collection('MGR_REPORTS_DS');
         apex_collection.create_or_truncate_collection('MGR_REPORTS_ASSIGNMENTS_DS');
         l_rc1 := apex_exec.open_rest_source_query(p_static_id => 'HCM_SELF_DETAILS');
         WHILE apex_exec.next_row(l_rc1) LOOP
            apex_collection.add_member(
               p_collection_name => 'MGR_DS',
               p_c001            => apex_exec.get_varchar2(l_rc1, 'APEX$RESOURCEKEY'),
               p_c002            => apex_exec.get_varchar2(l_rc1, 'PERSONNUMBER'),
               p_c003            => apex_exec.get_varchar2(l_rc1, 'DISPLAYNAME'),
               p_c004            => apex_exec.get_varchar2(l_rc1, 'USERNAME'),
               p_c005            => apex_exec.get_varchar2(l_rc1, 'FIRSTNAME'),
               p_c006            => apex_exec.get_varchar2(l_rc1, 'LASTNAME'),
               p_c007            => apex_exec.get_varchar2(l_rc1, 'MIDDLENAMES')
            );
            o_mgr_person_number := apex_exec.get_varchar2(l_rc1, 'PERSONNUMBER');
            o_mgr_user_name := apex_exec.get_varchar2(l_rc1, 'USERNAME');
            o_mgr_display_name := apex_exec.get_varchar2(l_rc1, 'DISPLAYNAME');
            o_mgr_email_address := apex_exec.get_varchar2(l_rc1, 'EMAILADDRESS');
         END LOOP;
         apex_exec.close(l_rc1);
   --------------------------------------------------------------------------------------------------------------------------------------------
   --------------------------------------------------------------------------------------------------------------------------------------------

         l_parameters2.DELETE;
         apex_exec.add_parameter(l_parameters2, 'finder', 'findReports;PersonNumber = ('
                                                          || o_mgr_person_number
                                                          || '),LineManagerFlag=true,DirectReportsFlag=false');
         l_rc2 := apex_exec.open_rest_source_query(
            p_static_id  => 'HCM_WORKER_DETAILS',
            p_parameters => l_parameters2
         );
         WHILE apex_exec.next_row(l_rc2) LOOP
            lv_reportee_person_id := apex_exec.get_number(l_rc2, 'PERSONID');
            lv_reportee_person_number := apex_exec.get_varchar2(l_rc2, 'PERSONNUMBER');
            apex_exec.open_array(l_rc2, 'NAMES');
            WHILE apex_exec.next_array_row(l_rc2) LOOP
               apex_collection.add_member(
                  p_collection_name => 'MGR_REPORTS_DS',
                  p_c001            => o_mgr_person_number,
                  p_c002            => o_mgr_display_name,
                  p_c003            => lv_reportee_person_id,
                  p_c004            => lv_reportee_person_number,
                  p_c005            => apex_exec.get_varchar2(l_rc2, 'DISPLAYNAME')
               );
            END LOOP;
            apex_exec.close_array(l_rc2);
         END LOOP;
         apex_exec.close(l_rc2);
   --------------------------------------------------------------------------------------------------------------------------------------------
   --------------------------------------------------------------------------------------------------------------------------------------------
         l_parameters2.DELETE;
         apex_exec.add_parameter(l_parameters2, 'finder', 'findReports;PersonNumber = ('
                                                          || o_mgr_person_number
                                                          || '),LineManagerFlag=true,DirectReportsFlag=false');
         l_rc2 := apex_exec.open_rest_source_query(
            p_static_id  => 'HCM_WORKER_DETAILS',
            p_parameters => l_parameters2
         );
         WHILE apex_exec.next_row(l_rc2) LOOP
            lv_reportee_person_id := apex_exec.get_number(l_rc2, 'PERSONID');
            lv_reportee_person_number := apex_exec.get_varchar2(l_rc2, 'PERSONNUMBER');
            apex_exec.open_array(l_rc2, 'WORKRELATIONSHIPS');
            WHILE apex_exec.next_array_row(l_rc2) LOOP
               lv_wr_period_of_service_id := apex_exec.get_varchar2(l_rc2, 'PERIODOFSERVICEID');
               lv_wr_primary_flag := apex_exec.get_varchar2(l_rc2, 'WRPRIMARYFLAG');
               apex_exec.open_array(l_rc2, 'ASSIGNMENTS');
               WHILE apex_exec.next_array_row(l_rc2) LOOP
                  apex_collection.add_member(
                     p_collection_name => 'MGR_REPORTS_ASSIGNMENTS_DS',
                     p_c001            => o_mgr_person_number,
                     p_c002            => o_mgr_display_name,
                     p_c003            => lv_reportee_person_id,
                     p_c004            => lv_reportee_person_number,
                     p_c005            => lv_wr_period_of_service_id,
                     p_c006            => lv_wr_primary_flag,
                     p_c007            => apex_exec.get_varchar2(l_rc2, 'ASSIGNMENTID'),
                     p_c008            => apex_exec.get_varchar2(l_rc2, 'ASSIGNMENTNUMBER'),
                     p_c009            => apex_exec.get_varchar2(l_rc2, 'ASSIGNMENTNAME'),
                     p_c010            => apex_exec.get_varchar2(l_rc2, 'ASSIGNMENTSTATUSTYPE'),
                     p_c011            => apex_exec.get_varchar2(l_rc2, 'PRIMARYASSIGNMENTFLAG'),
                     p_c012            => apex_exec.get_varchar2(l_rc2, 'ASSIGNPRIMARYFLAG')
                  );
               END LOOP;
               apex_exec.close_array(l_rc2);
            END LOOP;
            apex_exec.close_array(l_rc2);
         END LOOP;
         apex_exec.close(l_rc2);
      END IF;
   --------------------------------------------------------------------------------------------------------------------------------------------
   --------------------------------------------------------------------------------------------------------------------------------------------   
   EXCEPTION
      WHEN OTHERS THEN
         apex_exec.close(l_rc1);
         apex_exec.close(l_rc2);
   END get_emp_details;

END xx_hcm_element_entry_extn_pkg;