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_NSCM_BAL_PVT SQL Statements

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

Line: 467


   l_sql1 := 'Select 1'
                        || oki_dbi_util_pvt.get_nested_cols(l_col_tbl1,'YTD',p_trend_flag)
                        || oki_dbi_util_pvt.get_xtd_where(l_mv1,'N','YTD','119')
                        || l_where_clause1;

Line: 473


   l_sql2 := 'Select 1'
                        || oki_dbi_util_pvt.get_nested_cols(l_col_tbl2,'YTD',p_trend_flag)
                        || oki_dbi_util_pvt.get_xtd_where(l_mv2,'N','YTD','119')
                        || l_where_clause2;

Line: 478


   l_sql3 := 'Select 1'
                        || oki_dbi_util_pvt.get_nested_cols(l_col_tbl3,'YTD',p_trend_flag)
                        || oki_dbi_util_pvt.get_xtd_where(l_mv3,'N','YTD','119')
                        || l_where_clause3;

Line: 492


  l_sql4 := 'Select 1'
                        || oki_dbi_util_pvt.get_nested_cols(l_col_tbl4,'YTD',p_trend_flag)
                        || oki_dbi_util_pvt.get_xtd_where(l_mv4,'N','YTD','119')
                        || l_where_clause4;

Line: 554


    l_sql :=  'Select ' || l_viewby || ' , 0 c_xtd, 0 p_xtd
              , nvl(c_xtd1,0)+ nvl(c_xtd11,0) + nvl(c_xtd4,0)+ nvl(c_xtd44,0) - nvl(c_xtd2,0)- nvl(c_xtd3,0) c_ytd
              , nvl(p_xtd1,0)+ nvl(p_xtd11,0) + nvl(p_xtd4,0)+ nvl(p_xtd44,0)- nvl(p_xtd2,0)- nvl(p_xtd3,0) p_ytd
              , 0 c_itd, 0 p_itd from ';

Line: 561


    l_sql :=  'Select ' || l_viewby || '
              , nvl(c_xtd1,0)+ nvl(c_xtd11,0) + nvl(c_xtd4,0)+ nvl(c_xtd44,0) - nvl(c_xtd2,0)- nvl(c_xtd3,0) c_xtd
              , nvl(p_xtd1,0)+ nvl(p_xtd11,0) + nvl(p_xtd4,0)+ nvl(p_xtd44,0) - nvl(p_xtd2,0)- nvl(p_xtd3,0) p_xtd
              , 0 c_ytd, 0 p_ytd, 0 c_itd, 0 p_itd from ';

Line: 571


     l_sql :=  'Select ' || l_viewby || ' , 0 c_xtd, 0 p_xtd
              , nvl(c_xtd1,0)+ nvl(c_xtd11,0)- nvl(c_xtd2,0)- nvl(c_xtd3,0) c_ytd
              , nvl(p_xtd1,0)+ nvl(p_xtd11,0)- nvl(p_xtd2,0)- nvl(p_xtd3,0) p_ytd
              , 0 c_itd, 0 p_itd from ';

Line: 578


    l_sql :=  'Select ' || l_viewby || '
              , nvl(c_xtd1,0)+ nvl(c_xtd11,0)- nvl(c_xtd2,0)- nvl(c_xtd3,0) c_xtd
              , nvl(p_xtd1,0)+ nvl(p_xtd11,0)- nvl(p_xtd2,0)- nvl(p_xtd3,0) p_xtd
              , 0 c_ytd, 0 p_ytd, 0 c_itd, 0 p_itd from ';

Line: 593


     Balance Summary  Select clause
  */
  FUNCTION get_bal_sel_clause (
    p_view_by_dim               IN       VARCHAR2
  , p_view_by_col               IN       VARCHAR2
  , p_cur_suffix                IN       VARCHAR2)
    RETURN VARCHAR2
  IS
    l_sel_clause         VARCHAR2 (32767);

Line: 605


    l_viewby_select      VARCHAR2(32767);

Line: 606


    l_url_select         VARCHAR2(32767);

Line: 609


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

Line: 613


    l_viewby_select  :=  l_viewby_select ||
   ', OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_2
    ,OKI_PMEASURE_1,OKI_MEASURE_1,OKI_TMEASURE_1,OKI_CHANGE_1,OKI_TCHANGE_1
    ,OKI_KPI_MEASURE_1,OKI_PKPI_MEASURE_1,OKI_TKPI_MEASURE_1,OKI_PTKPI_MEASURE_1
    ,OKI_PERCENT_1,OKI_TPERCENT_1,OKI_PERCENT_CHANGE_1
    ,OKI_PMEASURE_2,OKI_MEASURE_2,OKI_TMEASURE_2,OKI_CHANGE_2,OKI_TCHANGE_2
    ,OKI_KPI_MEASURE_2,OKI_PKPI_MEASURE_2,OKI_TKPI_MEASURE_2,OKI_PTKPI_MEASURE_2
    ,OKI_PERCENT_2,OKI_TPERCENT_2,OKI_PERCENT_CHANGE_2
    ,OKI_CHANGE_3, OKI_TCHANGE_3
     FROM (SELECT  rank() over (&ORDER_BY_CLAUSE nulls last , '||p_view_by_col||') - 1 rnk ,'||p_view_by_col||'
    , OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_2
    ,OKI_PMEASURE_1,OKI_MEASURE_1,OKI_TMEASURE_1,OKI_CHANGE_1,OKI_TCHANGE_1
    ,OKI_KPI_MEASURE_1,OKI_PKPI_MEASURE_1,OKI_TKPI_MEASURE_1,OKI_PTKPI_MEASURE_1
    ,OKI_PERCENT_1,SUM(OKI_PERCENT_1) over() OKI_TPERCENT_1,OKI_PERCENT_CHANGE_1
    ,OKI_PMEASURE_2,OKI_MEASURE_2,OKI_TMEASURE_2,OKI_CHANGE_2,OKI_TCHANGE_2
    ,OKI_KPI_MEASURE_2,OKI_PKPI_MEASURE_2,OKI_TKPI_MEASURE_2,OKI_PTKPI_MEASURE_2
    ,OKI_PERCENT_2,SUM(OKI_PERCENT_2) over() OKI_TPERCENT_2,OKI_PERCENT_CHANGE_2
    ,OKI_CHANGE_3, OKI_TCHANGE_3
       FROM ( ';

Line: 635


       l_url_select :=
          'SELECT  decode(resource_id,-999,''pFunctionName=OKI_DBI_SCM_BAL_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP'','''') OKI_SALES_GROUP_URL '||
          ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_Cbal_url||')) OKI_DYNAMIC_URL_2 ';

Line: 640


       l_url_select :=
          'SELECT  ''''  OKI_SALES_GROUP_URL '||
          ' , '||l_cBal_url||' OKI_DYNAMIC_URL_2 ';

Line: 644


       l_url_select :=
          'SELECT  '''' OKI_SALES_GROUP_URL '||
          ' , '''' OKI_DYNAMIC_URL_2 ';

Line: 661


      l_sel_clause               := l_viewby_select || l_url_select ||
          '   ,'|| p_view_by_col ||
          ' , oset20.p_beg   OKI_PMEASURE_1, oset20.c_beg  OKI_MEASURE_1 '||
          ' , oset20.c_beg_tot OKI_TMEASURE_1, oset20.beg_chg OKI_CHANGE_1 '||
          ' , oset20.beg_chg_tot  OKI_TCHANGE_1, oset20.c_beg   OKI_KPI_MEASURE_1 '||
          ' , oset20.p_beg   OKI_PKPI_MEASURE_1, oset20.c_beg_tot  OKI_TKPI_MEASURE_1 '||
          ' , oset20.p_beg_tot  OKI_PTKPI_MEASURE_1, oset20.c_beg_per  OKI_PERCENT_1 '||
          ' , oset20.beg_per_chg  OKI_PERCENT_CHANGE_1, oset20.p_cur   OKI_PMEASURE_2 '||
          ' , oset20.c_cur  OKI_MEASURE_2, oset20.c_cur_tot  OKI_TMEASURE_2 '||
          ' , oset20.cur_chg    OKI_CHANGE_2, oset20.cur_chg_tot OKI_TCHANGE_2 '||
          ' , oset20.c_cur  OKI_KPI_MEASURE_2, oset20.p_cur   OKI_PKPI_MEASURE_2 '||
          ' , oset20.c_cur_tot  OKI_TKPI_MEASURE_2, oset20.p_cur_tot  OKI_PTKPI_MEASURE_2 '||
          ' , oset20.c_cur_per   OKI_PERCENT_2, oset20.cur_per_chg   OKI_PERCENT_CHANGE_2 '||
          ' , oset20.ptd_chg     OKI_CHANGE_3, oset20.ptd_chg_tot  OKI_TCHANGE_3 '||
          '   from  ( select  '|| p_view_by_col ||', oset15.c_cur c_cur '||
          '   , oset15.p_cur p_cur, oset15.c_cur_tot c_cur_tot '||
          '   , oset15.p_cur_tot p_cur_tot '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur','oset15.p_cur','NP') || ' cur_chg '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur_tot','oset15.p_cur_tot','NP') || ' cur_chg_tot '||
          '   , oset15.c_cur_per, oset15.p_cur_per '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur_per','oset15.p_cur_per','P') || ' cur_per_chg '||
          '   , oset15.c_beg c_beg, oset15.p_beg p_beg, oset15.c_beg_tot c_beg_tot '||
          '   , oset15.p_beg_tot p_beg_tot '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_beg','oset15.p_beg','NP') || ' beg_chg '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_beg_tot','oset15.p_beg_tot','NP') || ' beg_chg_tot '||
          '   , oset15.c_beg_per, oset15.p_beg_per '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_beg_per','oset15.p_beg_per','P') || ' beg_per_chg '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur','oset15.c_beg','NP') || ' ptd_chg '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.c_cur_tot','oset15.c_beg_tot','NP') || ' ptd_chg_tot '||
          '   from  (select '||  p_view_by_col ||', oset13.c_cur , oset13.c_cur_tot '||
               ' , oset13.p_cur , oset13.p_cur_tot '||
               ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_cur','oset13.c_cur_tot') || ' c_cur_per '||
               ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_cur','oset13.p_cur_tot') || ' p_cur_per '||
               ' , oset13.c_beg , oset13.c_beg_tot, oset13.p_beg , oset13.p_beg_tot '||
               ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.c_beg','oset13.c_beg_tot') || ' c_beg_per '||
               ' ,'||POA_DBI_UTIL_PKG.rate_clause('oset13.p_beg','oset13.p_beg_tot') || ' p_beg_per '||
          '   from  (select '|| p_view_by_col ||
               ' , (oset10.c_itd + oset10.c_ytd)  c_cur '||
               ' , (oset10.c_itd_tot + oset10.c_ytd_tot)  c_cur_tot '||
               ' , (oset10.p_itd + oset10.p_ytd)  p_cur '||
               ' , (oset10.p_itd_tot + oset10.p_ytd_tot)  p_cur_tot '||
               ' , (oset10.c_itd + oset10.c_ytd - oset10.c_xtd) c_beg '||
               ' , (oset10.c_itd_tot + oset10.c_ytd_tot - oset10.c_xtd_tot) c_beg_tot '||
               ' , (oset10.p_itd + oset10.p_ytd - oset10.p_xtd) p_beg '||
               ' , (oset10.p_itd_tot + oset10.p_ytd_tot - oset10.p_xtd_tot) p_beg_tot '||
               ' from ( select  oset05.'||p_view_by_col ||
               ' , SUM(NVL(oset05.c_ytd,0)) c_ytd '||
               ' , SUM(NVL(oset05.p_ytd,0)) p_ytd '||
               ' , SUM(SUM(NVL(oset05.c_ytd,0))) over ()  c_ytd_tot '||
               ' , SUM(SUM(NVL(oset05.p_ytd,0))) over ()  p_ytd_tot '||
               ' , SUM(NVL(oset05.c_xtd,0)) c_xtd '||
               ' , SUM(NVL(oset05.p_xtd,0)) p_xtd '||
               ' , SUM(SUM(NVL(oset05.c_xtd,0))) over ()  c_xtd_tot '||
               ' , SUM(SUM(NVL(oset05.p_xtd,0))) over ()  p_xtd_tot '||
               ' , SUM(NVL(oset05.c_itd,0)) c_itd '||
               ' , SUM(SUM(NVL(oset05.c_itd,0))) over ()  c_itd_tot '||
               ' , SUM(NVL(oset05.p_itd,0)) p_itd '||
               ' , SUM(SUM(NVL(oset05.p_itd,0))) over ()  p_itd_tot ';

Line: 794


        l_sql := 'Select 1'
                        || oki_dbi_util_pvt.get_nested_cols(l_col_tbl,'ITD',p_trend_flag)
                        || oki_dbi_util_pvt.get_itd_where(l_mv,p_trend_flag)
                        || l_where_clause;

Line: 799


     l_sql := 'Select ' || l_view_by_col
                        || oki_dbi_util_pvt.get_nested_cols(l_col_tbl,'ITD',p_trend_flag)
                        || oki_dbi_util_pvt.get_itd_where(l_mv,p_trend_flag)
                        || l_where_clause
                        || ' GROUP BY ' || l_view_by_col;

Line: 885


        l_sql := 'Select 1'
                        || oki_dbi_util_pvt.get_nested_cols(l_col_tbl,'YTD',p_trend_flag)
                        || oki_dbi_util_pvt.get_xtd_where(l_mv,'N','YTD','119')
                        || l_where_clause;

Line: 890


     l_sql := 'Select ' || l_view_by_col
                        || oki_dbi_util_pvt.get_nested_cols(l_col_tbl,'YTD',p_trend_flag)
                        || oki_dbi_util_pvt.get_xtd_where(l_mv,'N','YTD','119')
                        || l_where_clause
                        || ' GROUP BY ' || l_view_by_col;

Line: 1013


    l_sel_clause               := '  Select  viewby, cal_start_date, c_ytd,p_ytd,
	    lead(c_xtd,1) over(order by cal_start_date) c_xtd,
	    lead(p_xtd,1) over(order by cal_start_date) p_xtd
	  	  from ( Select   VIEWBY '||
        ' ,cal_start_date , c_ytd  '||
        ' ,p_ytd '||
        ' ,SUM(c_xtd) OVER( ORDER BY cal_start_date DESC ROWS UNBOUNDED PRECEDING)  c_xtd '||
        ' ,SUM(p_xtd) OVER( ORDER BY cal_start_date DESC ROWS UNBOUNDED PRECEDING)  p_xtd ';

Line: 1041


       l_sql := 'Select VIEWBY, '||
	         ' curr_bal OKI_MEASURE_1, '||
                 ' p_curr_bal OKI_PMEASURE_1' ||
                 '  ,'||OKI_DBI_UTIL_PVT.change_clause('curr_bal','p_curr_bal','NP') || ' OKI_CHANGE_1 '||
                ' FROM ( ' ||
                'Select  cal_start_date, VIEWBY, '||
                ' nvl(bal.c_bal,0) - nvl(xtd.c_xtd,0)  curr_bal '||
                ' ,nvl(bal.p_bal,0) - nvl(xtd.p_xtd,0)  p_curr_bal '||
                ' FROM ( '||
                ' Select ' || OKI_DBI_UTIL_PVT.add_measures('itd.c_itd','ytd.c_ytd') ||' c_bal '||
                  ' ,'|| OKI_DBI_UTIL_PVT.add_measures('itd.p_itd','ytd.p_ytd') ||' p_bal '||
                      ' FROM ( '|| p_itd ||') itd, ( '|| p_ytd || ') ytd ) bal , ('|| p_xtd || '))) xtd' ||
                 ' )  ' ;

Line: 1062


     Balance Detail Select clause
  */

  PROCEDURE get_balance_detail_sql (
    p_param                     IN       bis_pmv_page_parameter_tbl
  , x_custom_sql                OUT NOCOPY VARCHAR2
  , x_custom_output             OUT NOCOPY bis_query_attributes_tbl)
  IS

    l_query                  VARCHAR2 (32767);

Line: 1188


        SELECT
           OKI_ATTRIBUTE_1,
           cust.value     OKI_ATTRIBUTE_2,
           DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
           OKI_DATE_3,
           OKI_DATE_1,
	   OKI_DATE_2,
           OKI_MEASURE_1,
           OKI_TMEASURE_1,
           OKI_MEASURE_2,
           OKI_TMEASURE_2,
	   fact.chr_id OKI_ATTRIBUTE_5
     FROM(
       SELECT *
       FROM (
          SELECT
             rank() over (&ORDER_BY_CLAUSE nulls last) - 1 rnk ,
             customer_party_id,
             resource_id,
             oki_measure_2,
             oki_tmeasure_2,
	     oki_date_3,
	     oki_date_1,
             oki_date_2,
             oki_attribute_1,
             oki_measure_1,
             oki_tmeasure_1,
	     chr_id
         FROM (SELECT fact.*
		     , to_char(k.start_date) OKI_DATE_1
                     , to_char(k.end_date) OKI_DATE_2
                     , k.COMPLETE_k_number oki_attribute_1
                     , k.price_nego_' ||p_cur_suffix ||' OKI_MEASURE_1
                     , SUM(k.price_nego_' ||p_cur_suffix ||') over ()  OKI_TMEASURE_1
                  FROM (SELECT *
          FROM (
              SELECT oset5.chr_id    ,
                     oset5.customer_party_id  ,
                     oset5.resource_id   ,
                     nvl(oset5.Bal,0) OKI_MEASURE_2,
                     SUM(nvl(oset5.Bal,0)) over ()  OKI_TMEASURE_2,
				  	 to_char(oset5.date_signed) OKI_DATE_3
              FROM
                (SELECT
                    fact.chr_id,
                    fact.customer_party_id,
                    fact.resource_id,
					fact.date_signed
		    ';