[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_INV_CURR_RPT_PKG
Source
1 PACKAGE BODY OPI_DBI_INV_CURR_RPT_PKG AS
2 /*$Header: OPIDRIVDETB.pls 120.9 2006/01/06 01:31:34 srayadur noship $ */
3
4
5 /****************************************
6 * Select clause functions
7 ****************************************/
8
9 /* Current Inventory Status */
10 -- Outer select clause
11 FUNCTION get_curr_inv_stat_sel_clause (p_view_by_dim IN VARCHAR2,
12 p_join_tbl IN
13 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
14 RETURN VARCHAR2;
15
16 /* Current Inventory Expiration Status */
17 -- Outer select clause
18 FUNCTION get_curr_inv_exp_sel_clause (p_view_by_dim IN VARCHAR2,
19 p_join_tbl IN
20 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
21 RETURN VARCHAR2;
22
23
24 /* Inventory Days Onhand */
25 -- Outer select clause
26 FUNCTION get_inv_doh_sel_clause (p_view_by_dim IN VARCHAR2,
27 p_join_tbl IN
28 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
29 RETURN VARCHAR2;
30
31 /****************************************
32 * Helper functions
33 ****************************************/
34
35 /* Current Inventory Status */
36 -- Set up the in_join table for viewby grade.
37 PROCEDURE get_curr_inv_stat_mln
38 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
39 p_query IN OUT NOCOPY VARCHAR2);
40
41 -- Set up the in_join table for viewby item cat.
42 PROCEDURE get_curr_inv_stat_eni
43 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
44 p_query IN OUT NOCOPY VARCHAR2);
45
46 -- Get fixed view by columns for Current Inventory Status
47 -- inner SQL.
48 PROCEDURE get_curr_inv_viewby_cols
49 (p_view_by IN VARCHAR2,
50 p_view_by_col IN VARCHAR2,
51 p_query IN OUT NOCOPY VARCHAR2);
52
53 -- Parameter condition where clauses
54 PROCEDURE get_curr_inv_stat_param_cond
55 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
56 p_query IN OUT NOCOPY VARCHAR2);
57
58 -- Security Where clause
59 PROCEDURE get_curr_inv_sec_where_clause
60 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
61 p_query IN OUT NOCOPY VARCHAR2);
62
63 -- Concatenated, formatted viewby column
64 FUNCTION get_curr_inv_viewby_format (p_viewby IN VARCHAR2)
65 RETURN VARCHAR2;
66
67 /* Current inventory Expiration Status */
68 -- Filter function for when measures are all 0/NULL
69 FUNCTION get_curr_inv_exp_filter_clause (p_view_by_dim IN VARCHAR2)
70 RETURN VARCHAR2;
71
72 /* Inventory Days Onhand */
73 -- Filter function for when measures are all 0/NULL
74 FUNCTION get_inv_doh_filter_clause (p_view_by_dim IN VARCHAR2)
75 RETURN VARCHAR2;
76
77
78 /****************************************
79 * Current Inventory Expiration Status
80 ****************************************/
81
82 /* get_curr_inv_exp_stat_sql
83
84 Description
85 Current Inventory Expiration Status (Table) report query function.
86
87 Inputs
88 1. p_params - table of parameters with which report was run.
89
90 Outputs
91 1. x_custom_sql - sql report query.
92 2. x_custom_output - table of values for bind variables in
93 sql report query.
94
95 History
96
97 Date Author Action
98 07/11/05 Dinkar Gupta Wrote Function
99 */
100 PROCEDURE get_curr_inv_exp_stat_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
101 x_custom_sql OUT NOCOPY VARCHAR2,
102 x_custom_output OUT NOCOPY
103 BIS_QUERY_ATTRIBUTES_TBL)
104 IS
105 -- {
106 l_query VARCHAR2 (32767);
107
108 l_view_by VARCHAR2(120);
109 l_view_by_col VARCHAR2 (120);
110 l_xtd VARCHAR2(10);
111 l_comparison_type VARCHAR2(1);
112
113 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
114 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
115
116 l_where_clause VARCHAR2 (2000);
117 l_mv VARCHAR2 (30);
118
119 l_aggr_level_flag VARCHAR2(1);
120
121 l_custom_rec BIS_QUERY_ATTRIBUTES;
122
123 l_cur_suffix VARCHAR2 (5);
124
125 l_filter_clause VARCHAR2 (20000);
126 -- }
127 BEGIN
128 -- {
129 -- initialization block
130 x_custom_sql := NULL;
131 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL ();
132 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
133 l_query := NULL;
134 l_view_by := NULL;
135 l_view_by_col := NULL;
136 l_xtd := NULL;
137 l_comparison_type := NULL;
138 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
139 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
140 l_where_clause := NULL;
141 l_mv := NULL;
142 l_aggr_level_flag := NULL;
143 l_cur_suffix := NULL;
144 l_filter_clause := NULL;
145
146 -- Process the parameters using the template package.
147 opi_dbi_rpt_util_pkg.process_parameters (
148 p_param => p_param,
149 p_view_by => l_view_by,
150 p_view_by_col_name => l_view_by_col,
151 p_comparison_type => l_comparison_type,
152 p_xtd => l_xtd,
153 p_cur_suffix => l_cur_suffix,
154 p_where_clause => l_where_clause,
155 p_mv => l_mv,
156 p_join_tbl => l_join_tbl,
157 p_mv_level_flag => l_aggr_level_flag,
158 p_trend => 'N',
159 p_func_area => 'OPI',
160 p_version => '12.0',
161 p_role => '',
162 p_mv_set => 'CURR_INV_EXP',
163 p_mv_flag_type => 'CURR_INV_EXP_LEVEL');
164
165 -- Add the appropriate columns that need to be aggregated.
166 -- On-Hand Value. Need totals but no priors.
167 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
168 p_col_name => 'onhand_val_' || l_cur_suffix,
169 p_alias_name => 'onhand_val',
170 p_grand_total => 'Y',
171 p_prior_code =>
172 poa_dbi_util_pkg.NO_PRIORS,
173 p_to_date_type => 'NA');
174
175 -- Expired Value. Need totals but no priors.
176 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
177 p_col_name => 'expired_val_' || l_cur_suffix,
178 p_alias_name => 'expired_val',
179 p_grand_total => 'Y',
180 p_prior_code =>
181 poa_dbi_util_pkg.NO_PRIORS,
182 p_to_date_type => 'NA');
183
184 -- Get the quantities also.
185 -- On-Hand Quantities. No need for totals and priors.
186 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
187 p_col_name => 'onhand_qty',
188 p_alias_name => 'onhand_qty',
189 p_grand_total => 'N',
190 p_prior_code =>
191 poa_dbi_util_pkg.NO_PRIORS,
192 p_to_date_type => 'NA');
193
194 -- Expired Quantities. No need for totals and priors.
195 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
196 p_col_name => 'expired_qty',
197 p_alias_name => 'expired_qty',
198 p_grand_total => 'N',
199 p_prior_code =>
200 poa_dbi_util_pkg.NO_PRIORS,
201 p_to_date_type => 'NA');
202
203 -- Get the filtering clause for 0/NULL rows
204 l_filter_clause := get_curr_inv_exp_filter_clause
205 (p_view_by_dim => l_view_by);
206
207 -- Since there is no join to the time dimension, need a dummy condition
208 -- at the start of the where clause, since it starts with an AND.
209 l_where_clause := '1 = 1 ' || l_where_clause;
210
211 -- The status query provided by POA
212 l_query := poa_dbi_template_pkg.status_sql (
213 p_fact_name => l_mv,
214 p_where_clause => l_where_clause,
215 p_join_tables => l_join_tbl,
216 p_use_windowing => 'Y',
217 p_col_name => l_col_tbl,
218 p_use_grpid => 'N',
219 p_paren_count => 3,
220 p_filter_where => l_filter_clause,
221 p_generate_viewby => 'Y',
222 p_in_join_tables => NULL);
223
224 -- Final report query with select clause etc.
225 l_query := get_curr_inv_exp_sel_clause
226 (p_view_by_dim => l_view_by,
227 p_join_tbl => l_join_tbl) || '
228 ' || ' from ' || '
229 ' || l_query;
230
231 -- Subinventory aggregation level flag.
232 l_custom_rec.attribute_name := ':OPI_CURR_INV_EXP_AGG_FLAG';
233 l_custom_rec.attribute_value := l_aggr_level_flag;
234 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
235 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
236 x_custom_output.extend;
237 x_custom_output(x_custom_output.count) := l_custom_rec;
238
239 x_custom_sql := l_query;
240
241 return;
242 -- }
243 END get_curr_inv_exp_stat_sql;
244
245 /* get_curr_inv_exp_filter_clause
246
247 Description
248 Filter clause for the current inventory expiration report
249 in case all relevant measures are 0/NULL.
250
251 Input
252 Viewby dimension, since qty is a criterion for filtering on
253 viewby = item.
254
255 Decode 0's as NULL, because we don't want to show anything for
256 items that have 0 onhand.
257
258 Output
259 Filter clause
260
261 History
262
263 Date Author Action
264 07/13/05 Dinkar Gupta Wrote Function
265
266 */
267
268 FUNCTION get_curr_inv_exp_filter_clause (p_view_by_dim IN VARCHAR2)
269 RETURN VARCHAR2
270 IS
271 -- {
272 l_filter_clause VARCHAR2 (20000);
273
274 -- table column for filter clause
275 l_col_rec POA_DBI_UTIL_PKG.POA_DBI_FLEX_FILTER_REC;
276 l_col_tbl POA_DBI_UTIL_PKG.POA_DBI_FLEX_FILTER_TBL;
277 -- }
278 BEGIN
279 -- {
280 -- initialization block
281 l_filter_clause := NULL;
282 l_col_rec := NULL;
283 l_col_tbl := POA_DBI_UTIL_PKG.POA_DBI_FLEX_FILTER_TBL ();
284
285 -- Basic columns to filter out:
286 -- OPI_MEASURE3 Expired Value
287 -- OPI_MEASURE6 On-Hand Value
288 l_col_rec.measure_name := 'OPI_MEASURE3';
289 l_col_rec.modifier := 'DECODE_0';
290 l_col_tbl.extend;
291 l_col_tbl(l_col_tbl.count) := l_col_rec;
292
293 l_col_rec.measure_name := 'OPI_MEASURE6';
294 l_col_rec.modifier := 'DECODE_0';
295 l_col_tbl.extend;
296 l_col_tbl(l_col_tbl.count) := l_col_rec;
297
298 -- Item viewby special case
299 -- For viewby item, filter out additionally on:
300 -- OPI_MEASURE2 Expired Quantity
301 -- OPI_MEASURE5 On-Hand Quantity
302 IF (p_view_by_dim = C_VIEWBY_ITEM) THEN
303
304 l_col_rec.measure_name := 'OPI_MEASURE2';
305 l_col_rec.modifier := 'DECODE_0';
306 l_col_tbl.extend;
307 l_col_tbl(l_col_tbl.count) := l_col_rec;
308
309 l_col_rec.measure_name := 'OPI_MEASURE5';
310 l_col_rec.modifier := 'DECODE_0';
311 l_col_tbl.extend;
312 l_col_tbl(l_col_tbl.count) := l_col_rec;
313
314 END IF;
315
316 -- generate the filter clause
317 l_filter_clause := poa_dbi_util_pkg.get_filter_where (p_cols => l_col_tbl);
318
319 return l_filter_clause;
320 -- }
321 END get_curr_inv_exp_filter_clause;
322
323
324 /* get_curr_inv_exp_sel_clause
325
326 Description
327 Returns the select clause for the Current Inventory Expiration
328 Status (Table) report query.
329
330 Input
331
332 Outputs
333 1. l_sel_clause - Select clause of the report query
334
335 History
336
337 Date Author Action
338 07/13/05 Dinkar Gupta Wrote Function
339 */
340 FUNCTION get_curr_inv_exp_sel_clause (p_view_by_dim IN VARCHAR2,
341 p_join_tbl IN
342 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
343 RETURN VARCHAR2
344 IS
345 -- {
346 l_sel_clause VARCHAR2 (32767);
347 l_description varchar2 (120);
348 l_uom varchar2 (30);
349 l_view_by_fact_col VARCHAR2(400);
350 -- }
351 BEGIN
352 -- {
353 -- initialization block
354 l_sel_clause := NULL;
355 l_description := NULL;
356 l_uom := NULL;
357 l_view_by_fact_col := NULL;
358
359 -- fact column view by's
360 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
361 (p_join_tbl => p_join_tbl);
362
363 -- Description for item view by
364 opi_dbi_rpt_util_pkg.get_viewby_item_columns (
365 p_dim_name => p_view_by_dim,
366 p_description => l_description,
367 p_uom => l_uom);
368
369
370 l_sel_clause :=
371 'SELECT
372 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
373 || l_description || ' OPI_ATTRIBUTE1,
374 ' || l_uom || ' OPI_ATTRIBUTE2,
375 ' || 'oset.OPI_MEASURE2,
376 ' || 'oset.OPI_MEASURE3,
377 ' || 'oset.OPI_MEASURE5,
378 ' || 'oset.OPI_MEASURE6,
379 ' || 'oset.OPI_MEASURE7,
380 ' || 'oset.OPI_MEASURE8,
381 ' || 'oset.OPI_MEASURE9,
382 ' || 'oset.OPI_MEASURE10
383 ' || 'FROM
384 ' || '(SELECT (rank () over
385 ' || ' (&ORDER_BY_CLAUSE nulls last,
386 ' || l_view_by_fact_col || ')) - 1 rnk,
387 ' || l_view_by_fact_col || ',
388 ' || 'OPI_MEASURE2,
389 ' || 'OPI_MEASURE3,
390 ' || 'OPI_MEASURE5,
391 ' || 'OPI_MEASURE6,
392 ' || 'OPI_MEASURE7,
393 ' || 'OPI_MEASURE8,
394 ' || 'OPI_MEASURE9,
395 ' || 'OPI_MEASURE10
396 ' || 'FROM
397 ' || '(SELECT
398 ' || l_view_by_fact_col || ',
399 ' || opi_dbi_rpt_util_pkg.raw_str
400 (p_str => 'c_expired_qty')
401 || ' OPI_MEASURE2,
402 ' || opi_dbi_rpt_util_pkg.nvl_str
403 (p_str => 'c_expired_val')
404 || ' OPI_MEASURE3,
405 ' || opi_dbi_rpt_util_pkg.raw_str
406 (p_str => 'c_onhand_qty')
407 || ' OPI_MEASURE5,
408 ' || opi_dbi_rpt_util_pkg.nvl_str
409 (p_str => 'c_onhand_val')
410 || ' OPI_MEASURE6,
411 ' || opi_dbi_rpt_util_pkg.percent_str
412 (p_numerator => 'c_expired_val',
413 p_denominator => 'c_onhand_val',
414 p_measure_name => 'OPI_MEASURE7') || ',
415 ' || opi_dbi_rpt_util_pkg.nvl_str
416 (p_str => 'c_expired_val_total')
417 || ' OPI_MEASURE8,
418 ' || opi_dbi_rpt_util_pkg.nvl_str
419 (p_str => 'c_onhand_val_total')
420 || ' OPI_MEASURE9,
421 ' || opi_dbi_rpt_util_pkg.percent_str
422 (p_numerator => 'c_expired_val_total',
423 p_denominator => 'c_onhand_val_total',
424 p_measure_name => 'OPI_MEASURE10');
425
426 return l_sel_clause;
427 -- }
428 END get_curr_inv_exp_sel_clause;
429
430
431
432 /****************************************
433 * Inventory Days On-Hand
434 ****************************************/
435
436 /* get_inv_days_onh_sql
437
438 Description
439 Inventory Days On-Hand (Table) report query function.
440 3 part union-all query.
441
442 Inputs
443 1. p_params - table of parameters with which report was run.
444
445 Outputs
446 1. x_custom_sql - sql report query.
447 2. x_custom_output - table of values for bind variables in
448 sql report query.
449
450 History
451
452 Date Author Action
453 07/11/05 Dinkar Gupta Wrote Function
454 */
455 PROCEDURE get_inv_days_onh_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
456 x_custom_sql OUT NOCOPY VARCHAR2,
457 x_custom_output OUT NOCOPY
458 BIS_QUERY_ATTRIBUTES_TBL)
459 IS
460 -- {
461 l_query VARCHAR2 (32767);
462
463 l_view_by VARCHAR2(120);
464 l_view_by_col VARCHAR2 (120);
465
466
467 l_xtd VARCHAR2(10);
468
469 l_comparison_type VARCHAR2(1);
470 l_cur_suffix VARCHAR2 (5);
471
472 l_onh_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
473 l_prod_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
474 l_ship_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
475
476 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
477
478 l_onh_where_clause VARCHAR2 (2000);
479 l_prod_where_clause VARCHAR2 (2000);
480 l_ship_where_clause VARCHAR2 (2000);
481
482 l_onh_mv VARCHAR2 (30);
483 l_prod_mv VARCHAR2 (30);
484 l_ship_mv VARCHAR2 (30);
485
486 l_onh_aggr_level_flag VARCHAR2(1);
487 l_prod_aggr_level_flag VARCHAR2(1);
488 l_ship_aggr_level_flag VARCHAR2(1);
489
490 l_custom_rec BIS_QUERY_ATTRIBUTES;
491
492 l_filter_clause VARCHAR2 (20000);
493
494 l_unionall_tbl poa_dbi_util_pkg.POA_DBI_MV_TBL;
495
496 l_per_length NUMBER;
497 -- }
498 BEGIN
499 -- {
500 -- initialization block
501 x_custom_sql := NULL;
502 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL ();
503 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
504 l_query := NULL;
505 l_view_by := NULL;
506 l_view_by_col := NULL;
507 l_xtd := NULL;
508 l_comparison_type := NULL;
509 l_onh_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
510 l_prod_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
511 l_ship_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
512 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
513 l_onh_where_clause := NULL;
514 l_prod_where_clause := NULL;
515 l_ship_where_clause := NULL;
516 l_onh_mv := NULL;
517 l_prod_mv := NULL;
518 l_ship_mv := NULL;
519 l_onh_aggr_level_flag := NULL;
520 l_prod_aggr_level_flag := NULL;
521 l_ship_aggr_level_flag := NULL;
522 l_cur_suffix := NULL;
523 l_filter_clause := NULL;
524 l_unionall_tbl := poa_dbi_util_pkg.POA_DBI_MV_TBL ();
525 l_per_length := 0;
526
527 -- Process the parameters using the template package for the onhand
528 -- branch.
529 opi_dbi_rpt_util_pkg.process_parameters (
530 p_param => p_param,
531 p_view_by => l_view_by,
532 p_view_by_col_name => l_view_by_col,
533 p_comparison_type => l_comparison_type,
534 p_xtd => l_xtd,
535 p_cur_suffix => l_cur_suffix,
536 p_where_clause => l_onh_where_clause,
537 p_mv => l_onh_mv,
538 p_join_tbl => l_join_tbl,
539 p_mv_level_flag => l_onh_aggr_level_flag,
540 p_trend => 'N',
541 p_func_area => 'OPI',
542 p_version => '12.0',
543 p_role => '',
544 p_mv_set => 'INV_VAL_UOM',
548 -- production consumption branch.
545 p_mv_flag_type => 'INV_VAL_UOM_LEVEL');
546
547 -- Process the parameters using the template package for the
549 opi_dbi_rpt_util_pkg.process_parameters (
550 p_param => p_param,
551 p_view_by => l_view_by,
552 p_view_by_col_name => l_view_by_col,
553 p_comparison_type => l_comparison_type,
554 p_xtd => l_xtd,
555 p_cur_suffix => l_cur_suffix,
556 p_where_clause => l_prod_where_clause,
557 p_mv => l_prod_mv,
558 p_join_tbl => l_join_tbl,
559 p_mv_level_flag => l_prod_aggr_level_flag,
560 p_trend => 'N',
561 p_func_area => 'OPI',
562 p_version => '12.0',
563 p_role => '',
564 p_mv_set => 'PROD_CONS',
565 p_mv_flag_type => 'PROD_CONS_LEVEL');
566
567 -- Process the parameters using the template package for the
568 -- shipment consumption branch.
569 opi_dbi_rpt_util_pkg.process_parameters (
570 p_param => p_param,
571 p_view_by => l_view_by,
572 p_view_by_col_name => l_view_by_col,
573 p_comparison_type => l_comparison_type,
574 p_xtd => l_xtd,
575 p_cur_suffix => l_cur_suffix,
576 p_where_clause => l_ship_where_clause,
577 p_mv => l_ship_mv,
578 p_join_tbl => l_join_tbl,
579 p_mv_level_flag => l_ship_aggr_level_flag,
580 p_trend => 'N',
581 p_func_area => 'OPI',
582 p_version => '12.0',
583 p_role => '',
584 p_mv_set => 'COGS',
585 p_mv_flag_type => 'COGS_LEVEL');
586
587
588 -- Add the appropriate columns for the onhand branch.
589 -- Onhand Quantity is needed for viewby item.
590 poa_dbi_util_pkg.add_column (p_col_tbl => l_onh_col_tbl,
591 p_col_name => 'onhand_qty',
592 p_alias_name => 'onhand_qty',
593 p_grand_total => 'N',
594 p_prior_code =>
595 poa_dbi_util_pkg.NO_PRIORS,
596 p_to_date_type => 'RLX');
597
598 -- On-Hand Value. No need for totals.
599 poa_dbi_util_pkg.add_column (p_col_tbl => l_onh_col_tbl,
600 p_col_name => 'onhand_value_' || l_cur_suffix,
601 p_alias_name => 'onhand_val',
602 p_grand_total => 'Y',
603 p_prior_code =>
604 poa_dbi_util_pkg.BOTH_PRIORS,
605 p_to_date_type => 'RLX');
606
607 -- Production Consumption. No need for totals.
608 poa_dbi_util_pkg.add_column (p_col_tbl => l_prod_col_tbl,
609 p_col_name =>
610 'prod_usage_val_' || l_cur_suffix,
611 p_alias_name => 'prod_usage_val',
612 p_grand_total => 'Y',
613 p_prior_code =>
614 poa_dbi_util_pkg.BOTH_PRIORS,
615 p_to_date_type => 'RLX');
616
617 -- Shipment Consumption. No need for totals.
618 poa_dbi_util_pkg.add_column (p_col_tbl => l_ship_col_tbl,
619 p_col_name =>
620 'cogs_val_' || l_cur_suffix,
621 p_alias_name => 'cogs_val',
622 p_grand_total => 'Y',
623 p_prior_code =>
624 poa_dbi_util_pkg.BOTH_PRIORS,
625 p_to_date_type => 'RLX');
626
627 -- Get the filtering clause
628 l_filter_clause := get_inv_doh_filter_clause (p_view_by_dim => l_view_by);
629
630 -- Merge all data into the giant UNION ALL query data structure
631 l_unionall_tbl.extend;
632 l_unionall_tbl(l_unionall_tbl.count).mv_name := l_onh_mv;
633 l_unionall_tbl(l_unionall_tbl.count).mv_col := l_onh_col_tbl;
634 l_unionall_tbl(l_unionall_tbl.count).mv_where := l_onh_where_clause;
635 l_unionall_tbl(l_unionall_tbl.count).in_join_tbls := NULL;
636 l_unionall_tbl(l_unionall_tbl.count).use_grp_id := 'N';
637
638 l_unionall_tbl.extend;
639 l_unionall_tbl(l_unionall_tbl.count).mv_name := l_prod_mv;
640 l_unionall_tbl(l_unionall_tbl.count).mv_col := l_prod_col_tbl;
641 l_unionall_tbl(l_unionall_tbl.count).mv_where := l_prod_where_clause;
642 l_unionall_tbl(l_unionall_tbl.count).in_join_tbls := NULL;
643 l_unionall_tbl(l_unionall_tbl.count).use_grp_id := 'N';
644
645 l_unionall_tbl.extend;
646 l_unionall_tbl(l_unionall_tbl.count).mv_name := l_ship_mv;
647 l_unionall_tbl(l_unionall_tbl.count).mv_col := l_ship_col_tbl;
648 l_unionall_tbl(l_unionall_tbl.count).mv_where := l_ship_where_clause;
649 l_unionall_tbl(l_unionall_tbl.count).in_join_tbls := NULL;
650 l_unionall_tbl(l_unionall_tbl.count).use_grp_id := 'N';
651
652 -- Figure out the period length
653 CASE
654 -- {
655 WHEN l_xtd = 'RLW' THEN
656 l_per_length := 7;
657 WHEN l_xtd = 'RLM' THEN
658 l_per_length := 30;
659 WHEN l_xtd = 'RLQ' THEN
660 l_per_length := 90;
661 WHEN l_xtd = 'RLY' THEN
662 l_per_length := 365;
663 -- }
664 END CASE;
665
669 p_join_tables => l_join_tbl,
666 -- The union all query provided by POA
667 l_query := poa_dbi_template_pkg.union_all_status_sql (
668 p_mv => l_unionall_tbl,
670 p_use_windowing => 'Y',
671 p_paren_count => 5,
672 p_filter_where => l_filter_clause,
673 p_generate_viewby => 'Y');
674
675 -- Final report query with select clause etc.
676 l_query := get_inv_doh_sel_clause
677 (p_view_by_dim => l_view_by,
678 p_join_tbl => l_join_tbl) || '
679 ' || ' from (' || '
680 ' || l_query;
681
682 -- Make the nested pattern ITD since onhand quantity (and weight/
683 -- volume) are reported as a balance..
684 l_query := opi_dbi_rpt_util_pkg.replace_n
685 (p_orig_str => l_query,
686 p_match_str => '&RLX_NESTED_PATTERN',
687 p_replace_str => C_ROLLING_ITD_PATTERN,
688 p_start_pos => 1,
689 p_num_times => 2);
690
691 -- Get bind variables for the rolling period reports.
692 poa_dbi_util_pkg.get_custom_status_binds
693 (x_custom_output => x_custom_output);
694 poa_dbi_util_pkg.get_custom_rolling_binds
695 (p_custom_output => x_custom_output,
696 p_xtd => l_xtd);
697
698 -- Aggregation level flags
699 l_custom_rec.attribute_name := ':OPI_AGGREGATION_LEVEL_FLAG';
700 l_custom_rec.attribute_value := l_onh_aggr_level_flag;
701 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
702 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
703 x_custom_output.extend;
704 x_custom_output(x_custom_output.count) := l_custom_rec;
705
706 l_custom_rec.attribute_name := ':OPI_PROD_CONS_AGG_FLAG';
707 l_custom_rec.attribute_value := l_prod_aggr_level_flag;
708 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
709 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
710 x_custom_output.extend;
711 x_custom_output(x_custom_output.count) := l_custom_rec;
712
713 l_custom_rec.attribute_name := ':OPI_COGS_SHIP_AGG_FLAG';
714 l_custom_rec.attribute_value := l_ship_aggr_level_flag;
715 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
716 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
717 x_custom_output.extend;
718 x_custom_output(x_custom_output.count) := l_custom_rec;
719
720 l_custom_rec.attribute_name := ':OPI_INV_DOH_PER_LEN';
721 l_custom_rec.attribute_value := l_per_length;
722 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
723 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
724 x_custom_output.extend;
725 x_custom_output(x_custom_output.count) := l_custom_rec;
726
727 x_custom_sql := l_query;
728
729 return;
730 -- }
731 END get_inv_days_onh_sql;
732
733 /* get_inv_doh_filter_clause
734
735 Description
736 Filter clause for the inventory days onhand report
737 in case all relevant measures are 0/NULL.
738
739 Input
740 Viewby dimension, since qty is a criterion for filtering on
741 viewby = item.
742
743 Decode 0's as NULL, because we don't want to show anything for
744 items that have 0 onhand.
745
746 Output
747 Filter clause
748
749 History
750
751 Date Author Action
752 07/13/05 Dinkar Gupta Wrote Function
753
754 */
755
756 FUNCTION get_inv_doh_filter_clause (p_view_by_dim IN VARCHAR2)
757 RETURN VARCHAR2
758 IS
759 -- {
760 l_filter_clause VARCHAR2 (20000);
761
762 -- table column for filter clause
763 l_col_rec POA_DBI_UTIL_PKG.POA_DBI_FLEX_FILTER_REC;
764 l_col_tbl POA_DBI_UTIL_PKG.POA_DBI_FLEX_FILTER_TBL;
765 -- }
766 BEGIN
767 -- {
768 -- initialization block
769 l_filter_clause := NULL;
770 l_col_rec := NULL;
771 l_col_tbl := POA_DBI_UTIL_PKG.POA_DBI_FLEX_FILTER_TBL ();
772
773 -- Basic columns to filter out:
774 -- Cannot use OPI_MEASURE10/OPI_MEASURE11 because they are
775 -- computed in outer clause
776 -- OPI_MEASURE3 - Prior (On-Hand Value)
777 -- OPI_MEASURE4 - On-Hand Value
778 -- OPI_MEASURE6 - Production Consumption Value
779 -- OPI_MEASURE7 - Shipments Consumption Value
780 -- OPI_MEASURE8 - Total Value
781 -- OPI_MEASURE9 - Daily Average
782 -- OPI_MEASURE10- Prior Days Onhand
783 -- OPI_MEASURE11- Days Onhand
784 -- OPI_MEASURE17- Change
785 l_col_rec.measure_name := 'OPI_MEASURE3';
786 l_col_rec.modifier := 'DECODE_0';
787 l_col_tbl.extend;
788 l_col_tbl(l_col_tbl.count) := l_col_rec;
789
790 l_col_rec.measure_name := 'OPI_MEASURE4';
791 l_col_rec.modifier := 'DECODE_0';
792 l_col_tbl.extend;
793 l_col_tbl(l_col_tbl.count) := l_col_rec;
794
795 l_col_rec.measure_name := 'OPI_MEASURE6';
796 l_col_rec.modifier := 'DECODE_0';
797 l_col_tbl.extend;
798 l_col_tbl(l_col_tbl.count) := l_col_rec;
799
800 l_col_rec.measure_name := 'OPI_MEASURE7';
801 l_col_rec.modifier := 'DECODE_0';
802 l_col_tbl.extend;
803 l_col_tbl(l_col_tbl.count) := l_col_rec;
804
805 l_col_rec.measure_name := 'OPI_MEASURE8';
806 l_col_rec.modifier := 'DECODE_0';
807 l_col_tbl.extend;
808 l_col_tbl(l_col_tbl.count) := l_col_rec;
809
810 l_col_rec.measure_name := 'OPI_MEASURE9';
811 l_col_rec.modifier := 'DECODE_0';
812 l_col_tbl.extend;
813 l_col_tbl(l_col_tbl.count) := l_col_rec;
814
815 l_col_rec.measure_name := 'OPI_MEASURE10';
816 l_col_rec.modifier := 'DECODE_0';
817 l_col_tbl.extend;
818 l_col_tbl(l_col_tbl.count) := l_col_rec;
819
820 l_col_rec.measure_name := 'OPI_MEASURE11';
821 l_col_rec.modifier := 'DECODE_0';
822 l_col_tbl.extend;
823 l_col_tbl(l_col_tbl.count) := l_col_rec;
824
825 l_col_rec.measure_name := 'OPI_MEASURE17';
826 l_col_rec.modifier := 'DECODE_0';
827 l_col_tbl.extend;
828 l_col_tbl(l_col_tbl.count) := l_col_rec;
829
830 -- Item viewby special case
831 -- For viewby item, filter out additionally on:
832 -- OPI_MEASURE2 Expired Quantity
833 IF (p_view_by_dim = C_VIEWBY_ITEM) THEN
834
835 l_col_rec.measure_name := 'OPI_MEASURE2';
836 l_col_rec.modifier := 'DECODE_0';
837 l_col_tbl.extend;
838 l_col_tbl(l_col_tbl.count) := l_col_rec;
839
840 END IF;
841
842 -- generate the filter clause
843 l_filter_clause := poa_dbi_util_pkg.get_filter_where (p_cols => l_col_tbl);
844
845 return l_filter_clause;
846 -- }
847 END get_inv_doh_filter_clause;
848
849
850 /* get_inv_doh_sel_clause
851
852 Description
853 Returns the select clause for the Inventory Days Onhand
854 Status (Table) report query.
855
856 Input
857
858 Outputs
859 1. l_sel_clause - Select clause of the report query
860
861 History
862
863 Date Author Action
864 07/13/05 Dinkar Gupta Wrote Function
865 */
866 FUNCTION get_inv_doh_sel_clause (p_view_by_dim IN VARCHAR2,
867 p_join_tbl IN
868 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
869 RETURN VARCHAR2
870 IS
871 -- {
872 l_sel_clause VARCHAR2 (32767);
873 l_description varchar2 (120);
874 l_uom varchar2 (30);
875 l_view_by_fact_col VARCHAR2(400);
876 -- }
877 BEGIN
878 -- {
879 -- initialization block
880 l_sel_clause := NULL;
881 l_description := NULL;
882 l_uom := NULL;
883 l_view_by_fact_col := NULL;
884
885 -- fact column view by's
886 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
887 (p_join_tbl => p_join_tbl);
888
889 -- Description for item view by
890 opi_dbi_rpt_util_pkg.get_viewby_item_columns (
891 p_dim_name => p_view_by_dim,
892 p_description => l_description,
893 p_uom => l_uom);
894
895 l_sel_clause :=
896 'SELECT /* outer query */
897 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
898 || l_description || ' OPI_ATTRIBUTE1,
899 ' || l_uom || ' OPI_ATTRIBUTE2,
900 ' || 'OPI_MEASURE2,
901 ' || 'OPI_MEASURE3,
902 ' || 'OPI_MEASURE4,
903 ' || 'OPI_MEASURE12,
904 ' || 'OPI_MEASURE6,
905 ' || 'OPI_MEASURE13,
906 ' || 'OPI_MEASURE7,
907 ' || 'OPI_MEASURE14,
908 ' || 'OPI_MEASURE8,
909 ' || 'OPI_MEASURE15,
910 ' || 'OPI_MEASURE9,
911 ' || 'OPI_MEASURE16,
912 ' || 'OPI_MEASURE10,
913 ' || 'OPI_MEASURE11,
914 ' || 'OPI_MEASURE17,
915 ' || 'OPI_MEASURE18,
916 ' || 'OPI_MEASURE19
917 ' || 'FROM
918 ' || '(SELECT (rank () over /* rank clause */
919 ' || ' (&ORDER_BY_CLAUSE nulls last,
920 ' || l_view_by_fact_col || ')) - 1 rnk,
921 ' || l_view_by_fact_col || ',
922 ' || 'OPI_MEASURE2,
923 ' || 'OPI_MEASURE3,
924 ' || 'OPI_MEASURE4,
925 ' || 'OPI_MEASURE12,
926 ' || 'OPI_MEASURE6,
927 ' || 'OPI_MEASURE13,
928 ' || 'OPI_MEASURE7,
929 ' || 'OPI_MEASURE14,
930 ' || 'OPI_MEASURE8,
931 ' || 'OPI_MEASURE15,
932 ' || 'OPI_MEASURE9,
933 ' || 'OPI_MEASURE16,
934 ' || 'OPI_MEASURE10,
935 ' || 'OPI_MEASURE11,
936 ' || 'OPI_MEASURE17,
937 ' || 'OPI_MEASURE18,
938 ' || 'OPI_MEASURE19
939 ' || 'FROM
940 ' || '(SELECT /* extra for paren_cnt = 5 */
941 ' || l_view_by_fact_col || ',
942 ' || 'OPI_MEASURE2,
943 ' || 'OPI_MEASURE3,
944 ' || 'OPI_MEASURE4,
945 ' || 'OPI_MEASURE12,
946 ' || 'OPI_MEASURE6,
947 ' || 'OPI_MEASURE13,
948 ' || 'OPI_MEASURE7,
949 ' || 'OPI_MEASURE14,
950 ' || 'OPI_MEASURE8,
951 ' || 'OPI_MEASURE15,
952 ' || 'OPI_MEASURE9,
953 ' || 'OPI_MEASURE16,
957 ' || 'OPI_MEASURE18,
954 ' || 'OPI_MEASURE10,
955 ' || 'OPI_MEASURE11,
956 ' || 'OPI_MEASURE17,
958 ' || 'OPI_MEASURE19
959 ' || 'FROM
960 ' || '(SELECT /* days onhand computation */
961 ' || l_view_by_fact_col || ',
962 ' || 'OPI_MEASURE2,
963 ' || 'OPI_MEASURE3,
964 ' || 'OPI_MEASURE4,
965 ' || 'OPI_MEASURE12,
966 ' || 'OPI_MEASURE6,
967 ' || 'OPI_MEASURE13,
968 ' || 'OPI_MEASURE7,
969 ' || 'OPI_MEASURE14,
970 ' || 'OPI_MEASURE8,
971 ' || 'OPI_MEASURE15,
972 ' || 'OPI_MEASURE9,
973 ' || 'OPI_MEASURE16,
974 -- not truly a percentage, so multiply denom by 100
975 ' || opi_dbi_rpt_util_pkg.percent_str
976 (p_numerator => 'OPI_MEASURE3',
977 p_denominator => '(p_cons_daily_avg * 100)',
978 p_measure_name => 'OPI_MEASURE10') || ',
979 -- not truly a percentage, so multiply denom by 100
980 ' || opi_dbi_rpt_util_pkg.percent_str
981 (p_numerator => 'OPI_MEASURE4',
982 p_denominator => '(OPI_MEASURE9 * 100)',
983 p_measure_name => 'OPI_MEASURE11') || ',
984 -- not truly a percentage, so multiply denom by 100
985 ' || opi_dbi_rpt_util_pkg.percent_str
986 (p_numerator => 'OPI_MEASURE12',
987 p_denominator => '(OPI_MEASURE16 * 100)',
988 p_measure_name => 'OPI_MEASURE17') || ',
989 ' || opi_dbi_rpt_util_pkg.change_pct_str
990 (p_new_numerator => 'OPI_MEASURE4',
991 p_new_denominator => '(OPI_MEASURE9 * 100)',
992 p_old_numerator => 'OPI_MEASURE3',
993 p_old_denominator => '(p_cons_daily_avg * 100)',
994 p_measure_name => 'OPI_MEASURE18') || ',
995 ' || opi_dbi_rpt_util_pkg.change_pct_str
996 (p_new_numerator => 'OPI_MEASURE12',
997 p_new_denominator => '(OPI_MEASURE16 * 100)',
998 p_old_numerator => 'p_onhand_val_total',
999 p_old_denominator => '(p_cons_daily_avg_total * 100)',
1000 p_measure_name => 'OPI_MEASURE19') || '
1001 ' || 'FROM
1002 ' || '(SELECT /* basic measure computation */
1003 ' || l_view_by_fact_col || ',
1004 ' || opi_dbi_rpt_util_pkg.raw_str
1005 (p_str => 'c_onhand_qty')
1006 || ' OPI_MEASURE2,
1007 ' || opi_dbi_rpt_util_pkg.nvl_str
1008 (p_str => 'p_onhand_val')
1009 || ' OPI_MEASURE3,
1010 ' || opi_dbi_rpt_util_pkg.nvl_str
1011 (p_str => 'c_onhand_val')
1012 || ' OPI_MEASURE4,
1013 ' || opi_dbi_rpt_util_pkg.nvl_str
1014 (p_str => 'c_onhand_val_total')
1015 || ' OPI_MEASURE12,
1016 ' || opi_dbi_rpt_util_pkg.nvl_str
1017 (p_str => 'c_prod_usage_val')
1018 || ' OPI_MEASURE6,
1019 ' || opi_dbi_rpt_util_pkg.nvl_str
1020 (p_str => 'c_prod_usage_val_total')
1021 || ' OPI_MEASURE13,
1022 ' || opi_dbi_rpt_util_pkg.nvl_str
1023 (p_str => 'c_cogs_val')
1024 || ' OPI_MEASURE7,
1025 ' || opi_dbi_rpt_util_pkg.nvl_str
1026 (p_str => 'c_cogs_val_total')
1027 || ' OPI_MEASURE14,
1028 ' || opi_dbi_rpt_util_pkg.nvl_str
1029 (p_str =>
1030 opi_dbi_rpt_util_pkg.nvl_str
1031 ('c_prod_usage_val') || ' + ' ||
1032 opi_dbi_rpt_util_pkg.nvl_str
1033 ('c_cogs_val'))
1034 || ' OPI_MEASURE8,
1035 ' || opi_dbi_rpt_util_pkg.nvl_str
1036 (p_str =>
1037 opi_dbi_rpt_util_pkg.nvl_str
1038 ('c_prod_usage_val_total') || ' + ' ||
1039 opi_dbi_rpt_util_pkg.nvl_str
1040 ('c_cogs_val_total'))
1041 || ' OPI_MEASURE15,
1042 -- not truly a percentage, so multiply denom by 100
1043 ' || opi_dbi_rpt_util_pkg.percent_str_basic
1044 (p_numerator =>
1045 opi_dbi_rpt_util_pkg.neg_str (
1046 opi_dbi_rpt_util_pkg.nvl_str
1047 ('c_prod_usage_val') || ' + ' ||
1048 opi_dbi_rpt_util_pkg.nvl_str
1049 ('c_cogs_val')),
1050 p_denominator => ('(:OPI_INV_DOH_PER_LEN * 100)'),
1051 p_measure_name => 'OPI_MEASURE9') || ',
1052 -- not truly a percentage, so multiply denom by 100
1053 ' || opi_dbi_rpt_util_pkg.percent_str_basic
1054 (p_numerator =>
1055 opi_dbi_rpt_util_pkg.neg_str (
1056 opi_dbi_rpt_util_pkg.nvl_str
1057 ('c_prod_usage_val_total') || ' + ' ||
1058 opi_dbi_rpt_util_pkg.nvl_str
1059 ('c_cogs_val_total')),
1060 p_denominator => ('(:OPI_INV_DOH_PER_LEN * 100)'),
1061 p_measure_name => 'OPI_MEASURE16') || ',
1062 ' || ' null OPI_MEASURE10,
1063 ' || ' null OPI_MEASURE11,
1067 ' || ' p_onhand_val_total,
1064 ' || ' null OPI_MEASURE17,
1065 ' || ' null OPI_MEASURE18,
1066 ' || ' null OPI_MEASURE19,
1068 ' || opi_dbi_rpt_util_pkg.nvl_str
1069 (p_str =>
1070 opi_dbi_rpt_util_pkg.nvl_str
1071 ('p_prod_usage_val') || ' + ' ||
1072 opi_dbi_rpt_util_pkg.nvl_str
1073 ('p_cogs_val'))
1074 || ' p_total_cons_val,
1075 ' || opi_dbi_rpt_util_pkg.nvl_str
1076 (p_str =>
1077 opi_dbi_rpt_util_pkg.nvl_str
1078 ('p_prod_usage_val_total') || ' + ' ||
1079 opi_dbi_rpt_util_pkg.nvl_str
1080 ('p_cogs_val_total'))
1081 || ' p_total_cons_val_total,
1082 -- not truly a percentage, so multiply denom by 100
1083 ' || opi_dbi_rpt_util_pkg.percent_str_basic
1084 (p_numerator =>
1085 opi_dbi_rpt_util_pkg.neg_str (
1086 opi_dbi_rpt_util_pkg.nvl_str
1087 ('p_prod_usage_val') || ' + ' ||
1088 opi_dbi_rpt_util_pkg.nvl_str
1089 ('p_cogs_val')),
1090 p_denominator => ('(:OPI_INV_DOH_PER_LEN * 100)'),
1091 p_measure_name => 'p_cons_daily_avg') || ',
1092 -- not truly a percentage, so multiply denom by 100
1093 ' || opi_dbi_rpt_util_pkg.percent_str_basic
1094 (p_numerator =>
1095 opi_dbi_rpt_util_pkg.neg_str (
1096 opi_dbi_rpt_util_pkg.nvl_str
1097 ('p_prod_usage_val_total') || ' + ' ||
1098 opi_dbi_rpt_util_pkg.nvl_str
1099 ('p_cogs_val_total')),
1100 p_denominator => ('(:OPI_INV_DOH_PER_LEN * 100)'),
1101 p_measure_name => 'p_cons_daily_avg_total');
1102
1103 return l_sel_clause;
1104 -- }
1105 END get_inv_doh_sel_clause;
1106
1107
1108 /****************************************
1109 * Current Inventory Status
1110 ****************************************/
1111
1112 /* get_curr_inv_stat_sql
1113
1114 Description
1115 Current Inventory Status (Table) report query function.
1116
1117 Inputs
1118 1. p_params - table of parameters with which report was run.
1119
1120 Outputs
1121 1. x_custom_sql - sql report query.
1122 2. x_custom_output - table of values for bind variables in
1123 sql report query.
1124
1125 History
1126
1127 Date Author Action
1128 07/18/05 Dinkar Gupta Wrote Function
1129 */
1130 PROCEDURE get_curr_inv_stat_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1131 x_custom_sql OUT NOCOPY VARCHAR2,
1132 x_custom_output OUT NOCOPY
1133 BIS_QUERY_ATTRIBUTES_TBL)
1134 IS
1135 -- {
1136 l_query VARCHAR2 (32767);
1137
1138 l_view_by VARCHAR2(120);
1139 l_view_by_col VARCHAR2 (120);
1140 l_xtd VARCHAR2(10);
1141 l_comparison_type VARCHAR2(1);
1142
1143 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1144 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1145 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1146 l_in_join_rec poa_dbi_util_pkg.POA_DBI_IN_JOIN_REC;
1147
1148 l_where_clause VARCHAR2 (2000);
1149 l_mv VARCHAR2 (30);
1150
1151 l_aggr_level_flag VARCHAR2(1);
1152
1153 l_custom_rec BIS_QUERY_ATTRIBUTES;
1154
1155 l_cur_suffix VARCHAR2 (5);
1156
1157 l_filter_clause VARCHAR2 (20000);
1158
1159 l_view_by_str VARCHAR2 (1000);
1160 l_view_by_str_new VARCHAR2 (1000);
1161 l_view_by_str_nvl VARCHAR2 (1000);
1162
1163 l_item_id VARCHAR2(100);
1164
1165 l_viewby_rank_clause VARCHAR2 (4000);
1166
1167 -- }
1168 BEGIN
1169 -- {
1170 -- initialization block
1171 x_custom_sql := NULL;
1172 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL ();
1173 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1174 l_query := NULL;
1175 l_view_by := NULL;
1176 l_view_by_col := NULL;
1177 l_xtd := NULL;
1178 l_comparison_type := NULL;
1179 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1180 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1181 l_in_join_tbl := poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL ();
1182 l_in_join_rec := NULL;
1183 l_where_clause := NULL;
1184 l_mv := NULL;
1185 l_aggr_level_flag := NULL;
1186 l_cur_suffix := NULL;
1187 l_filter_clause := NULL;
1188 l_view_by_str := NULL;
1189 l_view_by_str_new := NULL;
1190 l_view_by_str_nvl := NULL;
1191 l_item_id := NULL;
1192 l_viewby_rank_clause := NULL;
1193
1194 -- Process the parameters using the template package for the
1195 -- shipment consumption branch.
1196 opi_dbi_rpt_util_pkg.process_parameters (
1197 p_param => p_param,
1198 p_view_by => l_view_by,
1199 p_view_by_col_name => l_view_by_col,
1200 p_comparison_type => l_comparison_type,
1201 p_xtd => l_xtd,
1202 p_cur_suffix => l_cur_suffix,
1203 p_where_clause => l_where_clause,
1204 p_mv => l_mv,
1208 p_func_area => 'OPI',
1205 p_join_tbl => l_join_tbl,
1206 p_mv_level_flag => l_aggr_level_flag,
1207 p_trend => 'N',
1209 p_version => '12.0',
1210 p_role => '',
1211 p_mv_set => 'CURR_INV_STAT',
1212 p_mv_flag_type => 'CURR_INV_STAT_LEVEL');
1213
1214
1215 -- Build the query from the SELECT clause
1216 l_query := get_curr_inv_stat_sel_clause
1217 (p_view_by_dim => l_view_by,
1218 p_join_tbl => l_join_tbl);
1219
1220 -- The inner join table for viewby grade
1221 get_curr_inv_stat_mln (p_param => p_param, p_query => l_query);
1222
1223 -- The inner join table for viewby inv cat
1224 get_curr_inv_stat_eni (p_param => p_param, p_query => l_query);
1225
1226 -- Since this query runs directly on the OLTP tables,
1227 -- get the correctly formatted viewby column keys from the
1228 -- OLTP tables, including default values for NULL.
1229 get_curr_inv_viewby_cols (p_view_by => l_view_by,
1230 p_view_by_col => l_view_by_col,
1231 p_query => l_query);
1232
1233 -- Get the parameter conditions in the where clause
1234 get_curr_inv_stat_param_cond (p_param => p_param, p_query => l_query);
1235
1236 -- Get the security where clause
1237 get_curr_inv_sec_where_clause (p_param => p_param, p_query => l_query);
1238
1239 -- The outermost ranking/order by clause with
1240 -- the join to the dimension tables.
1241 l_viewby_rank_clause :=
1242 poa_dbi_template_pkg.get_viewby_rank_clause (
1243 p_join_tables => l_join_tbl,
1244 p_use_windowing => 'Y');
1245
1246 -- Put the query together
1247 -- There should be no need for a filter clause.
1248 l_query := l_query || ' ) ) ) oset,
1249 ' || ' (SELECT (substr (&ITEM+ENI_ITEM_ORG, 1, instr (&ITEM+ENI_ITEM_ORG, ''-'') - 1)) inventory_item_id FROM eni_oltp_item_star where id = &ITEM+ENI_ITEM_ORG) item_uom,
1250 ' || l_viewby_rank_clause;
1251
1252 x_custom_sql := l_query;
1253
1254 return;
1255 -- }
1256 END get_curr_inv_stat_sql;
1257
1258 /* get_curr_inv_stat_mln
1259
1260 Description
1261 Current Inventory Status (Table) MTL_LOT_NUMBERS procedure.
1262
1263 For viewby or parameter of item grade, join MOQ to MLN and
1264 select the grade_code from MTL_LOT_NUMBERS.
1265
1266 Also append the relevant join conditions to the where clause.
1267
1268 Inputs
1269 1. p_params - table of parameters with which report was run.
1270 2. p_query - query with placeholders
1271
1272 Outputs
1273 1. p_query - query with MLN alias and join conditions.
1274
1275 History
1276
1277 Date Author Action
1278 07/18/05 Dinkar Gupta Wrote Function
1279 */
1280 PROCEDURE get_curr_inv_stat_mln
1281 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1282 p_query IN OUT NOCOPY VARCHAR2)
1283 IS
1284 -- {
1285
1286 l_join_mln BOOLEAN;
1287 l_outer_join_mln VARCHAR2 (4);
1288 -- }
1289 BEGIN
1290 -- {
1291 -- Initialization block
1292 l_join_mln := FALSE;
1293 l_outer_join_mln := '(+)'; -- by default, outer join
1294
1295 FOR i in 1..p_param.count
1296 LOOP
1297 -- {
1298 -- If only the Viewby is Item grade, must outer join mln
1299 IF (p_param(i).parameter_name = 'VIEW_BY' AND
1300 p_param(i).parameter_value =
1301 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL') THEN
1302 -- {
1303 l_join_mln := TRUE;
1304 -- }
1305 END IF;
1306
1307 -- If an item grade parameter has been specified,
1308 -- join to the mtl_lot_numbers table need not be an outer join.
1309 IF (p_param(i).parameter_name =
1310 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL'
1311 AND
1312 NOT (p_param(i).parameter_id is null OR
1313 p_param(i).parameter_id = 'All') ) THEN
1314 -- {
1315 l_join_mln := TRUE;
1316 -- no need to outer join since specific grade parameter value
1317 -- given.
1318 l_outer_join_mln := '';
1319 -- }
1320 END IF;
1321 -- }
1322 END LOOP;
1323
1324 IF (l_join_mln) THEN
1325 -- {
1326 p_query := replace (p_query, ':OPI_MLN_TABLE',
1327 'mtl_lot_numbers mln, ');
1328
1329 -- Also append the relevant conditions to the where clause
1330 -- with the correct outer join conditions.
1331 p_query := replace (p_query, ':OPI_MLN_CONDITIONS',
1332 ' AND fact.organization_id = mln.organization_id' ||
1333 l_outer_join_mln ||
1334 ' AND fact.inventory_item_id = mln.inventory_item_id' ||
1335 l_outer_join_mln ||
1336 ' AND fact.lot_number = mln.lot_number' ||
1337 l_outer_join_mln || ' ');
1338 -- }
1339 ELSE
1340 -- {
1341 -- MLN is not required
1342 p_query := replace (p_query, ':OPI_MLN_TABLE', '');
1343 p_query := replace (p_query, ':OPI_MLN_CONDITIONS', '');
1344 -- }
1345
1346 END IF;
1347
1348 return;
1349
1350 -- }
1351 END get_curr_inv_stat_mln;
1352
1353 /* get_curr_inv_stat_eni
1354
1355 Description
1356 Current Inventory Status (Table) ENI_OLTP_ITEM_STAR procedure.
1357
1361 Also append the relevant join conditions to the where clause.
1358 For viewby parameter of item cat, join MOQ to ENI_OLTP_ITEM_STAR
1359 and select inv_category_id from ENI_OLTP_ITEM_STAR
1360
1362
1363 Inputs
1364 1. p_viewby - viewby dimension key.
1365 2. p_query - query with placeholders
1366
1367 Outputs
1368 1. p_query - query with ENI alias and join conditions.
1369
1370 History
1371
1372 Date Author Action
1373 07/18/05 Dinkar Gupta Wrote Function
1374 */
1375 PROCEDURE get_curr_inv_stat_eni
1376 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1377 p_query IN OUT NOCOPY VARCHAR2)
1378 IS
1379 -- {
1380 l_join_eni BOOLEAN;
1381 -- }
1382 BEGIN
1383 -- {
1384
1385 -- initialization block
1386 l_join_eni := FALSE;
1387
1388 FOR i in 1..p_param.count
1389 LOOP
1390 -- {
1391 -- If only the Viewby is Item grade, must outer join mln
1392 IF ( (p_param(i).parameter_name = 'VIEW_BY' AND
1393 p_param(i).parameter_value =
1394 'ITEM+ENI_ITEM_INV_CAT') OR
1395 (p_param(i).parameter_name =
1396 'ITEM+ENI_ITEM_INV_CAT'
1397 AND
1398 NOT (p_param(i).parameter_id is null OR
1399 p_param(i).parameter_id = 'All')) ) THEN
1400 -- {
1401 l_join_eni := TRUE;
1402 -- }
1403 END IF;
1404 -- }
1405 END LOOP;
1406
1407 IF (l_join_eni) THEN
1408 -- {
1409 p_query := replace (p_query, ':OPI_ENI_OLTP_STAR_TABLE',
1410 'eni_oltp_item_star eios, ');
1411
1412 -- Also append the relevant conditions to the where clause
1413 -- with the correct outer join conditions.
1414 p_query := replace (p_query, ':OPI_ENI_OLTP_STAR_COND',
1415 ' AND fact.organization_id = eios.organization_id ' ||
1416 ' AND fact.inventory_item_id = eios.inventory_item_id ');
1417 -- }
1418 ELSE
1419 -- {
1420 -- ENI is not required
1421 p_query := replace (p_query, ':OPI_ENI_OLTP_STAR_TABLE', '');
1422 p_query := replace (p_query, ':OPI_ENI_OLTP_STAR_COND', '');
1423 -- }
1424
1425 END IF;
1426
1427
1428 return;
1429
1430 -- }
1431 END get_curr_inv_stat_eni;
1432
1433 /* get_curr_inv_viewby_format
1434
1435 Description
1436 Current Inventory Status (Table) viewby column formatted
1437 with decodes joining various columns to build various keys.
1438
1439 Inputs
1440 1. p_viewby - Dimension level key of viewby
1441
1442 Output
1443 3. l_viewby_format - Formatted viewby
1444
1445 History
1446
1447 Date Author Action
1448 07/20/05 Dinkar Gupta Wrote Function
1449
1450
1451 */
1452 FUNCTION get_curr_inv_viewby_format (p_viewby IN VARCHAR2)
1453 RETURN VARCHAR2
1454 IS
1455 -- {
1456 l_viewby_format VARCHAR2 (1000);
1457 -- }
1458 BEGIN
1459 -- {
1460
1461 -- initialization block
1462 l_viewby_format := NULL;
1463
1464 IF (p_viewby = 'ORGANIZATION+OPI_SUB_LOCATOR_LVL') THEN
1465 -- {
1466 l_viewby_format := ' decode (fact.locator_id,
1467 NULL, ''-1'',
1468 fact.locator_id || ''-'' || fact.subinventory_code || ''-'' || fact.organization_id) ';
1469 -- }
1470 ELSIF (p_viewby = 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL') THEN
1471 -- {
1472 l_viewby_format := ' decode (mln.grade_code,
1473 NULL, ''-1'',
1474 mln.grade_code || ''-'' || fact.inventory_item_id || ''-'' || fact.organization_id) ';
1475 -- }
1476 ELSIF (p_viewby = 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL') THEN
1477 -- {
1478 l_viewby_format := ' decode (fact.lot_number,
1479 NULL, ''-2'',
1480 fact.lot_number || ''-'' || fact.inventory_item_id || ''-'' || fact.organization_id) ';
1481 -- }
1482 ELSIF (p_viewby = 'ITEM+ENI_ITEM_ORG') THEN
1483 -- {
1484 l_viewby_format :=
1485 ' (fact.inventory_item_id || ''-'' || fact.organization_id) ';
1486 --}
1487 ELSIF (p_viewby = 'ITEM+ENI_ITEM_INV_CAT') THEN
1488 -- {
1489 l_viewby_format :=
1490 ' nvl (eios.inv_category_id, -1) ';
1491 --}
1492 ELSIF (p_viewby = 'ORGANIZATION+ORGANIZATION') THEN
1493 -- {
1494 l_viewby_format := ' to_char (fact.organization_id) ';
1495 --}
1496 ELSIF (p_viewby = 'ORGANIZATION+ORGANIZATION_SUBINVENTORY') THEN
1497 -- {
1498 l_viewby_format := ' decode (fact.subinventory_code,
1499 NULL, ''-1'',
1500 fact.subinventory_code || ''-'' || fact.organization_id) ';
1501 --}
1502 END IF;
1503
1504 return l_viewby_format;
1505
1506 -- }
1507 END get_curr_inv_viewby_format;
1508
1509
1510 /* get_curr_inv_viewby_cols
1511
1512 Description
1513 Current Inventory Status (Table) viewby column replacements.
1514
1515 This function identifies the viewby column in the inner
1516 query and what it should be replaced by to handle the NULL
1517 values of the viewby. Since the Current Inventory Status
1518 query is written on OLTP tables, the NULL values for certain
1522 Inputs
1519 dimensions (e.g. lot) have not been replaced with their
1520 'Unassigned' id's (e.g. -2) in the report query.
1521
1523 1. p_view_by - Dimension level key of viewby
1524 2. p_view_by_col - Standard column alias for dimension level
1525 3. p_query - Input query with place holders
1526
1527 Outputs
1528 1. p_query - query with viewby column name replaced
1529
1530 History
1531
1532 Date Author Action
1533 07/18/05 Dinkar Gupta Wrote Function
1534 */
1535 PROCEDURE get_curr_inv_viewby_cols
1536 (p_view_by IN VARCHAR2,
1537 p_view_by_col IN VARCHAR2,
1538 p_query IN OUT NOCOPY VARCHAR2)
1539 IS
1540 -- {
1541 l_view_by_str VARCHAR2 (1000);
1542 -- }
1543 BEGIN
1544 -- {
1545 -- initialization block
1546 l_view_by_str := get_curr_inv_viewby_format (p_view_by);
1547
1548 -- replace the placeholders
1549 p_query := replace (p_query, ':OPI_CURR_INV_STAT_VIEWBY_ALIAS',
1550 p_view_by_col);
1551 p_query := replace (p_query, ':OPI_CURR_INV_STAT_VIEWBY', l_view_by_str);
1552 return;
1553
1554 -- }
1555 END get_curr_inv_viewby_cols;
1556
1557 /* get_curr_inv_stat_param_cond
1558
1559 Description
1560 Current Inventory Status (Table) inner parameter conditions
1561 statement.
1562
1563 Inputs
1564 1. p_params - table of parameters with which report was run.
1565 2. p_query - query with placeholders
1566
1567 Outputs
1568 1. p_query - query with MLN alias and join conditions.
1569
1570 History
1571
1572 Date Author Action
1573 07/18/05 Dinkar Gupta Wrote Function
1574 */
1575 PROCEDURE get_curr_inv_stat_param_cond
1576 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1577 p_query IN OUT NOCOPY VARCHAR2)
1578 IS
1579 -- {
1580
1581 l_param_cond VARCHAR2 (4000);
1582
1583 -- }
1584 BEGIN
1585 -- {
1586 -- Initialization block
1587 l_param_cond := NULL;
1588
1589 FOR i in 1..p_param.count
1590 LOOP
1591 -- {
1592
1593 -- No need to put in conditions for the inventory category parameter
1594 -- since the only navigatio
1595 CASE
1596 -- {
1597 WHEN p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION' AND
1598 NOT (p_param(i).parameter_value IS NULL OR
1599 p_param(i).parameter_value = 'All') THEN
1600 l_param_cond := l_param_cond ||
1601 ' AND ( ' ||
1602 get_curr_inv_viewby_format (p_param(i).parameter_name) ||
1603 ' ) in (&ORGANIZATION+ORGANIZATION)';
1604 WHEN p_param(i).parameter_name =
1605 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND
1606 NOT (p_param(i).parameter_value IS NULL OR
1607 p_param(i).parameter_value = 'All') THEN
1608 l_param_cond := l_param_cond ||
1609 ' AND ( ' ||
1610 get_curr_inv_viewby_format (p_param(i).parameter_name) ||
1611 ' ) in (&ORGANIZATION+ORGANIZATION_SUBINVENTORY)';
1612 WHEN p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG' AND
1613 NOT (p_param(i).parameter_value IS NULL OR
1614 p_param(i).parameter_value = 'All') THEN
1615 l_param_cond := l_param_cond ||
1616 ' AND ( ' ||
1617 get_curr_inv_viewby_format (p_param(i).parameter_name) ||
1618 ' ) in (&ITEM+ENI_ITEM_ORG)';
1619 WHEN p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT' AND
1620 NOT (p_param(i).parameter_value IS NULL OR
1621 p_param(i).parameter_value = 'All') THEN
1622 l_param_cond := l_param_cond ||
1623 ' AND ( ' ||
1624 get_curr_inv_viewby_format (p_param(i).parameter_name) ||
1625 ' ) in (&ITEM+ENI_ITEM_INV_CAT)';
1626 WHEN p_param(i).parameter_name =
1627 'ORGANIZATION+OPI_SUB_LOCATOR_LVL' AND
1628 NOT (p_param(i).parameter_value IS NULL OR
1629 p_param(i).parameter_value = 'All') THEN
1630 l_param_cond := l_param_cond ||
1631 ' AND ( ' ||
1632 get_curr_inv_viewby_format (p_param(i).parameter_name) ||
1633 ' ) in (&ORGANIZATION+OPI_SUB_LOCATOR_LVL)';
1634 WHEN p_param(i).parameter_name =
1635 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL' AND
1636 NOT (p_param(i).parameter_value IS NULL OR
1637 p_param(i).parameter_value = 'All') THEN
1638 l_param_cond := l_param_cond ||
1639 ' AND ( ' ||
1640 get_curr_inv_viewby_format (p_param(i).parameter_name) ||
1641 ' ) in (&OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL)';
1642 WHEN p_param(i).parameter_name =
1643 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL' AND
1644 NOT (p_param(i).parameter_value IS NULL OR
1645 p_param(i).parameter_value = 'All') THEN
1646 l_param_cond := l_param_cond ||
1647 ' AND ( ' ||
1648 get_curr_inv_viewby_format (p_param(i).parameter_name) ||
1649 ' ) in (&OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL)';
1650 ELSE
1651 l_param_cond := l_param_cond || '';
1652 --}
1653 END CASE;
1654 -- }
1655 END LOOP;
1656
1657 -- add parameter conditions
1661
1658 p_query := replace (p_query, ':OPI_PARAM_CONDITIONS', l_param_cond);
1659
1660 return;
1662 -- }
1663 END get_curr_inv_stat_param_cond;
1664
1665
1666 /* get_curr_inv_sec_where_clause
1667
1668 Description
1669 Current Inventory Status (Table) viewby column security
1670 where clause.
1671
1672 Basically the standard OPI where clause from the
1673 common utility.
1674
1675 Inputs
1676 1. p_param - BIS parameter table
1677 2. p_query - Input query with place holders
1678
1679 Outputs
1680 1. p_query - query with viewby column name replaced
1681
1682 History
1683
1684 Date Author Action
1685 07/18/05 Dinkar Gupta Wrote Function
1686 */
1687 PROCEDURE get_curr_inv_sec_where_clause
1688 (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1689 p_query IN OUT NOCOPY VARCHAR2)
1690 IS
1691 -- {
1692 l_security_where_clause VARCHAR2 (2000);
1693 -- }
1694 BEGIN
1695 -- {
1696
1697 -- initialization block
1698 l_security_where_clause := '';
1699
1700 FOR i in 1..p_param.count
1701 LOOP
1702 -- {
1703 -- If only the Viewby is Item grade, must outer join mln
1704 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION') THEN
1705 -- {
1706 l_security_where_clause :=
1707 opi_dbi_rpt_util_pkg.get_security_where_clauses
1708 (p_org_value => p_param(i).parameter_value,
1709 p_trend => 'N');
1710 -- }
1711 END IF;
1712 -- }
1713 END LOOP;
1714
1715 -- replace the placeholders
1716 p_query := replace (p_query, ':OPI_SECURITY_CLAUSE',
1717 l_security_where_clause);
1718 return;
1719
1720 -- }
1721 END get_curr_inv_sec_where_clause;
1722
1723
1724
1725 /* get_curr_inv_stat_sel_clause
1726
1727 Description
1728 Returns the select clause for the Current Inventory
1729 Status (Table) report query.
1730
1731 Input
1732
1733 Outputs
1734 1. l_sel_clause - Select clause of the report query
1735
1736 History
1737
1738 Date Author Action
1739 07/13/05 Dinkar Gupta Wrote Function
1740 */
1741 FUNCTION get_curr_inv_stat_sel_clause (p_view_by_dim IN VARCHAR2,
1742 p_join_tbl IN
1743 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
1744 RETURN VARCHAR2
1745 IS
1746 -- {
1747 l_sel_clause VARCHAR2 (32767);
1748 l_description varchar2 (120);
1749 l_uom varchar2 (30);
1750 l_view_by_fact_col VARCHAR2(400);
1751 -- }
1752 BEGIN
1753 -- {
1754 -- initialization block
1755 l_sel_clause := NULL;
1756 l_description := NULL;
1757 l_uom := NULL;
1758 l_view_by_fact_col := NULL;
1759
1760 -- fact column view by's
1761 l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
1762 (p_join_tbl => p_join_tbl);
1763
1764 -- It is fine to sum primary_qty in the secondary qty measures
1765 -- in the rank clause. Since the report is item specific, the
1766 -- conversion between primary and secondary quantity is fixed,
1767 -- and hence the sort order on the primary and secondary quantity
1768 -- is the same.
1769 l_sel_clause :=
1770 'SELECT /* outer select */
1771 ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
1772 || 'v2.unit_of_measure OPI_ATTRIBUTE1,
1773 ' || 'oset.OPI_MEASURE2,
1774 ' || 'oset.OPI_MEASURE5,
1775 ' || 'v3.unit_of_measure OPI_ATTRIBUTE2,
1776 ' || 'oset.OPI_MEASURE4,
1777 ' || 'oset.OPI_MEASURE6
1778 ' || 'FROM
1779 ' || '(SELECT (rank () over
1780 ' || ' (&ORDER_BY_CLAUSE nulls last,
1781 ' || l_view_by_fact_col || ')) - 1 rnk,
1782 ' || l_view_by_fact_col || ',
1783 ' || 'OPI_MEASURE2,
1784 ' || 'OPI_MEASURE5,
1785 ' || 'OPI_MEASURE4,
1786 ' || 'OPI_MEASURE6
1787 ' || 'FROM
1788 ' || '(SELECT /* measure computation */
1789 ' || l_view_by_fact_col || ',
1790 ' || opi_dbi_rpt_util_pkg.raw_str
1791 (p_str => 'c_primary_qty')
1792 || ' OPI_MEASURE2,
1793 ' || opi_dbi_rpt_util_pkg.nvl_str
1794 (p_str => 'c_primary_qty_total')
1795 || ' OPI_MEASURE5,
1796 ' || opi_dbi_rpt_util_pkg.raw_str
1797 (p_str => 'c_secondary_qty')
1798 || ' OPI_MEASURE4,
1799 ' || opi_dbi_rpt_util_pkg.nvl_str
1800 (p_str => 'c_secondary_qty_total')
1801 || ' OPI_MEASURE6
1802 ' || ' FROM ( /* OLTP select */
1803 ' || ' SELECT
1804 ' || ' msi.primary_uom_code,
1805 ' || ' msi.secondary_uom_code,
1806 ' || ' :OPI_CURR_INV_STAT_VIEWBY :OPI_CURR_INV_STAT_VIEWBY_ALIAS,
1807 ' || ' sum (fact.transaction_quantity) c_primary_qty,
1808 ' || ' sum (sum (fact.transaction_quantity)) over () c_primary_qty_total,
1809 ' || ' sum (fact.secondary_transaction_quantity) c_secondary_qty,
1810 ' || ' sum (sum (fact.secondary_transaction_quantity)) over () c_secondary_qty_total
1811 ' || ' FROM mtl_onhand_quantities fact,
1812 ' || ' :OPI_MLN_TABLE
1813 ' || ' :OPI_ENI_OLTP_STAR_TABLE
1814 ' || ' mtl_system_items_b msi
1815 ' || ' WHERE fact.inventory_item_id = msi.inventory_item_id
1816 ' || ' AND fact.organization_id = msi.organization_id
1817 ' || ' :OPI_MLN_CONDITIONS
1818 ' || ' :OPI_ENI_OLTP_STAR_COND
1819 ' || ' :OPI_PARAM_CONDITIONS
1820 ' || ' :OPI_SECURITY_CLAUSE
1821 ' || ' GROUP BY msi.primary_uom_code,
1822 ' || ' msi.secondary_uom_code,
1823 ' || ' :OPI_CURR_INV_STAT_VIEWBY
1824 ';
1825
1826 return l_sel_clause;
1827 -- }
1828 END get_curr_inv_stat_sel_clause;
1829
1830
1831 END OPI_DBI_INV_CURR_RPT_PKG;