How To Upload AP Invoices Using Oracle WebADI Custom Integrator

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
 


Reference