Results for “per_assignments_v9”

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

Overview

PER_ASSIGNMENTS_V9 is a read-only database view owned by the APPS schema in Oracle E-Business Suite, classified under the PER (Human Resources) product family. Per the ETRM documentation for releases 12.1.1 and 12.2.2, its stated purpose is "used to support user interface" — meaning it exists primarily as a supporting data construct for an Oracle Forms or OAF-based HR screen rather than as a standalone public API or a denormalized reporting table. The view projects a small, curated set of person and assignment attributes associated with positions, filtered to a specific business condition.

Functionally, PER_ASSIGNMENTS_V9 returns employees and applicants who occupy a position, and it resolves position-level date ranges through the PER_GET_ASG_POS package rather than reading raw effective dates from the assignment row. This makes the view useful where position occupancy overlaps assignment occupancy and the caller needs the reconciled start and end dates of the position-held period.

Underlying Base Objects

The documented metadata identifies four referenced objects, all resolved via APPS synonyms: PER_ALL_ASSIGNMENTS_F, PER_ALL_PEOPLE_F, PER_PERSON_TYPES (all synonyms) and the PER_GET_ASG_POS package. The view text is a UNION of two branches:

• First branch selects assignments of type 'E' (employee) where POSITION_ID is not null, joined to PER_ALL_PEOPLE_F constrained to TRUNC(SYSDATE) falling between the person's effective start and end dates. It uses a NOT EXISTS subquery against PER_ALL_ASSIGNMENTS_F to suppress rows that are merely contiguous continuations of the preceding effective-dated row.
• Second branch selects assignments of type 'A' (applicant) where POSITION_ID is not null, with the person and assignment both effective as of TRUNC(SYSDATE). Its effective_start_date is computed by PER_GET_ASG_POS.START_DATE rather than taken directly from the assignment.

Both branches join PER_PERSON_TYPES on PERSON_TYPE_ID (with the +0 no-index hint on the person side) to expose SYSTEM_PERSON_TYPE.

Key Columns

• POSITION_ID — the position occupied by the assignment; always populated given the view's filters.
• ASSIGNMENT_TYPE — 'E' for employee or 'A' for applicant.
• PERSON_ID, FULL_NAME, APPLICANT_NUMBER, EMPLOYEE_NUMBER, DATE_OF_BIRTH — denormalized person attributes from PER_ALL_PEOPLE_F.
• ASSIGNMENT_ID — the assignment identifier.
• START_DATE / EFFECTIVE_START_DATE and EFFECTIVE_END_DATE — the position-held period, resolved through PER_GET_ASG_POS.START_DATE and END_DATE.
• SYSTEM_PERSON_TYPE — the seeded person type classification.
• BUSINESS_GROUP_ID — the HR business group owning the person record.

Common Use Cases and Queries

Because the view is UI-facing, it is most often queried for position-holder lookups and for validating occupancy windows. A typical query returns all current position holders in a business group:

• SELECT person_id, full_name, employee_number, position_id, start_date, effective_end_date FROM apps.per_assignments_v9 WHERE business_group_id = :p_bg ORDER BY full_name;
• SELECT assignment_id, full_name FROM apps.per_assignments_v9 WHERE position_id = :p_position_id AND sysdate BETWEEN start_date AND effective_end_date; — position occupancy check.
• SELECT person_id, full_name, assignment_type FROM apps.per_assignments_v9 WHERE employee_number IS NULL; — isolates applicants.

Direct SQL access is possible but should be treated cautiously: the view depends on PER_GET_ASG_POS and on SYSDATE-based filters, so results reflect the system date at execution rather than a caller-supplied effective date. For date-effective reporting, use PER_ALL_ASSIGNMENTS_F with explicit date parameters instead.