Search Results per_secondary_ass_statuses_pkg




Overview
Explore in AI Chat

Overview

APPS.PER_SECONDARY_ASS_STATUSES_PKG is a PL/SQL package body in the Oracle E-Business Suite Human Resources (PER) product family. It provides the server-side data-access layer for the PER_SECONDARY_ASS_STATUSES entity, which stores secondary assignment status records associated with an employee assignment. A secondary assignment status captures a status type applicable to an assignment for a defined date range, together with comments, a reason, and a set of descriptive flexfield attribute columns. The package encapsulates the insert, update, delete, and lock operations required to maintain these records while enforcing the validation rules that preserve data integrity across the status history of an assignment.

The package header declares private helper routines alongside the publicly documented DML procedures. These helpers include CHECK_UNIQUE_ROW, which verifies that no overlapping or duplicate status row already exists for the given assignment, status type, and start date; VALIDATE_DATE, which validates a supplied date against the surrounding status timeline for an assignment; VALIDATE_START_END_DATE, which confirms that a proposed start date precedes its end date and does not conflict with adjacent periods; and SET_END_DATE, a function that derives an appropriate end date for an existing record. The presence of VALIDATE_START_END_DATE explains why searches for that term resolve to this package: it is the internal routine responsible for the date-ordering and overlap validation applied during status maintenance.

Key Procedures and Functions

  • INSERT_ROW — Creates a new secondary assignment status record. It accepts the business group, assignment, status type, start and end dates, comments, reason, and descriptive flexfield attribute values, returning the generated primary key and row identifier. During execution it applies the package's uniqueness and date-range validations before writing to the base table.
  • LOCK_ROW — Obtains a row-level lock on an existing secondary assignment status record, typically by row identifier, so that a concurrent update or delete cannot modify the row while the caller is processing it. This supports the standard Oracle Forms optimistic locking model.
  • UPDATE_ROW — Modifies an existing secondary assignment status record, including its status type, dates, comments, reason, and flexfield attributes. It re-validates the revised date range against neighbouring records to prevent overlaps.
  • DELETE_ROW — Removes a secondary assignment status record from the base table after the caller has secured the row.

The package also exposes the helper procedures and the SET_END_DATE function to internal callers; these are not part of the documented external API surface but are essential to the validation behaviour of the DML routines.

Tables Accessed

  • PER_SECONDARY_ASS_STATUSES — The primary transactional table. All insert, update, delete, and lock operations target this table, and it holds the status type, date range, reason, comments, and attribute columns for each record.
  • PER_SECONDARY_ASS_STATUSES_S — The corresponding sequence-backed key source (or translated/secure view) used to generate and resolve the SECONDARY_ASS_STATUS_ID primary key during insert operations.

Both objects are referenced through APPS synonyms, so the package operates within the standard APPS schema and inherits the associated grants and row-level security conventions.

Usage Notes

This package is an internal server-side API rather than a public, supported interface. It is principally invoked by the Oracle Forms application that maintains secondary assignment statuses, where the LOCK_ROW, INSERT_ROW, UPDATE_ROW, and DELETE_ROW procedures back the standard block-level DML events. The validation helpers, including VALIDATE_START_END_DATE, execute transparently within those DML calls and are not intended for direct invocation.

Because the package is referenced by one other package, customisations should avoid calling it directly and should instead use the supported PER APIs or the underlying tables through approved interfaces. Any direct use must supply the correct business group context and honour the date-range and uniqueness rules enforced internally; bypassing them risks overlapping status periods and inconsistent assignment history.