COUPON_QUALIFICATIONS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ACTIVE_HUB_KEY

int

null

The Hub record ID

COUPON_QUALIFICATION_ID

int

√

null

Coupon qualification ID

COUPON_ID

int

√

null

COUPONS.COUPON_ID

Defined in XML

Coupon ID

COUPON_TITLE

varchar

100

√

null

Coupon title

COMPANY_ID

int

√

null

COMPANIES.COMPANY_ID

Defined in XML

Company ID

COMPANY_NAME

varchar

255

√

null

Company name

CUSTOMER_TYPE_ID

int

√

null

CUSTOMER_TYPES.CUSTOMER_TYPE_ID

Defined in XML

Customer type ID

CUSTOMER_TYPE_NAME

varchar

√

null

Customer type name

CUSTOMER_ID

int

√

null

CUSTOMERS.CUSTOMER_ID

Defined in XML

Customer ID

CUSTOMER_NAME

varchar

511

√

null

Customer name

CUSTOMER_NAME_LAST_FIRST

varchar

512

√

null

Customer name (last name, first name)

ACTIVITY_ID

int

√

null

ACTIVITIES.ACTIVITY_ID

Defined in XML

Activity ID

ACTIVITY_NAME

varchar

100

√

null

Activity name

CATEGORY_ID

int

√

null

ACTIVITY_CATEGORIES.ACTIVITY_CATEGORY_ID

Defined in XML

Activity category ID

CATEGORY_NAME

varchar

√

null

Activity category name

SEASON_ID

int

√

null

SEASONS.SEASON_ID

Defined in XML

Activity season ID

SEASON_NAME

varchar

√

null

Activity season name

PROGRAM_ID

int

√

null

PROGRAMS.PROGRAM_ID

Defined in XML

Program ID

PROGRAM_NAME

varchar

255

√

null

Program name

PROGRAM_TYPE_ID

int

√

null

PROGRAM_TYPES.PROGRAM_TYPE_ID

Defined in XML

Program type ID

PROGRAM_TYPE_NAME

varchar

√

null

Program type name

PACKAGE_ID

int

√

null

PACKAGES.PACKAGE_ID

Defined in XML

Package ID

PACKAGE_NAME

varchar

255

√

null

Package name

PACKAGE_CATEGORY_ID

int

√

null

PACKAGE_CATEGORIES.PACKAGE_CATEGORY_ID

Defined in XML

Package category ID

PACKAGE_CATEGORY_NAME

varchar

√

null

Package category name

POSPRODUCT_ID

int

√

null

POS_PRODUCTS.POSPRODUCT_ID

Defined in XML

POS product name

PRODUCT_NAME

varchar

100

√

null

POS product name

PRODUCT_DEPARTMENT_ID

int

√

null

PRODUCT_DEPARTMENTS.PRODUCT_DEPARTMENT_ID

Defined in XML

Product department ID

PRODUCT_DEPARTMENT_NAME

varchar

√

null

Product department name

PRODUCT_CLASS_ID

int

√

null

PRODUCT_CLASSES.PRODUCT_CLASS_ID

Defined in XML

Product class ID

PRODUCT_CLASS_NAME

varchar

√

null

Product class name

PRODUCT_SUBCLASS_ID

int

√

null

PRODUCT_SUBCLASSES.PRODUCT_SUBCLASS_ID

Defined in XML

Product subclass ID

PRODUCT_SUBCLASS_NAME

varchar

√

null

Product subclass name

USAGE_FEE_ID

int

√

null

USAGE_FEES.USAGE_FEE_ID

Defined in XML

Usage fee ID

USAGE_FEE_NAME

varchar

√

null

Usage fee name

DATE_FROM

date

√

null

Coupon effective date from

DATE_TO

date

√

null

Coupon effective date to

HAS_OVERRIDES

varchar

null

Whether this qualification has overrides of the coupon settings (Yes/No)

GLACCOUNT_ID

int

√

null

GLACCOUNTS.GLACCOUNT_ID

Defined in XML

Coupon GL account ID - if override

GL_ACCOUNT_NAME

varchar

200

√

null

Coupon GL account name - if override

GL_ACCOUNT_NUMBER

varchar

200

√

null

Coupon GL account number - if override

DISCOUNT_TYPE

varchar

√

null

Discount type - if override (amount / discount)

DISCOUNT_AMOUNT

money

21,4

√

null

Coupon discount amount - if override

DISCOUNT_PERCENT

money

21,4

√

null

Coupon discount percentage - if override

DAYS_OF_WEEK

varchar

√

null

Whether the coupon can be used on specific days of the week - if override

WEEKS_OF_MONTH

varchar

√

null

Whether the coupon can be used on specific weeks of the month - if override

MONTHS_OF_YEAR

varchar

√

null

Whether the coupon can be used on specific months of the year - if override

MAX_NUMBER_OF_USES

int

√

null

The total number of times that the coupon may be used - if override

MAX_NUMBER_OF_USES_PER_CUSTOMER

int

√

null

Maximum uses per customer for each coupon - if override

DAYS_SUNDAY

varchar

√

null

Whether the coupons will be configured for use on Sundays - if override (Yes/No)

DAYS_MONDAY

varchar

√

null

Whether the coupons will be configured for use on Mondays - if override (Yes/No)

DAYS_TUESDAY

varchar

√

null

Whether the coupons will be configured for use on Tuesdays - if override (Yes/No)

DAYS_WEDNESDAY

varchar

√

null

Whether the coupons will be configured for use on Wednesdays - if override (Yes/No)

DAYS_THURSDAY

varchar

√

null

Whether the coupons will be configured for use on Thursdays - if override (Yes/No)

DAYS_FRIDAY

varchar

√

null

Whether the coupons will be configured for use on Fridays - if override (Yes/No)

DAYS_SATURDAY

varchar

√

null

Whether the coupons will be configured for use on Saturdays - if override (Yes/No)

WEEK_OF_MONTH_1

varchar

√

null

Whether the coupons will be configured for use on days during the first week of the month - if override (Yes/No)

WEEK_OF_MONTH_2

varchar

√

null

Whether the coupons will be configured for use on days during the second week of the month - if override (Yes/No)

WEEK_OF_MONTH_3

varchar

√

null

Whether the coupons will be configured for use on days during the third week of the month - if override (Yes/No)

WEEK_OF_MONTH_4

varchar

√

null

Whether the coupons will be configured for use on days during the fourth week of the month - if override (Yes/No)

WEEK_OF_MONTH_5

varchar

√

null

Whether the coupons will be configured for use on days during the last week of the month - if override (Yes/No)

MONTHS_JANUARY

varchar

√

null

Whether the coupons will be configured for use during the month of January - if override (Yes/No)

MONTHS_FEBRUARY

varchar

√

null

Whether the coupons will be configured for use during the month of February - if override (Yes/No)

MONTHS_MARCH

varchar

√

null

Whether the coupons will be configured for use during the month of March - if override (Yes/No)

MONTHS_APRIL

varchar

√

null

Whether the coupons will be configured for use during the month of April - if override (Yes/No)

MONTHS_MAY

varchar

√

null

Whether the coupons will be configured for use during the month of May - if override (Yes/No)

MONTHS_JUNE

varchar

√

null

Whether the coupons will be configured for use during the month of June - if override (Yes/No)

MONTHS_JULY

varchar

√

null

Whether the coupons will be configured for use during the month of July - if override (Yes/No)

MONTHS_AUGUST

varchar

√

null

Whether the coupons will be configured for use during the month of August - if override (Yes/No)

MONTHS_SEPTEMBER

varchar

√

null

Whether the coupons will be configured for use during the month of September - if override (Yes/No)

MONTHS_OCTOBER

varchar

√

null

Whether the coupons will be configured for use during the month of October - if override (Yes/No)

MONTHS_NOVEMBER

varchar

√

null

Whether the coupons will be configured for use during the month of November - if override (Yes/No)

MONTHS_DECEMBER

varchar

√

null

Whether the coupons will be configured for use during the month of December - if override (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.COUPON_QUALIFICATIONS
AS
SELECT
    src.ACTIVE_HUB_KEY,
    src.COUPON_QUALIFICATION_ID,
    src.COUPON_ID,
    cou.COUPON_TITLE,
    src.COMPANY_ID,
    co1.COMPANY_NAME,
    src.CUSTOMER_TYPE_ID,
    ct1.CUSTOMER_TYPE_NAME,
    src.CUSTOMER_ID,
    NULLIF(CONCAT(cu1.CUSTOMER_FIRST_NAME, IIF(cu1.CUSTOMER_FIRST_NAME IS NOT NULL AND cu1.CUSTOMER_LAST_NAME IS NOT NULL, ' ', ''), cu1.CUSTOMER_LAST_NAME), '') AS CUSTOMER_NAME,
    NULLIF(CONCAT(cu1.CUSTOMER_LAST_NAME, IIF(cu1.CUSTOMER_FIRST_NAME IS NOT NULL AND cu1.CUSTOMER_LAST_NAME IS NOT NULL, ', ', ''), cu1.CUSTOMER_FIRST_NAME), '') AS CUSTOMER_NAME_LAST_FIRST,
    src.ACTIVITY_ID,
    ac1.ACTIVITYNAME AS ACTIVITY_NAME,
    src.CATEGORY_ID,
    acat.ACTIVITY_CATEGORY_NAME AS CATEGORY_NAME,
    src.SEASON_ID,
    se1.SEASON_NAME,
    src.PROGRAM_ID,
    pr1.PROGRAM_NAME,
    src.PROGRAM_TYPE_ID,
    pt1.PROGRAM_TYPE_NAME,
    src.PACKAGE_ID,
    pa1.PACKAGE_NAME,
    src.PACKAGE_CATEGORY_ID,
    pc1.CATEGORY_NAME AS PACKAGE_CATEGORY_NAME,
    src.POSPRODUCT_ID,
    ppr.PRODUCT_NAME,
    src.PRODUCT_DEPARTMENT_ID,
    pd1.PRODUCT_DEPARTMENT_NAME,
    src.PRODUCT_CLASS_ID,
    pc2.PRODUCT_CLASS_NAME,
    src.PRODUCT_SUBCLASS_ID,
    psc1.PRODUCT_SUBCLASS_NAME,
    src.USAGE_FEE_ID,
    uf1.USAGE_FEE_NAME,
    src.DATE_FROM,
    src.DATE_TO,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN 'No' ELSE 'Yes' END) AS HAS_OVERRIDES,
    src.GLACCOUNT_ID,
    gl1.ACCOUNTNAME AS GL_ACCOUNT_NAME,
    gl1.ACCOUNTNUMBER AS GL_ACCOUNT_NUMBER,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL ELSE src.DISCOUNT_TYPE END) AS DISCOUNT_TYPE,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL ELSE src.DISCOUNT_AMOUNT END) AS DISCOUNT_AMOUNT,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL ELSE src.DISCOUNT_PERCENT END) AS DISCOUNT_PERCENT,
    src.DAYS_OF_WEEK,
    src.WEEKS_OF_MONTH,
    src.MONTHS_OF_YEAR,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL ELSE src.MAX_NUMBER_OF_USES END) AS MAX_NUMBER_OF_USES,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL ELSE src.MAX_NUMBER_OF_USES_PER_CUSTOMER END) AS MAX_NUMBER_OF_USES_PER_CUSTOMER,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL WHEN SUBSTRING(src.DAYS_OF_WEEK, 1, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_SUNDAY,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL WHEN SUBSTRING(src.DAYS_OF_WEEK, 2, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_MONDAY,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL WHEN SUBSTRING(src.DAYS_OF_WEEK, 3, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_TUESDAY,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL WHEN SUBSTRING(src.DAYS_OF_WEEK, 4, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_WEDNESDAY,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL WHEN SUBSTRING(src.DAYS_OF_WEEK, 5, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_THURSDAY,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL WHEN SUBSTRING(src.DAYS_OF_WEEK, 6, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_FRIDAY,
    (CASE WHEN src.DAYS_OF_WEEK IS NULL THEN NULL WHEN SUBSTRING(src.DAYS_OF_WEEK, 7, 1) = 1 THEN 'Yes' ELSE 'No' END) AS DAYS_SATURDAY,
    (CASE WHEN src.WEEKS_OF_MONTH IS NULL THEN NULL WHEN SUBSTRING(src.WEEKS_OF_MONTH, 1, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_1,
    (CASE WHEN src.WEEKS_OF_MONTH IS NULL THEN NULL WHEN SUBSTRING(src.WEEKS_OF_MONTH, 2, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_2,
    (CASE WHEN src.WEEKS_OF_MONTH IS NULL THEN NULL WHEN SUBSTRING(src.WEEKS_OF_MONTH, 3, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_3,
    (CASE WHEN src.WEEKS_OF_MONTH IS NULL THEN NULL WHEN SUBSTRING(src.WEEKS_OF_MONTH, 4, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_4,
    (CASE WHEN src.WEEKS_OF_MONTH IS NULL THEN NULL WHEN SUBSTRING(src.WEEKS_OF_MONTH, 5, 1) = 1 THEN 'Yes' ELSE 'No' END) AS WEEK_OF_MONTH_5,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 1, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_JANUARY,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 2, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_FEBRUARY,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 3, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_MARCH,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 4, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_APRIL,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 5, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_MAY,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 6, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_JUNE,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 7, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_JULY,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 8, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_AUGUST,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 9, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_SEPTEMBER,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 10, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_OCTOBER,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL WHEN SUBSTRING(src.MONTHS_OF_YEAR, 11, 1) = 1 THEN 'Yes' ELSE 'No' END) AS MONTHS_NOVEMBER,
    (CASE WHEN src.MONTHS_OF_YEAR IS NULL THEN NULL 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.COUPON_QUALIFICATIONS src
    LEFT JOIN hub.COUPONS cou ON cou.COUPON_ID = src.COUPON_ID
    LEFT JOIN hub.COMPANIES co1 ON co1.COMPANY_ID = src.COMPANY_ID
    LEFT JOIN hub.CUSTOMERS cu1 ON cu1.CUSTOMER_ID = src.CUSTOMER_ID
    LEFT JOIN hub.ACTIVITIES ac1 ON ac1.ACTIVITY_ID = src.ACTIVITY_ID
    LEFT JOIN hub.PROGRAMS pr1 ON pr1.PROGRAM_ID = src.PROGRAM_ID
    LEFT JOIN hub.PACKAGES pa1 ON pa1.PACKAGE_ID = src.PACKAGE_ID
    LEFT JOIN hub.PACKAGE_CATEGORIES pc1 ON pc1.PACKAGE_CATEGORY_ID = src.PACKAGE_CATEGORY_ID
    LEFT JOIN hub.POS_PRODUCTS ppr ON ppr.POSPRODUCT_ID = src.POSPRODUCT_ID
    LEFT JOIN hub.USAGE_FEES uf1 ON uf1.USAGE_FEE_ID = src.USAGE_FEE_ID
    LEFT JOIN hub.GLACCOUNTS gl1 ON gl1.GLACCOUNT_ID = src.GLACCOUNT_ID
    LEFT JOIN hub.PRODUCT_DEPARTMENTS pd1 ON (pd1.PRODUCT_DEPARTMENT_ID = src.PRODUCT_DEPARTMENT_ID)
    LEFT JOIN hub.PROGRAM_TYPES pt1 ON (pt1.PROGRAM_TYPE_ID = src.PROGRAM_TYPE_ID)
    LEFT JOIN hub.SEASONS se1 ON (se1.SEASON_ID = src.SEASON_ID)
    LEFT JOIN hub.ACTIVITY_CATEGORIES acat ON (acat.ACTIVITY_CATEGORY_ID = src.CATEGORY_ID)
    LEFT JOIN hub.CUSTOMER_TYPES ct1 ON (ct1.CUSTOMER_TYPE_ID = src.CUSTOMER_TYPE_ID)
    LEFT JOIN hub.PRODUCT_CLASSES pc2 ON (pc2.PRODUCT_CLASS_ID = src.PRODUCT_CLASS_ID)
    LEFT JOIN hub.PRODUCT_SUBCLASSES psc1 ON (psc1.PRODUCT_SUBCLASS_ID = src.PRODUCT_SUBCLASS_ID);

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views