Search Results per_assignments_f_pk




Overview
Explore in AI Chat

Overview

APPS.HR_ASSIGNMENT_SET_AMENDMENTS_V is a reporting and integration view in Oracle E-Business Suite (EBS) 12.1.1 and 12.2.2 that exposes the contents of the HR_ASSIGNMENT_SET_AMENDMENTS table together with descriptive attributes resolved from the person, assignment, and lookup layers. The view presents, for each assignment-set amendment record, the assignment identifier, the assignment set to which it belongs, the full name of the person holding the assignment, the assignment number, and the decoded include/exclude indicator used to add or remove an assignment from an assignment set.

Its principal design characteristic is date-effectivity filtering driven by the current user session. The definition joins FND_SESSIONS on SESSION_ID = USERENV('SESSIONID') and restricts both PER_ALL_ASSIGNMENTS_F and PER_ALL_PEOPLE_F to rows where the session effective date falls between their ELEMENT_START/END effectivity boundaries. This means the view returns only the assignment and person versions valid for the session's effective date, rather than the full history held in the underlying date-tracked tables. A second predicate restricts the person row to CURRENT_EMPLOYEE_FLAG = 'Y', so the view is oriented toward current employees.

Underlying Base Objects

The documented base objects referenced by the view are:

  • HR_ASSIGNMENT_SET_AMENDMENTS (synonym) — the driving table holding the amendment records, supplying ASSIGNMENT_ID, ASSIGNMENT_SET_ID, INCLUDE_OR_EXCLUDE, and the standard WHO audit columns.
  • PER_ALL_ASSIGNMENTS_F (synonym) — the date-tracked assignment entity, joined on ASSIGNMENT_ID to supply ASSIGNMENT_NUMBER and to enforce effectivity against the session date.
  • PER_ALL_PEOPLE_F (synonym) — the date-tracked person entity, joined on PERSON_ID to supply FULL_NAME and CURRENT_EMPLOYEE_FLAG.
  • FND_SESSIONS (synonym) — supplies the session effective date used for the effectivity filter.
  • HR_LOOKUPS (view) — resolved twice, once for the INCLUDE_EXCLUDE lookup type to decode the include/exclude code, and once for YES_NO_ALL to decode the current-employee switch.
  • HR_API (package) — a dependent object recorded against the view.

The view text carries explicit optimizer hints — ORDERED, USE_NL, and an INDEX hint naming PER_ASSIGNMENTS_F_PK — indicating that the developers intended a nested-loop plan driven from the amendment table into PER_ALL_ASSIGNMENTS_F by primary key. Because ASG is PER_ALL_ASSIGNMENTS_F, the hint PER_ASSIGNMENTS_F_PK (the user's search term) is the unique index used to probe the assignment row for each amendment record. The view is a UNION ALL of two branches: one handling rows where CURRENT_EMPLOYEE_FLAG is 'Y', and one handling rows where the flag is NULL (treated as 'N' via NVL).

Key Columns

  • ROW_ID — the ROWID of the HR_ASSIGNMENT_SET_AMENDMENTS row, enabling direct DML on the base record.
  • ASSIGNMENT_ID — identifier of the assignment being included in or excluded from the set.
  • ASSIGNMENT_SET_ID — identifier of the assignment set affected by the amendment.
  • INCLUDE_OR_EXCLUDE — the decoded lookup meaning (uppercased) for the include/exclude action.
  • INC_EXC_CODE — the raw lookup code stored on the amendment row.
  • FULL_NAME — the person's full name from PER_ALL_PEOPLE_F.
  • ASSIGNMENT_NUMBER — the assignment's business-facing number.
  • CURRENT_EMPLOYEE_FLAG — the person's current-employee indicator, defaulted to 'N' via NVL.
  • CURRENT_EMP_SWITCH — the decoded YES_NO_ALL meaning for the flag.
  • CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard audit columns carried from the amendment table.

Common Use Cases and Queries

The view is typically used when reporting or validating the composition of assignment sets, and when feeding assignment-set membership data into downstream processes such as payroll, benefits eligibility, or security grouping.

  • Listing amendments for a given set: SELECT assignment_id, assignment_number, full_name, include_or_exclude FROM hr_assignment_set_amendments_v WHERE assignment_set_id = :set_id;
  • Confirming current employees only: the view already filters on CURRENT_EMPLOYEE_FLAG, so a query returns only current employees effective on the session date.
  • Auditing recent changes: SELECT row_id, assignment_set_id, last_updated_by, last_update_date FROM hr_assignment_set_amendments_v ORDER BY last_update_date DESC;

Because effectivity is session-driven through FND_SESSIONS, results are meaningful only within an EBS session context; direct SQL from outside the application may not return the expected effective-date rows.