Oracle Payables Release 11 Open Invoice Interface

1
Oracle PayablesRelease 11Open Invoice Interface
Spectrum Group
2
Agenda

• Introduction
• Invoice Import Interface
• Method
• Tables AP_EXPENSE_REPORT_HEADERS_ALL
• AP_EXPENSE_REPORT_LINES_ALL
• Results
• Invoice Import Exceptions Report
• Invoice Import Report
• Limitations
• No Purchase Order Matching
• Limited Edits
• No Easy Method for Error Correction
• Release 11 Open Invoice Interface

3
Invoice Import

• Initially designed for Expense Express and
• Project Accounting
• Creates invoice and distribution lines from
• information you load into the interface
  tables
• (AP_EXPENSE_REPORT_HEADERS_ALL and
• AP_EXPENSE_REPORT_LINES_ALL)
• Invoices imported via custom code that loads
• above tables.

4
Invoice Data
SQLLoader or PL/SQL
Invoice Import
Accepted Invoice Report
Invoice Exception Report
5
Minimum Requirements for old Invoice Import

• Invoice Line
• Report_Header_id
• Last_Update_Date
• Last_Updated_By
• Code_Combination_id
• Item_Description
• Set_Of_Books_Id
• Currency_Code
• Line_Type_Lookup_Code
• Creation_Date
• Created_By

• Invoice Header
• Report_Header_Id
• Week_End_Date
• Creation_Date
• Created_By
• Last_Update_Date
• Last_Updated_By
• Vouchno (0)
• Total
• Vendor_id
• Vendor_Site_id
• Invoice_Num
• Accts_Pay_Code_Combination_id
• Set_Of_Books_id
• Source
• Accounting_Date
• Default_Currency_Code

6
Invoice Import Results

• Runs through validation routine to assure
  invoices contain valid information
• Checks for Valid Vendor/site
• Checks Code Combinations
• Valid Open Accounting Period
• Source
• Provides reports Invoice Import and Invoice
  Import Exception Reports
• Will create standard or credit type invoices

7
Invoice Import Limitations

• Does not provide matching capabilities
• No method for making corrections
• Requires custom Form or SQLPlus
• No Proration capabilities
• Requires Code Combination ID up front

8
Then along comes the Open Invoice Interface

• A quick way to enter large numbers of invoices
  via the Invoice Gateway Form
• EDI or invoices from other systems may be brought
  into the Gateway for import
• Credit card transactions may be imported using
  the Credit Card Invoice Interface Summary
• Provides matching, proration, workflow
  processing, forms...

9
Payables Open Interface Import
The Invoice Gateway is a mechanism through which
the Interface Tables AP_INVOICES_INTERFACE AP_INV
OICE_LINES_INTERFACE Can be directly
updated. When you initiate importing invoices,
the import program validates all required invoice
data in these tables. After it validates the
information, Payables Open Interface Import
creates invoices from the information in the
Payables Open Interface tables. Payables also
creates scheduled payments based on the payment
terms. Invoice information contained in these
tables is then distributed to the appropriate
application tables. AP_INVOICES_ALL AP_PAYMENT_SCH
EDULES_ALL AP_INVOICE_DISTRIBUTIONS_ALL AP_BATCHES
_ALL
10
The minimum information that needs to be imported
into the interface tables to successfully
create and match Invoice records is as follows
11
Successful PO matching and Tolerance checking
occur with a minimum of information. If there is
a data element not included on the EDI or Vendor
file, PL/SQL can be utilized to bring in what is
missing.
SELECT po_header_id,vendor_site_id,vendor_id,org_i
d INTO v_po_header_id,v_vendor__site_id,v_ven
dor_id,v_org_id FROM po_headers_all WHERE
segment1 v_po_number BEGIN SELECT
vendor_name INTO v_vendor_name FROM
po_vendors WHERE vendor_id
v_vendor_id END
In the above example, the values for 5 different
data elements are obtained with the single data
element PO Number from the Vendor EDI file.
12
Notes

• The SOURCE column in AP_INVOICES_INTERFACE will
  contain the
• QuickCode defined for type of Invoice Batch we
  are importing.

• The GROUP_ID column in AP_INVOICES_INTERFACE will
• be the Gateway Batch Number.

• INVOICE_ID column serves as Primary Key and there
  must be
• corresponding records in both tables.

• There can be multiple Line IDs associated to one
  Invoice ID.

• To successfully match Invoices with their
  associated
• Purchase Orders the Purchase Order information
  must be
• contained in the Oracle Purchasing tables.

13
After the initial Invoice detail has been
transferred into AP_INVOICE_INTERFACE and
AP_INVOICE_LINES_INTERFACE using SQLLoader or
PL/SQL program, the user can enter the Invoice
Gateway and query the newly loaded Invoice detail.
The Source and Gateway Batch number must be given
and then the Find button clicked and all the
Invoice records assigned to that batch will be
brought in.
14
In the Invoice Gateway, when the Invoice batch is
queried, Invoice header and Invoice line
information can be viewed.
15
To facilitate the transfer of the data from the
Interface tables to the application tables the
Create Invoices button at the bottom of the
Invoice Gateway Form needs to be clicked.
Clicking the Create Invoices button and filling
in the following Create Invoices window starts
a concurrent request job that transfers the data
from the Interface tables to the application
tables, actually creating the Invoices.
16
The concurrent program is named Payables Open
Interface Import. This job produces a report
that shows all of the successfully created
Invoice records on the first pages and any
rejected records on the following pages.
Information produced by the Payables Open
Interface Report for successfully created records
includes (Supplier Number, Supplier Name,
Invoice , Invoice Date, Invoice Amount, Dist.
Number, Dist. Amount, Accounting Date, Source
Name, Batch Name, Hold Name, Hold Reason, GL
Date, Purge Flag.)
17
The Payables Open Interface Import matches the
Invoice information against the associated
Purchase Order and validates the data entered.
Reasons why an Invoice would be rejected are as
follows

• Incorrect Invoice Amount. Invoice amount does not
  equal sum of the line amounts.
• Accounting Date is not in an Open Period.
• Duplicate Invoice Number. Invoice Number for this
  supplier already exists.
• Inconsistent PO Supplier Number. Supplier per the
  Interface does not match
• supplier per matched PO.

18
Error Handling using Custom Log File
Using Oracle File I/O, custom log files can be
generated to identify records from the Vendor
file that have processing errors. These Items can
have user friendly references contained within.
19
Proration of Tax or Freight Charges

• Records in the AP_INTERFACE_LINES table create
  one or more invoice
• distributions. Note that one row may create more
  than one distribution. For
• example, if you enter a Tax line in this table
  and prorate it across three item
• lines, during the Open Interface Import the
  system will create three Tax
• distributions based on the single Tax line in
  this table.

• LINE_GROUP_NUMBER If you want to prorate a group
  of lines, enter a value
• to identify each line to which you want to
  prorate. For example, if you want to
• prorate tax to two Item Lines, enter the same
  Group Number for the two Item
• lines and theTax line.

• PRORATE_ACROSS_FLAG If you set this column to
  Y AND THIS IS A
• NON-Item type distribution(Tax,Miscellaneous, or
  Freight), Payables will
• prorate against all Item type distributions with
  the same LINE_GROUP_
• NUMBER as this line.

20
Account Overlay in Payables Open Interface Import
There are 4 ways that an Invoice distribution can
get accounting information when it is imported
from the Payables Open Interface tables. For
example, if you are matching to a Purchase Order
and you know that the cost center on the
purchase order is incorrect, the Account Overlay
feature can do this for you automatically during
import.

• DIST_CODE_COMBINATION_ID in AP_INVOICE_LINES_INTER
  FACE.

• DIST_CODE_CONCATENATED in AP_INVOICE_LINES_INTERFA
  CE.

• Distribution Set

• PO Distributions from a Purchase Order match.

You can use the Account Overlay feature to
overwrite one or more account segments of the
account that Payables generates from one of the
above 4 sources.
21
During Payables Open Interface Import, the system
uses values in the following four Payables Open
Interface table columns to overlay the account.

• DIST_CODE_CONCATENATED- used for overlay only if
  this value has
• one or more null segments.

• BALANCING_SEGMENT

• COST_CENTER

• ACCOUNT

BALANCING_SEGMENT, COST_CENTER, and ACCOUNT will
overlay any values in DIST_CODE_CONCATENATED,
even if it includes null values.
Account Overlay IS NOT IMPLEMENTED with
proration of Tax, Freight, or Miscellaneous
lines. The account for these lines comes from
the respective Item line. All other lines not
created by proration can be overlayed. In
addition, if an Item line includes tax, then the
tax account will be overlayed according to the
overlay rules of the item.
22
The user can individually view and approve each
Invoice.
23
During the approval process the system matches
the Invoice against any Tolerances that have been
defined by the organization. If there are no
discrepancies the following message is received
However , if there is a discrepancy a different
message is displayed.
24
The user can then view the Hold information
against this Invoice.
25
Open Interface Invoices Form
26
Open Interface Invoice Lines Form