Search Results person_manager_fk_key




Overview
Explore in AI Chat

Overview

EDW_ORGA_ORG_LCV is a collection view owned by the APPS schema and delivered as part of the HRI (Human Resources Intelligence) product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It serves as the Organization level collection view within the Organization dimension of the Oracle HR Intelligence / Enterprise Data Warehouse (EDW) data model. In this role, the view presents a denormalized, reporting-ready projection of organizational hierarchy data that downstream ETL processes, dimensional loads, and analytical queries consume.

The view functions as a semantic abstraction layer between the raw organization staging structures and the presentation layer used by HR analytics, dashboards, and organizational reporting. Its "LCV" suffix denotes a Level Collection View, indicating it supplies column definitions and collection-level metadata used to populate Organization dimension levels. Because it is a view rather than a table, no physical storage is allocated to it; it is resolved at runtime against the underlying base objects documented in the HRI schema.

Underlying Base Objects

The ETRM documentation records that EDW_ORGA_ORG_LCV is defined over EDWBV_ORGA_ORG_LCV, as reflected in the view text: the SELECT list is drawn directly from that base object. No additional base tables are documented in the supplied metadata; the "Referenced base objects" field is empty. In practice, EDWBV_ORGA_ORG_LCV itself is the business view layer for the Organization dimension, which in turn draws from HR organization staging tables within the EDW schema. Administrators should treat EDWBV_ORGA_ORG_LCV as the immediate dependency and trace further lineage through the HRI data model documentation if deeper impact analysis is required.

The view retains the naming conventions of the HRI collection layer, and its structure mirrors the target Organization level definition. The trailing NULL placeholders in the view text correspond to USER_ATTRIBUTE1 through USER_ATTRIBUTE5, which are exposed as columns but populated with NULL literals at this layer, reserving the descriptive-flexfield slots for downstream customization.

Key Columns

  • ORGANIZATION_PK — Primary key surrogate for the organization record; the principal join key for the dimension.
  • ORG_INT_EXT_FLAG — Internal/external indicator for the organization, distinguishing internal operating entities from external parties. This is the column most relevant to the user search term "org_int_ext_flag."
  • OPERATING_UNIT_FK — Foreign key linking the organization to its operating unit.
  • ORG_TREE1_LVL1_FK — Foreign key to the top level of organization hierarchy tree 1.
  • NAME, ORG_CODE, ORG_TYPE — Descriptive attributes identifying the organization, its short code, and classification.
  • ORGANIZATION_DP, DATE_FROM, DATE_TO — Date-effective tracking columns supporting slowly changing dimension behavior.
  • BUSINESS_GROUP, ORGANIZATION_ID, INSTANCE — Key HR identifiers, including the Business Group and the source instance.
  • ORG_PRIM_CST_MTHD — The primary costing method assigned to the organization.
  • LEVEL_NAME — The hierarchy level designation for the organization.
  • PERSON_MANAGER_ID, PERSON_MANAGER_FK, PERSON_MANAGER_FK_KEY — Manager identification and foreign keys linking to the person dimension.
  • ORG_CAT1 – ORG_CAT15 — Fifteen organization category/descriptive flexfield slots for classification and analysis.
  • LAST_UPDATE_DATE, CREATION_DATE — Audit columns; USER_ATTRIBUTE1–5 are exposed as NULLs.

Common Use Cases and Queries

Typical uses include populating the Organization dimension in the HR Intelligence warehouse, filtering internal versus external organizations, and joining to person and hierarchy dimensions for managerial reporting.

  • Retrieve all internal organizations by name and code:

SELECT ORGANIZATION_PK, NAME, ORG_CODE, ORG_TYPE, ORG_INT_EXT_FLAG
FROM APPS.EDW_ORGA_ORG_LCV
WHERE ORG_INT_EXT_FLAG = 'I';

  • Join to the person manager dimension for organizational reporting:

SELECT o.NAME, o.ORG_CODE, o.LEVEL_NAME, o.PERSON_MANAGER_ID
FROM APPS.EDW_ORGA_ORG_LCV o
WHERE o.BUSINESS_GROUP = :p_bg
AND SYSDATE BETWEEN o.DATE_FROM AND NVL(o.DATE_TO, SYSDATE);

  • Group organizations by primary costing method for cost analysis:

SELECT ORG_PRIM_CST_MTHD, COUNT(*)
FROM APPS.EDW_ORGA_ORG_LCV
GROUP BY ORG_PRIM_CST_MTHD;

Because the view is date-effective, queries should generally constrain on DATE_FROM and DATE_TO to return the correct version of each organization record.