The Order Processing Database Schema - Updated February 12, 2001


	Corporate Info \| News \| Solutions \| Products \| Partners \| Services \| Events \| Download \| How To Buy
	http://www.oracle.com/technology/documentation/index.html \| Site Map \| Search \| PDF Files \| Contact \| Glossary \| What's New

WebLogic Commerce Server Doc Home \| Order Processing Package \| Previous Topic \| Next Topic \| Contents \| Index

The Order Processing Database Schema

This topic describes the database schema for the BEA WebLogic Commerce Server Order Processing package. Understanding this schema will be helpful to those who may be customizing or extending the technologies provided in the product.

This topic includes the following sections:

The Entity-Relation Diagram

Figure 2-1 shows the Entity-Relation diagram for the BEA WebLogic Commerce Server order processing database.

Figure 2-1 Entity-Relation Diagram for the Order Processing Database

Explanations for the columns in each table are provided in the remainder of this topic.

The WLCS_CUSTOMER Database Table

Table 2-1 describes the metadata for the Commerce Server WLCS_CUSTOMER table. This table is used to store information about the customer in the order processing database.

Table 2-1 WLCS_CUSTOMER Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

CUSTOMER_ID

VARCHAR(20)

VARCHAR2(20)

A unique identifier for the customer. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_CUSTOMER table can be NULL.

CUSTOMER_TYPE

VARCHAR(256)

VARCHAR2(256)

A label for the customer (such as preferred, standard, or business).

FIRST_NAME

VARCHAR(30)

VARCHAR2(30)

The customer's first name.

LAST_NAME

VARCHAR(30)

VARCHAR2(30)

The customer's last name.

MIDDLE_NAME

VARCHAR(30)

VARCHAR2(30)

The customer's middle name.

TITLE

VARCHAR(10)

VARCHAR2(10)

The customer's preferred title (Mr., Mrs., Ms.).

SUFFIX

VARCHAR(10)

VARCHAR2(10)

The customer's preferred suffix (Jr., Sr.).

EMAIL

VARCHAR(80)

VARCHAR2(80)

The customer's email address.

HOME_PHONE

VARCHAR(15)

VARCHAR2(15)

The customer's home phone number.

BUSINESS_PHONE

VARCHAR(20)

VARCHAR2(20)

The customer's business phone number.

FAX

VARCHAR(15)

VARCHAR2(15)

The customer's fax number.

MAILING_GEOCODE

VARCHAR(10)

VARCHAR2(2)

The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.

MAILING_STREET1

VARCHAR(30)

VARCHAR2(30)

The first line in the customer's street address.

MAILING_STREET2

VARCHAR(30)

VARCHAR2(30)

The second line in the customer's street address.

MAILING_CITY

VARCHAR(30)

VARCHAR2(30)

The city in the customer's address.

MAILING_STATE

VARCHAR(40)

VARCHAR2(40)

The state in the customer's address.

MAILING_COUNTRY

VARCHAR(40)

VARCHAR2(40)

The country in the customer's address.

MAILING_POBOX

VARCHAR(30)

VARCHAR2(30)

The post office box in the customer's address.

MAILING_COUNTY

VARCHAR(30)

VARCHAR2(30)

The county in the customer's address.

MAILING_POSTAL_CODE

VARCHAR(10)

VARCHAR2(10)

The postal (zip) code in the customer's address.

MAILING_POSTAL_CODE
_TYPE

VARCHAR(10)

VARCHAR2(10)

Format or type of postal code, generally determined by country (such as zip code in the United States).

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
CUSTOMER_ID	VARCHAR(20)	VARCHAR2(20)	A unique identifier for the customer. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_CUSTOMER table can be NULL.
CUSTOMER_TYPE	VARCHAR(256)	VARCHAR2(256)	A label for the customer (such as preferred, standard, or business).
FIRST_NAME	VARCHAR(30)	VARCHAR2(30)	The customer's first name.
LAST_NAME	VARCHAR(30)	VARCHAR2(30)	The customer's last name.
MIDDLE_NAME	VARCHAR(30)	VARCHAR2(30)	The customer's middle name.
TITLE	VARCHAR(10)	VARCHAR2(10)	The customer's preferred title (Mr., Mrs., Ms.).
SUFFIX	VARCHAR(10)	VARCHAR2(10)	The customer's preferred suffix (Jr., Sr.).
EMAIL	VARCHAR(80)	VARCHAR2(80)	The customer's email address.
HOME_PHONE	VARCHAR(15)	VARCHAR2(15)	The customer's home phone number.
BUSINESS_PHONE	VARCHAR(20)	VARCHAR2(20)	The customer's business phone number.
FAX	VARCHAR(15)	VARCHAR2(15)	The customer's fax number.
MAILING_GEOCODE	VARCHAR(10)	VARCHAR2(2)	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
MAILING_STREET1	VARCHAR(30)	VARCHAR2(30)	The first line in the customer's street address.
MAILING_STREET2	VARCHAR(30)	VARCHAR2(30)	The second line in the customer's street address.
MAILING_CITY	VARCHAR(30)	VARCHAR2(30)	The city in the customer's address.
MAILING_STATE	VARCHAR(40)	VARCHAR2(40)	The state in the customer's address.
MAILING_COUNTRY	VARCHAR(40)	VARCHAR2(40)	The country in the customer's address.
MAILING_POBOX	VARCHAR(30)	VARCHAR2(30)	The post office box in the customer's address.
MAILING_COUNTY	VARCHAR(30)	VARCHAR2(30)	The county in the customer's address.
MAILING_POSTAL_CODE	VARCHAR(10)	VARCHAR2(10)	The postal (zip) code in the customer's address.
MAILING_POSTAL_CODE _TYPE	VARCHAR(10)	VARCHAR2(10)	Format or type of postal code, generally determined by country (such as zip code in the United States).

The WLCS_SHIPPING_ADDRESS Database Table

Table 2-2 describes the metadata for the Commerce Server WLCS_SHIPPING_ADDRESS table. This table is used to store information related to a customer's shipping address(es) in the order processing database.

Table 2-2 WLCS_SHIPPING_ADDRESS Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

SHIPPING_ADDRESS_ID

INTEGER

NUMBER(15)

A unique identifier for the shipping address. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_SHIPPING_
ADDRESS table can be NULL.

CUSTOMER_ID

VARCHAR(20)

VARCHAR2(20)

A unique identifier for the customer.

MAP_KEY

VARCHAR(30)

VARCHAR2(30)

Key that maps multiple shipping addresses with a single customer.

SHIPPING_GEOCODE

VARCHAR(2)

VARCHAR2(2)

The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.

SHIPPING_STREET1

VARCHAR(30)

VARCHAR2(30)

The first line in the customer's shipping address.

SHIPPING_STREET2

VARCHAR(30)

VARCHAR2(30)

The second line in the customer's shipping address.

SHIPPING_CITY

VARCHAR(30)

VARCHAR2(30)

The city in the customer's shipping address.

SHIPPING_STATE

VARCHAR(40)

VARCHAR2(40)

The state in the customer's shipping address.

SHIPPING_COUNTRY

VARCHAR(40)

VARCHAR2(40)

The country in the customer's shipping address.

SHIPPING_POBOX

VARCHAR(30)

VARCHAR2(30)

The post office box in the customer's shipping address.

SHIPPING_COUNTY

VARCHAR(30)

VARCHAR2(30)

The county in the customer's shipping address.

SHIPPING_POSTAL_CODE

VARCHAR(10)

VARCHAR2(10)

The postal (zip) code in the customer's shipping address.

SHIPPING_POSTAL_CODE
_TYPE

VARCHAR(10)

VARCHAR2(10)

Format or type of postal code, generally determined by country (such as zip code in the United States).

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
SHIPPING_ADDRESS_ID	INTEGER	NUMBER(15)	A unique identifier for the shipping address. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_SHIPPING_ ADDRESS table can be NULL.
CUSTOMER_ID	VARCHAR(20)	VARCHAR2(20)	A unique identifier for the customer.
MAP_KEY	VARCHAR(30)	VARCHAR2(30)	Key that maps multiple shipping addresses with a single customer.
SHIPPING_GEOCODE	VARCHAR(2)	VARCHAR2(2)	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
SHIPPING_STREET1	VARCHAR(30)	VARCHAR2(30)	The first line in the customer's shipping address.
SHIPPING_STREET2	VARCHAR(30)	VARCHAR2(30)	The second line in the customer's shipping address.
SHIPPING_CITY	VARCHAR(30)	VARCHAR2(30)	The city in the customer's shipping address.
SHIPPING_STATE	VARCHAR(40)	VARCHAR2(40)	The state in the customer's shipping address.
SHIPPING_COUNTRY	VARCHAR(40)	VARCHAR2(40)	The country in the customer's shipping address.
SHIPPING_POBOX	VARCHAR(30)	VARCHAR2(30)	The post office box in the customer's shipping address.
SHIPPING_COUNTY	VARCHAR(30)	VARCHAR2(30)	The county in the customer's shipping address.
SHIPPING_POSTAL_CODE	VARCHAR(10)	VARCHAR2(10)	The postal (zip) code in the customer's shipping address.
SHIPPING_POSTAL_CODE _TYPE	VARCHAR(10)	VARCHAR2(10)	Format or type of postal code, generally determined by country (such as zip code in the United States).

The WLCS_CREDIT_CARD Database Table

Table 2-3 describes the metadata for the Commerce Server WLCS_CREDIT_CARD table. This table is used to store information related to a customer's credit card(s) in the order processing database.

Table 2-3 WLCS_CREDIT_CARD Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

CREDIT_CARD_ID

INTEGER

NUMBER(15)

A unique identifier for the credit card. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_CREDIT_CARD table can be NULL.

CUSTOMER_ID

VARCHAR(20)

VARCHAR2(20)

A unique identifier for the customer.

MAP_KEY

VARCHAR(20)

VARCHAR2(20)

Key that maps multiple credit cards with a single customer.

CC_NUMBER

VARCHAR(200)

VARCHAR2(200)

The customer's credit card number. This is encrypted if is.encryption.
enable is set to true
in the weblogiccommerce.
properties file.

CC_TYPE

VARCHAR(20)

VARCHAR2(20)

The customer's credit card type (VISA, MasterCard, and so on).

CC_EXP_DATE

DATE

DATE

The expiration date on the customer's credit card.

CC_NAME

VARCHAR(50)

VARCHAR2(50)

The credit card holder's name.

CC_DISPLAY_NUMBER

VARCHAR(20)

VARCHAR2(20)

The version of the credit card number that is displayed (all Xs except last 4-digits).

CC_COMPANY

VARCHAR(50)

VARCHAR2(50)

The name of the credit card company.

BILLING_GEOCODE

VARCHAR(2)

VARCHAR2(2)

The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.

BILLING_STREET1

VARCHAR(30)

VARCHAR2(30)

The first line in the customer's billing address.

BILLING_STREET2

VARCHAR(30)

VARCHAR2(30)

The second line in the customer's billing address.

BILLING_CITY

VARCHAR(30)

VARCHAR2(30)

The city in the customer's billing address.

BILLING_STATE

VARCHAR(40)

VARCHAR2(40)

The state in the customer's billing address.

BILLING_COUNTRY

VARCHAR(40)

VARCHAR2(40)

The country in the customer's billing address.

BILLING_POBOX

VARCHAR(30)

VARCHAR2(30)

The post office box in the customer's billing address.

BILLING_COUNTY

VARCHAR(30)

VARCHAR2(30)

The county in the customer's billing address.

BILLING_POSTAL_CODE

VARCHAR(10)

VARCHAR2(10)

The postal (zip) code in the customer's billing address.

BILLING_POSTAL_CODE
_TYPE

VARCHAR(10)

VARCHAR2(10)

Format or type of postal code, generally determined by country (such as zip code in the United States).

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
CREDIT_CARD_ID	INTEGER	NUMBER(15)	A unique identifier for the credit card. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_CREDIT_CARD table can be NULL.
CUSTOMER_ID	VARCHAR(20)	VARCHAR2(20)	A unique identifier for the customer.
MAP_KEY	VARCHAR(20)	VARCHAR2(20)	Key that maps multiple credit cards with a single customer.
CC_NUMBER	VARCHAR(200)	VARCHAR2(200)	The customer's credit card number. This is encrypted if is.encryption. enable is set to true in the weblogiccommerce. properties file.
CC_TYPE	VARCHAR(20)	VARCHAR2(20)	The customer's credit card type (VISA, MasterCard, and so on).
CC_EXP_DATE	DATE	DATE	The expiration date on the customer's credit card.
CC_NAME	VARCHAR(50)	VARCHAR2(50)	The credit card holder's name.
CC_DISPLAY_NUMBER	VARCHAR(20)	VARCHAR2(20)	The version of the credit card number that is displayed (all Xs except last 4-digits).
CC_COMPANY	VARCHAR(50)	VARCHAR2(50)	The name of the credit card company.
BILLING_GEOCODE	VARCHAR(2)	VARCHAR2(2)	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
BILLING_STREET1	VARCHAR(30)	VARCHAR2(30)	The first line in the customer's billing address.
BILLING_STREET2	VARCHAR(30)	VARCHAR2(30)	The second line in the customer's billing address.
BILLING_CITY	VARCHAR(30)	VARCHAR2(30)	The city in the customer's billing address.
BILLING_STATE	VARCHAR(40)	VARCHAR2(40)	The state in the customer's billing address.
BILLING_COUNTRY	VARCHAR(40)	VARCHAR2(40)	The country in the customer's billing address.
BILLING_POBOX	VARCHAR(30)	VARCHAR2(30)	The post office box in the customer's billing address.
BILLING_COUNTY	VARCHAR(30)	VARCHAR2(30)	The county in the customer's billing address.
BILLING_POSTAL_CODE	VARCHAR(10)	VARCHAR2(10)	The postal (zip) code in the customer's billing address.
BILLING_POSTAL_CODE _TYPE	VARCHAR(10)	VARCHAR2(10)	Format or type of postal code, generally determined by country (such as zip code in the United States).

The WLCS_TRANSACTION Database Table

Table 2-4 describes the metadata for the Commerce Server WLCS_TRANSACTION table. This table is used to store data for every payment transaction in the order processing database.

Table 2-4 WLCS_TRANSACTION Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

TRANSACTION_ID

VARCHAR(25)

VARCHAR2(25)

A unique identifier for the transaction. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_
TRANSACTION table can be NULL.

BATCH_ID

VARCHAR(15)

VARCHAR2(15)

A unique identifier of a batch submitted for settlement, as returned by CyberCash. This field need not be populated for other external payment services.

TRAN_DATE

DATE

DATE

The date of the transaction (date on which the transaction was first started).

TRAN_STATUS

VARCHAR(20)

VARCHAR2(20)

The current status of the transaction (Settled, Authorized, MarkedForSettle, PendingSettle, Retry, Settled).

TRAN_AMOUNT

DOUBLE
PRECISION

NUMBER(16,4)

The most recent amount applied to the transaction (MarkForSettle amounts can be different from the authorization amount).

TRAN_CURRENCY

VARCHAR(30)

VARCHAR2(30)

The currency of the transaction.

CC_NUMBER

VARCHAR(200)

VARCHAR2(200)

The customer's credit card number. This is encrypted if is.encryption.
enable is set to true
in the weblogiccommerce.
properties file.

CC_TYPE

VARCHAR(20)

VARCHAR2(20)

The customer's credit card type (VISA, MasterCard, and so on).

CC_EXP_DATE

DATE

DATE

The expiration date on the customer's credit card.

CC_NAME

VARCHAR(50)

VARCHAR2(50)

The credit card holder's name.

CC_DISPLAY_NUMBER

VARCHAR(20)

VARCHAR2(20)

The version of the credit card number that is displayed (all Xs except last 4-digits).

CC_COMPANY

VARCHAR(50)

VARCHAR2(50)

The name of the credit card company.

GEOCODE

VARCHAR(2)

VARCHAR2(2)

The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.

STREET1

VARCHAR(30)

VARCHAR2(30)

The first line in the customer's street address.

STREET2

VARCHAR(30)

VARCHAR2(30)

The second line in the customer's street address.

CITY

VARCHAR(30)

VARCHAR2(30)

The city in the customer's address.

STATE

VARCHAR(40)

VARCHAR2(40)

The state in the customer's address.

COUNTRY

VARCHAR(40)

VARCHAR2(40)

The country in the customer's address.

POBOX

VARCHAR(30)

VARCHAR2(30)

The post office box in the customer's address.

COUNTY

VARCHAR(30)

VARCHAR2(30)

The county in the customer's address.

POSTAL_CODE

VARCHAR(10)

VARCHAR2(10)

The postal (zip) code in the customer's address.

POSTAL_CODE_TYPE

VARCHAR(10)

VARCHAR2(10)

Format or type of postal code, generally determined by country (such as zip code in the United States).

DESCRIPTION

VARCHAR(30)

VARCHAR2(30)

Any additional data. Can be NULL.

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
TRANSACTION_ID	VARCHAR(25)	VARCHAR2(25)	A unique identifier for the transaction. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ TRANSACTION table can be NULL.
BATCH_ID	VARCHAR(15)	VARCHAR2(15)	A unique identifier of a batch submitted for settlement, as returned by CyberCash. This field need not be populated for other external payment services.
TRAN_DATE	DATE	DATE	The date of the transaction (date on which the transaction was first started).
TRAN_STATUS	VARCHAR(20)	VARCHAR2(20)	The current status of the transaction (Settled, Authorized, MarkedForSettle, PendingSettle, Retry, Settled).
TRAN_AMOUNT	DOUBLE PRECISION	NUMBER(16,4)	The most recent amount applied to the transaction (MarkForSettle amounts can be different from the authorization amount).
TRAN_CURRENCY	VARCHAR(30)	VARCHAR2(30)	The currency of the transaction.
CC_NUMBER	VARCHAR(200)	VARCHAR2(200)	The customer's credit card number. This is encrypted if is.encryption. enable is set to true in the weblogiccommerce. properties file.
CC_TYPE	VARCHAR(20)	VARCHAR2(20)	The customer's credit card type (VISA, MasterCard, and so on).
CC_EXP_DATE	DATE	DATE	The expiration date on the customer's credit card.
CC_NAME	VARCHAR(50)	VARCHAR2(50)	The credit card holder's name.
CC_DISPLAY_NUMBER	VARCHAR(20)	VARCHAR2(20)	The version of the credit card number that is displayed (all Xs except last 4-digits).
CC_COMPANY	VARCHAR(50)	VARCHAR2(50)	The name of the credit card company.
GEOCODE	VARCHAR(2)	VARCHAR2(2)	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
STREET1	VARCHAR(30)	VARCHAR2(30)	The first line in the customer's street address.
STREET2	VARCHAR(30)	VARCHAR2(30)	The second line in the customer's street address.
CITY	VARCHAR(30)	VARCHAR2(30)	The city in the customer's address.
STATE	VARCHAR(40)	VARCHAR2(40)	The state in the customer's address.
COUNTRY	VARCHAR(40)	VARCHAR2(40)	The country in the customer's address.
POBOX	VARCHAR(30)	VARCHAR2(30)	The post office box in the customer's address.
COUNTY	VARCHAR(30)	VARCHAR2(30)	The county in the customer's address.
POSTAL_CODE	VARCHAR(10)	VARCHAR2(10)	The postal (zip) code in the customer's address.
POSTAL_CODE_TYPE	VARCHAR(10)	VARCHAR2(10)	Format or type of postal code, generally determined by country (such as zip code in the United States).
DESCRIPTION	VARCHAR(30)	VARCHAR2(30)	Any additional data. Can be NULL.

The WLCS_TRANSACTION_ENTRY Database Table

Table 2-5 describes the metadata for the Commerce Server WLCS_TRANSACTION_ENTRY table. This table is used to store (log) the different states a payment transaction has passed through in the order processing database.

Table 2-5 WLCS_TRANSACTION_ENTRY Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

TRANSACTION_ENTRY_ID

INTEGER

NUMBER(25)

A unique identifier for the transaction entry. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_TRANSACTION_
ENTRY table can be NULL.

TRAN_ENTRY_SEQUENCE

VARCHAR(30)

VARCHAR2(30)

Represents the running count per transaction.

TRAN_ENTRY_DATE

DATE

DATE

The date of the log entry.

TRAN_ENTRY_STATUS

VARCHAR(20)

VARCHAR2(20)

The status of the transaction when this entry was made.

TRAN_ENTRY_AMOUNT

DOUBLE
PRECISION

NUMBER(16,4)

The amount of the transaction when the log entry was made.

TRAN_ENTRY_CURRENCY

VARCHAR(30)

VARCHAR2(30)

The currency of the transaction.

TRANSACTION_ID

VARCHAR(25)

VARCHAR2(25)

A unique identifier for the transaction.

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
TRANSACTION_ENTRY_ID	INTEGER	NUMBER(25)	A unique identifier for the transaction entry. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_TRANSACTION_ ENTRY table can be NULL.
TRAN_ENTRY_SEQUENCE	VARCHAR(30)	VARCHAR2(30)	Represents the running count per transaction.
TRAN_ENTRY_DATE	DATE	DATE	The date of the log entry.
TRAN_ENTRY_STATUS	VARCHAR(20)	VARCHAR2(20)	The status of the transaction when this entry was made.
TRAN_ENTRY_AMOUNT	DOUBLE PRECISION	NUMBER(16,4)	The amount of the transaction when the log entry was made.
TRAN_ENTRY_CURRENCY	VARCHAR(30)	VARCHAR2(30)	The currency of the transaction.
TRANSACTION_ID	VARCHAR(25)	VARCHAR2(25)	A unique identifier for the transaction.

The WLCS_SAVED_ITEM_LIST Database Table

Table 2-6 describes the metadata for the Commerce Server WLCS_SAVED_ITEM_LIST table. This table is used to store information about the customer's saved shopping cart items in the order processing database.

Table 2-6 WLCS_SAVED_ITEM_LIST Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

CUSTOMER_ID

VARCHAR(20)

VARCHAR2(20)

A unique identifier for the customer.

SKU

VARCHAR(40)

VARCHAR2(40)

A unique identifier (the "Stock Keeping Unit," or SKU) for a product item.

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
CUSTOMER_ID	VARCHAR(20)	VARCHAR2(20)	A unique identifier for the customer.
SKU	VARCHAR(40)	VARCHAR2(40)	A unique identifier (the "Stock Keeping Unit," or SKU) for a product item.

The WLCS_ORDER Database Table

Table 2-7 describes the metadata for the Commerce Server WLCS_ORDER table. This table is used to store information about a customer's specific order in the order processing database.

Note: The BEA WebLogic Commerce Server product does not populate the SHIPPING_AMOUNT, SHIPPING_CURRENCY, PRICE_AMOUNT, or PRICE_CURRENCY columns.

Table 2-7 WLCS_ORDER Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

ORDER_ID

VARCHAR(20)

VARCHAR2(20)

A unique identifier for the order. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ORDER table can be NULL.

CUSTOMER_ID

VARCHAR(20)

VARCHAR2(20)

A unique identifier for the customer.

TRANSACTION_ID

VARCHAR(25)

VARCHAR2(25)

A unique identifier for the transaction.

STATUS

VARCHAR(20)

VARCHAR2(20)

The status of the order.

ORDER_DATE

DATE

DATE

The date the order was placed.

SHIPPING_METHOD

VARCHAR(40)

VARCHAR2(40)

The method by which the order is to be shipped.

SHIPPING_AMOUNT

DOUBLE

PRECISION

NUMBER(16,4)

The shipping amount for the order.

SHIPPING_CURRENCY

VARCHAR(10)

VARCHAR2(10)

The currency associated with the shipping amount.

PRICE_AMOUNT

DOUBLE

PRECISION

NUMBER(16,4)

The price of the order.

PRICE_CURRENCY

VARCHAR(10)

VARCHAR2(10)

The currency associated with the price.

SHIPPING_GEOGODE

VARCHAR(2)

VARCHAR2(2)

The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.

SHIPPING_STREET1

VARCHAR(30)

VARCHAR2(30)

The first line in the customer's shipping address.

SHIPPING_STREET2

VARCHAR(30)

VARCHAR2(30)

The second line in the customer's shipping address.

SHIPPING_CITY

VARCHAR(30)

VARCHAR2(30)

The city in the customer's shipping address.

SHIPPING_STATE

VARCHAR(40)

VARCHAR2(40)

The state in the customer's shipping address.

SHIPPING_COUNTRY

VARCHAR(40)

VARCHAR2(40)

The country in the customer's shipping address.

SHIPPING_POBOX

VARCHAR(30)

VARCHAR2(30)

The post office box in the customer's shipping address.

SHIPPING_COUNTY

VARCHAR(40)

VARCHAR2(30)

The county in the customer's shipping address.

SHIPPING_POSTAL_CODE

VARCHAR(10)

VARCHAR2(10)

The postal (zip) code in the customer's shipping address.

SHIPPING_POSTAL_CODE
_TYPE

VARCHAR(10)

VARCHAR2(10)

Format or type of postal code, generally determined by country (such as zip code in the United States).

SPECIAL_INSTRUCTIONS

VARCHAR(256)

VARCHAR2(256)

Any special shipping instructions associated with the order.

SPLITTING_PREFERENCE

VARCHAR(256)

VARCHAR2(256)

The splitting preferences for the customer's order.

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
ORDER_ID	VARCHAR(20)	VARCHAR2(20)	A unique identifier for the order. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ORDER table can be NULL.
CUSTOMER_ID	VARCHAR(20)	VARCHAR2(20)	A unique identifier for the customer.
TRANSACTION_ID	VARCHAR(25)	VARCHAR2(25)	A unique identifier for the transaction.
STATUS	VARCHAR(20)	VARCHAR2(20)	The status of the order.
ORDER_DATE	DATE	DATE	The date the order was placed.
SHIPPING_METHOD	VARCHAR(40)	VARCHAR2(40)	The method by which the order is to be shipped.
SHIPPING_AMOUNT	DOUBLE PRECISION	NUMBER(16,4)	The shipping amount for the order.
SHIPPING_CURRENCY	VARCHAR(10)	VARCHAR2(10)	The currency associated with the shipping amount.
PRICE_AMOUNT	DOUBLE PRECISION	NUMBER(16,4)	The price of the order.
PRICE_CURRENCY	VARCHAR(10)	VARCHAR2(10)	The currency associated with the price.
SHIPPING_GEOGODE	VARCHAR(2)	VARCHAR2(2)	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
SHIPPING_STREET1	VARCHAR(30)	VARCHAR2(30)	The first line in the customer's shipping address.
SHIPPING_STREET2	VARCHAR(30)	VARCHAR2(30)	The second line in the customer's shipping address.
SHIPPING_CITY	VARCHAR(30)	VARCHAR2(30)	The city in the customer's shipping address.
SHIPPING_STATE	VARCHAR(40)	VARCHAR2(40)	The state in the customer's shipping address.
SHIPPING_COUNTRY	VARCHAR(40)	VARCHAR2(40)	The country in the customer's shipping address.
SHIPPING_POBOX	VARCHAR(30)	VARCHAR2(30)	The post office box in the customer's shipping address.
SHIPPING_COUNTY	VARCHAR(40)	VARCHAR2(30)	The county in the customer's shipping address.
SHIPPING_POSTAL_CODE	VARCHAR(10)	VARCHAR2(10)	The postal (zip) code in the customer's shipping address.
SHIPPING_POSTAL_CODE _TYPE	VARCHAR(10)	VARCHAR2(10)	Format or type of postal code, generally determined by country (such as zip code in the United States).
SPECIAL_INSTRUCTIONS	VARCHAR(256)	VARCHAR2(256)	Any special shipping instructions associated with the order.
SPLITTING_PREFERENCE	VARCHAR(256)	VARCHAR2(256)	The splitting preferences for the customer's order.

The WLCS_ORDER_LINE Database Table

Table 2-8 describes the metadata for the Commerce Server WLCS_ORDER_LINE table. This table is used to store information about each line of a customer's shopping cart in the order processing database.

Table 2-8 WLCS_ORDER_LINE Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

ORDER_LINE_ID

INTEGER

NUMBER(15)

A unique identifier for each line in a customer's shopping cart. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ORDERLINE table can be NULL.

QUANTITY

DOUBLE
PRECISION

NUMBER(16,4)

The quantity of the item in the shopping cart.

PRODUCT_ID

VARCHAR(40)

VARCHAR2(40)

An identification number for the item in the shopping cart.

TAX_AMOUNT

DOUBLE
PRECISION

NUMBER(16,4)

The tax amount for the order.

TAX_CURRENCY

VARCHAR(10)

VARCHAR2(10)

The currency associated with the tax amount.

SHIPPING_AMOUNT

DOUBLE
PRECISION

NUMBER(16,4)

The shipping amount for the order.

SHIPPING_CURRENCY

VARCHAR(10)

VARCHAR2(10)

The currency associated with the shipping amount.

UNIT_PRICE_AMOUNT

DOUBLE
PRECISION

NUMBER(16,4)

The unit price amount for the item.

UNIT_PRICE_CURRENCY

VARCHAR(10)

VARCHAR2(10)

The currency associated with the unit price.

MSRP_AMOUNT

DOUBLE
PRECISION

NUMBER(16,4)

The MSRP amount for the item.

MSRP_CURRENCY

VARCHAR(10)

VARCHAR2(10)

The currency associated with the MSRP amount.

DESCRIPTION

VARCHAR(256)

VARCHAR2(256)

The name of the item that is part of the order.

ORDER_ID

VARCHAR(20)

VARCHAR2(20)

A unique identifier for the order.

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
ORDER_LINE_ID	INTEGER	NUMBER(15)	A unique identifier for each line in a customer's shopping cart. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_ORDERLINE table can be NULL.
QUANTITY	DOUBLE PRECISION	NUMBER(16,4)	The quantity of the item in the shopping cart.
PRODUCT_ID	VARCHAR(40)	VARCHAR2(40)	An identification number for the item in the shopping cart.
TAX_AMOUNT	DOUBLE PRECISION	NUMBER(16,4)	The tax amount for the order.
TAX_CURRENCY	VARCHAR(10)	VARCHAR2(10)	The currency associated with the tax amount.
SHIPPING_AMOUNT	DOUBLE PRECISION	NUMBER(16,4)	The shipping amount for the order.
SHIPPING_CURRENCY	VARCHAR(10)	VARCHAR2(10)	The currency associated with the shipping amount.
UNIT_PRICE_AMOUNT	DOUBLE PRECISION	NUMBER(16,4)	The unit price amount for the item.
UNIT_PRICE_CURRENCY	VARCHAR(10)	VARCHAR2(10)	The currency associated with the unit price.
MSRP_AMOUNT	DOUBLE PRECISION	NUMBER(16,4)	The MSRP amount for the item.
MSRP_CURRENCY	VARCHAR(10)	VARCHAR2(10)	The currency associated with the MSRP amount.
DESCRIPTION	VARCHAR(256)	VARCHAR2(256)	The name of the item that is part of the order.
ORDER_ID	VARCHAR(20)	VARCHAR2(20)	A unique identifier for the order.

The WLCS_SHIPPING_METHOD Database Table

Table 2-9 describes the metadata for the Commerce Server WLCS_SHIPPING_METHOD table. This table is used to store information about the shiping method in the order processing database.

Table 2-9 WLCS_SHIPPING_METHOD Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

PK_IDENTIFIER

VARCHAR(20)

VARCHAR2(20)

A unique identifier for the shipping method. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_SHIPPING_
METHOD table can be NULL.

CARRIER

VARCHAR(40)

VARCHAR2(40)

The carrier being used to ship the order (such as UPS, FedEx, and so on).

METHOD

VARCHAR(40)

VARCHAR2(40)

The method by which the order is to be shipped (such as air, 2nd day air, parcel post, and so on).

AVERAGE_SHIPPING_
TIME

INTEGER

NUMBER

The average number of days it will take the order to arrive.

PRICE_VALUE

DOUBLE PRECISION

NUMBER(16,4)

The amount it will cost to ship the order.

PRICE_CURRENCY

VARCHAR(10)

VARCHAR2(10)

The currency associated with the PRICE_VALUE column (such as dollars, pounds, lira, and so on).

WEIGHT_LIMIT

DOUBLE PRECISION

NUMBER(16,4)

The weight limit for the shipment.

RESTRICTIONS

VARCHAR(256)

VARCHAR2(256)

Any restrictions associated with the shipment.

DESCRIPTION

VARCHAR(256)

VARCHAR2(256)

A description of the shipping method (such as FedEx Overnight or Standard).

PO_BOX_ALLOWED

INTEGER

NUMBER

Specifies whether or not the shipment can be left at a post office box.

SIGNATURE_REQUIRED

INTEGER

NUMBER

Specifies whether or not a signature is required upon receipt of the shipment.

SATURDAY_DELIVERY

INTEGER

NUMBER

Specifies whether or not the shipment can be delivered on Saturday.

INTERNATIONAL_
DELIVERY

INTEGER

NUMBER

Specifies whether or not international delivery is an option.

SIZE_LIMIT

DOUBLE PRECISION

NUMBER(16,4)

The size limit for the shipment.

PACKAGING_TYPE

VARCHAR(50)

VARCHAR2(50)

The packaging type for the shipment.

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
PK_IDENTIFIER	VARCHAR(20)	VARCHAR2(20)	A unique identifier for the shipping method. This field is the table's primary key and cannot be NULL. All other fields in the WLCS_SHIPPING_ METHOD table can be NULL.
CARRIER	VARCHAR(40)	VARCHAR2(40)	The carrier being used to ship the order (such as UPS, FedEx, and so on).
METHOD	VARCHAR(40)	VARCHAR2(40)	The method by which the order is to be shipped (such as air, 2nd day air, parcel post, and so on).
AVERAGE_SHIPPING_ TIME	INTEGER	NUMBER	The average number of days it will take the order to arrive.
PRICE_VALUE	DOUBLE PRECISION	NUMBER(16,4)	The amount it will cost to ship the order.
PRICE_CURRENCY	VARCHAR(10)	VARCHAR2(10)	The currency associated with the PRICE_VALUE column (such as dollars, pounds, lira, and so on).
WEIGHT_LIMIT	DOUBLE PRECISION	NUMBER(16,4)	The weight limit for the shipment.
RESTRICTIONS	VARCHAR(256)	VARCHAR2(256)	Any restrictions associated with the shipment.
DESCRIPTION	VARCHAR(256)	VARCHAR2(256)	A description of the shipping method (such as FedEx Overnight or Standard).
PO_BOX_ALLOWED	INTEGER	NUMBER	Specifies whether or not the shipment can be left at a post office box.
SIGNATURE_REQUIRED	INTEGER	NUMBER	Specifies whether or not a signature is required upon receipt of the shipment.
SATURDAY_DELIVERY	INTEGER	NUMBER	Specifies whether or not the shipment can be delivered on Saturday.
INTERNATIONAL_ DELIVERY	INTEGER	NUMBER	Specifies whether or not international delivery is an option.
SIZE_LIMIT	DOUBLE PRECISION	NUMBER(16,4)	The size limit for the shipment.
PACKAGING_TYPE	VARCHAR(50)	VARCHAR2(50)	The packaging type for the shipment.

The WLCS_SECURITY Database Table

Table 2-10 describes the metadata for the Commerce Server WLCS_SECURITY table. This table is used to persist public and private keys for encryption and decryption purposes in the order processing database. This table is meant for internal use by the BEA WebLogic Commerce Server product.

Table 2-10 WLCS_SECURITY Table Metadata

Column Name
Cloudscape Type
Oracle Type
Description and Recommendations

ID

INTEGER

NUMBER(2)

A unique identifier for the key pair. This field is the table's primary key and cannot be NULL.

PUBLIC_KEY

VARCHAR(2000)

VARCHAR2(2000)

The public key to be used for encryption/decryption of credit cards.

PRIVATE_KEY

VARCHAR(2000)

VARCHAR2(2000)

The private key to be used for encryption/decryption of credit cards.

Column Name	Cloudscape Type	Oracle Type	Description and Recommendations
ID	INTEGER	NUMBER(2)	A unique identifier for the key pair. This field is the table's primary key and cannot be NULL.
PUBLIC_KEY	VARCHAR(2000)	VARCHAR2(2000)	The public key to be used for encryption/decryption of credit cards.
PRIVATE_KEY	VARCHAR(2000)	VARCHAR2(2000)	The private key to be used for encryption/decryption of credit cards.

The SQL Files and Defined Constraints

The BEA WebLogic Commerce Server product provides two SQL files to create the Cloudscape and Oracle versions of the order processing database. The SQL files are in the WL_COMMERCE_HOME\db\<database-vendor>\wlcs\ directories. WL_COMMERCE_HOME is the directory in which you installed the WebLogic Commerce Server software, and the <database-vendor> directory is either cloudscape or oracle. The files are:

create-order-cloudscape.sql
create-order-oracle.sql

You can run the create-* procedure for the desired database vendor type by invoking one of the following procedures in the WL_COMMERCE_HOME\db\directory:

create-all-cloudscape.bat (Windows) or
create-all-cloudscape.sh (UNIX)
create-all-oracle.bat (Windows) or
create-all-oracle.sh (UNIX)
Note: You can also create just the WebLogic Commerce Server or WebLogic Personalization Server specific databases. Simply substitute wlcs or wlps for all in the procedures shown above.

In each create-order-* SQL file, the database tables described earlier in this chapter are created. In addition, the SQL files define constraints. Table 2-11 shows the table name and describes the constraint(s) defined for it.

Note: The sample SQL statements shown in the table are from the create-order-oracle.sql file. The syntax is different for Cloudscape. Except where noted, the effect of each constraint is the same.

Table 2-11 Constraints Defined on Order Database Tables

Table Name
Constraints as Defined in create-order-oracle.sql

WLCS_SHIPPING_ADDRESS

If a customer is deleted from the database, the CUSTOMER_FK constraint causes all their associated shipping addresses to be deleted.

The constraint for the schema in Oracle is:
CONSTRAINT CUSTOMER_FK REFERENCES WLCS_CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE

WLCS_CREDIT_CARD

If a customer is deleted from the database, the CUSTOMER_CREDIT_CARD_FK constraint causes all their associated credit cards to be deleted.

The constraint for the schema in Oracle is:
CONSTRAINT CUSTOMER_CREDIT_CARD_FK REFERENCES WLCS_CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE

WLCS_TRANSACTION_ENTRY

If a transaction is deleted from the database, the WLCS_TRANSACTION_FK constraint causes all the associated transaction entries be deleted.

The constraint for the schema in Oracle is:
CONSTRAINT WLCS_TRANSACTION_FK REFERENCES WLCS_TRANSACTION(TRANSACTION_ID) ON DELETE CASCADE

WLCS_ORDER_LINE

If an order is deleted from the database, the WLCS_ORDER_FK constraint causes all the associated order line items to be deleted.

The constraint for the schema in Oracle is:
CONSTRAINT ORDER_FK REFERENCES WLCS_ORDER(ORDER_ID) ON DELETE CASCADE

Table Name	Constraints as Defined in create-order-oracle.sql
WLCS_SHIPPING_ADDRESS	If a customer is deleted from the database, the CUSTOMER_FK constraint causes all their associated shipping addresses to be deleted. The constraint for the schema in Oracle is: CONSTRAINT CUSTOMER_FK REFERENCES WLCS_CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE
WLCS_CREDIT_CARD	If a customer is deleted from the database, the CUSTOMER_CREDIT_CARD_FK constraint causes all their associated credit cards to be deleted. The constraint for the schema in Oracle is: CONSTRAINT CUSTOMER_CREDIT_CARD_FK REFERENCES WLCS_CUSTOMER(CUSTOMER_ID) ON DELETE CASCADE
WLCS_TRANSACTION_ENTRY	If a transaction is deleted from the database, the WLCS_TRANSACTION_FK constraint causes all the associated transaction entries be deleted. The constraint for the schema in Oracle is: CONSTRAINT WLCS_TRANSACTION_FK REFERENCES WLCS_TRANSACTION(TRANSACTION_ID) ON DELETE CASCADE
WLCS_ORDER_LINE	If an order is deleted from the database, the WLCS_ORDER_FK constraint causes all the associated order line items to be deleted. The constraint for the schema in Oracle is: CONSTRAINT ORDER_FK REFERENCES WLCS_ORDER(ORDER_ID) ON DELETE CASCADE