ZenotiHelp

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.