bom

-- ============================================================
-- Table  : bom — The bill-of-materials header: one row per BOM, naming it and setting which assemblies, locations and subsidiaries it may be used on. The component lines live on the revision, not here.
-- Purpose: Column-selected read of bom — auto-generated from field metadata
-- Grain  : One row per id
-- Caution: this record exposes no lastmodifieddate — there is no watermark column here, so incremental extraction must full-refresh the BOM headers. And a BOM header holds no components: quantities live on bomrevisioncomponent, one revision down.
-- Notes  : Auto-generated skeleton for NetSuite data landed in your lakehouse from a SuiteAnalytics Connect (or SuiteQL) extract — it never addresses live NetSuite. Identifiers are lowercase as NetSuite2.com renders them. Select-type columns hold numeric internal ids: BUILTIN.DF() display resolution exists only at extraction time, so decode ids by joining the landed list records; see quirks #display-values. Check-box columns arrive as 'T'/'F' strings; see quirks #tf-booleans.
-- ============================================================
SELECT
  t.id AS "Internal ID",
  t.name AS "BOM name",
  t.memo AS "Memo",
  t.legacybomforassembly AS "Assembly this BOM was migrated from (internal id)",
  t.usedonassembly AS "BOM is used on at least one assembly",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.usecomponentyield AS "Component yield is applied when this BOM is exploded",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.includechildren AS "Available to child subsidiaries",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.availableforallassemblies AS "Usable on every assembly",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.availableforalllocations AS "Usable at every location",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.restricttoassemblies AS "Assemblies the BOM is restricted to — MULTISELECT",
  t.restricttolocations AS "Locations the BOM is restricted to — MULTISELECT",
  t.availableforlocations AS "Locations the BOM is available at — MULTISELECT",
  t.availableforsubisidiaries AS "Subsidiaries the BOM is available in — MULTISELECT, and the id is misspelled on the record page itself",
  t.subsidiary AS "Subsidiaries — MULTISELECT, not a scalar foreign key",
  t.bomrevision AS "Revisions of this BOM — MULTISELECT; the queryable rows are on bomrevision",
  t.isinactive AS "Inactive",  -- 'T'/'F' string — compare = 'T', or CAST via CASE; see quirks #tf-booleans
  t.createddate AS "Date created",
  t.externalid AS "External ID"
FROM <catalog>.<schema>.bom t
WHERE
  1 = 1  -- no partition column on this record; the filters below are optional
  -- AND t.id = <id>
  -- no lastmodifieddate on this record — there is no watermark column, so incremental extraction must full-refresh it (see quirks #deletes)
ORDER BY t.id;