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_BACKLOG_PKG SQL Statements

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

Line: 183


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

Line: 210


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

Line: 255


SELECT	0	VIEWBY,
	0	VIEWBYID,
	0	ISC_ATTRIBUTE_2,
	0	ISC_ATTRIBUTE_3,
	0	ISC_ATTRIBUTE_1,
	0	ISC_ATTRIBUTE_4,
	0	ISC_ATTRIBUTE_5,
	0	ISC_ATTRIBUTE_6,
	0	ISC_ATTRIBUTE_7,
	0 	ISC_MEASURE_1,
	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_8,
	0 	ISC_MEASURE_16,
	0 	ISC_MEASURE_17,
	0 	ISC_MEASURE_18,
	0 	ISC_MEASURE_19,
	0 	ISC_MEASURE_20,
	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_15,
	0 	ISC_MEASURE_21,
	0 	ISC_MEASURE_22,
	0 	ISC_MEASURE_24,
	0 	ISC_MEASURE_25,
	0 	ISC_MEASURE_26,
	0 	ISC_MEASURE_27,
	0 	ISC_MEASURE_28,
	0 	ISC_MEASURE_29
  FROM	dual
 WHERE	1 = 2';

Line: 304


FROM (SELECT 	(rank() over (&ORDER_BY_CLAUSE nulls last,'||l_viewby_col||'))-1 rnk,
		'||l_viewby_col||',
		ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
		ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_16,ISC_MEASURE_17,
		ISC_MEASURE_18,ISC_MEASURE_19,ISC_MEASURE_20,ISC_MEASURE_9,ISC_MEASURE_10,
		ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
		isc_measure_5 - isc_measure_16	ISC_MEASURE_21, -- Revenue Booked in Prior Periods
		isc_measure_14 - isc_measure_17	ISC_MEASURE_22, -- Prior - Rev Booked in Prior Per
		isc_measure_7 - isc_measure_19	ISC_MEASURE_24, -- Gd Total - Rev Booked in Prior Per
		p_rev_total - p_rev_book_total	ISC_MEASURE_25, -- Gd Total - Prior - Rev Booked in Prior Per
		ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,ISC_MEASURE_29
	FROM (SELECT '||l_viewby_col||',
		nvl(c_net_book, 0)			ISC_MEASURE_1,
		(c_net_book - p_net_book)
		  / decode(p_net_book, 0, null,
			   abs(p_net_book)) *100	ISC_MEASURE_2,
		nvl(sum(c_net_book) over (), 0)		ISC_MEASURE_3,
		(sum(c_net_book) over () - sum(p_net_book) over ())
		  / decode(sum(p_net_book) over (), 0, null,
			   abs(sum(p_net_book) over ())) *100
							ISC_MEASURE_4,
		nvl(c_rev_rec, 0)			ISC_MEASURE_5,
		(c_rev_rec - p_rev_rec)
		  / decode(p_rev_rec, 0, null,
			   abs(p_rev_rec)) *100		ISC_MEASURE_6,
		nvl(sum(c_rev_rec) over (), 0)		ISC_MEASURE_7,
		(sum(c_rev_rec) over () - sum(p_rev_rec) over ())
		  / decode(sum(p_rev_rec) over (), 0, null,
			   abs(sum(p_rev_rec) over ())) *100
							ISC_MEASURE_8,
		nvl(c_rev_book, 0)		 	ISC_MEASURE_16, -- Revenue Booked this Period
		nvl(p_rev_book, 0)		 	ISC_MEASURE_17, -- Prior (Rev BTP)
		(c_rev_book - p_rev_book)
		  / decode(p_rev_book, 0, null,
			   abs(p_rev_book)) *100	ISC_MEASURE_18, -- Change (Rev BTP)
		nvl(sum(c_rev_book) over (), 0)	 	ISC_MEASURE_19, -- Gd Total - Rev BTP
		(sum(c_rev_book) over () - sum(p_rev_book) over ())
		  / decode(sum(p_rev_book) over (), 0, null,
			   abs(sum(p_rev_book) over ())) *100
							ISC_MEASURE_20, -- Gd Total - Change (Rev BTP)
		nvl(c_rev_backlog, 0)			ISC_MEASURE_9,
		(c_rev_backlog - p_rev_backlog)
		  / decode(p_rev_backlog, 0, null,
			   abs(p_rev_backlog)) *100	ISC_MEASURE_10,
		nvl(sum(c_rev_backlog) over (), 0)	ISC_MEASURE_11,
		(sum(c_rev_backlog) over () - sum(p_rev_backlog) over ())
		  / decode(sum(p_rev_backlog) over (), 0, null,
			   abs(sum(p_rev_backlog) over ())) *100
							ISC_MEASURE_12,
		nvl(p_net_book, 0)			ISC_MEASURE_13,
		nvl(p_rev_rec, 0)			ISC_MEASURE_14,
		nvl(p_rev_backlog, 0)			ISC_MEASURE_15,
		sum(nvl(p_net_book, 0)) over ()		ISC_MEASURE_26,
		sum(nvl(p_rev_rec, 0)) over ()		ISC_MEASURE_27,
		sum(nvl(p_rev_book, 0)) over ()		ISC_MEASURE_28,
		sum(nvl(p_rev_backlog, 0)) over ()	ISC_MEASURE_29,
		nvl(sum(p_rev_rec) over (), 0)		p_rev_total, -- Gd Total Prior Revenue
		nvl(sum(p_rev_book) over (), 0)		p_rev_book_total -- Gd Total Prior Rev BTP
		FROM
		(SELECT '||l_viewby_col||',
		sum(c_book_xtd)					c_net_book,
		sum(p_book_xtd)					p_net_book,
		sum(c_rev_rec_xtd)				c_rev_rec,
		sum(p_rev_rec_xtd)				p_rev_rec,
		sum(c_rev_book_xtd)				c_rev_book,
		sum(p_rev_book_xtd)				p_rev_book,
		sum(c_backlog) + sum(c_defer_rev)		c_rev_backlog,
		sum(p_backlog) + sum(p_defer_rev)		p_rev_backlog
		FROM
		(/* Compute XTD components */
		SELECT  '||l_viewby_col||',
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			nvl(net_booked_amt_'||l_curr_suffix||', 0), 0)	C_BOOK_XTD,
		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
			nvl(net_booked_amt_'||l_curr_suffix||', 0), 0)	P_BOOK_XTD,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			nvl(recognized_amt_'||l_curr_suffix||', 0), 0)	C_REV_REC_XTD,
		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
			nvl(recognized_amt_'||l_curr_suffix||', 0), 0)	P_REV_REC_XTD,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			nvl('||l_rev_book||', 0), 0)	C_REV_BOOK_XTD,
		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
			nvl('||l_rev_book||', 0), 0)	P_REV_BOOK_XTD,

		0					C_BACKLOG,
		0					P_BACKLOG,
		0					C_DEFER_REV,
		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,&BIS_NESTED_PATTERN) = cal.record_type_id'
		||l_flags
		||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
	UNION ALL /* Compute ITD components */
		SELECT '||l_viewby_col||',
		0					C_BOOK_XTD,
		0					P_BOOK_XTD,
		0					C_REV_REC_XTD,
		0					P_REV_REC_XTD,
		0					C_REV_BOOK_XTD,
		0					P_REV_BOOK_XTD,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			nvl(backlog_amt_'||l_curr_suffix||', 0), 0)	C_BACKLOG,
		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
			nvl(backlog_amt_'||l_curr_suffix||', 0), 0)	P_BACKLOG,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			nvl(deferred_amt_'||l_curr_suffix||', 0), 0)	C_DEFER_REV,
		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: 437


SELECT	ecat.value 		VIEWBY,
	ecat.id			VIEWBYID,
	NULL			ISC_ATTRIBUTE_2, -- Drill - Sales Group
	decode(ecat.leaf_node_flag, ''Y'',
		NULL,
		''pFunctionName='||l_func||'&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
				ISC_ATTRIBUTE_3,  -- Drill - Product Category
	NULL			ISC_ATTRIBUTE_1, -- Drill - Customer Classification
	''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_4, -- Drill - Net Booked
	''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_5, -- Drill - Revenue
	''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_6, -- Drill - Revenue Booked this Period
	''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_7, -- Drill - Product Revenue Backlog'
	||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: 462


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='||l_func||'&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
	NULL				ISC_ATTRIBUTE_1, -- Drill - Customer Classification
	decode(c.sales_grp_id, -1, NULL,
		''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'')
					ISC_ATTRIBUTE_4, -- Drill - Net Booked
	decode(c.sales_grp_id, -1, NULL,
		''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'')
					ISC_ATTRIBUTE_5, -- Drill - Revenue
	decode(c.sales_grp_id, -1, NULL,
		''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'')
					ISC_ATTRIBUTE_6, -- Drill - Revenue Booked this Period
	decode(c.sales_grp_id, -1, NULL,
		''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'')
					ISC_ATTRIBUTE_7, -- Drill - Product Revenue Backlog'
		||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: 494


SELECT	cc.value		VIEWBY,
	cc.id			VIEWBYID,
	NULL	  		ISC_ATTRIBUTE_2, -- Drill - Sales Group
	NULL			ISC_ATTRIBUTE_3, -- Drill - Product Category
	''pFunctionName='||l_func||'&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=CUSTOMER+FII_CUSTOMERS''
				ISC_ATTRIBUTE_1, -- Drill - Customer Classification
	''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_4, -- Drill - Net Booked
	''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_5, -- Drill - Revenue
	''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_6, -- Drill - Revenue Booked this Period
	''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_7, -- Drill - Product Revenue Backlog'
	||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: 516


SELECT	cust.value	VIEWBY,
	cust.id			VIEWBYID,
	NULL	  		ISC_ATTRIBUTE_2, -- Drill - Sales Group
	NULL			ISC_ATTRIBUTE_3, -- Drill - Product Category
	NULL			ISC_ATTRIBUTE_1, -- Drill - Customer Classification
	''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_4, -- Drill - Net Booked
	''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_5, -- Drill - Revenue
	''pFunctionName=FII_AR_SG_PROD_REV&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_6, -- Drill - Revenue Booked this Period
	''pFunctionName=ISC_DBI_REV_PIPELINE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY''
				ISC_ATTRIBUTE_7, -- Drill - Product Revenue Backlog'
	||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';