EGP_SYSTEM_ITEMS_B

-- ============================================================
-- Table  : EGP_SYSTEM_ITEMS_B — The item master — one row per item per inventory organization, carrying the item number as a real column plus status, type, 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 EGP_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 Fusion Cloud data landed in your lakehouse by a BICC extract — there is no SQL path to the SaaS database. Column names follow Oracle's table documentation — if your landed data still carries PVO attribute headers, map names first; see quirks #pvo-drift. 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 surrogate key — half of every item join",
  t.ORGANIZATION_ID AS "Owning inventory organization — the other half of every item join; items repeat per org",
  t.ITEM_NUMBER AS "The human-readable item number — a real column, not a flexfield (the Fusion delta from EBS SEGMENT1)",
  t.ITEM_TYPE AS "Item classification code (finished good, purchased…)",
  t.INVENTORY_ITEM_STATUS_CODE AS "Status code gating which transactions the item allows",
  t.APPROVAL_STATUS AS "Item approval state",
  t.CURRENT_PHASE_ID AS "Current lifecycle phase of the item",
  t.ITEM_CATALOG_GROUP_ID AS "Item class providing shared metadata and extensible attributes",
  t.PRIMARY_UOM_CODE AS "Primary unit of measure for the item's quantities",
  t.STOCK_ENABLED_FLAG AS "Item can be stocked",
  t.MTL_TRANSACTIONS_ENABLED_FLAG AS "Inventory transactions allowed for the item",
  t.CUSTOMER_ORDER_ENABLED_FLAG AS "Currently orderable by customers",
  t.PURCHASING_ENABLED_FLAG AS "Can be put on purchase orders",
  t.PLANNING_MAKE_BUY_CODE AS "Make vs buy planning attribute",
  t.BOM_ITEM_TYPE AS "Type of structure the item may have (standard, model, planning)",
  t.FULL_LEAD_TIME AS "Days to make or procure the item",
  t.LIST_PRICE_PER_UNIT AS "Default purchasing list price",
  t.START_DATE_ACTIVE AS "Item active-from date",
  tl.DESCRIPTION AS "Item short description — the only place it exists",
  tl.LONG_DESCRIPTION AS "Item extended description"
FROM <catalog>.<schema>.EGP_SYSTEM_ITEMS_B t
LEFT JOIN <catalog>.<schema>.EGP_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 business unit — see quirks guide #item-org-striping
  -- AND t.INVENTORY_ITEM_ID = <INVENTORY_ITEM_ID>
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.INVENTORY_ITEM_ID;