Search Results per_sps_upd




Overview
Explore in AI Chat

Overview

PER_SPS_SHD is a shadow (audit) package in the Oracle E-Business Suite APPS schema that supports the PER_SPS family of APIs responsible for maintaining the PER_SPINAL_POINT_STEPS_F entity — the table that stores spinal point step definitions used by Oracle Payroll and related compensation structures. In the documented 12.2.2 metadata the package is classified as an OTHER API, reflecting its role as an internal implementation component rather than a public business interface. Its principal purpose is to preserve historical and audit information for rows in PER_SPINAL_POINT_STEPS_F, so that every insert, update, or delete performed through the PER_SPS_BUS, PER_SPS_INS, PER_SPS_UPD, and PER_SPS_DEL packages leaves a corresponding shadow record. This shadow mechanism supports retroactive payroll processing, date-tracked corrections, and auditing of configuration changes to spinal point steps, which materially affect salary progression and grade step calculations. The user search term "per_sps_upd" identifies one of the four packages that depend upon PER_SPS_SHD, confirming that the shadow package is invoked whenever the update API manipulates spinal point step data.

Key Procedures and Functions

The documented package exposes eight procedures and functions, each serving a specific control or bookkeeping purpose within the shadow-write lifecycle:

  • RETURN_API_DML_STATUS — returns the DML status of the most recent API operation, allowing the caller to determine whether the shadow write succeeded.
  • CONSTRAINT_ERROR — handles constraint violation conditions raised during shadow DML, providing a controlled error path rather than an unhandled exception.
  • API_UPDATING — indicates whether an API-driven update is currently in progress, used to distinguish API updates from direct DML.
  • FIND_DT_UPD_MODES — determines the applicable date-track update modes for the entity, governing how effective dates are adjusted.
  • FIND_DT_DEL_MODES — determines the applicable date-track delete modes for the entity.
  • UPD_EFFECTIVE_END_DATE — adjusts the effective end date on shadow rows, preserving correct date-track history when records are superseded.
  • LCK — performs row locking to serialize concurrent shadow operations and prevent lost updates.
  • CONVERT_ARGS — normalizes and converts arguments passed into the shadow layer so that internal calls align with the expected datatypes.

Tables Accessed

PER_SPS_SHD references two documented tables through APPS synonyms. PER_SPINAL_POINT_STEPS_F is the primary business table; the shadow package reads and writes it to mirror the state of spinal point step records, including effective start and end dates and the identifying key columns. ALL_CONSTRAINTS is used to inspect constraint definitions, supporting the CONSTRAINT_ERROR handling path and validating that shadow writes conform to the underlying table constraints. No other tables are documented, indicating a tightly scoped implementation focused exclusively on the spinal point steps entity.

Usage Notes

PER_SPS_SHD is not intended for direct invocation by end users. It is called internally by the four dependent packages — PER_SPS_BUS, PER_SPS_INS, PER_SPS_UPD, and PER_SPS_DEL — which in turn are surfaced through Oracle Payroll configuration forms, date-tracked maintenance windows, and concurrent programs that manage grade and spinal point structures. In Oracle EBS 12.1.1 and 12.2.2 the package is invoked automatically whenever a spinal point step record is created, amended, or removed, ensuring the shadow table reflects the change with correct effective dating. Custom code should interact with the higher-level PER_SPS_BUS API rather than PER_SPS_SHD directly; direct calls bypass the intended layering and risk inconsistent date-track state. Because the package is documented as VALID with an OTHER API classification, it is subject to change between releases and should not be treated as a stable public interface. The package is marked Oracle Proprietary and Confidential Information, reinforcing that it is an internal component of the Payroll date-track framework.