MATERIALIZED VIEW: APPS.ISC_DBI_FM_0001_MV

Source

SELECT /* 12.0: bug#4526784 */
	 	fact.item_inv_org_id 								INV_ORG_ID,
		(item.inventory_item_id ||'-'|| item.organization_id) 			ITEM_ID,
		fact.ship_to_party_id 								CUSTOMER_ID,
		nvl(item.inv_category_id, -1) 							ITEM_CATEGORY_ID,
		grouping_id(fact.item_inv_org_id,
			fact.ship_to_party_id,
			(item.inventory_item_id||'-'||item.organization_id),
			nvl(item.inv_category_id, -1),
			day.ent_qtr_id,
			day.ent_period_id,
			day.week_id,
			day.report_date_julian) 						GRP_ID,
		grouping_id(nvl(item.inv_category_id, -1),
			(item.inventory_item_id||'-'||item.organization_id))			ITEM_CAT_FLAG,
		grouping_id(fact.ship_to_party_id)						CUSTOMER_FLAG,
		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,
		decode(grouping_id(day.ent_qtr_id,
				day.ent_period_id,
				day.week_id,
				day.report_date_julian),
			14,1,13,16,11,32,7,64) 							PERIOD_TYPE_ID,
		day.report_date_julian								DAY_ID,
		day.week_id									WEEK_ID,
		day.ent_period_id								ENT_PERIOD_ID,
		day.ent_qtr_id									ENT_QTR_ID,
		sum(decode(fact.item_type_code,'SERVICE',0,1))				SCHEDULE_LINE_CNT,
		count(decode(fact.item_type_code,'SERVICE',0,1))				SCHEDULE_L_C,
		count(*)									CNT
	   FROM	ISC.ISC_BOOK_SUM2_F 		fact,
		ENI.ENI_OLTP_ITEM_STAR 		item,
		FII.FII_TIME_DAY 		day
	  WHERE	fact.time_schedule_date_id = day.report_date
	    AND	fact.inventory_item_id = item.inventory_item_id
	    AND	fact.item_inv_org_id = item.organization_id
	    AND	fact.line_category_code <> 'RETURN'
	    AND	fact.item_type_code <> 'SERVICE'
	    AND	fact.shippable_flag = 'Y'
	    AND	fact.ordered_quantity <> 0
	    AND	fact.order_source_id <> 27
	    AND fact.charge_periodicity_code is NULL
	GROUP BY fact.item_inv_org_id,
		rollup(fact.ship_to_party_id),
		rollup(nvl(item.inv_category_id,-1),
			(item.inventory_item_id||'-'||item.organization_id)),
		grouping sets(day.ent_qtr_id,
			day.ent_period_id,
			day.week_id,
			day.report_date_julian)