Open-source manufacturing ERP, MES and QMS. Quoting, MRP, inventory, shop floor, quality and lot/serial traceability on one Postgres schema, with a REST API and MCP server. Self-host or use Carbon Cloud.
TypeScript ai-agentsbill-of-materialserpinventory-managementleanmanufacturingmanufacturing-execution-systemmcp-servermesmrpopen-source-erppostgresqlproduction-planningqmsquality-management-systemreact-routerself-hostedsupabasetraceabilitytypescript
CREATE OR REPLACE FUNCTION public.sync_update_job_operation_quantities(p_table text, p_operation text, p_new jsonb, p_old jsonb)
RETURNS void
LANGUAGE plpgsql
SET search_path TO 'public'
AS $function$
DECLARE
v_job_operation_id TEXT;
v_job_id TEXT;
v_user_id TEXT;
v_is_last_top_level_operation BOOLEAN := FALSE;
BEGIN
v_user_id := COALESCE(
p_new->>'updatedBy', p_new->>'createdBy',
p_old->>'updatedBy', p_old->>'createdBy'
);
IF p_operation = 'INSERT' THEN
v_job_operation_id := p_new->>'jobOperationId';
UPDATE "jobOperation"
SET
"quantityComplete" = "quantityComplete" +
CASE WHEN (p_new->>'type') = 'Production' THEN (p_new->>'quantity')::numeric ELSE 0 END,
"quantityReworked" = "quantityReworked" +
CASE WHEN (p_new->>'type') = 'Rework' THEN (p_new->>'quantity')::numeric ELSE 0 END,
"quantityScrapped" = "quantityScrapped" +
CASE WHEN (p_new->>'type') = 'Scrap' THEN (p_new->>'quantity')::numeric ELSE 0 END
WHERE id = v_job_operation_id;
ELSIF p_operation = 'UPDATE' THEN
v_job_operation_id := p_new->>'jobOperationId';
UPDATE "jobOperation"
SET
"quantityComplete" = "quantityComplete"
- CASE WHEN (p_old->>'type') = 'Production' THEN (p_old->>'quantity')::numeric ELSE 0 END
+ CASE WHEN (p_new->>'type') = 'Production' THEN (p_new->>'quantity')::numeric ELSE 0 END,
"quantityReworked" = "quantityReworked"
- CASE WHEN (p_old->>'type') = 'Rework' THEN (p_old->>'quantity')::numeric ELSE 0 END
+ CASE WHEN (p_new->>'type') = 'Rework' THEN (p_new->>'quantity')::numeric ELSE 0 END,
"quantityScrapped" = "quantityScrapped"
- CASE WHEN (p_old->>'type') = 'Scrap' THEN (p_old->>'quantity')::numeric ELSE 0 END
+ CASE WHEN (p_new->>'type') = 'Scrap' THEN (p_new->>'quantity')::numeric ELSE 0 END
WHERE id = v_job_operation_id;
ELSIF p_operation = 'DELETE' THEN
v_job_operation_id := p_old->>'jobOperationId';
UPDATE "jobOperation"
SET
"quantityComplete" = "quantityComplete" -
CASE WHEN (p_old->>'type') = 'Production' THEN (p_old->>'quantity')::numeric ELSE 0 END,
"quantityReworked" = "quantityReworked" -
CASE WHEN (p_old->>'type') = 'Rework' THEN (p_old->>'quantity')::numeric ELSE 0 END,
"quantityScrapped" = "quantityScrapped" -
CASE WHEN (p_old->>'type') = 'Scrap' THEN (p_old->>'quantity')::numeric ELSE 0 END
WHERE id = v_job_operation_id;
END IF;
UPDATE "jobOperation"
SET "status" = 'Done',
"updatedBy" = COALESCE(v_user_id, "updatedBy", "createdBy"),
"updatedAt" = NOW()
WHERE id = v_job_operation_id
AND "status" NOT IN ('Done', 'Canceled')
AND "targetQuantity" > 0
AND ("quantityComplete" + "quantityReworked") >= "targetQuantity";
SELECT jo."jobId" INTO v_job_id
FROM "jobOperation" jo
WHERE jo.id = v_job_operation_id;
SELECT EXISTS (
SELECT 1
FROM "jobOperation" jo
INNER JOIN "jobMakeMethod" jmm ON jmm.id = jo."jobMakeMethodId"
WHERE jo.id = v_job_operation_id
AND jmm."parentMaterialId" IS NULL
AND NOT EXISTS (
SELECT 1
FROM "jobOperationDependency" dep
INNER JOIN "jobOperation" child_jo ON child_jo.id = dep."operationId"
INNER JOIN "jobMakeMethod" child_jmm ON child_jmm.id = child_jo."jobMakeMethodId"
WHERE dep."dependsOnId" = jo.id
AND child_jmm."parentMaterialId" IS NULL
)
) INTO v_is_last_top_level_operation;
IF v_job_id IS NOT NULL AND v_is_last_top_level_operation THEN
UPDATE "job"
-- Counted as sync_finish_job_operation counts the receipt: the smallest of
-- the routing's own final operations (parallel ones each see every unit)
-- plus what rework branches completed.
SET "quantityComplete" = (
SELECT COALESCE(MIN(terminal_jo."quantityComplete") FILTER (
WHERE terminal_jo."reworkId" IS NULL AND terminal_jo."quantityComplete" > 0
), 0)
+ COALESCE(SUM(terminal_jo."quantityComplete") FILTER (
WHERE terminal_jo."reworkId" IS NOT NULL
), 0)
FROM "jobOperation" terminal_jo
INNER JOIN "jobMakeMethod" terminal_jmm ON terminal_jmm.id = terminal_jo."jobMakeMethodId"
WHERE terminal_jo."jobId" = v_job_id
AND terminal_jmm."parentMaterialId" IS NULL
AND terminal_jo.status <> 'Canceled'
AND NOT EXISTS (
SELECT 1
FROM "jobOperationDependency" dep
INNER JOIN "jobOperation" child_jo ON child_jo.id = dep."operationId"
INNER JOIN "jobMakeMethod" child_jmm ON child_jmm.id = child_jo."jobMakeMethodId"
WHERE dep."dependsOnId" = terminal_jo.id
AND child_jmm."parentMaterialId" IS NULL
)
)
WHERE id = v_job_id
AND status NOT IN ('Completed', 'Cancelled');
END IF;
END;
$function$;