Creating a Custom Web ADI Integrator to Upload data into R12 Oracle Applications

Custom Web ADI Integrator

A.        Creating Custom Integrators

a.         Assign yourself the ‘Desktop Integration’ Responsibility

b.         Add the menu ‘HR ADI Seeded Integrator Form Functions’ to the Desktop Integration Menu.

c.         Restart the application tier services

d.         Navigate to Desktop Integration and click on “Create Document”

e.         Select "HR Integrator Setup" to create a custom Integrator

f.          Select the Viewer, generally Excel 2007, to generate the document (Reporting Check box is used when downloading

g.         Select the layout as "Integrator Setup"

h.         Leave the content as "None"

i.          Review and select "Create Document".

j.          This action creates an Excel Spreadsheet. Make sure you "Enable Macros" when you open it

k.         Now using this spreadsheet we can create New Integrators. Basically, WebADI uses data from this standard template/layout to create/upload details about new Integrators. Complete the excel sheet with the Integrator Name

                                   

Column	Meaning
Metadata Type	CREATE : create new Integrator
	UPDATE : update an Integrator
	DOWNLOAD : download Integrator data
Application Short Name	Application Short Name, like AP, XXTEK
Integrator User Name	Integrator User Name, Appear in the drop down list
View Name	Name of the View used to download data, not
	required for CREATE type.
	GENERAL if don’t associate Integrator with form
Form Name	LETTER for mail merge.
	Specific form name to associate with Integrator
API Package Name	PROCEDURE or FUNCTION
API Procedure Name	Procedure that inserts/uploads data.
Interface User Name	Interface name displayed on to the user for this
	integrator on layout page.
Interface Parameter List Name	Name for the parameter list displayed on Layout
	page
API Type	PROCEDURE or FUNCTION
API Return Type	Date, Number or Varhar2 , leave it blank if you
	selected PROCEDURE above.

l.          Create the database package and procedure specified in the excel sheet. The sample code is as follows:

CREATE OR REPLACE PACKAGE xxaa_hp_pkg

IS

   PROCEDURE create_hp (p_loan_number          IN VARCHAR2,

                        p_supplier_name        IN VARCHAR2,

                        p_supplier_site        IN VARCHAR2,

                        p_payment_type         IN VARCHAR2,

                        p_installment_number   IN NUMBER,

                        p_invoice_date         IN DATE,

                        p_installment_amount   IN NUMBER,

                        p_interest_amount      IN NUMBER);

END xxaa_hp_pkg;

CREATE OR REPLACE PACKAGE BODY xxaa_hp_pkg

IS

   PROCEDURE create_hp (p_loan_number          IN VARCHAR2,

                        p_supplier_name        IN VARCHAR2,

                        p_supplier_site        IN VARCHAR2,

                        p_payment_type         IN VARCHAR2,

                        p_installment_number   IN NUMBER,

                        p_invoice_date         IN DATE,

                        p_installment_amount   IN NUMBER,

                        p_interest_amount      IN NUMBER)

   IS

      n_vendor_id        NUMBER;

      n_vendor_site_id   NUMBER;

      n_line_id          NUMBER;

   BEGIN

      SELECT vendor_id

        INTO n_vendor_id

        FROM po_vendors

       WHERE vendor_name = p_supplier_name;

      SELECT vendor_site_id

        INTO n_vendor_site_id

        FROM po_vendor_sites_all

       WHERE     vendor_site_code = p_supplier_site

             AND vendor_id IN (SELECT vendor_id

                                 FROM po_vendors

                                WHERE vendor_name = p_supplier_name);

      SELECT so_cycles_s.NEXTVAL INTO n_line_id FROM DUAL;

      INSERT INTO xxaa_hp (loan_number,

                            supplier_name,

                            vendor_site_code,

                            payment_type,

                            installment_number,

                            invoice_date,

                            installment_amount,

                            interest_amount,

                            vendor_id,

                            vendor_site_id,

                            line_id)

           VALUES (p_loan_number,

                   p_supplier_name,

                   p_supplier_site,

                   p_payment_type,

                   p_installment_number,

                   p_invoice_date,

                   p_installment_amount,

                   p_interest_amount,

                   n_vendor_id,

                   n_vendor_site_id,

                   n_line_id);

   EXCEPTION

      WHEN OTHERS

      THEN

         NULL;

   END;

END xxaa_hp_pkg;

m.        Click on Oracle --> Upload menu option. This will upload the Integrator definition into the system and the Integrator name would appear on the list of available integrators.

NOTE : Integrator commits only if all rows are uploaded successfully

Make sure that the API + Procedure is defined in the database, as the Integrator uses the procedure parameters to identify the data/columns to handle and shows it in the layout.

n.         Now the Custom Integrator is ready.

B.         Associate Function with Custom Integrator

Having created a Custom WebADI Integrator we need to take care of the Function Security in Oracle Apps. For this we need to create a Function and associate the Integrator with this function and assign it to the menu seeded WebADI Menu.

The steps to create this link are as follows

a.         Create a from function using Sysadmin or Application Developer responsibilities

b.         Navigate to Desktop Integration and click on “Create Document”

c.         Select "HR Maintain Integrator Form Function Associations". This is a standard integrator that allows us to associate a Form Function with an Integrator.

d.         Select layout "Form Function Association"(default standard).

e.         Select "Form Function Association" (default standard)for content.

f.          Provide the Application Short Name and Integrator name (just create in steps earlier), Click continue, and on Review screen, click on "Create Document" button. This will generate the excel sheet with all the required details. Open the excel sheet.

g.         Complete the excel sheet by entering the Form function name we created earlier.

h.         Click Oracle -> Upload. This will create the association between Integrator and the Form Function.

i.          Add the function to the Desktop Integration Menu.

j.          The Function is associated with the Custom Integrator.

C.        Creating Integrator Layout

Having created the Custom Integrator and associated a Function with Custom Integrators, we need to define a layout. This layout actually defines how the XLSheet will look like for uploading actual data.

a.         In the Desktop Integration Responsibility, click "Define Layout: function to create a Layout for existing or Custom Integrators.

b.         Select the Integrator created earlier

c.         Click Define Layout and give Layout a name

d.         Click Continue. The next screen shows all the procedure parameters. Choose as Header if the value should remain same for all data lines or Line if the value changes for each line. See the screen shot below. Click Apply. For complex layouts, we need to first identify Header and Lines at the start itself.

e.         That's it !! We are done. And ready to use this custom integrator to load data into Oracle Apps without coding or programming anymore

D.        Using the Custom Integrator

Before using the custom integrator the application server node has to be bounced once. The following steps are involved in using the Custom Integrator to upload data using excel

a.         Select Desktop Integration Responsibility and click on "Create Document" function.

b.         Select our custom Integrator from the list of available integrators

c.         Select the viewer, generally Excel, to generate the document.

d.         Select our layout we defined in our earlier steps.

e.         Select "None" on the Content tab.

f.          Review and click on Create Document.

g.         This will create and excel sheet with all the columns we defined in the layout. Fill the excel sheet with the data we want to upload.

h.         Click on Oracle -> Upload and wait for it to upload the records. Upon successful upload, a smiley icon is shown for each record.

NOTE :- Integrator commits only if all rows are uploaded successfully.

E.         Adding a LOV to a Column

The excel sheet may have columns for which a list of values need to be attached. In order to achieve this, the following update script needs to be applied

UPDATE bne_interface_cols_b

   SET val_id_col = 'VENDOR_NAME',   -- The Base Table column for which the LOV.

       val_mean_col = 'VENDOR_NAME', -- The Base Table column for which the LOV.

       val_type = 'TABLE',

       lov_type = 'POPLIST',

       val_obj_name = 'PO_VENDORS',  -- Base table for the LOV .

       val_addl_w_c =

          'vendor_type_lookup_code in (''''VENDOR'''',''''HP SUPPLIER'''')'

            -- LOV where clause.

 WHERE interface_col_name = 'P_SUPPLIER_NAME' -- The destination column for the LOV.

       AND application_id = 140            -- Application Id of the Integrator

       AND interface_code = 'GENERAL_1_INTF'