Register your Pl/SQL procedure as Concurrent program in Oracle apps

IN this article we will try to explore how we can register a pl/sql stored procedure in Oracle apps so that it be can tested from the front end of Oracle apps.


The steps will be as follows

1> Create a procedure
2> Create Executable
3> Create Program
4> Add the program to the request group

we will see each of these steps one by one.


First is to Create a Procedure

There are two important points that one must take care of when creating a procedure for registering as concurrent program.
There are two mandatory parameters that must be passed in the procedure along with other parameters of your choice

They are errbuf and retcode

errbuf is used for printing out the error message and retcode returns the status of the concurrent program

If retcode is 0 it means it a success
If retcode is 1 it means Concurrent program is warning
If retcode is 2 it means Concurrent Program is giving error.

with these thumb rules in mind lets create a simple procedure which will accept Org_ID and Vendor_ID as input parameter

and will return invoice_amount and invoice_number.

So my code will essentially look like this.

CREATE OR REPLACE PROCEDURE getInvoiceInfo(errbuf OUT VARCHAR2,retcode OUT VARCHAR2,P_Org_ID IN VARCHAR2,P_Vendor_ID IN VARCHAR2)
AS
Inv_number VARCHAR2(100);
Inv_amount VARCHAR2(100);
BEGIN
SELECT invoice_num, invoice_amount
INTO Inv_number,Inv_amount
FROM ap_invoices_all
WHERE ORG_ID = P_Org_ID and VENDOR_ID =P_Vendor_Id;
DBMS_OUTPUT.PUT_LINE('test');
fnd_file.put_line(fnd_file.output, 'Parameter received:' || P_Org_ID || P_Vendor_ID);

fnd_file.put_line(fnd_file.log, 'Parameter received:' || P_Org_ID || P_Vendor_ID);
EXCEPTION
WHEN OTHERS THEN
RETCODE := 2;
errbuf := 'Unknown Exception';

fnd_file.put_line(fnd_file.log, 'sqlerrm:' || SQLERRM);
END;



Just to test if the procedure is workin fine you can execute the following
SET SERVEROUTPUT ON
DECLARE
errbuf VARCHAR2(20);
retcode VARCHAR2(10);
BEGIN
getInvoiceInfo(
errbuf=>errbuf,
retcode=>retcode,
P_Org_ID=>'888',
P_Vendor_ID=>'9');
DBMS_OUTPUT.PUT_LINE('Code Executed');
END;

Here in my case i am getting a single record for this particular input but you get more than one row for the query

select * from ap_invoices_all where vendor_id=9 and Org_Id=888

Then this code might not work for you, you will have to use cursor in that case.

Considering you are getting a single value we will go ahead and register this procedure as a concurrent program in Oracle apps.

Next step is to create an executable for your concurrent program

Go to Application Developer Responsibility-->Concurrent-->Executable



In the next screen provide the following info

Execuatable -Give it a logical name

ShortName -Give it a unique name

Application- Ideally executable should be created in Custom application

Execution Method- It will PL/SQL Stored procedure as we are going to call a procedure in this executable.

Execution file Name- This should be the name of the procedure,In case you have your procedure wrapped in a package the name should be

For our program we will pass the following information



Now once you have the Executable ready We will create a program.

Go to following path in Application Navigator Responsibility

Concurrent-->Program

Provide the information as shown below

Program- Give some logical name

Short Name- Provide some unique Name

Application- Enter application name , Ideally it will custom

Executable Name- We will provide the executable we have created earlier.

Method-Automatically populated

OutputFormat- Select XML

Output Style- Select A4

Rest of the options you can keep the default values.




Now click on paramters tab to define the parameter for this concurrent program

Define the two parameters that will be passed as an input to this and save the changes for the concurrent program




Now assign this to a request group.

If you want to assing this to Application developer

then go to following navigation



System Administrator-->Security-->Responsibility-->Define

Double click on Request and get the Request group for the Application developer responsibility as shown below




Now once you have got the request group copy it and then go to following navigation

System Administrator-->Security-->Responsibility-->Request

Search with the group Name

Application Developer Reports

and add your program to this list




Now go to Application developer responsibility and test your code if it is working fine by passing the value for the Org Id and the Vendor ID

Validate the log file and the output file