Select /* 12.0: bug#4526784 */ UMARKER
,authoring_org_id
,rg_id
,prg_id
,-999 RESOURCE_ID
,SERVICE_ITEM_ORG_ID
,SERVICE_ITEM_CATEGORY_ID
,class_code
, Grouping_ID(class_code) CC_FLAG
, GROUPING_ID (umarker
,class_code
,rg_id
,prg_id
,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(CASE WHEN exp_renewal_flag = 2 THEN f.price_negotiated_f END) X_RG_AMT_F
, SUM(CASE WHEN exp_renewal_flag = 2 THEN f.price_negotiated_g END) X_RG_AMT_G
, SUM(CASE WHEN exp_renewal_flag = 2 THEN f.price_negotiated_sg END) X_RG_AMT_SG
, SUM(CASE WHEN exp_renewal_flag = 2 THEN f.price_negotiated_a END) X_RG_AMT_A
, SUM(CASE WHEN exp_renewal_flag = 4 THEN f.price_negotiated_f END) X_RC_AMT_F
, SUM(CASE WHEN exp_renewal_flag = 4 THEN f.price_negotiated_g END) X_RC_AMT_G
, SUM(CASE WHEN exp_renewal_flag = 4 THEN f.price_negotiated_sg END) X_RC_AMT_SG
, SUM(CASE WHEN exp_renewal_flag = 4 THEN f.price_negotiated_a END) X_RC_AMT_A
, SUM(CASE WHEN exp_renewal_flag = 1 THEN f.price_negotiated_f END) X_RO_O_AMT_F
, SUM(CASE WHEN exp_renewal_flag = 1 THEN f.price_negotiated_g END) X_RO_O_AMT_G
, SUM(CASE WHEN exp_renewal_flag = 1 THEN f.price_negotiated_sg END) X_RO_O_AMT_SG
, SUM(CASE WHEN exp_renewal_flag = 1 THEN f.price_negotiated_a END) X_RO_O_AMT_A
, SUM(CASE WHEN exp_renewal_flag = 8 THEN f.price_negotiated_f END) X_RD_O_AMT_F
, SUM(CASE WHEN exp_renewal_flag = 8 THEN f.price_negotiated_g END) X_RD_O_AMT_G
, SUM(CASE WHEN exp_renewal_flag = 8 THEN f.price_negotiated_sg END) X_RD_O_AMT_SG
, SUM(CASE WHEN exp_renewal_flag = 8 THEN f.price_negotiated_a END) X_RD_O_AMT_A
, SUM (CASE WHEN f.date_signed IS NOT NULL AND f.date_terminated IS NULL THEN f.price_negotiated_f ELSE 0 END) BAL_K_AMT_F
, SUM (CASE WHEN f.date_signed IS NOT NULL AND f.date_terminated IS NULL THEN f.price_negotiated_g ELSE 0 END) BAL_K_AMT_G
, SUM (CASE WHEN f.date_signed IS NOT NULL AND f.date_terminated IS NULL THEN f.price_negotiated_sg ELSE 0 END) BAL_K_AMT_SG
, SUM (CASE WHEN f.date_signed IS NOT NULL AND f.date_terminated IS NULL THEN f.price_negotiated_a ELSE 0 END) BAL_K_AMT_A
, COUNT(CASE WHEN exp_renewal_flag = 2 THEN f.price_negotiated_f END) C_X_RG_AMT_F
, COUNT(CASE WHEN exp_renewal_flag = 2 THEN f.price_negotiated_g END) C_X_RG_AMT_G
, COUNT(CASE WHEN exp_renewal_flag = 2 THEN f.price_negotiated_sg END)C_X_RG_AMT_SG
, COUNT(CASE WHEN exp_renewal_flag = 2 THEN f.price_negotiated_a END) C_X_RG_AMT_A
, COUNT(CASE WHEN exp_renewal_flag = 4 THEN f.price_negotiated_f END) C_X_RC_AMT_F
, COUNT(CASE WHEN exp_renewal_flag = 4 THEN f.price_negotiated_g END) C_X_RC_AMT_G
, COUNT(CASE WHEN exp_renewal_flag = 4 THEN f.price_negotiated_sg END)C_X_RC_AMT_SG
, COUNT(CASE WHEN exp_renewal_flag = 4 THEN f.price_negotiated_a END) C_X_RC_AMT_A
, COUNT(CASE WHEN exp_renewal_flag = 1 THEN f.price_negotiated_f END) C_X_RO_O_AMT_F
, COUNT(CASE WHEN exp_renewal_flag = 1 THEN f.price_negotiated_g END) C_X_RO_O_AMT_G
, COUNT(CASE WHEN exp_renewal_flag = 1 THEN f.price_negotiated_sg END)C_X_RO_O_AMT_SG
, COUNT(CASE WHEN exp_renewal_flag = 1 THEN f.price_negotiated_a END) C_X_RO_O_AMT_A
, COUNT(CASE WHEN exp_renewal_flag = 8 THEN f.price_negotiated_f END) C_X_RD_O_AMT_F
, COUNT(CASE WHEN exp_renewal_flag = 8 THEN f.price_negotiated_g END) C_X_RD_O_AMT_G
, COUNT(CASE WHEN exp_renewal_flag = 8 THEN f.price_negotiated_sg END)C_X_RD_O_AMT_SG
, COUNT(CASE WHEN exp_renewal_flag = 8 THEN f.price_negotiated_a END) C_X_RD_O_AMT_A
, COUNT (CASE WHEN f.date_signed IS NOT NULL AND f.date_terminated IS NULL THEN f.price_negotiated_f ELSE 0 END) C_BAL_K_AMT_F
, COUNT (CASE WHEN f.date_signed IS NOT NULL AND f.date_terminated IS NULL THEN f.price_negotiated_g ELSE 0 END) C_BAL_K_AMT_G
, COUNT (CASE WHEN f.date_signed IS NOT NULL AND f.date_terminated IS NULL THEN f.price_negotiated_sg ELSE 0 END) C_BAL_K_AMT_SG
, COUNT (CASE WHEN f.date_signed IS NOT NULL AND f.date_terminated IS NULL THEN f.price_negotiated_a ELSE 0 END) C_BAL_K_AMT_A
, COUNT (*) AS C_TOTAL FROM oki_Srm_002_mv f
,FII.FII_TIME_DAY FII
WHERE f.effective_expire_date = fii.report_date
GROUP BY umarker
, rg_id
, prg_id
, SERVICE_ITEM_ORG_ID
, SERVICE_ITEM_CATEGORY_ID
, authoring_org_id
, ROLLUP(class_code)
, ROLLUP (ent_qtr_id, ent_period_id, week_id, report_date_julian)
[Home] [Help]