[Home] [Help]
MATERIALIZED VIEW: APPS.OKI_SRM_037_MV
Source
Select /* 12.0: bug#4526784 */ 1 LUMARKER
, umarker
, authoring_org_id AUTHORING_ORG_ID
, rg_id RG_ID
, prg_id PRG_ID
, -999 RESOURCE_ID
, SERVICE_ITEM_CATEGORY_ID
, grouping_id(SERVICE_ITEM_CATEGORY_ID) PC_FLAG
, GROUPING_ID ( UMARKER
,rg_id
,prg_id
,f.authoring_org_id
,SERVICE_ITEM_CATEGORY_ID
,sts_code
,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,
sts_code,
sum(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_f END) O_RCR_AMT_F
,sum(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_g END) O_RCR_AMT_G
,sum(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_sg END) O_RCR_AMT_SG
,sum(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_a END) O_RCR_AMT_A
,sum(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_f END) B_RCR_AMT_F
,sum(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_g END) B_RCR_AMT_G
,sum(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_sg END) B_RCR_AMT_SG
,sum(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_a END) B_RCR_AMT_A
,sum(price_negotiated_f) C_R_AMT_F
,sum(price_negotiated_g) C_R_AMT_G
,sum(price_negotiated_sg) C_R_AMT_SG
,sum(price_negotiated_a) C_R_AMT_A
,count(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_f END) C_O_RCR_AMT_F
,count(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_g END) C_O_RCR_AMT_G
,count(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_sg END) C_O_RCR_AMT_SG
,count(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_a END) C_O_RCR_AMT_A
,count(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_f END) C_B_RCR_AMT_F
,count(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_g END) C_B_RCR_AMT_G
,count(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_sg END) C_B_RCR_AMT_SG
,count(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_a END) C_B_RCR_AMT_A
,count(price_negotiated_f) C_C_R_AMT_F
,count(price_negotiated_g) C_C_R_AMT_G
,count(price_negotiated_sg) C_C_R_AMT_SG
,count(price_negotiated_a ) C_C_R_AMT_A
,count(*) AS c_total FROM OKI_SRM_002_MV f
,FII.FII_TIME_DAY FII
WHERE date_cancelled = fii.report_date
AND renewal_flag IN (1, 3)
AND 1=1
GROUP BY UMARKER
,rg_id
,prg_id
,sts_code
, rollup(SERVICE_ITEM_CATEGORY_ID)
,authoring_org_id
,ROLLUP(ent_qtr_id, ent_period_id, week_id, report_date_julian)
UNION ALL
SELECT 2 LUMARKER
, 'RESOURCE' UMARKER
, authoring_org_id
, RESOURCE_GROUP_ID rg_id
, RESOURCE_GROUP_ID prg_id
, NVL (resource_id, -1) resource_id
, SERVICE_ITEM_CATEGORY_ID
, grouping(SERVICE_ITEM_CATEGORY_ID) PC_FLAG
, GROUPING_ID ( RESOURCE_GROUP_ID
,NVL (resource_id, -1)
,authoring_org_id
,SERVICE_ITEM_CATEGORY_ID
,sts_code
,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,
sts_code,
sum(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_f END) O_RCR_AMT_F
,sum(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_g END) O_RCR_AMT_G
,sum(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_sg END) O_RCR_AMT_SG
,sum(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_a END) O_RCR_AMT_A
,sum(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_f END) B_RCR_AMT_F
,sum(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_g END) B_RCR_AMT_G
,sum(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_sg END) B_RCR_AMT_SG
,sum(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_a END) B_RCR_AMT_A
,sum(price_negotiated_f) C_R_AMT_F
,sum(price_negotiated_g) C_R_AMT_G
,sum(price_negotiated_sg) C_R_AMT_SG
,sum(price_negotiated_a) C_R_AMT_A
,count(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_f END) C_O_RCR_AMT_F
,count(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_g END) C_O_RCR_AMT_G
,count(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_sg END) C_O_RCR_AMT_SG
,count(CASE WHEN f.cle_creation_date < date_cancelled THEN price_negotiated_a END) C_O_RCR_AMT_A
,count(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_f END) C_B_RCR_AMT_F
,count(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_g END) C_B_RCR_AMT_G
,count(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_sg END) C_B_RCR_AMT_SG
,count(CASE WHEN f.start_date < date_cancelled THEN price_negotiated_a END) C_B_RCR_AMT_A
,count(price_negotiated_f) C_C_R_AMT_F
,count(price_negotiated_g) C_C_R_AMT_G
,count(price_negotiated_sg) C_C_R_AMT_SG
,count(price_negotiated_a ) C_C_R_AMT_A
,count(*) AS c_total FROM OKI_SRM_001_MV f
,FII.FII_TIME_DAY FII
WHERE date_cancelled = fii.report_date
AND renewal_flag IN (1, 3)GROUP BY resource_group_id,
NVL (resource_id, -1),
rollup(SERVICE_ITEM_CATEGORY_ID),
authoring_org_id,
sts_code,
ROLLUP(ent_qtr_id, ent_period_id, week_id, report_date_julian)