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
Save your work
Define the Concurrent Program
Go to System Administrator>>Concurrent>>Program>>Define
Defining a Concurrent Program
Save your work
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
Save your work
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
