COUPON_BATCHES

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ACTIVE_HUB_KEY

int

null

The Hub record ID

COUPON_BATCH_ID

int

√

null

COUPON_BATCH_QUALIFICATIONS.COUPON_BATCH_ID	Defined in XML	R
COUPONS.COUPON_BATCH_ID	Defined in XML	R

Coupon batch ID

COUPON_BATCH_TITLE

varchar

100

√

null

Coupon batch title

COUPON_CODE_PREFIX

varchar

√

null

Coupon code prefix to be used when generating coupons

COUPON_CODE_SUFFIX

varchar

√

null

Coupon code suffix to be used when generating coupons

COUPON_CODE_MIN_NUMBER

int

√

null

Coupon code starting number to be used when generating coupons

COUPON_CODE_MAX_NUMBER

int

√

null

Coupon code ending number to be used when generating coupons

COUPON_TITLE

varchar

100

√

null

Coupon title for generated coupons

COUPON_DESCRIPTION

varchar

8000

√

null

Coupon description of generated coupons

COUPON_TYPE

varchar

√

null

Coupon type for generated coupons

DISCOUNT_TYPE

varchar

√

null

Discount type (amount / discount)

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

COUPON_CATEGORY_ID

int

√

null

REPORT_DEFINITIONS.REPORT_DEFINITION_ID

Defined in XML

Coupon category ID

COUPON_CATEGORY_NAME

varchar

100

√

null

Coupon category

DISCOUNT_AMOUNT

money

21,4

√

null

Coupon discount amount

DISCOUNT_PERCENT

money

21,4

√

null

Coupon discount percentage

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 coupons will be configured for use on specific days of the week

WEEKS_OF_MONTH

varchar

√

null

Whether the coupons will be configured for use on specific weeks of the month

MONTHS_OF_YEAR

varchar

√

null

Whether the coupons will be configured for use on specific months of the year

MAX_NUMBER_OF_USES

int

√

null

The total number of times that each coupon may be used

CUSTOMER_LIST_ID

int

√

null

COUPON_CATEGORIES.COUPON_CATEGORY_ID

Defined in XML

The ID of the custom list identifying the customers to be assigned coupons

CUSTOMER_LIST_NAME

varchar

√

null

The name of the custom list identifying the customers to be assigned coupons

NUMBER_PER_CUSTOMER

int

√

null

Number of coupons to be assigned to each customer

MAX_NUMBER_OF_USES_PER_CUSTOMER

int

√

null

Maximum uses per customer for each coupon

NOTIFY_CUSTOMER

varchar

√

null

Whether a notification should be emailed to each customer who gets coupons assigned to them (Yes/No)

DAYS_SUNDAY

varchar

null

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

DAYS_MONDAY

varchar

null

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

DAYS_TUESDAY

varchar

null

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

DAYS_WEDNESDAY

varchar

null

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

DAYS_THURSDAY

varchar

null

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

DAYS_FRIDAY

varchar

null

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

DAYS_SATURDAY

varchar

null

Whether the coupons will be configured for use on Saturdays (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 (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 (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 (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 (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 (Yes/No)

MONTHS_JANUARY

varchar

null

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

MONTHS_FEBRUARY

varchar

null

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

MONTHS_MARCH

varchar

null

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

MONTHS_APRIL

varchar

null

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

MONTHS_MAY

varchar

null

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

MONTHS_JUNE

varchar

null

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

MONTHS_JULY

varchar

null

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

MONTHS_AUGUST

varchar

null

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

MONTHS_SEPTEMBER

varchar

null

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

MONTHS_OCTOBER

varchar

null

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

MONTHS_NOVEMBER

varchar

null

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

MONTHS_DECEMBER

varchar

null

Whether the coupons will be configured for use 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.COUPON_BATCHES
AS
SELECT
    src.ACTIVE_HUB_KEY,
    src.COUPON_BATCH_ID,
    src.COUPON_BATCH_TITLE,
    src.COUPON_CODE_PREFIX,
    src.COUPON_CODE_SUFFIX,
    src.COUPON_CODE_MIN_NUMBER,
    src.COUPON_CODE_MAX_NUMBER,
    src.COUPON_TITLE,
    src.COUPON_DESCRIPTION,
    src.COUPON_TYPE,
    src.DISCOUNT_TYPE,
    src.CHARGE_ID,
    ch1.CHARGE_NAME,
    src.COUPON_CATEGORY_ID,
    cc1.COUPON_CATEGORY_NAME,
    src.DISCOUNT_AMOUNT,
    src.DISCOUNT_PERCENT,
    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.CUSTOMER_LIST_ID,
    rd1.REPORT_TITLE AS CUSTOMER_LIST_NAME,
    src.NUMBER_PER_CUSTOMER,
    src.MAX_NUMBER_OF_USES_PER_CUSTOMER,
    src.NOTIFY_CUSTOMER,
    (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.COUPON_BATCHES src
    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)
    LEFT JOIN hub.REPORT_DEFINITIONS rd1 ON (rd1.REPORT_DEFINITION_ID = src.CUSTOMER_LIST_ID);

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views