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

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

Line: 21


  l_mon_period_id	NUMBER:=32; --only select month buckets

Line: 27


  l_mon_num		NUMBER; -- number of months in the selected period

Line: 83


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

Line: 131


SELECT	0	VIEWBY,
	0	VIEWBYID,
	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_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_16,
	0 	ISC_MEASURE_17,
	0 	ISC_MEASURE_18
  FROM	dual
 WHERE	1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';

Line: 160


	FROM(select (rank() over (&ORDER_BY_CLAUSE nulls last,viewby_id))-1 rnk,
		viewby_id,
		ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,
		sum(plan_mds) over ()*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
		decode(sign(sum(plan_begin_inv) over()+sum(plan_end_inv) over()),0,null,-1,null,
		(sum(plan_begin_inv) over()+sum(plan_end_inv) over())/2/:ISC_MON_NUM) ISC_MEASURE_4,
		sum(comp_mds) over ()*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
		decode(sign(sum(comp_begin_inv) over()+sum(comp_end_inv) over()),0,null,-1,null,
		(sum(comp_begin_inv) over()+sum(comp_end_inv) over())/2/:ISC_MON_NUM) ISC_MEASURE_5,
		ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,
		sum(plan_ontime_total) over ()/decode(sum(plan_total_lines) over(),0,null,
		sum(plan_total_lines) over())*100	ISC_MEASURE_10,
		sum(comp_ontime_total) over ()/decode(sum(comp_total_lines) over(),0,null,
		sum(comp_total_lines) over())*100	ISC_MEASURE_11,
		ISC_MEASURE_13,ISC_MEASURE_14,ISC_MEASURE_15,
		sum(plan_required_hr_total) over()/decode(sum(plan_available_hr_total) over(),0,null,
			sum(plan_available_hr_total)over())*100	ISC_MEASURE_16,
		sum(comp_required_hr_total) over()/decode(sum(comp_available_hr_total) over(),0,null,
			sum(comp_available_hr_total)over())*100	ISC_MEASURE_17
		FROM (select c.viewby_id,
		sum(c.plan_inv_turns)					ISC_MEASURE_1,
		sum(c.comp_inv_turns)					ISC_MEASURE_2,
		sum(c.plan_inv_turns)-sum(comp_inv_turns)		ISC_MEASURE_3,
		sum(c.plan_mds_total)					plan_mds,
		sum(c.plan_begin_inv_total)				plan_begin_inv,
		sum(c.plan_end_inv_total)				plan_end_inv,
		sum(c.comp_mds_total)					comp_mds,
		sum(c.comp_begin_inv_total)				comp_begin_inv,
		sum(c.comp_end_inv_total)				comp_end_inv,
		sum(c.plan_ontime_lines)/decode(sum(c.plan_total_lines),0,null,
			sum(c.plan_total_lines))*100			ISC_MEASURE_7,
		sum(c.comp_ontime_lines)/decode(sum(c.comp_total_lines),0,null,
			sum(c.comp_total_lines))*100			ISC_MEASURE_8,
		(sum(c.plan_ontime_lines)/decode(sum(c.plan_total_lines),0,null,
			sum(c.plan_total_lines)))*100-
		(sum(c.comp_ontime_lines)/decode(sum(c.comp_total_lines),0,null,
			sum(c.comp_total_lines)))*100			ISC_MEASURE_9,
		sum(c.plan_ontime_lines) 				plan_ontime_total,
		sum(c.plan_total_lines)					plan_total_lines,
		sum(c.comp_ontime_lines)				comp_ontime_total,
		sum(c.comp_total_lines)					comp_total_lines,
		sum(c.plan_required_hr)/decode(sum(c.plan_available_hr),0,null,
			sum(c.plan_available_hr))*100			ISC_MEASURE_13,
		sum(c.comp_required_hr)/decode(sum(c.comp_available_hr),0,null,
			sum(c.comp_available_hr))*100			ISC_MEASURE_14,
		(sum(c.plan_required_hr)/decode(sum(c.plan_available_hr),0,null,
			sum(c.plan_available_hr)))*100-
		(sum(c.comp_required_hr)/decode(sum(c.comp_available_hr),0,null,
			sum(c.comp_available_hr)))*100			ISC_MEASURE_15,
		sum(c.plan_required_hr) 				plan_required_hr_total,
		sum(c.plan_available_hr) 				plan_available_hr_total,
		sum(c.comp_required_hr)					comp_required_hr_total,
		sum(c.comp_available_hr) 				comp_available_hr_total
		FROM (
		SELECT s.viewby_id,
		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
		sum(s.mds)*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
		decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
		sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) 	plan_inv_turns,
		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
		sum(s.mds)*365/(:ISC_CUR_END - :ISC_CUR_START+1)/
		decode(sign(sum(s.begin_inv+s.end_inv)),0,null,-1,null,
		sum(s.begin_inv+s.end_inv)/2/:ISC_MON_NUM),null) 	comp_inv_turns,
		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
		sum(s.mds),null)					plan_mds_total,
		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
		sum(s.begin_inv),null)					plan_begin_inv_total,
		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
		sum(s.end_inv),null)					plan_end_inv_total,
		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
		sum(s.mds),null)					comp_mds_total,
		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
		sum(s.begin_inv),null)					comp_begin_inv_total,
		decode(s.plan_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
		sum(s.end_inv),null)					comp_end_inv_total,
		null		plan_ontime_lines,
		null		plan_total_lines,
		null		comp_ontime_lines,
		null		comp_total_lines,
		null		plan_required_hr,
		null		plan_available_hr,
		null		comp_required_hr,
		null		comp_available_hr
		FROM
		(SELECT f.organization_id	VIEWBY_ID,
		dates.start_date		PERIOD,
		f.snapshot_id			PLAN_ID,
		sum(decode(dates.period_type,''P'',f.inventory_cost_g,0))	begin_inv,
		sum(decode(dates.period_type,''C'',f.inventory_cost_g,0))	end_inv,
		sum(decode(dates.period_type,''C'',f.mds_cost_g,0))	mds
		FROM
		(SELECT fii.start_date	START_DATE,
			fii.start_date  REPORT_DATE,
			''C''		PERIOD_TYPE
		 FROM FII_TIME_ENT_PERIOD fii
		 WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
		UNION ALL
		SELECT 	cur.start_date	start_date,
			pre.start_date  report_date,
			''P''		period_type
		FROM
		(SELECT fii.start_date	START_DATE,
		 rownum			ID
		 FROM FII_TIME_ENT_PERIOD fii
		 WHERE fii.start_date between :ISC_PRE_START and :ISC_PRE_END
		 ORDER by fii.start_date DESC)		pre,
		(SELECT fii.start_date	START_DATE,
		 rownum			ID
		 FROM FII_TIME_ENT_PERIOD fii
		 WHERE fii.start_date between :ISC_CUR_START and :ISC_CUR_END
		 ORDER by fii.start_date DESC)		cur
		WHERE cur.id = pre.id(+))	dates,
		ISC_DBI_PM_0001_MV f
		WHERE f.start_date = dates.report_date
		AND f.period_type_id = :ISC_MON_PERIOD_ID
		AND f.union1_flag <>:ISC_UNION_FLAG
		AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
		AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
		GROUP BY f.organization_id,dates.start_date,f.snapshot_id) s
		GROUP BY s.viewby_id,s.plan_id
		UNION ALL
		SELECT f.organization_id	VIEWBY_ID,
		null	plan_inv_turns,
		null	comp_inv_turns,
		null	plan_mds_total,
		null	plan_begin_inv_total,
		null	plan_end_inv_total,
		null	comp_mds_total,
		null	comp_begin_inv_total,
		null	comp_end_inv_total,
		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
		sum(f.total_lines)-sum(f.late_lines),null)		plan_ontime_lines,
		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
		sum(f.total_lines),null)				plan_total_lines,
		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
		sum(f.total_lines)-sum(f.late_lines),null)		comp_ontime_lines,
		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
		sum(f.total_lines),null)				comp_total_lines,
		null		plan_required_hr,
		null		plan_available_hr,
		null		comp_required_hr,
		null		comp_available_hr
		FROM
		ISC_DBI_PM_0001_MV f
		WHERE f.start_date = :ISC_CUR_START
		AND f.period_type_id = :ISC_PERIOD_TYPE_ID
		AND f.union2_flag <>:ISC_UNION_FLAG
		AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
		AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
		GROUP BY f.organization_id,f.snapshot_id
		UNION ALL
		SELECT f.organization_id	VIEWBY_ID,
		null	plan_inv_turns,
		null	comp_inv_turns,
		null	plan_mds_total,
		null	plan_begin_inv_total,
		null	plan_end_inv_total,
		null	comp_mds_total,
		null	comp_begin_inv_total,
		null	comp_end_inv_total,
		null	plan_ontime_lines,
		null	plan_total_lines,
		null	comp_ontime_lines,
		null	comp_total_lines,
		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
		sum(f.required_hours),null)	plan_required_hr,
		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
		sum(f.available_hours),null)	plan_available_hr,
		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
		sum(f.required_hours),null)	comp_required_hr,
		decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
		sum(f.available_hours),null)	comp_available_hr
		FROM
		ISC_DBI_PM_0002_MV f
		WHERE f.start_date = :ISC_CUR_START
		AND f.period_type_id = :ISC_PERIOD_TYPE_ID
		AND f.res_gp_flag =:ISC_RES_GP_FLAG
		AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
		GROUP BY f.organization_id,f.snapshot_id) c
		'||l_org_where||'
		GROUP BY c.viewby_id)
		WHERE (ISC_MEASURE_1 is not null OR ISC_MEASURE_2 is not null
			OR ISC_MEASURE_7 is not null OR ISC_MEASURE_7 is not null
			OR ISC_MEASURE_13 is not null OR ISC_MEASURE_14 is not null)) a,';

Line: 345


  l_stmt := 'SELECT org.name				VIEWBY,
		org.organization_id			VIEWBYID,
		'||l_inner_sql||'
		HR_ALL_ORGANIZATION_UNITS_TL org
		WHERE org.organization_id = a.viewby_id
		AND org.language = :ISC_LANG
		AND ((a.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX=-1))
		ORDER BY rnk';