Search Results po_req_lines_in_pool_src_v




Overview
Explore in AI Chat

Overview

PO_REQ_LINES_IN_POOL_SRC_V is a Purchasing (PO) module view owned by the APPS schema in Oracle E-Business Suite 12.1.1 and 12.2.2. Its documented purpose is to expose every requisition line that is available in the sourcing pool from Oracle Sourcing's perspective. Crucially, the view's WHERE clause does not filter on REQS_IN_POOL_FLAG = 'Y'. Instead, it relies on a set of structural and workflow-based predicates to determine pool candidacy, which distinguishes it from the standard pool views that depend on the flag maintained by the pooling concurrent programs.

Because of this design, the view acts as a "source of truth" for lines that ought to be eligible for sourcing, irrespective of whether the requisition pooling process has already stamped them. This makes it valuable for reconciliation, troubleshooting, and integration scenarios where a discrepancy between flag-based views and the underlying eligibility rules must be detected.

Underlying Base Objects

The view is defined over five base objects, all referenced through APPS synonyms:

The joins are outer (using (+) syntax, characteristic of pre-ANSI Oracle SQL in EBS), so lines without project or task distributions still appear, with NULL or derived values for project/task columns. The GROUP BY clause aggregates distributions so that one row per requisition line is returned, with project and task numbers collapsed to a single value, NULL, or the literal 'MULTIPLE'.

Key Columns

  • MODIFIED_BY_AGENT_FLAG — the column the user searched for. It identifies whether a requisition line was created or altered by an automated agent (such as Oracle Sourcing or a procurement agent). The view includes this column in both its SELECT list and GROUP BY, and filters it with NVL(MODIFIED_BY_AGENT_FLAG, 'N') = 'N', excluding any line touched by an agent.
  • REQUISITION_HEADER_ID / REQUISITION_NUMBER — header identifiers for linking to the requisition.
  • REQUISITION_LINE_ID / REQUISITION_LINE_NUM — the line-level keys.
  • REQUISITION_QUANTITY, NEED_BY_DATE, CREATION_DATE — descriptive attributes for the line.
  • AUCTION_HEADER_ID, AUCTION_LINE_NUMBER — linkage to the sourcing auction, when applicable.
  • ORG_ID — the operating unit, supporting Multi-Org access.
  • PROJECT_NUMBER, TASK_NUMBER — derived project/task identifiers, returned as a single value, NULL, or 'MULTIPLE'.

Common Use Cases and Queries

Typical scenarios include reconciling unpooled lines that satisfy sourcing eligibility, and auditing agent-modified lines. A representative query listing eligible lines for a given operating unit:

SELECT REQUISITION_NUMBER,
       REQUISITION_LINE_NUM,
       MODIFIED_BY_AGENT_FLAG,
       NEED_BY_DATE,
       PROJECT_NUMBER
FROM   APPS.PO_REQ_LINES_IN_POOL_SRC_V
WHERE  ORG_ID = :p_org_id
ORDER  BY CREATION_DATE DESC;

To identify lines excluded because they were touched by an agent, query the base table directly against the view's predicate:

SELECT REQUISITION_LINE_ID, MODIFIED_BY_AGENT_FLAG
FROM   APPS.PO_REQUISITION_LINES_ALL
WHERE  NVL(MODIFIED_BY_AGENT_FLAG,'N') = 'Y';

The key filter applied by the view is PRL.LINE_LOCATION_ID IS NULL, NVL(PRL.CANCEL_FLAG,'N')='N', NVL(PRL.CLOSED_CODE,'OPEN') <> 'FINALLY CLOSED', NVL(PRL.MODIFIED_BY_AGENT_FLAG,'N')='N', and PRL.SOURCE_TYPE_CODE='VENDOR'. Any deviation from these conditions removes a line from the result set, so they should be understood before relying on the view for pool reporting.