[Home] [Help]
MATERIALIZED VIEW: APPS.ISC_DBI_PM_0001_MV
Source
SELECT /* 12.0: bug#4526784 */
fact.snapshot_id SNAPSHOT_ID,
fact.organization_id ORGANIZATION_ID,
(star.inventory_item_id||'-'||star.organization_id) ITEM_ID,
nvl(star.inv_category_id, -1) INV_CATEGORY_ID,
nvl(star.vbh_category_id, -1) VBH_CATEGORY_ID,
grouping_id(fact.snapshot_id,
fact.organization_id,
nvl(star.vbh_category_id, -1),
nvl(star.inv_category_id, -1),
(star.inventory_item_id||'-'||star.organization_id),
fact.uom_code,
fact.start_date,
fact.period_type_id) GRP_ID,
decode(grouping_id(nvl(star.vbh_category_id, -1),
nvl(star.inv_category_id, -1),
(star.inventory_item_id||'-'||star.organization_id)),
0,0,5,1,3,2,7,3) ITEM_CAT_FLAG,
fact.uom_code UOM_CODE,
fact.start_date START_DATE,
fact.period_type_id PERIOD_TYPE_ID,
sum(fact.carrying_cost) CARRYING_COST,
count(fact.carrying_cost) CARRYING_COST_CNT,
sum(fact.carrying_cost_g) CARRYING_COST_G,
count(fact.carrying_cost_g) CARRYING_COST_G_CNT,
sum(fact.carrying_cost_g1) CARRYING_COST_G1,
count(fact.carrying_cost_g1) CARRYING_COST_G1_CNT,
sum(fact.cost_shortfall) COST_SHORTFALL,
count(fact.cost_shortfall) COST_SH_CNT,
sum(fact.cost_shortfall_g) COST_SHORTFALL_G,
count(fact.cost_shortfall_g) COST_SH_G_CNT,
sum(fact.cost_shortfall_g1) COST_SHORTFALL_G1,
count(fact.cost_shortfall_g1) COST_SH_G1_CNT,
sum(fact.inventory_cost) INVENTORY_COST,
count(fact.inventory_cost) INVENTORY_COST_CNT,
sum(fact.inventory_cost_g) INVENTORY_COST_G,
count(fact.inventory_cost_g) INVENTORY_COST_G_CNT,
sum(fact.inventory_cost_g1) INVENTORY_COST_G1,
count(fact.inventory_cost_g1) INVENTORY_COST_G1_CNT,
sum(fact.mds_cost) MDS_COST,
count(fact.mds_cost) MDS_COST_CNT,
sum(fact.mds_cost_g) MDS_COST_G,
count(fact.mds_cost_g) MDS_COST_G_CNT,
sum(fact.mds_cost_g1) MDS_COST_G1,
count(fact.mds_cost_g1) MDS_COST_G1_CNT,
sum(fact.mds_price) MDS_PRICE,
count(fact.mds_price) MDS_PRICE_CNT,
sum(fact.mds_price_g) MDS_PRICE_G,
count(fact.mds_price_g) MDS_PRICE_G_CNT,
sum(fact.mds_price_g1) MDS_PRICE_G1,
count(fact.mds_price_g1) MDS_PRICE_G1_CNT,
sum(fact.mds_quantity) MDS_QUANTITY,
count(fact.mds_quantity) MDS_QUANTITY_CNT,
sum(fact.production_cost) PRODUCTION_COST,
count(fact.production_cost) PRODUCTION_COST_CNT,
sum(fact.production_cost_g) PRO_COST_G,
count(fact.production_cost_g) PRO_COST_G_CNT,
sum(fact.production_cost_g1) PRO_COST_G1,
count(fact.production_cost_g1) PRO_COST_G1_CNT,
sum(fact.purchasing_cost) PURCHASING_COST,
count(fact.purchasing_cost) PURCHASING_COST_CNT,
sum(fact.purchasing_cost_g) PUR_COST_G,
count(fact.purchasing_cost_g) PUR_COST_G_CNT,
sum(fact.purchasing_cost_g1) PUR_COST_G1,
count(fact.purchasing_cost_g1) PUR_COST_G1_CNT,
sum(fact.late_lines) LATE_LINES,
count(fact.late_lines) LATE_LINES_CNT,
sum(fact.rev_shortfall) REV_SHORTFALL,
count(fact.rev_shortfall) REV_SH_CNT,
sum(fact.rev_shortfall_g) REV_SHORTFALL_G,
count(fact.rev_shortfall_g) REV_SH_G_CNT,
sum(fact.rev_shortfall_g1) REV_SHORTFALL_G1,
count(fact.rev_shortfall_g1) REV_SH_G1_CNT,
sum(fact.total_lines) TOTAL_LINES,
count(fact.total_lines) TOTAL_LINES_CNT,
sum(fact.union1_flag) UNION1_FLAG,
count(fact.union1_flag) UNION1_FLAG_CNT,
sum(fact.union2_flag) UNION2_FLAG,
count(fact.union2_flag) UNION2_FLAG_CNT,
count(*) CNT
FROM ISC.ISC_DBI_INV_DETAIL_SNAPSHOTS fact,
ENI.ENI_OLTP_ITEM_STAR star
WHERE fact.sr_inventory_item_id = star.inventory_item_id
AND fact.organization_id = star.organization_id
GROUP BY fact.snapshot_id,
fact.organization_id,
grouping sets((),
nvl(star.vbh_category_id, -1),
nvl(star.inv_category_id, -1),
(nvl(star.vbh_category_id, -1),
nvl(star.inv_category_id, -1),
(star.inventory_item_id||'-'||star.organization_id),
fact.uom_code)),
fact.start_date,
fact.period_type_id