Orders Released from Time Hold Report | Contents | SCVs | Search | Glossary | Reports | Solutions | XML | Index | Marketing Download Order Header Interface Summary Report |
Working with the Marketing Download Extract
Purpose: The Marketing Download Extract allows you to download order and customer information from Order Management System to an external system.
To download order and customer-related data: The system creates records in the Marketing Download Trigger table as a result of certain actions in Order Management System. You can then:
• Run the MDEXTR Marketing Download Order and Customer Extract (CSX1041) periodic function to extract order and customer-related records in the Marketing Download Trigger table to the appropriate Marketing Download table.
• Run the MDEXPRT Marketing Download Export (PFR0130) periodic function to extract the data in the Marketing Download tables to a pipe delimited text file.
The text file is then either:
• Placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property, if the File Storage API is not enabled for exports; or,
• Included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, if the File Storage API is enabled for exports.
For more information: See Creating Marketing Download Trigger Records and Populating the Marketing Download Tables and Extract Files, and see File Storage API for background on using the file storage API.
Marketing Download reports: You can print reports to review the data in the Marketing Download tables before the data is downloaded to an external system; see Setting up the Marketing Download Extract for more information.
Note: Because the Marketing Download extract process clears old trigger records from the table, back up the contents of the table before running the extract if you will need the data later.
Marketing download process:
In this chapter:
• Setting up the Marketing Download Extract
• Marketing Download Extract Process
- Creating Marketing Download Trigger Records
- Marketing Download Trigger Table
• Populating the Marketing Download Tables and Extract Files
- Marketing Download Order Header Table and Extract File
- Marketing Download Order Detail Table and Extract File
- Marketing Download Customer Status Change Table and Extract File
- Marketing Download Customer Inquiry Table and Extract File
- Marketing Download Customer Address Change Table and Extract File
- Marketing Download Customer Ownership Table and Extract File
- Marketing Download Customer Profile Table and Extract File
• Marketing Download Order Header Interface Summary Report
• Marketing Download Order Detail Interface Summary Report
• Marketing Download Customer Status Change Interface Report
• Marketing Download Customer Inquiry Interface Report
• Marketing Download Change of Address Interface Report
Setting up the Marketing Download Extract
System control values: The following system control values control trigger creation logic:
• Populate Marketing Download Trigger File (G33): controls the type(s) of triggers to create.
• Include All Customer Inquiry Triggers for Marketing Download (I09): controls whether to create customer inquiry triggers if there are corresponding order header triggers.
Periodic functions: The Marketing Download extract uses the following periodic functions:
• MDEXTR Marketing Download Order and Customer Extract (CSX1041) populates the Marketing Download order-related and customer-related tables based on the records in the Marketing Download Trigger table.
• MDEXPRT Marketing Download Export (PFR0130) exports the data in the Marketing Download order-related and customer-related tables to pipe-delimited files and places the files in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property. Once the extract files are created, the system deletes the records in the associated Marketing Download tables.
How to set up: To set up the Marketing Download periodic functions for periodic processing:
• Use Working with Periodic Functions (WPER) to set up each of the required Marketing Download periodic functions (for example, enter CSR1033 as the Program name for the function that populates the Marketing Download Source Download table).
• Use Working with Periodic Processes (WPPR) to assign each of the periodic functions to a periodic process.
• Use Scheduling Jobs to indicate when and how often to run the periodic process.
Generating reports: You can also set up periodic functions for the Marketing Download reports and assign them to the Marketing Download periodic process:
• MDADRCG (CSX1069): Marketing Download Change of Address Interface Report
• MDCSCHG (CSX1061): Marketing Download Customer Status Change Interface Report
• MDINQSM (CSX1066): Marketing Download Customer Inquiry Interface Report
• MDORDHD (CSX1044): Marketing Download Order Header Interface Summary Report
• MDORDTL (CSX1058): Marketing Download Order Detail Interface Summary Report
Note: To make sure the report prints correctly, select an option that reduces the page or fits the content to the printer margins at the Print dialog box.
Marketing Download Extract Process
Creating Marketing Download Trigger Records
Which activity creates a trigger? The setting of the Populate Marketing Download Trigger File (G33) system control value determines which type of activity in Order Management System causes the system to populate the Marketing Download Trigger table. Valid values are:
• ORDER: order entry or maintenance
• CUSTOMER: customer maintenance or catalog requests
• ORD/CUST: order entry or maintenance, customer maintenance, or catalog requests
Marketing download trigger type: When the system creates a record in the Marketing Download Trigger table, the record is assigned a trigger type based on the type of activity that was performed. The trigger type determines which fields are populated in the Marketing Download Trigger table and which Marketing Download table is populated when you run the marketing download trigger program. The following table lists the trigger type created based on the setting of the Populate Marketing Download Trigger File (G33) system control value and the action you performed.
SCV value: |
creates trigger type: |
when you: |
trigger includes: |
Marketing Download table populated: |
ORDER or ORD/CUST |
OH (order header) |
• create a new order in order entry or accept an order in batch order entry. A separate record is created for each order ship-to. • add a new ship-to address (using Accept/Add Rcp) in order maintenance. |
Customer sold to # Customer ship to # (if a permanent ship-to address is used) Entity Order # Order ship-to # |
Marketing Download Order Header Table and Extract File, Marketing Download Order Detail Table and Extract File Marketing Download Customer Inquiry Table and Extract File (only for a recipient customer) |
ORDER or ORD/CUST |
LH (order line history) |
create order line history (for example, adding, cancelling, changing, exchanging, returning, shipping, or selling out an item on an order; or billing an order). Note: The system does not create an LH record if the original OH record for the order is in the Marketing Download Trigger table. |
Order # Order ship-to # Order detail sequence # Order line history sequence # |
|
CUSTOMER or ORD/CUST |
CI (customer inquiry) |
• create a catalog request. • create a new customer in order entry, even if you don't accept the order. • create a new customer entity record, if the Track Customer History at Entity Level (F89) system control value is selected. |
Customer sold to # Entity #, if the activity was associated with a specific entity (for example, creating a new customer in order entry) |
|
CUSTOMER or ORD/CUST |
CO (customer ownership) |
create a customer ownership record. |
Customer sold to # |
Marketing Download Customer Ownership Table and Extract File |
CUSTOMER or ORD/CUST |
CS (customer status change) |
change one or more of these fields for a customer sold-to: • Mail name • Customer sold-to entity mail name • Rent name • Deliverable code (not delivery code) • Hold/bypass/fraud • Customer class • Mail code • Call code • Email opt in/opt out Note: The system may change one of the above fields when you create a catalog request for an existing customer. |
Customer sold to # Entity #, if the activity was related to a specific entity |
Marketing Download Customer Status Change Table and Extract File |
CUSTOMER or ORD/CUST |
CA (customer address change) |
change a customer sold-to or change or create a permanent customer ship-to address, including phone numbers and email address. |
Customer sold to # Customer ship to, if the activity was related to a ship-to address |
Marketing Download Customer Address Change Table and Extract File |
CUSTOMER or ORD/CUST |
CN (customer action note) |
create, work with, or resolve a customer action note. Note: The system populates the Marketing Download Customer Inquiry table only if a reason has been defined for the customer action note. |
Customer sold to # Customer action sequence # |
|
CUSTOMER or ORD/CUST |
CP (customer profile) |
create or change a customer profile record. |
Customer sold to # |
|
In addition to the fields listed above, each marketing download trigger record also includes company number, trigger type, trigger sequence number, status, date created, and time created. |
When is the trigger created? Each of the trigger record types indicated are created when the update in the system is complete. For an interactive process, such as creating or updating a customer in order entry, the trigger is created immediately. For an update that is handled by an asynchronous job, such as order creation, the trigger is not created until the job processing is complete.
Marketing Download Trigger Table
The system creates a a marketing download trigger record based on the Populate Marketing Download Trigger File (G33) system control value and the action you perform.
Field |
Description |
Company |
The code for the company where you performed the action that created the marketing download trigger. OH: the company from the Order Header table. LH: the company from the Order Line History table. CI: the company from the Catalog Request table. CN: the company from the Customer Action Note table. CO: the company from the Customer Ownership table. CP: the company from the Customer Profile table. CS: the company from the Customer Sold To table. CA: the company from the Customer Sold To table. Numeric, 3 positions; required. |
Trigger type |
The type of action that created the marketing download trigger. Valid values are: OH = order header LH = order line history CI = customer inquiry CO = customer ownership CP = customer profile CS = customer status change CA = customer address change CN = customer action note Alphanumeric, 2 positions; required. |
Trigger seq # |
A unique sequence number to distinguish records with the same company number and trigger type. Numeric, 7 positions; required. |
Customer Sold To |
The customer sold-to number associated with the activity that created the trigger. Populated for trigger types OH (order header), CS (customer status change), CI (customer inquiry), CA (customer address change), CO (customer ownership), CP (customer profile), and CN (customer action note). OH: the customer sold-to number from the Order Header table. CI: the customer sold-to number from the Catalog Request table. CN: the customer sold-to number from the Customer Action Note table. CO: the customer sold-to number from the Customer Ownership table. CP: the customer sold-to number from the Customer Profile table. CS: the customer sold-to number from the Customer Sold To table. CA: the customer sold-to number from the Customer Sold To table. Numeric, 9 positions; optional. |
Customer Ship To |
The customer ship-to number associated with the activity that created the trigger. Populated only for trigger types OH (order header) and CA (customer address change). The OH trigger type includes customer ship-to only if a permanent ship-to address is used on the order. The CA trigger type includes customer ship-to only if a permanent ship-to address was created or changed. OH: The customer ship-to number from the Order Ship To table. CA: The customer ship-to number from the Customer Ship To table. Numeric, 3 positions; optional. |
Customer action seq # |
The sequence number associated with the activity that created the trigger. Populated for trigger type CN (customer action note). The customer action sequence number from the Customer Action table. Numeric, 2 positions; optional. |
Entity |
The entity associated with the activity that created the trigger. Populated only for trigger types OH (order header), CS (customer status change) or CI (customer inquiry). OH: The entity from the Division table for the division associated with the source code on the order header. CI: The entity from the Division table for the division associated with the source code on the catalog request. CS: The entity from the Customer Sold To Entity table. Numeric, 3 positions; optional. |
Order # |
The order number associated with the activity that created the trigger. Populated only for trigger types OH (order header) or LH (order line history). OH: The order number from the Order Header table. LH: The order number from the Order Line History table. Numeric, 8 positions; optional. |
Order ship to # |
The ship-to number on the order associated with the activity that created the trigger. Populated only for trigger types OH (order header) or LH (order line history). OH: The order ship-to number from the Order Ship To table. LH: The order ship-to number from the Order Line History table. Numeric, 3 positions; optional. |
Order detail seq # |
The order line associated with the activity that created the trigger. Populated only for trigger type LH (order line history). The order detail sequence number from the Order Line History table. Numeric, 5 positions; optional. |
Order line hist seq # |
The order line history record associated with the activity that created the trigger. Populated only for trigger type LH (order line history). The order line history sequence number from the Order Line History table. Numeric, 2 positions; optional. |
Warehouse |
The code for the warehouse associated with the activity that created the trigger. Not populated for any of the trigger types. Numeric, 3 positions; optional. |
PO number |
The purchase order number associated with the activity that created the trigger. Not populated for any of the trigger types. Numeric, 7 positions; optional. |
PO detail seq # |
The purchase order line number associated with the activity that created the trigger. Not populated for any of the trigger types. Numeric, 5 positions; optional. |
Status |
Indicates whether the trigger has been downloaded to the Marketing Download tables. U = unprocessed; the trigger has not been downloaded to the Marketing Download tables. P = processed; the trigger has been downloaded to the Marketing Download tables. The periodic function deletes the marketing download trigger record once it is extracted to the Marketing Download tables. Alphanumeric, 1 position; required. |
Date created |
The date the trigger was created; this is the date when the activity was performed that created the trigger record. Numeric, 7 positions (CYYMMDD format); required. |
Time created |
The time the trigger was created; this is the time when the activity was performed that created the trigger record. Numeric, 6 positions (HHMMSS format); required. |
Date processed |
The date the trigger was downloaded to the Marketing Download tables. Numeric, 7 positions (CYYMMDD format); required. |
Populating the Marketing Download Tables and Extract Files
You can run the MDEXTR Marketing Download Order and Customer Extract (CSX1041) periodic function to create records in the Marketing Download tables.
You can also run the periodic function on demand through Working with Periodic Processes (WPPR).
If there is already data in the download tables: If you run the Marketing Download (CSX1041) periodic function while there are still records in the Marketing Download order or customer tables, the function writes an error to the APP.log: Data already exists in Marketing Download files. Please clear the files before running Download. See the Application Log for information on how to review this log.
You must first download or delete the records that are already in the Marketing Download order and customer tables before you can populate these tables with new information.
Order and customer-related tables: The MDEXTR Marketing Download Order and Customer Extract (CSX1041) periodic function creates records in these Marketing Download tables:
• Marketing Download Order Header Table and Extract File
• Marketing Download Order Detail Table and Extract File
• Marketing Download Customer Status Change Table and Extract File
• Marketing Download Customer Inquiry Table and Extract File
• Marketing Download Customer Address Change Table and Extract File
• Marketing Download Customer Ownership Table and Extract File
• Marketing Download Customer Profile Table and Extract File
During processing, the function changes the status of each selected trigger record to P (processed). Once the Marketing Download records are created, the function deletes the processed marketing download triggers.
You can then run the MDEXPRT Marketing Download Export (PFR0130) periodic function to export the data in the Marketing Download order-related and customer-related tables to pipe-delimited files. The text file is either placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property or included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, depending on whether the FILE_STORAGE_EXPORTS_ENABLED property is selected. See File Storage API for background.
The table below indicates the validation performed for different unprocessed (status = U) trigger types in the Marketing Download Trigger table.
For trigger type: |
the Marketing Download function: |
OH (order header) |
• retrieves the order header and order ship-to based on the trigger record’s company, order number, and order ship-to number. Note: The system includes orders that are on hold. • creates a record in the Marketing Download Order Detail Table and Extract File for each order line associated with the retrieved order ship-to number. The function does not create a Marketing Download order detail record if the order quantity is 0 (for example, the order line has been cancelled with a reason code that reduces demand). • determines if the order ship-to is for a recipient customer. If so, the function also creates a record in the Marketing Download Customer Inquiry Table and Extract File, using the customer sold-to number for the order ship-to. |
LH (order line history) |
• determines if there is an OH (order header) trigger for the same company, order number, and order ship-to number for the LH (order line history) trigger. If there is an OH trigger, the function deletes the order line history trigger. • retrieves the order line history record based on the trigger’s company, order number, order ship-to number, order detail sequence number, and order line history sequence number. • determines if the quantity for the order line history record is 0. If the quantity is 0, the function deletes the order line history trigger. • creates a record in the Marketing Download Order Detail Table and Extract File. |
CS (customer status change) |
• determines if there is an OH (order header) trigger for the same company, customer sold-to, and entity for the CS (customer status change) trigger. If so, the function deletes the customer status change trigger. • retrieves the customer sold-to and customer sold-to entity based on the trigger’s company and customer sold-to. • creates a record in the Marketing Download Customer Status Change Table and Extract File. |
CI (customer inquiry) |
• determines if there is an OH (order header) trigger for the same company, customer sold-to, and entity for the CI (customer inquiry) trigger. If so, the function looks at the Include All Customer Inquiry Triggers for Marketing Download (I09) system control value. If this system control value is selected, the function continues processing the CI trigger; if this system control value is unselected, the function deletes the customer inquiry trigger. • retrieves the customer sold-to and customer sold-to entity based on the trigger’s company and customer sold-to. • creates a record in the Marketing Download Customer Inquiry Table and Extract File. |
CN (customer action note) |
• retrieves the customer action record based on the trigger’s company, customer sold-to, and customer action sequence number. • determines if there is a reason code for the customer action record; if not, the function deletes the customer action note trigger. • creates a record in the Marketing Download Customer Inquiry Table and Extract File. |
CO (customer ownership) |
• retrieves the customer ownership records based on trigger record’s company and customer sold-to. • creates a record in the Marketing Download Customer Ownership Table and Extract File. |
CP (customer profile) |
• retrieves the customer profile records based on trigger record’s company and customer sold-to. • creates a record in the Marketing Download Customer Profile Table and Extract File. |
CA (customer address change) |
• determines if there is an OH (order header) trigger for the same company, customer sold-to, and customer ship-to as the CA (customer address change) trigger record. If there is a matching OH trigger, the function deletes the customer address change record. • retrieves the customer sold-to or customer ship-to based on the trigger’s company, customer sold-to number, and customer ship-to number. • creates a record in the Marketing Download Customer Address Change Table and Extract File. |
When you extract the records in the Marketing Download Trigger table to the Marketing Download order and customer tables, you can also print reports to review the data in the Marketing Download tables; see Setting up the Marketing Download Extract for setup information.
Marketing Download Order Header Table and Extract File
Marketing Download Order Header table: The MDEXTR Marketing Download Order and Customer Extract (CSX1041) periodic function creates a record for each marketing download trigger record with a trigger type of order header (OH); see Populating the Marketing Download Tables and Extract Files for more information on how the function determines when to create a record in the Marketing Download Order Header table.
Note: The MDEXTR periodic function also creates a record in the Marketing Download Order Detail Table and Extract File for each order detail line associated with the order ship-to in the Marketing Download Order Header table.
Marketing Download Order Header Export file: The MDEXPRT Marketing Download Export (PFR0130) periodic function creates a pipe-delimited text file based on the records in the Marketing Download Order Header table.
Name of file: The name of the file is 999.ORDER_HEADER.DATETIME.TXT, where 999 is the company code where you submitted the MDEXPRT periodic function and DATETIME is the date and time when the extract occurred, including an abbreviation, such as EDT, indicating the time zone. For example: 7.ORDER_HEADER.Mon-Apr-25-11-28-03-EDT-2016.TXT.
Location of file: The Marketing Download Order Header Export text file is either placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property or included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, depending on whether the FILE_STORAGE_EXPORTS_ENABLED property is selected. The zip file in the FILE_STORAGE table has the same name as the text file that it contains, except for the ZIP extension.
Example of file: In this example, the first row is a header record indicating the name of each column and the second row contains the actual data; see the Marketing Download Order Header Table for a description of the contents of this file.
Company|Order#|Ship To#|Customer#|Entity|CST Prefix|Gender Code|CST Last Name|CST First Name|CST Initial|CST Company Name|CST Internet Address|CST Address Line 1|CST Address Line 2|CST Apartment|CST City|CST State|CST Zip|CST Country|CST Phone #1 Day|CST Phone #2 Eve|CST Phone #3 Fax|Customer Class|CST Mail name?|CSE Mail name?|CST Rent name?|CST Mail code 1|CST Call code 1|Order Amount|Order Date|Payment Code #1|Payment Code #2|Order Type|Offer|Source Code|Ship Via|OST Customer#|OST CSH Ship To#|OST Recipient#|Gift Flag|Ship To Flag|Station ID|CST Deliverable Code|FPO Type|CST Hold/Bypass/Fraud|CSH Prefix|CSH Last Name|CSH First Name|CSH Initial|CSH Company Name|CSH Internet Address|CSH Address Line 1|CSH Address Line 2|CSH Apartment|CSH City|CSH State|CSH Zip|CSH Country|CSH Phone #1 Day|CSH Phone #2 Eve|CSH Phone #3 Fax|Alternate Customer#|CSH Address line 3|CSH Address line 4|Email Sts
7|19|1|4|777|||BROWN|THOMAS|R|||1234 SAMPLE STREET|||WESTBOROUGH|MA|01581|US|5083310100|5083310101|0||Y||Y|Y||303.96|20160425|4|0|P|OFR|SOURCE|1|0|0|0|N||0|||||||||||||||||0|0|0||||O1
Note: When you run the MDEXPRT Marketing Download Export periodic function, the system creates the Marketing Download Order Header Export file even if there is no data to extract from the Marketing Download Order Header table; in this situation, the text file contains only a header record indicating the column names.
Marketing Download Order Header Table
Field |
Description |
Company |
The code for the company where the order is located. From the Company in the Order Header table. Numeric, 3 positions. |
Order number |
The order number associated with the order you created or for which you created a new ship-to address. From the Order number in the Order Header table. Numeric, 8 positions. |
Order ship to number |
The order ship-to number associated with the order you created or for which you created a new ship-to address. From the Order ship to number in the Order Ship To table. Numeric, 3 positions. |
Customer number |
The sold-to customer on the order. From the Customer number in the Order Header table. Numeric, 9 positions. |
Entity |
The entity associated with the order. From the Entity number in the Division table for the division associated with the source code on the order header. Numeric, 3 positions. |
Name prefix |
The prefix of the sold-to customer. From the Prefix in the Customer Sold To table for the customer number on the order header. Alphanumeric, 3 positions. |
Gender code |
The gender code for the sold-to customer. From the Profile data code in the Customer Profile table for the customer number on the order header. The system uses the Default Male/Female Profile Code (C74) system control value as the gender code. Alphanumeric, 1 position. |
Last name |
The last name of the sold-to customer. From the Last name in the Customer Sold To table for the customer number on the order header. Alphanumeric, 25 positions. |
First name |
The first name of the sold-to customer. From the First name in the Customer Sold To table for the customer number on the order header. Alphanumeric, 15 positions. |
Initial |
The middle initial of the sold-to customer. From the Initial in the Customer Sold To table for the customer number on the order header. Alphanumeric, 1 position. |
Company name |
The company name of the sold-to customer. From the Company name in the Customer Sold To table for the customer number on the order header. Alphanumeric, 30 positions. |
Internet address |
The email address of the sold-to customer. From the Email in the Customer Sold To table for the customer number on the order header. Alphanumeric, 50 positions. |
Address line 1 |
The sold-to customer’s street address. From the Street address in the Customer Sold To table for the customer number on the order header. Alphanumeric, 32 positions. |
Address line 2 |
Address line 2 of the sold-to customer’s address. From the Address line 2 in the Customer Sold To table for the customer number on the order header. Alphanumeric, 32 positions. |
Apartment |
The apartment of the sold-to customer’s address. From the Apartment in the Customer Sold To table for the customer number on the order header. Alphanumeric, 10 positions. |
City |
The city of the sold-to customer’s address. From the City in the Customer Sold To table for the customer number on the order header. Alphanumeric, 25 positions. |
State |
The state code of the sold-to customer’s address. From the State in the Customer Sold To table for the customer number on the order header. Alphanumeric, 2 positions. |
Zip |
The postal code of the sold-to customer’s address. From the Zip in the Customer Sold To table for the customer number on the order header. Alphanumeric, 10 positions. |
Country |
The country code of the sold-to customer’s address. From the Country in the Customer Sold To table for the customer number on the order header. Alphanumeric, 3 positions. |
Phone number 1 day |
The sold-to customer’s day phone number. From the Phone in the Customer Sold To Phone Number table for phone type D. Numeric, 10 positions. |
Phone number 2 evening |
The sold-to customer’s evening phone number. From the Phone in the Customer Sold To Phone Number table for phone type E. Numeric, 10 positions. |
Phone number 3 fax |
The sold-to customer’s third (fax or mobile) number. From the Phone in the Customer Sold To Phone Number table for phone type F. Alphanumeric, 10 positions. |
Customer class |
The class of the sold-to customer. From the Customer class in the Customer Sold To table for the customer number on the order header. Alphanumeric, 2 positions. |
Mail name? |
Indicates whether the sold-to customer receives future catalogs. selected = mail catalogs to the customer. unselected = Do not mail catalogs to the customer. From the Mail name in the Customer Sold To table for the customer number on the order header. |
Customer entity mail name? |
Indicates whether the sold-to customer receives future catalogs. This field is used if the Track Customer History at Entity Level (F89) system control value is selected. Selected = mail catalogs to the customer. Unselected = Do not mail catalogs to the customer. This field is unselected if there is no customer information at the entity level (the Track Customer History at Entity Level (F89) system control value is unselected). From the Mail name in the Customer Sold To Entity table for the customer number on the order header and the entity for the division associated with the source code on the order header. |
Rent name? |
Indicates whether you can include the customer’s name in lists you sell to other companies for their own catalog mailings. Selected = Sell the customer’s name to another company. Unselected = Do not sell the customer’s name to another company. From the Rent name in the Customer Sold To table for the customer number on the order header. |
Mail code |
Indicates how often, and under what conditions, to send mail to the customer. From the Mail code in the Customer Sold To table for the customer number on the order header. Alphanumeric, 3 positions. |
Call code |
Specifies how often, and under what conditions, to call the customer. From the Call code in the Customer Sold To table for the customer number on the order header. Alphanumeric, 3 positions. |
Order amount |
The total merchandise amount on the order for the order ship-to, including soldout and cancelled order lines if the cancel reason code has Reduce demand unselected. The system multiplies the Quantity ordered by the Price from the Order Detail table for the order shipping address to determine the merchandise amount. Numeric, 9 positions with a 2-place decimal. |
Order date |
The date the order was created. From the Order date in the Order Header table. Numeric, 8 positions (YYYYMMDD format). |
Payment code 1 |
The code identifying the first form of payment on the order, based on charge sequence. The payment method must be active. From the Pay type code in the Order Payment Method table for the payment on the order with a charge sequence of 1. Numeric, 2 positions. |
Payment code 2 |
The code identifying the second form of payment on the order, based on charge sequence. The payment method must be active. From the Pay type code in the Order Payment Method table for the payment on the order with a charge sequence of 2. Numeric, 2 positions. |
Order type |
The order type of the order, such as phone or mail. From the Order type in the Order Header table. Alphanumeric, 1 position. |
Offer code |
The offer code associated with the source code on the order header. From the Offer in the Offer table for the source code on the order header. Alphanumeric, 3 positions. |
Source code |
The source code on the order header. From the Source code in the Order Header table. Alphanumeric, 9 positions. |
Ship via |
The ship via code on the order ship-to. From the Ship via in the Order Ship To table. Numeric, 2 positions. |
Customer number |
The sold-to customer number on the order. Populated only if a permanent ship-to customer has been selected for the sold-to customer on the order (the Ship To option in order entry). From the Customer number in the Order Ship To table. Numeric, 9 positions. |
Order ship to number |
The permanent ship-to customer number for the sold-to customer on the order. Populated only if a permanent ship-to customer has been selected for the sold-to customer on the order (the Ship To option in order entry). From the Ship to number in the Order Ship To table. Numeric, 3 positions. |
Recipient number |
The recipient customer number on the order. Populated only if a recipient customer number has been selected for the sold-to customer on the order (Sold To/Recip in order entry). From the Customer number in the Order Ship To table. Numeric, 9 positions. |
Gift flag |
Identifies a gift order. Selected = This is a gift order. Unselected = This is not a gift order. From the Gift order flag in the Order Ship To table. |
Ship to flag |
Selected indicates a permanent ship-to address is selected for the order. Populated only if a permanent ship-to customer has been selected for the sold-to customer on the order (the Ship To option in order entry). |
Station ID |
Blank. Numeric, 4 positions. |
Deliverable code |
The deliverable code for the ship-to customer. From the Deliverable code in the Customer Sold To table for the customer number on the order header. Alphanumeric, 1 position. |
Flexible payment option type |
The flexible payment option on the credit card on the order. The payment method must be active. D = deferred billing. I = installment billing. blank = regular billing. From the FPO payment code in the Order Payment Method table for the credit card payment on the order. Alphanumeric, 1 position. |
Hold/bypass/fraud |
Controls how credit checking is performed for the sold-to customer during order entry. H = Hold; place the customer’s orders on hold automatically. B = Bypass; orders for the customer are not included in the credit check function in order entry; however, the customer is still subject to other fraud-checking, as described in for the Fraud Checking (A68) system control value. F = Fraud; the system places the customer’s orders on fraud hold automatically. blank = normal fraud checking occurs in order entry. From the Hold/bypass/fraud flag in the Customer Sold To table for the customer number on the order header. Alphanumeric, 1 position. |
Ship to name prefix |
Prefix of the permanent ship-to customer on the order. From the Prefix in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 3 positions. |
Ship to last name |
Last name of the permanent ship-to customer on the order. From the Last name in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 25 positions. |
Ship to first name |
First name of the permanent ship-to customer on the order. From the First name in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 15 positions. |
Ship to initial |
Middle initial of the permanent ship-to customer on the order. From the Initial in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 1 position. |
Ship to company name |
Company name of the permanent ship-to customer on the order. From the Company name in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 30 positions. |
Ship to internet address |
Email address of the permanent ship-to customer on the order. From the Email address in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 50 positions. |
Ship to address line 1 |
Street address of the permanent ship-to customer address. From the Street address in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 32 positions. |
Ship to address line 2 |
Address line 2 of the permanent ship-to customer’s address. From the Address line 2 in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 32 positions. |
Ship to apartment |
Apartment of the permanent ship-to customer’s address. From the Apartment in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 10 positions. |
Ship to city |
City of the permanent ship-to customer’s address. From the City in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 25 positions. |
Ship to state |
State code of the permanent ship-to customer’s address. From the State in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 2 positions. |
Ship to zip |
Postal code of the permanent ship-to customer’s address. From the Zip in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 10 positions. |
Ship to country |
Country code of the permanent ship-to customer’s address. From the Country in the Customer Ship To table for the order ship-to on the order. Alphanumeric, 3 positions. |
Ship to phone number day |
Day phone number of the permanent ship-to customer on the order. From the Phone in the Customer Ship To Phone Number table for phone type D. Numeric, 10 positions. |
Ship to phone number evening |
Evening phone number of the permanent ship-to customer on the order. From the Phone in the Customer Ship To Phone Number table for phone type E. Numeric, 10 positions. |
Ship to phone number fax |
Third (fax or mobile) phone number of the permanent ship-to customer on the order. From the Phone in the Customer Ship To Phone Number table for phone type F. Numeric, 10 positions. |
Alternate customer number |
Alternate customer number of the sold-to customer. From the Interface customer code in the Customer Sold To table for the customer number on the order header. Alphanumeric, 15 positions. |
Address line 3 |
Address line 3 of the sold-to customer’s address. From the Address line 3 in the Customer Sold To Extended table for the customer number on the order header. Alphanumeric, 32 positions. |
Address line 4 |
Address line 4 of the sold-to customer’s address. From the Address line 4 in the Customer Sold To Extended table for the customer number on the order header. Alphanumeric, 32 positions. |
Email status |
Indicates the preferred method of correspondence. O1 (Email): Email is the preferred method of correspondence. O2 (Order-only email): Use email for order-related correspondence only; generate a document for other correspondence. O3 (No email): Do not use email for any correspondence; generate a document instead. O4 (Do not ask customer): Do not ask the customer for his/her email address; the customer has already been asked and has declined to provide it. The system does not generate any email correspondence to the customer, even if an email address is specified. From the Email status in the Customer Sold To table for the customer number on the order header. Alphanumeric, 2 positions. |
Marketing Download Order Detail Table and Extract File
Marketing Download Order Detail Table: This table contains a record for each marketing download trigger with a trigger type of order header (OH) or trigger type of LH (line history); see Populating the Marketing Download Tables and Extract Files for more information on how the MDEXTR Marketing Download Order and Customer Extract (CSX1041) periodic function determines when to create a record in the Marketing Download Order Detail table.
Note: Marketing Download order detail records are associated with a record in the Marketing Download Order Detail Table and Extract File if the order is newly created; a record does not exist in the Marketing Download Order Header table for Marketing Download order detail records that are associated with a maintenance transaction that created an order line history record.
Marketing Download Order Detail Export file: The MDEXPRT Marketing Download Export (PFR0130) periodic function creates a pipe-delimited text file based on the records in the Marketing Download Order Detail table.
Name of file: The name of the file is 999.ORDER_DETAIL.DATETIME.TXT, where 999 is the company code where you submitted the MDEXPRT periodic function and DATETIME is the date and time when the extract occurred, including an abbreviation, such as EDT, indicating the time zone. For example: 7.ORDER_DETAIL.Mon-Apr-25-11-28-03-EDT-2016.TXT.
Location of file: The Marketing Download Order Detail Export file text file is either placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property or included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, depending on whether the FILE_STORAGE_EXPORTS_ENABLED property is selected. The zip file in the FILE_STORAGE table has the same name as the text file that it contains, except for the ZIP extension.
Example of file: In this example, the first row is a header record indicating the name of each column and the subsequent rows contain the actual data; see the Marketing Download Order Detail Table for a description of the contents of this file.
Company|Order#|Ship To#|Line#|Customer#|Entity|Item/SKU|Qty Ordered|Qty Shipped|Qty Returned|Qty Canceled|Qty Sold Out|Qty Exchanged|Unit Price|Unit Offer Price|Return Reason Cd|Cancel Reason Code|Exchange Reason Code|Item Class|Long SKU Dept|Item Categ|Backorder Flag|Order Dt|Ship Dt|Return Dt|Cancel Dt|Sold Out Dt|Exchange Dt|Merch Loaded Cost|Price Meth|Disc Applied|Price Override Code|Promotion Code|Source Code|Offer
7|19|1|1|4|777|ITEM|6|0|0|0|0|0|31.66|80|000|000|000|1|7777|ICAT|N|20160425|0|0|0|0|0|40|6|A|||SOURCE|OFR
7|19|1|2|4|777|SKU BLUE SML WMNS|1|0|0|0|0|0|47.5|50|000|000|000|1|7777||N|20160425|0|0|0|0|0|0|6|A|||SOURCE|OFR
7|19|1|3|4|777|SKU RED|1|0|0|0|0|0|66.5|70|000|000|000|1|7777|ICAT|Y|20160425|0|0|0|0|0|45|6|A|||SOURCE|OFR
Note: When you run the MDEXPRT Marketing Download Export periodic function, the system creates the Marketing Download Order Detail Export file even if there is no data to extract from the Marketing Download Order Detail table; in this situation, the text file contains only a header record indicating the column names.
Marketing Download Order Detail Table
Field |
Description |
Company |
The code for the company where the order is located. OH: From the Company in the Order Detail table. LH: From the Company in the Order Line History table. Numeric, 3 positions. |
Order number |
The order number of the order you created or maintained. OH: From the Order number in the Order Detail table. LH: From the Order number in the Order Line History table. Numeric, 8 positions. |
Order ship to number |
The order ship-to number associated with the order you created or maintained. OH: From the Ship to number in the Order Detail table. LH: From the Ship to number in the Order Line History table. Numeric, 3 positions. |
Order detail number |
The order line number on the order you created or maintained. OH: From the Sequence number in the Order Detail table. LH: From the Sequence number in the Order Line History table. Numeric, 5 positions. |
Customer number |
The sold-to customer number on the order. From the Customer number in the Order Header table. Numeric, 9 positions. |
Entity |
The entity associated with the order. From the Entity number in the Division table for the division associated with the source code on the order detail; otherwise order header. Numeric, 3 positions. |
Item |
The item number on the order line. From the Item number in the Order Detail table. Alphanumeric, 12 positions. |
SKU |
The SKU code for the item on the order line. From the SKU code in the Order Detail table. Alphanumeric, 14 positions. |
Qty ordered |
The number of units of the item ordered. If the quantity is negative (a return), this field is blank. The Marketing Download function checks the associated order line history record to determine if the transaction was a return or exchange. • for return transactions, the function updates the Quantity returned with the negative order quantity. • for exchange transactions, the function updates the Quantity exchanged with the negative order quantity. OH: From the Quantity ordered in the Order Detail table. Order maintenance transactions The quantity ordered is populated if the order maintenance transaction increased the order quantity; in this case, the quantity ordered represents the additional amount. If the order maintenance transaction decreased the quantity ordered, the function updates the quantity cancelled. However, if the cancel reason code used has Reduce demand selected, the quantity cancelled updates the quantity ordered with a negative number. LH: From the Quantity in the Order Line History table. Numeric, 5 positions. |
Qty shipped |
The number of units of the item shipped. OH: From the Quantity shipped in the Order Detail table. LH: From the Quantity in the Order Line History table for the order line history record with an activity code of S (shipped) or B (express billed). Numeric, 5 positions. |
Qty returned |
The number of units of the item returned, including returns entered as a negative order quantity in order entry. OH: From the Quantity returned in the Order Detail table. LH: From the Quantity for the order line history record with an activity code of R (return). Numeric, 5 positions. |
Qty cancelled |
The number of units of the item cancelled. The function updates this field if the cancel reason code used has Reduce demand unselected; however, if the cancel reason code has Reduce demand selected, the quantity cancelled updates the quantity ordered with a negative number. OH: From the Quantity cancelled in the Order Detail table. LH: From the Quantity for the order line history record with an activity code of C (cancel). Numeric, 5 positions. |
Qty sold out |
The number of units of the item soldout. OH: From the Quantity sold out in the Order Detail table. LH: From the Quantity for the order line history record with an activity code of O (soldout). Numeric, 5 positions. |
Qty exchanged |
The number of units of the item exchanged. OH: From the Quantity returned in the Order Detail table. LH: From the Quantity for the order line history record with an activity code of E (exchange). Numeric, 5 positions. |
Unit price |
The actual selling price of a single unit of the item on this order. From the Pre-discount amount in the Order Detail table. Numeric, 7 positions with a 2-place decimal. |
Unit offer price |
The actual selling price of a single unit of the item, as defined in the offer. From the Price in the Item Price table for the offer associated with the order detail line. Numeric, 7 positions with a 2-place decimal. |
Return reason code |
The return reason code used to return a quantity of this item. From the Return reason code for the order line history record with an activity code of R (return). Alphanumeric, 3 positions. |
Cancel reason code |
The cancel reason code used to cancel a quantity of this item. From the Cancel reason code for the order line history record with an activity code of C (cancel). Alphanumeric, 3 positions. |
Exchange reason code |
The exchange reason code used to exchange a quantity of this item. From the Exchange reason code for the order line history record with an activity code of E (exchange). Alphanumeric, 3 positions. |
Item class |
The item class associated with the item. From the Item class in the Item table for the item on the order detail line. Alphanumeric, 3 positions. |
Long SKU department |
The long SKU department associated with the item. From the Long SKU department in the Item table for the item on the order detail line. Numeric, 4 positions. |
Item category |
The item category associated with the item. From the Category in the SKU table for the item on the order detail line. Alphanumeric, 4 positions. |
Backorder flag |
Indicates whether the item is on backorder. Selected = A quantity of the item is on backorder. Unselected = The item is not on backorder and can be reserved. Selected if there is a Backorder reason in the Order Detail table. |
Order date |
The date the order was created. From the Entered date in the Order Detail table. Numeric, 8 positions (YYYYMMDD format). |
Ship date |
The date a quantity of this item shipped. From the Date for the most recent order line history record with an activity code of S (shipped) or B (express billed). Numeric, 8 positions (YYYYMMDD format). |
Return date |
The date a quantity of this item was returned. From the Date for the most recent order line history record with an activity code of R (return). Numeric, 8 positions (YYYYMMDD format). |
Cancel date |
The date a quantity of this item was cancelled. From the Date for the most recent order line history record with an activity code of C (cancel). Numeric, 8 positions (YYYYMMDD format). |
Sold out date |
The date a quantity of this item was soldout. From the Date for the most recent order line history record with an activity code of O (soldout). Numeric, 8 positions (YYYYMMDD format). |
Exchange date |
The date a quantity of this item was exchanged. From the Date for the most recent order line history record with an activity code of E (exchange). Numeric, 8 positions (YYYYMMDD format). |
Merch loaded cost |
The loaded merchandise cost of this item. The function adds the vendor item cost (for the primary vendor defined for the item) to all of the vendor item additional charges associated with the vendor item to determine the loaded merchandise cost. This is the sum of Price from the Vendor Item table and the Percent or Unit amount from the Vendor Item Additional Charge table for each additional charge associated with the vendor item. The function first converts any vendor item additional charge percentages to a unit amount. Numeric, 9 positions with a 2-place decimal. |
Price method |
The price method used on the order detail line. 5 = column price 3 = contract price * = converted price 1 = coupon item price 2 = customer discount % 7 = offer price R = price override Y = price table K = price table default L = price table level override T = price table premium M = promotional item 6 = quantity break price |
|
I = repriced quantity break/item Q = repriced quantity break/offer V = repriced volume discount 4 = special source price U = upsell item N = no charge O = no charge/no cost E = price code P = regular hierarchy D = regular plus reprice C = use item cost SB = the Sale book item flag for the SKU/offer or item/offer is selected. From the Price method in the Order Detail table. Alphanumeric, 2 positions. |
Discount applied |
Indicates whether a discount has been applied to the order line. A = source discount B = order discount C = source and order discount blank = no discount From the Discount applied in the Order Detail table. Alphanumeric, 1 position. |
Price override code |
The price override reason code for the order line. From the Price override code in the Order Detail table. Alphanumeric, 1 position. |
Promotion code |
The promotion that applies to the order. Defaults from the Order Promotion table, where it is stored only if the Allow Manual Entry of Promotion Code (I63) system control value is selected, and the operator selected a promotion code when creating the order. If more than one Order Promotion record exists for the order, the system uses the first promotion code. If the Allow Manual Entry of Promotion Code (I63) system control value is unselected, this field is blank even if one or more promotions apply to the order. Alphanumeric, 7 positions. |
Source code |
The source code saved in the Order Detail table, if any. See the Default Offer Source Code to Order Detail Line (G28) system control value for a discussion of when the system saves the source code in the Order Detail table. Alphanumeric, 9 positions. |
Offer |
The offer associated with the order detail line. See Override Offer on Order Detail Line (D49) for a discussion on which offer is saved in the Order Detail table. Alphanumeric, 3 positions. |
Marketing Download Customer Status Change Table and Extract File
Marketing Download Customer Status Change table: This table contains a record for each marketing download trigger with a trigger type of customer status change (CS); see Populating the Marketing Download Tables and Extract Files for more information on how the MDEXTR Marketing Download Order and Customer Extract (CSX1041) periodic function determines when to create a record in the Marketing Download Customer Status Change table.
Marketing Download Customer Status Change Export file: The MDEXPRT Marketing Download Export (PFR0130) periodic function creates a pipe-delimited text file based on the records in the Marketing Download Customer Status Change table.
Name of file: The name of the file is 999.CUST_STATUS_CHANGE.DATETIME.TXT, where 999 is the company code where you submitted the MDEXPRT periodic function and DATETIME is the date and time when the extract occurred, including an abbreviation, such as EDT, indicating the time zone. For example: 7.CUST_STATUS_CHANGE.Mon-Apr-25-11-28-03-EDT-2016.TXT.
Location of file: The Marketing Download Customer Status Change Export text file is either placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property or included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, depending on whether the FILE_STORAGE_EXPORTS_ENABLED property is selected. The zip file in the FILE_STORAGE table has the same name as the text file that it contains, except for the ZIP extension.
Example of file: In this example, the first row is a header record indicating the name of each column and the subsequent row contains the actual data; see the Marketing Download Customer Status Change Table for a description of the contents of this file.
Company|Customer#|Entity|Trans Dt|Prefix|Last Name|First Name|Initial|Company Name|Address Line 1|Address Line 2|Apartment|City|State|Zip|Country|Customer Class|Mail Name?|CSE Mail Name?|Rent Name?|Mail Code|Call Code|Deliverable Code|Hold/Bypass/Fraud|Alt Customer#|Address Line 3|Address Line 4|Email Sts
555|122|0|20160210||BROWN|T|||1234 SAMPLE ST|ADD 2|250|NATICK|MA|01760|US|C1|Y||Y|N|||||ADD3|ADD4|O1
Note: When you run the MDEXPRT Marketing Download Export periodic function, the system creates the Marketing Download Customer Status Change Export file even if there is no data to extract from the Marketing Download Customer Status Change table; in this situation, the text file contains only a header record indicating the column names.
Marketing Download Customer Status Change Table
Field |
Description |
Company |
The code for the company where the customer status change took place. From the Company in the Customer Sold To table. Numeric, 3 positions. |
Customer number |
The sold-to customer number that was changed. From the Customer number in the Customer Sold To table. Numeric, 9 positions. |
Entity |
The entity associated with the sold-to customer. Used only if the Mail name in the Customer Sold To Entity table changed. From the Entity in the Marketing Download Trigger table. Numeric, 3 positions. |
Transaction date |
The date when the customer sold-to was changed. From the Date created in the Marketing Download Trigger table. Numeric, 8 positions. |
Prefix |
The prefix of the sold-to customer. From the Prefix in the Customer Sold To table. Alphanumeric, 3 positions. |
Last name |
The last name of the sold-to customer. From the Last name in the Customer Sold To table. Alphanumeric, 25 positions. |
First name |
The first name of the sold-to customer. From the First name in the Customer Sold To table. Alphanumeric, 15 positions. |
Initial |
The middle initial of the sold-to customer. From the Initial in the Customer Sold To table. Alphanumeric, 1 position. |
Company name |
The company name for the sold-to customer. From the Company name in the Customer Sold To table. Alphanumeric, 30 positions. |
Address line 1 |
The street address for the sold-to customer. From the Street address in the Customer Sold To table. Alphanumeric, 32 positions. |
Address line 2 |
Address line 2 for the sold-to customer. From the Address line 2 in the Customer Sold To table. Alphanumeric, 32 positions. |
Apartment |
The apartment for the sold-to customer. From the Apartment in the Customer Sold To table. Alphanumeric, 10 positions. |
City |
The city for the sold-to customer. From the City in the Customer Sold To table. Alphanumeric, 25 positions. |
State |
The state code for the sold-to customer. From the State in the Customer Sold To table. Alphanumeric, 2 positions. |
Zip |
The postal code for the sold-to customer. From the Zip in the Customer Sold To table. Alphanumeric, 10 positions. |
Country |
The country code for the sold-to customer. From the Country in the Customer Sold to table. Alphanumeric, 3 positions. |
Customer class |
The customer class for the sold-to customer. From the Customer class in the Customer Sold To table. Alphanumeric, 2 positions. |
Mail name? |
Indicates whether the customer receives future catalogs. Selected = Mail catalogs to the customer. Unselected = Do not mail catalogs to the customer. From the Mail name in the Customer Sold To table. |
Customer entity mail name? |
Indicates whether the sold-to customer, at the entity level, receives future catalogs. Selected = mail catalogs to the customer. Unselected = Do not mail catalogs to the customer. This field is unselected if there is no customer information at the entity level (the Track Customer History at Entity Level (F89) system control value is unselected). From the Mail name in the Customer Sold To Entity table. |
Rent name? |
Indicates whether to include the customer’s name in lists you sell to other companies for their own catalog mailings. Selected = Sell the customer’s name to another company. Unselected = Do not sell the customer’s name to another company. From the Rent name in the Customer Sold To table. |
Mail code |
A code that specifies how often, and under what conditions, to send mail to the customer. From the Mail code in the Customer Sold To table. Alphanumeric, 3 positions. |
Call code |
A code that specifies how often, and under what conditions, to call the customer. From the Call code in the Customer Sold To table. Alphanumeric, 3 positions. |
Deliverable code |
The deliverable code for the sold-to customer. From the Deliverable code in the Customer Sold To table. Alphanumeric, 1 position. |
Hold/bypass/fraud |
A code that controls how credit checking is performed for the customer during order entry. H = hold; the system places the customer’s orders on hold automatically. B = bypass; orders for the customer are not included in the credit check function in order entry. F = fraud; the system places the customer’s orders on “fraud” hold automatically. blank = normal fraud checking occurs in order entry. From the Hold/bypass/fraud in the Customer Sold To table. Alphanumeric, 1 position. |
Alternate customer number |
Alternate customer number for the sold-to customer. From the Interface customer code in the Customer Sold To table. Alphanumeric, 15 positions. |
Address line 3 |
Address line 3 for the sold-to customer’s address. From the Address line 3 in the Customer Sold To Extended table. Alphanumeric, 32 positions. |
Address line 4 |
Address line 4 for the sold-to customer’s address. From the Address line 4 in the Customer Sold To Extended table. Alphanumeric, 32 positions. |
Email status |
Indicates the preferred method of correspondence. O1 (Email): Email is the preferred method of correspondence. O2 (Order-only email): Use email for order-related correspondence only; generate a document for other correspondence. O3 (No email): Do not use email for any correspondence; generate a document instead. O4 (Do not ask customer): Do not ask the customer for his/her email address; the customer has already been asked and has declined to provide it. The system does not generate any email correspondence to the customer, even if an email address is specified. From the Email status in the Customer Sold To table. Alphanumeric, 2 positions. |
Marketing Download Customer Inquiry Table and Extract File
Marketing Download Customer Inquiry table: This table contains a record for each marketing download trigger with a trigger type of customer inquiry (CI), customer action note (CN; only if a reason has been defined for the customer action), or order header (OH; only for a recipient customer); see Populating the Marketing Download Tables and Extract Files for more information on how the MDEXTR Marketing Download Order and Customer Extract (CSX1041) periodic function determines when to create a record in the Marketing Download Customer Inquiry table.
Marketing Download Customer Inquiry Export file: The MDEXPRT Marketing Download Export (PFR0130) periodic function creates a pipe-delimited text file based on the records in the Marketing Download Customer Inquiry table.
Name of file: The name of the file is 999.CUST_INQUIRY.DATETIME.TXT, where 999 is the company code where you submitted the MDEXPRT periodic function and DATETIME is the date and time when the extract occurred, including an abbreviation, such as EDT, indicating the time zone. For example: 7.CUST_INQUIRY.Mon-Apr-25-11-28-03-EDT-2016.TXT.
Location of file: The Marketing Download Customer Inquiry Export text file is either placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property or included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, depending on whether the FILE_STORAGE_EXPORTS_ENABLED property is selected. The zip file in the FILE_STORAGE table has the same name as the text file that it contains, except for the ZIP extension.
Example of file: In this example, the first row is a header record indicating the name of each column and the subsequent row contains the actual data; see the Marketing Download Customer Inquiry Table for a description of the contents of this file.
Company|Customer#|Entity|CAC Seq#|Trans Dt|Prefix|Gender Code|Last Name|Initial|First Name|Company Name|Address Line 1|Address Line 2|Apartment|City|State|Zip|Country|Internet Address|Phone #1 Day|Phone # Eve|Phone #3 Fax|Customer Class|Mail Name?|CSE Mail Name?|Rent Name?|Mail Code|Mail Type|Offer|Source Code|Station ID|Deliverable Code|Hold/Bypass/Fraud|Action Entry Dt|Action Reason Code|Action Resolved Dt|Alt Customer#|Address Line 3|Address Line 4|Email Sts
7|4|777|0|20160425|||BROWN|R|THOMAS||1234 SAMPLE STREET|||WESTBOROUGH|MA|01581|US||5083310100|5083310101|0||Y||Y|Y|||||0|||0||0||||O1
Note: When you run the MDEXPRT Marketing Download Export periodic function, the system creates the Marketing Download Customer Inquiry Export file even if there is no data to extract from the Marketing Download Customer Inquiry table; in this situation, the text file contains only a header record indicating the column names.
Marketing Download Customer Inquiry Table
Field |
Description |
Company |
The company where the customer inquiry took place. From the Company in the Customer Sold To table. Numeric, 3 positions. |
Customer number |
The sold-to customer associated with the customer inquiry. From the Customer sold to in the Customer Sold To table. Numeric, 9 positions. |
Entity |
The entity associated with the sold-to customer. From the Entity in the Marketing Download Trigger table. Numeric, 3 positions. |
Customer action sequence number |
The customer action sequence number of the customer action note that was updated. From the Customer action sequence number in the Marketing Download Trigger table. Numeric, 2 positions. |
Transaction date |
The date the customer inquiry took place. From the Date created in the Marketing Download Trigger table. Numeric, 8 positions. |
Prefix |
The prefix of the sold-to customer. From the Prefix in the Customer Sold To table. Alphanumeric, 3 positions. |
Gender code |
The gender code for the sold-to customer. From the Profile data code in the Customer Profile table for the customer number on the marketing download trigger. The system uses the Default Male/Female Profile Code (C74) system control value as the gender code. Alphanumeric, 1 position. |
Last name |
The last name of the sold-to customer. From the Last name in the Customer Sold To table. Alphanumeric, 25 positions. |
Initial |
The middle initial of the sold-to customer. From the Initial in the Customer Sold To table. Alphanumeric, 1 position. |
First name |
The first name of the sold-to customer. From the First name in the Customer Sold To table. Alphanumeric, 15 positions. |
Company name |
The company name of the sold-to customer. From the Company name in the Customer Sold To table. Alphanumeric, 30 positions. |
Address line 1 |
The street address of the sold-to customer. From the Street address in the Customer Sold To table. Alphanumeric, 32 positions. |
Address line 2 |
Address line 2 of the sold-to customer. From the Address line 2 in the Customer Sold To table. Alphanumeric, 32 positions. |
Apartment |
The apartment of the sold-to customer. From the Apartment in the Customer Sold To table. Alphanumeric, 10 positions. |
City |
The city of the sold-to customer. From the City in the Customer Sold To table. Alphanumeric, 25 positions. |
State |
The state code of the sold-to customer. From the State in the Customer Sold To table. Alphanumeric, 2 positions. |
Zip |
The postal code of the sold-to customer. From the Zip in the Customer Sold To table. Alphanumeric, 10 positions. |
Country |
The country code of the sold-to customer. From the Country in the Customer Sold To table. Alphanumeric, 3 positions. |
Internet address |
The email address of the sold-to customer. From the Email in the Customer Sold To table. Alphanumeric, 50 positions. |
Phone number 1 day |
The day phone number of the sold-to customer. From the Phone in the Customer Sold To Phone Number table for phone type D. Numeric, 10 positions. |
Phone number 2 evening |
The evening phone number of the sold-to customer. From the Phone in the Customer Sold To Phone Number table for phone type E. Numeric, 10 positions. |
Phone number 3 fax |
The third (fax or mobile) phone number of the sold-to customer. From the Phone in the Customer Sold To Phone Number table for phone type F. Numeric, 10 positions. |
Customer class |
The customer class of the sold-to customer. From the Customer class in the Customer Sold To table. Alphanumeric, 2 positions. |
Mail name? |
Indicates whether the customer receives future catalogs. Selected = Mail catalogs to the customer. Unselected = Do not mail catalogs to the customer. From the Mail name in the Customer Sold To table. |
Customer entity mail name? |
Indicates whether the sold-to customer receives future catalogs. Selected = mail catalogs to the customer. Unselected = Do not mail catalogs to the customer. This field is unselected if there is no customer information at the entity level (the Track Customer History at Entity Level (F89) system control value is unselected). From the Mail name in the Customer Sold To Entity table. |
Rent name? |
Indicates whether to include the customer’s name in lists you sell to other companies for their own catalog mailings. Selected = Sell the customer’s name to another company. Unselected = Do not sell the customer’s name to another company. From the Rent name in the Customer Sold To table. |
Mail code |
A code that specifies how often, and under what conditions, to send mail to the customer. From the Mail code in the Customer Sold To table. Alphanumeric, 3 positions. |
Call code |
A code that specifies how often, and under what conditions, to call the customer. From the Call code in the Customer Sold To table. Alphanumeric, 3 positions. |
Mail type |
The code that identifies how you and the customer first established contact. B = buyer; the customer has placed an order. C = catalog requester; the customer has requested a catalog. L = list rental; the customer’s name was purchased from another company. R = recipient; the customer has received an order. S = suspect; the customer’s name was acquired through a telemarketing effort. Also, a customer that has placed an order, but then rejected the order. From the Current mail type in the Customer Sold To table. Alphanumeric, 1 position. |
Offer |
The offer associated with the current source code for the customer sold-to. From the Offer number in the Source code table for the source code for the customer sold-to. Alphanumeric, 3 positions. |
Source code |
The source code on the marketing download trigger record. From the Source code in the Customer Sold To table. Alphanumeric, 9 positions. |
Station ID |
This field is blank. Numeric, 4 positions. |
Deliverable code |
Identifies whether the address is a business (commercial) or residential address. B = business address. R = residential address. N = no distinction between business and residential address. From the Delivery code in the Customer Sold To table. Alphanumeric, 1 position. |
Hold/bypass/fraud |
A code that controls how credit checking is performed for the customer during order entry. H = hold; the system places the customer’s orders on hold automatically. B = bypass; orders for the customer are not included in the credit check function in order entry. F = fraud; the system places the customer’s orders on “fraud” hold automatically. blank = normal fraud checking occurs in order entry. From the Hold/bypass/fraud in the Customer Sold To table. Alphanumeric, 1 position. |
Action entry date |
The date the customer action note was created for the sold-to customer. From the Entry date in the Customer Action table. Numeric, 8 positions. |
Action reason code |
The reason code for the customer action note. From the Reason code in the Customer Action table. Alphanumeric, 2 positions. |
Action resolved date |
The date the customer action note was resolved. From the Resolved date in the Customer Action table. Numeric, 8 positions. |
Alternate customer number |
Alternate customer number for the sold-to customer. From the Interface customer code in the Customer Sold To table. Alphanumeric, 15 positions. |
Address line 3 |
Address line 3 of the sold-to customer’s address. From the Address line 3 in the Customer Sold To Extended table. Alphanumeric, 32 positions. |
Address line 4 |
Address line 4 of the sold-to customer’s address. From the Address line 4 in the Customer Sold To Extended table. Alphanumeric, 32 positions. |
Email status |
Indicates the preferred method of correspondence. O1 (Email): Email is the preferred method of correspondence. O2 (Order-only email): Use email for order-related correspondence only; generate a document for other correspondence. O3 (No email): Do not use email for any correspondence; generate a document instead. O4 (Do not ask customer): Do not ask the customer for his/her email address; the customer has already been asked and has declined to provide it. The system does not generate any email correspondence to the customer, even if an email address is specified. This is the Email status in the Customer Sold To table. Alphanumeric, 2 positions. |
Marketing Download Customer Address Change Table and Extract File
Marketing Download Customer Address Change table: This table contains a record for each marketing download trigger with a trigger type of customer address change (CA); see Populating the Marketing Download Tables and Extract Files for more information on how the MDEXTR Marketing Download Order and Customer Extract (CSX1041) periodic function determines when to create a record in the Marketing Download Customer Address Change table.
Marketing Download Customer Address Change Export file: The MDEXPRT Marketing Download Export (PFR0130) periodic function creates a pipe-delimited text file based on the records in the Marketing Download Customer Address Change table.
Name of file: The name of the file is 999.CUST_ADDRESS_CHANGE.DATETIME.TXT, where 999 is the company code where you submitted the MDEXPRT periodic function and DATETIME is the date and time when the extract occurred, including an abbreviation, such as EDT, indicating the time zone. For example: 7.CUST_ADDRESS_CHANGE.Mon-Apr-25-11-28-03-EDT-2016.TXT.
Location of file: The Marketing Download Customer Address Change Export text file is either placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property or included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, depending on whether the FILE_STORAGE_EXPORTS_ENABLED property is selected. The zip file in the FILE_STORAGE table has the same name as the text file that it contains, except for the ZIP extension.
Example of file: In this example, the first row is a header record indicating the name of each column and the subsequent row contains the actual data; see the Marketing Download Customer Address Change Table for a description of the contents of this file.
Company|Customer#|CSH Ship To#|Trans Dt|Prefix|Gender Code|Last Name|Initial|First Name|Company Name|Address Line 1|Address Line 2|Apartment|City|State|Zip|Country|Internet Address|Phone #1 Day|Phone # Eve|Phone #3 Fax|Alt Customer#|Address Line 3|Address Line 4|Email Sts
7|4|0|20160425|||BROWN|R|THOMAS||1234 SAMPLE STREET|||WESTBOROUGH|MA|01581|US||5083310100|5083310101|0||||O1
Note: When you run the MDEXPRT Marketing Download Export periodic function, the system creates the Marketing Download Customer Address Change Export file even if there is no data to extract from the Marketing Download Customer Address Change table; in this situation, the text file contains only a header record indicating the column names.
Marketing Download Customer Address Change Table
Field |
Description |
Company |
The company where the customer address change took place. From the Company in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Numeric, 3 positions. |
Customer number |
The customer sold-to number associated with the customer address change. From the Customer Sold To in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Numeric, 9 positions. |
Ship to number |
The ship-to number associated with the customer address change. Included only if the address change is for the ship-to customer. From the Ship to number in the Customer Ship To table. Numeric, 3 positions. |
Transaction date |
The date the customer address was changed. From the Date created in the Marketing Download Trigger table. Numeric, 8 positions. |
Prefix |
The prefix of the sold-to customer or ship-to customer, depending on which address changed. From the Prefix in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 3 positions. |
Gender code |
The gender code for the sold-to customer. From the Profile data code in the Customer Profile table for the customer number on the marketing download trigger. The function uses the Default Male/Female Profile Code (C74) system control value as the gender code. Alphanumeric, 1 position. |
Last name |
The last name of the sold-to customer or ship-to customer, depending on which address changed. From the Last name in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 25 positions. |
Initial |
The middle initial of the sold-to customer or ship-to customer, depending on which address changed. From the Initial in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 1 position. |
First name |
The first name of the sold-to customer or ship-to customer, depending on which address changed. From the First name in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 15 positions. |
Company name |
The company name for the sold-to customer or ship-to customer, depending on which address changed. From the Company name in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 30 positions. |
Address line 1 |
The street address of the sold-to customer or ship-to customer, depending on which address changed. From the Street address in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 32 positions. |
Address line 2 |
Address line 2 of the sold-to customer or ship-to customer, depending on which address changed. From the Address line 2 in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 32 positions. |
Apartment |
The apartment of the sold-to customer or ship-to customer, depending on which address changed. From the Apartment in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 10 positions. |
City |
The city of the sold-to customer or ship-to customer, depending on which address changed. From the City in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 25 positions. |
State |
The state code for the sold-to customer or ship-to customer, depending on which address changed. From the State in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 2 positions. |
Zip |
The postal code for the sold-to customer or ship-to customer, depending on which address changed. From the Zip in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 10 positions. |
Country |
The country code for the sold-to customer or ship-to customer, depending on which address changed. From the Country in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 3 positions. |
Internet address |
The email address for the sold-to customer or ship-to customer, depending on which address changed. From the Email in the Customer Sold To table (sold-to address change) or Customer Ship To table (ship-to address change). Alphanumeric, 50 positions. |
Phone number 1 day |
The day phone number for the sold-to customer or ship-to customer, depending on which address changed. From the Phone in the Customer Sold To Phone Number table (sold-to address change) or Customer Ship To Phone Number table (ship-to address change) for phone type D. Numeric, 10 positions. |
Phone number 2 evening |
The evening phone number for the sold-to customer or ship-to customer, depending on which address changed. From the Phone in the Customer Sold To Phone Number table (sold-to address change) or Customer Ship To Phone Number table (ship-to address change) for phone type E. Numeric, 10 positions. |
Phone number 3 fax |
The third (fax or mobile) phone number for the sold-to customer or ship-to customer, depending on which address changed. From the Phone in the Customer Sold To Phone Number table (sold-to address change) or Customer Ship To Phone Number table (ship-to address change) for phone type F. Numeric, 10 positions. |
Alternate customer number |
Alternate customer number for the sold-to customer. From the Interface customer code in the Customer Sold To table. Alphanumeric, 15 positions. |
Address line 3 |
Address line 3 for the sold-to customer’s address.From Address line 3 in the Customer Sold To Extended table. Alphanumeric, 32 positions. |
Address line 4 |
Address line 4 for the sold-to customer’s address. From Address line 4 in the Customer Sold To Extended table. Alphanumeric, 32 positions. |
Email status |
Indicates the preferred method of correspondence. O1 (Email): Email is the preferred method of correspondence. O2 (Order-only email): Use email for order-related correspondence only; generate a document for other correspondence. O3 (No email): Do not use email for any correspondence; generate a document instead. O4 (Do not ask customer): Do not ask the customer for his/her email address; the customer has already been asked and has declined to provide it. The system does not generate any email correspondence to the customer, even if an email address is specified. From the Email status in the Customer Sold To table. Alphanumeric, 2 positions. |
Marketing Download Customer Ownership Table and Extract File
Marketing Download Customer Ownership table: This table contains a record for each marketing download trigger with a trigger type of customer ownership (CO). If a customer has more than one ownership record, the system creates an ownership trigger record each time there is a change to either ownership. See Populating the Marketing Download Tables and Extract Files for more information on how the periodic function determines when to create a record in the Marketing Download Customer Ownership table.
Marketing Download Customer Ownership Export file: The MDEXPRT Marketing Download Export (PFR0130) periodic function creates a pipe-delimited text file based on the records in the Marketing Download Customer Ownership table.
Name of file: The name of the file is 999.CUST_OWNERSHIP.DATETIME.TXT, where 999 is the company code where you submitted the MDEXPRT periodic function and DATETIME is the date and time when the extract occurred, including an abbreviation, such as EDT, indicating the time zone. For example: 7.CUST_OWNERSHIP.Mon-Apr-25-11-28-03-EDT-2016.TXT.
Location of file: The Marketing Download Customer Ownership Export text file is either placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property or included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, depending on whether the FILE_STORAGE_EXPORTS_ENABLED property is selected. The zip file in the FILE_STORAGE table has the same name as the text file that it contains, except for the ZIP extension.
Example of file: In this example, the first row is a header record indicating the name of each column and the subsequent row contains the actual data; see the Marketing Download Customer Ownership Table for a description of the contents of this file.
Company|Customer#|Ownership ID|Active Flag|Entry Dt|Confirm Dt|Description
555|276|A123456789|Y|20160201|20160201|BANKER OWNERSHIP RECORDS
Note: When you run the MDEXPRT Marketing Download Export periodic function, the system creates the Marketing Download Customer Ownership Export file even if there is no data to extract from the Marketing Download Customer Ownership table; in this situation, the text file contains only a header record indicating the column names.
Marketing Download Customer Ownership Table
Field |
Description |
Company |
The company where the customer ownership record was created. From the Company in the Customer Ownership table. Numeric, 3 positions. |
Customer # |
The customer sold-to number associated with the customer ownership record. From the Customer # in the Customer Ownership table. Numeric, 9 positions. |
Ownership ID |
A code that represents a product the customer owns or previously owned. From the Ownership ID in the Customer Ownership table. Alphanumeric, 10 positions. |
Act Flag |
Indicates if the customer currently owns the product. Selected (default) = The customer currently owns the product. Unselected = The customer previously owned the product. From the Act in the Customer Ownership table. |
Entry Date |
The date the customer ownership record was created. From the Entry date in the Customer Ownership table. Numeric, 6 positions (MM/DD/YY format). |
Confirm Date |
The most recent date when the customer confirmed ownership of the product. From the Confirm date in the Customer Ownership table. Numeric, 6 positions (MM/DD/YY format). |
Description |
A description of the product the customer owns or previously owned. From the Description in the Customer Ownership table. Alphanumeric, 40 positions. |
Marketing Download Customer Profile Table and Extract File
Marketing Download Customer Profile table: This table contains a record for each marketing download trigger with a trigger type of customer profile (CP). If a customer has more than one profile record, the system creates a Marketing Download Customer Profile record for each Customer Profile record defined for the sold to customer. See Populating the Marketing Download Tables and Extract Files for more information on how the periodic function determines when to create a record in the Marketing Download Customer Profile table.
Marketing Download Customer Profile Export file: The MDEXPRT Marketing Download Export (PFR0130) periodic function creates a pipe-delimited text file based on the records in the Marketing Download Customer Profile table.
Name of file: The name of the file is 999.CUST_PROFILE.DATETIME.TXT, where 999 is the company code where you submitted the MDEXPRT periodic function and DATETIME is the date and time when the extract occurred, including an abbreviation, such as EDT, indicating the time zone. For example: 7.CUST_PROFILE.Mon-Apr-25-11-28-03-EDT-2016.TXT.
Location of file: The Marketing Download Customer Profile Export text file is either placed in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property or included in a zip file that is placed in the OMS-MARKETING container of the FILE_STORAGE table, depending on whether the FILE_STORAGE_EXPORTS_ENABLED property is selected. The zip file in the FILE_STORAGE table has the same name as the text file that it contains, except for the ZIP extension.
Example of file: In this example, the first row is a header record indicating the name of each column and the subsequent row contains the actual data; see the Marketing Download Customer Profile Table for a description of the contents of this file.
Company|Customer#|Profile Code|Profile Code Desc|Profile Data Code|Profile Data Code Desc
555|123456798|1|GENDER|M|MALE
Note: When you run the MDEXPRT Marketing Download Export periodic function, the system creates the Marketing Download Customer Profile Export file even if there is no data to extract from the Marketing Download Customer Profile table; in this situation, the text file contains only a header record indicating the column names.
Marketing Download Customer Profile Table
Field |
Description |
Company |
The company where the customer profile record was created. From the Company in the Customer Profile table. Numeric, 3 positions. |
Customer # |
The customer sold-to number associated with the customer profile record. From the Customer # in the Customer Profile table. Numeric, 9 positions. |
Profile Code |
A number that represents a demographic profile category. From the Profile code in the Customer Profile table. Alphanumeric, 10 positions. |
Profile Code Description |
The description of the demographic profile category. From the Description in the Profile table. Alphanumeric, 20 positions. |
Profile Data Code |
A user-defined code that represents a valid response for the profile category. From the Profile data code in the Customer Profile table. Alphanumeric, 30 positions. |
Profile Data Code Description |
Text that describes the profile data response. Example: For the profile category of Income level, you might have profile data codes such as the following: 1 = less than $20,000 2 = $20,000-$29,000 3 = $30,000-$39,000 4 = $40,000-$49,000 5 = more than $49,000 From the Description in the Profile Data table. Alphanumeric, 40 positions. |
Orders Released from Time Hold Report | Contents | SCVs | Search | Glossary | Reports | Solutions | XML | Index | Marketing Download Order Header Interface Summary Report |
SO02_13 OMSCS 18.1 April 2019 OTN