MATERIALIZED VIEW: APPS.ISC_DBI_CFM_009_MV

Source

SELECT	/* 12.0: bug#4526784 */
	 	mv.inv_org_id						INV_ORG_ID,
		ecat.top_node_flag					TOP_NODE_FLAG,
		ecat.parent_id						PARENT_ID,
		ecat.imm_child_id					IMM_CHILD_ID,
		mv.time_id						TIME_ID,
		mv.period_type_id					PERIOD_TYPE_ID,
		grouping_id(mv.inv_org_id)				INV_ORG_FLAG,
		grouping_id(mv.inv_org_id,
			ecat.top_node_flag,
			ecat.parent_id,
			ecat.imm_child_id,
			mv.time_id,
			mv.period_type_id) 				GRP_ID,
		sum(mv.booked_amt_g)					BOOKED_AMT_G,
		count(mv.booked_amt_g) 					BOOKED_AMT_G_CNT,
		sum(mv.booked_amt_g1)					BOOKED_AMT_G1,
		count(mv.booked_amt_g1) 				BOOKED_AMT_G1_CNT,
		sum(mv.booked_amt_f)					BOOKED_AMT_F,
		count(mv.booked_amt_f) 					BOOKED_AMT_F_CNT,
		sum(mv.booked_qty)					BOOKED_QTY,
		count(mv.booked_qty)					BOOKED_QTY_CNT,
		sum(mv.booked_amt2_g)					BOOKED_AMT2_G,
		count(mv.booked_amt2_g) 				BOOKED_AMT2_G_CNT,
		sum(mv.booked_amt2_g1)					BOOKED_AMT2_G1,
		count(mv.booked_amt2_g1) 				BOOKED_AMT2_G1_CNT,
		sum(mv.booked_amt2_f)					BOOKED_AMT2_F,
		count(mv.booked_amt2_f) 				BOOKED_AMT2_F_CNT,
		sum(mv.booked_qty2)					BOOKED_QTY2,
		count(mv.booked_qty2)					BOOKED_QTY2_CNT,
		sum(scheduled_days)					SCHEDULED_DAYS,
		count(scheduled_days)					SCHEDULED_DAYS_CNT,
		sum(requested_days)					REQUESTED_DAYS,
		count(requested_days)					REQUESTED_DAYS_CNT,
		sum(mv.booked_line_cnt)					BOOKED_LINE_CNT,
		count(mv.booked_line_cnt)				BOOKED_LINE_CNT_C,
		count(*)						CNT
	   FROM	isc_dbi_cfm_000_mv 			mv,
		ENI.ENI_DENORM_HIERARCHIES 		ecat,
		INV.MTL_DEFAULT_CATEGORY_SETS 		mtl
	  WHERE	mv.item_category_id = ecat.child_id
	    AND	ecat.object_type = 'CATEGORY_SET'
	    AND	ecat.object_id = mtl.category_set_id
	    AND	ecat.dbi_flag = 'Y'
	    AND	mtl.functional_area_id = 11
	    AND	mv.item_cat_flag = 1
	    AND	mv.customer_flag = 1
	  GROUP BY rollup(mv.inv_org_id),
		ecat.top_node_flag,
		ecat.parent_id,
		ecat.imm_child_id,
		mv.time_id,
		mv.period_type_id