Overview of Required fields for Payables Open Interface Import Records
=================================================================
TEST CASE FOR SIMPLE INVOICES - NOT PO MATCHED OR PROJECT RELATED
=================================================================
AP_INVOICE_INTERFACE
====================
Required Columns
================
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
================
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)
AP_INVOICE_LINES_INTERFACE
==========================
Required Columns for LINE_TYPE_LOOKUP_CODE = 'ITEM'
===================================================
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 for LINE_TYPE_LOOKUP_CODE = 'TAX'
===================================================
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 ('TAX')
TAX_CODE or TAX_CODE_ID (Validated against AP_TAX_CODES_ALL)
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))
Required Columns for LINE_TYPE_LOOKUP_CODE = 'FREIGHT'
===================================================
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))
=================================
TEST CASE FOR PO MATCHED INVOICES
=================================
AP_INVOICE_INTERFACE
====================
Required Columns
================
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.
Validated against PO_HEADERS)
INVOICE_AMOUNT (Positive Amount)
SOURCE (Must be in select lookup_code from ap_lookup_codes where lookup_type='SOURCE')
ORG_ID (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS_ALL.ORG_ID)
Optional Columns
================
INVOICE_DATE (Defaulted to SYSDATE)
INVOICE_TYPE_LOOKUP_CODE (Defaulted to 'STANDARD')
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)
AP_INVOICE_LINES_INTERFACE
==========================
Required Columns for PO Matched Lines
=====================================
INVOICE_ID (Populated from AP_INVOICES_INTERFACE.INVOICE_ID)
INVOICE_LINE_ID (Populated from AP_INVOICE_LINES_INTERFACE_S.NEXTVAL)
LINE_NUMBER (A unqiue number to the invoice)
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 for PO Matched Lines
=====================================
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)
======================================
TEST CASE FOR PROJECT RELATED INVOICES
======================================
AP_INVOICE_INTERFACE
====================
Required Columns
================
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)
ORG_ID (Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS_ALL.ORG_ID)
SOURCE (Must be in select lookup_code from ap_lookup_codes where lookup_type='SOURCE')
Optional Columns
================
INVOICE_DATE (Defaulted to SYSDATE)
INVOICE_TYPE_LOOKUP_CODE (Defaulted to 'STANDARD')
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)
AP_INVOICE_LINES_INTERFACE
==========================
Required Columns for project related lines
==========================================
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
PROJECT_ID (Validated against PA_PROJECTS_ALL.PROJECT_ID)
TASK_ID (Validated against PA_TASKS.TASK_ID)
EXPENDITURE_TYPE (Validated against PA_EXPENDITURE_TYPES.EXPENDITURE_TYPE)
EXPENDITURE_ITEM_DATE (Needs to be between the task start date and end date)
EXPENDITURE_ORGANIZATION_ID (Validated against PA_EXP_ORGS_IT.ORGANIZATION_ID)
PA_QUANTITY
Optional Columns for project related lines
==========================================
ACCOUNTING_DATE (Defaulted from Invoice Date or SYSDATE)
PA_ADDITION_FLAG ('Y' if the distribution has been transferred into Oracle Projects)
No comments:
Post a Comment