Use the Import Sales Order Template
Apply guidelines when you use the import 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
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 | Ordered Quantity | Request Cancel Date | Cancel Reason Code | Cancel Reason | Operation Mode |
---|---|---|---|---|---|
AS54888 | 0 | 2023/04/01 | LATE | Late Delivery | Cancel |
CN82441 | 0 | 2023/04/01 | NO_REASON | No Reason | Cancel |
WR001 | 0 | 2023/04/01 | DEFECTIVE | Item is Defective | Cancel |
CM62202 | 0 | 2023/04/01 | MISSING_PARTS | Parts are Missing | Cancel |
CM76962 | 0 | 2023/04/01 | DISCONTINUED | Discontinued Item | Cancel |
CM74237 | 0 | 2023/04/01 | PRICE_CHANGE | Price Change | Cancel |
CN62101 | 0 | 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.