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.OKI_DBI_SRM_BKRNWL_PVT SQL Statements

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

Line: 55


    l_viewby_select      VARCHAR2(10000);

Line: 56


    l_url_select         VARCHAR2(20000);

Line: 106


    l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(l_view_by, 'SRM', '6.0');

Line: 118


    l_viewby_select  :=  l_viewby_select ||
   ', OKI_DYNAMIC_URL_1 ,'|| l_prodcat_url ||',oki_measure_1 ,oki_measure_2 ,oki_calc_item1, oki_measure_3
    ,oki_measure_4, oki_calc_item2,oki_calc_item2 oki_calc_item4, oki_measure_5 ,oki_measure_6 ,oki_calc_item3
    , oki_calc_item3 oki_calc_item5, oki_measure_11 ,oki_measure_12 ,oki_measure_13 ,oki_measure_14
    ,oki_measure_15,oki_measure_16 ,oki_measure_17, oki_measure_18, oki_measure_19
     FROM (
     SELECT rank() over (&ORDER_BY_CLAUSE nulls last , '||l_view_by_col||') - 1 rnk ,'||l_view_by_col||'
    ,OKI_DYNAMIC_URL_1 ,oki_measure_1 ,oki_measure_2 ,oki_calc_item1 ,oki_measure_3 ,oki_measure_4 ,oki_calc_item2
    ,oki_measure_5 ,oki_measure_6 ,oki_calc_item3 ,oki_measure_11 ,oki_measure_12 ,oki_measure_13 ,oki_measure_14
    ,oki_measure_15 ,oki_measure_16,oki_measure_17,oki_measure_18, oki_measure_19
     FROM ( ';

Line: 133


    l_url_select     :=
          'SELECT  decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_BTS_RATE_DRPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1 ';

Line: 137


    l_url_select := ' SELECT NULL OKI_DYNAMIC_URL_1 ';

Line: 207


    l_query := l_viewby_select || l_url_select ||' ,'||
    -- Generate sql query
       l_view_by_col || ',' ||
      'oset10.booked_val OKI_MEASURE_1,
       oset10.start_val OKI_MEASURE_2,
       oset10.val_rate OKI_CALC_ITEM1,
       oset10.booked_lcount OKI_MEASURE_3,
       oset10.start_lcount OKI_MEASURE_4,
       oset10.lcount_rate OKI_CALC_ITEM2,
       oset10.booked_hcount OKI_MEASURE_5,
       oset10.start_hcount OKI_MEASURE_6,
       oset10.hcount_rate OKI_CALC_ITEM3,
       oset10.booked_val_tot OKI_MEASURE_11,
       oset10.start_val_tot OKI_MEASURE_12,
       oset10.val_rate_tot OKI_MEASURE_13,
       oset10.booked_lcount_tot OKI_MEASURE_14,
       oset10.start_lcount_tot OKI_MEASURE_15,
       oset10.lcount_rate_tot OKI_MEASURE_16,
       oset10.booked_hcount_tot OKI_MEASURE_17,
       oset10.start_hcount_tot OKI_MEASURE_18,
       oset10.hcount_rate_tot OKI_MEASURE_19
   FROM
   (
     SELECT '|| l_view_by_col || ',
         nvl(oset05.booked_val,0) booked_val,
         nvl(oset05.starting_val,0) start_val,
         oset05.booked_val/decode(oset05.starting_val,0,NULL,oset05.starting_val) val_rate,
         nvl(oset05.booked_lcount,0) booked_lcount,
         nvl(oset05.starting_lcount,0) start_lcount,
         oset05.booked_lcount /decode( oset05.starting_lcount,0,NULL,oset05.starting_lcount) lcount_rate,
         nvl(oset05.booked_hcount,0) booked_hcount,
         nvl(oset05.starting_hcount,0) start_hcount,
         oset05.booked_hcount /decode( oset05.starting_hcount,0,NULL,oset05.starting_hcount) hcount_rate,
         nvl(oset05.booked_val_tot,0) booked_val_tot,
         nvl(oset05.starting_val_tot,0) start_val_tot,
         oset05.booked_val_tot/decode(oset05.starting_val_tot,0,NULL,oset05.starting_val_tot) val_rate_tot,
         nvl(oset05.booked_lcount_tot,0) booked_lcount_tot,
         nvl(oset05.starting_lcount_tot,0) start_lcount_tot,
         oset05.booked_lcount_tot /decode( oset05.starting_lcount_tot,0,NULL,oset05.starting_lcount_tot) lcount_rate_tot,
         nvl(oset05.booked_hcount_tot,0) booked_hcount_tot,
         nvl(oset05.starting_hcount_tot,0) start_hcount_tot,
         oset05.booked_hcount_tot /decode( oset05.starting_hcount_tot,0,NULL,oset05.starting_hcount_tot) hcount_rate_tot
     FROM
      ('||
      		get_book_start_2way_sql( l_book_where,
		                        l_start_where,
				        l_viewby_col_special,
				        l_view_by_col,
				        l_cur_suffix )
        || l_VIEWBY_RANK_ORDER;

Line: 272


    l_select  varchar2(32767);

Line: 280


    l_select := 'sum(booked_val)          booked_val,
                 sum(booked_hcount)       booked_hcount,
                 sum(booked_lcount)       booked_lcount,
                 sum(booked_val_tot)      booked_val_tot,
                 sum(booked_hcount_tot)   booked_hcount_tot,
                 sum(booked_lcount_tot)   booked_lcount_tot,
                 sum(starting_val)        starting_val,
                 sum(starting_hcount)     starting_hcount,
                 sum(starting_lcount)     starting_lcount,
                 sum(starting_val_tot)    starting_val_tot,
                 sum(starting_hcount_tot) starting_hcount_tot,
                 sum(starting_lcount_tot) starting_lcount_tot ';

Line: 294


     ' SELECT '|| l_viewby_col_special ||l_view_by_col ||',
          NVL(SUM(fact.price_nego_' || l_cur_suffix || '),0) booked_val,
          NVL(COUNT(distinct(fact.chr_id)),0) booked_hcount,
          NVL(COUNT(distinct(fact.cle_id)),0) booked_lcount,
          NVL(SUM(SUM(fact.price_nego_' || l_cur_suffix || ')) over (),0) booked_val_tot,
          NVL(SUM(COUNT(distinct(fact.chr_id))) over (),0) booked_hcount_tot,
	  NVL(SUM(COUNT(distinct(fact.cle_id))) over (),0) booked_lcount_tot,
	  to_number(null) starting_val,
	  to_number(null) starting_hcount,
	  to_number(null) starting_lcount,
	  to_number(null) starting_val_tot,
	  to_number(null) starting_hcount_tot,
	  to_number(null) starting_lcount_tot '||
          l_book_where;

Line: 310


     ' SELECT '|| l_viewby_col_special ||l_view_by_col ||',
          to_number(null) booked_val,
          to_number(null) booked_hcount,
          to_number(null) booked_lcount,
          to_number(null) booked_val_tot,
          to_number(null) booked_hcount_tot,
          to_number(null) booked_lcount_tot,
          NVL(SUM(fact.price_nego_' || l_cur_suffix || '),0) starting_val,
          NVL(COUNT(distinct(fact.chr_id)),0) starting_hcount,
          NVL(COUNT(distinct(fact.cle_id)),0) starting_lcount,
          NVL(SUM(SUM(fact.price_nego_' || l_cur_suffix || ')) over (),0) starting_val_tot,
          NVL(SUM(COUNT(distinct(fact.chr_id))) over (),0) starting_hcount_tot,
          NVL(SUM(COUNT(distinct(fact.cle_id))) over (),0) starting_lcount_tot '||
          l_start_where;

Line: 328


   l_query := oki_dbi_util_pvt.two_way_join (l_select, l_query1, l_query2,l_join_column1, l_join_column1);

Line: 675


      'SELECT cal_NAME AS VIEWBY
          , (uset.p_booked) / DECODE (uset.p_started, 0, NULL, uset.p_started)
					            oki_measure_1
          , (uset.c_booked) / DECODE (uset.c_started, 0, NULL, uset.c_started)
                oki_measure_2
          , (uset.c_booked / DECODE (uset.c_started, 0, NULL, uset.c_started))
            -(uset.p_booked / DECODE (uset.p_started, 0, NULL, uset.p_started))
              oki_measure_3';

Line: 687


  Booking to Renewal Activity SQL Select clause

*******************************************************************/

  FUNCTION get_table_sel_clause (
  	p_view_by_dim 	IN 	VARCHAR2
      , p_view_by_col		IN 	VARCHAR2)
      RETURN VARCHAR2
  IS
    l_sel_clause	 VARCHAR2 (10000);

Line: 699


    l_viewby_select      VARCHAR2(10000);

Line: 700


    l_url_select         VARCHAR2(10000);

Line: 703


    l_viewby_select := oki_dbi_util_pvt.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');

Line: 718


   l_viewby_select  :=  l_viewby_select ||
      ', OKI_DYNAMIC_URL_1 ,OKI_DYNAMIC_URL_3, '|| l_prodcat_url || '
      ,OKI_MEASURE_1 ,OKI_MEASURE_2 ,OKI_CALC_ITEM1 ,OKI_CALC_ITEM2
      ,OKI_CALC_ITEM3 ,OKI_CALC_ITEM4 ,OKI_CALC_ITEM5 ,OKI_CALC_ITEM6,oki_measure_3
      ,OKI_MEASURE_4, oki_measure_5, OKI_CALC_ITEM7, OKI_CALC_ITEM8,OKI_CALC_ITEM17
      ,OKI_CALC_ITEM18, oki_measure_15, OKI_MEASURE_18, OKI_MEASURE_19
       FROM
       (SELECT
           rank() over (&ORDER_BY_CLAUSE nulls last,
	   '||p_view_by_col||') - 1 rnk
	   ,'||p_view_by_col||',OKI_DYNAMIC_URL_1
	   ,OKI_DYNAMIC_URL_3 ,OKI_MEASURE_1 ,OKI_MEASURE_2
	   ,OKI_CALC_ITEM1 ,OKI_CALC_ITEM2 ,OKI_CALC_ITEM3
	   ,OKI_CALC_ITEM4 ,OKI_CALC_ITEM5 ,OKI_CALC_ITEM6,oki_measure_3,OKI_MEASURE_4
	   ,'||POA_DBI_UTIL_PKG.rate_clause('oki_measure_2','oki_calc_item4') || ' oki_measure_5
	   ,OKI_CALC_ITEM7, OKI_CALC_ITEM8,OKI_CALC_ITEM17, OKI_CALC_ITEM18,
           SUM( '||POA_DBI_UTIL_PKG.rate_clause('oki_measure_2','oki_calc_item4') || ') over() oki_measure_15
	   ,OKI_CALC_ITEM8 OKI_MEASURE_18, OKI_CALC_ITEM18 OKI_MEASURE_19
       FROM ( ';

Line: 743


       l_url_select :=
          'SELECT  decode(resource_id,-999,''pFunctionName=OKI_DBI_SRM_BTS_RATIO_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_DYNAMIC_URL_1' ||
       ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_bookings_url||')) OKI_DYNAMIC_URL_3 ';

Line: 748


       l_url_select :=
           ' SELECT NULL OKI_DYNAMIC_URL_1
          , '||l_bookings_url||' OKI_DYNAMIC_URL_3 ';

Line: 775


       l_url_select :=
           ' SELECT NULL OKI_DYNAMIC_URL_1
          , '''' OKI_DYNAMIC_URL_3 ';

Line: 779


            l_sel_clause := l_viewby_select || l_url_select ||
                  '  ,'|| p_view_by_col ||
	          '  , oset20.rnwl_started oki_measure_1
		     , oset20.rnwl_booked oki_measure_2
		     , oset20.c_rnwl_rate oki_calc_item1
		     , oset20.rnwl_rate_chg oki_calc_item2
	             , oset20.rnwl_started_tot oki_calc_item3
		     , oset20.rnwl_booked_tot oki_calc_item4
		     , oset20.rnwl_rate_tot oki_calc_item5
		     , oset20.rnwl_rate_chg_tot oki_calc_item6
		     , oset20.c_rnwl_rate oki_measure_3
		     , oset20.p_rnwl_rate oki_measure_4
                     ,'||POA_DBI_UTIL_PKG.rate_clause('oset20.rnwl_booked','oset20.rnwl_booked_tot') || ' oki_measure_5
		     , oset20.c_rnwl_rate oki_calc_item7
		     , oset20.rnwl_rate_tot oki_calc_item17
		     , oset20.p_rnwl_rate oki_calc_item8
		     , oset20.p_rnwl_rate_tot oki_calc_item18
	       FROM
	            (SELECT '
		    	  || p_view_by_col ||'
                           , oset15.rnwl_booked
			   , oset15.rnwl_started
                           , oset15.c_rnwl_rate
			   , oset15.p_rnwl_rate
			   , oset15.rnwl_rate_chg
                           , oset15.rnwl_booked_tot
			   , oset15.rnwl_started_tot
                           , oset15.rnwl_rate_tot
                           , oset15.p_rnwl_rate_tot
			   , oset15.rnwl_rate_chg_tot
		     FROM
		     	(SELECT '|| p_view_by_col || '
                                 , oset10.c_rnwl_booked rnwl_booked
				 , oset10.c_rnwl_started rnwl_started
                                 , oset10.c_rnwl_rate c_rnwl_rate
                                 , oset10.p_rnwl_rate p_rnwl_rate
                                 ,'||OKI_DBI_UTIL_PVT.change_clause('oset10.c_rnwl_Rate',
                                                       'oset10.p_rnwl_Rate','P') || 'rnwl_rate_chg
			         , oset10.c_rnwl_booked_tot rnwl_booked_tot
				 , oset10.c_rnwl_started_tot rnwl_started_tot
                                 , oset10.c_rnwl_Rate_total rnwl_rate_tot
                                 , oset10.p_rnwl_Rate_total p_rnwl_rate_tot
                                 ,'||OKI_DBI_UTIL_PVT.change_clause('oset10.c_rnwl_Rate_total',
                                                       'oset10.p_rnwl_Rate_total','P') || 'rnwl_rate_chg_tot
             FROM
	         (SELECT oset05.' || p_view_by_col || '
		       , nvl(oset05.c_started,0) c_rnwl_started
		       , nvl(oset05.c_booked,0) c_rnwl_booked
		       , nvl(oset05.p_started,0) p_rnwl_started
		       , nvl(oset05.p_booked,0) p_rnwl_booked
                       , '||POA_DBI_UTIL_PKG.rate_clause('NVL(oset05.c_booked,0)','oset05.c_started','NP') || 'c_rnwl_Rate
                       , '||POA_DBI_UTIL_PKG.rate_clause('NVL(oset05.p_booked,0)','oset05.p_started','NP') || 'p_rnwl_Rate
	               , nvl(oset05.c_booked_total,0) c_rnwl_booked_tot
		       , nvl(oset05.p_booked_total,0) p_rnwl_booked_tot
                       , nvl(oset05.c_started_total,0) c_rnwl_started_tot
		       , nvl(oset05.p_started_total,0) p_rnwl_started_tot
                       , '||POA_DBI_UTIL_PKG.rate_clause('NVL(oset05.c_booked_total,0)', 'oset05.c_started_total','NP') || 'c_rnwl_Rate_total
                       , '||POA_DBI_UTIL_PKG.rate_clause('NVL(oset05.p_booked_total,0)', 'oset05.p_started_total','NP') || 'p_rnwl_Rate_total  from (';