MATERIALIZED VIEW: APPS.ISC_DBI_CFM_011_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,
		mv.return_flag						RETURN_FLAG,
		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,
			mv.return_flag) 				GRP_ID,
		sum(mv.fulfilled_amt_g)					FULFILLED_AMT_G,
		count(mv.fulfilled_amt_g)				FULFILLED_AMT_G_CNT,
		sum(mv.fulfilled_amt_g1)				FULFILLED_AMT_G1,
		count(mv.fulfilled_amt_g1)				FULFILLED_AMT_G1_CNT,
		sum(mv.fulfilled_amt_f)					FULFILLED_AMT_F,
		count(mv.fulfilled_amt_f)				FULFILLED_AMT_F_CNT,
		sum(mv.fulfilled_amt2_g)				FULFILLED_AMT2_G,
		count(mv.fulfilled_amt2_g)				FULFILLED_AMT2_G_CNT,
		sum(mv.fulfilled_amt2_g1)				FULFILLED_AMT2_G1,
		count(mv.fulfilled_amt2_g1)				FULFILLED_AMT2_G1_CNT,
		sum(mv.fulfilled_amt2_f)				FULFILLED_AMT2_F,
		count(mv.fulfilled_amt2_f)				FULFILLED_AMT2_F_CNT,
		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.returned_amt_g)					RETURNED_AMT_G,
		count(mv.returned_amt_g) 				RETURNED_AMT_G_CNT,
		sum(mv.returned_amt_g1)					RETURNED_AMT_G1,
		count(mv.returned_amt_g1) 				RETURNED_AMT_G1_CNT,
		sum(mv.returned_amt_f)					RETURNED_AMT_F,
		count(mv.returned_amt_f) 				RETURNED_AMT_F_CNT,
		sum(mv.fulfilled_qty) 					FULFILLED_QTY,
		count(mv.fulfilled_qty) 				FULFILLED_QTY_CNT,
		sum(mv.returned_qty) 					RETURNED_QTY,
		count(mv.returned_qty) 					RETURNED_QTY_CNT,
		sum(mv.book_to_fulfill_days)				BOOK_TO_FULFILL_DAYS,
		count(mv.book_to_fulfill_days)				BOOK_TO_FULFILL_DAYS_C,
		sum(mv.book_to_fulfill_cnt)				BOOK_TO_FULFILL_CNT,
		count(mv.book_to_fulfill_cnt)				BOOK_TO_FULFILL_CNT_C,
		sum(mv.lines_cnt) 					LINES_CNT,
		count(mv.lines_cnt) 					LINES_CNT_C,
		count(*)						CNT
	   FROM	isc_dbi_cfm_002_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.return_flag,
		mv.time_id,
		mv.period_type_id