[Home] [Help]
MATERIALIZED VIEW: APPS.OKI_SRM_016_MV
Source
Select /* 12.0: bug#4526784 */ 'RESOURCE' UMARKER
, authoring_org_id
, RESOURCE_GROUP_ID rg_id
, RESOURCE_GROUP_ID prg_id
, NVL (resource_id, -1) resource_id
, SERVICE_ITEM_ORG_ID
, SERVICE_ITEM_CATEGORY_ID
, GROUPING_ID ( RESOURCE_GROUP_ID
,NVL (resource_id, -1)
,authoring_org_id
,SERVICE_ITEM_ORG_ID
,SERVICE_ITEM_CATEGORY_ID
,ent_qtr_id
,ent_period_id
,week_id
,report_date_julian) GRP_ID
,decode( grouping_id(ent_qtr_id,ent_period_id,week_id,report_date_julian),
0 ,report_date_julian,
1 ,week_id,
3 ,ent_period_id,
7 ,ent_qtr_id) TIME_ID,
ent_qtr_id ENT_QTR_ID,
ent_period_id ENT_PERIOD_ID,
week_id WEEK_ID,
report_date_julian DAY_ID,
SUM ( f.price_negotiated_f * f.win_percent * 0.01) F_F_AMT_F
, SUM ( f.price_negotiated_g * f.win_percent * 0.01) F_F_AMT_G
, SUM ( f.price_negotiated_sg * f.win_percent * 0.01) F_F_AMT_SG
, SUM ( f.price_negotiated_a * f.win_percent * 0.01) F_F_AMT_a
, SUM (f.price_negotiated_f) F_R_AMT_F
, SUM (f.price_negotiated_g) F_R_AMT_G
, SUM (f.price_negotiated_sg) F_R_AMT_SG
, SUM (f.price_negotiated_a) F_R_AMT_a
, COUNT ( f.price_negotiated_f * f.win_percent * 0.01) C_F_F_AMT_F
, COUNT ( f.price_negotiated_g * f.win_percent * 0.01) C_F_F_AMT_G
, COUNT ( f.price_negotiated_sg * f.win_percent * 0.01) C_F_F_AMT_SG
, COUNT ( f.price_negotiated_a * f.win_percent * 0.01) C_F_F_AMT_a
, COUNT (f.price_negotiated_f) C_F_R_AMT_F
, COUNT (f.price_negotiated_g) C_F_R_AMT_G
, COUNT (f.price_negotiated_sg) C_F_R_AMT_SG
, COUNT (f.price_negotiated_a) C_F_R_AMT_a
,count(*) AS c_total FROM oki_Srm_001_mv f
,FII.FII_TIME_DAY FII
WHERE expected_close_date = fii.report_date
AND NVL(f.date_cancelled, f.date_signed) IS NULL
AND renewal_flag IN (1, 3)
AND 1=1
GROUP BY resource_group_id,
NVL (resource_id, -1),
SERVICE_ITEM_ORG_ID,
SERVICE_ITEM_CATEGORY_ID,
authoring_org_id,
ROLLUP(ent_qtr_id, ent_period_id, week_id, report_date_julian)