SELECT /* 12.0: bug#4526784 */
edibmv.time_id,
-1 AS inventory_item_id,
-1 AS product_category_id,
-1 AS organization_id,
null AS item_org_id,
SUM(edibmv.onhand_value_g) AS onhand_value_g,
COUNT(edibmv.onhand_value_g) AS cnt_onhand_value_g,
SUM(edibmv.intransit_value_g) AS intransit_value_g,
COUNT(edibmv.intransit_value_g) AS cnt_intransit_value_g,
SUM(edibmv.wip_value_g) AS wip_value_g,
COUNT(edibmv.wip_value_g) AS cnt_wip_value_g,
SUM(edibmv.inv_total_value_g) AS inv_total_value_g,
COUNT(edibmv.inv_total_value_g) AS cnt_inv_total_value_g,
-- begin secondary currency
SUM(edibmv.onhand_value_sg) AS onhand_value_sg,
COUNT(edibmv.onhand_value_sg) AS cnt_onhand_value_sg,
SUM(edibmv.intransit_value_sg) AS intransit_value_sg,
COUNT(edibmv.intransit_value_sg) AS cnt_intransit_value_sg,
SUM(edibmv.wip_value_sg) AS wip_value_sg,
COUNT(edibmv.wip_value_sg) AS cnt_wip_value_sg,
SUM(edibmv.inv_total_value_sg) AS inv_total_value_sg,
COUNT(edibmv.inv_total_value_sg) AS cnt_inv_total_value_sg,
-- end secondary currency
COUNT(*) AS count_total,
GROUPING_ID
(
edibmv.time_id
) AS gid,
1 AS MARKER
FROM
ENI_DBI_INV_BASE_MV edibmv
GROUP BY
grouping sets
(
( edibmv.time_id
)
)
UNION ALL
SELECT
edibmv.time_id,
null AS inventory_item_id,
edhi.parent_id product_category_id,
-- need to have org in select list if in grouping id
edibmv.organization_id AS organization_id,
null AS item_org_id,
SUM(edibmv.onhand_value_g) AS onhand_value_g,
COUNT(edibmv.onhand_value_g) AS cnt_onhand_value_g,
SUM(edibmv.intransit_value_g) AS intransit_value_g,
COUNT(edibmv.intransit_value_g) AS cnt_intransit_value_g,
SUM(edibmv.wip_value_g) AS wip_value_g,
COUNT(edibmv.wip_value_g) AS cnt_wip_value_g,
SUM(edibmv.inv_total_value_g) AS inv_total_value_g,
COUNT(edibmv.inv_total_value_g) AS cnt_inv_total_value_g,
-- begin secondary currency
SUM(edibmv.onhand_value_sg) AS onhand_value_sg,
COUNT(edibmv.onhand_value_sg) AS cnt_onhand_value_sg,
SUM(edibmv.intransit_value_sg) AS intransit_value_sg,
COUNT(edibmv.intransit_value_sg) AS cnt_intransit_value_sg,
SUM(edibmv.wip_value_sg) AS wip_value_sg,
COUNT(edibmv.wip_value_sg) AS cnt_wip_value_sg,
SUM(edibmv.inv_total_value_sg) AS inv_total_value_sg,
COUNT(edibmv.inv_total_value_sg) AS cnt_inv_total_value_sg,
-- end secondary currency
COUNT(*) AS count_total,
GROUPING_ID
(
edibmv.time_id,
edhi.parent_id
, edibmv.organization_id
) AS gid,
2 AS MARKER
FROM
ENI_DBI_INV_BASE_MV edibmv,
ENI.ENI_DENORM_HIERARCHIES edhi
where edibmv.item_category_id = edhi.child_id
GROUP BY
rollup(edibmv.organization_id),
grouping sets
(
(
edibmv.time_id,
edhi.parent_id
)
)
UNION ALL
SELECT
edibmv.time_id,
edibmv.inventory_item_id AS inventory_item_id,
edibmv.item_category_id AS product_category_id,
edom.master_organization_id AS organization_id,
edibmv.item_master_org_id AS item_org_id,
SUM(edibmv.onhand_value_g) AS onhand_value_g,
COUNT(edibmv.onhand_value_g) AS cnt_onhand_value_g,
SUM(edibmv.intransit_value_g) AS intransit_value_g,
COUNT(edibmv.intransit_value_g) AS cnt_intransit_value_g,
SUM(edibmv.wip_value_g) AS wip_value_g,
COUNT(edibmv.wip_value_g) AS cnt_wip_value_g,
SUM(edibmv.inv_total_value_g) AS inv_total_value_g,
COUNT(edibmv.inv_total_value_g) AS cnt_inv_total_value_g,
-- begin secondary currency
SUM(edibmv.onhand_value_sg) AS onhand_value_sg,
COUNT(edibmv.onhand_value_sg) AS cnt_onhand_value_sg,
SUM(edibmv.intransit_value_sg) AS intransit_value_sg,
COUNT(edibmv.intransit_value_sg) AS cnt_intransit_value_sg,
SUM(edibmv.wip_value_sg) AS wip_value_sg,
COUNT(edibmv.wip_value_sg) AS cnt_wip_value_sg,
SUM(edibmv.inv_total_value_sg) AS inv_total_value_sg,
COUNT(edibmv.inv_total_value_sg) AS cnt_inv_total_value_sg,
-- end secondary currency
COUNT(*) AS count_total,
GROUPING_ID
(
edibmv.time_id,
edibmv.inventory_item_id,
edibmv.item_category_id,
edom.master_organization_id,
edibmv.item_master_org_id
) AS gid,
3 AS MARKER
FROM
ENI_DBI_INV_BASE_MV edibmv,
ENI_DBI_ORG_MV edom
WHERE
edibmv.organization_id = edom.organization_id
GROUP BY
grouping sets
(
(
edibmv.time_id,
edibmv.inventory_item_id ,
edibmv.item_category_id,
edom.master_organization_id,
edibmv.item_master_org_id
)
)
[Home] [Help]