MATERIALIZED VIEW: APPS.ISC_DBI_PM_0002_MV

Source

SELECT	/* 12.0: bug#4526784 */
	 	fact.snapshot_id					SNAPSHOT_ID,
		fact.organization_id					ORGANIZATION_ID,
		res.id	 						RESOURCE_ID,
		res.department_fk  					DEPARTMENT_ID,
		res.resource_group_fk  					RESOURCE_GROUP_ID,
		grouping_id(fact.snapshot_id,
			fact.organization_id,
			res.resource_group_fk,
			res.department_fk,
			res.id,
			fact.start_date,
			fact.period_type_id) 				GRP_ID,
		decode(grouping_id(res.resource_group_fk,
				res.department_fk,
				res.id),
			0,0,5,1,3,2,7,3) 				RES_GP_FLAG,
		fact.start_date 					START_DATE,
		fact.period_type_id 					PERIOD_TYPE_ID,
		sum(fact.required_hours) 				REQUIRED_HOURS,
		count(fact.required_hours) 				REQUIRED_HOURS_CNT,
		sum(fact.available_hours) 				AVAILABLE_HOURS,
		count(fact.available_hours) 				AVAILABLE_HOURS_CNT,
		count(*) 						CNT
	   FROM	ISC.ISC_DBI_RES_SUM_SNAPSHOTS 		fact,
		eni_res_5_mv 				res
	  WHERE	fact.resource_id = res.resource_id
	    AND	fact.organization_id = res.organization_id
	    AND	((fact.organization_type=1 and res.department_id = fact.department_id) or fact.organization_type = 2)
	GROUP BY fact.snapshot_id,
		fact.organization_id,
		grouping sets((),
			(res.resource_group_fk),
			(res.department_fk),
			(res.resource_group_fk,
			 res.department_fk,
			 res.id)),
		fact.start_date,
		fact.period_type_id