account_balance_wallet

OrderFinancialSummaryView

Unified AI-ready financial business view designed to provide order-level financial insights, customer balances, payments, settlements, returns and accounting adjustments.
financial orders receivables customer balance ai semantic layer
Purpose

OrderFinancialSummaryView provides a consolidated financial view of customer orders. It is designed to support AI assistants, financial reporting, receivable analysis, customer account inquiries and operational decision-making.

Each record represents a single order and contains calculated financial amounts including gross sales, discounts, taxes, returns, payments, settlements and outstanding balances.

Business Meaning

This view represents the financial lifecycle of an order from the moment it is registered until it is invoiced, settled, adjusted or canceled.

It enables the AI Assistant to answer questions related to:

  • Customer outstanding balances.
  • Order financial summaries.
  • Total payments received.
  • Returns and their financial impact.
  • Accounting adjustments.
  • Settlement transfers between invoices.
  • Receivable analysis and collection monitoring.
Date Format Notes
calendar_month
Important

All date fields in this view are stored as STRING values and are not SQL DateTime columns.

The supported format is: 

YYYY/MM/DD

Example: 2023/04/09

Applications and AI agents should parse these values before performing date calculations, sorting or time-based analysis.

Financial Definitions
Field Meaning
SumPrice Gross order amount before discounts and taxes.
SumDis Total discounts including promotional discounts, invoice discounts and row discounts.
SumTax Tax amount calculated after discounts.
SumRet Financial impact of customer returns including return discounts and return row adjustments.
Khales Final net receivable amount generated by the order after applying discounts, taxes and returns.
SumPayment Total payments received against the order.
Mandeh Remaining outstanding balance after applying payments, settlements and accounting adjustments.
Settlement and Accounting Adjustments
Field Description
Tahatorbed Settlement debit amount. Represents amounts transferred from another invoice into this invoice. As a result, this invoice becomes debtor through settlement.
Tahatorbes Settlement credit amount. Represents amounts transferred from this invoice to another invoice. As a result, this invoice becomes creditor through settlement.
CreditAmount Credit note amount. Used for accounting adjustments that decrease customer liability, such as managerial discounts, account corrections or other approved credits.
DebitAmount Debit note amount. Used for accounting adjustments that increase customer liability, such as delivery charges, penalties or account corrections.
Outstanding Balance Formula

The outstanding balance (Mandeh) is calculated using the following business rule: 

Mandeh =
Khales
- SumPayment
+ Tahatorbed
- Tahatorbes
- CreditAmount
+ DebitAmount

This amount represents the actual receivable balance that remains collectible from the customer.

Customer and Distribution Information
Field Meaning
GrpDesc Customer Group description used to classify customers into business segments.
MPID Identifier of the delivery representative or distribution agent responsible for delivering the order.
MPName Name of the delivery representative or distribution agent responsible for the order fulfillment process.
Relationships

This view is the core financial aggregation layer of the system. It provides order-level financial lifecycle data including invoicing, returns, payments, settlements, and adjustments. It connects transactional data with customer, sales, logistics, and analytical views for end-to-end financial insight.

Related View Join Keys Description
OrderReturnSummaryView OrderID, CustID Links return transactions to order financials to calculate net revenue impact and refund adjustments.
OrderCollectionAnalyticsView OrderID, CustID Analyzes payment behavior, collection speed, and outstanding balances per order.
CustomerPotentialAnalyticsView CustID Correlates financial performance with customer behavioral scoring and potential estimation.
CustAssignToSeller CustID, SellerID Attributes financial performance to responsible sales representatives via customer assignment mapping.
CustomerVisitLogsView CustID, SellerID (indirect) Links visit activity to financial outcomes such as order generation and revenue realization.
SellerVisitPathCustomersView CustID, SellerID (indirect) Compares planned visits with realized financial performance per customer.
DeliveryOperationAnalyticsView OrderID (SalesDocID), MPID Connects financial orders with delivery execution to analyze fulfillment and cashflow timing.
InventoryView BranchID (indirect), Product-level via order lines Provides contextual inventory impact of financial transactions (consumption is not directly stored here).
OrderFinancialSummaryView (Current) OrderID, CustID, SellerID, BranchID, SCID Core financial fact view representing full lifecycle of each order including revenue, payments, returns, and balances.
Primary Financial Join Strategy
  • OrderID → Primary transaction key across finance, returns, and collections
  • CustID → Customer financial behavior and credit exposure analysis
  • SellerID → Sales performance attribution and responsibility tracking
  • BranchID / SCID → Organizational financial segmentation
  • MPID → Delivery execution impact on financial realization (indirect linkage)
Fields
Field Type Description
BranchID int Unique identifier of the branch.
BranchName string Name of the branch responsible for the order.
SCID int Sales channel identifier.
SCName string Name of the sales channel.
OrderID int Unique identifier of the order/invoice.
IsEbtal bit Cancellation flag. 1 indicates that the order has been canceled.
IsFinal bit Finalization flag indicating whether the order has been converted into an invoice.
StatusCode int Numeric representation of the operational status of the order.
StatusTitle string Human-readable description of the order status.
OrderDate string Order registration date stored in YYYY/MM/DD format.
DayName string Name of the weekday associated with the order date.
MountName string Name of the calendar month associated with the order date.
TahvilDate string Planned or actual delivery date stored in YYYY/MM/DD format.
CustID int Unique customer identifier.
CustTitle string Customer name or business title.
Lat decimal Customer latitude coordinate.
Lng decimal Customer longitude coordinate.
CityName string Customer city.
AreaName string Customer area or district.
GrpDesc string Customer group description used for customer segmentation.
GradeDesc string Customer grade classification.
SenfDesc string Customer business category or industry classification.
SellerID int Sales representative identifier.
VisitorName string Name of the sales representative.
MPID int Delivery representative (distribution agent) identifier responsible for order fulfillment.
MPName string Name of the delivery representative or distribution agent.
SumPrice decimal Gross order amount before discounts, taxes and returns.
ProdCount int Number of distinct products included in the order.
SumDis decimal Total discounts including promotional discounts, invoice discounts and row-level discounts.
SumTax decimal Total tax amount calculated after applying discounts.
SumRet decimal Financial impact of returned goods including return discounts and adjustments.
Khales decimal Net receivable amount generated by the order after discounts, taxes and returns.
SumPayment decimal Total payments received against the order.
Mandeh decimal Outstanding balance remaining after considering payments, settlements and accounting adjustments.
Tahatorbed decimal Settlement debit amount resulting from transfer of balances from other invoices into this invoice.
Tahatorbes decimal Settlement credit amount resulting from transfer of balances from this invoice to other invoices.
CreditAmount decimal Credit note amount used to decrease customer liability, such as managerial discounts or approved corrections.
DebitAmount decimal Debit note amount used to increase customer liability, such as delivery charges, penalties or corrections.
warning
Date Format Note

All date fields are stored as STRING in format YYYY/MM/DD and must be parsed before date operations.