MATERIALIZED VIEW: APPS.ISC_DBI_CPM_004_MV

Source

select /* 12.0: bug#4526784 */
		3				 						customer_flag,
		1									 	item_cat_flag,
		ecat.top_node_flag								top_node_flag,
		mv.ent_year_id       								ent_year_id,
		ecat.parent_id									parent_id,
		ecat.imm_child_id								imm_child_id,
		sum(mv.active_g)								active_g,
		sum(mv.active_new_g)								active_new_g,
		sum(mv.active_renew_g)								active_renew_g,
		count(mv.active_g)								c_active_g,
		count(mv.active_new_g)								c_active_new_g,
		count(mv.active_renew_g)							c_active_renew_g,
		sum(mv.active_g1)								active_g1,
		sum(mv.active_new_g1)								active_new_g1,
		sum(mv.active_renew_g1)								active_renew_g1,
		count(mv.active_g1)								c_active_g1,
		count(mv.active_new_g1)								c_active_new_g1,
		count(mv.active_renew_g1)							c_active_renew_g1,
		count(*)		       							c_total
	   FROM	isc_dbi_cpm_001_mv		mv,
		ENI.ENI_DENORM_HIERARCHIES	ecat,
		INV.MTL_DEFAULT_CATEGORY_SETS	mdcs
	  WHERE mv.customer_flag = 3
	    AND mv.item_cat_flag = 1
	    AND	mv.item_category_id = ecat.child_id
	    AND	ecat.dbi_flag = 'Y'
	    AND	ecat.object_type = 'CATEGORY_SET'
	    AND	ecat.object_id = mdcs.category_set_id
	    AND	mdcs.functional_area_id = 11
	GROUP BY
		mv.ent_year_id,
		ecat.top_node_flag,
		ecat.parent_id,
		ecat.imm_child_id