Sales Order Data Extract Files
The Sales Order Data Extract generates a zip file containing a set of pipe-delimited files, which contain the following types of information on sales orders and purchase orders:
- sold-to customer
- customization
- items
- order totals, locations, and other general order data
- order shipments
- ship-to customer
- status history
File naming: The zip file is named EXPORT_DATA_ORGANIZ_CD_20190825_045700, where ORGANIZ_CD is the organization code, 20190825 is the date generated in YYYYMMDD format, and 045700 is the time generated in HHMMSS format.
See File Storage API for Imports and Exports for information on receiving the exported files.
In this topic:
- XOM_CUSTOMER
- XOM_CUSTOMIZATION
- XOM_ITEM
- XOM_ORDER
- XOM_ORDER_SHIPMENT
- XOM_SHIPPING
- XOM_STATUS_HISTORY
For more information: See:
- Sales Order Data Extract for information on scheduling the extract and creating the extract files.
- View Sales Order Data Extract Job History for information on reviewing extract job history.
XOM_CUSTOMER
The table below lists the fields in the XOM_CUSTOMER pipe-delimited file, containing extracted data on the customer who placed each order. The information is found in the XOM_CUSTOMER table in the database.
The complete file name is EXPORT_DATA_XOM_CUSTOMER_ORGANIZ_CD_20190825_045700.dat, where ORGANIZ_CD is the organization code, 20190825 is the date generated in YYYYMMDD format, and 045700 is the time generated in HHMMSS format.
The file contains a header row. The heading for each column is indicated in the table below.
Personal data? If the Include Private Data flag was not selected when the export was generated, the data for all personal data fields in the file are replaced with the text ***** Data Privacy Blocked *****. The fields that can be masked include:
- Name fields
- Address fields, with the exception of the city, territory (province or state), and postal (zip) code
- Phone numbers
The information might also be masked because the order has already been anonymized. In this case, the data is replaced with a row of asterisks.
Field attributes: The field attributes listed below are based on what is supported through the database and the submit order message, or in the CreateDSOrder request message in the case of a purchase order.
For more information: See the of the Order screen for more information on customer-related fields in context, and see the Purchase Order screen for information on customer-related fields on purchase orders.
| Column Heading | Attributes | Description |
|---|---|---|
| REQUEST_ID |
Numeric, 10 |
A unique number assigned by Order Broker to identify an order. |
| ORDER_ID |
Alphanumeric, 30 |
The number identifying the order in the originating system. |
| LAST_NAME |
Alphanumeric, 50 |
The last name of the sold-to customer. |
| MIDDLE_NAME |
Alphanumeric, 50 |
The middle name of the sold-to customer. |
| FIRST_NAME |
Alphanumeric, 50 |
The first name of the sold-to customer. |
| PREFIX |
Alphanumeric, 50 |
The prefix to the sold-to-customer’s name, such as Dr. or Ms. |
| SUFFIX |
Alphanumeric, 50 |
The suffix to the sold-to customer’s name, such as Jr. |
| ADDRESS_1 |
Alphanumeric, 50 |
The first line of the sold-to customer’s address. |
| ADDRESS_2 |
Alphanumeric, 50 |
The second line of the sold-to customer’s address. |
| ADDRESS_3 |
Alphanumeric, 50 |
The third line of the sold-to customer’s address. |
| ADDRESS_4 |
Alphanumeric, 50 |
The fourth line of the sold-to customer’s address. |
| CITY |
Alphanumeric, 50 |
The city or town of the sold-to customer’s address. |
| TERRITORY |
Alphanumeric, 50 |
The state, province, or territory of the sold-to customer’s address. |
| POSTAL_CODE |
Alphanumeric, 50 |
The zip or postal code of the sold-to customer’s address. |
| DAY_PHONE |
Alphanumeric, 50 |
The daytime phone number of the sold-to customer. |
| EVENING_PHONE |
Alphanumeric, 50 |
The evening phone number of the sold-to customer. |
|
Alphanumeric, 250 |
The email address of the sold-to customer. |
|
| COUNTRY |
Alphanumeric, 3 |
The code identifying the country of the sold-to customer’s address. |
| COMPANY_NAME |
Alphanumeric, 50 |
The name of the sold-to customer’s company. |
| CUSTOMER_NO |
Alphanumeric, 30 |
The code identifying the customer in the originating system. |
| APT |
Alphanumeric, 50 |
The apartment or suite of the sold-to customer’s address. |
| TAX_EXEMPTION_NUMBER |
Alphanumeric, 50 |
Future use. |
| UNFORMATTED_PHONE |
Alphanumeric, 50 |
The customer’s daytime phone number with any formatting characters removed. |
XOM_CUSTOMIZATION
The table below lists the fields in the XOM_CUSTOMIZATION pipe-delimited file, containing extracted data on any customization or special handling for order lines. The information is found in the XOM_ORDER_ITEM_CUSTOMIZATION table in the database.
The complete file name is EXPORT_DATA_XOM_CUSTOMIZATION_ORGANIZ_CD_20190825_045700.dat, where ORGANIZ_CD is the organization code, 20190825 is the date generated in YYYYMMDD format, and 045700 is the time generated in HHMMSS format.
The file contains a header row. The heading for each column is indicated in the table below.
Personal data? This file does not contain any personal data.
Field attributes: The field attributes listed below are based on what is supported through the database and the submit order message, or in the CreateDSOrder in the case of a purchase order.
For more information: See the SubmitOrder Request Message and CreateDSOrder Request Message chapters in the Operations Guide for more information on how customization information is passed from the originating system.
| Column Heading | Attributes | Description |
|---|---|---|
| REQUEST_ID |
Numeric, 10 |
A unique number assigned by Order Broker to identify an order. |
| LINE_NUMBER |
Numeric, 10 |
The order line number receiving the customization. |
| SEQUENCE |
Numeric, 10 |
A unique number to identify each record. |
| CODE |
Alphanumeric, 50 |
The code used to identify the type of customization. |
| MESSAGE |
Alphanumeric |
The description of the customization. |
XOM_ITEM
The table below lists the fields in the XOM_ITEM pipe-delimited file, containing extracted data on the lines on orders. The information is found in the XOM_ITEM table in the database.
The complete file name is EXPORT_DATA_XOM_ITEM_ORGANIZ_CD_20190825_045700.dat, where ORGANIZ_CD is the organization code, 20190825 is the date generated in YYYYMMDD format, and 045700 is the time generated in HHMMSS format.
The file contains a header row. The heading for each column is indicated in the table below.
Personal data? This file does not contain any personal data.
Field attributes: The field attributes listed below are based on what is supported through the database and the submit order message, or in the CreateDSOrder in the case of a purchase order.
See the Details tab at the Order screen for more information on order detail lines, or see the Purchase Order screen for information on purchase orders.
| Column Heading | Attributes | Description |
|---|---|---|
| REQUEST_ID |
Numeric, 10 |
A unique number assigned by Order Broker to identify an order. |
| ORDER_ID |
Alphanumeric, 30 |
The number identifying the order in the originating system. |
| LINE_NUMBER |
Numeric, 10 |
The order line number assigned by Order Broker. |
| PRODUCT_CD |
Alphanumeric, 35 |
The product code for the default system in the organization. |
| PRODUCT_DESCRIPTION |
Alphanumeric, 40 |
The description of the item. |
| QUANTITY |
Number, 10 |
The requested quantity of the item. |
| LINE_ITEM_AMOUNT |
Number, 19.4 |
The single-unit price for the item charged by the originating system. |
| LINE_TAX_AMOUNT |
Number, 19.4 |
The total tax amount charged for a single unit of the item, passed by the originating system. |
| FULFILL_LOCATION_CD |
Alphanumeric, 10 |
The code identifying the fulfilling location, or the sourcing location for a ship-for-pickup order. |
| FULFILL_SYSTEM_CD |
Alphanumeric, 10 |
The code identifying the system associated with the fulfilling location. |
| STATUS |
Alphanumeric |
The current status of the order line. See Order and Line Statuses for more information. |
| STATUS_DATE |
Date |
The date and time of the most recent status update. |
| EXTENDED_DATA |
Alphanumeric |
Any special instructions for the purchase order line or sales order line passed by the originating system, and stored in the EXTENDED_DATA field in the XOM_ITEM table. |
| REQUESTER_LINE_NUMBER |
Number, 10 |
The line number in the originating system. |
| ORIGINAL_LINE_NUMBER |
Number, 10 |
Identifies the original line number on a sales order if the line was split. Otherwise, set to 0. |
| ORIGINAL_QUANTITY |
Number, 10 |
Identifies the original quantity of the line on a sales order was split. May differ from the QUANTITY if the line was split. |
| POLLED_COUNT |
Number, 10 |
The number of times the order line on a sales order has been polled. |
| EXTENDED_FREIGHT |
Number, 19.4 |
The extended freight charge passed by the originating system. |
| CUSTOMIZATION_CHARGE |
Number, 19.4 |
The charge amount for customization of the line. |
| GIFT_WRAP |
Alphanumeric, 1 |
Set to Y if the item should be gift wrapped; otherwise, set to N. |
| SHIP_ALONE |
Alphanumeric, 1 |
Set to Y if the item requires shipping alone; otherwise, set to N. |
| MESSAGES |
Alphanumeric |
Any message passed for the order line. Stored in the NOTE table in the database. |
| UNIT_SHIP_WEIGHT |
Numeric, 8.3 |
The unit shipping weight of the item, as passed by the originating system. |
| BATCH_ID |
Number, 10 |
A number assigned to group lines for printing and updating status. |
| SHIPMENT_ID |
Number, 10 |
Identifies a record in the XOM_ORDER file if the item has been shipped. |
| PICKUP_LOCATION_CD |
Alphanumeric, 10 |
The code identifying the location where the customer picks up the sales order. Included only for pickup or ship-for-pickup orders. |
| PICKUP_SYSTEM_CD |
Alphanumeric, 10 |
The code identifying the system associated with the location where the customer picks up the sales order. Included only for pickup or ship-for-pickup orders. |
| PICKUP_BY_DATE |
Date |
The date and time when the order line on a sales order is eligible to be automatically canceled. |
| AUTO_CANCELLED |
Date |
A setting of 1 indicates that the order line on a sales order was automatically canceled; otherwise, set to 0 or blank. See Auto-Cancel Unclaimed Orders for background. |
XOM_ORDER
The table below lists the fields in the XOM_ORDER pipe-delimited file, containing extracted data about the orders. The information is found in the XOM_ORDER table in the database.
The complete file name is EXPORT_DATA_XOM_ORDER_ORGANIZ_CD_20190825_045700.dat, where ORGANIZ_CD is the organization code, 20190825 is the date generated in YYYYMMDD format, and 045700 is the time generated in HHMMSS format.
The file contains a header row. The heading for each column is indicated in the table below.
Personal data? If the Include Private Data flag was not selected when the export was generated, the data for all personal data fields in the file are replaced with ***** Data Privacy Blocked *****.
The information might also be masked because the order has already been anonymized. In this case, the data is replaced with a row of asterisks.
Field attributes: The field attributes listed below are based on what is supported in the database and through the submit order message, or in the CreateDSOrder request message in the case of a purchase order.
| Column Heading | Attributes | Description |
|---|---|---|
| REQUEST_ID |
Numeric, 10 |
A unique number assigned by Order Broker to identify an order. |
| ORDER_ID |
Alphanumeric, 30 |
The number identifying the order in the originating system. |
| CREATE_TIMESTAMP |
Date |
The date and time when the order was created in Order Broker. |
| ORDER_TYPE |
Alphanumeric |
Possible order types:
|
| ORDER_DATE |
Date |
The transaction date passed by the originating system. |
| SUBTOTAL_AMOUNT |
Number, 19.4 |
The order total before taxes as passed by the originating system. |
| TAX_AMOUNT |
Number, 19.4 |
The total taxes for the order as passed by the originating system |
| TAX_DESCRIPTION |
Alphanumeric, 30 |
The same as the TAX_AMOUNT. |
| TOTAL_AMOUNT |
Number, 19.4 |
The total amount for the transaction as passed by the originating system. |
| ORIGINATING_EMPLOYEE_ID |
Alphanumeric, 10 |
From the employee ID passed by the originating system, or the buyer ID for a purchase order. |
| ORIGINATING_TRANS_ID |
Alphanumeric, 50 |
The transaction number passed by the originating system for a sales order. |
| ORIGINATING_CHANNEL |
Number, 10 |
The originating channel passed by the originating system for a sales order. |
| ORIGINATING_LOCATION_CD |
Alphanumeric, 10 |
The code identifying the originating location passed by the originating system. |
| ORIGINATING_SYSTEM_CD |
Alphanumeric, 10 |
The code identifying the system associated with the originating location, passed by the originating system. |
| STATUS |
Alphanumeric |
The current status of the order. See Order and Line Statuses for more information. |
| STATUS_DATE |
Date |
The most recent date and time when the status of the order was updated. |
| EXTENDED_DATA |
Alphanumeric |
The special instructions passed by the originating system. |
| SHIP_VIA |
Alphanumeric, 50 |
The code identifying the carrier for the order, passed by the originating system. |
| SHIP_VIA_DESCRIPTION |
Alphanumeric, 50 |
The description of the carrier for the order, passed by the originating system. |
| GIFT |
Alphanumeric, 1 |
Set to Y to indicate that the order is a gift, passed by the originating system. |
| FREIGHT_AMOUNT |
Number, 19.4 |
The total freight charges for the order, passed by the originating system. |
| BALANCE_DUE |
Number 19,4 |
The amount due when the order is picked up, typically used to indicate the balance due for a pickup or ship-for-pickup order. From the balance_due passed in the SubmitOrder request message. |
| SOURCE_CODE |
Alphanumeric, 50 |
A code identifying the source of the sales order in the originating system. From the source_code passed in the SubmitOrder request message. |
| POLLED_COUNT |
Numeric, 10 |
Set to 0. |
| REF_TRANSACTION_NO |
Alphanumeric, 50 |
From the ref_transaction_no passed in the SubmitOrder request message for a sales order. |
| CURRENCY |
Alphanumeric, 10 |
The three-position alphabetical ISO 4217 currency code for the order. From the currency passed by the originating system. |
| ADDITIONAL_FREIGHT |
Number, 19.4 |
The additional freight on the order, passed by the originating system. |
| ADDITIONAL_CHARGES |
Number, 19.4 |
The additional charges on the order, passed by the originating system. |
| SHIP_COMPLETE |
Alphanumeric, 1 |
Set to Y if the order should ship complete. |
| NOTES |
Alphanumeric |
Any message passed in the order_message tag of the SubmitOrder request message for a sales order. Stored in the NOTE table in the database. |
| GIFT_MESSAGES |
Alphanumeric |
Any gift message passed by the originating system. Stored in the NOTE table in the database. |
| FREIGHT_TAX |
Number, 19.4 |
The tax on freight for the order passed by the originating system. |
| UNDER_REVIEW |
Numeric, 1 |
Set to 1 if the sales order is currently under review; otherwise, set to 0. |
| IN_PROCESS |
Alphanumeric, 1 |
Set to Y if the sales order has not yet been “shopped” to a fulfilling location; otherwise, set to N. |
| FULFILLMENT_OVERRIDE |
Numeric, 1 |
Set to 1 if the sourcing location was specified in the SubmitOrder request for a ship-for-pickup order; otherwise, set to 0. Defaults to 0. Only used for new orders created in 16.0 or higher. |
XOM_ORDER_SHIPMENT
The table below lists the fields in the XOM_ORDER_SHIPMENT pipe-delimited file, containing extracted data on the shipments made for orders. The information is found in the XOM_ORDER_SHIPMENT table in the database.
The complete file name is EXPORT_DATA_XOM_ORDER_SHIPMENT_ORGANIZ_CD_20190825_045700.dat, where ORGANIZ_CD is the organization code, 20190825 is the date generated in YYYYMMDD format, and 045700 is the time generated in HHMMSS format.
The file contains a header row. The heading for each column is indicated in the table below.
Personal data? This file does not contain any personal data.
Field attributes: The field attributes listed below are based on what is supported through the StatusUpdate request message and the database.
| Column Heading | Attributes | Description |
|---|---|---|
| SHIPMENT_ID |
Number, 10 |
A unique number to identify a shipment. |
| CARRIER_CODE |
Alphanumeric, 50 |
From the carrier or shipping agent passed by the originating system. |
| TRACKING_NUMBER |
Alphanumeric, 255 |
From the tracking_number passed in the Order Status Update request message for a sales order. |
| SHIPMENT_DATETIME |
Date |
The date and time of the shipment. |
| FREIGHT_CHARGES |
Number, 19.4 |
From the freight charges in the XOM_ORDER_SHIPMENT table. |
| ACTUAL_WEIGHT |
Number, 12.4 |
The package weight. |
| FULFILLMENT_ID |
Alphanumeric, 255 |
A package identifier. |
XOM_SHIPPING
The table below lists the fields in the XOM_SHIPPING pipe-delimited file, containing extracted data on the customer who receives each order. A separate record is created for each shipped line.The information is found in the XOM_SHIPPING table in the database.
The complete file name is EXPORT_DATA_XOM_SHIPPING_ORGANIZ_CD_20190825_045700.dat, where ORGANIZ_CD is the organization code, 20190825 is the date generated in YYYYMMDD format, and 045700 is the time generated in HHMMSS format.
The file contains a header row. The heading for each column is indicated in the table below.
Personal data? If the Include Private Data flag was not selected when the export was generated, the data for all personal data fields in the file are replaced with ***** Data Privacy Blocked *****. The fields that can be masked include:
- Name fields
- Address fields, with the exception of the city, territory (province or state), postal (zip) code, and country code
- Phone numbers
Field attributes: The field attributes listed below are based on what is supported through the SubmitOrder request message and the database.
For more information: See the Header tab of the Order screen for more information on customer-related fields in context, and see the Purchase Order screen for information on customer-related fields on purchase orders.
| Column Heading | Attributes | Description |
|---|---|---|
| REQUEST_ID |
Numeric, 10 |
A unique number assigned by Order Broker to identify an order. |
| ORDER_ID |
Alphanumeric, 30 |
The number identifying the order in the originating system. |
| LINE_NUMBER |
Numeric, 10 |
The order line number shipped. |
| LAST_NAME |
Alphanumeric, 50 |
The last name of the ship-to customer. |
| MIDDLE_NAME |
Alphanumeric, 50 |
The middle name of the ship-to customer. |
| FIRST_NAME |
Alphanumeric, 50 |
The first name of the ship-to customer. |
| PREFIX |
Alphanumeric, 50 |
The prefix to the ship-to customer’s name, such as Dr. or Ms. |
| SUFFIX |
Alphanumeric, 50 |
The suffix to the ship-to customer’s name, such as Jr. |
| ADDRESS_1 |
Alphanumeric, 50 |
The first line of the ship-to customer’s address. |
| ADDRESS_2 |
Alphanumeric, 50 |
The second line of the ship-to customer’s address. |
| ADDRESS_3 |
Alphanumeric, 50 |
The third line of the ship-to customer’s address. |
| ADDRESS_4 |
Alphanumeric, 50 |
The fourth line of the ship-to customer’s address. |
| CITY |
Alphanumeric, 50 |
The city or town of the ship-to customer’s address. |
| TERRITORY |
Alphanumeric, 50 |
The state, province, or territory of the ship-to customer’s address. |
| POSTAL_CODE |
Alphanumeric, 50 |
The zip or postal code of the ship-to customer’s address. |
| COUNTRY |
Alphanumeric, 3 |
The three-position alphabetical or numeric (ISO Spec 3166) of the ship-to customer’s country. |
| DATE_ |
Date |
The date and time when the order was created. |
| REFERENCE |
Alphanumeric, 50 |
The shipping tracking number. From the tracking_number in the Order Status Update request message for a sales order. |
| VIA |
Alphanumeric, 50 |
The shipper for the order. From the shipping_agent in the Order Status Update request message for a sales order. |
| DAY_PHONE |
Alphanumeric, 50 |
The daytime phone number of the ship-to customer. |
| EVENING_PHONE |
Alphanumeric, 50 |
The evening phone number of the ship-to customer. |
|
Alphanumeric, 250 |
The email address of the ship-to customer. |
|
| COUNTRY |
Alphanumeric, 3 |
The code identifying the country of the ship-to customer’s address. |
| APT |
Alphanumeric, 50 |
The apartment or suite of the ship-to customer’s address. |
XOM_STATUS_HISTORY
The table below lists the fields in the XOM_STATUS_HISTORY pipe-delimited file, containing extracted data on the status history of each order. The information is found in the XOM_STATUS_HISTORY table in the database.
The complete file name is EXPORT_DATA_XOM_STATUS_HISTORY_ORGANIZ_CD_20190825_045700.dat, where ORGANIZ_CD is the organization code, 20190825 is the date generated in YYYYMMDD format, and 045700 is the time generated in HHMMSS format.
The file contains a header row. The heading for each column is indicated in the table below.
Personal data? The EMPLOYEE_ID in the file is not masked regardless of whether the Include Private Data flag was selected.
Field attributes: The field attributes listed below are based on what is supported through the SubmitOrder request message and the database.
For more information: See the Header tab of the Header tab screen for more information on customer-related fields in context, and see the Purchase Order screen for information on customer-related fields on purchase orders.
| Column Heading | Attributes | Description |
|---|---|---|
| REQUEST_ID |
Numeric, 10 |
A unique number assigned by Order Broker to identify an order. |
| ORDER_ID |
Alphanumeric, 30 |
The number identifying the order in the originating system. |
| LINE_NUMBER |
Numeric, 10 |
The order line number related to the activity. |
| SEQUENCE |
Number, 10 |
A unique number to identify the status history record for the order. |
| ACTIVITY_DATE |
Date |
The date and time when the activity occurred. |
| STATUS |
Alphanumeric |
The status of the order line at the time of the activity. See Order and Line Statuses for more information on sales orders. |
| REASON |
Alphanumeric, 50 |
The description of the status code, as described for the STATUS field, above. The same as the STATUS, except:
|
| EMPLOYEE_ID |
Alphanumeric, 10 |
The employee ID indicated for the order creation or status change. |
| TRANS_ID |
Alphanumeric, 50 |
The transaction_no passed by the originating system in the SubmitOrder request message for a sales order. |
| FULFILL_LOCATION_CD |
Alphanumeric, 10 |
The code identifying the location to fulfilling location, or the sourcing location for a ship-for-pickup order. |
| FULFILL_SYSTEM_CD |
Alphanumeric, 10 |
The code identifying the system associated with the fulfilling location. |
| SHIP_VIA |
Alphanumeric, 50 |
The code identifying the carrier for the order. From the ship_via passed in the SubmitOrder request message or the shipping_agent in the Status Update message for a sales order. |
| SHIPPING_REFERENCE |
Alphanumeric, 50 |
From the ship_via_description passed in the SubmitOrder request message or the tracking_number in the Status Update request message for a sales order. |
| QUANTITY |
Number, 10 |
The order line quantity associated with the activity. |
| SOURCE |
Alphanumeric |
The source associated with the activity: UI = User Interface WS = Web Service or API |
| ORDER_STATUS_REASON_CODE |
Alphanumeric, 30 |
The status reason code, if any, passed in the Order Status Update request message for a sales order. |
| ORDER_STATUS_REASON_NOTE |
Alphanumeric, 254 |
A note about the activity, if any, from the order_status_reason_note passed in the Order Status Update request message for a sales order. This field is also updated with the current Under Review status when the order is initially created, or when the Under Review status changes. |
| EXPORT_DATE |
Date |
The date when the sales order was exported through the Sales Order Data Extract. |
| CARTON_NBRS |
Alphanumeric, 500 |
The carton numbers used to ship the sales order. |
| SHIP_TO_CHANGE_ID |
Number, 10 |
A value in this field indicates that there has been a shipping address change for a sales order. You can review shipping address changes at the Order History Detail - Address Change Window. |
| UNFULFILLABLE_REASON_CODE |
Number, 10 |
A reason code generated by the Science Engine if a sales order is partially or fully unfulfillable. See the discussion of Science Engine responses at History tab of the Order screen for more information. |
| PICKUP_BY_DATE |
Date |
The last date before a pickup or ship-for-pickup order is eligible to be canceled by the auto-cancel unclaimed orders job. |
| PICKUP_BY_DATE_CHANGED |
Numeric, 1 |
If set to 1, indicates to display the Pickup By Date in order history as a change. |