COUPONS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ACTIVE_HUB_KEY

int

null

The Hub record ID

COUPON_ID

int

√

null

COUPON_QUALIFICATIONS.COUPON_ID	Defined in XML	R
COUPON_USAGE.COUPON_ID	Defined in XML	R
RECEIPT_DETAILS.COUPON_ID	Defined in XML	R

Coupon ID

COUPON_TITLE

varchar

100

√

null

Coupon title

COUPON_BATCH_ID

int

√

null

COUPON_BATCHES.COUPON_BATCH_ID

Defined in XML

Coupon batch ID if created as part of a batch

COUPON_BATCH_TITLE

varchar

100

√

null

Coupon batch title

COUPON_CODE

varchar

√

null

Coupon code

COUPON_TYPE

varchar

√

null

Coupon type

COUPON_CATEGORY_ID

int

√

null

COUPON_CATEGORIES.COUPON_CATEGORY_ID

Defined in XML

Coupon category ID

COUPON_CATEGORY_NAME

varchar

100

√

null

Coupon category

DISCOUNT_TYPE

varchar

√

null

Discount type (amount / discount)

DISCOUNT_AMOUNT

int

√

null

Coupon discount amount

DISCOUNT_PERCENT

int

√

null

Coupon discount percentage

COUPON_CONTRACT_ID

int

√

null

Coupon contract ID (if applicable)

CHARGE_ID

int

√

null

CHARGES.CHARGE_ID

Defined in XML

Charge ID to be associated to the coupons

CHARGE_NAME

varchar

√

null

Charge to be associated to the coupons

GLACCOUNT_ID

int

√

null

GLACCOUNTS.GLACCOUNT_ID

Defined in XML

Coupon GL account ID

GL_ACCOUNT_NAME

varchar

200

√

null

Coupon GL account name

GL_ACCOUNT_NUMBER

varchar

200

√

null

Coupon GL account number

DATE_FROM

date

√

null

Coupon effective date from

DATE_TO

date

√

null

Coupon effective date to

FREQUENCY_NUMBER

int

√

null

Coupon may be used again after this many days, weeks or months

FREQUENCY_PERIOD

varchar

√

null

Period for coupon reuse (Days / Weeks / Months)

DAYS_OF_WEEK

varchar

√

null

Whether the coupon can be used on specific days of the week

WEEKS_OF_MONTH

varchar

√

null

Whether the coupon can be used on specific weeks of the month

MONTHS_OF_YEAR

varchar

√

null

Whether the coupon can be used on specific months of the year

MAX_NUMBER_OF_USES

int

√

null

The total number of times that the coupon may be used

MAX_NUMBER_OF_USES_PER_CUSTOMER

int

√

null

Maximum uses per customer for each coupon

TOTAL_COUPONS_USED

int

√

null

The total number of times that this coupon has been used

TURN_OFF_COUPON

varchar

√

null

Whether use of this coupon has been turned off (Yes/No)

DAYS_SUNDAY

varchar

null

Whether the coupon can be used on Sundays (Yes/No)

DAYS_MONDAY

varchar

null

Whether the coupon can be used on Mondays (Yes/No)

DAYS_TUESDAY

varchar

null

Whether the coupon can be used on Tuesdays (Yes/No)

DAYS_WEDNESDAY

varchar

null

Whether the coupon can be used on Wednesdays (Yes/No)

DAYS_THURSDAY

varchar

null

Whether the coupon can be used on Thursdays (Yes/No)

DAYS_FRIDAY

varchar

null

Whether the coupon can be used on Fridays (Yes/No)

DAYS_SATURDAY

varchar

null

Whether the coupon can be used on Saturdays (Yes/No)

WEEK_OF_MONTH_1

varchar

null

Whether the coupon can be used on days during the first week of the month (Yes/No)

WEEK_OF_MONTH_2

varchar

null

Whether the coupon can be used on days during the second week of the month (Yes/No)

WEEK_OF_MONTH_3

varchar

null

Whether the coupon can be used on days during the third week of the month (Yes/No)

WEEK_OF_MONTH_4

varchar

null

Whether the coupon can be used on days during the fourth week of the month (Yes/No)

WEEK_OF_MONTH_5

varchar

null

Whether the coupon can be used on days during the last week of the month (Yes/No)

MONTHS_JANUARY

varchar

null

Whether the coupon can be used during the month of January (Yes/No)

MONTHS_FEBRUARY

varchar

null

Whether the coupon can be used during the month of February (Yes/No)

MONTHS_MARCH

varchar

null

Whether the coupon can be used during the month of March (Yes/No)

MONTHS_APRIL

varchar

null

Whether the coupon can be used during the month of April (Yes/No)

MONTHS_MAY

varchar

null

Whether the coupon can be used during the month of May (Yes/No)

MONTHS_JUNE

varchar

null

Whether the coupon can be used during the month of June (Yes/No)

MONTHS_JULY

varchar

null

Whether the coupon can be used during the month of July (Yes/No)

MONTHS_AUGUST

varchar

null

Whether the coupon can be used during the month of August (Yes/No)

MONTHS_SEPTEMBER

varchar

null

Whether the coupon can be used during the month of September (Yes/No)

MONTHS_OCTOBER

varchar

null

Whether the coupon can be used during the month of October (Yes/No)

MONTHS_NOVEMBER

varchar

null

Whether the coupon can be used during the month of November (Yes/No)

MONTHS_DECEMBER

varchar

null

Whether the coupon can be used during the month of December (Yes/No)

LAST_SYNC_TIME

datetime

16,3

null

Date and time Hub record was last modified

ACTIVE_HUB_PROCESS_ID

bigint

√

null

The Hub sync process ID

ORG_ID

int

√

null

Your organization’s unique identifier in the Active Net system

Relationships

Close relationships within degrees of separation

One
Two degrees

View Definition

CREATE   VIEW dbo.COUPONS
AS
SELECT
    src.ACTIVE_HUB_KEY,
    src.COUPON_ID,
    src.COUPON_TITLE,
    src.COUPON_BATCH_ID,
    cb1.COUPON_BATCH_TITLE,
    src.COUPON_CODE,
    src.COUPON_TYPE,
    src.COUPON_CATEGORY_ID,
    cc1.COUPON_CATEGORY_NAME,
    src.DISCOUNT_TYPE,
    src.DISCOUNT_AMOUNT,
    src.DISCOUNT_PERCENT,
    src.COUPON_CONTRACT_ID,
    src.CHARGE_ID,
    ch1.CHARGE_NAME,
    src.GLACCOUNT_ID,
    gl1.ACCOUNTNAME AS GL_ACCOUNT_NAME,
    gl1.ACCOUNTNUMBER AS GL_ACCOUNT_NUMBER,
    src.DATE_FROM,
    src.DATE_TO,
    src.FREQUENCY_NUMBER,
    src.FREQUENCY_PERIOD,
    src.DAYS_OF_WEEK,
    src.WEEKS_OF_MONTH,
    src.MONTHS_OF_YEAR,
    src.MAX_NUMBER_OF_USES,
    src.MAX_NUMBER_OF_USES_PER_CUSTOMER,
    src.TOTAL_COUPONS_USED,
    src.TURN_OFF_COUPON,
    (CASE WHEN SUBSTRING(src.DAYS_OF_WEEK, 1, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_SUNDAY,
    (CASE WHEN SUBSTRING(src.DAYS_OF_WEEK, 2, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_MONDAY,
    (CASE WHEN SUBSTRING(src.DAYS_OF_WEEK, 3, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_TUESDAY,
    (CASE WHEN SUBSTRING(src.DAYS_OF_WEEK, 4, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_WEDNESDAY,
    (CASE WHEN SUBSTRING(src.DAYS_OF_WEEK, 5, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_THURSDAY,
    (CASE WHEN SUBSTRING(src.DAYS_OF_WEEK, 6, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_FRIDAY,
    (CASE WHEN SUBSTRING(src.DAYS_OF_WEEK, 7, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_SATURDAY,
    (CASE WHEN SUBSTRING(src.WEEKS_OF_MONTH, 1, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_1,
    (CASE WHEN SUBSTRING(src.WEEKS_OF_MONTH, 2, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_2,
    (CASE WHEN SUBSTRING(src.WEEKS_OF_MONTH, 3, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_3,
    (CASE WHEN SUBSTRING(src.WEEKS_OF_MONTH, 4, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_4,
    (CASE WHEN SUBSTRING(src.WEEKS_OF_MONTH, 5, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_5,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 1, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_JANUARY,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 2, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_FEBRUARY,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 3, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_MARCH,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 4, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_APRIL,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 5, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_MAY,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 6, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_JUNE,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 7, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_JULY,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 8, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_AUGUST,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 9, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_SEPTEMBER,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 10, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_OCTOBER,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 11, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_NOVEMBER,
    (CASE WHEN SUBSTRING(src.MONTHS_OF_YEAR, 12, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_DECEMBER,
    src.LAST_SYNC_TIME,
    src.ACTIVE_HUB_PROCESS_ID,
    src.ORG_ID
FROM 
    hub.COUPONS src
    LEFT JOIN hub.COUPON_BATCHES cb1 ON (cb1.COUPON_BATCH_ID = src.COUPON_BATCH_ID)
    LEFT JOIN hub.GLACCOUNTS gl1 ON (gl1.GLACCOUNT_ID = src.GLACCOUNT_ID)
    LEFT JOIN hub.CHARGES ch1 ON (ch1.CHARGE_ID = src.CHARGE_ID)
    LEFT JOIN hub.COUPON_CATEGORIES cc1 ON (cc1.COUPON_CATEGORY_ID = src.COUPON_CATEGORY_ID);

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views