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

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

Line: 233


*       get activations Select clause SQL - Activations portlet
******************************************************************/

  FUNCTION get_activations_sel_clause (
    p_view_by_dim               IN       VARCHAR2
  , p_view_by_col               IN       VARCHAR2)
    RETURN VARCHAR2
  IS
    l_sel_clause         VARCHAR2 (32767);

Line: 245


    l_viewby_select      VARCHAR2(32767);

Line: 246


    l_url_select         VARCHAR2(32767);

Line: 253


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

Line: 268


    l_viewby_select  :=  l_viewby_select ||
   ',OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_2 ,OKI_DYNAMIC_URL_5 , '|| l_prodcat_url ||
     ',OKI_MEASURE_1, OKI_PMEASURE_1, OKI_TMEASURE_1, OKI_CHANGE_1,OKI_TCHANGE_1' ||
    ', OKI_PERCENT_1,OKI_TPERCENT_1,OKI_PERCENT_CHANGE_1,OKI_MEASURE_2,OKI_TMEASURE_2' ||
    ',OKI_KPI_MEASURE_2,OKI_PKPI_MEASURE_2,OKI_TKPI_MEASURE_2,OKI_PTKPI_MEASURE_2' ||
   ',OKI_PERCENT_2,OKI_TPERCENT_2,OKI_MEASURE_3,OKI_TMEASURE_3,OKI_MEASURE_4,OKI_TMEASURE_4,OKI_ATTRIBUTE_5,OKI_PERCENT_CHANGE_2' ||
  ',OKI_MEASURE_5,OKI_TMEASURE_5,OKI_KPI_MEASURE_5,OKI_PKPI_MEASURE_5,OKI_TKPI_MEASURE_5' ||
   ',OKI_PTKPI_MEASURE_5,OKI_PERCENT_5, OKI_TPERCENT_5,OKI_ATTRIBUTE_1,OKI_ATTRIBUTE_4 ' ||
   '  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_DYNAMIC_URL_5 ,OKI_MEASURE_1 '||
  ', OKI_PMEASURE_1, OKI_TMEASURE_1, OKI_CHANGE_1,OKI_TCHANGE_1,OKI_PERCENT_1,OKI_TPERCENT_1' ||
 ',OKI_PERCENT_CHANGE_1,OKI_MEASURE_2,OKI_TMEASURE_2,OKI_KPI_MEASURE_2,OKI_PKPI_MEASURE_2 '||
  ',OKI_TKPI_MEASURE_2,OKI_PTKPI_MEASURE_2,OKI_PERCENT_2,OKI_TPERCENT_2,OKI_MEASURE_3,OKI_ATTRIBUTE_5,OKI_PERCENT_CHANGE_2 ' ||
   ',OKI_TMEASURE_3,OKI_MEASURE_4,OKI_TMEASURE_4,OKI_MEASURE_5,OKI_TMEASURE_5,OKI_KPI_MEASURE_5 '||
  ',OKI_PKPI_MEASURE_5,OKI_TKPI_MEASURE_5,OKI_PTKPI_MEASURE_5,OKI_PERCENT_5,OKI_TPERCENT_5,OKI_ATTRIBUTE_1,OKI_ATTRIBUTE_4
       FROM ( ';

Line: 287


       l_url_select :=
          'SELECT  decode(resource_id,-999,''pFunctionName=OKI_DBI_SCM_ACT_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_newbus_url||')) OKI_DYNAMIC_URL_2 '||
          ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_activated_url||')) OKI_DYNAMIC_URL_5 ';

Line: 294


       l_url_select :=
         'SELECT  ''''  OKI_SALES_GROUP_URL '||
          ' , '||l_newbus_url||' OKI_DYNAMIC_URL_2 '||
          ' , '||l_activated_url||' OKI_DYNAMIC_URL_5 ';

Line: 299


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

Line: 306


      l_sel_clause               := l_viewby_select || l_url_select ||
          -- AK Attribute naming
          '   ,'|| p_view_by_col ||
          ' , oset20.C_TAC OKI_MEASURE_1  '||
          ' , oset20.p_TAC OKI_PMEASURE_1  '||
          ' , oset20.C_TAC_tot OKI_TMEASURE_1  '||
          ' , oset20.TAC_chg OKI_CHANGE_1  '||
          ' , oset20.TAC_chg_tot OKI_TCHANGE_1  '||
          ' , oset20.TAC_PoT OKI_PERCENT_1  '||
          ' , oset20.TAC_PoT_tot OKI_TPERCENT_1  '||
          ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.tac_PoT','oset20.p_tac_PoT','P') || ' OKI_PERCENT_CHANGE_1 '||
          ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.c_NB','oset20.p_NB','NP') || ' OKI_PERCENT_CHANGE_2 '||
          ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.c_NB_tot','oset20.p_NB_tot','NP') || ' OKI_ATTRIBUTE_1'||
          ' , oset20.C_NB OKI_MEASURE_2  '||
          ' , oset20.C_NB_tot OKI_TMEASURE_2  '||
          ' , oset20.C_NB OKI_KPI_MEASURE_2  '||
          ' , oset20.p_NB OKI_PKPI_MEASURE_2  '||
          ' , oset20.c_NB_tot OKI_TKPI_MEASURE_2  '||
          ' , oset20.p_NB_tot OKI_PTKPI_MEASURE_2  '||
          ' , oset20.NB_PoT OKI_PERCENT_2  '||
          ' , oset20.NB_PoT_tot OKI_TPERCENT_2  '||
          ' , oset20.c_AE OKI_MEASURE_3 '||
          ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.c_AR','oset20.p_AR','NP') || ' OKI_ATTRIBUTE_5 '||
          ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.c_AR_tot','oset20.p_AR_tot','NP') || ' OKI_ATTRIBUTE_4 '||
          ' , oset20.c_AE_tot OKI_TMEASURE_3 '||
          ' , oset20.c_Upl OKI_MEASURE_4 '||
          ' , oset20.c_Upl_tot OKI_TMEASURE_4 '||
          ' , oset20.c_AR OKI_MEASURE_5 '||
          ' , oset20.c_AR_tot OKI_TMEASURE_5 '||
          ' , oset20.c_AR OKI_KPI_MEASURE_5 '||
          ' , oset20.c_AR_tot OKI_TKPI_MEASURE_5 '||
          ' , oset20.p_AR OKI_PKPI_MEASURE_5 '||
          ' , oset20.p_AR_tot OKI_PTKPI_MEASURE_5 '||
          ' , oset20.AR_PoT OKI_PERCENT_5 '||
          ' , oset20.AR_PoT_tot OKI_TPERCENT_5 '||
          '   from '||
          '   ( select '||
          -- Change Calculation
          '    '|| p_view_by_col ||
          '   , oset15.C_TAC C_TAC '||
          '   , oset15.P_TAC P_TAC '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.C_TAC','oset15.P_TAC','NP') || ' TAC_chg '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.c_TAC','oset15.c_TAC_tot') || 'tac_PoT '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.p_TAC','oset15.p_TAC_tot') || 'p_tac_PoT '||
          '   , oset15.C_TAC_tot C_TAC_tot '||
          '   ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.C_TAc_tot','oset15.P_TAC_tot','NP') || ' TAC_chg_tot '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.c_TAC_tot','oset15.c_TAC_tot') || 'tac_PoT_tot '||
          '   , oset15.c_NB '||
          '   , oset15.p_NB '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.c_NB','oset15.c_TAC') || ' NB_PoT '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.p_NB','oset15.p_TAC') || ' p_NB_PoT '||
          '   , oset15.c_NB_tot '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.c_NB_tot','oset15.c_TAC_tot') || ' NB_PoT_tot '||
          ' , oset15.c_AE '||
          ' , oset15.c_Upl '||
          ' , oset15.c_AR '||
          ' , oset15.p_AE '||
          ' , oset15.p_AR '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.c_AR','oset15.c_TAC') || 'AR_PoT '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.p_AR','oset15.p_TAC') || 'p_AR_PoT '||
          ' , oset15.p_NB_tot '||
          ' , oset15.c_AE_tot '||
          ' , oset15.c_Upl_tot '||
          ' , oset15.c_AR_tot '||
          ' , oset15.p_AR_tot '||
          '   ,'||poa_dbi_util_pkg.rate_clause('oset15.c_AR_tot','oset15.c_TAC_tot') || 'AR_PoT_tot '||
          '   from  '||
          '    (select '||
               -- Calculated Measures
                p_view_by_col ||
               ' , oset10.c_NB '||
              ' , oset10.p_NB '||
               ' , oset10.c_AE '||
               ' , oset10.c_AR '||
               ' , oset10.p_AR '||
               ' , oset10.p_AE '||
               ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.c_NB','oset10.c_AR') ||' c_TAC '||
               ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.p_NB','oset10.p_AR') ||' p_TAC '||
               ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset10.c_AR','oset10.c_AE') ||' c_Upl '||
               ' , oset10.c_NB_tot '||
               ' , oset10.p_NB_tot '||
               ' , oset10.c_AE_tot '||
               ' , oset10.c_AR_tot '||
               ' , oset10.p_AR_tot '||
               ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.c_NB_tot','oset10.c_AR_tot') ||' c_TAC_tot '||
               ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset10.c_AR_tot','oset10.c_AE_tot') ||' c_Upl_tot '||
               ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.p_NB_tot','oset10.p_AR_tot') ||' p_TAC_tot ';

Line: 398


               '   ( select '||
               '        oset05.'||p_view_by_col ||
               '      , nvl(oset05.c_NBsgo + oset05.c_NBgo,0) c_NB '||
               '      , nvl(oset05.c_AEsxr + oset05.c_AEglr,0) c_AE '||
               '      , nvl(oset05.c_ARsro + oset05.c_ARgls,0) c_AR '||
               '      , nvl(oset05.p_NBsgo + oset05.p_NBgo,0) p_NB'||
               '      , nvl(oset05.p_AEsxr + oset05.p_AEglr,0) p_AE '||
               '      , nvl(oset05.p_ARsro + oset05.p_ARgls,0) p_AR '||
               '      , nvl(oset05.c_NBsgo_total + oset05.c_NBgo_total,0) c_NB_tot '||
               '      , nvl(oset05.c_AEsxr_total + oset05.c_AEglr_total,0) c_AE_tot '||
               '      , nvl(oset05.c_ARsro_total + oset05.c_ARgls_total,0) c_AR_tot '||
               '      , nvl(oset05.p_NBsgo_total + oset05.p_NBgo_total,0) p_NB_tot '||
               '      , nvl(oset05.p_ARsro_total + oset05.p_ARgls_total,0) p_AR_tot ';

Line: 416


               '   ( select '||
               '        oset05.'||p_view_by_col ||
               '      , nvl(oset05.c_NBsgo,0) c_NB '||
               '      , nvl(oset05.c_AEsxr,0) c_AE '||
               '      , nvl(oset05.c_ARsro,0) c_AR '||
               '      , nvl(oset05.p_NBsgo,0) p_NB'||
               '      , nvl(oset05.p_AEsxr,0) p_AE '||
               '      , nvl(oset05.p_ARsro,0) p_AR '||
               '      , nvl(oset05.c_NBsgo_total,0) c_NB_tot '||
               '      , nvl(oset05.c_AEsxr_total,0) c_AE_tot '||
               '      , nvl(oset05.c_ARsro_total,0) c_AR_tot '||
               '      , nvl(oset05.p_NBsgo_total,0) p_NB_tot '||
               '      , nvl(oset05.p_ARsro_total,0) p_AR_tot ';

Line: 653


        'Select  cal_NAME AS VIEWBY '||
       ' , nvl(uset.c_sro_amt,0)+nvl(uset.c_sgo_amt,0)+nvl(uset.c_glr_amt,0)+nvl(uset.c_gls_amt,0)  OKI_MEASURE_1 '||
       ' , nvl(uset.p_sro_amt,0)+nvl(uset.p_sgo_amt,0)+nvl(uset.p_glr_amt,0)+nvl(uset.p_gls_amt,0)  OKI_PMEASURE_1 '||
       ' ,'||OKI_DBI_UTIL_PVT.change_clause('(nvl(uset.c_sro_amt,0)+nvl(uset.c_sgo_amt,0)+nvl(uset.c_glr_amt,0)+
					nvl(uset.c_gls_amt,0))','(nvl(uset.p_sro_amt,0)+nvl(uset.p_sgo_amt,0)+
					nvl(uset.p_glr_amt,0)+
					nvl(uset.p_gls_amt,0))', 'NP' ) || ' OKI_CHANGE_1 ';

Line: 663


        'Select  cal.NAME AS VIEWBY '||
       ' , nvl(iset.c_sro_amt,0)+nvl(iset.c_sgo_amt,0)  OKI_MEASURE_1 '||
       ' , nvl(iset.p_sro_amt,0)+nvl(iset.p_sgo_amt,0)  OKI_PMEASURE_1 '||
       '   ,'||OKI_DBI_UTIL_PVT.change_clause('(nvl(iset.c_sro_amt,0)+nvl(iset.c_sgo_amt,0))','(nvl(iset.p_sro_amt,0)+nvl(iset.p_sgo_amt,0))', 'NP' ) || ' OKI_CHANGE_1 ';

Line: 805


        SELECT
           oki_attribute_1,
           cust.value oki_attribute_2,
           DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
	   to_char(OKI_DATE_3) OKI_DATE_3,
	   to_char(OKI_DATE_1) OKI_DATE_1,
	   to_char(OKI_DATE_2) 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 ,
             chr_id,
             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
        FROM (SELECT fact.*
				     , k.start_date OKI_DATE_1
                     , 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 ,
                     oset5.act_value OKI_MEASURE_2,
                     oset5.act_value_total OKI_TMEASURE_2,
		 			 oset5.date_signed OKI_DATE_3
              FROM
                (SELECT
                    fact.chr_id,
                    fact.customer_party_id,
                    fact.resource_id,
					fact.date_signed
		    ';

Line: 858


* Function get activationed detail value based on type selected
******************************************************************/

 Function new_ren_detail( p_type                   IN VARCHAR2
                         , p_new                   IN NUMBER
                         , p_ren                   IN NUMBER ) RETURN NUMBER IS
  BEGIN
    if (p_type = 'All' or p_type = 'ALL' or p_type is NULL) THEN
        return NVL(p_new,0)+NVL(p_ren,0);