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
, CLASS_CODE
, GROUPING_ID (f.class_code) CC_FLAG
, GROUPING_ID ( RESOURCE_GROUP_ID
,NVL (resource_id, -1)
,authoring_org_id
,SERVICE_ITEM_ORG_ID
,SERVICE_ITEM_CATEGORY_ID
, f. class_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,
sum(CASE WHEN renewal_flag =1 THEN price_negotiated_f END) G_R_AMT_F,
sum(CASE WHEN renewal_flag =1 THEN price_negotiated_g END) G_R_AMT_G,
sum(CASE WHEN renewal_flag =1 THEN price_negotiated_sg END) G_R_AMT_SG,
sum(CASE WHEN renewal_flag =1 THEN price_negotiated_a END) G_R_AMT_A,
sum(CASE WHEN renewal_flag =1 AND p_term_flag = 1 THEN p_price_negotiated_f END) G_O_AMT_F,
sum(CASE WHEN renewal_flag =1 AND p_term_flag = 1 THEN p_price_negotiated_g END) G_O_AMT_G,
sum(CASE WHEN renewal_flag =1 AND p_term_flag = 1 THEN p_price_negotiated_sg END) G_O_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND p_term_flag = 1 THEN p_price_negotiated_a END) G_O_AMT_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date THEN price_negotiated_f END) G_SCR_AMT_Y_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date THEN price_negotiated_g END) G_SCR_AMT_Y_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date THEN price_negotiated_sg END) G_SCR_AMT_Y_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date THEN price_negotiated_a END) G_SCR_AMT_Y_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date THEN price_negotiated_f END) G_SCR_AMT_Q_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date THEN price_negotiated_g END) G_SCR_AMT_Q_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date THEN price_negotiated_sg END) G_SCR_AMT_Q_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date THEN price_negotiated_a END) G_SCR_AMT_Q_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date THEN price_negotiated_f END) G_SCR_AMT_P_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date THEN price_negotiated_g END) G_SCR_AMT_P_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date THEN price_negotiated_sg END) G_SCR_AMT_P_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date THEN price_negotiated_a END) G_SCR_AMT_P_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date AND p_term_flag = 1 THEN p_price_negotiated_f END) G_SCO_AMT_Y_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date AND p_term_flag = 1 THEN p_price_negotiated_g END) G_SCO_AMT_Y_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) G_SCO_AMT_Y_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date AND p_term_flag = 1 THEN p_price_negotiated_a END) G_SCO_AMT_Y_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date AND p_term_flag = 1 THEN p_price_negotiated_f END) G_SCO_AMT_Q_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date AND p_term_flag = 1 THEN p_price_negotiated_g END) G_SCO_AMT_Q_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) G_SCO_AMT_Q_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date AND p_term_flag = 1 THEN p_price_negotiated_a END) G_SCO_AMT_Q_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date AND p_term_flag = 1 THEN p_price_negotiated_f END) G_SCO_AMT_P_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date AND p_term_flag = 1 THEN p_price_negotiated_g END) G_SCO_AMT_P_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) G_SCO_AMT_P_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date AND p_term_flag = 1 THEN p_price_negotiated_a END) G_SCO_AMT_P_A,
sum(CASE WHEN renewal_flag =1 AND cle_creation_date < date_signed THEN price_negotiated_f END) O_RGR_AMT_F,
sum(CASE WHEN renewal_flag =1 AND cle_creation_date < date_signed THEN price_negotiated_g END) O_RGR_AMT_G,
sum(CASE WHEN renewal_flag =1 AND cle_creation_date < date_signed THEN price_negotiated_sg END) O_RGR_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND cle_creation_date < date_signed THEN price_negotiated_a END) O_RGR_AMT_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_f END) B_RGR_AMT_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_g END) B_RGR_AMT_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_sg END) B_RGR_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_a END) B_RGR_AMT_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_f END) GL_R_AMT_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_g END) GL_R_AMT_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_sg END) GL_R_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_a END) GL_R_AMT_A,
sum(CASE WHEN renewal_flag =1 AND p_grace_end_date < date_signed THEN price_negotiated_f END) GR_R_AMT_F,
sum(CASE WHEN renewal_flag =1 AND p_grace_end_date < date_signed THEN price_negotiated_g END) GR_R_AMT_G,
sum(CASE WHEN renewal_flag =1 AND p_grace_end_date < date_signed THEN price_negotiated_sg END) GR_R_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND p_grace_end_date < date_signed THEN price_negotiated_a END) GR_R_AMT_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN date_signed - f.start_date END) GL_DAYS,
sum(CASE WHEN renewal_flag =0 THEN price_negotiated_f END) G_NB_O_AMT_F,
sum(CASE WHEN renewal_flag =0 THEN price_negotiated_g END) G_NB_O_AMT_G,
sum(CASE WHEN renewal_flag =0 THEN price_negotiated_sg END) G_NB_O_AMT_SG,
sum(CASE WHEN renewal_flag =0 THEN price_negotiated_a END) G_NB_O_AMT_A,
count(CASE WHEN renewal_flag =1 THEN price_negotiated_f END) C_G_R_AMT_F,
count(CASE WHEN renewal_flag =1 THEN price_negotiated_g END) C_G_R_AMT_G,
count(CASE WHEN renewal_flag =1 THEN price_negotiated_sg END) C_G_R_AMT_SG,
count(CASE WHEN renewal_flag =1 THEN price_negotiated_a END) C_G_R_AMT_A,
count(CASE WHEN renewal_flag =1 AND p_term_flag = 1 THEN p_price_negotiated_f END) C_G_O_AMT_F,
count(CASE WHEN renewal_flag =1 AND p_term_flag = 1 THEN p_price_negotiated_g END) C_G_O_AMT_G,
count(CASE WHEN renewal_flag =1 AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_G_O_AMT_SG,
count(CASE WHEN renewal_flag =1 AND p_term_flag = 1 THEN p_price_negotiated_a END) C_G_O_AMT_A,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date THEN price_negotiated_f END) C_G_SCR_AMT_Y_F,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date THEN price_negotiated_g END) C_G_SCR_AMT_Y_G,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date THEN price_negotiated_sg END) C_G_SCR_AMT_Y_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date THEN price_negotiated_a END) C_G_SCR_AMT_Y_A,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date THEN price_negotiated_f END) C_G_SCR_AMT_Q_F,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date THEN price_negotiated_g END) C_G_SCR_AMT_Q_G,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date THEN price_negotiated_sg END) C_G_SCR_AMT_Q_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date THEN price_negotiated_a END) C_G_SCR_AMT_Q_A,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date THEN price_negotiated_f END) C_G_SCR_AMT_P_F,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date THEN price_negotiated_g END) C_G_SCR_AMT_P_G,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date THEN price_negotiated_sg END) C_G_SCR_AMT_P_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date THEN price_negotiated_a END) C_G_SCR_AMT_P_A,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date AND p_term_flag = 1 THEN p_price_negotiated_f END) C_G_SCO_AMT_Y_F,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date AND p_term_flag = 1 THEN p_price_negotiated_g END) C_G_SCO_AMT_Y_G,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_G_SCO_AMT_Y_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_year_start_date AND fii.ent_year_end_date AND p_term_flag = 1 THEN p_price_negotiated_a END) C_G_SCO_AMT_Y_A,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date AND p_term_flag = 1 THEN p_price_negotiated_f END) C_G_SCO_AMT_Q_F,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date AND p_term_flag = 1 THEN p_price_negotiated_g END) C_G_SCO_AMT_Q_G,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_G_SCO_AMT_Q_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_qtr_start_date AND fii.ent_qtr_end_date AND p_term_flag = 1 THEN p_price_negotiated_a END) C_G_SCO_AMT_Q_A,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date AND p_term_flag = 1 THEN p_price_negotiated_f END) C_G_SCO_AMT_P_F,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date AND p_term_flag = 1 THEN p_price_negotiated_g END) C_G_SCO_AMT_P_G,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_G_SCO_AMT_P_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date BETWEEN fii.ent_period_start_date AND fii.ent_period_end_date AND p_term_flag = 1 THEN p_price_negotiated_a END) C_G_SCO_AMT_P_A,
count(CASE WHEN renewal_flag =1 AND cle_creation_date < date_signed THEN price_negotiated_f END) C_O_RGR_AMT_F,
count(CASE WHEN renewal_flag =1 AND cle_creation_date < date_signed THEN price_negotiated_g END) C_O_RGR_AMT_G,
count(CASE WHEN renewal_flag =1 AND cle_creation_date < date_signed THEN price_negotiated_sg END) C_O_RGR_AMT_SG,
count(CASE WHEN renewal_flag =1 AND cle_creation_date < date_signed THEN price_negotiated_a END) C_O_RGR_AMT_A,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_f END) C_B_RGR_AMT_F,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_g END) C_B_RGR_AMT_G,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_sg END) C_B_RGR_AMT_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_a END) C_B_RGR_AMT_A,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_f END) C_GL_R_AMT_F,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_g END) C_GL_R_AMT_G,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_sg END) C_GL_R_AMT_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN price_negotiated_a END) C_GL_R_AMT_A,
/* New columns for balance */
sum(CASE WHEN renewal_flag =0 AND f.start_date < date_signed THEN price_negotiated_f END) GL_S_AMT_F,
sum(CASE WHEN renewal_flag =0 AND f.start_date < date_signed THEN price_negotiated_g END) GL_S_AMT_G,
sum(CASE WHEN renewal_flag =0 AND f.start_date < date_signed THEN price_negotiated_sg END) GL_S_AMT_SG,
sum(CASE WHEN renewal_flag =0 AND f.start_date < date_signed THEN price_negotiated_a END) GL_S_AMT_A,
count(CASE WHEN renewal_flag =0 AND f.start_date < date_signed THEN price_negotiated_f END) C_GL_S_AMT_F,
count(CASE WHEN renewal_flag =0 AND f.start_date < date_signed THEN price_negotiated_g END) C_GL_S_AMT_G,
count(CASE WHEN renewal_flag =0 AND f.start_date < date_signed THEN price_negotiated_sg END) C_GL_S_AMT_SG,
count(CASE WHEN renewal_flag =0 AND f.start_date < date_signed THEN price_negotiated_a END) C_GL_S_AMT_A,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed AND p_term_flag = 1 THEN p_price_negotiated_f END) GL_O_AMT_F,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed AND p_term_flag = 1 THEN p_price_negotiated_g END) GL_O_AMT_G,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed AND p_term_flag = 1 THEN p_price_negotiated_sg END) GL_O_AMT_SG,
sum(CASE WHEN renewal_flag =1 AND f.start_date < date_signed AND p_term_flag = 1 THEN p_price_negotiated_a END) GL_O_AMT_A,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed AND p_term_flag = 1 THEN p_price_negotiated_f END) C_GL_O_AMT_F,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed AND p_term_flag = 1 THEN p_price_negotiated_g END) C_GL_O_AMT_G,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed AND p_term_flag = 1 THEN p_price_negotiated_sg END) C_GL_O_AMT_SG,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed AND p_term_flag = 1 THEN p_price_negotiated_a END) C_GL_O_AMT_A,
/* End of new columns for balance */
count(CASE WHEN renewal_flag =1 AND p_grace_end_date < date_signed THEN price_negotiated_f END) C_GR_R_AMT_F,
count(CASE WHEN renewal_flag =1 AND p_grace_end_date < date_signed THEN price_negotiated_g END) C_GR_R_AMT_G,
count(CASE WHEN renewal_flag =1 AND p_grace_end_date < date_signed THEN price_negotiated_sg END) C_GR_R_AMT_SG,
count(CASE WHEN renewal_flag =1 AND p_grace_end_date < date_signed THEN price_negotiated_a END) C_GR_R_AMT_A,
count(CASE WHEN renewal_flag =1 AND f.start_date < date_signed THEN date_signed - f.start_date END) GL_DAYS_COUNT,
count(CASE WHEN renewal_flag =0 THEN price_negotiated_f END) C_G_NB_O_AMT_F,
count(CASE WHEN renewal_flag =0 THEN price_negotiated_g END) C_G_NB_O_AMT_G,
count(CASE WHEN renewal_flag =0 THEN price_negotiated_sg END) C_G_NB_O_AMT_SG,
count(CASE WHEN renewal_flag =0 THEN price_negotiated_a END) C_G_NB_O_AMT_A,
count(*) AS c_total FROM oki_Srm_001_mv f
,FII.FII_TIME_DAY FII
WHERE f.date_signed = fii.report_date
GROUP BY resource_group_id,
NVL (resource_id, -1),
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]