This chapter covers the following topics:
Order Import is Order Management's open interface for entering, changing or canceling orders and returns. Use Order Import to bring in orders from external systems, legacy systems, EDI, or from internal systems such as internal orders created by Oracle Purchasing to fulfill internal requisitions. If you have enabled Multi-Org Access Control, you can import orders for all Operating Units that are accessible to you in a single submission. Please refer to the Oracle Order Management Open Interfaces, API, & Electronic Messaging Guide for more information on using Order Import across Operating Units.
Order Import has been implemented as a set of interface tables that must be loaded with the order or return data, and a set of APIs to process that data. A concurrent program is provided which calls the APIs to initiate processing of the data. In addition, Order Import provides forms that allow you to query orders from the interface tables, make corrections or changes to that data, and re-initiate the import process. Orders that fail to be imported are retained in the tables, and can be queried and corrected using the forms. Messages are provided to give you details of why the order did not import.
Order Import calls base Order Management APIs (specifically, Process Order API) to validate and insert or update data in the base order tables, thereby insuring that consistent processing occurs.
Queries in Order Import are optimized with the Cost Based Optimizer of the database. The Cost Based Optimizer uses generated statistical information to optimize queries. The Order Import Statistics concurrent program gathers statistics for use by the cost based optimizer. This concurrent program should be run after data is populated into the interface tables.
The Cost Based Optimizer performs a table analysis of all interface tables related to Order Import for determining optimum record processing. You have the option to submit this program prior to each submission of the Order Import concurrent program. If you normally process a similar number of interface records, you typically do not need to submit this program.
Order Import provides many features to ease the work of integrating order data from external and other types of system.
Order Import's main task is to provide a batch-like facility for inputting large numbers of orders into Order Management. It is runnable as a concurrent request, so you can schedule it to run at specific intervals throughout the day – for example, to coincide with schedules of your feeding systems. Once orders are imported into the base Order Management tables, the order and line workflows are started. All subsequent processing, including sourcing and scheduling activities, takes place as though the order were input manually.
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 Management has a set of forms you can use to review and correct data that is in the Order Import tables. They are called the Order Import Correction forms. They are accessible from the OM Menu under the Order Import menu item. They consist of a find screen followed by a series of forms where you can view and correct data. There are forms to display order headers, order lines, sales credits, price adjustments, return lot/serial numbers, payments, new customer and address records, pricing attributes, and the actions table. The forms have buttons to enable you to re-validate or re-import data that you have selected. There is another button to transfer to the Message Window to display any error messages in your data import. Viewing error and warning messages about imported orders replaces the Order Import Processing Exception Report used in earlier versions. Most fields do not have any validation or list of values within the window, so if you key over a field to correct it, you won't know if it is good until you either validate or re-import. If you decide an order or line is in the import tables in error, you can set the Reject_Flag to Y on the Status Tab to indicate that you don't want to continue processing it. The order or line will be deleted in the next run of Order Import. See the Flags section below for more information about the Reject_Flag. This can be useful if an order it too difficult to correct via the forms. This allows you to fix it in the feeder system and re-import it, or it can be used to purge off orders that may have resulted from duplicate runs of your feeder systems.
The user interface for the Correction Form is a folder window. The forms are not multi-select enabled for re-validating or re-importing using the button. The data from the header and lines import tables is presented in forms with the data organized logically onto various tabs. The other forms—discounts, sales credits, payments, pricing attributes, and actions—are single-tab forms. Screen shots of the Find screen and the Orders window are contained in the Oracle Order Management User's Guide.
Order Import can enter a new customer account with minimal data at the sold-to level on the order header. You can enter a new customer account at the ship-to, bill-to level or deliver_to at the order header or order line. An add customer interface table accommodates this: when the table is loaded it indicates the intention is to create a new customer account the required fields are populated for the new account. Order Import then creates a new customer account and, if all required data is present and valid in the interface tables, a party. You can associate the new customer account with an existing party by providing the party (organization or person) number in the interface tables. If that column is left null, Order Import creates the party as well as the customer account. The new customer is assigned to the Default customer profile class, which specifies various financial and credit checking information.
Order Import turns off all Trading Community Architecture business events before using the Add Customer functionality. This is done by setting the profile option HZ: Raise API Events to No at the user level for the session. This profile option is used to configure the raising of Granular (V2) and Business Object business events from Trading Community Architecture Public APIs. After Order Import is over, the value of the profile option is reverted to its old value. Order Management does not have seeded subscriptions to TCA business events. However some other products do and you may need to run the TCA Full Synch concurrent program after Order Import.
For more information about TCA integration, please refer to the Trading Community Architecture Administration Guide (Implementation chapter) or the Trading Community Architecture Technical Implementation Guide (Business Events section).
Import orders and book them through Order Import. If the order fails booking validation, the order is still imported, but is left in the Entered state. The Messages Window can be used to see why the order failed booking or you can just attempt to Book using the Book button, and then errors will be displayed. There are two ways to indicate that you want the order to be booked. You can load the actions interface table OE_ACTIONS_IFACE_ALL with a value of BOOK_ORDER in the OPERATION_CODE column to import orders in a booked status, or you can set the booked flag. See the section below on the Actions table for more information.
Customer item numbers or UPC numbers can be entered in Order Import the same way as manually created orders, so long as you have the cross-references and cross reference types set up in advance of running order import. In the interface tables you need to put the ‘item ordered' into the column named CUSTOMER_ITEM_NAME and if you know what kind of item number it is (customer, inventory item or one of the generic cross references), you can put its type into CUSTOMER_ITEM_ID_TYPE.
Input order changes and cancellations to existing orders via the Order Import open interface tables. There is a column in each of the interface tables called OPERATION_CODE where you put INSERT, UPDATE or DELETE. Null is equivalent to INSERT. If you want to make changes, you must specify an OPERATION_CODE of UPDATE. To cancel a line, use an operation of UPDATE and then make the ordered quantity = 0. To partially cancel, change the ordered quantity to the new quantity you want to remain on the line. To cancel an order in its entirety, use an operation of UPDATE at the header, and then set the CANCEL-FLAG to Y. All order changes and cancellations are subject to the Processing Constraints you defined.
Import returns just like you import orders, by choosing an order type that supports return line types. You can also import mixed orders – those are orders that have some outbound lines and also some inbound (return) lines. The path that the line follows is determined by the workflow attached to the line type. You might import returns or return lines from legacy systems, or from other order entry systems you might be running. There is a separate interface table where you can import anticipated lot/serial numbers – this table is only used for return lines.
Orders that are input using Order Import will get rule-based attachments automatically applied based on the setting of the profile option OM: Apply Automatic Attachments. If you have this profile option set to NO, you can still apply automatic attachments on an order by order basis by using the Actions Interface table – see the discussion of that table below. There is at this time no way to import note texts, or to create attachments via an open interface.
There are two ways to price orders being imported. You can let the system calculate the price, or you can populate the price fields in the lines interface table with the price you want to charge, and also populate the price-adjustment interface tables with price adjustments that result in that net price. You indicate which you want to use by setting a value in CALCULATE_PRICE_FLAG in the lines interface table. If the calculate price flag is Y, the system will ignore any pricing values loaded into the price fields and will calculate the price using the pricing engine. If the calculate price flag is N, you must populate unit list price, unit net price, and any price adjustments in the interface tables to account for the difference between list and net.
Note: If you are importing a modifier with a qualifier of type Order Amount, it will not be applied during order import. If you reprice the order after the import, the modifier with the Order Amount qualifier will be applied.
A common requirement from EDI customers is the ability to validate the price and payment terms that a customer sends in against what the system determines. EDI customers do not typically accept any price or terms that the customer sends in, but they need to keep track of what the customer said they thought they should get. The Customer Service Representative usually contacts the customer to resolve any discrepancies. For example, a customer may send in one price that they have been quoted by a salesperson, which assumed they received some discount. Perhaps the discount had expired by the time the order was imported. This results in a discrepancy that a CSR needs to investigate.
Order Import supports this requirement by letting you populate two attributes in the order lines interface table, CUSTOMER_ITEM_NET_PRICE and CUSTOMER_PAYMENT_TERM. If either of these columns contain data, Order Import compares the system-determined price or payment terms to these columns, and raises a warning if there is a difference. It will still import the order as long as there are no other errors in the order. In both cases, the system-determined value is what is used to process the order, and the customer value is retained on the order line in the base sales order tables for reference purposes.
Following is a table with examples of what would happen in different cases of the customer price and the system price – in addition, it shows how the Calculate Price Flag affects the process:
| Calculate Price Flag | Customer Provided Price | System Calculated Price | Action |
|---|---|---|---|
| N | 20 | - | Accept customer price. |
| Y | 20 | 20 | Accept customer price. (System = Customer). |
| Y | 20 | 10 | Accept customer price. (System < Customer). |
| Y | 20 | 30 | Don't accept Customer price. Report the error. (System > Customer). |
Order Import uses the Process Orders API to validate and process order data in the interface tables. For more information on open interfaces, see the Oracle Manufacturing APIs and Open Interfaces Manual.
There are no special workflow considerations for Order Import.
OM: Add Customer - Order Import
This profile controls whether the user has authorization to create customers, addresses and contacts with Order Import
OM: Run Order Import for XML
This profile controls whether Order Import is run synchronously whenever XML data is imported.
OM: Unique Order Source, Orig Sys Document Ref Combination For Each Customer
This profile controls whether uniqueness of this data is enforced by customer or across customers.
HZ: Raise API Events
This profile option is used to configure the raising of Granular (V2) and Business Object business events from Trading Community Architecture Public APIs. It is updateable at the Site, Application, Responsibility, User levels. Please refer to the section on Importing Customer Information for details on how Order Import uses it.
There is only one special setup for Order Import; otherwise, the same setup that you need to perform to manually key orders must be in place before you can import orders.
Set up the names of the sources from which you intend to import orders. There is a special setup window in Order Management allows you to define the name and description for your source. Import Source is a parameter you can use when you submit the Order Import concurrent program, and it is also one of the queryable fields on the Find window of the Order Import Correction window. The Import Source is carried in the order header also, so you can identify the origin of the order. Seeded Order Import sources include EDI and Internal Orders.
The Item Validation Org parameter for the operating unit of the user running Order Import determines the organization used for validating items and bill of material structures. Item Validation Org is an Order Management parameter that is set per operating unit.
To import orders, you need some means to load the Order Import tables. In most cases, you will develop a program or script using SQL-loader or some other programming language to convert data from your feeder system into the standard data format that Order Import is expecting.
Oracle Purchasing contains such a program (Create Internal Sales Order) that takes data from the Purchasing schema for internal requisitions and loads the Order Import tables. Similarly, the eCommerce Gateway product provides a program (Purchase Order Inbound) that loads the import tables for the Inbound Purchase Order EDI transaction set. You can take a look at that code to guide you in writing your own program to load the tables.
It is advisable that you set up Defaulting Rules in Order Management that will default as much of the order and line information as possible for your environment, thereby reducing the amount of data that would need to be populated into the import tables.
There are certain columns and tables in the set of import tables whose function is not self-evident. Here is some additional information about these attributes to help you be successful in loading the tables properly.
Several flags in the interface tables of Order Management affect Order Import processing. Valid values of these flags are Y, N and null. Null means different things depending on the particular flag. These flags are viewable and updateable from the Status Tab of both the Order Header and Lines forms of the Order Import Correction forms.
Force Apply Flag
(used for Change transactions only)
The Force Apply flag is used to indicate that you want to apply a Change transaction even though the change sequence numbers are out of order. Default is N, and a null value is equivalent to N. Typically a user would set this flag to Y (checked in the UI) if they determine that a set of changes should be applied regardless of the change sequence. See the section below for more information on Change Sequence Numbers and how they are used.
This flag is at the header level only.
Closed Flag
The Closed flag is used to indicate the line or order being imported should be imported in a Closed state. You might want to import a closed order so your historical data is all in one place, or to provide reference data for Returns. Default for this flag is N, and a null value is equivalent to N.
This flag is at both the header and the line level.
Canceled Flag
The Canceled flag is typically used to indicate that the line or order being imported should be imported in a Canceled state. Default is N, and a null value is equivalent to N.
This flag is at both the header and the line level.
Booked Flag
Set this flag to Y if you want this order to be booked once it is imported. Default is N and null value is equivalent to N.
This flag is available at the header level only.
Reject Flag
There may be orders or order lines you have determined you no longer want to attempt to process further. Using the Order Import Corrections window, you can select an order or line you no longer wish to process, go to the STATUS tab, and select the Rejected checkbox. Rejected orders or rejected order lines are Deleted during the next execution of the Order Import program. Default is N, and a null value is equivalent to N.
This flag is at both the header and the line level.
ERROR_FLAG
The error flag is set on by the Order Import process whenever an error is encountered during the validation process. Default is N, and a null value is equivalent to N.
This flag is at both the header and the line level.
READY_FLAG
The ready flag indicates that the record will be processed in the Order Import Process. Default is Y, and a null value is equivalent to Y. If the ready flag is N, the order will not be looked at when Order Import is run.
This flag is at the header level.
Validate Mode Parameter in Concurrent Manager
There is a validate mode parameter you can set when you submit Order Import to run through the concurrent manager. This parameter tells the process to only validate the records, and not to process valid records any further. Base Order Management tables will not have records inserted, updated, or deleted.
| READY_FLAG | (Only) Validate Parameter |
Processing |
|---|---|---|
| N | Y | Record is not processed |
| N | N | Record is not processed |
| Y or NULL | Y | Process to Validate Only |
| Y or NULL | N | Process to Insert/Update/Delete in Base Table |
Actions Table
One of the Order Management interface tables is the Actions table. Its purpose is to allow you to indicate what ‘actions' you want to be done to the order, once it has been written to the Order Management base tables. It is the Order Import equivalent of a user pressing the Action button on the Sales Order window after you have entered an order. Load the action name into the OPERATION_CODE column of this table, and populate other data as needed, and then Process Orders will execute that action if the order import is successful. You can hold or release an order or line from hold using this method; this is one way to book an order through Order Import. Other actions you can perform are Apply Automatic Attachments and Delink Config Item and Match & Reserve a configured item. Here is the character string you need to populate in OPERATION_CODE of the OE_ACTIONS_INTERFACE table and other data you need to put in the table to achieve each action.
| ACTION | OPERATION_CODE | OTHER DATA |
|---|---|---|
| Apply Automatic Attachments | AUTOMATIC_ATCHMT | none |
| Apply a Hold | APPLY_HOLD | hold_id, hold_type_code, hold_type_id, comments (optional), hold_until_date (optional) |
| Release a Hold | RELEASE_HOLD | hold_id, hold_type_code, hold_type_id, comments (optional), release_reason_code |
| Book the Order | BOOK_ORDER | none |
| Delink the Config Item | DELINK_CONFIG | none |
| Match and Reserve a configuration item for an ATO model | MATCH_AND_RESERVE | none |
Most attributes in the interface tables have two flavors – a code or name and an ID. You may choose to populate either the code or the ID for each attribute. If you populate IDs, performance will be improved. If you populate both an ID and a code for an attribute, the ID will be used and the value in the code field will be ignored.
When you send in changes to orders using Order Import, you need a way to tell Order Import what order or line you are changing. Your feeding system most likely doesn't know the Order Management Order Number. If it does, you can populate the interface column ORDER_NUMBER to locate your order. There are a group of columns in the interface tables that are carried over into the Sales Order tables, and these are used to locate the order to be changed.
For Order level changes, the following fields need to match between the change transaction in the interface tables and the existing order in the Sales Order tables:
ORIG_SYS_DOCUMENT_REF - note, this is often the customer's purchase order number
ORDER_SOURCE_ID
For Line level changes, the following fields need to match between the change transaction in the interface tables and the existing order in the Sales Order tables:
ORIG_SYS_DOCUMENT_REF - note, this is often the Customer's Purchase Order Number
ORDER_SOURCE_ID
ORIG_SYS_LINE_REF - note, this is often the customer's purchase order line Number concatenated with the shipment number or current customer request date.
If the existing order or line do not have these fields populated, you will not be able to make changes to them using Order Import.
Please note that if you need to update a line, the header should not be cancelled. In case the header is cancelled, the corresponding lines are also cancelled and if you attempt to update the lines, an error message will display.
Change sequence numbers are a way to control the sequence in which a group of changes is applied to an order. The use of change sequence numbers in Order Import is optional. Change sequence numbers are most frequently used by the EDI Purchase Order Change transaction, but you can also use them to control the order of application of changes, in the event you are importing changes from a legacy or third-party system. For more information about how change sequence numbers work, see the Oracle Order Management User's Guide.