Oracle Applications Interface Programs

Oracle Applications Interface Programs

1) Order Import Interface
2) Item Import (Item conversion)
3) On-hand Quantity
4) Customer Conversion
5) Customer API
6) Auto Invoice Interface
7) Receipt API
8) Auto Lockbox Interface
9) AP Invoice Interface
10)Vendor Interface/Conversion
11)Requisition Import
12)PO receipts interface
13)GL Interface
14)GL Budget Interface
15)GL Daily conversion rates

The Information for all the above mentioned Interface programs is given below:

Order Import Interface (Sales Order Conversion)

Interface tables:
· OE_HEADERS_IFACE_ALL
· OE_LINES_IFACE_ALL
· OE_ACTIONS_IFACE_ALL
· OE_ORDER_SOURCES
· OE_CUSTOMER_INFO_IFACE_ALL
· OE_PRICE_ADJS_IFACE_ALL
· OE_PRICE_ATTS_IFACE_ALL

Base tables:
· OE_ORDER_HEADERS_ALL
· OE_ORDER_LINES_ALL
Pricing tables
· QP_PRICING_ATTRIBUTES

During import of orders, shipping tables are not populated.

If importing customers together with the order, OE_ORDER_CUST_IFACE_ALL
Base Tables: HZ_PARTIES HZ_LOCATIONS

Orders can be categorized based on their status:
1. Entered orders 2. Booked orders 3. Closed orders
Concurrent Program: Order Import

Validations:
· Check for sold_to_org_id. If does not exist, create new customer by calling create_new_cust_info API.
· Check for sales_rep_id. Should exist for a booked order.
· Ordered_date should exist. -------- header level
· Delivery_lead_time should exist. ----------- line level
· Earliest_acceptable_date should exist.
· Freight_terms should exist

Order Import API OE_ORDER_PUB.GET_ORDER, PROCESS_ORDER
Concurrent programs will in turn call APIs.

2 APIs are called during order import process.
· OE_CNCL_ORDER_IMPORT_PVT (cancelled orders)
· ORDER_IMPORT_PRIVATE
Procedure: import_order()

On-hand quantity Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTION_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

The Transaction Manager picks up the rows to process based on the LOCK_FLAG, TRANSACTION_MODE, PROCESS_FLAG to manipulate the records in the table. Only records with TRANSACTION_MODE of 3, LOCK_FLAG of '2', and PROCESS_FLAG of '1' will be picked up by the Transaction Manager and assigned to a Transaction Worker. If a record fails to process completely, then PROCESS_FLAG will be set to '3' and ERROR_CODE and ERROR_EXPLANATION will be populated with the cause for the error.

Base tables: MTL_ON_HAND_QUANTITIES
MTL_LOT_NUMBERS MTL_SERIAL_NUMBERS

Concurrent program:

Validations: validate organization_id, organization_code.
Validate inventory item id.
Transaction period must be open.

Customer conversion Interface tables:
RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMER_PROFILES_INT_ALL
RA_CONTACT_PHONES_INT_ALL
RA_CUSTOMER_BANKS_INT_ALL
RA_CUST_PAY_METHOD_INT_ALL

Base tables: HZ_PARTIES
HZ_CONTACTS
HZ_PROFILES
HZ_LOCATIONS
Base tables for RA_CUSTOMERS_INTERFACE_ALL
RA_CUSTOMERS
RA_ADDRESSES_ALL
RA_CUSTOMER_RELATIONSHIPS_ALL
RA_SITE_USES_ALL

Uses TCA APIs.

Concurrent program: Customer Interface

Validations:
Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

Customer API
1. Set the organization id
Exec dbms_application_info.set_client_info(‘204’);

2. Create a party and an account
HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT()
HZ_CUST_ACCOUNT_V2PUB.CUST_ACCOUNT_REC_TYPE
HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

3. Create a physical location
HZ_LOCATION_V2PUB.CREATE_LOCATION()
HZ_LOCATION_V2PUB.LOCATION_REC_TYPE

4. Create a party site using party_id you get from step 2 and location_id from step 3.
HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE()
HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE

5. Create an account site using account_id you get from step 2 and party_site_id from step 4.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_ACCT_SITE_REC_TYPE

6. Create an account site use using cust_acct_site_id you get from step 5 ans site_use_code = ‘BILL_TO’.
HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE()
HZ_CUST_ACCOUNT_SITE_V2PUB.CUST_SITE_USE_REC_TYPE
HZ_CUSTOMER_PROFILE_V2PUB.CUSTOMER_PROFILE_REC_TYPE

Base table:
· HZ_PARTIES
· HZ_PARTY_SITES
· HZ_LOCATIONS
· HZ_CUST_ACCOUNTS
· HZ_CUST_SITE_USES_ALL
· HZ_CUST_ACCT_SITES_ALL
· HZ_PARTY_SITE_USES

Validations: Check if legacy values fetched are valid.
Check if customer address site is already created.
Check if customer site use is already created.
Check is customer header is already created.
Check whether the ship_to_site has associated bill_to_site
Check whether associated bill_to_site is created or not.
Profile amounts validation: validate cust_account_id, validate customer status.
Check if the location already exists in HZ_LOCATIONS. If does not exist, create new location.

Auto Invoice interface Interface tables: RA_INTERFACE_LINES_ALL

Base tables:
RA_CUSTOMER_TRX_ALL RA_BATCHES RA_CUSTOMER_TRX_LINES_ALL AR_PAYMENT_SCHEDULES_ALL RA_CUSTOMER_TRX_LINE_SALESREPS RA_CUST_TRX_GL_DIST_ALL AR_RECEIVABLES_APPLICATIONS AR_ADJUSTMENTS AR_CASH_RECEIPTS RA_CUSTOMER_TRX_TYPES_ALL
Concurrent Program: Auto invoice master program

Validations: check for amount, batch source name, conversion rate, conversion type.
Validate orig_system_bill_customer_id, orig_system_bill_address_id, quantity.
Validate if the amount includes tax flag.

Receipt APIAR_RECEIPT_API_PUB.CREATE_CASH
AR_RECEIPT_API_PUB.CREATE_AND_APPLY

To bring in Unapplied Receipts and Conversion Receipts for Open Debit items to reduce the balance to the original amount due.

Base tables: AR_CASH_RECEIPTS

Validations: check the currency and the exchange rate type to assign the exchange rate.
Validate bill to the customer.
Get bill to site use id.
Get the customer trx id for this particular transaction number.
Get payment schedule date for the customer trx id.

Lockbox interface Interface tables: AR_PAYMENTS_INTERFACE_ALL (Import data from bank file )

Base tables: AR_INTERIM_CASH_RECEIPTS_ALL AR_INTERIM_CASH_RCPT_LINES_ALL (Validate data in interface table and place in quick cash tables)

Related Tables: AR_BANK_ACCOUNTS_ALL AR_RECEIPT_METHODS
AR_TRANSMISSIONS_ALL HZ_CUST_ACCOUNTS HZ_CUST_SITE_USES_ALL AR_CASH_RECEIPTS
(POST QUICK CASH -- applies the receipts and updates customer balances)
Concurrent program: nav-> receivables->interfaces->lockbox

Validations: check for valid record type, transmission record id.
Validate sum of the payments within the transmission.
Identify the lockbox number (no given by a bank to identify a lockbox).

AP invoice interface Interface tables: 

AP_INVOICES_INTERFACE 

AP_INVOICE_LINES_INTERFACE

Base tables: AP_INVOICES_ALL – header information
AP_INVOICE_DISTRIBUTIONS_ALL – lines info

Concurrent program: Payables Open Interface Import

Validations: check for valid vendor
Check for valid vendor site code.
Check if record already exists in payables interface table.

Vendor conversion/interface No interface tables

Base tables: PO_VENDORS

 PO_VENDOR_SITES_ALL

No concurrent program as data is directly populated into base tables.

Validations: check if a vendor already exists with the same name as the TIMSS customer
mail name.
Check if the proper site code and id exists based on the site code from TIMSS.
Check for uppercase value of the vendor name existed in Oracle and in TIMSS, vendor name is mixed case, a new Oracle vendor will not be created.

Purchasing:
Interface Tables Base Tables
PO_HEADERS_INTERFACE PO_HEADERS_ALL
PO_LINES_INTERFACE PO_LINES_ALL
PO_REQUISITIONS_INTERFACE_ALL PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL
PO_REQ_DIST_INTERFACE_ALL PO_REQ_DISTRIBUTIONS_ALL
PO_DISTRIBUTIONS_INTERFACE PO_DISTRIBUTIONS_ALL
PO_RESCHEDULE_INTERFACE PO_REQUISITION_LINES_ALL

Requisition import Interface tables:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL

Basetables: PO_REQUISITIONS_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTIONS_ALL

Concurrent program: REQUISITION IMPORT

Validations: check for interface transaction source code, requisition destination type.
Check for quantity ordered, authorization status type.

PO Receipts Interface
Interface tables:
· RCV_HEADERS_INTERFACE
· RCV_TRANSACTIONS_INTERFACE

Base tables:
· RCV_SHIPMENT_HEADERS
· RCV_SHIPMENT_LINES
· RCV_TRANSACTIONS

Concurrent program: RECEIVING OPEN INTERFACE

Error messages: 1. Run RECEIVING INTERFACE ERRORS REPORT
2. Look in PO_INTERFACE_ERRORS
Query to check interface errors: PO_INTERFACE_ERRORS .inteface_transaction_id =
RCV_HEADERS_INTERFACE.header_interface_id and processing_status_code in (‘error’ ,’print’)

Validations:

GL interface Interface tables:

 GL_INTERFACE

Base tables:
GL_JE_HEADERS GL_JE_LINES GL_JE_BACTHES
Concurrent Program: Journal Import
Journal Posting --- populates GL_BALANCES

Validations: check SOB, journal source name, journal category name, actual flag
A – actual amounts
B – budget amounts
E – encumbrance amount
If u enter E in the interface table, then enter appropriate encumbrance ID.
B – budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if valid code combination.
Check if ccid is enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.

Validations for the staging table:
Check if the input data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.

GL budget interface Interface tables:
GL_BUDGET_INTERFACE

Base tables:
GL_BUDGETS GL_BUDGET_ASSIGNMENTS GL_BUDGET_TYPES
Concurrent program: Budget Upload

Validations: Check Account combination is valid or not. You check this in GL_CODE_COMBINATIONS table.

GL daily conversion rates
Interface tables: GL_DAILY_RATES_INTERFACE

Base tables:
· GL_DAILY_RATES
· GL_DAILY_CONVERSION_TYPES