MATERIALIZED VIEW: APPS.ISC_DBI_CPM_002_MV

Source

SELECT /* 12.0: bug#4526784 */
		grouping_id(cc.class_code, fact.customer_id)					customer_flag,
		grouping_id(nvl(item.vbh_category_id, -1), nvl(item.master_id, item.id)) 	item_cat_flag,
		grouping_id(day.report_date_julian, day.week_id, day.ent_period_id,
			    day.ent_qtr_id, fact.customer_id, cc.class_code,
                            nvl(item.vbh_category_id,-1),
			    nvl(item.master_id, item.id))					grp_id,
		decode(grouping_id(day.ent_qtr_id, day.ent_period_id, day.week_id, day.report_date_julian),
			14, day.report_date_julian,
			13, day.week_id,
			11, day.ent_period_id,
			7,  day.ent_qtr_id) 							time_id,
		fact.customer_id 								customer_id,
		cc.class_code									class_code,
		nvl(item.master_id, item.id)  							product_id,
		nvl(item.vbh_category_id , -1)     						item_category_id,
		day.report_date_julian								report_date_julian,
		day.week_id									week_id,
		day.ent_period_id								ent_period_id,
		day.ent_qtr_id									ent_qtr_id,
		sum(decode(fact.line_category_code, 'ORDER', fact.booked_amt_g, 0))		booked_amt_g,
		count(decode(fact.line_category_code, 'ORDER', fact.booked_amt_g, 0))		c_booked_amt_g,
		sum(decode(fact.line_category_code, 'ORDER', fact.booked_amt_g1, 0))		booked_amt_g1,
		count(decode(fact.line_category_code, 'ORDER', fact.booked_amt_g1, 0))	c_booked_amt_g1,
		sum(decode(fact.line_category_code, 'RETURN', fact.booked_amt_g, 0))		returned_amt_g,
		count(decode(fact.line_category_code, 'RETURN', fact.booked_amt_g, 0))	c_returned_amt_g,
		sum(decode(fact.line_category_code, 'RETURN', fact.booked_amt_g1, 0))		returned_amt_g1,
		count(decode(fact.line_category_code, 'RETURN', fact.booked_amt_g1, 0))	c_returned_amt_g1,
		count(*)					 				c_total
	   FROM	ISC.ISC_BOOK_SUM2_F 		fact,
		FII.FII_TIME_DAY 		day,
		ENI.ENI_OLTP_ITEM_STAR 		item,
		FII.FII_PARTY_MKT_CLASS		cc
	  WHERE fact.order_source_id	<> 10
	    AND	fact.order_source_id    <> 27
	    AND	fact.ordered_quantity	<> 0
	    AND fact.item_type_code	<> 'SERVICE'
	    AND fact.inventory_item_id 	= item.inventory_item_id
	    AND fact.item_inv_org_id	= item.organization_id
	    AND fact.time_booked_date_id = day.report_date
            AND fact.customer_id = cc.party_id
	    AND fact.charge_periodicity_code is NULL
	GROUP BY
		rollup(cc.class_code, fact.customer_id),
		rollup(nvl(item.vbh_category_id,-1), nvl(item.master_id, item.id)),
		grouping sets(day.ent_qtr_id, day.ent_period_id, day.week_id, day.report_date_julian)