Results for “per_position_structures_all_v”

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

Overview

The PER_POSITION_STRUCTURES_ALL_V view is a denormalized, read-only reporting object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Within the ETRM repository it is cataloged under the PO – Purchasing product module and carries the description "Retrofitted," indicating that its definition was reconstructed to align with a legacy or upgraded data model. The view resolves the raw identifiers held in the HR position model into business-friendly text, exposing the position name, the organization (business unit) name, and the associated job name in a single statement. This makes it particularly useful for consumers who search on a term such as position_name and require a flattened result set rather than a chain of joins across the PER schema. The view is treated as a query surface for reporting, concurrent program output, and integration extracts rather than as a transactional object; no DML should be issued against it.

Underlying Base Objects

The documented definition joins four sources. PER_POSITIONS supplies the position rows (PP) and is the anchor of the query. PER_JOBS (PJ) is joined on JOB_ID to bring in the job name. PER_ORGANIZATION_UNITS (POU) is joined on ORGANIZATION_ID to bring in the organization name. Finally, FINANCIALS_SYSTEM_PARAMETERS (FSP) is used solely to constrain the BUSINESS_GROUP_ID, restricting output to the business group configured for the current financials installation. ETRM further documents that PER_POSITION_UNITS, PER_POSITIONS and PER_JOBS are referenced through synonyms, while PER_ORGANIZATION_UNITS is itself a view layered over HR organization tables, and that the HR_GENERAL and HR_SECURITY packages participate in the lineage. The inclusion of HR_SECURITY is significant: because PER_POSITION_UNITS and PER_POSITIONS are secured views, the rows returned to any session are filtered by the security profile of the accessing user, so two users may legitimately see different result sets from the same SELECT. The DISTINCT keyword suppresses duplicate combinations produced by the join path.

Key Columns

• POSITION_NAME — the descriptive name of the position (PP.NAME); the column most frequently targeted by ad hoc searches for "position_name".
• ORGANIZATION_NAME — the name of the organization unit (POU.NAME) to which the position belongs.
• JOB_NAME — the name of the job (PJ.NAME) associated with the position.
• POSITION_ID — the surrogate primary key of the position, used for joins back to PER_POSITIONS or downstream tables.
• JOB_ID — the surrogate key of the job, used for joins to PER_JOBS and job-related reporting.
• ORGANIZATION_ID — the organization unit identifier, the key used for joins to HR organization and assignment data.

All three identifiers are exposed alongside their descriptive names, allowing the view to serve both as a presentation layer and as a bridging object for further joins.

Common Use Cases and Queries

Typical uses include position listings for a selected organization, validation extracts feeding purchasing or HR interfaces, and reference lookups in custom concurrent programs. Because the view is restricted to the current business group via FINANCIALS_SYSTEM_PARAMETERS, it is not suitable for cross–business group reporting. A representative query listing positions for a given organization is:

SELECT position_name,
       organization_name,
       job_name,
       position_id,
       job_id,
       organization_id
  FROM apps.per_position_structures_all_v
 WHERE organization_id = :p_organization_id
 ORDER BY position_name;

To locate a position by partial name, a case-insensitive search is usual:

SELECT position_name, organization_name, job_name
  FROM apps.per_position_structures_all_v
 WHERE UPPER(position_name) LIKE UPPER('%' || :p_search || '%');

To count positions by job within each organization:

SELECT organization_name, job_name, COUNT(*) position_count
  FROM apps.per_position_structures_all_v
 GROUP BY organization_name, job_name
 ORDER BY organization_name, job_name;

Analysts should note that returned rows are subject to HR security profile filtering, and that the DISTINCT clause hides multiplicity in the underlying join, so the view should not be used to infer one-to-one cardinality between positions and organizations.