Search Results pa_proj_req_distributions_v




Overview
Explore in AI Chat

Overview

The PA_PROJ_REQ_DISTRIBUTIONS_V view is an APPS-owned database object in the Oracle E-Business Suite Projects (PA) module. In both 12.1.1 and 12.2.2, it serves as a reporting and integration bridge between Oracle Purchasing and Oracle Projects. Its documented purpose is to display project-related requisition distributions in Oracle Purchasing that have not yet been converted into a purchase order.

Because requisition demand frequently originates from project-driven procurement activity, project accountants, buyers, and cost engineers need visibility into outstanding, unbought requisition lines before commitment and encumbrance balances are finalized. This view provides that visibility by joining Purchasing requisition data with Projects project/task identifiers and expenditure classifications, and by performing currency conversion through the PA_MULTI_CURRENCY package so that amounts are normalized to the functional (set of books) currency. In practice, the view is used primarily as the source for forms, concurrent programs, and custom reports that surface "project requisitions not yet ordered."

Underlying Base Objects

According to the ETRM metadata, the view is defined over the following referenced base objects:

The view text uses the ordered, nested-loops execution hints (ORDERED USE_NL(P, RD, RL)), indicating the distribution and line joins are intentionally driven to keep performance predictable on large requisition volumes. It restricts output to requisition distributions that have not been purchased as a purchase order, which is the behavioral core of the view.

Key Columns

  • SEGMENT1 – the requisition number (header) and, separately, the project number from PA_PROJECTS.
  • TYPE_NAME – the expenditure type name applied to the distribution.
  • AUTHORIZATION_STATUS (decoded Y/N) – whether the requisition is APPROVED; returns 'Y' for approved, otherwise 'N'.
  • LINE_NUM, ITEM_DESCRIPTION, NEED_BY_DATE, CREATION_DATE – requisition line identifying and scheduling attributes.
  • SUGGESTED_VENDOR_NAME, VENDOR_ID, TO_PERSON_ID, requester full name – sourcing and delivery information.
  • CURRENCY_CODE, RATE_DATE, RATE_TYPE, RATE – foreign currency, conversion date, rate type, and rate; returned as NULL when the line currency equals the functional currency.
  • Amount columns – quantity-based or amount-based line value computed via DECODE against the line type MATCHING_BASIS, converted through PA_MULTI_CURRENCY.CONVERT_AMOUNT_SQL.
  • REQ_LINE_QUANTITY, UNIT_PRICE, UNIT_MEAS_LOOKUP_CODE – quantity and unit-of-measure detail (suppressed for amount-based lines).
  • Project and task segments – project/task number and name identifying the charged project.

Common Use Cases and Queries

The most common scenario is generating an outstanding project commitments report: identifying approved requisitions that have not yet become purchase orders. A representative query follows.

  • Outstanding approved requisitions by project: 
    SELECT project_number, task_number, SEGMENT1 req_number,
       LINE_NUM, TYPE_NAME, NEED_BY_DATE
FROM   apps.pa_proj_req_distributions_v
WHERE  authorized_flag = 'Y'
ORDER BY project_number, task_number, NEED_BY_DATE;
  • Currency-converted value of unbought demand for a specific project, using the view's computed amount columns.
  • Buyer work queue: filtering on TO_PERSON_ID or VENDOR_ID to surface demand a buyer must convert to a purchase order.
  • Feed to encumbrance analysis: joining results to project budgets to compare planned versus requisitioned-but-not-ordered amounts.

Because the view is a convenience layer over Purchasing and Projects tables, it should be queried read-only; reporting should always apply the AUTHORIZATION_STATUS decode to separate approved from unapproved demand.