MATERIALIZED VIEW: APPS.OKI_SRM_035_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
       , class_code
       , 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
                      ,class_code
                      , trn_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,
        trn_code
	     , SUM( f.price_negotiated_f - f.trn_billed_value_f )	T_RV_AMT_F
       , SUM( f.price_negotiated_g - f.trn_billed_value_g )	T_RV_AMT_G
       , SUM( f.price_negotiated_sg - f.trn_billed_value_sg)T_RV_AMT_SG
       , SUM( f.price_negotiated_a - f.trn_billed_value_a )	T_RV_AMT_A
       , SUM( f.trn_billed_value_f )		T_BV_AMT_F
       , SUM( f.trn_billed_value_g )		T_bV_AMT_G
       , SUM( f.trn_billed_value_sg )		T_BV_AMT_SG
       , SUM( f.trn_billed_value_a )		T_BV_AMT_A
       , SUM (f.price_negotiated_f) BAL_K_AMT_F
       , SUM (f.price_negotiated_g) BAL_K_AMT_G
       , SUM (f.price_negotiated_sg)BAL_K_AMT_SG
       , SUM (f.price_negotiated_a) BAL_K_AMT_A
       , COUNT( f.price_negotiated_f - f.trn_billed_value_f ) C_T_RV_AMT_F
       , COUNT( f.price_negotiated_g - f.trn_billed_value_g )	C_T_RV_AMT_G
       , COUNT( f.price_negotiated_sg - f.trn_billed_value_sg)C_T_RV_AMT_SG
       , COUNT( f.price_negotiated_a - f.trn_billed_value_a )	C_T_RV_AMT_A
       , COUNT( f.trn_billed_value_f )		C_T_BV_AMT_F
       , COUNT( f.trn_billed_value_g )		C_T_BV_AMT_G
       , COUNT( f.trn_billed_value_sg )	  C_T_BV_AMT_SG
       , COUNT( f.trn_billed_value_a )		C_T_BV_AMT_A
       , COUNT (f.price_negotiated_f) C_BAL_K_AMT_F
       , COUNT (f.price_negotiated_g) C_BAL_K_AMT_G
       , COUNT (f.price_negotiated_sg)C_BAL_K_AMT_SG
       , COUNT (f.price_negotiated_a) C_BAL_K_AMT_A
       , count(*) AS c_total FROM   OKI_SRM_002_MV f
                     ,FII.FII_TIME_DAY FII
WHERE f.effective_term_date = fii.report_date
GROUP BY UMARKER
        ,rg_id
	,prg_id
	, class_code
	,trn_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
       , class_Code
       , 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
                      ,class_code
                      ,trn_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,
        trn_code
       , SUM( f.price_negotiated_f - f.trn_billed_value_f )	T_RV_AMT_F
       , SUM( f.price_negotiated_g - f.trn_billed_value_g )	T_RV_AMT_G
       , SUM( f.price_negotiated_sg - f.trn_billed_value_sg)T_RV_AMT_SG
       , SUM( f.price_negotiated_a - f.trn_billed_value_a )	T_RV_AMT_A
       , SUM( f.trn_billed_value_f )		T_BV_AMT_F
       , SUM( f.trn_billed_value_g )		T_bV_AMT_G
       , SUM( f.trn_billed_value_sg )		T_BV_AMT_SG
       , SUM( f.trn_billed_value_a )		T_BV_AMT_A
       , SUM (f.price_negotiated_f) BAL_K_AMT_F
       , SUM (f.price_negotiated_g) BAL_K_AMT_G
       , SUM (f.price_negotiated_sg)BAL_K_AMT_SG
       , SUM (f.price_negotiated_a) BAL_K_AMT_A
       , COUNT( f.price_negotiated_f - f.trn_billed_value_f ) C_T_RV_AMT_F
       , COUNT( f.price_negotiated_g - f.trn_billed_value_g )	C_T_RV_AMT_G
       , COUNT( f.price_negotiated_sg - f.trn_billed_value_sg)C_T_RV_AMT_SG
       , COUNT( f.price_negotiated_a - f.trn_billed_value_a )	C_T_RV_AMT_A
       , COUNT( f.trn_billed_value_f )		C_T_BV_AMT_F
       , COUNT( f.trn_billed_value_g )		C_T_BV_AMT_G
       , COUNT( f.trn_billed_value_sg )	  C_T_BV_AMT_SG
       , COUNT( f.trn_billed_value_a )		C_T_BV_AMT_A
       , COUNT (f.price_negotiated_f) C_BAL_K_AMT_F
       , COUNT (f.price_negotiated_g) C_BAL_K_AMT_G
       , COUNT (f.price_negotiated_sg)C_BAL_K_AMT_SG
       , COUNT (f.price_negotiated_a) C_BAL_K_AMT_A
       , count(*) AS c_total FROM   OKI_SRM_001_MV f
                     ,FII.FII_TIME_DAY FII
WHERE f.effective_term_date = fii.report_date
GROUP BY resource_group_id,
        NVL (resource_id, -1),
	      rollup(SERVICE_ITEM_CATEGORY_ID),
	      class_code,
	      trn_code,
      	authoring_org_id,
        ROLLUP(ent_qtr_id, ent_period_id, week_id, report_date_julian)