Results for “edwbv_organization_lcv”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

EDWBV_ORGANIZATION_LCV is a Business Intelligence (BI) view published within the Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 ETRM (E-Business Suite Technical Reference Manual) repository, categorized under the PER (Human Resources) product module. The view forms part of the Oracle Enterprise Data Warehouse (EDW) layer, which provides denormalized, reporting-optimized structures intended to support extracts, analytics, and downstream integration with Oracle Business Intelligence Applications (OBIA) and third-party data warehouses.

The view resolves the organizational hierarchy in EBS by combining the base organization unit definition with its business group, operating unit, accounting information, and inventory parameters into a single flattened representation. The name suffix "LCV" reflects its classification as a Level/List Change View used in the EDW change-data-capture framework, and the embedded "_DF:PER:..." timestamp markers signal that downstream ETL processes track the last update date of each contributing source table for incremental refresh.

Although the ETRM notes that this object is "not implemented in this database," the view text is fully documented and remains a standard deliverable in the EDW schema of ERP installations.

Underlying Base Objects

While ETRM lists no formally registered base objects, the view SQL reveals a well-defined dependency set drawn from the HR and Inventory modules:

• HR_ALL_ORGANIZATION_UNITS — referenced three times with aliases ORG (the primary organization), BG (the business group), and OU (the operating unit), establishing the self-referential hierarchy.
• HR_ORGANIZATION_INFORMATION — joined twice: OI1 with the 'ACCOUNTING INFORMATION' context to resolve the operating unit, and OI2 with the 'CLASS' context filtered to 'OPERATING_UNIT' and enabled flag 'Y'.
• MTL_PARAMETERS — outer-joined on ORGANIZATION_ID to supply inventory organization code and costing method.
• EDW_LOCAL_INSTANCE — provides the INSTANCE_CODE used to prefix each key, enabling multi-instance consolidation.

All joins involving OI1, OI2, OU, and MP are outer joins, ensuring that organizations lacking accounting or inventory configuration are still reported.

Key Columns

• ORGANIZATION_PK — surrogate key combining ORGANIZATION_ID and INSTANCE_CODE, formatted as "ORG_ID-INSTANCE".
• OPERATING_UNIT_FK — resolves to the operating unit identifier; when no operating unit is present, the DECODE logic returns the parent operating unit or appends "-BGRP" for business group-level rows.
• ORGANIZATION_DP — the display name, formatted as "Organization Name (Business Group Name)".
• NAME — concatenated descriptive label used in reporting.
• DATE_FROM / DATE_TO — effective dating of the organization record.
• BUSINESS_ (truncated in metadata) — business group name.
• INTERNAL_EXTERNAL_FLAG, TYPE — classification attributes of the organization.
• ORGANIZATION_CODE, PRIMARY_COST_METHOD — inventory-specific attributes from MTL_PARAMETERS.
• INSTANCE_CODE — source instance label for multi-instance deployments.

Common Use Cases and Queries

This view is typically used to populate organizational dimension tables in an EDW or to drive HR and inventory reporting across multiple operating units. A representative query lists all active organizations with their business group and operating unit context:

SELECT organization_pk,
       operating_unit_fk,
       name,
       date_from,
       date_to,
       internal_external_flag,
       instance_code
FROM   edwbv_organization_lcv
WHERE  date_to IS NULL OR date_to > SYSDATE;

For multi-instance consolidation, analysts add a filter on INSTANCE_CODE. For inventory-specific extracts, the ORGANIZATION_CODE and PRIMARY_COST_METHOD columns are projected alongside MTL_PARAMETERS attributes. The GREATEST() timestamp expression in the view text enables incremental extraction by identifying the most recent change across ORG, BG, OU, OI1, MP, and OI2.