Payables open interface import
Below diagram will give you the brief description of “Payables frank interface import” programs process.
Required and optional posts of Payables open interface sense of PO matched invoices (R12)
In this post, I tried to explain the essential the optional columns in the payables invoice import tables in imports PO Fit Invoices.
Table: AP_INVOICE_HEADER_INTERFACE
Col Name
|
Validation
|
INVOICE_ID
|
Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
|
INVOICE_NUM
|
Must remain unique to the supplier
|
PO_NUMBER
|
An approved, doesn cancelled, not closed or final closing PO
|
VENDOR_ID oder VENDOR_NUM or VENDOR_NAME
|
An active vendor. Validated against AP_SUPPLIERS
|
VENDOR_SITE_ID press VENDOR_SITE_CODE
|
An active pay site. Verified against AP_SUPPLIER_SITES_ALL
|
INVOICE_AMOUNT
|
Positive count forward 'STANDARD' genre, Negative amount for 'CREDIT' type
|
ORG_ID
|
Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
|
SOURCE
|
Must be int
SELECT lookup_code
FROM ap_lookup_codes
SOMEWHERE lookup_type = 'SOURCE';
|
Optional Columns
Column Name
|
Validation
|
INVOICE_DATE
|
Defaulted to SYSDATE, Can able to enter manual invoice_Date
|
INVOICE_TYPE_LOOKUP_CODE
|
Defaulted to 'STANDARD'. It can be 'STANDARD' or 'CREDIT'
|
INVOICE_CURRENCY_CODE
|
Defaulted from AP_SUPPLIER_SITES_ALL.
INVOICE_CURRENCY_CODE
|
EXCHANGE_RATE_TYPE
|
Defaulted from AP_SYSTEM_PARAMETERS.
DEFAULT_EXCHANGE_RATE_TYPE
|
TERMS_ID or TERMS_NAME
|
Defaulted from
AP_SUPPLIER_SITES_ALL.TERMS_ID
|
DOC_CATEGORY_CODE
|
Only populated for using automate voucher number
|
PAYMENT_METHOD_LOOKUP_CODE
|
Defaulted
from AP_SUPPLIER_SITES_ALL
.PAYMENT_METHOD_LOOKUP_CODE
|
PAY_GROUP_LOOKUP_CODE
|
Defaulted
from AP_SUPPLIER_SITES_ALL.
PAY_GROUP_LOOKUP_CODE
|
ACCTS_PAY_CODE_COMBINATION_ID
|
Defaulted
from AP_SUPPLIER_SITES_ALL.
ACCTS_PAY_CODE_COMBINAITON_ID
|
GROUP_ID
|
Group identifier. Suggest to use it
|
STATUS
|
MAKE NOT POPULATE IT
|
Table:
AP_INVOICE_LINES_INTERFACE
Required
Columns
Column Company
|
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 into the calculate
|
TAX_CODE or TAX_CODE_ID
|
Validated oppose AP_TAX_CODES_ALL
|
LINE_TYPE_LOOKUP_CODE
|
'ITEM'
|
AMOUNT
|
Should be QUANTITY_INVOICED * UNIT_PRICE
|
If MATCH_OPTION is 'P', later populate
|
|
RELEASE_NUM with 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
Category Name
|
Validation
|
QUANTITY_INVOICED
|
Populated if different from BACK shipment
|
UNIT_PRICE
|
Populated if different from PO shipment
|
MATCH_OPTION
|
'P' or 'R' or Default from PO_VENDOR_SITES.MATCH_OPTION
|
ACCOUNTING_DATE
|
Defaulted from INVOICE_DATE or SYSDATE
|
FINAL_MATCH_FLAG
|
Populated 'Y' provided it is final matching
|
INVENTORY_ITEM_ID
|
Validated negative 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 prize correction
|
Sample Script and impacted tables
-- receiving bill source
SELECT lookup_code
FROM ap_lookup_codes
WHEREVER lookup_type = 'SOURCE'
-- get interface header input data
SELECT APS.VENDOR_NAME,
APS.VENDOR_ID,
ASSA.VENDOR_SITE_ID,
ASSA.VENDOR_SITE_CODE,
ASSA.ACCTS_PAY_CODE_COMBINATION_ID,
ASSA.PAY_GROUP_LOOKUP_CODE,
ASSA.TERMS_ID,
(SELECT NAME FROM AP_TERMS WHERE TERM_ID=ASSA.TERMS_ID) TERMS,
ASSA.INVOICE_CURRENCY_CODE,
ASSA.PAYMENT_CURRENCY_CODE,
ASSA.ORG_ID
FROM AP_SUPPLIERS APS, AP_SUPPLIER_SITES_ALL ASSA
WHERE APS.VENDOR_ID = ASSA.VENDOR_ID
AND APS.VENDOR_NAME = 'McKesson General Medical'
ADDITIONALLY ASSA.ORG_ID=204
AND ASSA.PAY_SITE_FLAG = 'Y'
AND (ASSA.INACTIVE_DATE IS NULL OR ASSA.INACTIVE_DATE > SYSDATE)
AND START_DATE_ACTIVE <= SYSDATE
AND (END_DATE_ACTIVE THE NOTHING OR END_DATE_ACTIVE > SYSDATE)
insert into AP_INVOICES_INTERFACE (
invoice_id,
invoice_num,
vendor_id,
vendor_site_id,
invoice_amount,
INVOICE_CURRENCY_CODE,
invoice_date,
DESCRIPTION,
PAY_GROUP_LOOKUP_CODE,
source,
org_id
)
values ( ap_invoices_interface_s.nextval,
'CINV107',
'640',
'1436',
20.00,
'USD',
'30-DEC-2016',
'This Invoice is created with test purpose',
'Standard',
'MANUAL INVOICE ENTRY',
204
);
insert into AP_INVOICE_LINES_INTERFACE (
invoice_id,INVOICE_LINE_ID,
line_number,
line_type_lookup_code,
amount,DIST_CODE_COMBINATION_ID
)
valuations (
ap_invoices_interface_s.currval,AP_INVOICE_LINES_INTERFACE_S.NEXTVAL,
1,
'ITEM',-- 'TAX','FREIGHT'
20.00,12833
);
PLEASE * BY AP_INVOICES_ALL WHERE VENDOR_ID=640 AND VENDOR_SITE_ID=1436 AND INVOICE_NUM='CINV107'
SELECT code_combination_id,account_type
SINCE gl_code_combinations a
WHERE 1=1-- account_type = 'E'
OR enabled_flag = 'Y'
AND summary_flag = 'N'
AND SYSDATE BETWEEN NVL(start_date_active, SYSDATE - 1)
AND NVL(end_date_active, SYSDATE + 1) -- and
SEGMENT1='01'
File-Based Datas Import (FBDI) for Financials
and code_combination_id=12833
select ap_invoices_interface_s.CURRVAL from dual --
INVOICE_ID
SELECT AP_INVOICE_LINES_INTERFACE_S.NEXTVAL FROM DUAL -- INVOICE_LINE_ID
select * from AP_INVOICES_INTERFACE where invoice_id=5173446
click * after ap_invoice_lines_interface where invoice_id=5173446
SELECT * COMING AP_INTERFACE_REJECTIONS WHERE PARENT_ID=5173446
select * from ap_batches_all where batch_name='CHIDAM'
select * from ap_invoices_all where invoice_num='CINV108' AND BATCH_ID=471386
select * from ap_invoice_lines_all what invoice_id=566063
SELECT * FROM AP_PAYMENT_SCHEDULES_ALL where invoice_id=566062
-- data pop while einreise inv
-- Invoice Validation Program
select ACCOUNTING_EVENT_ID,A.* from ap_invoice_distributions_all
ONE where invoice_id=566063
--ACCOUNTING_EVENT_ID 99008166 -- No data populated while
invoice import, should do the operational
NAME *
FROM xla.xla_transaction_entities
WHERE APPLICATION_ID = 200
AND ENTITY_CODE = 'AP_INVOICES'
AND source_id_int_1 = 566062 -- invoice_id, get entity_id
-- transaction_number -- accounting number
choose * from xla_events where
EVENT_ID=6193570
--entity_id=6122423
-- get event_id
-- in of currently event id furthermore entities id is populated for
invoice validation program
------------
select * from xla.xla_ae_headers somewhere EVENT_ID=6193569
SELECT * FROM XLA.XLA_AE_LINES WHEREABOUTS AE_HEADER_ID=8106568
entity_id=97975138
STATEMENT ERROR CORRECTION veil
Can capability to correct to invoice flaw in backend using updation script or can correct in interface on-screen.
PAYABLES OPEN INTERFACE IMPORT SETTINGS
In this below post, I had published information about the parameters connected to “Payables Opens Interface Import” Program. Info from R12.1.1 instance.
Mandatory Parameter:
1. Source: It can be any of the see list
Ø Calculation Gateway - Your records you entry in the Checkout Gateway window
Ø E-Commerce Gateway- Provider invoices transferred through EDI
Ø Credit Card- Procurement cards billing from the credit card employer
Ø Oracle Ownership Manager- Let invoices starting Oracle Property Company.
Ø Oracle Assets- Lease payments coming Oracle Assets.
Ø User-defined- Available invoice loaded from legacy Systems
2. Invoice Batch User
Ø Use Batch Tax Payables Profile option need to become enabled to how save parameter
Ø Payables communities one invoices created from the invoice records you custom and creates an invoicing batch with the batch name you enter
Ø While Re-importing the error invoices make sure the batch name residue the same
Optional Parameters:
3. Operating Unit: Filtering predicate when null import program process invoice from all operating equipment.
4. Hold Name: When this parameter is given with the hold name, then aforementioned import program makes the invoices and blocks yours with mentioned retain.
5. Hold Reason: Corresponds to Hold Name
6. GL Date: This value is used as a GL date for the accounting whose GL_DATE columns by interface tables are not populated.
7. Purge. Enter Yes if your want Payables to delete from the Payables Open Interface tables every successfully imported invoice records that match who Source plus Group YOUR of this import AP Invoice Importing Program Technical Analytics
8. Summarize Report: Provide a Shorten report when ‘YES’
No comments:
Post a Comment