ZenotiHelp

Collections Fact Table

ZTZenoti Docs Team Updated April 10, 2026 5 min readGuide

Stores payment collection details for each invoice item, including payment modes, amounts, credit card transaction data, dispute information, and deposit details.

Columns

Column Name Data Type Nullable Description
factcollectionwid BIGINT IDENTITY No Surrogate primary key (auto-generated)
factcollectionid INTEGER Yes Source system identifier for the collection fact
centerwid BIGINT Yes Foreign key to the center where the collection was made
salecenterwid BIGINT Yes Foreign key to the center where the original sale occurred
userwid BIGINT Yes Foreign key to the guest/user dimension
itemtypewid BIGINT Yes Foreign key to the item type dimension
itemwid BIGINT Yes Foreign key to the generic item dimension
invoice_no VARCHAR(40) Yes Human-readable invoice number
invoiceid VARCHAR(36) Yes Unique identifier (GUID) of the invoice
invoiceitemid VARCHAR(36) Yes Unique identifier (GUID) of the invoice item
productwid BIGINT Yes Foreign key to the product dimension
servicewid BIGINT Yes Foreign key to the service dimension
packageversionwid BIGINT Yes Foreign key to the package version dimension
giftcardwid BIGINT Yes Foreign key to the gift card dimension
membershipversionwid BIGINT Yes Foreign key to the membership version dimension
paidbymembershipuserwid BIGINT Yes Foreign key to the user whose membership was used for payment
paidbyppcgcuserwid BIGINT Yes Foreign key to the user whose prepaid card/gift card was used
paidbyagentwid BIGINT Yes Foreign key to the agent who facilitated the payment
paymentmodenumber VARCHAR(72) Yes Payment mode reference number
orgpaymentoptionwid BIGINT Yes Foreign key to the organization payment option dimension
salebywid BIGINT Yes Foreign key to the user who made the sale
invoicestatus SMALLINT Yes Enumerator for invoice status
closebywid BIGINT Yes Foreign key to the user who closed the invoice
createdbywid BIGINT Yes Foreign key to the user who created the record
saledatetimeincenter TIMESTAMP Yes Sale datetime in center timezone
saledateincenter DATE Yes Sale date in center timezone
createddatetimeincenter TIMESTAMP Yes Record creation datetime in center timezone
createddateincenter DATE Yes Record creation date in center timezone
closeddatetimeincenter TIMESTAMP Yes Invoice closed datetime in center timezone
closeddateincenter DATE Yes Invoice closed date in center timezone
paymentdatetimeincenter TIMESTAMP Yes Payment datetime in center timezone
paymentdateincenter DATE Yes Payment date in center timezone
amount DOUBLE PRECISION Yes Payment amount
adjustedamount DOUBLE PRECISION Yes Adjusted payment amount
amounttype INTEGER Yes Enumerator for amount type
paymenttype INTEGER Yes Enumerator for payment type
transactionid VARCHAR(36) Yes GUID of the payment transaction
invoicesource INTEGER Yes Source/channel of the invoice (enumerator)
appointmentid VARCHAR(36) Yes GUID of the related appointment
appgroupid VARCHAR(36) Yes GUID of the related appointment group
podid INTEGER Yes Pod identifier for multi-tenant partitioning
void INTEGER Yes Indicates if the record is voided
payment_no VARCHAR(80) Yes Payment reference number
receipt_no VARCHAR(80) Yes Receipt reference number
paymentmodedata VARCHAR(65535) Yes Detailed payment mode metadata (JSON or CSV)
paymentoptiontype VARCHAR(100) Yes Type of payment option used
cctransactionfee DOUBLE PRECISION Yes Credit card transaction fee
amountbypayment DOUBLE PRECISION Yes Amount attributed to this specific payment mode
approvalnumber VARCHAR(256) Yes Payment approval/authorization number
creditcardlastfourdigits VARCHAR(32) Yes Last four digits of the credit card used
disputeamount DOUBLE PRECISION Yes Amount under dispute
disputestatus VARCHAR(512) Yes Current status of the dispute
disputereason VARCHAR(1028) Yes Reason for the dispute
disputedatetime TIMESTAMP Yes Datetime when the dispute was raised
disputedate DATE Yes Date when the dispute was raised
chargedbackamount DOUBLE PRECISION Yes Amount charged back
disputefee DOUBLE PRECISION Yes Fee associated with the dispute
respondbydatetime TIMESTAMP Yes Deadline datetime to respond to the dispute
respondbydate DATE Yes Deadline date to respond to the dispute
cctransactionid VARCHAR(40) Yes Credit card transaction identifier
DepositDate TIMESTAMP Yes Expected deposit date
DepositAmount FLOAT Yes Expected deposit amount
ActualDepositDate TIMESTAMP Yes Actual deposit date
ActualDepositAmount FLOAT Yes Actual deposit amount
DisputeSettledDate TIMESTAMP Yes Date when the dispute was settled
CCProcessor NVARCHAR(50) Yes Credit card processor name
CardSurchargeFee FLOAT Yes Card surcharge fee amount
ApportionedCCFee FLOAT Yes Apportioned credit card fee
tipsconveniencefee FLOAT Yes Tips or convenience fee amount
zenotimerchantid VARCHAR(128) Yes Zenoti merchant identifier
isavsdowngradefeeapplied BOOLEAN Yes Flag indicating if AVS downgrade fee was applied
cctransactionstatus NVARCHAR(128) Yes Status of the credit card transaction
utmsource NVARCHAR(128) Yes UTM source for marketing attribution
utmmedium NVARCHAR(128) Yes UTM medium for marketing attribution
bundlepackageversionwid BIGINT Yes Foreign key to the bundle package version dimension
bundlepackageno INTEGER Yes Bundle package number
cardtype VARCHAR(400) Yes Type of payment card
TransactionalRefundDate TIMESTAMP Yes Date of transactional refund
TransactionalRefundAmount FLOAT Yes Amount of transactional refund
ReversalDate TIMESTAMP Yes Date of payment reversal
TaxOnCardSurcharge FLOAT Yes Tax on the card surcharge
RedeemedTaxAmount FLOAT Yes Tax amount that was redeemed

Note: All fact tables also include standard ETL metadata columns (podname, organizationid, groupid, etlCreatedDate) which are omitted from this reference for brevity.

Primary Key

factcollectionwid

Foreign Keys

Column References
centerwid Bi_DimCenter.centerwid
salecenterwid Bi_DimCenter.centerwid
userwid Bi_DimUser.userwid
paidbymembershipuserwid Bi_DimUser.userwid
paidbyppcgcuserwid Bi_DimUser.userwid
paidbyagentwid Bi_DimUser.userwid
salebywid Bi_DimUser.userwid
closebywid Bi_DimUser.userwid
createdbywid Bi_DimUser.userwid
saledateincenter Bi_DimDate.datewid
createddateincenter Bi_DimDate.datewid
closeddateincenter Bi_DimDate.datewid
paymentdateincenter Bi_DimDate.datewid

Enumerator Values

Column Values
void true = Voided, false = Active
isavsdowngradefeeapplied true = Applied, false = Not applied
invoicestatus 0 - 3 = Open
4 = Closed
invoicesource 0 = Store (MMS)
1 = Mobile
2 = Web
3 = Imports
4 = Offline
5 = Dunning
6 = Employee App
7 = Manager App
8 = Auto Collection
9 = Biometric App
10 = Support Login
11 = Take 5
12 = Therapist App Android
13 = Therapist App iOS
14 = Mobile POS (Point of Sale) Android
15 = Mobile POS iOS
16 = PoS
17 = Shopify
18 = ECommerce
19 = Instore App
20 = Api Direct
21 = Unknown
22 = Extension
23 = Queue
24 = Kiosk
25 = Kiosk CMA (Customer Mobile App)
26 = Kiosk Web
27 = Invoice Refund
28 = Membership Unfreeze
29 = Membership Cancellation
30 = External CMA
31 = External Webstore
32 = Client App
33 = Bot
amounttype 1 = ToBeCollectedAmount
2 = ToBeCollectedTax
3 = PaymentWithoutTax
4 = TaxCollected
5 = Tips
6 = SSG
7 = EnvironmentalFee
8 = RedemptionWithoutTax
9 = PackageUsage
10 = MembershipUsage
11 = CashBackAmount
12 = Donations
13 = CollectedShippingCharge
paymenttype 1 = Cash
2 = Card
3 = Check
4 = Custom
5 = Membership Credit
6 = Gift Card
7 = PPC (Pre-Paid Card)
8 = Loyalty Points
9 = Package Usage
10 = Membership Usage
11 = CashBack
Was this article helpful?
Your feedback helps us improve our documentation.