This view provides enriched invoice-level and product-level data for reporting, AI analysis, logistics planning, financial calculation and customer insights.
Pweight → grams (g)PVol → cubic millimeters (mm³)ProdQtyAll → base unit (piece)QtyPackage → units per package/cartonProdQtyAll / QtyPackage
(if QtyPackage > 0)
Height × Width × Length (mm³)
This view acts as a core transactional semantic layer for sales and operational analytics. It connects customer, product, financial, and logistics domains through consistent business keys. Relationships are categorized into transactional, operational, and analytical layers to avoid circular dependency.
| Related View | Join Keys | Relationship Type | Description |
|---|---|---|---|
| CustAssignToSellerView | SellerID, CustID | Operational | Maps customer ownership to sales representatives for territory and coverage validation. |
| CustomerPotentialAnalyticsView | CustID | Analytical | Enables comparison of customer behavioral score with actual transactional performance. |
| CustomerVisitLogsView | CustID, SellerID | Operational | Links executed visits with sales outcomes to measure engagement effectiveness. |
| SellerVisitPathCustomersView | CustID, SellerID | Operational | Compares planned visits with actual sales execution coverage. |
| DeliveryOperationAnalyticsView | OrderID = SalesDocID | Operational | Connects sales transactions with logistics execution and delivery performance. |
| InventoryView | ProdID, BranchID | Operational | Provides stock context for sold products and supports replenishment planning. |
| OrderCollectionAnalyticsView | OrderID | Analytical | Analyzes payment behavior, receivable aging, and collection efficiency. |
| OrderFinancialSummaryView | OrderID | Transactional | Core financial aggregation layer including revenue, discounts, tax, and outstanding balance. |
| OrderReturnSummaryView | OrderID, ProdID, CustID | Transactional | Handles product-level return analysis and reverse logistics impact on orders. |
| SalesInvoiceView (Current) | OrderID, CustID, SellerID, MPID, ProdID, BranchID, SCID | Transactional (Fact) | Primary fact table representing invoice line-level sales transactions across all dimensions. |
OrderID → Core transaction backbone across financial, delivery, return, and collection systemsCustID → Customer intelligence, segmentation, and behavioral analysisSellerID → Sales force performance and territory managementMPID → Logistics execution and delivery performance trackingProdID → Product profitability, returns, and inventory consumptionBranchID / SCID → Organizational and channel segmentation| Field | Type | Description | |
|---|---|---|---|
| BranchID | int | Branch identifier | |
| BranchName | string | Branch name | |
| SCID | int | Sales channel ID | |
| SCName | string | Sales channel name | |
| OrderID | int | Invoice / Order ID | |
| StatusCode | int | Order status (1 Order, 2 Distribution, 3 Invoiced, 4 Canceled) | |
| StatusTitle | string | Status description | |
| OrderDate | string | Order date | |
| TahvilDate | string | Delivery date | |
| CustID | int | Customer ID | |
| CustTitle | string | Customer name | |
| Lat | decimal | Customer latitude | |
| Lng | decimal | Customer longitude | |
| CityName | string | City | |
| AreaName | string | Area | |
| SellerID | int | Salesperson ID | |
| VisitorName | string | Salesperson name | |
| MPID | Delivery Agent ID | int | Identifier of the delivery or distribution agent responsible for handling the order after assignment to logistics. |
| MPName | Delivery Agent Name | string | Name of the delivery or distribution representative responsible for order delivery. |
| ProdID | int | Product ID | |
| ProdName | string | Product name | |
| ProdGroup | int | Product group ID | |
| GrpDesc | string | Product group name | |
| BrandID | int | Brand ID | |
| BrandName | string | Brand name | |
| ProdQtyAll | decimal | Quantity in base unit (piece) | |
| QtyPackage | int | Units per package/carton | |
| Pweight | decimal | Weight per unit (grams) | |
| PVol | decimal | Volume per unit (mm³) | |
| IsPromo | bit | Promotion flag | |
| SumPrice | decimal | Gross line price | |
| SUMProdDiscount | decimal | Total product discount | |
| NetPrice | decimal | Net price after discount | |
| TaxPrice | decimal | Tax amount | |
| FinalPrice | decimal | Total final price |
All date fields in this view are stored as STRING in format YYYY/MM/DD.
They are not SQL DateTime types and must be parsed before date operations.