Search Results calculate_gl_amount

Overview

PA_FP_SUM_PV_V is a reporting view owned by the APPS schema within the Oracle Projects (PA) module. Its name reflects its function: a summarized financial plan ("FP") period-values view ("SUM_PV"). It exposes budget or forecast version data aggregated across a set of period columns, one for each planning period (PERIOD1 through PERIOD8 in the excerpted view text). Rather than presenting raw transactional detail, the view consolidates period amounts associated with a project, resource assignment, budget version, and amount type, making it suitable for on-screen financial plan displays and integration extracts.

In Oracle EBS 12.1.1 and 12.2.2, financial plan data is stored in normalized period-value tables such as PA_FP_PERIOD_VALUES_V, where each period is represented by a separate AMOUNT column. PA_FP_SUM_PV_V re-projects that structure into a pivoted, summed layout keyed by PROJECT_ID, RESOURCE_ASSIGNMENT_ID, VERSION_NAME, and AMOUNT_TYPE_CODE. This view is typically consumed by Oracle Projects forms and concurrent programs that render plan-versus-actual or plan summary screens, and it supplies the underlying result set that Oracle's reporting integration layer maps to the logical business object "View: PA_FP_SUM_PV_V".

Underlying Base Objects

ETRM 12.2.2 documents the following referenced base objects for this view:

• PA_BUDGET_VERSIONS (SYNONYM) — supplies VERSION_NAME and VERSION_NUM, identifying the budget or forecast version being reported.
• PA_AMOUNT_TYPES_VL (VIEW) — provides AMOUNT_TYPE_NAME, the translated display label for each amount type.
• PA_FP_PERIOD_VALUES_V (VIEW) — the primary period-value source, contributing PERIOD_AMOUNT1 through PERIOD_AMOUNTn, AMOUNT_SUBTYPE_CODE, PRECEDING_PERIODS_AMOUNT, and SUCCEEDING_PERIODS_AMOUNT.
• PA_RESOURCE_ASSIGNMENTS (SYNONYM) — links plan lines to RESOURCE_ASSIGNMENT_ID and, through the assignment, to PROJECT_ID.
• PA_FIN_PLAN_VIEW_GLOBAL (PACKAGE) — provides controlled global segmentation for financial plan display logic.
• PA_FP_ORG_FCST_UTILS (PACKAGE) — a utility package used in financial plan and organizational forecast processing.

The SQL joins these objects on project, version, resource assignment, and amount type, then aggregates period amounts with SUM. Transfer-price subtypes are handled with DECODE: AMOUNT_SUBTYPE_CODE values 'TP_REVENUE_OUT' and 'TP_COST_OUT' are multiplied by -1 so they reduce rather than inflate the summed period total.

Key Columns

• PROJECT_ID — the project to which the plan line belongs.
• RESOURCE_ASSIGNMENT_ID — links the plan amount to a specific resource assignment on the project.
• VERSION_NAME / VERSION_NUM — identify the budget or forecast version.
• AMOUNT_TYPE_NAME / AMOUNT_TYPE_CODE — the display name and internal code for the amount type (for example MARGIN, MARGIN_PERCENT, QUANTITY).
• SHOW_URL_FLAG — a DECODE-derived indicator that returns 'N' for MARGIN, MARGIN_PERCENT, and QUANTITY, and 'Y' otherwise, controlling whether a drill-down URL is presented.
• PRECEDING_PERIODS_AMOUNT / SUCCEEDING_PERIODS_AMOUNT — cumulative amounts before and after the displayed period range.
• PERIOD1 … PERIOD8 — summed amounts for each displayed planning period, netting transfer-price revenue and cost outflows.

Common Use Cases and Queries

Typical scenarios include verifying summed plan amounts for a version, reconciling transfer-price adjustments, and building extracts for downstream reporting. The view is widely queried by financial plan summary screens, so the following patterns are representative:

Plan amounts by project and version:

SELECT project_id, version_name, amount_type_code,
       period1, period2, period3, period4
FROM   apps.pa_fp_sum_pv_v
WHERE  project_id = :p_project_id
AND    version_name = :p_version;

Comparing plan totals to GL figures after conversion:

SELECT project_id, amount_type_code,
       period1 + period2 + period3 AS plan_total
FROM   apps.pa_fp_sum_pv_v
WHERE  amount_type_code = 'COST'
ORDER  BY project_id;

Identifying lines eligible for drill-down based on SHOW_URL_FLAG:

SELECT project_id, resource_assignment_id, amount_type_code
FROM   apps.pa_fp_sum_pv_v
WHERE  show_url_flag = 'Y'
AND    period1 > 0;

Where a user is investigating a "calculate_gl_amount" requirement, this view supplies the plan-period inputs that a GL amount calculation can consume; the amounts exposed in PERIOD1 through PERIODn, together with the amount type and version, form the plan side of any plan-to-GL comparison. Because PA_FP_SUM_PV_V is a view rather than a table, it cannot be updated directly; all maintenance must occur in the underlying PA financial plan tables and period-value tables.