Archive for January 2016
Please follow below steps to
export an application from PROD Instance :
1. Login to PROD APEX
Workspace.
2. Click on Application
builder icon as shown in below screenshot.
3. Click on Edit
button of the application that you want to export.We have to export Application No. 103 from PROD instance.
4. Below screen will appear. Now,Click on Export/Import icon.
5.Select Export
option and click Next.
5.Cick on Export Application
6.You will get f103.sql
export file for that application.
Please follow below steps to import
APEX Application successfully on another instance:
1. Check whether correct apex version is configured same as
PROD.
2. Login to Apex Environment(New Apex Instance).
3. After login, Run below query from SQL workshop to get
current workspace id.
4. Check whether above workspace id is matching with
workspace id in f103.sql file
(Application Exported from PROD) , If not then update the f103.sql file with above workspace id (new workspace id of the
instance workspace on which we are installing application). And save the
changes.
5. Now, Go to application builder and click on Import button as shown below
6. Below screen will appear, choose options as given below
and click on Next button :
·
Import File : Click on choose file and select f103.sql from directory location where
this file is kept.
·
File Type : Select option “Database Application, Page or Component Export”.
·
File Character Set : keep “Unicode UTF-8”.
7. It may take few minutes to import file.
8. Apex will show successful import message, just click on Next Button:
9. Below screen will appear, choose options as given below
and click on Install Application
button :
·
Parsing Schema : choose database schema
·
Build Status : Run and Build Application
·
Install As Application : select 2nd
option “Reuse Application ID from Export File”
10. Installation in progress, It may take few minutes to
install.
11. It will show successful message if application is
installed correctly.
Integrating APEX with Oracle E-Business Suite :
In order to connect from Oracle E-Business Suite to your new Oracle Application Express
Applications, a mechanism is required to hand control from Oracle E-Business Suite to Oracle Application Express. The following actions need to be performed:
We are going to use the cookie settings in the icx_sessions table of Oracle E-Business Suite.
Because, 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
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>
Next>
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 Authorization Scheme
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;
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.
In order to connect from Oracle E-Business Suite to your new Oracle Application Express
Applications, a mechanism is required to hand control from Oracle E-Business Suite to Oracle Application Express. The following actions need to be performed:
1. Create an Oracle Application Express application
2. Configure the Oracle Application Express login process if you want to authenticate user before accessing the application.
3. Define Oracle Application Express authorizations
4. Define Oracle E-Business Suite profile and functions
5. Link functions to menus and responsibility
Perform below activities from APEX side :
Creating an Oracle Application Express ApplicationWe are going to use the cookie settings in the icx_sessions table of Oracle E-Business Suite.
Because, 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 Name | Comments |
| EBS_USER_ID | Key to User; To check EBS authorization and to set EBS context (icx_sessions) |
| EBS_RESP_ID | Key to Responsibility; To check EBS authorization and to set EBS context (icx_sessions) |
| EBS_RESP_APPL_ID | Key to Responsibility Application; To check EBS authorization and to set EBS context (icx_sessions) |
| EBS_SEC_GROUP_ID | Key to Security Group; To check EBS authorization and to set EBS context (icx_sessions) |
| EBS_TIME_OUT | Session Time Out in Oracle E-Business Suite (icx_sessions) |
| EBS_URL | URL to return to EBS Homepage from APEX (icx_session_attributes) |
| EBS_ORG_ID | EBS ORG_ID (icx_sessions) - MO: Operating Unit from Responsibility |
| EBS_APPLICATION_NAME | To be displayed at the left tophand corner (application_name from fnd_application_tl using EBS_RESP_APPL_ID) |
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>
Next>
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 Authorization Scheme
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;
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.
Perform below activities from EBS side :
Update the FND: APEX URL profile option with the correct setting at the site level using the following steps:
1. Log in to Oracle E-Business Suite with
the SYSADMIN user
(http ://<EBS_Hostname>:8074/OA_HTML/AppsLogin)
2. Navigate to the System Administrator
responsibility > Profile > System menu option
3. Search for Profile %APEX%, click Find
4. for profile FND: APEX URL enter Site http://<EBS_Hostname>:8080/apex
5.
Save the profile
Perform the following steps to define the
Oracle E-Business Suite functions:
1. Navigate to the System Administrator responsibility
> Application > Function menu option
2. For calls to the page without
responsibility, create a function with the following details:
Description -
Function: APEX_FUN
User
Function Name: APEX Extension
Properties
–
Type:
JSP
Web HTML –
HTML Call: GWY.jsp?targetAppType=APEX&
p=<APEX Application Id>:
<APEX
Page>:<Session>:<Request>:<Debug>:<Clear Cache>:
<Parameter Pairs>
{ For example, to call Oracle Application
Express application 104, Page 1
use GWY.jsp?targetAppType=APEX&p=104:1:0::NO:1:
, all other parameters are
optional}
Add menu component under required Menu as
shown below-
Menu
Name:
User
Menu Name : [Specify User Menu Name here]
Menu
Type: Standard
3.
Create menu component –
Sequence:
1
Prompt:
Launch APEX Page
Function: APEX Extension
4.
Save the menu.
Following is the table detail which I want to populate from data in excel sheet.
Table Name : UPLOAD_DATA
Please follow below steps :
1) Created a “File Browse” item on a page and having internal name it P35_UPLOAD.
2) Created a button having internal name as “Upload“. This button is used to upload and process the data in the excel sheet.
3) Created a conditional branch when upload button is pressed, it should branch to the same page (35 in my case)
4) When we select an excel sheet using browse button and click on submit button, it will branch to same page (indirectly we are saying that its going to refresh the page). In such case even if there is no code written in the backend, APEX is going to load the excel sheet in a BLOB format into a table wwv_flow_files.
This is the internal table used by APEX. Our task is to read this table and get the required row which got inserted into this table. With each upload 1 row will get inserted into the table. One of the column of the table (BLOB_CONTENT) is BLOB where the actual excel sheet is uploaded. All other columns are metadata about excel.
When we upload an excel sheet, a random name will get generated for the file in the form FXXXXX/<file_name_you_uploaded>. You can use this to get the file details. Also when you refresh the page, it will upload the file to this table wwv_flow_files, but as soon as refresh completed and it displays the page back again, that row will get deleted again. So you have to get that row processed immediately using the code that you will write when submit button is pressed.
In my case, I want to upload the rows into UPLOAD_DATA table. My excel sheet content looks as shown below.
create or replace procedure "INSERT_ROW"
(
P_RECORD IN VARCHAR2
) AS
v_col_1 varchar2(100);
v_col_2 varchar2(100);
v_col_3 varchar2(100);
BEGIN
v_col_1 := substr(P_RECORD,1,instr(P_RECORD,',',1,1)-1);
v_col_2 := substr(P_RECORD,instr(P_RECORD,',',1,1)+1,instr(P_RECORD,',',1,2)-instr(P_RECORD,',',1,1)-1);
v_col_3 := substr(P_RECORD,instr(P_RECORD,',',1,2)+1,length(P_RECORD)-instr(P_RECORD,',',1,2));
--dbms_output.put_line('rECORD - ' ||v_col_1||' , '||v_col_2||' , '||v_col_3);
insert into UTR_OUTCOME(DESCRIPTION,STATUS,END_ON) values(v_col_1,v_col_2,v_col_3);
commit;
--dbms_output.put_line(v_col_5);
exception
when others then
--dbms_output.put_line('Error : '||SQLERRM);
insert into UTR_OUTCOME(DESCRIPTION) values('Error');
end;
Below is the code for uploading the excel sheet.
declare
v_temp BLOB;
v_record varchar2(3276);
v_len number := 0;
v_count number:=0;
v_data varchar2(32767);
begin
-- When an data is uploaded it is stored in the
-- FLOW_FILES.WWV_FLOW_FILE_OBJECTS$ table.
-- The wwv_flow_file_objects$ table is identified in the dads.conf
-- file in the PlsqlDocumentTablename parameter.
-- WWV_FLOW_FILES is a view to this table.
--File is uploaded in BLOB format so we need to extract data from it and insert into our table.
select blob_content into v_temp
from wwv_flow_files
where
created_on= (select max(created_on) from wwv_flow_files where UPDATED_BY = :app_user)
and id = (select max(id) from wwv_flow_files where updated_by = :app_user);
v_data := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(v_temp,DBMS_LOB.GETLENGTH(v_temp), 1));
DBMS_OUTPUT.PUT_LINE('THE LENGTH IS : ' || DBMS_LOB.GETLENGTH(v_temp));
DBMS_OUTPUT.PUT_LINE('THE BLOB IS READ : ' || v_data );
v_data := substr(v_data,instr(v_data,chr(10),1)+1,length(v_data));
v_len := length(v_data);
DBMS_OUTPUT.PUT_LINE(v_len);
delete from utr_outcome;
while v_count <= v_len-1
loop
v_count := v_count+1;
v_record := substr(v_data,v_count,instr(v_data,chr(10),v_count)-v_count);
DBMS_OUTPUT.PUT_LINE(v_record);
INSERT_ROW(v_record);
v_record :='';
v_count :=instr(v_data,chr(10),v_count);
DBMS_OUTPUT.PUT_LINE(v_count);
end loop;
Exception
When others then
DBMS_OUTPUT.PUT_LINE('Error : '||sqlerrm);
end;
Create a new PLSQL process under Processes section in APEX and put this code under the same. Make the process run when upload button is pressed.
Data in excel sheet will get loaded into table.
Hope this helps !! 
