BOM_STRUCTURES_B

-- ============================================================
-- Table  : BOM_STRUCTURES_B — The bill-of-material header — one bill per assembly item, organization, and alternate designator (manufacturing or engineering), with the common-bill pointer that lets organizations share one structure
-- Purpose: Column-selected read of BOM_STRUCTURES_B — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + BILL_SEQUENCE_ID
-- 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.BILL_SEQUENCE_ID AS "Surrogate key of the bill — what components hang off",
  t.ASSEMBLY_ITEM_ID AS "The assembly item the bill builds",
  t.ORGANIZATION_ID AS "Owning inventory organization",
  t.ALTERNATE_BOM_DESIGNATOR AS "NULL for the primary bill — alternates add rows per assembly and org",
  t.ASSEMBLY_TYPE AS "1 = manufacturing, 2 = engineering (per the data dictionary's own comment)",
  t.COMMON_BILL_SEQUENCE_ID AS "The shared bill the components actually live under — equals the own id when the bill is not common",
  t.COMMON_ASSEMBLY_ITEM_ID AS "Assembly of the common bill, when common",
  t.COMMON_ORGANIZATION_ID AS "Organization of the common bill — commons can cross orgs",
  t.EFFECTIVITY_CONTROL AS "Date vs unit effectivity control for the structure",
  t.IMPLEMENTATION_DATE AS "When the bill was implemented — NULL for pending engineering bills",
  t.PENDING_FROM_ECN AS "The change notice that created a pending bill",
  t.OBJ_NAME AS "PLM object name — NULL on classic item bills; the legacy views filter on it"
FROM <catalog>.<schema>.BOM_STRUCTURES_B t
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.BILL_SEQUENCE_ID = <BILL_SEQUENCE_ID>
  -- AND t.IMPLEMENTATION_DATE >= DATE '<DATE_FROM>'
  -- AND t.IMPLEMENTATION_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.BILL_SEQUENCE_ID;