ERRBUFF and RETCODE

Detailed Explanation on the usage of ERRBUFF and RETCODE:

If we register any concurrent program’s executable as pl/sql stored procedure we need to pass two mandatory parameters to that
                1.ERRBUFF
                2.RETCODE.

ERRBUFF needs to be passed as the first parameter and RETCODE as the second one. Both of these are OUT variable type. ERRBUFF is VARHCAR2 and RETCODE can be VARCHAR2/NUMBER.

To ERRBUFF we can assign the reason for the ERROR/WARNING,it could be a user defined message or we can assing SQLERRM.

To RETCODE we can assign below numbers

                0-For Success

                1-For Warning

                2-For Error

**IF anything is not assigned to RETCODE, by default the program will complete SUCCESS**

Ø What to do to complete the Concurrent Program in WARNING:

Create a stored procedure in database as below, this procedure will go into EXCEPTION block if the data entered for p_emp parameter not available in emp table.

In the EXCEPTION block assign the values to ERRBUFF and RETCODE as given,so that the program will complete in WARNING.

CREATE OR REPLACE PROCEDURE test_out_var_proc(errbuff  OUT  VARCHAR2,

                                                         retcode   OUT  VARCHAR2,

                                                         p_emp  IN NUMBER)

AS

v_name VARCHAR2(200);

BEGIN

    SELECT ename

    INTO     v_name

    FROM    scott.emp

    WHERE  empno=p_emp;

EXCEPTION

WHEN OTHERS THEN

errbuff:='Raising WARNING as entered EMP no there in system';

retcode:=1;

END;

Create an Executable with this procedure and then concurrent program with a parameter as shown below, after that attach this to required request group.

Now submit the Program with some junk EMP number, so that the program will go into EXCEPTION block of the procedure

Entered 8465 as input for p_emp, which will make the program to go into EXCEPTION and as we have assigned  RETCODE =1,program will be completed in WARNING

And the ERRBUFF can be seen by clicking the View Log button of the concurrent program

Ø What to do to complete the Concurrent Program in ERROR:

Use the same stored procedure, in WHEN OTHERS exception just use the below values for ERRBUF&RETCODE instead of previous values and compile the procedure. This procedure will go into EXCEPTION if the data entered for p_emp parameter not available in emp table.

errbuff:='Raising ERROR as entered EMP no there in system';

retcode:=2;

Now submit the concurrent program again for some junk employee number.As shown below program will complete in ERROR status and ERRBUFF can be seen by clicking ‘Details’ button is clicked. Also can be seen in the Log.

Calling Concurrent Program from Menu

       Generally concurrent programs can be submitted from the Oracle Apps in one of the following ways
 -Attach the Concurrent Program to the required responsibility's request group.Go to that particular responsibility and submit the concurrent program from SRS window
 -Find out the Menu of the required responsibility and add the concurrent program to the menu as a Function. 

The below scree shots  will give you detailed steps to call the concurrent program from Menu

Ø  Create a concurrent Program with Executable

Ø  Create a Form Function

Function: Mandatory

Any Name can be Chosen, No need to be same as Concurrent Program short name

User Function Name: Mandatory

Any Name can be Chosen, No need to be same as Concurrent Program short name

Description: NON Mandatory                                                                                                     

Any Name can be Chosen, No need to be same as Concurrent Program short name

Function: Automatically Populated From 1^st Tab

Type: Mandatory

     Chose Form from LOV

Maintenance Mode Support: None

Context Dependence: Responsibility

Function: Automatically Populated From 1^st Tab

                                                                                                                                                                                                                

**Even though the concurrent program is not attached to the Request Group of the Responsibility, we can attach to any responsibility

Ø  Get Menu Details of the Responsibility where we want to add :

Ø  Add the Function to Menu:

Once the above step is done you can see the Function in menu as below

 When you click on the Function you will see the below screen where you can submit the program.