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.

1 comment:

  1. Hello I want to open a APEX page from the screen of EBS, not from the menu functions. Can you please suggest how we can do that.
    I have made a apex screen to upload some files. Now this screen should show up when we click on a Upload button in EBS screen. How can we do that, maintaining all the EBS session variables.

    ReplyDelete