What is Order Import?
Order Import is an Order Management Open Interface that consists of open interface tables and a set of APIs. Order Import can import new, change, and completed sales orders or returns from other applications such as a legacy system. You can also import cancelled , closed and booked orders.
Interface tables:
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
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.
For Script Order Import Script
Item import (Item conversion)
Always import master and child records.
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.
- The orders may come from any source such as EDI transactions that are processed by the Oracle e-Commerce Gateway or internal orders created for internal requisitions developed in Oracle Purchasing or returns.
- Order Import features include validation and defaulting, processing constraint checks, applying and releasing of order holds, scheduling of shipments, then ultimately
- Inserting, updating or deleting the Orders in the base Order Management tables.
- Order Management checks all the data during the import process to ensure its validity within Order Management.
- Valid transactions are then converted into Orders with Lines, Reservations, Price Adjustments, and Sales Credits in the base Order Management tables.
- Order Import does not contain its own validation routines for the data. Instead, it calls the Process Orders API, which is the same API used to validate and insert orders if you are keying them through the Sales Order window.
- This design makes for better maintainability, as any enhancements or bug fixes done to Process Orders will immediately affect importing orders too.
- The Process Orders API uses Processing Constraints to evaluate whether a requested change can be made to an order.
- Order Import, because it uses Process Order API, evaluates all Processing Constraints, and any constraint violations are captured and can be reviewed using the Correction Forms and the Messaging Window.
- Order Import has a feature that allows you to run in validate only mode, to pre-screen the orders in a batch and correct all the errors before you run the import.
- If an order has any errors, then the entire order will be retained in the import tables. Importing is an all-or-nothing process per order.
- Order Import Interface (Sales Order Conversion)
- Order Import enables you to import Sales Orders into Oracle Applications instead of manually entering them.
- Pre-requisites:
- Order Type
- Line Type
- Items
- Customers
- Ship Method/ Freight Carrier
- Sales Person
- Sales Territories
- Customer Order Holds
- Sub Inventory/ Locations
- On hand Quantity
Interface tables:
- OE_HEADERS_IFACE_ALL
- OE_LINES_IFACE_ALL
- OE_ACTIONS_IFACE_ALL
- OE_ORDER_CUST_IFACE_ALL
- OE_PRICE_ADJS_IFACE_ALL
- OE_PRICE_ATTS_IFACE_ALL
- Base tables:
- OE_ORDER_HEADERS_ALL
- OE_ORDER_LINES_ALL
- 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
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
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
For Script Order Import Script
Item import (Item conversion)
Always import master and child records.
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.
