MATERIALIZED VIEW: APPS.ISC_DBI_CFM_004_MV

Source

SELECT /* 12.0: bug#4526784 */
	 	fact.item_inv_org_id								INV_ORG_ID,
		fact.customer_id 								CUSTOMER_ID,
		(item.inventory_item_id||'-'||item.organization_id)				ITEM_ID,
		fact.inv_uom_code 								UOM,
		nvl(item.vbh_category_id, -1) 							ITEM_CATEGORY_ID,
		grouping_id(fact.item_inv_org_id,
			fact.customer_id,
			(item.inventory_item_id||'-'||item.organization_id),
			nvl(item.vbh_category_id, -1),
			fact.inv_uom_code ,
			day.ent_qtr_id,
			day.ent_period_id,
			day.week_id,
			day.report_date_julian) 						GRP_ID,
		grouping_id(nvl(item.vbh_category_id, -1),
			(item.inventory_item_id||'-'||item.organization_id))			ITEM_CAT_FLAG,
		grouping_id(fact.customer_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(fact.booked_amt_g)								BOOKED_AMT_G,
		count(fact.booked_amt_g) 							BOOKED_AMT_G_CNT,
		sum(fact.booked_amt_g1)								BOOKED_AMT_G1,
		count(fact.booked_amt_g1) 							BOOKED_AMT_G1_CNT,
		sum(fact.booked_amt_f1)								BOOKED_AMT_F,
		count(fact.booked_amt_f1) 							BOOKED_AMT_F_CNT,
		sum(fact.booked_qty_inv) 							BOOKED_QTY,
		count(fact.booked_qty_inv) 							BOOKED_QTY_CNT,
		count(*)									CNT
	   FROM	ISC.ISC_BOOK_SUM2_F 		fact,
		ENI.ENI_OLTP_ITEM_STAR 		item,
		FII.FII_TIME_DAY 		day
	  WHERE	fact.time_booked_date_id = day.report_date
	    AND	fact.item_id = item.inventory_item_id
	    AND	fact.inv_org_id = item.organization_id
	    AND	fact.line_category_code <> 'RETURN'
	    AND	fact.item_type_code <> 'SERVICE'
	    AND	fact.order_source_id <> 10
	    AND	fact.order_source_id <> 27
	    AND	fact.ordered_quantity <> 0
	    AND	fact.unit_selling_price <> 0
	    AND fact.charge_periodicity_code is NULL
	GROUP BY fact.item_inv_org_id,
		rollup(fact.customer_id),
		rollup(nvl(item.vbh_category_id,-1),
			((item.inventory_item_id||'-'||item.organization_id), fact.inv_uom_code)),
		grouping sets(ent_qtr_id,
			ent_period_id,
			week_id,
			report_date_julian)