Downloading E-Commerce Offer Tables (EOFR)
Purpose: Use the Download E-Commerce Offer Tables 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.
Note: This menu option 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.
Additional downloads: Other downloads available for the e-commerce interface are:
• static tables
• item availability, gift certificates, and item changes
• B2B-related information, including selected source codes, price tables, and customer and individual information
Removing items and offers: Use the Removing Offers and Items from Download (ERMV) menu option to specify offers, items, or SKUs that are no longer active on the web storefront, so that you do not continue sending periodic updates.
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: MICROS recommends that you clear the records from each target table before running the extract to make sure that you capture the most up-to-date information from CWSerenade, as the extract process does not always write updated information in the extract table if there is already an existing record.
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
• Downloading B2B E-Commerce Static Tables (BBED) for information on the B2B download option, which you can use to extract source code, price table, customer user fields, and individual customer-related information
• the CWSerenade E-Commerce Integration Manual for information on the storefront database schema
In this topic:
• Process E-Commerce Downloads Screen
• Offer-Related Table Extract Summary
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 extract the selected offer information to the EC Offer table, and from there to the web storefront. Only the offer(s) you enter at this screen will be extracted. Valid values are: Selected (default) = download the selected offer(s) Unselected = do not extract the offer(s) |
Sources |
Indicates whether to extract source codes associated with the selected offer to the EC Source table, and from there to the web storefront. Only source codes associated with the offer you enter at the From offer field will be extracted. Valid values are: Selected (default) = extract source codes associated with the selected offer Unselected = do not extract source codes Note: You can also use the Downloading B2B E-Commerce Static Tables (BBED) menu option to extract source code information to another staging table. |
All items |
Indicates whether to extract all items, SKUs, and all other related e-commerce staging tables, and from there to the web storefront. All items related to the offer(s) you enter at this screen will be extracted. Valid values are: Selected (default) = extract item-related information for all items associated with the selected offer(s) Unselected = do not extract 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 extract only new items, SKUs, and other related information to the e-commerce staging tables for subsequent download 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 = extract item-related information for only new items associated with the selected offer(s) (items which have not been previously extracted) 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. |
Indicates whether to extract 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 = extract 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 extracts the latest current price (the most current price whose Effective date is not in the future). Unselected (default) = extract 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. See above under Downloading E-Commerce Offer Tables (EOFR) for a discussion and examples. |
|
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 = extract all existing SKU Offer and SKU Price information, even if it is identical to the corresponding Item Offer and Item Price information. Unselected (default) = extract SKU Offer and SKU Price information only if it differs from the Item Offer and Item Price information: • The EC SKU Breakpoint table (EXSPRC) is updated with the SKU Price information only if the SKU Price differs from the Item Price. 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. • The EC Offer SKU Link table (EXSOFL) is updated with the SKU Offer information only if the special handling or gift wrap information for the SKU Offer is different from the Item Offer. |
|
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 (MM/DD/YY 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 (MM/DD/YY format); display-only. |
Instructions:
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 to the related e-commerce staging tables. See Offer-Related Table Extract Summary for a listing of the information included in the extract.
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.
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.
CWSerenade 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 |
See Working with Source Codes (WSRC). Also, see Downloading B2B E-Commerce Static Tables (BBED) for information on downloading source codes as part of B2B functionality. |
If you select the Items or New Items field at the Process E-Commerce Downloads screen: |
|||
E-Commerce Category (IXECAT) |
EC Category (EXECAT) |
Company code E-Commerce category code Description |
|
Item E-Commerce Category (ECICAT) |
EC Item Category (EXITCT) |
Company code Item code E-Commerce category code |
|
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 |
|
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. |
Item Information (INIINF) |
EC Item (EXITEM) |
E-commerce item information (RP template) = ECI description long |
|
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. For more information: See: • above under Downloading E-Commerce Offer Tables (EOFR) for a discussion of current or future prices • the Create Item Offer Screen for information on assigning items to offers at item creation • Assigning Items/SKUs to Offers for information on working with item/offer assignment, including working with quantity price breaks |
E-Comm Item Image (ECIIMG) or E-Commerce Image Path (ECIMGP) |
EC Item Image (EXIMAG) |
Company code Item code Thumbnail image path Regular-size image path Large image path |
If you specify item image paths and filenames at the item level, this information is selected for the download; otherwise, the system builds the item image path and filename using the defaults set up in E-Commerce Job Control. See Working with E-Commerce Job Control (EJCT) for information on setting up defaults. |
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. For more information: See: • above under Downloading E-Commerce Offer Tables (EOFR) for a discussion of current or future prices • the Create Item Offer Screen for information on assigning items to offers at item creation |
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. For more information: See: • above under Downloading E-Commerce Offer Tables (EOFR) for a discussion of current or future prices • the Create SKU Offer Screen for information on assigning SKUs to offers 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 Gift certificate? (0=N, 1=Y) 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. For more information: See: • above under Downloading E-Commerce Offer Tables (EOFR) for a discussion of current or future prices • Note above for information on Short SKUs • the Create SKU Offer Screen for information on assigning SKUs to offers • Assigning Items/SKUs to Offers for information on working with item/offer assignment, including working with quantity price breaks |
E-Comm Up/Cross-Sell (ECSELL) |
EC Up-Sell (EXUSEL) |
Company code Item code Upsell item code Upsell description |
Only records with a type of U (upsell) are currently created in the E-Comm Up/Cross-Sell table and selected for download. See the Work with E-Commerce Upsell Screen. |
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 |
CWSerenade 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 |
CWSerenade only populates this table if records exist in the Item Ship Via Override table for an item. See Working with Item Ship Via Overrides. |
