Search Results fpa_sce_str_scores_tbl

Overview

The view FPA_AW_SCE_STR_SCORES_V is an APPS-owned database view belonging to the FPA – Project Portfolio Analysis product family in Oracle E-Business Suite 12.1.1 and 12.2.2. It exposes strategic investment scoring data that has been calculated and stored within an Oracle OLAP analytic workspace, presenting weighted scoring results at the intersection of a scenario, a planning cycle, and an investment criterion. The view acts as a relational window onto multidimensional OLAP data, allowing the strategic scoring results generated by Project Portfolio Analysis to be queried with ordinary SQL rather than through OLAP-specific access methods.

Because the view name carries the _AW_ (analytic workspace) infix and the columns are sourced from OLAP measures, this object is intended for reporting, verification, and downstream integration of portfolio scoring outcomes. Administrators and developers typically use it to inspect the weighted strategic scores produced for alternative scenarios, to reconcile scores presented in the FPA user interface, and to feed scoring results into custom dashboards, extracts, or third-party analytics. Note that in the documented ETRM metadata the object is recorded with status INVALID, meaning it must be recompiled (or its dependencies validated) after the associated package and OLAP workspace objects are properly in place.

Underlying Base Objects

The view is not defined over conventional relational tables. Its defining query invokes the OLAP_TABLE function (referenced through the public synonym owned by the OLAP option) to pivot data out of an analytic workspace identified dynamically by FPA_UTILITIES_PVT.AW_SPACE_NAME. The view text therefore depends on the package FPA_UTILITIES_PVT and on the OLAP_TABLE synonym, which constitute its documented referenced base objects. The OLAP_TABLE call specifies a duration query, an output table type of FPA_SCE_STR_SCORES_TBL, and a mapping that defines dimensions and measures.

• SCENARIO_D supplies the SCENARIO dimension, with the planning cycle derived as an attribute from PLANNING_CYCLE_SCENARIO_R.
• STRATEGIC_OBJ_D supplies the INVESTMENT_CRITERIA dimension.
• SCENARIO_OBJ_WSCORE_M supplies the WEIGHTED_SCORE measure.
• SCENARIO_OBJ_CWSCORE_M supplies the COST_WEIGHTED_SCORE measure.

Consequently, the view inherits the availability and validity of the underlying analytic workspace. If the workspace is not built, or if FPA_UTILITIES_PVT does not return a valid workspace name, the view cannot return rows.

Key Columns

• SCENARIO – Identifies the portfolio scenario under evaluation. Each scenario represents a distinct set of assumptions against which projects are ranked.
• PLANNING_CYCLE – The planning cycle attribute associated with the scenario, used to group or filter scoring results by cycle.
• INVESTMENT_CRITERIA – The strategic objective or investment criterion against which projects are scored.
• WEIGHTED_SCORE – The scenario-level weighted score for the investment criterion, sourced from the SCENARIO_OBJ_WSCORE_M measure.
• COST_WEIGHTED_SCORE – The cost-adjusted weighted score for the criterion, sourced from the SCENARIO_OBJ_CWSCORE_M measure.

Common Use Cases and Queries

Typical usage involves retrieving the weighted and cost-weighted scores across all criteria for one or more scenarios, often to compare scenario outcomes or to validate what the application displays.

• Reviewing all criterion scores for a given scenario and cycle.
• Comparing weighted versus cost-weighted scores to assess the impact of cost adjustments on ranking.
• Extracting scoring results into custom reports or data warehouses.

Sample query:

SELECT scenario,
       planning_cycle,
       investment_criteria,
       weighted_score,
       cost_weighted_score
FROM   apps.fpa_aw_sce_str_scores_v
WHERE  scenario = :p_scenario
ORDER  BY investment_criteria;

Because the view is documented as INVALID, ensure the OLAP workspace and FPA_UTILITIES_PVT are correctly configured, and recompile the view before relying on it in production reporting or integration paths.