[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_RDE_PKG
Source
1 PACKAGE BODY poa_dbi_rde_pkg
2 /* $Header: poadbirdeb.pls 120.1 2005/08/04 06:13:24 sriswami noship $ */
3 AS
4 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_url in VARCHAR2,p_view_by_col in VARCHAR2) return VARCHAR2;
5 FUNCTION get_trend_sel_clause return VARCHAR2;
6 FUNCTION get_rate_trend_sel_clause return VARCHAR2;
7 FUNCTION get_status_it_sel_clause(p_view_by_dim IN VARCHAR2,p_view_by_col in VARCHAR2) return VARCHAR2;
8 FUNCTION get_status_filter_where return VARCHAR2;
9 FUNCTION get_it_rpt_filter_where return VARCHAR2;
10 FUNCTION get_amt_rpt_sel_clause(p_view_by_dim in VARCHAR2, p_url in VARCHAR2,p_view_by_col in VARCHAR2) return VARCHAR2;
11 FUNCTION get_txn_rpt_sel_clause(p_view_by_dim in VARCHAR2, p_url in VARCHAR2,p_view_by_col in VARCHAR2) return VARCHAR2;
12
13 FUNCTION get_amt_rpt_filter_where return VARCHAR2;
14 FUNCTION get_txn_rpt_filter_where return VARCHAR2;
15
16
17 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
18 x_custom_sql OUT NOCOPY VARCHAR2,
19 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
20 IS
21 l_query varchar2(20000);
22 l_view_by_col varchar2(120);
23 l_view_by_value varchar2(300);
24 l_view_by varchar2(120);
25 l_as_of_date date;
26 l_prev_as_of_date date;
27 l_xtd varchar2(10);
28
29 l_comparison_type varchar2(1) := 'Y';
30 l_nested_pattern number;
31 l_cur_suffix varchar2(2);
32 l_custom_sql varchar2(10000);
33 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
34 l_url varchar2(300);
35 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
36 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
37 l_where_clause VARCHAR2(2000);
38 l_mv VARCHAR2(30);
39 ERR_MSG VARCHAR2(100);
40 ERR_CDE NUMBER;
41 l_context_code VARCHAR2(10);
42 l_to_date_type VARCHAR2(10);
43 BEGIN
44 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
45
46 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
47 poa_dbi_sutil_pkg.process_parameters(p_param
48 ,l_view_by
49 ,l_view_by_col
50 ,l_view_by_value
51 ,l_comparison_type
52 ,l_xtd
53 ,l_as_of_date
54 ,l_prev_as_of_date
55 ,l_cur_suffix
56 ,l_nested_pattern
57 ,l_where_clause
58 ,l_mv
59
60 ,l_join_tbl
61 ,l_in_join_tbl
62 , x_custom_output
63 ,'N'
64 ,'PO'
65 ,'6.0'
66 ,'COM'
67 ,'RTX');
68 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
69 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
70 l_to_date_type := 'RLX';
71 ELSE
72 l_to_date_type := 'XTD';
73 END IF;
74
75 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_expt_' || l_cur_suffix, 'amt_expt',p_to_date_type => l_to_date_type);
76 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_early_cnt', 'early_cnt',p_to_date_type => l_to_date_type);
77 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_late_cnt', 'late_cnt',p_to_date_type => l_to_date_type);
78 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_receipt_' || l_cur_suffix, 'amt_receipt',p_to_date_type => l_to_date_type);
79
80 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_receipt_cnt', 'cnt_receipt',p_to_date_type => l_to_date_type);
81 if((l_view_by = 'ITEM+POA_ITEMS')) then
82 l_url :=
83 'pFunctionName=POA_DBI_RDE_IT_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y';
84
85 else
86 l_url := null;
87
88 end if;
89 l_query := get_status_sel_clause(l_view_by, l_url,l_view_by_col) || ' from ' ||
90 poa_dbi_template_pkg.status_sql(l_mv,
91 l_where_clause,
92 l_join_tbl,
93 p_use_windowing => 'Y',
94 p_col_name => l_col_tbl,
95 p_use_grpid => 'N',
96 p_filter_where => get_status_filter_where,
97 p_in_join_tables => l_in_join_tbl);
98
99 x_custom_sql := l_query;
100
101 end;
102
103
104 PROCEDURE rate_trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
105 x_custom_sql OUT NOCOPY VARCHAR2,
106 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
107 IS
108 l_query varchar2(10000);
109 l_view_by varchar2(120);
110 l_view_by_col varchar2(120);
111 l_view_by_value VARCHAR2(300);
112 l_as_of_date date;
113 l_prev_as_of_date date;
114 l_xtd varchar2(10);
115 l_comparison_type varchar2(1) := 'Y';
116 l_nested_pattern number;
117 l_cur_suffix varchar2(2);
118 l_custom_sql varchar2(10000);
119 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
120 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
121 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
122 l_mv VARCHAR2(30);
123 l_where_clause VARCHAR2(2000);
124 l_context_code VARCHAR2(10);
125 l_to_date_type VARCHAR2(10);
126 BEGIN
127 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
128 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
129 poa_dbi_sutil_pkg.process_parameters(p_param
130 ,l_view_by
131 ,l_view_by_col
132 ,l_view_by_value
133 ,l_comparison_type
134 ,l_xtd
135 ,l_as_of_date
136 ,l_prev_as_of_date
137 ,l_cur_suffix
138 ,l_nested_pattern
139 ,l_where_clause
140 ,l_mv
141 ,l_join_tbl,l_in_join_tbl
142 ,x_custom_output,
143 'Y','PO', '6.0', 'COM','RTX');
144 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
145 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
146 l_to_date_type := 'RLX';
147 ELSE
148 l_to_date_type := 'XTD';
149 END IF;
150
151 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_receipt_' || l_cur_suffix,'amt_receipt', 'N',p_to_date_type => l_to_date_type);
152 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_expt_' || l_cur_suffix,'amt_expt', 'N',p_to_date_type => l_to_date_type);
153 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_receipt_cnt', 'cnt_receipt', 'N',p_to_date_type => l_to_date_type);
154 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_early_cnt','cnt_early', 'N',p_to_date_type => l_to_date_type);
155 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_late_cnt','cnt_late', 'N',p_to_date_type => l_to_date_type);
156
157
158
159
160 l_query := get_rate_trend_sel_clause || ' from
161 '|| poa_dbi_template_pkg.trend_sql(l_xtd,
162 l_comparison_type,
163 l_mv,
164 l_where_clause,
165 l_col_tbl,
166 p_use_grpid => 'N',
167 p_in_join_tables => l_in_join_tbl);
168 x_custom_sql := l_query;
169 END;
170
171
172 FUNCTION get_rate_trend_sel_clause return VARCHAR2 IS
173 l_sel_clause varchar2(4000);
174 BEGIN
175 l_sel_clause :='select cal.name VIEWBY,
176 ' || poa_dbi_util_pkg.rate_clause('c_amt_expt','c_amt_receipt') || ' POA_PERCENT1,
177 ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_amt_expt','c_amt_receipt'),poa_dbi_util_pkg.rate_clause('p_amt_expt','p_amt_receipt'),'P') || ' POA_MEASURE1,
178 ' ||
179 poa_dbi_util_pkg.rate_clause('(nvl(c_cnt_early,0) + nvl(c_cnt_late,0))','c_cnt_receipt') || ' POA_PERCENT3,
180 ' || poa_dbi_util_pkg.change_clause(
181 poa_dbi_util_pkg.rate_clause('(nvl(c_cnt_early,0) + nvl(c_cnt_late,0))','c_cnt_receipt'),
182 poa_dbi_util_pkg.rate_clause('(nvl(p_cnt_early,0) + nvl(p_cnt_late,0))','p_cnt_receipt'),'P') || ' POA_MEASURE2';
183 return l_sel_clause;
184 END;
185
186
187
188 PROCEDURE amt_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
189 x_custom_sql OUT NOCOPY VARCHAR2,
190 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
191 IS
192 l_query varchar2(20000);
193 l_view_by_col varchar2(120);
194 l_view_by_value varchar2(300);
195 l_view_by varchar2(120);
196 l_as_of_date date;
197 l_prev_as_of_date date;
198 l_xtd varchar2(10);
199
200 l_comparison_type varchar2(1) := 'Y';
201 l_nested_pattern number;
202 l_cur_suffix varchar2(2);
203 l_custom_sql varchar2(10000);
204 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
205 l_url varchar2(300);
206 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
207 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
208 l_where_clause VARCHAR2(2000);
209 l_mv VARCHAR2(30);
210 ERR_MSG VARCHAR2(100);
211 ERR_CDE NUMBER;
212 l_context_code VARCHAR2(10);
213 l_to_date_type VARCHAR2(10);
214 BEGIN
215 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
216
217 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
218 poa_dbi_sutil_pkg.process_parameters(p_param
219 ,l_view_by
220 ,l_view_by_col
221 ,l_view_by_value
222 ,l_comparison_type
223 ,l_xtd
224 ,l_as_of_date
225 ,l_prev_as_of_date
226 ,l_cur_suffix
227 ,l_nested_pattern
228 ,l_where_clause
229 ,l_mv
230
231 ,l_join_tbl
232 ,l_in_join_tbl
233 , x_custom_output
234 ,'N'
235 ,'PO'
236 ,'6.0'
237 ,'COM'
238 ,'RTX');
239 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
240 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
241 l_to_date_type := 'RLX';
242 ELSE
243 l_to_date_type := 'XTD';
244 END IF;
245
246 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_expt_' || l_cur_suffix, 'amt_expt',p_to_date_type => l_to_date_type);
247 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_receipt_' || l_cur_suffix, 'amt_receipt',p_to_date_type => l_to_date_type);
248 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_beforedue_' || l_cur_suffix, 'amt_early',p_to_date_type => l_to_date_type);
249 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_days_early', 'num_days_early',p_to_date_type => l_to_date_type);
250 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_early_cnt', 'early_cnt',p_to_date_type => l_to_date_type);
251 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_afterdue_' || l_cur_suffix, 'amt_late',p_to_date_type => l_to_date_type);
252 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_days_late', 'num_days_late',p_to_date_type => l_to_date_type);
253 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_late_cnt', 'late_cnt',p_to_date_type => l_to_date_type);
254 if((l_view_by = 'ITEM+POA_ITEMS')) then
255 l_url :=
256 'pFunctionName=POA_DBI_RDE_IT_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y';
257
258 else
259 l_url := null;
260
261 end if;
262 l_query := get_amt_rpt_sel_clause(l_view_by, l_url,l_view_by_col) || ' from ' ||
263 poa_dbi_template_pkg.status_sql(l_mv,
264 l_where_clause,
265 l_join_tbl,
266 p_use_windowing => 'Y',
267 p_col_name => l_col_tbl,
268 p_use_grpid => 'N',
269 p_filter_where => get_amt_rpt_filter_where,
270 p_in_join_tables => l_in_join_tbl);
271
272 x_custom_sql := l_query;
273
274 end;
275
276
277
278 PROCEDURE txn_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
279 x_custom_sql OUT NOCOPY VARCHAR2,
280 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
281 IS
282 l_query varchar2(20000);
283 l_view_by_col varchar2(120);
284 l_view_by_value varchar2(300);
285 l_view_by varchar2(120);
286 l_as_of_date date;
287 l_prev_as_of_date date;
288 l_xtd varchar2(10);
289
290 l_comparison_type varchar2(1) := 'Y';
291 l_nested_pattern number;
292 l_cur_suffix varchar2(2);
293 l_custom_sql varchar2(10000);
294 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
295 l_url varchar2(300);
296 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
297 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
298 l_where_clause VARCHAR2(2000);
299 l_mv VARCHAR2(30);
300 ERR_MSG VARCHAR2(100);
301 ERR_CDE NUMBER;
302 l_context_code VARCHAR2(10);
303 l_to_date_type VARCHAR2(10);
304 BEGIN
305 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
306
307 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
308 poa_dbi_sutil_pkg.process_parameters(p_param
309 ,l_view_by
310 ,l_view_by_col
311 ,l_view_by_value
312 ,l_comparison_type
313 ,l_xtd
314 ,l_as_of_date
315 ,l_prev_as_of_date
319 ,l_mv
316 ,l_cur_suffix
317 ,l_nested_pattern
318 ,l_where_clause
320
321 ,l_join_tbl
322 ,l_in_join_tbl
323 , x_custom_output
324 ,'N'
325 ,'PO'
326 ,'6.0'
327 ,'COM'
328 ,'RTX');
329 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
330 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
331 l_to_date_type := 'RLX';
332 ELSE
333 l_to_date_type := 'XTD';
334 END IF;
335
336 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_receipt_cnt', 'cnt_receipt',p_to_date_type => l_to_date_type);
337 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_days_early', 'num_days_early',p_to_date_type => l_to_date_type);
338 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_early_cnt', 'early_cnt',p_to_date_type => l_to_date_type);
339 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_days_late', 'num_days_late',p_to_date_type => l_to_date_type);
340 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_late_cnt', 'late_cnt',p_to_date_type => l_to_date_type);
341 if((l_view_by = 'ITEM+POA_ITEMS')) then
342 l_url :=
343 'pFunctionName=POA_DBI_RDE_IT_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y';
344
345 else
346 l_url := null;
347
348 end if;
349 l_query := get_txn_rpt_sel_clause(l_view_by, l_url,l_view_by_col) || ' from ' ||
350 poa_dbi_template_pkg.status_sql(l_mv,
351 l_where_clause,
352 l_join_tbl,
353 p_use_windowing => 'Y',
354 p_col_name => l_col_tbl,
355 p_use_grpid => 'N',
356 p_filter_where => get_amt_rpt_filter_where,
357 p_in_join_tables => l_in_join_tbl);
358
359 x_custom_sql := l_query;
360
361 end;
362
363
364
365
366
367 PROCEDURE it_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
368 x_custom_sql OUT NOCOPY VARCHAR2,
369 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
370 IS
371 l_query varchar2(10000);
372 l_view_by varchar2(120);
373 l_view_by_col varchar2(120);
374 l_view_by_value VARCHAR2(300);
375 l_as_of_date date;
376 l_prev_as_of_date date;
377 l_xtd varchar2(10);
378 l_comparison_type varchar2(1) := 'Y';
379 l_nested_pattern number;
380 l_cur_suffix varchar2(2);
381 l_custom_sql varchar2(10000);
382 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
383 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
384 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
385 l_where_clause VARCHAR2(2000);
386 l_mv VARCHAR2(30);
387 l_context_code VARCHAR2(10);
388 l_to_date_type VARCHAR2(10);
389 BEGIN
390 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
391 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
392
393 poa_dbi_sutil_pkg.process_parameters(p_param
394 ,l_view_by
395 ,l_view_by_col
396 ,l_view_by_value
397 ,l_comparison_type
398 ,l_xtd
399 ,l_as_of_date
400 ,l_prev_as_of_date
401 ,l_cur_suffix
402 ,l_nested_pattern
403 ,l_where_clause
404 ,l_mv
405 ,l_join_tbl
406 ,l_in_join_tbl
407 , x_custom_output
408 ,'N'
409 ,'PO'
410 ,'6.0'
411 ,'COM'
412 ,'RTX');
413 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
414 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
415 l_to_date_type := 'RLX';
416 ELSE
417 l_to_date_type := 'XTD';
418 END IF;
419
420 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_early_cnt', 'early_cnt',p_to_date_type => l_to_date_type);
421 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_late_cnt', 'late_cnt',p_to_date_type => l_to_date_type);
422 poa_dbi_util_pkg.add_column(l_col_tbl, 'qty_beforedue', 'qty_beforedue',p_to_date_type => l_to_date_type);
423
424 poa_dbi_util_pkg.add_column(l_col_tbl, 'qty_intol', 'qty_intol',p_to_date_type => l_to_date_type);
425 poa_dbi_util_pkg.add_column(l_col_tbl, 'qty_afterdue', 'qty_afterdue',p_to_date_type => l_to_date_type);
426 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_receipt_' || l_cur_suffix, 'amt_receipt',p_to_date_type => l_to_date_type);
427 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_receipt_cnt', 'cnt_receipt',p_to_date_type => l_to_date_type);
428 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_expt_' || l_cur_suffix, 'amt_expt',p_to_date_type => l_to_date_type);
429 l_query := get_status_it_sel_clause(l_view_by,l_view_by_col) || ' from
430 ' || poa_dbi_template_pkg.status_sql(l_mv,
431 l_where_clause,
435 p_use_grpid => 'N',
432 l_join_tbl,
433 p_use_windowing => 'Y',
434 p_col_name => l_col_tbl,
436 p_filter_where => get_it_rpt_filter_where,
437 p_in_join_tables => l_in_join_tbl);
438 x_custom_sql := l_query;
439
440
441 END;
442
443
444 FUNCTION get_status_filter_where return VARCHAR2
445
446 IS
447 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
448 BEGIN
449 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
450 l_col_tbl.extend;
451 l_col_tbl(1) := 'POA_MEASURE1';
452 l_col_tbl.extend;
453 l_col_tbl(2) := 'POA_PERCENT1';
454 l_col_tbl.extend;
455 l_col_tbl(3) := 'POA_MEASURE2';
456 l_col_tbl.extend;
457 l_col_tbl(4) := 'POA_PERCENT2';
458 l_col_tbl.extend;
459 l_col_tbl(5) := 'POA_PERCENT3';
460 l_col_tbl.extend;
461 l_col_tbl(6) := 'POA_MEASURE3';
462 l_col_tbl.extend;
463 l_col_tbl(7) := 'POA_MEASURE14';
464 l_col_tbl.extend;
465 l_col_tbl(8) := 'POA_MEASURE15';
466 l_col_tbl.extend;
467 l_col_tbl(9) := 'POA_ATTRIBUTE3';
468
469 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
470
471 END;
472
473 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_url in VARCHAR2, p_view_by_col in VARCHAR2) return VARCHAR2
474 IS
475 l_sel_clause varchar2(8000);
476 l_view_by_col_name varchar2(40);
477 BEGIN
478 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
479 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
480 l_sel_clause := l_sel_clause ||'
481 v.description POA_ATTRIBUTE1, --Description
482 ';
483 else
484 l_sel_clause := l_sel_clause || ' null POA_ATTRIBUTE1, --Description
485 ';
486 end if;
487 l_sel_clause := l_sel_clause ||
488 ' oset.POA_MEASURE1 POA_MEASURE1, -- Exception Amount
489 oset.POA_PERCENT1 POA_PERCENT1, -- Change
490 oset.POA_MEASURE2 POA_MEASURE2, -- Receipt Amount
491 oset.POA_PERCENT2 POA_PERCENT2, -- Exception Amount Rate
492 oset.POA_PERCENT3 POA_PERCENT3, -- Change
493 oset.POA_MEASURE3 POA_MEASURE3, -- Exception Transactions
494 oset.POA_MEASURE14 POA_MEASURE14, -- Change
495 oset.POA_MEASURE15 POA_MEASURE15, -- Exception Transactions Rate
496 oset.POA_ATTRIBUTE3 POA_ATTRIBUTE3, -- Change
497 oset.POA_MEASURE4 POA_MEASURE4, -- Total Exception Amount
498 oset.POA_MEASURE5 POA_MEASURE5, -- Total Change
499 oset.POA_MEASURE6 POA_MEASURE6, -- Total Receipt Amount
500 oset.POA_MEASURE7 POA_MEASURE7, -- Total Exception Amount Rate
501 oset.POA_MEASURE8 POA_MEASURE8, -- Total Change
502 oset.POA_MEASURE9 POA_MEASURE9, -- Total Exception Transactions
503 oset.POA_MEASURE10 POA_MEASURE10, -- Total Change
504 oset.POA_MEASURE11 POA_MEASURE11, -- Total Exception Transaction Rate
505 oset.POA_MEASURE12 POA_MEASURE12, -- Total Change
506 oset.POA_MEASURE1 POA_MEASURE13, -- Exception Amount for horiz bar chart
507 oset.POA_MEASURE16 POA_MEASURE16, -- KPI Prior Amt Rate
508 -- oset.POA_MEASURE7 POA_MEASURE19, -- KPI Total Amt Rate
509 oset.POA_MEASURE20 POA_MEASURE20, -- KPI Total Prior Amt Rate
510 oset.POA_MEASURE17 POA_MEASURE17, -- KPI prior txn rate
511 oset.POA_MEASURE22 POA_MEASURE22, -- KPI Total Prior txn rate ';
512 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
513 l_sel_clause := l_sel_clause || '
514 ''' || p_url || ''' POA_ATTRIBUTE5';
515 else
516 l_sel_clause := l_sel_clause || '
517 null POA_ATTRIBUTE5';
518 end if;
519 l_sel_clause := l_sel_clause || '
520 from
521 (select (rank() over
522 ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col ;
523
524 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
525 l_sel_clause := l_sel_clause || ', base_uom';
526 end if;
527
528 l_sel_clause := l_sel_clause || ')) - 1 rnk,'|| p_view_by_col ||',' ;
529
530 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
531 l_sel_clause := l_sel_clause ||' base_uom,';
532 end if;
533 l_sel_clause := l_sel_clause ||' POA_MEASURE1, POA_PERCENT1,
534 POA_MEASURE2, POA_PERCENT2,
535 POA_PERCENT3, POA_MEASURE3,
536 POA_MEASURE14, POA_MEASURE15,
537 POA_ATTRIBUTE3, POA_MEASURE4,
538 POA_MEASURE5, POA_MEASURE6,
539 POA_MEASURE7, POA_MEASURE8,
540 POA_MEASURE9, POA_MEASURE10,
541 POA_MEASURE11, POA_MEASURE12,
542 POA_MEASURE16,POA_MEASURE20,
543 POA_MEASURE17, POA_MEASURE22
544
545 from
546 (select ' || p_view_by_col || ',
547 ' || p_view_by_col || ' VIEWBY,
548 ';
549 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
550 l_sel_clause := l_sel_clause ||' base_uom,';
551 end if;
552 l_sel_clause := l_sel_clause ||
553 ' nvl(c_amt_expt,0) POA_MEASURE1,
554 ' || poa_dbi_util_pkg.change_clause('c_amt_expt','p_amt_expt') || ' POA_PERCENT1,
555 nvl(c_amt_receipt,0) POA_MEASURE2,
556 ' || poa_dbi_util_pkg.rate_clause('c_amt_expt','c_amt_receipt') || ' POA_PERCENT2,
557 ' || poa_dbi_util_pkg.change_clause(
558 poa_dbi_util_pkg.rate_clause('c_amt_expt', 'c_amt_receipt'),
559 poa_dbi_util_pkg.rate_clause('p_amt_expt', 'p_amt_receipt'),
560 'P') ||
561 ' POA_PERCENT3,
562 nvl(c_early_cnt,0) + nvl(c_late_cnt,0) POA_MEASURE3,
566 poa_dbi_util_pkg.rate_clause('(nvl(c_early_cnt,0) + nvl(c_late_cnt,0))','(nvl(c_cnt_receipt,0))'),
563 ' || poa_dbi_util_pkg.change_clause('(nvl(c_early_cnt,0)+nvl(c_late_cnt,0))','(nvl(p_early_cnt,0)+nvl(p_late_cnt,0))') || ' POA_MEASURE14,
564 ' || poa_dbi_util_pkg.rate_clause('(nvl(c_early_cnt,0) + nvl(c_late_cnt,0))','(nvl(c_cnt_receipt,0))') || ' POA_MEASURE15,
565 ' || poa_dbi_util_pkg.change_clause(
567 poa_dbi_util_pkg.rate_clause('(nvl(p_early_cnt,0) + nvl(p_late_cnt,0))','(nvl(p_cnt_receipt,0))'),
568 'P') ||
569 ' POA_ATTRIBUTE3,
570 nvl(c_amt_expt_total,0) POA_MEASURE4,
571 ' || poa_dbi_util_pkg.change_clause('c_amt_expt_total','p_amt_expt_total')
572 || ' POA_MEASURE5,
573 nvl(c_amt_receipt_total,0) POA_MEASURE6,
574 ' || poa_dbi_util_pkg.rate_clause('c_amt_expt_total','c_amt_receipt_total')
575 || ' POA_MEASURE7,
576 ' || poa_dbi_util_pkg.change_clause(
577 poa_dbi_util_pkg.rate_clause('c_amt_expt_total', 'c_amt_receipt_total'),
578 poa_dbi_util_pkg.rate_clause('p_amt_expt_total', 'p_amt_receipt_total'),
579 'P') ||
580 ' POA_MEASURE8,
581 nvl(c_early_cnt_total,0) + nvl(c_late_cnt_total,0) POA_MEASURE9,
582 ' || poa_dbi_util_pkg.change_clause('(nvl(c_early_cnt_total,0)+nvl(c_late_cnt_total,0))','(nvl(p_early_cnt_total,0)+nvl(p_late_cnt_total,0))') || ' POA_MEASURE10,
583 ' || poa_dbi_util_pkg.rate_clause('(nvl(c_early_cnt_total,0) + nvl(c_late_cnt_total,0))','(nvl(c_cnt_receipt_total,0))') || ' POA_MEASURE11,
584 ' || poa_dbi_util_pkg.change_clause(
585 poa_dbi_util_pkg.rate_clause('(nvl(c_early_cnt_total,0) + nvl(c_late_cnt_total,0))','(nvl(c_cnt_receipt_total,0))'),
586 poa_dbi_util_pkg.rate_clause('(nvl(p_early_cnt_total,0) + nvl(p_late_cnt_total,0))','(nvl(p_cnt_receipt_total,0))'),
587 'P') ||
588 ' POA_MEASURE12,
589 ' || poa_dbi_util_pkg.rate_clause('p_amt_expt','p_amt_receipt') || ' POA_MEASURE16,
590 ' || poa_dbi_util_pkg.rate_clause('p_amt_expt_total','p_amt_receipt_total') || ' POA_MEASURE20,
591 ' || poa_dbi_util_pkg.rate_clause('(nvl(p_early_cnt,0) + nvl(p_late_cnt,0))','(nvl(p_cnt_receipt,0))') || ' POA_MEASURE17,
592 ' || poa_dbi_util_pkg.rate_clause('(nvl(p_early_cnt_total,0) + nvl(p_late_cnt_total,0))','(nvl(p_cnt_receipt_total,0))') || ' POA_MEASURE22';
593
594 return l_sel_clause;
595 END;
596
597
598
599 FUNCTION get_amt_rpt_sel_clause(p_view_by_dim in VARCHAR2
600 ,p_url in VARCHAR2
601 ,p_view_by_col in VARCHAR2) return VARCHAR2
602 IS
603 l_sel_clause varchar2(4000);
604 l_view_by_col_name varchar2(40);
605 BEGIN
606 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
607 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
608 l_sel_clause := l_sel_clause ||'
609 v.description POA_ATTRIBUTE1, --Description
610 ';
611 else
612 l_sel_clause := l_sel_clause || ' null POA_ATTRIBUTE1, --Description
613 ';
614 end if;
615 l_sel_clause := l_sel_clause ||
616 'oset.POA_MEASURE1 POA_MEASURE1, --Exception Amount
617 oset.POA_PERCENT1 POA_PERCENT1, --Change
618 oset.POA_MEASURE2 POA_MEASURE2, --Receipt Amount
619 oset.POA_PERCENT2 POA_PERCENT2, --Exception Amount Rate
620 oset.POA_MEASURE3 POA_MEASURE3, --Early Amount
621 oset.POA_MEASURE4 POA_MEASURE4, --Avg Days Early
622 oset.POA_MEASURE5 POA_MEASURE5, --Late Amount
623 oset.POA_MEASURE6 POA_MEASURE6, --Avg Days Late
624 oset.POA_MEASURE7 POA_MEASURE7, --Total Exception Amount Rate
625 oset.POA_MEASURE8 POA_MEASURE8, --Total Change
626 oset.POA_MEASURE9 POA_MEASURE9, --Total Receipt Amount
627 oset.POA_MEASURE10 POA_MEASURE10, --Total Exception Amount Rate
628 oset.POA_MEASURE11 POA_MEASURE11, --Total Early Amount
629 oset.POA_MEASURE12 POA_MEASURE12, --Total Avg Days Early
630 oset.POA_MEASURE13 POA_MEASURE13, --Total Late Amt
631 oset.POA_MEASURE14 POA_MEASURE14, --Total Avg Days Late
632 oset.POA_MEASURE15 POA_MEASURE15, --In Tolerance Amt';
633 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
634 l_sel_clause := l_sel_clause || '
635 ''' || p_url || ''' POA_ATTRIBUTE4';
636 else
637 l_sel_clause := l_sel_clause || '
638 null POA_ATTRIBUTE4';
639 end if;
640 l_sel_clause := l_sel_clause || '
641 from
642 (select (rank() over
643 ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
644
645 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
646 l_sel_clause := l_sel_clause || ', base_uom';
647 end if;
648
649 l_sel_clause := l_sel_clause || ')) - 1 rnk,'|| p_view_by_col ||',' ;
650 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
651 l_sel_clause := l_sel_clause ||' base_uom,';
652 end if;
653 l_sel_clause := l_sel_clause ||' POA_MEASURE1, POA_PERCENT1,
654 POA_MEASURE2, POA_PERCENT2,
655 POA_MEASURE3, POA_MEASURE4,
656 POA_MEASURE5, POA_MEASURE6,
657 POA_MEASURE7, POA_MEASURE8,
658 POA_MEASURE9, POA_MEASURE10,
659 POA_MEASURE11, POA_MEASURE12,
660 POA_MEASURE13, POA_MEASURE14,
661 POA_MEASURE15
662 from
663 (select ' || p_view_by_col || ',
664 ' || p_view_by_col || ' VIEWBY,';
665 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
666 l_sel_clause := l_sel_clause ||' base_uom,';
667 end if;
668 l_sel_clause := l_sel_clause ||
669 'nvl(c_amt_expt,0) POA_MEASURE1,
673 nvl(c_amt_early,0) POA_MEASURE3,
670 ' || poa_dbi_util_pkg.change_clause('c_amt_expt','p_amt_expt') || ' POA_PERCENT1,
671 nvl(c_amt_receipt,0) POA_MEASURE2,
672 ' || poa_dbi_util_pkg.rate_clause('c_amt_expt','c_amt_receipt') || ' POA_PERCENT2,
674 c_num_days_early/decode(c_early_cnt, 0, null, c_early_cnt) POA_MEASURE4,
675 nvl(c_amt_late,0) POA_MEASURE5,
676 c_num_days_late/decode(c_late_cnt, 0, null, c_late_cnt) POA_MEASURE6,
677 nvl(c_amt_expt_total,0) POA_MEASURE7,
678 ' || poa_dbi_util_pkg.change_clause('c_amt_expt_total','p_amt_expt_total') || ' POA_MEASURE8,
679 nvl(c_amt_receipt_total,0) POA_MEASURE9,
680 ' || poa_dbi_util_pkg.rate_clause('c_amt_expt_total','c_amt_receipt_total') || ' POA_MEASURE10,
681 nvl(c_amt_early_total,0) POA_MEASURE11,
682 c_num_days_early_total/decode(c_early_cnt_total, 0, null, c_early_cnt_total) POA_MEASURE12,
683 nvl(c_amt_late_total,0) POA_MEASURE13,
684 c_num_days_late_total/decode(c_late_cnt_total, 0, null, c_late_cnt_total) POA_MEASURE14,
685 nvl(c_amt_receipt,0) - nvl(c_amt_early,0) - nvl(c_amt_late,0) POA_MEASURE15';
686
687
688 return l_sel_clause;
689 END;
690
691
692 FUNCTION get_txn_rpt_sel_clause(p_view_by_dim in VARCHAR2
693 ,p_url in VARCHAR2
694 ,p_view_by_col in VARCHAR2) return VARCHAR2
695 IS
696 l_sel_clause varchar2(4000);
697 l_view_by_col_name varchar2(40);
698 BEGIN
699 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
700 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
701 l_sel_clause := l_sel_clause ||'
702 v.description POA_ATTRIBUTE1, --Description
703 ';
704 else
705 l_sel_clause := l_sel_clause || ' null POA_ATTRIBUTE1, --Description
706 ';
707 end if;
708
709 l_sel_clause := l_sel_clause ||
710 'oset.POA_MEASURE1 POA_MEASURE1, --Exception Trans
711 oset.POA_PERCENT1 POA_PERCENT1, --Change
712 oset.POA_MEASURE2 POA_MEASURE2, --Receipt Trans
713 oset.POA_PERCENT2 POA_PERCENT2, --Exception Trans Rate
714 oset.POA_MEASURE3 POA_MEASURE3, --Early Trans
715 oset.POA_MEASURE4 POA_MEASURE4, --Avg Days Early
716 oset.POA_MEASURE5 POA_MEASURE5, --Late Trans
717 oset.POA_MEASURE6 POA_MEASURE6, --Avg Days Late
718 oset.POA_MEASURE7 POA_MEASURE7, --Total Exception Trans
719 oset.POA_MEASURE8 POA_MEASURE8, --Total Change
720 oset.POA_MEASURE9 POA_MEASURE9, --Total Receipt Trans
721 oset.POA_MEASURE10 POA_MEASURE10, --Total Exception Trans Rate
722 oset.POA_MEASURE11 POA_MEASURE11, --Total Early Trans
723 oset.POA_MEASURE12 POA_MEASURE12, --Total Avg Days Early
724 oset.POA_MEASURE13 POA_MEASURE13, --Total Late Trans
725 oset.POA_MEASURE14 POA_MEASURE14, --Total Avg Days Late
726 oset.POA_MEASURE15 POA_MEASURE15, --In Tolerance Trans.';
727 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
728 l_sel_clause := l_sel_clause || '
729 ''' || p_url || ''' POA_ATTRIBUTE4';
730 else
731 l_sel_clause := l_sel_clause || '
732 null POA_ATTRIBUTE4';
733 end if;
734 l_sel_clause := l_sel_clause || '
735 from
736 (select (rank() over
737 ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
738
739 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
740 l_sel_clause := l_sel_clause || ', base_uom';
741 end if;
742
743 l_sel_clause := l_sel_clause || ')) - 1 rnk,'|| p_view_by_col ||',' ;
744 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
745 l_sel_clause := l_sel_clause ||' base_uom,';
746 end if;
747 l_sel_clause := l_sel_clause ||' POA_MEASURE1, POA_PERCENT1,
748 POA_MEASURE2, POA_PERCENT2,
749 POA_MEASURE3, POA_MEASURE4,
750 POA_MEASURE5, POA_MEASURE6,
751 POA_MEASURE7, POA_MEASURE8,
752 POA_MEASURE9, POA_MEASURE10,
753 POA_MEASURE11, POA_MEASURE12,
754 POA_MEASURE13, POA_MEASURE14,
755 POA_MEASURE15
756 from
757 (select ' || p_view_by_col || ',
758 ' || p_view_by_col || ' VIEWBY,';
759 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
760 l_sel_clause := l_sel_clause ||' base_uom,';
761 end if;
762 l_sel_clause := l_sel_clause ||
763 'nvl(c_early_cnt,0) + nvl(c_late_cnt,0) POA_MEASURE1,
764 ' || poa_dbi_util_pkg.change_clause('(nvl(c_early_cnt,0)+nvl(c_late_cnt,0))','(nvl(p_early_cnt,0)+nvl(p_late_cnt,0))') || ' POA_PERCENT1,
765 nvl(c_cnt_receipt,0) POA_MEASURE2,
766 ' || poa_dbi_util_pkg.rate_clause('(nvl(c_early_cnt,0) + nvl(c_late_cnt,0))','c_cnt_receipt') || ' POA_PERCENT2,
767 nvl(c_early_cnt,0) POA_MEASURE3,
768 c_num_days_early/decode(c_early_cnt, 0, null, c_early_cnt) POA_MEASURE4,
769 nvl(c_late_cnt,0) POA_MEASURE5,
770 c_num_days_late/decode(c_late_cnt, 0, null, c_late_cnt) POA_MEASURE6,
771 nvl(c_early_cnt_total,0) + nvl(c_late_cnt_total, 0) POA_MEASURE7,
775 nvl(c_early_cnt_total,0) POA_MEASURE11,
772 ' || poa_dbi_util_pkg.change_clause('(nvl(c_early_cnt_total,0) + nvl(c_late_cnt_total,0))','(nvl(p_early_cnt_total,0) + nvl(p_late_cnt_total,0))') || ' POA_MEASURE8,
773 nvl(c_cnt_receipt_total,0) POA_MEASURE9,
774 ' || poa_dbi_util_pkg.rate_clause('(nvl(c_early_cnt_total,0) + nvl(c_late_cnt_total,0))','c_cnt_receipt_total') || ' POA_MEASURE10,
776 c_num_days_early_total/decode(c_early_cnt_total, 0, null, c_early_cnt_total) POA_MEASURE12,
777 nvl(c_late_cnt_total,0) POA_MEASURE13,
778 c_num_days_late_total/decode(c_late_cnt_total, 0, null, c_late_cnt_total) POA_MEASURE14,
779 nvl(c_cnt_receipt,0) - nvl(c_early_cnt,0) - nvl(c_late_cnt,0) POA_MEASURE15';
780
781
782 return l_sel_clause;
783 END;
784
785
786 FUNCTION get_it_rpt_filter_where return VARCHAR2
787 IS
788 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
789 BEGIN
790 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
791 l_col_tbl.extend;
792 l_col_tbl(1) := 'POA_MEASURE2';
793 l_col_tbl.extend;
794 l_col_tbl(2) := 'POA_MEASURE3';
795 l_col_tbl.extend;
796 l_col_tbl(3) := 'POA_MEASURE4';
797 l_col_tbl.extend;
798 l_col_tbl(4) := 'POA_MEASURE5';
799 l_col_tbl.extend;
800 l_col_tbl(5) := 'POA_MEASURE6';
801 l_col_tbl.extend;
802 l_col_tbl(6) := 'POA_MEASURE7';
803 l_col_tbl.extend;
804 l_col_tbl(7) := 'POA_MEASURE8';
805 l_col_tbl.extend;
806 l_col_tbl(8) := 'POA_MEASURE9';
807 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
808
809 END;
810
811 FUNCTION get_amt_rpt_filter_where return VARCHAR2
812 IS
813 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
814 BEGIN
815 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
816 l_col_tbl.extend;
817 l_col_tbl(1) := 'POA_MEASURE1';
818 l_col_tbl.extend;
819 l_col_tbl(2) := 'POA_PERCENT1';
820 l_col_tbl.extend;
821 l_col_tbl(3) := 'POA_MEASURE2';
822 l_col_tbl.extend;
823 l_col_tbl(4) := 'POA_PERCENT2';
824 l_col_tbl.extend;
825 l_col_tbl(5) := 'POA_MEASURE3';
826 l_col_tbl.extend;
827 l_col_tbl(6) := 'POA_MEASURE4';
828 l_col_tbl.extend;
829 l_col_tbl(7) := 'POA_MEASURE5';
830 l_col_tbl.extend;
831 l_col_tbl(8) := 'POA_MEASURE6';
832 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
833
834 END;
835
836 FUNCTION get_txn_rpt_filter_where return VARCHAR2
837 IS
838 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
839 BEGIN
840 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
841 l_col_tbl.extend;
842 l_col_tbl(1) := 'POA_MEASURE1';
843 l_col_tbl.extend;
844 l_col_tbl(2) := 'POA_PERCENT1';
845 l_col_tbl.extend;
846 l_col_tbl(3) := 'POA_MEASURE2';
847 l_col_tbl.extend;
848 l_col_tbl(4) := 'POA_PERCENT2';
849 l_col_tbl.extend;
850 l_col_tbl(5) := 'POA_MEASURE3';
851 l_col_tbl.extend;
852 l_col_tbl(6) := 'POA_MEASURE4';
853 l_col_tbl.extend;
854 l_col_tbl(7) := 'POA_MEASURE5';
855 l_col_tbl.extend;
856 l_col_tbl(8) := 'POA_MEASURE6';
857 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
858
859 END;
860
861 FUNCTION get_status_it_sel_clause(p_view_by_dim IN VARCHAR2,p_view_by_col in VARCHAR2) return VARCHAR2 IS
862 l_sel_clause varchar2(4000);
863 l_view_by_col_name varchar2(40);
864 BEGIN
865 --
866 --
867 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
868 --
869 l_sel_clause := l_sel_clause ||'
870 v.description POA_ATTRIBUTE1, --Description
871 v2.description POA_ATTRIBUTE2, --UOM
872 oset.POA_MEASURE2 POA_MEASURE2, --Early
873 oset.POA_MEASURE3 POA_MEASURE3, --In Tolerance
874 oset.POA_MEASURE4 POA_MEASURE4, --Late
875 oset.POA_MEASURE5 POA_MEASURE5, --Total
876 oset.POA_MEASURE6 POA_MEASURE6, --Exception Amount
877 oset.POA_MEASURE7 POA_MEASURE7, --Receipt Amount
878 oset.POA_MEASURE8 POA_MEASURE8, --Exception Transactions
879 oset.POA_MEASURE9 POA_MEASURE9, --Receipt Transactions
880 oset.POA_MEASURE10 POA_MEASURE10, --Total Exception Amount
881 oset.POA_MEASURE11 POA_MEASURE11, --Total Receipt Amount
882 oset.POA_MEASURE12 POA_MEASURE12, --Total Exception Transactions
883 oset.POA_MEASURE13 POA_MEASURE13 --Total Receipt Transactions
884 from
885 (select (rank() over
886
887 (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ', base_uom)) - 1 rnk,'
888
889 || p_view_by_col || ',
890 base_uom, POA_MEASURE2, POA_MEASURE3, POA_MEASURE4,
891 POA_MEASURE5, POA_MEASURE6, POA_MEASURE7,
892 POA_MEASURE8, POA_MEASURE9, POA_MEASURE10,
893 POA_MEASURE11, POA_MEASURE12,POA_MEASURE13 from
894 (select ' || p_view_by_col || ',
895 ' || p_view_by_col || ' VIEWBY,
896 base_uom,
897 decode(base_uom,null,to_number(null),nvl(c_qty_beforedue,0)) POA_MEASURE2,
898 decode(base_uom,null,to_number(null),nvl(c_qty_intol,0)) POA_MEASURE3,
899 decode(base_uom,null,to_number(null),nvl(c_qty_afterdue,0)) POA_MEASURE4,
900 decode(base_uom,null,to_number(null),(nvl(c_qty_beforedue,0)+nvl(c_qty_intol,0)+nvl(c_qty_afterdue,0))) POA_MEASURE5,
901
902 nvl(c_amt_expt,0) POA_MEASURE6,
903 nvl(c_amt_receipt,0) POA_MEASURE7,
904 nvl(c_early_cnt,0)+nvl(c_late_cnt,0) POA_MEASURE8,
905 nvl(c_cnt_receipt,0) POA_MEASURE9,
906 nvl(c_amt_expt_total,0) POA_MEASURE10,
907 nvl(c_amt_receipt_total,0) POA_MEASURE11,
908 nvl(c_early_cnt_total,0)+nvl(c_late_cnt_total,0) POA_MEASURE12,
909 nvl(c_cnt_receipt_total,0) POA_MEASURE13';
910
911 return l_sel_clause;
912
913 END;
914
915 PROCEDURE trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
916 x_custom_sql OUT NOCOPY VARCHAR2,
917 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
918 IS
919 l_query varchar2(10000);
920 l_view_by varchar2(120);
921 l_view_by_col varchar2(120);
922 l_view_by_value VARCHAR2(300);
923 l_as_of_date date;
924 l_prev_as_of_date date;
925 l_xtd varchar2(10);
926 l_comparison_type varchar2(1) := 'Y';
927 l_nested_pattern number;
928 l_cur_suffix varchar2(2);
929 l_custom_sql varchar2(10000);
930 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
931 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
932 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
933 l_mv VARCHAR2(30);
934 l_where_clause VARCHAR2(2000);
935 l_context_code VARCHAR2(10);
936 l_to_date_type VARCHAR2(10);
937 BEGIN
938 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
939 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
940 poa_dbi_sutil_pkg.process_parameters(p_param
941 ,l_view_by
942 ,l_view_by_col
943 ,l_view_by_value
944 ,l_comparison_type
945 ,l_xtd
946 ,l_as_of_date
947 ,l_prev_as_of_date
948 ,l_cur_suffix
949 ,l_nested_pattern
950 ,l_where_clause
951 ,l_mv
952 ,l_join_tbl
953 ,l_in_join_tbl
954 , x_custom_output
955 ,'Y','PO', '6.0', 'COM','RTX');
956 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
957 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
958 l_to_date_type := 'RLX';
959 ELSE
960 l_to_date_type := 'XTD';
961 END IF;
962
963 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_early_cnt', 'early_cnt', 'N',p_to_date_type => l_to_date_type);
964 poa_dbi_util_pkg.add_column(l_col_tbl, 'num_txns_late_cnt', 'late_cnt', 'N',p_to_date_type => l_to_date_type);
965 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_expt_' || l_cur_suffix,'amt_expt', 'N',p_to_date_type => l_to_date_type);
966
967 l_query := get_trend_sel_clause || ' from
968 '|| poa_dbi_template_pkg.trend_sql(l_xtd,
969 l_comparison_type,
970 l_mv,
971 l_where_clause,
972 l_col_tbl,
973 p_use_grpid => 'N',
974 p_in_join_tables => l_in_join_tbl);
975 x_custom_sql := l_query;
976 END;
977
978 FUNCTION get_trend_sel_clause return VARCHAR2 IS
979 l_sel_clause varchar2(4000);
980 BEGIN
981 l_sel_clause :='select cal.name VIEWBY,
982 nvl(p_amt_expt,0) POA_MEASURE3,
983 nvl(c_amt_expt,0) POA_MEASURE1,
984 ' || poa_dbi_util_pkg.change_clause('c_amt_expt','p_amt_expt') || ' POA_PERCENT1,
985 nvl(p_early_cnt,0)+nvl(p_late_cnt,0) POA_MEASURE4,
986 nvl(c_early_cnt,0)+nvl(c_late_cnt,0) POA_MEASURE2,
987 ' || poa_dbi_util_pkg.change_clause('(nvl(c_early_cnt,0)+nvl(c_late_cnt,0))','(nvl(p_early_cnt,0)+nvl(p_late_cnt,0))') || ' POA_PERCENT3';
988 return l_sel_clause;
989 END;
990 end poa_dbi_rde_pkg;