Results for “hr_all_positions_d”

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

Overview

The HR_ALL_POSITIONS_D view is a DateTrack history view owned by the APPS schema within the PER (Human Resources) product module of Oracle E-Business Suite. The view exposes the full effective-dated history of position records, allowing users and developers to query the state of a position as it existed at any point in time. Unlike its sibling view HR_ALL_POSITIONS_F, which returns only the current effective record for each position, HR_ALL_POSITIONS_D returns every dated row, including superseded versions that DateTrack has preserved for audit and reporting purposes. In EBS 12.1.1 and 12.2.2 environments, this view is commonly used in HRMS reports, extracts, and integration interfaces where a time-relative picture of position data is required. The view's status is VALID, and it is documented as being used specifically for DateTrack history.

The user search term location_desc maps directly to one of the view's columns, LOCATION_DESC, which is decoded from the underlying LOCATION_ID via the HR_GENERAL package.

Underlying Base Objects

According to the documented view text and the ETRM metadata, HR_ALL_POSITIONS_D is defined over several base objects:

• HR_ALL_POSITIONS_F (synonym) — the primary table holding the effective-dated position definitions, aliased as PSF in the view SQL.
• PER_POSITION_DEFINITIONS (synonym) — referenced as PPD, used to obtain the ID_FLEX_NUM for the position key flexfield segments.
• PAY_GRADE_RULES (view) — referenced in the join, relevant to grade and salary entry logic.
• HR_GENERAL (package) — supplies the decoding functions such as DECODE_LOCATION, DECODE_JOB, DECODE_POSITION, DECODE_ORGANIZATION, and GET_POSITION_DATE_END.
• FND_FLEX_EXT (package) — supplies GET_SEGS, used to concatenate the position definition key flexfield segments into a NAME value.
• FND_USER (synonym) — typically joined to resolve the last-updated-by user information.

The history is managed by the DateTrack framework, and the view's columns reflect the underlying HR_ALL_POSITIONS_F structure plus derived descriptive columns.

Key Columns

• POSITION_ID — the unique identifier for the position.
• EFFECTIVE_START_DATE / EFFECTIVE_END_DATE — the DateTrack effective dating bounds for the row.
• DATE_EFFECTIVE — the actual effective date of the position record.
• LOCATION_DESC — the decoded location name, derived via HR_GENERAL.DECODE_LOCATION(PSF.LOCATION_ID); this is the column most relevant to the user's search.
• JOB_DESC, ORGANIZATION_DESC, PAY_FREQ_PAYROLL_DESC — decoded descriptive values for job, organization, and payroll frequency.
• RELIEF_POSITION_DESC, SUCCESSOR_POSITION_DESC, SUPERVISOR_POSITION_DESC — decoded position names for related positions.
• SALARY_ENTRY_GRADE_DESC, BARGAINING_UNIT_DESC, FREQUENCY_DESC, OVERLAP_UNIT_DESC, PROBATION_UNIT_DESC — decoded lookup values controlling grade, bargaining unit, and period units.
• NAME — the position definition key flexfield segments concatenated via FND_FLEX_EXT.GET_SEGS.
• FTE, MAX_PERSONS, PROBATION_PERIOD, OVERLAP_PERIOD — numeric position attributes.
• POSITION_TYPE_DESC, REPLACEMENT_REQUIRED_DESC, PERMANENT_TEMPORARY_FLAG — classification and status flags.

Common Use Cases and Queries

Typical uses include historical headcount reporting, position audit trails, and effective-dated extracts for downstream systems. A common query filtered on the location description is:

• SELECT position_id, name, location_desc, effective_start_date, effective_end_date FROM hr_all_positions_d WHERE location_desc = 'Head Office' ORDER BY position_id, effective_start_date;
• SELECT position_id, name, location_desc, date_effective FROM hr_all_positions_d WHERE position_id = 1234 ORDER BY effective_start_date;
• SELECT location_desc, COUNT(DISTINCT position_id) FROM hr_all_positions_d WHERE TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date GROUP BY location_desc;

Because LOCATION_DESC is derived by a package function rather than stored directly, queries filtering on it cannot use an index and may perform a full scan; for high-volume reporting, it is preferable to join to HR_LOCATIONS_ALL or filter on LOCATION_ID. DateTrack history should be interpreted with effective date predicates to avoid returning superseded rows unintentionally.