This interface helps
us to import vendor invoices into Oracle applications from external systems
into Oracle Applications.
Interface tables:
1) AP_INVOICES_INTERFACE
This is the open
interface table for importing AP Invoices from external sources and stores
header information about invoices. Invoice data comes from sources including:
·
EDI invoices from suppliers that are loaded through Oracle e-Commerce
Gateway
·
Supplier invoices that are transferred through the Oracle XML Gateway
·
Invoices that are loaded using Oracle SQL*Loader
·
Lease invoices from Oracle Property Manager
·
Lease payments from Oracle Assets
·
Credit card transaction data that are loaded using the Credit Card
Invoice Interface Summary
·
Expense Report invoices from Oracle Internet Expenses
·
Payment Requests from Receivables
·
Invoices that are entered through the Invoice Gateway.
There is one row for
each invoice you import. Oracle Payables application uses this information to
create invoice header information when Payables Open Interface program is
submitted.
Data in the
AP_INVOICES_INTERFACE table used in conjunction with AP_INVOICE_LINES_INTERFACE
table to create Payables Invoice, Invoice lines, Distributions and Schedule
payments. Data in this table can be viewed and edited using ‘Open Interface
Invoices’ window. The Payables Open Interface program validates each record in
this interface table selected for import, and if the record contains valid data
then the program creates a Payables Invoice.
Important columns:
INVOICE_ID (Required) : Unique
identifier for this invoice within this batch. Same value should be populated
in invoice’s lines in the AP_INVOICE_LINES_INTERFACE table to identify the data
as belonging to the same invoice.
INVOICE_NUM (Required) :
Enter the invoice number that needs to be assigned to the invoice created in
Payables from this record.
INVOICE_TYPE_LOOKUP_CODE (Optional) :
Type of invoice: Credit or Standard.
INVOICE DATE (Optional) : Date of
the invoice. If you do not enter a value, the system uses the date you submit
Payables Open Interface Import as the invoice date.
PO_NUMBER (Optional) : Purchase
order number from PO_HEADERS.SEGMENT1. This column needs to be populated if
invoice to be matched with an purchase order.
VENDOR_ID & VENDOR_SITE_ID
(Required) : VENDOR_ID
is unique identifier for a supplier and VENDOR_SITE_ID is Internal
supplier site identifier. Supplier of the invoice to be derived by value in one
of the following columns in this table: VENDOR_ID, VENDOR_NUM, VENDOR_NAME,
VENDOR_SITE_ID or PO_NUMBER.
VENDOR_NUM &
VENDOR_NAME (Optional) : Supplier number and name. You must identify the
supplier by entering a value for one of the following columns in this table:
VENDOR_ID, VENDOR_NUM, VENDOR_SITE_ID, VENDOR_SITE CODE, or PO_NUMBER.
INVOICE_AMOUNT (Required) : Amount
of the invoice.
INVOICE_CURRENCY_CODE (Optional)
: Currency code for the invoice. If you want to create foreign currency
invoices, enter a currency code that is different from your functional
currency.
EXCHANGE_RATE (Optional) :
This column is required if you enter a foreign currency code in the
INVOICE_CURRENCY_CODE column and you enter User as the EXCHANGE_RATE_TYPE.
TERMS_ID (Optional) :
Internal identifier for the payment terms.
DESCRIPTION (Optional) : Enter
the description that you want to assign to the invoice created from this
record.
SOURCE (Required) :
Source of the invoice data. If you import EDI invoices from the Oracle EDI
Gateway, the source is EDI Gateway. For invoices you import using SQL*Loader,
use a QuickCode with the type Source that you have defined in the QuickCodes
window in Payables.
2) AP_INVOICE_LINES_INTERFACE
This is the lines
interface table for the AP Invoice Open Interface and it is used in conjunction
with AP_INVOICE_INTERFACE table. AP_INVOICE_LINES_INTERFACE stores information
used to create one or more invoice distributions. Note that one row in this
table may create, during the import process, more than one invoice
distribution.
Important columns:
INVOICE_ID (Required) :Enter the
INVOICE_ID of the corresponding invoice in the AP_INVOICES_INTERFACE table.
INVOICE_LINE_ID
: This value is not
required. You can enter a unique number for each invoice line of an invoice.
LINE_NUMBER (Optional): You
can enter a unique number to identify the line.
LINE_TYPE_LOOKUP_CODE (Required) :
Enter the lookup code for the type of invoice distribution that you want
Payables Open Interface Import to create from this record. The code you enter
must be ITEM, TAX, MISCELLANEOUS, or FREIGHT. These lookup codes are stored in
the AP_LOOKUP_CODES table.
AMOUNT (Required): The
invoice distribution amount. If you are matching to a purchase order, the
AMOUNT = QUANTITY_INVOICED x UNIT PRICE. If the total amount of all the invoice
distributions does not equal the amount of the invoice that has the same
INVOICE_ID, then Payables Open Interface Import will reject the invoice.
Concurrent
program:
Payables Open
Interface Import.
Parameters:
Source: Choose the source of
the invoices from the list of values. Use EDI Gateway, Credit Card, or a Source
type Quick Code you defined in the Payables Quick Codes window.
Group: To limit the import to
invoices with a particular Group ID, enter the Group ID. The Group must exactly
match the GROUP_ID in the Payables Open Interface tables.
Batch Name: Payables groups the
invoices created from the invoices you import and creates an invoice batch with
the batch name you enter. You can enter a batch name only if you have enabled
the Use Batch Control Payables option, and if you have enabled the Use Batch
Control Payables option, you must enter a batch name. If you use a batch name
and some invoices are rejected during the import process, you can import the
corrected invoices into the same batch if you enter the exact batch name during
the subsequent import.
Hold Name: If you want to place
all invoices on hold at the time of import, enter an Invoice Hold Reason. You
can define your own hold reasons in the Invoice Approvals window.
Hold Reason: Payables displays the
Invoice Hold Reason Description.
GL Date: If you want to assign
a specific GL Date to all invoices, enter a GL Date. If you do not enter a
value here, the system will assign a GL Date based on the GL Date Payables
option.
Purge: Enter yes if you want
Payables to delete all successfully imported invoice records that match the
Source and Group ID of this import. Payable does not delete any invoice data
for which it has not yet created invoices. If you want to purge later, you can
use the Payables Open Interface Purge Program.
Steps:
1. Firstly, let’s get a
unique number to be used as INVOICE_ID to the invoice to be imported.
This method ensures that each invoice has a unique INVOICE_ID assignment.
select apps.ap_invoices_interface_s.nextval from dual;
NEXTVAL
-------
152303
2. Then, create records in the Invoice Open
Interface tables through SQL queries.
INSERT INTO apps.ap_invoices_interface
(invoice_id, invoice_num, vendor_id, vendor_site_id,
invoice_amount, invoice_currency_code,
invoice_date,
description, pay_group_lookup_code,
SOURCE, org_id
)
VALUES (152303, 'INV100', '8163', '12345',
2300.00, 'USD', TO_DATE ('01-31-2012', 'mm-dd-yyyy'),
'This
Invoice is created for test purpose', 'WUFS
SUPPLIER',
'Manual
Invoice Entry', 58
);
INSERT INTO apps.ap_invoice_lines_interface
(invoice_id, line_number, line_type_lookup_code,
amount
)
VALUES (152303, 1, 'ITEM', 2300.00
);
3. You can go to Payables >
Invoices > Entry > Open Interface Invoices to check the details of
Invoice and Invoice Lines from the front end. If required you can do any
modifications here. Alternatively you can use these forms to put invoice data
in AP_INVOICES_INTERFACE and AP_INVOICE_LINES_INTERFACE tables.
4. Go to the front end and run the
concurrent program “Payables Open Interface Import” to submit a request
for Invoice Import.
Output:
output of Payables Open Interface Import
5. The imported invoice becomes available for review
in Invoices Workbench.
Interface Invoice through Invoice Bench.
No comments:
Post a Comment