Fusion Reference

HR_ALL_ORGANIZATION_UNITS_F

Product: PERmasterPer inventory orgDate-effective

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

Module: FoundationInventory-org partitioned (ORGANIZATION_ID)EBS equivalent: MTL_PARAMETERS EBS equivalent: HR_ALL_ORGANIZATION_UNITS EBS equivalent: ORG_ORGANIZATION_DEFINITIONS

Grain note

Date-effective: one row per org per EFFECTIVE_START_DATE/EFFECTIVE_END_DATE window — filter to the current row or every join multiplies

Notes

The organization NAME is not on this table — it lives in the translation companion (HR_ORGANIZATION_UNITS_F_TL, note the missing ALL — not yet cataloged), so name lookups are language-filtered. No enabled flag either: active status comes from the org classification rows. The ORGANIZATION_ID here is what INV_ORG_PARAMETERS and FUN_ALL_BUSINESS_UNITS_V ids resolve to.

What the badges mean

master: Data class: what the table holds — master data, transaction documents, control/configuration, interface/staging, or a documented view.

Fields

10 fields · 3 key

Table fields: position, field name, description, data type, and flags. 10 fields.
#	Field	Description	Type	Flags
1	ORGANIZATION_ID	The organization unit's key — the id every org-shaped reference in Fusion ultimately resolves to	NUMBER	Key
2	EFFECTIVE_START_DATE	Start of this date-effective slice — part of the primary key	DATE	Key
3	EFFECTIVE_END_DATE	End of this date-effective slice — part of the primary key	DATE	Key
4	BUSINESS_GROUP_ID	Enterprise partition the org belongs to	NUMBER
5	LEGAL_ENTITY_ID	Legal entity the org represents or belongs to	NUMBER
6	ORGANIZATION_CODE	Organization code	VARCHAR2
7	TYPE	User-defined organization type code	VARCHAR2
8	INTERNAL_EXTERNAL_FLAG	Internal vs external organization	VARCHAR2
9	LOCATION_ID	The org's physical location record	NUMBER
10	ESTABLISHMENT_ID	Establishment reference for statutory reporting	NUMBER

Field provenance: hand-curated. 3 key fields.

Boilerplate SQL

Starting point for reading the BICC-landed copy of HR_ALL_ORGANIZATION_UNITS_Fon Databricks — real DATE columns need no conversion, and the org anchor and the LAST_UPDATE_DATE watermark (the column incremental BICC extracts key on) are already in place. Set your Unity Catalog location, schema, and org values below; they’re substituted into the SQL and the copy button.

Query parameters

CatalogSchemaInventory org (ORGANIZATION_ID)Effective from (yyyy-mm-dd)Effective to (yyyy-mm-dd)watermark

Include optional filters (3)

-- ============================================================
-- 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 inventory org (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
  t.ORGANIZATION_ID = <inventory_org_id>  -- inventory org, NOT the business unit — see quirks guide #item-org-striping
  AND 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;

6 parameters not filled: <catalog>, <schema>, <inventory_org_id>, <EFFECTIVE_START_DATE>, <EFFECTIVE_END_DATE>, <watermark>

Relationships

1-hop neighbors — click a table to navigate there. FND lookup decode and translation edges are highlighted; they’re the joins newcomers most often get wrong.

Join details

FUN_ALL_BUSINESS_UNITS_VHR_ALL_ORGANIZATION_UNITS_Fforeign key · 1:N
ON FUN_ALL_BUSINESS_UNITS_V.BU_ID = HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID
INV_ORG_PARAMETERSHR_ALL_ORGANIZATION_UNITS_Fforeign key · 1:N
ON INV_ORG_PARAMETERS.ORGANIZATION_ID = HR_ALL_ORGANIZATION_UNITS_F.ORGANIZATION_ID

Browse more Foundationtables →