Search Results psp_assignments_v




Overview
Explore in AI Chat

Overview

The PSP_ASSIGNMENTS_V view is a Labor Distribution (PSP) reporting object owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes employee assignment records together with a derived assignment status, which is the attribute most frequently sought when this view is queried. The view is registered as VALID and is documented in ETRM as "Shows assignments for an employee."

Its principal role is to present a payroll-context view of an employee's assignments, including the associated business group, set of books (via GL_SET_OF_BOOKS_ID), and the effective date span for each assignment. Because the view resolves the active status of an assignment rather than requiring callers to reconstruct status history themselves, it is commonly used in labor distribution reporting, cost allocation extracts, and integration routines that must determine whether an assignment is currently active. The status returned is not simply the raw lookup value stored on the assignment record; it is conditionally resolved by comparing the current system date against the computed effective date of the assignment.

Underlying Base Objects

The view text shows the principal sources used to build each row:

An additional documented dependency set includes HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY (packages), which support the standard HR name and security predicates applied elsewhere in this object family. Note that the view text uses a hard-coded status type identifier of 8 for the active-status fallback, and treats the sentinel end date of 31-DEC-4712 as a null (open-ended) assignment.

Key Columns

  • PERSON_ID — identifier of the employee to whom the assignment belongs.
  • ASSIGNMENT_ID — identifier of the assignment record.
  • BUSINESS_GROUP_ID — the business group owning the assignment.
  • SET_OF_BOOKS_ID — set of books derived from the assignment's payroll, enabling ledger-based reporting.
  • BEGIN_DATE — earliest effective start date of the assignment.
  • END_DATE — latest effective end date, nulled when the sentinel 31-DEC-4712 is present.
  • EFFECTIVE_DATE — the greatest of the earliest start date, current date, and latest end date; used to drive the status decode.
  • ASSIGNMENT_STATUS — the resolved user status; when the effective date already lies in the past, the historical status (PAST.USER_STATUS) is returned, otherwise the status type identified by ID 8 is applied.

Common Use Cases and Queries

The view is typically used to enumerate assignments with a current status label or to retrieve ledger and business-group context for a person. A representative query is:

  • SELECT person_id, assignment_id, business_group_id, set_of_books_id, begin_date, end_date, assignment_status FROM apps.psp_assignments_v WHERE assignment_status = 'Active';
  • Filtering by person_id to return all assignments for a specific employee.
  • Joining to PER_PEOPLE_F or HR name views to produce assignment listings with employee names.
  • Using set_of_books_id and business_group_id as grouping keys in labor distribution and cost allocation extracts.

Because HR security packages are among the documented dependencies, queries should respect the security profile applied to the APPS schema, particularly in multi-business-group environments.