SUBSCRIPTION

This table stores the properties of subscriptions and recurring orders that has been placed by a specific shopper.

Column Descriptions:

NameTypeDescription
SUBSCRIPTION_IDBIGINT NOT NULLGenerated unique key to denote the subscription ID.
CATENTRY_IDBIGINTThe catalog entry of the subscription product or the item that is part of a recurring order.
ORDERITEMS_IDBIGINTThe orderitem of which this subscription is part of. Null in case of recurring order
ORDERS_IDBIGINT NOT NULLThe order of which this subscription or a recurring order is a part of.
STOREENT_IDINTEGER NOT NULLThe store entity the subscription or recurring order is a part of.
MEMBER_IDBIGINT NOT NULLThe customer that placed the subscription or recurring order
STATUSINTEGER NOT NULL DEFAULT 0Status of the subscription or recurring order
0
INACTIVE
1
ACTIVE
2
EXPIRED
3
CANCELED
4
COMPLETED
5
SUSPENDED
6
PENDINGCANCEL
RECURRINGINTEGER NOT NULLFlag to indicate whether this is a recurring order or not
  • 0- NOT RECURRING
  • 1- RECURRING
SUBSCPTYPE_IDCHAR (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
DESCRIPTIONVARCHAR (254)A mnemonic description of the subscription or a recurring order, entered by the customer, suitable for display to the customer.
FULFILLMENT_FREQINTEGER NOT NULLThe fulfillment frequency specified for the subscription or recurring order. The UOM of this is specified in FFMFREQ_UOM
PAYMENT_FREQINTEGER NOT NULLThe payment frequency specified for the subscription or recurring order. 0 indicates upfront payment. The UOM of this is specified in PAYMENTFREQ_UOM
NEXTFFMDATETIMESTAMPThe next fulfillment date as calculated by the subscription scheduler.
NEXTPAYMENTDATETIMESTAMPThe next payment date as calculated by the subscription scheduler.
TIMEPERIODDOUBLEThe duration of the subscription or recurring order specified by the customer. The UOM of this is specified in TIMEPERIOD_UOM.
REMAININGCOUNTINTEGER DEFAULT 0The 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
LASTUPDATETIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMPThe time this subscription or recurring order was most recently updated.
TIMEPLACEDTIMESTAMP NOT NULLThe time this subscription or recurring order was processed by the SubscriptionProcess command.
STARTDATETIMESTAMP NOT NULLThe start date specified by the customer for the subscription or recurring order.
ENDDATETIMESTAMP NOT NULLThe end date specified by the customer for the subscription or recurring order.
CANCELDATETIMESTAMPThe date when the subscription or recurring order was canceled.
QUANTITYINTEGERThe quantity requested by the customer while placing an order.
TOTALCOSTDECIMAL (20,5) DEFAULT 0The 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.
AMOUNTPAIDDECIMAL (20,5) DEFAULT 0The 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_TOCHARGEDECIMAL (20,5) DEFAULT 0The total amount to be charged per installment for the subscription or recurring order, in case recurring payment option was chosen.
CURRENCYVARCHAR (10)The currency for monetary amounts associated with this subscription or recurring order. This is the currency code according to ISO 4217 standards.
SUBSCRTEMPLATE_IDBIGINTReference to the template ID that will be associated to this subscription. Null in case of recurring orders.
FIELD1INTEGERCustomizable
FIELD2DECIMAL (20,5)Customizable
FIELD3VARCHAR (254)Customizable
FFMFREQ_UOMCHAR (16) NOT NULLThe unit of measurement of the fulfillment frequency such as days, weeks, months, etc. The supported types are DAY, HUR, MON, WEEK, ANN
PAYMENTFREQ_UOMCHAR (16) NOT NULLThe unit of measurement of the payment frequency such as days, weeks, months, etc.The supported types are DAY, HUR, MON, WEEK, ANN
STATUSREASONINTEGERThe 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_UOMCHAR (16)The unit of measurement of the time period such as days, weeks, months, etc. The supported types are DAY, HUR, MON, WEEK, ANN
TRANSIENTSTATEINTEGERThe state of the subscription or recurring order as updated by the subscription scheduler while processing a scheduled job.
OPTCOUNTERSMALLINT NOT NULL DEFAULT 0The optimistic concurrency control counter for the table. Every time there is an update to the table, the counter is incremented.

Indexes:

NameColumn NamesType
<SYSTEM-GENERATED>SUBSCRIPTION_IDPrimary Key
I0001384ORDERITEMS_ID+ORDERS_IDUnique Index
I0001360CATENTRY_IDNon-Unique Index
I0001361ORDERS_IDNon-Unique Index
I0001362STOREENT_IDNon-Unique Index
I0001363MEMBER_IDNon-Unique Index
I0001364SUBSCPTYPE_IDNon-Unique Index
I0001365SUBSCRTEMPLATE_IDNon-Unique Index
I0001404FFMFREQ_UOMNon-Unique Index
I0001405PAYMENTFREQ_UOMNon-Unique Index
I0001406TIMEPERIOD_UOMNon-Unique Index

Constrained By Parent Tables:

ConstraintColumnsParent TableParent ColumnsType
F_3646CATENTRY_IDCATENTRYCATENTRY_IDCascade
F_3647SUBSCPTYPE_IDSUBSCPTYPESUBSCPTYPE_IDCascade
F_3648ORDERITEMS_IDORDERITEMSORDERITEMS_IDCascade
F_3649ORDERS_IDORDERSORDERS_IDCascade
F_3650STOREENT_IDSTOREENTSTOREENT_IDCascade
F_3651MEMBER_IDMEMBERMEMBER_IDCascade
F_3652SUBSCRTEMPLATE_IDSUBSCRTEMPLATESUBSCRTEMPLATE_IDCascade
F_3690FFMFREQ_UOMQTYUNITQTYUNIT_IDCascade
F_3691PAYMENTFREQ_UOMQTYUNITQTYUNIT_IDCascade
F_3692TIMEPERIOD_UOMQTYUNITQTYUNIT_IDCascade

Referenced By Child Tables:

ConstraintColumnsChild TableChild ColumnsType
F_3653SUBSCRIPTION_IDSUBSCRSUSPENDSUBSCRIPTION_IDCascade
F_3659SUBSCRIPTION_IDSUBSCRSCHJOBSSUBSCRIPTION_IDCascade
F_3660SUBSCRIPTION_IDSUBSCRRENEWRENEWEDSUBSCR_IDCascade
F_3661SUBSCRIPTION_IDSUBSCRRENEWSUBSCRIPTION_IDCascade