Invoice Item Fact Table
ZTZenoti Docs Team Updated April 10, 2026 7 min readGuide
Stores line-item-level detail for every invoice transaction, including pricing, discounts, taxes, redemptions, payment breakdowns, and related appointment and campaign context.
Columns
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| factinvoiceitemwid | BIGINT IDENTITY | No | Surrogate primary key (auto-generated) |
| factinvoiceitemid | INTEGER | Yes | Source system identifier for the invoice item fact |
| centerwid | BIGINT | Yes | Foreign key to the center dimension |
| userwid | BIGINT | Yes | Foreign key to the guest/user dimension |
| salebywid | BIGINT | Yes | Foreign key to the user who made the sale |
| servicewid | BIGINT | Yes | Foreign key to the service dimension |
| productwid | BIGINT | Yes | Foreign key to the product dimension |
| membershipversionwid | BIGINT | Yes | Foreign key to the membership version dimension |
| packageversionwid | BIGINT | Yes | Foreign key to the package version dimension |
| giftcardwid | BIGINT | Yes | Foreign key to the gift card dimension |
| itemwid | BIGINT | Yes | Foreign key to the generic item dimension |
| itemtypewid | BIGINT | Yes | Foreign key to the item type dimension |
| invoiceid | VARCHAR(36) | Yes | Unique identifier (GUID) of the invoice |
| invoiceitemid | VARCHAR(36) | Yes | Unique identifier (GUID) of the invoice item |
| invoice_no | VARCHAR(40) | Yes | Human-readable invoice number |
| invoiceitempk | NUMERIC(19,0) | Yes | Source primary key of the invoice item |
| quantity | INTEGER | Yes | Quantity of items on the line |
| invoicesource | INTEGER | Yes | Source/channel of the invoice (enumerator) |
| finalsaleprice | DOUBLE PRECISION | Yes | Final sale price after all adjustments |
| discount | DOUBLE PRECISION | Yes | Discount amount applied |
| taxes | DOUBLE PRECISION | Yes | Tax amount on the line item |
| saleprice | DOUBLE PRECISION | Yes | Original sale price before adjustments |
| lpredemption | DOUBLE PRECISION | Yes | Loyalty point redemption amount |
| membershipredemption | DOUBLE PRECISION | Yes | Membership redemption amount |
| gcredemption | DOUBLE PRECISION | Yes | Gift card redemption amount |
| cashbackredemption | DOUBLE PRECISION | Yes | Cashback redemption amount |
| productsoldcount | INTEGER | Yes | Count of products sold on this line |
| campaignwid | BIGINT | Yes | Foreign key to the campaign dimension |
| discountwid | BIGINT | Yes | Foreign key to the discount dimension |
| customdiscountid | VARCHAR(36) | Yes | GUID of the custom discount applied |
| customdiscounttype | VARCHAR(512) | Yes | Type/name of the custom discount |
| closebywid | BIGINT | Yes | Foreign key to the user who closed the invoice |
| createdbywid | BIGINT | Yes | Foreign key to the user who created the invoice item |
| modifiedbywid | BIGINT | Yes | Foreign key to the user who last modified the item |
| podid | INTEGER | Yes | Pod identifier for multi-tenant partitioning |
| invoicedatetimeincenter | TIMESTAMP | Yes | Invoice datetime in the center's local timezone |
| invoicedateincenter | DATE | Yes | Invoice date in the center's local timezone |
| saledatetimeincenter | TIMESTAMP | Yes | Sale datetime in the center's local timezone |
| saledateincenter | DATE | Yes | Sale date in the center's local timezone |
| appliedbywid | BIGINT | Yes | Foreign key to the user who applied the transaction |
| considerforfinancialzero_discount | DOUBLE PRECISION | Yes | Discount amount considered as financial zero |
| groupinvoiceid | VARCHAR(36) | Yes | GUID linking grouped invoices together |
| groupinvoiceorder | SMALLINT | Yes | Order/sequence within a group invoice |
| isrecurringinvoicewid | BIGINT | Yes | Boolean WID flag indicating if this is a recurring invoice |
| membershipsold | SMALLINT | Yes | Flag indicating if a membership was sold |
| membergueststatuswid | BIGINT | Yes | Foreign key to the member/guest status dimension |
| firstgueststatus | SMALLINT | Yes | Enumerator for the guest's first-visit status |
| cancelornoshowstatus | SMALLINT | Yes | Enumerator for cancel or no-show status |
| appointmentpk | NUMERIC(19,0) | Yes | Source primary key of the related appointment |
| invoicestatus | SMALLINT | Yes | Enumerator for invoice status |
| ismembershipdiscountedtwid | BIGINT | Yes | Boolean WID flag indicating if membership discount was applied |
| isupgrademembershipwid | BIGINT | Yes | Boolean WID flag indicating membership upgrade |
| isdowngrademembershipwid | BIGINT | Yes | Boolean WID flag indicating membership downgrade |
| membershipuserid | VARCHAR(36) | Yes | GUID of the user whose membership was used |
| itemsboughtpreviously | NUMERIC(19,0) | Yes | Count of items the guest bought previously |
| createdateincenter | DATE | Yes | Record creation date in center timezone |
| createdatetimeincenter | TIMESTAMP | Yes | Record creation datetime in center timezone |
| campaignusagedatetimeincenter | TIMESTAMP | Yes | Campaign usage datetime in center timezone |
| campaignusagedateincenter | DATE | Yes | Campaign usage date in center timezone |
| closeddatetimeincenter | TIMESTAMP | Yes | Invoice closed datetime in center timezone |
| closeddateincenter | DATE | Yes | Invoice closed date in center timezone |
| lastupdateddatetimeincenter | TIMESTAMP | Yes | Last updated datetime in center timezone |
| lastupdateddateincenter | DATE | Yes | Last updated date in center timezone |
| ispaymentreceivedwid | BIGINT | Yes | Boolean WID flag indicating if payment was received |
| gc_ppcsold | SMALLINT | Yes | Flag indicating gift card or prepaid card sold |
| cash | DOUBLE PRECISION | Yes | Cash payment amount |
| custom | DOUBLE PRECISION | Yes | Custom payment amount |
| cheque | DOUBLE PRECISION | Yes | Cheque payment amount |
| card | DOUBLE PRECISION | Yes | Card payment amount |
| lppayment | DOUBLE PRECISION | Yes | Loyalty point payment amount |
| membershippayment | DOUBLE PRECISION | Yes | Membership payment amount |
| membershiprevenue | DOUBLE PRECISION | Yes | Revenue attributed to membership |
| membershipredemptionrevenue | DOUBLE PRECISION | Yes | Revenue from membership redemption |
| membershipbenefitredemptionrevenue | DOUBLE PRECISION | Yes | Revenue from membership benefit redemption |
| gcpayment | DOUBLE PRECISION | Yes | Gift card payment amount |
| gcrevenue | DOUBLE PRECISION | Yes | Revenue attributed to gift cards |
| gcredemptionrevenue | DOUBLE PRECISION | Yes | Revenue from gift card redemption |
| prepaidcardpayment | DOUBLE PRECISION | Yes | Prepaid card payment amount |
| prepaidcardrevenue | DOUBLE PRECISION | Yes | Revenue attributed to prepaid cards |
| packageredemptionrevenue | DOUBLE PRECISION | Yes | Revenue from package redemption |
| custompaymentname | VARCHAR(65535) | Yes | Name(s) of custom payment methods used |
| paidbycardsname | VARCHAR(65535) | Yes | Name(s) of cards used for payment |
| membershipirr | DOUBLE PRECISION | Yes | Membership internal rate of return |
| packageirr | DOUBLE PRECISION | Yes | Package internal rate of return |
| switchfromusermembershipid | VARCHAR(36) | Yes | GUID of the membership the user switched from |
| isrefundedwid | BIGINT | Yes | Boolean WID flag indicating if the item was refunded |
| refunddateincenter | DATE | Yes | Refund date in center timezone |
| refunddatetimeincenter | TIMESTAMP | Yes | Refund datetime in center timezone |
| itemsboughttogether | INTEGER | Yes | Count of items bought in the same transaction |
| isrebookwid | BIGINT | Yes | Boolean WID flag indicating if this is a rebook |
| void | BOOLEAN | Yes | Indicates if the record is voided |
| soldbys_csv | VARCHAR(65535) | Yes | Comma-separated list of sold-by user identifiers |
| resourcewid | BIGINT | Yes | Foreign key to the resource dimension |
| equipmentwid | BIGINT | Yes | Foreign key to the equipment dimension |
| therapistwid | BIGINT | Yes | Foreign key to the therapist (user) dimension |
| therapistrequesttype_statuswid | BIGINT | Yes | Foreign key to the therapist request type/status dimension |
| previoussaledate | DATE | Yes | Date of the guest's previous sale |
| nextsaledate | DATE | Yes | Date of the guest's next sale |
| loyaltytierwid | BIGINT | Yes | Foreign key to the loyalty tier dimension |
| appstatuswid | BIGINT | Yes | Foreign key to the appointment status dimension |
| isinvalidinvoice | BOOLEAN | Yes | Flag indicating if the invoice is invalid |
| comments | VARCHAR(65535) | Yes | Free-text comments on the invoice item |
| multiplesoldby | VARCHAR(65535) | Yes | Details of multiple sold-by employees |
| isappointmentrescheduled | SMALLINT | Yes | Flag indicating if the appointment was rescheduled |
| actualappointmenttime | TIMESTAMP | Yes | Actual appointment start time |
| actionperformeddatetimeincenter | TIMESTAMP | Yes | Datetime of the action performed in center timezone |
| rebookedappointmentcount | INTEGER | Yes | Count of rebooked appointments |
| isredo | BOOLEAN | Yes | Flag indicating if this is a redo service |
| isredone | BOOLEAN | Yes | Flag indicating if this service was redone |
| redotherapistwid | BIGINT | Yes | Foreign key to the therapist who performed the redo |
| usagetype | VARCHAR(80) | Yes | Type of usage (e.g., redemption type) |
| refundsourceinvoiceitemid | VARCHAR(65535) | Yes | GUID(s) of the source invoice item(s) for the refund |
| refundamount | DOUBLE PRECISION | Yes | Refund amount |
| refundquantity | INTEGER | Yes | Refund quantity |
| ispriceadjusted | BOOLEAN | Yes | Flag indicating if the price was adjusted |
| originalsaleprice | DOUBLE PRECISION | Yes | Original sale price before adjustment |
| nextservicedate | DATE | Yes | Date of the guest's next service |
| lastservicedate | DATE | Yes | Date of the guest's last service |
| followup | INTEGER | Yes | Follow-up indicator |
| isupsell | INTEGER | Yes | Flag indicating if this was an upsell |
| saletax | DOUBLE PRECISION | Yes | Sale tax amount |
| discountwithtax | DOUBLE PRECISION | Yes | Discount amount including tax |
| customdiscount | DOUBLE PRECISION | Yes | Custom discount amount |
| drr | DOUBLE PRECISION | Yes | Daily run rate |
| istaxexempted | SMALLINT | Yes | Flag indicating if tax exemption was applied |
| drrwithdiscountorordersetting | DOUBLE PRECISION | Yes | Daily run rate with discount or order setting |
| refundtax | DOUBLE PRECISION | Yes | Tax amount on refund |
| roundingadjustment | DOUBLE PRECISION | Yes | Rounding adjustment amount |
| restockquantity | FLOAT | Yes | Quantity restocked on refund |
| appointmentid | VARCHAR(36) | Yes | GUID of the related appointment |
| membershipfeetypedetailswid | BIGINT | Yes | Foreign key to membership fee type details dimension |
| AdditionalField | NVARCHAR(MAX) | Yes | JSON or free-text additional fields |
| RefundReasonFK | BIGINT | Yes | Foreign key to the refund reason |
| 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 |
| gcredemptionwithtax | DOUBLE PRECISION | Yes | Gift card redemption amount including tax |
| gctaxredeemed | DOUBLE PRECISION | Yes | Tax portion of the gift card redemption |
| lpredemptionwithtax | DOUBLE PRECISION | Yes | Loyalty point redemption amount including tax |
Note: All fact tables also include standard ETL metadata columns (
podname,organizationid,groupid,etlCreatedDate) which are omitted from this reference for brevity.
Primary Key
factinvoiceitemwid
Foreign Keys
| Column | References |
|---|---|
| centerwid | Bi_DimCenter.centerwid |
| userwid | Bi_DimUser.userwid |
| salebywid | Bi_DimUser.userwid |
| closebywid | Bi_DimUser.userwid |
| createdbywid | Bi_DimUser.userwid |
| modifiedbywid | Bi_DimUser.userwid |
| appliedbywid | Bi_DimUser.userwid |
| therapistwid | Bi_DimUser.userwid |
| redotherapistwid | Bi_DimUser.userwid |
| resourcewid | Bi_DimResource.resourcewid |
| invoicedateincenter | Bi_DimDate.datewid |
| saledateincenter | Bi_DimDate.datewid |
| createdateincenter | Bi_DimDate.datewid |
| campaignusagedateincenter | Bi_DimDate.datewid |
| closeddateincenter | Bi_DimDate.datewid |
| lastupdateddateincenter | Bi_DimDate.datewid |
| refunddateincenter | Bi_DimDate.datewid |
Enumerator Values
| Column | Values |
|---|---|
| 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 |
| invoicestatus | 0 = Open 1 = Open 2 = Open 3 = Open 4 = Closed |
| firstgueststatus | 0 = Not First Guest 1 = First Guest |
| cancelornoshowstatus | -2 = No Show -1 = Cancelled 0 = New/Open 1 = Closed 2 = Checkin 4 = Confirm 5 = Cancel 7 = Void 10 = Noshow (Redo Penalty) 11 = Cancelled (Redo Penalty) 12 = Closed (Redo Penalty) |
| void | TRUE = Voided; FALSE = Active. |
| isredo | TRUE = Redo; FALSE = Not a redo. |
| isredone | TRUE = Redone; FALSE = Not redone. |
| ispriceadjusted | TRUE = Adjusted; FALSE = Not adjusted. |
| isinvalidinvoice | TRUE = Invalid; FALSE = Valid. |
Was this article helpful?
Your feedback helps us improve our documentation.