RECEIPT_PAYMENTS

Columns

Column

Type

Size

Nulls

Auto

Default

Children

Parents

Comments

RECEIPT_PAYMENT_ID

int

√

null

ACCOUNT_CREDIT_HISTORY.RECEIPT_PAYMENT_ID	Defined in XML	R
PAYMENT_CHARGEBACKS.RECEIPT_PAYMENT_ID	Defined in XML	R
RECEIPT_DETAIL_PAYMENTS.RECEIPT_PAYMENT_ID	Defined in XML	R
RECEIPT_GL_POSTINGS.RECEIPT_PAYMENT_ID	Defined in XML	R
RECEIPT_PAYMENTS.REPLACED_RECEIPT_PAYMENT_ID	Defined in XML	R
RECEIPT_PAYMENTS.REVERSED_RECEIPT_PAYMENT_ID	Defined in XML	R

Receipt payment ID

RECEIPT_HEADER_ID

int

√

null

RECEIPT_HEADERS.RECEIPT_HEADER_ID

Defined in XML

ID of the receipt header that the payment belongs to

RECEIPT_NUMBER

nvarchar

8000

√

null

The receipt number that the payment belongs to

SITE_ID

int

√

null

SITES.SITE_ID

Defined in XML

ID of the site where the payment was created

SITE_NAME

varchar

255

√

null

Name of the site where the payment was created

WORKSTATION_ID

int

√

null

WORKSTATIONS.WORKSTATION_ID

Defined in XML

ID of the workstation where the payment was created

WORKSTATION_NAME

varchar

√

null

Name of the workstation where the payment was created

SYSTEMUSER_ID

int

√

null

SYSTEM_USERS.SYSTEMUSER_ID

Defined in XML

ID of the user who created the payment

SYSTEM_USER_NAME

varchar

511

null

Name of the user who created the payment

CASH_SUMMARY_SHEET_ID

int

√

null

ID of the cash summary sheet that was active on the workstation when the payment was created

CREATION_DATE_TIME

datetime

16,3

√

null

Date and time that the payment was created

CREATION_DATE_ONLY

date

√

null

Date that the payment was created (date only)

CUSTOMER_ID

int

√

null

CUSTOMERS.CUSTOMER_ID

Defined in XML

ID of the customer who made the payment

CUSTOMER_NAME

varchar

511

null

Name of the customer who made the payment

COMPANY_ID

int

√

null

COMPANIES.COMPANY_ID

Defined in XML

ID of the company who made the payment

COMPANY_NAME

varchar

255

√

null

Name of the company who made the payment

COMPANY_AUTHORIZED_AGENT_ID

int

√

null

The ID of the company authorized agent who made the payment

COMPANY_AUTHORIZED_AGENT_NAME

varchar

511

√

null

The name of the company authorized agent who made the payment

PAYMENT_AMOUNT

money

21,4

√

null

The amount of the payment processed or of the payment plan created

PAYMENT_TYPE

varchar

√

null

The type of payment (Cash / Cash Refund / Check / Check Refund / Credit Card / Credit Card Refund / Credit Memo / Debit Card / Debit Card Refund / ECP / ECP Refund / Financial Aid / Financial Aid Payment Plan / Gift Card / Gift Card Refund / Journal Entry / Manual Deduction / No Payment / Non-Monetary / Payment From Account / Payment Plan / Refund To Account)

CARD_TYPE_ID

int

√

null

CARD_TYPES.CARD_TYPE_ID

Defined in XML

ID of the credit card type

CARD_TYPE

varchar

√

null

Name of the credit card type

CARD_NUMBER

varchar

100

√

null

Masked card number

CARD_EXPIRATION

varchar

√

null

Credit card expiration date (mm/yyyy)

SAVED_CREDIT_CARD_ID

int

√

null

SAVED_CREDIT_CARDS.SAVED_CREDIT_CARD_ID

Defined in XML

ID of the saved credit card used for the payment or refund

DEVICE_INPUT_TYPE

varchar

√

null

Type of card swiped or tapped on a physical device (Credit Card / Debit Card)

BANK_ROUTING_NUMBER

varchar

100

√

null

Bank routing number for electronic check processing

BANK_ACCOUNT_TYPE

varchar

√

null

Bank account type for electronic check processing

BANK_ACCOUNT_NUMBER

varchar

√

null

Bank account number for electronic check processing

ECP_STATUS

varchar

√

null

The current electronic check processing status of the payment

AUTHORIZATION_NUMBER

varchar

200

√

null

The authorization number received for the payment

CLIENT_TRANSACTION_ID

varchar

√

null

The client transaction ID generated by the payment authorization process

IS_MANUAL_AUTHORIZATION

varchar

null

Whether the payment was manually authorized (Yes/No)

MANUAL_AUTHORIZATION_USER_ID

int

√

null

The ID of the user who manually authorized the payment

MANUAL_AUTHORIZATION_USER_NAME

varchar

511

null

The name of the user who manually authorized the payment

CASH_CHANGE_AMOUNT

money

21,4

√

null

The amount of change given for the payment

CUSTOM_PAYMENT_TYPE_ID

int

√

null

CUSTOM_PAYMENT_TYPES.CUSTOM_PAYMENT_TYPE_ID

Defined in XML

The ID of the custom payment type selected for the payment

CUSTOM_PAYMENT_TYPE_NAME

varchar

100

√

null

The name of the custom payment type selected for the payment

CHECK_NUMBER

varchar

100

√

null

Check number entered

GIFT_CARD_ID

int

√

null

GIFT_CARDS.GIFT_CARD_ID

Defined in XML

ID of the gift card for the payment or refund

PAYMENT_CANCELLED

varchar

√

null

Whether the payment has been cancelled (Yes/No)

CANCELLATION_REASON

varchar

200

√

null

The reason for the payment cancellation

REVERSED_RECEIPT_PAYMENT_ID

int

√

null

RECEIPT_PAYMENTS.RECEIPT_PAYMENT_ID

Defined in XML

ID of the receipt payment that is a reversal of this payment

REPLACED_RECEIPT_PAYMENT_ID

int

√

null

RECEIPT_PAYMENTS.RECEIPT_PAYMENT_ID

Defined in XML

ID of the receipt payment that this payment replaces

RECEIPT_DETAIL_ID

int

√

null

ID of an associated receipt detail record if applicable

PAYMENT_GL_ACCOUNT_ID

int

√

null

GLACCOUNTS.GLACCOUNT_ID

Defined in XML

ID of the GL account posted to, for specific payment types

PAYMENT_GL_ACCOUNT_NAME

varchar

200

√

null

Name of the GL account posted to, for specific payment types

PAYMENT_GL_ACCOUNT_NUMBER

varchar

200

√

null

GL account number posted to, for specific payment types

CREDIT_CARD_FEE_SURCHARGE_AMOUNT

money

21,4

√

null

Amount of credit card surcharge fee for this payment

CONVENIENCE_FEE_AMOUNT

money

21,4

√

null

Amount of convenience fee for this payment

GIFT_CERTIFICATE_ID

int

√

null

ID of the gift certificate for the payment or refund

VOIDED

varchar

null

Whether the receipt header has been voided (Yes/No)

SUBSIDY_USAGE_ID

int

√

null

Subsidy usage id is the reference of subsidy table.

SUBSIDY_USAGE_TRANSACTION_ID

int

√

null

Subsidy usage transaction id is the reference of the transactions which is paid by subsidy.

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

source_id

int

√

null

The ID value from the Active Net system

Relationships

Close relationships within degrees of separation

One
Two degrees

View Definition

CREATE VIEW dbo.RECEIPT_PAYMENTS
AS
SELECT
    src.RECEIPT_PAYMENT_ID,
    src.RECEIPT_HEADER_ID,
    FORMAT(rh1.RECEIPT_NUMBER, '0.000') AS RECEIPT_NUMBER,
    src.SITE_ID,
    si1.SITE_NAME,
    src.WORKSTATION_ID,
    ws1.WORKSTATION_NAME,
    src.SYSTEMUSER_ID,
    CONCAT(su1.USER_FIRST_NAME, IIF(su1.USER_FIRST_NAME IS NOT NULL AND su1.USER_LAST_NAME IS NOT NULL, ' ', ''), su1.USER_LAST_NAME) AS SYSTEM_USER_NAME,
    src.CASH_SUMMARY_SHEET_ID,
    src.CREATION_DATE_TIME,
    CAST(src.CREATION_DATE_TIME AS DATE) AS CREATION_DATE_ONLY,
    src.CUSTOMER_ID,
    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,
    src.COMPANY_ID,
    co1.COMPANY_NAME,
    src.COMPANY_AUTHORIZED_AGENT_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 COMPANY_AUTHORIZED_AGENT_NAME,
    src.PAYMENT_AMOUNT,
    src.PAYMENT_TYPE,
    src.CARD_TYPE_ID,
    ct1.CARD_TYPE_NAME AS CARD_TYPE,
    src.CARD_NUMBER,
    src.CARD_EXPIRATION,
    src.SAVED_CREDIT_CARD_ID,
    src.DEVICE_INPUT_TYPE,
    src.BANK_ROUTING_NUMBER,
    src.BANK_ACCOUNT_TYPE,
    src.BANK_ACCOUNT_NUMBER,
    src.ECP_STATUS,
    src.AUTHORIZATION_NUMBER,
    src.CLIENT_TRANSACTION_ID,
    IIF(src.MANUAL_AUTHORIZATION_USER_ID IS NULL, 'No', 'Yes') AS IS_MANUAL_AUTHORIZATION,
    src.MANUAL_AUTHORIZATION_USER_ID,
    CONCAT(su2.USER_FIRST_NAME, IIF(su2.USER_FIRST_NAME IS NOT NULL AND su2.USER_LAST_NAME IS NOT NULL, ' ', ''), su2.USER_LAST_NAME) AS MANUAL_AUTHORIZATION_USER_NAME,
    src.CASH_CHANGE_AMOUNT,
    src.CUSTOM_PAYMENT_TYPE_ID,
    cpt.CUSTOM_PAYMENT_TYPE_NAME,
    src.CHECK_NUMBER,
    src.GIFT_CARD_ID,
    src.PAYMENT_CANCELLED,
    src.CANCELLATION_REASON,
    src.REVERSED_RECEIPT_PAYMENT_ID,
    src.REPLACED_RECEIPT_PAYMENT_ID,
    src.RECEIPT_DETAIL_ID,
    src.PAYMENT_GL_ACCOUNT_ID,
    gl1.ACCOUNTNAME AS PAYMENT_GL_ACCOUNT_NAME,
    gl1.ACCOUNTNUMBER AS PAYMENT_GL_ACCOUNT_NUMBER,
    src.CREDIT_CARD_FEE_SURCHARGE_AMOUNT,
    src.CONVENIENCE_FEE_AMOUNT,
    src.GIFT_CARD_ID AS GIFT_CERTIFICATE_ID,
    'No' AS VOIDED,
	src.SUBSIDY_USAGE_ID,
	src.SUBSIDY_USAGE_TRANSACTION_ID,
    src.ACTIVE_HUB_KEY,
    src.LAST_SYNC_TIME,
    src.ACTIVE_HUB_PROCESS_ID,
    src.ORG_ID,
    src.RECEIPT_PAYMENT_ID AS source_id 
FROM 
    hub.RECEIPT_PAYMENTS src
    LEFT JOIN hub.RECEIPT_HEADERS rh1 ON (rh1.ORG_ID = src.ORG_ID AND rh1.RECEIPT_HEADER_ID = src.RECEIPT_HEADER_ID)
    LEFT JOIN hub.SITES si1 ON (si1.ORG_ID = src.ORG_ID AND si1.SITE_ID = src.SITE_ID)
    LEFT JOIN hub.SYSTEM_USERS su1 ON (su1.ORG_ID = src.ORG_ID AND su1.SYSTEMUSER_ID = src.SYSTEMUSER_ID)
    LEFT JOIN hub.CUSTOMERS cu1 ON (cu1.ORG_ID = src.ORG_ID AND cu1.CUSTOMER_ID = src.CUSTOMER_ID)
    LEFT JOIN hub.COMPANIES co1 ON (co1.ORG_ID = src.ORG_ID AND co1.COMPANY_ID = src.COMPANY_ID)
    LEFT JOIN hub.GLACCOUNTS gl1 ON (gl1.ORG_ID = src.ORG_ID AND gl1.GLACCOUNT_ID = src.PAYMENT_GL_ACCOUNT_ID)
    LEFT JOIN hub.WORKSTATIONS ws1 ON (ws1.ORG_ID = src.ORG_ID AND ws1.WORKSTATION_ID = src.WORKSTATION_ID)
    LEFT JOIN hub.SYSTEM_USERS su2 ON (su2.ORG_ID = src.ORG_ID AND su2.SYSTEMUSER_ID = src.MANUAL_AUTHORIZATION_USER_ID)
    LEFT JOIN hub.COMPANY_AUTHORIZED_AGENTS caa ON (caa.ORG_ID = src.ORG_ID AND caa.COMPANY_AUTHORIZED_AGENT_ID = src.COMPANY_AUTHORIZED_AGENT_ID)
    LEFT JOIN hub.CUSTOMERS cu2 ON (cu2.ORG_ID = src.ORG_ID AND cu2.CUSTOMER_ID = caa.CUSTOMER_ID)
    LEFT JOIN hub.CARD_TYPES ct1 ON (ct1.ORG_ID = src.ORG_ID AND ct1.CARD_TYPE_ID = src.CARD_TYPE_ID)
    LEFT JOIN hub.CUSTOM_PAYMENT_TYPES cpt ON (cpt.ORG_ID = src.ORG_ID AND cpt.CUSTOM_PAYMENT_TYPE_ID = src.CUSTOM_PAYMENT_TYPE_ID)
WHERE
    src.VOIDED = 0;

Description

Columns

Relationships

Close relationships within degrees of separation

View Definition

Possibly Referenced Tables/Views