Results for “per_letter_requests”

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

Overview

PER_LETTER_REQUESTS is a Human Resources (PER) module table in the HR schema that stores requests for specific types of standard letters. Within the Oracle E-Business Suite 12.1.1 and 12.2.2 data model, it acts as the transactional header that records a user's or process's intent to generate a standard letter defined in PER_LETTER_TYPES. Each row represents a single letter request, capturing the requester context, target entity, and processing status, while the actual per-recipient detail is held in the dependent child table PER_LETTER_REQUEST_LINES.

The ETRM metadata classifies this object, using a heuristic mined from its foreign key structure, as satellite-leaning. This is a modeling suggestion rather than a physical designation: the table behaves largely as a descriptive satellite attached to its referenced business keys (business group, letter type, and vacancy), with a distinct surrogate primary key and audit columns that track state changes over time.

Key Information Stored

The table is defined with 18 documented columns in the 12.2.2 physical schema. The most significant are:

• LETTER_REQUEST_ID — the surrogate primary key, enforced by the unique index PER_LETTER_REQUESTS_PK. It is the sole documented business-key candidate and uniquely identifies each request.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, establishing the business group (legislative) context for the request.
• LETTER_TYPE_ID — foreign key to PER_LETTER_TYPES, identifying which standard letter template is requested.
• VACANCY_ID — foreign key to PER_ALL_VACANCIES, linking the request to a recruitment vacancy where applicable.
• DATE_FROM — the effective or start date associated with the request.
• REQUEST_STATUS — the processing state of the letter request.
• AUTO_OR_MANUAL — indicates whether the request was generated automatically or entered manually.
• REQUEST_ID — correlates the row to a concurrent or workflow request.
• EVENT_ID — links the request to an originating business event.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle Applications Framework.
• PROGRAM_ID, PROGRAM_APPLICATION_ID, PROGRAM_UPDATE_DATE — concurrent program audit columns identifying the process that last touched the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns recording creation and last modification.

Common Use Cases and Queries

Typical scenarios include monitoring outstanding letter requests, reconciling generated letters against requests, and reporting on letter activity by business group or letter type. A common query joins the request header to its type for readability:

• SELECT r.letter_request_id, t.letter_type_name, r.request_status, r.auto_or_manual, r.date_from FROM per_letter_requests r, per_letter_types t WHERE r.letter_type_id = t.letter_type_id AND r.business_group_id = :p_bg_id;
• Filtering by REQUEST_STATUS to surface pending or failed requests for operational follow-up.
• Joining to PER_LETTER_REQUEST_LINES on LETTER_REQUEST_ID to produce recipient-level detail for a given request.
• Correlating REQUEST_ID or EVENT_ID to concurrent program or workflow history for troubleshooting generation failures.

Related Objects

The following objects are most significant in relation to PER_LETTER_REQUESTS, based on the documented key relationships:

• PER_LETTER_REQUEST_LINES — child table referencing LETTER_REQUEST_ID; holds individual line/recipient detail for each request.
• PER_LETTER_TYPES — parent reference via LETTER_TYPE_ID; defines the standard letter templates.
• HR_ALL_ORGANIZATION_UNITS — parent reference via BUSINESS_GROUP_ID; supplies the business group context.
• PER_ALL_VACANCIES — parent reference via VACANCY_ID; links requests to recruitment vacancies.
• PER_LETTER_REQUESTS_PK — the unique index enforcing the primary key on LETTER_REQUEST_ID.