Search Results total_value

Overview

PA_REP_PROJ_SP_GL_V is an Oracle EBS Projects (PA) reporting view owned by the APPS schema, defined over the sales pipeline of a project for a given General Ledger period. As documented in ETRM 12.2.2, the view's purpose is to expose the sales pipeline of a project aligned to a GL period, enabling pipeline reporting by period, organization, project class, and probability. The view is defined as a UNION of two queries, which drives its central behavior: each project is reported once for each qualifying combination of project players, project classes, and GL periods whose date range brackets the project's Expected Approval Date.

The user's search term "total_value" maps to two computed expressions in the view's SELECT list (both derived from PPA.PROJECT_VALUE). The first computes the weighted project value at the class level: NVL(PROJECT_VALUE,0) * NVL(CODE_PERCENTAGE/100,1). The second computes the probability-weighted value: NVL(PROJECT_VALUE,0) * PROBABILITY_PERCENTAGE/100 * NVL(CODE_PERCENTAGE/100,1). These columns are the quantitative backbone of pipeline analytics, but they are unnamed expressions in the view text, meaning consumers must reference them positionally or via a wrapper query.

Underlying Base Objects

The view is defined over the following ETRM-documented base objects: PA_PROJECTS_ALL, PA_PROJECT_CLASSES, PA_PROJECT_PLAYERS, PA_PROJECT_STATUSES, PA_PROBABILITY_MEMBERS, PA_IMPLEMENTATIONS, PA_PROJECTS_MAINT_UTILS (package), GL_PERIODS, and GL_SETS_OF_BOOKS. Joins are keyed on PROJECT_ID across the PA project objects; PA_PROBABILITY_MEMBERS is joined via PROBABILITY_MEMBER_ID to supply PROBABILITY_PERCENTAGE; PA_PROJECT_STATUSES supplies PROJECT_STATUS_NAME. GL_PERIODS is joined so that EXPECTED_APPROVAL_DATE BETWEEN GLP.START_DATE AND GLP.END_DATE, with the period set constrained through PA_IMPLEMENTATIONS and GL_SETS_OF_BOOKS on PERIOD_SET_NAME and ACCOUNTED_PERIOD_TYPE. PA_PROJECTS_MAINT_UTILS.GET_PRIMARY_CUSTOMER_NAME is invoked per row to resolve the primary customer name. Because the view is a UNION (rather than UNION ALL), duplicate rows across the two branches are eliminated, which affects row counts in reporting.

Key Columns

• CARRYING_OUT_ORGANIZATION_ID, ORG_ID — Operating unit context for multi-org reporting.
• PROJECT_ID, SEGMENT1, NAME — Project identity (internal ID, project number, and project name).
• CLASS_CATEGORY, CLASS_CODE, CODE_PERCENTAGE — Project class classification and its percentage share, used to weight project value.
• PROJECT_ROLE_TYPE, PERSON_ID — Project player role and the person assigned, from PA_PROJECT_PLAYERS.
• PROBABILITY_PERCENTAGE — Win probability from PA_PROBABILITY_MEMBERS.
• PROBABILITY_MEMBER_ID — Not projected but required by the WHERE clause; rows without it are excluded.
• PROJECT_TYPE, PROJECT_STATUS_NAME — Project type and current status description.
• EXPECTED_APPROVAL_DATE — The date that drives period assignment; the view filters out rows where it is null.
• PERIOD_YEAR, QUARTER_NUM, PERIOD_NAME, PERIOD_SET_NAME, PERIOD_NUM — GL period attributes derived from GL_PERIODS.
• Value expressions — The two unnamed totals described above (class-weighted and probability-weighted project value).

Common Use Cases and Queries

Typical use cases include sales pipeline reporting by GL period, weighted pipeline forecasting by probability and class, and identification of projects expected to be approved within a given period. Because the two value columns are unnamed, wrap the view in an inline view and alias them explicitly.

• Pipeline by period and class: SELECT period_name, class_category, class_code, SUM(value_col) FROM PA_REP_PROJ_SP_GL_V GROUP BY period_name, class_category, class_code.
• Probability-weighted forecast: SELECT period_name, SUM(prob_weighted_value) FROM PA_REP_PROJ_SP_GL_V GROUP BY period_name.
• Filter by organization: add WHERE org_id = :org_id or carrying_out_organization_id = :org_id.

Note that value and probability expressions must be referenced positionally in the inline view, e.g. SELECT period_name, "class_weighted_value", "prob_weighted_value" FROM (SELECT period_name, NVL(...), NVL(...) FROM PA_REP_PROJ_SP_GL_V).