[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_NSCM_ACT_PVT
Source
1 PACKAGE BODY OKI_DBI_NSCM_ACT_PVT AS
2 /* $Header: OKIPNACB.pls 120.4 2006/02/06 00:43:30 pubalasu noship $ */
3
4 /******************************************************************
5 * Procedure to return the query for Activations portlet
6 *
7 ******************************************************************/
8 PROCEDURE get_activations_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)
12 IS
13
14 l_query VARCHAR2 (32767);
15 l_view_by VARCHAR2 (120);
16 l_view_by_col VARCHAR2 (120);
17 l_as_of_date DATE;
18 l_prev_as_of_date DATE;
19 l_xtd VARCHAR2 (10);
20 l_xtd1 VARCHAR2 (10);
21 l_xtd2 VARCHAR2 (10);
22 l_comparison_type VARCHAR2 (1);
23 l_period_type VARCHAR2(10);
24 l_nested_pattern NUMBER;
25 l_cur_suffix VARCHAR2 (2);
26 l_where_clause VARCHAR2 (2000);
27 l_filter_where VARCHAR2 (240);
28 l_where_clause1 VARCHAR2 (2000);
29 l_where_clause2 VARCHAR2 (2000);
30 l_mv VARCHAR2 (2000);
31 l_mv1 VARCHAR2 (2000);
32 l_mv2 VARCHAR2 (2000);
33 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
34 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
35 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
36 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
37 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
38
39 l_to_date_xed CONSTANT VARCHAR2 (3) := 'XED';
40 l_to_date_xtd CONSTANT VARCHAR2 (3) := 'XTD';
41 l_to_date_ytd CONSTANT VARCHAR2 (3) := 'YTD';
42 l_to_date_itd CONSTANT VARCHAR2 (3) := 'ITD';
43 l_balance_logic VARCHAR2(10);
44
45 BEGIN
46
47 l_comparison_type := 'Y';
48 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
49 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
50 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
51 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
52 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
53
54 l_filter_where := ' (ABS(oki_measure_1)+ABS(oki_pmeasure_1)+ABS(oki_measure_3)) <> 0';
55
56 /* Balance logic for OI */
57 l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
58
59 IF (l_balance_logic = 'EVENTDATE') THEN
60 --{
61
62 OKI_DBI_UTIL_PVT.process_parameters ( p_param => p_param
63 , p_view_by => l_view_by
64 , p_view_by_col_name => l_view_by_col
65 , p_comparison_type => l_comparison_type
66 , p_xtd => l_xtd1
67 , p_as_of_date => l_as_of_date
68 , p_prev_as_of_date => l_prev_as_of_date
69 , p_cur_suffix => l_cur_suffix
70 , p_nested_pattern => l_nested_pattern
71 , p_where_clause => l_where_clause1
72 , p_mv => l_mv1
73 , p_join_tbl => l_join_tbl
74 , p_period_type => l_period_type
75 , p_trend => 'N'
76 , p_func_area => 'OKI'
77 , p_version => '6.0'
78 , p_role => NULL
79 , p_mv_set => 'SRM_ST_71' --Change done to support customer classication (NGM)
80 , p_rg_where => 'Y');
81
82 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
83 , p_col_name => 's_g_amt_' || l_cur_suffix
84 , p_alias_name => 'NBsgo'
85 , p_to_date_type => l_to_date_xtd);
86
87
88 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
89 , p_col_name => 's_xg_r_amt_' || l_cur_suffix
90 , p_alias_name => 'AEsxr'
91 , p_to_date_type => l_to_date_xtd);
92
93 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
94 , p_col_name => 's_rg_amt_' || l_cur_suffix
95 , p_alias_name => 'ARsro'
96 , p_to_date_type => l_to_date_xtd);
97
98
99 OKI_DBI_UTIL_PVT.process_parameters ( p_param => p_param
100 , p_view_by => l_view_by
101 , p_view_by_col_name => l_view_by_col
102 , p_comparison_type => l_comparison_type
103 , p_xtd => l_xtd2
104 , p_as_of_date => l_as_of_date
105 , p_prev_as_of_date => l_prev_as_of_date
106 , p_cur_suffix => l_cur_suffix
107 , p_nested_pattern => l_nested_pattern
108 , p_where_clause => l_where_clause2
109 , p_mv => l_mv2
110 , p_join_tbl => l_join_tbl
111 , p_period_type => l_period_type
112 , p_trend => 'N'
113 , p_func_area => 'OKI'
114 , p_version => '6.0'
115 , p_role => NULL
116 , p_mv_set => 'SRM_SG_71' --Change done to support customer classication (NGM)
117 , p_rg_where => 'Y');
118
119 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
120 , p_col_name => 'gl_s_amt_' || l_cur_suffix
121 , p_alias_name => 'NBgo'
122 , p_to_date_type => l_to_date_xtd);
123
124
125 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
126 , p_col_name => 'gL_o_amt_' || l_cur_suffix
127 , p_alias_name => 'AEglr'
128 , p_to_date_type => l_to_date_xtd);
129
130 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
131 , p_col_name => 'gl_r_amt_' || l_cur_suffix
132 , p_alias_name => 'ARgls'
133 , p_to_date_type => l_to_date_xtd);
134
135
136 l_mv_tbl.extend;
137 l_mv_tbl(1).mv_name := l_mv1;
138 l_mv_tbl(1).mv_col := l_col_tbl1;
139 l_mv_tbl(1).mv_where := l_where_clause1;
140 l_mv_tbl(1).in_join_tbls := NULL;
141 l_mv_tbl(1).use_grp_id := 'N';
142 l_mv_tbl(1).mv_xtd := l_xtd1;
143
144 l_mv_tbl.extend;
145
146 l_mv_tbl(2).mv_name := l_mv2;
147 l_mv_tbl(2).mv_col := l_col_tbl2;
148 l_mv_tbl(2).mv_where := l_where_clause2;
149 l_mv_tbl(2).in_join_tbls := NULL;
150 l_mv_tbl(2).use_grp_id := 'N';
151 l_mv_tbl(2).mv_xtd := l_xtd2;
152
153
154 -- Generate sql query
155
156 l_query := get_activations_sel_clause (l_view_by, l_view_by_col)
157
158 || ' from ('
159
160 || poa_dbi_template_pkg.union_all_status_sql
161 (p_mv => l_mv_tbl,
162 p_join_tables => l_join_tbl,
163 p_use_windowing => 'Y',
164 p_paren_count => 1,
165 p_filter_where => NULL,
166 p_generate_viewby => 'N') || ')'
167 || ' oset05 ) oset10) oset15) oset20 ) where' || l_filter_where || ')oset , '
168
169 || poa_dbi_template_pkg.get_viewby_rank_clause ( p_join_tables => l_join_tbl
170 , p_use_windowing => 'Y' );
171
172 --}
173 ELSE
174 --{
175
176 OKI_DBI_UTIL_PVT.process_parameters ( p_param => p_param
177 , p_view_by => l_view_by
178 , p_view_by_col_name => l_view_by_col
179 , p_comparison_type => l_comparison_type
180 , p_xtd => l_xtd
181 , p_as_of_date => l_as_of_date
182 , p_prev_as_of_date => l_prev_as_of_date
183 , p_cur_suffix => l_cur_suffix
184 , p_nested_pattern => l_nested_pattern
185 , p_where_clause => l_where_clause
186 , p_mv => l_mv
187 , p_join_tbl => l_join_tbl
188 , p_period_type => l_period_type
189 , p_trend => 'N'
190 , p_func_area => 'OKI'
191 , p_version => '6.0'
192 , p_role => NULL
193 , p_mv_set => 'SRM_ST_71' --Change done to support customer classication (NGM)
194 , p_rg_where => 'Y');
195
196 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
197 , p_col_name => 's_g_o_amt_' || l_cur_suffix
198 , p_alias_name => 'NBsgo'
199 , p_to_date_type => l_to_date_xtd);
200
201
202 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
203 , p_col_name => 's_x_r_amt_' || l_cur_suffix
204 , p_alias_name => 'AEsxr'
205 , p_to_date_type => l_to_date_xtd);
206
207 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
208 , p_col_name => 's_r_o_amt_' || l_cur_suffix
209 , p_alias_name => 'ARsro'
210 , p_to_date_type => l_to_date_xtd);
211
212 -- Generate sql query
213 l_query := get_activations_sel_clause (l_view_by, l_view_by_col) || ' from '
214
215 || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv
216 , p_where_clause => l_where_clause
217 , p_filter_where => l_filter_where
218 , p_join_tables => l_join_tbl
219 , p_use_windowing => 'Y'
220 , p_col_name => l_col_tbl
221 , p_use_grpid => 'N'
222 , p_paren_count => 6);
223 --}
224 END IF;
225
226 x_custom_sql := l_query;
227
228 OKI_DBI_UTIL_PVT.get_custom_status_binds (x_custom_output);
229
230 END get_activations_sql ;
231
232 /******************************************************************
233 * get activations Select clause SQL - Activations portlet
234 ******************************************************************/
235
236 FUNCTION get_activations_sel_clause (
237 p_view_by_dim IN VARCHAR2
238 , p_view_by_col IN VARCHAR2)
239 RETURN VARCHAR2
240 IS
241 l_sel_clause VARCHAR2 (32767);
242 l_newbus_url VARCHAR2(300);
243 l_activated_url VARCHAR2(300);
244 l_prodcat_url VARCHAR2(300);
245 l_viewby_select VARCHAR2(32767);
246 l_url_select VARCHAR2(32767);
247 l_balance_logic VARCHAR2(10);
248 BEGIN
249
250 /* Balance logic for OI */
251 l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
252
253 l_viewby_select := OKI_DBI_UTIL_PVT.get_viewby_select_clause(p_view_by_dim, 'SRM', '6.0');
254
255 -- when view by is Salesrep OKI_DBI_SCM_ACT_DTL_RPT
256 l_newbus_url := '''pFunctionName=OKI_DBI_SCM_ACT_DTL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&OKI_STATUS+ACT_TYPE=1''';
257
258 l_activated_url := '''pFunctionName=OKI_DBI_SCM_ACT_DTL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&OKI_STATUS+ACT_TYPE=2''';
259
260 IF(p_view_by_dim = 'ITEM+ENI_ITEM_PROD_LEAF_CAT')
261 THEN
262 l_prodcat_url := '''pFunctionName=OKI_DBI_SCM_ACT_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM'' OKI_VBH_CAT_URL ';
263 ELSE
264 l_prodcat_url := ''''' OKI_VBH_CAT_URL ';
265 END IF;
266
267
268 l_viewby_select := l_viewby_select ||
269 ',OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_2 ,OKI_DYNAMIC_URL_5 , '|| l_prodcat_url ||
270 ',OKI_MEASURE_1, OKI_PMEASURE_1, OKI_TMEASURE_1, OKI_CHANGE_1,OKI_TCHANGE_1' ||
271 ', OKI_PERCENT_1,OKI_TPERCENT_1,OKI_PERCENT_CHANGE_1,OKI_MEASURE_2,OKI_TMEASURE_2' ||
272 ',OKI_KPI_MEASURE_2,OKI_PKPI_MEASURE_2,OKI_TKPI_MEASURE_2,OKI_PTKPI_MEASURE_2' ||
273 ',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' ||
274 ',OKI_MEASURE_5,OKI_TMEASURE_5,OKI_KPI_MEASURE_5,OKI_PKPI_MEASURE_5,OKI_TKPI_MEASURE_5' ||
275 ',OKI_PTKPI_MEASURE_5,OKI_PERCENT_5, OKI_TPERCENT_5,OKI_ATTRIBUTE_1,OKI_ATTRIBUTE_4 ' ||
276 ' FROM (SELECT rank() over (&ORDER_BY_CLAUSE nulls last , '||p_view_by_col||') - 1 rnk ,'
277 ||p_view_by_col||',OKI_SALES_GROUP_URL, OKI_DYNAMIC_URL_2 ,OKI_DYNAMIC_URL_5 ,OKI_MEASURE_1 '||
278 ', OKI_PMEASURE_1, OKI_TMEASURE_1, OKI_CHANGE_1,OKI_TCHANGE_1,OKI_PERCENT_1,OKI_TPERCENT_1' ||
279 ',OKI_PERCENT_CHANGE_1,OKI_MEASURE_2,OKI_TMEASURE_2,OKI_KPI_MEASURE_2,OKI_PKPI_MEASURE_2 '||
280 ',OKI_TKPI_MEASURE_2,OKI_PTKPI_MEASURE_2,OKI_PERCENT_2,OKI_TPERCENT_2,OKI_MEASURE_3,OKI_ATTRIBUTE_5,OKI_PERCENT_CHANGE_2 ' ||
284
281 ',OKI_TMEASURE_3,OKI_MEASURE_4,OKI_TMEASURE_4,OKI_MEASURE_5,OKI_TMEASURE_5,OKI_KPI_MEASURE_5 '||
282 ',OKI_PKPI_MEASURE_5,OKI_TKPI_MEASURE_5,OKI_PTKPI_MEASURE_5,OKI_PERCENT_5,OKI_TPERCENT_5,OKI_ATTRIBUTE_1,OKI_ATTRIBUTE_4
283 FROM ( ';
285 IF(p_view_by_dim = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
286 THEN
287 l_url_select :=
288 '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 '||
289 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_newbus_url||')) OKI_DYNAMIC_URL_2 '||
290 ' , decode(resource_id,-999,'''',decode(rg_id,-1,'''','||l_activated_url||')) OKI_DYNAMIC_URL_5 ';
291
292 ELSIF(p_view_by_dim = 'ITEM+ENI_ITEM')
293 THEN
294 l_url_select :=
295 'SELECT '''' OKI_SALES_GROUP_URL '||
296 ' , '||l_newbus_url||' OKI_DYNAMIC_URL_2 '||
297 ' , '||l_activated_url||' OKI_DYNAMIC_URL_5 ';
298 ELSE
299 l_url_select :=
300 'SELECT '''' OKI_SALES_GROUP_URL '||
301 ' , '''' OKI_DYNAMIC_URL_2 '||
302 ' , '''' OKI_DYNAMIC_URL_5 ';
303
304 END IF;
305
306 l_sel_clause := l_viewby_select || l_url_select ||
307 -- AK Attribute naming
308 ' ,'|| p_view_by_col ||
309 ' , oset20.C_TAC OKI_MEASURE_1 '||
310 ' , oset20.p_TAC OKI_PMEASURE_1 '||
311 ' , oset20.C_TAC_tot OKI_TMEASURE_1 '||
312 ' , oset20.TAC_chg OKI_CHANGE_1 '||
313 ' , oset20.TAC_chg_tot OKI_TCHANGE_1 '||
314 ' , oset20.TAC_PoT OKI_PERCENT_1 '||
315 ' , oset20.TAC_PoT_tot OKI_TPERCENT_1 '||
316 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.tac_PoT','oset20.p_tac_PoT','P') || ' OKI_PERCENT_CHANGE_1 '||
317 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.c_NB','oset20.p_NB','NP') || ' OKI_PERCENT_CHANGE_2 '||
318 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.c_NB_tot','oset20.p_NB_tot','NP') || ' OKI_ATTRIBUTE_1'||
319 ' , oset20.C_NB OKI_MEASURE_2 '||
320 ' , oset20.C_NB_tot OKI_TMEASURE_2 '||
321 ' , oset20.C_NB OKI_KPI_MEASURE_2 '||
322 ' , oset20.p_NB OKI_PKPI_MEASURE_2 '||
323 ' , oset20.c_NB_tot OKI_TKPI_MEASURE_2 '||
324 ' , oset20.p_NB_tot OKI_PTKPI_MEASURE_2 '||
325 ' , oset20.NB_PoT OKI_PERCENT_2 '||
326 ' , oset20.NB_PoT_tot OKI_TPERCENT_2 '||
327 ' , oset20.c_AE OKI_MEASURE_3 '||
328 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.c_AR','oset20.p_AR','NP') || ' OKI_ATTRIBUTE_5 '||
329 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset20.c_AR_tot','oset20.p_AR_tot','NP') || ' OKI_ATTRIBUTE_4 '||
330 ' , oset20.c_AE_tot OKI_TMEASURE_3 '||
331 ' , oset20.c_Upl OKI_MEASURE_4 '||
332 ' , oset20.c_Upl_tot OKI_TMEASURE_4 '||
333 ' , oset20.c_AR OKI_MEASURE_5 '||
334 ' , oset20.c_AR_tot OKI_TMEASURE_5 '||
335 ' , oset20.c_AR OKI_KPI_MEASURE_5 '||
336 ' , oset20.c_AR_tot OKI_TKPI_MEASURE_5 '||
337 ' , oset20.p_AR OKI_PKPI_MEASURE_5 '||
338 ' , oset20.p_AR_tot OKI_PTKPI_MEASURE_5 '||
339 ' , oset20.AR_PoT OKI_PERCENT_5 '||
340 ' , oset20.AR_PoT_tot OKI_TPERCENT_5 '||
341 ' from '||
342 ' ( select '||
343 -- Change Calculation
344 ' '|| p_view_by_col ||
345 ' , oset15.C_TAC C_TAC '||
346 ' , oset15.P_TAC P_TAC '||
347 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.C_TAC','oset15.P_TAC','NP') || ' TAC_chg '||
348 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.c_TAC','oset15.c_TAC_tot') || 'tac_PoT '||
349 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.p_TAC','oset15.p_TAC_tot') || 'p_tac_PoT '||
350 ' , oset15.C_TAC_tot C_TAC_tot '||
351 ' ,'||OKI_DBI_UTIL_PVT.change_clause('oset15.C_TAc_tot','oset15.P_TAC_tot','NP') || ' TAC_chg_tot '||
352 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.c_TAC_tot','oset15.c_TAC_tot') || 'tac_PoT_tot '||
353 ' , oset15.c_NB '||
354 ' , oset15.p_NB '||
355 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.c_NB','oset15.c_TAC') || ' NB_PoT '||
356 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.p_NB','oset15.p_TAC') || ' p_NB_PoT '||
357 ' , oset15.c_NB_tot '||
358 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.c_NB_tot','oset15.c_TAC_tot') || ' NB_PoT_tot '||
359 ' , oset15.c_AE '||
360 ' , oset15.c_Upl '||
361 ' , oset15.c_AR '||
362 ' , oset15.p_AE '||
363 ' , oset15.p_AR '||
364 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.c_AR','oset15.c_TAC') || 'AR_PoT '||
365 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.p_AR','oset15.p_TAC') || 'p_AR_PoT '||
366 ' , oset15.p_NB_tot '||
367 ' , oset15.c_AE_tot '||
368 ' , oset15.c_Upl_tot '||
369 ' , oset15.c_AR_tot '||
370 ' , oset15.p_AR_tot '||
371 ' ,'||poa_dbi_util_pkg.rate_clause('oset15.c_AR_tot','oset15.c_TAC_tot') || 'AR_PoT_tot '||
372 ' from '||
373 ' (select '||
374 -- Calculated Measures
375 p_view_by_col ||
376 ' , oset10.c_NB '||
377 ' , oset10.p_NB '||
378 ' , oset10.c_AE '||
379 ' , oset10.c_AR '||
380 ' , oset10.p_AR '||
381 ' , oset10.p_AE '||
385 ' , oset10.c_NB_tot '||
382 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.c_NB','oset10.c_AR') ||' c_TAC '||
383 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.p_NB','oset10.p_AR') ||' p_TAC '||
384 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset10.c_AR','oset10.c_AE') ||' c_Upl '||
386 ' , oset10.p_NB_tot '||
387 ' , oset10.c_AE_tot '||
388 ' , oset10.c_AR_tot '||
389 ' , oset10.p_AR_tot '||
390 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.c_NB_tot','oset10.c_AR_tot') ||' c_TAC_tot '||
391 ' ,'|| OKI_DBI_UTIL_PVT.subtract_measures('oset10.c_AR_tot','oset10.c_AE_tot') ||' c_Upl_tot '||
392 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('oset10.p_NB_tot','oset10.p_AR_tot') ||' p_TAC_tot ';
393
394 IF (l_balance_logic = 'EVENTDATE') THEN
395 --{
396 l_sel_clause := l_sel_clause ||
397 ' from '||
398 ' ( select '||
399 ' oset05.'||p_view_by_col ||
400 ' , nvl(oset05.c_NBsgo + oset05.c_NBgo,0) c_NB '||
401 ' , nvl(oset05.c_AEsxr + oset05.c_AEglr,0) c_AE '||
402 ' , nvl(oset05.c_ARsro + oset05.c_ARgls,0) c_AR '||
403 ' , nvl(oset05.p_NBsgo + oset05.p_NBgo,0) p_NB'||
404 ' , nvl(oset05.p_AEsxr + oset05.p_AEglr,0) p_AE '||
405 ' , nvl(oset05.p_ARsro + oset05.p_ARgls,0) p_AR '||
406 ' , nvl(oset05.c_NBsgo_total + oset05.c_NBgo_total,0) c_NB_tot '||
407 ' , nvl(oset05.c_AEsxr_total + oset05.c_AEglr_total,0) c_AE_tot '||
408 ' , nvl(oset05.c_ARsro_total + oset05.c_ARgls_total,0) c_AR_tot '||
409 ' , nvl(oset05.p_NBsgo_total + oset05.p_NBgo_total,0) p_NB_tot '||
410 ' , nvl(oset05.p_ARsro_total + oset05.p_ARgls_total,0) p_AR_tot ';
411 --}
412 ELSE
413 --{
414 l_sel_clause := l_sel_clause ||
415 ' from '||
416 ' ( select '||
417 ' oset05.'||p_view_by_col ||
418 ' , nvl(oset05.c_NBsgo,0) c_NB '||
419 ' , nvl(oset05.c_AEsxr,0) c_AE '||
420 ' , nvl(oset05.c_ARsro,0) c_AR '||
421 ' , nvl(oset05.p_NBsgo,0) p_NB'||
422 ' , nvl(oset05.p_AEsxr,0) p_AE '||
423 ' , nvl(oset05.p_ARsro,0) p_AR '||
424 ' , nvl(oset05.c_NBsgo_total,0) c_NB_tot '||
425 ' , nvl(oset05.c_AEsxr_total,0) c_AE_tot '||
426 ' , nvl(oset05.c_ARsro_total,0) c_AR_tot '||
427 ' , nvl(oset05.p_NBsgo_total,0) p_NB_tot '||
428 ' , nvl(oset05.p_ARsro_total,0) p_AR_tot ';
429 --}
430 END IF;
431
432 RETURN l_sel_clause;
433 END get_activations_sel_clause;
434
435 /******************************************************************
436 * Procedure to return the query for Activations TREND portlet
437 *
438 ******************************************************************/
439 PROCEDURE get_activations_trend_sql (
440 p_param IN bis_pmv_page_parameter_tbl
441 , x_custom_sql OUT NOCOPY VARCHAR2
442 , x_custom_output OUT NOCOPY bis_query_attributes_tbl) IS
443
444 -- Variables associated with the parameter portlet
445 l_query VARCHAR2 (32767);
446 l_view_by VARCHAR2 (120);
447 l_view_by_col VARCHAR2 (120);
448 l_as_of_date DATE;
449 l_prev_as_of_date DATE;
450 l_xtd VARCHAR2 (10);
451 l_xtd1 VARCHAR2 (10);
452 l_xtd2 VARCHAR2 (10);
453 l_comparison_type VARCHAR2 (1);
454 l_nested_pattern NUMBER;
455 l_dim_bmap NUMBER;
456 l_cur_suffix VARCHAR2 (2);
457 l_custom_sql VARCHAR2 (10000);
458
459 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
460 l_col_tbl1 poa_dbi_util_pkg.poa_dbi_col_tbl;
461 l_col_tbl2 poa_dbi_util_pkg.poa_dbi_col_tbl;
462 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
463 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
464
465 l_period_code varchar2(1);
466 l_where_clause VARCHAR2 (2000);
467 l_where_clause1 VARCHAR2 (2000);
468 l_where_clause2 VARCHAR2 (2000);
469 l_mv VARCHAR2 (2000);
470 l_mv1 VARCHAR2 (2000);
471 l_mv2 VARCHAR2 (2000);
472 l_balance_logic VARCHAR2(10);
473
474 BEGIN
475
476 l_comparison_type := 'Y';
477 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
478 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
479 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
480 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl ();
481 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
482
483 /* Balance logic for OI */
484 l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
485
486 IF (l_balance_logic = 'EVENTDATE') THEN
487 --{
488 OKI_DBI_UTIL_PVT.process_parameters (p_param => p_param
489 , p_view_by => l_view_by
490 , p_view_by_col_name => l_view_by_col
491 , p_comparison_type => l_comparison_type
492 , p_xtd => l_xtd1
493 , p_as_of_date => l_as_of_date
494 , p_prev_as_of_date => l_prev_as_of_date
495 , p_cur_suffix => l_cur_suffix
496 , p_nested_pattern => l_nested_pattern
497 , p_where_clause => l_where_clause1
498 , p_mv => l_mv1
499 , p_join_tbl => l_join_tbl
500 ,p_period_type => l_period_code
501 , p_trend => 'Y'
502 , p_func_area => 'OKI'
503 , p_version => '7.0'
504 , p_role => NULL
505 --, p_mv_set => 'SRM' --NGM
506 , p_mv_set => 'SRM_ST_71' --Change done to support customer classication (NGM)
507 , p_rg_where => 'Y');
508 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
509 , p_col_name => 's_rg_amt_' || l_cur_suffix
510 , p_alias_name => 'sro_amt'
511 , p_grand_total => 'N'
512 , p_to_date_type => 'XTD');
513
514 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1
515 , p_col_name => 's_g_amt_' || l_cur_suffix
516 , p_alias_name => 'sgo_amt'
517 , p_grand_total => 'N'
518 , p_to_date_type => 'XTD');
519
520 OKI_DBI_UTIL_PVT.process_parameters (p_param => p_param
521 , p_view_by => l_view_by
522 , p_view_by_col_name => l_view_by_col
523 , p_comparison_type => l_comparison_type
524 , p_xtd => l_xtd2
525 , p_as_of_date => l_as_of_date
526 , p_prev_as_of_date => l_prev_as_of_date
527 , p_cur_suffix => l_cur_suffix
528 , p_nested_pattern => l_nested_pattern
529 , p_where_clause => l_where_clause2
530 , p_mv => l_mv2
531 , p_join_tbl => l_join_tbl
532 , p_period_type => l_period_code
533 , p_trend => 'Y'
534 , p_func_area => 'OKI'
535 , p_version => '7.0'
536 , p_role => NULL
537 , p_mv_set => 'SRM_SG_71'
538 , p_rg_where => 'Y');
539 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
540 , p_col_name => 'gl_s_amt_' || l_cur_suffix
541 , p_alias_name => 'gls_amt'
542 , p_grand_total => 'N'
543 , p_to_date_type => 'XTD');
544
545 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2
546 , p_col_name => 'gl_r_amt_' || l_cur_suffix
547 , p_alias_name => 'glr_amt'
548 , p_grand_total => 'N'
549 , p_to_date_type => 'XTD');
550
551
552 l_mv_tbl.extend;
556 l_mv_tbl(1).in_join_tbls := NULL;
553 l_mv_tbl(1).mv_name := l_mv1;
554 l_mv_tbl(1).mv_col := l_col_tbl1;
555 l_mv_tbl(1).mv_where := l_where_clause1;
557 l_mv_tbl(1).use_grp_id := 'N';
558 l_mv_tbl(1).mv_xtd := l_xtd1;
559
560 l_mv_tbl.extend;
561
562 l_mv_tbl(2).mv_name := l_mv2;
563 l_mv_tbl(2).mv_col := l_col_tbl2;
564 l_mv_tbl(2).mv_where := l_where_clause2;
565 l_mv_tbl(2).in_join_tbls := NULL;
566 l_mv_tbl(2).use_grp_id := 'N';
567 l_mv_tbl(2).mv_xtd := l_xtd2;
568
569
570
571 l_query := get_act_trend_sel_clause
572 || ' from '
573 ||poa_dbi_template_pkg.union_all_trend_sql
574 (p_mv => l_mv_tbl,
575 p_comparison_type => 'R',
576 p_filter_where => NULL);
577 --}
578 ELSE
579 --{
580 OKI_DBI_UTIL_PVT.process_parameters (p_param => p_param
581 , p_view_by => l_view_by
582 , p_view_by_col_name => l_view_by_col
583 , p_comparison_type => l_comparison_type
584 , p_xtd => l_xtd
585 , p_as_of_date => l_as_of_date
586 , p_prev_as_of_date => l_prev_as_of_date
587 , p_cur_suffix => l_cur_suffix
588 , p_nested_pattern => l_nested_pattern
589 , p_where_clause => l_where_clause
590 , p_mv => l_mv
591 , p_join_tbl => l_join_tbl
592 ,p_period_type => l_period_code
593 , p_trend => 'Y'
594 , p_func_area => 'OKI'
595 , p_version => '7.0'
596 , p_role => NULL
597 --, p_mv_set => 'SRM' --NGM
598 , p_mv_set => 'SRM_ST_71' --Change done to support customer classication (NGM)
599 , p_rg_where => 'Y');
600 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
601 , p_col_name => 's_r_o_amt_' || l_cur_suffix
602 , p_alias_name => 'sro_amt'
603 , p_grand_total => 'N'
604 , p_to_date_type => 'XTD');
605
606 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
607 , p_col_name => 's_g_o_amt_' || l_cur_suffix
608 , p_alias_name => 'sgo_amt'
609 , p_grand_total => 'N'
610 , p_to_date_type => 'XTD');
611
612 l_query :=
613 get_act_trend_sel_clause
614 || ' from '
615 || poa_dbi_template_pkg.trend_sql (p_xtd => l_xtd
616 , p_comparison_type => l_comparison_type
617 , p_fact_name => l_mv
618 , p_where_clause => l_where_clause
619 , p_col_name => l_col_tbl
620 , p_use_grpid => 'N');
621
622 --}
623 END IF;
624
625 x_custom_sql := l_query;
626
627 OKI_DBI_UTIL_PVT.get_custom_trend_binds (l_xtd
628 , l_comparison_type
629 , x_custom_output);
630
631 END get_activations_trend_sql ;
632
633 /*************************************************************
634 * Activations top SQL sel clause for TREND
635 ************************************************************/
636
637 FUNCTION get_act_trend_sel_clause
638 RETURN VARCHAR2
639 IS
640 l_sel_clause VARCHAR2 (10000);
641 l_balance_logic VARCHAR2(10);
642
643 BEGIN
644
645 /* Balance logic for OI */
646 l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
647
648 -- OKI_MEASURE_1 : Total Activated Value
649
650 IF (l_balance_logic = 'EVENTDATE') THEN
651 --{
652 l_sel_clause :=
653 'Select cal_NAME AS VIEWBY '||
654 ' , 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 '||
655 ' , 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 '||
656 ' ,'||OKI_DBI_UTIL_PVT.change_clause('(nvl(uset.c_sro_amt,0)+nvl(uset.c_sgo_amt,0)+nvl(uset.c_glr_amt,0)+
657 nvl(uset.c_gls_amt,0))','(nvl(uset.p_sro_amt,0)+nvl(uset.p_sgo_amt,0)+
658 nvl(uset.p_glr_amt,0)+
659 nvl(uset.p_gls_amt,0))', 'NP' ) || ' OKI_CHANGE_1 ';
660 ELSE
661 --{
662 l_sel_clause :=
663 'Select cal.NAME AS VIEWBY '||
664 ' , nvl(iset.c_sro_amt,0)+nvl(iset.c_sgo_amt,0) OKI_MEASURE_1 '||
665 ' , nvl(iset.p_sro_amt,0)+nvl(iset.p_sgo_amt,0) OKI_PMEASURE_1 '||
666 ' ,'||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 ';
667 --}
668 END IF;
669
670 /* ' ,'|| OKI_DBI_UTIL_PVT.add_measures('iset.c_sro_amt','iset.c_sgo_amt') ||' OKI_MEASURE_1 '||
674 RETURN l_sel_clause;
671 ' ,'|| OKI_DBI_UTIL_PVT.add_measures('iset.p_sro_amt','iset.p_sgo_amt') ||' OKI_PMEASURE_1 '||
672 ' ,'||OKI_DBI_UTIL_PVT.change_clause((OKI_DBI_UTIL_PVT.add_measures('iset.c_sro_amt','iset.c_sgo_amt')), (OKI_DBI_UTIL_PVT.add_measures('iset.p_sro_amt','iset.p_sgo_amt')),'NP') || ' OKI_CHANGE_1 '; */
673
675 END get_act_trend_sel_clause;
676 /******************************************************************
677 * Procedure to return the query for Activations DETAIL report
678 *
679 ******************************************************************/
680 PROCEDURE get_activations_detail_sql (
681 p_param IN bis_pmv_page_parameter_tbl
682 , x_custom_sql OUT NOCOPY VARCHAR2
683 , x_custom_output OUT NOCOPY bis_query_attributes_tbl) IS
684 l_query VARCHAR2 (32767);
685 l_view_by VARCHAR2 (120);
686 l_view_by_col VARCHAR2 (120);
687 l_as_of_date DATE;
688 l_prev_as_of_date DATE;
689 l_xtd VARCHAR2 (10);
690 l_comparison_type VARCHAR2 (1);
691 l_period_type VARCHAR2(10);
692 l_nested_pattern NUMBER;
693 l_cur_suffix VARCHAR2 (2);
694 l_where_clause VARCHAR2 (2000);
695 l_mv VARCHAR2 (2000);
696 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
697 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
698 --l_to_date_xed CONSTANT VARCHAR2 (3) := 'XED';
699 --l_to_date_xtd CONSTANT VARCHAR2 (3) := 'XTD';
700
701 l_rpt_specific_where VARCHAR2 (1000);
702 l_join_where VARCHAR2 (1000);
703 l_group_by VARCHAR2 (1000);
704 l_status_id VARCHAR2 (100);
705 l_filter_where VARCHAR2 (100);
706
707 l_additional_mv VARCHAR2 (2000);
708
709 BEGIN
710
711 l_comparison_type := 'Y';
712 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
713 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl ();
714 OKI_DBI_UTIL_PVT.process_parameters ( p_param => p_param
715 , p_view_by => l_view_by
716 , p_view_by_col_name => l_view_by_col
717 , p_comparison_type => l_comparison_type
718 , p_xtd => l_xtd
719 , p_as_of_date => l_as_of_date
720 , p_prev_as_of_date => l_prev_as_of_date
721 , p_cur_suffix => l_cur_suffix
722 , p_nested_pattern => l_nested_pattern
723 , p_where_clause => l_where_clause
724 , p_mv => l_mv
725 , p_join_tbl => l_join_tbl
726 , p_period_type => l_period_type
727 , p_trend => 'N'
728 , p_func_area => 'OKI'
729 , p_version => '7.0'
730 , p_role => NULL
731 , p_mv_set => 'SRM_CDTL_RPT'
732 , p_rg_where => 'Y');
733
734 -- MODIFIED IN 8.0
735 l_rpt_specific_where :=' AND fact.effective_active_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
736 AND fact.date_signed IS NOT NULL
737 ';
738
739 l_group_by := ' GROUP BY fact.chr_id, fact.customer_party_id, fact.resource_id,fact.date_signed';
740
741 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl
742 , p_col_name => 'price_negotiated_' || l_cur_suffix
743 , p_alias_name => 'act_value'
744 , p_prior_code => poa_dbi_util_pkg.no_priors);
745
746 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl ();
747
748 OKI_DBI_UTIL_PVT.join_rpt_where (p_join_tbl => l_join_tbl
749 , p_func_area => 'SRM'
750 , p_version => '6.0'
751 , p_role => NULL
752 , p_mv_set => 'SRM_CDTL_RPT');
753
754
755 l_filter_where := ' ( ABS(OKI_MEASURE_2) <> 0 ) ';
756
757 l_additional_mv := ' ) fact
758 , OKI_SCM_OCR_MV k
759 WHERE fact.chr_id = k.chr_id) ';
760
761 l_status_id := NVL(OKI_DBI_UTIL_PVT.get_param_id(p_param,'OKI_STATUS+ACT_TYPE'),'''All''');
762
763 if (l_status_id = '''All''' or l_status_id = '''ALL''' or l_status_id is NULL) THEN
764 l_where_clause := l_where_clause ||' ';
765 elsif (l_status_id = '''1''') THEN
766 l_where_clause := l_where_clause ||' AND renewal_flag in (0,2) ';
767 else
768 l_where_clause := l_where_clause ||' AND renewal_flag in (1,3) ';
769 end if;
770
771 l_query := get_act_dtl_sel_clause (l_cur_suffix, l_period_type,l_status_id)
772 || poa_dbi_template_pkg.dtl_status_sql2 (
773 p_fact_name => l_mv
774 , p_where_clause => l_where_clause || l_rpt_specific_where
775 , p_join_tables => l_join_tbl
779 , p_filter_where => l_filter_where||l_additional_mv
776 , p_use_windowing => 'Y'
777 , p_col_name => l_col_tbl
778 , p_use_grpid => 'N'
780 , p_paren_count => 5
781 , p_group_by => l_group_by
782 , p_from_clause => ' from '||l_mv ||' fact ');
783 x_custom_sql := l_query;
784
785 OKI_DBI_UTIL_PVT.get_custom_status_binds (x_custom_output);
786
787 END get_activations_detail_sql ;
788
789 /*********************************************************************
790 * Function to get top SQL for Activations detail report
791 *********************************************************************/
792
793 FUNCTION get_act_dtl_sel_clause (
794 p_cur_suffix IN VARCHAR2
795 , p_period_type_code IN VARCHAR2
796 , p_status_id in VARCHAR2)
797 RETURN VARCHAR2
798 IS
799 l_query VARCHAR2 (10000);
800 BEGIN
801
802 -- Generate sql query
803 l_query :=
804 '
805 SELECT
806 oki_attribute_1,
807 cust.value oki_attribute_2,
808 DECODE(fact.resource_id,-1,&UNASSIGNED,rsex.resource_name) oki_attribute_3,
809 to_char(OKI_DATE_3) OKI_DATE_3,
810 to_char(OKI_DATE_1) OKI_DATE_1,
811 to_char(OKI_DATE_2) OKI_DATE_2,
812 OKI_MEASURE_1,
813 OKI_TMEASURE_1,
814 OKI_MEASURE_2,
815 OKI_TMEASURE_2,
816 fact.chr_id OKI_ATTRIBUTE_5
817 FROM (select *
818 FROM (
819 SELECT
820 rank() over (&ORDER_BY_CLAUSE nulls last) - 1 rnk ,
821 chr_id,
822 customer_party_id,
823 resource_id,
824 oki_measure_2,
825 oki_tmeasure_2,
826 oki_date_3,
827 oki_date_1,
828 oki_date_2,
829 oki_attribute_1,
830 oki_measure_1,
831 oki_tmeasure_1
832 FROM (SELECT fact.*
833 , k.start_date OKI_DATE_1
834 , k.end_date OKI_DATE_2
835 , k.COMPLETE_k_number oki_attribute_1
836 , k.price_nego_' ||p_cur_suffix ||' OKI_MEASURE_1
837 , SUM(k.price_nego_' ||p_cur_suffix ||') over () OKI_TMEASURE_1
838 FROM (SELECT *
839 FROM (
840 SELECT oset5.chr_id ,
841 oset5.customer_party_id ,
842 oset5.resource_id ,
843 oset5.act_value OKI_MEASURE_2,
844 oset5.act_value_total OKI_TMEASURE_2,
845 oset5.date_signed OKI_DATE_3
846 FROM
847 (SELECT
848 fact.chr_id,
849 fact.customer_party_id,
850 fact.resource_id,
851 fact.date_signed
852 ';
853
854 RETURN l_query;
855 END get_act_dtl_sel_clause;
856
857 /*****************************************************************
858 * Function get activationed detail value based on type selected
859 ******************************************************************/
860
861 Function new_ren_detail( p_type IN VARCHAR2
862 , p_new IN NUMBER
863 , p_ren IN NUMBER ) RETURN NUMBER IS
864 BEGIN
865 if (p_type = 'All' or p_type = 'ALL' or p_type is NULL) THEN
866 return NVL(p_new,0)+NVL(p_ren,0);
867 elsif (p_type = '1') THEN
868 return p_new;
869 else
870 return p_ren;
871 end if;
872 END new_ren_detail;
873
874 END OKI_DBI_NSCM_ACT_PVT;