The Order Processing Database Schema

 

This topic describes the database schema for Managing Purchases and Processing Orders services. 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

• List of Tables Comprising the Order Processing Schema

• The Order Processing Data Dictionary

• The SQL Scripts Used to Create the Database

• Defined Constraints

 

---

The Entity-Relation Diagram

Figure 1-1 shows the logical Entity-Relation diagram for the WebLogic Portal order and discount tables in the WebLogic Portal database. See the subsequent sections in this chapter for information about the data type syntax.

Figure 1-1 Entity-Relation Diagram for the Order and Discount Tables

 
 
 
 
 
 

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

 

---

List of Tables Comprising the Order Processing Schema

 

The Commerce services order management system is comprised of the following tables:

• The DISCOUNT Database Table

• The DISCOUNT_ASSOCIATION Database Table

• The ORDER_ADJUSTMENT Database Table

• The ORDER_LINE_ADJUSTMENT Database Table

• The WLCS_CREDIT_CARD Database Table

• The WLCS_CUSTOMER Database Table

• The WLCS_ORDER Database Table

• The WLCS_ORDER_LINE Database Table

• The WLCS_SAVED_ITEM_LIST Database Table

• The WLCS_SECURITY Database Table

• The WLCS_SHIPPING_ADDRESS Database Table

• The WLCS_SHIPPING_METHOD Database Table

• The WLCS_TRANSACTION Database Table

• The WLCS_TRANSACTION_ENTRY Database Table

 

---

The Order Processing Data Dictionary

In this section, the schema tables are arranged alphabetically as a data dictionary.

Note: Even though the following documentation references "foreign keys" to various tables, these constraints do not currently exist in this release of Commerce services. However, they will be in place in future versions of Commerce services and we want you to be aware of these relationships now.

The DISCOUNT Database Table

Table 1-1 describes the metadata for the Commerce services DISCOUNT table. This table stores stores one or more discount records for every DISCOUNT_SET record.

See the section for information about the constraint defined for this table.

The Primary Key is DISCOUNT_ID.

 

Table 1-1 DISCOUNT

Column Name	Data Type	Null Value	Description and Recommendations
DISCOUNT_ID	NUMBER(15)	NOT NULL	PK—a unique, system-generated number to be used as the record ID.
APPLICATION_NAME	VARCHAR(100)	NOT NULL	FK—foreign key to the DISCOUNT_SET table.
DISCOUNT_TYPE	VARCHAR(10)	NOT NULL	The type of discount offered. It is used for an order or for an order line item.
DISCOUNT_NAME	VARCHAR(254)	NOT NULL	The name of the discount.
IS_GLOBAL	NUMBER(1)	NOT NULL	A flag showing whether or not this discount can be used globally.
PRIORITY	NUMBER(3)	NOT NULL	The level of priority this discount has over other discounts.
ALLOWED_USERS	NUMBER(10)	NOT NULL	The number of times the discount may be used.
MODIFIER	VARCHAR(254)	NOT NULL	Describes the actual discount to be applied. This is XML.
DISCOUNT_RULE	CLOB	NOT NULL	The method used to select items for discount. This is XML.
START_DATE	DATE	NOT NULL	The starting date and time of the discount
END_DATE	DATE	NOT NULL	The ending date and time of the discount.
IS_ACTIVE	NUMBER(1)	NOT NULL	A flag that determines whether the discount is active or not. Active=1, Not active=0
DESCRIPTION	VARCHAR(254)	NULL	The discount description.
DISPLAY_DESCRIPTION	VARCHAR(254)	NULL	The discount description used for display purposes only.

 

The DISCOUNT_ASSOCIATION Database Table

Table 1-2 describes the metadata for the Commerce services DISCOUNT_ASSOCIATION table. This table associates each customer with a discount and maintains information regarding the times the customer has used each discount.

See the section for information about the constraint defined for this table.

The Primary Key is DISCOUNT_ASSOCIATION_ID.

 

Table 1-2 DISCOUNT_ASSOCIATION

Column Name	Data Type	Null Value	Description and Recommendations
DISCOUNT_ASSOCIATION_ID	NUMBER(15)	NOT NULL	PK—a unique, system-generated number to be used as the record ID.
CUSTOMER_ID	VARCHAR(20)	NOT NULL	FK—foreign key to the DISCOUNT_SET table.
DISCOUNT_ID	NUMBER(15)	NOT NULL	FK—foreign key to the DISCOUNT_SET table.
USE_COUNT	NUMBER(10)	NOT NULL	The number of times the discount has been used.
DISPLAY_DESCRIPTION	VARCHAR(254)	NULL	The discount description used for display purposes only.

 

The ORDER_ADJUSTMENT Database Table

Table 1-3 describes the metadata for the Commerce services ORDER_ADJUSTMENT table. This table is used to maintain information about a discount taken at the order level (for example, $20.00 off any order between 1/1/02 and 1/31/02.)

See the section for information about the constraint defined for this table.

The Primary Key is ORDER_ADJUSTMENT_ID.

 

Table 1-3 ORDER_ADJUSTMENT

Column Name	Data Type	Null Value	Description and Recommendations
ORDER_ADJUSTMENT_ID	NUMBER(15)	NOT NULL	PK—a unique, system-generated number to be used as the record ID.
ORDER_ID	VARCHAR(20)	NOT NULL	FK—foreign key to the DISCOUNT_SET table.
ADJUSTMENT_TYPE	VARCHAR(20)	NULL	The type of adjustment being made to the order line item (e.g., order line discount, shipping discount, etc.)
COMPUTATION	VARCHAR(254)	NOT NULL	The number of times the discount has been used.
ADJUSTMENT_AMOUNT	NUMBER(16,4)	NOT NULL	The discount description used for display purposes only.
DISCOUNT_ID	NUMBER(15)	NULL	FK—foreign key to the DISCOUNT table.
DISPLAY_DESCRIPTION	VARCHAR(254)	NULL	The description used for display purposes only. Depending on the nature of the discount, the DISPLAY_DESCRIPTION is generated from either the Discount service or Campaign service.
CREATION_DATE	DATE	NOT NULL	The date and time the order adjustment was created.
MODIFIED_DATE	DATE	NULL	The date and time the order adjustment record was last modified.

 

The ORDER_LINE_ADJUSTMENT Database Table

Table 1-4 describes the metadata for the Commerce services ORDER_LINE_ADJUSTMENT table. This table is used to maintain information about a discount taken at the order line item level (for example, 10% off SKU "Power Drill").

See the section for information about the constraint defined for this table.

The Primary Key is ORDER_LINE_ADJUSTMENT_ID.

Table 1-4 ORDER_LINE_ADJUSTMENT Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
ORDER_LINE_ADJUSTMENT_ID	NUMBER(15)	NOT NULL	PK—a unique, system-generated number to be used as the record ID.
ORDER_LINE_ID	NUMBER(15)	NOT NULL	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.
ADJUSTMENT_TYPE	VARCHAR(20)	NULL	The type of adjustment being made to the order line item (e.g., order line discount, shipping discount, etc.)
ADJUSTMENT_AMOUNT	NUMBER(16,4)	NOT NULL	The dollar amount of the adjustment.
ADJUSTMENT_QUANTITY	NUMBER(16,4)	NOT NULL	The quantity amount for the adjustment.
ADJUSTED_UNIT_PRICE	NUMBER(16,4)	NOT NULL	The adjusted unit price of the specific line item.
COMPUTATION	VARCHAR(254)	NOT NULL	The computation for determining ADJUSTED_UNIT_PRICE.
CREATION_DATE	DATE	NOT NULL	The date and time the adjustment record was created.
MODIFIED_DATE	DATE	NULL	The date and time the adjustment record was last modified.
DISCOUNT_ID	NUMBER(15)	NULL	FK—a foreign key to the discount used from the DISCOUNT table.
DISPLAY_DESCRIPTION	VARCHAR(254)	NULL	The adjustment description used for display purposes.

 

The WLCS_CREDIT_CARD Database Table

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

See the section for information about the constraint defined for this table.

The Primary Key is CREDIT_CARD_ID.

Table 1-5 WLCS_CREDIT_CARD Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
CREDIT_CARD_ID	NUMBER(15)	NOT NULL	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.
CC_NUMBER	VARCHAR(200)	NULL	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)	NULL	The customer's credit card type, such as VISA or MasterCard.
CC_EXP_DATE	DATE	NULL	The expiration date on the customer's credit card.
CC_NAME	VARCHAR(50)	NULL	The credit card holder's name.
CC_DISPLAY_NUMBER	VARCHAR(20)	NULL	The version of the credit card number that is displayed (all Xs except last 4-digits).
CC_COMPANY	VARCHAR(50)	NULL	The name of the credit card company.
BILLING_GEOCODE	VARCHAR(2)	NULL	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
BILLING_STREET1	VARCHAR(30)	NULL	The first line in the customer's billing address.
BILLING_STREET2	VARCHAR(30)	NULL	The second line in the customer's billing address.
BILLING_CITY	VARCHAR(30)	NULL	The city in the customer's billing address.
BILLING_STATE	VARCHAR(40)	NULL	The state in the customer's billing address.
BILLING_COUNTRY	VARCHAR(40)	NULL	The country in the customer's billing address.
BILLING_POBOX	VARCHAR(30)	NULL	The post office box in the customer's billing address.
BILLING_COUNTY	VARCHAR(50)	NULL	The county in the customer's billing address.
BILLING_POSTAL_CODE	VARCHAR(10)	NULL	The postal (ZIP) code in the customer's billing address.
BILLING_POSTAL_CODE_TYPE	VARCHAR(10)	NULL	Format or type of postal code, generally determined by country (such as ZIP code in the United States).
CUSTOMER_ID	VARCHAR(20)	NULL	A unique identifier for the customer.
MAP_KEY	VARCHAR(60)	NULL	Key that maps multiple credit cards with a single customer.

 

The WLCS_CUSTOMER Database Table

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

The Primary Key is CUSTOMER_ID.

Table 1-6 WLCS_CUSTOMER Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
CUSTOMER_ID	VARCHAR(20)	NOT NULL	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(20)	NULL	A label for the customer (such as preferred, standard, or business).
FIRST_NAME	VARCHAR(30)	NULL	The customer's first name.
LAST_NAME	VARCHAR(30)	NULL	The customer's last name.
MIDDLE_NAME	VARCHAR(30)	NULL	The customer's middle name.
TITLE	VARCHAR(10)	NULL	The customer's preferred title, such as Mr., Mrs., or Ms.
SUFFIX	VARCHAR(10)	NULL	The customer's preferred suffix, such as Jr.or Sr.
EMAIL	VARCHAR(80)	NULL	The customer's email address.
HOME_PHONE	VARCHAR(15)	NULL	The customer's home phone number.
BUSINESS_PHONE	VARCHAR(20)	NULL	The customer's business phone number.
FAX	VARCHAR(15)	NULL	The customer's fax number.
MAILING_GEOCODE	VARCHAR(2)	NULL	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
MAILING_STREET1	VARCHAR(30)	NULL	The first line in the customer's street address.
MAILING_STREET2	VARCHAR(30)	NULL	The second line in the customer's street address.
MAILING_CITY	VARCHAR(30)	NULL	The city in the customer's address.
MAILING_STATE	VARCHAR(40)	NULL	The state in the customer's address.
MAILING_COUNTRY	VARCHAR(40)	NULL	The country in the customer's address.
MAILING_POBOX	VARCHAR(30)	NULL	The post office box in the customer's address.
MAILING_COUNTY	VARCHAR(50)	NULL	The county in the customer's address.
MAILING_POSTAL_CODE	VARCHAR(10)	NULL	The postal (ZIP) code in the customer's address.
MAILING_POSTAL_CODE_TYPE	VARCHAR(10)	NULL	Format or type of postal code, generally determined by country (such as ZIP code in the United States).

 

The WLCS_ORDER Database Table

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

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

The Primary Key is ORDER_ID.

Table 1-7 WLCS_ORDER Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
ORDER_ID	VARCHAR(20)	NOT NULL	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)	NULL	A unique identifier for the customer.
TRANSACTION_ID	VARCHAR(25)	NULL	A unique identifier for the transaction.
STATUS	VARCHAR(20)	NULL	The status of the order.
ORDER_DATE	DATE	NULL	The date the order was placed.
SHIPPING_METHOD	VARCHAR(40)	NULL	The method by which the order is to be shipped.
SHIPPING_AMOUNT	NUMBER(16,4)	NULL	The shipping amount for the order.
SHIPPING_CURRENCY	VARCHAR(10)	NULL	The currency associated with the shipping amount.
PRICE_AMOUNT	NUMBER(16,4)	NULL	The price of the order.
PRICE_CURRENCY	VARCHAR(10)	NULL	The currency associated with the price.
SHIPPING_GEOGODE	VARCHAR(2)	NULL	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
SHIPPING_STREET1	VARCHAR(30)	NULL	The first line in the customer's shipping address.
SHIPPING_STREET2	VARCHAR(30)	NULL	The second line in the customer's shipping address.
SHIPPING_CITY	VARCHAR(30)	NULL	The city in the customer's shipping address.
SHIPPING_STATE	VARCHAR(40)	NULL	The state in the customer's shipping address.
SHIPPING_COUNTRY	VARCHAR(40)	NULL	The country in the customer's shipping address.
SHIPPING_POBOX	VARCHAR(30)	NULL	The post office box in the customer's shipping address.
SHIPPING_COUNTY	VARCHAR(50)	NULL	The county in the customer's shipping address.
SHIPPING_POSTAL_CODE	VARCHAR(10)	NULL	The postal (ZIP) code in the customer's shipping address.
SHIPPING_POSTAL_CODE_TYPE	VARCHAR(10)	NULL	Format or type of postal code, generally determined by country, such as ZIP code in the United States.
SPECIAL_INSTRUCTIONS	VARCHAR(254)	NULL	Any special shipping instructions associated with the order.
SPLITTING_PREFERENCE	VARCHAR(254)	NULL	The splitting preferences for the customer's order.
ORDER_SUBTOTAL	NUMBER(16,4)	NULL	The sum of all the TOTAL_LINE_AMOUNT columns in the WLCS_ORDER_LINE table for that specific order.

 

The WLCS_ORDER_LINE Database Table

Table 1-8 describes the metadata for the Commerce services 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.

See the section for information about the constraint defined for this table.

The Primary Key is ORDER_LINE_ID.

Table 1-8 WLCS_ORDER_LINE Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
ORDER_LINE_ID	NUMBER(15)	NOT NULL	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	NUMBER(16,4)	NULL	The quantity of the item in the shopping cart.
PRODUCT_ID	VARCHAR(40)	NULL	An identification number for the item in the shopping cart.
TAX_AMOUNT	NUMBER(16,4)	NULL	The tax amount for the order.
TAX_CURRENCY	VARCHAR(10)	NULL	The currency associated with the tax amount.
SHIPPING_AMOUNT	NUMBER(16,4)	NULL	The shipping amount for the order.
SHIPPING_CURRENCY	VARCHAR(10)	NULL	The currency associated with the shipping amount.
UNIT_PRICE_AMOUNT	NUMBER(16,4)	NULL	The unit price amount for the item.
UNIT_PRICE_CURRENCY	VARCHAR(10)	NULL	The currency associated with the unit price.
MSRP_AMOUNT	NUMBER(16,4)	NULL	The MSRP amount for the item.
MSRP_CURRENCY	VARCHAR(10)	NULL	The currency associated with the MSRP amount.
DESCRIPTION	VARCHAR(254)	NULL	The name of the item that is part of the order.
ORDER_ID	VARCHAR(20)	NULL	A unique identifier for the order.
TOTAL_LINE_AMOUNT	NUMBER(16,4)	NULL	The total discounted price for the line item. UNIT_PRICE_AMOUNT (less any discount) times the QUANTITY.

The WLCS_SAVED_ITEM_LIST Database Table

Table 1-9 describes the metadata for the Commerce services 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.

There is no Primary Key.

Table 1-9 WLCS_SAVED_ITEM_LIST Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
CUSTOMER_ID	VARCHAR(20)	NULL	A unique identifier for the customer.
SKU	VARCHAR(40)	NULL	A unique identifier (the Stock Keeping Unit or SKU) for a product item.

 

The WLCS_SECURITY Database Table

Table 1-10 describes the metadata for the Commerce services 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 Commerce services product.

There is no Primary Key.

Table 1-10 WLCS_SECURITY Table Metadata

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

 

The WLCS_SHIPPING_ADDRESS Database Table

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

See the section for information about the constraint defined for this table.

The Primary Key is SHIPPING_ADDRESS_ID.

Table 1-11 WLCS_SHIPPING_ADDRESS Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
SHIPPING_ADDRESS_ID	NUMBER(15)	NOT NULL	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.
MAP_KEY	VARCHAR(60)	NULL	Key that maps multiple shipping addresses with a single customer.
SHIPPING_GEOCODE	VARCHAR(2)	NULL	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
SHIPPING_STREET1	VARCHAR(30)	NULL	The first line in the customer's shipping address.
SHIPPING_STREET2	VARCHAR(30)	NULL	The second line in the customer's shipping address.
SHIPPING_CITY	VARCHAR(30)	NULL	The city in the customer's shipping address.
SHIPPING_STATE	VARCHAR(40)	NULL	The state in the customer's shipping address.
SHIPPING_COUNTRY	VARCHAR(40)	NULL	The country in the customer's shipping address.
SHIPPING_POBOX	VARCHAR(30)	NULL	The post office box in the customer's shipping address.
SHIPPING_COUNTY	VARCHAR(50)	NULL	The county in the customer's shipping address.
SHIPPING_POSTAL_CODE	VARCHAR(10)	NULL	The postal (zip) code in the customer's shipping address.
SHIPPING_POSTAL_CODE_TYPE	VARCHAR(10)	NULL	Format or type of postal code, generally determined by country, such as ZIP code in the United States.
CUSTOMER_ID	VARCHAR(20)	NULL	A unique identifier for the customer.

 

The WLCS_SHIPPING_METHOD Database Table

Table 1-12 describes the metadata for the Commerce services WLCS_SHIPPING_METHOD table. This table is used to store information about the shipping method in the order processing database.

The Primary Key is PK_IDENTIFIER.

Table 1-12 WLCS_SHIPPING_METHOD Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
PK_IDENTIFIER	VARCHAR(20)	NOT NULL	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)	NULL	The carrier being used to ship the order, such as UPS or FedEx.
METHOD	VARCHAR(40)	NULL	The method by which the order is to be shipped, such as Air, 2nd Day Air, or Parcel Post.
AVERAGE_SHIPPING_TIME	NUMBER	NULL	The average number of days it will take the order to arrive.
PRICE_VALUE	NUMBER(16,4)	NULL	The amount it will cost to ship the order.
PRICE_CURRENCY	VARCHAR(10)	NULL	The currency associated with the PRICE_VALUE column, such as dollars, pounds, or lira.
WEIGHT_LIMIT	NUMBER(16,4)	NULL	The weight limit for the shipment.
RESTRICTIONS	VARCHAR(254)	NULL	Any restrictions associated with the shipment.
DESCRIPTION	VARCHAR(254)	NULL	A description of the shipping method, such as FedEx Overnight or Standard.
PO_BOX_ALLOWED	NUMBER	NULL	Specifies whether or not the shipment can be left at a post office box.
SIGNATURE_REQUIRED	NUMBER	NULL	Specifies whether or not a signature is required upon receipt of the shipment.
SATURDAY_DELIVERY	NUMBER	NULL	Specifies whether or not the shipment can be delivered on Saturday.
INTERNATIONAL_DELIVERY	NUMBER	NULL	Specifies whether or not international delivery is an option.
SIZE_LIMIT	NUMBER(16,4)	NULL	The size limit for the shipment.
PACKAGING_TYPE	VARCHAR(50)	NULL	The packaging type for the shipment.

 

The WLCS_TRANSACTION Database Table

Table 1-13 describes the metadata for the Commerce services WLCS_TRANSACTION table. This table is used to store data for every payment transaction in the order processing database.

See the section for information about the constraint defined for this table.

The Primary Key is TRANSACTION_ID.

Table 1-13 WLCS_TRANSACTION Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
TRANSACTION_ID	VARCHAR(25)	NOT NULL	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)	NULL	A unique identifier of a batch submitted for settlement, as returned by the Payment Web service. This field need not be populated for other external payment services.
TRAN_DATE	DATE	NULL	The date of the transaction (that is, date on which the transaction was first started).
TRAN_STATUS	VARCHAR(20)	NULL	The current status of the transaction (Settled, Authorized, MarkedForSettle, PendingSettle, Retry, or Settled).
TRAN_AMOUNT	NUMBER(16,4)	NULL	The most recent amount applied to the transaction. MarkForSettle amounts can be different from the authorization amount.
TRAN_CURRENCY	VARCHAR(30)	NULL	The currency of the transaction.
CC_NUMBER	VARCHAR(200)	NULL	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)	NULL	The customer's credit card type, such as VISA or MasterCard.
CC_EXP_DATE	DATE	NULL	The expiration date on the customer's credit card.
CC_NAME	VARCHAR(50)	NULL	The credit card holder's name.
CC_DISPLAY_NUMBER	VARCHAR(20)	NULL	The version of the credit card number that is displayed (displays all Xs except last 4-digits).
CC_COMPANY	VARCHAR(50)	NULL	The name of the credit card company.
GEOCODE	VARCHAR(2)	NULL	The code used by the TAXWARE system to identify taxes for the order based on jurisdiction.
STREET1	VARCHAR(30)	NULL	The first line in the customer's street address.
STREET2	VARCHAR(30)	NULL	The second line in the customer's street address.
CITY	VARCHAR(30)	NULL	The city in the customer's address.
STATE	VARCHAR(40)	NULL	The state in the customer's address.
COUNTRY	VARCHAR(40)	NULL	The country in the customer's address.
POBOX	VARCHAR(30)	NULL	The post office box in the customer's address.
DESCRIPTION	VARCHAR(30)	NULL	Any additional data. Can be NULL.
COUNTY	VARCHAR(50)	NULL	The county in the customer's address.
POSTAL_CODE	VARCHAR(10)	NULL	The postal (ZIP) code in the customer's address.
POSTAL_CODE_TYPE	VARCHAR(10)	NULL	Format or type of postal code, generally determined by country, such as Zip code in the United States.

 

The WLCS_TRANSACTION_ENTRY Database Table

Table 1-14 describes the metadata for the Commerce services 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.

See for information about the constraint defined for this table.

The Primary Key is TRANSACTION_ENTRY_ID.

Table 1-14 WLCS_TRANSACTION_ENTRY Table Metadata

Column Name	Data Type	Null Value	Description and Recommendations
TRANSACTION_ENTRY_ID	NUMBER(25)	NOT NULL	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)	NULL	Represents the running count per transaction.
TRAN_ENTRY_DATE	DATE	NULL	The date of the log entry.
TRAN_ENTRY_STATUS	VARCHAR(20)	NULL	The status of the transaction when this entry was made.
TRAN_ENTRY_AMOUNT	NUMBER(16,4)	NULL	The amount of the transaction when the log entry was made.
TRAN_ENTRY_CURRENCY	VARCHAR(30)	NULL	The currency of the transaction.
TRANSACTION_ID	VARCHAR(25)	NULL	A unique identifier for the transaction.

 

 

---

The SQL Scripts Used to Create the Database

The database schemas for WebLogic Portal and WebLogic Personalization Server are all created by executing the create_all script for the target database environment.

Scripts

Regardless of your database, execute one of the following to generate the necessary database objects for the modules desired ( WebLogic Portal, WebLogic Personalization Server, Commerce services, Campaign services and Sample Portal):

• PORTAL_HOME\db\create_all.bat (Windows)

• PORTAL_HOME/db/create_all.sh (UNIX)

  The following are the various directories underneath WL_COMMERCE_HOME/db
  (as seen in a UNIX environment):

  PORTAL_HOME/db/cloudscape/351

  PORTAL_HOME/db/oracle/817

Note: In this documentation,PORTAL_HOME is used to designate the directory where the product is installed.

Each of the databases supported have the same number of scripts in each of their subdirectories. The scripts are listed and described in Table 1-15 below.

Table 1-15 The Scripts Supporting the Databases

Script Name	Description
create_all.bat	Windows script used to connect to the database and create the necessary database objects for the modules desired (e.g., WebLogic Portal, WebLogic Personalization Server, Commerce services, Campaign services and Sample Portal)
create_all.sh	Unix script used to connect to the database and create the necessary database objects for the modules desired (e.g., WebLogic Portal, WebLogic Personalization Server, Commerce services, Campaign services and Sample Portal)
campaign_create_fkeys.sql	SQL script used to create all foreign keys associated with the Campaign services.
campaign_create_indexes.sql	SQL script used to create all indexes associated with the Campaign services.
campaign_create_tables.sql	SQL script used to create all tables associated with the Campaign services.
campaign_create_triggers.sql	SQL script used to create all database triggers associated with the Campaign services.
campaign_create_views.sql	SQL script used to create all views associated with the Campaign services.
campaign_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the Campaign services.
campaign_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the Campaign services.
campaign_drop_indexes.sql	SQL script used to drop all indexes associated with the Campaign services.
campaign_drop_tables.sql	SQL script used to drop all tables associated with the Campaign services.
campaign_drop_views.sql	SQL script used to drop all views associated with the Campaign services.
p13n_create_fkeys.sql	SQL script used to create all foreign keys associated with the WebLogic Personalization Server.
p13n_create_indexes.sql	SQL script used to create all indexes associated with the WebLogic Personalization Server.
p13n_create_tables.sql	SQL script used to create all tables associated with the WebLogic Personalization Server.
p13n_create_triggers.sql	SQL script used to create all database triggers associated with the WebLogic Personalization Server.
p13n_create_views.sql	SQL script used to create all views associated with the WebLogic Personalization Server.
p13n_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the WebLogic Personalization Server.
p13n_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the WebLogic Personalization Server.
p13n_drop_indexes.sql	SQL script used to drop all indexes associated with the WebLogic Personalization Server.
p13n_drop_tables.sql	SQL script used to drop all tables associated with the WebLogic Personalization Server.
p13n_drop_views.sql	SQL script used to drop all views associated with the WebLogic Personalization Server.
portal_create_fkeys.sql	SQL script used to create all foreign keys associated with the WebLogic Portal.
portal_create_indexes.sql	SQL script used to create all indexes associated with the WebLogic Portal.
portal_create_tables.sql	SQL script used to create all tables associated with the WebLogic Portal.
portal_create_triggers.sql	SQL script used to create all database triggers associated with the WebLogic Portal.
portal_create_views.sql	SQL script used to create all views associated with the WebLogic Portal.
portal_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the WebLogic Portal.
portal_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the WebLogic Portal.
portal_drop_indexes.sql	SQL script used to drop all indexes associated with the WebLogic Portal.
portal_drop_tables.sql	SQL script used to drop all tables associated with the WebLogic Portal.
portal_drop_views.sql	SQL script used to drop all views associated with the WebLogic Portal.
sample_portal_create_fkeys.sql	SQL script used to create all foreign keys associated with the Sample Portal.
sample_portal_create_indexes.sql	SQL script used to create all indexes associated with the Sample Portal.
sample_portal_create_tables.sql	SQL script used to create all tables associated with the Sample Portal.
sample_portal_create_triggers.sql	SQL script used to create all database triggers associated with the Sample Portal.
sample_portal_create_views.sql	SQL script used to create all views associated with the Sample Portal.
sample_portal_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the Sample Portal.
sample_portal_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the Sample Portal.
sample_portal_drop_indexes.sql	SQL script used to drop all indexes associated with the Sample Portal.
sample_portal_drop_tables.sql	SQL script used to drop all tables associated with the Sample Portal.
sample_portal_drop_views.sql	SQL script used to drop all views associated with the Sample Portal.
wlcs_create_fkeys.sql	SQL script used to create all foreign keys associated with the Commerce services.
wlcs_create_indexes.sql	SQL script used to create all indexes associated with the Commerce services.
wlcs_create_tables.sql	SQL script used to create all tables associated with the Commerce services.
wlcs_create_triggers.sql	SQL script used to create all database triggers associated with the Commerce services.
wlcs_create_views.sql	SQL script used to create all views associated with the Commerce services.
wlcs_drop_constraints.sql	SQL script used to drop all constraints (other than foreign keys) associated with the Commerce services.
wlcs_drop_fkeys.sql	SQL script used to drop all foreign key constraints associated with the Commerce services.
wlcs_drop_indexes.sql	SQL script used to drop all indexes associated with the Commerce services.
wlcs_drop_tables.sql	SQL script used to drop all tables associated with the Commerce services.
wlcs_drop_views.sql	SQL script used to drop all views associated with the Commerce services.

 

 

---

Defined Constraints

Various constraints are defined and used in the Order database schema. These constraints can be found in the following scripts:

wlcs_create_fkeys.sql—contains the Foreign Keys

wlcs_create_tables.sql—contains the Check Constraints

 

Table 1-16 Constraints Defined on Order Database Tables

Table Name	Constraints
DISCOUNT_ASSOCIATION	Column—CUSTOMER_ID Constraint—FK1_DISC_ASSOC Constraint Type—FOREIGN KEY Ensures that each CUSTOMER_ID references an existing WLCS_CUSTOMER via the CUSTOMER_ID column. Column—DISCOUNT_ID Constraint—FK2_DISC_ASSOC Constraint Type—FOREIGN KEY Ensures that each DISCOUNT_ID references an existing DISCOUNT via the DISCOUNT_ID column.
WLCS_CREDIT_CARD	Column—CUSTOMER_ID Constraint—FK1_CREDIT_CARD Constraint Type—FOREIGN KEY Ensures that each CUSTOMER_ID references an existing WLCS_CUSTOMER via the CUSTOMER_ID column
WLCS_ORDER_LINE	Column—ORDER_ID Constraint—FK1_ORDER_LINE Constraint Type—FOREIGN KEY Ensures that each ORDER_ID references an existing WLCS_ORDER via the ORDER_ID column.
ORDER_ADJUSTMENT	Column—DISCOUNT_ID Constraint—FK1_ORDER_ADJ Constraint Type—FOREIGN KEY Ensures that each DISCOUNT_ID references an existing DISCOUNT via the DISCOUNT_ID column.
ORDER_LINE_ADJUSTMENT	Column—DISCOUNT_ID Constraint—FK1_ORDER_L_ADJ Constraint Type—FOREIGN KEY Ensures that each DISCOUNT_ID references an existing DISCOUNT via the DISCOUNT_ID column
WLCS_SHIPPING_ADDRESS	Column—CUSTOMER_ID Constraint— FK1_SHIP_ADDR Constraint Type—FOREIGN KEY Ensures that each CUSTOMER_ID references an existing WLCS_CUSTOMER via the CUSTOMER_ID column.
WLCS_TRANSACTION_ENTRY	Column—TRANSACTION_ID Constraint—FK1_TRANS_ENTRY Constraint Type—FOREIGN KEY Ensures that each TRANSACTION_ID references an existing WLCS_TRANSACTION via the TRANSACTION_ID column.
DISCOUNT	Column—IS_GLOBAL Constraint— CC1_DISCOUNT Constraint Type—CHECK Ensures the value of the IS_GLOBAL column is either 0 (false) or 1 (true). Column—IS_ACTIVE Constraint— CC2_DISCOUNT Constraint Type—CHECK Ensures the value of the IS_ACTIVE column is either 0 (false) or 1 (true).