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.

APPS.ISC_DBI_REV_PL_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 159


      ELSE -- view by sales group, prod.cat selected
        l_prod_cat_where :='
		AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';

Line: 186


    ELSE -- a prod cat has been selected
      l_prod_cat_from := ',
		ENI_DENORM_HIERARCHIES		eni_cat,
		MTL_DEFAULT_CATEGORY_SETS	mdcs';

Line: 230


SELECT	0	VIEWBY,
	0	VIEWBYID,
	0	ISC_ATTRIBUTE_2,
	0	ISC_ATTRIBUTE_3,
	0	ISC_ATTRIBUTE_4,
	0 	ISC_MEASURE_2,
	0 	ISC_MEASURE_3,
	0 	ISC_MEASURE_4,
	0 	ISC_MEASURE_5,
	0 	ISC_MEASURE_6,
	0 	ISC_MEASURE_7,
	0 	ISC_MEASURE_9,
	0 	ISC_MEASURE_10,
	0 	ISC_MEASURE_11,
	0 	ISC_MEASURE_12,
	0 	ISC_MEASURE_13,
	0 	ISC_MEASURE_14,
	0 	ISC_MEASURE_16,
	0 	ISC_MEASURE_17,
	0 	ISC_MEASURE_18,
	0 	ISC_MEASURE_19,
	0 	ISC_MEASURE_20,
	0 	ISC_MEASURE_21
  FROM	dual
 WHERE	1 = 2';

Line: 262


		FROM(SELECT
		(rank() over (&ORDER_BY_CLAUSE nulls last,'||l_viewby_col||'))-1 rnk,
		'||l_viewby_col||',
		ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_7,
		ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,
		ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_21
		FROM
		(SELECT '||l_viewby_col||',
		nvl(c_backlog, 0)					ISC_MEASURE_2,
		c_backlog
		  / decode(sum(c_backlog) over (), 0, null,
			   sum(c_backlog) over ()) * 100		ISC_MEASURE_3,
		(c_backlog - p_backlog)
		   / decode(p_backlog, 0, null,
			    abs(p_backlog)) * 100			ISC_MEASURE_4,
		nvl(sum(c_backlog) over (), 0)				ISC_MEASURE_5,
		sum(c_backlog) over ()
		  / decode(sum(c_backlog) over (), 0, null,
			   sum(c_backlog) over ()) * 100		ISC_MEASURE_6,
		(sum(c_backlog) over () - sum(p_backlog) over ())
		   / decode(sum(p_backlog) over (), 0, null,
			    abs(sum(p_backlog) over ())) * 100		ISC_MEASURE_7,
		nvl(c_defer_rev, 0)					ISC_MEASURE_9,
		c_defer_rev
		  / decode(sum(c_defer_rev) over (), 0, null,
			   sum(c_defer_rev) over ()) * 100		ISC_MEASURE_10,
		(c_defer_rev - p_defer_rev)
		  / decode(p_defer_rev, 0, null,
			   abs(p_defer_rev)) * 100			ISC_MEASURE_11,
		nvl(sum(c_defer_rev) over (), 0)			ISC_MEASURE_12,
		sum(c_defer_rev) over ()
		  / decode(sum(c_defer_rev) over (), 0, null,
			   sum(c_defer_rev) over ()) * 100		ISC_MEASURE_13,
		(sum(c_defer_rev) over () - sum(p_defer_rev) over ())
		  / decode(sum(p_defer_rev) over (), 0, null,
			   abs(sum(p_defer_rev) over ())) * 100		ISC_MEASURE_14,
		nvl((c_backlog+c_defer_rev), 0)				ISC_MEASURE_16,
		(c_backlog+c_defer_rev)
		  / decode(sum(c_backlog+c_defer_rev) over (), 0, null,
			   sum(c_backlog+c_defer_rev) over ()) * 100	ISC_MEASURE_17,
		((c_backlog+c_defer_rev) - (p_backlog+p_defer_rev))
		  / decode((p_backlog+p_defer_rev), 0, null,
			   abs((p_backlog+p_defer_rev))) * 100		ISC_MEASURE_18,
		nvl(sum(c_backlog+c_defer_rev) over (), 0)		ISC_MEASURE_19,
		sum(c_backlog+c_defer_rev) over ()
		  / decode(sum(c_backlog+c_defer_rev) over (), 0, null,
			   sum(c_backlog+c_defer_rev) over ()) * 100	ISC_MEASURE_20,
		(sum(c_backlog+c_defer_rev) over () - sum(p_backlog+p_defer_rev) over ())
		   / decode(sum(p_backlog+p_defer_rev) over (), 0, null,
			    abs(sum(p_backlog+p_defer_rev) over ())) * 100	ISC_MEASURE_21
		FROM
		(
		SELECT '||l_viewby_col||',
		sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
			nvl(backlog_amt_'||l_curr_suffix||', 0), 0))	C_BACKLOG,
		sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
			nvl(backlog_amt_'||l_curr_suffix||', 0), 0))	P_BACKLOG,
		sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
			nvl(deferred_amt_'||l_curr_suffix||', 0), 0))	C_DEFER_REV,
		sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
			nvl(deferred_amt_'||l_curr_suffix||', 0), 0))	P_DEFER_REV
		FROM '||l_mv||' 	f,
		FII_TIME_RPT_STRUCT_V		cal'
		||l_prod_cat_from||'
     		WHERE f.time_id = cal.time_id
		AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
		AND cal.period_type_id = f.period_type_id
		AND bitand(cal.record_type_id,1143) = cal.record_type_id'
		||l_flags
		||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
		GROUP BY '||l_viewby_col||'))) c,';

Line: 342


    l_stmt := '	SELECT ecat.value 	VIEWBY,
		ecat.id			VIEWBYID,
		null			ISC_ATTRIBUTE_2, -- Drill - Sales Group
		decode(ecat.leaf_node_flag, ''Y'',
			NULL,
			''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
					ISC_ATTRIBUTE_3, -- Drill - Product Category
		'||l_drill_bklg||'	ISC_ATTRIBUTE_4, -- Drill - Net Prod Order Bklg Value'
		||l_inner_sql||'
		ENI_ITEM_VBH_NODES_V 	ecat
		WHERE ecat.parent_id = ecat.child_id
		'||l_cat_join||'
		AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
		ORDER BY rnk';

Line: 358


     l_stmt := 'SELECT
		decode(c.resource_id,null,g.group_name,
		       r.resource_name)	VIEWBY,
		decode(c.resource_id,null,to_char(c.sales_grp_id),
			c.resource_id||''.''||c.sales_grp_id)
					VIEWBYID,
		decode(c.resource_id, NULL,
			''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'',
			NULL)		ISC_ATTRIBUTE_2, -- Drill - Sales Group
		null			ISC_ATTRIBUTE_3, -- Drill - Product Category
		decode(c.resource_id, NULL,
			NULL,
			decode(c.sales_grp_id, -1, NULL,
				''pFunctionName=ISC_DBI_BKLG_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''))
					ISC_ATTRIBUTE_4, -- Drill - Net Prod Order Bklg Value'
		||l_inner_sql||'
		JTF_RS_GROUPS_VL		g,
		JTF_RS_RESOURCE_EXTNS_VL	r
		WHERE c.sales_grp_id = g.group_id
		AND c.resource_id = r.resource_id(+)
		AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
		ORDER BY rnk' ;

Line: 383


SELECT	cc.value		VIEWBY,
	cc.id			VIEWBYID,
	null			ISC_ATTRIBUTE_2, -- Drill - Sales Group
	null			ISC_ATTRIBUTE_3, -- Drill - Product Category
	'||l_drill_bklg||'	ISC_ATTRIBUTE_4, -- Drill - Net Prod Order Bklg Value'
	||l_inner_sql||'
	FII_PARTNER_MKT_CLASS_V cc
WHERE c.class_code = cc.id
AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
ORDER BY rnk';

Line: 395


     l_stmt := 'SELECT cust.value	VIEWBY,
		cust.id			VIEWBYID,
		null			ISC_ATTRIBUTE_2, -- Drill - Sales Group
		null			ISC_ATTRIBUTE_3, -- Drill - Product Category
		'||l_drill_bklg||'	ISC_ATTRIBUTE_4, -- Drill - Net Prod Order Bklg Value'
		||l_inner_sql||'
		FII_CUSTOMERS_V 	cust
  		WHERE c.customer_id = cust.id
		AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
		ORDER BY rnk';