[Home] [Help]
MATERIALIZED VIEW: APPS.ISC_WMS_001_MV
Source
SELECT /* 12.0: bug#4526784 */ f.organization_id INV_ORG_ID, decode(f.subinventory_code, NULL,NULL, -1,-1, (f.subinventory_code||'-'||f.organization_id)) SUBINVENTORY_CODE, nvl(i.inv_category_id,-1) ITEM_CATEGORY_ID, f.inventory_item_id||'-'||f.organization_id ITEM_ID, grouping_id(f.organization_id, decode(f.subinventory_code, NULL,NULL, -1,-1, (f.subinventory_code||'-'||f.organization_id)), nvl(i.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(i.inv_category_id,-1), (f.inventory_item_id||'-'||f.organization_id), f.requested_quantity_uom, 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, c.ent_qtr_id QTR_ID, c.ent_period_id MONTH_ID, c.week_id WEEK_ID, c.report_date_julian DAY_ID, f.requested_quantity_uom UOM, sum(f.shipped_quantity) SHIP_CONFIRM_QTY, count(f.shipped_quantity) SHIP_CONFIRM_QTY_CNT, sum(f.initial_pickup_date-f.pick_released_date) RELEASE_TO_SHIP, count(f.initial_pickup_date-f.pick_released_date) RELEASE_TO_SHIP_CNT, count(*) SHIP_CONFIRM_CNT FROM ISC.ISC_DBI_DEL_DETAILS_F f, FII.FII_TIME_DAY c, ENI.ENI_OLTP_ITEM_STAR i, OPI.OPI_DBI_CONC_PROG_RUN_LOG o WHERE f.time_ip_date_id = c.report_date AND f.pick_released_date IS NOT NULL AND f.organization_id = i.organization_id AND f.inventory_item_id = i.inventory_item_id AND f.released_status = 'C' AND f.wms_enabled_flag = 'Y' AND o.etl_type = 'WMS_PTS_GSD' AND f.pick_released_date >= o.last_run_date GROUP BY f.organization_id, c.ent_qtr_id, rollup(decode(f.subinventory_code,NULL,NULL,-1,-1,(f.subinventory_code||'-'||f.organization_id)), (nvl(i.inv_category_id,-1), (f.inventory_item_id||'-'||f.organization_id), f.requested_quantity_uom)), rollup(c.ent_period_id,c.week_id,c.report_date_julian)