📊 SQL View Documentation

Work Order Report
Power BI SQL Query

Complete field-level reference for the PostgreSQL view vw_frd_mrp_1_dashboard — every output column, its Odoo source table, and all calculated formulas explained with technical field names and SQL logic.

View Name
vw_frd_mrp_1_dashboard

Total Columns
49

Source Tables
15

Calculated Fields
16

Report Grain
WO × Output Log × Move Line

🗄️ Source Tables

🏭 MO Fields

⚙️ Workorder Fields

📦 Product Fields

📈 Metrics & KPIs

📋 Output Log Fields

🗄️

Source Tables & Joins

All PostgreSQL / Odoo tables referenced in the view

mrp_workorder

Core driver (alias: wo)
id, name, state
duration_expected
emp_duration, duration
total_actual_workcenter_time
qty_produced, production_id
workcenter_id

mrp_workcenter

alias: wc
name
costs_hour
employee_costs_hour
vaporized_location_id
destruction_location_id

mrp_production

alias: mp
id, name, state
product_qty, qty_producing
product_id
product_uom_id
create_date

product_product

alias: pp
product_tmpl_id
province_code (custom)

product_template

alias: pt
name (JSONB)
categ_id
product_format_id

product_category

alias: pc
src_type_id (custom)
src_category_id (custom)

product_format

alias: pf (custom table)
name

src_type / src_category

alias: st, sc (custom)
name

mrp_output_log

alias: ol
id, state, product_id
workorder_id (FK)

stock_move

alias: sm
id, state
product_uom_qty
location_id, location_dest_id
output_log_id (FK)

stock_move_line

alias: sml
net_weight (custom)
estimated_units (custom)
quantity, move_id (FK)

uom_uom

alias: uu (for sm)
alias: mp_uu (for mp)
name (JSONB)

stock_location

alias: source_loc
alias: dest_loc
name

product_consumption_log

alias: pcl
state (filtered = 'done')
Used in wo_scrap CTE

📐

Report Grain

What does one row represent?

⚠️

One row = One Workorder × One Output Log × One Stock Move Line.
Because of LEFT JOINs on mrp_output_log → stock_move → stock_move_line, a single Workorder produces multiple rows when it has multiple output logs or move lines. Duration and cost fields are protected by ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ol.id) = 1 guards so Power BI SUM aggregations do not double-count.

🏭

Manufacturing Order (MO) Fields

Source: mrp_production (alias: mp)

mp_id Manufacturing Order Internal ID

▼

Source Tablemrp_production

Source Columnmp.id

Data TypeINTEGER

DescriptionPrimary key of the manufacturing order. Use as a unique identifier for Power BI relationships.

mp_number & manufacturing_order MO Reference Number — both identical aliases

▼

Source Columnmp.name

Data TypeVARCHAR

ExampleWH/MO/00123

DescriptionHuman-readable MO reference. Exposed twice so Power BI can use one for display and another for slicers.

mp_state Manufacturing Order Status

▼

Source Columnmp.state

Valuesdraft · confirmed · progress · to_close · done · cancel

DescriptionOdoo selection field. Use in Power BI slicers. Values are technical codes, not labels.

mp_product_qty Planned Quantity on MO

▼

Source Columnmp.product_qty

DescriptionOriginally planned production quantity in MO UoM.

mp_qty_producing Quantity Currently Being Produced

▼

Source Columnmp.qty_producing

DescriptionReal-time quantity being set for production on the MO (in-progress quantity).

mp_product_uom MO Unit of Measure Label

Calculated

▼

Source Tablesmrp_production → uom_uom (alias: mp_uu)

Joinmp.product_uom_id = mp_uu.id

Formula

-- Extracts English label from JSONB column mp_uu.name->>'en_US'

mo_creation_date MO Created On (Timestamp)

▼

Source Columnmp.create_date

Data TypeTIMESTAMP WITH TIME ZONE

DescriptionWhen the MO was created. Use for date filtering and trend analysis in Power BI.

mo_workorder_count Total Workorders in this MO

Calculated

▼

SourceSubquery alias: mo_summary

Formula

-- mo_summary subquery (LEFT JOINed on mp.id = mo_summary.production_id): SELECT production_id, COUNT(id) AS mo_workorder_count, SUM(duration_expected) AS mo_expected_duration FROM mrp_workorder GROUP BY production_id -- In main SELECT: COALESCE(mo_summary.mo_workorder_count, 0)

DescriptionTotal count of workorders across the entire MO. COALESCE ensures no NULLs in Power BI.

mo_expected_duration Sum of All WO Planned Durations for MO (minutes)

Calculated

▼

SourceSubquery alias: mo_summary

Formula

COALESCE(mo_summary.mo_expected_duration, 0.0) -- mo_summary.mo_expected_duration = SUM(duration_expected) FROM mrp_workorder WHERE production_id = mp.id

DescriptionAggregate of all workorder planned durations for this MO. Pre-aggregated in subquery to prevent row multiplication.

⚙️

Workorder Fields

Source: mrp_workorder (wo) · mrp_workcenter (wc)

wo_id Workorder Internal ID

▼

Source Columnwo.id

DescriptionPrimary key of the workorder. Used in ROW_NUMBER() PARTITION BY to deduplicate duration/cost fields across output-log rows.

wo_name & workorder Workorder Operation Name

▼

Source Columnwo.name

DescriptionName of the work operation (e.g. "Assembly", "Quality Check"). Exposed twice as both wo_name and workorder for Power BI flexibility.

wo_state Workorder Status

▼

Source Columnwo.state

Valuespending · ready · progress · done · cancel

wo_qty_produced Quantity Produced at this Workorder Step

▼

Source Columnwo.qty_produced

DescriptionQuantity confirmed as produced at this specific workorder operation step.

wo_expected_duration & duration_expected Planned Duration — Deduplication Guard (minutes)

Calculated

▼

Source Columnwo.duration_expected

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ol.id) = 1 THEN wo.duration_expected ELSE 0.0 END

Why needed?A workorder can have multiple output log rows. Without this guard, SUM in Power BI would multiply the duration. Only the first output-log row (ordered by ol.id) carries the real value; all subsequent rows emit 0.

wo_duration & duration Actual Employee Duration — Dedup Guard (minutes)

Calculated

▼

Source Columnwo.emp_duration

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ol.id) = 1 THEN wo.emp_duration ELSE 0.0 END

DescriptionTotal time employees actually spent on this WO in minutes (emp_duration). Protected from row multiplication by the dedup guard.

total_actual_workcenter_time Actual Machine/Workcenter Time — Dedup Guard (minutes)

Calculated

▼

Source Columnwo.total_actual_workcenter_time

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ol.id) = 1 THEN wo.total_actual_workcenter_time ELSE 0.0 END

DescriptionTotal machine runtime (separate from employee time). Used as the basis for workcenter cost calculation.

workcenter Workcenter Name

▼

Source Tablemrp_workcenter (wc)

Source Columnwc.name

Joinwo.workcenter_id = wc.id (INNER JOIN)

workcenter_workorder_count Total WOs Ever Assigned to this Workcenter

Calculated

▼

SourceSubquery alias: wc_wo_count

Formula

-- wc_wo_count subquery (LEFT JOINed on wc.id): SELECT workcenter_id, COUNT(id) AS workorder_count FROM mrp_workorder GROUP BY workcenter_id -- In main SELECT: COALESCE(wc_wo_count.workorder_count, 0)

DescriptionLifetime total workorders assigned to this workcenter (all MOs). Useful for capacity/load analysis.

📦

Product Filter Fields

Source: product_product · product_template · product_category · product_format · src_type · src_category

product_name Finished Product Name (MO Level)

Product

▼

Join Chainmrp_production → product_product → product_template

Formula

pt.name->>'en_US' -- Extracts English text from JSONB name field on product.template

DescriptionName of the product being manufactured. The ->>'en_US' operator extracts the English translation from Odoo's multilingual JSONB column.

province_code Product Province / Region Code

Product

▼

Source Tableproduct_product (pp)

Source Columnpp.province_code — custom field

DescriptionCustom field added to product.product. Used for regional product filtering in Power BI slicers.

product_format Product Format Classification

Product

▼

Source Tableproduct_format (pf) — custom table

Joinpt.product_format_id = pf.id (LEFT JOIN — nullable)

DescriptionCustom product format classification (e.g. "Bulk", "Retail Pack"). NULL when not configured.

src_type Source Type

Product

▼

Join Chainproduct_template → product_category → src_type

Source Columnst.name

DescriptionCustom source type linked through product category. Provides a classification dimension for Power BI.

src_category Source Category

Product

▼

Join Chainproduct_template → product_category → src_category

Source Columnsc.name

📈

Metrics & KPI Fields

Source: stock_move · stock_move_line · mrp_workcenter — mostly calculated

demand_quantity Planned Move Quantity

Metric

▼

Source Columnsm.product_uom_qty — stock_move

DescriptionPlanned/demanded quantity on the stock move linked to the output log. Represents expected output.

weight_produced Net Weight of Output

Metric

▼

Source Columnsml.net_weight — custom field on stock_move_line

DescriptionActual net weight recorded on the move line (stored in grams). Base for kg and per-kg ratio calculations.

estimated_units Estimated Output Units

Metric

▼

Source Columnsml.estimated_units — custom field on stock_move_line

DescriptionCustom computed unit count on the move line. Used as the denominator in per-unit efficiency ratios.

uom Unit of Measure Label (Stock Move)

Metric

▼

Joinsm.product_uom = uu.id

Formula

uu.name->>'en_US' -- English UoM label from JSONB

quantity_produced_kg Output Weight in Kilograms

Calculated

▼

Formula

sml.net_weight / 1000.0 -- Assumes net_weight is stored in grams; divides by 1000 to get kg

DescriptionConverts raw weight to kilograms for display and per-kg ratios.

labor_mins_per_unit Labor Minutes per Produced Unit

Calculated

▼

Formula

wo.duration / NULLIF(sml.estimated_units, 0.0) -- wo.duration = actual work duration (minutes) from mrp_workorder -- sml.estimated_units = output units on stock_move_line -- NULLIF prevents ÷0 — returns NULL if units = 0

InterpretationEfficiency KPI: labor minutes spent per unit produced. Lower = more efficient.

labor_hours_per_unit Labor Hours per Produced Unit

Calculated

▼

Formula

(wo.duration / 60.0) / NULLIF(sml.estimated_units, 0.0) -- wo.duration / 60.0 converts minutes to hours -- NULLIF guards division-by-zero

labor_hours_per_kg Labor Hours per Kilogram Produced

Calculated

▼

Formula

(wo.duration / 60.0) / NULLIF(sml.net_weight / 1000.0, 0.0) -- Numerator: employee hours (duration in minutes ÷ 60) -- Denominator: kg produced (net_weight grams ÷ 1000) -- NULLIF prevents ÷0 when no weight recorded

total_waste & total_consumption Scrap and Material Consumed (from wo_scrap CTE)

Calculated

▼

SourceSubquery alias: wo_scrap (LEFT JOINed on wo.id)

Formula

-- wo_scrap joins: mrp_workorder, mrp_workcenter, stock_move, -- stock_move_line, product_consumption_log (state = 'done') total_waste = COALESCE(SUM( CASE WHEN sml_sub.location_dest_id IN (wc_sub.vaporized_location_id, wc_sub.destruction_location_id) THEN sml_sub.quantity ELSE 0 END ), 0) total_consumption = COALESCE(SUM( CASE WHEN sm_sub.state = 'done' AND sml_sub.location_dest_id NOT IN ( COALESCE(wc_sub.vaporized_location_id, -1), COALESCE(wc_sub.destruction_location_id, -1)) THEN sml_sub.quantity ELSE 0 END ), 0)

total_wasteQty moved to vaporized_location_id or destruction_location_id on the workcenter = scrapped material.

total_consumptionDone stock-move quantities NOT going to waste locations = legitimately consumed material.

waste_loss_pct Waste Loss Percentage

Calculated

▼

Formula

(wo_scrap.total_waste / NULLIF(wo_scrap.total_consumption, 0.0)) * 100.0 -- total_waste = qty to destruction / vaporize locations -- total_consumption = total done qty (non-waste) -- × 100.0 converts ratio to percentage -- NULLIF prevents ÷0 when no consumption recorded

Power BI TipFormat as %. 0% = no waste. 100% = all consumed was wasted.

performance_pct Time Performance % — Expected vs Actual

Calculated

▼

Formula

((wo.duration_expected - wo.duration) / NULLIF(wo.duration_expected, 0.0)) * 100.0 -- wo.duration_expected = planned duration in minutes -- wo.duration = actual duration in minutes -- Positive % → finished faster than planned (good) -- Negative % → took longer than planned (bad) -- NULLIF guards ÷0 when no expected duration configured

Example+20% = completed in 80% of expected time. −30% = took 130% of expected time.

labor_cost Employee Labor Cost for this Workorder

Calculated

▼

Formula

COALESCE(wc.employee_costs_hour, 0.0) * (wo.emp_duration / 60.0) -- wc.employee_costs_hour = hourly employee rate on mrp_workcenter -- wo.emp_duration / 60.0 = actual employee hours worked -- COALESCE treats NULL rate as 0 (no cost if rate not set)

Source Fieldsmrp_workcenter.employee_costs_hour × mrp_workorder.emp_duration

workcenter_cost Machine / Workcenter Operating Cost

Calculated

▼

Formula

COALESCE(wc.costs_hour, 0.0) * (wo.total_actual_workcenter_time / 60.0) -- wc.costs_hour = machine hourly rate on mrp_workcenter -- wo.total_actual_workcenter_time = actual machine time in minutes

Source Fieldsmrp_workcenter.costs_hour × mrp_workorder.total_actual_workcenter_time

total_operation_cost Blended Cost Rate (Labor + Machine) per Employee Hour

Calculated

▼

Formula

( (COALESCE(wc.employee_costs_hour, 0.0) * (wo.emp_duration / 60.0)) + (COALESCE(wc.costs_hour, 0.0) * (wo.total_actual_workcenter_time / 60.0)) ) / NULLIF(wo.emp_duration / 60.0, 0.0) -- Numerator: labor_cost + workcenter_cost (total spend in currency) -- Denominator: employee hours worked -- Result: effective blended hourly cost rate -- NULLIF prevents ÷0 when emp_duration = 0

InterpretationEffective combined cost rate (currency/hour) when both employee and machine costs are merged.

fully_productive_time OEE — Fully Productive Time (minutes)

Calculated

▼

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ol.id) = 1 THEN LEAST( COALESCE(wo.emp_duration, 0.0), COALESCE(wo.duration_expected, 0.0) ) ELSE 0.0 END -- LEAST picks the smaller of actual vs. planned minutes -- = time that was productive AND within the planned window -- Dedup guard: only first output-log row per WO carries value

DescriptionThe portion of actual time that falls within the planned window — truly productive time. Used for OEE-style analysis in Power BI.

reduce_speed OEE — Speed Loss / Overtime (minutes)

Calculated

▼

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ol.id) = 1 THEN GREATEST( 0.0, COALESCE(wo.emp_duration, 0.0) - COALESCE(wo.duration_expected, 0.0) ) ELSE 0.0 END -- actual − planned, floored at 0 via GREATEST(0.0, ...) -- If actual <= planned → 0 (no speed loss) -- If actual > planned → overtime minutes (speed loss)

DescriptionMinutes taken beyond planned duration. Represents inefficiency / reduced speed for OEE loss analysis.

📋

Output Log & Stock Move Fields

Source: mrp_output_log · product_product · product_template · stock_move · stock_location

Alias	Source Column	Source Table	Description
output_log_id	ol.id	mrp_output_log	PK of the output log entry linked to this workorder
output_product_id	ol.product_id	mrp_output_log	Product recorded in the output log (may differ from MO product)
output_log_state	ol.state	mrp_output_log	State of the output log entry (e.g. draft, done)
product_id	ol_pp.id	product_product (via ol)	product.product ID for the output log product
product_template_id	ol_pt.id	product_template (via ol_pp)	product.template ID for the output log product
product_template_name	ol_pt.name->>'en_US'	product_template (via ol_pp)	English name of the output log product template (JSONB extraction)
stock_move_id	sm.id	stock_move	PK of the stock move linked to the output log
stock_move_state	sm.state	stock_move	State: draft / confirmed / done / cancel
location_id	sm.location_id	stock_move	Source location ID (FK to stock_location)
location_name	source_loc.name	stock_location (alias: source_loc)	Human-readable name of the source stock location
location_dest_id	sm.location_dest_id	stock_move	Destination location ID (FK to stock_location)
location_dest_name	dest_loc.name	stock_location (alias: dest_loc)	Human-readable name of the destination stock location

🗂️

Full Column Quick Reference

All 49 output columns at a glance

#	Alias	Type	Category	Formula / Source Summary
1	mp_id	INT	MO	mp.id
2	mp_number	VARCHAR	MO	mp.name
3	manufacturing_order	VARCHAR	MO	mp.name (duplicate alias)
4	mp_state	VARCHAR	MO	mp.state
5	mp_product_qty	NUMERIC	MO	mp.product_qty
6	mp_qty_producing	NUMERIC	MO	mp.qty_producing
7	mp_product_uom	VARCHAR	MO	mp_uu.name->>'en_US'
8	mo_creation_date	TIMESTAMPTZ	MO	mp.create_date
9	wo_id	INT	WO	wo.id
10	wo_workcenter_id	INT	WO	wo.workcenter_id
11	wo_name	VARCHAR	WO	wo.name
12	workorder	VARCHAR	WO	wo.name (duplicate alias)
13	wo_state	VARCHAR	WO	wo.state
14	wo_qty_produced	NUMERIC	WO	wo.qty_produced
15	wo_expected_duration	NUMERIC	WO Calc	CASE WHEN ROW_NUMBER()=1 THEN wo.duration_expected ELSE 0
16	mo_expected_duration	NUMERIC	MO Calc	COALESCE(mo_summary.mo_expected_duration, 0)
17	duration_expected	NUMERIC	WO Calc	Same as wo_expected_duration
18	wo_duration	NUMERIC	WO Calc	CASE WHEN ROW_NUMBER()=1 THEN wo.emp_duration ELSE 0
19	duration	NUMERIC	WO Calc	Same as wo_duration
20	total_actual_workcenter_time	NUMERIC	WO Calc	CASE WHEN ROW_NUMBER()=1 THEN wo.total_actual_workcenter_time ELSE 0
21	workcenter	VARCHAR	WO	wc.name
22	product_name	VARCHAR	Product	pt.name->>'en_US'
23	province_code	VARCHAR	Product	pp.province_code (custom)
24	product_format	VARCHAR	Product	pf.name (custom table)
25	src_type	VARCHAR	Product	st.name (custom table)
26	src_category	VARCHAR	Product	sc.name (custom table)
27	demand_quantity	NUMERIC	Metric	sm.product_uom_qty
28	weight_produced	NUMERIC	Metric	sml.net_weight (custom)
29	estimated_units	NUMERIC	Metric	sml.estimated_units (custom)
30	uom	VARCHAR	Metric	uu.name->>'en_US'
31	quantity_produced_kg	NUMERIC	KPI	sml.net_weight / 1000.0
32	workcenter_workorder_count	INT	KPI	COALESCE(COUNT(wo) per wc, 0)
33	mo_workorder_count	INT	KPI	COALESCE(COUNT(wo) per MO, 0)
34	labor_mins_per_unit	NUMERIC	KPI	wo.duration / NULLIF(sml.estimated_units, 0)
35	labor_hours_per_unit	NUMERIC	KPI	(wo.duration/60) / NULLIF(sml.estimated_units, 0)
36	labor_hours_per_kg	NUMERIC	KPI	(wo.duration/60) / NULLIF(sml.net_weight/1000, 0)
37	total_waste	NUMERIC	KPI	SUM qty to waste locations (wo_scrap subquery)
38	total_consumption	NUMERIC	KPI	SUM done qty NOT to waste locations (wo_scrap)
39	waste_loss_pct	NUMERIC	KPI	(total_waste / NULLIF(total_consumption,0)) × 100
40	performance_pct	NUMERIC	KPI	((duration_expected − duration) / NULLIF(duration_expected,0)) × 100
41	labor_cost	NUMERIC	Cost	COALESCE(wc.employee_costs_hour,0) × (emp_duration/60)
42	workcenter_cost	NUMERIC	Cost	COALESCE(wc.costs_hour,0) × (total_actual_workcenter_time/60)
43	total_operation_cost	NUMERIC	Cost	(labor_cost + workcenter_cost) / NULLIF(emp_duration/60, 0)
44	fully_productive_time	NUMERIC	OEE	CASE ROW=1: LEAST(emp_duration, duration_expected) ELSE 0
45	reduce_speed	NUMERIC	OEE	CASE ROW=1: GREATEST(0, emp_duration − duration_expected) ELSE 0
46	output_log_id	INT	Log	ol.id
47	output_product_id	INT	Log	ol.product_id
48	output_log_state	VARCHAR	Log	ol.state
49	product_id … location_dest_name	MIXED	Log	product_product / product_template / stock_move / stock_location columns

Work Order ReportPower BI SQL Query

Source Tables & Joins

mrp_workorder

mrp_workcenter

mrp_production

product_product

product_template

product_category

product_format

src_type / src_category

mrp_output_log

stock_move

stock_move_line

uom_uom

stock_location

product_consumption_log

Report Grain

Manufacturing Order (MO) Fields

Workorder Fields

Product Filter Fields

Metrics & KPI Fields

Output Log & Stock Move Fields

Full Column Quick Reference

Work Order Report
Power BI SQL Query