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 (1)

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 (2)

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

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

AP Interface (2)

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.

AR Interfaces (3)

1. Customer Interface

Stardard 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

GL Interfaces (2)

1. Journal Interface (GL Interface):
Stardard import concurrent program: Jounral Import

Interface table:GL_interface
Base table:
GL_JE_BATCHES
GL_JE_HEADERSGL_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

Account Receivables 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

Account payables 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 setupsVendors
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 termsPayment 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

General ledger 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)