Monday, May 17, 2010
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
Optional Columns
Table: AP_INVOICE_LINES_INTERFACE
Required Columns
Optional Columns
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 |
UNIT_PRICE | Populated if different from |
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 |
PRICE_CORRECTION_FLAG | Populated 'Y' if it is price correction |
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.
0 Responses to “Required and optional columns of Payables open interface import of PO matched invoices (R12)”
Post a Comment