eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.ISC_DBI_FM_0000_MV

Source


SELECT /* 12.0: bug#4526784 */ fact.item_inv_org_id	INV_ORG_ID, fact.ship_to_party_id 					CUSTOMER_ID, (item.inventory_item_id||'-'||item.organization_id)	ITEM_ID, nvl(item.inv_category_id, -1) 				ITEM_CATEGORY_ID, item.vbh_category_id 					PROD_CATEGORY_ID, decode(grouping_id(nvl(item.inv_category_id, -1),(item.inventory_item_id||'-'||item.organization_id), fact.ship_to_party_id),
		0,0,1,1,6,2,7,3,2,4,3,5)			AGG_LEVEL,  grouping_id(fact.item_inv_org_id,fact.ship_to_party_id, (item.inventory_item_id||'-'||item.organization_id),fact.inv_uom_code,item.vbh_category_id, nvl(item.inv_category_id, -1), cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian) GRP_ID, grouping_id(nvl(item.inv_category_id, -1),(item.inventory_item_id||'-'||item.organization_id))			ITEM_CAT_FLAG, grouping_id(fact.ship_to_party_id)			CUSTOMER_FLAG, decode(grouping_id(cal.ent_qtr_id, cal.ent_period_id, cal.week_id, cal.report_date_julian),        14,cal.report_date_julian,13,cal.week_id, 11,cal.ent_period_id,7,cal.ent_qtr_id) TIME_ID, decode(grouping_id(cal.ent_qtr_id,cal.ent_period_id,cal.week_id,cal.report_date_julian),	14,1,13,16,11,32,7,64) 				PERIOD_TYPE_ID, cal.report_date_julian					DAY_ID,  cal.week_id						WEEK_ID, cal.ent_period_id					ENT_PERIOD_ID, cal.ent_qtr_id						ENT_QTR_ID, sum(fact.count_ship_line)				SHIPPED_LINE_CNT, count(fact.count_ship_line)				SHIPPED_L_C, sum(CASE WHEN fact.time_shipped_date_id > fact.time_schedule_date_id THEN fact.count_ship_line ELSE 0 END) LATE_LINE_CNT, count(CASE WHEN fact.time_shipped_date_id > fact.time_schedule_date_id THEN fact.count_ship_line ELSE 0 END) LATE_L_C, sum(CASE WHEN fact.time_shipped_date_id > fact.time_promise_date_id THEN fact.count_ship_line ELSE 0 END) LATE_LINE_PROMISE_CNT, count(CASE WHEN fact.time_shipped_date_id > fact.time_promise_date_id THEN fact.count_ship_line ELSE 0 END) LATE_L_PROMISE_C, sum(CASE WHEN fact.time_shipped_date_id < fact.time_schedule_date_id THEN fact.count_ship_line ELSE 0 END) EARLY_LINE_CNT, count(CASE WHEN fact.time_shipped_date_id < fact.time_schedule_date_id THEN fact.count_ship_line ELSE 0 END) EARLY_L_C, sum(CASE WHEN fact.time_shipped_date_id = fact.time_schedule_date_id THEN fact.count_ship_line ELSE 0 END) ON_TIME_LINE_CNT, count(CASE WHEN fact.time_shipped_date_id = fact.time_schedule_date_id THEN fact.count_ship_line ELSE 0 END) ON_TIME_L_C, sum(CASE WHEN fact.time_shipped_date_id > fact.time_schedule_date_id THEN (fact.time_shipped_date_id - fact.time_schedule_date_id) ELSE 0 END) DAYS_LATE, count(CASE WHEN fact.time_shipped_date_id > fact.time_schedule_date_id THEN (fact.time_shipped_date_id - fact.time_schedule_date_id) ELSE 0 END) DAYS_LATE_CNT, sum(CASE WHEN fact.time_shipped_date_id > fact.time_promise_date_id THEN (fact.time_shipped_date_id - fact.time_promise_date_id) ELSE 0 END) DAYS_LATE_PROMISE, count(CASE WHEN fact.time_shipped_date_id > fact.time_promise_date_id THEN (fact.time_shipped_date_id - fact.time_promise_date_id) ELSE 0 END) DAYS_LATE_PROMISE_CNT, sum(CASE WHEN fact.time_shipped_date_id > fact.time_schedule_date_id THEN fact.shipped_qty_inv ELSE 0 END) SHIPPED_LATE_QTY, count(CASE WHEN fact.time_shipped_date_id > fact.time_schedule_date_id THEN fact.shipped_qty_inv ELSE 0 END) SHIPPED_LATE_QTY_CNT, sum(CASE WHEN fact.time_shipped_date_id > fact.time_promise_date_id THEN fact.shipped_qty_inv ELSE 0 END) SHIPPED_LATE_P_QTY, count(CASE WHEN fact.time_shipped_date_id > fact.time_promise_date_id THEN fact.shipped_qty_inv ELSE 0 END) SHIPPED_LATE_P_QTY_CNT, sum(fact.shipped_date - fact.booked_date)	BOOK_TO_SHIP_DAYS, count(fact.shipped_date - fact.booked_date)	BOOK_TO_SHIP_CNT, sum(CASE WHEN fact.time_shipped_date_id > fact.time_schedule_date_id THEN (fact.shipped_date - fact.booked_date) ELSE 0 END)	BOOK_TO_SHIP_DAYS_LATE, count(CASE WHEN fact.time_shipped_date_id > fact.time_schedule_date_id THEN (fact.shipped_date - fact.booked_date)	ELSE 0 END) BOOK_TO_SHIP_LATE_CNT, sum(CASE WHEN fact.time_shipped_date_id > fact.time_promise_date_id THEN (fact.shipped_date - fact.booked_date) ELSE 0 END)	BOOK_TO_SHIP_DAYS_LATE_P, count(CASE WHEN fact.time_shipped_date_id > fact.time_promise_date_id THEN (fact.shipped_date - fact.booked_date)	ELSE 0 END) BOOK_TO_SHIP_LATE_P_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range1_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range1_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET1_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range2_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range2_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET2_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range3_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range3_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET3_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range4_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range4_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET4_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range5_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range5_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET5_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range6_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range6_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET6_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range7_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range7_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET7_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range8_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range8_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET8_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range9_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range9_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET9_LINE_CNT, sum(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range10_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range10_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET10_LINE_CNT, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range1_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range1_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET1_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range2_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range2_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET2_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range3_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range3_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET3_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range4_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range4_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET4_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range5_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range5_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET5_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range6_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range6_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET6_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range7_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range7_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET7_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range8_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range8_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET8_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range9_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range9_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET9_LINE_CNT_C, count(CASE WHEN (fact.shipped_date - fact.booked_date) >= nvl(bkt.range10_low,(fact.shipped_date - fact.booked_date)-1) AND (fact.shipped_date - fact.booked_date) < nvl(bkt.range10_high,(fact.shipped_date - fact.booked_date)+1)    THEN 1 ELSE 0 END) BUCKET10_LINE_CNT_C, fact.inv_uom_code		UOM, sum(fact.shipped_qty_inv) SHIPPED_QTY, sum(fact.freight_charge_f) FREIGHT_CHARGE_AMT_F,  count(fact.freight_charge_f) FREIGHT_CHARGE_AMT_F_CNT,  sum(fact.freight_charge_g) FREIGHT_CHARGE_AMT_G,  count(fact.freight_charge_g) FREIGHT_CHARGE_AMT_G_CNT,  sum(fact.freight_charge_g1) FREIGHT_CHARGE_AMT_G1,  count(fact.freight_charge_g1) FREIGHT_CHARGE_AMT_G1_CNT,  sum(fact.freight_cost_f) FREIGHT_COST_AMT_F,  count(fact.freight_cost_f) FREIGHT_COST_AMT_F_CNT,  sum(fact.freight_cost_g) FREIGHT_COST_AMT_G,  count(fact.freight_cost_g) FREIGHT_COST_AMT_G_CNT,  sum(fact.freight_cost_g1) FREIGHT_COST_AMT_G1,  count(fact.freight_cost_g1) FREIGHT_COST_AMT_G1_CNT,  count(fact.shipped_qty_inv) SHIPPED_QTY_CNT, count(*)	CNT FROM ISC.ISC_BOOK_SUM2_F fact,   ENI.ENI_OLTP_ITEM_STAR item,   FII.FII_TIME_DAY cal,   BIS.BIS_BUCKET_CUSTOMIZATIONS bkt,   BIS.BIS_BUCKET b WHERE fact.time_shipped_date_id = cal.report_date   AND fact.inventory_item_id = item.inventory_item_id   AND fact.item_inv_org_id = item.organization_id   AND b.short_name = 'ISC_DBI_DAYS_SHIP_AGING'   AND b.bucket_id = bkt.bucket_id   AND fact.line_category_code <> 'RETURN'   AND fact.item_type_code <> 'SERVICE'   AND fact.ordered_quantity <> 0   AND fact.order_source_id <> 27   AND fact.count_ship_line = 1   AND fact.charge_periodicity_code is NULL GROUP BY fact.item_inv_org_id, rollup(fact.ship_to_party_id),  rollup(nvl(item.inv_category_id,-1),((item.inventory_item_id||'-'||item.organization_id),inv_uom_code, item.vbh_category_id)), grouping sets(cal.ent_qtr_id,cal.ent_period_id,cal.week_id,cal.report_date_julian)