Web ADI

Web ADI (Applications Desktop Integrator) is a self–service Oracle application, accessed using a browser. There is no need to install it on individual client machines, all the processing takes place on the server rather than on individual client PCs. It generates Microsoft Excel and uploads the data you would like to upload.

Very often we get requirements to have validations of data before loading and display custom error message accordingly, it drives us to configure custom Web ADI.You can get many links, how to configure custom Web ADI. Here I am giving you an idea how to write custom API/packaged procedure, which will be used while configuring Custom Web ADI.

> raise_application_error can be used to display custom error message


CREATE OR REPLACE PACKAGE BODY  APPS.XX<ABC>_PKG AS
   ----------------------
   -- Exception Varibales
   ----------------------
   xxab_a_error          EXCEPTION;
   xxab_b_error          EXCEPTION;
   xxab_c_error          EXCEPTION;
   xxab_d_error          EXCEPTION;
   xxab_e_error          EXCEPTION;
   xxab_f_error          EXCEPTION; 

   PROCEDURE xx<abc>_proc (
             i_<a>      IN VARCHAR2
            ,i_<b>      IN VARCHAR2
            ,i_<c>   IN NUMBER
            ,i_<d>   IN NUMBER
            ,i_<e>      IN VARCHAR2
            ,i_<f>      IN VARCHAR2
   ) IS

   l_ou_count         NUMBER;
   l_default_org_id   NUMBER := fnd_global.org_id;
   l_ou_name          VARCHAR2(200);

   BEGIN

      -- Get Default operating unit and master organization id  
      BEGIN
         MO_GLOBAL.init('XXQP');
   
         mo_utils.get_default_ou(l_default_org_id, l_ou_name, l_ou_count);

         SELECT OE_Sys_Parameters.VALUE('MASTER_ORGANIZATION_ID', l_default_org_id)
     INTO l_org_id
           FROM dual;
      EXCEPTION
         WHEN OTHERS THEN
       raise_application_error (-20012,
                        'Error While Getting Organization ID ' || SQLERRM);
      END ;
     
      -- Validation according to your business logic and raise realted exception if it fails
      BEGIN

         -- When validation for input i_<a> fails raise exception xxab_a_error
       
      EXCEPTION
             WHEN NO_DATA_FOUND THEN
                     RAISE xxab_a_error ;
             WHEN OTHERS THEN
                     raise_application_error (-20010,
                                    ' Error while Validating <a> ' || SQLERRM);
      END ;          
   
   EXCEPTION
      WHEN xxab_a_error THEN
           raise_application_error(-20001,
                                   '<Appropriate custom Error Message>');
      WHEN OTHERS THEN
           raise_application_error (-20009,
                                  'Main ' || SQLERRM );
   END xx<abc>_proc;

END XX<ABC>_PKG;