Thursday, July 17, 2014

Upload a file in APEX and write it to the UNIX filesystem


A csv file is uploaded from Windows into a blob via APEX. Subsequently this blob is written to a file on Unix. There is a lot of code on the internet that do the job, but at the end you will see ctrl-M at the end of each line when opening the file in the vi editor. This is due to the difference between Windows and and Unix. Windows puts a carriage return and linefeed (CR/LF) at the end of each line whereas Unix is only using a linefeed. The extra symbol show up as ^M in vi. To get rid of this character we have to cast the raw (chunck of) data to char and then replace CHR(13) (the CR) by nothing before writing it to the filesystem. The rest of te code in this procedure write_dos_blob_to_unix_file is pretty much the same as other code you will find on the internet.

The page in Application Express (APEX) is similar to upload page I created earlier (see post Upload a file in APEX and submit concurrent request in EBS R12). Instead of submitting a request in the Oracle E-Business Suite R12 (EBS R12), the additional custom process ('Write BLOB to filesystem') calls a routine that writes the blob content to a unix file. This routine is added to the apex_global package, where it will run as APPS user (because of the authid clause). This was necessary because APPS already has the rights to write to the unix filesystem via directory 'XXCUST_TEMP_FILES'. (Here directory is an Oracle database object that contains the actual path on the filesystem and where rights can be assigned to).


Source in the process definition of the additional process 'Write BLOB to filesystem':

BEGIN
   apps.apex_global.write_dos_blob_to_unix_file('XXCUST_TEMP_FILES',TO_NUMBER(V('P12_FILE_ID')));
END;


The code that has been added to the apex_global package:

CREATE OR REPLACE PACKAGE apex_global AUTHID DEFINER AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    APEX_GLOBAL.pks
   *
   * DESCRIPTION
   *    Routines for collaboration of APEX with EBS
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/

   ...

   PROCEDURE write_dos_blob_to_unix_file (
      p_location  IN VARCHAR2,
      p_file_id   IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY apex_global AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    APEX_GLOBAL.pkb
   *
   * DESCRIPTION
   *    Routines for collaboration of APEX with EBS
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/

   ...

   PROCEDURE write_dos_blob_to_unix_file (
      p_location  IN VARCHAR2,
      p_file_id   IN NUMBER)
   IS
      c_chunck_size  CONSTANT   NUMBER := 32760;

      l_file_id                 NUMBER; 
      l_filename                VARCHAR2(100);

      l_blob                    BLOB;
      l_blob_length             NUMBER;
      l_bytes_written           NUMBER := 0;
      l_chunck                  RAW(32760);
      l_chunck_size             NUMBER;

      l_output                  utl_file.file_type;

   BEGIN
      -- select filename, blob incl length into variables
      SELECT file_name
      ,      dbms_lob.getlength(file_data)
      ,      file_data
      INTO   l_filename
      ,      l_blob_length
      ,      l_blob
      FROM   xxoic_files_pons
      WHERE  file_id = p_file_id;

      -- define output directory
      l_output := utl_file.fopen(p_location, l_filename, 'W', c_chunck_size);

      -- if small enough for a single write
      IF l_blob_length <= c_chunck_size THEN
         utl_file.put(l_output, replace(utl_raw.cast_to_varchar2(l_blob),chr(13)));
         utl_file.fflush(l_output);
      ELSE -- write in pieces
         l_chunck_size := c_chunck_size;
         WHILE l_bytes_written < l_blob_length
         LOOP
            l_chunck_size := least(c_chunck_size, l_blob_length - l_bytes_written);
            dbms_lob.read(l_blob, l_chunck_size, l_bytes_written + 1, l_chunck);

            utl_file.put(l_output, replace(utl_raw.cast_to_varchar2(l_chunck),chr(13)));
            utl_file.fflush(l_output); 

            l_bytes_written := l_bytes_written + l_chunck_size;
        END LOOP; 
      END IF;
      utl_file.fclose(l_output);
   END;
   
END;
/

Wednesday, July 16, 2014

Upload a file in APEX and submit concurrent request in EBS R12


One of the pages I built in Application Express (APEX) as an extension to the Oracle E-Business Suite R12 (EBS R12) is to upload an Excel file into a blob field and subsequently submit a concurrent process (batch job) in EBS R12 to process the Excel file. Although interesting too, in this post I won't bother you with how to process an Excel file in PL/SQL / Java. I will show how I managed solving the challenge with APEX, EBS R12 and the underlying database and moreover I will explain how I overcame some problems I encountered. Some special points for attention:
- Validation of the filename
- Success message
- Submit button
- Submit concurrent request in Oracle EBS
- Set 'environment' in submitted process

Validation of the filename
I had a look at the sample application 'Sample File Upload and Download' that comes along with APEX (see tab Packaged Applications on the Application Builder page). I noticed that validation of the filename is done via a database trigger (before insert). In case of an error a raise_application_error is done, resulting in a not so user friendly error messages. Due to the way APEX handles a field that is displayed as 'File Browse...' it is not possible to catch the filename in an earlier stage. However, it should be possible to solve this problem using javascript, but I am not a fan of such a contrived solution (my opinion).

Success message
In APEX it seems not to be possible to include a variabele in a Process Success Message that can be entered on Process definition page. After the concurrent process has been submitted in EBS R12 the user wants to see the request number, necessary to monitor the progress of the request and to view the request log in Oracle EBS.This is solved by calling the APEX routine g_print_success_message that can handle a composed string containing the request number. 

Submit button
When testing the page, the Submit button disappeared after an error in the filename. This error is set by a before insert database trigger (so after the regular validations / exception to the normal flow APEX works). When the page has been created via the create page wizzard (normal way of working) then default the Condition at the Button definition page is set to check whether FILE_ID is NULL. However, the trigger that raises the error runs after the process that retrieves the primary key / fills the FILE_ID (Process 'Get PK'). When returning on the page the Submit button has disappeared because now FILE_ID has a value. The solution is to change the Condition Type at the Button definition page to 'Button Not Conditional'.

Submit concurrent request in Oracle EBS R12
After the Excel file has been inserted in the database, we want to start a batch job inside EBS R12 that processes the file. This custom process was already developed in the Oracle E-Business R11i implementation, but it is interesting what changes we had to make in order to get it working when submitted from APEX. Besides passing the file identification (little change), now we had to add some extra parameters to the concurrent program (also see next paragraph about setting the environment in the submitted process).
In the Page Processing lane of the APEX page in hand, we added a Process 'Start concurrent request' after the (standard) Process 'Process Row of ....'. In this process an anonymous block (of PL/SQL code) is entered in 'Source'. Submitting the concurrent process could be achieved by calling the standard Oracle EBS routine fnd_request.submit_request. It turned out that in order to get the concurrent program actually running we had to run apps_initialize first.
However, it is not allowed to call the standard Oracle routines straight. APEX has no permission to do so and for security reasons it is not preferred to solve this by granting execute rights to APEX_EBS on those routines. As a central point of access we created in the APPS schema (standard Oracle EBS user, that can access all data / all routines) a package called apex_global (as in Oracle's white paper). It is defined with an authid clause of AUTHID DEFINER, which means that each routine from this package will be run with the owners rights (APPS) as opposed to invokers rights (current user which is APEX_EBS). Of course APEX_EBS must be granted execute rights to this package.
So in the anonymous block on the Process definition (source) you will see a call to a routine in the apps.apex_global package and in the apps.apex_global package you will see the call to the standard Oracle EBS routine with exactly the same parameters.

Set 'environment' in submitted process
A concurrent process submitted from outside Oracle EBS means that all context has been lost. Normally, when a concurrent request is submitted in Oracle EBS, Oracle knows what the user is allowed to do / what data the user is allowed to see. This is managed by some settings.These settings can also be achieved by running the standard Oracle EBS routines fnd_global.apps_initialize and depending on the MOAC setting (multiple organization access control) also mo_global.init or mo_global.set_policy_context (for the exact syntax see further down this post).
Consequence is that we have to pass the necessary information to the concurrent process. Fortunattely all this information is retrieved from the session information we when the APEX page was launched by Oracle EBS (see my post 'Authentication, Authorization and more Shared Components'). This more or less confidential information is passed to the concurrent program via parameters.

Creating the page step by step

1. Table and sequence definition
2. Page definition
3. Define apps package apex_global (partly)
4. Definition of concurrent program in Oracle EBS R12
5. Oracle EBS routine submitted in APEX
6. Add function to EBS R12

1. Table and sequence definition

Notice that the before insert or update on apex_ebs.xxapx_files is not only for the validation of the filename, but also fills the audit information (created_by, creation_date, etc) and moreover the org_id (company) that is copied from the Application Items.

Do the following as database user APPS (not possible in the SQL Workshop in APEX):

   CREATE OR REPLACE FORCE VIEW APPS.XXAPX_USER_V
   (  USER_ID,
      USER_NAME,
      START_DATE,
      END_DATE,
      DESCRIPTION,
      EMAIL_ADDRESS
   )
   AS
      SELECT user_id,
             user_name,
             start_date,
             end_date,
             description,
             email_address
      FROM fnd_user;
/

   GRANT SELECT ON APPS.XXAPX_USER_V TO APEX_EBS;
/

   DECLARE
     l_count NUMBER := 0;
   BEGIN
     SELECT COUNT(*) INTO l_count FROM all_tables WHERE table_name='XXAPX_FILES' AND owner = 'APEX_EBS'; 
     IF l_count = 1 THEN
       EXECUTE IMMEDIATE 'DROP TABLE APEX_EBS.XXAPX_FILES';
       EXECUTE IMMEDIATE 'DROP SYNONYM APPS.XXAPX_FILES';
     END IF;
   END;
/ 
   
   CREATE TABLE APEX_EBS.XXAPX_FILES 
   (  FILE_ID                NUMBER,
      FILE_NAME              VARCHAR2(256 BYTE), 
      FILE_CONTENT_TYPE      VARCHAR2(256 BYTE), 
      LANGUAGE               VARCHAR2(4 BYTE)  DEFAULT (USERENV('LANG')),
      ORACLE_CHARSET         VARCHAR2(30 BYTE) DEFAULT ( SUBSTR(USERENV( 'LANGUAGE'),INSTR(USERENV('LANGUAGE'),'.')+1) ),
      FILE_FORMAT            VARCHAR2(10 BYTE),        
      FILE_DATA              BLOB, 
      UPLOAD_DATE            DATE,
      ORG_ID                 NUMBER,
      CREATION_DATE          DATE, 
      CREATED_BY             VARCHAR2(255), 
      LAST_UPDATE_DATE       DATE, 
      LAST_UPDATED_BY        VARCHAR2(255), 
      CONSTRAINT XXAPX_FILES_PK PRIMARY KEY (FILE_ID) ENABLE
   );
/

   CREATE SYNONYM APPS.XXAPX_FILES FOR APEX_EBS.XXAPX_FILES;
/

   CREATE OR REPLACE TRIGGER apex_ebs.xxapx_files_biu 
      BEFORE INSERT OR UPDATE ON apex_ebs.xxapx_files
   FOR EACH ROW
      DECLARE
         l_ebs_user_name   VARCHAR2(255);
      
      FUNCTION get_ebs_user_name
         RETURN VARCHAR2
      IS
         l_user_id         NUMBER;
         l_user_name       VARCHAR2(255);
      BEGIN
         l_user_id := apex_util.get_session_state('EBS_USER_ID');

         BEGIN
            SELECT user_name
            INTO   l_user_name
            FROM   apps.xxapx_user_v
            WHERE  user_id = l_user_id;
         
         EXCEPTION
            WHEN OTHERS THEN
               l_user_name := NULL;            
         END;         

         RETURN l_user_name;         
      END;
   BEGIN
      IF LENGTH(:NEW.file_name)<4 THEN
         raise_application_error(-20000, 'Please enter a correct filename (e.g. "XXAPX_<name>.xls")');
      ELSIF (UPPER(SUBSTR(:NEW.file_name,1,5)) <> 'XXAPX') THEN 
         raise_application_error(-20000,  'The filename needs to start with ''XXAPX''.');      
      ELSIF SUBSTR(:NEW.file_name,-4) <> '.xls' THEN 
         raise_application_error(-20000,  'File must be Excel and extension .xls');      
      ELSIF NVL(dbms_lob.getlength(:NEW.file_data),0) > 15728640 THEN
          raise_application_error(-20000, 'The size of the uploaded file was over 15MB. Please upload a smaller sized file.');
      ELSE
         l_ebs_user_name := get_ebs_user_name;
        
         IF INSERTING THEN
             :NEW.creation_date := SYSDATE;
             :NEW.created_by :=  NVL(l_ebs_user_name,NVL(wwv_flow.g_user,USER));
         END IF;
         
         IF INSERTING OR UPDATING THEN
             :NEW.org_id := apex_util.get_session_state('EBS_ORG_ID');
             :NEW.last_update_date := SYSDATE;
             :NEW.last_updated_by  := NVL(l_ebs_user_name,NVL(wwv_flow.g_user,USER));
         END IF;
      END IF;   
   END;
/

   ALTER TRIGGER apex_ebs.xxapx_files_biu ENABLE
/

   -- Sequence for ID on APEX_EBS.XXAPX_FILES     
   DECLARE
     l_count NUMBER := 0;
   BEGIN
     SELECT COUNT(*) INTO l_count FROM all_sequences WHERE sequence_name='XXAPX_FILES_S' AND sequence_owner = 'APEX_EBS'; 
     IF l_count = 1 THEN
       EXECUTE IMMEDIATE 'DROP SEQUENCE APEX_EBS.XXAPX_FILES_S';
       EXECUTE IMMEDIATE 'DROP SYNONYM APPS.XXAPX_FILES_S';
     END IF;
   END;
/

   CREATE SEQUENCE APEX_EBS.XXAPX_FILES_S
     MINVALUE 1 
     MAXVALUE 999999999999999 
     INCREMENT BY 1 
     START WITH 1 
     NOCACHE 
     NOORDER 
     NOCYCLE;
/

   CREATE OR REPLACE SYNONYM APPS.XXAPX_FILES_S FOR APEX_EBS.XXAPX_FILES_S;
/


Do the following as database user APEX_EBS (can be done in the SQL Workshop in APEX):

  GRANT ALL ON APEX_EBS.XXAPX_FILES TO APPS WITH GRANT OPTION;
  GRANT ALL ON APEX_EBS.XXAPX_FILES_S TO APPS WITH GRANT OPTION;
/


2. Page definition

Most important part is the source in the process 'Start concurrent Process', a PL/SQL anonymous block that submits a concurrent process in EBS R12 and shows the request number assigned.



Application Builder
 Create Page >

Select a Page Type: Form
Next >
Form on a Table or View
Next>
Table / View Owner: APEX_EBS (default)
Table / View Name: XXAPX_FILES
Next >
Page Number: 10
Page Name: Upload Excel File
Page Title: Upload Excel File
Region Template: Form Region (default)
Breadcrumb: do not add breadcrumb region to page (default)
Next >

Do not use tabs
Next >
Primary Key Type: Select Primary Key Column(s)
Primary Key Column 1: FILE_ID (defaulted)
Next >
Source Type: Existing sequence
Sequence: XXOIC_FILES_RATE_TABLE_S
Next >
Select Column(s): all (default; Will hide all columns after all, except FILE_DATA)
Next >
Changed from default:
Create Button Label: Submit
Show Save Button: No
Show Delete Button: No
Next>
Branching: stay on the page after Page Submit and Cancel (cancel button will de removed later on)
After Page Submit and Processing Branche to Page: 10
After Cancel Button Pressed Branche to Page: 10
Next >
Create >
Edit Page >
Page Rendering: Upload Rate Table > Regions > Body > Upload Rate Table > Region Buttons
Double Click Region Button CANCEL (or choose Edit after right mousclick)
Press Delete and confirm delete action.
Page Rendering: Upload Rate Table > Regions > Body > Upload Rate Table > Region Buttons
Double Click Region Button CREATE (or choose Edit after right mouseclick)
Default there is a check whether FILE_ID is NULL. This causes the ‘Submit’ button to disappear in case an error is raised by a database trigger (where validation on FILE_NAME takes place). In that case FILE_ID already has a value.
Condition Type: Button Not Conditional
Apply Changes
Page Rendering: Upload Rate Table > Regions > Body > Upload Rate Table > Items
Doubleclick on P10_FILE_NAME
Display As: Hidden

Do the same for the rest of the Page Items, except for Page Item named ‘P10_FILE_DATA’.
Browse to the next Page Item by pressing >
After the last Page Item press Apply Changes
Values for Page Item ‘P10_FILE_DATA’:

Label :
Label: File Name
Settings:
Required: Yes
MIME Type Column: FILE_CONTENT_TYPE
Filename column: FILE_NAME
Character Set Column: ORACLE_CHARSET
BLOB last updated Column: UPLOAD_DATE
Display Download Link: No
Page Processing: Processing > Processes
Right mouseclick and click on Create
Process Type: PL/SQL
Process Attributes:
Name: Start Concurrent Request
Sequence: 50 (defaulted as latest process; Will renumber process ‘Reset Page’ next)
Point: On Submit – After Computations and Validations (defaulted)
Next >
PL/SQL anonymous block that submits a concurrent process in Oracle E-Business Suite and show the request number assigned. In APEX it seems not to be possible to include a variabele in a Success message. Therefore we make a call to the APEX routine apex_application.g_print_success_message.

DECLARE
   CURSOR c_filename (b_file_id NUMBER)
   IS
     SELECT file_name
     FROM xxapx_files
     WHERE 1=1
     AND file_id = b_file_id;

   l_request_id  NUMBER; 
   l_filename    VARCHAR2(100);
   
   l_user_id         NUMBER := TO_NUMBER(V('EBS_USER_ID'));
   l_resp_id         NUMBER := TO_NUMBER(V('EBS_RESP_ID'));
   l_resp_appl_id    NUMBER := TO_NUMBER(V('EBS_RESP_APPL_ID')); 
   l_sec_group_id    NUMBER := TO_NUMBER(V('EBS_SEC_GROUP_ID'));   

BEGIN
   -- initialiseren Oracle Apps
   apps.apex_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id,l_sec_group_id);
   
   OPEN c_filename (TO_NUMBER(V('P10_FILE_ID')));
   FETCH c_filename INTO l_filename;
   CLOSE c_filename;

   l_request_id := apps.apex_global.submit_request
                    ( application => 'XXCUST'
                    , program     => 'XXCUST_PROCESS_XLS_FILE'
                    , argument1   => V('P10_FILE_ID')
                    , argument2   => l_filename
                    , argument3   => to_char(l_user_id)
                    , argument4   => to_char(l_resp_id)
                    , argument5   => to_char(l_resp_appl_id)
                    , argument6   => to_char(l_sec_group_id));
   apex_application.g_print_success_message := 'Request ' || to_char(l_request_id) || ' has been started';
END;

Next >

The Succes Message is managed by the PL/SQL code. No Error Message.
Next >
No conditions, just like the by the APEX wizard already created processes.
Create Process
Page Processing: Processing > Processes
Double click on proces ‘reset page’
Process Point
Sequence: 70
Apply Changes


3. Define apps package apex_global (partly)

Do the following as database user APPS (not possible in the SQL Workshop in APEX):

CREATE OR REPLACE PACKAGE apex_global AUTHID DEFINER AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    APEX_GLOBAL.pks
   *
   * DESCRIPTION
   *    Routines for collaboration of APEX with EBS
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/
   
   FUNCTION check_ebs_credentials
      RETURN BOOLEAN;
      
   PROCEDURE apps_initialize (
      user_id           IN NUMBER,
      resp_id           IN NUMBER,
      resp_appl_id      IN NUMBER,
      security_group_id IN NUMBER DEFAULT 0,
      server_id         IN NUMBER DEFAULT -1);

   FUNCTION submit_request (
      application IN VARCHAR2 DEFAULT NULL,
      program     IN VARCHAR2 DEFAULT NULL,
      description IN VARCHAR2 DEFAULT NULL,
      start_time  IN VARCHAR2 DEFAULT NULL,
      sub_request IN BOOLEAN  DEFAULT FALSE,
      argument1   IN VARCHAR2 DEFAULT CHR(0),
      argument2   IN VARCHAR2 DEFAULT CHR(0),
      argument3   IN VARCHAR2 DEFAULT CHR(0),
      ...
      argument100 IN VARCHAR2 DEFAULT CHR(0))
      RETURN NUMBER;

END;
/

GRANT EXECUTE ON APPS.APEX_GLOBAL TO APEX_EBS;
/

CREATE OR REPLACE PACKAGE BODY apex_global AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    APEX_GLOBAL.pkb
   *
   * DESCRIPTION
   *    Routines for collaboration of APEX with EBS
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/
   
   FUNCTION check_ebs_credentials
      RETURN BOOLEAN
   IS
      c_ebs             VARCHAR2(240) := 'E-Business Suite';
      
      l_authorized      BOOLEAN;
      l_user_id         NUMBER;
      l_resp_id         NUMBER;
      l_resp_appl_id    NUMBER;    
      l_sec_group_id    NUMBER;
      l_org_id          NUMBER;
      l_time_out        NUMBER;
      l_ebs_url         VARCHAR2(100);
      l_appl_name       VARCHAR2(240);

      CURSOR get_apps_credentials
      IS
         SELECT iss.user_id
         ,      iss.responsibility_id
         ,      iss.responsibility_application_id
         ,      iss.security_group_id
         ,      iss.org_id
         ,      iss.time_out
         ,      isa.value
         FROM  apps.icx_sessions iss
         ,     apps.icx_session_attributes isa 
         WHERE iss.session_id = apps.icx_sec.getsessioncookie
         AND   isa.session_id = iss.session_id
         AND   isa.name = '_USERORSSWAPORTALURL';
         
      CURSOR get_appl_name (b_appl_id NUMBER)
      IS
         SELECT application_name
         FROM   apps.fnd_application_tl
         WHERE  application_id = b_appl_id
         AND    language = USERENV('LANG');
         
   BEGIN
      OPEN get_apps_credentials;
      FETCH get_apps_credentials 
      INTO l_user_id
      ,    l_resp_id 
      ,    l_resp_appl_id
      ,    l_sec_group_id
      ,    l_org_id
      ,    l_time_out
      ,    l_ebs_url;
      
      IF get_apps_credentials%NOTFOUND THEN  
         l_authorized := FALSE;
      ELSE
         l_authorized := TRUE;
         
         OPEN get_appl_name(l_resp_appl_id);
         FETCH get_appl_name INTO l_appl_name;
         IF get_appl_name%NOTFOUND THEN
            l_appl_name := c_ebs;
         END IF;
         CLOSE get_appl_name;

         apex_util.set_session_state('EBS_USER_ID',TO_CHAR(l_user_id));
         apex_util.set_session_state('EBS_RESP_ID',TO_CHAR(l_resp_id));
         apex_util.set_session_state('EBS_RESP_APPL_ID',TO_CHAR(l_resp_appl_id));
         apex_util.set_session_state('EBS_SEC_GROUP_ID',TO_CHAR(l_sec_group_id));
         apex_util.set_session_state('EBS_ORG_ID',TO_CHAR(l_org_id));      
--       apex_util.set_session_state('EBS_TIME_OUT',TO_CHAR(l_time_out));      
         apex_util.set_session_state('EBS_URL',l_ebs_url);     
         apex_util.set_session_state('EBS_APPLICATION_NAME',l_appl_name);    

         apex_util.set_session_max_idle_seconds(l_time_out*60,'APPLICATION');         
      END IF;
      
      CLOSE get_apps_credentials;
      RETURN l_authorized;
      
   EXCEPTION
      WHEN OTHERS THEN
         IF get_apps_credentials%ISOPEN THEN CLOSE get_apps_credentials; END IF;
         RETURN FALSE;      
   END;

   PROCEDURE apps_initialize (
      user_id           IN NUMBER,
      resp_id           IN NUMBER,
      resp_appl_id      IN NUMBER,
      security_group_id IN NUMBER DEFAULT 0,
      server_id         IN NUMBER DEFAULT -1) IS 
   BEGIN
      fnd_global.apps_initialize
         ( user_id
         , resp_id
         , resp_appl_id
         , security_group_id
         , server_id);
   END;

   FUNCTION submit_request (
      application IN VARCHAR2 DEFAULT NULL,
      program     IN VARCHAR2 DEFAULT NULL,
      description IN VARCHAR2 DEFAULT NULL,
      start_time  IN VARCHAR2 DEFAULT NULL,
      sub_request IN BOOLEAN  DEFAULT FALSE,
      argument1   IN VARCHAR2 DEFAULT CHR(0),
      argument2   IN VARCHAR2 DEFAULT CHR(0),
      argument3   IN VARCHAR2 DEFAULT CHR(0),
      ...
      argument100 IN VARCHAR2 DEFAULT CHR(0))
      RETURN NUMBER
   IS 
   BEGIN
      RETURN fnd_request.submit_request
               ( application
               , program    
               , description
               , start_time 
               , sub_request
               , argument1
               , argument2
               , argument3
               ...
               , argument100);
   END;
END;
/
 

4. Definition of concurrent program in Oracle EBS R12

This is the definition of the concurrent program in Oracle EBS R12 that is submitted while processing the APEX page. This request wiil be submitted from outside EBS, so we need to pass the necessary parameters to be able to set the right context in the Oracle EBS executable by calling  Oracle's procedure apps_initialize. We will pass back the session information retrieved from icx_session when the APEX page was launched (also see authorization scheme Check EBS Credentials posted earlier at Authentication, Authorization and more Shared Components).

Path in EBS R12: System Administrator: Concurrent > Program > Executable


Executable: Process Uploaded Excel File
Short Name: XXCUST_PROCESS_XLS_FILE
Application: XXCUST Custom Application
Description: Process Uploaded Excel File
Execution Method: PL/SQL Stored Procedure
Execution File Name: XXCUST_PROCESS_XLS_FILE.main


 Path in EBS R12: System Administrator: Concurrent > Program > Define


Program: Process Uploaded Excel File
Short Name: XXCUST_PROCESS_XLS_FILE
Application: XXCUST Custom Application
Description: Process Uploaded Excel File

Executable Name: Process Uploaded Excel File
Executable Method: PL/SQL Stored Procedure

Parameters (B)


Parameters:

SeqParameterDescription + PromptValue Set
10p_file_idFile IDFND_NUMBER
20p_file_nameFilename240 Characters
30p_user_idUser IDFND_NUMBER
40p_resp_idResponsibility IDFND_NUMBER
50p_resp_appl_idResponsibility Application IDFND_NUMBER
60p_sec_group_idSecurity Group IDFND_NUMBER


5. Oracle EBS routine submitted in APEX

Just an outline of the code.
  • In Oracle EBS the output parmeters p_errbuf and p_retcode are mandatary for procedures that are called from a concurrent program / executable. When the p-errbuf returns 1 it will show yellow (warning) on the view request screen and when 2 is returned it will show red (error).
  • Also notice that the first statements in the main procedure are to set the environment, otherwise it cannot select data from 'MOAC tables'. Running apps_intiatialize is always necessary. Depending on the MOAC settings (Multi Organization Access Control) in Oracle EBS it is also necessary to run mo_global.init or in our situation (MOAC is activated, but only single org) run mo_global.set_policy_context with parameter 'S' (single) and the org_id of the organization applicable (retrieved from the profile option 'ORG_ID' that usually is set at responsability level which is set by running apps_initialize; this is the normal way of working in Oracle EBS).     

CREATE OR REPLACE PACKAGE apps.xxcust_process_xls_file AUTHID CURRENT_USER
AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    XXCUST_PROCESS_XLS_FILE.pks
   *
   * DESCRIPTION
   *    Process Uploaded Excel file
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/

   PROCEDURE main ( p_errbuf        OUT VARCHAR2
                  , p_retcode       OUT VARCHAR2
                  , p_file_id       IN  NUMBER
                  , p_file_name     IN  VARCHAR2
                  , p_user_id       IN  NUMBER
                  , p_resp_id       IN  NUMBER
                  , p_resp_appl_id  IN  NUMBER
                  , p_sec_group_id  IN  NUMBER
                  );

END xxcust_process_xls_file;
/

CREATE OR REPLACE PACKAGE BODY apps.xxcust_process_xls_file
AS
   /****************************************************************
   *
   * PROGRAM NAME
   *    XXCUST_PROCESS_XLS_FILE.pkb
   *
   * DESCRIPTION
   *    Process Uploaded Excel file
   *
   * CHANGE HISTORY
   * Who          When         What
   * ---------------------------------------------------------------
   * M. Weeren    01-07-2014   Initial Version
   *****************************************************************/
   
   PROCEDURE main ( p_errbuf        OUT VARCHAR2
                  , p_retcode       OUT VARCHAR2
                  , p_file_id       IN  NUMBER
                  , p_file_name     IN  VARCHAR2
                  , p_user_id       IN  NUMBER
                  , p_resp_id       IN  NUMBER
                  , p_resp_appl_id  IN  NUMBER
                  , p_sec_group_id  IN  NUMBER)
   IS
      -- initialiaze Oracle Apps
      fnd_global.apps_initialize(p_user_id,p_resp_id,p_resp_appl_id,p_sec_group_id);
      mo_global.set_policy_context('S',fnd_profile.value('ORG_ID'));
  

      ....
  

   END;
END xxcust_process_xls_file;
/


6. Add function to EBS R12

This paragraph shows how to make the APEX page available in EBS R12. Hereafter the function can be attached to any menu where the user wants to get access to the APEX page (System Administrator: Application > Menu).

Path in EBS R12: System Administrator: Application > Function

Function:APEX UPLOAD XLS
User Function Name: APEX Upload Exel File
Description: APEX Upload Exel File
Type: SSWA jsp function
HTML Call: GWY.jsp?targetAppType=APEX&p=100:20:::::
(In this example 100 is the ID of the application in APEX and 20 is the number of the Page)




PS The complete GWY syntax is: GWY.jsp?targetAppType=APEX& p=: :::::. In this way it is possible to pass some session values via the URL to APEX: GWY.jsp?targetAppType=APEX&p=100:10:::::EBS_RESP_ID, EBS_APP_ID,EBS_SEC_GROUP:[RESPONSIBILITY_ID],[RESP_APPL_ID], [SECURITY_GROUP_ID].
But no USER_ID and no ORG_ID. Therefore in our application session parameters are retrieved from table icx_session using the function icx_sec.getsessioncookie to determine the actual session_id. Moreover it is safer not to pass parameters via the URL, despite the ability of APEX to manage whether manupalation of parameters via the URL must be ignored or not.

Thursday, July 10, 2014

Make an APEX page look like an Oracle EBS R12 page


I made the folowing changes to let the APEX page more resemble an E-Business Suite R12 page:
  • First of all, I created several Application Items (Shared Components > Logic) including EBS_APPLICATION_NAME and EBS_URL. These will be set in a routine check_ebs_credentials that will run when a page is launched (part of the Authorization Scheme I explained earlier).
  • For the User Interface select APEX Theme 20 (Traditional Blue), a standard theme that comes along with APEX (one of the Legacy Themes)
  • Logo (Shared Components > User Interface > User Interface Attributes)
    The Oracle Logo displayed in Oracle EBS R12 can also be displayed in the APEX application:
    • First copy the file FNDSSCORP.gif  $OA_MEDIA (EBS) to the directory 'images' in APEX
    • Logo Type: Image 
    • Logo: /i/FNDSSCORP.gif 
    • Logo attributes: alt="Oracle Logo" title="Oracle Logo" width="155" heigth="20" border="0" 
  • Navigation Bar Entries (Shared Components > Navigation)
    In the E-Business Suite R12, self-service pages (HTML pages) like the APEX pages can be called from a menu in forms (java program) or a menu in the browser (HTML page). In the first case the HTML page is opened in a new browser window and when done the user has to close that window. In the latter case the HTML page appears in the same browser window and when done the user can navigate back to the menu. This is a bit of a problem for navigation. In the normal E-Busisness Suite self-service pages (using OAF) this is managed correctly: in the navigation bar you will see ‘Close window’ or ‘Home’. Because it seems not possible to determine how the APEX page is launched, only the ‘Home’ entry has been provided. For closing a window all browsers have built-in possibilities.
    • Remove Logout
    • Add Home. For 'URL Target' enter &EBS_URL. (don't forget the dot)
      EBS_URL holds the value icx_session_attribute '_USERORSSWAPORTALURL' but can also be composed of the value of profile option APPS_SERVLET_AGENT concatenated with /OA.jsp?OAFunc=OAHOMEPAGE#
  • Template (Shared Components > Templates > Type: Page, Name: No Tabs).
    In the application I only make use of pages that use the page template named 'No tab'. The application name is added to the page header (however defined in the page body) and the user name has been removed from the footer as well as a copyright tekst was added to the bottom righthand corner. I couldn’t find how to set the built-in application substitution strings, so I made changes to the templates, however setting of the built-in application substitution strings would result in a more generic solution.
    • Definition > Body: 
      • The application name is added stored in the Application Item EBS_APPLICATION_NAME.
      • The style attribute is necessary to display the name with the correct format. Image blank.gif is used to force some space between the logo and the application name. 
      • Also two blank lines are added (one above the logo; one beneath the logo):

        <div id="t20PageHeader">
        <table border="0" cellpadding="0" cellspacing="0" summary="">
        <tr><td colspan=3> </td></tr>
        <tr>
        <td id="t20Logo" valign="top">#LOGO#<br />#REGION_POSITION_06#</td>
        <td id="t20HeaderMiddle" valign="top" width="100%"><img src="/i/blank.gif" width="20" border="0"><span title="EBS Application Name" style="font-family:Arial; color:#FFFFFF; font-size:16px; white-space:nowrap; font-weight:bold; vertical-align:top;">&EBS_APPLICATION_NAME.</span> #REGION_POSITION_07#<br /></td>
        <td id="t20NavBar" valign="top">#NAVIGATION_BAR#<br />#REGION_POSITION_08#</td>
        </tr>
        <tr><td colspan=3> </td></tr>
        </table>
        </div>
        <div id="t20BreadCrumbsLeft">#REGION_POSITION_01#</div>
        <table border="0" cellpadding="0" cellspacing="0" summary="" id="t20PageBody" width="100%" height="70%">
        <td width="100%" valign="top" height="100%" id="t20ContentBody">
        <div id="t20Messages">#GLOBAL_NOTIFICATION##SUCCESS_MESSAGE##NOTIFICATION_MESSAGE#</div>
        <div id="t20ContentMiddle">#BOX_BODY##REGION_POSITION_02##REGION_POSITION_04#</div>
        </td>
        <td valign="top" width="200" id="t20ContentRight">#REGION_POSITION_03#<br /></td>
        </tr>
        </table>
    • Definition > Footer 
      • Added an extra line to look it more like EBS R12. 
      • Also a copyright text is added like in EBS R12. 
      • The username has been deleted from the footer (&APP_USER.):

        <table border="0" cellpadding="0" cellspacing="0" summary="" id="t20PageFooter" width="100%">
        <tr><td colspan=3> </td></tr>
        <tr>
        <td id="t20Left" valign="top"><span id="t20UserPrompt"></span><br /></td>
        <td id="t20Center" valign="top">#REGION_POSITION_05#</td>
        <td id="t20Right" valign="top"><span id="t20Customize">#CUSTOMIZE#Copyright (c) 2014, Marc Weeren. All rights reserved.</span><br /></td>
        </tr>
        </table>
        <br class="t20Break"/>
        #FORM_CLOSE#
        #DEVELOPER_TOOLBAR#
        #GENERATED_CSS#
        #GENERATED_JAVASCRIPT#
        </body>
        </html>


Before:


After:


Wednesday, July 9, 2014

Authentication, Authorization and more Shared Components


So far I have not seen any customer who used Oracle Single Sign-on (OSSO) or Oracle Access Manager (OAM) collaborating with the Oracle E-Business Suite. So, I could not take advantage of the pre-configured Authentication Schemes in Oracle Application Express. In Oracle's White Paper a solution is described when using custom authentication for Oracle E-Business Suite Oracle. However, that solution is more suitable for a stand-alone application. For our problem, we are looking for a fully integrated application and therefor I developed another solution using the cookie settings in the icx_sessions table of Oracle E-Business Suite. Moreover, when checking the session variables I store more relevant Oracle E-Business Suite session information in Oracle Application Express Application Items ...

Application Items (Shared Components > Logic)
Scope: Application
Session State Protection: Restricted - May not be set from browser

Application Item NameComments
EBS_USER_IDKey to User; To check EBS authorization and to set EBS context (icx_sessions)
EBS_RESP_IDKey to Responsibility; To check EBS authorization and to set EBS context (icx_sessions)
EBS_RESP_APPL_IDKey to Responsibility Application; To check EBS authorization and to set EBS context (icx_sessions)
EBS_SEC_GROUP_IDKey to Security Group; To check EBS authorization and to set EBS context (icx_sessions)
EBS_TIME_OUTSession Time Out in Oracle E-Business Suite (icx_sessions)
EBS_URLURL to return to EBS Homepage from APEX (icx_session_attributes)
EBS_ORG_IDEBS ORG_ID (icx_sessions) - MO: Operating Unit from Responsibility
EBS_APPLICATION_NAMETo be displayed at the left tophand corner (application_name from fnd_application_tl using EBS_RESP_APPL_ID)

The Application Items are used in queries or when setting the ‘environment’ (apps_initialize).

Authentication (Shared Components > Security)
The Oracle Application Express pages are directly launched from the E-Business Suite. Additional login is not desirable, so no Authentication Scheme.

Authorization (Shared Components > Security)
I created an Authorization Scheme 'Check EBS Credentials' that will check whether the user has a valid E-Business Suite session. If so, then session attributes are copied into the Application Items. If not, then an error message will be displayed that access is not allowed. The E-Business Suite function icx_sec.getsessioncookie is used to determine the session_id. This session_id is the key to retrieve additional information from the E-Business Suite tables icx_sessions and icx_session_attributes.

Authorization Schemes: Create> (B)
 
Next> (B)
 

Name: Check EBS Credentials
Scheme Type: PL/SQL Function Returning Boolean
PL/SQL Function Body:
   BEGIN
      RETURN apps.apex_global.check_ebs_credentials; 
   END;
Error message displayed when scheme violated:  "Access not allowed: No valid E-Business Suite session."
Evaluation point: once per page view
Create Authorizartion Scheme (B)

Code of function check_ebs_credentials (from package body apps.apex_global):

   FUNCTION check_ebs_credentials
      RETURN BOOLEAN
   IS
      c_ebs             VARCHAR2(240) := 'E-Business Suite';
      
      l_authorized      BOOLEAN;
      l_user_id         NUMBER;
      l_resp_id         NUMBER;
      l_resp_appl_id    NUMBER;    
      l_sec_group_id    NUMBER;
      l_org_id          NUMBER;
      l_time_out        NUMBER;
      l_ebs_url         VARCHAR2(100);
      l_appl_name       VARCHAR2(240);

      CURSOR get_apps_credentials
      IS
         SELECT iss.user_id
         ,      iss.responsibility_id
         ,      iss.responsibility_application_id
         ,      iss.security_group_id
         ,      iss.org_id
         ,      iss.time_out
         ,      isa.value
         FROM  apps.icx_sessions iss
         ,     apps.icx_session_attributes isa 
         WHERE iss.session_id = apps.icx_sec.getsessioncookie
         AND   isa.session_id = iss.session_id
         AND   isa.name = '_USERORSSWAPORTALURL';
         
      CURSOR get_appl_name (b_appl_id NUMBER)
      IS
         SELECT application_name
         FROM   apps.fnd_application_tl
         WHERE  application_id = b_appl_id
         AND    language = USERENV('LANG');
         
   BEGIN
      OPEN get_apps_credentials;
      FETCH get_apps_credentials 
      INTO l_user_id
      ,    l_resp_id 
      ,    l_resp_appl_id
      ,    l_sec_group_id
      ,    l_org_id
      ,    l_time_out
      ,    l_ebs_url;
      
      IF get_apps_credentials%NOTFOUND THEN  
         l_authorized := FALSE;
      ELSE
         l_authorized := TRUE;
         
         OPEN get_appl_name(l_resp_appl_id);
         FETCH get_appl_name INTO l_appl_name;
         IF get_appl_name%NOTFOUND THEN
            l_appl_name := c_ebs;
         END IF;
         CLOSE get_appl_name;

         apex_util.set_session_state('EBS_USER_ID',TO_CHAR(l_user_id));
         apex_util.set_session_state('EBS_RESP_ID',TO_CHAR(l_resp_id));
         apex_util.set_session_state('EBS_RESP_APPL_ID',TO_CHAR(l_resp_appl_id));
         apex_util.set_session_state('EBS_SEC_GROUP_ID',TO_CHAR(l_sec_group_id));
         apex_util.set_session_state('EBS_ORG_ID',TO_CHAR(l_org_id));      
         apex_util.set_session_state('EBS_TIME_OUT',TO_CHAR(l_time_out));      
         apex_util.set_session_state('EBS_URL',l_ebs_url);     
         apex_util.set_session_state('EBS_APPLICATION_NAME',l_appl_name);    

         apex_util.set_session_max_idle_seconds(l_time_out*60,'APPLICATION');         
      END IF;
      
      CLOSE get_apps_credentials;
      RETURN l_authorized;
      
   EXCEPTION
      WHEN OTHERS THEN
         IF get_apps_credentials%ISOPEN THEN CLOSE get_apps_credentials; END IF;
         RETURN FALSE;      
   END;

Setting the session timeout seems not to work. After a while the Oracle E-Business Suite session shows a timeout, but if you did not close the APEX page to upload a file, it still lets you upload and submit a file ... Is this a bug?

Security Attributes (Shared Components > Security)
Access to any page in the APEX application is not allowed when no E-Business Suite session is active. This is arranged by setting the Authorization Scheme as a Security Attribute. However, it is also possible to manage authorization per page. In the latter case don't set the authorization scheme as shared component.

Security > Security Attributes: Authorization
 
Authorization Scheme: Check EBS Credentials
Apply Changes (B)

PS: In an attempt to get the session timeout working, I also tried to set the Maximum Session Idle Time In Seconds to 1800 (default value E-Business Suite). This is also a Security Attribute: Session Timeout. I read somewhere that this was a a condition to get the session timeout working. Unfortunately, it didn't help. Besides setting the Maximum Session Idle Time In Seconds here at application level it was initially already set at instance level by the dba (Oracle Application Express Administration).

Sunday, July 6, 2014

Installation / Activation of APEX in E-Business Suite R12

See Oracle Support Document 1306563.1 Extending Oracle E-Business Suite Release 12 using Oracle Application Express (APEX) for a white paper and a few scripts to get the integration of Application Express and E-Business Suite R12 working, including a demo application. In short:

  • Install Application Express in the Oracle E-Business Suite database. Download the latest version of Application Express from download.oracle.com (Developer Tools) and follow the instructions
  • Install Oracle REST Data Services (former APEX listener) as Web Listener 
    • Preferred by Oracle over Oracle HTTP Server and Embedded PL/SQL Gateway
    • Works standalone; Light use: no application server necessary (Web Logic, Glassfish)
    • Also see the article Moving to the APEX Listener by Dimitri Gielis 
  • Log into Oracle Application Express Administration
    • Create Workspace
      • Workspace Name: APEX_EBS
      • Schema Name: APEX_EBS
    • Set security setting ‘Allow Public Upload’ (Manage Instance)
  • Apply interoperability patch 12316083

By the way, I didn't do the installation myself. It was done by the dba'ers of the party where the customer has outsourced / hosted it's E-Business Suite application.

Next, build your application in APEX: it’s all about rights !

At my custsomers site they have the next releases / versions:
- Oracle E-Business Suite: release 12.1.3
- Oracle Application Express: release 4.2.4.00.08
- Oracle Database: version 11.2.0.4.0
Oracle Rest Data Services: version 2.0.7.91.15.01

In the white paper I missed the part how to make the APEX pages more look and feel like EBS pages ...

Friday, July 4, 2014

Need for change

When upgrading an E-Business Suite implementation from R11i to R12 we ran into a problem. In R11i the customer had a couple of custom pages for uploading and downloading Excel sheets with some validation on the filename and submitting a concurrent process to process the uploaded Excel Sheet. Those pages were built with HTML PL/SQL (htp package) and made accessible via functions of Function Type: 'SSWA plsql function that opens a new window (Kiosk Mode)'. It appears to me that the first E-Business Suite HTML pages (self-service pages) were built using this technique. However, this Function Type is no longer working in R12, although the system still accepted this Funcion Type as a valid value. So, we had to look for an alternative:

  • (Re)use the generic Upload / Download functions introduced in R12 (System Administrator > Generic File Manager); 
  • Build static HTML pages and put this file under $OA_HTML directory (Function Type : 'JSP Interoperable with OA' and HTML Call: name of the file);
  • Build custom pages using OAF; 
  • Integrate Oracle Application Express (APEX) and develop the same functionality.

It wouldn't be interesting for this blog if we had not opted for the last option :-)