APPS.BIX_PMV_EMC_BKLG_PRTLT_PKG SQL Statements

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

Line: 102

select name VIEWBY,
nvl(sum(CURR_BACKLOG),0)                        BIX_EMC_BACKLOG,
nvl(sum(PREV_BACKLOG),0)                        BIX_EMC_PREVBACKLOG
from
(
/* Outer most iview .Uses lag to select prior values for the corresponding year*/
select cal.name,cal.start_date,
SUM(
	 CASE when (cal.start_date between &BIS_CURRENT_REPORT_START_DATE and &BIS_CURRENT_ASOF_DATE
	 	  and cal.report_date = least(cal.end_date,:l_period_to_bind))
		  then
		  	nvl(ACCUMULATED_OPEN_EMAILS,0)+nvl(ACCUMULATED_EMAILS_IN_QUEUE,0)
		  else
		  	   null
		  end
   ) CURR_BACKLOG
,lag(
	SUM(
	 CASE WHEN (cal.start_date between &BIS_PREVIOUS_REPORT_START_DATE and &BIS_PREVIOUS_ASOF_DATE
	 	  and cal.report_date = least(cal.end_date,&BIS_PREVIOUS_ASOF_DATE ))
		  then
		  		nvl(ACCUMULATED_OPEN_EMAILS,0)+nvl(ACCUMULATED_EMAILS_IN_QUEUE,0)
		  else
		  	    null
		  end
		 )
) over (order by cal.start_date) PREV_BACKLOG
from
(
/* Selects measures for all years in time range from previous report start date to current as of date*/
   select fii604.name,fii604.start_date,fii604.end_date,cal.report_Date,cal.period_type_id,cal.time_id
   from fii_time_ent_year fii604, fii_time_rpt_struct cal
   where
   fii604.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
   and cal.report_date between fii604.start_date and fii604.end_date
   and cal.report_date in (least(fii604.end_date, :l_period_to_bind) , &BIS_PREVIOUS_ASOF_DATE)
   AND  bitAND(cal.record_type_id,:l_period_type_id ) = cal.record_type_id
   order by fii604.sequence
)cal,(
	   select period_type_id,time_id,ACCUMULATED_OPEN_EMAILS,ACCUMULATED_EMAILS_IN_QUEUE
	   from bix_Email_Details_mv
	   where period_type_id=:l_period_type_id
	   and row_type=:l_row_type '|| l_where_clause || '
		)mv
where mv.time_id(+)=cal.time_id
group by cal.name,cal.start_date
) recset /*End of outermost view */
WHERE recset.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND &BIS_CURRENT_ASOF_DATE
group by name';

Line: 160

SELECT fii604.name                                 VIEWBY,
nvl(sum(CURR_BACKLOG),0)                        BIX_EMC_BACKLOG,
nvl(sum(PREV_BACKLOG),0)                        BIX_EMC_PREVBACKLOG
FROM
(
   SELECT fii604.sequence                             SEQUENCE,
   SUM( CASE when
   (
   fii604.start_date between &BIS_CURRENT_REPORT_START_DATE
   and &BIS_CURRENT_ASOF_DATE
   )
        then
        nvl(ACCUMULATED_OPEN_EMAILS,0)+nvl(ACCUMULATED_EMAILS_IN_QUEUE,0)
        else
        NULL
        end
   ) CURR_BACKLOG,
   SUM( CASE when
   (
   fii604.start_date between &BIS_PREVIOUS_REPORT_START_DATE
   and &BIS_PREVIOUS_ASOF_DATE
   )
   then
   nvl(ACCUMULATED_OPEN_EMAILS,0)+nvl(ACCUMULATED_EMAILS_IN_QUEUE,0)
   else
   NULL
   end
   ) PREV_BACKLOG
FROM  '||l_period_type||'	fii604,
      bix_email_details_mv eml
WHERE eml.time_id = to_char
(
   least
      (
         fii604.end_date,
         (
         CASE
         WHEN
         :l_period_to_bind BETWEEN fii604.start_date AND fii604.end_date
         THEN
         :l_period_to_bind
         ELSE
         fii604.end_date
         END
         )
      )
,''J'')
AND fii604.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE AND
                           &BIS_CURRENT_ASOF_DATE
AND eml.row_type = :l_row_type
AND eml.period_type_id = :l_period_type_id ';

Line: 229

SELECT fii604.name                                 VIEWBY,
nvl(sum(CURR_BACKLOG),0)                        BIX_EMC_BACKLOG,
NULL                                            BIX_EMC_PREVBACKLOG
FROM
(
   SELECT fii604.name                             NAME,
   SUM(
        nvl(ACCUMULATED_OPEN_EMAILS,0)+nvl(ACCUMULATED_EMAILS_IN_QUEUE,0)
   )    CURR_BACKLOG,
   NULL PREV_BACKLOG
FROM  '||l_period_type||'	fii604,
      bix_email_details_mv eml
WHERE eml.time_id = to_char
(
   least
      (
         fii604.end_date,
         (
         CASE
         WHEN
         :l_period_to_bind BETWEEN fii604.start_date AND fii604.end_date
         THEN
         :l_period_to_bind
         ELSE
         fii604.end_date
         END
         )
      )
,''J'')
AND fii604.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE AND
                           &BIS_CURRENT_ASOF_DATE
AND eml.row_type = :l_row_type
AND eml.period_type_id = :l_period_type_id ';