Search Results po_reqs_in_pool_v




Overview
Explore in AI Chat

Overview

PO_REQS_IN_POOL_V is a Purchasing (PO) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes a filtered subset of requisition headers that represent requisitions still eligible to be placed on a purchase order — the "requisition pool" available to buyers and sourcing agents. The object carries the ETRM description "10SC ONLY - Retrofitted," indicating that it originated in a specialized or country-specific (10SC) implementation and was retrofitted into the standard application as a documented database object. The view is flagged VALID in the ETRM registry.

The view's role is primarily reporting and integration: it provides a simplified, denormalized projection of requisition data joined to preparer information, allowing external reports, forms, or interfaces to identify demand that can still be sourced without navigating the full requisition schema. Because it applies business filters internally, consumers do not need to re-implement the eligibility logic.

Underlying Base Objects

Per the ETRM 12.2.2 metadata, the view is defined over three referenced base objects, all accessed through APPS synonyms:

  • PO_REQUISITION_HEADERS — the primary source of requisition header attributes. The view text aliases it as PRQH within an inline (NO_MERGE) subquery, meaning the filters and column projections on the header table are isolated into a derived table before the outer join.
  • PO_REQUISITION_LINES_ALL — referenced through an EXISTS correlated subquery that determines whether at least one requisition line can still be placed on a purchase order.
  • PER_ALL_PEOPLE_F — the HR people table (effective-dated), joined to supply the preparer's full name.

The header-to-people join is an equijoin on PREPARER_ID = PERSON_ID, constrained by SYSDATE BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE so that only the currently effective person record is returned. The NO_MERGE hint prevents the optimizer from collapsing the inline view into the outer query, preserving the semantics of the filtered derived table.

Key Columns

  • SEGMENT1 — the requisition number, the user-facing document identifier.
  • REQUISITION_HEADER_ID — the unique primary key of the requisition header; used for joins and drill-down.
  • FULL_NAME — the formatted name of the preparer, sourced from PER_ALL_PEOPLE_F.
  • DESCRIPTION — the requisition header description or justification text.
  • AUTHORIZATION_STATUS — the approval state of the requisition (for example, APPROVED, IN PROCESS, REJECTED).
  • CLOSED_CODE — the closure state; the view excludes rows whose code is FINALLY CLOSED.
  • EMERGENCY_PO_NUM / EMERGENCY_PO_ORG_ID — emergency purchase order number and owning organization for emergency sourcing scenarios.
  • PCARD_ID — the procurement card identifier when the requisition is pcard-related.
  • APPS_SOURCE_CODE — the source application code for the requisition.
  • ORG_ID — the operating unit, supporting multi-org reporting and data security.

Common Use Cases and Queries

The view is most commonly used to power buyer worklists, sourcing dashboards, and custom reports that need a ready-filtered list of requisitions that can still be converted into purchase orders.

  • Listing open, pool-eligible requisitions for a given operating unit:
SELECT segment1, full_name, description, authorization_status
FROM   apps.po_reqs_in_pool_v
WHERE  org_id = :p_org_id
AND    authorization_status = 'APPROVED'
ORDER BY segment1;
  • Joining pool requisitions to their lines for demand detailing:
SELECT v.segment1, v.full_name, l.line_num, l.item_description, l.quantity
FROM   apps.po_reqs_in_pool_v v,
       apps.po_requisition_lines_all l
WHERE  v.requisition_header_id = l.requisition_header_id
AND    NVL(l.cancel_flag,'N') = 'N';
  • Counting requisitions per preparer to gauge workload distribution.

Because the view already enforces the core pool logic — excluding cancelled and finally closed headers, requiring at least one uncancelled vendor-sourced line with no line location and no sourcing activity — queries against it avoid re-encoding these rules. The eligible-line criteria applied by the EXISTS clause are: LINE_LOCATION_ID is null, AT_SOURCING_FLAG and MODIFIED_BY_AGENT_FLAG are not Y, CANCEL_FLAG is not Y, CLOSED_CODE is not FINALLY CLOSED, and SOURCE_TYPE_CODE equals VENDOR. Note also that requisition lines cancelled or closed individually will not qualify, so header visibility depends on the state of the associated lines.