Oracle Interface Details

Sales Order Interface
Standard import Program: ORDER IMPORT Interface tables:
OE_HEADERS_IFACE_ALL
OE_LINES_IFACE_ALL
OE_ACTIONS_IFACE_ALL
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
QP_PRICING_ATTRIBUTES

If importing customers together with the order
OE_ORDER_COST_IFACE_ALL
Base tables:
HZ_PARTIES
HZ_LOCATIONS

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 be exist.------------header level
--Delivery_lead_time should exist. --------line level
--Earliest_acceptable_date should exist
--Freight_terms should exist.

PO Interface

Requisition Interface
Standard import program: Requisition import

Interface tables:
PO_REQUISITIONS_INTERFACE_ALL
PO_REQ_DIST_INTERFACE_ALL
PO_INTERFACE_ERRORS

Base tables:
PO_REQUISITION_HEADERS_ALL
PO_REQUISITION_LINES_ALL
PO_REQ_DISTRIBUTION_ALL

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

INV Interface

1.      Item Interface
Standard import program: Item Import Interface tables:

MTL_SYSTEM_ITEMS_INTERFACE
MTL_TRANSACTIONS_INTERFACE
MTL_ITEM_REVISION_INTERFACE
MTL_DEMAND_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE
MTL_CROSS_REFERENCES_INTERFACE
MTL_INTERFACE_ERRORS

Base tables:
MTL_SYSTEM_ITEMS_B
MTL_ITEM_REVISIONS_B
MTL_ITEM_CATEGORIES
MTL_CROSS_REFERENCES
MTL_ITEM_STATUS
MTL_ITEM_TEMPLATES
MTL_CATEGORY_SETS_BMTL_CATEGORIES_B

Validations:
-- check for valid item type
-- check for valid part_id/segment1 of the source table
-- Validate part_id/segment1 for master org
-- Validate and translate template id of the source table
-- Check for valid template id (attributes are already set for items default attributes for that template, i.e., purchasable, stockable, etc)
-- check for valid item status
-- Validate primary uom of the source table
-- Validate attribute values
-- Validate other UOMs of the source table.
-- Check for unique item type. Discard the item, if par t has non-unique item type.
-- check for description, inv_um_uniqueness
-- Validate organization id.

ERROR CHECKING:
--When importing multiple revisions, if one record for an item fails validation, all revisions for that item fail. Resolve failed rows by checking the MTL_INTERFACE_ERRORS table.
Select table_name, column_name, error_message, message_name
From mtl_interface_errors

2.      On hand quantity Interface
Standard import program:
Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTIONS_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

Base tables:
MTL_ONHAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS
Standard import program:
Interface tables:
MTL_TRANSACTIONS_INTERFACE
MTL_TRANSACTIONS_LOTS_INTERFACE
MTL_SERIAL_NUMBERS_INTERFACE

Base tables:
MTL_ONHAND_QUANTITIES
MTL_LOT_NUMBERS
MTL_SERIAL_NUMBERS

Validations:
--Validate organization_id, organization_code
-- Validate inventory_item_id
-- Transaction period must be open

AP interfaces

1. Invoice Interface
   Standard import program: Payables Open Interface Import
   
   Interface tables:
   AP_INVOICES_INTERFACE
   AP_INVOICE_LINES_INTERFACE
   
   Base tables:
   AP_INVOICES_ALL (header information)
   AP_INVOICE_DISTRIBUTIONS_ALL (line information)
   
   
   Validations:
   --check for valid vendor
   --check for valid vendor site code
   --check if record already exists in Payables interface table.
   
   2. Vendor/Supplier Interface
   Standard import program:
   Supplier Open Interface Import
   Supplier Sites Open Interface Import
   Supplier Site Contacts Open Interface Import
   
   Interface tables:
   AP_SUPPLIERS_INT
   AP_SUPPLIER_SITES_INT
   AP_SUP_SITE_CONTACT_INT
   
   Base tables:
   PO_VENDORS
   PO_VENDOR_SITES_ALL
   PO_VENDOR_CONTACTS
   
   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 exists in oracle and in TIMSS, vendor name is mixed case, a new Oracle vendor will not be created.

AP data Model

For preliminary understanding, we will break the Module functionality into following components:

1) AP setups
2) Invoice Workbench
3) Payments workbench
4) Invoice and Payments accounting
5) Interfaces
We will take each component one by One:

1) AP setups
Vendors
Vendors : PO_VENDORS
Vendors Sites : PO_VENDOR_SITES_ALL

Banks
Banks and bank branches : AP_BANK_BRANCHES
Bank accounts : AP_BANK_ACCOUNTS_ALL
Payable Documents : AP_CHECK_STOCKS_ALL

Pay terms
Payment Terms : AP_TERMS
Pay Term Lines : AP_TERM_LINES

Payables options
Payables options : AP_SYSTEM_PARAMETERS

Distribution sets
Distribution sets : AP_DISTRIBUTION_SETS_ALL
Distribution set lines : AP_DISTRIBUTION_SET_LINES_ALL

2) Invoice Workbench :
Invoice Batch : AP_BATCHES_ALL
Invoice Headers : AP_INVOICES_ALL
Invoice Distributions : AP_INVOICE_DISTRIBUTIONS_ALL
Invoice Scheduled Payments : AP_PAYMENT_SCHEDULES_ALL
Invoice Holds : AP_HOLDS_ALL

3) Payments Workbench :
Payment Batch Sets : AP_PBATCH_SETS_ALL
                                   AP_PBATCH_SET_LINES_ALL
Payments : AP_CHECKS_ALL
                   AP_INVOICE_PAYMENTS_ALL
Payment Distributions : AP_PAYMENT_DISTRIBUTIONS_ALL

4) Invoice and payments accounting :
Accounting Events : AP_ACCOUNTING_EVENTS_ALL
Accounting Headers : AP_AE_HEADERS_ALL
Accounting Lines : AP_AE_LINES_ALL

5) Interfaces
Invoice interface : AP_INVOICES_INTERFACE
                             AP_INVOICE_LINES_INTERFACE


Supplier/Vendor interface : AP_SUPPLIERS_INT
                                           AP_SUPPLIER_SITES_INT
                                           AP_SUP_SITE_CONTACT_INT

AR Interfaces

1. Customer Interface
   Standard import concurrent program: Customer Interface
   
   Interface table:
   RA_CUSTOMERS_INTERFACE_ALL
   RA_CUSTOEMR_PROFILES_INT_ALL
   RA_CONTACT_PHONES_INT_ALL
   RA_CUST_PAY_METHOD_INT_ALL
   RA_CUSTOMER_BANKS_INT_ALL
   
   Base table: skipped
   
   Validations:
   --check if legacy values fetched are valid
   -- Check if customer address site is already created
   -- Check if customer address site use is already created
   -- Check if customer header is already created
   -- Check if the ship_to_site has associated with bill_to_site
   -- Check if associated bill_to_site is created or not.
   --Validate profile amounts: validate cust_account_id, customer_status
   --Check if the location already exists in HZ_LOCATIONS. If not, create new location.
   
   2. Auto Invoice Interface
   Stardard import concurrent program:
   Auto Invoice Master Program
   Auto Invoice Import Program
   Auto Invoice Purge Program
   
   Interface tables:
   RA_INTERFACE_LINES_ALL
   RA_INTERFACE_DISTRIBUTIONS_ALL
   RA_INTERFACE_SALESCREDITS_ALL
   
   Base tables:
   RA_CUSTOMER_TRX_ALL
   RA_CUSTOMER_TRX_LINES_ALL
   RA_CUSTOMER_TRX_LINE_SALESREPS
   RA_CUSTOMER_TRX_TYPES_ALL
   RA_CUST_TRX_GL_DIST_ALL (connect to GL)
   RA_BATCHES
   AR_PAYMENT_SCHEDULES_ALL
   AR_RECEIVABLES_APPLICATIONS
   AR_CASH_RECEIPTSAR_ADJUSTMENTS
   
   3. Auto Lockbox Interface
   Standard Import Program: (N): Lockbox -> Submit Lockbox Processing
   
   Interface tables:
   AR_PAYMENTS_INTERFACE_ALL
   AR_INTERIM_CASH_RECEIPTS_ALL
   AR_INTERIM_CASH_RCPT_LINES_ALL
   
   Base tables:
   AR_CASH_RECEIPTS_ALL
   AR_RECEIVABLES_APPLICAITONS_ALL
   AR_PAYMENT_SCHEDULES_ALL
   AR_CASH_RECEIPT_HISTORY_ALL

AR Data Model

For preliminary understanding, we will break the Module functionality into following components:

1) AR setups
2) Transactions Workbench
3) Receipts workbench
4) Interfaces

We will take each component one by One:

1) AR setups :
System Options : AR_SYSTEM_PARAMETERS
Transaction Types : RA_CUST_TRX_TYPES_ALL
Transactions Sources : RA_BATCH_SOURCES_ALL
Banks : AP_BANK_BRANCHES
Bank accounts : AP_BANK_ACCOUNTS_ALL
(Customer/Internal)
Receipt Classes : AR_RECEIPT_CLASSES
Receipt Sources : AR_BATCH_SOURCES_ALL
Payment Methods : AR_RECEIPT_METHODS
Customers : HZ_CUST_ACCOUNTS
                    HZ_CUST_SITE_USES_ALL
                    HZ_CUST_PROFILE_AMTS
                    HZ_CUST_ACCOUNT_ROLES
                    HZ_PARTIES
                    HZ_PARTY_SITES
                    HZ_PARTY_SITE_USES

Customer profile class: HZ_CUST_PROFILE_CLASSES
                                      HZ_CUST_PROF_CLASS_AMTS
Dunning letters : AR_DUNNING_LETTERS_B
                           AR_DUNNING_LETTERS_TL
Dunning Letter Sets : AR_DUNNING_LETTER_SETS
                                  AR_DUNNING_LETTER_SET_LINES
Statement Cycles : AR_STATEMENT_CYCLES
                               AR_STATEMENT_CYCLE_DATES_ALL
Payment Terms : RA_TERMS
                            RA_TERMS_LINES
                            RA_TERMS_LINES_DISCOUNTS


2) Transactions Workbench:
Batch : RA_BATCHES_ALL
Headers : RA_CUSTOMER_TRX_ALL
Lines : RA_CUSTOMER_TRX_LINES_ALL
Sales credits : RA_CUST_TRX_LINE_SALESREPS_ALL
Distributions : RA_CUST_TRX_LINE_GL_DIST_ALL
Payment schedules : AR_PAYMENT_SCHEDULES_ALL

3) Receipts Workbench :
Batch : AR_BATCHES_ALL
Receipts : AR_CASH_RECEIPTS_ALL
                 AR_CASH_RECEIPT_HISTORY_ALL
Receipt Applications : AR_RECEIVABLE_APPLICATIONS_ALL

4) Interfaces :
Auto invoice : RA_INTERFACE_LINES_ALL
                       RA_INTERFACE_SALESCREDITS_ALL
                       RA_INTERFACE_DISTRIBUTIONS_ALL


Receipts/Lockbox : AR_PAYMENTS_INTERFACE_ALL
                               AR_INTERIM_CASH_RECEIPTS_ALL
                               AR_INTERIM_CASH_RCPT_LINES_ALL

Customers : RA_CUSTOMERS_INTERFACE_ALL
                    RA_CUSTOMER_PROFILES_INT_ALL
                    RA_CUST_PAY_METHOD_INT_ALL
                    RA_CUSTOMER_BANKS_INT_ALL
                    RA_CONTACT_PHONES_INT_ALL

Tax Locations and Rates: AR_TAX_INTERFACE

GL Interfaces

1. Journal Interface (GL Interface):
   Stardard import concurrent program: Jounral Import
   
   Interface table:
   GL_interface
   
   Base table:
   GL_JE_BATCHES
   GL_JE_HEADERS
   GL_JE_LINES
   
   Validations:
   --check SOB, journal source name, journal category name, actual flag
   A-actual amounts, B-budget amount, E-encumbrance amount
   if you enter E in the interface table, then enter appreciate encumbrance ID.
   if you enter B in the interface table, then enter appreciate budget ID.
   --Check if accounting data or GL data based period name is valid (i.e., not closed)
   --Check if accounting data falls in open or future open period status.
   --Check chart of account 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.
   
   2. Budget Interface :
   Stardard import concurrent program: Budget Upload
   
   Interface table:
   GL_BUDGET_INTERFACE
   
   Base table:
   GL_BUDGETS
   GL_BUDGET_ASSIGNMENTS
   GL_BUDGET_TYPES
   
   Columns:
   Budget_name, budget_entity_name, currency_code, fiscal_year, budget_entity_id, set_of_books_id, code_combination_id, period_type, dr_flag, period(n)_amount, segment(n), and who columns

GL data model

For Preliminary Understanding of the GL data model, we will split it into following components:

1) GL setups
2) Journals
3) Budgets
4) Interfaces

We will take each component one by One:

1) GL setups :
Period types : GL_PERIOD_TYPES
Accounting Calendar : GL_PERIOD_SETS, GL_PERIODS
Accounting ID : GL_CODE_COMBINATIONS
Chart of Accounts : FND_FLEX_VALUE_SETS
(Accounting Flex STR) FND_ID_FLEX_STRUCTURES
FND_ID_FLEX_SEGMENTS
FND_FLEX_VALUES

Set of Books : GL_SETS_OF_BOOKS
Period Statuses : GL_PERIOD_STATUSES
Currencies : FND_CURRENCIES
Currency Conversion Rates : GL_DAILY_RATES
Period Rates : GL_TRANSLATION_RATES

2) Journals (Actual / Budget/ Encumbrance) :
Journal Batch : GL_JE_BATCHES
Journal Header : GL_JE_HEADERS
Journal Lines : GL_JE_LINES
Journal Posting Interim Table : GL_POSTING_INTERIM
Balances : GL_BALANCES

3) Budgets :
Budget Definition : GL_BUDGETS
GL_BUDGET_VERSIONS
Budget Organizations : GL_BUDGET_ENTITIES
Budget Org Ranges : GL_BUDGET_ASSIGNMENT_RANGES
Budget Org Range Assignments: GL_BUDGET_ASSIGNMENTS
Budget Journals : GL_JE_BATCHES
GL_JE_HEADERS
GL_JE_LINES

4) Interfaces:
Journals Interface : GL_INTERFACE
(To Upload Journals from
Feeder and Legacy Systems)

Budgets Interface : GL_BUDGET_INTERFACE
(To upload Budget amounts)

Daily Rates Interface : GL_DAILY_RATES_INTERFACE
(To Upload Conversion rates)