Oracle ATG Web Commerce uses the following tables to store information about promotions:

dcs_promotion

This table contains information about promotions.

Column

Data Type

Constraint

promotion_id

VARCHAR(40)

NOT NULL UNIQUE

(primary key)

The unique identifier associated with the promotion.

version

INT

NOT NULL

The version of the promotion. Oracle ATG Web Commerce uses this value to allow several people to edit the same promotion at the same time.

creation_date

DATE

NULL

The date the promotion was created.

start_date

DATE

NULL

The date on which the promotion will become available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.

end_date

DATE

NULL

The last date on which the promotion will be available. This is an optional field that can be used by the SQL Repository as part of an RQL filter to prevent items from being loaded from the database.

display_name

VARCHAR(254)

NULL

The name of the promotion that is displayed in the ACC.

description

VARCHAR(254)

NULL

A text description of the promotion.

promotion_type

INT

NULL

Indicates the type of promotion.

enabled

NUMERIC(1)

NULL CHECK(enabled in (01))

Determines whether or not the promotion is valid.

begin_usable

DATE

NULL

The date Oracle ATG Web Commerce will begin using this promotion.

end_usable

DATE

NULL

The date ATG Commerce will stop using this promotion.

priority

INT

NULL

The order in which the promotion should be applied. Low priority takes precedence.

global

NUMERIC(1)

NULL CHECK(global in (01))

Determines whether this promotion is global.

anon_profile

NUMERIC(1)

NULL CHECK(anon_profile in (01))

Determines whether the promotion should be given to users with anonymous profiles.

allow_multiple

NUMERIC(1)

NULL CHECK(allow_multiple in (01))

Determines whether a user can receive more than one copy of the promotion.

uses

INT

NULL

Determines how many times the promotion can be used by a single customer.

rel_expiration

NUMERIC(1)

NULL CHECK(rel_expiration in (0,1))

time_until_expire

integer

NULL

The time left until the promotion expires.

dcs_promo_media

This table contains information about media used in promotions.

Column

Data Type

Constraint

promotion_id

VARCHAR(40)

NOT NULL

(primary key)

The unique identifier of the promotion to which this belongs. References dcs_promotion(promotion_id)

tag

VARCHAR(42)

NOT NULL

(primary key)

The identifier for the promotion instruction.

media_id

VARCHAR(40)

NOT NULL

The unique identifier associated with this media item. References dcs_media(media_id)

dcs_discount_promo

This table contains information about promotion discounts. Oracle ATG Web Commerce uses this information to deduct the correct amount from a user’s order, based on the rules of the promotion.

Column

Data Type

Constraint

promotion_id

VARCHAR(40)

NOT NULL UNIQUE

(primary key)

The unique identifier associated with the promotion. References dcs_promotion(promotion_id)

calculator

VARCHAR(254)

NOT NULL

The Oracle ATG Web Commerce calculator that interprets the promotion and applies the discount.

adjuster

DOUBLE PRECISION

NOT NULL

The number by which the promotion discounts. For example, the percent off the purchase.

pmdl_rule

LONG VARCHAR

NOT NULL

The promotion rule that specifies under what conditions the promotion applies.

one_use

NUMERIC (1, 0)

DEFAULT NULL

Determines whether a promotion can be used more than once for a given user.

dcs_promo_upsell

This table contains information about upsell promotions. Oracle ATG Web Commerce uses this information to determine if the upsell feature is enabled in a promotion.

Column

Data Type

Constraint

promotion_id

VARCHAR(40)

NOT NULL

(primary key)

The unique identifier associated with the promotion. References dcs_promotion(promotion_id)

upsell

NUMERIC (1,0)

DEFAULT NULL

Determines whether upselling is enabled for a promotion.

dcs_upsell_action

This table contains information about dynamic products used in Upsell Actions.

Column

Data Type

Constraint

action_id

VARCHAR(40)

NOT NULL

(primary key)

The unique identifier associated with the Upsell Action.

version

integer

NOT NULL

Manages the optimistic locking feature of the SQL Repository. This value is automatically incremented by the SQL Repository when any value of the item is modified. If you change rows directly outside of the Oracle ATG Web Commerce framework, you should also increment the version number.

name

VARCHAR(40)

NOT NULL

The name of the Upsell Action.

upsell_prd_grp

LONG VARCHAR

DEFAULT NULL

The content group associated with an Upsell Action.

dcs_close_qualif

This table contains information about Closeness Qualifiers.

Column

Data Type

Constraint

close_qualif_id

VARCHAR(40)

NOT NULL

(primary key)

The ID of the Closeness Qualifier.

version

integer

NOT NULL

Manages the optimistic locking feature of the SQL Repository. This value is automatically incremented by the SQL Repository when any value of the item is modified. If you change rows directly outside of the Oracle ATG Web Commerce framework, you should also increment the version number.

name

VARCHAR(40)

NOT NULL

The name of the Closeness Qualifier.

priority

integer

DEFAULT NULL

The priority given to a Closeness Qualifier in the context of a promotion. Closeness Qualifiers are evaluated in the order specified by their priority.

qualifier

LONG VARCHAR

DEFAULT NULL

The PMDL rule that describes under which circumstances the Closeness Qualifier applies.

upsell_media

VARCHAR(40)

DEFAULT NULL

The media item associated with the Closeness Qualifier.

upsell_action

VARCHAR(40)

DEFAULT NULL

The Upsell Action assigned to the Closeness Qualifier.

dcs_prm_cls_qlf

This table associates Closeness Qualifiers with promotions.

Column

Data Type

Constraint

promotion_id

VARCHAR(40)

NOT NULL

(primary key)

The unique identifier associated with the promotion. References dcs_promotion(promotion_id)

closeness_qualif

NUMERIC (1,0)

NOT NULL

The unique identifier associated with the Closeness Qualifier. References dcs_close_qualif(close_qualif_id)

dcs_upsell_prods

This table associates fixed products with Upsell Actions.

Column

Data Type

Constraint

action_id

VARCHAR(40)

NOT NULL

(primary key)

The unique identifier associated with the Upsell Action. References dcs_upsell_action(action_id)

product_id

VARCHAR(40)

NOT NULL

The ID of a product associated with the Upsell Action.

sequence_num

integer

NOT NULL

Used to order rows in this table.

dcs_prom_used_evt

This table contains information about Uses Promotion events.

Column

Data Type

Constraint

id

VARCHAR(40)

NOT NULL UNIQUE

(primary key)

The ID of the event.

clocktime

TIMESTAMP

NULL

The date and time that the event was sent.

orderid

VARCHAR(40)

NULL

The ID of the order for which the promotion was used.

promotionid

VARCHAR(40)

NULL

The ID of the promotion that was used.

order_amount

NUMERIC(26,7)

NULL

The amount of the order for which the promotion was used.

discount

NUMERIC(26,7)

NULL

The amount discounted as a result of the promotion that was used.

profileid

VARCHAR(40)

NULL

The profile ID of the user associated with the request when this message is sent in the context of an HTTP request.

sessionid

VARCHAR(100)

NULL

The current session ID associated with the request when this message is sent in the context of an HTTP request.

parentsessionid

VARCHAR(100)

NULL

The parent session ID. This ID may be different from the request’s current session ID on application servers that use a separate session ID for each Web application.

dcs_promo_rvkd

This table contains information about Promotion Revoked events.

Column

Data Type

Constraint

id

VARCHAR(40)

NOT NULL UNIQUE

(primary key)

The ID of the event.

time_stamp

TIMESTAMP

NULL

The date and time that the event was sent.

promotionid

VARCHAR(254)

NOT NULL

The ID of the promotion that was revoked.

profileid

VARCHAR(254)

NOT NULL

The profile ID of the user associated with the request when this message is sent in the context of an HTTP request.

sessionid

VARCHAR(100)

NULL

The current session ID associated with the request when this message is sent in the context of an HTTP request.

parentsessionid

VARCHAR(100)

NULL

The parent session ID. This ID may be different from the request’s current session ID on application servers that use a separate session ID for each Web application.

dcs_promo_grntd

This table contains information about Promotion Offered events.

Column

Data Type

Constraint

id

VARCHAR(40)

NOT NULL UNIQUE

(primary key)

The ID of the event.

time_stamp

TIMESTAMP

NULL

The date and time that the event was sent.

promotionid

VARCHAR(254)

NOT NULL

The ID of the promotion that was offered.

profileid

VARCHAR(254)

NOT NULL

The profile ID of the user associated with the request when this message is sent in the context of an HTTP request.

sessionid

VARCHAR(100)

NULL

The current session ID associated with the request when this message is sent in the context of an HTTP request.

parentsessionid

VARCHAR(100)

NULL

The parent session ID. This ID may be different from the request’s current session ID on application servers that use a separate session ID for each Web application.