Search Results per_letter_requests_v




Overview
Explore in AI Chat

Overview

PER_LETTER_REQUESTS_V is a PL/SQL view owned by the APPS schema within the PER (Human Resources) product family. It is a presentation-layer object whose primary role is to support the Letter Requests user interface in Oracle E-Business Suite. The view denormalizes the transactional letter-request record by joining it to letter-type definitions, concurrent program definitions, and two HR lookup sets, so that the UI and any downstream reporting layer can display descriptive meanings instead of raw coded identifiers.

Because the view already resolves the coded REQUEST_STATUS and AUTO_OR_MANUAL values into their lookup meanings, it is the natural entry point for queries that need to report on the lifecycle of generated letters. The view is documented as a single-owner APPS view and does not itself store data; all columns derive from the underlying base objects. In EBS 12.1.1 and 12.2.2 the view retains an identical structure and is the supported interface for letter-request information rather than direct querying of PER_LETTER_REQUESTS.

Underlying Base Objects

The documented base objects referenced by PER_LETTER_REQUESTS_V are PER_LETTER_REQUESTS (synonym), PER_LETTER_TYPES (synonym), FND_CONCURRENT_PROGRAMS_VL, HR_LOOKUPS, and, through dependencies, HR_API, HR_GENERAL, HR_SECURITY, OTA_EVENTS, and PER_VACANCIES. The join logic is central to its behavior:

  • PER_LETTER_REQUESTS PLR — the driving transaction table, providing the letter request identifier, business group, date, status, and audit columns.
  • PER_LETTER_TYPES PLT — supplies the letter type name and generation status type, joined on PLR.LETTER_TYPE_ID = PLT.LETTER_TYPE_ID.
  • FND_CONCURRENT_PROGRAMS_VL CP — supplies the concurrent program identifier and name, joined on the letter type's concurrent program and restricted to CP.APPLICATION_ID = 800 (the HR application).
  • HR_LOOKUPS FL1 — joined on LOOKUP_TYPE = 'REQUEST_STATUS' to decode the request status into a meaning under the alias D_REQUEST_STATUS.
  • HR_LOOKUPS FL2 — joined on LOOKUP_TYPE = 'AUTO_OR_MANUAL' to decode the generation mode into D_AUTO_OR_MANUAL.

The presence of HR_SECURITY, HR_API, and HR_GENERAL among the dependencies confirms that row-level security and HR date-tracked lookups are honored when the view is queried.

Key Columns

Common Use Cases and Queries

This view is typically used to report on letter issuance, monitor failed or pending requests, and feed status information into downstream integrations. A search for request_status maps directly to the REQUEST_STATUS / D_REQUEST_STATUS pair.

SELECT letter_request_id,
       letter_type_name,
       request_status,
       d_request_status,
       auto_or_manual,
       date_from,
       request_id
FROM   apps.per_letter_requests_v
WHERE  d_request_status = :p_status
ORDER  BY date_from DESC;

To reconcile letters against their concurrent requests and business group:

SELECT business_group_id,
       letter_type_name,
       d_request_status,
       request_id
FROM   apps.per_letter_requests_v
WHERE  business_group_id = :p_bg_id
AND    date_from BETWEEN :p_start AND :p_end;

Because the view decodes lookups, filter queries against D_REQUEST_STATUS or D_AUTO_OR_MANUAL for readable predicates, and reserve REQUEST_STATUS for joins to other lookup-driven objects.