The 1.8.X or 1.9.X Patch is Stuck on Creating the Materialized View SOLAP_ACT_COST_PLAN_EST and SOLAP_PLANNING_ACCURACY_IDX (Doc ID 981199.1)

Last updated on OCTOBER 16, 2016

Applies to:

Oracle Utilities Work and Asset Management - Version 1.8.0 and later
Information in this document applies to any platform.

Symptoms

The 1.8.X or 1.9.X database patch is still running and is stuck on creating materialized view Synergen. SOLAP_ACT_COST_PLAN_EST. Here is the SQL that is running:

SELECT

--
-- Description:
-- This is the final view called by WO Actual Cost to Planning Est Metric
--
-- Dependencies:
-- SA_OLAP_DATE_DIMENSION
-- SOLAP_1_ACT_COST_PLAN_EST
-- SV_ACT_COST_PLAN_QTR_OFFSET
-- DUAL
--
-- Called from:
-- ActualCostToPlanningEstimate.xls
--
-- History:
-- 18-2 07-Aug-2008 BBT Changed to become a materialized view.
-- 8.1 20-Nov-2006 HVU Revised script to meet standard
-- 8.0 14-Nov-2006 HVU Initial Version
--
NVL (a.data_source, 'z_date_filler') AS data_source,
NVL (a.plant, 'z_date_filler') AS plant, work_type, work_class,
work_category, a.work_order_no, a.planner_name,
TRUNC (a.created_date) AS created_date,
NVL (a.created_year, dd.YEAR) AS created_year,
NVL (a.created_qtr, dd.year_qtr) AS created_qtr,
NVL (a.created_month, dd.year_month) AS created_month,
DECODE (((SELECT time_basis
FROM sv_act_cost_plan_qtr_offset)),
'QUARTERLY', NVL (a.created_qtr, dd.year_qtr),
NVL (a.created_month, dd.year_month)
) AS time_basis,
NVL (a.original_estimate_amount, 0) AS original_estimate_amount,
NVL (a.revised_estimate_amount, 0) AS revised_estimated_amount,
NVL (a.actual_amount, 0) AS actual_amount,
NVL (a.actual_to_orig_est_pct, 0) AS actual_to_orig_est_pct,
NVL (a.actual_to_revised_est_pct, 0) AS actual_to_revised_est_pct
FROM solap_1_act_cost_plan_est a, sa_olap_date_dimension dd
WHERE a.created_month(+) = dd.year_month
AND dd.year_month >=
(SELECT (TO_CHAR
(ADD_MONTHS (SYSDATE,
- (SELECT months_to_offset
FROM
sv_act_cost_plan_qtr_offset)
),
'yyyy-mm'
)
)
FROM DUAL)
AND dd.year_month <= (SELECT TO_CHAR (SYSDATE, 'yyyy-mm')
FROM DUAL)

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms