Search Results pjm_req_commitments_v

Overview

The PJM_REQ_COMMITMENTS_V view is a Project Manufacturing (PJM) reporting object that consolidates data about project-related purchase requisition distributions. Its stated purpose in the ETRM repository is straightforward: it exposes purchase requisition distributions that are associated with projects and tasks, allowing project-centric analysis of procurement commitments before they become purchase orders. In Oracle EBS 12.1.1 and 12.2.2, project manufacturing integrates with Oracle Purchasing and Oracle Projects so that requisition demand originating from a project or task can be tracked as a commitment against project budgets. This view supplies the join between the requisition line, its distribution, and the project/task/expenditure type context required for that tracking.

The view is documented as not implemented in the source database, meaning the ETRM extraction captured its definition but the object was not physically present in the reference instance. Its role is therefore best understood as a reporting and integration access point: external reports, extracts, or interfaces can read project requisition commitment data without reimplementing the multi-table join logic each time.

Underlying Base Objects

Although the ETRM metadata records no referenced base objects for this view, the view text itself reveals a comprehensive join across the Purchasing, Projects, Human Resources, and General Ledger schemas. The principal Purchasing objects are PO_REQUISITION_HEADERS_ALL, PO_REQUISITION_LINES_ALL, PO_REQ_DISTRIBUTIONS_ALL, PO_DOCUMENT_TYPES, and PO_LINE_TYPES. Project context is drawn from PA_PROJECTS, PA_TASKS, and PA_EXPENDITURE_TYPES. Requester identity is resolved through PER_PEOPLE_F, the operating unit through HR_ORGANIZATION_UNITS, and currency information through GL_SETS_OF_BOOKS and MTL_PARAMETERS. The view restricts output to requisition headers whose type lookup is 'PURCHASE' and matches the REQUSITION document subtype, excludes finally closed or cancelled lines, and requires that the line location be null, filtering the result to commitment-style demand.

Key Columns

• SEGMENT1 (RH): Requisition number used to identify the commitment source document.
• TYPE_NAME: Document type description for the requisition.
• Authorization status flag: DECODE expression returning 'Y' for APPROVED requisitions and 'N' otherwise.
• LINE_NUM, ITEM_DESCRIPTION, NEED_BY_DATE, CREATION_DATE: Core requisition line attributes.
• SUGGESTED_VENDOR_NAME, VENDOR_ID: Supplier information at line level.
• PERSON_ID, FULL_NAME: Requester identity.
• Currency columns: NVL expressions for currency code, unit price, rate date, rate type, and rate, with TO_NUMBER/DECODE handling for amount-type lines.
• REQ_LINE_QUANTITY and computed amount: Quantity and quantity multiplied by unit price.
• Project and task columns: Project SEGMENT1 and NAME, TASK_NUMBER and TASK_NAME, and the associated PROJECT_ID and TASK_ID.
• Expenditure columns: EXPENDITURE_TYPE, EXPENDITURE_CATEGORY, and REVENUE_CATEGORY_CODE.
• Organization: O.NAME and ORGANIZATION_ID identify the operating/organization context.
• Keys: REQUISITION_HEADER_ID, REQUISITION_LINE_ID, and DISTRIBUTION_ID enable drill-down.

Common Use Cases and Queries

Typical uses include project commitment reporting, requisition aging by task, and reconciliation of requisition demand before conversion to purchase orders. A representative query lists pending commitments per project:

SELECT PROJECT_ID, TASK_ID, SEGMENT1, TYPE_NAME,
       REQ_LINE_QUANTITY, PERSON_ID, FULL_NAME, NEED_BY_DATE
FROM   PJM_REQ_COMMITMENTS_V
WHERE  PROJECT_ID = :project_id
ORDER  BY NEED_BY_DATE;

A second pattern aggregates committed amounts by task and expenditure type for budget monitoring:

SELECT TASK_ID, EXPENDITURE_TYPE,
       SUM(REQ_LINE_QUANTITY * UNIT_PRICE) committed_amount
FROM   PJM_REQ_COMMITMENTS_V
GROUP  BY TASK_ID, EXPENDITURE_TYPE;

Because the view already applies the project, task, requester, and currency joins, these queries avoid duplicating common view logic.