Search Results per_letter_requests_pkg




Overview
Explore in AI Chat

Overview

The APPS.PER_LETTER_REQUESTS_PKG package body is a server-side PL/SQL implementation within the Oracle E-Business Suite 12.1.1 and 12.2.2 environments supporting the Oracle Human Resources (HRMS) letter generation framework. It provides the programmatic interface for creating, validating, and maintaining letter requests — the records that associate a worker with a specific letter or template to be produced by the Letter Generation concurrent program. The package encapsulates the business logic that governs how letter requests and their associated request lines are inserted, locked, updated, deleted, and confirmed, ensuring that data integrity rules and uniqueness constraints are respected before any persistent change is applied. The body is documented in the ETRM repository with a VALID status under the APPS schema and is classified as an OTHER API type rather than a public HRMS business API. As documented, the object is not referenced by any other database object, indicating that it functions as a terminal implementation whose entry points are invoked directly by forms, concurrent programs, or custom code rather than being called by sibling packages.

Key Procedures and Functions

The documented package metadata identifies eight procedures and functions, each focused on a discrete aspect of letter request maintenance:

  • CHECK_REQUEST_UNIQUE — Validates uniqueness constraints for a letter request, preventing duplicate or conflicting request definitions from being committed.
  • CHECK_REQUEST_LINES — Verifies the integrity and correctness of the request lines attached to a letter request prior to persistence.
  • CONFIRM_DELETE_LINES — Confirms the removal of request lines, providing the controlled deletion path for child records.
  • INSERT_ROW — Performs the actual insertion of a new letter request record after validation succeeds.
  • LOCK_ROW — Acquires a row-level lock on a letter request, supporting concurrency control during interactive or batch updates.
  • UPDATE_ROW — Applies modifications to an existing letter request record.
  • DELETE_ROW — Removes an existing letter request record from the data model.
  • CONCURRENT_PROGRAM_CALL — Serves as the entry point used when the package is driven from a concurrent program, orchestrating the letter request processing flow in a batch context.

The metadata does not document parameter signatures; the descriptions above reflect the functional roles implied by the procedure names and the standard table-handler pattern used throughout HRMS packages.

Tables Accessed

The package operates against three documented tables, all accessed through APPS synonyms:

  • PER_LETTER_REQUESTS — The primary transactional table holding letter request header records; this is the principal target of INSERT_ROW, UPDATE_ROW, DELETE_ROW, and LOCK_ROW.
  • PER_LETTER_REQUESTS_S — The translated or sequence/attribute sibling table that stores language-specific or secondary descriptive columns for letter requests, maintained alongside the base table.
  • PER_LETTER_REQUEST_LINES — The child table storing individual request line details, validated by CHECK_REQUEST_LINES and removed through CONFIRM_DELETE_LINES.

Additional dependencies documented in the metadata include FND_MESSAGE and FND_REQUEST (for message text and concurrent request submission), APP_EXCEPTION and HR_UTILITY (for error propagation and shared HR utilities), and the DUAL and STANDARD objects.

Usage Notes

PER_LETTER_REQUESTS_PKG is typically invoked from the Oracle HRMS Letters form, where the insert, lock, update, and delete row handlers back the form's block-level DML processing. The CONCURRENT_PROGRAM_CALL procedure supports submission of letter generation through FND_REQUEST from a concurrent program, making the package reachable from batch scheduling as well as interactive sessions. Because the package is not referenced by any other database object, it should be treated as an implementation-layer component; customizations should prefer supported public HRMS APIs where available and use this package only where form-level letter request maintenance requires it. The presence of CHECK_REQUEST_UNIQUE and CHECK_REQUEST_LINES indicates that callers should anticipate validation failures raised through APP_EXCEPTION and surfaced via FND_MESSAGE, particularly when bulk-inserting requests outside the standard form.