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

dcspp_order

This table stores information associated with a user’ s order such as the submitted date, the state, and the price.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

The ID associated with the order.

type

integer

not null

An integer representing the specific type of item descriptor.

version

integer

not null

The version of the data within the row.

order_class_type

varchar(40)

null

A string representing the type of order.

profile_id

varchar (40)

null

The ID of the profile to which the order belongs.

description

varchar (64)

null

A text description of the order.

state

varchar (40)

null

The state of the order.

state_detail

varchar (254)

null

Detailed state information about the order.

created_by_order

varchar (40)

null

Contains the ID of the order from which this order was created. Used by the Customer Service Module in situations such as exchanges.

origin_of_order

enum(10)

null

Enumerated property that provides information about where the order came from. The default options are:

default
scheduledOrder
contactCenter

You can extend this to track orders from other sources.

creation_date

date

null

The date and time the order was created.

submitted_date

date

null

The date and time the order was submitted for processing.

last_modified_date

date

null

The date the order was last modified.

Note: To improve performance in an Online Transactional Processing (OLTP) environment, you may drop this index.

completed_date

date

null

The date the order was completed.

price_info

varchar (40)

null

The price data for the order.

tax_price_info

varchar (40)

null

The price data for the tax of the order.

explicitly_saved

boolean

null

Customers can save orders without checking out to start a fresh order. If this is done, explicitly_saved is set to true.

When the customer starts another session, the current active shopping cart is identified as an incomplete order where explicitly_saved is false.

agent_id

varchar (40)

null

ID of the agent associated with the order, if any.

sales_channel

number(10)

null

Identifier of the sales channel through which the order was placed, if any.

creation_site_id

varchar (40)

null

Contains the ID of the site on which this order was created. Used by the multisite feature.

site_id

varchar (40)

null

Contains the ID of the site the user was on for the most recent activity on this order. Used by the multisite feature.

gwp

boolean

null

Indicates whether the order includes an item added as a result of a gift with purchase promotion.

dcspp_ship_group

This table stores information associated with a shipping group such as the shipping date, the state, and the shipping cost.

Column

Data Type

Constraint

Description

shipping_group_id
(primary key)

varchar (40)

not null

The ID associated with the shipping group.

type

integer

not null

An integer representing the specific type of item descriptor.

version

integer

not null

The version of the data within the row.

shipgrp_class_type

varchar (40)

null

A string representing the type of shipping group.

shipping_method

varchar (40)

null

The shipping method, for example Ground, Next Day, etc.

description

varchar (64)

null

A text description of the shipping order.

ship_on_date

date

null

The date to ship the items within the shipping group.

actual_ship_date

date

null

The actual date that the items within the shipping group were shipped.

state

varchar (40)

null

The state of the shipping group.

state_detail

varchar (254)

null

Detailed state information about the shipping group.

submitted_date

date

null

The date that the shipping group was submitted for processing.

price_info

varchar (40)

null

The price data for the shipping group.

order_ref

varchar (40)

null

The ID of the order to which this shipping group belongs.

dcspp_pay_group

This table stores information associated with a payment group such as the amount, the state, and the submitted date.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar (40)

not null

The ID associated with the payment group.

type

integer

not null

An integer representing the specific type of item descriptor.

Version

integer

not null

The version of the data within the row.

paygrp_class_type

varchar (40)

null

A string representing the type of payment group.

payment_method

varchar (40)

null

The payment method, for example Credit Card, Gift Certificate, etc.

amount

numeric (19,7)

null

The amount of the order this payment group represents.

amount_authorized

numeric (19,7)

null

The amount of the order that was authorized.

amount_debited

numeric (19,7)

null

The amount of the order was been debited.

amount_credited

numeric(19,7)

null

The amount of the order that was credited.

currency_code

varchar (10)

null

The currency code for the payment group.

state

varchar (40)

null

The state of the payment group.

state_detail

varchar (254)

null

Detailed state information about the payment group.

submitted_date

date

null

The date and time the payment group was submitted for processing.

order_ref

varchar (40)

null

The ID of the order to which this payment group belongs.

dcspp_item

This table stores all the properties of an item in a cart such as the SKU ID, the quantity, and the price.

Column

Data Type

Constraint

Description

commerce_item_id
(primary key)

varchar(40)

not null

The unique identifier associated with the item.

type

integer

not null

An integer representing the specific type of item descriptor.

version

integer

not null

The version of the data within the row.

item_class_type

varchar(40)

null

A string representing the type of commerce item.

catalog_id

varchar(40)

null

The ID of the catalog associated with this commerce item.

catalog_ref_id

varchar(40)

null

The catalog reference ID of the item that this item represents (the SKU).

catalog_key

varchar(40)

null

An optional property that can be used to signify from which catalog the item was added. Usually contains a locale.

product_id

varchar(40)

null

The ID of the product that this item represents.

quantity

integer

null

The quantity of the item.

state

varchar(40)

null

The state of the commerce item.

state_detail

varchar(254)

null

Detailed state information about the commerce item.

price_info

varchar(40)

null

The price data for the commerce item.

order_ref

varchar(40)

null

The ID of the order to which this commerce item belongs.

site_id

varchar(40)

null

ID of the site from which the item was added. Used by the multisite feature.

dcspp_relationship

This table stores information associated with the relationship of an order such as the relationship type.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

The unique identifier associated with the relationship.

type

integer

not null

An integer representing the specific type of item descriptor.

version

integer

not null

The version of the data within the row.

rel_class_type

varchar(40)

null

A string representing the type of relationship.

relationship_type

varchar(40)

null

The type of the relationship. For example, SHIPPINGQUANTITY.

order_ref

varchar(40)

null

The ID of the order to which this relationship belongs.

dcspp_rel_orders

This table stores the individual entries of the specialInstructions property of the atg.commerce.order.Order object.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

References dcspp_order(order_id).

related_orders

varchar(40)

not null

The ID of another order which was created in reference to this order.

seq_num
(primary key)

varchar(40)

not null

The sequence number in the list of related orders.

dcspp_order_inst

This table stores the individual entries of the specialInstructions property of the atg.commerce.order.Order object.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

The unique identifier of the order to which this instruction belongs. References dcspp_order(order_id).

tag
(primary key)

varchar(42)

not null

The identifier for the order instruction.

special_inst

varchar(254)

null

The instruction information.

dcspp_order_sg

This table stores the list of ShippingGroup ids that are contained within an Order.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

The unique identifier of the order to which the shipping group belongs. References dcspp_order(order_id).

shipping_groups

varchar(40)

not null

The ID of the shipping group.

sequence_num
(primary key)

integer

not null

The element number within the list to which the shipping group belongs.

dcspp_order_pg

This table stores the list of PaymentGroup IDs that are contained within an order.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

The unique identifier of the order to which the payment group belongs. References dcspp_order(order_id).

payment_groups

varchar(40)

not null

The ID of the payment group.

sequence_num
(primary key)

integer

not null

The element number within the list to which the payment group belongs.

dcspp_order_item

This table stores the list of CommerceItem IDs that are contained within an order.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

The unique identifier of the order to which the commerce item belongs. References dcspp_order(order_id).

commerce_items

varchar(40)

not null

The ID of the commerce item.

sequence_num
(primary key)

integer

not null

The element number within the list to which the commerce item belongs.

dcspp_order_rel

This table stores the list of Relationship IDs that are contained within an order.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

The unique identifier of the order to which the relationship belongs. References dcspp_order(order_id)

relationships

varchar(40)

not null

The ID of the relationship.

sequence_num
(primary key)

integer

not null

The element number within the list to which the relationship belongs.

dbcpp_sched_order

This table contains information related to scheduled orders.

Column

Data Type

Constraint

Description

scheduled_order_id
(primary key)

varchar(40)

not null

The repository ID of the scheduled order entry.

type

int

not null

The repository type of the item.

version

int

not null

The repository version number.

name

wvarchar(32)

null

The user defined name of a scheduled order.

profile_id

varchar(40)

null

The profile ID of the owner of a scheduled order.

create_date

timestamp

null

The creation date of the scheduled order.

start_date

timestamp

null

The date that the scheduled order is to begin submission.

end_date

timestamp

null

The date that the scheduled order is to terminate submission.

template_order

varchar(32)

null

The order ID of the template order.

state

int

null

The state of the scheduled order.

next_scheduled

timestamp

null

The time of the next scheduled submission.

schedule

varchar(255)

null

The schedule of when the order is to be submitted.

siteId

varchar(40)

null

ID of the site with which this scheduled order is associated.

dcspp_ship_inst

This table contains information about any special instructions associated with the shipping group.

Column

Data Type

Constraint

Description

shipping_group_id
(primary key)

varchar(40)

not null

The unique identifier of the shipping group to which this instruction belongs. References dcspp_ship_group
(shipping_group_id)
.

tag
(primary key)

varchar(42)

not null

The identifier for the shipping group instruction.

special_inst

varchar(254)

null

The instruction information.

dcspp_hrd_ship_grp

This table contains information about the hardgood shipping group such as the tracking number.

Column

Data Type

Constraint

Description

shipping_group_id
(primary key)

varchar(40)

not null

The unique identifier associated with the shipping group. References dcspp_ship_group
(shipping_group_id)
.

tracking_number

varchar(40)

null

The tracking number for the hardgood shipping group.

dcspp_ele_ship_grp

This table contains information about an electronic shipping group such as the e-mail address.

Column

Data Type

Constraint

Description

shipping_group_id
(primary key)

varchar(40)

not null

The unique identifier associated with the shipping group. References dcspp_ship_group
(shipping_group_id)
.

email_address

varchar(40)

null

The e-mail address of the user to which this shipping group belongs.

dcspp_isp_ship_grp

Column

Data Type

Constraint

Description

shipping_group_id
(primary key)

varchar(40)

not null

The unique identifier associated with the shipping group. References dcspp_ship_group
(shipping_group_id)
.

location_id

varchar (40)

null

The location at which the item will be picked up by the customer.

first_name

varchar (40)

null

The first name of the customer who will pick up the item.

middle_name

varchar (40)

null

The middle name of the customer who will pick up the item.

last_name

varchar (40)

null

The last name of the customer who will pick up the item.

dcspp_ship_addr

This table contains information about the shipping address of a shipping group.

Column

Data Type

Constraint

Description

shipping_group_id
(primary key)

varchar(40)

not null

The unique identifier associated with the shipping group. References dcspp_ship_group
(shipping_group_id)
.

prefix

varchar(40)

null

The prefix associated with the name in the shipping address. (Miss, Mrs., etc.)

first_name

varchar(40)

null

The first name of the user at this shipping address.

middle_name

varchar(40)

null

The middle name of the user at this shipping address.

last_name

varchar(40)

null

The last name of the user at this shipping address.

suffix

varchar(40)

null

The suffix associated with the name in the shipping address. (Jr. Sr., etc.)

job_title

varchar(40)

null

The job title of the person in the shipping address.

address_1

varchar(50)

null

The street and number of this shipping address.

address_2

varchar(50)

null

The street and number of this shipping address.

address_3

varchar(50)

null

The street and number of this shipping address.

city

varchar(40)

null

The city of this shipping address.

county

varchar(50)

null

The county of this shipping address.

state

varchar(40)

null

The state of this shipping address.

postal_code

varchar(10)

null

The postal code of this shipping address.

country

varchar(40)

null

The country of this shipping address.

phone_number

varchar(40)

null

The phone number of the user at this shipping address.

fax_number

varchar(40)

null

The fax number of the user at this shipping address.

email

varchar(40)

null

The e-mail of the user at this shipping address.

dcspp_hand_inst

This table stores all the properties of the atg.commerce.order.HandlingInstruction object such as the item and quantity to which the handling instruction belongs.

Column

Data Type

Constraint

Description

handling_inst_id
(primary key)

varchar(40)

not null

The unique identifier associated with the handling instruction.

type

integer

not null

An integer representing the specific type of item descriptor.

version

integer

not null

The version of the data within the row.

hndinst_class_type

varchar(40)

null

A string representing the type of handling instruction.

handling_method

varchar(40)

null

The handling method.

shipping_group_id

varchar(40)

null

The ID of the shipping group to which this handling instruction belongs.

commerce_item_id

varchar(40)

null

The ID of the commerce item that this handling instruction represents.

quantity

integer

null

The quantity of the commerce item to which this handling instruction applies.

dcspp_gift_inst

This table stores all the properties of a gift list handling instruction object such as the gift list ID.

Column

Data Type

Constraint

Description

handling_inst_id
(primary key)

varchar(40)

not null

The unique identifier associated with the gift list handling instruction. References dcspp_hand_inst(handling_inst_id).

giftlist_id

varchar(40)

null

The ID of the gift list that this handling instruction represents.

giftlist_item_id

varchar(40)

null

The ID of the item within the gift list that this handling instruction represents.

dcspp_sg_hand_inst

This table stores the list of HandlingInstruction IDs that are contained within a shipping group.

Column

Data Type

Constraint

Description

shipping_group_id
(primary key)

varchar(40)

not null

The unique identifier of the shipping group to which the handling instruction belongs. References dcspp_ship_group
(shipping_group_id)
.

handling_instrs

varchar(40)

not null

The ID of the handling instruction.

sequence_num
(primary key)

integer

not null

The element number within the list to which the handling instruction belongs.

dcspp_pay_inst

This table stores the individual entries of the specialInstructions property of the atg.commerce.order.PaymentGroup object. The table contains special order instructions for each item in a certain payment group.

Column

Data Type

Constraint

Description

payment_group_id

varchar(40)

not null

The unique identifier associated with the payment group. References dcspp_pay_group
(payment_group_id)
.

Tag

varchar(42)

not null

The identifier for the payment group instruction.

special_inst

varchar(254)

null

The instruction information.

dcspp_config_item

This table includes information on configurable commerce items.

Column

Data Type

Constraint

Description

config_item_id
(primary key)

varchar(40)

not null

References dcspp_item
(commerce_item_id)
.

reconfig_data

varchar(255)

null

Optional reconfiguration data about this configurable commerce item.

notes

varchar(255)

null

Optional notes about this configurable commerce item.

dcspp_subsku_item

This table includes information on a sub-SKU.

Column

Data Type

Constraint

Description

subsku_item_id
(primary key)

varchar(40)

not null

References dcspp_item
(commerce_item_id)
.

ind_quantity

integer

null

The quantity of this sub-SKU that is contained within a single configurable commerce item.

dcspp_item_ci

This table includes information on a configurable commerce item.

Column

Data Type

Constraint

Description

commerce_item_id
(primary key)

varchar(40)

not null

References dcspp_item
(commerce_item_id)
.

commerce_items

varchar(40)

not null

The ID of the sub-SKU commerce item within this configurable commerce item.

sequence_num
(primary key)

integer

not null

The sequence number in the list of sub-SKU commerce items.

dcspp_gift_cert

This table stores all the properties of a gift certificate in an order such as the profile ID and the gift certificate number.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

The unique identifier associated with the payment group. References dcspp_pay_group(payment_group_id).

profile_id

varchar(40)

null

The profile ID to which this gift certificate payment group belongs.

gift_cert_number

varchar(50)

null

The gift certificate number that this gift certificate payment group represents.

dcspp_store_cred

This table includes information on store credit.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

References dcspp_payment_group
(payment_group_id)
.

profile_id

varchar(40)

null

The profile id of the user associated with the store credit referenced in the store_cred_number.

store_cred_number

varchar(50)

null

The store credit number associated with this store credit.

dcspp_credit_card

This table stores all the properties of a credit card in an order such as the credit card number, the expiration month, the day of the month, and year, and the credit card type.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

The unique identifier associated with the payment group. References dcspp_pay_group
(payment_group_id)
.

credit_card_number

varchar(40)

null

The credit card number.

credit_card_type

varchar(40)

null

The type of the credit card.

expiration_month

varchar(20)

null

The month the credit card expires.

exp_day_of_month

varchar(20)

null

The day of the month the credit card expires.

expiration_year

varchar(20)

null

The year the credit card expires.

dcspp_bill_addr

This table stores the billing address information for a certain payment group.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

The unique identifier associated with the payment group. References dcspp_pay_group(payment_group_id).

prefix

varchar(40)

null

The prefix associated with the name in the billing address. (Miss, Mrs., etc.)

first_name

varchar(40)

null

The first name of the user at this billing address.

middle_name

varchar(40)

null

The middle name of the user at this shipping address.

last_name

varchar(40)

null

The last name of the user at this shipping address.

suffix

varchar(40)

null

The suffix associated with the name in the billing address. (Jr. Sr., etc.)

job_title

varchar(40)

null

The job title of the person in the billing address.

address_1

varchar(50)

null

The street and number of this billing address.

address_2

varchar(50)

null

The street and number of this billing address.

address_3

varchar(50)

null

The street and number of this billing address.

city

varchar(40)

null

The city of this billing address.

county

varchar(40)

null

The county of this billing address.

state

varchar(40)

null

The state of this billing address.

postal_code

varchar(10)

null

The postal code of this billing address.

country

varchar(40)

null

The country of this billing address.

phone_number

varchar(40)

null

The phone number of the user at this billing address.

fax_number

varchar(40)

null

The fax number of the user at this billing address.

email

varchar(40)

null

The e-mail of the user at this billing address.

dcspp_pay_status

This table stores all the properties of a payment status in a payment group such as the amount, the transaction success, and the transaction timestamp.

Column

Data Type

Constraint

Description

status_id
(primary key)

varchar(40)

not null

The unique identifier associated with the payment status.

type

integer

not null

An integer representing the specific type of item descriptor.

version

integer

not null

The version of the data within the row.

trans_id

varchar(50)

null

The transaction ID of the payment status.

amount

numeric (19,7)

null

The amount this payment status represents.

trans_success

numeric (1)

null

A flag indicating the success of the transaction. Checks trans_success in (01).

error_message

varchar(254)

null

The error message of the transaction, if any.

trans_timestamp

date

null

The timestamp of the transaction.

dcspp_cc_status

This table stores all the properties of a credit card status in a payment group.

Column

Data Type

Constraint

Description

status_id
(primary key)

varchar(40)

not null

The unique identifier associated with the credit card status. References dcspp_pay_status(status_id).

auth_expiration

timestamp

null

The time and date the authorization expires.

avs_code

varchar(40)

null

The address verification code.

avs_desc_result

varchar(254)

null

The address verification descriptive result.

dcspp_gc_status

This table stores all the properties of a gift certificate status in a payment group.

Column

Data Type

Constraint

Description

status_id
(primary key)

varchar(40)

not null

The unique identifier associated with the gift certificate status. References dcspp_pay_status(status_id).

auth_expiration

timestamp

null

The time and date the authorization expires.

dcspp_sc_status

This table stores all the properties of a store credit status in a payment group.

Column

Data Type

Constraint

Description

status_id
(primary key)

varchar(40)

not null

The unique identifier associated with the store credit status. References dcspp_pay_status(status_id).

auth_expiration

timestamp

null

The time and date the authorization expires.

dcspp_auth_status

This table stores the list of PaymentStatus IDs that are contained within a PaymentGroup that represent authorizations.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

The unique identifier of the payment group which the authorization status belongs. References dcspp_pay_group
(payment_group_id)
.

auth_status

varchar(40)

not null

The ID of the authorization status.

sequence_num
(primary key)

integer

not null

The element number within the list to which the authorization status belongs.

dcspp_debit_status

This table stores the list of PaymentStatus IDs that are contained within a PaymentGroup that represent debits.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

The unique identifier of the payment group which the debit status belongs. References dcspp_pay_group(payment_group_id).

debit_status

varchar(40)

not null

The ID of the debit status.

sequence_num
(primary key)

integer

not null

The element number within the list to which the debit status belongs.

dcspp_cred_status

This table stores the list of PaymentStatus IDs that are contained within a PaymentGroup that represent credits.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

The unique identifier of the payment group which the credit status belongs. References dcspp_pay_group(payment_group_id).

credit_status

varchar(40)

not null

The ID of the credit status.

sequence_num
(primary key)

integer

not null

The element number within the list to which the credit status belongs.

dcspp_shipitem_rel

This table contains information about relationships between shipping groups and commerce items. This table stores all the properties of a ShippingGroupCommerceItemRelationship such as shipping group ID, commerce item ID, and relationship quantity.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

The unique identifier associated with the shipping group commerce item relationship. References dcspp_relationship
(relationship_id)
.

shipping_group_id

varchar(40)

null

The ID of the shipping group for this relationship.

commerce_item_id

varchar(40)

null

The ID of the commerce item for this relationship.

quantity

integer

null

The quantity of the commerce item to which this relationship applies.

returned_qty

numeric
(19, 7)

null

The returned quantity of the commerce item.

amount

numeric (19,7)

null

The amount of the commerce item to which this relationship applies.

state

varchar(40)

null

The state of the relationship.

state_detail

varchar(254)

null

Detailed state information about the relationship.

dcspp_rel_range

This table includes information on the ranges associated with a particular relationship. Specifically, ShippingGroupCommerceItemRelationship objects refer to some quantity of a CommerceItem. The range tells you which commerce items are in the given shipping group.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

The relationship that uses this range.

low_bound

integer

null

The index (inclusive) of the first item in the range.

high_bound

integer

null

The index (inclusive) of the last item in the range.

dcspp_det_range

This table includes information on the ranges associated with a particular item price detail. Specifically, DetailedItemPriceInfo objects refer to some quantity of a CommerceItem. The range tells you which commerce items are priced in a particular way.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

The relationship that uses this range.

low_bound

integer

null

The index (inclusive) of the first item in the range.

high_bound

integer

null

The index (inclusive) of the last item in the range.

dcspp_payitem_rel

This table contains information about relationships between payment groups and commerce items. This table stores all the properties of a PaymentGroupCommerceItemRelationship such as payment group ID, commerce item ID, and relationship amount.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

The unique identifier associated with the payment group commerce item relationship. References dcspp_relationship
(relationship_id)
.

payment_group_id

varchar(40)

null

The ID of the payment group for this relationship.

commerce_item_id

varchar(40)

null

The ID of the commerce item for this relationship.

quantity

numeric (19,7)

null

The quantity of the commerce item to which this relationship applies.

amount

numeric (19,7)

null

The amount of the commerce item to which this relationship applies.

dcspp_payship_rel

This table contains information about relationships between payment and shipping groups. This table stores all the properties of a PaymentGroupShippingGroupRelationship such as payment group ID, shipping group ID, and relationship amount.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

The unique identifier associated with the payment group shipping group relationship. References dcspp_relationship
(relationship_id)
.

payment_group_id

varchar(40)

null

The ID of the payment group for this relationship.

shipping_group_id

varchar(40)

null

The ID of the shipping group for this relationship.

amount

numeric (19,7)

null

The amount of the shipping group to which this relationship applies.

dcspp_payorder_rel

This table stores all the properties of a PaymentGroupOrderRelationship such as payment group ID, order ID, and relationship amount.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

The unique identifier associated with the payment group order relationship. References dcspp_relationship
(relationship_id)
.

payment_group_id

varchar(40)

null

The ID of the payment group for this relationship.

order_id

varchar(40)

null

The ID of the order for this relationship.

amount

numeric (19,7)

null

The amount of the order to which this relationship applies.

dcspp_amount_info

This table stores all the common properties of the priceInfo object.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier associated with the amount info.

type

integer

not null

An integer representing the specific type of item descriptor.

version

integer

not null

The version of the data within the row.

currency_code

varchar(10)

null

The currency code of the amount info.

amount

numeric (19,7)

null

The amount of the amount info.

discounted

numeric (1)

null

Flag indicating if a discount has been applied. Checks discounted in (01).

amount_is_final

tinyint

null

Flag indicating if amount is final amount. Checks amount_is_final in (0,1).

dcspp_order_price

This table contains information about the price it costs to put together an order. This table stores all the properties of an OrderPriceInfo object such as raw subtotal, tax, and shipping.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier associated with the order price info. References dcspp_amount_info
(amount_info_id)
.

raw_subtotal

numeric (19,7)

null

The raw subtotal of the order.

tax

numeric (19,7)

null

The tax for the order.

shipping

numeric (19,7)

null

The shipping cost for the order.

manual_adj_total

numeric (19,7)

null

Total amount of all manual adjustments associated with the order.

dcspp_item_price

This table contains information about the price of a specific item. This table stores the properties of an ItemPriceInfo object such as list price, raw total price, and sale price.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier associated with the item price info. References dcspp_amount_info
(amount_info_id)
.

list_price

numeric (19,7)

null

The list price of the item.

raw_total_price

numeric (19,7)

null

The raw total price of the item.

sale_price

numeric (19,7)

null

The sale price of the item.

on_sale

numeric (1)

null

Flag indicating whether item is on sale. Checks on_sale in (01).

qty_discounted

numeric (19,0)

null

The quantity of the items that were discounted.

qty_as_qualifier

numeric (19,0)

null

The quantity of the items that are being used as qualifiers.

order_discount

double precision

null

The discount associated with the item.

price_list

varchar(40)

null

The price list associated with the item.

dcspp_tax_price

This table contains all the tax information associated with an order. This table stores all the properties of a TaxPriceInfo object such as city tax, county tax, state tax, and country tax.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier associated with the tax price info. References dcspp_amount_info
(amount_info_id)
.

city_tax

numeric (19,7)

null

The amount of the city tax.

county_tax

numeric (19,7)

null

The amount of the county tax.

state_tax

numeric (19,7)

null

The amount of the state tax.

country_tax

numeric (19,7)

null

The amount of the country tax.

dcspp_ship_price

This table stores information about the as raw shipping cost of an order

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier associated with the shipping price info. References dcspp_amount_info(amount_info_id).

raw_shipping

numeric (19,7)

null

The amount of the raw shipping cost.

dcspp_amtinfo_adj

This table stores the list of PricingAdjustment IDs that are contained within a PriceInfo object.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier of the amount info to which the adjustment belongs. References dcspp_amount_info(amount_info_id).

adjustments

varchar(40)

not null

The unique identifier of the adjustment.

sequence_num
(primary key)

integer

not null

The element number within the list to which the adjustment belongs.

dcspp_price_adjust

This table stores all the properties of a PricingAdjustment object such as pricing model and adjustment.

Column

Data Type

Constraint

Description

adjustment_id
(primary key)

varchar(40)

not null

The unique identifier associated with the adjustment.

version

integer

not null

The version of the data within the row.

adj_description

varchar(254)

null

A description of the adjustment.

pricing_model

varchar(40)

null

The unique identifier of the pricing model used for this adjustment.

Adjustment

double precision

null

The amount of the adjustment.

qty_adjusted

integer

null

The quantity of items this adjustment represents.

manual_adjustment

varchar(40)

null

The amount of the adjustment.

dcspp_shipitem_sub

This table contains the shipping subtotal for a shipping group in an order.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier of the amount info to which the shipping subtotal info belongs. References dcspp_amount_info
(amount_info_id)
.

shipping_group_id
(primary key)

varchar(42)

not null

The ID associated with the shipping group.

ship_item_subtotal

varchar(40)

not null

The subtotal of the shipping charge.

dcspp_taxshipitem

This table contains the tax information for a shipping group in an order.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier of the amount info to which the shipping tax info belongs. References dcspp_amount_info
(amount_info_id)
.

shipping_group_id
(primary key)

varchar(42)

not null

The ID associated with the shipping group.

tax_ship_item_sub

varchar(40)

not null

The amount of the shipping tax.

dcspp_ntaxshipitem

This table contains information about the non-taxable items in a shipping group in an order.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

References dcspp_amount_info
(amount_info_id)
.

shipping_group_id
(primary key)

varchar(42)

not null

The ID associated with the shipping group.

non_tax_item_sub

varchar(40)

not null

The amount of the shipping group that is non-taxable

dcspp_itmprice_det

This table stores the list of DetailedItemPriceInfo IDs contained within a PriceInfo object.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier of the amount info to which the detailed price info belongs. References dcspp_amount_info
(amount_info_id)
.

cur_price_details

varchar(40)

not null

The detailed price info.

sequence_num
(primary key)

integer

not null

The element number within the list to which the detailed price info belongs.

dcspp_det_price

This table stores detailed item price information such as quantity and quantity as qualifier.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier associated with the detailed item price info.

quantity

numeric (19,0)

null

The quantity of items to which this detailed item price info applies.

qty_as_qualifier

numeric (19,0)

null

The quantity of items that acted as a qualifier.

order_discount

double precision

null

The amount of the order discount.

tax

double precision

null

The amount of the tax on the order.

dcs_submt_ord_evt

This table contains information about Order Submitted events.

Column

Data Type

Constraint

Description

id
(primary key)

varchar(40)

not null unique

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 that was submitted.

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_ord_merge_evt

This table contains information about Orders Merged events.

Column

Data Type

Constraint

Description

id
(primary key)

varchar(40)

not null
unique

The ID of the event.

clocktime

timestamp

null

The date and time that the event was sent.

sourceorderid

varchar(40)

null

The ID of the source order that was merged into the destination order.

destorderid

varchar(40)

null

The ID of the destination order into which the source order was merged.

sourceremoved

tinyint

null

Determines whether the source order is removed after it is merged with the destination order. Checks sourceremoved in (0,1).

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.

dcspp_order_adj

This table contains information about manual adjustments performed by agents using CSC.

Column

Data Type

Constraint

Description

order_id
(primary key component)

varchar(40)

not null

The ID of the order that received the adjustment; foreign key to the dcspp_order table and a component of the primary key.

adjustment_id

varchar(40)

not null

The ID of the adjustment.

sequence_num
(primary key component)

integer

not null

A sequentially generated number that combines with the order_id to create a unique primary key.

dcspp_manual_adj

This table contains information about manual adjustments performed by agents using CSC.

Column

Data Type

Constraint

Description

manual_adjust_id
(primary key)

varchar(40)

not null

The ID of the manual adjustment.

type

integer

not null

The repository item type of the manual adjustment. Possible values are:

0 – OrderFixedAmountAdjustment

adjustment_type

integer

not null

The type of adjustment made. Possible values are:

0 – amountOff (credit)
1 – amountIncrease (debit)

reason

integer

not null

The reason for applying the adjustment. Possible values are:

0 – reasonAppeasement
1 - reasonOther

amount

double

null

Amount of the adjustment.

notes

varchar(255)

null

Agent notes related to this adjustment.

version

integer

not null

The integer incremented with each revision to prevent version conflict.

dcspp_shipitem_tax

That table holds the shippingItemsTaxPriceInfos property of the taxPriceInfo item descriptor in the order repository.

Column

Data Type

Constraint

Description

amount_info_id
(primary key)

varchar(40)

not null

The unique identifier associated with the tax PriceInfo.

shipping_group_id
(primary key)

varchar(40)

not null

The ID of the shipping group described by the PriceInfo.

ship_item_tax

varchar(40)

not null

The ID of a PriceInfo that describes the tax amount for the items in a particular shipping group in the order.

dbcpp_approverids

This table contains profile ids of users who have approved the order.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

References dcspp_order(order_id).

approver_ids

varchar(40)

not null

Profile ID for an approver who approved the order.

sequence_num
(primary key)

int

not null

The sequence number of the approver ID in a list.

dbcpp_authapprids

This table contains profile IDs of users who have authorization to approve the order.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

References dcspp_order(order_id).

auth_appr_ids

varchar(40)

not null

Profile ID for a valid approver for an order.

sequence_num
(primary key)

int

not null

The sequence number of the approver ID in a list.

dbcpp_apprsysmsgs

This table contains system-generated messages for order approvals.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

References dcspp_order(order_id).

appr_sys_msgs

varchar(254)

not null

System generated message for an order requiring approval.

sequence_num
(primary key)

int

not null

The sequence number of the system message in a list.

dbcpp_appr_msgs

This table contains user messages for order approvals.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

References dcspp_order(order_id).

approver_msgs

varchar(254)

not null

Approver message for an order, which has been approved or rejected.

sequence_num
(primary key)

int

not null

The sequence number of the approver message in a list.

dbcpp_invoice_req

This table contains information related to the invoice request payment method.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

References dcspp_pay_group
(payment_group_id)
.

po_number

varchar(40)

null

The user-supplied purchase order number that was used to create this invoice request.

pref_format

varchar(40)

null

The preferred format for delivery of the invoice created from this invoice request (e.g., text, HTML, XML DTD, etc.).

Note: Oracle ATG Web Commerce does not currently use this field. The field is provided as a placeholder for applications that may wish to let the user set a preferred delivery format and then try to honor that preference when delivering an invoice.

pref_delivery

varchar(40)

null

The preferred mode for delivery of the invoice created from this invoice request (e.g., postal mail, e-mail, electronic, etc.).

Note: Oracle ATG Web Commerce does not currently use this field. The field is provided as a placeholder for applications that may wish to let the user set a preferred delivery channel and then try to honor that preference when delivering an invoice.

disc_percent

numeric(19, 7)

null

The discount percentage offered as part of the payment terms for this invoice, if the invoice is paid within disc_days. This is part of the conventional representation of payment terms, which consists of discount percentage, discount days, and net days. For example, payment terms of 2/10/net 30 means that a 2% discount is offered if payment is made within 10 days, but payment in full must be received within 30 days.

disc_days

int

null

The discount days part of the payment terms – i.e., the number of days within which the invoice must be paid in order to qualify for the specified discount percentage.

net_days

int

null

The net days part of the payment terms – i.e. the number of within which the invoice must be paid in full.

pmt_due_date

timestamp

null

The actual date on which payment is due.

dbcpp_cost_center

This table contains information related to cost centers.

Column

Data Type

Constraint

Description

cost_center_id
(primary key)

varchar(40)

not null

The repository ID of the cost center.

type

int

not null

The repository type of the item.

version

int

not null

The repository version number.

costctr_class_type

varchar(40)

null

The mapped name of the class type of the cost center

identifier

varchar(40)

null

The name of the cost center.

amount

numeric(19, 7)

null

The amount assigned to the cost center.

order_ref

varchar(40)

null

The ID of the order associated with this cost center.

dbcpp_order_cc

This table contains information about which cost centers are contained in which orders.

Column

Data Type

Constraint

Description

order_id
(primary key)

varchar(40)

not null

References dcspp_order(order_id).

cost_centers

varchar(40)

not null

Cost center ID of the cost center within the order.

sequence_num
(primary key)

int

not null

The sequence number of the cost center in a list.

dbcpp_sched_clone

This table contains information related to which cloned orders are associated with scheduled orders.

Column

Data Type

Constraint

Description

scheduled_order_id
(primary key)

varchar(40)

not null

References dbcpp_sched_order
(scheduled_order_id)
.

cloned_order

varchar(40)

not null

The order ID of the cloned order which is derived from a template order.

sequence_num
(primary key)

integer

not null

The sequence number of the cloned order in a list.

dbcpp_ccitem_rel

This table contains information related to a cost center/commerce item relationship object. It ties commerce items to cost centers.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

References dcspp_relationship
(relationship_id)
.

cost_center_id

varchar(40)

null

The ID of the cost center which the relationship references.

commerce_item_id

varchar(40)

null

The ID of the commerce item which the relationship references.

Quantity

numeric(19, 0)

null

The quantity of commerce items which are assigned to the cost center.

Amount

numeric(19, 7)

null

The amount which is assigned to the cost center.

dbcpp_ccship_rel

This table contains information related to a cost center/shipping group relationship object. It ties shipping amounts to cost centers.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

References dcspp_relationship
(relationship_id)
.

Cost_center_id

varchar(40)

null

The ID of the cost center which the relationship references.

shipping_group_id

varchar(40)

null

The ID of the shipping group which the relationship references.

Amount

numeric(19, 7)

null

The shipping amount which is assigned to the cost center.

dbcpp_ccorder_rel

This table contains information related to a cost center/order relationship object. It ties order amounts to cost centers.

Column

Data Type

Constraint

Description

relationship_id
(primary key)

varchar(40)

not null

References dcspp_relationship
(relationship_id)
.

Cost_center_id

varchar(40)

null

The ID of the cost center which the relationship references.

order_id

varchar(40)

null

The ID of the order which the relationship references.

amount

numeric(19, 7)

null

The order amount which is assigned to the cost center.

dbcpp_pmt_req

This table contains requisition information related to a payment group.

Column

Data Type

Constraint

Description

payment_group_id
(primary key)

varchar(40)

not null

References dcspp_pay_group
(payment_group_id)
.

req_number

varchar(40)

null

A requisition number for a payment group.


Copyright © 1997, 2013 Oracle and/or its affiliates. All rights reserved. Legal Notices