APPS.ISC_DBI_BACKORDER_TREND_PKG SQL Statements

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

Line: 52

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

Line: 97

	l_sql_stmt := 'SELECT	fii1.start_date  VIEWBY,';

Line: 111

   FROM	(SELECT	dates.start_date					START_DATE,
		sum(decode(mv.time_snapshot_date_id, dates.curr_day,
			   mv.backorder_line_cnt, NULL))		CURR_BKORD_LINES,
		sum(decode(mv.time_snapshot_date_id, dates.prev_day,
			   mv.backorder_line_cnt, NULL))		PREV_BKORD_LINES,
		count(distinct(decode(mv.time_snapshot_date_id, dates.curr_day,
				      decode(mv.item_id,''-'',null,mv.item_id),
				      null)))				CURR_BKORD_ITEMS,
		count(distinct(decode(mv.time_snapshot_date_id, dates.prev_day,
				      decode(mv.item_id,''-'',null,mv.item_id),
				      null)))				PREV_BKORD_ITEMS
	   FROM	(SELECT	curr.start_date	START_DATE,
			curr.day	CURR_DAY,
			prev.day	PREV_DAY
		   FROM	(SELECT start_date,
				day,
				rownum	ID
			   FROM
			(SELECT	fii1.start_date				START_DATE,
				     fii1.start_date			DAY
			   FROM	'||l_period_type||'		fii1
			  WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
						   AND &BIS_CURRENT_ASOF_DATE
			   )
			ORDER BY start_date DESC)		curr,
			(SELECT start_date,
				day,
				rownum	ID
			   FROM
			(SELECT	fii1.start_date				START_DATE,
				     fii1.start_date			DAY
			   FROM	'||l_period_type||'		fii1
			  WHERE	fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
						   AND &BIS_PREVIOUS_ASOF_DATE)
			ORDER BY start_date DESC)		prev
		  WHERE	curr.id = prev.id(+))			dates,
		ISC_DBI_FM_0007_MV 				mv
	  WHERE	mv.time_snapshot_date_id IN (dates.curr_day, dates.prev_day)
	    AND ((1=1'
		||l_inv_org_where
		||l_inv_cat_where
		||l_item_where
		||l_customer_where
		||')
	     OR	mv.inv_org_id IS NULL) -- snapshot taken but no data
	GROUP BY dates.start_date)	s,
	'||l_period_type||'		fii1
     WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
			   AND &BIS_CURRENT_ASOF_DATE
     AND	fii1.start_date = s.start_date(+)
     ORDER BY fii1.start_date';

Line: 163

	l_sql_stmt := 'SELECT	fii1.NAME   VIEWBY,';

Line: 177

   FROM	(SELECT	dates.start_date					START_DATE,
		sum(decode(mv.time_snapshot_date_id, dates.curr_day,
			   mv.backorder_line_cnt, NULL))		CURR_BKORD_LINES,
		sum(decode(mv.time_snapshot_date_id, dates.prev_day,
			   mv.backorder_line_cnt, NULL))		PREV_BKORD_LINES,
		count(distinct(decode(mv.time_snapshot_date_id, dates.curr_day,
				      decode(mv.item_id,''-'',null,mv.item_id),
				      null)))				CURR_BKORD_ITEMS,
		count(distinct(decode(mv.time_snapshot_date_id, dates.prev_day,
				      decode(mv.item_id,''-'',null,mv.item_id),
				      null)))				PREV_BKORD_ITEMS
	   FROM	(SELECT	curr.start_date	START_DATE,
			curr.day	CURR_DAY,
			prev.day	PREV_DAY
		   FROM	(SELECT start_date,
				day,
				rownum	ID
			   FROM
			(SELECT	fii1.start_date				START_DATE,
				max(mv.time_snapshot_date_id)		DAY
			   FROM	'||l_period_type||'		fii1,
				ISC_DBI_FM_0007_MV		mv
			  WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
						   AND &BIS_CURRENT_ASOF_DATE
			    AND	mv.time_snapshot_date_id (+) >= fii1.start_date
			    AND	mv.time_snapshot_date_id (+) <= fii1.end_date
			    AND	mv.time_snapshot_date_id (+) <= &BIS_CURRENT_ASOF_DATE
			GROUP BY fii1.start_date)
			ORDER BY start_date DESC)		curr,
			(SELECT start_date,
				day,
				rownum	ID
			   FROM
			(SELECT	fii1.start_date				START_DATE,
				max(mv.time_snapshot_date_id)		DAY
			   FROM	'||l_period_type||'		fii1,
				ISC_DBI_FM_0007_MV		mv
			  WHERE	fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
						   AND &BIS_PREVIOUS_ASOF_DATE
			    AND	mv.time_snapshot_date_id (+) >= fii1.start_date
			    AND	mv.time_snapshot_date_id (+) <= fii1.end_date
			    AND	mv.time_snapshot_date_id (+) <= &BIS_PREVIOUS_ASOF_DATE
			GROUP BY fii1.start_date)
			ORDER BY start_date DESC)		prev
		  WHERE	curr.id = prev.id(+))			dates,
		ISC_DBI_FM_0007_MV 				mv
	  WHERE	mv.time_snapshot_date_id IN (dates.curr_day, dates.prev_day)
	    AND ((1=1'
		||l_inv_org_where
		||l_inv_cat_where
		||l_item_where
		||l_customer_where
		||')
	     OR	mv.inv_org_id IS NULL) -- snapshot taken but no data
	GROUP BY dates.start_date)	s,
	'||l_period_type||'		fii1
     WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
			   AND &BIS_CURRENT_ASOF_DATE
     AND	fii1.start_date = s.start_date(+)
     ORDER BY fii1.start_date';