Using SQL to Populate the Payables Open Interface Tables
A. Example of a SQL program used to
populate an invoice in AP
Invoices Interface tables:
Invoices Interface tables:
DECLARE
p_invoice_id NUMBER;
i NUMBER;
p_invoice_id NUMBER;
i NUMBER;
BEGIN
-- Get invoice_id
--
select
AP_INVOICES_INTERFACE_S.nextval
into p_invoice_id
from dual;
into p_invoice_id
from dual;
-- Insert an
invoice header --
insert into
ap_invoices_interface
(invoice_id,
invoice_num,
invoice_type_lookup_code,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
source,
group_id)
VALUES
(p_invoice_id,
'testcase1',
'STANDARD',
2,
2,
10,
'USD',
'INVOICE GATEWAY',
'testcase1');
(invoice_id,
invoice_num,
invoice_type_lookup_code,
vendor_id,
vendor_site_id,
invoice_amount,
invoice_currency_code,
source,
group_id)
VALUES
(p_invoice_id,
'testcase1',
'STANDARD',
2,
2,
10,
'USD',
'INVOICE GATEWAY',
'testcase1');
-- Insert invoice
line --
for i in 1..10 loop
INSERT
into ap_invoice_lines_interface
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
dist_code_combination_id)
VALUES
(p_invoice_id,
AP_INVOICE_LINES_INTERFACE_S.nextval,
i,
'ITEM',
1,
12831);
(invoice_id,
invoice_line_id,
line_number,
line_type_lookup_code,
amount,
dist_code_combination_id)
VALUES
(p_invoice_id,
AP_INVOICE_LINES_INTERFACE_S.nextval,
i,
'ITEM',
1,
12831);
end loop;
commit;
END;
/
/
1. Simple Invoice:
Note: You should not populate any PO or PA related columns for a simple
invoice. Here are the tables and reference columns identified for use when
populating data for a simple invoice:
AP_INVOICES_INTERFACE TABLE
============================
============================
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, you should only
populate one of these columns for vendor info for the data consistence)
VENDOR_SITE_ID or VENDOR_SITE_CODE
(An active pay site. Validated against PO_VENDOR_SITES, you should
only populate one of these columns for vendor site info for data consistency)
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')
==============
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, you should only
populate one of these columns for vendor info for the data consistence)
VENDOR_SITE_ID or VENDOR_SITE_CODE
(An active pay site. Validated against PO_VENDOR_SITES, you should
only populate one of these columns for vendor site info for data consistency)
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 –
If you do not populate them, then the value will be defaulted as
indicated:
=====================================================================
INVOICE_DATE
(Defaulted to SYSDATE)
INVOICE_TYPE_LOOKUP_CODE
(Defaulted to 'STANDARD')
INVOICE_CURRENCY_CODE
(Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE)
TERMS_ID or TERMS_NAME
(Defaulted from PO_VENDOR_SITES.TERMS_ID, if you populate it,
should populate one of them)
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_COMBINATION_ID)
GROUP_ID
(Group identifier. Suggested to use)
STATUS
(DO NOT POPULATE!!!)
EXCHANGE_RATE_TYPE
(Defaulted from AP_SYSTEM_PARAMETERS.DEFAULT_EXCHANGE_RATE_TYPE
if the invoice is using foreign currency)
=====================================================================
INVOICE_DATE
(Defaulted to SYSDATE)
INVOICE_TYPE_LOOKUP_CODE
(Defaulted to 'STANDARD')
INVOICE_CURRENCY_CODE
(Defaulted from PO_VENDOR_SITES.INVOICE_CURRENCY_CODE)
TERMS_ID or TERMS_NAME
(Defaulted from PO_VENDOR_SITES.TERMS_ID, if you populate it,
should populate one of them)
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_COMBINATION_ID)
GROUP_ID
(Group identifier. Suggested to use)
STATUS
(DO NOT POPULATE!!!)
EXCHANGE_RATE_TYPE
(Defaulted from AP_SYSTEM_PARAMETERS.DEFAULT_EXCHANGE_RATE_TYPE
if the invoice is using foreign currency)
AP_INVOICE_LINES_INTERFACE TABLE
==================================
==================================
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
================================================
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
(You should only populate one of them)
DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
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))
(Optional. Defaulted from INVOICE_DATE or SYSDATE)
DIST_CODE_CONCATENATED or DIST_CODE_COMBINATION_ID
(You should only populate one of them)
DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
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 /Optional
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, you should only populate one of them)
AMOUNT
=========================================================
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, you should only populate one of them)
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)
You should only populate one of them if you need to populate it yourself.
DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
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))
(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)
You should only populate one of them if you need to populate it yourself.
DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
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/Optional
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
============================================================
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)
You should only populate one of them if you need to populate it yourself.
DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
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))
(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)
You should only populate one of them if you need to populate it yourself.
DIST_CODE_CONCATENATED needs to meet security rules, no parent segment.
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))
2. PO Matched
Invoice:
You should not populate DIST_CODE_COMBINATION_ID, DIST_CODE_CONCATENATED
or any PA related column for PO matched invoices.
You should not populate DIST_CODE_COMBINATION_ID, DIST_CODE_CONCATENATED
or any PA related column for PO matched invoices.
There are two ways to match an invoice
to a Purchasing Order:
1. Matching to a PO shipment (MATCH_OPTION = ‘P’)
2. Matching to a PO receipt (MATCH_OPTION = ‘R’).
1. Matching to a PO shipment (MATCH_OPTION = ‘P’)
2. Matching to a PO receipt (MATCH_OPTION = ‘R’).
Here are the tables and reference
columns identified for use when populating data for a PO matched invoice:
AP_INVOICES_INTERFACE TABLE
=============================
=============================
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
==============
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
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.ORG_ID)
(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.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, should only populate one of them)
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. Suggested to use)
STATUS
(DO NOT POPULATE !!!!)
==============
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, should only populate one of them)
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. Suggested to use)
STATUS
(DO NOT POPULATE !!!!)
AP_INVOICE_LINES_INTERFACE
TABLE
===================================
===================================
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 unique number to the invoice)
LINE_TYPE_LOOKUP_CODE
('ITEM')
AMOUNT
(Should be QUANTITY_INVOICED * UNIT_PRICE)
=====================================
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
(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)
PO_NUMBER or PO_HEADER_ID
(Validated against PO_HEADERS, should only populate one of them.)
PO_LINE_NUMBER or PO_LINE_ID
(Validated against PO_LINES, should only populate one of them.)
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID
(Validated against PO_LINE_LOCATIONS, should only populate one of them.)
===============================
RELEASE_NUM or PO_RELEASE_ID
(For Blanket Release only, validated against PO_RELEASES)
PO_NUMBER or PO_HEADER_ID
(Validated against PO_HEADERS, should only populate one of them.)
PO_LINE_NUMBER or PO_LINE_ID
(Validated against PO_LINES, should only populate one of them.)
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID
(Validated against PO_LINE_LOCATIONS, should only populate one of them.)
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, should only populate one of them)
================================
RECEIPT_NUMBER
(Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM)
RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID
(Validated against RCV_TRANSACTIONS, should only populate one of them)
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)
=====================================
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)
3. Project Related Invoices
You should not populate DIST_CODE_COMBINATION_ID,
DIST_CODE_CONCATENATED or any PO related column for project related invoices.
You need to populate
PROJECT_ID
TASK_ID
EXPENDITURE_TYPE
EXPENDITURE_ITEM_DATE
EXPENDITURE_ORGANIZATION_ID
PA_QUANTITY
PROJECT_ID
TASK_ID
EXPENDITURE_TYPE
EXPENDITURE_ITEM_DATE
EXPENDITURE_ORGANIZATION_ID
PA_QUANTITY
Payables Open Invoice Import will pass
this project information data into the Project Account Generator
to generate an account for you. Here are the reference columns which are required for
populating a project related invoice:
to generate an account for you. Here are the reference columns which are required for
populating a project related invoice:
AP_INVOICES_INTERFACE TABLE
============================
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, should only populate one of them)
VENDOR_SITE_ID or VENDOR_SITE_CODE
(An active pay site. Validated against PO_VENDOR_SITES, should only populate one of them)
INVOICE_AMOUNT
(Positive amount)
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')
============================
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, should only populate one of them)
VENDOR_SITE_ID or VENDOR_SITE_CODE
(An active pay site. Validated against PO_VENDOR_SITES, should only populate one of them)
INVOICE_AMOUNT
(Positive amount)
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')
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 !!!)
================
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 !!!)
AP_INVOICE_LINES_INTERFACE
TABLE
===================================
===================================
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.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
===============================
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.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)
==========================================
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