[Home] [Help]
MATERIALIZED VIEW: APPS.OPI_MTL_VAR_SUM_MV
Source
Select /* 12.0: bug#4526784 */ f.organization_id organization_id,
f.Assembly_Item_id Assembly_Item_id,
f.Assembly_Item_id||'-'||f.organization_id Item_Org_id,
nvl(i.inv_category_id,-1) inv_category_id,
grouping_id (f.organization_id,
f.Assembly_Item_id,
nvl(i.inv_category_id,-1),
c.ent_qtr_id,
c.ent_period_id,
c.week_id,
c.report_date_julian,
f.Source) GROUPING_ID,
grouping_id (nvl (i.inv_category_id,-1),
f.Assembly_Item_id) ITEM_CAT_FLAG,
decode (grouping_id (ent_qtr_id, ent_period_id,
week_id, report_date_julian),
14, report_date_julian,
13, week_id,
11, ent_period_id,
7, ent_qtr_id) time_id,
decode (grouping_id (c.ent_qtr_id, c.ent_period_id,
c.week_id, c.report_date_julian),
14, 1,
13, 16,
11, 32,
7,64) period_type_id,
c.ent_qtr_id qtr_id,
c.ent_period_id month_id,
c.week_id week_id,
c.report_date_julian day_id,
f.Source Source,
SUM (f.Std_Usage_Value_B) Std_value_b,
COUNT (f.Std_Usage_Value_B) Std_value_b_cnt,
SUM (f.Std_Usage_Value_G) Std_value_g,
COUNT (f.Std_Usage_Value_G) Std_value_g_cnt,
SUM (f.Act_Usage_Value_B) Act_value_b,
COUNT (f.Act_Usage_Value_B) Act_value_b_cnt,
SUM (f.Act_Usage_Value_G) Act_value_g,
COUNT(f.Act_Usage_Value_G) Act_value_g_cnt,
SUM (f.Std_Usage_Value_SG) Std_value_sg,
COUNT(f.Std_Usage_Value_SG) Std_value_sg_cnt,
SUM (f.Act_Usage_Value_SG) Act_value_sg,
COUNT (f.Act_Usage_Value_SG) Act_value_sg_cnt,
COUNT(*) cnt
FROM opi_mtl_var_mv_f f,
ENI.ENI_OLTP_ITEM_STAR i,
FII.FII_TIME_DAY c
WHERE i.organization_id = f.organization_id
AND i.inventory_item_id = f.Assembly_Item_id
AND c.report_date = f.Completion_date
GROUP BY
f.organization_id,
f.source,
rollup (nvl(i.inv_category_id,-1), f.Assembly_Item_id),
grouping sets (c.ent_qtr_id, c.ent_period_id,
c.week_id, c.report_date_julian)