APPS.ISC_DBI_RETURN_VALUE_TREND_PKG SQL Statements

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

Line: 29

  l_cust_flag		NUMBER; -- 0 for customer and 1 for no customer selected

Line: 84

		(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: 133

      l_cust_flag := 0; -- customer selected

Line: 187

SELECT	fii.name				VIEWBY,
	s.pre_return_amt			ISC_MEASURE_2, -- prev return value
	s.cur_return_amt			ISC_MEASURE_1, -- curr return value
	(s.cur_return_amt - s.pre_return_amt)
	  / decode( s.pre_return_amt,0,
		    NULL,
		    abs(s.pre_return_amt)) * 100	ISC_MEASURE_3 -- return value change
   FROM (SELECT dates.start_date						START_DATE,
		sum(decode(dates.period, ''C'', fact.'||l_return_amt||', 0))	CUR_RETURN_AMT,
		sum(decode(dates.period, ''P'', fact.'||l_return_amt||', 0))	PRE_RETURN_AMT
	   FROM	(SELECT	fii.start_date					START_DATE,
			''C''						PERIOD,
			least(fii.end_date, &BIS_CURRENT_ASOF_DATE)	REPORT_DATE
		   FROM	'||l_period_type||'	fii
		  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
					   AND &BIS_CURRENT_ASOF_DATE
		UNION ALL
		 SELECT	p2.start_date					START_DATE,
			''P''						PERIOD,
			p1.report_date					REPORT_DATE
		   FROM	(SELECT	least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE)	REPORT_DATE,
				rownum						ID
			   FROM	'||l_period_type||'	fii
			  WHERE	fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
						   AND &BIS_PREVIOUS_ASOF_DATE
			  ORDER BY fii.start_date DESC) p1,
			(SELECT	fii.start_date					START_DATE,
				rownum						ID
			   FROM	'||l_period_type||'	fii
			  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
						   AND &BIS_CURRENT_ASOF_DATE
			  ORDER BY fii.start_date DESC) p2
		  WHERE	p1.id(+) = p2.id)						dates,
		ISC_DBI_CFM_'||l_mv||'_MV			fact,
		FII_TIME_RPT_STRUCT_V			cal'
		||l_prod_cat_from||'
	  WHERE	cal.report_date = dates.report_date
	    AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id
	    AND fact.time_id = cal.time_id
	    AND fact.period_type_id = cal.period_type_id'
	||l_flags_where||l_org_where||l_prod_cat_where||l_prod_where||l_cust_where||l_ret_reason_where||'
    	  GROUP BY dates.start_date) 	s,
		'||l_period_type||' 		fii
  WHERE fii.start_date = s.start_date(+)
    AND fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
			   AND &BIS_CURRENT_ASOF_DATE
  ORDER BY fii.start_date ';