Working with the Marketing Download Extract

Working with the Marketing Download Extract

Purpose: The Marketing Download Extract allows you to download order, customer, source code, and vendor 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 in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

See Creating Marketing Download Trigger Records.

To download source code and vendor data: The marketing download extract works differently for source code and vendor data than it does for order and customer-related data. You can:

• run the MDSRC Marketing Download Source Extract (CSR1033) periodic function to create records in the Marketing Download Source Download table, based on the records in the Source Code table.

• Run MDVND Marketing Download Vendor Extract (CSR1038) the periodic function to create records in the Marketing Download Vendor Download table, based on the records in the Vendor table.

See Populating the Marketing Download Tables and Extract Files.

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 Source Code Table

- Marketing Download Vendor Download Table

• 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

• Marketing Download Source Code Interface Summary 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:

• MDSRC Marketing Download Source Extract (CSR1033) populates the Marketing Download Source Download table.

• MDVND Marketing Download Vendor Extract (CSR1038) populates the Marketing Download Vendor Download table for vendors whose User 1 field contains the text ACTIVE.

• MDEXTR Marketing Download Order and Customer Extract (CSX1041) populates the Marketing Download order-related and customer-related tables (all the Marketing Download tables besides Marketing Download Source Download and Marketing Download Vendor Download) 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

• MDSRCSM (CSX1036): Marketing Download Source Code 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

Note: The Marketing Download source code and vendor extracts do not use the Marketing Download Trigger table; instead, there are periodic functions that extract these records directly to the related Marketing Download tables. See Populating the Marketing Download Tables and Extract Files for more information.

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 #	Marketing Download Order Detail Table and Extract File
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)	Marketing Download Customer Inquiry Table and Extract File
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 #	Marketing Download Customer Inquiry Table and Extract File
CUSTOMER or ORD/CUST	CP (customer profile)	create or change a customer profile record.	Customer sold to #	Marketing Download Customer Profile Table and Extract File
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 in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

Source and vendor data: The MDSRC Marketing Download Source Extract (CSR1033) and MDVND Marketing Download Vendor Extract (CSR1038) periodic functions populate the Marketing Download Source Code Table and Marketing Download Vendor Download Table tables. See Setting up the Marketing Download Extract for more information on setting up these periodic functions.

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.

Location of file: The system creates the Marketing Download Order Header Export file in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

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. For example: 7.ORDER_HEADER.Mon-Apr-25-11-28-03-EDT-2016.TXT.

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.

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.

Location of file: The system creates the Marketing Download Order Detail Export file in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

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. For example: 7.ORDER_DETAIL.Mon-Apr-25-11-28-03-EDT-2016.TXT.

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.

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.

Location of file: The system creates the Marketing Download Customer Status Change Export file in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

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. For example: 7.CUST_STATUS_CHANGE.Mon-Apr-25-11-28-03-EDT-2016.TXT.

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.

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.

Location of file: The system creates the Marketing Download Customer Inquiry Export file in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

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. For example: 7.CUST_INQUIRY.Mon-Apr-25-11-28-03-EDT-2016.TXT.

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.

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.

Location of file: The system creates the Marketing Download Customer Address Change Export file in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

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. For example: 7.CUST_ADDRESS_CHANGE.Mon-Apr-25-11-28-03-EDT-2016.TXT.

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.

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.

Location of file: The system creates the Marketing Download Customer Ownership Export file in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

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. For example: 7.CUST_OWNERSHIP.Mon-Apr-25-11-28-03-EDT-2016.TXT.

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.

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.

Location of file: The system creates the Marketing Download Customer Profile Export file in the Marketing folder of the directory defined in the CWDIRECTCP_ FTP_FOLDER property.

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. For example: 7.CUST_PROFILE.Mon-Apr-25-11-28-03-EDT-2016.TXT.

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.

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.

Marketing Download Source Code Table

This table contains a record for each record in the Source Code table for the company you specified; see Populating the Marketing Download Tables and Extract Files for more information on how the MDSRC periodic function determines when to create a record in the Marketing Download Source Code table.

Field	Description
Company	The company where the source code is located. From the Company in the Source table. Numeric, 3 positions.
Source code	The code to identify a segment of your customer base. From the Source code in the Source table. Alphanumeric, 9 positions.
Entity	The entity associated with the source code. From the Entity number in the Division table for the division code for the source code. Numeric, 3 positions.
Offer	The offer for the source code. From the Offer number in the Source table. Alphanumeric, 3 positions.
Source description	The description of the source code. From the Description in the Source table. Alphanumeric, 30 positions.
Source category	The source category assigned to the source code. From the Source category in the Source table. Alphanumeric, 2 positions.
List source	The broker’s key code for this source code. From the List source in the Source table. Alphanumeric, 9 positions.
Season	The season for the offer assigned to the source code. From the Season in the Offer table for the offer assigned to the source code. Alphanumeric, 3 positions.
List broker code	The code for the list broker who provided the list of names. From the List broker code in the Source table. Alphanumeric, 3 positions.
List cost	The cost to purchase this list from the broker. From the List cost in the Source table. Numeric, 9 positions with a 2-place decimal.
Offer start date	The date when the offer associated with the source code becomes active. From the Start date in the Offer table. Numeric, 8 positions.
Net circulation	The number of names rented from another company or generated from your own customer base (house list) for the catalog mailing, after duplicate names have been removed. From the Net circulation in the Source table. Numeric, 7 positions.

Marketing Download Vendor Download Table

This table contains a record for each existing record in the Vendor table in the company you specified. See Populating the Marketing Download Tables and Extract Files for more information on how the MDVND periodic function determines when to create a record in the Marketing Download Vendor Download table.

Note: The function creates a record in the Marketing Download Vendor Download table only for vendor records whose Vendor user 1 field contains the text ACTIVE.

Field	Description
Company	The company where the vendor is located. From the Company in the Vendor table. Numeric, 3 positions.
Vendor number	The code to identify a supplier. From the Vendor number in the Vendor table. Numeric, 7 positions.
Vendor name	The name of the vendor. From the Name in the Vendor table. Alphanumeric, 30 positions.
Contact name	The name of the contact person for this vendor. From the Contact name in the Vendor table. Alphanumeric, 30 positions.
Address line 1	The street address for the vendor. From Address line 1 in the Vendor table. Alphanumeric, 32 positions.
Address line 2	Address line 2 for the vendor. From Address line 2 in the Vendor table. Alphanumeric, 32 positions.
Address line 3	Address line 3 for the vendor. From Address line 3 in the Vendor table. Alphanumeric, 32 positions.
City	The city for the vendor. From the City in the Vendor table. Alphanumeric, 25 positions.
State	The state code for the vendor. From the State in the Vendor table. Alphanumeric, 2 positions.
Country	The country code for the vendor. From the Country in the Vendor table. Alphanumeric, 3 positions.
Zip	The postal code for the vendor. From the Zip in the Vendor table. Alphanumeric, 10 positions.
Phone number	The phone number for the vendor. From the Telephone number in the Vendor table. Numeric, 10 positions.
Fax number	The fax number for the vendor. From the FAX in the Vendor table. Numeric, 10 positions.
User field 3	User field 3 for the vendor. From User field 3 in the Vendor table. Alphanumeric, 10 positions.

Orders Released from Time Hold Report

Contents

SCVs

Search

Glossary

Reports

Solutions

XML

Index

Marketing Download Order Header Interface Summary Report

SO02_13 OROMS 17.0 2018 OTN