BOM_COMPONENTS_B

-- ============================================================
-- Table  : BOM_COMPONENTS_B — Single-level bill components — one row per component per operation per effectivity window, with usage quantity, yield, supply type, and the supply subinventory shop-floor issues pull from
-- Purpose: Column-selected read of BOM_COMPONENTS_B — auto-generated from field metadata
-- Grain  : One row per COMPONENT_SEQUENCE_ID
-- Caution: Rows are date-effective — the same component reappears per revision window; filter EFFECTIVITY_DATE/DISABLE_DATE around an as-of date (and IMPLEMENTATION_DATE IS NOT NULL) or explosions double-count
-- Notes  : Auto-generated skeleton for Oracle EBS R12 data landed in your lakehouse. Dates are real DATE/TIMESTAMP columns — no conversion needed. WHO audit columns omitted (see the quirks guide); the optional LAST_UPDATE_DATE watermark filter supports incremental extracts.
-- ============================================================
SELECT
  t.COMPONENT_SEQUENCE_ID AS "Surrogate key of the component row",
  t.BILL_SEQUENCE_ID AS "The bill the component belongs to — also where the organization resolves from",
  t.COMPONENT_ITEM_ID AS "The component item",
  t.OPERATION_SEQ_NUM AS "The routing operation that consumes the component — 1 when no routing; a value match, not a foreign key",
  t.ITEM_NUM AS "Sort/display sequence on the bill",
  t.COMPONENT_QUANTITY AS "Usage quantity per assembly",
  t.COMPONENT_YIELD_FACTOR AS "Yield factor applied to usage",
  t.EFFECTIVITY_DATE AS "When the component row becomes effective — the as-of filter column",
  t.DISABLE_DATE AS "When the row stops being effective — NULL while active",
  t.IMPLEMENTATION_DATE AS "NULL on unimplemented ECO rows — filter it out for the as-built view",
  t.PLANNING_FACTOR AS "Planning percentage for option/model components",
  t.QUANTITY_RELATED AS "Whether the component quantity is tied to the option quantity",
  t.WIP_SUPPLY_TYPE AS "Supply method — push, pull, phantom, bulk, supplier; numeric, decode from your instance's manufacturing lookups",
  t.SUPPLY_SUBINVENTORY AS "Default supply subinventory for issues",
  t.SUPPLY_LOCATOR_ID AS "Default supply locator",
  t.OPTIONAL AS "Whether the component is optional on a model bill",
  t.BOM_ITEM_TYPE AS "Component's BOM item type — standard, model, option class…; numeric, undecoded",
  t.BASIS_TYPE AS "Item vs lot basis for the usage quantity"
FROM <catalog>.<schema>.BOM_COMPONENTS_B t
WHERE
  1 = 1  -- no partition column on this table; the filters below are optional
  -- AND t.COMPONENT_SEQUENCE_ID = <COMPONENT_SEQUENCE_ID>
  -- AND t.EFFECTIVITY_DATE >= DATE '<DATE_FROM>'
  -- AND t.EFFECTIVITY_DATE <= DATE '<DATE_TO>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.COMPONENT_SEQUENCE_ID;