Collections fact table
Bi_FactCollections_s3
Field | Field Type | Description | PrimaryKey | MappedDimensionTable DimensionTableField | |
|---|---|---|---|---|---|
AdjustedAmount | FLOAT | When the payment is made with a liability instrument whose price is different from the value associated with it, this field shows you the value realized by the business. | |||
Amount | FLOAT | The actual amount against the corresponding amount type. This is a numerical value. Use the amount type and amount fields together to infer how much has been collected, how much is left to be collected, etc. | |||
AmountByPayment | FLOAT | If the payment was made using the benefits available in a membership, this is the invoice value of the service performed. For other types of payments, this is the value same as the 'amount' field. | |||
AmountType | VARCHAR(200) | The type of amount(Bi_DimStatus). | Bi_DimStatus_s3 StatusTypeCode | ||
AppGroupId | VARCHAR(36) | A unique identifier for each appointment group. Appointment group refers to a set of appointments that are booked together for a guest. | |||
AppointmentId | VARCHAR(36) | A unique identifier for each appointment. | |||
ApprovalNumber | NVARCHAR(512) | For credit card payments, the approval number for the credit card transaction gets populated here. | |||
CCTransactionFee | FLOAT | For credit card payments, the transaction fee collected gets populated here. This gets duplicated for each transaction in the invoice, it does not get apportioned for each line item in the invoice. In case of group invoices, the transaction fee is duplicated across the group invoices and is not apportioned for each invoice. | |||
CCTransactionID | NVARCHAR(160) | A unique identifier for each 'cctransactionfee' collected. In case of group invoices which are paid through a single cc transaction, the 'cc transaction ID' against all the group invoices is the same. | |||
CenterId | VARCHAR(36) | A unique identifier for the center, generated in Centre dimension(Bi_DimCentre). | Bi_DimCenter_s3 centerid | ||
ChargedBackAmount | FLOAT | The amount that is charged to the business after the dispute is resolved. | |||
ClosedBy | NVARCHAR(MAX) | A unique identifier of the employee who closed the invoice(bi_dimuser). | Bi_Dimuser_s3 userid | ||
ClosedDateInCenter | DATE | The date on which the invoice was closed. This value is not populated if the invoice is not closed. | |||
ClosedDateTimeInCenter | TIMESTAMP | The date and time at which the invoice was closed. This value is not populated if the invoice is not closed. | |||
CreatedBy | NVARCHAR(MAX) | A unique identifier of the employee who created the invoice(bi_dimuser). | Bi_Dimuser_s3 userid | ||
CreatedDateInCenter | DATE | The date on which the invoice item was created. | |||
CreatedDateTimeInCenter | TIMESTAMP | The date and time at which the invoice item was created. | |||
CreditCardLastFourDigits | NVARCHAR(64) | For credit card payments, the last four digits of the credit card get populated here. | |||
DisputeAmount | FLOAT | The amount that is being disputed by the customer. | |||
DisputeDate | DATE | If a dispute was raised, this is the date on which it was raised. | |||
DisputeDateTime | TIMESTAMP | If a dispute was raised, this is the date and time at which it was raised. | |||
DisputeFee | FLOAT | The processing fee that the customer must pay for raising the dispute. | |||
DisputeReason | NVARCHAR(1024) | The reason for the dispute. | |||
DisputeStatus | NVARCHAR(512) | The status of the dispute. | |||
EtlCreatedBy | DECIMAL(19,0) | 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. | |||
factcollectionid | INT | A unique identifier for each record generated in the fact table. | Yes | ||
GiftcardId | NVARCHAR(MAX) | A unique identifier generated for a Gift Card(Bi_DimGiftCard). | Bi_DimGiftCard_s3 GiftCardId | ||
Invoice_No | NVARCHAR(320) | The unique identifier for the invoice within the center. | |||
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. | |||
InvoiceSource | INT | The source from where the invoice was created(Bi_DimItemSource). | Bi_DimItemSource_s3 SourceNum | ||
InvoiceStatus | INT | This indicates whether the invoice is open or closed. | |||
ItemId | VARCHAR(36) | A unique identifier for the Item(Bi_DimItems). | Bi_DimItems_S3 itemid | ||
ItemType | INT | A unique identifier for the Item Type(Bi_DimItemType). | Bi_DimItemType_S3 itemtypeid | ||
MembershipVersionId | NVARCHAR(MAX) | A unique identifier for membership version(Bi_DimMembership). | Bi_DimMembership_s3 MembershipVersionId | ||
OrganizationId | VARCHAR(36) | A unique identifier for the organition for which this data belongs | Bi_DimCenter_s3 OrganizationId | ||
OrgPaymentOptionId | VARCHAR(144) | A unique identifier for Payment type option used for the payment(bi_dimorgpaymentoption). | Bi_DimOrgPaymentOption_s3 paymentoptiontype | ||
PackageVersionId | NVARCHAR(MAX) | A unique identifier for package version(Bi_DimPackage). | Bi_DimPackage_s3 PackageVersionId | ||
PaidByAgentId | NVARCHAR(MAX) | The unique identifier of the custom payment agent for making the payment(Bi_DimAgent). | Bi_DimAgent_s3 AgentId | ||
PaidByMembershipUserid | NVARCHAR(MAX) | A unique identifier for the membership user (Bi_DimMembershipUser). | Bi_DimMembershipUser_s3 UserMembershipId | ||
PaidByPPCGCuserid | NVARCHAR(MAX) | A unique identifier for the giftcard/prepaid user (Bi_DimGiftCardUser). | Bi_DimGiftCardUser_s3 GiftCardUserId | ||
Payment_No | NVARCHAR(320) | A unique identifier for each transaction. This could be blank in some cases. This maps to the payment number in the invoice in the core system. | |||
PaymentDateInCenter | DATE | The date on which the payment was made. | |||
PaymentDateTimeInCenter | TIMESTAMP | The date and time at which the payment was made. | |||
PaymentModeData | NVARCHAR(65535) | For custom payments, the name of the custom payment method gets populated in the custom payment name field. If the appropriate option isn't there, the user can enter the information in the Custom payment additional details field. | |||
PaymentModeNumber | VARCHAR(200) | Indicates the mode of payment. For gift card or prepaid card payments, this populates the 'giftcarduserid'. For membership payments, this populates 'usermembershipid'. For package payments, this populates 'packageuserid'. | |||
PaymentOptionType | VARCHAR(200) | For card payments, the type of card gets populated here. For cheque payments, the name of the cheque gets populated here. | |||
PaymentType | NVARCHAR(MAX) | The mode of payment(Bi_DimStatus). | Bi_DimStatus_s3 StatusTypeCode | ||
PodName | VARCHAR(20) | Internal to the ETL, says from which POD data is being synced the table | |||
ProductId | NVARCHAR(MAX) | A unique identifier for a product, generated in Product dimension(Bi_DimProduct). | Bi_DimProduct_s3 productid | ||
Receipt_No | NVARCHAR(320) | A receipt number generated for an invoice after the invoice is closed. It is associated with the invoice. | |||
RespondByDate | DATE | The date on which the dispute was actioned upon. | |||
RespondByDateTime | TIMESTAMP | The date and time at which the dispute was actioned upon. | |||
SaleById | NVARCHAR(MAX) | A unique identifier of the employee who made the sale(bi_dimuser). | Bi_Dimuser_s3 userid | ||
SaleCenterId | VARCHAR(36) | A unique identifier of the centre where the sale happened(Bi_DimCentre). | Bi_DimCenter_s3 centerid | ||
SaleDateInCenter | DATE | 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. | |||
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. | |||
ServiceId | NVARCHAR(MAX) | A unique identifier for a Service, generated in Service dimension(Bi_DimService). | Bi_DimService_s3 ServiceID | ||
TransactionId | VARCHAR(36) | A unique non-blank identifier for each transaction. For example, if an invoice is paid by using two swipes of a credit card, then two unique transaction IDs get associated with the invoice. There is an exception in case of group invoices. Even if they are paid through a single transaction, the 'transaction ID' is different against each of the group invoices. | |||
UserId | VARCHAR(36) | A unique identifier for the guest, generated in User dimension(Bi_DimUser). | Bi_Dimuser_s3 userid | ||
Void | BOOLEAN | Indicates if the row should be inserted or deleted. 1- delete, 0-insert | |||
DepositDate | TIMESTAMP | Expected date on which payment processor deposits amount into customer bank account | |||
DepositAmount | FLOAT | Expected amount to be deposited in customer account. Transaction amount – transaction fee | |||
ActualDepositDate | TIMESTAMP | Actual deposit date shared by payment processor | |||
ActualDepositAmount | FLOAT | Actual deposit amount shared by payment processor | |||
DisputeSettledDate | TIMESTAMP | Date on which a dispute is either “won” or “lost” | |||
CCProcessor | NVARCHAR(50) | Indicates the payment processor used to process payments | |||
CardSurchargeFee | FLAOT | This value is the surcharge fee collected for credit card transactions. Surcharge fee is apportioned at transactionid and invoiceitem level. To calculate surcharge fee amount, sum values in this column for a combination of transactionid and invoiceitemid for a given invoice and “amounttype” = ‘Paymentwithouttax’ and ‘TaxCollected’ | |||
ApportionedCCFee | FLOAT | This column contains the apportioned cc transaction fee for the group invoice transaction. | |||
groupid | BIGINT | Internal to Zenoti. Used for data processing. | |||
zenotimerchantid | VARCHAR(128) | Indicates customer account in the payment system. Internal to Zenoti. | |||
isavsdowngradefeeapplied | BOOLEAN | Indicates if AVS decline fee is applied or not for CC transactions. | |||
cctransactionstatus | NVARCHAR(128) | Transaction status tells you if the transaction has been successful or not | |||
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. | |||
cardtype | VARCHAR(400) | ||||