Search Results per_pos_structure_versions_v




Overview
Explore in AI Chat

Overview

PER_POS_STRUCTURE_VERSIONS_V is an APPS-owned database view in the Oracle E-Business Suite Human Resources (PER) product family. Per the ETRM documentation, it is a VALID view whose stated purpose is to support the user interface. It exposes version records belonging to position structures defined in Oracle HRMS, and it is the primary presentation-layer object from which the Position Structures and Position Hierarchy windows and related list-of-values constructs resolve version information.

The view is significant because a position structure in Oracle HRMS is versioned: each structure can have multiple dated versions, and each version can itself be created as a copy of an earlier version. The view flattens this self-referencing relationship into a single user-friendly row, resolving the copied-from structure name and the copied version number into display columns. This makes it a convenient reporting and integration target for anyone tracing the lineage of a position hierarchy.

Underlying Base Objects

The documented base objects referenced by PER_POS_STRUCTURE_VERSIONS_V are PER_POSITION_STRUCTURES and PER_POS_STRUCTURE_VERSIONS, both accessed through APPS synonyms in the 12.2.2 ETRM metadata. The view text performs a self-join between two aliases of PER_POS_STRUCTURE_VERSIONS (PSV and PSV2) combined with an outer join to PER_POSITION_STRUCTURES (PST).

The join condition is expressed with an OR: when COPY_STRUCTURE_VERSION_ID is populated, PSV2 is matched on the copied version's ID; when it is null, PSV2 is matched by ROWID so that the row pairs with itself. This dual-branch pattern allows the same view definition to return the source version's number for copied versions while still returning a row for original versions. The outer join to PER_POSITION_STRUCTURES on POSITION_STRUCTURE_ID supplies the structure name (PST.NAME) used in the COPIED_POS_STRUCTURE_ID column.

Key Columns

  • ROW_ID — the ROWID of the underlying PER_POS_STRUCTURE_VERSIONS row; used by Oracle Forms for optimistic locking and row identification.
  • POS_STRUCTURE_VERSION_ID — primary key of the structure version.
  • BUSINESS_GROUP_ID — the HR business group (legislation/security context) that owns the version.
  • POSITION_STRUCTURE_ID — the parent position structure this version belongs to; this is the column most commonly used to filter the view.
  • DATE_FROM / DATE_TO — effective dates of the version; DATE_TO null indicates the current version.
  • VERSION_NUMBER — the sequence number of the version within the structure.
  • COPY_STRUCTURE_VERSION_ID — the version from which this version was copied, if any.
  • COPIED_POS_STRUCTURE_ID — decoded structure name of the copied-from structure (PST.NAME).
  • COPIED_VERSION_NUMBER — the VERSION_NUMBER of the copied-from version, resolved via PSV2.
  • REQUEST_ID, PROGRAM_APPLICATION_ID, PROGRAM_ID, PROGRAM_UPDATE_DATE — concurrent program audit columns identifying the request that created or updated the row.
  • LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATE — standard WHO audit columns.

Common Use Cases and Queries

The most frequent use case is determining the current version of a given position structure, or enumerating all historical versions of it. Because the view exposes both structural and audit information, it also supports lineage reporting — identifying which structure a version was copied from and at what version.

The following query returns all versions of a position structure, most recent first:

  • SELECT pos_structure_version_id, position_structure_id, version_number, date_from, date_to, copied_version_number FROM apps.per_pos_structure_versions_v WHERE position_structure_id = :p_structure_id ORDER BY date_from DESC;

To isolate the current, open-ended version:

  • SELECT pos_structure_version_id, version_number, date_from FROM apps.per_pos_structure_versions_v WHERE position_structure_id = :p_structure_id AND date_to IS NULL;

For lineage analysis of copied versions, filter on COPY_STRUCTURE_VERSION_ID being not null and select COPIED_POS_STRUCTURE_ID and COPIED_VERSION_NUMBER to display the source. Because the view is not date-tracked in the DateTrack sense, consumers performing as-of reporting should apply their own DATE_FROM/DATE_TO predicates. Integration and reporting queries should always qualify the APPS schema and observe business group security, since PER objects are partitioned by BUSINESS_GROUP_ID.