Results for “pa_adw_orgs_b_v”

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

Overview

PA_ADW_ORGS_B_V is a retrofitted Oracle Projects (PA) view owned by the APPS schema. It is a foundational component of the Oracle Projects Analytics / Data Warehouse (ADW) integration layer, providing a consolidated organization dimension used by Oracle Business Intelligence and other reporting tools that consume Projects data. In Oracle EBS 12.1.1 and 12.2.2, the view exposes a simplified, denormalized list of valid project organizations and business groups, together with a set of placeholder columns (USER_COL1 through USER_COL10) reserved for downstream analytical extensions.

The view has particular significance in the ADW model because it bridges the HR organization hierarchy with the Projects implementation configuration. The joining condition against PA_IMPLEMENTATIONS restricts the returned organizations to those whose HR organization type is a valid project organization type, thereby filtering out non-project entities that exist in HR_ORGANIZATION_UNITS. A UNION branch injects a sentinel row with ORGANIZATION_ID of -1 and the meaning of the 'UNKNOWN' lookup from PA_LOOKUPS ('ADW RESOURCE NAME'), ensuring that the analytics layer always has a placeholder for unresolvable or null organization references.

Underlying Base Objects

The documented base objects referenced by the view and its environment are:

• HR_ORGANIZATION_UNITS (VIEW): Joined twice, aliased HRORG and HRBG. HRORG supplies the project organization record, while HRBG resolves the parent business group name via the self-join on BUSINESS_GROUP_ID = HRBG.ORGANIZATION_ID.
• PA_IMPLEMENTATIONS (SYNONYM): Provides PROJECT_ORGANIZATION_TYPE, which is compared to HRORG.TYPE to confirm the organization qualifies as a project organization.
• PA_LOOKUPS (VIEW): Supplies the 'UNKNOWN' resource name from the ADW RESOURCE NAME lookup type used in the UNION branch.
• HR_SECURITY (PACKAGE) and HR_GENERAL (PACKAGE): Referenced in the broader metadata, reflecting the security and utility logic that governs visibility of HR organizations to the Projects schema.

The view therefore depends on the HR organization model, the Projects implementation setup, and the Projects lookup framework, rather than holding any independent data of its own.

Key Columns

• ORGANIZATION_ID: Primary key of the HR organization unit; -1 for the synthetic UNKNOWN row.
• ORGANIZATION: Name of the project organization (HRORG.NAME) or the lookup meaning for the sentinel row.
• BUSINESS_GROUP: Name of the owning business group (HRBG.NAME), enabling multi-organization reporting.
• USER_COL1 – USER_COL10: Ten NULL placeholder columns reserved for ADW-level customization and future attribute mapping.

Common Use Cases and Queries

Typical uses include populating the organization dimension in Projects data warehouse extracts, validating which HR organizations are recognized as project organizations, and resolving business group context for cross-organization reporting.

Sample query listing project organizations:

• SELECT organization_id, organization, business_group FROM apps.pa_adw_orgs_b_v WHERE organization_id > 0 ORDER BY organization;
• SELECT organization_id, organization FROM apps.pa_adw_orgs_b_v WHERE organization_id = -1;

Because the view is read-only and defined only over HR and PA configuration objects, it is safe for concurrent reporting use; performance depends on indexes on HR_ORGANIZATION_UNITS (ORGANIZATION_ID, BUSINESS_GROUP_ID, TYPE) and the PA_IMPLEMENTATIONS configuration rows.