MATERIALIZED VIEW: APPS.ENI_DBI_GL_BASE_SUM_C_MV

Source

SELECT  /* 12.0: bug#4526784 */
               -- top level nodes, and the virtual root node
		-2 AS parent_prod_cat_id,
		edhi.parent_id AS child_prod_cat_id,
		edgb.time_id,
		SUM(edgb.prim_actual_g) AS prim_actual_g,
		COUNT(edgb.prim_actual_g) AS cnt_prim_actual_g,
		SUM(edgb.sec_actual_g) AS sec_actual_g,
		COUNT(edgb.sec_actual_g) AS cnt_sec_actual_g,
		COUNT(*),
		1 AS MARKER
	FROM
		eni_dbi_gl_base_sum_mv edgb,
		ENI.ENI_DENORM_HIERARCHIES edhi
	WHERE
		edgb.product_category_id = edhi.imm_child_id
		AND edhi.parent_id = edhi.child_id
		AND gid = 0
		AND marker = 1
		AND edhi.top_node_flag = 'Y'
	GROUP BY
		edhi.parent_id,
		edgb.time_id
	UNION ALL
	SELECT -- intermediate nodes, with their direct children
		edhi.parent_id AS parent_prod_cat_id,
		edhi.imm_child_id AS child_prod_cat_id,
		edgb.time_id,
		SUM(edgb.prim_actual_g) AS prim_actual_g,
		COUNT(edgb.prim_actual_g) AS cnt_prim_actual_g,
		SUM(edgb.sec_actual_g) AS sec_actual_g,
		COUNT(edgb.sec_actual_g) AS cnt_sec_actual_g,
		COUNT(*),
		2 AS MARKER
	FROM
		eni_dbi_gl_base_sum_mv edgb,
		ENI.ENI_DENORM_HIERARCHIES edhi
	WHERE
		edgb.product_category_id = edhi.imm_child_id
		AND edhi.parent_id <> edhi.child_id
		AND edhi.imm_child_id = edhi.child_id
		AND gid = 0
		AND marker = 1
	GROUP BY
		edhi.parent_id,
		edhi.imm_child_id,
		edgb.time_id