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_CPM_000_MV

Source


select /* 12.0: bug#4526784 */
		grouping_id(cc.class_code, line.customer_party_id)				customer_flag,
		grouping_id(i.vbh_category_id, i.inventory_item_id||'-'||i.organization_id) 	item_cat_flag,
		grouping_id(day.report_date_julian, day.week_id, day.ent_period_id,
			    day.ent_qtr_id, line.customer_party_id, cc.class_code,
                            i.vbh_category_id,
			    i.inventory_item_id||'-'||i.organization_id,
			    line.service_item_id||'-'||line.inv_organization_id,
			    i.inventory_item_id, i.organization_id)				grp_id,
		decode(grouping_id(day.ent_qtr_id, day.ent_period_id, day.week_id, day.report_date_julian),
			14, day.report_date_julian,
			13, day.week_id,
			11, day.ent_period_id,
			7,  day.ent_qtr_id) 							time_id,
		line.customer_party_id 								customer_id,
		cc.class_code									class_code,
		i.inventory_item_id||'-'||i.organization_id  					product_id,
		i.vbh_category_id      								item_category_id,
		line.service_item_id||'-'||line.inv_organization_id				service_item_id,
		i.inventory_item_id								inventory_item_id,
		i.organization_id								organization_id,
		day.report_date_julian								report_date_julian,
		day.week_id									week_id,
		day.ent_period_id								ent_period_id,
		day.ent_qtr_id									ent_qtr_id,
		sum(decode(mx.id,   1, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g)) 				activated_g,
		sum(decode(mx.id,   1, decode(line.renewal_flag, 0, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g,
								 2, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g))) 	activated_new_g,
		sum(decode(mx.id,   1, decode(line.renewal_flag, 1, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g,
								 3, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g))) 	activated_renew_g,
		sum(decode(mx.id,   2, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g)) 				expired_g,
		sum(decode(mx.id,   2, decode(line.renewal_flag, 0, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g,
								 2, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g))) 	expired_new_g,
		sum(decode(mx.id,   2, decode(line.renewal_flag, 1, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g,
								 3, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g))) 	expired_renew_g,
		count(decode(mx.id, 1, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g)) 				c_activated_g,
		count(decode(mx.id, 1, decode(line.renewal_flag, 0, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g,
								 2, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g))) 	c_activated_new_g,
		count(decode(mx.id, 1, decode(line.renewal_flag, 1, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g,
								 3, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g))) 	c_activated_renew_g,
		count(decode(mx.id, 2, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g)) 				c_expired_g,
		count(decode(mx.id, 2, decode(line.renewal_flag, 0, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g,
								 2, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g))) 	c_expired_new_g,
		count(decode(mx.id, 2, decode(line.renewal_flag, 1, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g,
								 3, line.price_negotiated_g + line.ubt_amt_g + line.supp_credit_g + line.credit_amt_g))) 	c_expired_renew_g,
		sum(decode(mx.id,   1, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg)) 				activated_g1,
		sum(decode(mx.id,   1, decode(line.renewal_flag, 0, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg,
								 2, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg))) activated_new_g1,
		sum(decode(mx.id,   1, decode(line.renewal_flag, 1, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg,
								 3, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg))) activated_renew_g1,
		sum(decode(mx.id,   2, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg)) 				expired_g1,
		sum(decode(mx.id,   2, decode(line.renewal_flag, 0, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg,
								 2, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg))) expired_new_g1,
		sum(decode(mx.id,   2, decode(line.renewal_flag, 1, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg,
								 3, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg))) expired_renew_g1,
		count(decode(mx.id, 1, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg)) 				c_activated_g1,
		count(decode(mx.id, 1, decode(line.renewal_flag, 0, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg,
								 2, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg))) c_activated_new_g1,
		count(decode(mx.id, 1, decode(line.renewal_flag, 1, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg,
								 3, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg))) c_activated_renew_g1,
		count(decode(mx.id, 2, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg)) 				c_expired_g1,
		count(decode(mx.id, 2, decode(line.renewal_flag, 0, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg,
								 2, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg))) c_expired_new_g1,
		count(decode(mx.id, 2, decode(line.renewal_flag, 1, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg,
								 3, line.price_negotiated_sg + line.ubt_amt_sg + line.supp_credit_sg + line.credit_amt_sg))) c_expired_renew_g1,
		count(*)					 				c_total
	   FROM	OKI.OKI_DBI_CLE_B 		line,
		FII.FII_TIME_DAY 		day,
		OKI.OKI_DBI_MULTIPLEXER_B 	mx,
		ENI.ENI_OLTP_ITEM_STAR 		i,
		FII.FII_PARTY_MKT_CLASS		cc
	  WHERE	line.application_id	= 515
	    AND	line.buy_or_sell	= 'S'
	    AND	line.scs_code		in ('SERVICE', 'WARRANTY')
	    AND	line.root_lty_code	in ('SERVICE', 'WARRANTY', 'EXT_WARRANTY')
	    AND	line.date_signed	is not null
	    AND line.term_flag		= 1
	    AND line.gsd_flag           = 1
	    AND	mx.id in (1, 2)
	    AND	day.report_date = decode(mx.id, 1, trunc(line.effective_start_date), trunc(line.effective_end_date))
	    AND	line.covered_item_id     = i.inventory_item_id
	    AND	line.covered_item_org_id = i.organization_id
            AND line.customer_party_id = cc.party_id
	GROUP BY
		rollup(cc.class_code, line.customer_party_id),
		rollup(i.vbh_category_id, (i.inventory_item_id||'-'||i.organization_id,i.inventory_item_id,i.organization_id)),
		line.service_item_id||'-'||line.inv_organization_id,
		grouping sets(day.ent_qtr_id, day.ent_period_id, day.week_id, day.report_date_julian)