How to Upload AP Invoices using Oracle WebADI Custom Integrator

Oracle WebADI:

Oracle Web Application Desktop Integrator (Web ADI) is Oracle E-Business Suite's solution for integrating E-Business Suite applications with desktop applications such as Microsoft Excel, Word and Projects.  "Integrators" encapsulate the metadata and other information needed to integrate a particular Oracle E-Business Suite task with a desktop application.  You can use the Desktop Integration Framework (DIF) to create custom integrators for Oracle Web ADI in Oracle E-Business Suite Release 12.1.2. The ability to create custom importers was added in EBS 12.1.3

Prerequisites:

1.  Setup

Excel Sheet Options

A.  Go to File > Options > Trust Center

B.  Click the button 'Trust Center Settings'

C.  In Macro Settings -> check radio button Enable all Macros

D.  Click the checkbox 'Trust access to VBA project object model'

 

2. Set the profile option “BNE Allow No Security Rule” to yes as given in the below Screen shot

Integrators:

Integrator is a set of metadata that encapsulates all the information needed to integrate a particular Oracle E-Business Suite task with a desktop application.

By defining an integrator, you enable your users to do the following:

1.      Generate a formatted data template on the desktop containing application-specific fields for data entry.

2.      Enter or modify the data in the desktop application with the same kind of validation rules that existed in the EBS application.

3.      Upload data from the desktop document to feed the data to Staging Tables, Interface tables or directly to base tables.

4.      Verify the results of the upload process and correct any errors or data issues immediately.

   Setups:

    1. Define Custom Package for uploading invoices into our custom Table

    Create Custom Table:

    AP_CUSTOM_INV_INTERFACE

     create or replace package WebADI_INVOICE_UPLOAD_PKG is
 
PROCEDURE ap_upload_proc (
              p_invoice_num VARCHAR2,
              p_invoice_date DATE,
              p_vendor_num VARCHAR2,
              p_vendor_site_code VARCHAR2,
              p_invoice_amount NUMBER,
              p_invoice_currency_code VARCHAR2,
              p_description VARCHAR2,
              p_line_type_lookup_code VARCHAR2,
              p_line_number NUMBER,
              p_segment1 VARCHAR2,
              p_segment2 VARCHAR2,
              p_segment3 VARCHAR2,
              p_segment4 VARCHAR2,
              p_segment5 VARCHAR2,
              p_segment6 VARCHAR2,
              p_segment7 VARCHAR2,
              p_terms_name VARCHAR2,
              p_tax_code VARCHAR2);

end WebADI_INVOICE_UPLOAD_PKG;

    create or replace package body WebADI_INVOICE_UPLOAD_PKG is

PROCEDURE ap_upload_proc(
            p_invoice_num VARCHAR2,
            p_invoice_date DATE,
            p_vendor_num VARCHAR2,
            p_vendor_site_code VARCHAR2,
            p_invoice_amount NUMBER,
            p_invoice_currency_code VARCHAR2,
            p_description VARCHAR2,
            p_line_type_lookup_code VARCHAR2,
            p_line_number NUMBER,
            p_segment1 VARCHAR2,
            p_segment2 VARCHAR2,
            p_segment3 VARCHAR2,
            p_segment4 VARCHAR2,
            p_segment5 VARCHAR2,
            p_segment6 VARCHAR2,
            p_segment7 VARCHAR2,
            p_terms_name VARCHAR2,
            p_tax_code VARCHAR2)
AS
BEGIN

  INSERT into apps.AP_CUSTOM_INV_INTERFACE
        (invoice_num, invoice_date, vendor_num, vendor_site_code, invoice_amount, invoice_currency_code,       description, line_type_lookup_code,
         line_number, segment1, segment2, segment3, segment4, segment5, segment6, segment7, terms_name, tax_code,
         created_by, creation_date)
    VALUES
        (p_invoice_num, p_invoice_date, p_vendor_num, p_vendor_site_code, round(p_invoice_amount,2), p_invoice_currency_code, p_description, p_line_type_lookup_code,
         p_line_number, p_segment1, p_segment2, p_segment3, p_segment4, p_segment5, p_segment6, p_segment7, p_terms_name, p_tax_code,
         FND_GLOBAL.USER_ID, sysdate);

EXCEPTION
  WHEN OTHERS THEN

    raise_application_error(-20001, 'Error : ' || SQLERRM);
END ap_upload_proc;
end WebADI_INVOICE_UPLOAD_PKG;

   

   2. Create Custom Integrator

        Navigation: Desktop Integration Manager >> Create Intergrator

Enter the following information
Integrator Name: Enter your Integrator name
Internal Name: Enter Internal name
Application: Payables
Enable Display in Create Document Page  
Add function 
As "BNE_ADI_CREATE_DOCUMENT" this is very important.
If you are not taken this the custom integrator will not  visible in Oracle webADI uploading 
Click On Next button  

Interface Name: Enter the Interface Name
Interface Type: API - Procedure
Package Name: Give the Package name created in 1st step
Procedure/Function: Give the process name created in 1st step
Click on Apply button

Once you select AP_INV_INTERFACE all the columns automatically appear showing in the below screen shot .

Click on Next button

Click on Next button

Click on Next button

 Click on Submit button

 Once we submit immediatly we will get confirmation message

3. Define Layout 

Navigation: Desktop Integration Manager >> Manage Integrator >> Define Layout 

Select Custom integrator created in 2nd step and click on GO button

Click on Create button

Layout Name: Enter the Layout Name

Number of Headers: 1

Click on Next button

Change Placement to Line to all the columns showing in the below screen shoot and click on 

Next button

Enter the Data entry rows

Click on Apply button.

 4. Define Custom validation program

a. Create custom package with the following validation

• Check the length of vendor number
• Check if any null vendor number is given
• Check Whether given Vendor and Vendor Site Code matches
• Check for 'LINE_TYPE_LOOKUP_CODE'
• Check for existance of AP invoices
• Check whether the given invoice date is with in the current given month
• Check whether the given invoice date is null
• Check for 'TAX_CODE'
• Check for Invoice Currency code
• Check for invalid Code combinations

b. Define Executable:

Navigation: System  Administrator >> Concurrent >> Program >> Executable

Enter the following information

Executable: Give Executable name

Short Name: Short name 

Application: Give application Name

Execution Method: PL/SQL Stored Procedure

Execution File Name: Enter pkg name and procedure name

Save your work.

c. Define Program:

Navigation: System  Administrator >> Concurrent >> Program >>Define

Enter the following information

Program Name: Enter the program name

Shot Name: Give short name

Application: Application Name

Executable Name: Give Executable name

Click on Parameters 

Parameters:

Month Starting Date:

Month Ending Date:

Save your work.

d. Assign this program to request group


5. Upload the invoice


Navigation: Oracle Web ADI >> Create Document

Select our custom integrator and click on Next button

take your excel version and click on next button

Click on Crate Document

Click on Open

Now document is created click on Close button

Enter the information and then Click on Add Ins
Select oracle and then click on Upload

Click on Upload

Now we will get below confirmation message