APPS.ISC_DBI_FULF_PERF_PKG SQL Statements

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

Line: 10

  l_select_stmt			VARCHAR2(10000);

Line: 11

  l_union_select_stmt		VARCHAR2(10000);

Line: 13

  l_inner_select_stmt		VARCHAR2(10000);

Line: 77

		(SELECT 1
		FROM org_access o
		WHERE o.responsibility_id = fnd_global.resp_id
		AND o.resp_application_id = fnd_global.resp_appl_id
		AND o.organization_id = inv_org)
	OR EXISTS
		(SELECT 1
		FROM mtl_parameters org
		WHERE org.organization_id = inv_org
		AND NOT EXISTS
			(SELECT 1
			FROM org_access ora
			WHERE org.organization_id = ora.organization_id)))';

Line: 171

    THEN l_select_stmt := '
 SELECT	items.value						VIEWBY,
	items.id						VIEWBYID,
 	'||l_drill||'
								ISC_ATTRIBUTE_2, -- drill on Booked Value
	NULL							ISC_ATTRIBUTE_3, -- drill across url
	items.description					ISC_ATTRIBUTE_4, -- item description
	mtl.unit_of_measure					ISC_ATTRIBUTE_5, -- item uom
	'||l_measures||'
   FROM
(SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, item_id)) - 1		rnk,
	item_id,
	uom,
	'||l_measures||'
   FROM
(SELECT	c.item_id,
	c.uom,		';

Line: 188

	l_inner_select_stmt := '
		 SELECT	fact.item_id					ITEM_ID,
			fact.uom					UOM,';

Line: 191

	l_union_select_stmt := '
		 SELECT item_id						ITEM_ID,
			uom						UOM,';

Line: 206

    THEN l_select_stmt := '
 SELECT	org.name						VIEWBY,
	org.organization_id					VIEWBYID,
	'||l_drill||'
 								ISC_ATTRIBUTE_2, -- drill on Booked Value
	NULL							ISC_ATTRIBUTE_3, -- drill across url
	NULL							ISC_ATTRIBUTE_4, -- item description
	NULL							ISC_ATTRIBUTE_5, -- item uom
	'||l_measures||'
   FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, inv_org_id)) - 1		rnk,
	inv_org_id,
	'||l_measures||'
   FROM
(SELECT	c.inv_org_id,	';

Line: 221

	l_inner_select_stmt := '
		 SELECT	fact.inv_org_id					INV_ORG_ID,';

Line: 223

	l_union_select_stmt := '
		 SELECT inv_org_id					INV_ORG_ID,';

Line: 235

    THEN l_select_stmt := '
 SELECT	cust.value						VIEWBY,
	cust.id							VIEWBYID,
 	''pFunctionName=ISC_DBI_BOOK_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''
								ISC_ATTRIBUTE_2, -- drill on Booked Value
	NULL							ISC_ATTRIBUTE_3, -- drill across url
	NULL							ISC_ATTRIBUTE_4, -- item description
	NULL							ISC_ATTRIBUTE_5, -- item uom
	'||l_measures||'
   FROM
(SELECT	(rank() over (&ORDER_BY_CLAUSE nulls last, customer_id)) - 1		rnk,
	customer_id,
	'||l_measures||'
   FROM
(SELECT	c.customer_id,	';

Line: 250

	l_inner_select_stmt := '
		 SELECT	fact.customer_id				CUSTOMER_ID,';

Line: 252

	l_union_select_stmt := '
		 SELECT customer_id					CUSTOMER_ID,';

Line: 263

    l_select_stmt := '
 SELECT	ecat.value					VIEWBY,
	ecat.id						VIEWBYID,
	'||l_drill||'
							ISC_ATTRIBUTE_2, -- drill on Booked Value
	decode(ecat.leaf_node_flag, ''Y'',
		''pFunctionName=ISC_DBI_FULF_PERF&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
		''pFunctionName=ISC_DBI_FULF_PERF&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
								ISC_ATTRIBUTE_3, -- drill across url
	NULL							ISC_ATTRIBUTE_4, -- item description
	NULL							ISC_ATTRIBUTE_5, -- item uom
	'||l_measures||'
   FROM
(SELECT (rank() over (&ORDER_BY_CLAUSE nulls last, item_category_id)) - 1	rnk,
	item_category_id,
	'||l_measures||'
   FROM
(SELECT	c.item_category_id,	';

Line: 283

	l_inner_select_stmt := '
		 SELECT	eni_cat.parent_id				ITEM_CATEGORY_ID,';

Line: 286

	l_inner_select_stmt := '
		 SELECT	eni_cat.imm_child_id				ITEM_CATEGORY_ID,';

Line: 289

	l_union_select_stmt := '
		 SELECT item_category_id				ITEM_CATEGORY_ID,';

Line: 308

	      l_inner_select_stmt := '
		 SELECT	fact.parent_id					ITEM_CATEGORY_ID,';

Line: 311

	      l_inner_select_stmt := '
		 SELECT fact.imm_child_id				ITEM_CATEGORY_ID,';

Line: 338

  l_stmt := l_select_stmt || '
	c.curr_booked_qty					ISC_MEASURE_1, -- book qty
	c.curr_booked_value					ISC_MEASURE_2, -- book
	(c.curr_booked_value-c.prev_booked_value)
	  / decode(c.prev_booked_value, 0, NULL,
		   abs(c.prev_booked_value)) * 100		ISC_MEASURE_3, -- book change
	c.curr_fulfill_qty					ISC_MEASURE_4, -- fulf qty
	c.curr_fulfill_value					ISC_MEASURE_5, -- fulf
	(c.curr_fulfill_value-c.prev_fulfill_value)
	  / decode(c.prev_fulfill_value, 0, NULL,
		   abs(c.prev_fulfill_value)) * 100		ISC_MEASURE_6, -- fulf change
	c.curr_booked_value
	  / decode(c.curr_fulfill_value, 0, NULL,
		   c.curr_fulfill_value)			ISC_MEASURE_7, -- book to fulf r
	c.curr_booked_value
	  / decode(c.curr_fulfill_value, 0, NULL,
		   c.curr_fulfill_value) -
	c.prev_booked_value
	  / decode(c.prev_fulfill_value, 0, NULL,
		   c.prev_fulfill_value)			ISC_MEASURE_8, -- book to fulf r change
	sum(c.curr_booked_value) over ()			ISC_MEASURE_9, -- gd total book
	(sum(c.curr_booked_value) over () - sum(c.prev_booked_value) over ())
	  / decode(sum(c.prev_booked_value) over (), 0, NULL,
		   abs(sum(c.prev_booked_value) over ()))
	  * 100							ISC_MEASURE_10, -- gd total book change
	sum(c.curr_fulfill_value) over ()			ISC_MEASURE_11, -- gd total fulf
	(sum(c.curr_fulfill_value) over () - sum(c.prev_fulfill_value) over ())
	  / decode(sum(c.prev_fulfill_value) over (), 0, NULL,
		   abs(sum(c.prev_fulfill_value) over ()))
	  * 100							ISC_MEASURE_12, -- gd total fulf change
	sum(c.curr_booked_value) over ()
	  / decode(sum(c.curr_fulfill_value) over (), 0, NULL,
		   sum(c.curr_fulfill_value) over ())		ISC_MEASURE_13, -- gd total book to fulf r
	sum(c.curr_booked_value) over ()
	  / decode(sum(c.curr_fulfill_value) over (), 0, NULL,
		   sum(c.curr_fulfill_value) over ()) -
	sum(c.prev_booked_value) over ()
	  / decode(sum(c.prev_fulfill_value) over (), 0, NULL,
		   sum(c.prev_fulfill_value) over ())		ISC_MEASURE_14, -- gd total book to fulf r change
	c.curr_booked_value					ISC_MEASURE_15, -- KPI book
	c.prev_booked_value					ISC_MEASURE_16, -- KPI book prior
	c.curr_fulfill_value					ISC_MEASURE_17, -- KPI fulf
	c.prev_fulfill_value					ISC_MEASURE_18, -- KPI fulf prior
	c.curr_booked_value
	  / decode(c.curr_fulfill_value, 0, NULL,
		   c.curr_fulfill_value)			ISC_MEASURE_19, -- KPI book to fulf r
	c.prev_booked_value
	  / decode(c.prev_fulfill_value, 0, NULL,
		   c.prev_fulfill_value)			ISC_MEASURE_20, -- KPI book to fulf r prior
	sum(c.curr_booked_value) over ()
	  / decode(sum(c.curr_fulfill_value) over (), 0, NULL,
		   sum(c.curr_fulfill_value) over ())		ISC_MEASURE_21, -- KPI gd total book to fulf r
	sum(c.prev_booked_value) over ()
	  / decode(sum(c.prev_fulfill_value) over (), 0, NULL,
		   sum(c.prev_fulfill_value) over ())		ISC_MEASURE_22, -- KPI gd total book to fulf r prior
	sum(c.curr_booked_value) over ()			ISC_MEASURE_24, -- KPI gd total book
	sum(c.prev_booked_value) over ()			ISC_MEASURE_25, -- KPI gd total book prior
	sum(c.curr_fulfill_value) over ()			ISC_MEASURE_27, -- KPI gd total fulf
	sum(c.prev_fulfill_value) over ()			ISC_MEASURE_28 -- KPI gd total fulf prior
	   FROM	('||l_union_select_stmt||'
		sum(curr_booked_qty)					CURR_BOOKED_QTY,
		sum(curr_booked_value)					CURR_BOOKED_VALUE,
		sum(prev_booked_value)					PREV_BOOKED_VALUE,
		sum(curr_fulfill_qty)					CURR_FULFILL_QTY,
		sum(curr_fulfill_value)					CURR_FULFILL_VALUE,
		sum(prev_fulfill_value)					PREV_FULFILL_VALUE
	   FROM ('||l_inner_select_stmt||'
		fact.inv_org_id						INV_ORG,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			   fact.booked_qty, 0)				CURR_BOOKED_QTY,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			   fact.booked_amt_'||l_curr_suffix||', 0)	CURR_BOOKED_VALUE,
		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
			   fact.booked_amt_'||l_curr_suffix||', 0)	PREV_BOOKED_VALUE,
		0							CURR_FULFILL_QTY,
		0							CURR_FULFILL_VALUE,
		0							PREV_FULFILL_VALUE
	   FROM '||l_mv1||'			fact,
		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
	  WHERE fact.time_id = cal.time_id'
		||l_flags_where||'
	    AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
	    AND cal.period_type_id = fact.period_type_id
	    AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
		||l_prod_cat_where
		||l_prod_where
		||l_cust_where||'
		UNION ALL
		'||l_inner_select_stmt||'
		fact.inv_org_id						INV_ORG,
		0							CURR_BOOKED_QTY,
		0							CURR_BOOKED_VALUE,
		0							PREV_BOOKED_VALUE,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			   fact.fulfilled_qty, 0)			CURR_FULFILL_QTY,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			   fact.fulfilled_amt_'||l_curr_suffix||', 0)	CURR_FULFILL_VALUE,
		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
			   fact.fulfilled_amt_'||l_curr_suffix||', 0)	PREV_FULFILL_VALUE
	   FROM '||l_mv2||'			fact,
		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
	  WHERE fact.time_id = cal.time_id'
		||l_flags_where||'
	    AND fact.return_flag = 0
	    AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
	    AND cal.period_type_id = fact.period_type_id
	    AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
		||l_prod_cat_where
		||l_prod_where
		||l_cust_where||')
	  WHERE	'||l_inv_org_where
		||l_union_group_by_stmt||')	c))	a,'
		||l_where_stmt;