AR_SCHEDULED_DETAILS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

AR_SCHEDULED_DETAIL_ID

int

√

null

CUSTOMER_THIRD_PARTY_BILLING_LINKS.AR_SCHEDULED_DETAIL_ID

Defined in XML

The AR schedule detail ID identifying the payment plan charge

AR_SCHEDULED_HEADER_ID

int

√

null

AR_SCHEDULED_HEADERS.AR_SCHEDULED_HEADER_ID

Defined in XML

The AR schedule header ID identifying the payment plan

PAYER_CUSTOMER_ID

int

√

null

CUSTOMERS.CUSTOMER_ID

Defined in XML

The ID of the customer that is the owner of the payment plan

PAYER_CUSTOMER_NAME

varchar

511

√

null

The name of the customer that is the owner of the payment plan

PAYER_CUSTOMER_NAME_LAST_FIRST

varchar

512

√

null

The name of the customer that is the owner of the payment plan (last name, first name)

PAYER_COMPANY_ID

int

√

null

COMPANIES.COMPANY_ID

Defined in XML

The ID of the company that is the owner of the payment plan

PAYER_COMPANY_NAME

varchar

255

√

null

The name of the company that is the owner of the payment plan

TRANSACTION_CUSTOMER_ID

int

√

null

CUSTOMERS.CUSTOMER_ID

Defined in XML

The ID of the customer that the payment plan charge is for

TRANSACTION_CUSTOMER_NAME

varchar

511

√

null

The name of the customer that the payment plan charge is for

TRANSACTION_CUSTOMER_NAME_LAST_FIRST

varchar

512

√

null

The name of the customer that the payment plan charge is for (last name, first name)

TRANSACTION_COMPANY_ID

int

√

null

COMPANIES.COMPANY_ID

Defined in XML

The ID of the company that the payment plan charge is for

TRANSACTION_COMPANY_NAME

varchar

255

√

null

The name of the company that the payment plan charge is for

RECEIPT_HEADER_ID

int

√

null

The ID of the receipt that generated the payment plan charge

RECEIPT_DATE

date

√

null

The date of the receipt that generated the payment plan charge

RECEIPT_NUMBER

varchar

√

null

The number of the receipt that generated the payment plan charge

RECEIPT_DETAIL_ID

int

√

null

The ID of the receipt detail item that generated the payment plan charge

TRANSACTION_ID

int

√

null

The ID of the transaction that generated the payment plan charge

TRANSACTION_SITE_ID

int

√

null

SITES.SITE_ID

Defined in XML

The transaction site ID where the payment plan charge was generated

TRANSACTION_SITE_NAME

varchar

255

√

null

The transaction site name where the payment plan charge was generated

REVENUE_SITE_ID

int

√

null

SITES.SITE_ID

Defined in XML

The revenue site ID that the payment plan charge was generated for

REVENUE_SITE_NAME

varchar

255

√

null

The revenue site name that the payment plan charge was generated for

GLACCOUNT_ID

int

√

null

GLACCOUNTS.GLACCOUNT_ID

Defined in XML

The revenue GL account ID that the payment plan charge was generated for

GL_ACCOUNT_NAME

varchar

200

√

null

The revenue GL account name that the payment plan charge was generated for

GL_ACCOUNT_NUMBER

varchar

200

√

null

The revenue GL account number that the payment plan charge was generated for

FACILITY_ID

int

√

null

FACILITIES.FACILITY_ID

Defined in XML

The facility ID that the payment plan charge was generated for

FACILITY_NAME

varchar

255

√

null

The facility name that the payment plan charge was generated for

PROGRAM_ID

int

√

null

PROGRAMS.PROGRAM_ID

Defined in XML

The program ID that the payment plan charge was generated for

PROGRAM_NAME

varchar

255

√

null

The program name that the payment plan charge was generated for

PERMIT_ID

int

√

null

The ID of the permit that the payment plan charge was generated for

PERMIT_NUMBER

int

√

null

The permit number that the payment plan charge was generated for

MEMBERSHIP_ID

int

√

null

The ID of the membership that the payment plan charge was generated for

PACKAGE_ID

int

√

null

PACKAGES.PACKAGE_ID

Defined in XML

The package ID that the payment plan charge was generated for

PACKAGE_NAME

varchar

255

√

null

The package name that the payment plan charge was generated for

ACTIVITY_ID

int

√

null

ACTIVITIES.ACTIVITY_ID

Defined in XML

The activity ID that the payment plan charge was generated for

ACTIVITY_NAME

varchar

100

√

null

The activity name that the payment plan charge was generated for

ACTIVITY_NUMBER

varchar

√

null

The activity number that the payment plan charge was generated for

POSPRODUCT_ID

int

√

null

POS_PRODUCTS.POSPRODUCT_ID

Defined in XML

The POS product ID that the payment plan charge was generated for

PRODUCT_NAME

varchar

100

√

null

The POS product name that the payment plan charge was generated for

MODULE

varchar

√

null

The system module that the payment plan charge belongs to (Activities / Programs / Facilities / Memberships / POS / Other)

DESCRIPTION

varchar

200

√

null

The description of the payment plan charge

DATE_DUE

date

√

null

The date that the payment plan charge will be or was due on

AMOUNT_DUE

money

21,4

√

null

The amount that is owed whether due now or in the future

AGED_DUE_FUTURE

money

21,4

null

The amount that is due in the future

AGED_DUE_TODAY

money

21,4

null

The amount that is specifically due today

AGED_DUE_1_TO_30

money

21,4

null

The amount that has been due for 1 to 30 days

AGED_DUE_31_TO_60

money

21,4

null

The amount that has been due for 31 to 60 days

AGED_DUE_61_TO_90

money

21,4

null

The amount that has been due for 61 to 90 days

AGED_DUE_OVER_90

money

21,4

null

The amount that has been due for greater than 90 days

ACTIVE_HUB_KEY

int

null

The Hub record ID

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.AR_SCHEDULED_DETAILS
AS
SELECT
    src.AR_SCHEDULED_DETAIL_ID,
    src.AR_SCHEDULED_HEADER_ID,
    src.PAYER_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 PAYER_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 PAYER_CUSTOMER_NAME_LAST_FIRST,
    src.PAYER_COMPANY_ID,
    co1.COMPANY_NAME AS PAYER_COMPANY_NAME,
    src.TRANSACTION_CUSTOMER_ID,
    NULLIF(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 TRANSACTION_CUSTOMER_NAME,
    NULLIF(CONCAT(cu2.CUSTOMER_LAST_NAME, IIF(cu2.CUSTOMER_FIRST_NAME IS NOT NULL AND cu2.CUSTOMER_LAST_NAME IS NOT NULL, ', ', ''), cu2.CUSTOMER_FIRST_NAME), '') AS TRANSACTION_CUSTOMER_NAME_LAST_FIRST,
    src.TRANSACTION_COMPANY_ID,
    co2.COMPANY_NAME AS TRANSACTION_COMPANY_NAME,
    src.RECEIPT_HEADER_ID,
    src.RECEIPT_DATE,
    src.RECEIPT_NUMBER,
    src.RECEIPT_DETAIL_ID,
    src.TRANSACTION_ID,
    src.TRANSACTION_SITE_ID,
    si1.SITE_NAME AS TRANSACTION_SITE_NAME,
    src.REVENUE_SITE_ID,
    si2.SITE_NAME AS REVENUE_SITE_NAME,
    src.GLACCOUNT_ID,
    gl1.ACCOUNTNAME AS GL_ACCOUNT_NAME,
    gl1.ACCOUNTNUMBER AS GL_ACCOUNT_NUMBER,
    src.FACILITY_ID,
    fa1.FACILITY_NAME,
    src.PROGRAM_ID,
    pr1.PROGRAM_NAME,
    src.PERMIT_ID,
    pe1.PERMIT_NUMBER,
    src.MEMBERSHIP_ID,
    src.PACKAGE_ID,
    pa1.PACKAGE_NAME,
    src.ACTIVITY_ID,
    ac1.ACTIVITYNAME AS ACTIVITY_NAME,
    ac1.ACTIVITYNUMBER AS ACTIVITY_NUMBER,
    src.POSPRODUCT_ID,
    ppr.PRODUCT_NAME,
    src.MODULE,
    src.DESCRIPTION,
    src.DATE_DUE,
    src.AMOUNT_DUE,
    ISNULL(CASE WHEN DATEDIFF(DAY, src.DATE_DUE, CAST(GETDATE() AS DATE)) < 0 THEN src.AMOUNT_DUE ELSE 0 END, 0) AS AGED_DUE_FUTURE,
    ISNULL(CASE WHEN DATEDIFF(DAY, src.DATE_DUE, CAST(GETDATE() AS DATE)) = 0 THEN src.AMOUNT_DUE ELSE 0 END, 0) AS AGED_DUE_TODAY,
    ISNULL(CASE WHEN DATEDIFF(DAY, src.DATE_DUE, CAST(GETDATE() AS DATE)) BETWEEN 1 AND 30 THEN src.AMOUNT_DUE ELSE 0 END, 0) AS AGED_DUE_1_TO_30,
    ISNULL(CASE WHEN DATEDIFF(DAY, src.DATE_DUE, CAST(GETDATE() AS DATE)) BETWEEN 31 AND 60 THEN src.AMOUNT_DUE ELSE 0 END, 0) AS AGED_DUE_31_TO_60,
    ISNULL(CASE WHEN DATEDIFF(DAY, src.DATE_DUE, CAST(GETDATE() AS DATE)) BETWEEN 61 AND 90 THEN src.AMOUNT_DUE ELSE 0 END, 0) AS AGED_DUE_61_TO_90,
    ISNULL(CASE WHEN DATEDIFF(DAY, src.DATE_DUE, CAST(GETDATE() AS DATE)) > 90 THEN src.AMOUNT_DUE ELSE 0 END, 0) AS AGED_DUE_OVER_90,
    src.ACTIVE_HUB_KEY,
    src.LAST_SYNC_TIME,
    src.ACTIVE_HUB_PROCESS_ID,
    src.ORG_ID
FROM 
    hub.AR_SCHEDULED_DETAILS src
    LEFT JOIN hub.CUSTOMERS cu1 ON cu1.CUSTOMER_ID = src.PAYER_CUSTOMER_ID
    LEFT JOIN hub.COMPANIES co1 ON co1.COMPANY_ID = src.PAYER_COMPANY_ID
    LEFT JOIN hub.CUSTOMERS cu2 ON cu2.CUSTOMER_ID = src.TRANSACTION_CUSTOMER_ID
    LEFT JOIN hub.COMPANIES co2 ON co2.COMPANY_ID = src.TRANSACTION_COMPANY_ID
    LEFT JOIN hub.SITES si1 ON si1.SITE_ID = src.TRANSACTION_SITE_ID
    LEFT JOIN hub.SITES si2 ON si2.SITE_ID = src.REVENUE_SITE_ID
    LEFT JOIN hub.GLACCOUNTS gl1 ON gl1.GLACCOUNT_ID = src.GLACCOUNT_ID
    LEFT JOIN hub.FACILITIES fa1 ON fa1.FACILITY_ID = src.FACILITY_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.ACTIVITIES ac1 ON ac1.ACTIVITY_ID = src.ACTIVITY_ID
    LEFT JOIN hub.PERMITS pe1 ON pe1.PERMIT_ID = src.PERMIT_ID
    LEFT JOIN hub.POS_PRODUCTS ppr ON ppr.POSPRODUCT_ID = src.POSPRODUCT_ID;

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views