interfaces

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_ALLPay 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_ALL3) 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_LINES4) 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)

Interfaces

Interfaces:
   -------------


Interface is one of the Program which will be used to transfer the data from Oracle
database tables in to flat file   (or)
Flat files      into  Database tables

We have two types of Interfaces.

1)Inbound Interface
2)outbound Interface

Outbound Interface will be used to extract the data from oracle Database tables into 
the flat files.

Inbound Interface will be used to upload the data from legacy system (Flat files) into
Oracle Applications base tables.

While developing the outbound Interface we will use UTL_File to Extract the data.
While Developing the Inbound interface  we will use SQL * loader to import the data 
into base taqbles.

Converstion Vs Interfaces

Interfaces:
It is schedule concurrent Process which will be executed multiple times
We will not be knowing flat file Volume
We need to handle all the expected exceptions
Error Reports and sending mail alerts, If any error occurs
Only in Enhancement or customization projects

Conversion:
One time data transfer
We will be know exact falt file Volume
No need to know the exceptions
Not required to upload all records
Only in Implementation,Migration or up-gradation projects

UTL_FILE Package :
==================

this is One of the PL/SQL Package which will be used to transfer the data
from table to files
from files to tables

But when we are working for file to table we will use SQl *Loader
to transfer from table to file we have no alternative we have to use UTL_FILE.

We will use following three functions to generate the file.

1)Utl_File.fopen     = To open (or) Create the file
2)Utl_File.Put_line  = To Transfer the data into the File.
3)Utl_File.fclose    = To close the File after Data transfer.



outbound Interface Process:
=============================

1)Develop the PL/SQL Program (Either Procedure or Package)
2)Write the Cursor to retrieve the data from database tables.
3)Create file or Open the File by using UTL_File.fopen().
4)Open the Cursor
5)If any validations are there write the validations
6)Transfer the Data into File by using UTL_File.Put_Line().
7)Close the Cursor.
8)Close the File by using UTL_File.fclose();
9)Register the Procedure or Package as Concurrent Program and submit from SRS Window.



For Ex : We required the flat file with following Format.

Supplierno,Supliername,Cdate,Sitename


Develop the Program to Transfer Supplier Purchase orders datainto file


Supplier No from
Supplier No To  

PoNumber,POtype,Amount,Cdate,No,Name,Site


Validations:

1)If Parameters are null select all the supplier details
2)If 'from' is given  'To' is not given take till Last Supplier
3)If 'To' is given and 'from' is not given take from first.
4)If Purchase order amount is more then 3500 then only transfer into file.