Loading

Monday, May 17, 2010

Required and Optional Columns of Payables open interface import for loading SIMPLE INVOICES


In this post, I tried to explain the required and optional columns in the payables invoice import tables for importing simple invoices i.e. not matched with any PO or Project Related.
 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
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
 LINE_TYPE_LOOKUP_CODE = 'ITEM'
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
LINE_TYPE_LOOKUP_CODE                  
'ITEM'
AMOUNT                              

ACCOUNTING_DATE
Optional. Defaulted from INVOICE_DATE or SYSDATE
DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID
Validated against
 - 'Parent' must be 'No' for All the segments values
 - DIST_CODE_CONCATENATED needs to meet the security rules
 - DIST_CODE_COMBINATION_ID must be in
SELECT code_combination_id
  FROM gl_code_combinations
 WHERE account_type = 'E'
   AND enabled_flag = 'Y'
   AND summary_flag = 'N'
   AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE - 1)
                   AND NVL (end_date_active, SYSDATE + 1)
LINE_TYPE_LOOKUP_CODE = 'TAX'
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                 
'TAX'
AMOUNT                              

ACCOUNTING_DATE
Optional. Defaulted from INVOICE_DATE or SYSDATE
DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID
Optional. 
 Defaulted from AP_TAX_CODES.TAX_CODE_COMBINATION_ID. If one of them is populated, then validated against 
 - 'Parent' must be 'No' for All the segments values
 - DIST_CODE_CONCATENATED needs to meet the security rules
 - DIST_CODE_COMBINATION_ID must be in
SELECT code_combination_id
  FROM gl_code_combinations
 WHERE account_type = 'E'
   AND enabled_flag = 'Y'
   AND summary_flag = 'N'
   AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE - 1)
                   AND NVL (end_date_active, SYSDATE + 1)

LINE_TYPE_LOOKUP_CODE = 'FREIGHT'
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
LINE_TYPE_LOOKUP_CODE                  
'FREIGHT'
AMOUNT                              

ACCOUNTING_DATE
Optional. Defaulted from INVOICE_DATE or SYSDATE
DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID
Optional. Defaulted from AP_SYSTEM_PARAMETERS.
FREIGHT_CODE_COMBINATION_ID. If one of them is populated, then validated against 
 - 'Parent' must be 'No' for All the segments values
 - DIST_CODE_CONCATENATED needs to meet the security rules
 - DIST_CODE_COMBINATION_ID must be in
SELECT code_combination_id
  FROM gl_code_combinations
 WHERE account_type = 'E'
   AND enabled_flag = 'Y'
   AND summary_flag = 'N'
   AND SYSDATE BETWEEN NVL (start_date_active, SYSDATE - 1)
                   AND NVL (end_date_active, SYSDATE + 1)

0 Responses to “Required and Optional Columns of Payables open interface import for loading SIMPLE INVOICES”

Post a Comment

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.