Results for “per_letter_request_lines”

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

Overview

The PER_LETTER_REQUEST_LINES table resides in the HR schema and belongs to the Oracle E-Business Suite Human Resources (PER) product family. It stores the detail rows associated with individual letter requests in Oracle EBS 12.1.1 and 12.2.2. Where the parent table PER_LETTER_REQUESTS captures the header-level request (for example, a request to generate a confirmation-of-employment or salary-verification letter for an employee), this child table holds the specific line-level particulars that drive the actual generation of each letter — the person, assignment, effective dating, assignment status context, and related booking or contract references that the letter engine consumes.

From a data-modeling perspective, the metadata's Data Vault classification heuristic identifies this object as a link. This is a modeling suggestion: the table records relationships and associations between a letter request, a person, an assignment, an organization (business group), and an assignment status type, rather than acting as a pure descriptive hub or a fully attributed satellite. Practitioners building a warehouse layer may therefore treat it as a transactional link connecting those business entities.

Key Information Stored

The table is owned by HR and contains 21 documented columns. The most significant include:

• LETTER_REQUEST_LINE_ID — the surrogate primary key, backed by the unique index PER_LETTER_REQUEST_LINES_PK. This is the only documented unique index, so no separate business-key candidate is exposed by the metadata.
• LETTER_REQUEST_ID — foreign key to PER_LETTER_REQUESTS; ties each line to its parent request header.
• BUSINESS_GROUP_ID — foreign key to HR_ALL_ORGANIZATION_UNITS, establishing the organization (business group) context that governs the record.
• PERSON_ID and ASSIGNMENT_ID — the employee and assignment the letter line applies to.
• ASSIGNMENT_STATUS_TYPE_ID — foreign key to PER_ASSIGNMENT_STATUS_TYPES, defining the assignment status relevant to the letter.
• DATE_FROM — effective start date for the line.
• OBJECT_VERSION_NUMBER — optimistic locking column used by the Oracle framework and the PL/SQL APIs.
• REQUEST_ID, PROGRAM_ID, PROGRAM_APPLICATION_ID, and PROGRAM_UPDATE_DATE — concurrent program and request context identifying the batch that created or updated the row.
• CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN — standard WHO audit columns.
• OTA_BOOKING_STATUS_TYPE_ID, OTA_BOOKING_ID, OTA_EVENT_ID, and CONTRACT_ID — optional references into Oracle Training Administration (OTA) bookings/events and contract data, used when a letter line relates to a booking or contractual context.

Common Use Cases and Queries

Typical usage centers on auditing, reconciliation, and reporting on letter generation. Common patterns include listing all lines for a given request, tracing which person and assignment a letter covered, and joining to the parent request to resolve status and letter type.

• Lines for a request: SELECT * FROM per_letter_request_lines WHERE letter_request_id = :p_request_id;
• Person/assignment detail: join PER_LETTER_REQUESTS on LETTER_REQUEST_ID and resolve the business group via HR_ALL_ORGANIZATION_UNITS on BUSINESS_GROUP_ID.
• Status context: join PER_ASSIGNMENT_STATUS_TYPES on ASSIGNMENT_STATUS_TYPE_ID to report the assignment status that applied to each letter line.
• Batch troubleshooting: filter by REQUEST_ID or PROGRAM_ID to isolate lines produced by a specific concurrent program run.
• Audit reporting: use CREATION_DATE, CREATED_BY, and LAST_UPDATE_DATE to track who generated letters and when.

Related Objects

• PER_LETTER_REQUESTS — parent header table; join on LETTER_REQUEST_ID.
• HR_ALL_ORGANIZATION_UNITS — business group/organization; join on BUSINESS_GROUP_ID.
• PER_ASSIGNMENT_STATUS_TYPES — assignment status reference; join on ASSIGNMENT_STATUS_TYPE_ID.
• PER_ALL_PEOPLE_F and PER_ALL_ASSIGNMENTS_F — resolve PERSON_ID and ASSIGNMENT_ID to the underlying person and assignment.
• PER_LETTER_REQUEST_LINES_PK — the primary-key index supporting unique access by LETTER_REQUEST_LINE_ID.
• OTA booking/event tables and contract tables — referenced indirectly via OTA_BOOKING_ID, OTA_EVENT_ID, and CONTRACT_ID.