[Home] [Help]
MATERIALIZED VIEW: APPS.OPI_INV_CCA_SUM_MV
Source
Select /* 12.0: bug#4526784 */ f.organization_id organization_id,
f.uom_code uom_code,
decode (f.cycle_count_header_id,
-1, to_char (-1),
f.cycle_count_header_id||'-'||f.organization_id)
cycle_count_header_id,
decode (f.abc_class_id,
to_char (-1), to_char(-1),
f.abc_class_id||'-'||f.cycle_count_header_id||'-'||f.organization_id)
cycle_count_class_id,
decode(f.subinventory_code,
NULL, NULL,
to_char(-1), to_char(-1),
(f.subinventory_code||'-'||f.organization_id))
subinventory_code,
nvl (i.inv_category_id,-1) inv_category_id,
(f.inventory_item_id||'-'||f.organization_id) item_org_id,
grouping_id(f.organization_id,
decode (f.cycle_count_header_id,
-1, to_char(-1),
f.cycle_count_header_id||'-'||f.organization_id),
decode (f.abc_class_id,
to_char(-1), to_char(-1),
f.abc_class_id||'-'||f.cycle_count_header_id||'-'||f.organization_id),
decode (f.subinventory_code,
NULL, NULL,
to_char(-1), to_char(-1),
(f.subinventory_code||'-'||f.organization_id)),
nvl(i.inv_category_id,-1),
(f.inventory_item_id||'-'||f.organization_id))
aggregation_level_flag,
f.source source,
grouping_id (f.organization_id,
decode (f.cycle_count_header_id,
-1, to_char(-1),
f.cycle_count_header_id||'-'||f.organization_id),
decode (f.abc_class_id,
to_char(-1), to_char(-1),
f.abc_class_id||'-'||f.cycle_count_header_id||'-'||f.organization_id),
decode(f.subinventory_code,
NULL, NULL,
to_char(-1), to_char(-1),
(f.subinventory_code||'-'||f.organization_id)),
nvl(i.inv_category_id,-1),
f.uom_code,
f.inventory_item_id||'-'||f.organization_id,
f.source,
c.ent_year_id,
c.ent_qtr_id,
c.ent_period_id,
c.week_id,
c.report_date_julian) grp_id,
decode (grouping_id (c.ent_qtr_id, c.ent_period_id,
c.week_id, c.report_date_julian),
14, c.report_date_julian,
13, c.week_id,
11, c.ent_period_id,
7, c.ent_qtr_id) time_id,
decode (grouping_id (c.ent_qtr_id, c.ent_period_id,
c.week_id, c.report_date_julian),
14, 1,
13, 16,
11, 32,
7, 64) period_type_id,
c.ent_year_id year_id,
c.ent_qtr_id qtr_id,
c.ent_period_id month_id,
c.week_id week_id,
c.report_date_julian day_id,
sum (decode (nvl (hit_or_miss,-99),
-99, 0,
1)) number_of_total_entries,
count (decode (nvl (hit_or_miss, -99),
-99, 0,
1)) count_tot_entries,
sum (decode (f.hit_or_miss,
1, 1,
0)) number_of_hits,
count (decode (f.hit_or_miss,
1, 1,
0)) count_hits,
sum (decode (f.hit_or_miss,
0, 1,
0)) number_of_misses,
count (decode (f.hit_or_miss,
0, 1,
0)) count_misses,
sum (decode (f.exact_match,
1, 1,
0)) number_of_exact_matches,
count (decode (f.exact_match,
1, 1,
0)) count_exact_matches,
sum (decode (f.exact_match,
0, 1,
0)) number_of_adjustments,
count (decode (f.exact_match,
0, 1,
0)) count_adjustments,
sum (f.positive_adjustment_qty) positive_adjustment_qty,
count (f.positive_adjustment_qty) count_pos_adj_qty,
sum (f.negative_adjustment_qty) negative_adjustment_qty,
count (f.negative_adjustment_qty) count_neg_adj_qty,
sum (f.positive_adjustment_val_b) positive_adjustment_val_b,
count (f.positive_adjustment_val_b) count_pos_adj_val_b,
sum (f.positive_adjustment_val_g) positive_adjustment_val_g,
count (f.positive_adjustment_val_g) count_pos_adj_val_g,
sum (f.negative_adjustment_val_b) negative_adjustment_val_b,
count (f.negative_adjustment_val_b) count_neg_adj_val_b,
sum (f.negative_adjustment_val_g) negative_adjustment_val_g,
count (f.negative_adjustment_val_g) count_neg_adj_val_g,
sum (f.positive_adjustment_qty + f.negative_adjustment_qty)
gross_adjustment_qty,
count (f.positive_adjustment_qty + f.negative_adjustment_qty)
count_gr_adj_qty,
sum (f.positive_adjustment_val_b + f.negative_adjustment_val_b)
gross_adjustment_val_b,
count (f.positive_adjustment_val_b + f.negative_adjustment_val_b)
count_gr_adj_val_b,
sum (f.positive_adjustment_val_g + f.negative_adjustment_val_g)
gross_Adjustment_val_g,
count (f.positive_adjustment_val_g + f.negative_adjustment_val_g)
count_gr_adj_val_g,
sum (f.positive_adjustment_qty - f.negative_adjustment_qty)
net_adjustment_qty,
count (f.positive_adjustment_qty - f.negative_adjustment_qty)
count_net_adj_qty,
sum (f.positive_adjustment_val_b - f.negative_adjustment_val_b)
net_adjustment_val_b,
count (f.positive_adjustment_val_b - f.negative_adjustment_val_b)
count_net_adj_val_b,
sum (f.positive_adjustment_val_g - f.negative_adjustment_val_g)
net_adjustment_val_g,
count (f.positive_adjustment_val_g - f.negative_adjustment_val_g)
count_net_adj_val_g,
sum (f.system_inventory_qty) system_inventory_qty,
count (f.system_inventory_qty) count_sys_inv_qty,
sum (system_inventory_val_b) system_inventory_val_b,
count (system_inventory_val_b) count_sys_inv_val_b,
sum (system_inventory_val_g) system_inventory_val_g,
count (system_inventory_val_g) count_sys_inv_val_g,
sum (f.positive_adjustment_val_sg) positive_adjustment_val_sg,
count (f.positive_adjustment_val_sg) count_pos_adj_val_sg,
sum (f.negative_adjustment_val_sg) negative_adjustment_val_sg,
count (f.negative_adjustment_val_sg) count_neg_adj_val_sg,
sum (f.positive_adjustment_val_sg + f.negative_adjustment_val_sg) gross_adjustment_val_sg,
count (f.positive_adjustment_val_sg + f.negative_adjustment_val_sg) count_gr_adj_val_sg,
sum (f.positive_adjustment_val_sg - f.negative_adjustment_val_sg) net_adjustment_val_sg,
count (f.positive_adjustment_val_sg - f.negative_adjustment_val_sg) count_net_adj_val_sg,
sum (f.system_inventory_val_sg) system_inventory_val_sg,
count (f.system_inventory_val_sg) count_sys_inv_val_sg,
count(*) count_total
FROM OPI.OPI_DBI_INV_CCA_F f,
FII.FII_TIME_DAY c,
ENI.ENI_OLTP_ITEM_STAR i
WHERE i.organization_id = f.organization_id
AND i.inventory_item_id = f.inventory_item_id
AND c.report_date = f.approval_date
GROUP BY
f.organization_id,
f.source,
grouping sets((),
(decode(f.subinventory_code,
NULL, NULL,
to_char(-1), to_char(-1),
(f.subinventory_code||'-'||f.organization_id))),
(nvl(i.inv_category_id,-1)),
(decode (f.cycle_count_header_id,
-1, to_char(-1),
f.cycle_count_header_id||'-'||f.organization_id)),
(decode (f.cycle_count_header_id,
-1, to_char(-1),
f.cycle_count_header_id||'-'||f.organization_id),
decode (f.abc_class_id,
to_char(-1), to_char(-1),
f.abc_class_id||'-'||f.cycle_count_header_id||'-'||f.organization_id)),
((decode(f.subinventory_code,
NULL,NULL,
to_char(-1), to_char(-1),
(f.subinventory_code||'-'||f.organization_id))),
(nvl(i.inv_category_id,-1)),
(decode (f.cycle_count_header_id,
-1, to_char(-1),
f.cycle_count_header_id||'-'||f.organization_id)),
(decode (f.abc_class_id,
to_char(-1), to_char(-1),
f.abc_class_id||'-'||f.cycle_count_header_id||'-'||f.organization_id)),
(f.inventory_item_id||'-'||f.organization_id),
(f.uom_code)) ),
grouping sets (c.ent_year_id, c.ent_qtr_id,
c.ent_period_id, c.week_id, c.report_date_julian)