This interface helps us to import
vendor invoices into Oracle applications from external systems into Oracle
Applications.
Pre-requisites:
Set of Books
Code combinations
Employees
Look ups
Interface tables:
AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
Error table:
1.AP_INTERFACE_REJECTIONS
2.AP_INTERFACE_CONTROLS
Base tables:
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
Concurrent program &
Parameter
Nav : AP Superuser
responsibility--View--Request--Submit New Request --select Program Payables
Open Interface Import
Operating Unit: Operating
Unit Name
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.
Summarize Report: Yes
or No.
Validations:
Check for valid vendor
Check for Source, Location, org_id,
currency_code’s validity
Check for valid vendor site code.
Check if record already exists in Payables
interface table.
Some important columns that
need to be populated in the interface tables:
AP_INVOICES_INTERFACE:
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.
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.
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
Payable 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 Payable s Open
Interface Import will reject the invoice.
DESCRIPTION
TAX_CODE
PO_NUMBER
PO_LINE_NUMBER
PO_SHIPMENT_NUM
PO_DISTRIBUTION_NUM
PO_UNIT_OF_MEASURE
QUANTITY_INVOICED
DIST_CODE_CONCATENATED
DIST_CODE_COMBINATION_ID
ATTRIBUTE1
ATTRIBUTE2
ATTRIBUTE3
ATTRIBUTE4
ATTRIBUTE5
ORG_ID
Debugging Interface Errors
After the interface program
completes invoices will be created in the system.
For the records failing the
validations no invoices will be created and the records will be errored out in
the interface.
Some of the most fatal errors are
No supplier or supplier site
The invoice number is a duplicate
If the invoice level information is
correct, Payables will validate all values at the line level, and the
rejections report will list all line level problems. If a distribution is
rejected, the whole invoice is rejected.You can correct the data in one of the
following ways:
Use the Open Interface Invoices
window to correct problems directly in the Payables Open Interface tables.
Invoice Validation
Once the Invoices have been created
invoice validation needs to be done so that invoices can be processed further
to make payments and transfer to GL.
For this we need to either manually
validate individual invoices from the Invoice Screen or run the Invoice
Validation Program.
This activity will validate the
invoices and allow further processing, In case invoices fail in validation then
the invoices will be put on hold .
the user can see the hold details
by pressing the "HOLDS" button on the invoice screen. at the back end
hold details will be stored in the table : AP_INVOICE_HOLDS
but only 22 invoices are being rejected.
But the reason in the ap_interface_rejections is blank.
how could you trace the errors?
Payables open Interface Import says the records are 'REJECTED', but does not display any error messages.
If the DEBUG Log is enabled then we find the errors.
Without debug we do not see the reject reason for the records. This happens
only to invoices which are rejected due to not availability of exchange rate.
1) Bug Summary
A. Functional
1. Description
When an foreign currency invoice is imported, with no
exchange rate, the invoice is rejected without any
rejection code, and is not displayed in the rejection list.
2. Resolution
Invoice without any exchange rate data should be rejected
with valid rejection code.
INVALID DISTRIBUTION ACCT
Given GL Code combination is invalid, to verify use the below query by passing the parameter of gl code combination
SELECT * FROM GL_CODE_COMBINATIONS_KFV WHERE CONCATENATED_SEGMENTS = <GL Code combination>;
If there is no value from the above query then given code combination is not available in oracle.
DUPLICATE LINE NUMBER
If the invoice file has the value of line number multiple times then it will throw the error like this.Correct the line number and make it unique and process the file.
ZX_IMP_TAX_RATE_AMT_MISMATCH
Tax code is defined for zero tax value and the file having the tax value of greater than zero.Either change the tax code or tax amount value.
NO EXCHANGE RATE
In case of foreign currency involved in the payment process then exchange rate is mandatory. Make sure exchange rate is available
INVALID ASSIGNMENT
Sequential numbering setup missed against the operating unit
After completing to extract the data from EBS. please user below query:
SELECT hou.name ou_name,
aia.source,
aps.vendor_name,
aps.segment1 vendor_no,
assa.attribute1 coda_vendor_no,
aps.vendor_type_lookup_code vendor_type,
aia.invoice_type_lookup_code invoice_type,
aia.invoice_num,
aia.invoice_date,
aia.gl_date,
aia.invoice_currency_code,
aia.invoice_amount,
aila.description,
gcck.concatenated_segments ap_liability_acc,
aila.overlay_dist_code_concat,
aila.line_type_lookup_code,
aila.amount line_amount,
aila.tax_classification_code,
aia.exchange_rate,
aia.exchange_rate_type,
DECODE (aila.base_amount,
NULL, NVL (aia.exchange_rate, 1) * aila.amount,
aila.base_amount) functional_amount,
tax_invoice_internal_seq,
supplier_tax_invoice_date
FROM apps.ap_invoices_all aia,
apps.ap_invoice_lines_all aila,
apps.gl_code_combinations_kfv gcc,
apps.gl_code_combinations_kfv gcck,
apps.ap_suppliers aps,
apps.ap_supplier_sites_all assa,
apps.hr_operating_units hou
WHERE aia.org_id = '<YOUR ORG ID>'
AND aia.created_by = '<YOUR USER>'
---and TO_CHAR (aia.creation_date, 'DD-MON-YYYY') = '23-AUG-2018'
AND aia.invoice_id = aila.invoice_id
AND aia.vendor_id = aps.vendor_id
AND aia.vendor_id = assa.vendor_id
AND aia.vendor_site_id = assa.vendor_site_id
AND aia.org_id = hou.organization_id
AND aila.default_dist_ccid = gcc.code_combination_id
AND aia.accts_pay_code_combination_id = gcck.code_combination_id
AND aia.source = '<SOURCE name>'
No comments:
Post a Comment