HR_ALL_ORGANIZATION_UNITS_F

-- ============================================================
-- Table  : HR_ALL_ORGANIZATION_UNITS_F — The date-effective master list of every organization unit — business units, inventory orgs, departments, and legal-entity org rows all resolve their ids here, one row per org per effectivity window
-- Purpose: Column-selected read of HR_ALL_ORGANIZATION_UNITS_F — auto-generated from field metadata
-- Grain  : One row per ORGANIZATION_ID + EFFECTIVE_START_DATE + EFFECTIVE_END_DATE
-- Caution: Date-effective: one row per org per EFFECTIVE_START_DATE/EFFECTIVE_END_DATE window — filter to the current row or every join multiplies
-- 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.ORGANIZATION_ID AS "The organization unit's key — the id every org-shaped reference in Fusion ultimately resolves to",
  t.EFFECTIVE_START_DATE AS "Start of this date-effective slice — part of the primary key",
  t.EFFECTIVE_END_DATE AS "End of this date-effective slice — part of the primary key",
  t.BUSINESS_GROUP_ID AS "Enterprise partition the org belongs to",
  t.LEGAL_ENTITY_ID AS "Legal entity the org represents or belongs to",
  t.ORGANIZATION_CODE AS "Organization code",
  t.TYPE AS "User-defined organization type code",
  t.INTERNAL_EXTERNAL_FLAG AS "Internal vs external organization",
  t.LOCATION_ID AS "The org's physical location record",
  t.ESTABLISHMENT_ID AS "Establishment reference for statutory reporting"
FROM <catalog>.<schema>.HR_ALL_ORGANIZATION_UNITS_F t
WHERE
  CURRENT_DATE BETWEEN t.EFFECTIVE_START_DATE AND t.EFFECTIVE_END_DATE  -- current row of a date-effective table — see quirks guide #date-effective
  -- AND t.EFFECTIVE_START_DATE = '<EFFECTIVE_START_DATE>'
  -- AND t.EFFECTIVE_END_DATE = '<EFFECTIVE_END_DATE>'
  -- AND t.LAST_UPDATE_DATE >= TIMESTAMP '<watermark>'  -- the column incremental BICC extracts key on
ORDER BY t.EFFECTIVE_START_DATE;