OrderReturnSummaryView | ACECloudify AI Data Catalog

Purpose

This view provides detailed information about returned sales transactions, including product-level return quantities, discounts, refund amounts, and customer information. It is used for financial analysis, return rate tracking, and logistics optimization.

Business Rules

Each record represents a returned product line within a return transaction.
Return amounts are calculated based on product price and returned quantity.
Net return amount excludes discounts applied to returned items.
Promo items may have zero or full refund depending on business rules.

Key Definitions

RetAmount: Total value of returned products before discount.
RetDiscount: Discount applied on returned items.
NetRetAmount: Final return amount after discount deduction.
MainRetID: Unique return transaction identifier.
VisitorMPID: Delivery / distribution agent responsible for handling return process.
IsPromo: Indicates whether returned item was part of a promotion.

Relationships

This view represents the reverse logistics and product return lifecycle layer. It connects return transactions with orders, customers, products, sales, financials, and inventory systems to enable end-to-end analysis of return behavior, product quality issues, and financial impact.

Related View	Join Keys	Description
OrderFinancialSummaryView	OrderID, CustID	Provides financial baseline of original order to evaluate return impact on revenue and net profit.
OrderCollectionAnalyticsView	OrderID, CustID	Analyzes whether returned orders influence payment behavior or cash collection performance.
CustomerPotentialAnalyticsView	CustID	Identifies whether return patterns correlate with customer dissatisfaction or churn risk.
CustAssignToSeller	SellerID, CustID	Evaluates seller-level return responsibility and territory-based return concentration.
CustomerVisitLogsView	SellerID, CustID	Links return frequency with customer engagement and visit effectiveness.
SellerVisitPathCustomersView	SellerID, CustID	Compares planned visits with return patterns to identify operational or product-related issues.
DeliveryOperationAnalyticsView	OrderID (via fulfillment chain), MPID	Provides indirect linkage between delivery execution and return initiation or handling efficiency.
InventoryView	ProdID, BranchID, StoreID	Reflects reverse stock movement where returned goods increase available inventory levels.
OrderReturnSummaryView (Current)	MainRetID, OrderID, CustID, ProdID	Core reverse logistics fact table representing product-level return transactions and financial adjustments.

Primary Return Lifecycle Strategy

MainRetID → Unique return transaction identifier (grain of return event)
OrderID → Links return to original sales transaction lifecycle
CustID → Customer satisfaction and return behavior analysis
ProdID → Product quality, defect rate, and return frequency analysis
SellerID → Sales accountability and return concentration tracking
BranchID / StoreID → Operational return processing and warehouse impact
VisitorMPID → Logistics handling performance for return pickup/processing

Fields

Field	Type	Description
MainRetID	int	Return transaction ID
OrderID	int	Original order ID
VisitorMPID	int	Delivery agent ID handling return
PersonName	string	Return handler / responsible person
BranchID	int	Branch ID
BranchName	string	Branch name
StoreID	int	Store ID
StoreName	string	Store name
CustID	int	Customer ID
CustTitle	string	Customer name
CustGrpDesc	string	Customer group description
ProdID	int	Product ID
ProdName	string	Product name
GrpDesc	string	Product group
BrandName	string	Brand name
RetQty	decimal	Returned quantity
QtyPackage	int	Units per package/carton
ProdPrice	decimal	Unit price of product
IsPromo	bit	Promotion flag
RetAmount	decimal	Gross return amount
RetDiscount	decimal	Return discount amount
NetRetAmount	decimal	Net return amount after discount
RetDate	string	Return date (YYYY/MM/DD)
FaktorDate	string	Invoice date
SCName	string	Sales channel name
SellerID	int	Salesperson ID
SellerName	string	Salesperson name
Lat	decimal	Customer latitude
Lng	decimal	Customer longitude

Date Format

schedule

Important

All date fields in this view are stored as STRING values in the format YYYY/MM/DD. These fields are not SQL DateTime types and must be converted before performing date-based filtering, sorting, or aggregation.

RetDate → Return date (string format)
FaktorDate → Invoice reference date (string format)

Usage

Return rate analysis
Customer dissatisfaction tracking
Refund and financial reconciliation
Reverse logistics planning
AI anomaly detection in returns

Important Notes

warning

Business Logic Note

Returned items may have different pricing rules depending on promotion status. NetRetAmount is the final valid financial value for accounting and reporting purposes.