MATERIALIZED VIEW: APPS.ENI_DBI_PRC_UNION_MV

Source

SELECT /* 12.0: bug#4526784 */
	time_id,
	inventory_item_id,
	product_category_id,
	master_organization_id AS organization_id,
        inventory_item_id ||'-'|| master_organization_id AS item_org_id,
	SUM(rev_prim_actual_g) AS rev_prim_actual_g,
	COUNT(rev_prim_actual_g) AS cnt_rev_prim_actual_g,
	SUM(rev_sec_actual_g) AS rev_sec_actual_g,
	COUNT(rev_sec_actual_g) AS cnt_rev_sec_actual_g,
	SUM(0) AS prim_actual_g,
	COUNT(0) AS cnt_prim_actual_g,
	SUM(0) AS sec_actual_g,
	COUNT(0) AS cnt_sec_actual_g,
	SUM(0) AS cogs_value_g,
	COUNT(0) AS cnt_cogs_value_g,
	SUM(0) AS cogs_value_sg,
	COUNT(0) AS cnt_cogs_value_sg,
	SUM(0) AS inv_total_value_g,
	COUNT(0) AS cnt_total_value_g,
	COUNT(*),
	1 AS MARKER
FROM
	eni_dbi_ar_base_mv ears,
	eni_dbi_org_mv edom
WHERE
	ears.organization_id = edom.organization_id
GROUP BY
	ears.time_id,
	ears.inventory_item_id,
	ears.product_category_id,
	edom.master_organization_id
UNION ALL
SELECT
	time_id,
	-1 AS inventory_item_id, -- every category will group into itself since every item id is the same
	product_category_id,
	-1 AS organization_id,
        '-1' AS item_org_id,
	SUM(0) AS rev_prim_actual_g,
	COUNT(0) AS cnt_rev_prim_actual_g,
	SUM(0) AS rev_sec_actual_g,
	COUNT(0) AS cnt_sec_prim_actual_g,
	SUM(prim_actual_g)  AS prim_actual_g,
	COUNT(prim_actual_g) AS cnt_prim_actual_g,
	SUM(sec_actual_g)  AS sec_actual_g,
	COUNT(sec_actual_g) AS cnt_sec_actual_g,
	SUM(0) AS cogs_value_g,
	COUNT(0) AS cnt_cogs_value_g,
	SUM(0) AS cogs_value_sg,
	COUNT(0) AS cnt_cogs_value_sg,
	SUM(0) AS inv_total_value_g,
	COUNT(0) AS cnt_total_value_g,
	COUNT(*),
	2 AS MARKER
FROM
	eni_dbi_gl_base_mv egba
GROUP BY
	egba.time_id,
	egba.product_category_id
UNION ALL
SELECT
	 edcbm.time_id,
	 edcbm.inventory_item_id,
	eois.vbh_category_id AS product_category_id,
	edom.master_organization_id AS organization_id,
        edcbm.inventory_item_id ||'-'|| edom.master_organization_id AS item_org_id,
	SUM(0) AS rev_prim_actual_g,
	COUNT(0) AS cnt_rev_prim_actual_g,
	SUM(0) AS rev_sec_actual_g,
	COUNT(0) AS cnt_rev_sec_actual_g,
	SUM(0) AS prim_actual_g,
	COUNT(0) AS cnt_prim_actual_g,
	SUM(0) AS sec_actual_g,
	COUNT(0) AS cnt_sec_actual_g,
	SUM(edcbm.cogs_val_g) AS cogs_value_g,
	COUNT(edcbm.cogs_val_g) AS cnt_cogs_value_g,
	SUM(edcbm.cogs_val_sg) AS cogs_value_sg,
	COUNT(edcbm.cogs_val_sg) AS cnt_cogs_value_sg,
	SUM(0) AS inv_total_value_g,
	COUNT(0) AS cnt_total_value_g,
	COUNT(*),
	3 AS MARKER
FROM
	eni_dbi_cogs_base_mv edcbm,
	ENI.ENI_OLTP_ITEM_STAR eois,
	eni_dbi_org_mv edom
WHERE
	eois.inventory_item_id = edcbm.inventory_item_id
	and eois.organization_id =  edcbm.organization_id
	and  edcbm.organization_id = edom.organization_id
GROUP BY
	 edcbm.time_id,
	 edcbm.inventory_item_id,
	eois.vbh_category_id,
	edom.master_organization_id