The BOM cache — a local mirror of Infor's exploded BOMs

Audience: developers & AI agents · Scope: the two cache tables, how they are refreshed, and the tools and jobs that read them · Last reviewed: 2026-07-25

TL;DR — Exploding a BOM in Infor is slow, so the site keeps its own flat copy: one row per (item → material) pair in #__configbox_external_infor_item_materials. A nightly job asks Infor which items are new or changed since we last cached them, re-explodes just those, and writes the result back. Infor itself holds the bookmark, in a custom column item_mst.Uf_BCDateLastBomCalc — the site writes to the production ERP on every refresh. A DB flag, not a lock, keeps refresh and read from overlapping.

What it is & why it exists

The slash method needs to answer "what parts make up item X?" in milliseconds, for any of tens of thousands of items, while a user waits. The underlying query is a five-level self-join across Infor's job / jobmatl / item tables (see cpq.md) and takes minutes for a large batch. So it is run ahead of time, in bulk, and the flat result is cached locally.

The cache is derived data with no independent value — if it is wrong or empty, the fix is always to re-run the refresh. Nothing here is a source of truth.

How it fits together

   Infor (MSSQL)                                       site DB
   ────────────                                        ───────
   item_mst ────── which items are stale? ────────►  (three queries, below)
      │                                                    │
      │  ◄── explode BOMs for that batch ──────────────────┤
      │      (5-level join, batches of 50)                 │
      │                                                    ▼
      │                                    #__configbox_external_infor_items
      │                                    #__configbox_external_infor_item_materials
      │                                                    │  (FK, ON DELETE CASCADE)
      └── UPDATE item_mst.Uf_BCDateLastBomCalc = getdate() ─┘
                    ▲
                    └── the bookmark lives in the ERP, not here

File	Role
CUST/models/bcitembomcalculator.php	the whole cache engine — staleness detection, batching, storage, the flags, purge
CUST/models/csimaterials.php → fetchMaterialsDataViaDb()	the five-level explosion query itself
cli/cb_bom_cache_update.php	the nightly refresh
cli/cb_bom_cache_get_spreadsheet.php	resolve an Excel list of items against the cache → CSV
CUST/controllers/adminbccachedbom.php + views/adminbccachedbom/	Infor Flat BOM cached plus Warehouse data — the interactive cached lookup
CUST/controllers/adminbccsibom.php + views/adminbccsibom/	Infor Flat BOM Calculator (Fresh) — bypasses the cache
system_overrides/BcCsiItem.php, BcCsiSlimItem.php, BcCsiCachedMaterial.php, BcCsiItemMaterial.php, BcCsiItemMaterialsRequest.php	the value objects passed around

Control / data flow — the refresh cycle

updateBomCache($batchSize = 50) builds one work list from four sources, de-duplicates it, and processes it in batches:

#	Question asked	Where
1	Which items have never been cached? (Uf_BCDateLastBomCalc IS NULL)	Infor
2	Which items changed since we cached them? (DATEDIFF(s, Uf_BCDateLastBomCalc, RecordDate) > 2)	Infor
3	Which items have a job that changed since we cached the item?	Infor
4	Which cached root items contain any item from 2 or 3 as a material?	site DB

Source 4 is the important one: changing a screw invalidates every luminaire that uses it, so the cache walks up from changed materials to the roots that must be re-exploded.

For each batch, cacheItemMaterials() then:

1. loads slim item records from Infor,
2. asks ConfigboxModelCsimaterials::getMaterialsForItems() to explode them,
3. in one transaction: deletes the existing rows for those items, inserts the items, inserts the materials (both in chunks of 100–500),
4. writes getdate() back into item_mst.Uf_BCDateLastBomCalc in Infor, with one retry on connection failure.

The 2-second tolerance in the DATEDIFF comparisons exists because step 4 stamps a time that is necessarily slightly later than the read in step 1 — without it every item would look permanently changed.

# Nightly refresh (this is what cron runs)
php docroot/cli/cb_bom_cache_update.php --batch-size=50

# Force a run past a stuck in-progress flag
php docroot/cli/cb_bom_cache_update.php --ignore-in-progress-flag=1

--redo=1 purges the cache. It runs purgeCache(): DELETE FROM #__configbox_external_infor_items with no WHERE (materials follow via the FK cascade), and sets Uf_BCDateLastBomCalc = NULL for every row in Infor's item_mst. The next refresh then re-explodes the entire catalogue. Never pass it from cron.

Reading through the cache

loadCachedBomItems($mergeDuplicates, ...$itemRequests) is the read path used by the tools and the spreadsheet CLI. It resolves item names (wildcards * are translated to SQL %), checks which are missing from the cache, and then:

• fewer than 500 missing (getMaxMissingItems()): caches them on the fly, so a read can trigger a write and take minutes;
• 500 or more missing: throws, on the grounds that the cache is not populated enough to answer.

$mergeDuplicates = true aggregates by material across all requested roots — summing quantities and concatenating paths — instead of returning one row per occurrence. It switches the session sql_mode to include ONLY_FULL_GROUP_BY to do so.

Progress is published to APCu under cachedBom.statusMsg so the admin screens can poll a status line.

The concurrency flags

Refresh and read must not overlap, and the guard is two system variables, not a lock:

Flag (ConfigboxSystemVars)	Set by	Blocks
bc_bom_caching_in_progress	the refresh	readers, via waitForCachingProcesses()
bc_bom_loading_in_progress	the read/spreadsheet path	the refresh, via waitForLoadingProcesses()

Both waiters poll every 5 seconds for up to 5 minutes, then give up and abort. The refresh clears its flag in a finally, and cb_bom_cache_update.php installs signal handlers so Ctrl-C also clears it — but a hard kill (SIGKILL, an OOM, a container restart) leaves the flag set and every later run refuses to start until someone passes --ignore-in-progress-flag=1. That is the single most common way this job appears "broken".

Admin tools

Three interactive calculators, all under the admin backend and all gated on com_configbox.core.manage:

Screen	Controller	What it does
Infor Flat BOM cached plus Warehouse data	adminbccachedbom	resolves items through the cache and joins live warehouse quantities (on hand, WIP, allocated, ordered) from Infor. Accepts an uploaded .xlsx of item names + quantities.
Infor Flat BOM Calculator (Fresh)	adminbccsibom	explodes live, bypassing the cache — the tool to use when you suspect the cache is stale
CPQ Flat BOM Calculator	adminbccpqbom	the CPQ path, from a configuration code rather than an item name

The Excel upload path (getItemMaterialRequestsFromExcel()) reads columns A (item name) and B (quantity), defaults a missing/non-numeric quantity to 1, and drops the first row if cell A1 is literally Item. Requests are capped at 200 items per call (ConfigboxModelCsimaterials::maxItemRequests).

# The same lookup from the CLI — needs an .xlsx of item names, writes bom.csv to the temp dir
php docroot/cli/cb_bom_cache_get_spreadsheet.php --file=/path/to/items.xlsx

Admin settings

The cache itself has no settings — no TTL, no size cap, no on/off switch. It is configured entirely by the Infor DB credentials it reads through (Custom Settings → Infor DB; see the Infor hub), and its refresh cadence is a cron entry, not a setting.

Data model

Created by migration 0.3.26.php.

Table	Columns	Notes
#__configbox_external_infor_items	item (key), description, cached_on	one row per cached root item; cached_on is indexed
#__configbox_external_infor_item_materials	item, material, pmt_code, qty, um, path	the flat BOM. FK item → infor_items.item, ON DELETE CASCADE ON UPDATE CASCADE — deleting an item drops its materials

qty is the quantity per one of the root item; the caller multiplies by the requested root quantity. path is the JSON array of item names from root to material and is the natural sort key. Migration 0.4.13.php/0.4.14.php adjusted pmt_code and um after the fact.

The index intended for material was created on pmt_code instead — 0.3.26.php runs create index material on … (pmt_code). Source 4 of the refresh (WHERE material IN (…)) is therefore unindexed on that column, which is the most likely reason a refresh with many changed items is slow.

In Infor, the cache owns one custom column: item_mst.Uf_BCDateLastBomCalc. It is the bookmark, and it is written on every successful batch.

Integrations & contracts

Everything here goes over the direct MSSQL path (BcHelper::getInforDb(20, 20) — 20s connect, 20s timeout), not the IDO REST API. See infor/database.md.

CloudWatch namespace BOM-Cache: Count-Uncached-Items-Detected, Count-Changed-Items-Detected, Count-Items-With-Changed-Jobs-Detected, Count-Cached-Items-Affected, Count-Unique-Items-To-Process, Bom-Cache-Size-Items, Bom-Cache-Size-Materials, Count-Bom-Cache-Refresh-Successes / -Failures, Time-Bom-Cache-Refresh, BOM-Cache-Count-Items-Updated, BOM-Cache-Count-Materials-Updated, BOM-Cache-Time-Per-Item, BOM-Cache-Count-Materials-Per-Item.

BOM-Cache-Time-Per-Item is written twice per batch — once divided by item count, once by material count — so the metric mixes two different quantities.

Logs: custom_bom_cache_caching (refresh) and custom_bom_cache_loading (reads).

Deployment runbook (manual steps)

1. Run migrations (0.3.26, 0.4.13, 0.4.14) — automatic on next page load.
2. Set the Infor DB credentials and confirm the site-to-site VPN is up; the whole cache is unreachable without it.
3. Confirm the custom column Uf_BCDateLastBomCalc exists on item_mst in that Infor environment. Without it every refresh query fails — this is an ERP-side prerequisite, not something the site creates.
4. Seed the cache: run cb_bom_cache_update.php by hand. The first run explodes the whole catalogue and takes hours — run it in a session that survives a disconnect and watch custom_bom_cache_caching.
5. Add the cron entry (nightly, under flock) — see scheduled jobs.
6. Smoke test: open Infor Flat BOM cached plus Warehouse data, enter one known item, and confirm rows come back; compare against Infor Flat BOM Calculator (Fresh) for the same item.

Turning it off: remove the cron entry. Reads keep working against whatever is cached; a fully empty cache makes every slash-method lookup miss, which pushes quote lines to no_bom rather than erroring.

Testing

No automated coverage — it needs a live Infor and the VPN. Verify by comparing the cached and fresh calculators for the same item, which is exactly what the two tools exist for.

Gotchas & caveats

• A refresh writes to production Infor. It is not a read-only job. A purge (--redo=1) nulls a column on every row of item_mst.
• A stuck in-progress flag silently blocks all later runs. Check ConfigboxSystemVars::getVar('bc_bom_caching_in_progress') first when the cache stops updating.
• A read can become a write. Under 500 missing items, a lookup caches on the fly — an interactive screen can therefore hang for minutes and hit Infor hard.
• Deleted Infor items are never removed from the cache. Staleness detection only ever adds work; there is no reconciliation pass for items that no longer exist.
• Batch size is a real tuning knob. 50 is the default; larger batches mean fewer, longer Infor transactions.
• storeItems() passes a key on the final chunk but not on the batched ones — the in-loop calls pass null where the trailing call passes 'item'. Worth understanding before changing the batching.

Possible follow-ups

• Replace the DB flags with the GET_LOCK(…, 0) + finally pattern used by the nudge build and stage-sync export, so a killed process cannot wedge the job.
• Fix the material index (currently created on pmt_code).
• Add a reconciliation pass that drops cached items no longer present in Infor.

Related docs

• Hub: BOM & CPQ · spokes: position-boms.md · cpq.md · rest-api.md
• infor/database.md — the MSSQL connection this depends on
• Scheduled jobs · Monitoring