Collections fact table

AdjustedAmount

FLOAT

When the payment is made with a liability instrument whose price is different from the value associated with it, this field shows you the value realized by the business.

Amount

FLOAT

The actual amount against the corresponding amount type. This is a numerical value. Use the amount type and amount fields together to infer how much has been collected, how much is left to be collected, etc.

AmountByPayment

FLOAT

If the payment was made using the benefits available in a membership, this is the invoice value of the service performed. For other types of payments, this is the value same as the 'amount' field.

AmountType

VARCHAR(200)

The type of amount(Bi_DimStatus).

Bi_DimStatus

StatusTypeCode

AppGroupId

VARCHAR(36)

A unique identifier for each appointment group. Appointment group refers to a set of appointments that are booked together for a guest.

AppointmentId

VARCHAR(36)

A unique identifier for each appointment.

ApprovalNumber

NVARCHAR(512)

For credit card payments, the approval number for the credit card transaction gets populated here.

CCTransactionFee

FLOAT

For credit card payments, the transaction fee collected gets populated here. This gets duplicated for each transaction in the invoice, it does not get apportioned for each line item in the invoice.

In case of group invoices, the transaction fee is duplicated across the group invoices and is not apportioned for each invoice.

CCTransactionID

NVARCHAR(160)

A unique identifier for each 'cctransactionfee' collected. In case of group invoices which are paid through a single cc transaction, the 'cc transaction ID' against all the group invoices is the same.

Centerwid

bigint

A unique identifier for the center, generated in Centre dimension(Bi_DimCentre).

Bi_DimCenter

centerwid

ChargedBackAmount

FLOAT

The amount that is charged to the business after the dispute is resolved.

ClosedBywid

bigint

A unique identifier of the employee who closed the invoice(bi_dimuser).

Bi_Dimuser

uerwid

ClosedDateInCenter

DATE

The date on which the invoice was closed. This value is not populated if the invoice is not closed.

ClosedDateTimeInCenter

TIMESTAMP

The date and time at which the invoice was closed. This value is not populated if the invoice is not closed.

CreatedBywid

bigint

A unique identifier of the employee who created the invoice(bi_dimuser).

Bi_Dimuser

uerwid

CreatedDateInCenter

DATE

The date on which the invoice item was created.

CreatedDateTimeInCenter

TIMESTAMP

The date and time at which the invoice item was created.

CreditCardLastFourDigits

NVARCHAR(64)

For credit card payments, the last four digits of the credit card get populated here.

DisputeAmount

FLOAT

The amount that is being disputed by the customer.

DisputeDate

DATE

If a dispute was raised, this is the date on which it was raised.

DisputeDateTime

TIMESTAMP

If a dispute was raised, this is the date and time at which it was raised.

DisputeFee

FLOAT

The processing fee that the customer must pay for raising the dispute.

DisputeReason

NVARCHAR(1024)

The reason for the dispute.

DisputeStatus

NVARCHAR(512)

The status of the dispute.

EtlCreatedBy

DECIMAL(19,0)

ETL system admin user who inserted or updated the corresponding record in this table.

EtlCreatedDate

TIMESTAMP

Latest timestamp at which the corresponding record was either inserted or updated in this table.

factcollectionid

INT

A unique identifier for each record generated in the fact table.

factcollectionwid

bigint

A unique identifier for each record generated in the fact table.

Yes

Giftcardwid

bigint

A unique identifier generated for a Gift Card(Bi_DimGiftCard).

Bi_DimGiftCard

GiftCardwid

Invoice_No

NVARCHAR(320)

The unique identifier for the invoice within the center.

InvoiceId

VARCHAR(36)

The unique identifier for the invoice within the organization.

InvoiceItemId

VARCHAR(36)

The unique identifier for each line item within the invoice.

InvoiceSource

INT

The source from where the invoice was created(Bi_DimItemSource). 

Bi_DimItemSource

SourceNum

InvoiceStatus

INT

This indicates whether the invoice is open or closed.

Itemwid

bigint

A unique identifier for the Item(Bi_DimItems).

Bi_DimItems

itemwid

ItemTypewid

bigint

A unique identifier for the Item Type(Bi_DimItemType).

Bi_DimItemType

itemtypewid

MembershipVersionwid

bigint

A unique identifier for membership version(Bi_DimMembership).

Bi_DimMembership

MembershipVersionwid

OrganizationId

VARCHAR(36)

A unique identifier for the organition for which this data belongs

Bi_DimCenter

OrganizationId

OrgPaymentOptionwid

bigint

A unique identifier for Payment type option used for the payment(bi_dimorgpaymentoption).

Bi_DimOrgPaymentOption

paymentoptiontype

PackageVersionwid

bigint

A unique identifier for package version(Bi_DimPackage).

Bi_DimPackage

PackageVersionwid

PaidByAgentwid

bigint

The unique identifier of the custom payment agent for making the payment(Bi_DimAgent).

Bi_DimAgent

Agentwid

PaidByMembershipUserwid

bigint

A unique identifier for the membership user (Bi_DimMembershipUser).

Bi_DimMembershipUser

UserMembershipwid

PaidByPPCGCuserwid

bigint

A unique identifier for the giftcard/prepaid user (Bi_DimGiftCardUser).

Bi_DimGiftCardUser

GiftCardUserwid

Payment_No

NVARCHAR(320)

A unique identifier for each transaction. This could be blank in some cases. This maps to the payment number in the invoice in the core system.

PaymentDateInCenter

DATE

The date on which the payment was made.

PaymentDateTimeInCenter

TIMESTAMP

The date and time at which the payment was made.

PaymentModeData

NVARCHAR(65535)

For custom payments, the name of the custom payment method gets populated in the custom payment name field. If the appropriate option isn't there, the user can enter the information in the Custom payment additional details field.

PaymentModeNumber

VARCHAR(200)

Indicates the mode of payment.

For gift card or prepaid card payments, this populates the 'giftcarduserid'.

For membership payments, this populates 'usermembershipid'.

For package payments, this populates 'packageuserid'.

PaymentOptionType

VARCHAR(200)

For card payments, the type of card gets populated here. For cheque payments, the name of the cheque gets populated here.

PaymentType

NVARCHAR(MAX)

The mode of payment(Bi_DimStatus).

Bi_DimStatus

StatusTypeCode

PODID

integer

Internal to the ETL, says from which POD data is being synced the table

PodName

VARCHAR(20)

Internal to the ETL, says from which POD data is being synced the table

Productwid

bigint

A unique identifier for a product, generated in Product dimension(Bi_DimProduct).

Bi_DimProduct

productwid

Receipt_No

NVARCHAR(320)

A receipt number generated for an invoice after the invoice is closed. It is associated with the invoice.

RespondByDate

DATE

The date on which the dispute was actioned upon.

RespondByDateTime

TIMESTAMP

The date and time at which the dispute was actioned upon.

SaleBywid

bigint

A unique identifier of the employee who made the sale(bi_dimuser).

Bi_Dimuser

uerwid

SaleCenterwid

bigint

A unique identifier of the centre where the sale happened(Bi_DimCentre).

Bi_DimCenter

centerwid

SaleDateInCenter

DATE

For Service item type, this is the date and time at which the service was performed.

For other item types, this is the date and time at which the item was added to the invoice.

SaleDateTimeInCenter

TIMESTAMP

For Service item type, this is the date and time at which the service was performed.

For other item types, this is the date and time at which the item was added to the invoice.

Servicewid

bigint

A unique identifier for a Service, generated in Service dimension(Bi_DimService).

Bi_DimService

Servicewid

TransactionId

VARCHAR(36)

A unique non-blank identifier for each transaction.

For example, if an invoice is paid by using two swipes of a credit card, then two unique transaction IDs get associated with the invoice.

There is an exception in case of group invoices. Even if they are paid through a single transaction, the 'transaction ID' is different against each of the group invoices.

Userwid

bigint

A unique identifier for the guest, generated in User dimension(Bi_DimUser).

Bi_Dimuser

uerwid

Void

BOOLEAN

Indicates if the row should be inserted or deleted. 1- delete, 0-insert

DepositDate

TIMESTAMP

Expected date on which payment processor deposits amount into customer bank account

DepositAmount

FLOAT

Expected amount to be deposited in customer account. Transaction amount – transaction fee

ActualDepositDate

TIMESTAMP

Actual deposit date shared by payment processor

ActualDepositAmount

FLOAT

Actual deposit amount shared by payment processor

DisputeSettledDate

TIMESTAMP

Date on which a dispute is either “won” or “lost”

CCProcessor

NVARCHAR(50)

Indicates the payment processor used to process payments

CardSurchargeFee

FLOAT

This value is the surcharge fee collected for credit card transactions. Surcharge fee is apportioned at transactionid and invoiceitem level. To calculate surcharge fee amount, sum values in this column for a combination of transactionid and invoiceitemid for a given invoice and “amounttype” = ‘Paymentwithouttax’ and ‘TaxCollected’

ApportionedCCFee

FLOAT

This column contains the apportioned CC transaction fee for the group invoice transaction.

groupid

Internal to Zenoti. Used for data processing.

BIGINT

zenotimerchantid

VARCHAR(128)

Indicates customer account in the payment system. Internal to Zenoti.

isavsdowngradefeapplied

BOOLEAN

Indicates if AVS decline fee is applied or not for CC transactions.

cctransactionstatus

NVARCHAR(128)

Transaction status tells you if the transaction has been successful or not

utmsource

NVARCHAR(128)

Helps you the identify the booking source (google, Facebook, Instagram)

utmmedium

NVARCHAR(128)

Helps you the identify the booking source (google, Facebook, Instagram)

BundlePackageID

VARCHAR(36)

Helps you identify the bundle package (GUID). In case of multiple bundle packages, then ID remains same.

BundlePackageNo

INT

Helps you identify the number of bundle packages in a invoice.

cardtype

VARCHAR(400)