APPS.ISC_DBI_BACKLOG_PDUE_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: 79

		(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 = fact.inv_org_id)
	OR EXISTS
		(SELECT 1
		FROM mtl_parameters org
		WHERE org.organization_id = fact.inv_org_id
		AND NOT EXISTS
			(SELECT 1
			FROM org_access ora
			WHERE org.organization_id = ora.organization_id)))';

Line: 172

    THEN l_select_stmt := '
 SELECT	items.value			VIEWBY,
	items.id			VIEWBYID,
	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: 187

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

Line: 190

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

Line: 205

    THEN l_select_stmt := '
 SELECT	org.name			VIEWBY,
	org.organization_id		VIEWBYID,
	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: 218

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

Line: 220

	l_union_select_stmt := '
		 SELECT inv_org_id		INV_ORG_ID,';

Line: 232

    THEN l_select_stmt := '
 SELECT	cust.value			VIEWBY,
	cust.id				VIEWBYID,
	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: 245

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

Line: 247

	l_union_select_stmt := '
		 SELECT customer_id		CUSTOMER_ID,';

Line: 258

    l_select_stmt := '
 SELECT	ecat.value			VIEWBY,
	ecat.id				VIEWBYID,
	decode(ecat.leaf_node_flag, ''Y'',
		''pFunctionName=ISC_DBI_BACKLOG_PDUE&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
		''pFunctionName=ISC_DBI_BACKLOG_PDUE&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: 276

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

Line: 279

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

Line: 282

	l_union_select_stmt := '
		 SELECT item_category_id	ITEM_CATEGORY_ID,';

Line: 301

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

Line: 304

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

Line: 339

  l_stmt := l_select_stmt || '
	c.curr_bklg_qty						ISC_MEASURE_1, -- bklg qty
	c.curr_bklg_value					ISC_MEASURE_2, -- bklg
	(c.curr_bklg_value - c.prev_bklg_value)
	  / decode(c.prev_bklg_value, 0, NULL,
		   abs(c.prev_bklg_value)) * 100		ISC_MEASURE_3, -- bklg change
	c.curr_bklg_value
	  / decode(sum(c.curr_bklg_value) over (), 0, NULL,
		   sum(c.curr_bklg_value) over ()) * 100	ISC_MEASURE_4, -- bklg % of total
	c.curr_pdue_qty						ISC_MEASURE_5, -- pdue qty
	c.curr_pdue_value					ISC_MEASURE_6, -- pdue
	(c.curr_pdue_value - c.prev_pdue_value)
	  / decode(c.prev_pdue_value, 0, NULL,
		   abs(c.prev_pdue_value)) * 100		ISC_MEASURE_7, -- pdue change
	c.curr_pdue_value
	  / decode(sum(c.curr_pdue_value) over (), 0, NULL,
		   sum(c.curr_pdue_value) over ()) * 100	ISC_MEASURE_8, -- pdue % of total
	sum(c.curr_bklg_value) over ()				ISC_MEASURE_9, -- gd total bklg
	(sum(c.curr_bklg_value) over () - sum(c.prev_bklg_value) over ())
	  / decode(sum(c.prev_bklg_value) over (), 0, NULL,
		   abs(sum(c.prev_bklg_value) over ())) * 100	ISC_MEASURE_10, -- gd total bklg change
	sum(c.curr_bklg_value) over ()
	  / decode(sum(c.curr_bklg_value) over (), 0, NULL,
		   sum(c.curr_bklg_value) over ()) * 100	ISC_MEASURE_11, -- gd total bklg % of total
	sum(c.curr_pdue_value) over ()				ISC_MEASURE_12, -- gd total pdue
	(sum(c.curr_pdue_value) over () - sum(c.prev_pdue_value) over ())
	  / decode(sum(c.prev_pdue_value) over (), 0, NULL,
		   abs(sum(c.prev_pdue_value) over ())) * 100	ISC_MEASURE_13, -- gd total pdue change
	sum(c.curr_pdue_value) over ()
	  / decode(sum(c.curr_pdue_value) over (), 0, NULL,
		   sum(c.curr_pdue_value) over ()) * 100	ISC_MEASURE_14, -- gd total pdue % of total
	c.curr_bklg_value					ISC_MEASURE_15, -- KPI bklg
	c.prev_bklg_value					ISC_MEASURE_16, -- KPI bklg prior
	c.curr_pdue_value					ISC_MEASURE_17, -- KPI pdue
	c.prev_pdue_value					ISC_MEASURE_18, -- KPI pdue prior
	sum(c.curr_bklg_value) over ()				ISC_MEASURE_19, -- gd total KPI bklg
	sum(c.prev_bklg_value) over ()				ISC_MEASURE_20, -- gd total KPI bklg prior
	sum(c.curr_pdue_value) over ()				ISC_MEASURE_22,	-- gd total KPI pdue
	sum(c.prev_pdue_value) over ()				ISC_MEASURE_23	-- gd.total KPI pdue prior
	   FROM	('||l_union_select_stmt||'
		sum(y_bklg_qty) + sum(book_qty_ytd)
		  - sum(fulf_qty_ytd)		CURR_BKLG_QTY,
		sum(c_y_bklg) + sum(c_book_ytd)
		  - sum(c_fulf_ytd)		CURR_BKLG_VALUE,
		sum(p_y_bklg) + sum(p_book_ytd)
		  - sum(p_fulf_ytd)		PREV_BKLG_VALUE,
		sum(curr_pdue_qty)		CURR_PDUE_QTY,
		sum(curr_pdue_value)		CURR_PDUE_VALUE,
		sum(prev_pdue_value)		PREV_PDUE_VALUE
	   FROM /* Compute year backlog balance */
		('||l_inner_select_stmt||'
		decode(time.report_date, &BIS_CURRENT_ASOF_DATE,
			fact.bklog_qty, 0)	Y_BKLG_QTY,
		0				BOOK_QTY_YTD,
		0				FULF_QTY_YTD,
		decode(time.report_date, &BIS_CURRENT_ASOF_DATE,
			fact.bklg_amt_'||l_curr_suffix||', 0)	C_Y_BKLG,
		decode(time.report_date, &BIS_PREVIOUS_ASOF_DATE,
			fact.bklg_amt_'||l_curr_suffix||', 0)	P_Y_BKLG,
		0				C_BOOK_YTD,
		0				P_BOOK_YTD,
		0				C_FULF_YTD,
		0				P_FULF_YTD,
		NULL				CURR_PDUE_QTY,
		NULL				CURR_PDUE_VALUE,
		NULL				PREV_PDUE_VALUE
	   FROM '||l_mv1||'		fact,
		FII_TIME_DAY			time'||l_prod_cat_from||'
	  WHERE time.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
	    AND time.ent_year_start_date = fact.time_snapshot_date_id'
	    	||l_flags_where
		||l_inv_org_where
		||l_prod_cat_where
		||l_prod_where
		||l_cust_where||'
	UNION ALL /* Computer YTD net Booking */'
		||l_inner_select_stmt||'
		0				Y_BKLG_QTY,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			fact.booked_qty2, 0)	BOOK_QTY_YTD,
		0				FULF_QTY_YTD,
		0				C_Y_BKLG,
		0				P_Y_BKLG,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
		       fact.booked_amt2_'||l_curr_suffix||', 0)	C_BOOK_YTD,
		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
		       fact.booked_amt2_'||l_curr_suffix||', 0)	P_BOOK_YTD,
		0				C_FULF_YTD,
		0				P_FULF_YTD,
		NULL				CURR_PDUE_QTY,
		NULL				CURR_PDUE_VALUE,
		NULL				PREV_PDUE_VALUE
	   FROM '||l_mv_book||'		fact,
		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
	  WHERE	cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
	    AND fact.time_id = cal.time_id
	    AND fact.period_type_id = cal.period_type_id
	    AND bitand(cal.record_type_id, 119) = cal.record_type_id'
	    	||l_flags_where2
		||l_inv_org_where
		||l_prod_cat_where
		||l_prod_where
		||l_cust_where||'
	UNION ALL /* Computer YTD net Fulfillment */'
		||l_inner_select_stmt||'
		0				Y_BKLG_QTY,
		0				BOOK_QTY_YTD,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			fact.booked_qty, 0)	FULF_QTY_YTD,
		0				C_Y_BKLG,
		0				P_Y_BKLG,
		0				C_BOOK_YTD,
		0				P_BOOK_YTD,
		decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			fact.booked_amt_'||l_curr_suffix||', 0)	C_FULF_YTD,
		decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
			fact.booked_amt_'||l_curr_suffix||', 0)	P_FULF_YTD,
		NULL				CURR_PDUE_QTY,
		NULL				CURR_PDUE_VALUE,
		NULL				PREV_PDUE_VALUE
	   FROM '||l_mv_fulf||'		fact,
		FII_TIME_RPT_STRUCT_V		cal'||l_prod_cat_from||'
	  WHERE	cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
	    AND fact.time_id = cal.time_id
	    AND fact.period_type_id = cal.period_type_id
	    AND bitand(cal.record_type_id, 119) = cal.record_type_id'
	    	||l_flags_where2
		||l_inv_org_where
		||l_prod_cat_where
		||l_prod_where
		||l_cust_where||'
	UNION ALL'
		||l_inner_select_stmt||'
		0				Y_BKLG_QTY,
		0				BOOK_QTY_YTD,
		0				FULF_QTY_YTD,
		0				C_Y_BKLG,
		0				P_Y_BKLG,
		0				C_BOOK_YTD,
		0				P_BOOK_YTD,
		0				C_FULF_YTD,
		0				P_FULF_YTD,
		decode(fact.time_snapshot_date_id, a.day,
		       fact.pdue_qty, NULL)	CURR_PDUE_QTY,
		decode(fact.time_snapshot_date_id, a.day,
		       fact.pdue_amt_'||l_curr_suffix||', NULL)	CURR_PDUE_VALUE,
		decode(fact.time_snapshot_date_id, b.day,
		       fact.pdue_amt_'||l_curr_suffix||', NULL)	PREV_PDUE_VALUE
	   FROM (SELECT max(time_snapshot_date_id)		day
		   FROM	'||l_mv2||'			fact
		  WHERE	fact.time_snapshot_date_id BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
						       AND &BIS_CURRENT_ASOF_DATE
					)	a,
		(SELECT max(time_snapshot_date_id)		day
		   FROM	'||l_mv2||'			fact
		  WHERE	fact.time_snapshot_date_id BETWEEN &BIS_PREVIOUS_EFFECTIVE_START_DATE
						       AND &BIS_PREVIOUS_ASOF_DATE
					)	b,
		'||l_mv2||'		fact'||l_prod_cat_from||'
	  WHERE fact.time_snapshot_date_id IN (a.day, b.day)
	    AND fact.late_schedule_flag = 1'
		||l_flags_where
		||l_inv_org_where
		||l_prod_cat_where
		||l_prod_where
		||l_cust_where||')'	-- end of A UNION ALL B
		||l_union_group_by_stmt||')	c))	a,'
		||l_where_stmt;