WEB ADI Creation (using API)

1: Create a table
   Where WEB ADI will store the data. Table might be created in Custom schema.

2. Create a Package.
   (It has to be in APPS Schema)
   In this case XX_TRAVEL_EXPENSE_ADI_PKG package,
   procedure XX_TRAVEL_EXPENSE_PRC.
** Note XX_TRAVEL_EXPENSE_PRC parameters should start with p_
(

      p_company             VARCHAR2,
      p_vendor_name         VARCHAR2,
      p_invoice_num         VARCHAR2,
      p_inv_currency        VARCHAR2,
      p_invoice_date        DATE,

)
then only prompt will automatically appear company , vendor_name, invoice_num, inv_currency, invoice_date.

3. Once the database object is created,  then create integrator.
DECLARE
   ln_application_id    NUMBER;
   lc_integtr_code      VARCHAR2 (50);
   lx_interface_code    VARCHAR2 (50);
   lx_param_list_code   VARCHAR2 (50);
   ln_application_id    NUMBER;
   ln_ret       number;
  
  --Create integrator 
   BEGIN
      bne_integrator_utils.create_integrator
          (p_application_id            => 20003,
           p_object_code               => 'XX_TRAVEL_INV',
           p_integrator_user_name      => 'XX Travel Invoice Upload WEB ADI',
           p_language                  => 'US',
           p_source_language           => 'US',
           p_user_id                   => -1,
           p_integrator_code           => lc_integtr_code
          );
      DBMS_OUTPUT.put_line ('lc_integtr_code =  ' || lc_integtr_code);
   END;

Check it once it is created

SELECT *
  FROM bne_integrators_b
 WHERE integrator_code LIKE 'XX_TRAVEL_INV%';

4. Create Interface
DECLARE
   ln_application_id    NUMBER;
   lc_integtr_code      VARCHAR2 (50);
   lx_interface_code    VARCHAR2 (50);
   lx_param_list_code   VARCHAR2 (50);
   ln_application_id    NUMBER;
   BEGIN
      bne_integrator_utils.create_interface_for_api
           (p_application_id           => 20003,
            p_object_code              => 'XX_TRAVEL_INV',
            p_integrator_code          => 'XX_TRAVEL_INV_INTG',
            p_api_package_name         => 'XX_TRAVEL_EXPENSE_ADI_PKG',
            p_api_procedure_name       => 'XX_TRAVEL_EXPENSE_PRC',
            p_interface_user_name      => 'XX Travel Invoice Upload WEB ADI',
            p_param_list_name          => 'XX Travel Invoice PL',
            p_api_type                 => 'PROCEDURE',
            p_api_return_type          => NULL,
            p_upload_type              => 2,
            p_language                 => 'US',
            p_source_lang              => 'US',
            p_user_id                  => -1,
            p_param_list_code          => lx_param_list_code,
            p_interface_code           => lx_interface_code
           );
      DBMS_OUTPUT.put_line ('lx_interface_code  =   ' || lx_interface_code);
      DBMS_OUTPUT.put_line ('lx_param_list_code =   ' || lx_param_list_code);
   --EXCEPTION
   --   WHEN OTHERS
   --   THEN
   --      DBMS_OUTPUT.put_line ('Error  =      ' || SQLERRM);
   END;

Check Interface

SELECT *
  FROM bne_interface_cols_tl
 WHERE interface_code LIKE 'XX%' ;

SELECT *
  FROM bne_interface_cols_vl
 WHERE interface_code LIKE 'XX%';

SELECT *
  FROM bne_interface_cols_b
 WHERE interface_code LIKE 'XX%'


4. Create an ADI function with following values:

FunctionName UserFunctionName Description         Type                MaintainanceMode ContextDependence
************************************************************************************************************************
<User defined>  <User defined> <User defined>  SSWA servlet function None Responsibility

Form Application
*********************************************************************************
Parameter
***************************************
<none> <none> bne:page=BneCreateDoc&bne:noreview=true&bne:integrator=20003:GENERAL%25&bne:reporting=N

HTML Call Host Name
*********************************************************************************
BneApplicationService http://le0003.oracleads.com:80


5. Use the ORACLE WEB ADI responsibility and go to DEFINE LAYOUT option

Give LAYOUT name
From the drop down select the INTERGRATOR USER NAME as defined above and click on 'GO'
A screen with all the procedure parameters will appear.
Select 'LINE' as placement value for all the parameters and APPLY

7. Create and entry against the USER FUNCTION NAME in the menu under which ADI needs to be accessed.
ADI is ready to use.



Additional Steps:
Add Date LOV in the WEB ADI Column


DECLARE
   ln_application_id    NUMBER;
   lc_integtr_code      VARCHAR2 (50);
   lx_interface_code    VARCHAR2 (50);
   lx_param_list_code   VARCHAR2 (50);
   ln_application_id    NUMBER;
BEGIN
   bne_integrator_utils.create_calendar_lov
                                (p_application_id          => 20003,
                                 p_interface_code          => 'XX_TRAVEL_INV_INTF',
                                 p_interface_col_name      => 'P_INVOICE_DATE',--proc params
                                 p_window_caption          => 'Select Date',
                                 p_window_width            => 400,
                                 p_window_height           => 300,
                                 p_table_columns           => 'INVOICE_DATE',
                                 p_user_id                 => 10803
                                );
END;

ADD Lov in the fields,

DECLARE
   ln_application_id    NUMBER;
   lc_integtr_code      VARCHAR2 (50);
   lx_interface_code    VARCHAR2 (50);
   lx_param_list_code   VARCHAR2 (50);
   ln_application_id    NUMBER;  
BEGIN
   bne_integrator_utils.create_table_lov
                                (p_application_id          => 20003,
                                 p_interface_code          => 'XX_TRAVEL_INV_INTF',
                                 p_interface_col_name      => 'P_INV_CURRENCY',
                                 p_id_col                  => 'CURRENCY_CODE',
                                 p_mean_col                => 'CURRENCY_CODE',
                                 p_desc_col                => 'CURRENCY_CODE',                               
                                 p_table                   => 'GL_CURRENCIES',
                                 p_addl_w_c                => 'NVL (ENABLED_FLAG, ''N'') = ''Y''',
                                 p_window_caption          => 'Select Currency',
                                 p_window_width            => 400,
                                 p_window_height           => 300,
                                 p_table_block_size        => 10,
                                 p_table_sort_order        => 'Yes',
                                 p_user_id                 => 0
                                );
END;

Other usefull API:

1. Delete Integrator

DECLARE

   v_value   NUMBER;

BEGIN

   v_value :=

      bne_integrator_utils.delete_integrator (20003, 'XX_TRAVEL_INV_INTG');

   DBMS_OUTPUT.put_line (v_value);

END;

2. delete Interface

DECLARE

   v_value   NUMBER;

BEGIN

   v_value :=

      bne_integrator_utils.DELETE_INTERFACE (20003, 'XX_TRAVEL_INV_INTF');

   DBMS_OUTPUT.put_line (v_value);

END; 

Change the Prompt lebel,

UPDATE bne_interface_cols_tl

      SET prompt_left = 'Company',

          prompt_above = 'Company',

          user_hint = '*List - Text'

    WHERE prompt_left = 'COMPANY'

      AND interface_code = 'XX_TRAVEL_INV_INTF'

      AND LANGUAGE = 'US';

 UPDATE bne_interface_cols_tl

      SET prompt_left = 'Invoice Date',

          prompt_above = 'Invoice Date',

          user_hint = '*List - Date'

    WHERE prompt_left = 'INVOICE_DATE'

      AND interface_code = 'XX_TRAVEL_INV_INTF'

      AND LANGUAGE = 'US';