📊 SQL View Documentation

Employee & Resource
Efficiency Report

Complete field-level reference for the PostgreSQL view mrp_19_emp_res_eff — every output column, its Odoo source table and CTE, and all calculated formulas explained with technical field names and SQL logic. Covers Employee KPIs, Workcenter KPIs, and OEE calculations.

View Name
mrp_19_emp_res_eff

Total Columns
39

CTEs
4

Source Tables
16

Calculated Fields
20

Report Grain
1 row per Employee × Workorder

🔗 CTE Architecture

🗄️ Source Tables

🔍 Filter Fields

👤 Employee KPIs

⚙️ Workcenter KPIs

📈 OEE Fields

🗂️ Quick Reference

🔗

CTE Architecture

4 Common Table Expressions that pre-aggregate data before the main SELECT

ℹ️

The view uses a CTE-first pattern — all heavy aggregation happens in 4 named CTEs, and the main SELECT only joins and formats the results. This prevents row multiplication and ensures all metrics are pre-aggregated at the correct grain.

CTE 1 — employee_productivity

Aggregates actual working minutes per employee per workorder from the workcenter productivity log. Captures the resource calendar the employee was working under.

SELECT wp.workorder_id, wp.employee_id, SUM(wp.duration) AS employee_working_minutes, MAX(wp.resource_calendar_id) AS resource_calendar_id FROM mrp_workcenter_productivity wp WHERE wp.employee_id IS NOT NULL GROUP BY wp.workorder_id, wp.employee_id -- Grain: one row per (employee × workorder) -- Source: mrp_workcenter_productivity -- Key output: employee_working_minutes, resource_calendar_id

CTE 2 — workorder_employee_count

Counts the number of distinct employees who logged time on each workorder. Used to split planned_runtime proportionally across employees.

SELECT workorder_id, COUNT(DISTINCT employee_id) AS employee_count FROM employee_productivity -- reads CTE 1 GROUP BY workorder_id -- Grain: one row per workorder -- Key output: employee_count

CTE 3 — workorder_output

Aggregates finished product outputs (estimated_units, net_weight, lot names) from stock move lines. Resolves workorder_id from both stock_move and mrp_output_log to handle cases where workorder_id on the move is NULL.

SELECT COALESCE(sm.workorder_id, ol.workorder_id) AS workorder_id, sm.product_id, SUM(sml.estimated_units) AS estimated_units, SUM(sml.net_weight) AS net_weight, STRING_AGG(DISTINCT COALESCE(sml.lot_name, lot.name), ', ') AS lot_name FROM stock_move sm JOIN stock_move_line sml ON sml.move_id = sm.id LEFT JOIN stock_lot lot ON sml.lot_id = lot.id LEFT JOIN mrp_output_log ol ON sm.output_log_id = ol.id WHERE (sm.workorder_id IS NOT NULL OR ol.workorder_id IS NOT NULL) AND sm.production_id IS NOT NULL AND sm.raw_material_production_id IS NULL -- finished goods only, exclude raw material returns GROUP BY COALESCE(sm.workorder_id, ol.workorder_id), sm.product_id -- Grain: one row per (workorder × product) -- Sources: stock_move, stock_move_line, stock_lot, mrp_output_log

CTE 4 — employee_output

Aggregates quantity and weight produced per employee per workorder directly from the output log (only 'done' state entries). Provides the employee-level production figures.

SELECT workorder_id, employee_id, COALESCE(SUM(estimated_units), 0.0) AS quantity_produced, COALESCE(SUM(net_weight), 0.0) AS weight_produced FROM mrp_output_log WHERE state = 'done' GROUP BY workorder_id, employee_id -- Grain: one row per (employee × workorder) -- Source: mrp_output_log (done entries only) -- Key output: quantity_produced, weight_produced

🗄️

Source Tables & Joins

All PostgreSQL / Odoo tables referenced in the view

mrp_workorder

Core driver (alias: wo)
INNER JOIN via employee_productivity
id, name, state
duration_expected
total_actual_workcenter_time
production_id, operation_id
workcenter_id

mrp_production

alias: mp
LEFT JOIN on mp.id = wo.production_id
id, name
product_id
create_date

mrp_routing_workcenter

alias: rw
LEFT JOIN on wo.operation_id
time_cycle_manual (Ideal Cycle Time)

mrp_workcenter_productivity

alias: wp (in CTE 1)
workorder_id, employee_id
duration (minutes)
resource_calendar_id

mrp_output_log

Used in CTE 4 directly
Used in CTE 3 (LEFT JOIN)
workorder_id, employee_id
estimated_units, net_weight
state (filtered = 'done')

mrp_workcenter

alias: wc
LEFT JOIN on wo.workcenter_id
name
resource_calendar_id

hr_employee

alias: emp
LEFT JOIN on ep.employee_id
id, name
resource_id (FK)

resource_resource

alias: res
LEFT JOIN on emp.resource_id
calendar_id (fallback for rc_emp)

resource_calendar

alias: rc_emp (employee calendar)
alias: rc_wc (workcenter calendar)
hours_per_day
rc_emp JOIN: COALESCE(ep.resource_calendar_id, res.calendar_id, wc.resource_calendar_id)

stock_move

alias: sm (in CTE 3)
workorder_id, output_log_id
product_id, production_id
raw_material_production_id

stock_move_line

alias: sml (in CTE 3)
estimated_units (custom)
net_weight (custom)
lot_name, lot_id, move_id

stock_lot

alias: lot (in CTE 3)
LEFT JOIN on sml.lot_id
name (fallback for lot_name)

product_product

alias: pp
JOIN: COALESCE(wo_output.product_id, mp.product_id)
product_tmpl_id
province_code (custom)

product_template

alias: pt
JOIN on pp.product_tmpl_id
name (JSONB)
categ_id, product_format_id

product_format

alias: pf (custom table)
LEFT JOIN on pt.product_format_id
name

src_type / src_category

alias: st, sc (custom tables)
Via product_category → pc
name

📐

Report Grain

What does one row represent?

⚠️

One row = One Employee × One Workorder.
The view is driven by INNER JOIN employee_productivity ep ON ep.workorder_id = wo.id. This means only workorders that have at least one employee productivity log entry appear. Each unique (employee, workorder) pair produces one row.

Workcenter-level fields (runtime_minutes, workcenter_quantity_produced, units_per_minute, units_per_hour, machine_utilization_pct, total_available_machine_time, workcenter_efficiency_pct) are protected by ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ep.employee_id) = 1 guards so that SUM in Power BI does not double-count machine-level metrics across multiple employees on the same WO.

🔍

Filter / Dimension Fields

Identification and slicing fields — direct column reads

employee_name Full Name of the Employee

Dimension

▼

Source Tablehr_employee (emp)

Source Columnemp.name

Joinemp.id = ep.employee_id (LEFT JOIN)

DescriptionFull name of the employee who logged time on the workorder. Primary slicer for employee-level analysis.

workorder_name Workorder / Operation Name

Dimension

▼

Source Tablemrp_workorder (wo)

Source Columnwo.name

DescriptionName of the manufacturing operation (e.g. "Milling", "Drying", "Packaging"). Also exposed as work_order in the workcenter section.

mo_name & manufacturing_order Manufacturing Order Reference

Dimension

▼

Source Tablemrp_production (mp)

Source Columnmp.name

Joinmp.id = wo.production_id (LEFT JOIN)

DescriptionManufacturing order reference (e.g. WH/MO/00214). Exposed twice — as mo_name for employee section and manufacturing_order for workcenter section.

workcenter_name & workcenter Workcenter Where Operation Was Performed

Dimension

▼

Source Tablemrp_workcenter (wc)

Source Columnwc.name

Joinwc.id = wo.workcenter_id (LEFT JOIN)

product_name & product Finished Product Name

Calculated

▼

Join Chainproduct_product → product_template

Product ResolutionCOALESCE(wo_output.product_id, mp.product_id)

Formula

pt.name->>'en_US' -- product_product joined on: -- COALESCE(wo_output.product_id, mp.product_id) -- First tries the actual output product from workorder_output CTE -- Falls back to MO final product if no output stock moves exist yet

DescriptionResolves to the specific product produced at the workorder level (from workorder_output CTE), falling back to the MO-level finished product. Exposed twice for filtering flexibility.

province_code Product Province / Region Code

Dimension

▼

Source Columnpp.province_code — custom field on product_product

product_format Product Format Classification

Dimension

▼

Source Tableproduct_format (pf) — custom table

Joinpt.product_format_id = pf.id (LEFT JOIN)

src_type & src_category Source Type & Category

Dimension

▼

Join Chainproduct_template → product_category → src_type / src_category

Source Columnsst.name, sc.name

lot_name Lot / Batch Name of the Output

Dimension

▼

SourceCTE 3: workorder_output

Formula

-- In workorder_output CTE: STRING_AGG(DISTINCT COALESCE(sml.lot_name, lot.name), ', ') -- sml.lot_name = stored lot name on move line (custom field) -- lot.name = name from stock_lot (fallback via sml.lot_id) -- COALESCE picks whichever is available first -- STRING_AGG concatenates all distinct lot names with ', '

DescriptionComma-separated list of all distinct lot/batch names produced on this workorder. Useful for traceability.

mo_creation_date MO Created On

Dimension

▼

Source Columnmp.create_date

Data TypeTIMESTAMP WITH TIME ZONE

DescriptionWhen the manufacturing order was created. Primary date dimension for time-series and trend analysis in Power BI.

👤

Employee Resource Efficiency Fields

One value per employee per workorder — no dedup guard needed

quantity_produced Units Produced by This Employee on This WO

Calculated

▼

SourceCTE 4: employee_output (alias: eo)

Source Tablemrp_output_log

Formula

COALESCE(eo.quantity_produced, 0.0) -- eo.quantity_produced = COALESCE(SUM(estimated_units), 0.0) -- from mrp_output_log WHERE state = 'done' -- Joined on: eo.workorder_id = wo.id AND eo.employee_id = emp.id -- COALESCE outer guard for unmatched employees

DescriptionActual finished units recorded by this employee on this workorder. Sourced from done output log entries.

weight_produced Net Weight Produced by This Employee (grams)

Calculated

▼

SourceCTE 4: employee_output (alias: eo)

Formula

COALESCE(eo.weight_produced, 0.0) -- eo.weight_produced = COALESCE(SUM(net_weight), 0.0) -- from mrp_output_log WHERE state = 'done'

employee_working_minutes Actual Minutes Logged by This Employee

Employee

▼

SourceCTE 1: employee_productivity (alias: ep)

Formula

ep.employee_working_minutes -- = SUM(wp.duration) FROM mrp_workcenter_productivity -- WHERE wp.employee_id IS NOT NULL -- GROUP BY wp.workorder_id, wp.employee_id

DescriptionTotal minutes the employee was logged as active on this workorder's productivity timer. Direct input for all efficiency ratios.

employee_working_hours Working Minutes Converted to Hours

Calculated

▼

Formula

ep.employee_working_minutes / 60.0 -- Simple conversion: minutes ÷ 60 = hours

units_per_employee_per_hour Throughput per Active Employee Labor Hour

Calculated

▼

Formula

CASE WHEN COALESCE(ep.employee_working_minutes, 0) > 0 THEN COALESCE(eo.quantity_produced, 0.0) / (ep.employee_working_minutes / 60.0) ELSE NULL END -- Numerator: eo.quantity_produced (done units from employee_output CTE) -- Denominator: employee hours worked (emp_working_minutes / 60) -- Returns NULL (not 0) when no time logged — prevents distorting averages

InterpretationHigher = more units per hour of employee labor. Key individual productivity KPI.

standard_time Standard (Planned) Duration for the Operation (minutes)

Employee

▼

Source Tablemrp_workorder (wo)

Source Columnwo.duration_expected

DescriptionPlanned/standard time set on the workorder routing. The benchmark used in efficiency and availability calculations.

actual_time Actual Machine Runtime for the Operation (minutes)

Employee

▼

Source Tablemrp_workorder (wo)

Source Columnwo.total_actual_workcenter_time

DescriptionActual workcenter machine runtime in minutes. Used in OEE availability and performance calculations.

employee_efficiency_pct Employee Time Efficiency Percentage

Calculated

▼

Formula

CASE WHEN COALESCE(ep.employee_working_minutes, 0) > 0 THEN (wo.duration_expected / ep.employee_working_minutes) * 100.0 ELSE NULL END -- wo.duration_expected = standard/planned minutes (mrp_workorder) -- ep.employee_working_minutes = actual minutes logged by this employee (CTE 1) -- > 100% → employee finished faster than planned (efficient) -- < 100% → employee took longer than planned (inefficient) -- Returns NULL when no time logged

Interpretation>100% = faster than standard. <100% = slower than standard. Measures how well the employee performed against the routing target.

total_available_labour_time Employee's Daily Shift Hours from Resource Calendar

Employee

▼

Source Tableresource_calendar (alias: rc_emp)

Source Columnrc_emp.hours_per_day

Calendar Resolution

LEFT JOIN resource_calendar rc_emp ON rc_emp.id = COALESCE( ep.resource_calendar_id, -- from mrp_workcenter_productivity (CTE 1) res.calendar_id, -- from resource_resource linked to hr_employee wc.resource_calendar_id -- workcenter calendar as final fallback )

DescriptionDaily available working hours from the employee's resource calendar. Used as the denominator in labour utilization. Falls back through 3 sources to find the best available calendar.

labour_utilization_pct Employee Labour Utilization Percentage

Calculated

▼

Formula

CASE WHEN COALESCE(rc_emp.hours_per_day, 0) > 0 THEN ((ep.employee_working_minutes / 60.0) / rc_emp.hours_per_day) * 100.0 ELSE NULL END -- ep.employee_working_minutes / 60.0 = actual employee hours worked -- rc_emp.hours_per_day = scheduled available hours (resource_calendar) -- Returns NULL when no calendar configured

Interpretation100% = employee fully utilized all scheduled shift hours. >100% = overtime. Measures scheduling efficiency.

attendance_to_output_ratio Throughput per Standard 6-Day Work Week

Calculated

▼

Formula

COALESCE(eo.quantity_produced, 0.0) / 6.0 -- Divides employee's quantity_produced by 6.0 -- 6.0 = assumed scheduled work days per week (hard-coded constant) -- Provides a daily production rate proxy

DescriptionMeasures daily production throughput by dividing total units by the 6-day scheduled work week constant. A simple proxy for daily output rate per employee.

⚙️

Workcenter Resource Efficiency Fields

Machine-level KPIs — protected by ROW_NUMBER() dedup guard to prevent double-counting across employees

⚠️

Fields marked with ROW_NUMBER guard emit the real value only for the first employee record (ordered by ep.employee_id) in each workorder partition, and 0.0 for all subsequent employees. This ensures Power BI SUM gives the correct per-workorder total even when multiple employees share one WO.

workcenter_quantity_produced Total WO Output Units — ROW_NUMBER Guard

Calculated

▼

SourceCTE 3: workorder_output (alias: wo_output)

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ep.employee_id) = 1 THEN wo_output.estimated_units ELSE 0.0 END -- wo_output.estimated_units = SUM(sml.estimated_units) -- from workorder_output CTE (stock_move_line) -- Only first employee row per WO carries the value

DescriptionTotal units produced at the workorder (machine) level. Assigned to only the first employee row to prevent double-counting when multiple employees worked on the same WO.

runtime_minutes Actual Machine Runtime — ROW_NUMBER Guard (minutes)

Calculated

▼

Source Columnwo.total_actual_workcenter_time

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ep.employee_id) = 1 THEN wo.total_actual_workcenter_time ELSE 0.0 END -- wo.total_actual_workcenter_time = machine-level runtime from mrp_workorder

planned_runtime Planned Runtime Split Proportionally by Employee Count (minutes)

Calculated

▼

Formula

CASE WHEN COALESCE(wec.employee_count, 0) > 0 THEN wo.duration_expected / wec.employee_count ELSE 0.0 END -- wo.duration_expected = planned/standard minutes (mrp_workorder) -- wec.employee_count = COUNT(DISTINCT employee_id) from workorder_employee_count CTE (CTE 2) -- Divides the total planned time equally among all employees on the WO -- Returns 0.0 when no employees counted

DescriptionEach employee's fair share of the planned operation time. Used to assess individual contribution against the proportional plan.

workcenter_efficiency_pct Workcenter Time Efficiency % — ROW_NUMBER Guard

Calculated

▼

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ep.employee_id) = 1 AND COALESCE(wo.total_actual_workcenter_time, 0) > 0 THEN (wo.duration_expected / wo.total_actual_workcenter_time) * 100.0 ELSE 0.0 END -- wo.duration_expected = planned minutes -- wo.total_actual_workcenter_time = actual machine minutes -- > 100% → machine ran faster than planned -- < 100% → machine ran slower than planned

units_per_minute Machine Throughput in Units per Minute — ROW_NUMBER Guard

Calculated

▼

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ep.employee_id) = 1 AND COALESCE(wo.total_actual_workcenter_time, 0) > 0 THEN wo_output.estimated_units / wo.total_actual_workcenter_time ELSE 0.0 END -- Numerator: wo_output.estimated_units (CTE 3: SUM of output units) -- Denominator: wo.total_actual_workcenter_time (minutes)

units_per_hour Machine Throughput in Units per Hour — ROW_NUMBER Guard

Calculated

▼

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ep.employee_id) = 1 AND COALESCE(wo.total_actual_workcenter_time, 0) > 0 THEN wo_output.estimated_units / (wo.total_actual_workcenter_time / 60.0) ELSE 0.0 END -- Numerator: wo_output.estimated_units -- Denominator: actual machine hours (total_actual_workcenter_time / 60)

total_available_machine_time Machine Daily Operating Limit from Calendar — ROW_NUMBER Guard (hours)

Calculated

▼

Source Tableresource_calendar (alias: rc_wc)

Joinrc_wc.id = wc.resource_calendar_id

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ep.employee_id) = 1 THEN rc_wc.hours_per_day ELSE 0.0 END -- rc_wc.hours_per_day = daily available hours from workcenter resource_calendar -- Only first employee row per WO carries the calendar hours

DescriptionMachine's total available hours per day per its resource calendar. Used as the denominator for machine utilization.

machine_utilization_pct Machine Utilization Percentage — ROW_NUMBER Guard

Calculated

▼

Formula

CASE WHEN ROW_NUMBER() OVER (PARTITION BY wo.id ORDER BY ep.employee_id) = 1 AND COALESCE(rc_wc.hours_per_day, 0) > 0 THEN ((wo.total_actual_workcenter_time / 60.0) / rc_wc.hours_per_day) * 100.0 ELSE 0.0 END -- Numerator: wo.total_actual_workcenter_time / 60 = machine hours used -- Denominator: rc_wc.hours_per_day = calendar available hours -- × 100 = percentage

Interpretation100% = machine ran the entire available shift. Measures scheduling vs actual runtime.

📈

Overall Equipment Effectiveness (OEE) Fields

OEE = Availability × Performance × Quality

ℹ️

OEE is the industry-standard manufacturing KPI. These fields provide the three OEE components and the combined score. Note: These fields are NOT protected by ROW_NUMBER guards — they appear on every employee row. Aggregate with caution: use MAX or take first-employee-row filtering in Power BI measures.

ideal_cycle_time Manual Ideal Cycle Time from Routing (minutes per unit)

OEE

▼

Source Tablemrp_routing_workcenter (rw)

Source Columnrw.time_cycle_manual

Joinrw.id = wo.operation_id (LEFT JOIN)

Formula

COALESCE(rw.time_cycle_manual, 0.0) -- time_cycle_manual = manually defined cycle time on the routing operation -- Used in OEE Performance calculation -- COALESCE defaults to 0 when not set

DescriptionThe designed/ideal minutes per unit as defined on the routing. Represents the theoretical maximum speed of the operation.

availability_pct OEE Availability Component (%)

Calculated

▼

Formula

CASE WHEN COALESCE(wo.duration_expected, 0) > 0 THEN (wo.total_actual_workcenter_time / wo.duration_expected) * 100.0 ELSE 100.0 END -- Numerator: wo.total_actual_workcenter_time = actual machine runtime (minutes) -- Denominator: wo.duration_expected = planned runtime (minutes) -- Defaults to 100% when no planned time set (not penalised) -- OEE standard: (Run Time / Planned Production Time) × 100

OEE RoleMeasures what fraction of the planned production time the machine was actually running. Losses = unplanned stops, breakdowns.

performance_pct OEE Performance Component (%)

Calculated

▼

Formula

CASE WHEN COALESCE(wo.total_actual_workcenter_time, 0.0) > 0 THEN ( (COALESCE(rw.time_cycle_manual, 0.0) * COALESCE(eo.quantity_produced, 0.0)) / wo.total_actual_workcenter_time ) * 100.0 ELSE 100.0 END -- Numerator: ideal_cycle_time × units_produced -- = theoretical time that should have been needed -- Denominator: wo.total_actual_workcenter_time (actual time used) -- rw.time_cycle_manual = ideal cycle time per unit (mrp_routing_workcenter) -- eo.quantity_produced = done units by this employee (employee_output CTE) -- OEE standard: (Ideal Cycle Time × Total Count) / Run Time × 100

OEE RoleMeasures running speed vs ideal speed. Losses = slow cycles, minor stops. 100% = machine ran at designed speed throughout.

quality_pct OEE Quality Component (%) — Currently Fixed at 100%

OEE

▼

Formula

CASE WHEN COALESCE(wo_output.estimated_units, 0.0) > 0 THEN (wo_output.estimated_units / wo_output.estimated_units) * 100.0 ELSE 100.0 END -- (estimated_units / estimated_units) always = 1.0 → 100% -- Assumption: all produced units are good (no defects tracked yet) -- When a defect/scrap count becomes available, replace denominator -- with total_units_produced (good + defective)

OEE RoleCurrently assumes all output is defect-free (Good Units = Total Units). This field should be updated once defect/rework tracking is implemented in Odoo.

oee_pct Overall OEE Score — Availability × Performance × Quality × 100

Calculated

▼

Formula

( -- Availability ratio: (CASE WHEN COALESCE(wo.duration_expected, 0) > 0 THEN wo.total_actual_workcenter_time / wo.duration_expected ELSE 1.0 END) * -- Performance ratio: (CASE WHEN COALESCE(wo.total_actual_workcenter_time, 0.0) > 0 THEN (COALESCE(rw.time_cycle_manual, 0.0) * COALESCE(eo.quantity_produced, 0.0)) / wo.total_actual_workcenter_time ELSE 1.0 END) * -- Quality ratio: (CASE WHEN COALESCE(wo_output.estimated_units, 0.0) > 0 THEN wo_output.estimated_units / wo_output.estimated_units ELSE 1.0 END) ) * 100.0 -- Each component defaults to 1.0 (100%) when its denominator is 0 -- Final result: A × P × Q × 100 = OEE percentage -- Industry benchmark: World-class OEE ≥ 85%

Interpretation85%+ = World-class. 60–85% = Typical. <60% = Significant losses. Since quality is always 100%, OEE effectively equals Availability × Performance here.

🗂️

Full Column Quick Reference

All 34 output columns at a glance

#	Alias	Type	Section	Source / Formula Summary
1	employee_name	VARCHAR	Dimension	emp.name (hr_employee)
2	workorder_name	VARCHAR	Dimension	wo.name (mrp_workorder)
3	mo_name	VARCHAR	Dimension	mp.name (mrp_production)
4	workcenter_name	VARCHAR	Dimension	wc.name (mrp_workcenter)
5	product_name	VARCHAR	Dimension / Calc	pt.name->>'en_US' — product resolved via COALESCE(wo_output.product_id, mp.product_id)
6	province_code	VARCHAR	Filter	pp.province_code (custom)
7	product_format	VARCHAR	Filter	pf.name (custom table)
8	src_type	VARCHAR	Filter	st.name (custom via product_category)
9	src_category	VARCHAR	Filter	sc.name (custom via product_category)
10	lot_name	VARCHAR	Filter / Calc	STRING_AGG(DISTINCT COALESCE(sml.lot_name, lot.name), ', ') — from CTE 3
11	mo_creation_date	TIMESTAMPTZ	Filter	mp.create_date
12	quantity_produced	NUMERIC	Employee KPI	COALESCE(eo.quantity_produced, 0) — from CTE 4 (done output logs)
13	weight_produced	NUMERIC	Employee KPI	COALESCE(eo.weight_produced, 0) — from CTE 4
14	employee_working_minutes	NUMERIC	Employee KPI	ep.employee_working_minutes — SUM(wp.duration) from CTE 1
15	employee_working_hours	NUMERIC	Employee KPI	ep.employee_working_minutes / 60.0
16	units_per_employee_per_hour	NUMERIC	Employee KPI	CASE: quantity_produced / (emp_minutes/60) ELSE NULL
17	standard_time	NUMERIC	Employee KPI	wo.duration_expected (minutes)
18	actual_time	NUMERIC	Employee KPI	wo.total_actual_workcenter_time (minutes)
19	employee_efficiency_pct	NUMERIC	Employee KPI	CASE: (duration_expected / emp_working_minutes) × 100 ELSE NULL
20	total_available_labour_time	NUMERIC	Employee KPI	rc_emp.hours_per_day — COALESCE(ep.calendar, res.calendar, wc.calendar)
21	labour_utilization_pct	NUMERIC	Employee KPI	CASE: (emp_hours / rc_emp.hours_per_day) × 100 ELSE NULL
22	attendance_to_output_ratio	NUMERIC	Employee KPI	COALESCE(quantity_produced, 0) / 6.0
23	workcenter	VARCHAR	WC Dimension	wc.name (duplicate alias)
24	manufacturing_order	VARCHAR	WC Dimension	mp.name (duplicate alias)
25	work_order	VARCHAR	WC Dimension	wo.name (duplicate alias)
26	product	VARCHAR	WC Dimension	pt.name->>'en_US' (duplicate alias)
27	workcenter_quantity_produced	NUMERIC	WC KPI	CASE ROW=1: wo_output.estimated_units ELSE 0 — ROW_NUMBER dedup guard
28	runtime_minutes	NUMERIC	WC KPI	CASE ROW=1: wo.total_actual_workcenter_time ELSE 0 — ROW_NUMBER guard
29	planned_runtime	NUMERIC	WC KPI	CASE: duration_expected / wec.employee_count ELSE 0
30	workcenter_efficiency_pct	NUMERIC	WC KPI	CASE ROW=1 AND actual>0: (duration_expected/actual_time)×100 ELSE 0
31	units_per_minute	NUMERIC	WC KPI	CASE ROW=1 AND actual>0: estimated_units/actual_time ELSE 0
32	units_per_hour	NUMERIC	WC KPI	CASE ROW=1 AND actual>0: estimated_units/(actual_time/60) ELSE 0
33	total_available_machine_time	NUMERIC	WC KPI	CASE ROW=1: rc_wc.hours_per_day ELSE 0
34	machine_utilization_pct	NUMERIC	WC KPI	CASE ROW=1 AND cal>0: (actual_hours/rc_wc.hours_per_day)×100 ELSE 0
35	ideal_cycle_time	NUMERIC	OEE	COALESCE(rw.time_cycle_manual, 0) — mrp_routing_workcenter
36	availability_pct	NUMERIC	OEE	CASE: (actual_time/duration_expected)×100 ELSE 100
37	performance_pct	NUMERIC	OEE	CASE: (ideal_cycle_time × qty / actual_time)×100 ELSE 100
38	quality_pct	NUMERIC	OEE	CASE: (units/units)×100 = 100% always (no defect data yet)
39	oee_pct	NUMERIC	OEE	Availability_ratio × Performance_ratio × Quality_ratio × 100

Employee & ResourceEfficiency Report

CTE Architecture

CTE 1 — employee_productivity

CTE 2 — workorder_employee_count

CTE 3 — workorder_output

CTE 4 — employee_output

Source Tables & Joins

mrp_workorder

mrp_production

mrp_routing_workcenter

mrp_workcenter_productivity

mrp_output_log

mrp_workcenter

hr_employee

resource_resource

resource_calendar

stock_move

stock_move_line

stock_lot

product_product

product_template

product_format

src_type / src_category

Report Grain

Filter / Dimension Fields

Employee Resource Efficiency Fields

Workcenter Resource Efficiency Fields

Overall Equipment Effectiveness (OEE) Fields

Full Column Quick Reference

Employee & Resource
Efficiency Report