Search Results por_view_reqs_by_group_v




Overview
Explore in AI Chat

Overview

POR_VIEW_REQS_BY_GROUP_V is a reporting view owned by the APPS schema in Oracle E-Business Suite, belonging to the ICX (Oracle iProcurement) product family. Its documented description is "View Requisition By Group." The view presents purchase requisition header information consolidated at the requisition-header level, enriched with preparer, approval-status, supplier, currency, and corporate-card attributes. It is designed to support iProcurement requisition search, review, and management pages, and it is equally suited to custom reporting and integration scenarios where a flattened, presentation-ready requisition record is required.

The name is significant to the search term "security_position_structure_id": the view's inline aggregation subquery joins to PO_SYSTEM_PARAMETERS through PSP1.SECURITY_POSITION_STRUCTURE_ID = PEH1.POSITION_STRUCTURE_ID. This hard-codes a security relationship between the current user's position hierarchy (resolved through FND_USER, PO_EMPLOYEE_HIERARCHIES, and FND_GLOBAL.USER_ID) and the security position structure configured in the system parameters, filtering requisitions to those the user is authorized to see. As a result, the view is security-aware and may return fewer rows for a given user than the total requisition population.

Underlying Base Objects

The view is defined across a broad set of APPS objects. Documented references include AP_CARDS, FINANCIALS_SYSTEM_PARAMETERS, GL_SETS_OF_BOOKS, HR_EMPLOYEES_CURRENT_V, PO_EMPLOYEE_HIERARCHIES, PO_LINE_TYPES, PO_LOOKUP_CODES, PO_REQUISITION_HEADERS, PO_REQUISITION_LINES, PO_SYSTEM_PARAMETERS, and PO_VENDORS, alongside the PL/SQL packages FND_CURRENCY, FND_GLOBAL, FND_PROFILE, HR_GENERAL, HR_PERSON_NAME, and HR_SECURITY.

PO_REQUISITION_HEADERS supplies header data such as SEGMENT1 and AUTHORIZATION_STATUS. PO_REQUISITION_LINES is aggregated in an inline subquery to produce line totals, supplier summarization, and a placed-on-PO indicator. PO_LOOKUP_CODES is joined four times (aliases PLC_AUTH, PLC_CANCEL, PLC_CLOSED, PLC_DOCTYPE) to resolve displayed values for authorization status, cancel flag, closed code, and document type. HR_EMPLOYEES_CURRENT_V and AP_CARDS enrich the row with the preparer's full name and any corporate card number. FINANCIALS_SYSTEM_PARAMETERS and GL_SETS_OF_BOOKS provide set-of-books and currency context. The FND and HR packages supply runtime formatting and security resolution through FND_GLOBAL.USER_ID, FND_PROFILE.VALUE_WNPS, FND_CURRENCY.SAFE_GET_FORMAT_MASK, and the HR sets of bookkeeping.

Key Columns

  • REQUISITION_HEADER_ID — Primary key of the requisition, joined from PO_REQUISITION_HEADERS.
  • SEGMENT1 — The user-visible requisition number.
  • PREPARER_ID and FULL_NAME / LAST_NAME (uppercased) — The employee who created the requisition, resolved through HR_EMPLOYEES_CURRENT_V.
  • DESCRIPTION and AUTHORIZATION_STATUS plus PLC_AUTH.DISPLAYED_FIELD — Descriptive text and the readable approval status.
  • CANCEL_FLAG / PLC_CANCEL.DISPLAYED_FIELD and NVL(CLOSED_CODE,'OPEN') / PLC_CLOSED.DISPLAYED_FIELD — Cancellation and closure indicators.
  • APC.CARD_NUMBER — Corporate procurement card number, where applicable.
  • CREATION_DATE (formatted twice) — Once via the ICX_DATE_FORMAT_MASK profile and once as ISO YYYY-MM-DD.
  • TOTAL — Aggregated line value using UNIT_PRICE × (QUANTITY − QUANTITY_CANCELLED), formatted through FND_CURRENCY.
  • SUPPLIER / PLACED_ON_PO — Uppercased vendor name (or MULTIPLE_VALUE) and a Y/N flag indicating lines already placed on a purchase order.
  • EMERGENCY_PO_NUM — Emergency purchase order reference.

Common Use Cases and Queries

Typical uses include building requisition dashboards, auditing approval states, and feeding downstream reporting where a single row per requisition is preferable to header-line joins. Because the view enforces position-hierarchy security internally, it is well suited to self-service reporting for buyers and preparers.

A basic retrieval of requisitions for the current user:

  • SELECT REQUISITION_HEADER_ID, SEGMENT1, FULL_NAME, DESCRIPTION, AUTHORIZATION_STATUS, PLC_AUTH_DISPLAYED_FIELD, TOTAL, SUPPLIER FROM APPS.POR_VIEW_REQS_BY_GROUP_V;
  • Filtering by status: ... WHERE AUTHORIZATION_STATUS = 'APPROVED';
  • Aggregating spend: SELECT SUPPLIER, SUM(TOTAL) FROM APPS.POR_VIEW_REQS_BY_GROUP_V GROUP BY SUPPLIER;
  • Identifying open items: ... WHERE CANCEL_FLAG = 'N' AND NVL(CLOSED_CODE,'OPEN') = 'OPEN';

Note that column aliases for the PO_LOOKUP_CODES DISPLAYED_FIELD values may be duplicated; consumers should reference positional or explicit aliases when selecting. The view reflects the security-position structure of the environment, so results vary with the profile and hierarchy configuration and with the authenticated FND user context.