Saturday, September 26, 2015

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'

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect