PROGRAMS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

ACTIVE_HUB_KEY

int

null

The Hub record ID

ACTIVITY_CATEGORY_ID

Unknown

null

ACTIVITY_CATEGORIES.ACTIVITY_CATEGORY_ID

Defined in XML

PROGRAM_ID

int

√

null

AR_SCHEDULED_DETAILS.PROGRAM_ID	Defined in XML	R
CHARGE_QUALIFICATION_LINKS.PROGRAM_ID	Defined in XML	R
COUPON_BATCH_QUALIFICATIONS.PROGRAM_ID	Defined in XML	R
COUPON_QUALIFICATIONS.PROGRAM_ID	Defined in XML	R
CUSTOM_QUESTION_ANSWERS.PROGRAM_ID	Defined in XML	R
CUSTOMER_SCHOLARSHIP_QUALIFICATIONS.PROGRAM_ID	Defined in XML	R
DEFERRED_REVENUE_PROJECTIONS.PROGRAM_ID	Defined in XML	R
INTEREST_LISTS.PROGRAM_ID	Defined in XML	R
PREREQUISITES.PREREQUISITE_PROGRAM_ID	Defined in XML	R
PREREQUISITES.PROGRAM_ID	Defined in XML	R
PROGRAM_FEES.PROGRAM_ID	Defined in XML	R
PROGRAM_REGISTRATIONS.PROGRAM_ID	Defined in XML	R
PROGRAM_SESSIONS.PROGRAM_ID	Defined in XML	R
TRANSACTIONS.PROGRAM_ID	Defined in XML	R
WISHLISTS.PROGRAM_ID	Defined in XML	R

Program ID

PROGRAM_NAME

varchar

255

√

null

Program name. All programs whose names fully or partially match the specified name are returned.

PROGRAM_TYPE_ID

int

√

null

PROGRAM_TYPES.PROGRAM_TYPE_ID

Defined in XML

The program type ID of the program.

PROGRAM_TYPE

varchar

√

null

The program type of the program.

CATEGORY_ID

int

√

null

The category ID of the program.

CATEGORY_NAME

varchar

√

null

The category of the program.

SUB_CATEGORY_ID

int

√

null

ACTIVITY_SUB_CATEGORIES.ACTIVITY_SUB_CATEGORY_ID

Defined in XML

The other category ID of the program.

SUBCATEGORY_NAME

varchar

√

null

The other category of the program.

ACTIVITY_DEPARTMENT_ID

int

√

null

ACTIVITY_DEPARTMENTS.ACTIVITY_DEPARTMENT_ID

Defined in XML

The department ID of the program.

DEPARTMENT_NAME

varchar

√

null

The department of the program.

SEASON_ID

int

√

null

SEASONS.SEASON_ID

Defined in XML

The parent season ID of the program.

SEASON_NAME

varchar

√

null

The parent season of the program.

CHILD_SEASON_ID

int

√

null

CHILD_SEASONS.CHILD_SEASON_ID

Defined in XML

The child season ID of the program.

CHILD_SEASON_NAME

varchar

√

null

The child season of the program.

AGESMIN

int

√

null

Programs whose age ranges are fully or partially within the specified range are returned.

AGESMINMONTH

int

√

null

Minimum participant age (month) of the program.

AGESMAX

int

√

null

Programs whose age ranges are fully or partially within the specified range are returned.

AGESMAXMONTH

int

√

null

Maximum participant age (month) of the program.

GENDER_INDEX

int

√

null

GENDERS.GENDER_INDEX

Defined in XML

The gender ID of the program

GENDER

varchar

100

√

null

The gender restriction of the program

CATALOGNUMBER

varchar

255

√

null

Program number. All programs whose program numbers fully or partially match the specified program number are returned.

AGECALCDATE

datetime

16,3

√

null

The start date for calculating participant age.

SITE_ID

int

√

null

SITES.SITE_ID

Defined in XML

The site IDs assigned to the program. You can specify up to 5 sites, with each site separated by a comma.

SITE_NAME

varchar

255

√

null

The site Names assigned to the program. You can specify up to 5 sites, with each site separated by a comma.

MODIFIED_DATE

datetime

16,3

√

null

The date and time the program was last modified

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

RETIRED

varchar

√

null

Whether the program has been retired (Yes/No)

PROGRAMSTATUS_ID

int

√

null

The status ID of the program (0 = Open, 1 = Closed, 2 = Tentative, 3 = On Hold, 4 = Retired).

PROGRAM_STATUS_DESC

varchar

255

√

null

The current status of the program name.

MODIFIED_DATE_ONLY

date

√

null

The date the program was last modified (date only)

TOTAL_ENROLLMENTS

int

√

null

The total number of session time slot enrollments for the program

TOTAL_ENROLLED_PARTICIPANTS

int

√

null

The total number of participants enrolled for any session in the program

TOTAL_WAITLISTS

int

√

null

The total number of session time slots waitlists for the program

TOTAL_WAITLISTED_PARTICIPANTS

int

√

null

The total number of participants waitlisted for any session in the program

SUPERVISOR_ID

int

√

null

SUPERVISORS.SUPERVISOR_ID

Defined in XML

ID OF THE PROGRAM SUPERVISOR

SUPERVISOR_NAME

varchar

511

null

NAME OF THE PROGRAM SUPERVISOR. FORMAT:

ORG_ID

int

√

null

Your organization’s unique identifier in the Active Net system

MAX_FILLRATE

float

√

null

The maximum Fill Rate of all session dates within the program

AVG_FILLRATE

float

√

null

The average Fill Rate of all session dates within the program

DESCRIPTION

varchar

8000

√

null

Description of the program

HIDE_ON_INTERNET

varchar

√

null

Whether this program should be hidden from online display (Yes/No)

HAS_WAITLIST

varchar

√

null

Whether this program has a waitlist (Yes/No)

ALLOW_INDIVIDUAL_SESSION_SELECTION

varchar

√

null

Whether this program allows individual sessions to be selected during registration (Yes/No)

ONLINE_SESSION_SELECTION

varchar

√

null

How sessions for this program should be displayed online (Calendar / Session List)

RESERVATION_UNIT

varchar

√

null

The time units that should be selectable during registration (Same as Billing Unit / Daily / Weekly / Monthly / No Choices)

DEFAULT_PAYMENT_CYCLE

varchar

√

null

How payment plans should be billed for this program (No Automatic Plans / Weekly / Every Other Week / Twice a Month / Monthly / Quarterly / Every Four Weeks / Per Session)

DUE_DATE_LEAD_TIME_DAYS

int

√

null

The number of days prior to each payment cycle that payment should be due

UP_FRONT_CYCLES_TO_COLLECT

int

√

null

The number of payment cycles that should be immediately due during registration

AUTO_CHARGE_CREDIT_CARD

varchar

√

null

Whether the saved credit card should be automatically charged when payments become due

IN_PERSON_REGISTRATION_DATE_TIME

datetime

16,3

√

null

The date and time that registration opens for in-person enrollments

IN_PERSON_REGISTRATION_DATE_ONLY

date

√

null

The date that registration opens for in-person enrollments

NON_RESIDENT_IN_PERSON_REGISTRATION_DATE_TIME

datetime

16,3

√

null

The date and time that registration opens for in-person enrollments for non-residents

NON_RESIDENT_IN_PERSON_REGISTRATION_DATE_ONLY

date

√

null

The date that registration opens for in-person enrollments for non-residents

MEMBER_IN_PERSON_REGISTRATION_DATE_TIME

datetime

16,3

√

null

The date and time that registration opens for in-person enrollments for members

MEMBER_IN_PERSON_REGISTRATION_DATE_ONLY

date

√

null

The date that registration opens for in-person enrollments for members

IN_PERSON_REGISTRATION_END_DATE_TIME

datetime

16,3

√

null

The date and time that registration ends for in-person enrollments

IN_PERSON_REGISTRATION_END_DATE_ONLY

date

√

null

The date that registration ends for in-person enrollments

INTERNET_DISPLAY_DATE_TIME

datetime

16,3

√

null

The date and time that this program should first appear online

INTERNET_DISPLAY_DATE_ONLY

date

√

null

The date that this program should first appear online

INTERNET_REGISTRATION_DATE_TIME

datetime

16,3

√

null

The date and time that registration opens for online enrollments

INTERNET_REGISTRATION_DATE_ONLY

date

√

null

The date that registration opens for online enrollments

NON_RESIDENT_INTERNET_REGISTRATION_DATE_TIME

datetime

16,3

√

null

The date and time that registration opens for online enrollments for non-residents

NON_RESIDENT_INTERNET_REGISTRATION_DATE_ONLY

date

√

null

The date that registration opens for online enrollments for non-residents

MEMBER_INTERNET_REGISTRATION_DATE_TIME

datetime

16,3

√

null

The date and time that registration opens for online enrollments for members

MEMBER_INTERNET_REGISTRATION_DATE_ONLY

date

√

null

The date that registration opens for online enrollments for members

INTERNET_REGISTRATION_END_DATE_TIME

datetime

16,3

√

null

The date and time that registration ends for online enrollments

INTERNET_REGISTRATION_END_DATE_ONLY

date

√

null

The date that registration ends for online enrollments

TAX_RECEIPT_ELIGIBLE

varchar

√

null

Whether this program is eligible for tax receipts (Yes/No)

SHOW_PRICE_INFO_ONLINE

varchar

√

null

Whether price info for this program should be displayed online (Yes/No)

SHOW_DISCOUNT_ONLINE

varchar

√

null

Whether discount info for this program should be displayed online (Yes/No)

SHOW_SCHOLARSHIP_ONLINE

varchar

√

null

Whether scholarship info for this program should be displayed online (Yes/No)

ALLOW_THIRD_PARTY_BILLING

varchar

√

null

Whether third-party billing should be available for this program (Yes/No)

REQUIRE_AUTHORIZED_PICKUPS

varchar

√

null

Whether this program requires entry of authorized pickups during enrollment (Yes/No)

FIRST_SESSION_BEGIN_DATE_TIME

datetime

16,3

√

null

The starting date and time of the earliest session for this program

FIRST_SESSION_BEGIN_DATE_ONLY

date

√

null

The starting date of the earliest session for this program

ONLINE_URL

varchar

255

√

null

The online landing page for this program

source_id

int

√

null

The ID value from the Active Net system

CANCELLATION_REASON_ID

int

√

null

DC Program cancellation reason ID, from Reasons table.

CANCELLATION_REASON_TEXT

varchar

4000

√

null

DC Program cancellation reason text, from Reasons table.

CANCELLATION_REASON_DATE

datetime

16,3

√

null

DC Program cancellation date.

Relationships

Close relationships within degrees of separation

One
Two degrees

View Definition

CREATE VIEW dbo.PROGRAMS
AS
SELECT
    src.ACTIVE_HUB_KEY,
    src.PROGRAM_ID,
    src.PROGRAM_NAME,
    src.PROGRAM_TYPE_ID,
    pt1.PROGRAM_TYPE_NAME AS PROGRAM_TYPE,
    src.CATEGORY_ID,
    acat.ACTIVITY_CATEGORY_NAME AS CATEGORY_NAME,
    src.SUB_CATEGORY_ID,
    ascat.ACTIVITY_SUB_CATEGORY_NAME AS SUBCATEGORY_NAME,
    src.ACTIVITY_DEPARTMENT_ID,
    adep.ACTIVITY_DEPARTMENT_NAME AS DEPARTMENT_NAME,
    src.SEASON_ID,
    se1.SEASON_NAME,
    src.CHILD_SEASON_ID,
    cs1.CHILD_SEASON_NAME,
    src.AGESMIN,
    src.AGESMINMONTH,
    src.AGESMAX,
    src.AGESMAXMONTH,
    src.GENDER_INDEX,
    ge1.GENDER_NAME AS GENDER,
    src.CATALOGNUMBER,
    src.AGECALCDATE,
    src.SITE_ID,
    si1.SITE_NAME,
    src.MODIFIED_DATE,
    src.LAST_SYNC_TIME,
    src.ACTIVE_HUB_PROCESS_ID,
    src.RETIRED,
    src.PROGRAMSTATUS_ID,
    src.PROGRAM_STATUS_DESC,
    src.MODIFIED_DATE_ONLY,
    src.TOTAL_ENROLLMENTS,
    src.TOTAL_ENROLLED_PARTICIPANTS,
    src.TOTAL_WAITLISTS,
    src.TOTAL_WAITLISTED_PARTICIPANTS,
    src.SUPERVISOR_ID,
    CONCAT(cu2.CUSTOMER_FIRST_NAME, IIF(cu2.CUSTOMER_FIRST_NAME IS NOT NULL AND cu2.CUSTOMER_LAST_NAME IS NOT NULL, ' ', ''), cu2.CUSTOMER_LAST_NAME) AS SUPERVISOR_NAME,
    src.ORG_ID,
    src.MAX_FILLRATE,
    src.AVG_FILLRATE,
    src.DESCRIPTION,
    src.HIDE_ON_INTERNET,
    src.HAS_WAITLIST,
    src.ALLOW_INDIVIDUAL_SESSION_SELECTION,
    src.ONLINE_SESSION_SELECTION,
    src.RESERVATION_UNIT,
    src.DEFAULT_PAYMENT_CYCLE,
    src.DUE_DATE_LEAD_TIME_DAYS,
    src.UP_FRONT_CYCLES_TO_COLLECT,
    src.AUTO_CHARGE_CREDIT_CARD,
    src.IN_PERSON_REGISTRATION_DATE_TIME,
    CAST(src.IN_PERSON_REGISTRATION_DATE_TIME AS DATE) AS IN_PERSON_REGISTRATION_DATE_ONLY,
    src.NON_RESIDENT_IN_PERSON_REGISTRATION_DATE_TIME,
    CAST(src.NON_RESIDENT_IN_PERSON_REGISTRATION_DATE_TIME AS DATE) AS NON_RESIDENT_IN_PERSON_REGISTRATION_DATE_ONLY,
    src.MEMBER_IN_PERSON_REGISTRATION_DATE_TIME,
    CAST(src.MEMBER_IN_PERSON_REGISTRATION_DATE_TIME AS DATE) AS MEMBER_IN_PERSON_REGISTRATION_DATE_ONLY,
    src.IN_PERSON_REGISTRATION_END_DATE_TIME,
    CAST(src.IN_PERSON_REGISTRATION_END_DATE_TIME AS DATE) AS IN_PERSON_REGISTRATION_END_DATE_ONLY,
    src.INTERNET_DISPLAY_DATE_TIME,
    CAST(src.INTERNET_DISPLAY_DATE_TIME AS DATE) AS INTERNET_DISPLAY_DATE_ONLY,
    src.INTERNET_REGISTRATION_DATE_TIME,
    CAST(src.INTERNET_REGISTRATION_DATE_TIME AS DATE) AS INTERNET_REGISTRATION_DATE_ONLY,
    src.NON_RESIDENT_INTERNET_REGISTRATION_DATE_TIME,
    CAST(src.NON_RESIDENT_INTERNET_REGISTRATION_DATE_TIME AS DATE) AS NON_RESIDENT_INTERNET_REGISTRATION_DATE_ONLY,
    src.MEMBER_INTERNET_REGISTRATION_DATE_TIME,
    CAST(src.MEMBER_INTERNET_REGISTRATION_DATE_TIME AS DATE) AS MEMBER_INTERNET_REGISTRATION_DATE_ONLY,
    src.INTERNET_REGISTRATION_END_DATE_TIME,
    CAST(src.INTERNET_REGISTRATION_END_DATE_TIME AS DATE) AS INTERNET_REGISTRATION_END_DATE_ONLY,
    src.TAX_RECEIPT_ELIGIBLE,
    src.SHOW_PRICE_INFO_ONLINE,
    src.SHOW_DISCOUNT_ONLINE,
    src.SHOW_SCHOLARSHIP_ONLINE,
    src.ALLOW_THIRD_PARTY_BILLING,
    src.REQUIRE_AUTHORIZED_PICKUPS,
    src.FIRST_SESSION_BEGIN_DATE_TIME,
    CAST(src.FIRST_SESSION_BEGIN_DATE_TIME AS DATE) AS FIRST_SESSION_BEGIN_DATE_ONLY,
    src.ONLINE_URL,
    src.PROGRAM_ID AS source_id,
	src.CANCELLATION_REASON_ID,
	src.CANCELLATION_REASON_TEXT,
	src.CANCELLATION_REASON_DATE	
FROM
    hub.PROGRAMS src
    LEFT JOIN hub.SITES si1 ON (si1.ORG_ID = src.ORG_ID AND si1.SITE_ID = src.SITE_ID)
    LEFT JOIN hub.SUPERVISORS su1 ON (su1.ORG_ID = src.ORG_ID AND su1.SUPERVISOR_ID = src.SUPERVISOR_ID)
    LEFT JOIN hub.CUSTOMERS cu2 ON (cu2.ORG_ID = src.ORG_ID AND cu2.CUSTOMER_ID = su1.CUSTOMER_ID)
    LEFT JOIN hub.PROGRAM_TYPES pt1 ON (pt1.ORG_ID = src.ORG_ID AND pt1.PROGRAM_TYPE_ID = src.PROGRAM_TYPE_ID)
    LEFT JOIN hub.SEASONS se1 ON (se1.ORG_ID = src.ORG_ID AND se1.SEASON_ID = src.SEASON_ID)
    LEFT JOIN hub.CHILD_SEASONS cs1 ON (cs1.ORG_ID = src.ORG_ID AND cs1.CHILD_SEASON_ID = src.CHILD_SEASON_ID)
    LEFT JOIN hub.ACTIVITY_CATEGORIES acat ON (acat.ORG_ID = src.ORG_ID AND acat.ACTIVITY_CATEGORY_ID = src.CATEGORY_ID)
    LEFT JOIN hub.ACTIVITY_SUB_CATEGORIES ascat ON (ascat.ORG_ID = src.ORG_ID AND ascat.ACTIVITY_SUB_CATEGORY_ID = src.SUB_CATEGORY_ID)
    LEFT JOIN hub.ACTIVITY_DEPARTMENTS adep ON (adep.ORG_ID = src.ORG_ID AND adep.ACTIVITY_DEPARTMENT_ID = src.ACTIVITY_DEPARTMENT_ID)
    LEFT JOIN hub.GENDERS ge1 ON (ge1.ORG_ID = src.ORG_ID AND ge1.GENDER_INDEX = src.GENDER_INDEX);
(CONVERT([date],[MODIFIED_DATE]))

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views