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.