Required and optional columns of Payables open interface import of PO matched invoices (R12)
In this post, I tried to explain the required and optional columns in the payables invoice import tables for importing PO Matched Invoices.
TABLE: AP_INVOICE_INTERFACE
Required Columns
Column Name
|
Validation
|
INVOICE_ID
|
Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
|
INVOICE_NUM
|
Must be unique to the supplier
|
PO_NUMBER
|
An approved, not cancelled, not closed or final closed PO
|
VENDOR_ID or VENDOR_NUM or VENDOR_NAME
|
An active vendor. Validated against PO_VENDORS
|
VENDOR_SITE_ID or VENDOR_SITE_CODE
|
An active pay site. Validated against PO_VENDOR_SITES
|
INVOICE_AMOUNT
|
Positive amount for 'STANDARD' type, Negative amount for 'CREDIT' type
|
ORG_ID
|
Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
|
SOURCE
|
Must be in
SELECT lookup_code
FROM ap_lookup_codes
WHERE lookup_type = 'SOURCE';
|
Column Name
|
Validation
|
INVOICE_DATE
|
Defaulted to SYSDATE
|
INVOICE_TYPE_LOOKUP_CODE
|
Defaulted to 'STANDARD'. It can be 'STANDARD' or 'CREDIT'
|
INVOICE_CURRENCY_CODE
|
Defaulted from PO_VENDOR_SITES.
INVOICE_CURRENCY_CODE
|
EXCHANGE_RATE_TYPE
|
Defaulted from AP_SYSTEM_PARAMETERS.
DEFAULT_EXCHANGE_RATE_TYPE
|
TERMS_ID or TERMS_NAME
|
Defaulted from
PO_VENDOR_SITES.TERMS_ID
|
DOC_CATEGORY_CODE
|
Only populated if using automatic voucher number
|
PAYMENT_METHOD_LOOKUP_CODE
|
Defaulted from PO_VENDOR_SITES
.PAYMENT_METHOD_LOOKUP_CODE
|
PAY_GROUP_LOOKUP_CODE
|
Defaulted from PO_VENDOR_SITES.
PAY_GROUP_LOOKUP_CODE
|
ACCTS_PAY_CODE_COMBINATION_ID
|
Defaulted from PO_VENDOR_SITES.
ACCTS_PAY_CODE_COMBINAITON_ID
|
GROUP_ID
|
Group identifier. Suggest to use it
|
STATUS
|
DO NOT POPULATE IT
|
Table: AP_INVOICE_LINES_INTERFACE
Required Columns
Column Name
|
Validation
|
INVOICE_ID
|
Populated from AP_INVOICES_INTERFACE.INVOICE_ID
|
INVOICE_LINE_ID
|
Populated from AP_INVOICE_LINES_INTERFACE_S.
NEXTVAL
|
LINE_NUMBER
|
A unique number to the invoice
|
TAX_CODE or TAX_CODE_ID
|
Validated against AP_TAX_CODES_ALL
|
LINE_TYPE_LOOKUP_CODE
|
'ITEM'
|
AMOUNT
|
Should be QUANTITY_INVOICED * UNIT_PRICE
|
If MATCH_OPTION is 'P', then populate
| |
RELEASE_NUM or PO_RELEASE_ID
|
For Blanket Release only,
validated against PO_RELEASES_ALL
|
PO_NUMBER or PO_HEADER_ID
|
Validated against PO_HEADER_ALL
|
PO_LINE_NUMBER or PO_LINE_ID
|
Validated against PO_LINES_ALL
|
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID
|
Validated against PO_LINE_LOCATIONS_ALL
|
If MATCH_OPTION is 'R', then populate
| |
RECEIPT_NUMBER
|
Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM
|
RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID
|
Validated against RCV_TRANSACTIONS
|
Column Name
|
Validation
|
QUANTITY_INVOICED
|
Populated if different from PO shipment
|
UNIT_PRICE
|
Populated if different from PO shipment
|
MATCH_OPTION
|
'P' or 'R' or Defaulted from PO_VENDOR_SITES.MATCH_OPTION
|
ACCOUNTING_DATE
|
Defaulted from INVOICE_DATE or SYSDATE
|
FINAL_MATCH_FLAG
|
Populated 'Y' if it is final matching
|
INVENTORY_ITEM_ID
|
Validated against PO_LINES.INVENTORY_ITEM_ID
|
INVENTORY_DESCRIPTION
|
Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION
|
SHIP_TO_LOCATION_CODE
|
Populated if different from PO shipment
|
PRICE_CORRECTION_FLAG
|
Populated 'Y' if it is price correction
|
=======
Base Tables:
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
(&)
Payment Tables
PAYABLES OPEN INTERFACE IMPORT PARAMETERS
In this below post, I had published information about the parameters related to “Payables Open Interface Import” Program. Info from R12.1.1 instance.
Mandatory Parameters:
1. Source: It can be any of the below list
Ø Invoice Gateway - Invoice records you entered in the Invoice Gateway window
Ø E-Commerce Gateway- Supplier invoices transferred through EDI
Ø Credit Card- Procurement cards transactions from the credit card issuer
Ø Oracle Property Manager- Lease invoices from Oracle Property Manager.
Ø Oracle Assets- Lease payments from Oracle Assets.
Ø User-defined- For invoice loaded from legacy Systems
2. Invoice Batch Name
Ø Use Batch Control Payables Profile option need to be enabled to use this parameter
Ø Payables groups the invoices created from the invoice records you import and creates an invoice batch with the batch name you enter
Ø While Re-importing the error invoices make sure the batch name remains the same
Optional Parameters:
3. Operating Unit: Filtering predicate when null import program process invoice from all operating units.
4. Hold Name: When this parameter is provided with the hold name, then the import program creates the invoices and blocks them with mentioned hold.
5. Hold Reason: Corresponds to Hold Name
6. GL Date: This value is used as a GL date for the invoices whose GL_DATE columns in interface tables are not populated.
7. Purge. Enter Yes if you want Payables to delete from the Payables Open Interface tables all successfully imported invoice records that match the Source and Group ID of this import
Credit goes to the below website(s) :
http://krishnareddyoracleapps.blogspot.com/2012/06/payables-open-interface-import.html
No comments:
Post a Comment