MTL_SYSTEM_ITEMS_B

-- ============================================================
-- Table  : MTL_SYSTEM_ITEMS_B — The item master — one row per item per inventory organization, carrying identity (the SEGMENTn flexfield the user-visible item number concatenates from), status, unit of measure, and the control flags for every functional area; every balance, transaction, and order line joins back to it on INVENTORY_ITEM_ID + ORGANIZATION_ID
-- Purpose: Column-selected read of MTL_SYSTEM_ITEMS_B — auto-generated from field metadata
-- Grain  : One row per inventory org (ORGANIZATION_ID) + INVENTORY_ITEM_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.INVENTORY_ITEM_ID AS "Item id — the surrogate key every balance, transaction, and order line joins on (always together with the org)",
  t.ORGANIZATION_ID AS "Inventory organization — items repeat per org, master-org row plus child-org overrides",
  t.SEGMENT1 AS "System Items flexfield segment 1 — the item number users see, in single-segment configurations",  -- flexfield segment: concatenation is configuration-defined — see quirks guide #flexfields
  t.DESCRIPTION AS "Item description — the untranslated copy; translations live on the TL companion",
  CASE t.ITEM_TYPE WHEN 'FG' THEN 'Finished good' WHEN 'K' THEN 'Kit' WHEN 'OP' THEN 'Outside processing item' WHEN 'P' THEN 'Purchased item' WHEN 'PH' THEN 'Phantom item' WHEN 'PL' THEN 'Planning' WHEN 'REF' THEN 'Reference item' WHEN 'SA' THEN 'Subassembly' WHEN 'SI' THEN 'Supply item' ELSE t.ITEM_TYPE END AS "User item type — finished good, purchased, subassembly…; decodes through the FND lookup",  -- lookup: ITEM_TYPE
  t.INVENTORY_ITEM_STATUS_CODE AS "Item status — controls which functions accept the item; statuses are site-defined",
  t.PRIMARY_UOM_CODE AS "Primary unit of measure — the unit balances and primary quantities are stored in",
  t.LOT_CONTROL_CODE AS "Lot control — 1 = no control, 2 = full lot control",
  t.SERIAL_NUMBER_CONTROL_CODE AS "Serial control — no control, at receipt, at sales-order issue, or predefined",
  t.INVENTORY_ITEM_FLAG AS "Whether this is an inventory item at all",
  t.STOCK_ENABLED_FLAG AS "Whether the item can be stocked",
  t.MTL_TRANSACTIONS_ENABLED_FLAG AS "Whether material transactions are allowed for the item",
  t.PURCHASING_ITEM_FLAG AS "Whether the item can be purchased",
  t.CUSTOMER_ORDER_FLAG AS "Whether the item can be put on a customer order",
  t.PLANNER_CODE AS "The planner responsible for the item in this org",
  t.FULL_LEAD_TIME AS "Processing lead time in days, used by planning",
  t.ENABLED_FLAG AS "Whether the item row is enabled",
  t.START_DATE_ACTIVE AS "Effectivity start date",
  tl.DESCRIPTION AS "Item description in this row's language",
  tl.LONG_DESCRIPTION AS "Long item description in this row's language"
FROM <catalog>.<schema>.MTL_SYSTEM_ITEMS_B t
LEFT JOIN <catalog>.<schema>.MTL_SYSTEM_ITEMS_TL tl
  ON tl.INVENTORY_ITEM_ID = t.INVENTORY_ITEM_ID
 AND tl.ORGANIZATION_ID = t.ORGANIZATION_ID
 AND tl.LANGUAGE = '<language>'  -- one language or rows multiply
WHERE
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the operating unit — see quirks guide #two-orgs
  -- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- WHO watermark, bulk-stamped by batch jobs; see quirks guide #who-columns
ORDER BY t.INVENTORY_ITEM_ID;