[Home] [Help]
MATERIALIZED VIEW: APPS.OPI_WMS_001_MV
Source
Select /* 12.0: bug#4526784 */ grouping_id(f.organization_id,
decode(f.subinventory_code,
NULL,NULL,
-1,-1,
(f.subinventory_code||'-'||f.organization_id)),
nvl(item.inv_category_id,-1),
(f.inventory_item_id||'-'||f.organization_id)) AGG_LEVEL,
grouping_id(f.organization_id,
decode(f.subinventory_code,
NULL,NULL,
-1,-1,
(f.subinventory_code||'-'||f.organization_id)),
nvl(item.inv_category_id,-1),
(f.inventory_item_id||'-'||f.organization_id),
f.putaway_uom_code,
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), 0,c.report_date_julian,1,c.week_id,3,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),
0, 1,
1, 16,
3, 32,
7, 64) PERIOD_TYPE_ID,
f.organization_id,
decode(f.subinventory_code, null, null, -1, -1, f.subinventory_code || '-' || f.organization_id) subinventory_code,
nvl(item.inv_category_id, -1) inv_category_id,
f.inventory_item_id || '-' || f.organization_id item_org_id,
f.putaway_uom_code uom_code,
c.ent_qtr_id QTR_ID,
c.ent_period_id MONTH_ID,
c.week_id WEEK_ID,
c.report_date_julian DAY_ID,
sum(f.putaway_completion_date - f.rcv_transaction_date) rtp_cycle_time,
count(f.putaway_completion_date - f.rcv_transaction_date) c_rtp_cycle_time,
sum(f.putaway_quantity * f.putaway_uom_conv_rate) putaway_quantity,
count(f.putaway_quantity * f.putaway_uom_conv_rate) c_putaway_quantity,
count(*)
FROM OPI.OPI_DBI_WMS_RTP_F f,
FII.FII_TIME_DAY c,
ENI.ENI_OLTP_ITEM_STAR item
WHERE f.organization_id = item.organization_id
AND f.inventory_item_id = item.inventory_item_id
AND trunc(f.putaway_completion_date) = c.report_date
GROUP BY f.organization_id,
rollup(decode(f.subinventory_code,
NULL,NULL,
-1,-1,
(f.subinventory_code||'-'||f.organization_id)), (nvl(item.inv_category_id,-1), (f.inventory_item_id || '-' || f.organization_id), f.putaway_uom_code)),
c.ent_qtr_id,rollup(c.ent_period_id,c.week_id,c.report_date_julian)