Search Results edwbv_orga_org_lcv




Overview
Explore in AI Chat

Overview

EDWBV_ORGA_ORG_LCV is a database view owned by the APPS schema in Oracle E-Business Suite, delivered as part of the HRI (Human Resources Intelligence) product family, the HR analytics component of Oracle's E-Business Suite Intelligence applications. It is documented with a status of VALID and functions as the base view for the Organization level of the Organization dimension. In ETRM architecture, dimensional base views of this type supply the lowest, most granular layer of a dimension; the higher-level materialized views, levels, and hierarchies used by the HR Intelligence warehouse and its dashboards are built upon the rows this view returns.

The view is relevant to both Oracle EBS 12.1.1 and 12.2.2, since the HR Intelligence schema and its dimensional objects remained fundamentally the same across these releases. Its principal role is to denormalize and conform organization attributes drawn from the HR organization model into a structure suitable for reporting and integration, mapping each organization to a surrogate primary key, to an operating unit (or business group), and to a position in the organization hierarchy tree.

Underlying Base Objects

The documented metadata does not enumerate the referenced base objects, but the view text demonstrates that EDWBV_ORGA_ORG_LCV is defined over the core HR organization tables and supporting warehouse helpers. The aliases in the SELECT reveal the following dependencies:

  • ORG — the HR organization definition (HR_ORGANIZATION_UNITS), supplying the organization ID, name, type, internal/external flag, and date-effective ranges.
  • BGR — the business group record associated with the organization.
  • OU — the operating unit above the organization, used to derive the operating unit foreign key.
  • OI2 — organization information of classification type, used to test whether an organization is itself an operating unit.
  • MP — the organization's primary master/definition record (for example, the inventory organization), providing the organization code and primary cost method.
  • TREE — the organization hierarchy tree, supplying the organization level and parent organization ID.
  • INST — the instance code identifying the source EBS instance.
  • HR_GENERAL.DECODE_LOOKUP — the standard lookup decoding function used to translate internal flags and organization types into display values.

Key Columns

  • ORGANIZATION_PK — the surrogate primary key, composed as organization ID concatenated with the instance code.
  • OPERATING_UNIT_FK — points to the operating unit above the organization; if none exists and the organization is itself an operating unit, it points to itself, otherwise it points to the business group level.
  • ORG_TREE1_LVL1_FK — the foreign key into level 1 of the organization hierarchy, handling organizations outside the primary hierarchy (NA_EDW) and levels deeper than eight.
  • NAME / ORGANIZATION_DP — the display name, formed from the organization name with the business group name in parentheses.
  • DATE_FROM / DATE_TO — the date-effective range of the organization record.
  • BUSINESS_GROUP — the business group name.
  • INT_EXT_FLAG / ORG_TYPE — decoded lookup values for internal/external status and organization classification.
  • ORG_CODE / ORG_PRIM_CST_MTHD — the organization code and primary cost method.
  • INSTANCE — the source instance code, enabling multi-instance consolidation.

Common Use Cases and Queries

This view is typically queried directly for organization-level reporting, or joined to fact views in the HR Intelligence star schema. A simple retrieval of organizations for a given instance:

  • SELECT organization_pk, name, business_group, org_type FROM apps.edwbv_orga_org_lcv WHERE instance = :instance_code ORDER BY name;
  • Hierarchy analysis, resolving each organization to its level‑1 ancestor: SELECT organization_pk, org_tree1_lvl1_fk, name FROM apps.edwbv_orga_org_lcv;
  • Operating unit rollup: SELECT operating_unit_fk, COUNT(*) FROM apps.edwbv_orga_org_lcv GROUP BY operating_unit_fk;

Because the view exposes decoded values and prebuilt foreign keys, it is well suited to feeding BI Publisher reports, OBIEE repositories, and custom extracts without re-implementing the hierarchy logic.