Bi_FactInvoiceItem
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
|---|---|---|---|---|---|
ActionPerformedDateTimeInCenter | TIMESTAMP | The latest date and time on which a change was made to the appointment status, the appointment start time, or the appointment end time. | |||
ActualAppointmentTime | TIMESTAMP | The scheduled start date and time of an appointment at the time of its creation. If any changes are made to the appointment start date, this column will not change. | |||
AddOnGuestMembershipStatus | INT | Membership status of the guest as an add-on member. If the guest is associated with a membership as an add-on member, the status of the membership is populated here. If the guest is associated with multiple memberships as an add-on member, the status is populated as per a precedence order. - If a membership is actually sold on the corresponding sale date (when 'membership sold in invoice' is marked as True), the membership status as of the corresponding sale date is considered. Otherwise, the membership status as of the day prior to the corresponding sale date is considered. If the guest was associated with a membership as an add-on member in the past but was then removed from the membership, this field populates 'Add-on member before but not currently'. In this case, the status of the membership does not matter. If the guest was never associated with any membership as an add-on member, this field populates 'Never been an add-on member'. The following list describes the precendence order from highest priority on top to lowest priority at the bottom: Possible values: - Active - Frozen - Suspended - Prior Member with service credits - Prior Member without service credits - Add-on member before but not currently - Never been an add-on member - Not applicable | |||
AppliedBYwid | bigint | A unique identifier for the user that applied the discount.(Bi_DimUser) | Bi_Dimuser userwid | ||
AppointmentPK | numeric(19) | Unique identifier for each appointment. | |||
appstatuswid | bigint | A unique identifier that signifies the status of the Appointment, generated in status dimension(Bi_DimStatus) | Bi_DimStatus dimstatuswid | ||
Campaignwid | bigint | The unique identifier that indicates the Campaign details(Bi_DimCampaign). | |||
CampaignUsageDateInCenter | DATE | Date on which the campaign was applied to the corresponding invoice item in centre. | |||
CampaignUsageDateTimeInCenter | TIMESTAMP | Date and time at which the campaign was applied to the corresponding invoice item in centre. | |||
CancelOrNoShowStatus | SMALLINT | Flag that indicates whether the appointments with status as 'Cancel' or 'Noshow' have a cancellation or no-show fee associated with them. Possible values: - Cancelled Appointment: Appointment has a cancellation fee. - No Show: Appointment has a no-show fee. - Fulfilled Appointment: Appointment does not have a cancellation or no-show fee. | |||
Card | FLOAT | Payment received through card for the corresponding invoice item. This is a right-side transaction. | |||
Cash | FLOAT | Payment received through cash for the corresponding invoice item. This is a right-side transaction. | |||
CashBackRedemption | FLOAT | Amount redeemed from cashback for the corresponding invoice item. This is a left-side transaction. | |||
centerwid | bigint | A unique identifier for the center, generated in Centre dimension(Bi_DimCentre). | Bi_DimCenter centerwid | ||
cheque | FLOAT | Payment received via cheque for the corresponding invoice item. This is a right-side transaction. | |||
ClosedBywid | bigint | A unique identifier of the employee who closed the invoice(bi_dimuser). | |||
ClosedDateInCenter | DATE | The date on which the invoice was closed. | |||
ClosedDateTimeInCenter | TIMESTAMP | The date and time at which the invoice was closed. | |||
Comments | NVARCHAR(65535) | Comments entered for the corresponding invoice. | |||
ConsiderForFinancialZero_Discount | FLOAT | Payment received via custom payments that are not considered in financials for the corresponding invoice item. This is a right-side transaction. | |||
CreateDateInCenter | DATE | The date on which the invoice item was added into the invoice. | |||
CreateDateTimeInCenter | TIMESTAMP | The date and time at which the invoice item was added into the invoice. | |||
CreatedBywid | bigint | A unique identifier of the employee who created the invoice(bi_dimuser). | Bi_Dimuser userwid | ||
Custom | FLOAT | Payment received via monetary- financial custom payments for the corresponding invoice item. This is a right-side transaction. | |||
CustomDiscount | FLOAT | Custom discount applied on the corresponding invoice item. This is already included in 'discount applied'. | |||
CustomDiscountid | VARCHAR(36) | A system-generated unique identifier that is populated when a membership discount is applied on the invoice item. | |||
CustomDiscountType | VARCHAR(2048) | This field displays 'Membership Discount' when a membership discount is applied on the invoice item. Otherwise, it is blank. | |||
CustomPaymentName | NVARCHAR(65535) | Comma-separated list of custom payment types used while making the payment. | |||
Discount | FLOAT | Total discount applied on the corresponding invoice item. | |||
Discountwid | bigint | A unique identifier generated for a discount.(Bi_DimDiscount) | Bi_DimDiscount discountwid | ||
DiscountUserMembershipwid | bigint | If membership discount is applied it is populated with user membership id that was used | |||
discountWithTax | FLOAT | Total discount applied on the corresponding invoice item inclusive of apportioned tax. | |||
DRR | FLOAT | Daily recurring revenue of the membership calculated based on the membership payment frequency and amount. This is calculated assuming that the 'discount or order setting' is disabled. | |||
DRRWithDiscountorOrderSetting | FLOAT | Daily recurring revenue of the membership calculated based on the membership payment frequency and amount. This is calculated assuming that the 'discount or order setting' is enabled. | |||
Equipmentwid | bigint | The unique identifier that indicates the Equipment that used as part of the appoinmtnet(Bi_DimEquipment). | Bi_DimEquipment Equipmentwid | ||
etlCreatedBy | INT | 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. | |||
factinvoiceitemid | INT | A system-generated unique identifier for each record in the sales data source. This is generated within Analytics. | |||
factinvoiceitemwid | bigint | A system-generated unique identifier for each record in the sales data source. This is generated within Analytics. | Yes | ||
FinalSalePrice | FLOAT | The price at which the item was sold. The price is inclusive of discounts and taxes. | |||
FirstGuestStatus | SMALLINT | Note: Do not use this field. It is relevant for very specific scenarios only. | |||
followup | INT | Note: Do not use this field. It is relevant for very specific scenarios only. | |||
GC_PPCSold | SMALLINT | This flag indicates if gc/ppc is sold as part of the line item. | |||
GCPayment | FLOAT | Payment received via gift cards for the corresponding invoice item. This is a right-side transaction. | |||
GCRedemption | FLOAT | Amount redeemed from gift cards for the corresponding invoice item. This is a left-side transaction. | |||
GCRedemptionRevenue | FLOAT | Revenue recognized via gift cards, excluding tax. | |||
GCRevenue | FLOAT | Revenue recognised out of gc payment(right side payment) | |||
giftCardwid | bigint | A unique identifier generated for a Gift Card(Bi_DimGiftCard). | Bi_DimGiftCard GiftCardwid | ||
GroupInvoiceId | VARCHAR(36) | Invoice ID of the actual sale of the membership. This gets populated against all the subsequent recurring cycles and helps identify the original membership sale. | |||
GroupInvoiceOrder | INT | Number order that indicates the order of the recurring membership invoice. For set up membership, this will be 0 and for the subsequent recurring cycles, this will be 1,2,3,… | |||
Invoice_no | NVARCHAR(160) | Unique identifier for the invoice (unique within the center). | |||
invoiceDateInCenter | DATE | This is the same as the sale date for all records except for records which contain the initial sale of the membership. For these, it is populated as the membership start date. | |||
invoiceDateTimeInCenter | TIMESTAMP | Displays centre date and time at which the invoice was created. | |||
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. | |||
InvoiceItemPK | DECIMAL(19,0) | Unique identifier for each invoice item within the invoice. | |||
InvoiceSource | INT | The source from where the invoice was created(Bi_DimItemSource). | Bi_DimItemSource SourceNum | ||
invoiceStatus | SMALLINT | This indicates whether the invoice is open or closed. 4 is considered as close and all the remaining values are considered as open | Bi_DimStatus StatusTypeCode | ||
isAppointmentReScheduled | SMALLINT | Flag that indicates whether the appointment start time was changed from the initial start time that was set at the time of creation of the appointment. - 1: Appointment was rescheduled. - 0: Appointment was not rescheduled. | |||
IsDowngradeMembership | bigint | If a membership is cancelled and a new membership is purchased on the same day and the payment made to the new membership is lower than that of the old membership, it is considered as a downgrade membership.(Bi_DimStatus) | Bi_DimStatus StatusTypeCode | ||
isinvalidinvoice | BOOLEAN | Flag that indicates whether the invoice item is valid. - True: Invalid invoice. - False: Valid invoice. | |||
IsMembershipDiscount | INT | This indicates whether membership discount is applied on this invoice item or not | |||
IsMembershipDiscountedtwid | bigint | Flag that indicates whether the membership corresponding to the invoice item is discounted.(Bi_DimStatus) | Bi_DimStatus StatusTypeCode | ||
IsPaymentReceivedwid | bigint | This flag indicates whether any payment (right-side transaction) is received for the corresponding invoice item.(Bi_DimStatus) | Bi_DimStatus StatusTypeCode | ||
IsPriceAdjusted | BOOLEAN | Flag that indicates whether the sale price is adjusted. - True: Adjusted. - False: Not adjusted. | |||
IsRebookwid | bigint | A unique identifier that signifies whether an appointment is rebooked or not, generated in status dimension(Bi_DimStatus) | Bi_DimStatus StatusTypeCode | ||
IsRecurringInvoicewid | bigint | Flag that indicates whether the invoice is a recurring invoice.(Bi_DimStatus) | Bi_DimStatus StatusTypeCode | ||
IsRedo | BOOLEAN | Flag that indicates whether the corresponding appointment is a redo appointment (initial appointment). - True: Appointment led to a redo appointment. - False: Appointment is not a redo appointment. | |||
IsRedone | BOOLEAN | Flag that indicates whether the corresponding appointment is a re-done appointment (subsequent appointment). - True: Appointment is a re-done appointment. - False: Appointment is not a re-done appointment. | |||
IsRefundedwid | bigint | Flag that indicates whether the invoice item is refunded (whether the corresponding invoice item is the source for a refund). The actual refund invoice is separate.(Bi_DimStatus) | Bi_DimStatus StatusTypeCode | ||
isTaxExempted | SMALLINT | Flag that indicates whether tax is exempted for the guest. - True: Tax is exempted. - False: Tax is not exempted. | |||
IsUpgradeMembershipwid | bigint | If a membership is cancelled and a new membership is purchased on the same day and the payment made to the new membership is higher than that of the old membership, it is considered as an upgrade membership.(Bi_DimStatus) | Bi_DimStatus StatusTypeCode | ||
isUpsell | INT | If the guest buys two packages within 365 days within the same business unit, this flag is marked as True for the second package. | |||
Itemwid | bigint | A unique identifier for the Item(Bi_DimItems). | Bi_DimItems itemwid | ||
ItemsBoughtPreviously | numeric(19) | It represnts all the distinct item types that guest has purchased before this line item. It is the sum of 2 to the power of distict itemtype. | |||
ItemsBoughtTogether | INT | Indicates the item types bought together in the invoice. | |||
ItemTypewid | bigint | A unique identifier for the Item Type(Bi_DimItemType). | Bi_DimItemType itemtypewid | ||
LastServiceDate | DATE | Sale date of the previous appointment with 'appointment status' = 'Closed' or 'Confirmed' or 'Checkin' or 'Open'. The invoice status of the previous appointment does not matter. This field only gets populated if the current invoice item is of service item type. | |||
LastUpdatedDateInCenter | DATE | The centre date at which the invoice item was updated in the invoice. | |||
LastUpdatedDateTimeInCenter | TIMESTAMP | The centre date and time at which the invoice item was updated in the invoice. | |||
loyaltytierwid | bigint | loyalty tier to which guest belongs | bi_dimguestloyaltytier loyaltytierwid | ||
LPPayment | FLOAT | Payment received via loyalty points for the corresponding invoice item. This is a right-side transaction. | |||
LPRedemption | FLOAT | Amount redeemed from loyalty points for the corresponding invoice item. This is a left-side transaction. | |||
membergueststatuswid | SMALLINT | Membership status of the guest as a primary member. If the guest is associated with a membership as a primary member, the status of the membership is populated here. If the guest is associated with multiple memberships as a primary member, the status is populated as per a precedence order.(Bi_DimStatus) | Bi_DimStatus StatusTypeCode | ||
MembershipBenefitRedemptionRevenue | FLOAT | Revenue recognised out of membership service usages | |||
MembershipIRR | FLOAT | Initial revenue recognized via memberships, excluding tax. | |||
MembershipPayment | FLOAT | Payment received via memberships for the corresponding invoice item. This is a right-side transaction. | |||
MembershipRedemption | FLOAT | Amount redeemed from memberships for the corresponding invoice item. This is a left-side transaction. | |||
MembershipRedemptionRevenue | FLOAT | Revenue recognised out of membership redemption(left side payment) | |||
MembershipRevenue | FLOAT | Revenue recognised out of membership payment(right side payment) | |||
MembershipSold | SMALLINT | Flag indicates whether this is new membership sale or not. It is marked for setup membership line item if setup membership exists. | |||
MembershipUserId | VARCHAR(36) | The unique identifier of the member user that was purchased as part of this invoice item | Bi_DimMembershipUser UserMembershipwid | ||
membershipVersionwid | bigint | A unique identifier for membership version(Bi_DimMembership). | Bi_DimMembership MembershipVersionwid | ||
ModifiedBywid | bigint | A code that identifies the User that modified the record.(Bi_DimUser) | Bi_Dimuser userwid | ||
MultipleSoldBy | NVARCHAR(65535) | coma seperated sale by employee ids of the line item. | |||
NextSaleDate | DATE | Sale date of the next visit of the guest. Recurring invoices, refund invoices, voided invoices, cancelled, and no-show appointments are not considered for the next visit. The invoice status can be either open or closed for the next visit. This field does not get populated if the current invoice item is a refund, recurring, or invalid invoice item. | |||
NextServiceDate | DATE | Sale date of the next appointment with 'appointment status' = 'Closed' or 'Confirmed' or 'Checkin' or 'Open'. The invoice status of the next appointment does not matter. This field only gets populated if the current invoice item is of service item type. | |||
OrganizationId | VARCHAR(36) | A unique identifier for the organition for which this data belongs | Bi_DimCenter OrganizationID | ||
OriginalSalePrice | FLOAT | Original sale price of the invoice item. If the sale price is not adjusted, 'sale price' and 'original sale price' will be the same. | |||
PackageIRR | FLOAT | Initial revenue recognized via packages, excluding tax. | |||
PackageRedemptionRevenue | FLOAT | Revenue recognized via packages, excluding tax. | |||
packageVersionwid | bigint | A unique identifier for package version(Bi_DimPackage). | Bi_DimPackage PackageVersionwid | ||
PaidByCardsName | NVARCHAR(65535) | Comma-separated list of card types used while making the payment. | |||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
PODID | integer | Internal to the ETL, says from which POD data is being synced the table | |||
PrePaidCardPayment | FLOAT | Payment received via prepaid cards for the corresponding invoice item. This is a right-side transaction. | |||
PrePaidCardRevenue | FLOAT | Revenue recognized via prepaid cards, excluding tax. | |||
PreviousSaleDate | DATE | Sale date of the previous visit of the guest. Recurring invoices, refund invoices, voided invoices, cancelled, and no-show appointments are not considered for the previous visit. The invoice status can be either open or closed for the previous visit. This field does not get populated if the current invoice item is a refund, recurring, or invalid invoice item. | |||
productwid | bigint | A unique identifier for a product, generated in Product dimension(Bi_DimProduct). | Bi_DimProduct productwid | ||
ProductSoldCount | INT | The number of products sold. | |||
Quantity | INT | Quantity sold of the corresponding invoice item. | |||
RebookedAppointmentCount | INT | The number of appointments or appointment groups that got rebooked from the source. This is populated only for source appointments. If the entire appointment group is rebooked, this field populates the number of times the appointment group is rebooked. If a few appointments within a group are rebooked, this field populates the number of times the appointment is rebooked. | |||
RedoTherapistwid | bigint | A unique identifier for the Therapist performing the redo service.(Bi_DimUser) | Bi_Dimuser userwid | ||
RefundAmount | FLOAT | Amount refunded for the invoice item. This is the 'sale price' of the refund invoice item associated with this source invoice item. This field is only populated for invoice items that are the source for a refund - where is refunded is True. | |||
RefundDateInCenter | DATE | Date on which the invoice item is refunded. This is the 'sale date' of the refund invoice item associated with this source invoice item. | |||
RefundDateTimeInCenter | TIMESTAMP | Date and time at which the invoice item is refunded. This is the 'sale date and time' of the refund invoice item associated with this source invoice item. | |||
RefundQuantity | INT | Quantity refunded for the invoice item. This is the 'quantity sold' of the refund invoice item associated with this source invoice item. This is populated only for products. This field is only populated for invoice items that are the source for a refund - where is refunded is True. | |||
RefundSourceInvoiceItemId | VARCHAR(65535) | This field establishes the link between the source invoice item and the refund invoice item. It is only populated for refund invoice items. This field populates the invoice item ID of the source invoice item. | |||
RefundTax | FLOAT | Tax refunded for the invoice item. This is the 'tax' of the refund invoice item associated with this source invoice item. | |||
ReStockQuantity | FLOAT | This is populated for 'item type code' = 'product refund'. This is the quantity restocked out of the refunded quantity. | |||
resourcewid | bigint | Unique ID of the room in which service is provided | Bi_DimResource Resourcewid | ||
RoundingAdjustment | FLOAT | Rounding adjustments done for the particular invoice. | |||
SaleBywid | bigint | A unique identifier of the employee who made the sale(bi_dimuser). | Bi_Dimuser userwid | ||
SaleDateInCenter | DATE | For Service item type, this is the date on which the service was performed. For other item types, this is the date on 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. | |||
SalePrice | FLOAT | The sale price of the item derived as follows: sale price = 'list price' - 'discount applied' - 'loyalty point redemption amount' - 'membership redemption amount' - 'gift card redemption amount' - 'cashback redemption amount' + 'tax.' | |||
saletax | FLOAT | Tax applicable on the invoice item before discount. | |||
servicewid | bigint | A unique identifier for a Service, generated in Service dimension(Bi_DimService). | Bi_DimService Servicewid | ||
SOLDBYS_csv | VARCHAR(MAX) | coma seperated sale by employee ids of the line item. | |||
SwitchFromUserMembershipId | VARCHAR(36) | User membership id of the membership from which an upgrade/downgrade is done. | |||
Taxes | FLOAT | Final tax applied on the invoice item. | |||
Therapistwid | bigint | Unique identifier for each employee who is scheduled to service the guest. This is generated in analytics and it does not exist in core.(Bi_DimUser) | Bi_Dimuser userwid | ||
TherapistRequestType_statuswid | bigint | A unique identifier that identifies the therapist request type made for the appointment.(Bi_Dimstatus) | Bi_DimStatus StatusTypeCode | ||
UsageType | VARCHAR(160) | This shows whether package usage or membership usage is applied on the invoice item. Membership credit redemptions are not populated as 'membership usage'. Only membership service redemptions are populated as 'membership usage'. | |||
Userwid | bigint | A unique identifier for the guest, generated in User dimension(Bi_DimUser). | Bi_Dimuser userwid | ||
void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
membershipfeetypedetailswid | BIGINT | This column helps you to identify the type of fee levied on memberships | |||
groupid | Internal to Zenoti. Used for data processing. | BIGINT | |||
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. | |||