ERRBUFF & RETCODE

Hi ALL,
As we all know there are two mandatory parameters that need to be pased for all the procedures called 
1.ERRBUFF 
2.RETCODE..
They are very useful parameters if used properly..like setting the status of the conucrrent program and putting the log messages...
i think it is better we make some rule like we make the program end in Error when ever there is a unidentified exception occurs based on your business rule...

define ERRBUFF as the first parameter and Retcode as the second one...Dont forget to specify the out variable type in it...

CREATE PROCEDURE SAMPLE_PROC (ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
p_1 varchar2)

The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error

we can set our concurrent program to any of the three status by using these parameters...
for example
Begin
.....
Exception
when others then
fnd_File.put_line(fnd_file.log,'Unhandled exception occurred in package.procedure'||SQLERRM);
retcode='2';
end;
Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.....




Example.

Concurrent Programs Warning-Error: ERRBUFF and RETCODE

The ERRBUFF can be returned with any message.

The RETCODE can be returned with one of three values:

   0  -- Success
   1  -- Warning
   2  -- Error

Below is an example of a package where I can pass an employee number and it can return its full name. If no data found for the employee number passed, the concurrent program will turn YELLOW with the message 'No Employee Found'. If there is any other error occurs, it will turn RED with SQLERRM message.

----------------------------------------------------------

-- Package Specification

----------------------------------------------------------

CREATE OR REPLACE PACKAGE apps.emp_test_pkg

IS

   FUNCTION emp_name (

      errbuff       OUT  NOCOPY  VARCHAR2,

      retcode       OUT  NOCOPY  VARCHAR2,

      p_emp_number  IN           NUMBER)

      RETURN  VARCHAR2;

    

END emp_test_pkg;

/

----------------------------------------------------------

-- Package Body

----------------------------------------------------------

CREATE OR REPLACE PACKAGE BODY apps.emp_test_pkg

IS

 

   FUNCTION emp_name (

      errbuff       OUT  NOCOPY  VARCHAR2,

      retcode       OUT  NOCOPY  VARCHAR2,

      p_emp_number  IN           NUMBER)

      RETURN  VARCHAR2

   IS

      lv_emp_name   VARCHAR2(300)  DEFAULT  NULL;

    

   BEGIN

       

      SELECT (papf.first_name || ' ' || papf.last_name)

        INTO lv_emp_name

        FROM per_all_people_f  papf

       WHERE 1=1

         AND papf.employee_number = p_emp_number;

    

      RETURN (lv_emp_name);

    

   EXCEPTION

      WHEN NO_DATA_FOUND THEN

         errbuff := 'No employee found for ' || p_emp_number;

         retcode := '1';        -- warning

       

         fnd_file.put_line(fnd_file.log, errbuff);

         RETURN (lv_emp_name);

       

      WHEN OTHERS THEN

       

         errbuff := SQLERRM;

         retcode := '2';        -- error

       

         fnd_file.put_line(fnd_file.log, errbuff);

         RETURN (lv_emp_name);

 

   END emp_name;

 

END emp_test_pkg;

/