APPS.ISC_DBI_REV_CUM_TREND_PKG SQL Statements

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

Line: 133

  SELECT report_date_julian INTO l_day_id1 FROM fii_time_day WHERE report_date = l_curr_start;

Line: 134

  SELECT report_date_julian INTO l_day_id2 FROM fii_time_day WHERE report_date = l_curr_end;

Line: 137

  SELECT report_date_julian INTO l_day_id1 FROM fii_time_day WHERE report_date = l_prior_start;

Line: 138

  SELECT report_date_julian INTO l_day_id2 FROM fii_time_day WHERE report_date = l_prior_end;

Line: 183

    ELSE -- view by sales group, prod.cat selected
      l_prod_cat_where :='
		AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';

Line: 222

SELECT	0 	ISC_MEASURE_2,
	0	ISC_MEASURE_1,
	0 	ISC_MEASURE_4,
	0 	ISC_MEASURE_3

  FROM	dual
 WHERE	1 = 2';

Line: 245

SELECT	SUBSTR(month_name,1,3)								VIEWBY,
	SUM(P_NET_BOOK)	OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING)	ISC_MEASURE_2,
	CASE WHEN c_net_book IS NULL THEN to_number(NULL)
	     ELSE SUM(C_NET_BOOK) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING)
	     END									ISC_MEASURE_1,
	SUM(P_REV) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING)		ISC_MEASURE_4,
	CASE WHEN c_rev IS NULL THEN to_number(NULL)
	     ELSE SUM(C_REV) OVER (ORDER BY FII_EFFECTIVE_NUM ROWS UNBOUNDED PRECEDING)
	     END									ISC_MEASURE_3

 FROM
(SELECT	MAX(month_name)			MONTH_NAME,
	FII_EFFECTIVE_NUM		FII_EFFECTIVE_NUM,
	SUM(C_NET_BOOK)			C_NET_BOOK,
	SUM(P_NET_BOOK)			P_NET_BOOK,
	SUM(C_REV)			C_REV,
	SUM(P_REV)			P_REV
  FROM (
	SELECT	per.sequence 		FII_EFFECTIVE_NUM,
		per.name 		MONTH_NAME,
		per.ent_period_id 	ID,
   		NULL 			C_NET_BOOK,
		   (CASE WHEN per.end_date <= :ISC_PRIOR_END
			 THEN f.net_booked_amt_'||l_curr_suffix||'
			 ELSE to_number(NULL) END
		   ) 			P_NET_BOOK,
   		NULL 			C_REV,
		   (CASE WHEN per.end_date <= :ISC_PRIOR_END
			 THEN f.recognized_amt_'||l_curr_suffix||'
			 ELSE to_number(NULL) END
		   ) 			P_REV
	  FROM	FII_TIME_ENT_PERIOD   		per,
		'||l_mv||'  		f'
		||l_prod_cat_from||'
	 WHERE	per.ent_period_id = f.time_id
	   AND	per.start_date >= :ISC_PRIOR_START
	   AND	per.end_date   <= :ISC_PRIOR_END
	   AND	f.period_type_id = 32'
    		||l_flags
		||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
     UNION ALL
	(
	 SELECT	per.sequence 		FII_EFFECTIVE_NUM,
		per.name 		MONTH_NAME,
		per.ent_period_id 	ID,
		   (CASE WHEN per.start_date >= :ISC_CURR_START
			  AND per.end_date < &BIS_CURRENT_ASOF_DATE
                	 THEN f.net_booked_amt_'||l_curr_suffix||'
  		  	 ELSE to_number(NULL) END
		   )  			C_NET_BOOK,
		0 			P_NET_BOOK,
		   (CASE WHEN per.start_date >= :ISC_CURR_START
			  AND per.end_date < &BIS_CURRENT_ASOF_DATE
               		 THEN f.recognized_amt_'||l_curr_suffix||'
		    	 ELSE to_number(NULL) END
		   ) 			C_REV,
		0 			P_REV
  	  FROM	FII_TIME_ENT_PERIOD   		per,
		'||l_mv||'  		f'
		||l_prod_cat_from||'
	 WHERE	per.ent_period_id = f.time_id
	   AND	per.start_date >= :ISC_CURR_START
	   AND	per.end_date   < &BIS_CURRENT_ASOF_DATE
	   AND	f.period_type_id = 32'
		||l_flags
		||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
     UNION ALL
	 SELECT	per.sequence 		FII_EFFECTIVE_NUM,
		per.name 		MONTH_NAME,
		per.ent_period_id 	ID,
		f.net_booked_amt_'||l_curr_suffix||'	C_NET_BOOK,
		0 					P_NET_BOOK,
		f.recognized_amt_'||l_curr_suffix||'	C_REV,
		0 					P_REV
  	  FROM	FII_TIME_RPT_STRUCT_V   	cal,
		FII_TIME_ENT_PERIOD		per,
		'||l_mv||'  		f'
		||l_prod_cat_from||'
	 WHERE	cal.time_id = f.time_id
	   AND	cal.report_date between per.start_date and per.end_date
	   AND	cal.report_date = &BIS_CURRENT_ASOF_DATE
	   AND	bitand(cal.record_type_id, 23) = cal.record_type_id'
		||l_flags
		||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
     UNION ALL
	SELECT  per.sequence 		FII_EFFECTIVE_NUM,
		per.name 		MONTH_NAME,
		per.ent_period_id 	ID,
   		CASE WHEN per.end_date > :ISC_TEMP
		     THEN to_number(NULL)
		     ELSE 0 END		C_NET_BOOK,
		0  			P_NET_BOOK,
   		CASE WHEN per.end_date > :ISC_TEMP
		     THEN to_number(NULL)
		     ELSE 0 END		C_REV,
		0  			P_REV
	  FROM	FII_TIME_ENT_PERIOD per
         WHERE	per.start_date >= :ISC_CURR_START
	   AND	per.end_date   <= :ISC_CURR_END
	))
GROUP BY FII_EFFECTIVE_NUM
ORDER BY FII_EFFECTIVE_NUM
)';

Line: 351

SELECT  days					VIEWBY,
	SUM(DECODE(SIGN(report_date - &BIS_CURRENT_ASOF_DATE),
		   1, NULL, C_NET_BOOK))	ISC_MEASURE_1,
	SUM(DECODE(SIGN(report_date - :ISC_PRIOR_END),
		   1, NULL, P_NET_BOOK)) 	ISC_MEASURE_2,
	SUM(DECODE(SIGN(report_date - &BIS_CURRENT_ASOF_DATE),
		   1, NULL, C_REV))		ISC_MEASURE_3,
	SUM(DECODE(SIGN(report_date - :ISC_PRIOR_END),
		   1, NULL, P_REV)) 		ISC_MEASURE_4
  FROM (
	SELECT	g.report_date - :ISC_CURR_START + to_number('||l_adjust1||')
						DAYS,
		report_date,
		NVL(SUM(SUM(f.c_book_xtd)) OVER
		   (ORDER BY g.report_date - :ISC_CURR_START + to_number('||l_adjust1||')
		   ROWS UNBOUNDED PRECEDING),0) C_NET_BOOK,
		0 				P_NET_BOOK,
		NVL(SUM(SUM(f.c_rev_xtd)) OVER
		   (ORDER BY g.report_date - :ISC_CURR_START + to_number('||l_adjust1||')
 		   ROWS UNBOUNDED PRECEDING),0)	C_REV,
		0				P_REV
	  FROM	FII_TIME_DAY	g,
		(SELECT time_id,
			net_booked_amt_'||l_curr_suffix||'	C_BOOK_XTD,
			recognized_amt_'||l_curr_suffix||'	C_REV_XTD
		   FROM	'||l_mv||'  	f'
			||l_prod_cat_from||'
	 	  WHERE	f.period_type_id (+) = 1'
			||l_flags
			||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
		)  		f
	 WHERE	g.report_date_julian  = f.time_id (+)
	   AND	'||l_id||'
      GROUP BY	g.report_date - :ISC_CURR_START + to_number('||l_adjust1||'),
		report_date
  UNION ALL
	SELECT	g.report_date - :ISC_PRIOR_START + to_number('||l_adjust2||')
						DAYS,
		report_date,
		to_number(NULL) 		C_NET_BOOK,
		NVL(SUM(SUM(f.p_book_xtd)) OVER
		   (ORDER BY g.report_date - :ISC_PRIOR_START + to_number('||l_adjust2||')
		   ROWS UNBOUNDED PRECEDING),0)	P_NET_BOOK,
		to_number(NULL) 		C_REV,
		NVL(SUM(SUM(f.p_rev_xtd)) OVER
		   (ORDER BY g.report_date-:ISC_PRIOR_START+to_number('||l_adjust2||')
		   ROWS UNBOUNDED PRECEDING),0)	P_REV
	  FROM	FII_TIME_DAY	g,
		(SELECT	time_id,
			net_booked_amt_'||l_curr_suffix||'	P_BOOK_XTD,
			recognized_amt_'||l_curr_suffix||'	P_REV_XTD
		   FROM	'||l_mv||'  	f'
			||l_prod_cat_from||'
	 	  WHERE	f.period_type_id (+) = 1'
			||l_flags
			||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
		)		f
	 WHERE	g.report_date_julian  = f.time_id (+)
	   AND	'||l_id2||'
      GROUP BY	g.report_date - :ISC_PRIOR_START + to_number('||l_adjust2||'),
		report_date
	)
GROUP BY days
ORDER BY days';