APPS.ISC_DBI_NET_BOOK_FULF_PKG SQL Statements

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

Line: 163

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

Line: 190

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

Line: 234

SELECT	0	VIEWBY,
	0	VIEWBYID,
	0	ISC_ATTRIBUTE_2,
	0	ISC_ATTRIBUTE_3,
	0	ISC_ATTRIBUTE_4,
	0	ISC_ATTRIBUTE_5,
	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_9,
	0 	ISC_MEASURE_10,
	0 	ISC_MEASURE_11,
	0 	ISC_MEASURE_12,
	0 	ISC_MEASURE_13
  FROM	dual
 WHERE	1 = 2';

Line: 260

		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_8,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13
		FROM
		(SELECT '||l_viewby_col||',
		nvl(a.c_book, 0)					ISC_MEASURE_2,
		(a.c_book-a.p_book)
		  / abs(decode(a.p_book,0,null,a.p_book))*100		ISC_MEASURE_3,
		nvl(a.c_book_ret, 0)					ISC_MEASURE_4,
		(a.c_book_ret - a.p_book_ret)
		  / abs(decode(a.p_book_ret,0,null,a.p_book_ret))*100	ISC_MEASURE_5,
		nvl((a.c_book - a.c_book_ret), 0)			ISC_MEASURE_6,
		((a.c_book-a.c_book_ret) - (a.p_book-a.p_book_ret))
		  / abs(decode((a.p_book-a.p_book_ret), 0,
				null,(a.p_book-a.p_book_ret)))*100	ISC_MEASURE_7,
		nvl(sum(a.c_book) over (), 0)				ISC_MEASURE_8,
		(sum(a.c_book) over () - sum(a.p_book) over ())
		  / abs(decode(sum(a.p_book) over (), 0,
				null,sum(a.p_book) over ()))*100	ISC_MEASURE_9,
		nvl(sum(a.c_book_ret) over (), 0)			ISC_MEASURE_10,
		(sum(a.c_book_ret) over () - sum(a.p_book_ret) over ())
		  / abs(decode(sum(a.p_book_ret) over (), 0,
				null,sum(a.p_book_ret) over ()))*100	ISC_MEASURE_11,
		nvl(sum(a.c_book - a.c_book_ret) over (), 0)		ISC_MEASURE_12,
		(sum(a.c_book-a.c_book_ret) over () - sum(a.p_book-a.p_book_ret) over ())
		  / abs(decode(sum(a.p_book-a.p_book_ret) over (), 0,
			  null,sum(a.p_book-a.p_book_ret) over ()))*100	ISC_MEASURE_13
		FROM
		(SELECT '||l_viewby_col||',
		sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
			   nvl(booked_amt_'||l_curr_suffix||',0),0))	C_BOOK,
		sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
			   nvl(booked_amt_'||l_curr_suffix||',0),0))	P_BOOK,
		sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
			   nvl(returned_amt_'||l_curr_suffix||',0),0))	C_BOOK_RET,
		sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
			   nvl(returned_amt_'||l_curr_suffix||',0),0))	P_BOOK_RET
		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||'
		GROUP BY '||l_viewby_col||') a)) c,';

Line: 319

    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_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
					ISC_ATTRIBUTE_3, -- Drill - Product Category
		'||l_drill_ord||'	ISC_ATTRIBUTE_4, -- Drill - Orders Value
		'||l_drill_ret||'	ISC_ATTRIBUTE_5, -- Drill - Returns 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: 336

     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_NET_BOOK_FULF&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_BK_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''	))
					ISC_ATTRIBUTE_4, -- Drill - Orders Value
		decode(c.resource_id, NULL,
			NULL,
			decode(c.sales_grp_id, -1, NULL,
				''pFunctionName=ISC_DBI_RET_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''))
					ISC_ATTRIBUTE_5, -- Drill - Returns 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: 366

SELECT	cc.value		VIEWBY,
	cc.id			VIEWBYID,
	null			ISC_ATTRIBUTE_2, -- Drill - Sales Group
	null			ISC_ATTRIBUTE_3, -- Drill - Product Category
	'||l_drill_ord||'	ISC_ATTRIBUTE_4, -- Drill - Orders Value
	'||l_drill_ret||'	ISC_ATTRIBUTE_5, -- Drill - Returns 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: 379

     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_ord||'	ISC_ATTRIBUTE_4, -- Drill - Orders Value
		'||l_drill_ret||'	ISC_ATTRIBUTE_5, -- Drill - Returns 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';