Payable s open interface import
Below diagram will give you the brief description of
“ Payable s open interface import” programs process.
Table Details:
AP Invoice Interface tables
1.AP_INVOICES_INTERFACE
2.AP_INVOICE_LINES_INTERFACE
AP Invoice Base Tables:
1. AP_INVOICES_ALL
2.AP_INVOICE_DISTRIBUTIONS_ALL
3. AP_PAYMENT_SCHEDULES_ALL
Error table:
1.AP_INTERFACE_REJECTIONS
2.AP_INTERFACE_CONTROLS
AP Invoice Interface tables
1.AP_INVOICES_INTERFACE
2.AP_INVOICE_LINES_INTERFACE
AP Invoice Base Tables:
1. AP_INVOICES_ALL
2.AP_INVOICE_DISTRIBUTIONS_ALL
3. AP_PAYMENT_SCHEDULES_ALL
Error table:
1.AP_INTERFACE_REJECTIONS
2.AP_INTERFACE_CONTROLS
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
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
|
=======
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
Optional Columns
Table: AP_INVOICE_LINES_INTERFACE LINE_TYPE_LOOKUP_CODE = 'ITEM' Required Columns
LINE_TYPE_LOOKUP_CODE = 'TAX'
Required Columns
LINE_TYPE_LOOKUP_CODE = 'FREIGHT' Required Columns
|
==============
|
Base Tables:
AP_INVOICES_ALL
AP_INVOICE_LINES_ALL
AP_INVOICE_DISTRIBUTIONS_ALL
(&)
Payment Tables
PAYABLES OPEN
INTERFACE IMPORT PARAMETERS
In this below post, I had published information about the
parameters related to “Payables Open Interface Import” Program.
Info from R12.1.1 instance.
Mandatory Parameters:
1. Source:
It can be any of the below list
Ø Invoice
Gateway - Invoice records you entered in the
Invoice Gateway window
Ø E-Commerce
Gateway- Supplier invoices transferred through EDI
Ø Credit
Card- Procurement cards transactions from the credit card issuer
Ø Oracle
Property Manager- Lease invoices from Oracle Property
Manager.
Ø Oracle
Assets- Lease payments from Oracle Assets.
Ø User-defined-
For invoice loaded from legacy Systems
2. Invoice
Batch Name
Ø Use
Batch Control Payables Profile option need to be enabled to use this parameter
Ø Payables
groups the invoices created from the invoice records you import and creates an
invoice batch with the batch name you enter
Ø While
Re-importing the error invoices make sure the batch name remains the same
Optional Parameters:
3. Operating
Unit: Filtering predicate when null import program process invoice
from all operating units.
4. Hold
Name: When this parameter is provided with the hold name, then the
import program creates the invoices and blocks them with mentioned hold.
5. Hold
Reason: Corresponds to Hold Name
6. GL
Date: This value is used as a GL date for the invoices whose
GL_DATE columns in interface tables are not populated.
7. Purge. Enter
Yes if you want Payables to delete from the Payables Open Interface tables all
successfully imported invoice records that match the Source and Group ID of
this import
8. Summarize Report: Provides a
Summarize report when ‘YES’
Question & Answer
1) Explain the Interface process.
Answer The interface process starts with uploading the legacy data,
which is in the form of flat file to the temporary table, and validating the
data in the temporary. After that the data to be transferred to the Interface
tables. From there by running the Customer Open Interface concurrent program,
the data that is in the interface tables are transferred to main tables.
2) What are the various tables involved in AP Interface?
Answer AP_INVOICES_INTERFACE
AP_INVOICE_LINES_INTERFACE
3) What is the program used to import invoices from interface
tables?
Answer - Payables Open Interface Import
4) What the various validation to be done before transferring
data from temporary table to interface tables?
Answer The various type of validations are:
Not null validation:
Not null validation:
few columns like
- Invoice Number,
- Invoice Date,
- Currency Code etc should not null
Data Type Validation:
- Invoice Amount
- Invoice Date
Business Validation:
- Is the GL date in open period? GL_PERIOD_STATUSES
- Invoice number is not repeating
- Invoice distribution total should match with Invoice header
amount
Generation of CCID
GET_CCID
5) What cases do you use Mapping? How did you achieve
mapping functionality? Give some examples?
6) How can achieve that either complete files is
inserted or complete file is rejected; while inserting into Open
Interface tables?
7) Can you give segment values or CCID while inserted
records into AP_INVOICE_LINES_INTERFACE table?
8) Does the records in the Interface table get auto-purged after
getting successfully imported into base tables?
Ans) No. We have run Payables Open Interface Purge
9) What are
the errors encountered while Importing Invoices have u faced in you experience?
Ans)
Payables
open interface import part - 2
Required
and Optional Columns of Payables open interface import for loading SIMPLE
INVOICES (i.e. not matched with any PO or Project Related.)
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)
|
===
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
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
|
No comments:
Post a Comment