DBOrderPromo

The DBOrderPromo component, which usually appears in the Order Adjust Price stage of an order processing pipeline, runs a query to retrieve data about promotions from the database and then adjusts the item price accordingly.

Note  This component is used for compatibility with Commerce Server version 2.0 sites, in which the database connection is provided by the Content object in the Pipe Context. Commerce Server 3.0 sites should use the DBOrderPromoADO component.

• Query. Specifies either the full SQL text of the query that is executed by this component or the name of a query whose full SQL text is stored in the Content object.

This query must return at least the following database columns:

• cond_column. Name of the item attribute against which the promotion condition is checked. If it is a promotion on hats, then this would be set to the name of the column that stores the product description (for example, _product_type).
• cond_op. Comparison operator that compares cond_column with cond_value; for example = or <>.
• cond_value. Value indicating the item being promoted; for example, hats. If this value is a number, it must be an integer value; floating point numbers (for example, 10.0) are not accepted.
• cond_all. Optional field. If set to 1 (True), then any product in the set of products to be purchased will trigger this promotion. Otherwise, this promotion will be triggered only by products that meet the criteria defined in cond_column, cond_op, and cond_value (the condition set). Products are removed from the condition and award sets after they have been involved in a promotion , in order to prevent a single product from being a participant in multiple promotions.
• award_column. Value indicating the award criterion.
• award_op. Comparison operator that compares award_column with award_value; for example = or <>.
• award_value. Indicates the item being awarded, for example, gloves.
• award_all. Optional field. If set to 1 (True), then the award can be applied to any product. Otherwise, this award will be applied only to products that meet the criteria defined in award_column, award_op, and award_value (the award set).
• shopper_column. Value indicating the shopper-specific information; can be @ (a wildcard value) indicating that all shoppers are eligible for the promotion.
• shopper_op. Should be set to = if a shopper is specified; otherwise, it is @.
• shopper_value. Value indicating the type of shopper; if it is any shopper, set it to @.
• shopper_all. Optional field indicating whether this promotion is available to all customers. If this value is 0 (False), the promotion is available only to customers who meet the criteria defined in shopper_column, shopper_op, and shopper_value.
• cond_min.Value, in cents (or other currency unit), or a number of units of the minimum purchase necessary to be eligible for the promotion. The interpretation of cond_min depends on cond_basis.
• cond_basis. Value indicating whether cond_min is measured in price or quantity (P or Q, respectively).
• award_max. Size of the award set.
• disc_value. Number indicating the amount of the discount.
• disc_type. Type of discount, either % or $.
• date_start. Optional field. Date the promotion begins, at one second after midnight.
• date_end. Optional field. Date the promotion ends, at one second after midnight. Because date_start and date_end are actually date/time pairs that specify midnight, date_end excludes the date specified. Thus, if you want the last day of a sale to be 3/31, be sure to specify that the sale ends on 4/1.

Remarks

An order promotion has three criteria: the customer criterion, a condition criterion, and an award criterion. If the customer does not match the customer criterion, the promotion is not applied. If the customer does match the customer criterion, then the other two criteria are applied to all the items in the order, producing two sets: the condition set (those matching the condition criterion) and the award set (those matching the award criterion).

For example, if the promotion is Buy $100 in hats, get a free pair of gloves, the condition set is all of the hats the customer bought, and the award set is the gloves the customer will receive for free. Often, the same item is in both sets.

The condition set must be of some minimum size ($100 in this example). This is given by two variables: cond_min (10000 in this case, because the value is given in cents) and cond_basis (P in this case, meaning that size is measured by price and not by quantity).

The award set is limited to a maximum size (one in the example); this size is always measured in quantity:

• A maximum of 0 means no maximum; thus, if a customer meets the qualifications, discount on as many of the award items as the customer chooses to purchase. In this example, that would mean Buy $100 in hats, get as many free pairs of gloves as you want.
• If the size of the condition set doubles (or triples or quadruples), the maximum size of the award is similarly multiplied. In this example, if the customer buys $500 worth of hats, the customer gets five free pairs of gloves.

There are two types of discounts (specified by the value disc_type):

• Percentage. Where disc_type is % and disc_value is interpreted to mean percentage.
• Cents off. Where disc_type is $ and disc_value is interpreted to mean number of currency units off each product in the award set.

The preceding example has the values set as follows.

Note  A given product can be adjusted only once. For this reason, the order in which promotions are applied is important. For example, if you apply a query that returns two rows from a promotion table, only the first promotion will be applied.

Related Topic

• DbOrderPromoADO