Working with the Data Warehouse Integration in CWSerenade
Purpose: CWSerenade allows you to capture data to download to the CWData data warehouse. You can use the CWData data warehouse to analyze your data; see CWData Overview for a better understanding of how you can use CWData.
If you capture data warehouse transactions, CWSerenade:
• creates records in the DW Capture Transaction table to track each transaction that will be downloaded to the data warehouse. The DW Capture Transaction table acts as a “to do” list for the transactions that require download to the data warehouse. This process saves system resources that would be required to write each data warehouse transaction record at the same time as processing the transactions in CWSerenade. See DW Capture Transaction Table.
• creates records in DW Staging tables to capture non-transactional activity. For example, updates to your company, such as a change in address, are stored in the DW Company table for download. See DW Common Reference Table.
In this topic:
• Which DW Staging Tables are Updated by Each Initial Load Stage?
• Submitting an Incremental Load
• What Data is Captured in the DW Common Reference Table?
• DW Capture Transaction Table
• CWSerenade Activities Captured in the DW Capture Transaction Table
• What Key Data is Captured for each DW Capture Transaction Record?
• Working with Data Warehouse Transactions (WDWT)
• Work with Data Warehouse Transactions Screen
• Process Data Warehouse Changes Window
• Reprocess Data Warehouse Changes Window
Before you can download CWSerenade data to the data warehouse staging tables, you must perform the necessary CWSerenade setup and processing.
Information requiring setup includes:
System Control Value |
Description |
Select this field to capture information on activity in CWSerenade for transfer to the CWData data warehouse. CWSerenade uses the DW Capture Transaction table to track each transaction that should be downloaded to the data warehouse. See DW Capture Transaction Table. |
|
Enter the number of days to retain records in the DW Capture Transaction table before purging them. The Process DW Changes job performs the purge, deleting any records if: Original process date is less than or equal to the current system date - the number of purge days. Example: Today is 11/30, and you have set this value to 5. Any record whose Original process date is 11/25 or earlier is purged. You can also set this job up as part of your periodic processing, or run it on demand. |
|
Suppress Customer Credit Card Information in Data Warehouse (I49) |
Select this field to prevent the system from downloading and storing customer credit card information in the CWData data warehouse. When the system processes a data warehouse load and you suppress customer credit card information, the system does not populate the Last credit card and Last credit card expiration date fields in the DW Customer Sold To table. Because these fields are not populated, the last_credit_card and last_credit_card_expire fields in the Customer table in the data warehouse are not populated. Encryption: If you use credit card encryption and you do not prevent the system from downloading and storing customer credit card information in the CWData data warehouse, the credit card number will not be encrypted, since the information is being sent to an external system. See the Data Security and Encryption Guide for an overview. Tokenization: If you use credit card tokenization and you do not prevent the system from downloading and storing customer credit card information in the CWData data warehouse, the number downloaded may be a token rather than the actual credit card number. |
Select this field if you want the system to pass the hidden tax amount included in the order line price for orders subject to Value Added Tax (VAT) as a separate amount to CWData. • The hidden tax amount defined for the order line is passed to CWData in the Tax field in the DW Order Detail table. • The merchandise amount defined for the order line is passed to CWData in the Price field in the DW Order Detail table and DW Order Detail Activity table. Leave this field unselected if you want the system to pass the hidden tax amount included in the order line price for orders subject to Value Added Tax (VAT) as part of the order line price to CWData. • The hidden tax amount, along with the merchandise amount defined for the order line, is passed to CWData in the Price field in the DW Order Detail table and DW Order Detail Activity table. • The Tax field in the DW Order Detail table remains blank. |
Secured Feature |
Description |
If you allow access to this feature, the user can process the initial load of the data warehouse for CWData. If you prohibit access to this feature, the user cannot advance to the Process Initial Load Screen in the Work with Data Warehouse Transactions menu option. |
Menu Option |
Description |
Allows you to review and download CWSerenade data to the CWData data warehouse. |
Periodic Function |
Description |
Process Data Warehouse Changes (program name DWR0068) |
Builds records in each related DW Staging table based on the key in the DW Capture Transaction table. You can submit this process on demand at the Process Data Warehouse Changes Window in the Work with Data Warehouse Transactions menu option. |
Use the following properties in Working with Customer Properties (PROP) to define whether the Relic job is installed on the CWSerenade application server and runs immediately after the system completes an initial or incremental load, or whether the Relic job is installed as a stand-alone application on the CWData server and at defined intervals monitors the DW Common Reference table for the end-of-file record to trigger the transfer of records.
Note: Define these settings on each of your CWSerenade application servers.
Property Setting |
Description |
RELIC_INI |
The location on the CWSerenade application server where Relic is installed. The recommended directory is C:/Serenade/CWSerenade/CWData/relic/relic.ini, where C is the root drive where CWSerenade is installed. Note: Populating this property setting indicates that the Relic job is installed on the CWSerenade application server and runs immediately after the system completes an initial or incremental load. Important: Leave this setting blank to indicate that the Relic job is installed on the CWData server and at defined intervals monitors the DW Common Reference table for the end-of-file record to trigger the transfer of records. See Running Relic as a Stand-Alone Application in the CWData Reference Guide for more information. |
RELIC_LOG_DIRECTORY |
The location on the CWSerenade application server where the Relic log files are stored. The recommended directory is C:/Serenade/CWSerenade/CWData/relic/logs/, where C is the root drive where CWSerenade is installed. Note: Enter a log directory only if you have installed Relic on the CWSerenade application server. |
Required information: The information you need to update in the relic.ini file includes the email information for error notifications, and the database login and password. The additional information in the file should not be changed.
Setup for email notifications: Complete the following entries in the relic.ini file to generate error notifications related to CWData processing:
• smtp_server: The mail server to generate error notifications for CWData processing.
• from_email: The “from” email address to use for error notifications.
• to_email: The email address to receive error notifications.
Setup for the database login and password: Use the following steps to specify the database, user ID, and password for access to the CWData destination database:
1. On the CWSerenade application server, advance to the C:\Serenade\CWSerenade\CWData\dbconfig folder, where C: is the default drive of the application server.
2. Double-click the runthis.bat file. A window opens for you to enter the database name or IP address:
3. Enter the database name or IP address and click OK. A window opens for you to enter a valid database user ID:
4. Enter the user ID and click OK. A window opens for you to enter the password for the user ID:
5. Enter the password and click OK.
Your entries automatically update the following settings in the relic.ini file:
• jdbc.dst.database: Set to jdbc:sqlserver://SERVERNAME:1433;DatabaseName=cwi_staging;selectMethod=direct, where SERVERNAME is the current server and cwi_staging is your entry in the first window in the steps above.
• jdbc.dst.login: Set to your entry in the second window in the steps above.
• jdbc.dst.password: Set to your entry in the third window in the steps above. Note that the password is encrypted.
If the Use Data Warehouse (G94) system control value is selected, you can download CWSerenade data to the DW Staging tables.
1. Submit an initial load to extract and load CWSerenade data to the CWData data warehouse. See Submitting an Initial Load for instructions and processing details.
2. On a periodic basis, submit an incremental load to extract and load changes. See Submitting an Incremental Load for instructions and processing details.
Note: You can start capturing changes at the same time you perform the initial extract and load. If there is a period when transactions are captured by the initial load and the incremental load; the system captures the latest image during the incremental load and passes the most recent data to the data warehouse.
Important: Before loading data to the CWData data warehouse, it is imperative that you verify the integrity of your CWSerenade data. Make sure you do not have any invalid records or the data in the CWData database will be inaccurate.
Download to staging tables: This flowchart explains how CWSerenade data is downloaded to the CWSerenade DW Staging tables.
You can submit an initial load at the Process Initial Load Screen in Working with Data Warehouse Transactions (WDWT). MICROS recommends you submit the initial load in stages since the load can be time-consuming.
Loading the data in stages: There are 3 categories of records to include in the initial extract and load. You should load each category separately, in the order presented below.
1. Supporting data: Reference information such as vendors, codes, financial, and business structures and the company itself.
2. Customers: You must load all customers before loading orders.
3. Orders: Based on selected date range. Note: The system does not download quotes until they are converted to orders; see Entering Pre-Order Quotes for an overview.
The submitted job runs in the QSYSNOMAX queue so as not to impair the performance of other submitted jobs.
System updates: When you perform an initial load, the system:
1. |
Creates records in the DW Staging tables, based on the category of records you selected to extract and load; see Which DW Staging Tables are Updated by Each Initial Load Stage?. |
2. |
Generates the Data Warehouse Staging Errors Report if you selected to extract orders. This report displays order transactions that contain item or SKU errors. For example, you perform a merge/purge which updates an old order that contains an item or SKU that has since been deleted. Transactions that contain item/SKU errors remain in the DW Capture Transaction table until the errors are corrected. The system does not create any records in these DW Staging tables that are related to the order transaction in error: • DW Order Header • DW Order Detail • DW Order Ship To • DW Order Payment • DW Order Payment Activity • DW Order Additional Charges |
3. |
Creates an end-of-file record in the DW Common Reference table. |
4. |
Starts the Relic process to transfer the data to the cwi_staging database. See Relic Processing. |
Which DW Staging Tables are Updated by Each Initial Load Stage?
This table indicates the DW Staging tables the system updates, based on the category of records you selected to extract and load.
Note: The system does not update the DW Price Change Hist Detl table or DW Price Change Hist Summ table when you perform an initial load, regardless of the selected data to extract.
DW staging table: |
refers to CWSerenade table(s): |
DW Staging tables updated during each selected extract and load: |
|
DW A/R Type |
A/R Type |
DW Accounting Period |
Accounting Period |
DW Common Reference |
The DW Common Reference table is updated by many CWSerenade tables; see What Data is Captured in the DW Common Reference Table? for a list of CWSerenade tables that update the DW Common Reference table. |
DW Company |
Company Company Address |
DW Division |
Division |
DW Entity |
Entity |
DW Geography |
pulls address information from: Customer Sold To Customer Bill To Customer Ship To Company Address Entity Vendor Warehouse |
DW Terms |
Terms |
DW Warehouse |
Warehouse |
DW Staging tables updated when you extract supporting data: |
|
DW A/R Open Item |
A/R Open Item |
DW A/R Payment Detail |
A/R Payment Detail |
DW Correspondence History |
Correspondence History |
DW Customer Bill To |
Customer Bill To Customer Bill To Extended Customer Bill To Phone Number |
DW E-Comm Item Category |
Ecommerce Item Categories |
DW Item |
Item |
DW Item Attribute Assign |
Item Attribute Assignment |
DW Item Trans Activity |
Item Transaction History |
DW Manifest Audit |
Manifest Upload Audit |
DW Offer |
Offer |
DW SKU |
SKU |
DW SKU Cross Reference |
SKU Cross Reference |
DW SKU Offer |
SKU Offer |
DW SKU UPC Note: The UPC code is numeric, 14 positions, right-justified and zero filled. |
Item UPC Note: The UPC code is alphanumeric, 14 positions. |
DW Source |
Source |
DW Vendor |
Vendor Vendor Extended |
DW Vendor History Detail |
A/P Invoice Detail History PO Detail PO Detail Estimated Charge PO Header PO Receipt Vendor Charge Back Detail |
DW Staging tables updated when you extract customer tables: |
|
DW Catalog Request |
Catalog Request |
DW Customer Affinity |
Customer Affinity |
DW Customer Individual |
Customer Individual |
DW Customer Membership |
Customer Membership |
DW Customer Profile |
Customer Profile |
DW Customer Ship To |
Customer Ship To Customer Ship To Extended Customer Ship To Order History Customer Ship To Phone Number |
DW Customer Ship To Ent |
Customer Ship To Entity |
DW Customer Sold To |
Customer Sold To Customer Sold To Extended Customer Sold To Order History Customer Sold To Phone Number |
DW Customer Sold To Email |
Customer Sold To Email |
DW Customer Sold To Ent |
Customer Sold To Entity |
DW Customer Warranty |
Customer Warranty Track |
DW Customer Ownership |
Customer Ownership |
DW Staging tables updated when you extract order tables: |
|
DW Order Additional Chg |
Order Additional Charge |
DW Order Detail |
Order Detail |
DW Order Detail Activity |
Order Line History Note: The system includes only System Delivered Order Line Activity Codes in the data warehouse extract. |
DW Order Header |
Order Header Order Header Extended |
DW Order Payment |
Order Payment Method |
DW Order Payment Activity |
Invoice Payment Method |
DW Order Ship To |
Order Ship To |
Submitting an Incremental Load
If the Use Data Warehouse (G94) system control value is selected, the system creates records in the DW Capture Transaction table, based on CWSerenade activity. The DW Capture Transaction table acts as a “to do” list for the transactions that require download to the data warehouse.
You can submit an incremental load:
• at the Process Data Warehouse Changes Window.
• by submitting the Process Data Warehouse Changes periodic function (program name DWR0068).
When you perform an incremental load, the system:
1. |
Builds the supporting DW Staging tables for non-transactional activity. The supporting tables include information such as codes and categories used to group and classify data. See DW Common Reference Table. |
2. |
Creates records in the DW Staging tables, based on the records in the DW Capture Transaction table. Using the File code and the Key information for each DW Capture Transaction record, the job builds records in the related DW Staging table for each transaction. See DW Capture Transaction Table. |
3. |
Purges DW transaction records, based on the Data Warehouse Transaction Purge Days (H10) system control value. The system checks each record in the DW Transaction table and deletes any record whose Original process date is equal to the current system date minus the number of purge days specified. For example, if you processed a record a week ago, and the number of purge days is 7, the record is now eligible to be purged. |
4. |
Generates the Data Warehouse Staging Errors Report. This report displays order transactions that contain item or SKU errors. For example, you perform a merge/purge which updates an old order that contains an item or SKU that has since been deleted. Transactions that contain item/SKU errors remain in the DW Capture Transaction table until the errors are corrected. The system does not create any records in these DW Staging tables that are related to the order transaction in error: • DW Order Header • DW Order Detail • DW Order Ship To • DW Order Payment • DW Order Payment Activity • DW Order Additional Charges |
5. |
Creates an end-of-file record in the DW Common Reference table. |
6. |
Starts the Relic process to transfer the data to the cwi_staging database. See Relic Processing. |
The system repopulates the DW Common Reference table each time you process changes to the data warehouse. See What Data is Captured in the DW Common Reference Table? to view the non-transactional data captured in the DW Common Reference table.
Field |
Description |
Company |
The company where you submitted the data warehouse load. |
File |
A code used to identify the CWSerenade table containing data to download to the data warehouse. See What Data is Captured in the DW Common Reference Table? for an understanding of the data downloaded to the data warehouse from the DW Common Reference table. |
Key |
A code used to identify the CWSerenade company and data to download to the data warehouse. The File field identifies the CWSerenade table where the data is located. The Key field is then used to identify the record in the table to download. Example: If the File is EXR (exchange reason), the File key identifies the CWSerenade company and exchange reason record to download. See What Data is Captured in the DW Common Reference Table? for an understanding of the file code assigned to each DW capture transaction record, based on the CWSerenade activity. |
Description |
A description of the data captured, based on the File and Key. Example: If the File is EXR (exchange reason) and the File key is 001 (exchange reason code), the Description is the description of the exchange reason code. |
What Data is Captured in the DW Common Reference Table?
This table indicates:
• which CWSerenade table is referenced, based on the File code assigned to the DW common reference record.
• what File key is defined, based on the File code assigned to the DW common reference record.
• what Description is defined, based on the File code assigned to the DW common reference record.
File code: |
Refers to CWSerenade table: |
Menu Option: |
File key: |
Description: |
ADD |
Additional Charge Code |
WADC |
additional charge code |
additional charge description |
ADR |
Add Reason |
WADR |
add reason code |
add reason description |
BNK |
Bank |
WBNK |
bank number |
bank description |
BUY |
Buyer |
WBUY |
buyer code |
buyer name |
CCL |
Customer Class |
WCCL |
customer class code |
customer class description |
CNR |
Cancel Reason |
WCNR |
cancel reason code |
cancel reason description |
CNT |
Country |
WCTY |
country code |
country code description |
DRN |
Dispute Reason |
WDSR |
dispute reason code |
dispute reason description |
ECA |
Ecommerce Category |
WECC |
ecommerce category code |
ecommerce category code |
EXR |
Exchange Reason |
WEXR |
exchange reason code |
exchange reason description |
GZF |
Geographic Zone |
WGZN |
geographic zone code |
geographic zone description |
HAZ |
Hazard Code |
WHAZ |
hazard code |
hazard description |
IAT |
Item Attribute |
WIAT |
item attribute code |
item attribute description |
IAV |
Item Attribute Value |
WIAT |
item attribute value type code + item attribute value code |
item attribute value description |
ICC |
Item Cycle |
WICL |
item cycle code |
item cycle description |
ICL |
Item Class |
WICL |
item class code |
item class description |
IST |
Item Status |
WIST |
item status code |
item status description |
ITC |
Item Category |
WITC |
item category code |
item category description |
LCL |
Location Class |
WLCL |
location class code |
location class description |
LDV |
Long SKU Division |
WLDV |
long SKU division code |
long SKU division description |
LNG |
Language |
WLAN |
language code |
language description |
LOB |
Line of Business |
WLOB |
line of business code |
line of business description |
LSC |
Long SKU Class |
WLSC |
long SKU class code |
long SKU class description |
LSD |
Long SKU Department |
WLSD |
long SKU department code |
long SKU department description |
LST |
List Source |
WLSR |
list source code |
list source description |
MCC |
Mail/Call Code |
WMCC |
mail/call code |
mail/call code description |
OTY |
Order Type |
WOTY |
order type code |
order type description |
PAY |
Pay Type |
WPAY |
pay type code |
pay type description |
PDA |
Profile Data |
WPFL |
profile data code |
profile data description |
POR |
Price Override Reason Code |
WPOR |
price override reason code |
price override reason description |
PRM |
Promotion |
WPRO |
promotion code |
promotion description |
PRP |
Prep Code |
WPRC |
prep code |
prep code description |
RCY |
Recency |
WRCC |
recency code |
recency description |
RTR |
Return Reason |
WRTR |
return reason code |
return reason description |
SCG |
Source Category |
WSCT |
source category code |
source category description |
SEA |
Season |
WSEA |
season code |
season description |
SEO |
SKU Element 1 |
WSK1 |
SKU element 1 code |
SKU element 1 description |
SET |
SKU Element 3 |
WSK3 |
SKU element 3 code |
SKU element 3 description |
SEW |
SKU Element 2 |
WSK2 |
SKU element 2 code |
SKU element 2 description |
SLC |
Soldout Control |
WSLD |
soldout control code |
soldout control description |
SLS |
Salesman |
WSLS |
salesman code |
salesman description |
TRC |
Item Transaction Code |
WITC |
item transaction code |
item transaction description |
TRR |
Item Transaction Reason Code |
WIT1 |
item transaction reason code |
item transaction reason description |
VIA |
Ship Via |
WVIA |
ship via code |
ship via description |
When you perform certain CWSerenade activities, the system creates a record in the DW Capture Transaction table to download to the data warehouse. Each record in the DW Capture Transaction table contains a File code, indicating the CWSerenade table(s) affected by the transaction, the key fields necessary to retrieve transaction details, and which DW Staging table to update. See CWSerenade Activities Captured in the DW Capture Transaction Table to view the types of activities that create a DW capture transaction record.
Note: Direct table updates are not captured in the DW Capture Transaction table.
You can view the records in the DW Capture Transaction table at the Work with Data Warehouse Transactions Screen. The system downloads the records in the DW Capture Transaction table to the DW Staging tables when you submit an initial or incremental load.
Field |
Description |
Company |
The company where the CWSerenade activity occurred. |
Sequence # |
A unique number assigned to each DW capture transaction record. |
File name |
A code identifying the CWSerenade table containing records you wish to download to the data warehouse. This code also indicates which DW Staging table to update when you process an initial or incremental load. Example: If you create a catalog request, the system creates a DW capture transaction record with a File name of CRQ, indicating a change has been made to the Catalog Request table and this record should update the DW Catalog Request table. The File key field identifies the particular CWSerenade company and data, in this example, catalog request record, that has been updated. See What Key Data is Captured for each DW Capture Transaction Record? for an understanding of the file code assigned to each DW capture transaction record, based on the CWSerenade activity. |
File key |
A code used to identify the CWSerenade company and data that has been updated. The File name field identifies the CWSerenade table where the updated data is located and the DW Staging table to update when you process an initial or incremental load. The File key field is then used to identify the record in the table that has been updated. Example: If the File name is CRQ (catalog request), the File key indicates the CWSerenade company and catalog request record that has been updated. See What Key Data is Captured for each DW Capture Transaction Record? for an understanding of the file code assigned to each DW capture transaction record, based on the CWSerenade activity. |
Capture date |
The date the CWSerenade activity occurred which created the DW capture transaction record. |
Capture time |
The time the CWSerenade activity occurred which created the DW capture transaction record. |
Capture type |
A code that indicates whether the CWSerenade activity represents creation of a new record, a change to an existing record, or a delete of an existing record. Valid values are: • A: a new record was added. • C: an existing record was updated. • D: an existing record was deleted. The system creates a delete DW capture transaction record only when you delete a sold to customer or a bill to customer. When you send a delete transaction to the data warehouse, the system deletes the customer record and also deletes associated records that apply to the customer. If the deleted customer was merged into a target customer, the system updates the associated records that apply to the target customer with the information that was defined for the deleted customer. |
Original process date |
The date the DW capture transaction record was originally processed, creating the record in the related DW Staging table. |
Original process time |
The time the DW capture transaction record was originally processed, creating the record in the related DW Staging table. |
Last process date |
The date the DW capture transaction record was last downloaded to the DW Staging table. |
Last process time |
The time the DW capture transaction record was last downloaded to the DW Staging table. |
Captured by |
The CWSerenade program that created the DW capture transaction record. |
CWSerenade Activities Captured in the DW Capture Transaction Table
This table indicates:
• which CWSerenade activities create a DW capture transaction record.
• which CWSerenade tables the DW capture transaction record references.
• what file code is assigned to the DW capture transaction record, based on the CWSerenade table(s) referenced.
See What Key Data is Captured for each DW Capture Transaction Record? to review a table displaying the key data captured for each DW capture transaction record and the DW Staging table updated, based on the file code assigned to the DW capture transaction record.
RDC activity: |
Menu option: |
creates a DW transaction record which refers to CWSerenade table: |
File code: |
create an A/R payment post an A/R refund |
WCRT |
A/R Payment Detail |
APD |
create an A/R open item |
WCRT |
A/R Open Item |
ARI |
enter orders perform batch affinity update perform merge/purge |
OEOM MBAU MMCS |
Customer Affinity |
CAF |
update a sold to or bill to customer delete a sold to or bill to customer |
WCST WCBT OEOM |
Customer Bill To Customer Bill To Extended Customer Bill To Phone Number |
CBT |
create or update a customer sold to email address |
WCST OEOM WCAT |
Customer Sold To Email |
CEM |
enter or bill orders if you track customer history by entity |
OEOM MCON |
Customer Ship To Entity |
CHE |
enter orders if you specify individuals update individuals associated with sold to customers |
WCST OEOM |
Customer Individual |
CIF |
update customer profile data in Work with Customers or while entering orders |
WCST OEOM |
Customer Profile |
CPL |
enter or change catalog requests |
WCAT OEOM |
Catalog Request |
CRQ |
enter or bill orders if you track customer history by entity |
OEOM MCON |
Customer Sold To Entity |
CSE |
create or update ship to customers enter or bill orders |
WCST OEOM MCON |
Customer Ship To Customer Ship To Extended Customer Ship To Order History Customer Ship To Phone Number |
CSH |
create or update a customer membership |
WWCM OEOM |
Customer Membership |
CSM |
create, update, or delete sold to customers enter or bill orders perform merge/purge |
WCST OEOM MCON MMCS |
Customer Sold To Customer Sold To Extended Customer Sold To Order History Customer Sold To Phone Number |
CST |
create or update customer warranty information enter orders |
WCST OEOM |
Customer Warranty |
CWT |
create or update customer ownership information |
WCST |
Customer Ownership |
CSO |
assign an item to an ecommerce category |
MITM |
Ecommerce Item Categories |
EIC |
assign attributes and values to items |
MITM |
Item Attribute Assignment |
IAA |
perform any inventory transaction |
PORC WITI WITB WVCB MPIR MCON |
Item Transaction History |
ITH |
create or update an item |
MITM |
Item |
ITM |
confirm shipments using PC manifest or manually confirm by pick control number |
MCON |
Manifest Upload Audit |
MUA |
create or update offers enter orders (for date of first offer) |
WOFR |
Offer |
OFR |
enter or bill orders add or update the order email address |
OEOM MCON |
Order Additional Charge |
OHD |
Order Payment Method |
|||
Order Detail Order Line History |
|||
Order Header Order Header Extended |
|||
Invoice Payment Method |
|||
Order Ship To |
|||
create or update item/offers or SKU/offers |
MITM MISO MUSO |
SKU Offer |
SKO |
create or update SKU level information |
MITM |
SKU |
SKU |
create or update source codes enter orders |
WSRC OEOM |
Source |
SRC |
create a SKU cross reference |
MITM |
SKU Cross Reference |
SXR |
assign UPC codes to items |
MITM |
SKU UPC |
UPC |
create or update vendors |
WVEN |
Vendor Vendor Extended |
VND |
enter, update, or receive purchase orders enter or process a vendor charge back |
MPOE PORC WVCB |
A/P Invoice Detail History PO Detail PO Detail Estimated Charge PO Header PO Receipt Vendor Charge Back Detail |
What Key Data is Captured for each DW Capture Transaction Record?
This table indicates:
• the key data that is captured in the File key field, based on the CWSerenade table(s) referenced in the File code field for the DW capture transaction record.
• the DW Staging table updated, based on the File code defined for the DW capture transaction record.
File code: |
Refers to CWSerenade table(s): |
File key: |
DW Staging table updated: |
APD |
A/R Payment Detail |
111222222233333, where: 111 is the company code 2222222 is the A/R open item number 33333 is the A/R payment detail sequence number |
DW A/R Payment Detail |
ARI |
A/R Open Item |
1112222222, where: 111 is the company code 2222222 is the A/R open item number |
DW A/R Open Item |
CAF |
Customer Affinity |
1112222222223, where: 111 is the company code 222222222 is the sold to customer number 3 is the affinity type |
DW Customer Affinity |
CBT |
Customer Bill To Customer Bill To Extended Customer Bill To Phone Number |
1112222222, where: 111 is the company code 2222222 is the customer bill to number |
DW Customer Bill To |
CEM |
Customer Sold To Email |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the customer email sequence number |
DW Customer Sold To Email DW Correspondence History |
CHE |
Customer Ship To Entity |
111222222222333444, where 111 is the company code 222222222 is the sold to customer number 333 is the ship to number 444 is the entity number |
DW Customer Ship To Ent |
CIF |
Customer Individual |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the customer individual number |
DW Customer Individual |
CPL |
Customer Profile |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the profile code |
DW Customer Profile |
CRQ |
Catalog Request |
1112222222223333333444444, where: 111 is the company code 222222222 is the customer number 3333333 is the request date 444444 is the request time |
DW Catalog Request |
CSE |
Customer Sold To Entity |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the entity number |
DW Customer Sold To Ent |
CSH |
Customer Ship To Customer Ship To Extended Customer Ship To Order History Customer Ship To Phone Number Customer Shipment History |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the ship to number |
DW Customer Ship To |
CSM |
Customer Membership |
1112222222223333333333444, where: 111 is the company code 222222222 is the customer number 333333333333 is the membership ID 444 is the membership sequence number |
DW Customer Membership |
CSO |
Customer Ownership |
1112222222223333333333, where: 111 is the company code 222222222 is the customer number 3333333333 is the ownership ID |
DW Customer Ownership |
CST |
Customer Sold To Customer Sold To Extended Customer Sold To Order History Customer Sold To Phone Number Customer Shipment History |
111222222222, where: 111 is the company code 222222222 is the sold to customer number |
DW Customer Sold To |
CWT |
Customer Warranty |
111222222222333, where: 111 is the company code 222222222 is the sold to customer number 333 is the customer warranty sequence number |
DW Customer Warranty |
EIC |
Ecommerce Item Categories |
1112222222222223333, where: 111 is the company code 222222222222 is the item number 3333 is the ecommerce item category |
DW Ecomm Item Category |
IAA |
Item Attribute Assignment |
111222222333333444444444444, where: 111 is the company code 222222 is the attribute type 333333 is the attribute value 444444444444 is the item number |
DW Item Attribute Assign |
ITH |
Item Transaction History |
111222222222222333333333333334445555555666666777, where: 111 is the company code 222222222222 is the item number 33333333333333 is the SKU code 444 is the warehouse code 5555555 is the transaction date 666666 is the transaction time 777 is the transaction sequence number |
DW Item Trans Activity |
ITM |
Item |
111222222222222, where: 111 is the company code 222222222222 is the item number |
DW Item |
MUA |
Manifest Upload Audit |
111222222233333334444445566666, where: 111 is the company code 2222222 is the pick control number 3333333 is the batch date 444444 is the batch time 55 is the label number 66666 is the manifest upload audit sequence number |
DW Manifest Audit |
OFR |
Offer |
111222, where: 111 is the company code 222 is the offer code |
DW Offer |
OHD |
Order Additional Charge |
11122222222, where: 111 is the company code 22222222 is the order number |
DW Order Additional Chg |
Order Payment Method |
DW Order Payment DW Order Payment Activity |
||
Order Detail Order Line History |
DW Order Detail DW Order Detail Activity |
||
Order Header Order Header Extended |
DW Order Header |
||
Invoice Payment Method |
DW Order Payment Activity |
||
Order Ship To |
DW Order Ship To |
||
SKO |
SKU Offer |
11122222222222233333333333333444, where: 111 is the company code 222222222222 is the item number 33333333333333 is the SKU code 444 is the offer code |
DW SKU Offer |
SKU |
SKU |
111222222222222, where: 111 is the company code 222222222222 is the item number |
DW SKU |
SRC |
Source |
1112222, where: 111 is the company code 2222 is the source code |
DW Source |
SXR |
SKU Cross Reference |
1112222333333333333333333333333333333, where: • 111 is the company code • 2222 is the SKU cross reference type • 333333333333333333333333333333 is the SKU cross reference number. |
DW SKU Cross Reference |
UPC |
Item UPC |
1112222222222223333333333333344455555555555555, where: 111 is the company code 222222222222 is the item number 33333333333333 is the SKU code 444 is the UPC type 55555555555555 is the UPC code |
DW SKU UPC |
VND |
Vendor Vendor Extended |
1112222222, where: 111 is the company code 2222222 is the vendor number |
DW Vendor |
A/P Invoice Detail History PO Detail PO Detail Estimated Charge PO Header PO Receipt Vendor Charge Back Detail |
DW Vendor History Detail |
| CWData Overview | Contents | SCVs | Search | Glossary | Reports | Solutions | XML | Index | Working with Data Warehouse Transactions (WDWT) |
WDWT OROMS 5.0 2018 OTN