APPS.ISC_DBI_RETURN_REASON_PKG SQL Statements

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

Line: 23

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

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 = 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: 127

      l_cust_flag := 0; -- customer selected

Line: 140

 SELECT	ret.value				VIEWBY, -- return reason
	ret.id					VIEWBYID,
	c.prev_return				ISC_MEASURE_7, -- return value (prior)
	c.curr_return				ISC_MEASURE_1, -- return value
	(c.curr_return - c.prev_return)
	  / decode(c.prev_return, 0, NULL,
		   abs(c.prev_return)) * 100	ISC_MEASURE_2, -- change (return value),
	c.curr_return
	  / decode(sum(c.curr_return) over(), 0, NULL,
		   sum(c.curr_return) over())
	  * 100					ISC_MEASURE_3, -- Percent of Total
	c.lines_cnt				ISC_MEASURE_4, -- lines affected
	sum(c.curr_return) over()		ISC_MEASURE_5, -- grand total for return value
	(sum(c.curr_return) over() - sum(c.prev_return) over())
	  / decode(sum(c.prev_return) over(), 0, NULL,
		   abs(sum(c.prev_return) over()))
	  * 100					ISC_MEASURE_6, -- grand total for return value change
	sum(c.lines_cnt) over()			ISC_MEASURE_8 -- grand total for lines affected
   FROM (SELECT	mv.return_reason				REASON,
		sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			   mv.'||l_return_amt||', 0))		CURR_RETURN,
		sum(decode(cal.report_date, &BIS_PREVIOUS_ASOF_DATE,
			   mv.'||l_return_amt||', 0))		PREV_RETURN,
		sum(decode(cal.report_date, &BIS_CURRENT_ASOF_DATE,
			   mv.lines_cnt, 0))			LINES_CNT
	   FROM ISC_DBI_CFM_007_MV	mv,
		FII_TIME_RPT_STRUCT_V	cal'
		||l_prod_cat_from||'
	  WHERE mv.time_id = cal.time_id
	    AND mv.period_type_id = cal.period_type_id
	    AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id
	    AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
	    AND mv.customer_flag = :ISC_CUST_FLAG
	    AND mv.item_cat_flag = :ISC_ITEM_CAT_FLAG
	    AND mv.return_reason_flag = 0'
		||l_org_where||l_prod_cat_where||l_prod_where||l_cust_where||l_ret_reason_where||'
	GROUP BY mv.return_reason)	c,
	BIS_ORDER_ITEM_RET_REASON_V	ret
  WHERE c.reason = ret.id
 &ORDER_BY_CLAUSE NULLS LAST';