Creating a Concurrent Program of type PL/SQL Stored procedure, using Oracle Apps API
By Jag - March 02, 2014
Creating a Concurrent Program of type PL/SQL Stored procedure
In this session we are creating a Concurrent Program of type Stored Procedure using the Oracle Apps API from database tier.
Pre-requisite: Create a stored procedure and compile it successfully.
The script for demo is
create or replace procedure XX_CP_API (errbuf OUT varchar2, retcode OUT
varchar2) AS
cursor po_cursor is
select
PO_HEADER_ID POI,
TYPE_LOOKUP_CODE TLC,
VENDOR_ID VID,
CURRENCY_CODE CC
from
PO_HEADERS_ALL
where rownum<10;
begin
FND_FILE.put_line(FND_FILE.output,’Starting processing:’);
FOR po_rec in po_cursor
LOOP
FND_FILE.put_line(FND_FILE.output,
po_rec.POI || po_rec.TLC ||
po_rec.VID || po_rec.CC);
END LOOP;
FND_FILE.put_line(FND_FILE.output,’Done!’);
commit;
— Return 0 for successful completion.
errbuf := ”;
retcode := ’0′;
exception
when others then
errbuf := sqlerrm;
retcode := ’2′;
end;
/
Step1: Registering the Executable from back end.
The PL/SQL code to create an executable from back-end is,
From SQL * PLUS>
BEGIN
FND_PROGRAM.executable (‘XX_CP_API’ — executable name
, ‘Payables’ — application
, ‘XX_CP_API’ — short_name
, ‘Executable for Employee INFORMATION’ — description
, ‘PL/SQL Stored Procedure’ — execution_method
, ‘XX_CP_API’ — execution_file_name
, ” — subroutine_name
, ” — Execution File Path
, ‘US’ — language_code
, ”);
COMMIT;
END;
/
PL/SQL procedure successfully completed.
Result of Step1: Query in the front-end to verify whether the executable is created or not.
System Administrator>>Concurrent>>Program>>Executable.
F11 AND ENTER XX_CP_API in the executable and press ctrl +f11 to see the result
Concurrent Program Executable
Verify the new entries in these tables
select
APPLICATION_ID ,
EXECUTABLE_ID ,
EXECUTABLE_NAME
from FND_EXECUTABLES where EXECUTABLE_NAME=’XX_CP_API’;
Select APPLICATION_ID, EXECUTABLE_ID , USER_EXECUTABLE_NAME , DESCRIPTION From FND_EXECUTABLES_TL where USER_EXECUTABLE_NAME=’XX_CP_API’;
2] You can use the below query to get all the Execution Methods available:
SELECT MEANING
FROM fnd_lookup_values
WHERE lookup_type = ‘CP_EXECUTION_METHOD_CODE’
AND enabled_flag = ‘Y’
/
Step 2: Register the Concurrent program from back end
From SQL>
BEGIN
FND_PROGRAM.register(‘XX_CP_API’ — program
, ‘Payables’ — application
, ‘Y’ — enabled
, ‘XX_CP_API’ — short_name
, ‘ Employee Information’ — description
, ‘XX_CP_API’ — executable_short_name
, ‘Payables’ — executable_application
, ” — execution_options
, ” — priority
, ‘Y’ — save_output
, ‘Y’ — print
, ” — cols
, ” — rows
, ” — style
, ‘N’ — style_required
, ” — printer
, ” — request_type
, ” — request_type_application
, ‘Y’ — use_in_srs
, ‘N’ — allow_disabled_values
, ‘N’ — run_alone
, ‘TEXT’ — output_type
, ‘N’ — enable_trace
, ‘Y’ — restart
, ‘Y’ — nls_compliant
, ” — icon_name
, ‘US’); — language_code
COMMIT;
END;
/
View this step from apps.
System Administrator>> Concurrent>> Program>> Define.
F11 and type XX_CP_API in the program and press ctrl+f11 to see the result
Defining a Concurrent Program
Verify the tables for new program entries:
select
APPLICATION_ID,
CONCURRENT_PROGRAM_ID,
CONCURRENT_PROGRAM_NAME,
EXECUTABLE_ID
from fnd_concurrent_programs where EXECUTABLE_ID=15973
/
select
APPLICATION_ID ,
CONCURRENT_PROGRAM_ID ,
USER_CONCURRENT_PROGRAM_NAME
from FND_CONCURRENT_PROGRAMS_TL
where USER_CONCURRENT_PROGRAM_NAME=’XX_CP_API’
/
Step3:
Attach the concurrent program to the request group: Below is the program to Attach Concurrent program to the request group from back-end.
BEGIN
FND_PROGRAM.add_to_group(‘XX_CP_API’ — program_short_name
, ‘Payables’ — application
, ‘All Reports’ — Report Group Name
, ‘SQLAP’); — Report Group Application
COMMIT;
END;
Verify from Oracle Apps, if the CP is attached to Request group.
System administrator>> security>> responsibility>> request
F11 and enter All Reports in the group and hit ctrl+f11. Select application as payables and check for the request name (XX_CP_API). It should be visible.
Attaching Concurrent Program to a request group
Step4: Submit the Concurrent Program from Back-end.
We first need to initialize oracle applications session using fnd_global.apps_initialize (user_id, responsibility_id, application_responsibility_id) and then run fnd_request.submit_request.
How to get the parameters user_id, responsibility_id, application_responsibility_id?
Select USER_ID, USER_NAME
from FND_USER
where USER_NAME=’OPERATIONS’;
USER_ID =>1318
select application_id, Responsibility_id, responsibility_name
from FND_RESPONSIBILITY_VL
where responsibility_name like ‘Payables, Vision Operations (USA)’
/
RESPONSIBILITY_ID=>50554
RESPONSIBILITY_APPLICATION_ID=>200
DECLARE
l_request_id NUMBER(30);
begin
FND_GLOBAL.APPS_INITIALIZE (user_id => 1318, resp_id => 50554, resp_appl_id =>200);
l_request_id:= FND_REQUEST.SUBMIT_REQUEST (‘SQLAP’,'XX_CP_API’);
DBMS_OUTPUT.PUT_LINE(l_request_id);
commit;
end;
/
Verify the result from Oracle apps:
Change to payables responsibility. View>>Requests>>Find.
Viewing a Concurrent Request
Click on View output:
Output of a Concurrent Program
Done…..
0 comments