Creating a Concurrent Program of type PL/SQL Stored procedure, using Oracle Apps API
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
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
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
Output of a Concurrent Program
Read More
Steps to create a Concurrent Program of type PL/SQL Stored Procedure
The PL/SQL stored procedure is another popular type of the concurrent program in Oracle Applications extensions. The popularity of this type of the concurrent program for the concurrent processing is probably related to the fact that PL/SQL is still seen by most developers to be the most efficient way of manipulating data in Oracle databases; another important factor for using PL/SQL is that the Oracle Applications database comes with a huge library of PL/SQL programs and APIs that are freely available to developers for reuse in their own custom modules and extensions.The methodology of creating a PL/SQL concurrent program is similar to creating a SQL*Plus Script or any other concurrent program. First, you have to write the code in PL/SQL and save it into the Oracle Applications database in the APPS schema.
Register it with AOL through the Concurrent Program Executable screen by specifying PL/SQL Stored Procedure in the Execution Method field and the name of the PL/SQL stored procedure defined in the Execution File Name field.
We will illustrate how to do this with an example:
Create a PL/SQL stored procedure
create or replace procedure XX_PO_HEADERS_ALL_PROC (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<5;
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;
/
Compile this procedure from SQL prompt.
Note: No need to place this in any Application Folder as this is a stored procedure and is stored in Database.
Note 2: This PL/SQL stored procedure has 2 parameters. These parameters are used for the interaction between the concurrent program and the pl/sql Program.
System administrator>> concurrent>> program>> executable
Enter the following details
Concurrent Program Executable
Define the Concurrent Program
Go to System Administrator>>Concurrent>>Program>>Define
Defining a Concurrent Program
Attach this concurrent program to a Request group
System Administrator>> Security>> Responsibility>> Request.
F11.
Group Field: Type All Reports and Press ctrl +F11
Ensure Application is: Payables
Click on + icon on the toolbar to create a new request.
Assigning Concurrent Program to a Request Group
Run the request
Switch the Responsibility to Payables.>>Toolbar>>View >> Requests.
We get the find Requests screen.
Submit a New request>>Single request>>Provide the Name of the program>>Submit.
Submitting a Concurrent request
Read More
Creating a Concurrent Program of type SQL script in Oracle Apps r12: (without parameters)
This article helps users to create a new Concurrent Program by using SQL script. We run a simple SQL script and we don’t define any parameters.Step1: Write a simple select query on emp table:
select empno, ename, sal from emp;
save it as emp_sql.sql at D:Concurrent Programs. (Some folder of your choice)
Step2: Copy this sql script to location on the server:
D:oracle VIS apps apps_st appl ap 12.0.0 sql
Step3: Create executable for this script in Oracle APPS.
You can do this from either System Administrator or from Application Developer.
We do it from System Administrator:
Navigation: System Administrator>> Concurrent>> Program>> Executable.
Input to the screen:
Executable: any related name
Short name: Unique short name as this is required in next screen (Define)
Application: Payables (that is the reason we have placed the script at ap12.0.0sql)
Execution Method: SQL * Plus
Execution File Name: <script name without extension> i.e emp_sql
Defining a Concurrent Program Executable
Step4: Define the concurrent program for this script:
Navigation: System Administrator>>Concurrent>>Program>>Define
Enter the details:
Program Name: Any related name. Must be unique as we have to give this name while attaching to Responsibility.
Enabled check box: Checked.
Short name: Any related Short name
Application: Payables
Executable:
Name: Give the Short name we gave for Executable
Method is populated automatically.
Use SRS: Check box: Default checked.
Output: Format: text
Save and Print Check boxes are checked by default.
Save.
Defining a Concurrent Program
Navigate to
System Administrator>> Security>> Responsibility>> Request.
Query for All Reports in the Group Field by typing F11, ‘All Reports‘ and ctrl+F11
Ensure Application is: Payables
Navigate to requests section and Click on + icon on the toolbar to create a new request.
Type Name Application
program emp_sql payables
save
Adding Concurrent program to a Request Group
Toolbar >>Switch Responsibility>>Payables
Toolbar>> View >> Requests>>Submit a new request>>Single Request>>
Enter the Name and click Submit.
Click No
Click Find and make sure that your request is completed normally.
Click ‘View Output’ to see the result
Done
Executing the Concurrent Program
Output of the Concurrent Program
FND_CONCURRENT_PROGRAMS: Contains information about the name and description of the concurrent program, execution method, standalone flag, print style, and other attributes that define the program.
FND_EXECUTABLES: Contains attributes about concurrent program executables, including the concurrent program executable name alongside the name of the actual executable file on the operating system such as shell script, Oracle Report, Java class, and others. The execution method is associated with the concurrent program executable to help the Concurrent Manager identify how and with what tool to run the physical executable file.
Application Component
Read More
Creating a Concurrent Program with default Parameter Value Set
Use the following SQL script :“select EMPNO, ENAME, SAL, DEPTNO from emp where deptno=&1;” and name it as “emp_param_default.sql” and place it D:oracleVISappsapps_stapplap12.0.0sql.Define the Concurrent Program Executable
Navigate to System administrator>> Concurrent>> Program>> Executable.
Create a concurrent program executable with name “emp_param_default”.
Creating a Concurrent Program Executable
System Administrator>> Concurrent >> Program>> Define.
Defining a Concurrent Program
Enter the data:
Seq = 10, Parameter = deptno, Value Set = 15 Number ( or any number format)
SAVE.
Defining Parameters
System Administrator>> Security>> Responsibility>> Request.
F11 and enter ‘All Reports‘ then ctrl+F11.
Ensure that Application is PAYABLES.
Go to requests and click New Icon in the toolbar to create a new row as shown in the screen.
SAVE.
Adding Concurrent Program to a Request Group
Switch Responsibility (Toolbar>>Click Switch responsibility icon) to Payables.
View>> Requests>> Submit a New Request>>
Submitting a Concurrent request
In the SRS (Standard Request Submission) screen, enter the name of the concurrent program.
Click Submit. Another Request ..NO, Click Find and observe the status. Click View Output.
DONE.
The output would look like this:
Output of a Concurrent Program
We have DEPT table. Register it with Apps:
BEGIN
AD_DD.REGISTER_TABLE (‘FND’,'DEPT’,'T’);
END;
Register the columns
BEGIN
AD_DD.REGISTER_COLUMN (‘FND’, ‘DEPT’,'DEPTNO’, 1, ‘NUMBER’, 20, ‘Y’, ‘Y’);
AD_DD.REGISTER_COLUMN (‘FND’, ‘DEPT’,'DNAME’, 2, ‘VARCHAR2′, 20, ‘Y’, ‘Y’);
AD_DD.REGISTER_COLUMN (‘FND’, ‘DEPT’, ‘LOC’, 3, ‘VARCHAR2′, 20, ‘Y’, ‘Y’);
END;
Open the Apps go to System Administrator Responsibility and Create a Table value set by name “PARAMS_TABLE”. Select validation type as table and click edit information button and enter values as shown:
Creating a Value Set
Validation table info
PARAMS_TABLE.sql
select * from emp where deptno=&1;
and place it in “D:oracleVISappsapps_stapplap12.0.0sql”
Create Concurrent Program Executable
In The Apps go to System Administrator>> Concurrent>> Executable
and Create SQL Script con prg. Executable
Creating Concurrent Program Executable
System Administrator>> Concurrent>> Program>> Define.
Defining a Concurrent Program
Defining Parameters
System Administrator>> Security>> Responsibility>> Request.
F11 and enter All Reports then ctrl+F11.
Ensure that Application is PAYABLES.
Navigate to requests and click New Icon in the toolbar to create a new row as shown in the screen.
SAVE.
Attaching Concurrent Program to a Request Group
Switch Responsibility (Toolbar>>Click Switch responsibility icon) to Payables.
View>> Requests>> Submit a New Request>> Single Request
In the SRS (Standard Request Submission) screen, enter the name of the concurrent program.
Click Submit. Another Request…NO, Click Find and observe the status. Click View Output.
DONE.
No comments:
Post a Comment