WebSphere Commerce database table: SUBSCRIPTION

SUBSCRIPTION

Properties of subscriptions and recurring orders that has been placed by a specific shopper.

Column Descriptions:

Name Type Description
SUBSCRIPTION_ID BIGINT NOT NULL Generated unique key to denote the subscription ID.
CATENTRY_ID BIGINT The catalog entry of the subscription product or the item that is part of a recurring order.
ORDERITEMS_ID BIGINT The orderitem of which this subscription is part of. Null in case of recurring order
ORDERS_ID BIGINT NOT NULL The order of which this subscription or a recurring order is a part of.
STOREENT_ID INTEGER NOT NULL The store entity the subscription or recurring order is a part of.
MEMBER_ID BIGINT NOT NULL The customer that placed the subscription or recurring order
STATUS INTEGER NOT NULL DEFAULT 0 Status of the subscription or recurring order
0

INACTIVE

1

ACTIVE

2

EXPIRED

3

CANCELED

4

COMPLETED

5

SUSPENDED

6

PENDINGCANCEL

RECURRING INTEGER NOT NULL Flag to indicate whether this is a recurring order or not

0- NOT RECURRING
1- RECURRING

SUBSCPTYPE_ID CHAR (32) NOT NULL DEFAULT 'NONE' The identifier of the subscription type that the catalog entry maps to.

'NONE' in case of recurring orders
'TIME-BASED' in case of subscriptions

DESCRIPTION VARCHAR (254) A mnemonic description of the subscription or a recurring order, entered by the customer, suitable for display to the customer.
FULFILLMENT_FREQ INTEGER NOT NULL The fulfillment frequency specified for the subscription or recurring order. The UOM of this is specified in FFMFREQ_UOM
PAYMENT_FREQ INTEGER NOT NULL The payment frequency specified for the subscription or recurring order. 0 indicates upfront payment. The UOM of this is specified in PAYMENTFREQ_UOM
NEXTFFMDATE TIMESTAMP The next fulfillment date as calculated by the subscription scheduler.
NEXTPAYMENTDATE TIMESTAMP The next payment date as calculated by the subscription scheduler.
TIMEPERIOD DOUBLE The duration of the subscription or recurring order specified by the customer. The UOM of this is specified in TIMEPERIOD_UOM.
REMAININGCOUNT INTEGER DEFAULT 0 The remaining issues or fulfillment events of the subscription or recurring order. Ex: In case a 1 year subscription to monthly magazine, this column would store the number of issues(12) to be sent to the shopper
LASTUPDATE TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP The time this subscription or recurring order was most recently updated.
TIMEPLACED TIMESTAMP NOT NULL The time this subscription or recurring order was processed by the SubscriptionProcess command.
STARTDATE TIMESTAMP NOT NULL The start date specified by the customer for the subscription or recurring order.
ENDDATE TIMESTAMP NOT NULL The end date specified by the customer for the subscription or recurring order.
CANCELDATE TIMESTAMP The date when the subscription or recurring order was canceled.
QUANTITY INTEGER The quantity requested by the customer while placing an order.
TOTALCOST DECIMAL (20,5) DEFAULT 0 The total cost of the subscription or recurring order for the given time period. This is the sum of the orderitem cost, tax, shipping, discounts, etc.
AMOUNTPAID DECIMAL (20,5) DEFAULT 0 The total amount paid so far for this subscription or recurring order. In case of upfront payment, this value is the same as the total cost.
AMOUNT_TOCHARGE DECIMAL (20,5) DEFAULT 0 The total amount to be charged per installment for the subscription or recurring order, in case recurring payment option was chosen.
CURRENCY VARCHAR (10) The currency for monetary amounts associated with this subscription or recurring order. This is the currency code according to ISO 4217 standards.
SUBSCRTEMPLATE_ID BIGINT Reference to the template ID that will be associated to this subscription. Null in case of recurring orders.
FIELD1 INTEGER Customizable
FIELD2 DECIMAL (20,5) Customizable
FIELD3 VARCHAR (254) Customizable
FFMFREQ_UOM CHAR (16) NOT NULL The unit of measurement of the fulfillment frequency such as days, weeks, months, etc. The supported types are DAY, HUR, MON, WEEK, ANN
PAYMENTFREQ_UOM CHAR (16) NOT NULL The unit of measurement of the payment frequency such as days, weeks, months, etc.The supported types are DAY, HUR, MON, WEEK, ANN
STATUSREASON INTEGER The reason behind the status of the subscription or recurring order

1- Subscription canceled by the system due to backorder
2- Subscription canceled by the system due to bad inventory
3- Subscription canceled by the system as the catalog entry is not buyable
4- Subscription canceled by the system as the catalog entry has been marked for deletion
5- Subscription canceled by the system as the payment of a child order failed

TIMEPERIOD_UOM CHAR (16) The unit of measurement of the time period such as days, weeks, months, etc. The supported types are DAY, HUR, MON, WEEK, ANN
TRANSIENTSTATE INTEGER The state of the subscription or recurring order as updated by the subscription scheduler while processing a scheduled job.
OPTCOUNTER SMALLINT NOT NULL DEFAULT 0 The optimistic concurrency control counter for the table. Every time there is an update to the table, the counter is incremented.

Indexes:

Name Column Names Type
<SYSTEM-GENERATED> SUBSCRIPTION_ID Primary Key
I0001384 ORDERITEMS_ID+ORDERS_ID Unique Index
I0001360 CATENTRY_ID Non-Unique Index
I0001361 ORDERS_ID Non-Unique Index
I0001362 STOREENT_ID Non-Unique Index
I0001363 MEMBER_ID Non-Unique Index
I0001364 SUBSCPTYPE_ID Non-Unique Index
I0001365 SUBSCRTEMPLATE_ID Non-Unique Index
I0001404 FFMFREQ_UOM Non-Unique Index
I0001405 PAYMENTFREQ_UOM Non-Unique Index
I0001406 TIMEPERIOD_UOM Non-Unique Index

Constrained By Parent Tables:

Constraint Columns Parent Table Parent Columns Type
F_3646 CATENTRY_ID CATENTRY CATENTRY_ID Cascade
F_3647 SUBSCPTYPE_ID SUBSCPTYPE SUBSCPTYPE_ID Cascade
F_3648 ORDERITEMS_ID ORDERITEMS ORDERITEMS_ID Cascade
F_3649 ORDERS_ID ORDERS ORDERS_ID Cascade
F_3650 STOREENT_ID STOREENT STOREENT_ID Cascade
F_3651 MEMBER_ID MEMBER MEMBER_ID Cascade
F_3652 SUBSCRTEMPLATE_ID SUBSCRTEMPLATE SUBSCRTEMPLATE_ID Cascade
F_3690 FFMFREQ_UOM QTYUNIT QTYUNIT_ID Cascade
F_3691 PAYMENTFREQ_UOM QTYUNIT QTYUNIT_ID Cascade
F_3692 TIMEPERIOD_UOM QTYUNIT QTYUNIT_ID Cascade

Referenced By Child Tables:

Constraint Columns Child Table Child Columns Type
F_3653 SUBSCRIPTION_ID SUBSCRSUSPEND SUBSCRIPTION_ID Cascade
F_3659 SUBSCRIPTION_ID SUBSCRSCHJOBS SUBSCRIPTION_ID Cascade
F_3660 SUBSCRIPTION_ID SUBSCRRENEW RENEWEDSUBSCR_ID Cascade
F_3661 SUBSCRIPTION_ID SUBSCRRENEW SUBSCRIPTION_ID Cascade

Name	Type	Description
SUBSCRIPTION_ID	BIGINT NOT NULL	Generated unique key to denote the subscription ID.
CATENTRY_ID	BIGINT	The catalog entry of the subscription product or the item that is part of a recurring order.
ORDERITEMS_ID	BIGINT	The orderitem of which this subscription is part of. Null in case of recurring order
ORDERS_ID	BIGINT NOT NULL	The order of which this subscription or a recurring order is a part of.
STOREENT_ID	INTEGER NOT NULL	The store entity the subscription or recurring order is a part of.
MEMBER_ID	BIGINT NOT NULL	The customer that placed the subscription or recurring order
STATUS	INTEGER NOT NULL DEFAULT 0	Status of the subscription or recurring order 0 INACTIVE 1 ACTIVE 2 EXPIRED 3 CANCELED 4 COMPLETED 5 SUSPENDED 6 PENDINGCANCEL
RECURRING	INTEGER NOT NULL	Flag to indicate whether this is a recurring order or not 0- NOT RECURRING 1- RECURRING
SUBSCPTYPE_ID	CHAR (32) NOT NULL DEFAULT 'NONE'	The identifier of the subscription type that the catalog entry maps to. 'NONE' in case of recurring orders 'TIME-BASED' in case of subscriptions
DESCRIPTION	VARCHAR (254)	A mnemonic description of the subscription or a recurring order, entered by the customer, suitable for display to the customer.
FULFILLMENT_FREQ	INTEGER NOT NULL	The fulfillment frequency specified for the subscription or recurring order. The UOM of this is specified in FFMFREQ_UOM
PAYMENT_FREQ	INTEGER NOT NULL	The payment frequency specified for the subscription or recurring order. 0 indicates upfront payment. The UOM of this is specified in PAYMENTFREQ_UOM
NEXTFFMDATE	TIMESTAMP	The next fulfillment date as calculated by the subscription scheduler.
NEXTPAYMENTDATE	TIMESTAMP	The next payment date as calculated by the subscription scheduler.
TIMEPERIOD	DOUBLE	The duration of the subscription or recurring order specified by the customer. The UOM of this is specified in TIMEPERIOD_UOM.
REMAININGCOUNT	INTEGER DEFAULT 0	The remaining issues or fulfillment events of the subscription or recurring order. Ex: In case a 1 year subscription to monthly magazine, this column would store the number of issues(12) to be sent to the shopper
LASTUPDATE	TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP	The time this subscription or recurring order was most recently updated.
TIMEPLACED	TIMESTAMP NOT NULL	The time this subscription or recurring order was processed by the SubscriptionProcess command.
STARTDATE	TIMESTAMP NOT NULL	The start date specified by the customer for the subscription or recurring order.
ENDDATE	TIMESTAMP NOT NULL	The end date specified by the customer for the subscription or recurring order.
CANCELDATE	TIMESTAMP	The date when the subscription or recurring order was canceled.
QUANTITY	INTEGER	The quantity requested by the customer while placing an order.
TOTALCOST	DECIMAL (20,5) DEFAULT 0	The total cost of the subscription or recurring order for the given time period. This is the sum of the orderitem cost, tax, shipping, discounts, etc.
AMOUNTPAID	DECIMAL (20,5) DEFAULT 0	The total amount paid so far for this subscription or recurring order. In case of upfront payment, this value is the same as the total cost.
AMOUNT_TOCHARGE	DECIMAL (20,5) DEFAULT 0	The total amount to be charged per installment for the subscription or recurring order, in case recurring payment option was chosen.
CURRENCY	VARCHAR (10)	The currency for monetary amounts associated with this subscription or recurring order. This is the currency code according to ISO 4217 standards.
SUBSCRTEMPLATE_ID	BIGINT	Reference to the template ID that will be associated to this subscription. Null in case of recurring orders.
FIELD1	INTEGER	Customizable
FIELD2	DECIMAL (20,5)	Customizable
FIELD3	VARCHAR (254)	Customizable
FFMFREQ_UOM	CHAR (16) NOT NULL	The unit of measurement of the fulfillment frequency such as days, weeks, months, etc. The supported types are DAY, HUR, MON, WEEK, ANN
PAYMENTFREQ_UOM	CHAR (16) NOT NULL	The unit of measurement of the payment frequency such as days, weeks, months, etc.The supported types are DAY, HUR, MON, WEEK, ANN
STATUSREASON	INTEGER	The reason behind the status of the subscription or recurring order 1- Subscription canceled by the system due to backorder 2- Subscription canceled by the system due to bad inventory 3- Subscription canceled by the system as the catalog entry is not buyable 4- Subscription canceled by the system as the catalog entry has been marked for deletion 5- Subscription canceled by the system as the payment of a child order failed
TIMEPERIOD_UOM	CHAR (16)	The unit of measurement of the time period such as days, weeks, months, etc. The supported types are DAY, HUR, MON, WEEK, ANN
TRANSIENTSTATE	INTEGER	The state of the subscription or recurring order as updated by the subscription scheduler while processing a scheduled job.
OPTCOUNTER	SMALLINT NOT NULL DEFAULT 0	The optimistic concurrency control counter for the table. Every time there is an update to the table, the counter is incremented.

Name	Column Names	Type
<SYSTEM-GENERATED>	SUBSCRIPTION_ID	Primary Key
I0001384	ORDERITEMS_ID+ORDERS_ID	Unique Index
I0001360	CATENTRY_ID	Non-Unique Index
I0001361	ORDERS_ID	Non-Unique Index
I0001362	STOREENT_ID	Non-Unique Index
I0001363	MEMBER_ID	Non-Unique Index
I0001364	SUBSCPTYPE_ID	Non-Unique Index
I0001365	SUBSCRTEMPLATE_ID	Non-Unique Index
I0001404	FFMFREQ_UOM	Non-Unique Index
I0001405	PAYMENTFREQ_UOM	Non-Unique Index
I0001406	TIMEPERIOD_UOM	Non-Unique Index

Constraint	Columns	Parent Table	Parent Columns	Type
F_3646	CATENTRY_ID	CATENTRY	CATENTRY_ID	Cascade
F_3647	SUBSCPTYPE_ID	SUBSCPTYPE	SUBSCPTYPE_ID	Cascade
F_3648	ORDERITEMS_ID	ORDERITEMS	ORDERITEMS_ID	Cascade
F_3649	ORDERS_ID	ORDERS	ORDERS_ID	Cascade
F_3650	STOREENT_ID	STOREENT	STOREENT_ID	Cascade
F_3651	MEMBER_ID	MEMBER	MEMBER_ID	Cascade
F_3652	SUBSCRTEMPLATE_ID	SUBSCRTEMPLATE	SUBSCRTEMPLATE_ID	Cascade
F_3690	FFMFREQ_UOM	QTYUNIT	QTYUNIT_ID	Cascade
F_3691	PAYMENTFREQ_UOM	QTYUNIT	QTYUNIT_ID	Cascade
F_3692	TIMEPERIOD_UOM	QTYUNIT	QTYUNIT_ID	Cascade

Constraint	Columns	Child Table	Child Columns	Type
F_3653	SUBSCRIPTION_ID	SUBSCRSUSPEND	SUBSCRIPTION_ID	Cascade
F_3659	SUBSCRIPTION_ID	SUBSCRSCHJOBS	SUBSCRIPTION_ID	Cascade
F_3660	SUBSCRIPTION_ID	SUBSCRRENEW	RENEWEDSUBSCR_ID	Cascade
F_3661	SUBSCRIPTION_ID	SUBSCRRENEW	SUBSCRIPTION_ID	Cascade