What the module is for

The module is the system of record for how products are defined and changed: parts and revisions with lifecycle states, multi-level bills of materials with effectivity, CAD and specification files, formal change control from request to order to notice with where-used impact, approved manufacturers, product compliance declarations, requirements traceability and stage gates for new product introduction (NPI). It replaces the product lifecycle management (PLM), product data management (PDM) and bill-of-materials tools a business usually pays for, such as Siemens Teamcenter, PTC Windchill, Dassault 3DEXPERIENCE (ENOVIA), Arena, Propel, Autodesk Fusion Manage, Duro, OpenBOM and PDXpert; for formulated products the specification also names Siemens Opcenter RD&L and Specright. One vendor's own list of what such a tool must do is a useful comparison ^[1]; so are the buyer-side comparisons ^[2][3].

What it does not replace is the design tool. CAD authoring stays where it is. The module holds the files that CAD produces, one set per revision, and the data around them: which revision is current, which assemblies use it, who approved the last change.

What is in, and what is not

• In: the part master extension with revision schemes and lifecycle states; engineering, manufacturing and service bills of materials with effectivity, substitutes and where-used; the CAD and neutral file vault and specifications; ECR, ECO and ECN with change board approvals and stock and work-in-progress disposition; approved manufacturers and compliance declarations (RoHS, REACH, Prop 65, PFAS); requirements and traceability; NPI and APQP gates; process formulas.
• Out: CAD authoring; routings and execution recipes (M04 reads released definitions from here); quality change control for processes (M08 owns it, and a change order here carries a soft link to it).

Eleven capabilities in three tiers

Each capability carries a tier. MVP must exist for a small or mid-size business to cancel the incumbent subscription with confidence. STD is parity with mainstream tools, what a buyer expects in a demo. BIC marks best-in-class work. This course builds the MVP capabilities of part and revision control, bills of materials with where-used and a BOM compare, the file vault by fingerprint, and change management, plus effectivity, the approved manufacturer list and declarations; the cost roll-up, check-in and check-out, the mBOM derivation, compliance roll-up, NPI gates, requirements and formulation are added later the same way.

The standards, and what each one is used for

A part and its revisions

A part is the thing: a pump assembly, a bearing, a fastener pack. A revision is one version of its definition: revision A, then revision B after a change. The module keeps them in two tables. plm_part extends a spine item (item_id points at core_item), so there is one item list for the whole business and no second part master. plm_part_revision holds each revision of a part, and the part's current_revision_id says which is current. The revision lives only in plm_part_revision, never also on the item, so there is one place to look.

A part can be on its first revision for years, and a revision outlives a lifecycle change: a part is deprecated (do not use it in new designs) while its last released revision is still being built. Release is the moment a definition becomes something other modules may depend on: it publishes plm.part.released, and from then on manufacturing and purchasing can plan against it.

A header and its lines

A BOM is stored in two tables. plm_bom is the header: it belongs to one parent revision (parent_revision_id), has a bom_view and a status. plm_bom_line is one component of it, with the quantity and unit, the position, and the dates and serial numbers it applies to. A BOM is attached to a revision, not to a part: revision B of a pump has its own BOM, which may differ from revision A's.

These fields are what to check in your current tool: a feature list that says 'BOM management' does not tell you whether it has a revision rule, effectivity columns and separate views. The sources the specification lists for this capability are ^[1][2][3].

Exploding a multi-level BOM

The course seed is a small pump, drawn here. Each box is a part; the number after the name is the quantity per one of its parent. The shaft assembly is a phantom: it is never stocked, so its components pass straight through to the parent.

To find how many of a part one finished pump needs, multiply the quantities along each branch from the top to that part, then add the branches. The bearing PLM-5000 appears twice: through the shaft it is 1 × 1 × 1 × 2 = 2 (pump to motor to rotor to shaft to bearing), and through the cover it is 1 × 1 × 1 = 1. One pump needs 3 bearings. The fasteners PLM-4100 appear through the rotor (8) and the cover (4): 12 fasteners. Done by hand once, this is what the database function must return; done by a program only, you cannot tell whether it is right.

Views: engineering to manufacturing

The engineering BOM (eBOM) is what the designer drew. The manufacturing BOM (mBOM) is how it is built: phantoms are made transparent, packaging and consumables are added, and the lines match how material is issued on the floor. The module will derive the mBOM from the eBOM and hand it on, to M04 as operation materials and to M12 as demand for planning; that derivation is added later, and in this course the manufacturing engineer enters a manufacturing BOM by hand. Deriving it, not retyping it, is the point: a retyped second BOM drifts from the first.

Where-used: reading the BOM upward

Where-used asks which assemblies contain a part, at every level above it. It is the question behind every change: before you change a bearing you need to know every product that carries it. It is also the reason a BOM is stored as rows, not as a drawing.

The query climbs one level at a time until nothing is above. A recursive query is a query that feeds its own result back in; here each pass adds the parents of the previous pass. A part must never be its own ancestor, so a loop is a data error to be found before it hangs a screen. The sample carries the path it has climbed and never steps into a part already on it, so even a loop in the data cannot make it run forever; your own function should go further and stop with an error naming the part. Each assembly is returned once, at its nearest level.

WITH RECURSIVE up AS (
  SELECT r.part_id AS parent_part_id, 1 AS level_up,
         ARRAY[$1::uuid, r.part_id] AS path
  FROM plm_bom_line l
  JOIN plm_bom b ON b.id = l.bom_id
       AND b.status = 'released' AND b.bom_view = 'engineering'
  JOIN plm_part_revision r ON r.id = b.parent_revision_id
  WHERE l.child_part_id = $1
  UNION ALL
  SELECT r.part_id, up.level_up + 1, up.path || r.part_id
  FROM up
  JOIN plm_bom_line l ON l.child_part_id = up.parent_part_id
  JOIN plm_bom b ON b.id = l.bom_id
       AND b.status = 'released' AND b.bom_view = 'engineering'
  JOIN plm_part_revision r ON r.id = b.parent_revision_id
  WHERE r.part_id <> ALL (up.path)  -- never climb into a part already on this path
)
SELECT parent_part_id, min(level_up) AS level_up  -- nearest level
FROM up GROUP BY parent_part_id;

Effectivity: which revision, when

A part may have several released revisions over its life, and the factory may build two of them in the same month: new stock after a date, old stock until it runs out, new serial numbers from a number onward. Effectivity is the rule that says which revision applies. It comes in four kinds: date, serial, lot, and use-up, where old stock is consumed first and the new revision starts only when it is gone; effective_at is the planned date the old stock runs out. An immediate change applies from the day it is implemented.

The compliance rule of the module is blunt: effectivity must be unambiguous, so MES and MRP always resolve exactly one revision. How you store the dates decides whether that is true.

This course stores the date a revision starts and lets the next start end the previous one. A resolver then returns, for a part and a date (and a serial number where the change is by serial), the single revision that applies, and refuses with an error if it cannot. A part that mixes date effectivity with serial or lot effectivity across its revisions is refused too, because then the same unit could match two rules.

A BOM line carries its own effectivity as well (effectivity_from, effectivity_to, serial_from, serial_to). The explosion applies it: it skips a line whose effectivity_from is after the date, whose effectivity_to is before it, or whose serial range excludes the serial asked about. An empty bound does not limit, and a query without a serial ignores the serial range. Where-used ignores line effectivity, so a change is assessed against every line that may use the part.

The thirteen tables, column by column

This is the table Session 2 builds from. Every table also has the standard columns of the spine (id, tenant_id, created_at, created_by, updated_at, updated_by, row_version, archived_at and ext), which are not repeated here. An arrow (→) is a foreign key to the table named. The types: text, int, bool, date, ts (a timestamp with time zone), json, uuid, qty (numeric(18,6)) and money (numeric(18,4)). req means required (not null); a list after a colon is the only values allowed. The specification also lists plm_requirement, plm_trace_link, plm_formula and plm_formula_line, which this course does not build.

Request, order, notice

Three documents carry a change. The engineering change request (ECR) is the problem or idea: anyone with a reason can raise one. The engineering change order (ECO) is the controlled change: which parts change, from which revision to which, and when. The engineering change notice (ECN) tells everyone who needs to know that it is now in force. A vendor guide to engineering change management software is listed among the specification's sources ^[1].

An ECO also has a change type (design, bom, document, supplier, cost or compliance), an effectivity type (immediate, date, serial, lot or use-up) with an effective date when it needs one, and three decisions about what already exists: what to do with work in progress (use as is, rework, scrap), with stock (use as is, rework, scrap, return) and with products in the field (none, on failure, retrofit, recall). A change order that does not say what happens to the parts already made is incomplete, because the factory will decide it on the floor, differently on each shift.

Approvals that mean something

The board's decision is a row per approver in plm_eco_approval: a role (engineering, manufacturing, purchasing), the person, a decision (pending, approved or rejected), the time, and a link to an electronic signature in the spine (core_e_signature), which records who signed, what the signature meant, and a hash of the record signed. A tick box is not a signature: the signature is tied to the exact content approved, so it cannot be moved to a later edit.

Two further rules keep the order honest. After the board review starts, the order's fields and affected items cannot change, so what was approved is what is implemented. And before a change is implemented, a check confirms that the new effective date does not fall on or before the start of the revision being replaced. Implementing publishes plm.eco.implemented; approval publishes plm.eco.approved. Other modules consume qms.capa_action, svc.ticket.problem and mes.parameter.out_of_spec and turn them into change requests, so a field problem or an out-of-spec reading can start a change without anyone retyping it.

Approved manufacturers and their risk

A bought part is not bought from just anyone. The approved manufacturer list (AML) records, for each part, the manufacturers and manufacturer part numbers (MPN) you may buy it from. Each entry in plm_manufacturer_part has a status (approved, preferred, restricted or obsolete) and a lifecycle (active, NRND, EOL, obsolete or unknown). NRND means not recommended for new designs; EOL means end of life. Lifecycle is the manufacturer's word about the part, the status is your decision about the manufacturer, and they are separate columns because they change independently.

Compliance declarations and roll-up

A declaration says whether a part, or a manufacturer's part, meets a substance rule. plm_compliance_declaration holds a regulation (RoHS, REACH substances of very high concern, Prop 65, TSCA PFAS, conflict minerals or other), a status (compliant, non_compliant, exempt or unknown), the substances found, the evidence (a certificate, kept as an attachment), where the information came from (supplier, test or engineering), and a date it is valid until. RoHS restricts certain hazardous substances in electrical and electronic equipment ^[2]; REACH is the EU chemicals regulation ^[3]; California Prop 65 requires warnings for listed chemicals ^[4]. A declaration without evidence, or past its date, is a claim, not a record.

Roll-up is where-used again, applied to a status: the status of an assembly is the worst status found among the bought parts below it. This course uses one rule, kept simple on purpose: compliant only if every bought part is compliant or exempt, non_compliant if any is non_compliant, otherwise unknown. Whether a rule is enough for your market is a question for whoever signs your declarations; the module's job is to make the answer visible rather than to make it for you.

Roles and screens

Four jobs work in the module, each a core_job_role row. Session 3 writes their rights into the Role and Exposure Matrix; everyone else only looks, and a supplier contact or contractor has no role and sees nothing.

• /plm/parts, the desk screen: the part and BOM editor. A released revision shows read-only, with the words "change by ECO".
• /plm/changes, the review screen: change requests and change orders by status, one at a time with affected items, dispositions and approvals; the board signs here.
• /plm/bom, the floor and manager screen: the released BOM, one level at a time, with no edit control anywhere.

Behind the screens are routes, each with its own narrow account: eco-submit (/api/plm/submit), eco-release (/api/plm/release), plm-loader (/api/plm/import, closed at cut-over) and plm-resolve (/api/plm/resolve, for MRP and MES), plus the read-only plm-check account for the checks. Signing (/api/plm/sign) runs as the signed-in board member.

Import order

Real data comes in parts first, as the specification recommends: the current released revision of each part and its BOMs, then approved manufacturers, declarations and files, and change history only where an auditor or customer requires it. The loader writes in this order, because each step needs the one before it:

1. Units (core_uom).
2. Manufacturers (core_party).
3. Items (core_item), reusing one another module already made.
4. Parts (plm_part).
5. Each part's current revision, in_work, flagged as a baseline.
6. Files (core_attachment and plm_file), before the release, because a released revision is frozen.
7. The release of the revisions.
8. BOMs, in_work, with their lines.
9. The release of each BOM whose children all resolve to a released revision.
10. Manufacturer parts (plm_manufacturer_part).
11. Declarations (plm_compliance_declaration).
12. Closed ECOs for the history you must keep, already implemented and flagged as imported.

Checks before cut-over

Nothing replaces the old tool until these hold at zero exceptions, on your own data:

• Every part in released or deprecated lifecycle points at a released revision of itself as its current revision, and every part in concept, in design or prototype has none.
• No released BOM has a line whose child part lacks a released revision.
• Exactly one revision resolves for every part with a released revision, on a date long past, today, a date years ahead, and the day of every change's effective date and the day before it.
• No part is its own ancestor.
• The three promises of the module: a released revision and its BOM cannot be edited in place; an ECO cannot be implemented without every board approval signed; exactly one revision resolves for any part and date or serial.

Four measures, and what comes after

Four measures tell you whether the module works, all computed from its own tables. The checks script in Session 6 reports the first three; the fourth needs a record of post-release corrections that you add later.

Beyond this course the module goes further: a requirements tree with verification methods and trace links to parts, tests and documents; NPI projects with phase gates, following the planning and gate structure of AIAG's APQP ^[5]; and formulation for process industries, where a recipe moves from general to site to master level under ISA-88. You add them the same way you add everything: a table, a rule the database enforces, a screen and a test.