Select /* 12.0: bug#4526784 */ f.umarker
, f.authoring_org_id
, f.rg_id
, f.prg_id
, -999 RESOURCE_ID
, SERVICE_ITEM_ORG_ID
, SERVICE_ITEM_CATEGORY_ID
, f.class_code
, grouping (f.class_code) CC_FLAG
,GROUPING_ID (umarker,
rg_id, prg_id
,SERVICE_ITEM_ORG_ID
,SERVICE_ITEM_CATEGORY_ID
,class_code
,authoring_org_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 renewal_flag =1 THEN price_negotiated_f END) S_R_AMT_F,
sum(CASE WHEN renewal_flag =1 THEN price_negotiated_g END) S_R_AMT_G,
sum(CASE WHEN renewal_flag =1 THEN price_negotiated_sg END) S_R_AMT_SG,
sum(CASE WHEN renewal_flag =1 THEN price_negotiated_a END) S_R_AMT_A,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date THEN price_negotiated_f END) S_GPR_AMT_Y_F,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date THEN price_negotiated_g END) S_GPR_AMT_Y_G,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date THEN price_negotiated_sg END) S_GPR_AMT_Y_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date THEN price_negotiated_a END) S_GPR_AMT_Y_A,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date THEN price_negotiated_f END) S_GPR_AMT_Q_F,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date THEN price_negotiated_g END) S_GPR_AMT_Q_G,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date THEN price_negotiated_sg END) S_GPR_AMT_Q_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date THEN price_negotiated_a END) S_GPR_AMT_Q_A,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date THEN price_negotiated_f END) S_GPR_AMT_P_F,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date THEN price_negotiated_g END) S_GPR_AMT_P_G,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date THEN price_negotiated_sg END) S_GPR_AMT_P_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date THEN price_negotiated_a END) S_GPR_AMT_P_A,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date AND p_term_flag = 1 THEN p_price_negotiated_f END) S_GPO_AMT_Y_F,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date AND p_term_flag = 1 THEN p_price_negotiated_g END) S_GPO_AMT_Y_G,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) S_GPO_AMT_Y_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date AND p_term_flag = 1 THEN p_price_negotiated_a END) S_GPO_AMT_Y_A,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date AND p_term_flag = 1 THEN p_price_negotiated_f END) S_GPO_AMT_Q_F,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date AND p_term_flag = 1 THEN p_price_negotiated_g END) S_GPO_AMT_Q_G,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) S_GPO_AMT_Q_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date AND p_term_flag = 1 THEN p_price_negotiated_a END) S_GPO_AMT_Q_A,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date AND p_term_flag = 1 THEN p_price_negotiated_f END) S_GPO_AMT_P_F,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date AND p_term_flag = 1 THEN p_price_negotiated_g END) S_GPO_AMT_P_G,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) S_GPO_AMT_P_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date AND p_term_flag = 1 THEN p_price_negotiated_a END) S_GPO_AMT_P_A,
sum(CASE WHEN renewal_flag =1 AND date_cancelled IS NOT NULL THEN price_negotiated_f END) C_SCR_AMT_F,
sum(CASE WHEN renewal_flag =1 AND date_cancelled IS NOT NULL THEN price_negotiated_g END) C_SCR_AMT_G,
sum(CASE WHEN renewal_flag =1 AND date_cancelled IS NOT NULL THEN price_negotiated_sg END) C_SCR_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND date_cancelled IS NOT NULL THEN price_negotiated_a END) C_SCR_AMT_A,
sum(CASE WHEN renewal_flag =1 AND nvl(date_signed,f.start_date + 1) > f.start_date AND nvl(date_cancelled, f.start_date + 1) > f.start_date THEN price_negotiated_f END) B_R_AMT_F,
sum(CASE WHEN renewal_flag =1 AND nvl(date_signed,f.start_date + 1) > f.start_date AND nvl(date_cancelled, f.start_date + 1) > f.start_date THEN price_negotiated_g END) B_R_AMT_G,
sum(CASE WHEN renewal_flag =1 AND nvl(date_signed,f.start_date + 1) > f.start_date AND nvl(date_cancelled, f.start_date + 1) > f.start_date THEN price_negotiated_sg END) B_R_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND nvl(date_signed,f.start_date + 1) > f.start_date AND nvl(date_cancelled, f.start_date + 1) > f.start_date THEN price_negotiated_a END) B_R_AMT_A,
sum(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL THEN price_negotiated_f END) S_G_O_AMT_F,
sum(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL THEN price_negotiated_g END) S_G_O_AMT_G,
sum(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL THEN price_negotiated_sg END) S_G_O_AMT_SG,
sum(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL THEN price_negotiated_a END) S_G_O_AMT_A,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL THEN price_negotiated_f END) S_R_O_AMT_F,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL THEN price_negotiated_g END) S_R_O_AMT_G,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL THEN price_negotiated_sg END) S_R_O_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL THEN price_negotiated_a END) S_R_O_AMT_A,
/* Addition of new columns for balance */
sum(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_f END) S_G_AMT_F,
sum(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_g END) S_G_AMT_G,
sum(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_sg END) S_G_AMT_SG,
sum(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_a END) S_G_AMT_A,
count(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_f END) C_S_G_AMT_F,
count(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_g END) C_S_G_AMT_G,
count(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_sg END) C_S_G_AMT_SG,
count(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_a END) C_S_G_AMT_A,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_f END) S_RG_AMT_F,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_g END) S_RG_AMT_G,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_sg END) S_RG_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_a END) S_RG_AMT_A,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_f END) C_S_RG_AMT_F,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_g END) C_S_RG_AMT_G,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_sg END) C_S_RG_AMT_SG,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND date_signed<=f.start_date THEN price_negotiated_a END) C_S_RG_AMT_A,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 AND date_signed<=f.start_date THEN p_price_negotiated_f END) S_XG_R_AMT_F,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 AND date_signed<=f.start_date THEN p_price_negotiated_g END) S_XG_R_AMT_G,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 AND date_signed<=f.start_date THEN p_price_negotiated_sg END) S_XG_R_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 AND date_signed<=f.start_date THEN p_price_negotiated_a END) S_XG_R_AMT_A,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 AND date_signed<=f.start_date THEN p_price_negotiated_f END) C_S_XG_R_AMT_F,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 AND date_signed<=f.start_date THEN p_price_negotiated_g END) C_S_XG_R_AMT_G,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 AND date_signed<=f.start_date THEN p_price_negotiated_sg END) C_S_XG_R_AMT_SG,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 AND date_signed<=f.start_date THEN p_price_negotiated_a END) C_S_XG_R_AMT_A,
/* Addition of new columns for balance */
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 THEN p_price_negotiated_f END) S_X_R_AMT_F,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 THEN p_price_negotiated_g END) S_X_R_AMT_G,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 THEN p_price_negotiated_sg END) S_X_R_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 THEN p_price_negotiated_a END) S_X_R_AMT_A,
count(CASE WHEN renewal_flag =1 THEN price_negotiated_f END) C_S_R_AMT_F,
count(CASE WHEN renewal_flag =1 THEN price_negotiated_g END) C_S_R_AMT_G,
count(CASE WHEN renewal_flag =1 THEN price_negotiated_sg END) C_S_R_AMT_SG,
count(CASE WHEN renewal_flag =1 THEN price_negotiated_a END) C_S_R_AMT_A,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date THEN price_negotiated_f END) C_S_GPR_AMT_Y_F,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date THEN price_negotiated_g END) C_S_GPR_AMT_Y_G,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date THEN price_negotiated_sg END) C_S_GPR_AMT_Y_SG,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date THEN price_negotiated_a END) C_S_GPR_AMT_Y_A,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date THEN price_negotiated_f END) C_S_GPR_AMT_Q_F,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date THEN price_negotiated_g END) C_S_GPR_AMT_Q_G,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date THEN price_negotiated_sg END) C_S_GPR_AMT_Q_SG,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date THEN price_negotiated_a END) C_S_GPR_AMT_Q_A,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date THEN price_negotiated_f END) C_S_GPR_AMT_P_F,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date THEN price_negotiated_g END) C_S_GPR_AMT_P_G,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date THEN price_negotiated_sg END) C_S_GPR_AMT_P_SG,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date THEN price_negotiated_a END) C_S_GPR_AMT_P_A,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date AND p_term_flag = 1 THEN p_price_negotiated_f END) C_S_GPO_AMT_Y_F,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date AND p_term_flag = 1 THEN p_price_negotiated_g END) C_S_GPO_AMT_Y_G,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_S_GPO_AMT_Y_SG,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_year_start_date AND p_term_flag = 1 THEN p_price_negotiated_a END) C_S_GPO_AMT_Y_A,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date AND p_term_flag = 1 THEN p_price_negotiated_f END) C_S_GPO_AMT_Q_F,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date AND p_term_flag = 1 THEN p_price_negotiated_g END) C_S_GPO_AMT_Q_G,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_S_GPO_AMT_Q_SG,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_qtr_start_date AND p_term_flag = 1 THEN p_price_negotiated_a END) C_S_GPO_AMT_Q_A,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date AND p_term_flag = 1 THEN p_price_negotiated_f END) C_S_GPO_AMT_P_F,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date AND p_term_flag = 1 THEN p_price_negotiated_g END) C_S_GPO_AMT_P_G,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_S_GPO_AMT_P_SG,
count(CASE WHEN renewal_flag =1 AND date_signed < fii.ent_period_start_date AND p_term_flag = 1 THEN p_price_negotiated_a END) C_S_GPO_AMT_P_A,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 THEN p_price_negotiated_f END) C_S_X_R_AMT_F,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 THEN p_price_negotiated_g END) C_S_X_R_AMT_G,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_S_X_R_AMT_SG,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL AND p_term_flag = 1 THEN p_price_negotiated_a END) C_S_X_R_AMT_A,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL THEN price_negotiated_f END) C_S_R_O_AMT_F,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL THEN price_negotiated_g END) C_S_R_O_AMT_G,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL THEN price_negotiated_sg END) C_S_R_O_AMT_SG,
count(CASE WHEN renewal_flag =1 AND date_signed IS NOT NULL THEN price_negotiated_a END) C_S_R_O_AMT_A,
count(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL THEN price_negotiated_f END) C_S_G_O_AMT_F,
count(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL THEN price_negotiated_g END) C_S_G_O_AMT_G,
count(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL THEN price_negotiated_sg END) C_S_G_O_AMT_SG,
count(CASE WHEN renewal_flag =0 AND date_signed IS NOT NULL THEN price_negotiated_a END) C_S_G_O_AMT_A,
count(CASE WHEN renewal_flag =1 AND nvl(date_signed,f.start_date + 1) > f.start_date AND nvl(date_cancelled, f.start_date + 1) > f.start_date THEN price_negotiated_f END) C_B_R_AMT_F,
count(CASE WHEN renewal_flag =1 AND nvl(date_signed,f.start_date + 1) > f.start_date AND nvl(date_cancelled, f.start_date + 1) > f.start_date THEN price_negotiated_g END) C_B_R_AMT_G,
count(CASE WHEN renewal_flag =1 AND nvl(date_signed,f.start_date + 1) > f.start_date AND nvl(date_cancelled, f.start_date + 1) > f.start_date THEN price_negotiated_sg END) C_B_R_AMT_SG,
count(CASE WHEN renewal_flag =1 AND nvl(date_signed,f.start_date + 1) > f.start_date AND nvl(date_cancelled, f.start_date + 1) > f.start_date THEN price_negotiated_a END) C_B_R_AMT_A,
count(CASE WHEN renewal_flag =1 AND date_cancelled IS NOT NULL THEN price_negotiated_f END) C_C_SCR_AMT_F,
count(CASE WHEN renewal_flag =1 AND date_cancelled IS NOT NULL THEN price_negotiated_g END) C_C_SCR_AMT_G,
count(CASE WHEN renewal_flag =1 AND date_cancelled IS NOT NULL THEN price_negotiated_sg END) C_C_SCR_AMT_SG,
count(CASE WHEN renewal_flag =1 AND date_cancelled IS NOT NULL THEN price_negotiated_a END) C_C_SCR_AMT_A,
count(*) AS c_total FROM oki_Srm_002_mv f
,FII.FII_TIME_DAY FII
WHERE f.start_date = fii.report_date
GROUP BY umarker
,rg_id, prg_id
,SERVICE_ITEM_ORG_ID
,SERVICE_ITEM_CATEGORY_ID
,ROLLUP(class_code)
,authoring_org_id
,ROLLUP(ent_qtr_id, ent_period_id, week_id, report_date_julian)
[Home] [Help]