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
Select a Page Type: Form
Next >
Next>
Table / View Name: XXAPX_FILES
Next >
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 >
Next >
Primary Key Column 1: FILE_ID (defaulted)
Next >
Sequence: XXOIC_FILES_RATE_TABLE_S
Next >
Next >
Create Button Label: Submit
Show Save Button: No
Show Delete Button: No
Next>
After Page Submit and Processing Branche to Page: 10
After Cancel Button Pressed Branche to Page: 10
Next >
Double Click Region Button CANCEL (or choose Edit after right mousclick)
Double Click Region Button CREATE (or choose Edit after right mouseclick)
Condition Type: Button Not Conditional
Apply Changes
Doubleclick on P10_FILE_NAME
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
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
Right mouseclick and click on Create
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 >
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 >
Next >
Create Process
Double click on proces ‘reset page’
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
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:
| Seq | Parameter | Description + Prompt | Value Set |
| 10 | p_file_id | File ID | FND_NUMBER |
| 20 | p_file_name | Filename | 240 Characters |
| 30 | p_user_id | User ID | FND_NUMBER |
| 40 | p_resp_id | Responsibility ID | FND_NUMBER |
| 50 | p_resp_appl_id | Responsibility Application ID | FND_NUMBER |
| 60 | p_sec_group_id | Security Group ID | FND_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=
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.
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.
ReplyDeleteI 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.