Downloading E-Commerce Offer Files (EOFR)
Purpose: Use the Download E-Commerce Offer Files menu option to select offer, source, and item-related information for download to the web storefront. Typically, this type of information changes periodically.
You can download all three types of information at once, or selected types as needed.
Type of download: The setting of the Generate E-Commerce Offer Tables (M29) system control value defines the output of the e-commerce offer download.
-
If this system control value is selected, the e-commerce offer download (EOFR) populates the E-Commerce Offer Staging tables; see Offer-Related Table Extract Summary.
-
If this system control value is unselected, the e-commerce offer download (EOFR) generates the E-Commerce Product Web XML File (ProductWeb).
Additional downloads: Other downloads available for the e-commerce interface are:
-
static tables
-
item availability and item changes
For more information: See:
-
Downloading E-Commerce Static Tables (ESTF) for an overview of the download process related to the e-commerce interface, including the additional downloads.
-
the E-Commerce Integration Manual for information on the storefront database schema.
In this topic:
Process E-Commerce Downloads Screen
How to display this screen: Enter EOFR in the Fast path field at the top of any menu, or select Download E-Commerce Offer Tables from a menu.
Note:
All records in each related table for your company will be selected for download, unless noted otherwise in the field descriptions.
| Field | Description |
|---|---|
|
Offers |
Indicates whether to include the selected offer information in the e-commerce offer download for subsequent transfer to the web storefront. Only the offer(s) you enter at this screen will be included. Valid values are: Selected (default) = download the selected offer(s). Unselected = do not download the offer(s). |
|
Sources |
Indicates whether to include source codes associated with the selected offer in the e-commerce offer download for subsequent transfer to the web storefront. Only source codes associated with the offer you enter at the From offer field will be included. Valid values are: Selected (default) = include source codes associated with the selected offer. Unselected = do not include source codes. |
|
All items |
Indicates whether to include all items, SKUs, and all other related offer information in the e-commerce offer download for subsequent transfer to the web storefront. All items related to the offer(s) you enter at this screen will be included. Valid values are: Selected (default) = include item-related information for all items associated with the selected offer(s). Unselected = do not include item-related information for all items. Select either this field or the New items field, but not both. If you select both fields, the following message displays: Either All Items or New Items can be processed but not both. |
|
New items |
Indicates whether to include only new items in the e-commerce offer download for subsequent transfer to the web storefront. Only items related to the offer(s) you enter at this screen and which have not previously been extracted will be selected. Valid values are: Selected = The e-commerce offer download includes item-related information for only new items associated with the selected offer(s); only items which have not been previously extracted are included in the download. When you submit the download, the system updates the Ecommerce Extract Date field in the Item Offer table with the date of the extract; the system uses this field to determine whether the item has previously been included in an e-commerce offer download. Unselected (default) = do restrict extract of item-related information to only new items. Select either this field or the All items field, but not both. If you select both fields, the following message displays: Either All Items or New Items can be processed but not both. |
|
Future prices |
Indicates whether to include the latest future price for an item or SKU in the selected offer(s), or the most current price that is not in the future. Valid values are: Selected = include the latest future prices for items and SKUs in the selected offer(s). Future prices are those whose Effective dates are later than the current date. If there are no future prices for an item or SKU, the job includes the latest current price (the most current price whose Effective date is not in the future). Unselected (default) = include the most current price for items and SKUs in the selected offer(s), as long as the prices’ Effective dates are not later than the current date. If there are no prices for an item or SKU except for future prices, the job does not include the item or SKU in the extract. |
|
All SKU offers |
Indicates whether to include all SKU Offers and SKU Prices, regardless of whether they are different from the Item offer and Item Price. Valid values are: Selected = include all existing SKU Offer and SKU Price information, even if it is identical to the corresponding Item Offer and Item Price information. Unselected (default) = include SKU Offer and SKU Price information only if it differs from the Item Offer and Item Price information:
Note: The SKU Price is included only if the regular price differs from the Item Price. If the Associate price alone differs, then the SKU Price record is not selected for extract.
|
|
Offer |
Enter one or more offers to download to the web storefront. As you enter each offer, its description, start date, and end date are displayed below. You must specify at least one offer. Only the offer(s) you enter and tables associated with the selected offer(s) will be included in the extract. Offer codes are defined in and validated against the Offer table. See Working with Offers (WOFR). Alphanumeric, 3 positions; required. |
|
Description |
The description of an offer you have specified for download to the web storefront. Alphanumeric, 30 positions; display-only. |
|
Start date |
The first date when the selected offer is effective for processing orders. From the Offer date range specified for the offer. Numeric, 6 positions (in user date format); display-only. |
|
End date |
The last date when the selected offer is effective for processing orders. From the Offer date range specified for the offer. Numeric, 6 positions (in user date format); display-only. |
Instructions:
| # | Step |
|---|---|
|
1. |
Optionally reset any of the fields to exclude related information from selection. Note: Select either the All items field or the New items field. You cannot select both fields. |
|
2. |
Use the Offer field to specify at least one offer to include in the extract. The system validates each entry, and displays the offer’s description and effective dates below after each. |
|
3. |
Select OK. The system validates your entries and highlights any fields you need to correct. Correct any fields and select OK again. |
|
4. |
Select Submit to submit the extract. The system displays the following message: E-Commerce download submitted to batch. Also, the system submits the job ECOMM_OFR, which extracts the selected information for download. |
|
5. |
The setting of the Generate E-Commerce Offer Tables (M29) system control value defines the output of the e-commerce offer download.
|
Offer-Related Table Extract Summary
Purpose: This table provides a summary of the tables and fields included in the offer table extract, and how they map to the e-commerce staging tables. The system populates these tables when you submit the e-commerce offer download and the Generate E-Commerce Offer Tables (M29) system control value is selected.
Information included: The e-commerce offer download adds new information to the staging tables if the tables have not been cleared since the last download; it does not update records once they are extracted to the staging tables. However, once the records are downloaded to the web storefront and cleared from the staging tables, changes to existing records are eligible to be extracted to the staging tables and passed to the storefront.
Removing items and offers:
All SKUs included? SKU-level information is included in the extract only if there is a SKU Offer and SKU Price record. For example, you can use the Creating Item/SKU Offers (MISO) menu option to create SKU Offers and SKU Prices for an item when you add it to a new offer. SKU-level offer and price information is included in the extract if you select the All SKU offers option when generating the extract; otherwise, SKU Offers are included only when they differ from the item-level information, such as a special handling or price that differs from the Item Offer or Item Price setting.
Future prices? The Future prices field on the Process E-Commerce Downloads screen controls whether to download the most current prices whose Effective dates are not in the future, or the latest prices whose Effective dates are later than the current date. If you would like to download both current and future prices, you can run the download job twice, resetting this field as needed.
Example: AB100 BLUE LRGE has the following prices set up for the selected offer(s):
| Date | Quantity | Price |
|---|---|---|
|
1/1 |
1 |
10.00 |
|
1/1 |
5 |
9.00 |
|
1/10 |
1 |
10.50 |
|
2/1 |
1 |
8.50 |
|
2/1 |
5 |
7.50 |
|
3/1 |
1 |
7.00 |
|
3/1 |
5 |
6.99 |
|
1. The current date is 1/15. If you run the extract job with the Future prices field unselected, the job selects the following prices: |
||
|
1/1 |
5 |
9.00 (most recent price for this quantity not greater than current date) |
|
1/10 |
1 |
10.50 (most recent price for this quantity not greater than the current date) |
|
2. The current date is 1/15. If you run the extract job with the Future prices field selected, the job selects the following prices: |
||
|
3/1 |
1 |
7.00 (latest future price for this quantity) |
|
3/1 |
5 |
6.99 (latest future price for this quantity) |
Note:
-
Item or SKU prices are included in the following extract tables (see Offer-Related Table Extract Summary for more information):
-
EC Item Breakpoint (EXIPRC): includes single-unit and multi-unit prices at the item level
-
EC Offer Item Link (EXIOFL): includes single-unit prices at the item level
-
EC Offer SKU Link (EXSOFL): includes single-unit prices at the SKU level
-
EC SKU Breakpoint (EXSPRC): includes single-unit and multi-unit prices at the SKU level
-
-
If you run the extract job with the Future prices field selected, but there is no future price for the item or SKU, the job extracts the most recent price instead.
-
If you run the extract job with the Future prices field unselected, but there is no current price for an item or SKU and just a future price, the extract omits the item or SKU prices from the extract, with the exception of the EC Offer Item Link table; this table includes a record for the item with a price of 0.
Note:
Clear the records from each target table before running the extract to make sure that you capture the most up-to-date information from Order Administration, as the extract process does not always write updated information in the extract table if there is already an existing record.
Selection for download: All item-related information (such as hazard codes and e-commerce categories) are selected for download only if they are assigned to items that are selected for download.
| OROACS Table | Staging Table | Fields | Additional Information |
|---|---|---|---|
|
If you select the Offer field at the Process E-Commerce Downloads screen: |
|||
|
Offer (MSOFFR) |
EC Offer (EXOFFR) |
Company code Offer code Description Start date End date |
Only the offer(s) you indicate at the screen is/are selected. |
|
If you select the Sources field at the Process E-Commerce Downloads screen: |
|||
|
Source (MSSRC) |
EC Source (EXSRCE) |
Company code Source code Source code description Discount percentage Offer |
|
|
If you select the Items or New Items field at the Process E-Commerce Downloads screen: |
|||
|
Long SKU Class (INLSKC) OR Retail Class (INRTCP) |
EC Class (EXCLAS) |
Company code Long SKU department code Long SKU class code Long SKU class description |
A long SKU class is selected for download only if it is assigned to an item selected for download, and the item is also assigned a long SKU department. The same long SKU class can be downloaded multiple times, if it is paired with different long SKU departments. See Working with Long SKU Classes (WLSC). Note: If you select the Use Retail Integration (H26) system control value, the interface uses the Retail Class table to populate the EC Class table. |
|
Item Coordinate (INITCO) |
EC Cross-Sell (EXCSEL) |
Company code Item code Cross-sell item code Cross-sell description Coordinate sequence Coordinate type |
An item coordinate is selected for download only if the primary item (Note: not necessarily the coordinate item) is selected for download. Also, only the first 50 positions of the item coordinate description (a 70-position field) is included in the Cross-sell description. |
|
Long SKU Department (INLSKD) |
EC Department (EXDEPT) |
Company code Long SKU department code Long SKU department description |
|
|
Shipper/Item (OESHIM) |
EC Extra Shipping (EXSCIT) |
Company code Ship via code Item code Extra shipping charge |
|
|
Hazard (INHZRD) |
EC Hazard (EXHAZA) |
Company code Hazard code Hazard description |
|
|
Item (INITEM) |
EC Item (EXITEM) |
Company code Item code Item description Height Length Width Selling quantity Shipping weight Unit of measure code Vendor number Second language description SKU? (0=N, 1=Y) Active on web? (0=N, 1=Y) Long SKU department Long SKU class Long SKU category Hazard code User fields 1 through 4 Status |
Each non-SKU’d item associated with a selected offer(s) (that is, there is an item/offer assignment) is selected for download. SKU’d items are also included in the selection if they have item/offer assignments. See the Create Item Screen for field descriptions. Note: The Active on web? flag is not currently used. |
|
Item Offer (INIOFR) |
EC Item (EXITEM) |
Allow gift wrap? (0=N, 1=Y) |
See the Create Item Offer Screen. |
|
SKU (INSKU) |
EC Item (EXITEM) |
List price |
The Original price in the EC Item table is from the highest List price for all SKUs. |
|
Item Price (INIPRC) |
EC Item Breakpoint (EXIPRC) |
Company code Offer code Item code Breakpoint quantity Price |
You set up a price breakpoint for a quantity of 1 when you create an item/offer assignment and specify a price. You can also specify additional price breakpoints for larger quantities. The extract includes the latest current or future price (based on the Future prices field), for each item-level quantity breakpoint. See:
Assigning Items/SKUs to Offers for information on working with item/offer assignment, including working with quantity price breaks |
|
Item Offer (INIOFR) |
EC Offer Item Link (EXIOFL) |
Company code Offer code Item code Price (single unit) Special handling price Item alias Gift wrap price Custom special handling format code |
The extract includes the latest current or future price (based on the Future prices field), for each single-unit item-level price. See:
|
|
SKU Offer (INSKOF) |
EC Offer SKU Link (EXSOFL) |
Company code Offer code Item code Short SKU Price (single-unit) Special handling price Gift wrap price Custom special handling format code |
A SKU/offer is selected for download only if the special handling or gift wrap information differs from the item/offer, or if you select the All SKU offers option when generating the extract. The extract includes the latest current or future price (based on the Future prices field), for each single-unit SKU-level price. See:
Note: The Short SKU is stored in the SKU table and is a 7-position number used to identify SKUs and items. You can view the short SKU on display screens in Work with Items (MITM) and Inventory Inquiry (DINI). See Create Item (Base Information) Screen. |
|
SKU (INSKU) |
EC SKU (EXSKU) |
Company code Item code Short SKU (assigned by the system at SKU creation) SKU code SKU description SKU element 1 through 3 SKU element 1 through 3 description Restrict orders? (0=N, 1=Y) User fields 1 through 5 Soldout control code Status UPC codes (first two) |
A SKU is selected for download only if it has a SKU/offer selected for download (because it differs from the item/offer, as described above). Non-SKU’d items are also selected for download in this table. Note: The Short SKU is stored in the SKU table and is a 7-position number used to identify SKUs and items. You can view the short SKU on display screens in Work with Items (MITM) and Inventory Inquiry (DINI). See the Create Item (Base Information) Screen or the Create SKU 1 of 2 (With Overrides) Screen and the Create SKU 2 of 2 (With Overrides) Screen. |
|
SKU Price (INSPRC) |
EC SKU Breakpoint (EXSPRC) |
Company code Offer code Item code Short SKU (assigned by the system at SKU creation) Breakpoint quantity Price |
A SKU price is selected for download only if the SKU-level offer price differs from the Item Offer price, or if you select the All SKU offers option when generating the extract). The extract includes the latest current or future price (based on the Future prices field), for each SKU-level quantity breakpoint. See:
Assigning Items/SKUs to Offers for information on working with item/offer assignment, including working with quantity price breaks |
|
Keyword (INKWDP) |
EC Keyword (EXKEYW) |
Company code Item code Keyword |
|
|
Page Letter Alias (INPLAL) |
EC Alias Item (EXITMA) |
Company code Item code Offer Alias item |
Order Administration includes all item aliases for an item for the offer selected in the e-commerce download. |
|
Item Ship Via Override (INISVO) |
EC Item Ship Via Override (EXISVO) |
Company code Item number Shipping code |
Order Administration only populates this table if records exist in the Item Ship Via Override table for an item. See Working with Item Ship Via Overrides. |
E-Commerce Product Web XML File (ProductWeb)
Purpose: The E-Commerce Product Web XML file (ProductWeb) contains item offer information for the offers selected to download on the Process E-Commerce Downloads Screen. The system generates this file when Downloading E-Commerce Offer Files (EOFR) if the Generate E-Commerce Offer Tables (M29) system control value is unselected.The e-commerce offer download creates a zip file in the OMS-ECOMMERCE container of the FILE_STORAGE table. The zip file contains the E-Commerce Product Web XML File (ProductWeb). Use the file storage API to download the file. See the File Storage API for background.
Name of file: The name of the file is ProductWeb_999_YYMMDDHHMMSS.xml, where 999 is the company code where you submitted the e-commerce offer download and YYMMDDHHMMSS is the date and time when the download occurred. When the file storage API is enabled, the file is saved in a zip file with the same name except for the .zip extension.
XML version: The ECOMMERCE_PRODUCT_XML_VERSION in Working with Customer Properties (PROP) defines the XML version of the ProductWeb XML file.
ECommerce extract date: For each item offer included in the ProductWeb XML file, the system updates the ECommerce Extract Date field in the Item Offer table with the date when the e-commerce offer download was submitted. The system uses this date if you select to include only new items in the e-commerce offer download to determine which items are new and which items have previously been downloaded.
Application log: The system writes any messages related to the e-commerce offer download to the Application Log.
Active procedure: The system creates an active procedure while the ProductWeb file is generated; once the e-commerce offer download process completes, the system clears the active procedure.
More information: See the Order Administration Web Services Guide on My Oracle Support (ID 2953017.1) for more information on the E-Commerce Product Web XML File.