Reference Guide for Creating Queries with the Dynamic Query Tool (DQT)

Report Column

Data Type

Description

ORDERS

Use this root table to get information about orders placed in the system

Orders

Contains order information

Order #

Numeric

Order number

User #

Numeric

Internal Buyer ID (e.g., 10012)

Order Status #

Numeric

Internal ID for current order status. Could be one of the following:

1 - Input

2 - Approved

3 - Rejected

4 - Processing

5 - Shipped

6 - Cancelled

7 – Manual Quote Required

8 – User Approval Required

9 - Deleted

10 - Printed

11 – Approval Required

Total Cost

Decimal

Total order cost

Order Date

Date

The date on which the order was placed

Comments

String

Any comments entered by the customer while placing the order

Billing Address Id

Numeric

Depends on the order payment method:

Cash payment: null

CreditCard: null

AccountingCodes: null

OnAccount: CostCenter contact person’s address ID OR buyer’s contact person’s address ID

DeferredPayment: Buyer’s address ID

Order Placed At

Numeric

Internal ID of the Print Shop at which the order was placed

Order Due Date

Date

The date on which the order is expected to be completed

Company #

Numeric

The internal ID of the buyer’s company at the time of placing the order

Special Instruction

String

Special instructions for the operator

Confirmation #

String

Purchase Order #

String

PO number if the payment method is cost center.

Reward #

String

VAT

String

VAT number

Users

Contains information about buyers who placed the orders

User #

Numeric

Internal ID of user

Account Status

Numeric

Internal ID of the status of user’s account [0=Inactive, 1=Active]

User Name

String

Login name

Company #

Numeric

Internal ID of user’s company

Facility Id

Numeric

Internal ID of user’s Print Shop

Contact Id

Numeric

Internal ID of user’s contact

VAT Number

String

VAT number

Facilities

Contains information about printshops which executed the orders

Facility Id

Numeric

Internal ID of Print Shop

Printshop

String

Unique name of Print Shop

ContactId

Numeric

Internal ID of Print Shop contact person

Printshop Approval Limit

Money

A currency amount below which the order approval for orders to the Print Shop is automatic (also called ‘floor limit’)

Store Number

String

The name of the store associated with the Print Shop

Active

Boolean

Value indicating whether the printshop is disabled or not [Note that value of ‘False’ means that the printshop is not disabled, i.e. it is active]

Company

Conatins information about buyers’ companies

Company #

Numeric

Internal ID of the company

Company

String

Name of the company

Cost Center

String

Name of the company’s cost center that is displayed to the buyers on the storefront

Account Code

String

The name the buyer sees for the collection of accounting codes for the company.

Account Code Enabled

Boolean

Value indicating whether accounting codes are enabled for the company

Order Payment Transaction

Contains information about order payment details

Payment Method #

Numeric

Internal ID of payment method used for the order

Amount

Decimal

Order amount

Payment Date

Date

Date on which the order payment was made

Project Code TransactionID

Numeric

Internal ID of transaction pertaining to the order

Order Status

Gives the current status of the order

Status

String

Current status of the order (could be one of the following:

Rejected, In Production, Shipped, Canceled,

ManualQuoteRequired, UserApprovalRequired,

Printed, Deleted, User Approved, Approved,

Approval Required

Order Taxes

Contains information about taxes levied on the order

Tax Authority #

Numeric

Internal ID of the tax authority. (A tax authority represents a single tax (local, city, county, or state) that the Print Shop must collect and identifies the taxing authority to which this sale shall be reported.)

Taxable Amount

Decimal

Sum of taxable items in the order. This may not be the tax amount used to compute the taxes in some situations.  For example, some items or details listed as taxable may not be when their particular details are taken into account.  Furthermore, when multiple taxes are used, each tax authority may compute different taxable amounts.

Tax

Decimal

The total tax for the order, as calculated adding all the individual taxable amounts

Order Line Items

Contains information about line items in the order

Order LineItem #

Numeric

Internal ID of order line item

Item Price

Money

Obsolete. Please use ‘Item Total’ field from ‘Order LineItem Cost’ schema to get the item cost.

Quantity

Numeric

The quantity in the line item.  For printed order, this is the number of copies.  (For catalog documents sold in sets, this is the number of sets.)

Line Item Status

Numeric

Internal ID of line item status

1 - User Approved

2 - Approved

3 - Out Of Stock

4 - Processing

5 - Completed

6 - Canceled

7 - ManualQuoteRequired

8 - UserApprovalRequired

9 - Deleted

Order Totals

Contains information about taxes and shipping costs related to the order

Order Sub Total

Decimal

Order Total - (Adjustments + Discounts + Fees + Shipping)

Order Tax Quote

Decimal

Actual tax levied for the order

Order Shipping Quote

Decimal

Actual Shipping levied for the order

Order Shipping Cost

Decimal

Sum of order quote pricing entries related to shipping

Contacts

Contains information about buyer’s contacts

Contact Id

Numeric

Internal ID of contact

Display

String

Name of the contact displayed as ‘First Name, Last Name’

Salutation

String

Title of the contact

Department

String

Department of the contact

First Name

String

First name of the contact

Middle Name

String

Middle name of the contact

Last Name

String

Last name of the contact

Address Line 1

String

Address line 1

Address Line 2

String

Address line 2

City

String

City

State/Province

String

State

Postal Code

String

Zip

Country

String

Country

Phone 1

String

Phone number 1

Phone 2

String

Phone number 2

Fax

String

Fax

Reward #

String

Department

String

Department of the contact

Email

String

Email address of the contact

Payment Methods

Gives the payment method used in the order

Payment Method

String

Payment method used for the order. (Could be one of the following:

Credit Card, Deferred Payment, Other Account, Cost Center, Accounting Codes, Pay At Store, PayPal

Please note that this should be used in conjunction with the order information.

Ticket Trees

Contains information about job ticket

Buyer Supplied Name

String

Name entered by the buyer at the time of placing the order

Catalog Item

Numeric

Internal ID of the catalog item for which the order was placed

Quantity

Numeric

Quantity of order line item (For printed order, this is the number of copies.  For catalog documents sold in sets, this is the number of sets.)

Account Code Usage

Contains information about order payment wrt accounting codes

Account Code By Buyer

String

Account code entered by the buyer when placing an order with payment method as ‘Accounting Codes’

Tax Authorities

Contains information about tax authority that has been used to levy tax for the order

Tax Authority Code

String

An identification number or code that facilitates reporting.

Tax Rate

Decimal

The rate, as a percentage. Each item in the order is taxed at this rate.

Tax Authority

String

Name that identifies the tax for reporting

Order Payment Cost Center

Contains information about billing entities used for the order

Account Number

String

Account number of the billing entity used for the order payment

Order LineItem Cost

Contains information about order line itemcosts

Item Total

Money

Order line item cost

Item Price

Decimal

Order line item price per unit (cost divided by quantity)

Accounting Codes

Contains information about accounting codes of the buyer’s company

Account Code

String

Name of the accounting code that the buyer sees during checkout. [Please note that this is at the company level and NOT at the order level.]

TicketTree Parts

Contains information about job ticket parts

Quantity Ordered

Numeric

Quantity of order line item

Job Index Within Part

Numeric

Index of order line item within order

Combined FileId

Numeric

Internal ID of merged file created by merging multiple files for the line item

Operator Id

Numeric

Internal user ID of operator who has been assigned the job (selected in ‘Production View’ screen)

Delivery Details

Contains information about delivery details of the order

Shipping Quantity

Numeric

Quantity of order line item

Shipment Date

Date

Delivery date of the order

Shipment Method Id

Numeric

Internal ID of delivery method for the order (e.g.

1 - Customer Pick-Up

2 - Courier

3 - FedEx Overnight

4 - UPS Overnight

Shipment Tracking Code

String

Tracking code for the order line item; this is used to track the order

Shipment Instructions

String

Any special delivery instructions specific to the order

Order Address

Contains information about order recipients

Order AddressId

Numeric

Internal ID of order delivery address

First Name

String

First name of the recipient

Middle Name

String

Middle name of the recipient

Last Name

String

Last name of the recipient

Company Name

String

Company of the recipient

Department

String

Department of the recipient

Address Line 1

String

Address line 1

Address Line 2

String

Address line 2

City

String

City

State/Province

String

State

Postal Code

String

Zip

Country

String

Country

Phone 1

String

Phone number 1

Phone 2

String

Phone number 2

Fax

String

Fax

Email

String

Email address of the recipient

Inventory

Contains information about product for which the order was placed

Inventory Managed

Boolean

Value that indicates whether the catalog item’s inventory is managed by MarketDirect StoreFront (applicable only for non-printed and static product inventory)

Threshold

Numeric

This is the re-order point (e.g. when inventory level reaches 10, send email notification to the concerned)

Catalog Description

String

Brief description of the product

Catalog Item #

Numeric

Internal ID of the product

Catalog Name

String

Name of the product

Catalog Type

String

Type of product. Could be one of the following:

Static document, Fusion Pro, AdHoc, XMPie, Kit, Non-printed product

SKU

String

Product id

Item Cost

Money

Cost of the product

Created Date

Date

Date of creation of the product

Product Id

Numeric

Internal ID of the printed product (null for non-printed products)

ValID From

Date

Date from which the product is being displayed on the storefront.

ValID To

Date

Date from which the product will no longer be displayed on the storefront.

Inventory

Numeric

Initial inventory of the product

Production Notes

String

Production notes for the operator who will produce the job. These notes are displayed on the production job ticket

when the product order is ready for production and on tickets sent to output devices.

Rank

Numeric

Display priority of the product

MIS Id

String

ManagementInformationSystemId

Long Description

String

Detailed description of the product

Free Tax

Boolean

Value that indicates whether shipping calculations will be made on the product

Free Shipping

Boolean

Value that indicates whether tax calculations will be made on the product

Back Order Allowed

Boolean

Value that indicates whether processing of products that are on back-order is allowed

Output Devices

Contains information about output devices used to produce the order

Device Type Id

Numeric

Internal ID of output device type. Could be one of the following:

0 - Print Shop

1 - EFI MicroPress

2 - CD-ROM Device

3 - EFI Fiery

4 - EFI Balance

5 - EDOX Document Server

6 - EFI OneFlow

7 - Folder

8 - Fiery XF

9 - JDF Connector

Output Directory

String

The directory that is used to store copies of files

Output Device

String

Name of the output device

Device Status

String

Status of the output device. Could be one of the following:

Error, Online, Offline, Warning

Ticket Format

String

Type of job ticket used for the output device. Could be one of the following:

JDF (Job Description Format),

MarketDirect StoreFront

Delivery Method

Contains information about the order delivery method

Shipping Method

String

Name of the delivery method (e.g. Airborne Overnight, Customer pick-up, etc.)

Documents

Contains information about documents in the job ticket

User #

Numeric

Internal ID of buyer, e.g. 10012

Document Received Date

Date

Creation date of the job ticket for the order

Document Type

Numeric

Type of document uploded in the job ticket. Could be one of the following:

0 - User Document

1 - Catalog Document

2 - Variable Data Document

Copies

Numeric

Number of copies for the order

Document Files

Contains information about files in the job ticket

Position In Document

Numeric

Index of the file in the job ticket

Output Device Id

Numeric

Internal ID of the output device used for the job

Order Document Status

Numeric

Internal ID of the order document status. Could be one of the following:

1 - User Approved

2 - Approved

3 - Setup

4 - Printing

5 - Binding

6 - Error

7 - Canceled

8 - Printed

9 - Completed

10 - Deleted

11 - Waiting Approval

12 - Transferring

13 - Transferred

14 - Rejected

Output Device Types

Contains information about output device types

Device Type

String

Type of the output device. Could be one of the following:

Print Shop, EFI MicroPress, CD-ROM Device, EFI Fiery, EFI Balance, EDOX Document Server, EFI OneFlow, Folder, Fiery XF, JDF Connector

Files

Contains information about files uploaded by buyer for the order

File Recieved Date

Date

Creation date of the job ticket for the order

Original File Name

String

Name of the (native) file as uploaded by the buyer

User Note

String

Notes entered by the buyer when uploading an offline file

File Receiving Mode

String

Mode of the file upload. Could be one of the following:

Digital Upload, Offline material, Digital Background Upload

User Files

Contains information about files uploaded by buyer for the order

File Path

String

Path where the buyer’s files (native and converted) are stored

File Status

Numeric

Internal ID of the file status. Could be one of the following:

0 - UploadError

1 - Converting

2 - ConversionSuccess

3 - ConversionError

4 - NativeUpload

5 - OfflineUpload

6 - Preflighting

7 - PreflightComplete

8 - CheckingFonts

9 - FontCheckComplete

10 - FontCheckError

11 - CheckingFontsForNative

12 - FontCheckCompletedForNative

13 - FontCheckErrorForNative

File Size

Numeric

Size of the uploaded file in bytes

File Uploaded Through

String

Method of file upload. Could be one of the following:

·         Web

·         Driver

·         Background

·         Uploader

USERS

Use this root table for getting information about users in the system

Users

Contains basic user details

User #

Numeric

Internal ID of user

Account Status

Numeric

Internal ID of the status of user’s account [0=Inactive, 1=Active]

User Name

String

Login name

Company #

Numeric

Internal ID of user’s company

Facility Id

Numeric

Internal ID of user’s Print Shop/facility

Contact Id

Numeric

Internal ID of user’s contact

Contacts

Contains information about user contacts

Contact Id

Numeric

Internal ID of contact

Display

String

Name of the contact displayed as ‘First Name, Last Name’

Salutation

String

Title of the contact

Department

String

Department of the contact

First Name

String

First name of the contact

Middle Name

String

Middle name of the contact

Last Name

String

Last name of the contact

Address Line 1

String

Address line 1

Address Line 2

String

Address line 2

City

String

City

State/Province

String

State

Postal Code

String

Zip

Country

String

Country

Phone 1

String

Phone number 1

Phone 2

String

Phone number 2

Fax

String

Fax

Reward #

String

Department

String

Department of the contact

Email

String

Email address of the contact

Facility

Contains information about Print Shop used by the user for order placements

Facility Id

Numeric

Internal ID of Print Shop

Printshop

String

Unique name of Print Shop

ContactId

Numeric

Internal ID of Print Shop contact person

Printshop Approval Limit

Money

A currency amount below which the order approval for orders to the Print Shop is automatic (also called ‘floor limit’)

Store Number

String

The name of the store associated with the Print Shop

Active

Boolean

Value indicating whether the printshop is disabled or not [Note that value of ‘False’ means that the printshop is not disabled, i.e. it is active]

User Company

Contains information about company to which the user belongs

Company #

Numeric

Internal ID of the company

Company

String

Name of the company

Cost Center

String

Name of the company’s cost center that is displayed to the buyers on the storefront

Account Code

String

The name the buyer sees for the collection of accounting codes for the company.

Account Code Enabled

Boolean

Value indicating whether accounting codes are enabled for the company

Department

Contains information about department to which the user belongs

Department #

Numeric

Internal ID of the department

Department

String

Name of the department

OPERATOR VIEW

Use this root table to get information about the order line items, as seen by the operator

Operator View

Order #

Numeric

Internal ID of the order

Order LineItem #

Numeric

Internal ID of the order line item

Order Due Date

Date

Date on which the order is due

Order Date

Date

Date on which the order was placed

Order Status #

Numeric

Internal ID for current order status. Could be one of the following:

1 - Input

2 - Approved

3 - Rejected

4 - Processing

5 - Shipped

6 - Cancelled

7 - ManualQuoteRequired

8 - UserApprovalRequired

9 - Deleted

10 - Printed

11 - ApprovalRequired

Status

String

Status description

BW Impression

Numeric

Number of black & white impressions in the order line item

Color Impression

Numeric

Number of color impressions in the order line item

BW Pages

Numeric

Total number of black & white pages in the order line item

Color Pages

Numeric

Total number of color pages in the order line item

Quantity

Numeric

The quantity in the line item.  For printed order, this is the number of copies.  (For catalog documents sold in sets, this is the number of sets.)

Amount

Money

Order line item cost

Company

String

Name of buyer’s company

Last Name

String

Last name of buyer

First Name

String

First name of buyer

User Phone

String

Phone number of buyer

Output Device

String

Name of the output device that produced the customer’s job

Current Printshop

Numeric

Internal ID of buyer’s current printshop

Order Placed At Printshop

Numeric

Internal ID of printshop where the order was placed

File Uploaded Through

String

Method of file upload. Could be one of the following:

Web, Driver, Background Uploader

Catalog Item #

Numeric

Internal ID of order line item product

Catalog Description

String

Name of the product in the order line item

Catalog Type

String

Type of product. Could be one of the following:

Static document, Fusion Pro, AdHoc, XMPie, Kit, Non-printed product

Buyer Supplied Name

String

Name of the product as entered by the buyer when placing the order

Special Instruction

String

Any special instructions the buyer provided

on the ticket or print options pages before placing the order.

Operator Notes

String

Any notes the shop added to the job after the order was placed.

Production Notes

String

Notes added by the administrator who created or edited the product.

Kit Production Note

String

This type of notes will exist only when

the job is part of a kit product and the parent kit product has additional production notes.

INVENTORY

Use this root table for information on products in the system

Inventory

Inventory Managed

Boolean

Value that indicates whether the catalog item’s inventory is managed by MarketDirect StoreFront (applicable only for non-printed and static product inventory)

Threshold

Numeric

This is the re-order point (e.g. when inventory level reaches 10, send email notification to the concerned)

Catalog Description

String

Brief description of the product

Catalog Item #

Numeric

Internal ID of the product

Catalog Name

String

Name of the product

Catalog Type

String

Type of product. Could be one of the following:

Static document, Fusion Pro, AdHoc, XMPie, Kit, Non-printed product

SKU

String

Product id

Item Cost

Money

Cost of the product

Created Date

Date

Date of creation of the product

Product Id

Numeric

Internal ID of the printed product (null for non-printed products)

ValID From

Date

Date from which the product is being displayed on the storefront.

ValID To

Date

Date from which the product will no longer be displayed on the storefront.

Inventory

Numeric

Initial inventory of the product

Production Notes

String

Production notes for the operator who will produce the job. These notes are displayed on the production job ticket

when the product order is ready for production and on tickets sent to output devices.

Rank

Numeric

Display priority of the product

MIS Id

String

ManagementInformationSystemId

Long Description

String

Detailed description of the product

Free Tax

Boolean

Value that indicates whether shipping calculations will be made on the product

Free Shipping

Boolean

Value that indicates whether tax calculations will be made on the product

Back Order Allowed

Boolean

Value that indicates whether processing of products that are on back-order is allowed

OUTPUT DEVICES

Use this root table for getting information about output devices in the system

Output Devices

Contains basic information about output devices

Device Type Id

Numeric

Internal ID of output device type. Could be one of the following:

0 - Print Shop

1 - EFI MicroPress

2 - CD-ROM Device

3 - EFI Fiery

4 - EFI Balance

5 - EDOX Document Server

6 - EFI OneFlow

7 - Folder

8 - Fiery XF

9 - JDF Connector

Output Directory

String

The directory that is used to store copies of files

Output Device

String

Name of the output device

Device Status

String

Status of the output device. Could be one of the following:

Error, Online, Offline, Warning

Ticket Format

String

Type of job ticket used for the output device. Could be one of the following:

JDF (Job Description Format),

MarketDirect StoreFront

Facility

Contains information about Print Shops which use the output devices for producing customer’s jobs

Facility Id

Numeric

Internal ID of Print Shop

Printshop

String

Unique name of Print Shop

ContactId

Numeric

Internal ID of Print Shop contact person

Printshop Approval Limit

Money

A currency amount below which the order approval for orders to the Print Shop is automatic (also called ‘floor limit’)

Store Number

String

The name of the store associated with the Print Shop

Active

Boolean

Value indicating whether the Print Shop is disabled or not [Note that value of ‘False’ means that the Print Shop is not disabled, i.e. it is active]

Output Device Types

Contains information about output device types

Device Type

String

Type of the output device. Could be one of the following:

Print Shop, EFI MicroPress, CD-ROM Device, EFI Fiery, EFI Balance, EDOX Document Server, EFI OneFlow, Folder, Fiery XF, JDF Connector

ACCOUNTING CODES

Use this root table for getting information on company accounting codes

Accounting Codes

Contains basic information about accounting codes used by companies in the system

Account Code

String

Name of the accounting code that the buyer sees during checkout. [Please note that this is at the company level and NOT at the order level.]

Company

Contains information about companies in the system (may or may not be using accounting codes)

Company #

Numeric

Internal ID of the company

Company

String

Name of the company

Cost Center

String

Name of the company’s cost center that is displayed to the buyers on the storefront

Account Code

String

The name the buyer sees for the collection of accounting codes for the company.

Account Code Enabled

Boolean

Value indicating whether accounting codes are enabled for the company

Account Code Usage

Contains information about buyer-supplied account code during order check-out

Account Code By Buyer

String

Account code entered by the buyer when placing an order with payment method as ‘Accounting Codes’

Order Payment Transaction

Contains information about order payments

Payment Method #

Numeric

Internal ID of payment method used for the order

Amount

Decimal

Order amount

Payment Date

Date

Date on which the order payment was made

Project Code TransactionID

Numeric

Internal ID of transaction pertaining to the order

SPENDING ACCOUNT

Use this root table for getting information about spending accounts in the system

Spending Account

Contains basic information about spending account

Account Display Name

String

Display name used to reference the Spending Account. This is the name that is displayed for the user in the banner region of the page. E.g. Department C Spending Account.

Amount

Decimal

The spending limit for the account in the standard currency of the site

Auto Reload Amount

Decimal

This is the amount that will be reloaded (i.e., added to the existing balance for the spending account) on the first day of the reload period specified in the Reload Frequency field.

Auto Reload Date

Date

Date on which to reload the spending account

Auto Reload Mode

Numeric

The interval at which to reload the spending account. Could be one of the following values:

0 - OneTime,

1 - Monthly,

2 - Quarterly,       

3 - Halfyearly,

4 - Annual,

Allow Carry Over

Boolean

Value indicating whether it is allowed to let the previous balance of the spending account carry over into the amount reloaded

Account Deleted

Boolean

Value indicating if the spending account is deleted

Account Email

String

email ID to which notifications of inconsistent account usage, etc. should be sent.

Account Type

Numeric

Type of spending account. Could be one of the following:

0 - User Level,

1 - Department Level

Spending Account Name

String

Name of the spending account

Company Name

String

Name of the company that the user / department is associated with

User Spending Details

Contains information about transactions that have used spending accounts

Order #

Numeric

Internal ID of order that is placed against the spending account

Amount Spend

Decimal

Order amount used from the spending account

Amount Available

Decimal

Balance available in the spending account after the order placement

Last Used Date

Date

Order date

Users

Contains information about buyers who have used spending account for order payments

User #

Numeric

Internal ID of user

Account Status

Numeric

Internal ID of the status of user’s account [0=Inactive, 1=Active]

User Name

String

Login name

Company #

Numeric

Internal ID of user’s company

Facility Id

Numeric

Internal ID of user’s Print Shop/facility

Contact Id

Numeric

Internal ID of user’s contact

Contacts

Contains information about the buyers’ contacts

Contact Id

Numeric

Internal ID of contact

Display

String

Name of the contact displayed as ‘First Name, Last Name’

Salutation

String

Title of the contact

Department

String

Department of the contact

First Name

String

First name of the contact

Middle Name

String

Middle name of the contact

Last Name

String

Last name of the contact

Address Line 1

String

Address line 1

Address Line 2

String

Address line 2

City

String

City

State/Province

String

State

Postal Code

String

Zip

Country

String

Country

Phone 1

String

Phone number 1

Phone 2

String

Phone number 2

Fax

String

Fax

Reward #

String

Department

String

Department of the contact

Email

String

Email address of the contact

VAT Number

String

VAT number

Facility

Contains information about printshop that the buyer uses for placing orders

Facility Id

Numeric

Internal ID of Print Shop

Printshop

String

Unique name of Print Shop

ContactId

Numeric

Internal ID of Print Shop contact person

Printshop Approval Limit

Money

A currency amount below which the order approval for orders to the Print Shop is automatic (also called ‘floor limit’)

Store Number

String

The name of the store associated with the Print Shop

Active

Boolean

Value indicating whether the printshop is disabled or not [Note that value of ‘False’ means that the printshop is not disabled, i.e. it is active]

User Company

Contains information about company that the buyer belongs to

Company #

Numeric

Internal ID of the company

Company

String

Name of the company

Cost Center

String

Name of the company’s cost center that is displayed to the buyers on the storefront

Account Code

String

The name the buyer sees for the collection of accounting codes for the company.

Account Code Enabled

Boolean

Value indicating whether accounting codes are enabled for the company

Department

Contains information about the department that the buyer belongs to

Department #

Numeric

Internal ID of the department

Department

String

Name of the department