[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_CC_RPT_PKG
Source
1 PACKAGE BODY opi_dbi_cc_rpt_pkg AS
2 /*$Header: OPIDRICCAB.pls 120.0 2005/05/24 18:18:25 appldev noship $ */
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6 /* Get Item Description when view by is item */
7 PROCEDURE get_cc_item_columns ( p_dim_name IN VARCHAR2,
8 p_description OUT NOCOPY VARCHAR2,
9 p_col_type IN VARCHAR2 := 'ITEM');
10
11 /* Cycle Count Accuracy Report */
12 FUNCTION get_cc_rpt_sel_clause (p_view_by_dim IN VARCHAR2,
13 p_join_tbl IN
14 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
15 RETURN VARCHAR2;
16
17
18 /* Cycle Count Accuracy Trend Report */
19 FUNCTION get_cc_trd_sel_clause(p_view_by_dim IN VARCHAR2)
20 RETURN VARCHAR2;
21
22 /* Hit/Miss Summary */
23 FUNCTION get_hitmiss_sel_clause (p_view_by_dim IN VARCHAR2,
24 p_join_tbl IN
25 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
26 RETURN VARCHAR2;
27
28 /* Cycle Count Adjustment Summary Report */
29 FUNCTION get_adj_rpt_sel_clause (p_view_by_dim IN VARCHAR2,
30 p_join_tbl IN
31 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
32 RETURN VARCHAR2;
33
34 /* Cycle Count Adjustment Detail Report */
35 FUNCTION get_adj_dtl_sel_clause (p_view_by_dim IN VARCHAR2,
36 p_join_tbl IN
37 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
38 RETURN VARCHAR2;
39
40 /*----------------------------------------
41 Cycle Count Accuracy Report Function
42 ----------------------------------------*/
43 PROCEDURE get_tbl_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
44 x_custom_sql OUT NOCOPY VARCHAR2,
45 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
46 IS
47 l_query VARCHAR2(32767);
48 l_view_by VARCHAR2(120);
49 l_view_by_col VARCHAR2 (120);
50 l_xtd VARCHAR2(10);
51 l_comparison_type VARCHAR2(1);
52 l_cur_suffix VARCHAR2(5);
53 l_custom_sql VARCHAR2 (10000);
54
55 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
56 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
57
58 l_where_clause VARCHAR2 (2000);
59 l_mv VARCHAR2 (30);
60
61 l_aggregation_level_flag VARCHAR2(10);
62
63 l_custom_rec BIS_QUERY_ATTRIBUTES;
64
65 BEGIN
66
67 -- initialization block
68 l_comparison_type := 'Y';
69 l_aggregation_level_flag := '0';
70
71 -- clear out the column and Join info tables.
72 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
73 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
74
75 -- get all the query parameters
76 opi_dbi_rpt_util_pkg.process_parameters (
77 p_param => p_param,
78 p_view_by => l_view_by,
79 p_view_by_col_name => l_view_by_col,
80 p_comparison_type => l_comparison_type,
81 p_xtd => l_xtd,
82 p_cur_suffix => l_cur_suffix,
83 p_where_clause => l_where_clause,
84 p_mv => l_mv,
85 p_join_tbl => l_join_tbl,
86 p_mv_level_flag => l_aggregation_level_flag,
87 p_trend => 'N',
88 p_func_area => 'OPI',
89 p_version => '7.0',
90 p_role => '',
91 p_mv_set => 'CCAC',
92 p_mv_flag_type => 'CCA_LEVEL');
93
94 -- Add measure columns that need to be aggregated
95 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
96 p_col_name => 'number_of_hits' ,
97 p_alias_name => 'hits',
98 p_grand_total => 'Y',
99 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
100 p_to_date_type => 'XTD');
101
102 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
103 p_col_name => 'number_of_exact_matches' ,
104 p_alias_name => 'exact_matches',
105 p_grand_total => 'Y',
106 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
107 p_to_date_type => 'XTD');
108
109 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
110 p_col_name => 'number_of_total_entries',
111 p_alias_name => 'tot_entries',
112 p_grand_total => 'Y',
113 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
114 p_to_date_type => 'XTD');
115
116 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
117 p_col_name => 'system_inventory_val_' || l_cur_suffix,
118 p_alias_name => 'system_val',
119 p_grand_total => 'Y',
120 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
121 p_to_date_type => 'XTD');
122
123 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
124 p_col_name => 'gross_adjustment_val_' || l_cur_suffix,
125 p_alias_name => 'gross_adj_val',
126 p_grand_total => 'Y',
127 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
128 p_to_date_type => 'XTD');
129
130 -- construct the query
131 l_query := get_cc_rpt_sel_clause (l_view_by, l_join_tbl)
132 || ' from
133 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
134 p_where_clause => l_where_clause,
135 p_join_tables => l_join_tbl,
136 p_use_windowing => 'Y',
137 p_col_name => l_col_tbl,
138 p_use_grpid => 'N',
139 p_paren_count => 3,
140 p_filter_where => NULL,
141 p_generate_viewby => 'Y',
142 p_in_join_tables => NULL);
143
144 -- prepare output for bind variables
145 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
146 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
147
148 -- set the basic bind variables for the status SQL
149 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
150
151 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
152 l_custom_rec.attribute_name := ':OPI_CCA_LEVEL_FLAG';
153 l_custom_rec.attribute_value := l_aggregation_level_flag;
154 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
155 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
156 x_custom_output.extend;
157 x_custom_output(x_custom_output.count) := l_custom_rec;
158
159
160 x_custom_sql := l_query;
161
162 END get_tbl_sql;
163
164
165 /*--------------------------------------------------
166 Function: get_cc_rtp_sel_clause
167 Description: builds the outer select clause for
168 Cycle Count Accuracy Report
169 ---------------------------------------------------*/
170 FUNCTION get_cc_rpt_sel_clause(p_view_by_dim IN VARCHAR2,
171 p_join_tbl IN
172 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
173 RETURN VARCHAR2
174 IS
175
176 l_sel_clause VARCHAR2(15000);
177 l_view_by_col_name VARCHAR2(60);
178 l_description VARCHAR2(30);
179 l_drill_across_rep_1 VARCHAR2(50);
180 l_drill_across_rep_2 VARCHAR2(50);
181 l_view_by_fact_col VARCHAR2(400);
182 l_drill_across VARCHAR2(1000);
183
184 BEGIN
185
186 -- initialization block
187 l_drill_across_rep_1 := 'OPI_DBI_CC_HM_TBL_REP' ;
188 l_drill_across_rep_2 := 'OPI_DBI_CC_ADJ_TBL_REP' ;
189
190 -- Column to get view by column name
191 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
192 (p_view_by_dim);
193
194 -- Item Description for item view by
195 get_cc_item_columns (p_view_by_dim, l_description);
196
197 -- fact column view by's
198 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
199 (p_join_tbl);
200
201 l_drill_across := '
202 ''pFunctionName='||l_drill_across_rep_1||'&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=' || p_view_by_dim || ''' OPI_DYNAMIC_URL_1,
203 ''pFunctionName='||l_drill_across_rep_2||'&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=' || p_view_by_dim || ''' OPI_DYNAMIC_URL_2,
204 ''pFunctionName='||l_drill_across_rep_1||'&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=' || p_view_by_dim || ''' OPI_DYNAMIC_URL_3';
205
206 -- Outer select clause
207 l_sel_clause :=
208 'SELECT
209 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
210 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
211 ' || l_description || ' OPI_ATTRIBUTE2,
212 ' || 'oset.OPI_MEASURE1,
213 ' || 'oset.OPI_MEASURE3,
214 ' || 'oset.OPI_MEASURE4,
215 ' || 'oset.OPI_MEASURE5,
216 ' || 'oset.OPI_MEASURE7,
217 ' || 'oset.OPI_MEASURE8,
218 ' || 'oset.OPI_MEASURE9,
219 ' || 'oset.OPI_MEASURE10,
220 ' || 'oset.OPI_MEASURE12,
221 ' || 'oset.OPI_MEASURE13,
222 ' || 'oset.OPI_MEASURE14,
223 ' || 'oset.OPI_MEASURE15,
224 ' || 'oset.OPI_MEASURE16,
225 ' || 'oset.OPI_MEASURE17,
226 ' || 'oset.OPI_MEASURE18,
227 ' || 'oset.OPI_MEASURE19,
228 ' || 'oset.OPI_MEASURE20,
229 ' || 'oset.OPI_MEASURE21,
230 ' || 'oset.OPI_MEASURE22,
231 ' || 'oset.OPI_MEASURE23,
232 ' || 'oset.OPI_MEASURE24,
233 ' || 'oset.OPI_MEASURE25,
234 ' || 'oset.OPI_MEASURE26,
235 ' || 'oset.OPI_MEASURE27,
236 ' || 'oset.OPI_MEASURE28,
237 ' || 'oset.OPI_MEASURE29,
238 ' || 'oset.OPI_MEASURE30,
239 ' || 'oset.OPI_MEASURE31,
240 ' || 'oset.OPI_MEASURE32,
241 ' || 'oset.OPI_MEASURE33,
242 ' || 'oset.OPI_MEASURE34,
243 ' || l_drill_across || '
244 ' || 'FROM
245 ' || '(SELECT (rank () over
246 ' || ' (&ORDER_BY_CLAUSE nulls last,
247 ' || l_view_by_fact_col || ')) - 1 rnk,
248 ' || l_view_by_fact_col || ',
249 ' || 'OPI_MEASURE1,
250 ' || 'OPI_MEASURE3,
251 ' || 'OPI_MEASURE4,
252 ' || 'OPI_MEASURE5,
253 ' || 'OPI_MEASURE7,
254 ' || 'OPI_MEASURE8,
255 ' || 'OPI_MEASURE9,
256 ' || 'OPI_MEASURE10,
257 ' || 'OPI_MEASURE12,
258 ' || 'OPI_MEASURE13,
259 ' || 'OPI_MEASURE14,
260 ' || 'OPI_MEASURE15,
261 ' || 'OPI_MEASURE16,
262 ' || 'OPI_MEASURE17,
263 ' || 'OPI_MEASURE18,
264 ' || 'OPI_MEASURE19,
265 ' || 'OPI_MEASURE20,
266 ' || 'OPI_MEASURE21,
267 ' || 'OPI_MEASURE22,
268 ' || 'OPI_MEASURE23,
269 ' || 'OPI_MEASURE24,
270 ' || 'OPI_MEASURE25,
271 ' || 'OPI_MEASURE26,
272 ' || 'OPI_MEASURE27,
273 ' || 'OPI_MEASURE28,
274 ' || 'OPI_MEASURE29,
275 ' || 'OPI_MEASURE30,
276 ' || 'OPI_MEASURE31,
277 ' || 'OPI_MEASURE32,
278 ' || 'OPI_MEASURE33,
279 ' || 'OPI_MEASURE34
280 ' || 'FROM
281 ' || '(SELECT
282 ' || l_view_by_fact_col || ',
283 ' || opi_dbi_rpt_util_pkg.nvl_str (
284 p_str => 'c_tot_entries',
285 p_default_val => 0)
286 || ' OPI_MEASURE1,
287 ' || opi_dbi_rpt_util_pkg.percent_str(
288 p_numerator => 'p_hits',
289 p_denominator => 'p_tot_entries',
290 p_measure_name => 'OPI_MEASURE3') || ',
291 ' || opi_dbi_rpt_util_pkg.percent_str (
292 p_numerator => 'c_hits',
293 p_denominator => 'c_tot_entries',
294 p_measure_name => 'OPI_MEASURE4') || ',
295 ' || opi_dbi_rpt_util_pkg.change_pct_str (
296 p_new_numerator => 'c_hits',
297 p_new_denominator => 'c_tot_entries',
298 p_old_numerator => 'p_hits',
299 p_old_denominator => 'p_tot_entries',
300 p_measure_name => 'OPI_MEASURE5') || ',
301 ' || opi_dbi_rpt_util_pkg.nvl_str (
302 p_str => 'c_gross_adj_val',
303 p_default_val => 0)
304 || ' OPI_MEASURE7,
305 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
306 p_numerator => 'p_gross_adj_val',
307 p_denominator => 'p_system_val',
308 p_measure_name => 'OPI_MEASURE8') || ',
309 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
313 ' || opi_dbi_rpt_util_pkg.change_pct_str (
310 p_numerator => 'c_gross_adj_val',
311 p_denominator => 'c_system_val',
312 p_measure_name => 'OPI_MEASURE9') || ',
314 p_new_numerator => 'c_gross_adj_val',
315 p_new_denominator => 'c_system_val',
316 p_old_numerator => 'p_gross_adj_val',
317 p_old_denominator => 'p_system_val',
318 p_measure_name => 'OPI_MEASURE10') || ',
319 ' || opi_dbi_rpt_util_pkg.percent_str (
320 p_numerator => 'p_exact_matches',
321 p_denominator => 'p_tot_entries',
322 p_measure_name => 'OPI_MEASURE12') || ',
323 ' || opi_dbi_rpt_util_pkg.percent_str (
324 p_numerator => 'c_exact_matches',
325 p_denominator => 'c_tot_entries',
326 p_measure_name => 'OPI_MEASURE13') || ',
327 ' || opi_dbi_rpt_util_pkg.change_pct_str (
328 p_new_numerator => 'c_exact_matches',
329 p_new_denominator => 'c_tot_entries',
330 p_old_numerator => 'p_exact_matches',
331 p_old_denominator => 'p_tot_entries',
332 p_measure_name => 'OPI_MEASURE14') || ',
333 ' || opi_dbi_rpt_util_pkg.nvl_str (
334 p_str => 'c_tot_entries_total',
335 p_default_val => 0)
336 || ' OPI_MEASURE15,
337 ' || opi_dbi_rpt_util_pkg.percent_str (
338 p_numerator =>'c_hits_total',
339 p_denominator =>'c_tot_entries_total',
340 p_measure_name => 'OPI_MEASURE16') || ',
341 ' || opi_dbi_rpt_util_pkg.change_pct_str (
342 p_new_numerator => 'c_hits_total',
343 p_new_denominator => 'c_tot_entries_total',
344 p_old_numerator => 'p_hits_total',
345 p_old_denominator => 'p_tot_entries_total',
346 p_measure_name => 'OPI_MEASURE17') || ',
347 ' || opi_dbi_rpt_util_pkg.nvl_str (
348 p_str => 'c_gross_adj_val_total',
349 p_default_val => 0)
350 || ' OPI_MEASURE18,
351 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
352 p_numerator =>'c_gross_adj_val_total',
353 p_denominator =>'c_system_val_total',
354 p_measure_name => 'OPI_MEASURE19') || ',
355 ' || opi_dbi_rpt_util_pkg.change_pct_str (
356 p_new_numerator => 'c_gross_adj_val_total',
357 p_new_denominator => 'c_system_val_total',
358 p_old_numerator => 'p_gross_adj_val_total',
359 p_old_denominator => 'p_system_val_total',
360 p_measure_name => 'OPI_MEASURE20') || ',
361 ' || opi_dbi_rpt_util_pkg.percent_str (
362 p_numerator =>'c_exact_matches_total',
363 p_denominator => 'c_tot_entries_total',
364 p_measure_name => 'OPI_MEASURE21') || ',
365 ' || opi_dbi_rpt_util_pkg.change_pct_str(
366 p_new_numerator => 'c_exact_matches_total',
367 p_new_denominator => 'c_tot_entries_total',
368 p_old_numerator => 'p_exact_matches_total',
369 p_old_denominator => 'p_tot_entries_total',
373 p_denominator => 'c_tot_entries',
370 p_measure_name => 'OPI_MEASURE22') ||',
371 ' || opi_dbi_rpt_util_pkg.percent_str (
372 p_numerator =>'c_hits',
374 p_measure_name => 'OPI_MEASURE23') || ',
375 ' || opi_dbi_rpt_util_pkg.percent_str (
376 p_numerator =>'p_hits',
377 p_denominator => 'p_tot_entries',
378 p_measure_name => 'OPI_MEASURE24') || ',
379 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
380 p_numerator =>'c_gross_adj_val',
381 p_denominator => 'c_system_val',
382 p_measure_name => 'OPI_MEASURE25') || ',
383 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
384 p_numerator =>'p_gross_adj_val',
385 p_denominator => 'p_system_val',
386 p_measure_name => 'OPI_MEASURE26') || ',
387 ' || opi_dbi_rpt_util_pkg.percent_str (
388 p_numerator =>'c_exact_matches',
389 p_denominator => 'c_tot_entries',
390 p_measure_name => 'OPI_MEASURE27') || ',
391 ' || opi_dbi_rpt_util_pkg.percent_str (
392 p_numerator =>'p_exact_matches',
393 p_denominator => 'p_tot_entries',
394 p_measure_name => 'OPI_MEASURE28') || ',
395 ' || opi_dbi_rpt_util_pkg.percent_str (
396 p_numerator =>'c_hits_total',
397 p_denominator => 'c_tot_entries_total',
398 p_measure_name => 'OPI_MEASURE29') || ',
399 ' || opi_dbi_rpt_util_pkg.percent_str (
400 p_numerator =>'p_hits_total',
401 p_denominator => 'p_tot_entries_total',
402 p_measure_name => 'OPI_MEASURE30') || ',
403 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
404 p_numerator =>'c_gross_adj_val_total',
405 p_denominator => 'c_system_val_total',
406 p_measure_name => 'OPI_MEASURE31') || ',
407 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
408 p_numerator =>'p_gross_adj_val_total',
409 p_denominator => 'p_system_val_total',
410 p_measure_name => 'OPI_MEASURE32') || ',
411 ' || opi_dbi_rpt_util_pkg.percent_str (
412 p_numerator =>'c_exact_matches_total',
413 p_denominator => 'c_tot_entries_total',
414 p_measure_name => 'OPI_MEASURE33') || ',
415 ' || opi_dbi_rpt_util_pkg.percent_str (
416 p_numerator =>'p_exact_matches_total',
417 p_denominator => 'p_tot_entries_total',
418 p_measure_name => 'OPI_MEASURE34') ;
419
420
421 RETURN l_sel_clause;
422
426 /*-----------------------------------------------------------------------------------
423 END get_cc_rpt_sel_clause;
424
425
427 Function: get_cc_item_columns
428 Description: When view by is item this function adds
429 column for item description to outer select
430 ------------------------------------------------------------------------------------*/
431 PROCEDURE get_cc_item_columns ( p_dim_name VARCHAR2,
432 p_description OUT NOCOPY VARCHAR2,
433 p_col_type IN VARCHAR2 := 'ITEM')
434 IS
435 l_view VARCHAR2(3);
436
437 BEGIN
438 CASE
439 WHEN p_col_type = 'ITEM' THEN
440 BEGIN
441 l_view := 'v';
442 END;
443 WHEN p_col_type = 'UOM' THEN
444 BEGIN
445 l_view := 'v2';
446 END;
447 END CASE;
448
449 CASE
450 WHEN p_dim_name = 'ITEM+ENI_ITEM_ORG' THEN
451 BEGIN
452 p_description := l_view || '.' ||'description';
453 END;
454 ELSE
455 BEGIN
456 p_description := 'NULL';
457 END;
458 END CASE;
459 END get_cc_item_columns;
460
461
462
463 /*----------------------------------------
464 Trend Report Function
465 ----------------------------------------*/
466 PROCEDURE get_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
467 x_custom_sql OUT NOCOPY VARCHAR2,
468 x_custom_output OUT NOCOPY
469 BIS_QUERY_ATTRIBUTES_TBL)
470 IS
471 l_query VARCHAR2(32767);
472 l_view_by VARCHAR2(120);
473 l_view_by_col VARCHAR2 (120);
474 l_xtd VARCHAR2(10);
475 l_comparison_type VARCHAR2(1);
476 l_cur_suffix VARCHAR2(5);
477 l_custom_sql VARCHAR2 (10000);
478
479 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
480 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
481
482 l_where_clause VARCHAR2 (2000);
483 l_mv VARCHAR2 (30);
484
485 l_aggregation_level_flag VARCHAR2(10);
486
487 l_custom_rec BIS_QUERY_ATTRIBUTES;
488
489 BEGIN
490
491 -- initialization block
492 l_comparison_type := 'Y';
493 l_aggregation_level_flag := '0';
494
495 -- clear out the tables.
496 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
497 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
498
499 -- get all the query parameters
500 opi_dbi_rpt_util_pkg.process_parameters (
501 p_param => p_param,
502 p_view_by => l_view_by,
503 p_view_by_col_name => l_view_by_col,
504 p_comparison_type => l_comparison_type,
505 p_xtd => l_xtd,
506 p_cur_suffix => l_cur_suffix,
507 p_where_clause => l_where_clause,
508 p_mv => l_mv,
509 p_join_tbl => l_join_tbl,
510 p_mv_level_flag =>l_aggregation_level_flag,
511 p_trend => 'Y',
512 p_func_area => 'OPI',
513 p_version => '7.0',
514 p_role => '',
515 p_mv_set => 'CCAC',
516 p_mv_flag_type => 'CCA_LEVEL');
517 -- Add measure columns that need to be aggregated
518 -- No Grand totals required.
519 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
520 p_col_name => 'number_of_hits' ,
521 p_alias_name => 'hits',
522 p_grand_total => 'N',
523 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
524 p_to_date_type => 'XTD');
525
526 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
527 p_col_name => 'number_of_exact_matches' ,
528 p_alias_name => 'exact_matches',
529 p_grand_total => 'N',
530 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
531 p_to_date_type => 'XTD');
532
533 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
537 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
534 p_col_name => 'number_of_total_entries',
535 p_alias_name => 'tot_entries',
536 p_grand_total => 'N',
538 p_to_date_type => 'XTD');
539
540 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
541 p_col_name => 'system_inventory_val_' || l_cur_suffix,
542 p_alias_name => 'system_val',
543 p_grand_total => 'N',
544 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
545 p_to_date_type => 'XTD');
546
547 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
548 p_col_name => 'gross_adjustment_val_' || l_cur_suffix,
549 p_alias_name => 'gross_adj_val',
550 p_grand_total => 'N',
551 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
552 p_to_date_type => 'XTD');
553
554 -- Merge Outer and Inner Query
555 l_query := get_cc_trd_sel_clause(l_view_by) ||
556 ' from ' ||
557 poa_dbi_template_pkg.trend_sql (
558 p_xtd => l_xtd,
559 p_comparison_type => l_comparison_type,
560 p_fact_name => l_mv,
561 p_where_clause => l_where_clause,
562 p_col_name => l_col_tbl,
563 p_use_grpid => 'N');
564
565
566 -- Prepare PMV bind variables
567 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
568 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
569
570 -- get all the basic binds used by POA queries
571 -- Do this before adding any of our binds, since the procedure
572 -- reinitializes the output table
573 poa_dbi_util_pkg.get_custom_trend_binds (
574 p_xtd => l_xtd,
575 p_comparison_type => l_comparison_type,
576 x_custom_output => x_custom_output);
577
578 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
579 l_custom_rec.attribute_name := ':OPI_CCA_LEVEL_FLAG';
580 l_custom_rec.attribute_value := l_aggregation_level_flag;
581 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
582 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
583 x_custom_output.extend;
584 x_custom_output(x_custom_output.count) := l_custom_rec;
585
586 x_custom_sql := l_query;
587
588 END get_trd_sql;
589
590 /*--------------------------------------------------
591 Function: get_cc_trd_sel_clause
592 Description: builds the outer select clause for
593 Cycle Count Accuracy Trend Report
594 ---------------------------------------------------*/
595 FUNCTION get_cc_trd_sel_clause (p_view_by_dim IN VARCHAR2)
596 RETURN VARCHAR2
597 IS
598
599 l_sel_clause varchar2(7500);
600
601 BEGIN
602
603 -- Main Outer query
604
605 l_sel_clause :=
606 'SELECT
607 ' || ' cal.name VIEWBY,
608 ' || opi_dbi_rpt_util_pkg.nvl_str (p_str => 'iset.c_tot_entries')
609 || ' OPI_MEASURE1,
610 ' || opi_dbi_rpt_util_pkg.percent_str (
614 ' || opi_dbi_rpt_util_pkg.percent_str (
611 p_numerator =>'iset.p_hits',
612 p_denominator => 'iset.p_tot_entries',
613 p_measure_name => 'OPI_MEASURE3') || ',
615 p_numerator =>'iset.c_hits',
616 p_denominator => 'iset.c_tot_entries',
617 p_measure_name => 'OPI_MEASURE4') || ',
618 ' || opi_dbi_rpt_util_pkg.change_pct_str (
619 p_new_numerator => 'iset.c_hits',
620 p_new_denominator => 'iset.c_tot_entries',
621 p_old_numerator => 'iset.p_hits',
622 p_old_denominator => 'iset.p_tot_entries',
623 p_measure_name => 'OPI_MEASURE5') || ',
624 ' || opi_dbi_rpt_util_pkg.nvl_str (p_str => 'iset.c_gross_adj_val')
625 || ' OPI_MEASURE7,
626 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
627 p_numerator =>'iset.p_gross_adj_val',
628 p_denominator => 'iset.p_system_val',
629 p_measure_name => 'OPI_MEASURE8') || ',
630 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
631 p_numerator =>'iset.c_gross_adj_val',
632 p_denominator => 'iset.c_system_val',
633 p_measure_name => 'OPI_MEASURE9') || ',
634 ' || opi_dbi_rpt_util_pkg.change_pct_str (
635 p_new_numerator => 'iset.c_gross_adj_val',
636 p_new_denominator => 'iset.c_system_val',
637 p_old_numerator => 'iset.p_gross_adj_val',
638 p_old_denominator => 'iset.p_system_val',
639 p_measure_name => 'OPI_MEASURE10') || ',
640 ' || opi_dbi_rpt_util_pkg.percent_str (
641 p_numerator =>'iset.p_exact_matches',
642 p_denominator => 'iset.p_tot_entries',
643 p_measure_name => 'OPI_MEASURE12') || ',
644 ' || opi_dbi_rpt_util_pkg.percent_str (
645 p_numerator =>'iset.c_exact_matches',
646 p_denominator => 'iset.c_tot_entries',
647 p_measure_name => 'OPI_MEASURE13') || ',
648 ' || opi_dbi_rpt_util_pkg.change_pct_str (
649 p_new_numerator => 'iset.c_exact_matches',
650 p_new_denominator => 'iset.c_tot_entries',
651 p_old_numerator => 'iset.p_exact_matches',
652 p_old_denominator => 'iset.p_tot_entries',
653 p_measure_name => 'OPI_MEASURE14') ;
654 RETURN l_sel_clause;
655
656 END get_cc_trd_sel_clause;
657
658
659 PROCEDURE get_hm_tbl_sql(
660 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
661 x_custom_sql OUT NOCOPY VARCHAR2,
662 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
663 )
664 IS
665 l_query VARCHAR2(32767);
666 l_view_by VARCHAR2(120);
667 l_view_by_col VARCHAR2 (120);
668 l_xtd VARCHAR2(10);
669 l_comparison_type VARCHAR2(1);
670 l_cur_suffix VARCHAR2(5);
671 l_custom_sql VARCHAR2 (10000);
672
673 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
674 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
675
676 l_where_clause VARCHAR2 (2000);
677 l_mv VARCHAR2 (30);
678
682 BEGIN
679 l_aggregation_level_flag VARCHAR2(10);
680
681 l_custom_rec BIS_QUERY_ATTRIBUTES;
683
684 -- initialization block
685 l_aggregation_level_flag := '0';
686 l_comparison_type := 'Y';
687
688 -- clear out the tables.
689 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
690 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
691
692
693 -- get all the query parameters
694 opi_dbi_rpt_util_pkg.process_parameters (
695 p_param => p_param,
696 p_view_by => l_view_by,
697 p_view_by_col_name => l_view_by_col,
698 p_comparison_type => l_comparison_type,
699 p_xtd => l_xtd,
700 p_cur_suffix => l_cur_suffix,
701 p_where_clause => l_where_clause,
702 p_mv => l_mv,
703 p_join_tbl => l_join_tbl,
704 p_mv_level_flag => l_aggregation_level_flag,
705 p_trend => 'N',
706 p_func_area => 'OPI',
707 p_version => '7.0',
708 p_role => '',
709 p_mv_set => 'CCAC',
710 p_mv_flag_type => 'CCA_LEVEL');
711
712 -- Add measure columns that need to be aggregated
713 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
714 p_col_name => 'number_of_hits' ,
715 p_alias_name => 'hits',
716 p_grand_total => 'Y',
717 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
718 p_to_date_type => 'XTD');
719
720 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
721 p_col_name => 'number_of_exact_matches',
722 p_alias_name => 'exact_matches',
723 p_grand_total => 'Y',
724 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
725 p_to_date_type => 'XTD');
726
727 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
728 p_col_name => 'number_of_misses',
729 p_alias_name => 'misses',
730 p_grand_total => 'Y',
731 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
732 p_to_date_type => 'XTD');
733
734 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
735 p_col_name => 'number_of_total_entries',
736 p_alias_name => 'tot_entries',
737 p_grand_total => 'Y',
738 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
739 p_to_date_type => 'XTD');
740
741
742 -- construct the query
743 l_query := get_hitmiss_sel_clause (l_view_by, l_join_tbl)
744 || ' from
745 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
746 p_where_clause => l_where_clause,
747 p_join_tables => l_join_tbl,
748 p_use_windowing => 'Y',
749 p_col_name => l_col_tbl,
750 p_use_grpid => 'N',
751 p_paren_count => 3,
752 p_filter_where => NULL,
753 p_generate_viewby => 'Y',
754 p_in_join_tables => NULL);
755
756 -- prepare output for bind variables
757 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
758 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
759
760 -- set the basic bind variables for the status SQL
761 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
762
763 -- Passing aggregation level flag to PMV
764 l_custom_rec.attribute_name := ':OPI_CCA_LEVEL_FLAG';
765 l_custom_rec.attribute_value := l_aggregation_level_flag;
766 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
767 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
768 x_custom_output.extend;
769 x_custom_output(x_custom_output.count) := l_custom_rec;
770
771
772 x_custom_sql := l_query;
773
774 END get_hm_tbl_sql;
775
776
777 /*--------------------------------------------------
778 Function: get_hitmiss_sel_clause
779 Description: builds the outer select clause for
780 Hit/Miss Summary Report
781 ---------------------------------------------------*/
782
783 FUNCTION get_hitmiss_sel_clause(p_view_by_dim IN VARCHAR2,
784 p_join_tbl IN
785 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
786 return VARCHAR2
787 IS
788
789 l_sel_clause varchar2(7000);
790 l_view_by_col_name varchar2(60);
791 l_description varchar2(30);
792 l_err varchar2(200);
793 l_view_by_fact_col VARCHAR2 (400);
794
795 BEGIN
796
797 -- Column to get view by column name
798 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
799 (p_view_by_dim);
800
801
802 -- Description for item view by
803 get_cc_item_columns (p_view_by_dim, l_description);
804
805 -- fact column view by's
806 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
807 (p_join_tbl);
808
809 -- Outer select clause
810 l_sel_clause :=
811 'SELECT
812 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
813 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
814 ' || l_description || ' OPI_ATTRIBUTE2,
815 ' || 'oset.OPI_MEASURE1,
816 ' || 'oset.OPI_MEASURE3,
817 ' || 'oset.OPI_MEASURE4,
818 ' || 'oset.OPI_MEASURE5,
819 ' || 'oset.OPI_MEASURE6,
820 ' || 'oset.OPI_MEASURE8,
821 ' || 'oset.OPI_MEASURE9,
822 ' || 'oset.OPI_MEASURE10,
823 ' || 'oset.OPI_MEASURE11,
824 ' || 'oset.OPI_MEASURE13,
825 ' || 'oset.OPI_MEASURE14,
826 ' || 'oset.OPI_MEASURE15,
827 ' || 'oset.OPI_MEASURE16,
828 ' || 'oset.OPI_MEASURE17,
829 ' || 'oset.OPI_MEASURE18,
830 ' || 'oset.OPI_MEASURE19,
831 ' || 'oset.OPI_MEASURE20,
832 ' || 'oset.OPI_MEASURE21,
833 ' || 'oset.OPI_MEASURE22,
834 ' || 'oset.OPI_MEASURE23,
835 ' || 'oset.OPI_MEASURE24,
836 ' || 'oset.OPI_MEASURE25,
837 ' || 'oset.OPI_MEASURE26
838 ' || 'FROM
839 ' || '(SELECT (rank () over
840 ' || ' (&ORDER_BY_CLAUSE nulls last,
841 ' || l_view_by_fact_col || ')) - 1 rnk,
842 ' || l_view_by_fact_col || ',
843 ' || 'OPI_MEASURE1,
844 ' || 'OPI_MEASURE3,
845 ' || 'OPI_MEASURE4,
846 ' || 'OPI_MEASURE5,
847 ' || 'OPI_MEASURE6,
848 ' || 'OPI_MEASURE8,
849 ' || 'OPI_MEASURE9,
850 ' || 'OPI_MEASURE10,
851 ' || 'OPI_MEASURE11,
852 ' || 'OPI_MEASURE13,
853 ' || 'OPI_MEASURE14,
854 ' || 'OPI_MEASURE15,
855 ' || 'OPI_MEASURE16,
856 ' || 'OPI_MEASURE17,
857 ' || 'OPI_MEASURE18,
858 ' || 'OPI_MEASURE19,
859 ' || 'OPI_MEASURE20,
860 ' || 'OPI_MEASURE21,
861 ' || 'OPI_MEASURE22,
862 ' || 'OPI_MEASURE23,
863 ' || 'OPI_MEASURE24,
864 ' || 'OPI_MEASURE25,
865 ' || 'OPI_MEASURE26
866 ' || 'FROM
867 ' || '(SELECT
868 ' || l_view_by_fact_col || ',
869 ' || opi_dbi_rpt_util_pkg.nvl_str (p_str => 'c_tot_entries')
870 || ' OPI_MEASURE1,
871 ' || opi_dbi_rpt_util_pkg.nvl_str (
872 p_str => 'c_hits')
873 || ' OPI_MEASURE3,
874 ' || opi_dbi_rpt_util_pkg.percent_str (
875 p_numerator =>'p_hits',
876 p_denominator => 'p_tot_entries',
877 p_measure_name => 'OPI_MEASURE4') || ',
878 ' || opi_dbi_rpt_util_pkg.percent_str (
879 p_numerator =>'c_hits',
880 p_denominator => 'c_tot_entries',
881 p_measure_name => 'OPI_MEASURE5') || ',
882 ' || opi_dbi_rpt_util_pkg.change_pct_str (
883 p_new_numerator => 'c_hits',
884 p_new_denominator => 'c_tot_entries',
885 p_old_numerator => 'p_hits',
886 p_old_denominator => 'p_tot_entries',
887 p_measure_name => 'OPI_MEASURE6') || ',
888 ' || opi_dbi_rpt_util_pkg.nvl_str (
889 p_str => 'c_misses')
890 || ' OPI_MEASURE8,
891 ' || opi_dbi_rpt_util_pkg.percent_str (
892 p_numerator =>'p_misses',
893 p_denominator => 'p_tot_entries',
894 p_measure_name => 'OPI_MEASURE9') || ',
895 ' || opi_dbi_rpt_util_pkg.percent_str (
896 p_numerator =>'c_misses',
897 p_denominator => 'c_tot_entries',
898 p_measure_name => 'OPI_MEASURE10') || ',
899 ' || opi_dbi_rpt_util_pkg.change_pct_str (
900 p_new_numerator => 'c_misses',
901 p_new_denominator => 'c_tot_entries',
902 p_old_numerator => 'p_misses',
903 p_old_denominator => 'p_tot_entries',
904 p_measure_name => 'OPI_MEASURE11') || ',
905 ' || opi_dbi_rpt_util_pkg.nvl_str (
906 p_str => 'c_exact_matches')
907 || ' OPI_MEASURE13,
908 ' || opi_dbi_rpt_util_pkg.percent_str (
909 p_numerator =>'p_exact_matches',
910 p_denominator => 'p_tot_entries',
911 p_measure_name => 'OPI_MEASURE14') || ',
912 ' || opi_dbi_rpt_util_pkg.percent_str (
913 p_numerator =>'c_exact_matches',
914 p_denominator => 'c_tot_entries',
915 p_measure_name => 'OPI_MEASURE15') || ',
916 ' || opi_dbi_rpt_util_pkg.change_pct_str (
917 p_new_numerator => 'c_exact_matches',
918 p_new_denominator => 'c_tot_entries',
919 p_old_numerator => 'p_exact_matches',
920 p_old_denominator => 'p_tot_entries',
921 p_measure_name => 'OPI_MEASURE16') || ',
922 ' || opi_dbi_rpt_util_pkg.nvl_str (
923 p_str => 'c_tot_entries_total')
924 || ' OPI_MEASURE17,
925 ' || opi_dbi_rpt_util_pkg.nvl_str (
926 p_str => 'c_hits_total')
927 || ' OPI_MEASURE18,
928 ' || opi_dbi_rpt_util_pkg.percent_str (
929 p_numerator =>'c_hits_total',
930 p_denominator => 'c_tot_entries_total',
931 p_measure_name => 'OPI_MEASURE19') || ',
932 ' || opi_dbi_rpt_util_pkg.change_pct_str (
933 p_new_numerator => 'c_hits_total',
934 p_new_denominator => 'c_tot_entries_total',
935 p_old_numerator => 'p_hits_total',
936 p_old_denominator => 'p_tot_entries_total',
937 p_measure_name => 'OPI_MEASURE20') || ',
938 ' || opi_dbi_rpt_util_pkg.nvl_str (p_str => 'c_misses_total')
939 || ' OPI_MEASURE21,
940 ' || opi_dbi_rpt_util_pkg.percent_str (
941 p_numerator =>'c_misses_total',
942 p_denominator => 'c_tot_entries_total',
943 p_measure_name => 'OPI_MEASURE22') || ',
944 ' || opi_dbi_rpt_util_pkg.change_pct_str (
945 p_new_numerator => 'c_misses_total',
946 p_new_denominator => 'c_tot_entries_total',
947 p_old_numerator => 'p_misses_total',
948 p_old_denominator => 'p_tot_entries_total',
949 p_measure_name => 'OPI_MEASURE23') || ',
950 ' || opi_dbi_rpt_util_pkg.nvl_str (
951 p_str => 'c_exact_matches_total')
952 || ' OPI_MEASURE24,
953 ' || opi_dbi_rpt_util_pkg.percent_str (
954 p_numerator =>'c_exact_matches_total',
955 p_denominator => 'c_tot_entries_total',
956 p_measure_name => 'OPI_MEASURE25') || ',
957 ' || opi_dbi_rpt_util_pkg.change_pct_str (
958 p_new_numerator => 'c_exact_matches_total',
959 p_new_denominator => 'c_tot_entries_total',
960 p_old_numerator => 'p_exact_matches_total',
961 p_old_denominator => 'p_tot_entries_total',
962 p_measure_name => 'OPI_MEASURE26') ;
963
964 RETURN l_sel_clause;
965
966 END get_hitmiss_sel_clause;
967
968 /*----------------------------------------
969 Cycle Count Adjustment Summary Report Function
970 ----------------------------------------*/
971 PROCEDURE get_adj_tbl_sql(
972 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
973 x_custom_sql OUT NOCOPY VARCHAR2,
974 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
975 )
976 IS
977 l_query VARCHAR2(32767);
978 l_view_by VARCHAR2(120);
979 l_view_by_col VARCHAR2 (120);
980 l_xtd VARCHAR2(10);
981 l_comparison_type VARCHAR2(1);
982 l_cur_suffix VARCHAR2(5);
983 l_custom_sql VARCHAR2 (10000);
984
985 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
986 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
987
988 l_where_clause VARCHAR2 (2000);
989 l_mv VARCHAR2 (30);
990
991 l_aggregation_level_flag VARCHAR2(10);
992
993 l_custom_rec BIS_QUERY_ATTRIBUTES;
994
995 BEGIN
996
997 -- initialization block
998 l_comparison_type := 'Y';
999 l_aggregation_level_flag := '0';
1000
1001 -- clear out the column and Join info tables.
1002 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1003 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1004
1005 -- get all the query parameters
1006 opi_dbi_rpt_util_pkg.process_parameters (
1007 p_param => p_param,
1008 p_view_by => l_view_by,
1009 p_view_by_col_name => l_view_by_col,
1010 p_comparison_type => l_comparison_type,
1011 p_xtd => l_xtd,
1012 p_cur_suffix => l_cur_suffix,
1013 p_where_clause => l_where_clause,
1014 p_mv => l_mv,
1015 p_join_tbl => l_join_tbl,
1016 p_mv_level_flag => l_aggregation_level_flag,
1017 p_trend => 'N',
1018 p_func_area => 'OPI',
1019 p_version => '7.0',
1020 p_role => '',
1021 p_mv_set => 'CCAD',
1022 p_mv_flag_type => 'CCA_LEVEL');
1023
1024 -- Add measure columns that need to be aggregated
1025 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1029 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1026 p_col_name => 'system_inventory_qty' ,
1027 p_alias_name => 'system_qty',
1028 p_grand_total => 'N',
1030 p_to_date_type => 'XTD');
1031
1032 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1033 p_col_name => 'net_adjustment_qty' ,
1034 p_alias_name => 'net_adj_qty',
1035 p_grand_total => 'N',
1036 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1037 p_to_date_type => 'XTD');
1038
1039 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1040 p_col_name => 'gross_adjustment_qty' ,
1041 p_alias_name => 'gross_adj_qty',
1042 p_grand_total => 'N',
1043 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1044 p_to_date_type => 'XTD');
1045
1046 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1047 p_col_name => 'number_of_adjustments' ,
1048 p_alias_name => 'adjustments',
1049 p_grand_total => 'Y',
1050 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1051 p_to_date_type => 'XTD');
1052
1053 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1054 p_col_name => 'number_of_total_entries',
1055 p_alias_name => 'tot_entries',
1056 p_grand_total => 'Y',
1057 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1058 p_to_date_type => 'XTD');
1059
1060 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1061 p_col_name => 'system_inventory_val_' || l_cur_suffix,
1062 p_alias_name => 'system_val',
1063 p_grand_total => 'Y',
1064 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1065 p_to_date_type => 'XTD');
1066
1067 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1068 p_col_name => 'gross_adjustment_val_' || l_cur_suffix,
1069 p_alias_name => 'gross_adj_val',
1070 p_grand_total => 'Y',
1071 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1072 p_to_date_type => 'XTD');
1073
1074 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1075 p_col_name => 'net_adjustment_val_' || l_cur_suffix,
1076 p_alias_name => 'net_adj_val',
1077 p_grand_total => 'Y',
1078 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1079 p_to_date_type => 'XTD');
1080
1081 -- construct the query
1082 l_query := get_adj_rpt_sel_clause (l_view_by, l_join_tbl)
1083 || ' from
1084 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
1085 p_where_clause => l_where_clause,
1086 p_join_tables => l_join_tbl,
1087 p_use_windowing => 'Y',
1088 p_col_name => l_col_tbl,
1089 p_use_grpid => 'N',
1090 p_paren_count => 3,
1091 p_filter_where => NULL,
1092 p_generate_viewby => 'Y',
1093 p_in_join_tables => NULL);
1094
1095 -- prepare output for bind variables
1096 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1097 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1098
1099 -- set the basic bind variables for the status SQL
1100 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
1101
1102 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
1103 l_custom_rec.attribute_name := ':OPI_CCA_LEVEL_FLAG';
1104 l_custom_rec.attribute_value := l_aggregation_level_flag;
1105 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1106 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1107 x_custom_output.extend;
1108 x_custom_output(x_custom_output.count) := l_custom_rec;
1109
1110
1111 x_custom_sql := l_query;
1112
1113 END get_adj_tbl_sql;
1114
1115 /*--------------------------------------------------
1116 Function: get_adj_rpt_sel_clause
1117 Description: builds the outer select clause for
1118 Cycle Count Adjustment Summary Report
1119 ---------------------------------------------------*/
1120 FUNCTION get_adj_rpt_sel_clause(p_view_by_dim IN VARCHAR2,
1121 p_join_tbl IN
1122 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
1123 RETURN VARCHAR2
1124 IS
1125
1126 l_sel_clause VARCHAR2(7500);
1127 l_view_by_col_name VARCHAR2(60);
1128 l_item VARCHAR2(30);
1129 l_uom VARCHAR2(30);
1130 l_view_by_fact_col VARCHAR2(400);
1131 BEGIN
1132
1133 -- Column to get view by column name
1134 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
1135 (p_view_by_dim);
1136
1137 -- Item Description for item view by
1138 get_cc_item_columns (p_view_by_dim, l_item, 'ITEM');
1139 get_cc_item_columns (p_view_by_dim, l_uom, 'UOM');
1140
1141 -- fact column view by's
1142 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
1143 (p_join_tbl);
1144
1145 -- Outer select clause
1146 l_sel_clause :=
1147 'SELECT
1148 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
1149 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
1150 ' || l_item || ' OPI_ATTRIBUTE2,
1151 ' || l_uom || ' OPI_ATTRIBUTE3,
1152 ' || 'oset.OPI_MEASURE2,
1153 ' || 'oset.OPI_MEASURE3,
1154 ' || 'oset.OPI_MEASURE5,
1155 ' || 'oset.OPI_MEASURE6,
1156 ' || 'oset.OPI_MEASURE8,
1157 ' || 'oset.OPI_MEASURE9,
1158 ' || 'oset.OPI_MEASURE10,
1159 ' || 'oset.OPI_MEASURE11,
1160 ' || 'oset.OPI_MEASURE12,
1161 ' || 'oset.OPI_MEASURE14,
1162 ' || 'oset.OPI_MEASURE15,
1163 ' || 'oset.OPI_MEASURE16,
1164 ' || 'oset.OPI_MEASURE17,
1165 ' || 'oset.OPI_MEASURE18,
1166 ' || 'oset.OPI_MEASURE19,
1167 ' || 'oset.OPI_MEASURE20,
1168 ' || 'oset.OPI_MEASURE21,
1169 ' || 'oset.OPI_MEASURE22,
1170 ' || 'oset.OPI_MEASURE23,
1171 ' || 'oset.OPI_MEASURE24,
1172 ' || 'oset.OPI_MEASURE25,
1173 ' || 'oset.OPI_MEASURE26,
1174 ' || 'oset.OPI_MEASURE27
1175 ' || 'FROM
1176 ' || '(SELECT (rank () over
1177 ' || ' (&ORDER_BY_CLAUSE nulls last,
1178 ' || l_view_by_fact_col || ')) - 1 rnk,
1179 ' || l_view_by_fact_col || ',
1180 ' || 'OPI_MEASURE2,
1181 ' || 'OPI_MEASURE3,
1182 ' || 'OPI_MEASURE5,
1183 ' || 'OPI_MEASURE6,
1184 ' || 'OPI_MEASURE8,
1185 ' || 'OPI_MEASURE9,
1186 ' || 'OPI_MEASURE10,
1187 ' || 'OPI_MEASURE11,
1188 ' || 'OPI_MEASURE12,
1189 ' || 'OPI_MEASURE14,
1190 ' || 'OPI_MEASURE15,
1191 ' || 'OPI_MEASURE16,
1192 ' || 'OPI_MEASURE17,
1193 ' || 'OPI_MEASURE18,
1194 ' || 'OPI_MEASURE19,
1195 ' || 'OPI_MEASURE20,
1196 ' || 'OPI_MEASURE21,
1197 ' || 'OPI_MEASURE22,
1198 ' || 'OPI_MEASURE23,
1199 ' || 'OPI_MEASURE24,
1200 ' || 'OPI_MEASURE25,
1201 ' || 'OPI_MEASURE26,
1202 ' || 'OPI_MEASURE27
1203 ' || 'FROM
1204 ' || '(SELECT
1205 ' || l_view_by_fact_col || ',
1206 ' || opi_dbi_rpt_util_pkg.nvl_str (
1207 p_str => 'c_tot_entries',
1208 p_default_val => 0)
1209 || ' OPI_MEASURE2,
1210 ' || opi_dbi_rpt_util_pkg.nvl_str (
1211 p_str => 'c_adjustments',
1212 p_default_val => 0)
1213 || ' OPI_MEASURE3,
1214 ' || opi_dbi_rpt_util_pkg.nvl_str (
1215 p_str => 'c_system_qty',
1216 p_default_val => 0)
1217 || ' OPI_MEASURE5,
1218 ' || opi_dbi_rpt_util_pkg.nvl_str (
1219 p_str => 'c_system_val',
1220 p_default_val => 0)
1221 || ' OPI_MEASURE6,
1222 ' || opi_dbi_rpt_util_pkg.nvl_str (
1223 p_str => 'c_gross_adj_qty',
1224 p_default_val => 0)
1225 || ' OPI_MEASURE8,
1226 ' || opi_dbi_rpt_util_pkg.nvl_str (
1227 p_str => 'c_gross_adj_val',
1228 p_default_val => 0)
1229 || ' OPI_MEASURE9,
1230 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
1231 p_numerator => 'p_gross_adj_val',
1232 p_denominator => 'p_system_val',
1233 p_measure_name => 'OPI_MEASURE10') || ',
1234 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
1235 p_numerator => 'c_gross_adj_val',
1236 p_denominator => 'c_system_val',
1237 p_measure_name => 'OPI_MEASURE11') || ',
1238 ' || opi_dbi_rpt_util_pkg.change_pct_str (
1239 p_new_numerator => 'c_gross_adj_val',
1240 p_new_denominator => 'c_system_val',
1241 p_old_numerator => 'p_gross_adj_val',
1242 p_old_denominator => 'p_system_val',
1243 p_measure_name => 'OPI_MEASURE12') || ',
1244 ' || opi_dbi_rpt_util_pkg.nvl_str (
1245 p_str => 'c_net_adj_qty',
1246 p_default_val => 0)
1247 || ' OPI_MEASURE14,
1248 ' || opi_dbi_rpt_util_pkg.nvl_str (
1249 p_str => 'c_net_adj_val',
1250 p_default_val => 0)
1251 || ' OPI_MEASURE15,
1252 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
1253 p_numerator => 'p_net_adj_val',
1254 p_denominator => 'p_system_val',
1255 p_measure_name => 'OPI_MEASURE16') || ',
1256 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
1257 p_numerator => 'c_net_adj_val',
1258 p_denominator => 'c_system_val',
1259 p_measure_name => 'OPI_MEASURE17') || ',
1260 ' || opi_dbi_rpt_util_pkg.change_pct_str (
1261 p_new_numerator => 'c_net_adj_val',
1262 p_new_denominator => 'c_system_val',
1263 p_old_numerator => 'p_net_adj_val',
1264 p_old_denominator => 'p_system_val',
1265 p_measure_name => 'OPI_MEASURE18') || ',
1266 ' || opi_dbi_rpt_util_pkg.nvl_str (
1267 p_str => 'c_tot_entries_total',
1268 p_default_val => 0)
1272 p_default_val => 0)
1269 || ' OPI_MEASURE19,
1270 ' || opi_dbi_rpt_util_pkg.nvl_str (
1271 p_str => 'c_adjustments_total',
1273 || ' OPI_MEASURE20,
1274 ' || opi_dbi_rpt_util_pkg.nvl_str (
1275 p_str => 'c_system_val_total',
1276 p_default_val => 0)
1277 || ' OPI_MEASURE21,
1278 ' || opi_dbi_rpt_util_pkg.nvl_str (
1279 p_str => 'c_gross_adj_val_total',
1280 p_default_val => 0)
1281 || ' OPI_MEASURE22,
1282 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
1283 p_numerator =>'c_gross_adj_val_total',
1284 p_denominator =>'c_system_val_total',
1285 p_measure_name => 'OPI_MEASURE23') || ',
1286 ' || opi_dbi_rpt_util_pkg.change_pct_str (
1287 p_new_numerator => 'c_gross_adj_val_total',
1288 p_new_denominator => 'c_system_val_total',
1289 p_old_numerator => 'p_gross_adj_val_total',
1290 p_old_denominator => 'p_system_val_total',
1291 p_measure_name => 'OPI_MEASURE24') || ',
1292 ' || opi_dbi_rpt_util_pkg.nvl_str (
1293 p_str => 'c_net_adj_val_total',
1294 p_default_val => 0)
1295 || ' OPI_MEASURE25,
1296 ' || opi_dbi_rpt_util_pkg.pos_denom_percent_str (
1297 p_numerator =>'c_net_adj_val_total',
1298 p_denominator =>'c_system_val_total',
1299 p_measure_name => 'OPI_MEASURE26') || ',
1300 ' || opi_dbi_rpt_util_pkg.change_pct_str (
1301 p_new_numerator => 'c_net_adj_val_total',
1302 p_new_denominator => 'c_system_val_total',
1303 p_old_numerator => 'p_net_adj_val_total',
1304 p_old_denominator => 'p_system_val_total',
1305 p_measure_name => 'OPI_MEASURE27');
1306
1307 RETURN l_sel_clause;
1308
1309 END get_adj_rpt_sel_clause;
1310
1311 /*----------------------------------------
1312 Cycle Count Adjustment Detail Report Function
1313 ----------------------------------------*/
1314 PROCEDURE get_adj_dtl_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1315 x_custom_sql OUT NOCOPY VARCHAR2,
1316 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1317 IS
1318 l_query VARCHAR2(32767);
1319 l_view_by VARCHAR2(120);
1320 l_view_by_col VARCHAR2 (120);
1321 l_xtd VARCHAR2(10);
1322 l_comparison_type VARCHAR2(1);
1323 l_cur_suffix VARCHAR2(5);
1324 l_custom_sql VARCHAR2 (10000);
1325
1326 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1330 l_mv VARCHAR2 (30);
1327 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1328
1329 l_where_clause VARCHAR2 (2000);
1331
1332 l_aggregation_level_flag VARCHAR2(10);
1333
1334 l_custom_rec BIS_QUERY_ATTRIBUTES;
1335
1336 BEGIN
1337
1338 -- initialization block
1339 l_comparison_type := 'Y';
1340 l_aggregation_level_flag := '0';
1341
1342
1343 -- clear out the column and Join info tables.
1344 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1345 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1346
1347 -- get all the query parameters
1348 opi_dbi_rpt_util_pkg.process_parameters (
1349 p_param => p_param,
1350 p_view_by => l_view_by,
1351 p_view_by_col_name => l_view_by_col,
1352 p_comparison_type => l_comparison_type,
1353 p_xtd => l_xtd,
1354 p_cur_suffix => l_cur_suffix,
1355 p_where_clause => l_where_clause,
1356 p_mv => l_mv,
1357 p_join_tbl => l_join_tbl,
1358 p_mv_level_flag => l_aggregation_level_flag,
1359 p_trend => 'N',
1360 p_func_area => 'OPI',
1361 p_version => '7.0',
1362 p_role => '',
1363 p_mv_set => 'CCAD',
1364 p_mv_flag_type => 'CCA_LEVEL');
1365
1366
1367 -- Add measure columns that need to be aggregated
1368 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1369 p_col_name => 'system_inventory_qty' ,
1370 p_alias_name => 'system_qty',
1371 p_grand_total => 'N',
1372 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1373 p_to_date_type => 'XTD');
1374
1375 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1376 p_col_name => 'positive_adjustment_qty' ,
1377 p_alias_name => 'positive_adj_qty',
1378 p_grand_total => 'N',
1379 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1380 p_to_date_type => 'XTD');
1381
1382 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1383 p_col_name => 'negative_adjustment_qty' ,
1384 p_alias_name => 'negative_adj_qty',
1385 p_grand_total => 'N',
1386 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1387 p_to_date_type => 'XTD');
1388
1389 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1390 p_col_name => 'number_of_total_entries',
1391 p_alias_name => 'tot_entries',
1392 p_grand_total => 'Y',
1393 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1394 p_to_date_type => 'XTD');
1395
1396 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1397 p_col_name => 'system_inventory_val_' || l_cur_suffix,
1398 p_alias_name => 'system_val',
1399 p_grand_total => 'Y',
1400 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1401 p_to_date_type => 'XTD');
1402
1403 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1404 p_col_name => 'positive_adjustment_val_' || l_cur_suffix,
1405 p_alias_name => 'positive_adj_val',
1406 p_grand_total => 'Y',
1407 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1408 p_to_date_type => 'XTD');
1409
1410 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1411 p_col_name => 'negative_adjustment_val_' || l_cur_suffix,
1412 p_alias_name => 'negative_adj_val',
1413 p_grand_total => 'Y',
1414 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1415 p_to_date_type => 'XTD');
1416
1417 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1418 p_col_name => 'gross_adjustment_val_' || l_cur_suffix,
1419 p_alias_name => 'gross_adj_val',
1420 p_grand_total => 'Y',
1421 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1422 p_to_date_type => 'XTD');
1423
1424 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1425 p_col_name => 'net_adjustment_val_' || l_cur_suffix,
1426 p_alias_name => 'net_adj_val',
1427 p_grand_total => 'Y',
1431 -- construct the query
1428 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
1429 p_to_date_type => 'XTD');
1430
1432 l_query := get_adj_dtl_sel_clause (l_view_by, l_join_tbl)
1433 || ' from
1434 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
1435 p_where_clause => l_where_clause,
1436 p_join_tables => l_join_tbl,
1437 p_use_windowing => 'Y',
1438 p_col_name => l_col_tbl,
1439 p_use_grpid => 'N',
1440 p_paren_count => 3,
1441 p_filter_where => NULL,
1442 p_generate_viewby => 'Y',
1443 p_in_join_tables => NULL);
1444
1445 -- prepare output for bind variables
1446 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1447 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1448
1449 -- set the basic bind variables for the status SQL
1450 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
1451
1452 -- Passing OPI_AGGREGATION_LEVEL_FLAG to PMV
1453 l_custom_rec.attribute_name := ':OPI_CCA_LEVEL_FLAG';
1454 l_custom_rec.attribute_value := l_aggregation_level_flag;
1455 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1456 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1457 x_custom_output.extend;
1458 x_custom_output(x_custom_output.count) := l_custom_rec;
1459
1460
1461 x_custom_sql := l_query;
1462 END get_adj_dtl_sql;
1463
1464 /*--------------------------------------------------
1465 Function: get_adj_dtl_sel_clause
1466 Description: builds the outer select clause for
1467 Cycle Count Adjustment Summary Report
1468 ---------------------------------------------------*/
1469 FUNCTION get_adj_dtl_sel_clause(p_view_by_dim IN VARCHAR2,
1470 p_join_tbl IN
1471 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
1472 RETURN VARCHAR2
1473 IS
1474
1475 l_sel_clause VARCHAR2(7500);
1476 l_view_by_col_name VARCHAR2(60);
1477 l_item VARCHAR2(30);
1478 l_uom VARCHAR2(30);
1479 l_view_by_fact_col VARCHAR2(400);
1480
1481 BEGIN
1482
1483 -- Column to get view by column name
1484 l_view_by_col_name := opi_dbi_rpt_util_pkg.get_view_by_col_name
1485 (p_view_by_dim);
1486
1487 -- Item Description for item view by
1488 get_cc_item_columns (p_view_by_dim, l_item, 'ITEM');
1489 get_cc_item_columns (p_view_by_dim, l_uom, 'UOM');
1490
1491 -- fact column view by's
1492 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
1493 (p_join_tbl);
1494
1495 -- Outer select clause
1496 l_sel_clause :=
1497 'SELECT
1498 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
1499 || l_view_by_col_name || ' OPI_ATTRIBUTE1,
1500 ' || l_item || ' OPI_ATTRIBUTE2,
1501 ' || l_uom || ' OPI_ATTRIBUTE3,
1502 ' || 'oset.OPI_MEASURE1,
1503 ' || 'oset.OPI_MEASURE3,
1504 ' || 'oset.OPI_MEASURE4,
1505 ' || 'oset.OPI_MEASURE6,
1506 ' || 'oset.OPI_MEASURE7,
1507 ' || 'oset.OPI_MEASURE9,
1508 ' || 'oset.OPI_MEASURE10,
1509 ' || 'oset.OPI_MEASURE12,
1510 ' || 'oset.OPI_MEASURE13,
1511 ' || 'oset.OPI_MEASURE14,
1512 ' || 'oset.OPI_MEASURE15,
1513 ' || 'oset.OPI_MEASURE16,
1514 ' || 'oset.OPI_MEASURE17,
1515 ' || 'oset.OPI_MEASURE18,
1516 ' || 'oset.OPI_MEASURE19
1517 ' || 'FROM
1518 ' || '(SELECT (rank () over
1519 ' || ' (&ORDER_BY_CLAUSE nulls last,
1520 ' || l_view_by_fact_col || ')) - 1 rnk,
1521 ' || l_view_by_fact_col || ',
1522 ' || 'OPI_MEASURE1,
1523 ' || 'OPI_MEASURE3,
1524 ' || 'OPI_MEASURE4,
1525 ' || 'OPI_MEASURE6,
1526 ' || 'OPI_MEASURE7,
1527 ' || 'OPI_MEASURE9,
1528 ' || 'OPI_MEASURE10,
1529 ' || 'OPI_MEASURE12,
1530 ' || 'OPI_MEASURE13,
1531 ' || 'OPI_MEASURE14,
1532 ' || 'OPI_MEASURE15,
1533 ' || 'OPI_MEASURE16,
1534 ' || 'OPI_MEASURE17,
1535 ' || 'OPI_MEASURE18,
1536 ' || 'OPI_MEASURE19
1537 ' || 'FROM
1538 ' || '(SELECT
1539 ' || l_view_by_fact_col || ',
1540 ' || opi_dbi_rpt_util_pkg.nvl_str (
1541 p_str => 'c_tot_entries',
1542 p_default_val => 0)
1543 || ' OPI_MEASURE1,
1544 ' || opi_dbi_rpt_util_pkg.nvl_str (
1545 p_str => 'c_system_qty',
1546 p_default_val => 0)
1547 || ' OPI_MEASURE3,
1548 ' || opi_dbi_rpt_util_pkg.nvl_str (
1549 p_str => 'c_system_val',
1550 p_default_val => 0)
1551 || ' OPI_MEASURE4,
1552 ' || opi_dbi_rpt_util_pkg.nvl_str (
1553 p_str => 'c_positive_adj_qty',
1557 p_str => 'c_positive_adj_val',
1554 p_default_val => 0)
1555 || ' OPI_MEASURE6,
1556 ' || opi_dbi_rpt_util_pkg.nvl_str (
1558 p_default_val => 0)
1559 || ' OPI_MEASURE7,
1560 ' || opi_dbi_rpt_util_pkg.nvl_str (
1561 p_str => 'c_negative_adj_qty',
1562 p_default_val => 0)
1563 || ' OPI_MEASURE9,
1564 ' || opi_dbi_rpt_util_pkg.nvl_str (
1565 p_str => 'c_negative_adj_val',
1566 p_default_val => 0)
1567 || ' OPI_MEASURE10,
1568 ' || opi_dbi_rpt_util_pkg.nvl_str (
1569 p_str => 'c_gross_adj_val',
1570 p_default_val => 0)
1571 || ' OPI_MEASURE12,
1572 ' || opi_dbi_rpt_util_pkg.nvl_str (
1573 p_str => 'c_net_adj_val',
1574 p_default_val => 0)
1575 || ' OPI_MEASURE13,
1576 ' || opi_dbi_rpt_util_pkg.nvl_str (
1577 p_str => 'c_tot_entries_total',
1578 p_default_val => 0)
1579 || ' OPI_MEASURE14,
1580 ' || opi_dbi_rpt_util_pkg.nvl_str (
1581 p_str => 'c_system_val_total',
1582 p_default_val => 0)
1583 || ' OPI_MEASURE15,
1584 ' || opi_dbi_rpt_util_pkg.nvl_str (
1585 p_str => 'c_positive_adj_val_total',
1586 p_default_val => 0)
1587 || ' OPI_MEASURE16,
1588 ' || opi_dbi_rpt_util_pkg.nvl_str (
1589 p_str => 'c_negative_adj_val_total',
1590 p_default_val => 0)
1591 || ' OPI_MEASURE17,
1592 ' || opi_dbi_rpt_util_pkg.nvl_str (
1593 p_str => 'c_gross_adj_val_total',
1594 p_default_val => 0)
1595 || ' OPI_MEASURE18,
1596 ' || opi_dbi_rpt_util_pkg.nvl_str (
1597 p_str => 'c_net_adj_val_total',
1598 p_default_val => 0)
1599 || ' OPI_MEASURE19';
1600
1601 RETURN l_sel_clause;
1602
1603 END get_adj_dtl_sel_clause;
1604
1605
1606 END opi_dbi_cc_rpt_pkg;