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

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

Line: 62


  l_query := 'select  1 VIEWBYID, ''Hello'' VIEWBY ,''SG Url'' OKI_SALES_GROUP_URL ,
''RUL 2 Url'' OKI_DYNAMIC_URL_1,1 OKI_MEASURE_1  ,2 OKI_PMEASURE_1  ,3 OKI_TMEASURE_1  ,
4 OKI_CHANGE_1  ,5 OKI_TCHANGE_1, 6 OKI_KPI_MEASURE_1  ,7 OKI_PKPI_MEASURE_1  ,
8 OKI_TKPI_MEASURE_1  ,9 OKI_PTKPI_MEASURE_1, 10 OKI_PERCENT_1  ,11 OKI_TPERCENT_1  ,
12 OKI_PERCENT_CHANGE_1  ,13 OKI_MEASURE_2  ,14 OKI_TMEASURE_2  ,15 OKI_KPI_MEASURE_2  ,
16 OKI_PKPI_MEASURE_2  ,17 OKI_TKPI_MEASURE_2  ,18 OKI_PTKPI_MEASURE_2  ,19 OKI_PERCENT_2  ,
20 OKI_TPERCENT_2   FROM DUAL' ;

Line: 107


* GET TERMINATIONS SELECT CLAUSE SQL - TERMINATIONS PORTLET
**********************************************************/

  FUNCTION Get_Terminations_Sel_Clause(
    p_view_by_dim		IN	VARCHAR2,
    p_view_by_col		IN	VARCHAR2) RETURN VARCHAR2 IS


    l_sel_clause		VARCHAR2(32767);

Line: 117


    l_viewby_select		VARCHAR2(32767);

Line: 118


    l_url_select		VARCHAR2(32767);

Line: 123


    l_viewby_select := OKI_DBI_UTIL_PVT.Get_Viewby_Select_Clause(p_view_by_dim, 'SRM', '7.0');

Line: 129


    l_viewby_select := l_viewby_select||', OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_1, OKI_MEASURE_1,
 OKI_PMEASURE_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_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
     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_1, OKI_MEASURE_1, OKI_PMEASURE_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_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
       FROM ( ';

Line: 143


      THEN l_url_select := 'SELECT decode(resource_id, -999,
''pFunctionName=OKI_DBI_SCM_TRM_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_terminated_url||')) OKI_DYNAMIC_URL_1 ';

Line: 148


      THEN l_url_select := 'SELECT  ''''  OKI_SALES_GROUP_URL, '||l_terminated_url||' OKI_DYNAMIC_URL_1';

Line: 149


      ELSE l_url_select := 'SELECT '''' OKI_SALES_GROUP_URL, '''' OKI_DYNAMIC_URL_1';

Line: 153


      l_sel_clause := l_viewby_select||l_url_select||
	  -- AK Attribute naming
	  ', '||p_view_by_col||
	  ', oset20.c_t_rv	OKI_MEASURE_1'||
	  ', oset20.p_t_rv	OKI_PMEASURE_1'||
	  ', oset20.c_t_rv_tot	OKI_TMEASURE_1'||
	  ', oset20.c_t_rv_chg	OKI_CHANGE_1'||
	  ', oset20.c_t_rv_chg_tot	OKI_TCHANGE_1'||
	  ', oset20.c_t_rv	OKI_KPI_MEASURE_1'||
	  ', oset20.p_t_rv	OKI_PKPI_MEASURE_1'||
	  ', oset20.c_t_rv_tot	OKI_TKPI_MEASURE_1'||
	  ', oset20.p_t_rv_tot	OKI_PTKPI_MEASURE_1'||
	  ', oset20.c_t_rv_pot	OKI_PERCENT_1'||
	  ', oset20.c_t_rv_pot_tot	OKI_TPERCENT_1'||
	  ', '||OKI_DBI_UTIL_PVT.Change_Clause('oset20.c_t_rv_pot','oset20.p_t_rv_pot','P')||'	OKI_PERCENT_CHANGE_1'||
	  ', oset20.c_t_bv	OKI_MEASURE_2'||
	  ', oset20.c_t_bv_tot	OKI_TMEASURE_2'||
	  ', oset20.c_t_bv	OKI_KPI_MEASURE_2'||
	  ', oset20.p_t_bv	OKI_PKPI_MEASURE_2'||
	  ', oset20.c_t_bv_tot	OKI_TKPI_MEASURE_2'||
	  ', oset20.p_t_bv_tot	OKI_PTKPI_MEASURE_2'||
	  ', oset20.c_t_bv_pot	OKI_PERCENT_2'||
	  ', oset20.c_t_bv_pot_tot	OKI_TPERCENT_2'||
	  '   from ( select'||
	  -- Change Calculation
	  ' '||p_view_by_col||
	  ', oset15.c_t_rv	C_T_RV'||
	  ', oset15.p_t_rv	P_T_RV'||
	  ', '||OKI_DBI_UTIL_PVT.Change_Clause('oset15.c_t_rv','oset15.p_t_rv','NP') || '	C_T_RV_CHG'||
	  ', '||POA_DBI_UTIL_PKG.Rate_Clause('oset15.c_t_rv','oset15.c_t_rv_tot') || '	C_T_RV_POT'||
	  ', '||POA_DBI_UTIL_PKG.Rate_Clause('oset15.p_t_rv','oset15.p_t_rv_tot') || '	P_T_RV_POT'||
	  ', oset15.c_t_rv_tot	C_T_RV_TOT'||
	  ', oset15.p_t_rv_tot	P_T_RV_TOT'||
	  ', '||OKI_DBI_UTIL_PVT.Change_Clause('oset15.c_t_rv_tot','oset15.p_t_rv_tot','NP') || '	C_T_RV_CHG_TOT'||
	  ', '||poa_dbi_util_pkg.Rate_Clause('oset15.c_t_rv_tot','oset15.c_t_rv_tot')||'	C_T_RV_POT_TOT'||
	  ', oset15.c_t_bv	C_T_BV'||
	  ', oset15.p_t_bv	P_T_BV'||
	  ', '||POA_DBI_UTIL_PKG.Rate_Clause('oset15.c_t_rv', (OKI_DBI_UTIL_PVT.add_measures('oset15.c_t_rv','oset15.c_t_bv')))||'	C_T_BV_POT'||
	  ', oset15.c_t_bv_tot	C_T_BV_TOT'||
	  ', oset15.p_t_bv_tot	P_T_BV_TOT'||
	  ', '||POA_DBI_UTIL_PKG.Rate_Clause('oset15.c_t_rv_tot', (OKI_DBI_UTIL_PVT.add_measures('oset15.c_t_rv_tot','oset15.c_t_bv_tot')))||'	C_T_BV_POT_TOT'||
	  '   from (select '||
--marker
		-- Calculated Measures
		p_view_by_col ||
		', nvl(oset10.c_t_rv,0)	C_T_RV'||
		', nvl(oset10.p_t_rv,0)	P_T_RV'||
		', nvl(oset10.c_t_bv,0)	C_T_BV'||
		', nvl(oset10.p_t_bv,0)	P_T_BV'||
		', nvl(oset10.c_t_rv_tot,0)	C_T_RV_TOT'||
		', nvl(oset10.p_t_rv_tot,0)	P_T_RV_TOT'||
		', nvl(oset10.c_t_bv_tot,0)	C_T_BV_TOT'||
		', nvl(oset10.p_t_bv_tot,0)	P_T_BV_TOT'||
		' from '||
/*??*/
		'   ( select oset05.'||p_view_by_col||
		', nvl(oset05.c_t_rv,0)	C_T_RV'||
		', nvl(oset05.p_t_rv,0)	P_T_RV'||
		', nvl(oset05.c_t_bv,0)	C_T_BV'||
		', nvl(oset05.p_t_bv,0)	P_T_BV'||
		', nvl(oset05.c_t_rv_total,0)	C_T_RV_TOT'||
		', nvl(oset05.p_t_rv_total,0)	P_T_RV_TOT'||
		', nvl(oset05.c_t_bv_total,0)	C_T_BV_TOT'||
		', nvl(oset05.p_t_bv_total,0)	P_T_BV_TOT';

Line: 237


SELECT	k.complete_k_number			OKI_ATTRIBUTE_1,
	cust.value				OKI_ATTRIBUTE_2,
        DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
	v.value				OKI_ATTRIBUTE_4,
	to_char(k.start_date)			OKI_DATE_1,
	to_char(fact.oki_date_2)		OKI_DATE_2,
	to_char(fact.oki_date_3)		OKI_DATE_3,
	k.price_nego_'||p_cur_suffix||'         OKI_MEASURE_1,
	fact.oki_measure_2			OKI_MEASURE_2,
	fact.t_t_bv				OKI_TMEASURE_2,
	fact.oki_measure_3			OKI_MEASURE_3,
	fact.t_t_rv				OKI_TMEASURE_3,
	fact.chr_id                             OKI_ATTRIBUTE_6
  FROM (SELECT *
	  FROM (SELECT	rank() over (&ORDER_BY_CLAUSE NULLS LAST, chr_id, trn_code) - 1	RNK,
			chr_id,
			trn_code,
			customer_party_id,
			resource_id,
			oki_date_2,
			oki_date_3,
			oki_measure_2,
			t_t_bv,
			oki_measure_3,
			t_t_rv
		  FROM (SELECT	oset5.chr_id			CHR_ID,
				oset5.trn_code			TRN_CODE,
				oset5.customer_party_id		CUSTOMER_PARTY_ID,
				oset5.resource_id		RESOURCE_ID,
				oset5.date_terminated		OKI_DATE_2,
				oset5.termination_entry_date    OKI_DATE_3,
				oset5.t_bv			OKI_MEASURE_2,
				oset5.t_bv_total			T_T_BV,
				oset5.t_rv			OKI_MEASURE_3,
				oset5.t_rv_total			T_T_RV
			   FROM	(SELECT fact.chr_id,
					fact.trn_code,
					fact.customer_party_id,
					fact.resource_id,
					min(fact.date_terminated) date_terminated,
					min(fact.termination_entry_date) termination_entry_date';

Line: 485


SELECT  cal.name		VIEWBY,
	nvl(iset.c_t_rv,0)	OKI_MEASURE_1,
	nvl(iset.p_t_rv,0)	OKI_PMEASURE_1,
	'||OKI_DBI_UTIL_PVT.Change_Clause('nvl(iset.c_t_rv,0)','nvl(iset.p_t_rv,0)','NP')||'	OKI_CHANGE_1';