Use the Import Sales Order Template

Reflect the Structure of the Oracle Database

To help visualize the structure, go to File-Based Data Import (FBDI) for Oracle SCM, look in the Order Management chapter, click Import Sales Orders, then click SourceSalesOrderImportTemplate.xlsm. Examine the file that you download.

Enter your data so it reflects the structure of the Oracle database. The Source Sales Order Import Template uses a separate spreadsheet in an Excel workbook to represent each interface table.

A row near the top of each spreadsheet contains column headers.
Each column header represents the name of an interface table column.
The spreadsheet displays columns in a specific sequence.
You must not modify the sequence. If you do, import will fail.
Hide columns you don't need, but you must not delete them. If you delete a column, import will fail.

Source Sales Order Import Template includes a tab for each of these interface tables. The _INT suffix indicates its an interface table. You must include data in each required table.

Table Name	Details This Table Must Contain	Required
DOO_ORDER_HEADERS_ALL_INT	Order header data.	Yes
DOO_ORDER_LINES_ALL_INT	Order line data.	Yes
DOO_ORDER_ADDRESSES_INT	Address for the sales order.	Yes
DOO_ORDER_TXN_ATTRIBUTES_INT	Attributes that might be associated with an order line.	Yes
DOO_ORDER_SALES_CREDITS_INT	Sales credits for the sales representative that the source order references.	No
DOO_ORDER_PAYMENTS_INT	Payment details for the order line.	No
DOO_ORDER_LOT_SERIALS_INT	Lot serial details for the order line.	No
DOO_ORDER_DOC_REFERENCES_INT	References to documents that Order Management imports with the source order.	No
DOO_ORDER_CHARGES_INT	Charges for the sales order.	Required only if you don't calculate pricing in Oracle Pricing.
DOO_ORDER_CHARGE_COMPS_INT	Charge components for the sales order.	Required only if you don't calculate pricing in Oracle Pricing.
DOO_ORDER_BILLING_PLANS_INT	Billing plans for the sales order.	No
DOO_ORDER_MANUAL_PRICE_ADJ_INT	Manual price adjustments for the sales order.	No
DOO_ORDER_HDRS_ALL_EFF_B_INT	Flexfield details for the order header.	No
DOO_ORDER_LINES_ALL_EFF_B_INT	Flexfield details for the order line.	No
DOO_PROJECTS_INT	Projects for the sales order.	No

Example

Here's an example where the columns and values on the DOO_ORDER_CHARGES_INT worksheet reflect data from the Manage Pricing Charge Definitions page in the Setup and Maintenance work area.

For example:

Table Name	Details This Table Must Contain	Attribute Value
Code	Charge Definition Code	QP_SALE_PRICE
Name	Charge Definition	Sale Price
Applies To	Apply To	PRICE
Price Type	Price Type	One Time
Charge Type	Charge Type	Sale
Charge Subtype	Charge Subtype	Price

Enter Data in Required Columns

Note

Source Sales Order Import Template uses an asterisk ( * ) to indicate required columns. For example, the Source Transaction Schedule Identifier column on the DOO_ORDER_LINES_ALL_INT tab is required.
The template uses an asterisk ( * ) to indicate each required column.
The template uses a double asterisk ( ** ) to indicate the color group requires a value in at least one of these columns. For example, if a color group includes columns **a, **b, and c, then you must include a value in a or b, or a and b.
Required columns aren't always contiguous. Carefully examine column headings in each spreadsheet to make sure you include data for each required column.
To locate required headings on each spreadsheet, press CTRL + F, enter ~* (a tilde and an asterisk), and then click Find All.

Use Color to Determine What's Required

The template uses a blue background for column headings, by default. It uses other colors to group some columns.
For example, the DOO_ORDER_HEADERS_ALL_INT sheet uses a tan color to group columns Buying Party Identifier, Buying Party Name, and Buying Party Number. You must enter a value in at least one column of this color group.
Some sheets include more than one color group. You must enter at least one value for each color group.
Color groups might not display contiguously. Carefully examine the color group on each spreadsheet to make sure you include at least one value for each color group.
Some color groups are conditional. For example, you must enter a value for at least one column in the color group that represents these columns.
- Requested Fulfillment Organization Identifier
- Requested Fulfillment Organization Code
- Requested Fulfillment Organization Name
The DOO_ORDER_LINES_ALL_INT sheet contains these columns and uses them only for rows that references a return order. The sheet includes an instructional note immediately above each conditional color group.

Use the Correct Data Type

Use the format that the database column uses. For example, data in column Source Transaction Identifier of tab DOO_ORDER_LINES_ALL_INT must use format VARCHAR2(50).
Use example data and descriptive text to help determine the type of data to include.
Source Sales Order Import Template comes predefined with example data already populated, and some column headers include descriptive text.
For example, click the DOO_ORDER_LINES_ALL_INT tab, and notice that row seven in column Requested Fulfillment Organization Name includes a value of Vision Operations.
Position your mouse over column header Requested Fulfillment Organization Name, and then read the descriptive text that indicates this field must contain only VARCHAR2(240 CHAR) data. The value Vision Operations meets this requirement.
Make sure you remove example data before you import data.

For details, see Oracle Datatypes in Oracle Server Concepts Manual.

Format Amounts and Dates

Format for Amounts, Dates and Identifiers.

Note

You must use a comma (,) for the thousands separator and a period (.) for the decimal separator in number columns. For example, use 1,500.25. Don't use 1500,25.
Use a whole number when required. If a column must use a whole number, then the control file that validates data in this column includes only whole numbers. If it doesn't, then the import fails.
You must use format YYYY/MM/DD HH:MM:SS for each date field. For example, 1/15/2019 10:10:10 AM is January 15, year 2019, 10 hours, 10 minutes, and 10 seconds after 00:00:00 AM.

Format Internal Identifiers

If your data includes an internal identifier, then make sure you map it correctly.

An internal identifier is an identifier that Order Management uses to create a reference between a lookup that you specify for an item, customer, or reference data, to details from the Product Information Manager work area, Trading Community Architecture, or collection data. This data resides in the Order Orchestration and Planning Repository. For example, if your order uses Payment Terms, then you must set up this value in Oracle, then set up collections processing.
You must map your source data value to an Order Management value for each internal identifier column.
Read the descriptive text in the spreadsheet for help with columns that require an internal identifier.
The template includes more than one column that specifies details for internal identifiers. It uses the Identifier suffix to identify these columns, such as Source Transaction Identifier or Buying Party Identifier.
If your source data includes an identifier column that doesn't require setup in Product Information Management, Trading Community Architecture, or data collection, then you can use the implementation pages in your implementation project to get the identifier and other attributes that you must map for the internal identifier.

Import Customer Items

Make sure you correctly set up the rank for each customer item relationship before you import orders. For details, see Import Customer Items Into Order Management.

Other Things to Consider

If the value that you import for any attribute starts with a space character and you want to keep the space, then you must enclose the value with double quotation marks (" ").

Assume your attribute contains the text MyValue, and the value has three space characters before MyValue. Here's how you import that:

" MyValue"

where

There are three space characters between the opening quotation mark and MyValue.

If you don't do this, your import will still work but the imported value won't have any leading spaces.

Sequence Your Sales Orders

If you import more than one sales order, then Order Management creates them in a sequence according to the Source Transaction Identifier attribute and the Source Transaction System attribute.

Assume you have these values on the DOO_ORDER_HEADERS_ALL_INT worksheet.

Source Transaction Identifier	Source Transaction System
00001	LEG
00002	LEG
00003	GPR
00006	GPR
00005	GPR
00004	LEG

Here's the sequence that Order Management will use when it creates sales orders for these transactions.

Source Transaction Identifier	Sales Order
00001	50003
00002	50004
00003	50005
00006	50008
00005	50007
00004	50006

where

50002 is the most recent sales order that exists in Oracle data when you start the import, so the import starts with the next number that's available, which is 50003.
Order Management creates sales orders in a sequence according to Source Transaction Identifier regardless of the value in Source Transaction System.

The sales order numbers in this example assume that no other transactions happen during the import. Assume you create sales order 50004 in the Order Management work area while you're importing, and you create 50004 right after the import creates order 50003 for source transaction 00001 but before the import creates the sales order for transaction 00002. In this case, the import would use the next available order number for transaction 00002, which is order number 50005.

You can't modify this sequence.

Cancel Order Lines

Follow these guidelines when you use file-based data import to cancel an order line.

Use the same template populated with the same data that you used when you imported the source order.
Modify values only in the DOO_ORDER_LINES_ALL_INT worksheet. Don't edit data in any of the other worksheets.

Set these values in the DOO_ORDER_LINES_ALL_INT worksheet on the order line that you need to cancel.

Attribute	Value
Ordered Quantity	0 If you set this value to 0, then the import will set the order line status to Cancelled.
Request Cancel Date	Specify the date when you want Order Management to cancel the line.
Cancel Reason Code	Enter a code.
Cancel Reason	Enter a reason.
Operation Mode	Cancel

Here's how to determine what values you can use for the reason code and the reason:

Go to the Setup and Maintenance work area, then go to the task.
- Offering: Order Management
- Functional Area: Orders
- Task: Manage Order Lookups

On the Manage Order Lookups page, search for the value.

Attribute	Value
Lookup Type	DOO_RETURN_REASON

In the Lookup Codes section, examine the values in the:
- Lookup Code attribute. You can use any one of these values in the Cancel Reason Code attribute in your worksheet.
- Meaning attribute. You can use any one of these values in the Cancel Reason attribute in your worksheet.
- If you don't see a value that meets your needs, then add one.

If you:

Created the original order in Order Management, then set the Source Transaction Identifier to the SOURCE_ORDER_ID.
Didn't create the original order in Order Management, then set the Source Transaction Identifier to SOURCE_ORDER_NUMBER.

Make sure you specify the same value for the:

Source Transaction Identifier attribute in DOO_ORDER_LINES_ALL_INT and in DOO_ORDER_HEADERS_ALL_INT
Source Transaction System attribute in DOO_ORDER_LINES_ALL_INT and in DOO_ORDER_HEADERS_ALL_INT

You can cancel more than one line at the same time. Here's what that might look like.

Product Number	Request Cancel Date	Cancel Reason Code	Cancel Reason	Operation Mode
AS54888	2023/04/01	LATE	Late Delivery	Cancel
CN82441	2023/04/01	NO_REASON	No Reason	Cancel
WR001	2023/04/01	DEFECTIVE	Item is Defective	Cancel
CM62202	2023/04/01	MISSING_PARTS	Parts are Missing	Cancel
CM76962	2023/04/01	DISCONTINUED	Discontinued Item	Cancel
CM74237	2023/04/01	PRICE_CHANGE	Price Change	Cancel
CN62101	2023/04/01	QTY_CHANGE	Quantity Change	Cancel

You can cancel as many lines as you need to cancel. There's no limit on the number of lines that you can cancel.

For more, see Import Return Orders and Cancel Backorder During Import.