[Home] [Help]
PACKAGE BODY: APPS.OPI_DBI_RPT_UTIL_PKG
Source
1 PACKAGE BODY OPI_DBI_RPT_UTIL_PKG AS
2 /*$Header: OPIDRMFGUTB.pls 120.9 2006/02/24 03:23:27 achandak noship $ */
3
4 s_pkg_name CONSTANT VARCHAR2 (50) := 'opi_dbi_rpt_util_pkg';
5
6 /*++++++++++++++++++++++++++++++++++++++++*/
7 /* Local Functions
8 /*++++++++++++++++++++++++++++++++++++++++*/
9 PROCEDURE init_dim_map (p_dim_map out NOCOPY
10 poa_dbi_util_pkg.poa_dbi_dim_map,
11 p_mv_set IN VARCHAR2);
12
13 PROCEDURE reinit_dim_map (p_dim_map in out NOCOPY
14 poa_dbi_util_pkg.poa_dbi_dim_map,
15 p_mv IN VARCHAR2,
16 p_mv_set IN VARCHAR2);
17
18 FUNCTION get_mv (p_mv_set IN VARCHAR2,
19 p_mv_level_flag IN VARCHAR2,
20 p_view_by IN VARCHAR2,
21 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
22 RETURN VARCHAR2;
23
24 FUNCTION get_col_name (p_dim_name VARCHAR2)
25 RETURN VARCHAR2;
26
27 FUNCTION get_table (p_dim_name VARCHAR2)
28 RETURN VARCHAR2;
29
30 FUNCTION get_mv_flag_where_clause (p_mv_flag_type IN VARCHAR2,
31 p_trend IN VARCHAR2 := 'N',
32 p_mv IN VARCHAR2 := '',
33 p_mv_where_clause IN VARCHAR2)
34 RETURN VARCHAR2;
35
36 FUNCTION get_mv_specific_where_clause(p_mv_set IN VARCHAR2)
37 RETURN VARCHAR2;
38
39 FUNCTION get_scrap_filter_clause
40 RETURN VARCHAR2;
41
42 FUNCTION get_item_flag_where_clause(p_trend IN VARCHAR2 := 'N')
43 RETURN VARCHAR2;
44
45 FUNCTION get_rsc_flag_where_clause(p_trend IN VARCHAR2 := 'N')
46 RETURN VARCHAR2;
47
48 FUNCTION get_inv_val_flag_where_clause(p_trend IN VARCHAR2 := 'N')
49 RETURN VARCHAR2;
50
51 FUNCTION get_rtx_flag_where_clause(p_trend IN VARCHAR2 := 'N')
52 RETURN VARCHAR2;
53
54 FUNCTION get_rtp_flag_where_clause(p_trend IN VARCHAR2 := 'N')
55 RETURN VARCHAR2;
56
57 FUNCTION get_wms_c_utz_item_where_cl (p_trend IN VARCHAR2 := 'N')
58 RETURN VARCHAR2;
59
60 FUNCTION get_wms_c_utz_sub_where_cl (p_trend IN VARCHAR2 := 'N')
61 RETURN VARCHAR2;
62
63 FUNCTION get_wms_stor_utz_where_cl (p_trend IN VARCHAR2 := 'N')
64 RETURN VARCHAR2;
65
66
67 /* mochawla - declare functions for Pick and OP in DBI 7.1*/
68 FUNCTION get_pex_flag_where_clause(p_trend IN VARCHAR2 := 'N')
69 RETURN VARCHAR2;
70
71 FUNCTION get_per_flag_where_clause(p_trend IN VARCHAR2 := 'N')
72 RETURN VARCHAR2;
73
74 FUNCTION get_opp_flag_where_clause(p_trend IN VARCHAR2 := 'N')
75 RETURN VARCHAR2;
76
77 FUNCTION get_oper_flag_where_clause(p_trend IN VARCHAR2 := 'N')
78 RETURN VARCHAR2;
79
80 /* Current Inventory Status */
81 FUNCTION get_curr_inv_exp_where_cl (p_trend IN VARCHAR2 := 'N')
82 RETURN VARCHAR2;
83
84 /* Inventory Days Onhand */
85 FUNCTION get_prod_cons_where_cl (p_trend IN VARCHAR2 := 'N')
86 RETURN VARCHAR2;
87
88 FUNCTION get_cogs_ship_where_cl (p_trend IN VARCHAR2 := 'N')
89 RETURN VARCHAR2;
90
91 FUNCTION get_trx_reason_where_clause (p_trend IN VARCHAR2 := 'N')
92 RETURN VARCHAR2;
93
94 PROCEDURE get_join_info (p_view_by IN varchar2,
95 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
96 x_join_tbl OUT NOCOPY
97 poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
98 p_mv_set IN VARCHAR2);
99
100 FUNCTION get_mv_level_flag (p_mv_flag_type VARCHAR2,
101 p_dim_name VARCHAR2,
102 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
103 RETURN VARCHAR2;
104
105
106 FUNCTION get_item_flag_val (p_dim_name VARCHAR2,
107 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
108 RETURN VARCHAR2;
109
110 FUNCTION get_inv_val_aggr_flag (p_dim_name VARCHAR2,
111 p_dim_map IN
112 poa_dbi_util_pkg.poa_dbi_dim_map)
113 RETURN VARCHAR2;
114
115 FUNCTION get_wms_rtx_aggr_flag (p_dim_name VARCHAR2,
116 p_dim_map IN
117 poa_dbi_util_pkg.poa_dbi_dim_map)
118 RETURN VARCHAR2;
119
120 FUNCTION get_wms_rtp_aggr_flag (p_dim_name VARCHAR2,
121 p_dim_map IN
122 poa_dbi_util_pkg.poa_dbi_dim_map)
123 RETURN VARCHAR2;
124
125 FUNCTION get_resource_level_flag_val (p_dim_name IN VARCHAR2,
126 p_dim_map IN
127 poa_dbi_util_pkg.poa_dbi_dim_map)
128 RETURN VARCHAR2;
129
130 FUNCTION get_wms_c_utz_item_aggr_flag (p_dim_name VARCHAR2,
131 p_dim_map IN
132 poa_dbi_util_pkg.poa_dbi_dim_map)
133 RETURN VARCHAR2;
134
135 FUNCTION get_wms_c_utz_sub_aggr_flag (p_dim_name VARCHAR2,
136 p_dim_map IN
137 poa_dbi_util_pkg.poa_dbi_dim_map)
138 RETURN VARCHAR2;
139
140 FUNCTION get_wms_stor_utz_aggr_flag (p_dim_name VARCHAR2,
141 p_dim_map IN
142 poa_dbi_util_pkg.poa_dbi_dim_map)
143 RETURN VARCHAR2;
144
145 /* mochawla - declare functions for Pick and OP in DBI 7.1*/
146 FUNCTION get_wms_pex_aggr_flag (p_dim_name VARCHAR2,
147 p_dim_map IN
148 poa_dbi_util_pkg.poa_dbi_dim_map)
149 RETURN VARCHAR2;
150
151 FUNCTION get_wms_per_aggr_flag (p_dim_name VARCHAR2,
152 p_dim_map IN
153 poa_dbi_util_pkg.poa_dbi_dim_map)
154 RETURN VARCHAR2;
155
156 FUNCTION get_wms_opp_aggr_flag (p_dim_name VARCHAR2,
157 p_dim_map IN
158 poa_dbi_util_pkg.poa_dbi_dim_map)
159 RETURN VARCHAR2;
160
161 FUNCTION get_wms_oper_aggr_flag (p_dim_name VARCHAR2,
162 p_dim_map IN
163 poa_dbi_util_pkg.poa_dbi_dim_map)
164 RETURN VARCHAR2;
165
166 /* Current Inventory Expiration Status */
167 FUNCTION get_rollup1_aggr_flag (p_dim_name VARCHAR2,
168 p_dim_map IN
169 poa_dbi_util_pkg.poa_dbi_dim_map)
170 RETURN VARCHAR2;
171
172 /*Scrap By Reason */
173 FUNCTION get_trx_reason_aggr_flag (p_dim_name VARCHAR2,
174 p_dim_map IN
175 poa_dbi_util_pkg.poa_dbi_dim_map)
176 RETURN VARCHAR2;
177
178 /* changes for Cycle Count in DBI 7.0 */
179 FUNCTION get_cca_flag_where_clause(p_trend IN VARCHAR2 := 'N')
180 RETURN VARCHAR2;
181
182
183 FUNCTION get_cca_level_flag_val (p_dim_name IN VARCHAR2,
184 p_dim_map IN
185 poa_dbi_util_pkg.poa_dbi_dim_map)
186 RETURN VARCHAR2;
187
188 FUNCTION get_agg_level (p_mv_lvl_tbl IN opi_dbi_rpt_util_pkg.MV_AGG_LVL_TBL,
189 p_dim_bmap IN NUMBER)
190 RETURN NUMBER;
191
192 /*
193 Changes for Product Cost Management: Product Gross Margin in DBI 7.0
194 */
195
196 FUNCTION get_prodcat_cust_flag_val(p_dim_name IN VARCHAR2,
197 p_dim_map IN
198 poa_dbi_util_pkg.poa_dbi_dim_map)
199 RETURN VARCHAR2;
200
201 FUNCTION get_prdcat_cust_where_clause(p_trend IN VARCHAR2 := 'N')
202 RETURN VARCHAR2;
203
204 FUNCTION select_mv (p_mv_set IN VARCHAR2, p_mv_level_flag IN VARCHAR2,
205 p_view_by IN VARCHAR2, p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
206 RETURN VARCHAR2;
207
208 FUNCTION get_mv_where_clause_flag (p_mv IN VARCHAR2)
209 RETURN VARCHAR2;
210
211 /*++++++++++++++++++++++++++++++++++++++++*/
212 /* Function Definitions
213 /*++++++++++++++++++++++++++++++++++++++++*/
214
215 /* process_parameters
216
217 Generic routine to process the parameters passed in from the PMV
218 page.
219
220 Points of note:
221 p_mv_level_flag - For DBI 6.0 all report queries using this
222 package will use a flag to decide which rows of
223 their MVs they which need to query.
224 For instance, for Scrap and Material Usage, this
225 amounts to the item_cat_flag.
226 For unrecognized cost variance, there is no such
227 flag.
228 p_mv_flag_type - This determines what type of MV level flag is being
229 computed i.e. item_cat_flag, or nothing and
230 correspondingly what needs to be added to the
231 where clause.
232
233
234 Date Author Action
235 06/02/03 Dinkar Gupta Wrote Function
236
237 */
238 PROCEDURE process_parameters (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
239 p_view_by OUT NOCOPY VARCHAR2,
240 p_view_by_col_name OUT NOCOPY VARCHAR2,
241 p_comparison_type OUT NOCOPY VARCHAR2,
242 p_xtd OUT NOCOPY VARCHAR2,
243 p_cur_suffix OUT NOCOPY VARCHAR2,
244 p_where_clause OUT NOCOPY VARCHAR2,
245 p_mv OUT NOCOPY VARCHAR2,
246 p_join_tbl OUT NOCOPY
247 poa_dbi_util_pkg.poa_dbi_join_tbl,
248 p_mv_level_flag OUT NOCOPY VARCHAR2,
249 p_trend IN VARCHAR2,
250 p_func_area IN VaRCHAR2,
251 p_version IN VARCHAR2,
252 p_role IN VARCHAR2,
253 p_mv_set IN VARCHAR2,
254 p_mv_flag_type IN VARCHAR2 := 'NONE')
255 IS
256 l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
257 l_dim_bmap NUMBER := 0;
258 l_org_val VARCHAR2 (120) := NULL;
259
260 l_as_of_date DATE;
261 l_prev_as_of_date DATE;
262 l_nested_pattern NUMBER;
263
264 l_mv_where_clause VARCHAR2(1) := 'Y'; -- Determines if MV Flag Where Clause needs to be appended.
265
266 l_stmt_id NUMBER := 0;
267
268 BEGIN
269
270 -- initialize the dimension map with all the required dimensions.
271 l_stmt_id := 10;
272 init_dim_map (p_dim_map => l_dim_map,
273 p_mv_set => p_mv_set);
274
275 -- Get the various parameter values from the utility package.
276 -- This package will also compute the l_dim_bmap
277 l_stmt_id := 20;
278 poa_dbi_util_pkg.get_parameter_values (p_param => p_param,
279 p_dim_map => l_dim_map,
280 p_view_by => p_view_by,
281 p_comparison_type => p_comparison_type,
282 p_xtd => p_xtd,
283 p_as_of_date => l_as_of_date,
284 p_prev_as_of_date => l_prev_as_of_date,
285 p_cur_suffix => p_cur_suffix,
286 p_nested_pattern => l_nested_pattern,
287 p_dim_bmap => l_dim_bmap);
288
289 -- Find out the view by column name
290 l_stmt_id := 30;
291 IF (l_dim_map.exists (p_view_by)) THEN
292 p_view_by_col_name := l_dim_map(p_view_by).col_name;
293 END IF;
294
295
296 -- Get the org values
297 IF (l_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
298 l_org_val := l_dim_map ('ORGANIZATION+ORGANIZATION').value;
299 END IF;
300
301 -- Finally get the join info based on the dimension level parameters
302 -- passed in.
303 l_stmt_id := 40;
304 get_join_info (p_view_by => p_view_by,
305 p_dim_map => l_dim_map,
306 x_join_tbl => p_join_tbl,
307 p_mv_set => p_mv_set);
308
309 -- Get the item cat flag value for MV aggregation
310 l_stmt_id := 50;
311 p_mv_level_flag := get_mv_level_flag (p_mv_flag_type => p_mv_flag_type,
312 p_dim_name => p_view_by,
313 p_dim_map => l_dim_map);
314
315 -- Get the MV we need to join to.
316 l_stmt_id := 60;
317 p_mv := get_mv (p_mv_set => p_mv_set,
318 p_mv_level_flag => p_mv_level_flag,
319 p_view_by => p_view_by,
320 p_dim_map => l_dim_map);
321
322 reinit_dim_map (p_dim_map => l_dim_map,
323 p_mv => p_mv,
324 p_mv_set => p_mv_set);
325
326 -- Determine if MV Flag Where Clause needs to be appended. By default the value is 'Y'
327 l_stmt_id := 70;
328 l_mv_where_clause := get_mv_where_clause_flag (p_mv => p_mv);
329
330 -- Get the dimension level specific where clauses
331 -- and the security where clause.
332
333 l_stmt_id := 75;
334 if(p_mv_set = 'CPD') then
335 p_where_clause := '2=2';
336 else
337 p_where_clause := '';
338 end if;
339
340 l_stmt_id := 80;
341 p_where_clause := p_where_clause || poa_dbi_util_pkg.get_where_clauses (p_dim_map => l_dim_map,
342 p_trend => p_trend);
343
344 -- Attach the optional MV flag where clause
345 l_stmt_id := 90;
346 p_where_clause := p_where_clause ||
347 get_mv_flag_where_clause (
348 p_mv_flag_type => p_mv_flag_type,
349 p_trend => p_trend,
350 p_mv => p_mv,
351 p_mv_where_clause => l_mv_where_clause) ||
352 get_mv_specific_where_clause(p_mv_set => p_mv_set);
353
354 -- attach the security clause
355 l_stmt_id := 100;
356 p_where_clause := p_where_clause ||
357 get_security_where_clauses (p_org_value => l_org_val,
358 p_trend => p_trend);
359
360 END process_parameters;
361
362 /*++++++++++++++++++++++++++++++++++++++++*/
363 /* To determine if the MV where flag clause
364 is required.
365 /*++++++++++++++++++++++++++++++++++++++++*/
366 /* get_mv_where_clause_flag
367 If the MV definition does not have a
368 MV Level Flag or an aggregation
369 level flag return 'N' else return 'Y'
370 */
371 FUNCTION get_mv_where_clause_flag (p_mv IN VARCHAR2)
372 RETURN VARCHAR2
373 IS
374 l_mv_where_clause_flag VARCHAR2(1) := 'Y';
375 BEGIN
376 l_mv_where_clause_flag :=
377 (CASE p_mv
378 WHEN 'OPI_PGM_CAT_MV' THEN
379 'N'
380 ELSE
381 'Y'
382 END);
383 RETURN l_mv_where_clause_flag;
384 END get_mv_where_clause_flag;
385
386
387 /*++++++++++++++++++++++++++++++++++++++++*/
388 /* Where clause building routine
389 /*++++++++++++++++++++++++++++++++++++++++*/
390
391 /* get_mv_specific_where_clause
392
393 Depending on which report we want to populate, tag on any specific
394 conditions to the where clause.
395 */
396
397 FUNCTION get_mv_specific_where_clause(p_mv_set IN VARCHAR2)
398 RETURN VARCHAR2
399
400 IS
401
402 l_mv_specific_where_clause VARCHAR2 (200) :='';
403
404 BEGIN
405
406 IF (p_mv_set = 'SCR') THEN
407 l_mv_specific_where_clause := get_scrap_filter_clause;
408 ELSE
409 l_mv_specific_where_clause := '';
410 END IF;
411
412 return l_mv_specific_where_clause;
413
414 END get_mv_specific_where_clause;
415
416
417 /* get_mv_flag_where_clause
418
419 Depending on which MV flag is being used, get a different
420 where clause statement. The flag type is determined by p_mv_flag_type:
421 'ITEM_CAT' - Scrap or material usage need item_cat_flag.
422
423 'CCA_LEVEL' - 7.0 changes for cycle count Accuracy
424
425 'PRD_CUST' - 7.0 changes for Product Cost Management: Product Gross Margin
426
427 'WMS_PEX' : 7.1 - Pick Exceptions
428 'WMS_PER' : 7.1 - Pick Exceptions by Reason
429 'WMS_OPP' : 7.1 - OP Performance
430 'WMS_OPER': 7.1 - OP Exception by Reason
431 */
432 FUNCTION get_mv_flag_where_clause (p_mv_flag_type IN VARCHAR2,
433 p_trend IN VARCHAR2 := 'N',
434 p_mv IN VARCHAR2 := '',
435 p_mv_where_clause IN VARCHAR2)
436 RETURN VARCHAR2
437 IS
438
439 l_mv_flag_where_clause VARCHAR2 (200) := '';
440
441 BEGIN
442
443 IF (p_mv_where_clause = 'Y') THEN
444 l_mv_flag_where_clause :=
445 (CASE p_mv_flag_type
446 WHEN 'ITEM_CAT' THEN
447 get_item_flag_where_clause (p_trend)
448 WHEN 'RESOURCE_LEVEL' THEN
449 get_rsc_flag_where_clause (p_trend)
450 WHEN 'INV_VAL_LEVEL' THEN
451 get_inv_val_flag_where_clause (p_trend)
452 WHEN 'INV_VAL_UOM_LEVEL' THEN
453 -- Same as 'INV_VAL'
454 get_inv_val_flag_where_clause (p_trend)
455 WHEN 'CCA_LEVEL' THEN
456 get_cca_flag_where_clause (p_trend)
457 WHEN 'WMS_RTX' THEN
458 get_rtx_flag_where_clause (p_trend)
459 WHEN 'WMS_RTP' THEN
460 get_rtp_flag_where_clause (p_trend)
461 WHEN 'PRD_CUST' THEN
462 get_prdcat_cust_where_clause(p_trend)
463 WHEN 'WMS_CURR_UTZ_ITEM_LEVEL' THEN
464 get_wms_c_utz_item_where_cl (p_trend)
465 WHEN 'WMS_CURR_UTZ_SUB_LEVEL' THEN
466 get_wms_c_utz_sub_where_cl (p_trend)
467 WHEN 'WMS_STOR_UTZ_LEVEL' THEN
468 get_wms_stor_utz_where_cl (p_trend)
469 WHEN 'WMS_PEX' THEN
470 get_pex_flag_where_clause (p_trend)
471 WHEN 'WMS_PER' THEN
472 get_per_flag_where_clause (p_trend)
473 WHEN 'WMS_OPP' THEN
474 get_opp_flag_where_clause (p_trend)
475 WHEN 'WMS_OPER' THEN
476 get_oper_flag_where_clause (p_trend)
477 WHEN 'CURR_INV_EXP_LEVEL' THEN
478 get_curr_inv_exp_where_cl (p_trend)
479 WHEN 'PROD_CONS_LEVEL' THEN
480 get_prod_cons_where_cl (p_trend)
481 WHEN 'COGS_LEVEL' THEN
482 get_cogs_ship_where_cl (p_trend)
483 WHEN 'TRX_REASON_LEVEL' THEN
484 get_trx_reason_where_clause (p_trend)
485 ELSE
486 ''
487 END);
488 END IF;
489
490 RETURN l_mv_flag_where_clause;
491
492 END get_mv_flag_where_clause;
493
494
495 /* get_scrap_filter_clause
496
497 Return the 'source = 1' condition to filter out process data
498 for scrap reports.
499
500 */
501 FUNCTION get_scrap_filter_clause
502 RETURN VARCHAR2
503 IS
504 l_scrap_filter_clause VARCHAR2 (200) := '';
505
506 BEGIN
507
508 l_scrap_filter_clause := ' AND fact.source = 1 ';
509
510 RETURN l_scrap_filter_clause;
511 END get_scrap_filter_clause;
512
513
514
515 /* get_item_flag_where_clause
516
517 Return the where clause for OPI specific Item cat flag
518 */
519 FUNCTION get_item_flag_where_clause(p_trend IN VARCHAR2 := 'N')
520 RETURN VARCHAR2
521 IS
522 l_flag_where_clause VARCHAR2 (200) := '';
523
524 BEGIN
525
526 l_flag_where_clause := 'AND fact.item_cat_flag = :OPI_ITEM_CAT_FLAG ';
527
528 RETURN l_flag_where_clause;
529
530 END get_item_flag_where_clause;
531
532
533
534 /* get_rsc_flag_where_clause
535
536 Return the where clause for OPI specific Resource flag
537 */
538 FUNCTION get_rsc_flag_where_clause(p_trend IN VARCHAR2 := 'N')
539 RETURN VARCHAR2
540 IS
541 l_flag_where_clause VARCHAR2 (200) := '';
542
543 BEGIN
544
545 l_flag_where_clause := 'AND fact.RESOURCE_LEVEL_FLAG = :OPI_RESOURCE_LEVEL_FLAG';
546
547
548 RETURN l_flag_where_clause;
549
550 END get_rsc_flag_where_clause;
551
552 /* get_inv_val_flag_where_clause
553
554 Return the where clause for Inventory Value specific aggregation
555 level flag flag
556 */
557 FUNCTION get_inv_val_flag_where_clause(p_trend IN VARCHAR2 := 'N')
558 RETURN VARCHAR2
559 IS
560 l_flag_where_clause VARCHAR2 (200) := '';
561
562 BEGIN
563
564 l_flag_where_clause := 'AND fact.aggregation_level_flag = :OPI_AGGREGATION_LEVEL_FLAG ';
565
566 RETURN l_flag_where_clause;
567
568 END get_inv_val_flag_where_clause;
569
570 FUNCTION get_rtx_flag_where_clause(p_trend IN VARCHAR2 := 'N')
571 RETURN VARCHAR2
572 IS
573 l_flag_where_clause VARCHAR2 (200) := '';
574
575 BEGIN
576
577 l_flag_where_clause := 'AND fact.agg_level = :OPI_RTX_AGG_LEVEL_FLAG ';
578
579 RETURN l_flag_where_clause;
580
581 END get_rtx_flag_where_clause;
582
583 FUNCTION get_rtp_flag_where_clause(p_trend IN VARCHAR2 := 'N')
584 RETURN VARCHAR2
585 IS
586 l_flag_where_clause VARCHAR2 (200) := '';
587
588 BEGIN
589
590 l_flag_where_clause := 'AND fact.agg_level = :OPI_RTP_AGG_LEVEL_FLAG ';
591
592 RETURN l_flag_where_clause;
593
594 END get_rtp_flag_where_clause;
595
596 /* get_wms_c_utz_item_where_cl */
597 FUNCTION get_wms_c_utz_item_where_cl (p_trend IN VARCHAR2 := 'N')
598 RETURN VARCHAR2
599 IS
600 l_flag_where_clause VARCHAR2 (200) := '';
601
602 BEGIN
603
604 l_flag_where_clause :=
605 ' AND fact.aggregation_level_flag = :OPI_WMS_CURR_UTZ_ITEM_FLAG
606 ';
607
608 RETURN l_flag_where_clause;
609
610 END get_wms_c_utz_item_where_cl;
611
612 /* get_wms_c_utz_sub_where_cl */
613 FUNCTION get_wms_c_utz_sub_where_cl (p_trend IN VARCHAR2 := 'N')
614 RETURN VARCHAR2
615 IS
616 l_flag_where_clause VARCHAR2 (200) := '';
617
618 BEGIN
619
620 l_flag_where_clause :=
621 ' AND fact.aggregation_level_flag = :OPI_WMS_CURR_UTZ_SUB_FLAG
622 ';
623
624 RETURN l_flag_where_clause;
625
626 END get_wms_c_utz_sub_where_cl;
627
628 /* get_wms_stor_utz_where_cl */
629 FUNCTION get_wms_stor_utz_where_cl (p_trend IN VARCHAR2 := 'N')
630 RETURN VARCHAR2
631 IS
632 l_flag_where_clause VARCHAR2 (200) := '';
633
634 BEGIN
635
636 l_flag_where_clause :=
637 ' AND fact.aggregation_level_flag = :OPI_WMS_STOR_UTZ_FLAG
638 ';
639
640 RETURN l_flag_where_clause;
641
642 END get_wms_stor_utz_where_cl;
643
644 --
645 -- DBI 7.1 - Functions for Pick and OP Exceptions Region
646 --
647 /* Function: get_pex_flag_where_clause
648 Description: return bind variable for agg level in Pick Exception Report*/
649
650 FUNCTION get_pex_flag_where_clause(p_trend IN VARCHAR2 := 'N')
651 RETURN VARCHAR2
652 IS
653 l_flag_where_clause VARCHAR2 (200) := '';
654
655 BEGIN
656
657 l_flag_where_clause := 'AND fact.agg_level = :OPI_PEX_AGG_LEVEL_FLAG ';
658
659 RETURN l_flag_where_clause;
660
661 END get_pex_flag_where_clause;
662
663 /* Function: get_per_flag_where_clause
664 Description: return bind variable for agg level in Pick Ex By Reason*/
665 FUNCTION get_per_flag_where_clause(p_trend IN VARCHAR2 := 'N')
666 RETURN VARCHAR2
667 IS
668 l_flag_where_clause VARCHAR2 (200) := '';
669
670 BEGIN
671
672 l_flag_where_clause := 'AND fact.agg_level = :OPI_PER_AGG_LEVEL_FLAG ';
673
674 RETURN l_flag_where_clause;
675
676 END get_per_flag_where_clause;
677
678 /* Function: get_opp_flag_where_clause
679 Description: return bind variable for agg level in OP Performance */
680 FUNCTION get_opp_flag_where_clause(p_trend IN VARCHAR2 := 'N')
681 RETURN VARCHAR2
682 IS
683 l_flag_where_clause VARCHAR2 (200) := '';
684
685 BEGIN
686
687 l_flag_where_clause := 'AND fact.agg_level = :OPI_OPP_AGG_LEVEL_FLAG ';
688
689 RETURN l_flag_where_clause;
690
691 END get_opp_flag_where_clause;
692
693 /* Function: get_oper_flag_where_clause
694 Description: return bind variable for agg level in OP Exc by Reason */
695 FUNCTION get_oper_flag_where_clause(p_trend IN VARCHAR2 := 'N')
696 RETURN VARCHAR2
697 IS
698 l_flag_where_clause VARCHAR2 (200) := '';
699
700 BEGIN
701
702 l_flag_where_clause := 'AND fact.agg_level = :OPI_OPER_AGG_LEVEL_FLAG ';
703
704 RETURN l_flag_where_clause;
705
706 END get_oper_flag_where_clause;
707
708 /* get_curr_inv_exp_where_cl */
709 FUNCTION get_curr_inv_exp_where_cl (p_trend IN VARCHAR2 := 'N')
710 RETURN VARCHAR2
711 IS
712 -- {
713 l_flag_where_clause VARCHAR2 (200) := '';
714 -- }
715 BEGIN
716 -- {
717 l_flag_where_clause :=
718 ' AND fact.aggregation_level_flag = :OPI_CURR_INV_EXP_AGG_FLAG
719 ';
720
721 RETURN l_flag_where_clause;
722 -- }
723 END get_curr_inv_exp_where_cl;
724
725 /* get_prod_cons_where_cl */
726 FUNCTION get_prod_cons_where_cl (p_trend IN VARCHAR2 := 'N')
727 RETURN VARCHAR2
728 IS
729 -- {
730 l_flag_where_clause VARCHAR2 (200) := '';
731 -- }
732 BEGIN
733 --{
734 l_flag_where_clause :=
735 ' AND fact.aggregation_level_flag = :OPI_PROD_CONS_AGG_FLAG
736 ';
737
738 RETURN l_flag_where_clause;
739 -- }
740 END get_prod_cons_where_cl;
741
742 /* get_cogs_ship_where_cl */
743 FUNCTION get_cogs_ship_where_cl (p_trend IN VARCHAR2 := 'N')
744 RETURN VARCHAR2
745 IS
746 -- {
747 l_flag_where_clause VARCHAR2 (200) := '';
748 -- }
749 BEGIN
750 -- {
751 l_flag_where_clause :=
752 ' AND fact.aggregation_level_flag = :OPI_COGS_SHIP_AGG_FLAG
753 ';
754
755 RETURN l_flag_where_clause;
756 -- }
757 END get_cogs_ship_where_cl;
758
759 /* Function: get_trx_flag_where_clause
760 Description: return bind variable for agg level in Scrap By Reason*/
761 FUNCTION get_trx_reason_where_clause(p_trend IN VARCHAR2 := 'N')
762 RETURN VARCHAR2
763 IS
764 l_flag_where_clause VARCHAR2 (200) := '';
765
766 BEGIN
767
768 l_flag_where_clause := 'AND fact.agg_level = :OPI_TRX_REASON_FLAG ';
769
770 RETURN l_flag_where_clause;
771
772 END get_trx_reason_where_clause;
773
774 /* DBI 7.0 changes for Cycle Count
775 Function: get_cca_flag_where_clause
776 Description: return the where clause for OPI specific CCA flag
777 */
778 FUNCTION get_cca_flag_where_clause(p_trend IN VARCHAR2 := 'N')
779 RETURN VARCHAR2
780 IS
781 l_flag_where_clause VARCHAR2(200) := ' ';
782 BEGIN
783 l_flag_where_clause := 'AND fact.aggregation_level_flag = :OPI_CCA_LEVEL_FLAG';
784
785 RETURN l_flag_where_clause;
786
787 END get_cca_flag_where_clause;
788
789 /*
790 adwajan 26-JAN-2004
791 DBI 7.0 changes for Product Cost Management: Product Gross Margin
792 Function: get_prdcat_cust_where_clause
793 Description: return the where clause for PRD_CUST fag value
794 */
795 FUNCTION get_prdcat_cust_where_clause(p_trend IN VARCHAR2 := 'N')
796 RETURN VARCHAR2
797 IS
798 l_flag_where_clause VARCHAR2 (200) := '';
799 BEGIN
800 l_flag_where_clause := 'AND fact.CUSTOMER_ITEM_CAT_FLAG = :OPI_PRDCAT_CUST_FLAG';
801 RETURN l_flag_where_clause;
802 END get_prdcat_cust_where_clause;
803
804 /* get_security_where_clauses
805
806 For OPI, this is quite simple.
807 */
808
809 FUNCTION get_security_where_clauses(p_org_value IN VARCHAR2, p_trend IN VARCHAR2 := 'N')
810 RETURN VARCHAR2
811 IS
812
813 l_sec_where_clause VARCHAR2(1000):='';
814
815 BEGIN
816
817 if(p_org_value is null or p_org_value = '' or p_org_value = 'All') then
818
819 l_sec_where_clause :=
820 ' AND (EXISTS
821 (SELECT 1
822 FROM org_access o
823 WHERE o.responsibility_id = fnd_global.resp_id
824 AND o.resp_application_id = fnd_global.resp_appl_id
825 AND o.organization_id = fact.organization_id)
826 OR EXISTS
827 (SELECT 1
828 FROM mtl_parameters org
829 WHERE org.organization_id = fact.organization_id
830 AND NOT EXISTS
831 (SELECT 1
832 FROM org_access ora
833 WHERE org.organization_id = ora.organization_id))) ';
834
835 end if;
836
837 return l_sec_where_clause;
838
839 END get_security_where_clauses;
840
841
842 /*++++++++++++++++++++++++++++++++++++++++*/
843 /* Functions to get the MV
844 /*++++++++++++++++++++++++++++++++++++++++*/
845 /* get_mv
846
847 Gets the MV for the rack concerned.
848
849
850 For 6.0:
851 For OPI, there is only one MV per rack.
852
853 The p_mv_set parameter is used to determine which MV is being
854 used i.e. when p_mv_set:
855 'SCR' - Scrap rack, so use opi_scrap_sum_mv
856 'MUV' - Material usage variance, so use opi_mtl_var_sum_mv
857 'MCV' - Material cost variance, so use opi_mfg_var_sum_mv
858 'CUV' - Current unrecognized variance, so use opi_dbi_curr_unrec_var_f
859 'RSUT'- Resorce Utilization
860 'RSVR'- Resource Variance
861 'RSEF'- Resource Efficiency
862
863
864 For 7.0:
865 'INV_VAL' - Inventory value rack, so use opi_inv_val_sum_mv
866 'ONH' - Onhand Inventory
867 'INT' - Intransit Inventory
868 Intoduce two levels for Cycle Count Reports
869 'CCAC' - Reports that do not need UOM description
870 'CCAD' - Report that need UOM description.
871 In this case a join with UOM dimension is also added
872
873 For 7.1:
874 'RTX' - WMS - Receiving
875 'RTP' - WMS - Putaways
876 'WMS_CURR_UTZ_ITEM' - Current Utilization (Item)
877 'WMS_CURR_UTZ_SUB' - Current Utilization (Subinventory)
878 'WMS_STOR_UTZ' - Warehouse Storage utilization
879 'PEX' - WMS - Pick Exceptions
880 'PER' - WMS - Pick Exceptions By Reason
881 'OPP' - WMS - OP Performance
882 'OPER'- WMS - OP Exceptions By Reason
883 */
884
885 FUNCTION get_mv (p_mv_set IN VARCHAR2,
886 p_mv_level_flag IN VARCHAR2,
887 p_view_by IN VARCHAR2,
888 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
889 RETURN VARCHAR2
890
891 IS
892
893 l_mv_name VARCHAR2(2000) := '';
894
895 BEGIN
896
897 -- For OPI, we only need to have one MV per rack. We do not use
898 -- POAs bitmap capability for DBI 6.0 since we have different
899 -- flags for hitting differently aggregated rows.
900
901
902 -- Based on the program calling, use different MVs
903 l_mv_name :=
904 (CASE p_mv_set
905 WHEN 'SCR' THEN 'opi_scrap_sum_mv'
906 WHEN 'MUV' THEN 'opi_mtl_var_sum_mv'
907 WHEN 'MCV' THEN 'opi_mfg_var_sum_mv'
908 WHEN 'CUV' THEN 'opi_dbi_curr_unrec_var_f'
909 WHEN 'RSUT' THEN 'opi_dbi_res_utl_mv'
910 WHEN 'RSVR' THEN 'opi_dbi_res_var_mv'
911 WHEN 'RSEF' THEN 'opi_dbi_res_var_mv'
912 WHEN 'INV_VAL' THEN 'opi_inv_val_sum_mv'
913 WHEN 'INV_VAL_UOM' THEN 'opi_inv_val_sum_mv'
914 WHEN 'ONH' THEN 'opi_inv_val_sum_mv'
915 WHEN 'INT' THEN 'opi_inv_val_sum_mv'
916 WHEN 'CCAC' THEN 'opi_inv_cca_sum_mv'
917 WHEN 'RTX' THEN 'opi_wms_002_mv'
918 WHEN 'RTP' THEN 'opi_wms_001_mv'
919 WHEN 'CCAD' THEN 'opi_inv_cca_sum_mv'
920 WHEN 'PGM' THEN select_mv(p_mv_set => p_mv_set,
921 p_mv_level_flag => p_mv_level_flag,
922 p_view_by => p_view_by,
923 p_dim_map => p_dim_map)
924 WHEN 'WMS_CURR_UTZ_ITEM' THEN 'opi_dbi_wms_curr_utz_item_f'
925 WHEN 'WMS_CURR_UTZ_SUB' THEN 'opi_dbi_wms_curr_utz_sub_f'
926 WHEN 'WMS_STOR_UTZ' THEN 'opi_wms_004_mv'
927 WHEN 'PEX' THEN 'opi_wms_006_mv'
928 WHEN 'PER' THEN 'opi_wms_007_mv'
929 WHEN 'OPP' THEN 'opi_wms_008_mv'
930 WHEN 'OPER' THEN 'opi_wms_009_mv'
931 WHEN 'OTP' THEN 'opi_ontime_prod_001_mv'
932 WHEN 'CPD' THEN 'opi_curr_prod_del_001_mv'
933 WHEN 'CURR_INV_EXP' THEN 'opi_dbi_curr_inv_exp_f'
934 WHEN 'PROD_CONS' THEN 'opi_jobs_001_mv'
935 WHEN 'COGS' THEN 'opi_cogs_001_mv'
936 WHEN 'CURR_INV_STAT' THEN 'mtl_onhand_quantities'
937 WHEN 'SBR' THEN 'opi_scrap_reason_001_mv'
938 ELSE ''
939 END);
940
941 RETURN l_mv_name;
942
943 END get_mv;
944
945
946 /*++++++++++++++++++++++++++++++++++++++++*/
947 /* Setting up list of dimensions to track
948 /*++++++++++++++++++++++++++++++++++++++++*/
949 /* init_dim_map
950
951 Initialize the dimension map with all needed dimensions.
952
953 This function needs to keep track of all possible dimensions
954 the DBI 6.0 reports are interested in. The POA utility package
955 get_parameter_values functions looks at the parameter table
956 passed in by PMV. For parameters names for which it finds a
957 matching key in this dimension map table, it records the value.
958 In other words, if the dimension map does not have an entry for
959 ORGANIZATION+ORGANIZATION, then PMV's organization parameter
960 will never be recorded.
961
962 For OPI's DBI 6.0, the needed dimensions levels are:
963 Organization+Organization
964 Item+Eni_item_inv_cat
965 Item+Eni_item_org
966
967 For 7.0, The following dimension levels are added:
968 Organization+Organization_subinventory
969 OPI_INV_CC+OPI_INV_CC_LVL
970 OPI_INV_CC+OPI_INV_CC_CLS_LVL
971
972 For Product Cost Management DBI 7.0 following dimensions are added
973 ITEM+ENI_ITEM_VBH_CAT - Product Category
974 CUSTOMER+FII_CUSTOMERS - Customers
975
976 DBI 7.1 - For Warehouse Management add following dimensions
977 OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL
978 OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL
979
980 DBI 12.0 - For Current Inventory Status report
981 ORGANIZATION+OPI_SUB_LOCATOR_LVL
982
983 DBI 8.0 - For Scrap By Reason Report
984 OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL
985 */
986 PROCEDURE init_dim_map (p_dim_map out NOCOPY
987 poa_dbi_util_pkg.poa_dbi_dim_map,
988 p_mv_set IN VARCHAR2)
989 IS
990
991 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
992
993 BEGIN
994
995 -- Category dimension level
996 l_dim_rec.col_name := get_col_name ('ITEM+ENI_ITEM_INV_CAT');
997 l_dim_rec.view_by_table := get_table ('ITEM+ENI_ITEM_INV_CAT');
998 l_dim_rec.bmap := CATEGORY_BMAP;
999 l_dim_rec.generate_where_clause := 'Y';
1000 p_dim_map('ITEM+ENI_ITEM_INV_CAT') := l_dim_rec;
1001
1002
1003 -- Item dimension level
1004 l_dim_rec.col_name := get_col_name ('ITEM+ENI_ITEM_ORG');
1005 l_dim_rec.view_by_table := get_table ('ITEM+ENI_ITEM_ORG');
1006 l_dim_rec.bmap := ITEM_BMAP;
1007 l_dim_rec.generate_where_clause := 'Y';
1008 p_dim_map('ITEM+ENI_ITEM_ORG') := l_dim_rec;
1009
1010
1011 -- Organzation dimension level
1012 l_dim_rec.col_name := get_col_name ('ORGANIZATION+ORGANIZATION');
1013 l_dim_rec.view_by_table := get_table('ORGANIZATION+ORGANIZATION');
1014 l_dim_rec.bmap := ORG_BMAP;
1015 l_dim_rec.generate_where_clause := 'Y';
1016 p_dim_map('ORGANIZATION+ORGANIZATION') := l_dim_rec;
1017
1018 -- Resource dimension level
1019 l_dim_rec.col_name := get_col_name ('RESOURCE+ENI_RESOURCE');
1020 l_dim_rec.view_by_table := get_table('RESOURCE+ENI_RESOURCE');
1021 l_dim_rec.bmap := RES_BMAP;
1022 l_dim_rec.generate_where_clause := 'Y';
1023 p_dim_map('RESOURCE+ENI_RESOURCE') := l_dim_rec;
1024
1025 -- Resource Group dimension level
1026 l_dim_rec.col_name := get_col_name ('RESOURCE+ENI_RESOURCE_GROUP');
1027 l_dim_rec.view_by_table := get_table('RESOURCE+ENI_RESOURCE_GROUP');
1028 l_dim_rec.bmap := RES_GRP_BMAP;
1029 l_dim_rec.generate_where_clause := 'Y';
1030 p_dim_map('RESOURCE+ENI_RESOURCE_GROUP') := l_dim_rec;
1031
1032 -- Resource Department dimension level
1033 l_dim_rec.col_name := get_col_name ('RESOURCE+ENI_RESOURCE_DEPARTMENT');
1034 l_dim_rec.view_by_table := get_table('RESOURCE+ENI_RESOURCE_DEPARTMENT');
1035 l_dim_rec.bmap := RES_DEPT_BMAP;
1036 l_dim_rec.generate_where_clause := 'Y';
1037 p_dim_map('RESOURCE+ENI_RESOURCE_DEPARTMENT') := l_dim_rec;
1038
1039 -- Subinventory Dimension level
1040 l_dim_rec.col_name :=
1041 get_col_name ('ORGANIZATION+ORGANIZATION_SUBINVENTORY');
1042 l_dim_rec.view_by_table :=
1043 get_table('ORGANIZATION+ORGANIZATION_SUBINVENTORY');
1044 l_dim_rec.bmap := ORG_SUB_BMAP;
1045 l_dim_rec.generate_where_clause := 'Y';
1046 p_dim_map('ORGANIZATION+ORGANIZATION_SUBINVENTORY') := l_dim_rec;
1047
1048 -- Cycle Count Header dimension level
1049 l_dim_rec.col_name := get_col_name ('OPI_INV_CC+OPI_INV_CC_LVL');
1050 l_dim_rec.view_by_table := get_table('OPI_INV_CC+OPI_INV_CC_LVL');
1051 l_dim_rec.bmap := CYCLE_COUNT_HEADER_BMAP;
1052 l_dim_rec.generate_where_clause := 'Y';
1053 p_dim_map('OPI_INV_CC+OPI_INV_CC_LVL') := l_dim_rec;
1054
1055 -- Cycle Count Class dimension level
1056 l_dim_rec.col_name := get_col_name ('OPI_INV_CC+OPI_INV_CC_CLS_LVL');
1057 l_dim_rec.view_by_table := get_table('OPI_INV_CC+OPI_INV_CC_CLS_LVL');
1058 l_dim_rec.bmap := CYCLE_COUNT_CLASS_BMAP;
1059 l_dim_rec.generate_where_clause := 'Y';
1060 p_dim_map('OPI_INV_CC+OPI_INV_CC_CLS_LVL') := l_dim_rec;
1061
1062 -- Product Category Dimension Level
1063 l_dim_rec.col_name := get_col_name ('ITEM+ENI_ITEM_VBH_CAT');
1064 l_dim_rec.view_by_table := get_table('ITEM+ENI_ITEM_VBH_CAT');
1065 --l_dim_rec.bmap := RES_DEPT_BMAP;
1066 l_dim_rec.generate_where_clause := 'Y';
1067 p_dim_map('ITEM+ENI_ITEM_VBH_CAT') := l_dim_rec;
1068
1069
1070 -- Customer dimension level
1071 l_dim_rec.col_name := get_col_name ('CUSTOMER+FII_CUSTOMERS');
1072 l_dim_rec.view_by_table := get_table('CUSTOMER+FII_CUSTOMERS');
1073 --l_dim_rec.bmap := RES_DEPT_BMAP;
1074 l_dim_rec.generate_where_clause := 'Y';
1075 p_dim_map('CUSTOMER+FII_CUSTOMERS') := l_dim_rec;
1076
1077 -- Reason Code Dimension Level
1078 l_dim_rec.col_name := get_col_name ('OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL');
1079 l_dim_rec.view_by_table := get_table('OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL');
1080 l_dim_rec.bmap := REASON_CODE_BMAP;
1081 l_dim_rec.generate_where_clause := 'Y';
1082 p_dim_map('OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL') := l_dim_rec;
1083
1084 -- Operation Plan Name Dimension Level
1085 l_dim_rec.col_name := get_col_name ('OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL');
1086 l_dim_rec.view_by_table := get_table('OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL');
1087 l_dim_rec.bmap := OP_PLAN_BMAP;
1088 l_dim_rec.generate_where_clause := 'Y';
1089 p_dim_map('OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL') := l_dim_rec;
1090
1091 -- Locator Dimension Level
1092 l_dim_rec.col_name := get_col_name ('ORGANIZATION+OPI_SUB_LOCATOR_LVL');
1093 l_dim_rec.view_by_table := get_table ('ORGANIZATION+OPI_SUB_LOCATOR_LVL');
1094 l_dim_rec.bmap := LOCATOR_BMAP;
1095 l_dim_rec.generate_where_clause := 'Y';
1096 p_dim_map('ORGANIZATION+OPI_SUB_LOCATOR_LVL') := l_dim_rec;
1097
1098 -- Item Grade dimension level
1099 l_dim_rec.col_name := get_col_name ('OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL');
1100 l_dim_rec.view_by_table := get_table ('OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL');
1101 l_dim_rec.bmap := ITEM_GRADE_BMAP;
1102 l_dim_rec.generate_where_clause := 'Y';
1103 p_dim_map('OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL') := l_dim_rec;
1104
1105 -- Item Lot dimension level
1106 l_dim_rec.col_name := get_col_name ('OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL');
1107 l_dim_rec.view_by_table := get_table ('OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL');
1108 l_dim_rec.bmap := ITEM_LOT_BMAP;
1109 l_dim_rec.generate_where_clause := 'Y';
1110 p_dim_map('OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL') := l_dim_rec;
1111
1112 -- Transaction Reason Dimension Level
1113 l_dim_rec.col_name := get_col_name ('OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL');
1114 l_dim_rec.view_by_table := get_table('OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL');
1115 l_dim_rec.bmap := TRX_REASON_BMAP;
1116 l_dim_rec.generate_where_clause := 'Y';
1117 p_dim_map('OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL') := l_dim_rec;
1118
1119
1120 -- Exceptions
1121
1122 -- For the WMS Current Capacity subinventory query, do not
1123 -- include the Item and Category as part of the where clause
1124 -- since those two dimensions are not part of the subinventory
1125 -- capacity table.
1126 IF (p_mv_set = 'WMS_CURR_UTZ_SUB') THEN
1127 p_dim_map(C_DIMNAME_ITEM).generate_where_clause := 'N';
1128 p_dim_map(C_DIMNAME_INV_CAT).generate_where_clause := 'N';
1129 END IF;
1130
1131 -- For Current Inventory Status, do not include the Item Category
1132 -- and organization where clause as they are display only parameters.
1133 IF (p_mv_set = 'CURR_INV_STAT') THEN
1134 p_dim_map(C_DIMNAME_ORG).generate_where_clause := 'N';
1135 p_dim_map(C_DIMNAME_INV_CAT).generate_where_clause := 'N';
1136 END IF;
1137
1138 END init_dim_map;
1139
1140
1141 /*++++++++++++++++++++++++++++++++++++++++++++*/
1142 /* Reinitialize the column name in the dim_map
1143 /*++++++++++++++++++++++++++++++++++++++++++++*/
1144 /*
1145 This Function is called from process_parameters if the p_mv_set is related to
1146 Product Gross Margin. If the MV determined by select_mv is the Inline View the
1147 column name is parent_id
1148 */
1149 PROCEDURE reinit_dim_map (p_dim_map in out NOCOPY
1150 poa_dbi_util_pkg.poa_dbi_dim_map,
1151 p_mv IN VARCHAR2,
1152 p_mv_set IN VARCHAR2)
1153 IS
1154
1155 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
1156 BEGIN
1157 IF (p_mv <> 'OPI_PGM_SUM_MV' AND p_mv <> 'OPI_PGM_CAT_MV' AND p_mv_set = 'PGM') THEN
1158 l_dim_rec := p_dim_map('ITEM+ENI_ITEM_VBH_CAT');
1159 l_dim_rec.col_name := 'parent_id';
1160 p_dim_map('ITEM+ENI_ITEM_VBH_CAT') := l_dim_rec;
1161 END IF;
1162 END reinit_dim_map;
1163
1164 /*++++++++++++++++++++++++++++++++++++++++*/
1165 /* Dimension level join tables and columns
1166 /*++++++++++++++++++++++++++++++++++++++++*/
1167 /* get_col_name
1168
1169 Get the column name of the viewby join tables that the query will
1170 have to join to.
1171
1172
1173 DBI 7.0 Changes for Cycle Count Reports
1174 Cycle Count - Added cycle_count_header_id
1175 Cycle Count Class - Added cycle_count_class_id
1176
1177 DBI 7.0 Changes for Product Cost Management: Product Gross Margin Reports
1178 ITEM+ENI_ITEM_VBH_CAT - item_category_id
1179 CUSTOMER+FII_CUSTOMERS - customer_id
1180
1181 DBI 7.1 - For Warehouse Management add following dimensions
1182 OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL
1183 OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL
1184
1185 DBI 12.0 - For Current Inventory Status
1186 ORGANIZATION+OPI_SUB_LOCATOR_LVL
1187 OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL
1188 OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL
1189
1190 DBI 8.0 - For Scrap By Reason Report
1191 OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL
1192
1193 */
1194 FUNCTION get_col_name (p_dim_name VARCHAR2)
1195 RETURN VARCHAR2
1196 IS
1197
1198 l_col_name VARCHAR2(100);
1199
1200 BEGIN
1201
1202 l_col_name :=
1203 (CASE p_dim_name
1204 WHEN 'ORGANIZATION+ORGANIZATION' THEN 'organization_id'
1205 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN 'inv_category_id'
1206 WHEN 'ITEM+ENI_ITEM_ORG' THEN 'item_org_id'
1207 WHEN 'RESOURCE+ENI_RESOURCE' THEN 'resource_org_id'
1208 WHEN 'RESOURCE+ENI_RESOURCE_GROUP' THEN 'resource_group_id'
1209 WHEN 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN 'resource_department_id'
1210 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN 'subinventory_code'
1211 WHEN 'OPI_INV_CC+OPI_INV_CC_LVL' THEN 'cycle_count_header_id'
1212 WHEN 'OPI_INV_CC+OPI_INV_CC_CLS_LVL' THEN 'cycle_count_class_id'
1213 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN 'item_category_id'
1214 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN 'customer_id'
1215 WHEN 'OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL'
1216 THEN 'reason_id'
1217 WHEN 'OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL'
1218 THEN 'operation_plan_id'
1219 WHEN 'ORGANIZATION+OPI_SUB_LOCATOR_LVL' THEN 'locator_id'
1220 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL' THEN 'grade_code'
1221 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL' THEN 'lot_number'
1222 WHEN 'OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL'
1223 THEN 'scrap_reason_id'
1224 ELSE ''
1225 END);
1226
1227 RETURN l_col_name;
1228
1229 END get_col_name;
1230
1231 /* get_table
1232
1233 Return the join table based on the dimension
1234
1235 DBI 7.0 Changes for Cycle Count Reports
1236 Cycle Count - opi_inv_cc_lvl_v
1237 Cycle Count Class - opi_inv_cc_lvl_v
1238
1239 DBI 7.0 Changes for Product Cost Management Reports
1240 Product Category - eni_item_vbh_cat_v
1241 Customer - fii_customers_v
1242
1243 DBI 7.1 - For Warehouse Management add following
1244 Exception Reason Code - opi_wms_task_exc_reasons_lvl_v
1245 Operation Plan - opi_wms_op_plan_name_lvl_v
1246
1247 DBI 12.0 - For Current Inventory Status add following:
1248 Locator - OPI_LOCATORS_V
1249
1250 DBI 8.0 - For Scrap By Reason Report
1251 Transaction Reason - OPI_MFG_MTL_TRX_REASONS_LVL_V
1252 */
1253 FUNCTION get_table (p_dim_name VARCHAR2)
1254 RETURN VARCHAR2
1255 IS
1256 l_table VARCHAR2(4000);
1257
1258 BEGIN
1259
1260 l_table :=
1261 (CASE p_dim_name
1262 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN 'eni_item_inv_cat_v'
1263 WHEN 'ITEM+ENI_ITEM_ORG' THEN 'eni_item_org_v '
1264 WHEN 'ORGANIZATION+ORGANIZATION' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))'
1265 WHEN 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN
1266 'eni_resource_department_v'
1267 WHEN 'RESOURCE+ENI_RESOURCE_GROUP' THEN 'eni_resource_group_v'
1268 WHEN 'RESOURCE+ENI_RESOURCE' THEN 'eni_resource_v'
1269 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN 'opi_subinventories_v'
1270 WHEN 'OPI_INV_CC+OPI_INV_CC_LVL' THEN 'opi_inv_cc_lvl_v'
1271 WHEN 'OPI_INV_CC+OPI_INV_CC_CLS_LVL' THEN 'opi_inv_cc_cls_v'
1272 --WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN 'eni_item_vbh_cat_v'
1273 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN 'eni_item_vbh_nodes_v'
1274 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN 'fii_customers_v'
1275 WHEN 'OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL' THEN
1276 'opi_wms_task_exc_reasons_lvl_v'
1277 WHEN 'OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL' THEN
1278 'opi_wms_op_plan_name_lvl_v'
1279 WHEN 'ORGANIZATION+OPI_SUB_LOCATOR_LVL' THEN
1280 'opi_locators_v'
1281 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL' THEN
1282 'opi_inv_item_grade_lvl_v'
1283 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL' THEN
1284 'opi_inv_item_lot_lvl_v'
1285 WHEN 'OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL' THEN
1286 'opi_mfg_mtl_trx_reasons_lvl_v'
1287 ELSE ''
1288 END);
1289
1290 RETURN l_table;
1291
1292 END get_table;
1293
1294 /* Function: get_join_info
1295 DBI 7.0 Changes for Cycle Count Reports
1296 1. Cycle Count and Cycle Count Class to l_join_rec.column_name
1297 2. Add 'CCAD' to get join info for UOM
1298
1299 DBI 7.0 Changes for Product Cost Management: Product Gross Margin Reports
1300 1. Add Product Category and Customer dimension to l_join_rec.column_name
1301 2. Add 'PGM' to get join info for UOM
1302 */
1303 PROCEDURE get_join_info (p_view_by IN varchar2,
1304 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
1305 x_join_tbl OUT NOCOPY
1306 poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
1307 p_mv_set IN VARCHAR2)
1308 IS
1309 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1310
1311 BEGIN
1312
1313 -- reinitialize the join table
1314 x_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1315
1316 -- If the view by column is not in the bitmap, then
1317 -- there is nothing to join to. Can this ever be true?
1318 IF (NOT p_dim_map.exists(p_view_by)) THEN
1319 RETURN;
1320 END IF;
1321
1322 -- Otherwise, join to a table
1323 -- The view by table
1324 l_join_rec.table_name := p_dim_map(p_view_by).view_by_table;
1325 l_join_rec.table_alias := 'v';
1326 -- the fact column to join to
1327 l_join_rec.fact_column := p_dim_map(p_view_by).col_name;
1328
1329 -- depending on the dimension level, select the appropriate
1330 -- join table column name
1331 l_join_rec.column_name :=
1332 (CASE p_view_by
1333 WHEN 'ORGANIZATION+ORGANIZATION' THEN
1334 'id'
1335 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN
1336 'id'
1337 WHEN 'ITEM+ENI_ITEM_ORG' THEN
1338 'id'
1339 WHEN 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN
1340 'id'
1341 WHEN 'RESOURCE+ENI_RESOURCE_GROUP' THEN
1342 'id'
1343 WHEN 'RESOURCE+ENI_RESOURCE' THEN
1344 'id'
1345 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN
1346 'id'
1347 WHEN 'OPI_INV_CC+OPI_INV_CC_LVL' THEN
1348 'id'
1349 WHEN 'OPI_INV_CC+OPI_INV_CC_CLS_LVL' THEN
1350 'id'
1351 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
1352 'id'
1353 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN
1354 'id'
1355 WHEN 'OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL' THEN
1356 'id'
1357 WHEN 'OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL' THEN
1358 'id'
1359 WHEN 'ORGANIZATION+OPI_SUB_LOCATOR_LVL' THEN
1360 'id'
1361 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL' THEN
1362 'id'
1363 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL' THEN
1364 'id'
1365 WHEN 'OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL' THEN
1366 'id'
1367 ELSE
1368 ''
1369 END);
1370
1371 l_join_rec.dim_outer_join :=
1372 (CASE p_view_by
1373
1374 WHEN 'ORGANIZATION+ORGANIZATION' THEN
1375 'N'
1376 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN
1377 'N'
1378 WHEN 'ITEM+ENI_ITEM_ORG' THEN
1379 'N'
1380 ELSE
1381 'N'
1382 END);
1383
1384 IF ( (p_view_by = 'ITEM+ENI_ITEM_VBH_CAT') AND p_mv_set = 'PGM') THEN
1385 l_join_rec.additional_where_clause := 'v.parent_id = v.child_id';
1386 END IF;
1387 -- Add the join table
1388 x_join_tbl.extend;
1389 x_join_tbl(x_join_tbl.count) := l_join_rec;
1390
1391 -- Get the uom join
1392 IF ( (p_view_by = 'ITEM+ENI_ITEM_ORG') AND
1393 ((p_mv_set = 'SCR') OR (p_mv_set = 'CUV') OR (p_mv_set = 'ONH') OR
1394 (p_mv_set = 'INT') OR (p_mv_set = 'MCV') OR (p_mv_set = 'PGM') OR
1395 (p_mv_set = 'CCAD') OR (p_mv_set = 'RTX') OR
1396 (p_mv_set = 'RTP') OR (p_mv_set = 'WMS_CURR_UTZ_SUB') OR
1397 (p_mv_set = 'WMS_CURR_UTZ_ITEM') OR (p_mv_set = 'OTP') OR
1398 (p_mv_set = 'CPD') OR (p_mv_set = 'WMS_STOR_UTZ') OR
1399 (p_mv_set = 'CURR_INV_EXP') OR
1400 (p_mv_set = 'INV_VAL_UOM') OR
1401 (p_mv_set = 'PROD_CONS') OR
1402 (p_mv_set = 'COGS') ) ) THEN
1403 l_join_rec.table_name := 'mtl_units_of_measure_vl';
1404 l_join_rec.table_alias := 'v2';
1405 l_join_rec.fact_column :='uom_code';
1406 l_join_rec.column_name := 'uom_code';
1407 l_join_rec.dim_outer_join := 'N';
1408
1409 x_join_tbl.extend;
1410 x_join_tbl(x_join_tbl.count) := l_join_rec;
1411 END IF;
1412
1413 -- The UOM join for Current Inventory Status is special
1414 IF (p_mv_set = 'CURR_INV_STAT') THEN
1415 -- {
1416 l_join_rec.table_name := 'mtl_units_of_measure_vl';
1417 l_join_rec.table_alias := 'v2';
1418 l_join_rec.fact_column :='primary_uom_code';
1419 l_join_rec.column_name := 'uom_code';
1420 l_join_rec.dim_outer_join := 'N';
1421
1422 x_join_tbl.extend;
1423 x_join_tbl(x_join_tbl.count) := l_join_rec;
1424
1425 l_join_rec.table_name := 'mtl_units_of_measure_vl';
1426 l_join_rec.table_alias := 'v3';
1427 l_join_rec.fact_column :='secondary_uom_code';
1428 l_join_rec.column_name := 'uom_code';
1429 l_join_rec.dim_outer_join := 'Y';
1430
1431 x_join_tbl.extend;
1432 x_join_tbl(x_join_tbl.count) := l_join_rec;
1433
1434 -- }
1435 END IF;
1436
1437 END get_join_info;
1438
1439
1440 /*++++++++++++++++++++++++++++++++++++++++*/
1441 /* View by information for outer queries
1442 /*++++++++++++++++++++++++++++++++++++++++*/
1443 /*
1444 For the status_sql, get the name of the viewby column.
1445 */
1446 FUNCTION get_view_by_col_name (p_dim_name VARCHAR2)
1447 RETURN VARCHAR2
1448 IS
1449 l_col_name VARCHAR2(60);
1450 BEGIN
1451
1452 l_col_name :=
1453 (CASE p_dim_name
1454 WHEN 'ORGANIZATION+ORGANIZATION' THEN
1455 ' v.value'
1456 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN
1457 ' v.value'
1458 WHEN 'ITEM+ENI_ITEM_ORG' THEN
1459 ' v.value'
1460 WHEN 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN
1461 ' decode(v.value, NULL, ''Unassigned'', v.value)'
1462 WHEN 'RESOURCE+ENI_RESOURCE_GROUP' THEN
1463 ' decode(v.value, NULL, ''Unassigned'', v.value)'
1464 WHEN 'RESOURCE+ENI_RESOURCE' THEN
1465 ' decode(v.value, NULL, ''Unassigned'', v.value)'
1466 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN
1467 ' decode(v.value, NULL, ''Unassigned'', v.value)'
1468 WHEN 'OPI_INV_CC+OPI_INV_CC_LVL' THEN
1469 'v.value'
1470 WHEN 'OPI_INV_CC+OPI_INV_CC_CLS_LVL' THEN
1471 'v.value'
1472 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
1473 ' v.value'
1474 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN
1475 ' v.value'
1476 WHEN 'OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL' THEN
1477 ' v.value'
1478 WHEN 'OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL' THEN
1479 ' v.value'
1480 WHEN 'ORGANIZATION+OPI_SUB_LOCATOR_LVL' THEN
1481 ' v.value'
1482 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL' THEN
1483 ' v.value'
1484 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL' THEN
1485 ' v.value'
1486 WHEN 'OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL' THEN
1487 'v.value'
1488 ELSE ' '
1489 END);
1490
1491 RETURN l_col_name;
1492 END get_view_by_col_name;
1493
1494 FUNCTION get_viewby_select_clause (p_viewby IN VARCHAR2)
1495 RETURN VARCHAR2
1496 IS
1497 l_viewby_sel VARCHAR2(200) := '';
1498 l_view_by_col VARCHAR2 (100) := '';
1499 BEGIN
1500
1501 l_view_by_col := get_view_by_col_name (p_viewby);
1502
1503 l_viewby_sel :=
1504 (CASE p_viewby
1505 WHEN 'ORGANIZATION+ORGANIZATION' THEN
1506 l_view_by_col || ' VIEWBY,
1507 v.id VIEWBYID, '
1508 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN
1509 l_view_by_col || ' VIEWBY,
1510 v.id VIEWBYID, '
1511 WHEN 'ITEM+ENI_ITEM_ORG' THEN
1512 l_view_by_col || ' VIEWBY ,
1513 v.id VIEWBYID, '
1514 WHEN 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN
1515 l_view_by_col || ' VIEWBY ,
1516 v.id VIEWBYID, '
1517 WHEN 'RESOURCE+ENI_RESOURCE_GROUP' THEN
1518 l_view_by_col || ' VIEWBY ,
1519 v.id VIEWBYID, '
1520 WHEN 'RESOURCE+ENI_RESOURCE' THEN
1521 l_view_by_col || ' VIEWBY ,
1522 v.id VIEWBYID, '
1523 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN
1524 l_view_by_col || ' VIEWBY ,
1525 v.id VIEWBYID, '
1526 WHEN 'OPI_INV_CC+OPI_INV_CC_LVL' THEN
1527 l_view_by_col || ' VIEWBY ,
1528 v.id VIEWBYID, '
1529 WHEN 'OPI_INV_CC+OPI_INV_CC_CLS_LVL' THEN
1530 l_view_by_col || ' VIEWBY ,
1531 v.id VIEWBYID, '
1532 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
1533 l_view_by_col || ' VIEWBY,
1534 v.id VIEWBYID, '
1535 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN
1536 l_view_by_col || ' VIEWBY ,
1537 v.id VIEWBYID, '
1538 WHEN 'OPI_WMS_TASK_EXC_REASONS+OPI_WMS_TASK_EXC_REASONS_LVL' THEN
1539 l_view_by_col || ' VIEWBY ,
1540 v.id VIEWBYID, '
1541 WHEN 'OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL' THEN
1542 l_view_by_col || ' VIEWBY ,
1543 v.id VIEWBYID, '
1544 WHEN 'ORGANIZATION+OPI_SUB_LOCATOR_LVL' THEN
1545 l_view_by_col || ' VIEWBY ,
1546 v.id VIEWBYID, '
1547 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_GRADE_LVL' THEN
1548 l_view_by_col || ' VIEWBY ,
1549 v.id VIEWBYID, '
1550 WHEN 'OPI_INV_ITEM_ATTRIB+OPI_INV_ITEM_LOT_LVL' THEN
1551 l_view_by_col || ' VIEWBY ,
1552 v.id VIEWBYID, '
1553 WHEN 'OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL' THEN
1554 l_view_by_col || ' VIEWBY ,
1555 v.id VIEWBYID, '
1556 ELSE ''
1557 END);
1558
1559 return l_viewby_sel;
1560
1561 END get_viewby_select_clause;
1562
1563 /*++++++++++++++++++++++++++++++++++++++++*/
1564 /* MV level aggregation flag
1565 /*++++++++++++++++++++++++++++++++++++++++*/
1566
1567 /* get_mv_level_flag
1568
1569 Return the MV level flag based on what is requested in p_mv_flag_type:
1570 'ITEM_CAT' - Scrap or material usage need item_cat_flag.
1571
1572 DBI 7.0 Changes for Cycle Count Reports
1573 CCA_LEVEL - Cycle Count
1574
1575 DBI 7.0 Changes for Product Cost Management: Product Gross Margin Reports
1576 PRD_CUST - Product Gross Margin Report: Product Category + Customer grouping
1577
1578 DBI 7.1 Changes for Receipt to Putaway Cycle Time Rack for WMS
1579
1580 mochawla - DBI 7.1 changes for Pick and OP Exceptions
1581 WMS_PEX - Pick Exceptions
1582 WMS_PER - Pick Exceptions by Reason
1583 WMS_OPP - OP Performance
1584 WMS_OPER- OP Exceptions by Reason
1585 */
1586 FUNCTION get_mv_level_flag (p_mv_flag_type VARCHAR2,
1587 p_dim_name VARCHAR2,
1588 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
1589 RETURN VARCHAR2
1590 IS
1591
1592 l_mv_level_flag VARCHAR2 (10) := '';
1593
1594 BEGIN
1595
1596 l_mv_level_flag :=
1597 (CASE p_mv_flag_type
1598 WHEN 'ITEM_CAT' THEN
1599 get_item_flag_val (p_dim_name, p_dim_map)
1600 WHEN 'RESOURCE_LEVEL' THEN
1601 get_resource_level_flag_val (p_dim_name, p_dim_map)
1602 WHEN 'INV_VAL_LEVEL' THEN
1603 get_inv_val_aggr_flag (p_dim_name, p_dim_map)
1604 WHEN 'INV_VAL_UOM_LEVEL' THEN
1605 -- Same as 'INV_VAL_LEVEL'
1606 get_inv_val_aggr_flag (p_dim_name, p_dim_map)
1607 WHEN 'CCA_LEVEL' THEN
1608 get_cca_level_flag_val (p_dim_name, p_dim_map)
1609 WHEN 'PRD_CUST' THEN
1610 get_prodcat_cust_flag_val(p_dim_name, p_dim_map)
1611 WHEN 'WMS_RTX' THEN
1612 get_wms_rtx_aggr_flag(p_dim_name, p_dim_map)
1613 WHEN 'WMS_RTP' THEN
1614 get_wms_rtp_aggr_flag(p_dim_name, p_dim_map)
1615 WHEN 'WMS_CURR_UTZ_ITEM_LEVEL' THEN
1616 get_wms_c_utz_item_aggr_flag (p_dim_name, p_dim_map)
1617 WHEN 'WMS_CURR_UTZ_SUB_LEVEL' THEN
1618 get_wms_c_utz_sub_aggr_flag (p_dim_name, p_dim_map)
1619 WHEN 'WMS_STOR_UTZ_LEVEL' THEN
1620 get_wms_stor_utz_aggr_flag (p_dim_name, p_dim_map)
1621 WHEN 'WMS_PEX' THEN
1622 get_wms_pex_aggr_flag(p_dim_name, p_dim_map)
1623 WHEN 'WMS_PER' THEN
1624 get_wms_per_aggr_flag(p_dim_name, p_dim_map)
1625 WHEN 'WMS_OPP' THEN
1626 get_wms_opp_aggr_flag(p_dim_name, p_dim_map)
1627 WHEN 'WMS_OPER' THEN
1628 get_wms_oper_aggr_flag(p_dim_name, p_dim_map)
1629 WHEN 'CURR_INV_EXP_LEVEL' THEN
1630 get_rollup1_aggr_flag (p_dim_name, p_dim_map)
1631 WHEN 'PROD_CONS_LEVEL' THEN
1632 get_rollup1_aggr_flag (p_dim_name, p_dim_map)
1633 WHEN 'COGS_LEVEL' THEN
1634 get_rollup1_aggr_flag (p_dim_name, p_dim_map)
1635 WHEN 'TRX_REASON_LEVEL' THEN
1636 get_trx_reason_aggr_flag(p_dim_name, p_dim_map)
1637 ELSE
1638 ''
1639 END);
1640
1641 RETURN l_mv_level_flag;
1642
1643 END get_mv_level_flag;
1644
1645
1646 /* get_resource_level_flag_val
1647
1648 Compute the item_cat_flag value based on the parameters passed to
1649 determine the aggregation level of the MV rows that the query will
1650 have to run against.
1651 */
1652 FUNCTION get_resource_level_flag_val (p_dim_name IN VARCHAR2,
1653 p_dim_map IN
1654 poa_dbi_util_pkg.poa_dbi_dim_map)
1655 RETURN VARCHAR2
1656 IS
1657
1658 l_resource_level_flag varchar2(1) := '0';
1659
1660 l_org_val VARCHAR2 (120) := NULL;
1661 l_res_val VARCHAR2 (120) := NULL;
1662 l_res_group_val VARCHAR2 (120) := NULL;
1663 l_dept_val VARCHAR2 (120) := NULL;
1664
1665 BEGIN
1666
1667 -- Get the org, item and cat values
1668 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
1669 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
1670 END IF;
1671
1672 IF (p_dim_map.exists ('RESOURCE+ENI_RESOURCE')) THEN
1673 l_res_val := p_dim_map ('RESOURCE+ENI_RESOURCE').value;
1674 END IF;
1675
1676 IF (p_dim_map.exists ('RESOURCE+ENI_RESOURCE_GROUP')) THEN
1677 l_res_group_val := p_dim_map ('RESOURCE+ENI_RESOURCE_GROUP').value;
1678 END IF;
1679
1680 IF (p_dim_map.exists ('RESOURCE+ENI_RESOURCE_DEPARTMENT')) THEN
1681 l_dept_val := p_dim_map ('RESOURCE+ENI_RESOURCE_DEPARTMENT').value;
1682 END IF;
1683
1684
1685 CASE
1686 WHEN p_dim_name = 'ORGANIZATION+ORGANIZATION' THEN
1687 BEGIN
1688 IF l_res_val IS NULL OR
1689 l_res_val = '' OR
1690 l_res_val = 'All' THEN
1691 IF l_dept_val IS NULL OR
1692 l_dept_val = '' OR
1693 l_dept_val = 'All' THEN
1694 IF l_res_group_val IS NULL OR
1695 l_res_group_val = '' OR
1696 l_res_group_val = 'All' THEN
1697 l_resource_level_flag := '7';
1698 ELSE
1699 l_resource_level_flag := '3';
1700 END IF;
1701 ELSIF l_res_group_val IS NULL OR
1702 l_res_group_val = '' OR
1703 l_res_group_val = 'All' THEN
1704 l_resource_level_flag := '5';
1705 END IF;
1706 END IF;
1707 END;
1708
1709 WHEN p_dim_name = 'RESOURCE+ENI_RESOURCE_GROUP' THEN
1710 BEGIN
1711 IF l_res_val IS NULL OR
1712 l_res_val = '' OR
1713 l_res_val = 'All' THEN
1714 IF l_dept_val IS NULL OR
1715 l_dept_val = '' OR
1716 l_dept_val = 'All' THEN
1717 l_resource_level_flag := '3';
1718 END IF;
1719 END IF;
1720 END;
1721
1722 WHEN p_dim_name = 'RESOURCE+ENI_RESOURCE_DEPARTMENT' THEN
1723 BEGIN
1724 IF l_res_val IS NULL OR
1725 l_res_val = '' OR
1726 l_res_val = 'All' THEN
1727 IF l_res_group_val IS NULL OR
1728 l_res_group_val = '' OR
1729 l_res_group_val = 'All' THEN
1730 l_resource_level_flag := '5';
1731 END IF;
1732 END IF;
1733 END;
1734 WHEN p_dim_name = 'RESOURCE+ENI_RESOURCE' THEN
1735 BEGIN
1736 l_resource_level_flag := '0';
1737 END;
1738 ELSE
1739 BEGIN
1740 IF l_res_val IS NULL OR
1741 l_res_val = '' OR
1742 l_res_val = 'All' THEN
1743 IF l_dept_val IS NULL OR
1744 l_dept_val = '' OR
1745 l_dept_val = 'All' THEN
1746 IF l_res_group_val IS NULL OR
1747 l_res_group_val = '' OR
1748 l_res_group_val = 'All' THEN
1749 l_resource_level_flag := '7';
1750 ELSE
1751 l_resource_level_flag := '3';
1752 END IF;
1753 ELSIF l_res_group_val IS NULL OR
1754 l_res_group_val = '' OR
1755 l_res_group_val = 'All' THEN
1756 l_resource_level_flag := '5';
1757 END IF;
1758 END IF;
1759 END;
1760 END CASE;
1761
1762 RETURN l_resource_level_flag;
1763
1764 END get_resource_level_flag_val;
1765
1766
1767 /* get_item_flag_val
1768
1769 Compute the item_cat_flag value based on the parameters passed to
1770 determine the aggregation level of the MV rows that the query will
1771 have to run against.
1772 */
1773 FUNCTION get_item_flag_val (p_dim_name VARCHAR2,
1774 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
1775 RETURN VARCHAR2
1776 IS
1777
1778 l_item_flag varchar2(1);
1779
1780 l_org_val VARCHAR2 (120) := NULL;
1781 l_cat_val VARCHAR2 (120) := NULL;
1782 l_item_val VARCHAR2 (120) := NULL;
1783
1784 BEGIN
1785
1786 -- Get the org, item and cat values
1787 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
1788 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
1789 END IF;
1790
1791 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
1792 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
1793 END IF;
1794
1795 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
1796 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
1797 END IF;
1798
1799
1800 CASE
1801 WHEN p_dim_name = 'ORGANIZATION+ORGANIZATION' THEN
1802 BEGIN
1803 IF l_item_val IS NULL OR
1804 l_item_val = '' OR
1805 l_item_val = 'All' THEN
1806 IF l_cat_val IS NULL OR
1807 l_cat_val = '' OR
1808 l_cat_val = 'All' THEN
1809
1810 l_item_flag := '3';
1811 ELSE
1812 l_item_flag := '1';
1813 END IF;
1814 ELSE
1815 l_item_flag := '0';
1816 END IF;
1817 END;
1818 WHEN p_dim_name = 'ITEM+ENI_ITEM_INV_CAT' THEN
1819 BEGIN
1820 IF l_item_val IS NULL OR
1821 l_item_val = '' OR
1822 l_item_val = 'All' THEN
1823 l_item_flag := '1';
1824 ELSE
1825 l_item_flag := '0';
1826 END IF;
1827 END;
1828 WHEN p_dim_name = 'ITEM+ENI_ITEM_ORG' THEN l_item_flag := '0';
1829 ELSE
1830 BEGIN
1831 IF l_item_val is null or l_item_val = '' or
1832 l_item_val = 'All' THEN
1833 IF l_cat_val is null or l_cat_val = '' or
1834 l_cat_val = 'All'THEN
1835 l_item_flag := '3';
1836 ELSE
1837 l_item_flag := '1';
1838 END IF;
1839 ELSE
1840 l_item_flag := '0';
1841 END IF;
1842 END;
1843 END CASE;
1844
1845 RETURN l_item_flag;
1846
1847 END get_item_flag_val;
1848
1849
1850 /* get_inv_val_aggr_flag
1851
1852 Compute the item_cat_flag value based on the parameters passed to
1853 determine the aggregation level of the MV rows that the query will
1854 have to run against.
1855 */
1856 FUNCTION get_inv_val_aggr_flag (p_dim_name VARCHAR2,
1857 p_dim_map IN
1858 poa_dbi_util_pkg.poa_dbi_dim_map)
1859 RETURN VARCHAR2
1860 IS
1861
1862 l_item_flag varchar2(1) := NULL;
1863 l_grouping_level NUMBER := 0;
1864
1865 l_org_val VARCHAR2 (4000) := NULL;
1866 l_cat_val VARCHAR2 (4000) := NULL;
1867 l_item_val VARCHAR2 (4000) := NULL;
1868 l_sub_val VARCHAR2 (4000) := NULL;
1869
1870 -- Grouping IDs for the various dimensions
1871 ITEM_GROUPING_ID CONSTANT INTEGER := 1;
1872 CAT_GROUPING_ID CONSTANT INTEGER := 2;
1873 SUB_GROUPING_ID CONSTANT INTEGER := 4;
1874
1875
1876 BEGIN
1877
1878 -- Get the org, item and cat values
1879 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
1880 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
1881 END IF;
1882
1883 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
1884 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
1885 END IF;
1886
1887 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
1888 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
1889 END IF;
1890
1891 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
1892 l_sub_val :=
1893 p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
1894 END IF;
1895
1896
1897 -- if the view by is item level, or specific item is specified, go to
1898 -- level 0
1899 IF p_dim_name = 'ITEM+ENI_ITEM_ORG' OR
1900 NOT (l_item_val IS NULL OR
1901 l_item_val = 'All' ) THEN
1902 l_grouping_level := 0;
1903 ELSE
1904
1905 -- we can at least use rows grouped at the item level
1906 l_grouping_level := l_grouping_level + ITEM_GROUPING_ID;
1907
1908 -- If the viewby is not subinventory and no specific sub is specified,
1909 -- we can use rows that roll up sub
1910 IF p_dim_name <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND
1911 (l_sub_val IS NULL OR
1912 l_sub_val = 'All') THEN
1913 l_grouping_level := l_grouping_level + SUB_GROUPING_ID;
1914 END IF;
1915
1916 -- If the viewby is not category and no specific cat is specified,
1917 -- we can use rows that roll up sub
1918 IF p_dim_name <> 'ITEM+ENI_ITEM_INV_CAT' AND
1919 (l_cat_val IS NULL OR
1920 l_cat_val = 'All') THEN
1921 l_grouping_level := l_grouping_level + CAT_GROUPING_ID;
1922 END IF;
1923
1924 END IF;
1925
1926 RETURN to_char (l_grouping_level);
1927
1928 END get_inv_val_aggr_flag;
1929
1930 FUNCTION get_wms_rtx_aggr_flag(p_dim_name VARCHAR2,
1931 p_dim_map IN
1932 poa_dbi_util_pkg.poa_dbi_dim_map)
1933 RETURN VARCHAR2
1934 IS
1935
1936 l_item_flag varchar2(1) := NULL;
1937 l_grouping_level NUMBER := 0;
1938 l_org_val VARCHAR2 (4000) := NULL;
1939 l_cat_val VARCHAR2 (4000) := NULL;
1940 l_item_val VARCHAR2 (4000) := NULL;
1941 l_sub_val VARCHAR2 (4000) := NULL;
1942
1943 BEGIN
1944 -- Get the org, item, subinventory and category values
1945 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
1946 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
1947 END IF;
1948
1949 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
1950 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
1951 END IF;
1952
1953 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
1954 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
1955 END IF;
1956
1957 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
1958 l_sub_val :=
1959 p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
1960 END IF;
1961
1962 /* if item, subinventory or category dimensions are needed, use level 0
1963 * otherwise use level 3 */
1964 if p_dim_name = 'ITEM+ENI_ITEM_ORG' or
1965 p_dim_name = 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' or
1966 p_dim_name = 'ITEM+ENI_ITEM_INV_CAT' or
1967 not ( l_cat_val is null or l_cat_val = 'All' ) or
1968 not ( l_item_val is null or l_item_val = 'All' ) or
1969 not ( l_sub_val is null or l_sub_val = 'All' ) then
1970 l_grouping_level := 0;
1971 else
1972 l_grouping_level := 3;
1973 end if;
1974
1975 RETURN to_char (l_grouping_level);
1976
1977 END get_wms_rtx_aggr_flag;
1978
1979
1980
1981
1982 FUNCTION get_wms_rtp_aggr_flag(p_dim_name VARCHAR2,
1983 p_dim_map IN
1984 poa_dbi_util_pkg.poa_dbi_dim_map)
1985 RETURN VARCHAR2
1986 IS
1987
1988 l_item_flag varchar2(1) := NULL;
1989 l_grouping_level NUMBER := 0;
1990
1991 l_org_val VARCHAR2 (4000) := NULL;
1992 l_cat_val VARCHAR2 (4000) := NULL;
1993 l_item_val VARCHAR2 (4000) := NULL;
1994 l_sub_val VARCHAR2 (4000) := NULL;
1995
1996 -- Grouping IDs for the various dimensions
1997 CAT_GROUPING_ID CONSTANT INTEGER := 3;
1998 SUB_GROUPING_ID CONSTANT INTEGER := 4;
1999
2000
2001 BEGIN
2002
2003 -- Get the org, item, subinventory and category values
2004 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
2005 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
2006 END IF;
2007
2008 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
2009 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
2010 END IF;
2011
2012 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
2013 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
2014 END IF;
2015
2016 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
2017 l_sub_val :=
2018 p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
2019 END IF;
2020
2021
2022 -- if the viewby is item or category, or specific item/category is selected,
2023 -- use level 0
2024 IF p_dim_name = 'ITEM+ENI_ITEM_ORG' OR
2025 NOT (l_item_val IS NULL OR l_item_val = 'All' ) OR
2026 p_dim_name='ITEM+ENI_ITEM_INV_CAT' OR
2027 NOT (l_cat_val IS NULL OR l_cat_val='All')
2028 THEN
2029 l_grouping_level := 0;
2030 -- If the viewby is subinventory or a specific subinventory is selected,
2031 -- use level 3
2032 ELSIF p_dim_name = 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' OR
2033 NOT (l_sub_val IS NULL OR l_sub_val = 'All')
2034 THEN
2035 l_grouping_level := 3;
2036 ELSE
2037 -- If we have come till here, the only dimension required is org
2038 -- so use level 7
2039 l_grouping_level := 7;
2040 END IF;
2041
2042 RETURN to_char (l_grouping_level);
2043
2044 END get_wms_rtp_aggr_flag;
2045
2046
2047 /* DBI 7.0 Changes for Cycle Count
2048 Function: get_cca_level_flag_val
2049 Description: Calculates the best aggregation level for any combination
2050 of user selected parameters
2051 */
2052 FUNCTION get_cca_level_flag_val (p_dim_name IN VARCHAR2,
2053 p_dim_map IN
2054 poa_dbi_util_pkg.poa_dbi_dim_map)
2055 RETURN VARCHAR2
2056 IS
2057 -- Aggregation Levels for the various dimensions
2058 ITEM_LEVEL_ID CONSTANT INTEGER := 1;
2059 CAT_LEVEL_ID CONSTANT INTEGER := 2;
2060 SUB_LEVEL_ID CONSTANT INTEGER := 4;
2061 CCS_LEVEL_ID CONSTANT INTEGER := 8;
2062 CC_LEVEL_ID CONSTANT INTEGER := 16;
2063
2064 l_org_val VARCHAR2 (120) := NULL;
2065 l_cch_val VARCHAR2 (120) := NULL;
2066 l_cccs_val VARCHAR2 (120) := NULL;
2067 l_item_val VARCHAR2 (120) := NULL;
2068 l_cat_val VARCHAR2 (120) := NULL;
2069 l_subinv_val VARCHAR2 (120) := NULL;
2070
2071 l_agg_lvl_flag NUMBER := 0;
2072 l_in_bmap NUMBER := 0;
2073
2074 l_mv_agg_lvl_tbl MV_AGG_LVL_TBL; /* Table of Aggregation levels */
2075 BEGIN
2076
2077 l_mv_agg_lvl_tbl := MV_AGG_LVL_TBL (); /* Initialize the table */
2078
2079 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
2080 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
2081 END IF;
2082
2083 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
2084 l_subinv_val := p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
2085 END IF;
2086
2087 IF (p_dim_map.exists ('OPI_INV_CC+OPI_INV_CC_LVL')) THEN
2088 l_cch_val := p_dim_map ('OPI_INV_CC+OPI_INV_CC_LVL').value;
2089 END IF;
2090
2091 IF (p_dim_map.exists ('OPI_INV_CC+OPI_INV_CC_CLS_LVL')) THEN
2092 l_cccs_val := p_dim_map ('OPI_INV_CC+OPI_INV_CC_CLS_LVL').value;
2093 END IF;
2094
2095 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
2096 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
2097 END IF;
2098
2099 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
2100 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
2101 END IF;
2102
2103 IF p_dim_name <> 'OPI_INV_CC+OPI_INV_CC_LVL' AND
2104 (l_cch_val IS NULL OR
2105 l_cch_val = '' OR
2106 l_cch_val = 'All'
2107 )
2108 THEN
2109 l_in_bmap := l_in_bmap + CC_LEVEL_ID;
2110 END IF;
2111
2112 IF p_dim_name <> 'OPI_INV_CC+OPI_INV_CC_CLS_LVL' AND
2113 (l_cccs_val IS NULL OR
2114 l_cccs_val = '' OR
2115 l_cccs_val = 'All'
2116 )
2117 THEN
2118 l_in_bmap := l_in_bmap + CCS_LEVEL_ID;
2119 END IF;
2120
2121 IF p_dim_name <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND
2122 (l_subinv_val IS NULL OR
2123 l_subinv_val = '' OR
2124 l_subinv_val = 'All'
2125 )
2126 THEN
2127 l_in_bmap := l_in_bmap + SUB_LEVEL_ID;
2128 END IF;
2129
2130 IF p_dim_name <> 'ITEM+ENI_ITEM_INV_CAT' AND
2131 (l_cat_val IS NULL OR
2132 l_cat_val = '' OR
2133 l_cat_val = 'All'
2134 )
2135 THEN
2136 l_in_bmap := l_in_bmap + CAT_LEVEL_ID;
2137 END IF;
2138
2139 IF p_dim_name <> 'ITEM+ENI_ITEM_ORG' AND
2140 (l_item_val IS NULL OR
2141 l_item_val = '' OR
2142 l_item_val = 'All'
2143 )
2144 THEN
2145 l_in_bmap := l_in_bmap + ITEM_LEVEL_ID;
2146 END IF;
2147
2148 --
2149 -- Populate the table with aggregation level values from MV
2150 -- Start with most preferred aggregation level
2151 --
2152 l_mv_agg_lvl_tbl.extend;
2153 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 31; /* org level*/
2154
2155 l_mv_agg_lvl_tbl.extend;
2156 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 29; /* inv category */
2157
2158 l_mv_agg_lvl_tbl.extend;
2159 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 27; /* subinv */
2160
2161 l_mv_agg_lvl_tbl.extend;
2162 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 15; /* cycle count Header*/
2163
2164 l_mv_agg_lvl_tbl.extend;
2165 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 7; /* cycle count class */
2166
2167 l_mv_agg_lvl_tbl.extend;
2168 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 0; /* item level*/
2169
2170 --
2171 -- calculate the most effecient level for the combination of parameters
2172 --
2173 l_agg_lvl_flag := get_agg_level(l_mv_agg_lvl_tbl, l_in_bmap);
2174 RETURN to_char(l_agg_lvl_flag);
2175 END get_cca_level_flag_val;
2176
2177
2178 /*
2179 DBI 7.0 Changes for Product Cost Management: Product Gross Margin Reports
2180 Function: get_prodcat_cust_flag_val
2181 Description: Compute the prodcat_cust_flag value based on the parameters passed to
2182 determine the aggregation level of the MV rows that the query will
2183 have to run against.
2184 */
2185 FUNCTION get_prodcat_cust_flag_val(p_dim_name IN VARCHAR2,
2186 p_dim_map IN
2187 poa_dbi_util_pkg.poa_dbi_dim_map)
2188 RETURN VARCHAR2
2189 IS
2190
2191 l_org_val VARCHAR2 (120) := NULL;
2192 l_cat_val VARCHAR2 (120) := NULL;
2193 l_item_val VARCHAR2 (120) := NULL;
2194 l_customer_val VARCHAR2 (120) := NULL;
2195 l_prodcat_cust_flag number := 0;
2196
2197 l_agg_lvl_flag NUMBER := 0;
2198 l_mv_agg_lvl_tbl MV_AGG_LVL_TBL; /* Table of Aggregation levels */
2199
2200 /*
2201 C P I Value
2202 --------------
2203 0 0 0 0
2204 0 0 1 1
2205 0 1 1 3
2206 1 0 0 4
2207 1 0 1 5
2208 1 1 1 7
2209 */
2210 -- Aggregation Levels for the various dimensions
2211 ITEM_LEVEL_ID CONSTANT INTEGER := 1;
2212 CAT_LEVEL_ID CONSTANT INTEGER := 2;
2213 CUST_LEVEL_ID CONSTANT INTEGER := 4;
2214
2215
2216 BEGIN
2217 l_mv_agg_lvl_tbl := MV_AGG_LVL_TBL (); /* Initialize the table */
2218
2219 -- Get the customer, cat and item values
2220
2221 IF (p_dim_map.exists ('ITEM+ENI_ITEM_VBH_CAT')) THEN
2222 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_VBH_CAT').value;
2223 END IF;
2224
2225 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
2226 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
2227 END IF;
2228
2229 IF (p_dim_map.exists ('CUSTOMER+FII_CUSTOMERS')) THEN
2230 l_customer_val := p_dim_map ('CUSTOMER+FII_CUSTOMERS').value;
2231 END IF;
2232 --grouping_id(customer_id,nvl(item.vbh_category_id, -1),(item.inventory_item_id||''-''||item.organization_id))
2233
2234 IF (p_dim_name <> 'ITEM+ENI_ITEM_ORG' AND (l_item_val is NULL or l_item_val = 'All')) THEN
2235 l_prodcat_cust_flag := l_prodcat_cust_flag + ITEM_LEVEL_ID;
2236 END IF;
2237
2238 IF (p_dim_name <> 'ITEM+ENI_ITEM_VBH_CAT' AND (l_cat_val is NULL or l_cat_val = 'All')) THEN
2239 l_prodcat_cust_flag := l_prodcat_cust_flag + CAT_LEVEL_ID;
2240 END IF;
2241
2242 IF (p_dim_name <> 'CUSTOMER+FII_CUSTOMERS' AND (l_customer_val is NULL or l_customer_val = 'All')) THEN
2243 l_prodcat_cust_flag := l_prodcat_cust_flag + CUST_LEVEL_ID;
2244 END IF;
2245
2246
2247 --
2248 -- Populate the table with aggregation level values from MV
2249 -- Start with most preferred aggregation level
2250 --
2251 l_mv_agg_lvl_tbl.extend;
2252 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 7; /* Customer, Category, Item */
2253
2254 l_mv_agg_lvl_tbl.extend;
2255 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 5; /* Category */
2256
2257 l_mv_agg_lvl_tbl.extend;
2258 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 4; /* Category, Item */
2259
2260 l_mv_agg_lvl_tbl.extend;
2261 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 3; /* Customer */
2262
2263 l_mv_agg_lvl_tbl.extend;
2264 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 1; /* Customer, Category */
2265
2266 l_mv_agg_lvl_tbl.extend;
2267 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 0; /* Customer, Category, Item */
2268
2269 --
2270 -- calculate the most effecient level for the combination of parameters
2271 --
2272 l_agg_lvl_flag := get_agg_level(l_mv_agg_lvl_tbl, l_prodcat_cust_flag);
2273 RETURN to_char(l_agg_lvl_flag);
2274
2275 END get_prodcat_cust_flag_val;
2276
2277
2278 /* get_wms_c_utz_item_aggr_flag*/
2279 FUNCTION get_wms_c_utz_item_aggr_flag (p_dim_name VARCHAR2,
2280 p_dim_map IN
2281 poa_dbi_util_pkg.poa_dbi_dim_map)
2282 RETURN VARCHAR2
2283 IS
2284 l_grouping_level NUMBER;
2285
2286 l_org_val VARCHAR2 (4000);
2287 l_cat_val VARCHAR2 (4000);
2288 l_item_val VARCHAR2 (4000);
2289 l_sub_val VARCHAR2 (4000);
2290
2291 -- The aggregation flag values
2292 L_ITEM_LEVEL CONSTANT NUMBER := 0;
2293 L_SUB_CAT_LEVEL CONSTANT NUMBER := 1;
2294 L_ORG_LEVEL CONSTANT NUMBER := 7;
2295
2296 BEGIN
2297
2298 -- Initialization block
2299 l_grouping_level := NULL;
2300 l_org_val := NULL;
2301 l_cat_val := NULL;
2302 l_item_val := NULL;
2303 l_sub_val := NULL;
2304
2305 -- Get the org, sub, item and cat values
2306 IF (p_dim_map.exists (C_DIMNAME_ORG)) THEN
2307 l_org_val := p_dim_map (C_DIMNAME_ORG).value;
2308 END IF;
2309
2310 IF (p_dim_map.exists (C_DIMNAME_INV_CAT)) THEN
2311 l_cat_val := p_dim_map (C_DIMNAME_INV_CAT).value;
2312 END IF;
2313
2314 IF (p_dim_map.exists (C_DIMNAME_ITEM)) THEN
2315 l_item_val := p_dim_map (C_DIMNAME_ITEM).value;
2316 END IF;
2317
2318 IF (p_dim_map.exists (C_DIMNAME_SUB)) THEN
2319 l_sub_val :=
2320 p_dim_map (C_DIMNAME_SUB).value;
2321 END IF;
2322
2323 -- Compute the correct flag value
2324 IF p_dim_name = C_VIEWBY_ITEM OR
2325 NOT (l_item_val IS NULL OR
2326 l_item_val = C_ALL) THEN
2327
2328 -- if the view by is item level, or specific item is specified, go to
2329 -- level 0
2330 l_grouping_level := L_ITEM_LEVEL;
2331
2332 ELSIF p_dim_name = C_VIEWBY_INV_CAT OR
2333 p_dim_name = C_VIEWBY_SUB OR
2334 NOT (l_cat_val IS NULL OR l_cat_val = C_ALL) OR
2335 NOT (l_sub_val IS NULL OR l_sub_val = C_ALL) THEN
2336 -- if the view by is sub/cat level, or specific sub/cat
2337 -- is specified, go to level 1
2338 l_grouping_level := L_SUB_CAT_LEVEL;
2339
2340 ELSE
2341 -- The viewby is org, and at the most only org parameter values
2342 -- are specified.
2343 l_grouping_level := L_ORG_LEVEL;
2344 END IF;
2345
2346 return to_char (l_grouping_level);
2347
2348 END get_wms_c_utz_item_aggr_flag;
2349
2350 /* get_wms_c_utz_sub_aggr_flag*/
2351 FUNCTION get_wms_c_utz_sub_aggr_flag (p_dim_name VARCHAR2,
2352 p_dim_map IN
2353 poa_dbi_util_pkg.poa_dbi_dim_map)
2354 RETURN VARCHAR2
2355 IS
2356 l_grouping_level NUMBER;
2357
2358 l_org_val VARCHAR2 (4000);
2359 l_sub_val VARCHAR2 (4000);
2360
2361 -- The aggregation flag values
2362 L_SUB_LEVEL CONSTANT NUMBER := 1;
2363 L_ORG_LEVEL CONSTANT NUMBER := 7;
2364
2365 BEGIN
2366
2367 -- Initialization block
2368 l_grouping_level := NULL;
2369 l_org_val := NULL;
2370 l_sub_val := NULL;
2371
2372 -- Get the org, sub, item and cat values
2373 IF (p_dim_map.exists (C_DIMNAME_ORG)) THEN
2374 l_org_val := p_dim_map (C_DIMNAME_ORG).value;
2375 END IF;
2376
2377 IF (p_dim_map.exists (C_DIMNAME_SUB)) THEN
2378 l_sub_val :=
2379 p_dim_map (C_DIMNAME_SUB).value;
2380 END IF;
2381
2382 -- Compute the correct flag value
2383 IF p_dim_name = C_VIEWBY_SUB OR
2384 NOT (l_sub_val IS NULL OR
2385 l_sub_val = C_ALL) THEN
2386
2387 -- if the view by is item level, or specific item is specified, go to
2388 -- level 0
2389 l_grouping_level := L_SUB_LEVEL;
2390
2391 ELSE
2392 -- The viewby is org, and at the most only org parameter values
2393 -- are specified.
2394 l_grouping_level := L_ORG_LEVEL;
2395
2396 END IF;
2397
2398 return to_char (l_grouping_level);
2399
2400 END get_wms_c_utz_sub_aggr_flag;
2401
2402 /* get_wms_stor_utz_aggr_flag*/
2403 FUNCTION get_wms_stor_utz_aggr_flag (p_dim_name VARCHAR2,
2404 p_dim_map IN
2405 poa_dbi_util_pkg.poa_dbi_dim_map)
2406 RETURN VARCHAR2
2407 IS
2408 l_grouping_level NUMBER;
2409
2410 l_org_val VARCHAR2 (4000);
2411 l_cat_val VARCHAR2 (4000);
2412 l_item_val VARCHAR2 (4000);
2413 l_sub_val VARCHAR2 (4000);
2414
2415 -- The aggregation flag values
2416 L_ITEM_LEVEL CONSTANT NUMBER := 0;
2417 L_SUB_CAT_LEVEL CONSTANT NUMBER := 1;
2418 L_ORG_LEVEL CONSTANT NUMBER := 7;
2419
2420 BEGIN
2421
2422 -- Initialization block
2423 l_grouping_level := NULL;
2424 l_org_val := NULL;
2425 l_cat_val := NULL;
2426 l_item_val := NULL;
2427 l_sub_val := NULL;
2428
2429 -- Get the org, sub, item and cat values
2430 IF (p_dim_map.exists (C_DIMNAME_ORG)) THEN
2431 l_org_val := p_dim_map (C_DIMNAME_ORG).value;
2432 END IF;
2433
2434 IF (p_dim_map.exists (C_DIMNAME_INV_CAT)) THEN
2435 l_cat_val := p_dim_map (C_DIMNAME_INV_CAT).value;
2436 END IF;
2437
2438 IF (p_dim_map.exists (C_DIMNAME_ITEM)) THEN
2439 l_item_val := p_dim_map (C_DIMNAME_ITEM).value;
2440 END IF;
2441
2442 IF (p_dim_map.exists (C_DIMNAME_SUB)) THEN
2443 l_sub_val :=
2444 p_dim_map (C_DIMNAME_SUB).value;
2445 END IF;
2446
2447 -- Compute the correct flag value
2448 IF p_dim_name = C_VIEWBY_ITEM OR
2449 NOT (l_item_val IS NULL OR
2450 l_item_val = C_ALL) THEN
2451
2452 -- if the view by is item level, or specific item is specified, go to
2453 -- level 0
2454 l_grouping_level := L_ITEM_LEVEL;
2455
2456 ELSIF p_dim_name = C_VIEWBY_INV_CAT OR
2457 p_dim_name = C_VIEWBY_SUB OR
2458 NOT (l_cat_val IS NULL OR l_cat_val = C_ALL) OR
2459 NOT (l_sub_val IS NULL OR l_sub_val = C_ALL) THEN
2460 -- if the view by is sub/cat level, or specific sub/cat
2461 -- is specified, go to level 1
2462 l_grouping_level := L_SUB_CAT_LEVEL;
2463
2464 ELSE
2465 -- The viewby is org, and at the most only org parameter values
2466 -- are specified.
2467 l_grouping_level := L_ORG_LEVEL;
2468 END IF;
2469
2470 return to_char (l_grouping_level);
2471
2472 END get_wms_stor_utz_aggr_flag;
2473
2474 /* Function: get_wms_pex_aggr_flag
2475 Description: Compute best aggregation level of MV rows that the query
2476 will run against for any combination of report parameters
2477 */
2478 FUNCTION get_wms_pex_aggr_flag(p_dim_name VARCHAR2,
2479 p_dim_map IN
2480 poa_dbi_util_pkg.poa_dbi_dim_map)
2481 RETURN VARCHAR2
2482 IS
2483 -- Aggregation Levels for the various dimensions
2484 ITEM_LEVEL_ID CONSTANT INTEGER := 1;
2485 CAT_LEVEL_ID CONSTANT INTEGER := 2;
2486 SUB_LEVEL_ID CONSTANT INTEGER := 4;
2487
2488 l_org_val VARCHAR2 (120) := NULL;
2489 l_item_val VARCHAR2 (120) := NULL;
2490 l_cat_val VARCHAR2 (120) := NULL;
2491 l_subinv_val VARCHAR2 (120) := NULL;
2492
2493 l_agg_lvl_flag NUMBER := 0;
2494 l_in_bmap NUMBER := 0;
2495
2496 l_mv_agg_lvl_tbl MV_AGG_LVL_TBL; /* Table of Aggregation levels */
2497 BEGIN
2498
2499 l_mv_agg_lvl_tbl := MV_AGG_LVL_TBL (); /* Initialize the table */
2500
2501 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
2502 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
2503 END IF;
2504
2505 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
2506 l_subinv_val := p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
2507 END IF;
2508
2509 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
2510 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
2511 END IF;
2512
2513 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
2514 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
2515 END IF;
2516
2517 IF p_dim_name <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND
2518 (l_subinv_val IS NULL OR
2519 l_subinv_val = '' OR
2520 l_subinv_val = 'All'
2521 )
2522 THEN
2523 l_in_bmap := l_in_bmap + SUB_LEVEL_ID;
2524 END IF;
2525
2526 IF p_dim_name <> 'ITEM+ENI_ITEM_INV_CAT' AND
2527 (l_cat_val IS NULL OR
2528 l_cat_val = '' OR
2529 l_cat_val = 'All'
2530 )
2531 THEN
2532 l_in_bmap := l_in_bmap + CAT_LEVEL_ID;
2533 END IF;
2534
2535 IF p_dim_name <> 'ITEM+ENI_ITEM_ORG' AND
2536 (l_item_val IS NULL OR
2537 l_item_val = '' OR
2538 l_item_val = 'All'
2539 )
2540 THEN
2541 l_in_bmap := l_in_bmap + ITEM_LEVEL_ID;
2542 END IF;
2543
2544 --
2545 -- Populate the table with aggregation level values from MV
2546 -- Start with most preferred aggregation level
2547 --
2548 l_mv_agg_lvl_tbl.extend;
2549 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 7; /* org level*/
2550
2551 l_mv_agg_lvl_tbl.extend;
2552 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 3; /* org sub category */
2553
2554 l_mv_agg_lvl_tbl.extend;
2555 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 1; /* org sub inv cat level */
2556
2557 l_mv_agg_lvl_tbl.extend;
2558 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 0; /* Most Granular Level*/
2559
2560 --
2561 -- calculate the most effecient level for the combination of parameters
2562 --
2563 l_agg_lvl_flag := get_agg_level(l_mv_agg_lvl_tbl, l_in_bmap);
2564 RETURN to_char(l_agg_lvl_flag);
2565 END get_wms_pex_aggr_flag;
2566
2567 /* Function: get_wms_per_aggr_flag
2568 Description: Compute best aggregation level of MV rows that the query
2569 will run against for any combination of report parameters
2570 */
2571 FUNCTION get_wms_per_aggr_flag(p_dim_name VARCHAR2,
2572 p_dim_map IN
2573 poa_dbi_util_pkg.poa_dbi_dim_map)
2574 RETURN VARCHAR2
2575 IS
2576 -- Aggregation Levels for the various dimensions
2577 REASON_LEVEL_ID CONSTANT INTEGER := 1;
2578 ITEM_LEVEL_ID CONSTANT INTEGER := 2;
2579 CAT_LEVEL_ID CONSTANT INTEGER := 4;
2580 SUB_LEVEL_ID CONSTANT INTEGER := 8;
2581
2582 l_org_val VARCHAR2 (120) := NULL;
2583 l_item_val VARCHAR2 (120) := NULL;
2584 l_cat_val VARCHAR2 (120) := NULL;
2585 l_subinv_val VARCHAR2 (120) := NULL;
2586 l_reason_val VARCHAR2 (120) := NULL;
2587
2588 l_agg_lvl_flag NUMBER := 0;
2589 l_in_bmap NUMBER := 0;
2590
2591 l_mv_agg_lvl_tbl MV_AGG_LVL_TBL; /* Table of Aggregation levels */
2592 BEGIN
2593
2594 l_mv_agg_lvl_tbl := MV_AGG_LVL_TBL (); /* Initialize the table */
2595
2596 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
2597 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
2598 END IF;
2599
2600 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
2601 l_subinv_val := p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
2602 END IF;
2603
2604 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
2605 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
2606 END IF;
2607
2608 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
2609 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
2610 END IF;
2611
2612 IF (p_dim_map.exists ('OPI_WMS_TASK_EXC_REASONS + OPI_WMS_TASK_EXC_REASONS_LVL')) THEN
2613 l_item_val := p_dim_map ('OPI_WMS_TASK_EXC_REASONS + OPI_WMS_TASK_EXC_REASONS_LVL').value;
2614 END IF;
2615
2616 IF p_dim_name <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND
2617 (l_subinv_val IS NULL OR
2618 l_subinv_val = '' OR
2619 l_subinv_val = 'All'
2620 )
2621 THEN
2622 l_in_bmap := l_in_bmap + SUB_LEVEL_ID;
2623 END IF;
2624
2625 IF p_dim_name <> 'ITEM+ENI_ITEM_INV_CAT' AND
2626 (l_cat_val IS NULL OR
2627 l_cat_val = '' OR
2628 l_cat_val = 'All'
2629 )
2630 THEN
2631 l_in_bmap := l_in_bmap + CAT_LEVEL_ID;
2632 END IF;
2633
2634 IF p_dim_name <> 'ITEM+ENI_ITEM_ORG' AND
2635 (l_item_val IS NULL OR
2636 l_item_val = '' OR
2637 l_item_val = 'All'
2638 )
2639 THEN
2640 l_in_bmap := l_in_bmap + ITEM_LEVEL_ID;
2641 END IF;
2642
2643 IF p_dim_name <> 'OPI_WMS_TASK_EXC_REASONS + OPI_WMS_TASK_EXC_REASONS_LVL' AND
2644 (l_item_val IS NULL OR
2645 l_item_val = '' OR
2646 l_item_val = 'All'
2647 )
2648 THEN
2649 l_in_bmap := l_in_bmap + REASON_LEVEL_ID;
2650 END IF;
2651
2652 --
2653 -- Populate the table with aggregation level values from MV
2654 -- Start with most preferred aggregation level
2655 --
2656 l_mv_agg_lvl_tbl.extend;
2657 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 14; /* org reason code level*/
2658
2659 l_mv_agg_lvl_tbl.extend;
2660 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 6; /* org sub reason level */
2661
2662 l_mv_agg_lvl_tbl.extend;
2663 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 2; /* org sub inv cat reason level */
2664
2665 l_mv_agg_lvl_tbl.extend;
2666 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 0; /* Most Granular Level*/
2667
2668 --
2669 -- calculate the most effecient level for the combination of parameters
2670 --
2671 l_agg_lvl_flag := get_agg_level(l_mv_agg_lvl_tbl, l_in_bmap);
2672 RETURN to_char(l_agg_lvl_flag);
2673 END get_wms_per_aggr_flag;
2674
2675 /* Function: get_wms_opp_aggr_flag
2676 Description: Compute best aggregation level of MV rows that the query
2677 will run against for any combination of report parameters
2678 */
2679 FUNCTION get_wms_opp_aggr_flag(p_dim_name VARCHAR2,
2680 p_dim_map IN
2681 poa_dbi_util_pkg.poa_dbi_dim_map)
2682 RETURN VARCHAR2
2683 IS
2684 -- Aggregation Levels for the various dimensions
2685 OP_LEVEL_ID CONSTANT INTEGER := 0;
2686 ITEM_LEVEL_ID CONSTANT INTEGER := 2;
2687 CAT_LEVEL_ID CONSTANT INTEGER := 4;
2688 SUB_LEVEL_ID CONSTANT INTEGER := 8;
2689
2690 l_org_val VARCHAR2 (120) := NULL;
2691 l_item_val VARCHAR2 (120) := NULL;
2692 l_cat_val VARCHAR2 (120) := NULL;
2693 l_subinv_val VARCHAR2 (120) := NULL;
2694 l_op_val VARCHAR2 (120) := NULL;
2695
2696 l_agg_lvl_flag NUMBER := 0;
2697 l_in_bmap NUMBER := 0;
2698
2699 l_mv_agg_lvl_tbl MV_AGG_LVL_TBL; /* Table of Aggregation levels */
2700 BEGIN
2701
2702 l_mv_agg_lvl_tbl := MV_AGG_LVL_TBL (); /* Initialize the table */
2703
2704 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
2705 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
2706 END IF;
2707
2708 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
2709 l_subinv_val := p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
2710 END IF;
2711
2712 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
2713 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
2714 END IF;
2715
2716 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
2717 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
2718 END IF;
2719
2720 IF (p_dim_map.exists ('OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL')) THEN
2721 l_op_val := p_dim_map ('OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL').value;
2722 END IF;
2723
2724 IF p_dim_name <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND
2725 (l_subinv_val IS NULL OR
2726 l_subinv_val = '' OR
2727 l_subinv_val = 'All'
2728 )
2729 THEN
2730 l_in_bmap := l_in_bmap + SUB_LEVEL_ID;
2731 END IF;
2732
2733 IF p_dim_name <> 'ITEM+ENI_ITEM_INV_CAT' AND
2734 (l_cat_val IS NULL OR
2735 l_cat_val = '' OR
2736 l_cat_val = 'All'
2737 )
2738 THEN
2739 l_in_bmap := l_in_bmap + CAT_LEVEL_ID;
2740 END IF;
2741
2742 IF p_dim_name <> 'ITEM+ENI_ITEM_ORG' AND
2743 (l_item_val IS NULL OR
2744 l_item_val = '' OR
2745 l_item_val = 'All'
2746 )
2747 THEN
2748 l_in_bmap := l_in_bmap + ITEM_LEVEL_ID;
2749 END IF;
2750
2751 IF p_dim_name <> 'OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL' AND
2752 (l_op_val IS NULL OR
2753 l_op_val = '' OR
2754 l_op_val = 'All'
2755 )
2756 THEN
2757 l_in_bmap := l_in_bmap + OP_LEVEL_ID;
2758 END IF;
2759
2760 --
2761 -- Populate the table with aggregation level values from MV
2762 -- Start with most preferred aggregation level
2763 --
2764 l_mv_agg_lvl_tbl.extend;
2765 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 14; /* org op plan level*/
2766
2767 l_mv_agg_lvl_tbl.extend;
2768 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 6; /* org sub plan category */
2769
2770 l_mv_agg_lvl_tbl.extend;
2771 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 2; /* org sub inv cat plan level */
2772
2773 l_mv_agg_lvl_tbl.extend;
2774 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 0; /* Most Granular Level*/
2775
2776 --
2777 -- calculate the most effecient level for the combination of parameters
2778 --
2779 l_agg_lvl_flag := get_agg_level(l_mv_agg_lvl_tbl, l_in_bmap);
2780 RETURN to_char(l_agg_lvl_flag);
2781
2782 END get_wms_opp_aggr_flag;
2783
2784 /* Function: get_wms_oper_aggr_flag
2785 Description: Compute best aggregation level of MV rows that the query
2786 will run against for any combination of report parameters
2787 */
2788 FUNCTION get_wms_oper_aggr_flag(p_dim_name VARCHAR2,
2789 p_dim_map IN
2790 poa_dbi_util_pkg.poa_dbi_dim_map)
2791 RETURN VARCHAR2
2792 IS
2793 -- Aggregation Levels for the various dimensions
2794 REASON_LEVEL_ID CONSTANT INTEGER := 0;
2795 OP_LEVEL_ID CONSTANT INTEGER := 2;
2796 ITEM_LEVEL_ID CONSTANT INTEGER := 4;
2797 CAT_LEVEL_ID CONSTANT INTEGER := 8;
2798 SUB_LEVEL_ID CONSTANT INTEGER := 16;
2799
2800 l_org_val VARCHAR2 (120) := NULL;
2801 l_item_val VARCHAR2 (120) := NULL;
2802 l_cat_val VARCHAR2 (120) := NULL;
2803 l_subinv_val VARCHAR2 (120) := NULL;
2804 l_op_val VARCHAR2 (120) := NULL;
2805 l_reason_val VARCHAR2 (120) := NULL;
2806
2807 l_agg_lvl_flag NUMBER := 0;
2808 l_in_bmap NUMBER := 0;
2809
2810 l_mv_agg_lvl_tbl MV_AGG_LVL_TBL; /* Table of Aggregation levels */
2811 BEGIN
2812
2813 l_mv_agg_lvl_tbl := MV_AGG_LVL_TBL (); /* Initialize the table */
2814
2815 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
2816 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
2817 END IF;
2818
2819 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
2820 l_subinv_val := p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
2821 END IF;
2822
2823 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
2824 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
2825 END IF;
2826
2827 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
2828 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
2829 END IF;
2830
2831 IF (p_dim_map.exists ('OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL')) THEN
2832 l_op_val := p_dim_map ('OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL').value;
2833 END IF;
2834
2835 IF (p_dim_map.exists ('OPI_WMS_TASK_EXC_REASONS + OPI_WMS_TASK_EXC_REASONS_LVL')) THEN
2836 l_reason_val := p_dim_map ('OPI_WMS_TASK_EXC_REASONS + OPI_WMS_TASK_EXC_REASONS_LVL').value;
2837 END IF;
2838
2839 IF p_dim_name <> 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' AND
2840 (l_subinv_val IS NULL OR
2841 l_subinv_val = '' OR
2842 l_subinv_val = 'All'
2843 )
2844 THEN
2845 l_in_bmap := l_in_bmap + SUB_LEVEL_ID;
2846 END IF;
2847
2848 IF p_dim_name <> 'ITEM+ENI_ITEM_INV_CAT' AND
2849 (l_cat_val IS NULL OR
2850 l_cat_val = '' OR
2851 l_cat_val = 'All'
2852 )
2853 THEN
2854 l_in_bmap := l_in_bmap + CAT_LEVEL_ID;
2855 END IF;
2856
2857 IF p_dim_name <> 'ITEM+ENI_ITEM_ORG' AND
2858 (l_item_val IS NULL OR
2859 l_item_val = '' OR
2860 l_item_val = 'All'
2861 )
2862 THEN
2863 l_in_bmap := l_in_bmap + ITEM_LEVEL_ID;
2864 END IF;
2865
2866 IF p_dim_name <> 'OPI_WMS_OP_PLAN+OPI_WMS_OP_PLAN_NAME_LVL' AND
2867 (l_op_val IS NULL OR
2868 l_op_val = '' OR
2869 l_op_val = 'All'
2870 )
2871 THEN
2872 l_in_bmap := l_in_bmap + OP_LEVEL_ID;
2873 END IF;
2874
2875 IF p_dim_name <> 'OPI_WMS_TASK_EXC_REASONS + OPI_WMS_TASK_EXC_REASONS_LVL' AND
2876 (l_reason_val IS NULL OR
2877 l_reason_val = '' OR
2878 l_reason_val = 'All'
2879 )
2880 THEN
2881 l_in_bmap := l_in_bmap + REASON_LEVEL_ID;
2882 END IF;
2883
2884 --
2885 -- Populate the table with aggregation level values from MV
2886 -- Start with most preferred aggregation level
2887 --
2888 l_mv_agg_lvl_tbl.extend;
2889 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 28; /* org reason code level*/
2890
2891 l_mv_agg_lvl_tbl.extend;
2892 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 12; /* org sub reason level */
2893
2894 l_mv_agg_lvl_tbl.extend;
2895 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 4; /* org sub inv cat reason level */
2896
2897 l_mv_agg_lvl_tbl.extend;
2898 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 0; /* Most Granular Level*/
2899
2900 --
2901 -- calculate the most effecient level for the combination of parameters
2902 --
2903 l_agg_lvl_flag := get_agg_level(l_mv_agg_lvl_tbl, l_in_bmap);
2904 RETURN to_char(l_agg_lvl_flag);
2905 END get_wms_oper_aggr_flag;
2906
2907 /* Standardize the rollup flag computation */
2908 /*
2909 There are a distinct number of possible rollups possible
2910 along dimensions. Since many MVs use the same rollup
2911 and aggregation scheme, we're trying to reuse the flag
2912 computation code.
2913
2914 The idea is that we will have the flag computed based on the
2915 rollup #:
2916
2917 Rollup1: Organization - Inventory Category - Item
2918
2919 */
2920
2921 /* get_rollup1_aggr_flag
2922 Returns aggregation for rollup on:
2923 Org - Inv Cat - Item
2924
2925 */
2926 FUNCTION get_rollup1_aggr_flag (p_dim_name VARCHAR2,
2927 p_dim_map IN
2928 poa_dbi_util_pkg.poa_dbi_dim_map)
2929 RETURN VARCHAR2
2930 IS
2931 -- {
2932 l_grouping_level NUMBER;
2933
2934 l_org_val VARCHAR2 (4000);
2935 l_cat_val VARCHAR2 (4000);
2936 l_item_val VARCHAR2 (4000);
2937
2938 -- The aggregation flag values
2939 L_ITEM_LEVEL CONSTANT NUMBER := 0;
2940 L_CAT_LEVEL CONSTANT NUMBER := 1;
2941 L_ORG_LEVEL CONSTANT NUMBER := 3;
2942 -- }
2943 BEGIN
2944 -- {
2945 -- Initialization block
2946 l_grouping_level := NULL;
2947 l_org_val := NULL;
2948 l_cat_val := NULL;
2949 l_item_val := NULL;
2950
2951 -- Get the org, sub, item and cat values
2952 IF (p_dim_map.exists (C_DIMNAME_ORG)) THEN
2953 -- {
2954 l_org_val := p_dim_map (C_DIMNAME_ORG).value;
2955 -- }
2956 END IF;
2957
2958 IF (p_dim_map.exists (C_DIMNAME_INV_CAT)) THEN
2959 -- {
2960 l_cat_val := p_dim_map (C_DIMNAME_INV_CAT).value;
2961 -- }
2962 END IF;
2963
2964 IF (p_dim_map.exists (C_DIMNAME_ITEM)) THEN
2965 -- {
2966 l_item_val := p_dim_map (C_DIMNAME_ITEM).value;
2967 -- }
2968 END IF;
2969
2970 -- Compute the correct flag value
2971 IF p_dim_name = C_VIEWBY_ITEM OR
2972 NOT (l_item_val IS NULL OR
2973 l_item_val = C_ALL) THEN
2974 -- {
2975 -- if the view by is item level, or specific item is specified, go to
2976 -- level 0
2977 l_grouping_level := L_ITEM_LEVEL;
2978 -- }
2979
2980 ELSIF p_dim_name = C_VIEWBY_INV_CAT OR
2981 NOT (l_cat_val IS NULL OR l_cat_val = C_ALL) THEN
2982 -- {
2983 -- if the view by is sub/cat level, or specific sub/cat
2984 -- is specified, go to level 1
2985 l_grouping_level := L_CAT_LEVEL;
2986 -- }
2987 ELSE
2988 -- {
2989 -- The viewby is org, and at the most only org parameter values
2990 -- are specified.
2991 l_grouping_level := L_ORG_LEVEL;
2992 -- }
2993 END IF;
2994
2995 return to_char (l_grouping_level);
2996 -- }
2997 END get_rollup1_aggr_flag;
2998
2999 /* Function: get_trx_reason_aggr_flag
3000 Description: Compute best aggregation level of MV rows that the query
3001 will run against for any combination of report parameters
3002 */
3003 FUNCTION get_trx_reason_aggr_flag(p_dim_name VARCHAR2,
3004 p_dim_map IN
3005 poa_dbi_util_pkg.poa_dbi_dim_map)
3006 RETURN VARCHAR2
3007 IS
3008 -- Aggregation Levels for the various dimensions
3009 REASON_LEVEL_ID CONSTANT INTEGER := 1;
3010 ITEM_LEVEL_ID CONSTANT INTEGER := 2;
3011 CAT_LEVEL_ID CONSTANT INTEGER := 4;
3012
3013 l_org_val VARCHAR2 (120) := NULL;
3014 l_item_val VARCHAR2 (120) := NULL;
3015 l_cat_val VARCHAR2 (120) := NULL;
3016 l_reason_val VARCHAR2 (120) := NULL;
3017
3018 l_agg_lvl_flag NUMBER := 0;
3019 l_in_bmap NUMBER := 0;
3020
3021 l_mv_agg_lvl_tbl MV_AGG_LVL_TBL; /* Table of Aggregation levels */
3022 BEGIN
3023
3024 l_mv_agg_lvl_tbl := MV_AGG_LVL_TBL (); /* Initialize the table */
3025
3026 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
3027 l_org_val := p_dim_map ('ORGANIZATION+ORGANIZATION').value;
3028 END IF;
3029
3030 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
3031 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
3032 END IF;
3033
3034 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
3035 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
3036 END IF;
3037
3038 IF (p_dim_map.exists ('OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL')) THEN
3039 l_reason_val := p_dim_map ('OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL').value;
3040 END IF;
3041
3042 IF p_dim_name <> 'ITEM+ENI_ITEM_INV_CAT' AND
3043 (l_cat_val IS NULL OR
3044 l_cat_val = '' OR
3045 l_cat_val = 'All'
3046 )
3047 THEN
3048 l_in_bmap := l_in_bmap + CAT_LEVEL_ID;
3049 END IF;
3050
3051 IF p_dim_name <> 'ITEM+ENI_ITEM_ORG' AND
3052 (l_item_val IS NULL OR
3053 l_item_val = '' OR
3054 l_item_val = 'All'
3055 )
3056 THEN
3057 l_in_bmap := l_in_bmap + ITEM_LEVEL_ID;
3058 END IF;
3059
3060 IF p_dim_name <> 'OPI_MFG_TRX_REASON+OPI_MFG_MTL_TRX_REASON_LVL' AND
3061 (l_item_val IS NULL OR
3062 l_item_val = '' OR
3063 l_item_val = 'All'
3064 )
3065 THEN
3066 l_in_bmap := l_in_bmap + REASON_LEVEL_ID;
3067 END IF;
3068
3069 --
3070 -- Populate the table with aggregation level values from MV
3071 -- Start with most preferred aggregation level
3072 --
3073 l_mv_agg_lvl_tbl.extend;
3074 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 6 ; /* org reason code level*/
3075
3076 l_mv_agg_lvl_tbl.extend;
3077 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 2; /* org inv cat reason level */
3078
3079 l_mv_agg_lvl_tbl.extend;
3080 l_mv_agg_lvl_tbl(l_mv_agg_lvl_tbl.COUNT).value := 0; /* Most Granular Level*/
3081
3082 --
3083 -- calculate the most effecient level for the combination of parameters
3084 --
3085 l_agg_lvl_flag := get_agg_level(l_mv_agg_lvl_tbl, l_in_bmap);
3086 RETURN to_char(l_agg_lvl_flag);
3087 END get_trx_reason_aggr_flag;
3088
3089
3090 /*++++++++++++++++++++++++++++++++++++++++*/
3091 /* Selecting the MV for Product Gross Margin
3092 /*++++++++++++++++++++++++++++++++++++++++*/
3093 /*
3094 This Function is called from process_parameters if the p_mv_set is related to
3095 Product Gross Margin. The following are the various combinations for all the MVs selected
3096 depending on the mv_level_flag and the Dimension Values.
3097 -OPI_PGM_CAT_MV
3098 -OPI_PGM_SUM_MV
3099 -Inline View.
3100 */
3101
3102 FUNCTION select_mv (p_mv_set IN VARCHAR2, p_mv_level_flag IN VARCHAR2, p_view_by IN VARCHAR2,
3103 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
3104 RETURN VARCHAR2
3105 IS
3106 l_mv VARCHAR2(2000) := '';
3107 l_cat_val VARCHAR2 (120) := NULL;
3108 l_time_val VARCHAR2 (1) := NULL;
3109 BEGIN
3110 IF (p_dim_map.exists ('ITEM+ENI_ITEM_VBH_CAT')) THEN
3111 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_VBH_CAT').value;
3112 END IF;
3113
3114 IF (p_view_by = ('TIME+FII_TIME_ENT_YEAR') OR p_view_by = ('TIME+FII_TIME_ENT_QTR')
3115 OR p_view_by = ('TIME+FII_TIME_ENT_PERIOD') OR p_view_by = ('TIME+FII_TIME_WEEK')) THEN
3116 l_time_val := '1';
3117 END IF;
3118
3119 IF ((l_cat_val = 'All' OR l_cat_val IS NULL) AND l_time_val IS NULL AND
3120 (p_mv_level_flag = '5' OR p_mv_level_flag = '7'))
3121 THEN
3122 l_mv := 'OPI_PGM_CAT_MV';
3123 ELSIF (l_cat_val <> 'All' AND l_cat_val IS NOT NULL)
3124 THEN
3125 --
3126 -- Bug 3896892 : Add secondary global currency columns to inline query
3127 -- 1. fulfilled_val_sg
3128 -- 2. cogs_val_sg
3129 --
3130 l_mv := ' (select
3131 v.imm_child_id item_category_id,
3132 v.parent_id parent_id,
3133 f.fulfilled_val_b FULFILLED_VAL_B,
3134 f.fulfilled_val_g FULFILLED_VAL_G,
3135 f.cogs_val_b COGS_VAL_B,
3136 f.cogs_val_g COGS_VAL_G,
3137 f.fulfilled_qty FULFILLED_QTY,
3138 f.customer_item_cat_flag CUSTOMER_ITEM_CAT_FLAG,
3139 f.item_org_id ITEM_ORG_ID,
3140 f.uom_code UOM_CODE,
3141 f.time_id TIME_ID,
3142 f.organization_id ORGANIZATION_ID,
3143 f.top_model_org_id TOP_MODEL_ORG_ID,
3144 f.customer_ID CUSTOMER_ID,
3145 f.fulfilled_val_sg,
3146 f.cogs_val_sg
3147 from
3148 opi_pgm_sum_mv f,
3149 eni_denorm_hierarchies v,
3150 mtl_default_category_sets m
3151 where
3152 m.functional_area_id = 11
3153 and v.object_id = m.category_set_id
3154 and v.dbi_flag = ''Y''
3155 and v.object_type = ''CATEGORY_SET''
3156 and f.item_category_id = v.child_id
3157 )';
3158 ELSE
3159 l_mv := 'OPI_PGM_SUM_MV';
3160 END IF;
3161 RETURN l_mv;
3162 END select_mv;
3163
3164 /* percent_str
3165
3166 Gets the string for percentage change of two specified strings.
3167 Better than copying CASE statements everywhere
3168 */
3169 FUNCTION percent_str (p_numerator IN VARCHAR2,
3170 p_denominator IN VARCHAR2,
3171 p_measure_name IN VARCHAR2)
3172 RETURN VARCHAR2
3173 IS
3174 l_percentage_calc VARCHAR2 (600) := '';
3175 l_nvl_denominator VARCHAR2 (100) := nvl_str (p_denominator);
3176 l_nvl_numerator VARCHAR2 (100) := nvl_str (p_numerator);
3177
3178 BEGIN
3179
3180 l_percentage_calc :=
3181 'CASE WHEN ' || l_nvl_denominator || ' = 0 THEN to_number (NULL)
3182 ' || ' ELSE (' || l_nvl_numerator || '/' || p_denominator || ') *100
3183 ' || 'END
3184 ' || p_measure_name || ' ';
3185
3186 return l_percentage_calc;
3187
3188 END percent_str;
3189
3190 /* percent_str_basic
3191
3192 Gets the string for percentage change of two specified strings.
3193 Better than copying CASE statements everywhere.
3194 No NVLs on numerator.
3195 */
3196 FUNCTION percent_str_basic (p_numerator IN VARCHAR2,
3197 p_denominator IN VARCHAR2,
3198 p_measure_name IN VARCHAR2)
3199 RETURN VARCHAR2
3200 IS
3201 l_percentage_calc VARCHAR2 (600) := '';
3202 l_nvl_denominator VARCHAR2 (100) := nvl_str (p_denominator);
3203
3204 BEGIN
3205
3206 l_percentage_calc :=
3207 'CASE WHEN ' || l_nvl_denominator || ' = 0 THEN to_number (NULL)
3208 ' || ' WHEN ' || p_numerator || ' IS NULL THEN to_number (NULL)
3209 ' || ' ELSE (' || p_numerator || '/' || p_denominator || ') *100
3210 ' || 'END
3211 ' || p_measure_name || ' ';
3212
3213 return l_percentage_calc;
3214
3215 END percent_str_basic;
3216
3217
3218 /* pos_denom_percent_str
3219
3220 Gets the string for percentage change of two specified strings if
3221 the denominator is positive and greater than 0.
3222 Better than copying CASE statements everywhere.
3223 */
3224 FUNCTION pos_denom_percent_str (p_numerator IN VARCHAR2,
3225 p_denominator IN VARCHAR2,
3226 p_measure_name IN VARCHAR2)
3227 RETURN VARCHAR2
3228 IS
3229 l_percentage_calc VARCHAR2 (600) := '';
3230 l_nvl_denominator VARCHAR2 (100) := nvl_str (p_denominator);
3231 l_nvl_numerator VARCHAR2 (100) := nvl_str (p_numerator);
3232
3233 BEGIN
3234
3235 l_percentage_calc :=
3236 'CASE WHEN ' || l_nvl_denominator || ' <= 0 THEN to_number (NULL)
3237 ' || ' ELSE (' || l_nvl_numerator || '/' || p_denominator || ') *100
3238 ' || 'END
3239 ' || p_measure_name || ' ';
3240
3241 return l_percentage_calc;
3242
3243 END pos_denom_percent_str;
3244
3245 /* pos_denom_percent_str_basic
3246
3247 Gets the string for percentage change of two specified strings if
3248 the denominator is positive and greater than 0.
3249 Better than copying CASE statements everywhere.
3250 No NVLs on numerator.
3251 */
3252 FUNCTION pos_denom_percent_str_basic (p_numerator IN VARCHAR2,
3253 p_denominator IN VARCHAR2,
3254 p_measure_name IN VARCHAR2)
3255 RETURN VARCHAR2
3256 IS
3257 l_percentage_calc VARCHAR2 (600) := '';
3258 l_nvl_denominator VARCHAR2 (100) := nvl_str (p_denominator);
3259
3260 BEGIN
3261
3262 l_percentage_calc :=
3263 'CASE WHEN ' || l_nvl_denominator || ' <= 0 THEN to_number (NULL)
3264 ' || ' WHEN ' || p_numerator || ' IS NULL THEN to_number (NULL)
3265 ' || ' ELSE (' || p_numerator || '/' || p_denominator || ') *100
3266 ' || 'END
3267 ' || p_measure_name || ' ';
3268
3269 return l_percentage_calc;
3270
3271 END pos_denom_percent_str_basic;
3272
3273 /* change_str
3274 Get the percentage change string. Better than writing out all the case
3275 statements
3276 */
3277 FUNCTION change_str (p_new_numerator IN VARCHAR2,
3278 p_old_numerator IN VARCHAR2,
3279 p_denominator IN VARCHAR2,
3280 p_measure_name IN VARCHAR2)
3281 RETURN VARCHAR2
3282 IS
3283 l_change_calc VARCHAR2 (1000) := '';
3284 l_nvl_denominator VARCHAR2 (100) := nvl_str (p_denominator);
3285 l_nvl_new_numerator VARCHAR2 (100) := nvl_str (p_new_numerator);
3286 l_nvl_old_numerator VARCHAR2 (100) := nvl_str (p_old_numerator);
3287
3288 BEGIN
3289
3290 l_change_calc :=
3291 'CASE WHEN ' || l_nvl_denominator || ' = 0 THEN to_number (NULL)
3292 ' || ' ELSE ((' || l_nvl_new_numerator || ' - ' || l_nvl_old_numerator
3293 || ')/ abs (' || p_denominator || ')) * 100
3294 ' || 'END
3295 ' || p_measure_name || ' ';
3296
3297 RETURN l_change_calc;
3298 END change_str;
3299
3300 /* change_str_basic
3301 Get the percentage change string. Better than writing out all the case
3302 statements.
3303 No NVLs on numerator.
3304 */
3305 FUNCTION change_str_basic (p_new_numerator IN VARCHAR2,
3306 p_old_numerator IN VARCHAR2,
3307 p_denominator IN VARCHAR2,
3308 p_measure_name IN VARCHAR2)
3309 RETURN VARCHAR2
3310 IS
3311 l_change_calc VARCHAR2 (1000) := '';
3312 l_nvl_denominator VARCHAR2 (100) := nvl_str (p_denominator);
3313
3314 BEGIN
3315
3316 l_change_calc :=
3317 'CASE WHEN ' || l_nvl_denominator || ' = 0 THEN to_number (NULL)
3318 ' || ' WHEN ' || p_new_numerator || ' IS NULL THEN to_number (NULL)
3319 ' || ' WHEN ' || p_old_numerator || ' IS NULL THEN to_number (NULL)
3320 ' || ' ELSE ((' || p_new_numerator || ' - ' || p_old_numerator
3321 || ')/ abs (' || p_denominator || ')) * 100
3322 ' || 'END
3323 ' || p_measure_name || ' ';
3324
3325 RETURN l_change_calc;
3326 END change_str_basic;
3327
3328 /* change_pct_str
3329 Get the change in percentage string. Better than writing out all the case
3330 statements
3331 */
3332 FUNCTION change_pct_str (p_new_numerator IN VARCHAR2,
3333 p_new_denominator IN VARCHAR2,
3334 p_old_numerator IN VARCHAR2,
3335 p_old_denominator IN VARCHAR2,
3336 p_measure_name IN VARCHAR2)
3337 RETURN VARCHAR2
3338 IS
3339 l_change_pct_calc VARCHAR2 (1000) := '';
3340 l_nvl_new_denominator VARCHAR2 (100) := nvl_str (p_new_denominator);
3341 l_nvl_old_denominator VARCHAR2 (100) := nvl_str (p_old_denominator);
3342 l_nvl_new_numerator VARCHAR2 (100) := nvl_str (p_new_numerator);
3343 l_nvl_old_numerator VARCHAR2 (100) := nvl_str (p_old_numerator);
3344
3345 BEGIN
3346
3347 l_change_pct_calc :=
3348 'CASE WHEN ' || l_nvl_old_denominator || ' = 0 THEN to_number (NULL)
3349 ' || 'WHEN ' || l_nvl_new_denominator || ' = 0 THEN to_number (NULL)
3350 ' || ' ELSE ((' || l_nvl_new_numerator || '/'
3351 || l_nvl_new_denominator ||
3352 ') -
3353 ' || '(' || l_nvl_old_numerator || '/'
3354 || l_nvl_old_denominator || '))*100
3355 ' || 'END
3356 ' || p_measure_name || ' ';
3357
3358 RETURN l_change_pct_calc;
3359
3360 END change_pct_str;
3361
3362 /* change_pct_str_basic
3363 Get the change in percentage string. Better than writing out all the case
3364 statements. No NVLs on numerator.
3365 */
3366 FUNCTION change_pct_str_basic (p_new_numerator IN VARCHAR2,
3367 p_new_denominator IN VARCHAR2,
3368 p_old_numerator IN VARCHAR2,
3369 p_old_denominator IN VARCHAR2,
3370 p_measure_name IN VARCHAR2)
3371 RETURN VARCHAR2
3372 IS
3373 l_change_pct_calc VARCHAR2 (1000) := '';
3374 l_nvl_new_denominator VARCHAR2 (100) := nvl_str (p_new_denominator);
3375 l_nvl_old_denominator VARCHAR2 (100) := nvl_str (p_old_denominator);
3376 l_nvl_new_numerator VARCHAR2 (100) := nvl_str (p_new_numerator);
3377 l_nvl_old_numerator VARCHAR2 (100) := nvl_str (p_old_numerator);
3378
3379 BEGIN
3380
3381 l_change_pct_calc :=
3382 'CASE WHEN ' || l_nvl_old_denominator || ' = 0 THEN to_number (NULL)
3383 ' || 'WHEN ' || l_nvl_new_denominator || ' = 0 THEN to_number (NULL)
3384 ' || 'WHEN ' || p_new_numerator || ' IS NULL THEN to_number (NULL)
3385 ' || 'WHEN ' || p_old_numerator || ' IS NULL THEN to_number (NULL)
3386 ' || ' ELSE ((' || p_new_numerator || '/'
3387 || l_nvl_new_denominator ||
3388 ') -
3389 ' || '(' || p_old_numerator || '/'
3390 || l_nvl_old_denominator || '))*100
3391 ' || 'END
3392 ' || p_measure_name || ' ';
3393
3394 RETURN l_change_pct_calc;
3395
3396 END change_pct_str_basic;
3397
3398
3399 /* rate_str
3400 Calculates a rate given a numerator and denominator;
3401 p_rate_type = 'P' indicates percentage inputs
3402 p_rate_type = 'NP' indicates absolute inputs
3403 */
3404
3405 FUNCTION rate_str (p_numerator IN VARCHAR2,
3406 p_denominator IN VARCHAR2,
3407 p_rate_type IN VARCHAR2 := 'P')
3408 RETURN VARCHAR2
3409 IS
3410 BEGIN
3411 -- if rate is a ratio
3412 if(p_rate_type = 'NP') then
3413 return '(' || p_numerator || '/decode(' || p_denominator || ',0,null,'
3414 || p_denominator || '))';
3415 end if;
3416
3417 -- if rate is a percent
3418 return '((nvl(' || p_numerator || ',0)/decode(' || p_denominator || ',0,null,'
3419 || p_denominator || '))*100)';
3420 END rate_str;
3421
3422
3423 /* nvl_str
3424 Convert a string into its NVL (str, val)
3425 The default NVL value is 0
3426 */
3427 FUNCTION nvl_str (p_str IN VARCHAR2,
3428 p_default_val IN NUMBER := 0)
3429 RETURN VARCHAR2
3430 IS
3431 BEGIN
3432
3433 return ('nvl (' || p_str || ', ' || p_default_val || ')');
3434
3435 END nvl_str;
3436
3437 /*
3438 If the value of the string is NEGATIVE, return NULL.
3439 Else return itself.
3440 */
3441 FUNCTION neg_str(p_str IN VARCHAR2)
3442 RETURN VARCHAR2
3443 IS
3444 BEGIN
3445
3446 return ('decode (sign (' || p_str || '), -1, NULL, ' || p_str || ') ');
3447
3448 END neg_str;
3449
3450 /* raw_str
3451 If the string is NULL, return NULL.
3452 Else return itself.
3453 */
3454 FUNCTION raw_str (p_str IN VARCHAR2)
3455 RETURN VARCHAR2
3456 IS
3457 BEGIN
3458
3459 return (' CASE WHEN ' || p_str || ' IS NULL THEN
3460 to_number (NULL)
3461 ELSE ' || p_str || '
3462 END ');
3463
3464 END raw_str;
3465
3466
3467 /*----------------------------------------------------------------------
3468 Function performs a bitand of p_dim_bmap with each vaue in p_mv_lvl_tbl
3469 If the result is same as the value return it
3470 If no record satisfies the check, return the most granular level
3471
3472 If p_mv_lvl_tbl is not initialized return -1 to signal error
3473 -----------------------------------------------------------------------*/
3474 FUNCTION get_agg_level (p_mv_lvl_tbl IN opi_dbi_rpt_util_pkg.MV_AGG_LVL_TBL,
3475 p_dim_bmap IN NUMBER)
3476 RETURN NUMBER
3477 IS
3478 NO_INITIALIZE EXCEPTION; /* raise exception when table is not initialized */
3479
3480 BEGIN
3481 IF nvl(p_mv_lvl_tbl.count, -1) > 0 THEN
3482 FOR cntr IN p_mv_lvl_tbl.FIRST .. p_mv_lvl_tbl.LAST LOOP
3483 IF bitand(p_mv_lvl_tbl(cntr).value, p_dim_bmap) = p_mv_lvl_tbl(cntr).value
3484 THEN
3485 RETURN p_mv_lvl_tbl(cntr).value;
3486 END IF;
3487 END LOOP;
3488 RETURN (p_mv_lvl_tbl(p_mv_lvl_tbl.LAST).value);
3489 ELSE
3490 RAISE NO_INITIALIZE;
3491 END IF;
3492 EXCEPTION
3493 WHEN NO_INITIALIZE THEN
3494 RETURN (-1);
3495 END get_agg_level;
3496
3497 /* Build the fact view by columns string using the join table
3498 for queries using windowing.
3499 */
3500 FUNCTION get_fact_select_columns (p_join_tbl IN
3501 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
3502 RETURN VARCHAR2
3503 IS
3504 l_fact_select_cols VARCHAR2(400);
3505 BEGIN
3506
3507 l_fact_select_cols := '';
3508
3509 FOR l_num IN p_join_tbl.first .. p_join_tbl.last
3510 LOOP
3511 IF (p_join_tbl.exists(l_num)) THEN
3512 l_fact_select_cols := l_fact_select_cols ||
3513 p_join_tbl(l_num).fact_column || ',
3514 ';
3515 END IF;
3516 END LOOP;
3517 -- trim trailing comma and carriage returns, and add a space
3518 l_fact_select_cols := rtrim (l_fact_select_cols, ',
3519 ') || ' ';
3520
3521 return l_fact_select_cols;
3522
3523 END get_fact_select_columns;
3524
3525 /*
3526 For viewby = item_org, various reports have to display
3527 a description and unit of measure
3528 */
3529 PROCEDURE get_viewby_item_columns (p_dim_name VARCHAR2,
3530 p_description OUT NOCOPY VARCHAR2,
3531 p_uom OUT NOCOPY VARCHAR2)
3532
3533 IS
3534 l_description varchar2(30);
3535 l_uom varchar2(30);
3536
3537 BEGIN
3538 CASE
3539 WHEN p_dim_name = 'ITEM+ENI_ITEM_ORG' THEN
3540 BEGIN
3541 p_description := 'v.description';
3542 p_uom := 'v2.unit_of_measure';
3543 END;
3544 ELSE
3545 BEGIN
3546 p_description := 'null';
3547 p_uom := 'null';
3548 END;
3549 END CASE;
3550
3551 END get_viewby_item_columns;
3552
3553 /* Replace_n
3554
3555 Replace a substring of a given string with a different string n times.
3556 The substring is removed if the replacement string is NULL.
3557
3558 Parameters:
3559 p_orig_str - Original string
3560 p_match_str - Pattern (substring) to be matched in p_orig_str
3561 p_replace_str - Pattern to replace p_match_str with. If NULL, then
3562 p_match_str is removed from replace_once.
3563 p_start_pos - Starting position for replacements in original string.
3564 p_num_times - Number of times replacements is required
3565
3566 Date Author Action
3567 07/11/05 Dinkar Gupta Wrote Function
3568
3569 */
3570 FUNCTION replace_n (p_orig_str IN VARCHAR2,
3571 p_match_str IN VARCHAR2,
3572 p_replace_str IN VARCHAR2,
3573 p_start_pos IN NUMBER,
3574 p_num_times IN NUMBER)
3575 RETURN VARCHAR2
3576 IS
3577 -- {
3578
3579 l_init_str VARCHAR2 (32767);
3580
3581 l_new_str VARCHAR2 (32767);
3582
3583 l_pos NUMBER;
3584
3585 -- }
3586 BEGIN
3587 -- {
3588
3589 IF (p_num_times < 0 OR p_start_pos < 1) THEN
3590 -- {
3591 l_new_str := replace (p_orig_str, p_match_str, p_replace_str);
3592 -- }
3593 ELSE
3594 -- {
3595 -- pick the right starting position
3596 l_new_str := NULL;
3597 l_new_str := substr (p_orig_str, p_start_pos);
3598
3599 -- don't lose the unreplaced part
3600 l_init_str := NULL;
3601 l_init_str := substr (p_orig_str, 1, p_start_pos - 1);
3602
3603 FOR l_num_times IN 1 .. p_num_times
3604 LOOP
3605 -- {
3606
3607 l_pos := instr (l_new_str, p_match_str);
3608 IF (l_pos > 0 AND l_pos < length (l_new_str)) THEN
3609 -- {
3610 l_init_str := l_init_str ||
3611 substr (l_new_str, 1, l_pos - 1) ||
3612 p_replace_str;
3613 l_new_str := substr (l_new_str, l_pos + length (p_match_str));
3614 -- }
3615 END IF;
3616
3617 -- }
3618 END LOOP;
3619
3620 -- add in any originally ignored bits
3621 l_new_str := l_init_str || l_new_str;
3622
3623 -- }
3624 END IF;
3625
3626 return l_new_str;
3627 -- }
3628 END replace_n;
3629
3630 /* -------------------------------------------------------------------------------------------
3631 Procedure Name: set_inv_convergence_date
3632 Parameters : x_return_status (OUT parameter)
3633 Purpose : This procedure is only called by OPM core team for a
3634 customer that is migrating from some older release (11i's)
3635 to Release 12. When this procedure is called we would
3636 insert the trunc of the sysdate into one of our DBI log
3637 table, which would be used by all ETL's as the R12
3638 migration date.
3639 ----------------------------------------------------------------------------------------------
3640 */
3641
3642 PROCEDURE set_inv_convergence_date (x_return_status OUT NOCOPY VARCHAR2)
3643 IS
3644 l_stmt_id NUMBER;
3645 l_inv_convergence_date DATE;
3646 l_proc_name CONSTANT VARCHAR2 (60):= 'set_inv_convergence_date';
3647
3648 BEGIN
3649 --{
3650 l_stmt_id := 0;
3651 --Initialization of variables
3652 x_return_status := 'N';
3653
3654 l_stmt_id := 10;
3655 SELECT trunc(sysdate)
3656 INTO l_inv_convergence_date
3657 FROM dual;
3658
3659 l_stmt_id := 20;
3660 merge_inv_convergence_date(p_migration_date=>
3661 l_inv_convergence_date);
3662 l_stmt_id := 30;
3663 --return success to the calling code
3664 x_return_status := 'Y';
3665
3666 EXCEPTION
3667 --{
3668 WHEN OTHERS THEN
3669 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
3670 l_proc_name || ' ' ||
3671 '#' || l_stmt_id ||
3672 ': ' || SQLERRM);
3673 x_return_status := 'N';
3674 --}
3675 --}
3676
3677 END set_inv_convergence_date;
3678
3679 /* -------------------------------------------------------------------------------------------
3680 Procedure Name: get_inv_convergence_date
3681 Parameters : p_inv_migration_date (OUT parameter)
3682 Purpose : This procedure is called by DBI ETL's in the areas of
3683 Manufacturing and Inventory to get the R12 migration date,
3684 which was inserted by the 'set'procedure into one of DBI
3685 log table viz. OPI_DBI_CONC_PROG_RUN_LOG.
3686 If such date is not found in the log table, we look at
3687 the max of the last update in the Inventory Balances Table
3688 viz. IC_LOCT_INV.
3689 For a new customer directly going into R12, the above 2
3690 would not give any date, so in that case we simply use the
3691 sysdate for the purpose.
3692 The procedure insert_inv_convergence_date is used to insert
3693 the date in the log table in case it was not earlier
3694 present there.
3695 ----------------------------------------------------------------------------------------------
3696 */
3697
3698 PROCEDURE get_inv_convergence_date (p_inv_migration_date OUT NOCOPY DATE)
3699 IS
3700
3701 l_stmt_id NUMBER;
3702 l_inv_migration_date ic_loct_inv.last_update_date%type;
3703 l_rowcount NUMBER;
3704 l_proc_name CONSTANT VARCHAR2 (60):= 'get_inv_convergence_date';
3705
3706 BEGIN
3707 --{
3708 l_stmt_id := 0;
3709 l_rowcount := 0;
3710 p_inv_migration_date := trunc(sysdate);
3711
3712 l_stmt_id := 10;
3713 SELECT count (*)
3714 INTO l_rowcount
3715 FROM
3716 OPI_DBI_CONC_PROG_RUN_LOG
3717 WHERE
3718 ETL_TYPE = 'R12_MIGRATION';
3719
3720 --get the R12 Convergence Date from the Log Table
3721 l_stmt_id := 20;
3722 IF l_rowcount <> 0 THEN
3723 --{
3724 SELECT
3725 last_run_date
3726 INTO
3727 l_inv_migration_date
3728 FROM
3729 opi_dbi_conc_prog_run_log
3730 WHERE
3731 etl_type = 'R12_MIGRATION';
3732 p_inv_migration_date := l_inv_migration_date;
3733 --}
3734 ELSE
3735 --{
3736 SELECT
3737 MAX(last_update_date)
3738 INTO
3739 l_inv_migration_date
3740 FROM
3741 ic_loct_inv
3742 WHERE
3743 migrated_ind = 1;
3744 l_stmt_id :=40;
3745 IF l_inv_migration_date IS NOT NULL THEN
3746 --{
3747 p_inv_migration_date := trunc (l_inv_migration_date);
3748 merge_inv_convergence_date (p_migration_date =>
3749 p_inv_migration_date);
3750 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
3751 l_proc_name || ' ' ||
3752 '#' || l_stmt_id ||
3753 ': ' || 'R12 migration date is not available in log. Using maximum of last
3754 update date from Inventory Balances Table');
3755 --}
3756 ELSE
3757 --{
3758 p_inv_migration_date := trunc (sysdate);
3759 merge_inv_convergence_date(p_migration_date =>
3760 p_inv_migration_date);
3761 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
3762 l_proc_name || ' ' ||
3763 '#' || l_stmt_id ||
3764 ': ' || 'Using sysdate as R12 migration date i.e.
3765 the date on which the respective Initial Load was run');
3766 --}
3767 END IF;
3768 --}
3769 END IF;
3770
3771 EXCEPTION
3772 --{
3773 WHEN NO_DATA_FOUND THEN
3774 p_inv_migration_date := trunc (sysdate);
3775 merge_inv_convergence_date(p_migration_date =>
3776 p_inv_migration_date);
3777 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
3778 l_proc_name || ' ' ||
3779 '#' || l_stmt_id ||
3780 ': ' || 'Using sysdate as R12 migration date i.e.
3781 the date on which the respective Initial Load was run');
3782 WHEN OTHERS THEN
3783 BIS_COLLECTION_UTILITIES.PUT_LINE (s_pkg_name || '.' ||
3784 l_proc_name || ' ' ||
3785 '#' || l_stmt_id ||
3786 ': ' || SQLERRM);
3787 --}
3788 --}
3789
3790 END get_inv_convergence_date;
3791
3792 /* -------------------------------------------------------------------------------------------
3793 Procedure Name: merge_inv_convergence_date
3794 Parameters : p_migration_date (IN parameter)
3795 Purpose : This is one centralized merge procedure to merge a row into
3796 the log table with the R12 migration date with ETL_TYPE as
3797 'R12_MIGRATION'.The commit to the log table after inserting
3798 /upadting this row would be done in this procedure only.
3799 ----------------------------------------------------------------------------------------------
3800 */
3801
3802 PROCEDURE merge_inv_convergence_date (p_migration_date IN DATE)
3803 IS
3804 l_user_id NUMBER;
3805 l_login_id NUMBER;
3806 l_program_id NUMBER;
3807 l_program_login_id NUMBER;
3808 l_program_application_id NUMBER;
3809 l_request_id NUMBER;
3810 l_stmt_id NUMBER;
3811 BEGIN
3812 --{
3813 l_stmt_id := 0;
3814 --Initialization of variables
3815 l_user_id := nvl(fnd_global.user_id, -1);
3816 l_login_id := nvl(fnd_global.login_id, -1);
3817 l_program_id := nvl (fnd_global.conc_program_id, -1);
3818 l_program_login_id := nvl (fnd_global.conc_login_id, -1);
3819 l_program_application_id := nvl (fnd_global.prog_appl_id, -1);
3820 l_request_id := nvl (fnd_global.conc_request_id, -1);
3821
3822 l_stmt_id := 10;
3823 MERGE INTO OPI_DBI_CONC_PROG_RUN_LOG log
3824 USING (
3825 SELECT
3826 'R12_MIGRATION' ETL_TYPE,
3827 p_migration_date LAST_RUN_DATE , -- R12 migration date
3828 sysdate CREATION_DATE,
3829 sysdate LAST_UPDATE_DATE,
3830 l_user_id CREATED_BY,
3831 l_user_id LAST_UPDATED_BY,
3832 l_login_id LAST_UPDATE_LOGIN,
3833 l_request_id REQUEST_ID,
3834 l_program_application_id PROGRAM_APPLICATION_ID,
3835 l_program_id PROGRAM_ID,
3836 l_program_login_id PROGRAM_LOGIN_ID,
3837 '-1' DRIVING_TABLE_CODE,
3838 '-1' LOAD_TYPE,
3839 '-1' BOUND_TYPE,
3840 NULL BOUND_LEVEL_ENTITY_CODE,
3841 NULL BOUND_LEVEL_ENTITY_ID,
3842 NULL FROM_BOUND_DATE,
3843 NULL TO_BOUND_DATE,
3844 NULL FROM_BOUND_ID,
3845 NULL TO_BOUND_ID,
3846 NULL COMPLETION_STATUS_CODE,
3847 NULL STOP_REASON_CODE
3848 FROM
3849 dual) migration_data
3850 ON (log.etl_type = migration_data.etl_type
3851 )
3852 WHEN MATCHED THEN
3853 UPDATE SET
3854 log.last_run_date = migration_data.last_run_date
3855 ,log.last_update_date = migration_data.last_update_date
3856 ,log.last_updated_by = migration_data.last_updated_by
3857 ,log.last_update_login = migration_data.last_update_login
3858 WHEN NOT MATCHED THEN
3859 INSERT (ETL_TYPE, LAST_RUN_DATE, CREATION_DATE, LAST_UPDATE_DATE, CREATED_BY,
3860 LAST_UPDATED_BY, LAST_UPDATE_LOGIN, REQUEST_ID, PROGRAM_APPLICATION_ID,
3861 PROGRAM_ID, PROGRAM_LOGIN_ID, DRIVING_TABLE_CODE, LOAD_TYPE, BOUND_TYPE,
3862 BOUND_LEVEL_ENTITY_CODE, BOUND_LEVEL_ENTITY_ID, FROM_BOUND_DATE,
3863 TO_BOUND_DATE, FROM_BOUND_ID, TO_BOUND_ID, COMPLETION_STATUS_CODE,
3864 STOP_REASON_CODE)
3865 VALUES (migration_data.etl_type, migration_data.LAST_RUN_DATE, migration_data.CREATION_DATE,
3866 migration_data.LAST_UPDATE_DATE, migration_data.CREATED_BY, migration_data.LAST_UPDATED_BY,
3867 migration_data.LAST_UPDATE_LOGIN, migration_data.REQUEST_ID, migration_data.PROGRAM_APPLICATION_ID,
3868 migration_data.PROGRAM_ID, migration_data.PROGRAM_LOGIN_ID, migration_data.DRIVING_TABLE_CODE,
3869 migration_data.LOAD_TYPE, migration_data.BOUND_TYPE, migration_data.BOUND_LEVEL_ENTITY_CODE,
3870 migration_data.BOUND_LEVEL_ENTITY_ID, migration_data.FROM_BOUND_DATE, migration_data.TO_BOUND_DATE,
3871 migration_data.FROM_BOUND_ID, migration_data.TO_BOUND_ID, migration_data.COMPLETION_STATUS_CODE,
3872 migration_data.STOP_REASON_CODE);
3873
3874 --Commit the above insertion
3875 l_stmt_id := 20;
3876 commit;
3877 --}
3878 END merge_inv_convergence_date;
3879
3880
3881 FUNCTION OPI_UM_CONVERT (
3882 p_item_id number,
3883 p_item_qty number,
3884 p_from_unit varchar2,
3885 p_to_unit varchar2 ) RETURN number
3886 IS
3887 l_stmt_num NUMBER;
3888 l_debug_msg VARCHAR2(1000);
3889 l_proc_name VARCHAR2 (60);
3890 l_uom_rate number;
3891 l_uom_qty number;
3892
3893 BEGIN
3894 l_proc_name := 'OPI_UM_CONVERT';
3895
3896 --l_uom_rate := NULL;
3897 if ( p_from_unit <> p_to_unit )then
3898
3899 inv_convert.inv_um_conversion(p_from_unit , p_to_unit , p_item_id, l_uom_rate);
3900 if ( l_uom_rate = -99999 ) then
3901 --RAISE UOM_CONV_ERROR;
3902 OPI_DBI_RPT_UTIL_PKG.g_pk_uom_conversion := 0;
3903 l_debug_msg := 'Conversion not found from unit '|| p_from_unit || 'to unit' || p_to_unit || 'for item id ' || p_item_id ;
3904 OPI_DBI_BOUNDS_PKG.write(s_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
3905 end if;
3906 else
3907 l_uom_rate := 1;
3908 end if;
3909 /** Default precision for inventory was 6 decimals
3910 Changed the default precision to 5 since INV supports a standard
3911 precision of 5 decimal places.
3912 */
3913 l_uom_qty := l_uom_rate * p_item_qty;
3914 l_uom_qty := round(l_uom_qty, 5);
3915
3916 RETURN l_uom_qty;
3917
3918
3919 EXCEPTION
3920 WHEN OTHERS THEN
3921 l_debug_msg := 'Conversion not found from unit '|| p_from_unit || 'to unit' || p_to_unit || 'for item id ' || p_item_id || SQLcode || ' - ' ||SQLERRM;
3922 OPI_DBI_BOUNDS_PKG.write(s_pkg_name,l_proc_name,l_stmt_num,l_debug_msg);
3923 RAISE;
3924
3925 END OPI_UM_CONVERT;
3926
3927 END OPI_DBI_RPT_UTIL_PKG;