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’;
    
 
Optional Columns
    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
Optional Columns
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
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply