Oracle Applications Interface Programs
1) Order Import Interface2) Item Import (Item conversion)3) On-hand Quantity4) Customer Conversion
5) Customer API6) Auto Invoice Interface7) Receipt API8) Auto Lockbox Interface9) AP Invoice Interface10)Vendor Interface/Conversion11)Requisition Import12)PO receipts interface13)GL Interface14)GL Budget Interface15)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 conversionInterface 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 interfaceInterface 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
5) Customer API6) Auto Invoice Interface7) Receipt API8) Auto Lockbox Interface9) AP Invoice Interface10)Vendor Interface/Conversion11)Requisition Import12)PO receipts interface13)GL Interface14)GL Budget Interface15)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 conversionInterface 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 interfaceInterface 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
Item import (Item conversion)
Interface tables:
· MTL_SYSTEM_ITEMS_INTERFACE
· MTL_ITEM_REVISIONS_INTERFACE( If importing revisions, populate)
· MTL_ITEM_CATEGORIES_INTERFACE(If importing categories, populate)
· MTL_INTERFACE_ERRORS
Item import can be run in create mode or update mode.
Running the Item Open Interface In Create Mode:
Populate the mtl_system_items_interface with the following minimum required columns when creating new items:
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
ORGANIZATION_ID = Master Org id.
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n)
Note: Using the ITEM_NUMBER column in the mtl_system_items_interface is required if you are populating revision history data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both.
Running the Item Open Interface In Update Mode:
To update existing item(s), set TRANSACTION_TYPE = 'UPDATE'.
For best performance, use inventory_item_id when updating items.
Functionality
Every attribute updateable from the Item form is updateable through the interface, and all required validations are performed to enforce:
· Item Attribute Interdependencies
· Master - Child Attribute Dependencies
· Status Controlled Attributes
· Templates can be applied to existing Items. For best results, use template_id and template_name.
· The Item status can be changed for existing Items, and the proper attributes are Defaulted / Set accordingly. The Status change is recorded in
MTL_PENDING_ITEM_STATUS.
· To populate material costs from IOI: Populate the LIST_PRICE_PER_UNIT column with a value while importing items and you will see your material cost for your item in MTL_SYSTEM_ITEMS after running the Item Import process. (CREATE transaction_type only)
· When launching items into the Master Item Org, the Child records are copied into MTL_SYSTEM_ITEMS_INTERFACE for validation, and are identified with transaction_type of 'AUTO_CHILD'. These records are deleted if the parameter 'Delete Processed Rows' has been passed as 'Yes', and remain for diagnostic purposes if the parameter is passed as 'No'. When the defining attribute for a Functional area is enabled, the proper default category set and category is assigned to the Item.
· Master Items were loaded before child records in MTL_SYSTEM_ITEMS.
Not Supported Issues
=========================================
· Item Costs cannot be UPDATED (using "UPDATE" transaction_type) through the interface.
· New Item revisions cannot be added to existing Items.
· Current functionality does not support updates to a PTO MODEL ITEM through
the IOI update feature. See notes: 1076412.6 and 2121870.6 Updating Item Attributes to NULL The method to update these columns to NULL is to use the following values:
1. for Numeric fields: insert -999999
2. for Character fields: insert '!'
3. for Date fields: the above list does not include any updateable date fields.
Importing Master and Child Records
==================================
The user procedures are as follows :
1. Populate the item interface tables (mtl_system_items_interface). This step is necessary if you are creating items and categories in the same run. For importing item category assignments for already existing items, you do not need to populate item interface table.
2. Populate the item categories interface table (mtl_item_categories_interface).
The user needs to populate the following mandatory columns in item categories interface table:
A. Either inventory_item_id or item_number. When item and category are being imported together, then user can only specify the item_number, since item id will be generated by the import process.
B. Either organization_id or organization_code or both.
C. The transaction_type column should be 'CREATE'. We do not support 'UPDATE' or 'DELETE' for item category assignment.
D. Either category_set_id or category_set_name or both.
E. Either category_id or category_name or both.
F. Process_flag column as 1.
G. Populate the set_process_id column. The item and category interface records should have the same set_process_id, if you are importing item and category assignment together.
3. After populating the item and category interface tables, launch the Item Import process from the applications. In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have have any value. Through the import process, we can only create item category assignment(s).
Updation or Deletion of item category assignment is not supported.
4. Once the concurrent process completes, check the mtl_interface_errors table for any error(s) during the item and category import. Correct those error conditions in the interface tables and run the item import again. If the process_flag is 7, that means the item category interface records were successfully imported.
Revisions
==============================
Note: Using the ITEM_NUMBER column in the mtl_system_items_interface table is required if you are populating revision data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported, plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both. For historical item revision data, do NOT populate the REVISION column in the mtl_system_items_interface table. This column is used only if the current revision of the item is being imported.
Populate these columns in the mtl_item_revisions_interface table:
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
ORGANIZATION_ID = Master Org ID.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = (Must match the item_number in mtl_system_items_interface table.)
Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.
Run the IOI process. Navigate --> Inventory: Items: Import Items
There are 6 parameters to enter to begin the process:
1. Specify one or all organizations.
2. Validate items, yes or no.
3. Process items, yes or no.
4. Delete processed rows, yes or no.
5. Process set (null for all)
6. Create or update items (1 for create, 2 for update)
Note: If you are importing Master and Child records, insert them into the mtl_system_items_interface and mtl_item_revisions_interface tables, and run them at the same time by setting the 'All organizations' parameter to 'Yes'. If you do not do this, then the Child revision records will not be imported.
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;
Base tables:
§ MTL_SYSTEM_ITEMS_B
§ MTL_ITEM_REVISIONS_B
§ MTL_CATEGORIES_B
§ MTL_CATEGORY_SETS_B
§ MTL_ITEM_STATUS
§ MTL_ITEM_TEMPLATES
Concurrent program: Item Import
Validations: check for valid item type.
Check for valid part_id/segment 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 part has non-unique item type.
Check for description, inv_um uniqueness
Validate organization id.
Load master records and category records only if all validations are passed.
Load child record if no error found.
Interface Tables Base Tables
MTL_SYSTEM_ITEMS_INTERFACE MTL_SYSTEM_ITEMS
MTL_TRANSACTIONS_INTERFACE
MTL_ITEM_REVISION_INTERFACE MTL_ITEM_REVISIONS
MTL_DEMAND_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE MTL_ITEM_CATEGORIES
MTL_CROSS_REFERENCES_INTERFACE MTL_CROSS_REFERENCES
· MTL_SYSTEM_ITEMS_INTERFACE
· MTL_ITEM_REVISIONS_INTERFACE( If importing revisions, populate)
· MTL_ITEM_CATEGORIES_INTERFACE(If importing categories, populate)
· MTL_INTERFACE_ERRORS
Item import can be run in create mode or update mode.
Running the Item Open Interface In Create Mode:
Populate the mtl_system_items_interface with the following minimum required columns when creating new items:
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
ORGANIZATION_ID = Master Org id.
DESCRIPTION = 'Description of the item'
ITEM_NUMBER and/or SEGMENT(n)
Note: Using the ITEM_NUMBER column in the mtl_system_items_interface is required if you are populating revision history data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both.
Running the Item Open Interface In Update Mode:
To update existing item(s), set TRANSACTION_TYPE = 'UPDATE'.
For best performance, use inventory_item_id when updating items.
Functionality
Every attribute updateable from the Item form is updateable through the interface, and all required validations are performed to enforce:
· Item Attribute Interdependencies
· Master - Child Attribute Dependencies
· Status Controlled Attributes
· Templates can be applied to existing Items. For best results, use template_id and template_name.
· The Item status can be changed for existing Items, and the proper attributes are Defaulted / Set accordingly. The Status change is recorded in
MTL_PENDING_ITEM_STATUS.
· To populate material costs from IOI: Populate the LIST_PRICE_PER_UNIT column with a value while importing items and you will see your material cost for your item in MTL_SYSTEM_ITEMS after running the Item Import process. (CREATE transaction_type only)
· When launching items into the Master Item Org, the Child records are copied into MTL_SYSTEM_ITEMS_INTERFACE for validation, and are identified with transaction_type of 'AUTO_CHILD'. These records are deleted if the parameter 'Delete Processed Rows' has been passed as 'Yes', and remain for diagnostic purposes if the parameter is passed as 'No'. When the defining attribute for a Functional area is enabled, the proper default category set and category is assigned to the Item.
· Master Items were loaded before child records in MTL_SYSTEM_ITEMS.
Not Supported Issues
=========================================
· Item Costs cannot be UPDATED (using "UPDATE" transaction_type) through the interface.
· New Item revisions cannot be added to existing Items.
· Current functionality does not support updates to a PTO MODEL ITEM through
the IOI update feature. See notes: 1076412.6 and 2121870.6 Updating Item Attributes to NULL The method to update these columns to NULL is to use the following values:
1. for Numeric fields: insert -999999
2. for Character fields: insert '!'
3. for Date fields: the above list does not include any updateable date fields.
Importing Master and Child Records
==================================
The user procedures are as follows :
1. Populate the item interface tables (mtl_system_items_interface). This step is necessary if you are creating items and categories in the same run. For importing item category assignments for already existing items, you do not need to populate item interface table.
2. Populate the item categories interface table (mtl_item_categories_interface).
The user needs to populate the following mandatory columns in item categories interface table:
A. Either inventory_item_id or item_number. When item and category are being imported together, then user can only specify the item_number, since item id will be generated by the import process.
B. Either organization_id or organization_code or both.
C. The transaction_type column should be 'CREATE'. We do not support 'UPDATE' or 'DELETE' for item category assignment.
D. Either category_set_id or category_set_name or both.
E. Either category_id or category_name or both.
F. Process_flag column as 1.
G. Populate the set_process_id column. The item and category interface records should have the same set_process_id, if you are importing item and category assignment together.
3. After populating the item and category interface tables, launch the Item Import process from the applications. In the item import parameters form, for the parameter 'set process id', specify the 'set process id' value given in the mtl_item_categories_interface table. The parameter 'Create or Update' can have have any value. Through the import process, we can only create item category assignment(s).
Updation or Deletion of item category assignment is not supported.
4. Once the concurrent process completes, check the mtl_interface_errors table for any error(s) during the item and category import. Correct those error conditions in the interface tables and run the item import again. If the process_flag is 7, that means the item category interface records were successfully imported.
Revisions
==============================
Note: Using the ITEM_NUMBER column in the mtl_system_items_interface table is required if you are populating revision data into the mtl_item_revisions_interface table. The value of the ITEM_NUMBER must equal the concatenated segments(n) of the item being imported, plus the segment separator. If you are not importing revision history you can populate either ITEM_NUMBER or the SEGMENT(n) column(s) or both. For historical item revision data, do NOT populate the REVISION column in the mtl_system_items_interface table. This column is used only if the current revision of the item is being imported.
Populate these columns in the mtl_item_revisions_interface table:
PROCESS_FLAG = 1
TRANSACTION_TYPE = 'CREATE'
SET_PROCESS_ID = 1
ORGANIZATION_ID = Master Org ID.
REVISION
EFFECTIVITY_DATE
IMPLEMENTATION_DATE
ITEM_NUMBER = (Must match the item_number in mtl_system_items_interface table.)
Each row in the mtl_item_revisions_interface table must have the REVISION and EFFECTIVITY_DATE in alphabetical (ASCII sort) and chronological order.
Run the IOI process. Navigate --> Inventory: Items: Import Items
There are 6 parameters to enter to begin the process:
1. Specify one or all organizations.
2. Validate items, yes or no.
3. Process items, yes or no.
4. Delete processed rows, yes or no.
5. Process set (null for all)
6. Create or update items (1 for create, 2 for update)
Note: If you are importing Master and Child records, insert them into the mtl_system_items_interface and mtl_item_revisions_interface tables, and run them at the same time by setting the 'All organizations' parameter to 'Yes'. If you do not do this, then the Child revision records will not be imported.
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;
Base tables:
§ MTL_SYSTEM_ITEMS_B
§ MTL_ITEM_REVISIONS_B
§ MTL_CATEGORIES_B
§ MTL_CATEGORY_SETS_B
§ MTL_ITEM_STATUS
§ MTL_ITEM_TEMPLATES
Concurrent program: Item Import
Validations: check for valid item type.
Check for valid part_id/segment 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 part has non-unique item type.
Check for description, inv_um uniqueness
Validate organization id.
Load master records and category records only if all validations are passed.
Load child record if no error found.
Interface Tables Base Tables
MTL_SYSTEM_ITEMS_INTERFACE MTL_SYSTEM_ITEMS
MTL_TRANSACTIONS_INTERFACE
MTL_ITEM_REVISION_INTERFACE MTL_ITEM_REVISIONS
MTL_DEMAND_INTERFACE
MTL_ITEM_CATEGORIES_INTERFACE MTL_ITEM_CATEGORIES
MTL_CROSS_REFERENCES_INTERFACE MTL_CROSS_REFERENCES
Fundementals of APPS.
Following are the fundementals of APPS:
1.)Concurrent Program
2.)Concurrent Manager
3.)Value Set
4.)Look Up
Concurrent Program :
By using the Concurrent program, you can schedule your program to run. For example, your organization is entering the new data for every 24 hours and since this data is to be converted to Oracle ERP base tables, You can schedule the concuurent program to run for every 24 Hours.
Before creating the concurrent program you need to create EXECUTABLE for it.
what is Executable?
Executable is the means by which you specify the name of your code and the extension to it.
For example : You want to run the shell script which loads the legacy data into custom Staging table with the help of CTL file. Then you need to mention the following in the EXECUTABLE section.
Executable: XXX_DEMO_HELLOWORLD /* Name of the Executable */
Short Name: XXXDH
Application: XXGeneral Ledger /* Name of your Application */
Execution Method : Shell
Execution File Name : DEMO.sh
In the above discussion, Execution Method is the means of loading the data. Shell is the means for executing the shell script. If you want execute a pl/sql procedure, than the Execution Method will be 'PL/SQL Stored Procedure' etc....
Now while creating the concurrent program, you need to mention the name of your concurrent program and the short name for it, Application and in the Executable section of concurrent program mention the name of Executable which you have created i--e, XXX_DEMO_HELLOWORLD . After entering this name you can see that Executable Method will be displayed automatically as 'Shell'
Concurrent Manager:
Well, the Concurrent Manager's job is nothing but monitoring the concurrent programs. It's like a manager which manages the parallel execution of concurrent programs.
Say for example in your project there are 10 resources who are working on 10 Application Modules like INV,QA,PA etc...Each and every resource have to run their own concurrent programs. Now what happens if different resources are submitting their programs at once......they can't keep on waiting till other 9 resource programs gets completed. So the concurrent manager manages this issue and sees that all 10 concurrent programs runs parallely . This Background Process, Concurrent Manager will be running ideally 24x7.
Value Set:
Oracle Apps uses value set to validate that correct data is being entered in the fields in screen. While defining the parameters in the concurrent program you will give the value set for those parameters.
Now what's the hell with this parameters in the concurrent program?
Parameters will be defined in the concurrent proograms to simplify the process of execution of your code.
you can't hardcode some things in your custom PL/SQL program. so what you do is ,define that Hard coding part in concurrent program as 'Parameter'.
In simple of words, Value Set means group or set of values.
LOOKUP:
Lookup is nothing but set of codes and their meanings. The simplest example is take Railway Compartment berths.
LookUP Code Meaning
LB Lower Berth
MB Middle Berth
UB Upper Berth.
Let us consider that you have a table RAILWAY with columns as NAME, T_NAME, T_NUMBER,BERTH.
BERTH column will hold values of LB,MB,UB respectively.
Now dont get amused that LOOKUP's usage is limited to this only. Please Consider a scenario below.
Say suppose that you have 1 Lakh records. Now you want to change name of Lower Berth to Lowest Berth. You can't keep on update these 1Lakh records. so only thing you will be doing is just renaming it in the LOOKUP. previously for LB Lookup it's meaning is Lower Berth. Now just rename the meaning to Lowest Berth and continue to use the same LookUP code. Here BERTH is the Lookup Type and LB,MB,UB are Lookup Codes.
1.)Concurrent Program
2.)Concurrent Manager
3.)Value Set
4.)Look Up
Concurrent Program :
By using the Concurrent program, you can schedule your program to run. For example, your organization is entering the new data for every 24 hours and since this data is to be converted to Oracle ERP base tables, You can schedule the concuurent program to run for every 24 Hours.
Before creating the concurrent program you need to create EXECUTABLE for it.
what is Executable?
Executable is the means by which you specify the name of your code and the extension to it.
For example : You want to run the shell script which loads the legacy data into custom Staging table with the help of CTL file. Then you need to mention the following in the EXECUTABLE section.
Executable: XXX_DEMO_HELLOWORLD /* Name of the Executable */
Short Name: XXXDH
Application: XXGeneral Ledger /* Name of your Application */
Execution Method : Shell
Execution File Name : DEMO.sh
In the above discussion, Execution Method is the means of loading the data. Shell is the means for executing the shell script. If you want execute a pl/sql procedure, than the Execution Method will be 'PL/SQL Stored Procedure' etc....
Now while creating the concurrent program, you need to mention the name of your concurrent program and the short name for it, Application and in the Executable section of concurrent program mention the name of Executable which you have created i--e, XXX_DEMO_HELLOWORLD . After entering this name you can see that Executable Method will be displayed automatically as 'Shell'
Concurrent Manager:
Well, the Concurrent Manager's job is nothing but monitoring the concurrent programs. It's like a manager which manages the parallel execution of concurrent programs.
Say for example in your project there are 10 resources who are working on 10 Application Modules like INV,QA,PA etc...Each and every resource have to run their own concurrent programs. Now what happens if different resources are submitting their programs at once......they can't keep on waiting till other 9 resource programs gets completed. So the concurrent manager manages this issue and sees that all 10 concurrent programs runs parallely . This Background Process, Concurrent Manager will be running ideally 24x7.
Value Set:
Oracle Apps uses value set to validate that correct data is being entered in the fields in screen. While defining the parameters in the concurrent program you will give the value set for those parameters.
Now what's the hell with this parameters in the concurrent program?
Parameters will be defined in the concurrent proograms to simplify the process of execution of your code.
you can't hardcode some things in your custom PL/SQL program. so what you do is ,define that Hard coding part in concurrent program as 'Parameter'.
In simple of words, Value Set means group or set of values.
LOOKUP:
Lookup is nothing but set of codes and their meanings. The simplest example is take Railway Compartment berths.
LookUP Code Meaning
LB Lower Berth
MB Middle Berth
UB Upper Berth.
Let us consider that you have a table RAILWAY with columns as NAME, T_NAME, T_NUMBER,BERTH.
BERTH column will hold values of LB,MB,UB respectively.
Now dont get amused that LOOKUP's usage is limited to this only. Please Consider a scenario below.
Say suppose that you have 1 Lakh records. Now you want to change name of Lower Berth to Lowest Berth. You can't keep on update these 1Lakh records. so only thing you will be doing is just renaming it in the LOOKUP. previously for LB Lookup it's meaning is Lower Berth. Now just rename the meaning to Lowest Berth and continue to use the same LookUP code. Here BERTH is the Lookup Type and LB,MB,UB are Lookup Codes.
Why is the name 'APPS' given to Oracle Technologies?
Let's assume that you are working on Inventory and Purchasing Modules. You want to create Tables, Indexes, Sequences, Synonyms,package scripts etc......You can create everything in your custom schema i--e, XXXINVor XXXPO. But the recommended process is to create Tables, Indexes,Sequences in your custom schema i--e, XXXINV or XXXPO and create the synonyms of the same in APPS schema. and creating other scripts like package scripts in APPS schema .
The reason why you create Tables,Indexes,Sequences etc.. in Custom schema and package scripts in APPS schema is :::::: It's simple. Having all the scripts belonging to different modules in one schema. At the time of migration other than tables everything can be taken from this central schema (APPS) and can be migrated accordingly.
The tables are still owned by their respective schema, but now we have a central schema named APPS. Oracle ERP simply connects to APPS database schema for all its operations.
For Example: Your Project is having modules like INV,PO,PA,GL,AR,AP,QA etc.. then you have to maintain your database in such a way that all the tables and their related scripts are defined in their respective custom schemas and the synonym for the same is defined in APPS schema. Before creating the synonym, you need to give all GRANT permissions to the APPS schema from your custom schema. And create all the package scripts of INV,PO,PA,GL,ARA,AP,QA etc.. in APPS schema. So that at the time of migration it becomes easier to migrate all the stuff from one CENTAL schema............i..e, APPS.
So the name APPS for Oracle Technologies.
The reason why you create Tables,Indexes,Sequences etc.. in Custom schema and package scripts in APPS schema is :::::: It's simple. Having all the scripts belonging to different modules in one schema. At the time of migration other than tables everything can be taken from this central schema (APPS) and can be migrated accordingly.
The tables are still owned by their respective schema, but now we have a central schema named APPS. Oracle ERP simply connects to APPS database schema for all its operations.
For Example: Your Project is having modules like INV,PO,PA,GL,AR,AP,QA etc.. then you have to maintain your database in such a way that all the tables and their related scripts are defined in their respective custom schemas and the synonym for the same is defined in APPS schema. Before creating the synonym, you need to give all GRANT permissions to the APPS schema from your custom schema. And create all the package scripts of INV,PO,PA,GL,ARA,AP,QA etc.. in APPS schema. So that at the time of migration it becomes easier to migrate all the stuff from one CENTAL schema............i..e, APPS.
So the name APPS for Oracle Technologies.
Pre-requisites for becoming an APPS Technical Consultant.
Following are the pre-requisites for becoming an APPS Technical Consultant.
1.) Must have a strong knowledge on SQL Concepts.
2.) Must have a strong knowledge on PL/SQL Concepts.
After this, it is advisable to have knowledge on Oracle Reports, Forms( Since Forms are getting saturated from Oracle R12, it is advisable to go for OA Framework. Again to learn OA Framework, u need to have a strong knowledge on JAVA)
1.) Must have a strong knowledge on SQL Concepts.
2.) Must have a strong knowledge on PL/SQL Concepts.
After this, it is advisable to have knowledge on Oracle Reports, Forms( Since Forms are getting saturated from Oracle R12, it is advisable to go for OA Framework. Again to learn OA Framework, u need to have a strong knowledge on JAVA)
No comments:
Post a Comment