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
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' |
| 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) |
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) |
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
0 Responses to “Required and Optional Columns of Payables open interface import for loading SIMPLE INVOICES”
Post a Comment