MATERIALIZED VIEW: APPS.ISC_DBI_CFM_007_MV

Source

SELECT	/* 12.0: bug#4526784 */
	 	f.inv_org_id								INV_ORG_ID,
		f.item_id								ITEM_ID,
		f.item_category_id							ITEM_CATEGORY_ID,
		f.master_item_id							MASTER_ITEM_ID,
		f.customer_id								CUSTOMER_ID,
		f.return_reason								RETURN_REASON,
		f.uom									UOM,
		grouping_id(f.inv_org_id,
			f.customer_id,
			f.item_id,
			f.uom,
			f.item_category_id,
			f.master_item_id,
			f.return_reason,
			cal.ent_qtr_id,
			cal.ent_period_id,
			cal.week_id,
			cal.report_date_julian) 					GRP_ID,
		decode(grouping_id(f.item_category_id,
				f.master_item_id,
				f.item_id),
			1,4,3,1,7,3,0)							ITEM_CAT_FLAG,
		grouping_id(f.customer_id)						CUSTOMER_FLAG,
		grouping_id(f.return_reason)						RETURN_REASON_FLAG,
		decode(grouping_id(cal.ent_qtr_id,
				cal.ent_period_id,
				cal.week_id,
				cal.report_date_julian),
			14,cal.report_date_julian,
			13,cal.week_id,
			11,cal.ent_period_id,
			7,cal.ent_qtr_id) 						TIME_ID,
		decode(grouping_id(cal.ent_qtr_id,
				cal.ent_period_id,
				cal.week_id,
				cal.report_date_julian),
			14,1,13,16,11,32,7,64) 						PERIOD_TYPE_ID,
		cal.report_date_julian							DAY_ID,
		cal.week_id								WEEK_ID,
		cal.ent_period_id							ENT_PERIOD_ID,
		cal.ent_qtr_id								ENT_QTR_ID,
		sum(f.returned_amt_g)							RETURNED_AMT_G,
		count(f.returned_amt_g) 						RETURNED_AMT_G_CNT,
		sum(f.returned_amt_g1)							RETURNED_AMT_G1,
		count(f.returned_amt_g1) 						RETURNED_AMT_G1_CNT,
		sum(f.returned_amt_f)							RETURNED_AMT_F,
		count(f.returned_amt_f) 						RETURNED_AMT_F_CNT,
		sum(f.returned_qty) 							RETURNED_QTY,
		count(f.returned_qty) 							RETURNED_QTY_CNT,
		count(*) 								LINES_CNT
	   FROM	isc_dbi_cfm_003_mv 		f,
		FII.FII_TIME_DAY 		cal
	  WHERE	f.time_fulfilled_date_id = cal.report_date
	GROUP BY rollup(f.customer_id),
		rollup(f.return_reason),
		grouping sets((f.inv_org_id,
				f.item_category_id,
				f.master_item_id,
				f.item_id,
				f.uom),
			      (f.master_item_id,
				f.item_category_id),
			      (f.inv_org_id,
				f.item_category_id),
			      f.inv_org_id),
		grouping sets(cal.ent_qtr_id,
				cal.ent_period_id,
				cal.week_id,
				cal.report_date_julian)