[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_WMS_RPT_PKG
Source
1 PACKAGE BODY OPI_DBI_WMS_RPT_PKG AS
2 /* $Header: OPIDRWWAAB.pls 120.0 2005/05/24 19:05:17 appldev noship $ */
3 -- ----------------------------------------
4 -- Declare Private Procedures and Functions
5 -- ----------------------------------------
6 FUNCTION GET_PICK_EX_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
7 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
8 RETURN VARCHAR2;
9
10 FUNCTION GET_PICK_REASON_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
11 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
12 RETURN VARCHAR2;
13
14 FUNCTION GET_PICK_EX_TRD_SEL_CLAUSE (p_view_by_dim IN VARCHAR2)
15 RETURN VARCHAR2;
16
17 FUNCTION GET_OPP_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
18 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
19 RETURN VARCHAR2;
20
21 FUNCTION GET_OP_EX_REASON_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
22 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
23 RETURN VARCHAR2;
24
25 -- -------------------------------------------------------------------
26 -- Name : GET_PICK_EX_SQL
27 -- Description: Generate query for Picks and Exception Analysis Report
28 -- -------------------------------------------------------------------
29 PROCEDURE GET_PICK_EX_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
30 x_custom_sql OUT NOCOPY VARCHAR2,
31 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
32
33 IS
34
35 l_query VARCHAR2(15000);
36 l_view_by VARCHAR2(120);
37 l_view_by_col VARCHAR2 (120);
38 l_xtd VARCHAR2(10);
39 l_comparison_type VARCHAR2(1);
40 l_cur_suffix VARCHAR2(5);
41 l_custom_sql VARCHAR2 (10000);
42 l_subinv_val VARCHAR2 (120) := NULL;
43 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
44 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
45 l_where_clause VARCHAR2 (2000);
46 l_mv VARCHAR2 (30);
47 l_aggregation_level_flag VARCHAR2(10);
48 l_custom_rec BIS_QUERY_ATTRIBUTES;
49 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
50 l_filter_where VARCHAR2(120);
51 BEGIN
52 -- initialization block
53 l_comparison_type := 'Y';
54 l_aggregation_level_flag := '0';
55
56 -- clear out the column and Join info tables.
57 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
58 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
59 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
60
61 -- get all the query parameters
62 opi_dbi_rpt_util_pkg.process_parameters (
63 p_param => p_param,
64 p_view_by => l_view_by,
65 p_view_by_col_name => l_view_by_col,
66 p_comparison_type => l_comparison_type,
67 p_xtd => l_xtd,
68 p_cur_suffix => l_cur_suffix,
69 p_where_clause => l_where_clause,
70 p_mv => l_mv,
71 p_join_tbl => l_join_tbl,
72 p_mv_level_flag => l_aggregation_level_flag,
73 p_trend => 'N',
74 p_func_area => 'OPI',
75 p_version => '7.1',
76 p_role => '',
77 p_mv_set => 'PEX',
78 p_mv_flag_type => 'WMS_PEX');
79
80 -- Add measure columns that need to be aggregated
81 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
82 p_col_name => 'picks' ,
83 p_alias_name => 'picks',
84 p_grand_total => 'Y',
85 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
86 p_to_date_type => 'RLX');
87
88 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
89 p_col_name => 'picks_with_exceptions' ,
90 p_alias_name => 'picks_with_exceptions',
91 p_grand_total => 'Y',
92 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
93 p_to_date_type => 'RLX');
94
95 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
96 p_col_name => 'pick_exceptions',
97 p_alias_name => 'pick_exceptions',
98 p_grand_total => 'Y',
99 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
100 p_to_date_type => 'RLX');
101
102 --Add filtering condition to suppress rows
103 l_filter_where := 'OPI_MEASURE11 > 0 or OPI_MEASURE1 > 0';
104
105 --Generate Final Query
106 l_query := GET_PICK_EX_SEL_CLAUSE (l_view_by, l_join_tbl) || fnd_global.newline
107 || 'from
108 ' || poa_dbi_template_pkg.status_sql (
109 p_fact_name => l_mv,
110 p_where_clause => l_where_clause,
111 p_join_tables => l_join_tbl,
112 p_use_windowing => 'Y',
113 p_col_name => l_col_tbl,
114 p_use_grpid => 'N',
115 p_paren_count => 3,
116 p_filter_where => l_filter_where,
117 p_generate_viewby => 'Y',
118 p_in_join_tables => NULL);
119
120 -- prepare output for bind variables
121 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
122 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
123
124 -- set the basic bind variables for the status SQL
125 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
126 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
127
128 -- Passing aggregation level flag to PMV
129 l_custom_rec.attribute_name := ':OPI_PEX_AGG_LEVEL_FLAG';
130 l_custom_rec.attribute_value := l_aggregation_level_flag;
131 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
132 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
133 x_custom_output.extend;
134
135 x_custom_output(x_custom_output.count) := l_custom_rec;
136
137 commit;
138
139 x_custom_sql := l_query;
140
141 END GET_PICK_EX_SQL;
142
143 -- -------------------------------------------------------------------
144 -- Name : GET_PICK_EX_SEL_CLAUSE
145 -- Description: build select clause for Picks and Exception Analysis
146 -- -------------------------------------------------------------------
147 FUNCTION GET_PICK_EX_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
148 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
149 RETURN VARCHAR2
150 IS
151 l_sel_clause VARCHAR2(15000);
152 l_view_by_col_name VARCHAR2(120);
153 l_description VARCHAR2(30);
154 l_uom VARCHAR2(30);
155 l_view_by_fact_col VARCHAR2(400);
156
157 BEGIN
158 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
159 (p_view_by_dim);
160 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
161 (p_join_tbl);
162
163 -- Item Description and UOM for item view by
164 opi_dbi_rpt_util_pkg.get_viewby_item_columns(
165 p_dim_name => p_view_by_dim,
166 p_description => l_description,
167 p_uom => l_uom);
168
169 -- Start generating SELECT clause for query
170 l_sel_clause :=
171 'SELECT
172 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
173 || fnd_global.newline ||' ' || l_description ||
174 ' OPI_ATTRIBUTE10 ';
175
176 l_sel_clause := l_sel_clause ||'
177 ,OPI_MEASURE11
178 ,OPI_MEASURE1
179 ,OPI_MEASURE2
180 ,OPI_MEASURE3
181 ,OPI_MEASURE12
182 ,OPI_MEASURE4
183 ,OPI_MEASURE5
184 ,OPI_MEASURE13
185 ,OPI_MEASURE6
186 ,OPI_MEASURE7
187 ,OPI_MEASURE14
188 ,OPI_MEASURE8
189 ,OPI_MEASURE9
190 ,OPI_MEASURE21
191 ,OPI_MEASURE22
192 ,OPI_MEASURE23
193 ,OPI_MEASURE24
194 ,OPI_MEASURE25
195 ,OPI_MEASURE26
196 ,OPI_MEASURE27
197 ,OPI_MEASURE28
198 ,OPI_MEASURE29
199 ,OPI_MEASURE30
200 ,OPI_MEASURE31
201 ,OPI_MEASURE32
202 ,OPI_MEASURE33 '|| fnd_global.newline;
203
204 l_sel_clause := l_sel_clause || 'FROM ( SELECT
205 rank() over (&ORDER_BY_CLAUSE nulls last '||', '||l_view_by_fact_col||') - 1 rnk
206 ,'||l_view_by_fact_col;
207
208 l_sel_clause := l_sel_clause ||'
209 ,OPI_MEASURE11
210 ,OPI_MEASURE1
211 ,OPI_MEASURE2
212 ,OPI_MEASURE3
213 ,OPI_MEASURE12
214 ,OPI_MEASURE4
215 ,OPI_MEASURE5
216 ,OPI_MEASURE13
217 ,OPI_MEASURE6
218 ,OPI_MEASURE7
219 ,OPI_MEASURE14
220 ,OPI_MEASURE8
221 ,OPI_MEASURE9
222 ,OPI_MEASURE21
223 ,OPI_MEASURE22
224 ,OPI_MEASURE23
225 ,OPI_MEASURE24
226 ,OPI_MEASURE25
227 ,OPI_MEASURE26
228 ,OPI_MEASURE27
229 ,OPI_MEASURE28
230 ,OPI_MEASURE29
231 ,OPI_MEASURE30
232 ,OPI_MEASURE31
233 ,OPI_MEASURE32
234 ,OPI_MEASURE33 '|| fnd_global.newline;
235
236 l_sel_clause := l_sel_clause ||
237 'FROM ( SELECT ' || fnd_global.newline ||'
238 ' || l_view_by_fact_col || ',
239 ' || opi_dbi_rpt_util_pkg.nvl_str (
240 p_str => 'p_picks',
241 p_default_val => 0) || ' OPI_MEASURE11,
242 ' || opi_dbi_rpt_util_pkg.nvl_str (
243 p_str => 'c_picks',
244 p_default_val => 0) || ' OPI_MEASURE1,
245 ' || opi_dbi_rpt_util_pkg.change_str (
246 p_new_numerator => 'c_picks',
247 p_old_numerator => 'p_picks',
248 p_denominator => 'p_picks',
249 p_measure_name => 'OPI_MEASURE2') || ',
250 ' || opi_dbi_rpt_util_pkg.percent_str(
251 p_numerator => 'c_picks',
252 p_denominator => 'c_picks_total',
253 p_measure_name => 'OPI_MEASURE3') || ',
254 ' || opi_dbi_rpt_util_pkg.nvl_str (
255 p_str => 'p_picks_with_exceptions',
256 p_default_val => 0) || ' OPI_MEASURE12,
257 ' || opi_dbi_rpt_util_pkg.nvl_str (
258 p_str => 'c_picks_with_exceptions',
259 p_default_val => 0) || ' OPI_MEASURE4,
260 ' || opi_dbi_rpt_util_pkg.change_str (
261 p_new_numerator => 'c_picks_with_exceptions',
262 p_old_numerator => 'p_picks_with_exceptions',
263 p_denominator => 'p_picks_with_exceptions',
264 p_measure_name => 'OPI_MEASURE5') || ',
265 ' || opi_dbi_rpt_util_pkg.percent_str(
266 p_numerator => 'p_picks_with_exceptions',
267 p_denominator => 'p_picks',
268 p_measure_name => 'OPI_MEASURE13') || ',
269 ' || opi_dbi_rpt_util_pkg.percent_str(
270 p_numerator => 'c_picks_with_exceptions',
271 p_denominator => 'c_picks',
272 p_measure_name => 'OPI_MEASURE6') || ',
273 ' || opi_dbi_rpt_util_pkg.change_pct_str (
274 p_new_numerator => 'c_picks_with_exceptions',
275 p_new_denominator => 'c_picks',
276 p_old_numerator => 'p_picks_with_exceptions',
277 p_old_denominator => 'p_picks',
278 p_measure_name => 'OPI_MEASURE7') || ',
279 ' || opi_dbi_rpt_util_pkg.nvl_str (
280 p_str => 'p_pick_exceptions',
281 p_default_val => 0) || ' OPI_MEASURE14,
282 ' || opi_dbi_rpt_util_pkg.nvl_str (
283 p_str => 'c_pick_exceptions',
284 p_default_val => 0) || ' OPI_MEASURE8,
285 ' || opi_dbi_rpt_util_pkg.change_str (
286 p_new_numerator => 'c_pick_exceptions',
287 p_old_numerator => 'p_pick_exceptions',
288 p_denominator => 'p_pick_exceptions',
289 p_measure_name => 'OPI_MEASURE9') || ',
290 ' || opi_dbi_rpt_util_pkg.nvl_str (
291 p_str => 'c_picks_total',
292 p_default_val => 0) || ' OPI_MEASURE21,
293 ' || opi_dbi_rpt_util_pkg.change_str (
294 p_new_numerator => 'c_picks_total',
295 p_old_numerator => 'p_picks_total',
296 p_denominator => 'p_picks_total',
297 p_measure_name => 'OPI_MEASURE22') || ',
298 ' || opi_dbi_rpt_util_pkg.percent_str(
299 p_numerator => 'c_picks_total',
300 p_denominator => 'c_picks_total',
301 p_measure_name => 'OPI_MEASURE23') || ',
302 ' || opi_dbi_rpt_util_pkg.nvl_str (
303 p_str => 'c_picks_with_exceptions_total',
304 p_default_val => 0) || ' OPI_MEASURE24,
305 ' || opi_dbi_rpt_util_pkg.change_str (
306 p_new_numerator => 'c_picks_with_exceptions_total',
307 p_old_numerator => 'p_picks_with_exceptions_total',
308 p_denominator => 'p_picks_with_exceptions_total',
309 p_measure_name => 'OPI_MEASURE25') || ',
310 ' || opi_dbi_rpt_util_pkg.percent_str(
311 p_numerator => 'c_picks_with_exceptions_total',
312 p_denominator => 'c_picks_total',
313 p_measure_name => 'OPI_MEASURE26') || ',
314 ' || opi_dbi_rpt_util_pkg.change_pct_str (
315 p_new_numerator => 'c_picks_with_exceptions_total',
316 p_new_denominator => 'c_picks_total',
317 p_old_numerator => 'p_picks_with_exceptions_total',
318 p_old_denominator => 'p_picks_total',
319 p_measure_name => 'OPI_MEASURE27') || ',
320 ' || opi_dbi_rpt_util_pkg.nvl_str (
321 p_str => 'c_pick_exceptions_total',
322 p_default_val => 0) || ' OPI_MEASURE28,
323 ' || opi_dbi_rpt_util_pkg.change_str (
324 p_new_numerator => 'c_pick_exceptions_total',
325 p_old_numerator => 'p_pick_exceptions_total',
326 p_denominator => 'p_pick_exceptions_total',
327 p_measure_name => 'OPI_MEASURE29') || ',
328 ' || opi_dbi_rpt_util_pkg.percent_str(
329 p_numerator => 'c_picks_with_exceptions',
330 p_denominator => 'c_picks',
331 p_measure_name => 'OPI_MEASURE30') || ',
332 ' || opi_dbi_rpt_util_pkg.percent_str(
333 p_numerator => 'p_picks_with_exceptions',
334 p_denominator => 'p_picks',
335 p_measure_name => 'OPI_MEASURE31') || ',
336 ' || opi_dbi_rpt_util_pkg.percent_str(
337 p_numerator => 'c_picks_with_exceptions_total',
338 p_denominator => 'c_picks_total',
339 p_measure_name => 'OPI_MEASURE32') || ',
340 ' || opi_dbi_rpt_util_pkg.percent_str(
341 p_numerator => 'p_picks_with_exceptions_total',
342 p_denominator => 'p_picks_total',
343 p_measure_name => 'OPI_MEASURE33');
344 RETURN l_sel_clause;
345 END GET_PICK_EX_SEL_CLAUSE;
346
347 -- -------------------------------------------------------------------
348 -- Name : GET_EX_REASON_SQL
349 -- Description: Generate query for Picks Exception By Reason Report
350 -- -------------------------------------------------------------------
351 PROCEDURE GET_EX_REASON_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
352 x_custom_sql OUT NOCOPY VARCHAR2,
353 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
354
355 IS
356 l_query VARCHAR2(15000);
357 l_view_by VARCHAR2(120);
358 l_view_by_col VARCHAR2 (120);
359 l_xtd VARCHAR2(10);
360 l_comparison_type VARCHAR2(1);
361 l_cur_suffix VARCHAR2(5);
362 l_custom_sql VARCHAR2 (10000);
363 l_subinv_val VARCHAR2 (120) := NULL;
364 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
365 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
366 l_where_clause VARCHAR2 (2000);
367 l_mv VARCHAR2 (30);
368 l_aggregation_level_flag VARCHAR2(10);
369 l_custom_rec BIS_QUERY_ATTRIBUTES;
370 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
371
372 BEGIN
373 -- initialization block
374 l_comparison_type := 'Y';
375 l_aggregation_level_flag := '0';
376
377 -- clear out the column and Join info tables.
378 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
379 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
380 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
381
382 -- get all the query parameters
383 opi_dbi_rpt_util_pkg.process_parameters (
384 p_param => p_param,
385 p_view_by => l_view_by,
386 p_view_by_col_name => l_view_by_col,
387 p_comparison_type => l_comparison_type,
388 p_xtd => l_xtd,
389 p_cur_suffix => l_cur_suffix,
390 p_where_clause => l_where_clause,
391 p_mv => l_mv,
392 p_join_tbl => l_join_tbl,
393 p_mv_level_flag => l_aggregation_level_flag,
394 p_trend => 'N',
395 p_func_area => 'OPI',
396 p_version => '7.1',
397 p_role => '',
398 p_mv_set => 'PER',
399 p_mv_flag_type => 'WMS_PER');
400
401 -- Add measure columns that need to be aggregated
402 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
403 p_col_name => 'exceptions',
404 p_alias_name => 'exceptions',
405 p_grand_total => 'Y',
406 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
407 p_to_date_type => 'RLX');
408
409 l_query := GET_PICK_REASON_SEL_CLAUSE (l_view_by, l_join_tbl) || fnd_global.newline
410 || 'from
411 ' || poa_dbi_template_pkg.status_sql (
412 p_fact_name => l_mv,
413 p_where_clause => l_where_clause,
414 p_join_tables => l_join_tbl,
415 p_use_windowing => 'Y',
416 p_col_name => l_col_tbl,
417 p_use_grpid => 'N',
418 p_paren_count => 3,
419 p_filter_where => NULL,
420 p_generate_viewby => 'Y',
421 p_in_join_tables => NULL);
422
423 -- prepare output for bind variables
424 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
425 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
426
427 -- set the basic bind variables for the status SQL
428 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
429 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
430
431 -- Passing aggregation level flag to PMV
432 l_custom_rec.attribute_name := ':OPI_PER_AGG_LEVEL_FLAG';
433 l_custom_rec.attribute_value := l_aggregation_level_flag;
434 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
435 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
436 x_custom_output.extend;
437
438 x_custom_output(x_custom_output.count) := l_custom_rec;
439
440 commit;
441
442 x_custom_sql := l_query;
443
444 END GET_EX_REASON_SQL;
445
446 -- -------------------------------------------------------------------
447 -- Name : GET_PICK_REASON_SEL_CLAUSE
448 -- Description: build select clause for Picks Exception By Reason
449 -- -------------------------------------------------------------------
450 FUNCTION GET_PICK_REASON_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
451 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
452 RETURN VARCHAR2
453 IS
454 l_sel_clause VARCHAR2(15000);
455 l_view_by_col_name VARCHAR2(120);
456 l_view_by_fact_col VARCHAR2(400);
457 BEGIN
458 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
459 (p_view_by_dim);
460 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
461 (p_join_tbl);
462
463 -- Start generating SELECT clause for query
464 l_sel_clause :=
465 'SELECT
466 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
467 || fnd_global.newline;
468
469 l_sel_clause := l_sel_clause ||
470 'OPI_MEASURE1
471 ,OPI_MEASURE2
472 ,OPI_MEASURE3
473 ,OPI_MEASURE4
474 ,OPI_MEASURE5
475 ,OPI_MEASURE6' || fnd_global.newline ||
476 'FROM
477 (SELECT (rank () over
478 (&ORDER_BY_CLAUSE nulls last,
479 ' || l_view_by_fact_col || ')) - 1 rnk,
480 ' || l_view_by_fact_col || ',
481 OPI_MEASURE1,
482 OPI_MEASURE2,
483 OPI_MEASURE3,
484 OPI_MEASURE4,
485 OPI_MEASURE5,
486 OPI_MEASURE6'|| fnd_global.newline;
487
488 l_sel_clause := l_sel_clause ||
489 'FROM ( SELECT ' || fnd_global.newline ||'
490 ' || l_view_by_fact_col || ',
491 ' || opi_dbi_rpt_util_pkg.nvl_str (
492 p_str => 'c_exceptions',
493 p_default_val => 0) || ' OPI_MEASURE1,
494 ' || opi_dbi_rpt_util_pkg.change_str (
495 p_new_numerator => 'c_exceptions',
496 p_old_numerator => 'p_exceptions',
497 p_denominator => 'p_exceptions',
498 p_measure_name => 'OPI_MEASURE2') || ',
499 ' || opi_dbi_rpt_util_pkg.percent_str(
500 p_numerator => 'c_exceptions',
501 p_denominator => 'c_exceptions_total',
502 p_measure_name => 'OPI_MEASURE3') || ',
503 ' || opi_dbi_rpt_util_pkg.nvl_str (
504 p_str => 'c_exceptions_total',
505 p_default_val => 0) || ' OPI_MEASURE4,
506 ' || opi_dbi_rpt_util_pkg.change_str (
507 p_new_numerator => 'c_exceptions_total',
508 p_old_numerator => 'p_exceptions_total',
509 p_denominator => 'p_exceptions_total',
510 p_measure_name => 'OPI_MEASURE5') || ',
511 ' || opi_dbi_rpt_util_pkg.percent_str(
512 p_numerator => 'c_exceptions_total',
513 p_denominator => 'c_exceptions_total',
514 p_measure_name => 'OPI_MEASURE6');
515 RETURN l_sel_clause;
516 END GET_PICK_REASON_SEL_CLAUSE;
517
518 -- -------------------------------------------------------------
519 -- Name : GET_PICK_TRD_SQL
520 -- Description: Generate query for Picks and Exception Trend
521 -- -------------------------------------------------------------
522 PROCEDURE get_pick_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
523 x_custom_sql OUT NOCOPY VARCHAR2,
524 x_custom_output OUT NOCOPY
525 BIS_QUERY_ATTRIBUTES_TBL)
526 IS
527 l_query VARCHAR2(32767);
528 l_view_by VARCHAR2(120);
529 l_view_by_col VARCHAR2 (120);
530 l_xtd VARCHAR2(10);
531 l_comparison_type VARCHAR2(1);
532 l_cur_suffix VARCHAR2(5);
533 l_custom_sql VARCHAR2 (10000);
534 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
535 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
536 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
537 l_where_clause VARCHAR2 (2000);
538 l_mv VARCHAR2 (30);
539 l_aggregation_level_flag VARCHAR2(10);
540 l_custom_rec BIS_QUERY_ATTRIBUTES;
541 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
542 BEGIN
543 -- initialization block
544 l_comparison_type := 'Y';
545 l_aggregation_level_flag := '0';
546
547 -- clear out the tables.
548 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
549 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
550 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
551
552 -- Get Report Parameters for query
553 opi_dbi_rpt_util_pkg.process_parameters (
554 p_param => p_param,
555 p_view_by => l_view_by,
556 p_view_by_col_name => l_view_by_col,
557 p_comparison_type => l_comparison_type,
558 p_xtd => l_xtd,
559 p_cur_suffix => l_cur_suffix,
560 p_where_clause => l_where_clause,
561 p_mv => l_mv,
562 p_join_tbl => l_join_tbl,
563 p_mv_level_flag =>l_aggregation_level_flag,
564 p_trend => 'Y',
565 p_func_area => 'OPI',
566 p_version => '7.1',
567 p_role => '',
568 p_mv_set => 'PEX',
569 p_mv_flag_type => 'WMS_PEX');
570
571 -- Add measure columns to be aggregated
572 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
573 p_col_name => 'picks' ,
574 p_alias_name => 'picks',
575 p_grand_total => 'N',
576 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
577 p_to_date_type => 'RLX');
578
579 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
580 p_col_name => 'picks_with_exceptions',
581 p_alias_name => 'picks_with_exceptions',
582 p_grand_total => 'N',
583 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
584 p_to_date_type => 'RLX');
585
586 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
587 p_col_name => 'pick_exceptions',
588 p_alias_name => 'pick_exceptions',
589 p_grand_total => 'N',
590 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
591 p_to_date_type => 'RLX');
592
593 --Generate Final Query
594 l_query := GET_PICK_EX_TRD_SEL_CLAUSE(l_view_by) ||
595 ' from ' ||
596 poa_dbi_template_pkg.trend_sql(
597 p_xtd => l_xtd,
598 p_comparison_type => l_comparison_type,
599 p_fact_name => l_mv,
600 p_where_clause => l_where_clause,
601 p_col_name => l_col_tbl,
602 p_use_grpid => 'N',
603 p_in_join_tables => NULL,
604 p_fact_hint => poa_dbi_sutil_pkg.get_fact_hint(l_mv)
605 );
606
607 -- prepare output for bind variables
608 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
609 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
610
611 -- set the basic bind variables for the trend SQL
612 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
613 p_comparison_type => l_comparison_type,
614 x_custom_output => x_custom_output);
615 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
616
617 -- Passing AGGREGATION_LEVEL_FLAG to PMV
618 l_custom_rec.attribute_name := ':OPI_PEX_AGG_LEVEL_FLAG';
619 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
620 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
621 l_custom_rec.attribute_value := l_aggregation_level_flag;
622 x_custom_output.extend;
623 x_custom_output(x_custom_output.count) := l_custom_rec;
624
625 commit;
626 x_custom_sql := l_query;
627
628 END get_pick_trd_sql;
629
630 -- -------------------------------------------------------------------
631 -- Name : GET_PICK_EX_TRD_SEL_CLAUSE
632 -- Description: build select clause for Picks and Exception Trend
633 -- -------------------------------------------------------------------
634 FUNCTION GET_PICK_EX_TRD_SEL_CLAUSE (p_view_by_dim IN VARCHAR2)
635 RETURN VARCHAR2
636 IS
637 l_sel_clause varchar2(7500);
638 BEGIN
639 -- Main Outer query
640 l_sel_clause := 'SELECT
641 ' || ' cal.name VIEWBY,
642 ' || opi_dbi_rpt_util_pkg.nvl_str (
643 p_str => 'p_picks',
644 p_default_val => 0) || ' OPI_MEASURE11,
645 ' || opi_dbi_rpt_util_pkg.nvl_str (
646 p_str => 'c_picks',
647 p_default_val => 0) || ' OPI_MEASURE1,
648 ' || opi_dbi_rpt_util_pkg.change_str (
649 p_new_numerator => 'c_picks',
650 p_old_numerator => 'p_picks',
651 p_denominator => 'p_picks',
652 p_measure_name => 'OPI_MEASURE2') || ',
653 ' || opi_dbi_rpt_util_pkg.nvl_str (
654 p_str => 'p_picks_with_exceptions',
655 p_default_val => 0) || ' OPI_MEASURE12,
656 ' || opi_dbi_rpt_util_pkg.nvl_str (
657 p_str => 'c_picks_with_exceptions',
658 p_default_val => 0) || ' OPI_MEASURE3,
659 ' || opi_dbi_rpt_util_pkg.change_str (
660 p_new_numerator => 'c_picks_with_exceptions',
661 p_old_numerator => 'p_picks_with_exceptions',
662 p_denominator => 'p_picks_with_exceptions',
663 p_measure_name => 'OPI_MEASURE4') || ',
664 ' || opi_dbi_rpt_util_pkg.percent_str(
665 p_numerator => 'p_picks_with_exceptions',
666 p_denominator => 'p_picks',
667 p_measure_name => 'OPI_MEASURE13') || ',
668 ' || opi_dbi_rpt_util_pkg.percent_str(
669 p_numerator => 'c_picks_with_exceptions',
670 p_denominator => 'c_picks',
671 p_measure_name => 'OPI_MEASURE5') || ',
672 ' || opi_dbi_rpt_util_pkg.change_pct_str (
673 p_new_numerator => 'c_picks_with_exceptions',
674 p_new_denominator => 'c_picks',
675 p_old_numerator => 'p_picks_with_exceptions',
676 p_old_denominator => 'p_picks',
677 p_measure_name => 'OPI_MEASURE6') || ',
678 ' || opi_dbi_rpt_util_pkg.nvl_str (
679 p_str => 'p_pick_exceptions',
680 p_default_val => 0) || ' OPI_MEASURE14,
681 ' || opi_dbi_rpt_util_pkg.nvl_str (
682 p_str => 'c_pick_exceptions',
683 p_default_val => 0) || ' OPI_MEASURE7,
684 ' || opi_dbi_rpt_util_pkg.change_str (
685 p_new_numerator => 'c_pick_exceptions',
686 p_old_numerator => 'p_pick_exceptions',
687 p_denominator => 'p_pick_exceptions',
688 p_measure_name => 'OPI_MEASURE8');
689 RETURN l_sel_clause;
690
691 END GET_PICK_EX_TRD_SEL_CLAUSE;
692
693 -- -------------------------------------------------------------------
694 -- Name : GET_OPP_SQL
695 -- Description: Generate query for Picks and Exception Analysis Report
696 -- -------------------------------------------------------------------
697 PROCEDURE GET_OPP_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
698 x_custom_sql OUT NOCOPY VARCHAR2,
699 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
700
701 IS
702 l_query VARCHAR2(15000);
703 l_view_by VARCHAR2(120);
704 l_view_by_col VARCHAR2 (120);
705 l_xtd VARCHAR2(10);
706 l_comparison_type VARCHAR2(1);
707 l_cur_suffix VARCHAR2(5);
708 l_custom_sql VARCHAR2 (10000);
709 l_subinv_val VARCHAR2 (120) := NULL;
710 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
711 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
712 l_where_clause VARCHAR2 (2000);
713 l_mv VARCHAR2 (30);
714 l_aggregation_level_flag VARCHAR2(10);
715 l_custom_rec BIS_QUERY_ATTRIBUTES;
716 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
717 l_filter_where VARCHAR2(120);
718 BEGIN
719 -- initialization block
720 l_comparison_type := 'Y';
721 l_aggregation_level_flag := '0';
722
723 -- clear out the column and Join info tables.
724 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
725 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
726 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
727
728 -- get all the query parameters
729 opi_dbi_rpt_util_pkg.process_parameters (
730 p_param => p_param,
731 p_view_by => l_view_by,
732 p_view_by_col_name => l_view_by_col,
733 p_comparison_type => l_comparison_type,
734 p_xtd => l_xtd,
735 p_cur_suffix => l_cur_suffix,
736 p_where_clause => l_where_clause,
737 p_mv => l_mv,
738 p_join_tbl => l_join_tbl,
739 p_mv_level_flag => l_aggregation_level_flag,
740 p_trend => 'N',
741 p_func_area => 'OPI',
742 p_version => '7.1',
743 p_role => '',
744 p_mv_set => 'OPP',
745 p_mv_flag_type => 'WMS_OPP');
746
747 -- Add measure columns that need to be aggregated
748 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
749 p_col_name => 'elapsed_time' ,
750 p_alias_name => 'elapsed_time',
751 p_grand_total => 'Y',
752 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
753 p_to_date_type => 'RLX');
754
755 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
756 p_col_name => 'executions' ,
757 p_alias_name => 'executions',
758 p_grand_total => 'Y',
759 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
760 p_to_date_type => 'RLX');
761
762 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
763 p_col_name => 'exec_with_exceptions',
764 p_alias_name => 'exec_with_exceptions',
765 p_grand_total => 'Y',
766 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
767 p_to_date_type => 'RLX');
768
769 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
770 p_col_name => 'exceptions',
771 p_alias_name => 'exceptions',
772 p_grand_total => 'Y',
773 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
774 p_to_date_type => 'RLX');
775
776 --Add filtering condition to suppress rows
777 l_filter_where := 'OPI_MEASURE4 > 0' ||
778 ' OR OPI_MEASURE13 > 0' ||
779 ' OR OPI_MEASURE15 > 0' ;
780
781 l_query := GET_OPP_SEL_CLAUSE (l_view_by, l_join_tbl)
782 || ' from
783 ' || poa_dbi_template_pkg.status_sql (
784 p_fact_name => l_mv,
785 p_where_clause => l_where_clause,
786 p_join_tables => l_join_tbl,
787 p_use_windowing => 'Y',
788 p_col_name => l_col_tbl,
789 p_use_grpid => 'N',
790 p_paren_count => 3,
791 p_filter_where => l_filter_where,
792 p_generate_viewby => 'Y',
793 p_in_join_tables => NULL);
794
795 -- prepare output for bind variables
796 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
797 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
798
799 -- set the basic bind variables for the status SQL
800 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
801 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
802
803 -- Passing aggregation level flag to PMV
804 l_custom_rec.attribute_name := ':OPI_OPP_AGG_LEVEL_FLAG';
805 l_custom_rec.attribute_value := l_aggregation_level_flag;
806 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
807 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
808 x_custom_output.extend;
809 x_custom_output(x_custom_output.count) := l_custom_rec;
810
811 commit;
812
813 x_custom_sql := l_query;
814
815 END GET_OPP_SQL;
816
817 -- -------------------------------------------------------------------
818 -- Name : GET_OPP_SEL_CLAUSE
819 -- Description: build select clause for Operation Plan Performance
820 -- -------------------------------------------------------------------
821 FUNCTION GET_OPP_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
822 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
823 RETURN VARCHAR2
824 IS
825 l_sel_clause VARCHAR2(15000);
826 l_view_by_col_name VARCHAR2(120);
827 l_view_by_fact_col VARCHAR2(400);
828 BEGIN
829 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
830 (p_view_by_dim);
831 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
832 (p_join_tbl);
833
834 -- Start generating SELECT clause for query
835 l_sel_clause :=
836 'SELECT
837 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline;
838
839 l_sel_clause := l_sel_clause ||'
840 OPI_MEASURE13
841 ,OPI_MEASURE2
842 ,OPI_MEASURE3
843 ,OPI_MEASURE4
844 ,OPI_MEASURE5
845 ,OPI_MEASURE15
846 ,OPI_MEASURE6
847 ,OPI_MEASURE7
848 ,OPI_MEASURE16
849 ,OPI_MEASURE8
850 ,OPI_MEASURE9
851 ,OPI_MEASURE21
852 ,OPI_MEASURE22
853 ,OPI_MEASURE23
854 ,OPI_MEASURE24
855 ,OPI_MEASURE25
856 ,OPI_MEASURE26
857 ,OPI_MEASURE27
858 ,OPI_MEASURE28'|| fnd_global.newline;
859
860 l_sel_clause := l_sel_clause ||
861 'FROM ( SELECT
862 rank() over (&ORDER_BY_CLAUSE nulls last '||', '||l_view_by_fact_col||') - 1 rnk
863 ,'||l_view_by_fact_col;
864
865 l_sel_clause := l_sel_clause ||'
866 ,OPI_MEASURE13
867 ,OPI_MEASURE2
868 ,OPI_MEASURE3
869 ,OPI_MEASURE4
870 ,OPI_MEASURE5
871 ,OPI_MEASURE15
872 ,OPI_MEASURE6
873 ,OPI_MEASURE7
874 ,OPI_MEASURE16
875 ,OPI_MEASURE8
876 ,OPI_MEASURE9
877 ,OPI_MEASURE21
878 ,OPI_MEASURE22
879 ,OPI_MEASURE23
880 ,OPI_MEASURE24
881 ,OPI_MEASURE25
882 ,OPI_MEASURE26
883 ,OPI_MEASURE27
884 ,OPI_MEASURE28'|| fnd_global.newline;
885
886 l_sel_clause := l_sel_clause ||
887 'FROM ( SELECT ' || fnd_global.newline ||
888 l_view_by_fact_col || fnd_global.newline ||
889 ',' || opi_dbi_rpt_util_pkg.rate_str (
890 p_numerator => 'p_elapsed_time',
891 p_denominator => 'p_executions',
892 p_rate_type => 'NP') || 'OPI_MEASURE13,
893 ' || opi_dbi_rpt_util_pkg.rate_str (
894 p_numerator => 'c_elapsed_time',
895 p_denominator => 'c_executions',
896 p_rate_type => 'NP') || 'OPI_MEASURE2,
897 ' || opi_dbi_rpt_util_pkg.change_pct_str_basic(
898 p_new_numerator => 'c_elapsed_time',
899 p_new_denominator => 'c_executions',
900 p_old_numerator => 'p_elapsed_time',
901 p_old_denominator => 'c_executions',
902 p_measure_name => 'OPI_MEASURE3') || ',
903 ' || opi_dbi_rpt_util_pkg.nvl_str (
904 p_str => 'c_executions',
905 p_default_val => 0) || ' OPI_MEASURE4,
906 ' || opi_dbi_rpt_util_pkg.nvl_str (
907 p_str => 'c_exec_with_exceptions',
908 p_default_val => 0) || ' OPI_MEASURE5,
909 ' || opi_dbi_rpt_util_pkg.rate_str (
910 p_numerator => 'p_exec_with_exceptions',
911 p_denominator => 'p_executions',
912 p_rate_type => 'P') || 'OPI_MEASURE15,
913 ' || opi_dbi_rpt_util_pkg.rate_str (
914 p_numerator => 'c_exec_with_exceptions',
915 p_denominator => 'c_executions',
916 p_rate_type => 'P') || 'OPI_MEASURE6,
917 ' || opi_dbi_rpt_util_pkg.change_pct_str (
918 p_new_numerator => 'c_exec_with_exceptions',
919 p_new_denominator => 'c_executions',
920 p_old_numerator => 'p_exec_with_exceptions',
921 p_old_denominator => 'p_executions',
922 p_measure_name => 'OPI_MEASURE7') || ',
923 ' || opi_dbi_rpt_util_pkg.nvl_str (
924 p_str => 'p_exceptions',
925 p_default_val => 0) || ' OPI_MEASURE16,
926 ' || opi_dbi_rpt_util_pkg.nvl_str (
927 p_str => 'c_exceptions',
928 p_default_val => 0) || ' OPI_MEASURE8,
929 ' || opi_dbi_rpt_util_pkg.change_str (
930 p_new_numerator => 'c_exceptions',
931 p_old_numerator => 'p_exceptions',
932 p_denominator => 'p_exceptions',
933 p_measure_name => 'OPI_MEASURE9') || ',
934 ' || opi_dbi_rpt_util_pkg.rate_str (
935 p_numerator => 'c_elapsed_time_total',
936 p_denominator => 'c_executions_total',
937 p_rate_type => 'NP') || 'OPI_MEASURE21,
938 ' || opi_dbi_rpt_util_pkg.change_pct_str_basic(
939 p_new_numerator => 'c_elapsed_time_total',
940 p_new_denominator => 'c_executions_total',
941 p_old_numerator => 'p_elapsed_time_total',
942 p_old_denominator => 'c_executions_total',
943 p_measure_name => 'OPI_MEASURE22') || ',
944 ' || opi_dbi_rpt_util_pkg.nvl_str (
945 p_str => 'c_executions_total',
946 p_default_val => 0) || ' OPI_MEASURE23,
947 ' || opi_dbi_rpt_util_pkg.nvl_str (
948 p_str => 'c_exec_with_exceptions_total',
949 p_default_val => 0) || ' OPI_MEASURE24,
950 ' || opi_dbi_rpt_util_pkg.rate_str (
951 p_numerator => 'c_exec_with_exceptions_total',
952 p_denominator => 'c_executions_total',
953 p_rate_type => 'P') || 'OPI_MEASURE25,
954 ' || opi_dbi_rpt_util_pkg.change_pct_str (
955 p_new_numerator => 'c_exec_with_exceptions_total',
956 p_new_denominator => 'c_executions_total',
957 p_old_numerator => 'p_exec_with_exceptions_total',
958 p_old_denominator => 'p_executions_total',
959 p_measure_name => 'OPI_MEASURE26') || ',
960 ' || opi_dbi_rpt_util_pkg.nvl_str (
961 p_str => 'c_exceptions_total',
962 p_default_val => 0) || ' OPI_MEASURE27,
963 ' || opi_dbi_rpt_util_pkg.change_str (
964 p_new_numerator => 'c_exceptions_total',
965 p_old_numerator => 'p_exceptions_total',
966 p_denominator => 'p_exceptions_total',
967 p_measure_name => 'OPI_MEASURE28');
968
969 RETURN l_sel_clause;
970 END GET_OPP_SEL_CLAUSE;
971
972 -- -------------------------------------------------------------------
973 -- Name : GET_OP_EX_REASON_SQL
974 -- Description: Generate query for Op Plan Exception by Reason Report
975 -- -------------------------------------------------------------------
976 PROCEDURE GET_OP_EX_REASON_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
977 x_custom_sql OUT NOCOPY VARCHAR2,
978 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
979
980 IS
981 l_query VARCHAR2(15000);
982 l_view_by VARCHAR2(120);
983 l_view_by_col VARCHAR2 (120);
984 l_xtd VARCHAR2(10);
985 l_comparison_type VARCHAR2(1);
986 l_cur_suffix VARCHAR2(5);
987 l_custom_sql VARCHAR2 (10000);
988 l_subinv_val VARCHAR2 (120) := NULL;
989 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
990 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
991 l_where_clause VARCHAR2 (2000);
992 l_mv VARCHAR2 (30);
993 l_aggregation_level_flag VARCHAR2(10);
994 l_custom_rec BIS_QUERY_ATTRIBUTES;
995 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
996 l_filter_where VARCHAR2(120);
997 BEGIN
998 -- initialization block
999 l_comparison_type := 'Y';
1000 l_aggregation_level_flag := '0';
1001
1002 -- clear out the column and Join info tables.
1003 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1004 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1005 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
1006
1007 -- get all the query parameters
1008 opi_dbi_rpt_util_pkg.process_parameters (
1009 p_param => p_param,
1010 p_view_by => l_view_by,
1011 p_view_by_col_name => l_view_by_col,
1012 p_comparison_type => l_comparison_type,
1013 p_xtd => l_xtd,
1014 p_cur_suffix => l_cur_suffix,
1015 p_where_clause => l_where_clause,
1016 p_mv => l_mv,
1017 p_join_tbl => l_join_tbl,
1018 p_mv_level_flag => l_aggregation_level_flag,
1019 p_trend => 'N',
1020 p_func_area => 'OPI',
1021 p_version => '7.1',
1022 p_role => '',
1023 p_mv_set => 'OPER',
1024 p_mv_flag_type => 'WMS_OPER');
1025
1026 -- Add measure columns that need to be aggregated
1027 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1028 p_col_name => 'exceptions',
1029 p_alias_name => 'exceptions',
1030 p_grand_total => 'Y',
1031 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1032 p_to_date_type => 'RLX');
1033 --Add filtering condition to suppress rows
1034 l_filter_where := NULL;
1035
1036 --Generate Final Query
1037 l_query := GET_OP_EX_REASON_SEL_CLAUSE (l_view_by, l_join_tbl)
1038 || ' from
1039 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
1040 p_where_clause => l_where_clause,
1041 p_join_tables => l_join_tbl,
1042 p_use_windowing => 'Y',
1043 p_col_name => l_col_tbl,
1044 p_use_grpid => 'N',
1045 p_paren_count => 3,
1046 p_filter_where => l_filter_where,
1047 p_generate_viewby => 'Y',
1048 p_in_join_tables => NULL);
1049 -- prepare output for bind variables
1050 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1051 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1052
1053 -- set the basic bind variables for the status SQL
1054 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
1055 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
1056
1057 -- Passing OPI_AGGREGATION_LEVEL_FLAGS to PMV
1058 l_custom_rec.attribute_name := ':OPI_OPER_AGG_LEVEL_FLAG';
1059 l_custom_rec.attribute_value := l_aggregation_level_flag;
1060 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1061 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1062 x_custom_output.extend;
1063 x_custom_output(x_custom_output.count) := l_custom_rec;
1064
1065 commit;
1066
1067 x_custom_sql := l_query;
1068 END GET_OP_EX_REASON_SQL;
1069
1070 -- -------------------------------------------------------------------
1071 -- Name : GET_OP_EX_REASON_SEL_CLAUSE
1072 -- Description: build select clause for Op Exception by Reason
1073 -- -------------------------------------------------------------------
1074 FUNCTION GET_OP_EX_REASON_SEL_CLAUSE(p_view_by_dim IN VARCHAR2, p_join_tbl IN
1075 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
1076 RETURN VARCHAR2
1077 IS
1078 l_sel_clause VARCHAR2(15000);
1079 l_view_by_col_name VARCHAR2(120);
1080 l_view_by_fact_col VARCHAR2(400);
1081 BEGIN
1082 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
1083 (p_view_by_dim);
1084 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
1085 (p_join_tbl);
1086
1087 -- Start generating SELECT clause for query
1088 l_sel_clause :=
1089 'SELECT
1090 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
1091 || fnd_global.newline;
1092
1093 l_sel_clause := l_sel_clause ||
1094 ' OPI_MEASURE1
1095 ,OPI_MEASURE2
1096 ,OPI_MEASURE3
1097 ,OPI_MEASURE4
1098 ,OPI_MEASURE5
1099 ,OPI_MEASURE6
1100 FROM
1101 (SELECT (rank () over
1102 (&ORDER_BY_CLAUSE nulls last,
1103 ' || l_view_by_fact_col || ')) - 1 rnk,
1104 ' || l_view_by_fact_col || ',
1105 OPI_MEASURE1,
1106 OPI_MEASURE2,
1107 OPI_MEASURE3,
1108 OPI_MEASURE4,
1109 OPI_MEASURE5,
1110 OPI_MEASURE6'|| fnd_global.newline;
1111
1112 l_sel_clause := l_sel_clause ||
1113 'FROM ( SELECT ' || fnd_global.newline ||
1114 l_view_by_fact_col || fnd_global.newline ||
1115 ',' || opi_dbi_rpt_util_pkg.nvl_str (
1116 p_str => 'c_exceptions',
1117 p_default_val => 0) || ' OPI_MEASURE1,
1118 ' || opi_dbi_rpt_util_pkg.change_str (
1119 p_new_numerator => 'c_exceptions',
1120 p_old_numerator => 'p_exceptions',
1121 p_denominator => 'p_exceptions',
1122 p_measure_name => 'OPI_MEASURE2') || ',
1123 ' || opi_dbi_rpt_util_pkg.percent_str(
1124 p_numerator => 'c_exceptions',
1125 p_denominator => 'c_exceptions_total',
1126 p_measure_name => 'OPI_MEASURE3') || ',
1127 ' || opi_dbi_rpt_util_pkg.nvl_str (
1128 p_str => 'c_exceptions_total',
1129 p_default_val => 0) || ' OPI_MEASURE4,
1130 ' || opi_dbi_rpt_util_pkg.change_str (
1131 p_new_numerator => 'c_exceptions_total',
1132 p_old_numerator => 'p_exceptions_total',
1133 p_denominator => 'p_exceptions_total',
1134 p_measure_name => 'OPI_MEASURE5') || ',
1135 ' || opi_dbi_rpt_util_pkg.percent_str(
1136 p_numerator => 'c_exceptions_total',
1137 p_denominator => 'c_exceptions_total',
1138 p_measure_name => 'OPI_MEASURE6');
1139 RETURN l_sel_clause;
1140 END GET_OP_EX_REASON_SEL_CLAUSE;
1141
1142 END opi_dbi_wms_rpt_pkg;