Below Min Price Sales

Finds recent Amazon Sales Orders sold below the Proxy SKU's minimum price floor and estimates the loss — a post-sale leak detector for pricing that slipped under the floor.

Purpose

Pricing / Sales users auditing whether any Amazon order actually shipped below its computed min price.
Answers: which recent Amazon sales went out under floor, by how much, and what did it cost us?
Ranks the worst offenders first by estimated loss so the biggest leaks surface immediately.
Defaults to showing only below-floor lines within the last few days.

How to Access

Desk > Reports > Below Min Price Sales, or /app/query-report/Below Min Price Sales. Report type: Script Report. Reference DocType: Sales Order.

Filters

Filter	Type	Required	Description
Days Back	Int	No	Look-back window in days. Defaults to 3. Orders are filtered by `creation > now − days_back`.
Only Below Min	Check	No	Default 1 (on). When checked, keeps only rows where `deficit > 0` (sold under floor).
Minimum Deficit	Currency	No	When set, keeps only rows where `deficit >= minimum_deficit`.

Columns

Column	Type	Description
Sales Order	Link → Sales Order	The submitted Amazon Sales Order.
Created	Datetime	Sales Order `creation`.
Item	Link → Item	`item_code` from the Sales Order line.
UOM	Data	Line UOM.
Proxy SKU	Link → Proxy SKU	The Proxy SKU whose floor was used (the one with the lowest min price for this item+UOM).
Qty	Float	Line quantity sold.
Sold Rate	Currency	Line `rate` (price actually sold at).
Min Price	Currency	The applicable `current_min_price` floor.
Deficit	Currency	`min_price − sold_rate`, rounded to 2 dp. Positive = sold under floor.
Est. Loss	Currency	`max(deficit, 0) × qty`, rounded to 2 dp.

How It Works

The report pulls submitted Amazon Sales Order lines, looks up each item+UOM's minimum price floor from active pricing, and computes the per-line deficit and loss.

flowchart LR
  SO[Sales Order Item ⋈ Sales Order<br/>docstatus=1, amazon_order_id set,<br/>creation > now − days_back] --> K[item_code + uom keys]
  K --> MP[min price map]
  PR[Amazon Product Pricing ⋈ Proxy SKU<br/>is_active=1, current_min_price > 0] --> MP
  MP -->|lowest min per item+uom| B[build rows]
  SO --> B
  B -->|deficit = min − rate<br/>loss = max deficit,0 × qty| FIL[filters: only_below_min, minimum_deficit]
  FIL --> SORT[sort by est. loss desc]
  SORT --> OUT[report rows]

Order side: Sales Order Item joined to Sales Order where docstatus = 1, amazon_order_id IS NOT NULL, and creation > now − days_back. Only Amazon orders are considered.
Floor side: Amazon Product Pricing joined to Proxy SKU where both are active and current_min_price > 0. Because multiple active proxies can share an item+UOM, the report keeps the lowest min price for that key — the most conservative comparison, so a sale is only flagged when it is below every proxy's floor.
Lines whose item+UOM has no mapped floor are dropped.
deficit = min_price − sold_rate; estimated_loss = max(deficit, 0) × qty. Rows are sorted by estimated loss, highest first.
Only Below Min is on by default; turn it off to see at-or-above-floor lines too (those have deficit <= 0).

Conditional formatting (from the report JS)

Any row with deficit > 0 is rendered in red (--red-600) across the row's cells.

Notes

Only submitted Sales Orders that carry an amazon_order_id are scanned — non-Amazon orders are excluded by design.
A non-empty report means real below-floor sales occurred; pair it with Amazon Pricing Push Readiness to find pricings that should have been blocked before the push.
Related: Amazon Pricing Push Monitor for the feed-submission side of the same pipeline.

Navgold