This post is very helpful in case if you want to call Oracle
Report (Concurrent Program) from the Oracle standard form and the output file
should open automatically without going to view request form.
Requirement: We
want to call a report “XX Purchase Order Print Report” from the Standard
Purchase Order Form and the output should directly open from this form only (User
does not want to go to view request form).
Solution Steps:
·
First we will create a menu item on Oracle
purchase order form using personalization. Navigate to Purchase Order Form -> Help -> Diagnostics -> Custom Code
-> Personalize.
·
Create a menu item with the following entry as
mentioned below:
o
Seq :1
o
Description :View
Purchase Order print
o
Trigger Event :When
New Form Instance
·
Click on Actions Tab and enter the following
information:
o
Seq :1
o
Type :Menu
o
Menu Entry :MENU1
o
Menu Label :Print
Purchase Order
·
Create a new personalization record to call the
Oracle Report and enter the following information:
o
Seq :2
o
Description :Call
Purchase Order Print
o
Trigger Event :MENU1
·
Click on Actions Tab and enter the following
information:
o
Seq :1
o
Type :Builtin
o
Builtin Type :Call
Custom Library
o
Argument :MENU1
·
Now download
the Custom.pll file from the Server and open it in Oracle Form builder.
Path:
$AU_TOP/resource/Custom.pll
·
In the Custom.pll go to Program Units -> CUSTOM (Package Body) -> Procedure Event (event_name
varchar2 ) and paste the code as mentioned below:
----------------------------------------------------------------------------------------------
IF event_name =
'MENU1'
THEN
DECLARE
l_req_id NUMBER;
l_status VARCHAR2 (30) :=
'NORMAL';
l_param1 VARCHAR2 (200) := NULL;
l_param3 VARCHAR2 (200) := NULL;
BEGIN
l_param1
:= NAME_IN ('PO_HEADERS.PO_HEADER_ID');
l_param3
:= NAME_IN ('PO_HEADERS.ORG_ID');
l_req_id
:= XX_PO_PRINT_F (l_param1, l_param3);
editor_pkg.report (l_req_id, 'Y');
END;
End if;
---------------------------------------------------------------------------------------------------
=========
Fuction: XX_PO_PRINT_F
create or replace FUNCTION XX_PO_PRINT_F(P_PO_HEADER_ID VARCHAR2,
P_ORG_ID VARCHAR2)
RETURN NUMBER
IS
L_LAYOUT BOOLEAN;
L_REQUEST_ID NUMBER := 0;
L_ORG_ID NUMBER;
L_USERID NUMBER;
L_RESP_ID NUMBER;
L_APPL_RESP_ID NUMBER;
L_PHASE VARCHAR2 (240);
L_STATUS VARCHAR2 (240);
L_REQUEST_PHASE VARCHAR2
(240);
L_REQUEST_STATUS VARCHAR2
(240);
L_FINISHED BOOLEAN;
L_MESSAGE VARCHAR2 (240);
L_APPL_SHORT_NAME VARCHAR2 (240);
L_USER_PROGRAM_NAME VARCHAR2
(240);
BEGIN
L_USERID := FND_PROFILE.VALUE
('USER_ID');
L_RESP_ID := FND_PROFILE.VALUE
('RESP_ID');
L_APPL_RESP_ID := FND_PROFILE.VALUE
('RESP_APPL_ID');
FND_GLOBAL.APPS_INITIALIZE (L_USERID,
L_RESP_ID, L_APPL_RESP_ID);
BEGIN
SELECT
T2.APPLICATION_SHORT_NAME, T1.USER_CONCURRENT_PROGRAM_NAME
INTO
L_APPL_SHORT_NAME, L_USER_PROGRAM_NAME
FROM
FND_CONCURRENT_PROGRAMS_VL T1, FND_APPLICATION_VL T2
WHERE
CONCURRENT_PROGRAM_NAME = 'XX_PO_REPORT'
AND
T1.APPLICATION_ID = T2.APPLICATION_ID;
EXCEPTION
WHEN
NO_DATA_FOUND
THEN
L_APPL_SHORT_NAME := NULL;
L_USER_PROGRAM_NAME := NULL;
END;
IF
L_APPL_SHORT_NAME IS NOT NULL
THEN
L_LAYOUT
:=
FND_REQUEST.ADD_LAYOUT (L_APPL_SHORT_NAME,
'XX_PO_REPORT',
'en',
'IN',
'PDF'
);
END IF;
L_REQUEST_ID :=
FND_REQUEST.SUBMIT_REQUEST('XXBLL','XX_PO_REPORT','','',FALSE,
TO_NUMBER(P_ORG_ID),TO_NUMBER(P_PO_HEADER_ID),'Y');
STANDARD.COMMIT;
IF
L_REQUEST_ID > 0
THEN
L_FINISHED :=
FND_CONCURRENT.WAIT_FOR_REQUEST
(REQUEST_ID =>
L_REQUEST_ID,
INTERVAL => 0,
MAX_WAIT => 0,
PHASE => L_PHASE,
STATUS => L_STATUS,
DEV_PHASE =>
L_REQUEST_PHASE,
DEV_STATUS => L_REQUEST_STATUS,
MESSAGE => L_MESSAGE
);
IF (UPPER (L_REQUEST_STATUS) =
'NORMAL')
THEN
RETURN L_REQUEST_ID;
END IF;
END IF;
RETURN L_REQUEST_ID;
END;
============
·
Now upload the file on the server (take backup
of the Custom.pll) and compile it using the command.
CMD:
frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=CUSTOM.plx
module_type=LIBRARY batch=yes compile_all=special
·
Now restart
oracle application and clear the cache. Restart the application so that the
impact of Custom code takes place.
