MATERIALIZED VIEW: APPS.ENI_DBI_PRC_SUM2_MV

Source

SELECT /* 12.0: bug#4526784 */
	time_id,
    -1 AS inventory_item_id,
    -1 AS product_category_id,
    -1 AS organization_id,
        '-1' AS item_org_id,
	SUM(rev_amount) AS rev_amount,
	COUNT(rev_amount) AS cnt_rev_amount,
	SUM(rev_sec_amount) AS rev_sec_amount,
	COUNT(rev_sec_amount) AS cnt_rev_sec_amount,
	SUM(exp_amount) AS exp_amount,
	COUNT(exp_amount) AS cnt_exp_amount,
	SUM(exp_sec_amount) AS exp_sec_amount,
	COUNT(exp_sec_amount) AS cnt_exp_sec_amount,
	SUM(cogs_amount) AS cogs_amount,
	COUNT(cogs_amount) AS cnt_cogs_amount,
	SUM(cogs_sec_amount) AS cogs_sec_amount,
	COUNT(cogs_sec_amount) AS cnt_cogs_sec_amount,
        COUNT(*),
	1 AS MARKER
FROM
	ENI_DBI_PRC_SUM1_MV odipmv
GROUP BY
	odipmv.time_id
UNION ALL
SELECT
            odipmv.time_id,
            -1 AS inventory_item_id,
            edhi.parent_id product_category_id,
            -1 AS organization_id,
            '-1' AS item_org_id,
 	    SUM(rev_amount) AS rev_amount,
	    COUNT(rev_amount) AS cnt_rev_amount,
	    SUM(rev_sec_amount) AS rev_sec_amount,
	    COUNT(rev_sec_amount) AS cnt_rev_sec_amount,
	    SUM(exp_amount) AS exp_amount,
	    COUNT(exp_amount) AS cnt_exp_amount,
	    SUM(exp_sec_amount) AS exp_sec_amount,
	    COUNT(exp_sec_amount) AS cnt_exp_sec_amount,
	    SUM(cogs_amount) AS cogs_amount,
	    COUNT(cogs_amount) AS cnt_cogs_amount,
	    SUM(cogs_sec_amount) AS cogs_sec_amount,
	    COUNT(cogs_sec_amount) AS cnt_cogs_sec_amount,
            COUNT(*),
            2 AS MARKER
FROM
	ENI_DBI_PRC_SUM1_MV odipmv,
        ENI.ENI_DENORM_HIERARCHIES edhi
where   odipmv.product_category_id = edhi.child_id
GROUP BY
         odipmv.time_id,
         edhi.parent_id