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.
PACKAGE BODY: APPS.OKI_DBI_NSCM_TRM_PVT

Source


1 PACKAGE BODY 	OKI_DBI_NSCM_TRM_PVT AS
2 /* $Header: OKIPNTRB.pls 120.4 2005/10/10 04:40:35 kamsharm noship $ */
3 
4 /******************************************************************
5 *  Procedure to return the query for Terminations portlet
6 ******************************************************************/
7 
8   PROCEDURE Get_Terminations_Sql(
9 	p_param			IN  BIS_PMV_PAGE_PARAMETER_TBL,
10 	x_custom_sql		OUT NOCOPY VARCHAR2,
11 	x_custom_output		OUT NOCOPY bis_query_attributes_tbl) IS
12 
13   l_query			VARCHAR2(32767);
14   l_view_by			VARCHAR2(120);
15   l_view_by_col			VARCHAR2(120);
16   l_as_of_date			DATE;
17   l_prev_as_of_date		DATE;
18   l_xtd				VARCHAR2(10);
19   l_comparison_type		VARCHAR2(1);
20   l_period_type			VARCHAR2(10);
21   l_nested_pattern		NUMBER;
22   l_cur_suffix			VARCHAR2(2);
23   l_where_clause		VARCHAR2(2000);
24   l_filter_where		VARCHAR2(240);
25   l_mv				VARCHAR2(2000);
26   l_col_tbl			POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl;
27   l_join_tbl			POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl;
28   l_to_date_xed			CONSTANT VARCHAR2(3):='XED';
29   l_to_date_xtd			CONSTANT VARCHAR2(3):='XTD';
30   l_to_date_ytd			CONSTANT VARCHAR2(3):='YTD';
31   l_to_date_itd			CONSTANT VARCHAR2(3):='ITD';
32 
33     BEGIN
34 
35     l_comparison_type := 'Y';
36 
37     l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
38     l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
39 
40     oki_dbi_util_pvt.process_parameters (
41 		p_param			=> p_param,
42 		p_view_by		=> l_view_by,
43 		p_view_by_col_name	=> l_view_by_col,
44 		p_comparison_type	=> l_comparison_type,
45 		p_xtd			=> l_xtd,
46 		p_as_of_date		=> l_as_of_date,
47 		p_prev_as_of_date	=> l_prev_as_of_date,
48 		p_cur_suffix		=> l_cur_suffix,
49 		p_nested_pattern	=> l_nested_pattern,
50 		p_where_clause		=> l_where_clause,
51 		p_mv			=> l_mv,
52 		p_join_tbl		=> l_join_tbl,
53 		p_period_type		=> l_period_type,
54 		p_trend			=> 'N',
55 		p_func_area		=> 'OKI',
56 		p_version		=> '7.0',
57 		p_role			=> NULL ,
58 		p_mv_set		=> 'SRM_TM_71',
59 		p_rg_where		=> 'Y');
60 
61 
62   l_query := 'select  1 VIEWBYID, ''Hello'' VIEWBY ,''SG Url'' OKI_SALES_GROUP_URL ,
63 ''RUL 2 Url'' OKI_DYNAMIC_URL_1,1 OKI_MEASURE_1  ,2 OKI_PMEASURE_1  ,3 OKI_TMEASURE_1  ,
64 4 OKI_CHANGE_1  ,5 OKI_TCHANGE_1, 6 OKI_KPI_MEASURE_1  ,7 OKI_PKPI_MEASURE_1  ,
65 8 OKI_TKPI_MEASURE_1  ,9 OKI_PTKPI_MEASURE_1, 10 OKI_PERCENT_1  ,11 OKI_TPERCENT_1  ,
66 12 OKI_PERCENT_CHANGE_1  ,13 OKI_MEASURE_2  ,14 OKI_TMEASURE_2  ,15 OKI_KPI_MEASURE_2  ,
67 16 OKI_PKPI_MEASURE_2  ,17 OKI_TKPI_MEASURE_2  ,18 OKI_PTKPI_MEASURE_2  ,19 OKI_PERCENT_2  ,
68 20 OKI_TPERCENT_2   FROM DUAL' ;
69 
70      POA_DBI_UTIL_PKG.Add_Column(
71 		p_col_tbl		=> l_col_tbl,
72 		p_col_name		=> 't_rv_amt_'||l_cur_suffix,
73 		p_alias_name		=> 't_rv',
74 		p_to_date_type		=> l_to_date_xtd);
75 
76 
77      POA_DBI_UTIL_PKG.Add_Column(
78 		p_col_tbl		=> l_col_tbl,
79 		p_col_name		=> 't_bv_amt_' || l_cur_suffix,
80 		p_alias_name		=> 't_bv',
81 		p_to_date_type		=> l_to_date_xtd);
82 
83     l_filter_where := '  ( ABS(oki_measure_1) <> 0 or ABS(oki_pmeasure_1) <> 0 or ABS(oki_measure_2) <> 0)';
84 
85     -- Generate sql query
86     l_query := Get_Terminations_Sel_Clause(l_view_by, l_view_by_col)
87 	|| ' from '
88 	|| POA_DBI_TEMPLATE_PKG.Status_Sql (
89 		p_fact_name 		=> l_mv,
90 		p_where_clause		=> l_where_clause,
91 		p_filter_where		=> l_filter_where,
92 		p_join_tables		=> l_join_tbl,
93 		p_use_windowing		=> 'Y',
94 		p_col_name		=> l_col_tbl,
95 		p_use_grpid		=> 'N',
96 		p_paren_count		=> 6);
97 
98 
99 
100     x_custom_sql := '/* OKI_DBI_SCM_TRM_SUM_RPT */ '||l_query;
101 
102     oki_dbi_util_pvt.get_custom_status_binds (x_custom_output);
103 
104   END get_terminations_sql;
105 
106 /**********************************************************
107 * GET TERMINATIONS SELECT CLAUSE SQL - TERMINATIONS PORTLET
108 **********************************************************/
109 
110   FUNCTION Get_Terminations_Sel_Clause(
111     p_view_by_dim		IN	VARCHAR2,
112     p_view_by_col		IN	VARCHAR2) RETURN VARCHAR2 IS
113 
114 
115     l_sel_clause		VARCHAR2(32767);
116     l_terminated_url		VARCHAR2(300);
117     l_viewby_select		VARCHAR2(32767);
118     l_url_select		VARCHAR2(32767);
119 
120   BEGIN
121 
122 
123     l_viewby_select := OKI_DBI_UTIL_PVT.Get_Viewby_Select_Clause(p_view_by_dim, 'SRM', '7.0');
124 
125     -- when view by is Salesrep OKI_DBI_SCM_ACT_DTL_RPT
126 
127     l_terminated_url := '''pFunctionName=OKI_DBI_SCM_TRM_DTL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
128 
129     l_viewby_select := l_viewby_select||', OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_1, OKI_MEASURE_1,
130  OKI_PMEASURE_1, OKI_TMEASURE_1, OKI_CHANGE_1, OKI_TCHANGE_1, OKI_KPI_MEASURE_1, OKI_PKPI_MEASURE_1,
131  OKI_TKPI_MEASURE_1, OKI_PTKPI_MEASURE_1, OKI_PERCENT_1, OKI_TPERCENT_1, OKI_PERCENT_CHANGE_1,
132  OKI_MEASURE_2, OKI_TMEASURE_2, OKI_KPI_MEASURE_2, OKI_PKPI_MEASURE_2, OKI_TKPI_MEASURE_2,
133  OKI_PTKPI_MEASURE_2, OKI_PERCENT_2, OKI_TPERCENT_2
134      FROM (SELECT rank() over (&ORDER_BY_CLAUSE NULLS LAST , '||p_view_by_col||') - 1 rnk ,
135 '||p_view_by_col||', OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_1, OKI_MEASURE_1, OKI_PMEASURE_1,
136  OKI_TMEASURE_1, OKI_CHANGE_1, OKI_TCHANGE_1, OKI_KPI_MEASURE_1, OKI_PKPI_MEASURE_1,
137 OKI_TKPI_MEASURE_1, OKI_PTKPI_MEASURE_1, OKI_PERCENT_1, OKI_TPERCENT_1, OKI_PERCENT_CHANGE_1,
138  OKI_MEASURE_2, OKI_TMEASURE_2, OKI_KPI_MEASURE_2, OKI_PKPI_MEASURE_2, OKI_TKPI_MEASURE_2,
139  OKI_PTKPI_MEASURE_2, OKI_PERCENT_2, OKI_TPERCENT_2
140        FROM ( ';
141 
142     IF (p_view_by_dim = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
143       THEN l_url_select := 'SELECT decode(resource_id, -999,
144 ''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,
145  decode(resource_id, -999, '''',
146  decode( rg_id, -1, '''', '||l_terminated_url||')) OKI_DYNAMIC_URL_1 ';
147     ELSIF (p_view_by_dim IN('ITEM+ENI_ITEM','OKI_STATUS+TERM_REASON'))
148       THEN l_url_select := 'SELECT  ''''  OKI_SALES_GROUP_URL, '||l_terminated_url||' OKI_DYNAMIC_URL_1';
149       ELSE l_url_select := 'SELECT '''' OKI_SALES_GROUP_URL, '''' OKI_DYNAMIC_URL_1';
150     END IF;
151 
152 
153       l_sel_clause := l_viewby_select||l_url_select||
154 	  -- AK Attribute naming
155 	  ', '||p_view_by_col||
156 	  ', oset20.c_t_rv	OKI_MEASURE_1'||
157 	  ', oset20.p_t_rv	OKI_PMEASURE_1'||
158 	  ', oset20.c_t_rv_tot	OKI_TMEASURE_1'||
159 	  ', oset20.c_t_rv_chg	OKI_CHANGE_1'||
160 	  ', oset20.c_t_rv_chg_tot	OKI_TCHANGE_1'||
161 	  ', oset20.c_t_rv	OKI_KPI_MEASURE_1'||
162 	  ', oset20.p_t_rv	OKI_PKPI_MEASURE_1'||
163 	  ', oset20.c_t_rv_tot	OKI_TKPI_MEASURE_1'||
164 	  ', oset20.p_t_rv_tot	OKI_PTKPI_MEASURE_1'||
165 	  ', oset20.c_t_rv_pot	OKI_PERCENT_1'||
166 	  ', oset20.c_t_rv_pot_tot	OKI_TPERCENT_1'||
167 	  ', '||OKI_DBI_UTIL_PVT.Change_Clause('oset20.c_t_rv_pot','oset20.p_t_rv_pot','P')||'	OKI_PERCENT_CHANGE_1'||
168 	  ', oset20.c_t_bv	OKI_MEASURE_2'||
169 	  ', oset20.c_t_bv_tot	OKI_TMEASURE_2'||
170 	  ', oset20.c_t_bv	OKI_KPI_MEASURE_2'||
171 	  ', oset20.p_t_bv	OKI_PKPI_MEASURE_2'||
172 	  ', oset20.c_t_bv_tot	OKI_TKPI_MEASURE_2'||
173 	  ', oset20.p_t_bv_tot	OKI_PTKPI_MEASURE_2'||
174 	  ', oset20.c_t_bv_pot	OKI_PERCENT_2'||
175 	  ', oset20.c_t_bv_pot_tot	OKI_TPERCENT_2'||
176 	  '   from ( select'||
177 	  -- Change Calculation
178 	  ' '||p_view_by_col||
179 	  ', oset15.c_t_rv	C_T_RV'||
180 	  ', oset15.p_t_rv	P_T_RV'||
181 	  ', '||OKI_DBI_UTIL_PVT.Change_Clause('oset15.c_t_rv','oset15.p_t_rv','NP') || '	C_T_RV_CHG'||
182 	  ', '||POA_DBI_UTIL_PKG.Rate_Clause('oset15.c_t_rv','oset15.c_t_rv_tot') || '	C_T_RV_POT'||
183 	  ', '||POA_DBI_UTIL_PKG.Rate_Clause('oset15.p_t_rv','oset15.p_t_rv_tot') || '	P_T_RV_POT'||
184 	  ', oset15.c_t_rv_tot	C_T_RV_TOT'||
185 	  ', oset15.p_t_rv_tot	P_T_RV_TOT'||
186 	  ', '||OKI_DBI_UTIL_PVT.Change_Clause('oset15.c_t_rv_tot','oset15.p_t_rv_tot','NP') || '	C_T_RV_CHG_TOT'||
187 	  ', '||poa_dbi_util_pkg.Rate_Clause('oset15.c_t_rv_tot','oset15.c_t_rv_tot')||'	C_T_RV_POT_TOT'||
188 	  ', oset15.c_t_bv	C_T_BV'||
189 	  ', oset15.p_t_bv	P_T_BV'||
190 	  ', '||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'||
191 	  ', oset15.c_t_bv_tot	C_T_BV_TOT'||
192 	  ', oset15.p_t_bv_tot	P_T_BV_TOT'||
193 	  ', '||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'||
194 	  '   from (select '||
195 --marker
196 		-- Calculated Measures
197 		p_view_by_col ||
198 		', nvl(oset10.c_t_rv,0)	C_T_RV'||
199 		', nvl(oset10.p_t_rv,0)	P_T_RV'||
200 		', nvl(oset10.c_t_bv,0)	C_T_BV'||
201 		', nvl(oset10.p_t_bv,0)	P_T_BV'||
202 		', nvl(oset10.c_t_rv_tot,0)	C_T_RV_TOT'||
203 		', nvl(oset10.p_t_rv_tot,0)	P_T_RV_TOT'||
204 		', nvl(oset10.c_t_bv_tot,0)	C_T_BV_TOT'||
205 		', nvl(oset10.p_t_bv_tot,0)	P_T_BV_TOT'||
206 		' from '||
207 /*??*/
208 		'   ( select oset05.'||p_view_by_col||
209 		', nvl(oset05.c_t_rv,0)	C_T_RV'||
210 		', nvl(oset05.p_t_rv,0)	P_T_RV'||
211 		', nvl(oset05.c_t_bv,0)	C_T_BV'||
212 		', nvl(oset05.p_t_bv,0)	P_T_BV'||
213 		', nvl(oset05.c_t_rv_total,0)	C_T_RV_TOT'||
214 		', nvl(oset05.p_t_rv_total,0)	P_T_RV_TOT'||
215 		', nvl(oset05.c_t_bv_total,0)	C_T_BV_TOT'||
216 		', nvl(oset05.p_t_bv_total,0)	P_T_BV_TOT';
217 
218     RETURN l_sel_clause;
219   END get_terminations_sel_clause;
220 
221 
222 
223 /******************************************************************
224 *  Procedure to return the query for Terminations Detail report
225 ******************************************************************/
226 
227   FUNCTION Get_Trm_Dtl_Sel_Clause(
228 		p_cur_suffix		IN	VARCHAR2,
229 		p_period_type_code	IN	VARCHAR2) RETURN VARCHAR2 IS
230 
231   l_query	VARCHAR2(10000);
232 
233   BEGIN
234 
235 -- Generate sql query
236     l_query := '
237 SELECT	k.complete_k_number			OKI_ATTRIBUTE_1,
238 	cust.value				OKI_ATTRIBUTE_2,
239         DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
240 	v.value				OKI_ATTRIBUTE_4,
241 	to_char(k.start_date)			OKI_DATE_1,
242 	to_char(fact.oki_date_2)		OKI_DATE_2,
243 	to_char(fact.oki_date_3)		OKI_DATE_3,
244 	k.price_nego_'||p_cur_suffix||'         OKI_MEASURE_1,
245 	fact.oki_measure_2			OKI_MEASURE_2,
246 	fact.t_t_bv				OKI_TMEASURE_2,
247 	fact.oki_measure_3			OKI_MEASURE_3,
248 	fact.t_t_rv				OKI_TMEASURE_3,
249 	fact.chr_id                             OKI_ATTRIBUTE_6
250   FROM (SELECT *
251 	  FROM (SELECT	rank() over (&ORDER_BY_CLAUSE NULLS LAST, chr_id, trn_code) - 1	RNK,
252 			chr_id,
253 			trn_code,
254 			customer_party_id,
255 			resource_id,
256 			oki_date_2,
257 			oki_date_3,
258 			oki_measure_2,
259 			t_t_bv,
260 			oki_measure_3,
261 			t_t_rv
262 		  FROM (SELECT	oset5.chr_id			CHR_ID,
263 				oset5.trn_code			TRN_CODE,
264 				oset5.customer_party_id		CUSTOMER_PARTY_ID,
265 				oset5.resource_id		RESOURCE_ID,
266 				oset5.date_terminated		OKI_DATE_2,
267 				oset5.termination_entry_date    OKI_DATE_3,
268 				oset5.t_bv			OKI_MEASURE_2,
269 				oset5.t_bv_total			T_T_BV,
270 				oset5.t_rv			OKI_MEASURE_3,
271 				oset5.t_rv_total			T_T_RV
272 			   FROM	(SELECT fact.chr_id,
273 					fact.trn_code,
274 					fact.customer_party_id,
275 					fact.resource_id,
276 					min(fact.date_terminated) date_terminated,
277 					min(fact.termination_entry_date) termination_entry_date';
278 
279      RETURN l_query;
280   END Get_Trm_Dtl_Sel_Clause;
281 
282   PROCEDURE Get_Terminations_Detail_Sql(
283 		p_param		IN		BIS_PMV_PAGE_PARAMETER_TBL,
284 		x_custom_sql	OUT NOCOPY 	VARCHAR2,
285 		x_custom_output	OUT NOCOPY 	BIS_QUERY_ATTRIBUTES_TBL) IS
286 
287   l_query		VARCHAR2(32767);
288   l_where_clause	VARCHAR2(2000);
289   l_rpt_specific_where	VARCHAR2(2000);
290   l_join_where		VARCHAR2(2000);
291   l_group_by		VARCHAR2(2000);
292   l_filter_where	VARCHAR2(240);
293   l_view_by		VARCHAR2(240);
294   l_view_by_col		VARCHAR2(240);
295   l_mv			VARCHAR2(2000);
296   l_period_type		VARCHAR2(10);
297   l_xtd			VARCHAR2(10);
298   l_curr_suffix		VARCHAR2(2);
299   l_comparison_type	VARCHAR2(1);
300 
301   l_as_of_date		DATE;
302   l_prev_as_of_date	DATE;
303 
304   l_nested_pattern	NUMBER;
305 
306   l_col_tbl		POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl;
307   l_join_tbl		POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl;
308   l_additional_where       VARCHAR2(32767);
309   BEGIN
310 
311     l_comparison_type	:= 'Y';
312     l_join_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl();
313     l_col_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl();
314 
318 		p_view_by_col_name	=> l_view_by_col,
315     OKI_DBI_UTIL_PVT.Process_Parameters(
316 		p_param			=> p_param,
317 		p_view_by		=> l_view_by,
319 		p_comparison_type	=> l_comparison_type,
320 		p_xtd			=> l_xtd,
321 		p_as_of_date		=> l_as_of_date,
322 		p_prev_as_of_date	=> l_prev_as_of_date,
323 		p_cur_suffix		=> l_curr_suffix,
324 		p_nested_pattern	=> l_nested_pattern,
325 		p_where_clause		=> l_where_clause,
326 		p_mv			=> l_mv,
327 		p_join_tbl		=> l_join_tbl,
328 		p_period_type		=> l_period_type,
329 		p_trend			=> 'N',
330 		p_func_area		=> 'OKI',
331 		p_version		=> '6.0',
332 		p_role			=> NULL,
333                 p_mv_set                => 'SRM_DTL_RPT',
334 		p_rg_where		=> 'Y');
335 
336     l_rpt_specific_where    :=
337       ' AND fact.effective_term_date between &BIS_CURRENT_EFFECTIVE_START_DATE
338                                 and &BIS_CURRENT_ASOF_DATE';
339 
340     l_group_by := ' GROUP BY fact.chr_id, fact.trn_code, fact.customer_party_id, fact.resource_id';
341 
342 
343      poa_dbi_util_pkg.Add_Column(
344 		 p_col_tbl	=> l_col_tbl
345 		,p_col_name	=> 'trn_billed_value_' || l_curr_suffix
346 		,p_alias_name	=> 't_bv'
347 		,p_prior_code    => poa_dbi_util_pkg.no_priors);
348 
349      poa_dbi_util_pkg.Add_Column(
350 		 p_col_tbl	=> l_col_tbl
351 		,p_col_name	=> 'price_negotiated_' || l_curr_suffix  ||' - trn_billed_value_' || l_curr_suffix
352 		,p_alias_name	=> 't_rv'
353 		,p_prior_code    => poa_dbi_util_pkg.no_priors);
354 
355     l_join_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl();
356 
357     oki_dbi_util_pvt.join_rpt_where ( p_join_tbl     => l_join_tbl
358                                     , p_func_area    => 'SRM'
359                                     , p_version      => '6.0'
360                                     , p_role         => NULL
361                                     , p_mv_set       => 'SRM_DTL_RPT');
362 
363     oki_dbi_util_pvt.add_join_table (p_join_tbl            => l_join_tbl
364                                   , p_column_name          => 'id'
365                                   , p_table_name           => 'OKI_TERM_REASONS_V'
366         			  , p_table_alias          => 'v'
367                                   , p_fact_column          => 'trn_code'
368                                   , p_additional_where_clause => NULL);
369 
370 
371     l_filter_where  := ' ( abs(OKI_MEASURE_3) + abs(OKI_MEASURE_2) ) <> 0 ';
372 
373 
374     l_query := Get_Trm_Dtl_Sel_Clause(l_curr_suffix, l_period_type)
375 	|| poa_dbi_template_pkg.dtl_status_sql2 (
376                                                p_fact_name         => l_mv
377                                              , p_where_clause      => l_where_clause || l_rpt_specific_where
378                                              , p_join_tables       => l_join_tbl
379                                              , p_use_windowing     => 'Y'
380                                              , p_col_name          => l_col_tbl
381                                              , p_use_grpid         => 'N'
382                                              , p_filter_where      => l_filter_where
383                                              , p_paren_count       => 5
384                                              , p_group_by          => l_group_by
385                                              , p_from_clause       => ' from '||l_mv ||' fact ');
386 
387     x_custom_sql := '/* OKI_DBI_SCM_TRM_DTL_RPT */ '||l_query;
388 
389     OKI_DBI_UTIL_PVT.Get_Custom_Status_Binds(x_custom_output);
390 
391   END Get_Terminations_Detail_Sql;
392 
393 
394 
395 /******************************************************************
396 *  Procedure to return the query for Terminations TREND portlet
397 ******************************************************************/
398   PROCEDURE get_terminations_trend_sql(
399 	p_param			IN		BIS_PMV_PAGE_PARAMETER_TBL,
400 	x_custom_sql		OUT NOCOPY	VARCHAR2,
401 	x_custom_output		OUT NOCOPY	BIS_QUERY_ATTRIBUTES_TBL) IS
402 
403   -- Variables associated with the parameter portlet
404   l_query		VARCHAR2(32767);
405   l_view_by		VARCHAR2(120);
406   l_view_by_col		VARCHAR2(120);
407   l_as_of_date		DATE;
408   l_prev_as_of_date	DATE;
409   l_xtd			VARCHAR2(10);
410   l_comparison_type	VARCHAR2(1);
411   l_nested_pattern	NUMBER;
412   l_dim_bmap		NUMBER;
413   l_cur_suffix		VARCHAR2(2);
414   l_custom_sql		VARCHAR2(10000);
415 
416   l_col_tbl		POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl;
417   l_join_tbl		POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl;
418 
419   l_period_code		VARCHAR2(1);
420   l_where_clause	VARCHAR2(2000);
421   l_mv			VARCHAR2(2000);
422 
423   BEGIN
424 
425     l_comparison_type := 'Y';
426     l_join_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Join_Tbl();
427     l_col_tbl := POA_DBI_UTIL_PKG.Poa_Dbi_Col_Tbl();
428 
429     OKI_DBI_UTIL_PVT.Process_Parameters(
430 	p_param			=> p_param,
431 	p_view_by		=> l_view_by,
432 	p_view_by_col_name	=> l_view_by_col,
433 	p_comparison_type	=> l_comparison_type,
434 	p_xtd			=> l_xtd,
435 	p_as_of_date		=> l_as_of_date,
436 	p_prev_as_of_date	=> l_prev_as_of_date,
437 	p_cur_suffix		=> l_cur_suffix,
438 	p_nested_pattern	=> l_nested_pattern,
439 	p_where_clause		=> l_where_clause,
440 	p_mv			=> l_mv,
441 	p_join_tbl		=> l_join_tbl,
442 	p_period_type		=> l_period_code,
443 	p_trend			=> 'Y',
444 	p_func_area		=> 'OKI',
445 	p_version		=> '7.0',
446 	p_role			=> NULL,
447 	p_mv_set		=> 'SRM_TM_71',
448 	p_rg_where		=> 'Y');
449 
450     POA_DBI_UTIL_PKG.Add_Column(
451 	p_col_tbl		=> l_col_tbl,
452 	p_col_name		=> 't_rv_amt_'||l_cur_suffix,
453 	p_alias_name		=> 't_rv',
454 	p_grand_total		=> 'N',
455 	p_to_date_type		=> 'XTD');
456 
457     l_query := Get_Trm_Trend_Sel_Clause||' FROM '
458       ||POA_DBI_TEMPLATE_PKG.Trend_Sql(
459 		p_xtd			=> l_xtd,
460 		p_comparison_type	=> l_comparison_type,
461 		p_fact_name		=> l_mv,
462 		p_where_clause		=> l_where_clause,
463 		p_col_name		=> l_col_tbl,
464 		p_use_grpid		=> 'N');
465 
466     x_custom_sql := '/* OKI_DBI_SCM_TRM_GPH_RPT */ '||l_query;
467     OKI_DBI_UTIL_PVT.Get_Custom_Trend_Binds(
468 	l_xtd,
469 	l_comparison_type,
470 	x_custom_output);
471 
472   END Get_Terminations_Trend_Sql;
473 
474 /********************************************
475 * Terminations top SQL sel clause for TREND
476 *********************************************/
477 
478   FUNCTION Get_Trm_Trend_Sel_Clause RETURN VARCHAR2 IS
479     l_sel_clause	VARCHAR2(10000);
480 
481   BEGIN
482 
483    --OKI_MEASURE_1: Total Terminated Value
484     l_sel_clause := '
485 SELECT  cal.name		VIEWBY,
486 	nvl(iset.c_t_rv,0)	OKI_MEASURE_1,
487 	nvl(iset.p_t_rv,0)	OKI_PMEASURE_1,
488 	'||OKI_DBI_UTIL_PVT.Change_Clause('nvl(iset.c_t_rv,0)','nvl(iset.p_t_rv,0)','NP')||'	OKI_CHANGE_1';
489 
490     RETURN l_sel_clause;
491   END Get_Trm_Trend_Sel_Clause;
492 
493 
494   END OKI_DBI_NSCM_TRM_PVT;
495