Results for “ghr_ss_pos_chg_dates_v”

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

Overview

GHR_SS_POS_CHG_DATES_V is a read-only database view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It belongs to the GHR — US Federal Human Resources product family and is documented in ETRM as being consumed by Fed SS (Federal Self-Service) views. The view presents a denormalized, point-in-time snapshot of assignment and position change activity, enriching each assignment with effective-dated position attribute identifiers resolved through the GHR_PA_HISTORY audit trail. Because it is defined over assignment, position, and the GHR public-sector history table, it serves as a reporting and integration substrate rather than a transactional object.

Underlying Base Objects

The view is defined over a subquery that selects from PER_ASSIGNMENTS_F2 (itself a view), joined implicitly with position data. Its documented base objects are:

• PER_ASSIGNMENTS_F2 (VIEW) — supplies PERSON_ID, ASSIGNMENT_ID, ASSIGNMENT_NUMBER, PRIMARY_FLAG, and EFFECTIVE_DATE.
• HR_ALL_POSITIONS_F (SYNONYM) — supplies POSITION_ID and position context.
• PER_POSITION_EXTRA_INFO (SYNONYM) — holds the Federal position-level descriptive flexfield records (e.g., GHR_US_POS_GRP1, GHR_US_POS_GRP2, GHR_US_POS_VALID_GRADE) that are resolved to history identifiers.
• GHR_PA_HISTORY (SYNONYM) — the public-sector history table used to resolve the effective-dated PA_HISTORY_ID for each position extra-info record as of the assignment effective date.
• HR_GENERAL and HR_SECURITY (PACKAGE) — provide utility and row-level security logic invoked by the parent Fed SS views.

The correlated subqueries apply a "latest row not exceeding the change effective date" pattern, selecting MAX(PA_HISTORY_ID) where the history effective date is the maximum value less than or equal to POS_CHG.EFFECTIVE_DATE.

Key Columns

• PERSON_ID — unique identifier of the person associated with the assignment.
• ASSIGNMENT_ID — primary key of the assignment record in PER_ASSIGNMENTS_F.
• ASSIGNMENT_NUMBER — the user-facing alphanumeric assignment identifier; the term most commonly searched by report authors and support analysts.
• PRIMARY_FLAG — indicates whether the assignment is the person's primary assignment (Y/N).
• POSITION_ID — foreign key to HR_ALL_POSITIONS_F identifying the position held.
• EFFECTIVE_DATE — the date on which the assignment/position change takes effect; drives the point-in-time resolution of the history subqueries.
• POS_GRP1_ID, POS_GRP2_ID — resolved GHR_PA_HISTORY identifiers for the Federal position group 1 and group 2 extra-info attributes valid as of the row's effective date.
• POS_VALID_GRADE_ID — resolved history identifier for the position's valid grade attribute.

Common Use Cases and Queries

Typical use is Federal reporting that requires assignment history aligned to position change dates, particularly where position group or valid-grade values must be reconstructed as of a historical effective date. A sample query retrieving assignment details for a person is:

SELECT assignment_number, effective_date, position_id, pos_grp1_id, pos_grp2_id, pos_valid_grade_id FROM apps.ghr_ss_pos_chg_dates_v WHERE person_id = :p_person_id ORDER BY effective_date;

Analysts frequently filter by ASSIGNMENT_NUMBER to trace a specific assignment's change timeline, and by EFFECTIVE_DATE range to produce point-in-time position attribute extracts. Because the view is not secured at the row level itself, callers should apply HR_SECURITY business-group and security-profile predicates when querying outside the delivered Fed SS views.