1 PACKAGE BODY ISC_DBI_SUTIL_PKG AS
2 /*$Header: iscdbisutilb.pls 120.1 2005/06/14 11:25:38 appldev $ */
3
4
5 /*++++++++++++++++++++++++++++++++++++++++*/
6 /* Local Functions
7 /*++++++++++++++++++++++++++++++++++++++++*/
8 PROCEDURE init_dim_map (p_dim_map out NOCOPY
9 poa_dbi_util_pkg.poa_dbi_dim_map,
10 p_mv_set IN VARCHAR2);
11
12 FUNCTION get_mv (p_mv_set IN VARCHAR2,
13 p_mv_level_flag IN VARCHAR2,
14 p_view_by IN VARCHAR2,
15 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
16 RETURN VARCHAR2;
17
18 FUNCTION get_col_name (p_dim_name VARCHAR2)
19 RETURN VARCHAR2;
20
21 FUNCTION get_table (p_dim_name VARCHAR2)
22 RETURN VARCHAR2;
23
24 FUNCTION get_security_where_clause(p_org_value IN VARCHAR2, p_trend IN VARCHAR2 DEFAULT 'N', p_mv_set IN VARCHAR2)
25 RETURN VARCHAR2;
26
27 FUNCTION get_additional_where_clause(p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map, p_mv_set IN VARCHAR2, p_view_by IN VARCHAR2)
28 RETURN VARCHAR2;
29
30 FUNCTION get_mv_flag_where_clause (p_mv_flag_type IN VARCHAR2, p_trend IN VARCHAR2 DEFAULT 'N',
31 p_mv IN VARCHAR2 DEFAULT '', p_mv_where_clause IN VARCHAR2)
32 RETURN VARCHAR2;
33
34 FUNCTION get_flag_where_clause(p_trend IN VARCHAR2 DEFAULT 'N')
35 RETURN VARCHAR2;
36
37 FUNCTION get_flag_where_clause2(p_trend IN VARCHAR2 DEFAULT 'N')
38 RETURN VARCHAR2;
39
40 PROCEDURE update_col_name (p_dim_map IN out NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map,
41 p_mv_set IN VARCHAR2,
42 p_dim_name VARCHAR2);
43
44 PROCEDURE get_join_info (p_view_by IN varchar2,
45 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
46 x_join_tbl OUT NOCOPY
47 poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
48 p_mv_set IN VARCHAR2);
49
50 PROCEDURE populate_in_join_tbl(p_in_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_in_join_tbl,
51 p_view_by in VARCHAR2,
52 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map);
53
54 FUNCTION get_mv_level_flag (p_mv_flag_type VARCHAR2,
55 p_dim_name VARCHAR2,
56 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
57 RETURN VARCHAR2;
58
59 FUNCTION get_flag_one_val (p_dim_name VARCHAR2,
60 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
61 RETURN VARCHAR2;
62
63 FUNCTION get_flag_two_val (p_dim_name VARCHAR2,
64 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
65 RETURN VARCHAR2;
66
67 FUNCTION get_flag_three_val (p_dim_name VARCHAR2,
68 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
69 RETURN VARCHAR2;
70
71 FUNCTION get_flag_four_val (p_dim_name VARCHAR2,
72 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
73 RETURN VARCHAR2;
74
75 FUNCTION get_flag_five_val (p_dim_name VARCHAR2,
76 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
77 RETURN VARCHAR2;
78
79 FUNCTION get_mv_where_clause_flag (p_mv IN VARCHAR2)
80 RETURN VARCHAR2;
81
82 /*++++++++++++++++++++++++++++++++++++++++*/
83 /* Function Definitions
84 /*++++++++++++++++++++++++++++++++++++++++*/
85
86 /* process_parameters
87
88 Generic routine to process the parameters passed in from the PMV
89 page.
90
91 Points of note:
92 p_mv_level_flag - all report queries using this
93 package will use a flag to decide which rows of
94 their MVs they will need to query.
95
96 */
97 PROCEDURE process_parameters (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
98 p_view_by OUT NOCOPY VARCHAR2,
99 p_view_by_col_name OUT NOCOPY VARCHAR2,
100 p_comparison_type OUT NOCOPY VARCHAR2,
101 p_xtd OUT NOCOPY VARCHAR2,
102 p_cur_suffix OUT NOCOPY VARCHAR2,
103 p_where_clause OUT NOCOPY VARCHAR2,
104 p_mv OUT NOCOPY VARCHAR2,
105 p_join_tbl OUT NOCOPY
106 poa_dbi_util_pkg.poa_dbi_join_tbl,
107 p_mv_level_flag OUT NOCOPY VARCHAR2,
108 p_trend IN VARCHAR2,
109 p_func_area IN VaRCHAR2,
110 p_version IN VARCHAR2,
111 p_role IN VARCHAR2,
112 p_mv_set IN VARCHAR2,
113 p_mv_flag_type IN VARCHAR2 DEFAULT 'NONE',
114 p_in_join_tbl OUT NOCOPY
115 poa_dbi_util_pkg.poa_dbi_in_join_tbl)
116 IS
117 l_dim_map poa_dbi_util_pkg.poa_dbi_dim_map;
118 l_dim_bmap NUMBER;
119 l_org_val VARCHAR2 (120);
120
121 l_as_of_date DATE;
122 l_prev_as_of_date DATE;
123 l_nested_pattern NUMBER;
124
125 l_mv_where_clause VARCHAR2(1); -- Determines if MV Flag Where Clause needs to be appended.
126
127 l_stmt_id NUMBER;
128
129 BEGIN
130
131 l_mv_where_clause := 'Y';
132 l_stmt_id := 0;
133
134 -- initialize the dimension map with all the required dimensions.
135 l_dim_bmap := 0;
136 l_org_val := NULL;
137 l_stmt_id := 10;
138 init_dim_map (p_dim_map => l_dim_map,
139 p_mv_set => p_mv_set);
140
141 -- Get the various parameter values from the utility package.
142 -- This package will also compute the l_dim_bmap
143 l_stmt_id := 20;
144 poa_dbi_util_pkg.get_parameter_values (p_param => p_param,
145 p_dim_map => l_dim_map,
146 p_view_by => p_view_by,
147 p_comparison_type => p_comparison_type,
148 p_xtd => p_xtd,
149 p_as_of_date => l_as_of_date,
150 p_prev_as_of_date => l_prev_as_of_date,
151 p_cur_suffix => p_cur_suffix,
152 p_nested_pattern => l_nested_pattern,
153 p_dim_bmap => l_dim_bmap);
154
155 l_stmt_id := 25;
156 -- In certain cases, we may need to use different dimension cols from the MVs
157 update_col_name(l_dim_map,p_mv_set,p_view_by);
158
159 -- Find out the view by column name
160 l_stmt_id := 30;
161 IF (l_dim_map.exists (p_view_by)) THEN
162 p_view_by_col_name := l_dim_map(p_view_by).col_name;
163 END IF;
164
165 -- Get the org values
166 IF (l_dim_map.exists ('ORGANIZATION+ORGANIZATION')) THEN
167 l_org_val := l_dim_map ('ORGANIZATION+ORGANIZATION').value;
168 END IF;
169
170 -- Finally get the join info based on the dimension level parameters
171 -- passed in.
172 l_stmt_id := 40;
173 get_join_info (p_view_by => p_view_by,
174 p_dim_map => l_dim_map,
175 x_join_tbl => p_join_tbl,
176 p_mv_set => p_mv_set);
177
178 -- Get the flag value for MV aggregation
179 l_stmt_id := 50;
180 p_mv_level_flag := get_mv_level_flag (p_mv_flag_type => p_mv_flag_type,
181 p_dim_name => p_view_by,
182 p_dim_map => l_dim_map);
183
184 -- Get the MV we need to join to.
185 l_stmt_id := 60;
186 p_mv := get_mv (p_mv_set => p_mv_set,
187 p_mv_level_flag => p_mv_level_flag,
188 p_view_by => p_view_by,
189 p_dim_map => l_dim_map);
190
191 -- Determine if MV Flag Where Clause needs to be appended. By default the value is 'Y'
192 l_stmt_id := 70;
193 l_mv_where_clause := get_mv_where_clause_flag (p_mv => p_mv);
194
195 -- Get the dimension level specific where clauses
196 -- and the security where clause.
197 l_stmt_id := 80;
198 p_where_clause := poa_dbi_util_pkg.get_where_clauses (p_dim_map => l_dim_map,
199 p_trend => p_trend);
200
201 -- Attach the optional MV flag where clause
202 l_stmt_id := 90;
203 p_where_clause := p_where_clause ||
204 get_mv_flag_where_clause (p_mv_flag_type => p_mv_flag_type,
205 p_trend => p_trend,
206 p_mv => p_mv,
207 p_mv_where_clause => l_mv_where_clause);
208
209 -- Attach the security clause
210 l_stmt_id := 100;
211 p_where_clause := p_where_clause ||
212 get_security_where_clause (p_org_value => l_org_val,
213 p_trend => p_trend,
214 p_mv_set => p_mv_set);
215
216 -- Attach the additional where clauses
217 l_stmt_id := 110;
218 p_where_clause := p_where_clause ||
219 get_additional_where_clause (p_dim_map => l_dim_map,
220 p_mv_set => p_mv_set,
221 p_view_by => p_view_by);
222
223 l_stmt_id := 120;
224 -- Add extra join conditions (if necessary)
225 populate_in_join_tbl(p_in_join_tbl => p_in_join_tbl,
226 p_view_by => p_view_by,
227 p_dim_map => l_dim_map);
228
229
230 END process_parameters;
231
232 /*++++++++++++++++++++++++++++++++++++++++*/
233 /* To determine if the MV where flag clause
234 is required.
235 /*++++++++++++++++++++++++++++++++++++++++*/
236 /* get_mv_where_clause_flag
237 If the MV definition does not have a
238 MV Level Flag or an aggregation
239 level flag return 'N' else return 'Y'
240 */
241 FUNCTION get_mv_where_clause_flag (p_mv IN VARCHAR2)
242 RETURN VARCHAR2
243 IS
244 l_mv_where_clause_flag VARCHAR2(1);
245 BEGIN
246 l_mv_where_clause_flag :=
247 (CASE p_mv
248 WHEN 'ISC_DBI_CFM_003_MV' THEN
249 'N'
250 ELSE
251 'Y'
252 END);
253 RETURN l_mv_where_clause_flag;
254 END get_mv_where_clause_flag;
255
256 /*++++++++++++++++++++++++++++++++++++++++*/
257 /* Where clause building routine
258 /*++++++++++++++++++++++++++++++++++++++++*/
259 /* get_mv_flag_where_clause
260
261 Depending on which MV flag is being used, get a different
262 where clause statement. The flag type is determined by p_mv_flag_type:
263 'FLAG1' - service_level, unsecured org, carrier
264 'FLAG2' - service_level, carrier
265 'FLAG3' - unsecured org,customer for isc_trn_005_mv; item_category & customer for isc_dbi_fm_0000_mv
266 'FLAG4' - subinventory, inventory category
267 'FLAG5' - inventory category, item
268
269 */
270 FUNCTION get_mv_flag_where_clause (p_mv_flag_type IN VARCHAR2,
271 p_trend IN VARCHAR2 DEFAULT 'N',
272 p_mv IN VARCHAR2 DEFAULT '',
273 p_mv_where_clause IN VARCHAR2)
274 RETURN VARCHAR2
275 IS
276
277 l_mv_flag_where_clause VARCHAR2 (200);
278
279 BEGIN
280 l_mv_flag_where_clause := '';
281
282 IF (p_mv_where_clause = 'Y') THEN
283 l_mv_flag_where_clause :=
284 (CASE p_mv_flag_type
285 WHEN 'FLAG1' THEN
286 get_flag_where_clause (p_trend)
287 WHEN 'FLAG2' THEN
288 get_flag_where_clause (p_trend)
289 WHEN 'FLAG3' THEN
290 get_flag_where_clause (p_trend)
291 WHEN 'FLAG4' THEN
292 get_flag_where_clause (p_trend)
293 WHEN 'FLAG5' THEN
294 get_flag_where_clause2 (p_trend)
295 ELSE
296 ''
297 END);
298 END IF;
299
300 RETURN l_mv_flag_where_clause;
301
305 /* get_flag_where_clause
302 END get_mv_flag_where_clause;
303
304
306 Return the where clause for ISC specific agg_level flag.
307 Can be used for flag1, flag2, flag3, flag4
308 */
309 FUNCTION get_flag_where_clause(p_trend IN VARCHAR2 DEFAULT 'N')
310 RETURN VARCHAR2
311 IS
312 l_flag_where_clause VARCHAR2 (200);
313
314 BEGIN
315
316 l_flag_where_clause := 'AND fact.agg_level = :ISC_AGG_FLAG ';
317 RETURN l_flag_where_clause;
318
319 END get_flag_where_clause;
320
321 /* get_flag_where_clause2
322 Return the where clause for ISC specific agg_level flag.
323 Can be used for flag5
324 */
325 FUNCTION get_flag_where_clause2(p_trend IN VARCHAR2 DEFAULT 'N')
326 RETURN VARCHAR2
327 IS
328 l_flag_where_clause2 VARCHAR2 (200);
329
330 BEGIN
331
332 l_flag_where_clause2 := 'AND fact.agg_level = :ISC_AGG_FLAG2 ';
333 RETURN l_flag_where_clause2;
334
335 END get_flag_where_clause2;
336
337 /* get_security_where_clause
338 For ISC, this is quite simple.
339 */
340
341 FUNCTION get_security_where_clause(p_org_value IN VARCHAR2, p_trend IN VARCHAR2 DEFAULT 'N', p_mv_set IN VARCHAR2)
342 RETURN VARCHAR2
343 IS
344
345 l_sec_where_clause VARCHAR2(1000);
346
347 BEGIN
348
349 l_sec_where_clause :='';
350
351 if(p_org_value is null or p_org_value = '' or p_org_value = 'All')
352 then l_sec_where_clause :=
353 (CASE p_mv_set
354 WHEN 'BQ1' THEN ' ' -- no organization security for TM reports
355 WHEN 'BY1' THEN ' '
356 WHEN 'BW1' THEN ' '
357 WHEN 'BX1' THEN ' '
358 WHEN 'BZ1' THEN ' '
359 WHEN 'BP1' THEN ' '
360 WHEN 'BP2' THEN ' '
361 WHEN 'BT1' THEN ' '
362 WHEN 'BU1' THEN ' '
363 WHEN 'C01' THEN ' '
364 WHEN 'C11' THEN ' '
365 WHEN 'C21' THEN ' '
366 WHEN 'C31' THEN ' '
367 WHEN 'C32' THEN ' '
368 WHEN 'C41' THEN ' '
369 WHEN 'C42' THEN ' '
370 ELSE 'AND (EXISTS
371 (SELECT 1
372 FROM org_access o
373 WHERE o.responsibility_id = fnd_global.resp_id
374 AND o.resp_application_id = fnd_global.resp_appl_id
375 AND o.organization_id = fact.inv_org_id)
376 OR EXISTS
377 (SELECT 1
378 FROM mtl_parameters org
379 WHERE org.organization_id = fact.inv_org_id
380 AND NOT EXISTS
381 (SELECT 1
382 FROM org_access ora
383 WHERE org.organization_id = ora.organization_id)))'
384 END);
385 end if;
386
387 return l_sec_where_clause;
388
389 END get_security_where_clause;
390
391
392 /*++++++++++++++++++++++++++++++++++++++++*/
393 /* Functions to get the MV
394 /*++++++++++++++++++++++++++++++++++++++++*/
395 /* get_mv
396
397 Gets the MV for the content group concerned.
398
399 The p_mv_set parameter is used to determine which MV is being
400 used i.e. when p_mv_set:
401 'BQ1','BY1','BZ1','C01' - Rated Freight Cost per Unit Weight/Vol so use isc_trn_000_mv
402 'C11','C21' - Rated Freight Cost per Dist so use isc_trn_006_mv
403 'BW1','BX1' - On-Time Arrival Rate/Trend so use isc_trn_001_mv
404 'BP1' - Trip Stop Arrival Performance Trend: use isc_trn_001_mv
405 'BP2' - Trip Stop Arrival Performance Trend: use isc_trn_002_mv
406 'BT1','BU1' - Freight Cost Recovery Rate: use isc_trn_005_mv or isc_dbi_fm_0000_mv
407 'RS1' - WMS Release To Ship: use isc_wms_000_mv
408 'RS2' - WMS Release To Ship: use isc_wms_001_mv
409
410 */
411 FUNCTION get_mv (p_mv_set IN VARCHAR2,
412 p_mv_level_flag IN VARCHAR2,
413 p_view_by IN VARCHAR2,
414 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
415 RETURN VARCHAR2
416
417 IS
418 l_mv_name VARCHAR2(1000);
419 l_bt1_mv VARCHAR2(1000);
420 l_item_val VARCHAR2 (120);
421 l_cust_val VARCHAR2 (120);
422 l_item_needed boolean;
423 l_cust_needed boolean;
424
425 BEGIN
426 l_mv_name := '';
427 l_bt1_mv := '';
428 l_item_val := NULL;
429 l_cust_val := NULL;
430 l_item_needed:=false;
431 l_cust_needed:=false;
432
433
434 -- For Freight Cost Recovery Rate Report/Trend, figure out which MV to hit
435 IF (p_mv_set = 'BT1' OR p_mv_set = 'BU1' ) THEN
436
437 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
438 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
439 END IF;
440
441 IF (p_dim_map.exists ('CUSTOMER+FII_CUSTOMERS')) THEN
442 l_cust_val := p_dim_map ('CUSTOMER+FII_CUSTOMERS').value;
443 END IF;
444
445 -- If customer or item is needed, use isc_dbi_fm_0000_mv, else use isc_trn_005_mv
446 IF ( (l_item_val IS NULL OR l_item_val = 'All')
447 AND p_view_by <> 'ITEM+ENI_ITEM_ORG') THEN
448 l_item_needed := false;
449 ELSE l_item_needed := true;
450 END IF;
451
452 IF ( (l_cust_val IS NULL OR l_cust_val = 'All')
453 AND p_view_by <> 'CUSTOMER+FII_CUSTOMERS') THEN
454 l_cust_needed := false;
455 ELSE l_cust_needed := true;
456 END IF;
457
458 IF ( l_cust_needed OR l_item_needed ) THEN
459 l_bt1_mv:='isc_dbi_fm_0000_mv';
460 ELSE l_bt1_mv:= 'isc_trn_005_mv';
461 END IF;
462 END IF;
463
464 -- Based on the program calling, use different MVs
465 l_mv_name :=
466 (CASE p_mv_set
470 WHEN 'BY1' THEN 'isc_trn_000_mv'
467 WHEN 'BQ1' THEN 'isc_trn_000_mv'
468 WHEN 'BW1' THEN 'isc_trn_001_mv'
469 WHEN 'BX1' THEN 'isc_trn_001_mv'
471 WHEN 'BZ1' THEN 'isc_trn_000_mv'
472 WHEN 'C01' THEN 'isc_trn_000_mv'
473 WHEN 'C11' THEN 'isc_trn_006_mv'
474 WHEN 'C21' THEN 'isc_trn_006_mv'
475 WHEN 'BP1' THEN 'isc_trn_001_mv'
476 WHEN 'BP2' THEN 'isc_trn_002_mv'
477 WHEN 'C31' THEN 'isc_trn_003_mv'
478 WHEN 'C32' THEN 'isc_trn_004_mv'
479 WHEN 'C41' THEN 'isc_trn_003_mv'
480 WHEN 'C42' THEN 'isc_trn_004_mv'
481 WHEN 'BT1' THEN l_bt1_mv
482 WHEN 'BU1' THEN l_bt1_mv
483 WHEN 'RS1' THEN 'isc_wms_000_mv'
484 WHEN 'RS2' THEN 'isc_wms_001_mv'
485 ELSE ''
486 END);
487
488 RETURN l_mv_name;
489
490 END get_mv;
491
492 /*++++++++++++++++++++++++++++++++++++++++*/
493 /* Function to add extra where clauses if needed
494 /*++++++++++++++++++++++++++++++++++++++++*/
495 /* get_additional_where_clause
496
497 This function adds additional filter conditions
498 Used when p_mv_set:
499 'BT1','BU1' - Freight Cost Recovery Rate - filter on top_node_flag if it is needed and specifies the extra where
500 clauses if hitting ISC_DBI_FM_0000_MV and prod cat has been specified.
501
502 */
503 FUNCTION get_additional_where_clause (p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
504 p_mv_set IN VARCHAR2,p_view_by IN VARCHAR2)
505 RETURN VARCHAR2
506
507 IS
508 l_item_val VARCHAR2 (120);
509 l_cust_val VARCHAR2 (120);
510 l_pcat_val VARCHAR2 (120);
511 l_item_needed boolean;
512 l_cust_needed boolean;
513 l_where_clause VARCHAR2 (10000);
514
515 BEGIN
516
517 l_where_clause := '';
518
519 -- For Freight Cost Recovery Rate Report/Trend, figure out if top_node_flag where clause is needed
520 IF (p_mv_set = 'BT1' OR p_mv_set = 'BU1' ) THEN
521 l_where_clause := ' AND (fact.freight_charge_amt_g is not null OR fact.freight_cost_amt_g is not null)';
522 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
523 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
524 END IF;
525
526 IF (p_dim_map.exists ('CUSTOMER+FII_CUSTOMERS')) THEN
527 l_cust_val := p_dim_map ('CUSTOMER+FII_CUSTOMERS').value;
528 END IF;
529
530 IF (p_dim_map.exists ('ITEM+ENI_ITEM_VBH_CAT')) THEN
531 l_pcat_val := p_dim_map ('ITEM+ENI_ITEM_VBH_CAT').value;
532 END IF;
533
534 -- Top node Where clause is only needed when isc_trn_005_mv is used and Product Category is 'All'
535 IF ( (l_item_val IS NULL OR l_item_val = 'All')
536 AND p_view_by <> 'ITEM+ENI_ITEM_ORG') THEN
537 l_item_needed := false;
538 ELSE l_item_needed := true;
539 END IF;
540
541 IF ( (l_cust_val IS NULL OR l_cust_val = 'All')
542 AND p_view_by <> 'CUSTOMER+FII_CUSTOMERS') THEN
543 l_cust_needed := false;
544 ELSE l_cust_needed := true;
545 END IF;
546
547 IF ( NOT l_cust_needed AND NOT l_item_needed ) THEN
548 IF (l_pcat_val IS NULL OR l_pcat_val = 'All') THEN
549 l_where_clause := l_where_clause || '
550 AND fact.top_node_flag = ''Y'' ';
551 END IF;
552 END IF;
553
554 -- Only add this where clause if the prod cat is specified
555 IF NOT (l_pcat_val IS NULL OR l_pcat_val = 'All') THEN
556 IF l_cust_needed OR l_item_needed THEN -- extra joins are needed if hitting ISC_DBI_FM_0000_MV
557 l_where_clause := l_where_clause || ' AND fact.prod_category_id = eni_cat.child_id
558 AND eni_cat.parent_id IN &ITEM+ENI_ITEM_VBH_CAT
559 AND eni_cat.dbi_flag = ''Y''
560 AND eni_cat.object_type = ''CATEGORY_SET''
561 AND eni_cat.object_id = mdcs.category_set_id
562 AND mdcs.functional_area_id = 11 ';
563 ELSE l_where_clause := l_where_clause || ' AND fact.prod_category_id in &ITEM+ENI_ITEM_VBH_CAT ';
564 END IF;
565 END IF;
566
567 END IF;
568
569 RETURN l_where_clause;
570
571 END get_additional_where_clause;
572
573
574 PROCEDURE populate_in_join_tbl(
575 p_in_join_tbl out NOCOPY poa_dbi_util_pkg.poa_dbi_in_join_tbl,
576 p_view_by in VARCHAR2,
577 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
578 IS
579
580 l_in_join_rec poa_dbi_util_pkg.POA_DBI_IN_JOIN_REC;
581 l_pcat_val VARCHAR2 (120);
582 l_cust_val VARCHAR2 (120);
583 l_item_val VARCHAR2 (120);
584 l_item_needed boolean;
585 l_cust_needed boolean;
586
587 BEGIN
588
589 p_in_join_tbl := poa_dbi_util_pkg.poa_dbi_in_join_tbl();
590
591 IF (p_dim_map.exists ('ITEM+ENI_ITEM_VBH_CAT')) THEN
592 l_pcat_val := p_dim_map ('ITEM+ENI_ITEM_VBH_CAT').value;
593 END IF;
594
595 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
596 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
597 END IF;
598
599 IF (p_dim_map.exists ('CUSTOMER+FII_CUSTOMERS')) THEN
600 l_cust_val := p_dim_map ('CUSTOMER+FII_CUSTOMERS').value;
601 END IF;
602
603 IF ( (l_item_val IS NULL OR l_item_val = 'All')
604 AND p_view_by <> 'ITEM+ENI_ITEM_ORG') THEN
605 l_item_needed := false;
606 ELSE l_item_needed := true;
607 END IF;
608
609 IF ( (l_cust_val IS NULL OR l_cust_val = 'All')
613 END IF;
610 AND p_view_by <> 'CUSTOMER+FII_CUSTOMERS') THEN
611 l_cust_needed := false;
612 ELSE l_cust_needed := true;
614
615 -- If a product category has been specified and you're hitting ISC_DBI_FM_0000_MV
616 IF (l_item_needed OR l_cust_needed) AND
617 NOT (l_pcat_val IS NULL OR l_pcat_val = 'All') THEN
618 l_in_join_rec.table_name := 'eni_denorm_hierarchies';
619 l_in_join_rec.table_alias := 'eni_cat';
620 p_in_join_tbl.extend;
621 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
622
623 l_in_join_rec.table_name := 'mtl_default_category_sets';
624 l_in_join_rec.table_alias := 'mdcs';
625 p_in_join_tbl.extend;
626 p_in_join_tbl(p_in_join_tbl.count) := l_in_join_rec;
627
628 END IF;
629
630 END populate_in_join_tbl;
631
632
633 /*++++++++++++++++++++++++++++++++++++++++*/
634 /* Setting up list of dimensions to track
635 /*++++++++++++++++++++++++++++++++++++++++*/
636 /* init_dim_map
637
638 Initialize the dimension map with all needed dimensions.
639
640 This function needs to keep track of all possible dimensions
641 the DBI 7.1 reports are interested in. The POA utility package
642 get_parameter_values functions looks at the parameter table
643 passed in by PMV. For parameters names for which it finds a
644 matching key in this dimension map table, it records the value.
645 In other words, if the dimension map does not have an entry for
646 ORGANIZATION+ORGANIZATION, then PMV's organization parameter
647 will never be recorded.
648
649 For ISC's DBI 7.1, the needed dimensions levels are:
650 ORGANIZATION+ORGANIZATION - Organization
651 ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE - Transportation Mode
652 ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER - Freight Carrier
653 ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL - Service Level
654 CURRENCY+FII_CURRENCIES - Currency
655 ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION - Shipment Direction
656 ITEM+ENI_ITEM_INV_CAT - Inventory Category
657 ITEM+ENI_ITEM_ORG - Item
658 ITEM+ENI_ITEM_VBH_CAT - Product Category
659 CUSTOMER+FII_CUSTOMERS - Customers
660 ORGANIZATION+ORGANIZATION_SUBINVENTORY - Subinventory
661 ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION - Unsecured Organization
662
663
664 */
665 PROCEDURE init_dim_map (p_dim_map out NOCOPY
666 poa_dbi_util_pkg.poa_dbi_dim_map,
667 p_mv_set IN VARCHAR2)
668 IS
669
670 l_dim_rec poa_dbi_util_pkg.poa_dbi_dim_rec;
671
672 BEGIN
673
674 -- Inventory Category dimension level
675 l_dim_rec.col_name := get_col_name ('ITEM+ENI_ITEM_INV_CAT');
676 l_dim_rec.view_by_table := get_table ('ITEM+ENI_ITEM_INV_CAT');
677 l_dim_rec.generate_where_clause := 'Y';
678 p_dim_map('ITEM+ENI_ITEM_INV_CAT') := l_dim_rec;
679
680 -- Item dimension level
681 l_dim_rec.col_name := get_col_name ('ITEM+ENI_ITEM_ORG');
682 l_dim_rec.view_by_table := get_table ('ITEM+ENI_ITEM_ORG');
683 l_dim_rec.generate_where_clause := 'Y';
684 p_dim_map('ITEM+ENI_ITEM_ORG') := l_dim_rec;
685
686 -- Organzation dimension level
687 l_dim_rec.col_name := get_col_name ('ORGANIZATION+ORGANIZATION');
688 l_dim_rec.view_by_table := get_table('ORGANIZATION+ORGANIZATION');
689 l_dim_rec.generate_where_clause := 'Y';
690 p_dim_map('ORGANIZATION+ORGANIZATION') := l_dim_rec;
691
692 -- Unsecured Organzation dimension level
693 l_dim_rec.col_name := get_col_name ('ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION');
694 l_dim_rec.view_by_table := get_table('ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION');
695 l_dim_rec.generate_where_clause := 'Y';
696 p_dim_map('ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION') := l_dim_rec;
697
698 -- Mode dimension level
699 l_dim_rec.col_name := get_col_name ('ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE');
700 l_dim_rec.view_by_table := get_table('ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE');
701 l_dim_rec.generate_where_clause := 'Y';
702 p_dim_map('ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE') := l_dim_rec;
703
704 -- Carrier Dimension Level
705 l_dim_rec.col_name := get_col_name ('ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER');
706 l_dim_rec.view_by_table := get_table('ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER');
707 l_dim_rec.generate_where_clause := 'Y';
708 p_dim_map('ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER') := l_dim_rec;
709
710 -- Service Level Dimension Level
711 l_dim_rec.col_name := get_col_name ('ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL');
712 l_dim_rec.view_by_table := get_table('ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL');
713 l_dim_rec.generate_where_clause := 'Y';
714 p_dim_map('ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL') := l_dim_rec;
715
716 -- Shipment Direction Dimension Level
717 l_dim_rec.col_name := get_col_name ('ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION');
718 l_dim_rec.view_by_table := get_table('ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION');
719 l_dim_rec.generate_where_clause := 'Y';
720 p_dim_map('ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION') := l_dim_rec;
721
722 -- Product Category Dimension Level
723 l_dim_rec.col_name := get_col_name ('ITEM+ENI_ITEM_VBH_CAT');
724 l_dim_rec.view_by_table := get_table('ITEM+ENI_ITEM_VBH_CAT');
725 l_dim_rec.generate_where_clause := 'N';
726 p_dim_map('ITEM+ENI_ITEM_VBH_CAT') := l_dim_rec;
727
728 -- Customer dimension level
729 l_dim_rec.col_name := get_col_name ('CUSTOMER+FII_CUSTOMERS');
730 l_dim_rec.view_by_table := get_table('CUSTOMER+FII_CUSTOMERS');
731 l_dim_rec.generate_where_clause := 'Y';
732 p_dim_map('CUSTOMER+FII_CUSTOMERS') := l_dim_rec;
733
737 l_dim_rec.generate_where_clause := 'Y';
734 -- Subinventory dimension level
735 l_dim_rec.col_name := get_col_name ('ORGANIZATION+ORGANIZATION_SUBINVENTORY');
736 l_dim_rec.view_by_table := get_table('ORGANIZATION+ORGANIZATION_SUBINVENTORY');
738 p_dim_map('ORGANIZATION+ORGANIZATION_SUBINVENTORY') := l_dim_rec;
739
740 END init_dim_map;
741
742
743 /*++++++++++++++++++++++++++++++++++++++++*/
744 /* Dimension level join tables and columns
745 /*++++++++++++++++++++++++++++++++++++++++*/
746 /* get_col_name
747
748 Get the column name of the viewby join tables that the query will
749 have to join to.
750 */
751 FUNCTION get_col_name (p_dim_name VARCHAR2)
752 RETURN VARCHAR2
753 IS
754
755 l_col_name VARCHAR2(100);
756
757 BEGIN
758
759 l_col_name :=
760 (CASE p_dim_name
761 WHEN 'ORGANIZATION+ORGANIZATION' THEN 'inv_org_id'
762 WHEN 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION' THEN 'inv_org_id'
763 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN 'item_category_id'
764 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN 'prod_category_id'--product category
765 WHEN 'ITEM+ENI_ITEM_ORG' THEN 'item_id'
766 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN 'customer_id'
767 WHEN 'ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE' THEN 'mode_of_transport'
768 WHEN 'ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER' THEN 'carrier_id'
769 WHEN 'ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL' THEN 'service_level'
770 WHEN 'ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION' THEN 'shipment_direction'
771 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN 'subinventory_code'
772 ELSE ''
773 END);
774
775 RETURN l_col_name;
776
777 END get_col_name;
778
779 /*++++++++++++++++++++++++++++++++++++++++*/
780 /* Dimension level columns
781 /*++++++++++++++++++++++++++++++++++++++++*/
782 /* update_col_name
783
784 Update the name of the viewby dimension columns the report will
785 hit in the MVs.
786 */
787 PROCEDURE update_col_name (p_dim_map IN out NOCOPY poa_dbi_util_pkg.poa_dbi_dim_map, p_mv_set IN VARCHAR2, p_dim_name VARCHAR2)
788 IS
789
790 l_pcat_val VARCHAR2 (120);
791 l_item_val VARCHAR2 (120);
792 l_cust_val VARCHAR2 (120);
793 l_item_needed boolean;
794 l_cust_needed boolean;
795
796 BEGIN
797
798 -- For Freight Cost Recovery Rate Report, we will use prod_category_id or imm_child_id when viewby=prod cat
799 IF ( p_mv_set = 'BT1' AND p_dim_map.exists ('ITEM+ENI_ITEM_VBH_CAT')) THEN
800
801 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
802 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
803 END IF;
804
805 IF (p_dim_map.exists ('CUSTOMER+FII_CUSTOMERS')) THEN
806 l_cust_val := p_dim_map ('CUSTOMER+FII_CUSTOMERS').value;
807 END IF;
808
809 IF ( (l_item_val IS NULL OR l_item_val = 'All')
810 AND p_dim_name <> 'ITEM+ENI_ITEM_ORG') THEN
811 l_item_needed := false;
812 ELSE l_item_needed := true;
813 END IF;
814
815 IF ( (l_cust_val IS NULL OR l_cust_val = 'All')
816 AND p_dim_name <> 'CUSTOMER+FII_CUSTOMERS') THEN
817 l_cust_needed := false;
818 ELSE l_cust_needed := true;
819 END IF;
820
821
822 l_pcat_val := p_dim_map ('ITEM+ENI_ITEM_VBH_CAT').value;
823 -- We want to use imm_child_id column when we are hitting ISC_TRN_005_MV (when neither item or customer are needed)
824 IF ( NOT (l_pcat_val IS NULL OR l_pcat_val = 'All') AND
825 NOT l_cust_needed AND
826 NOT l_item_needed) THEN
827 p_dim_map('ITEM+ENI_ITEM_VBH_CAT').col_name := 'imm_child_id';
828 END IF;
829 END IF;
830
831 END update_col_name;
832
833
834 /* get_table
835
836 Return the join table based on the dimension
837 Product Category - eni_item_vbh_cat_v
838 Customer - fii_customers_v
839 */
840 FUNCTION get_table (p_dim_name VARCHAR2)
841 RETURN VARCHAR2
842 IS
843 l_table VARCHAR2(4000);
844
845 BEGIN
846
847 l_table :=
848 (CASE p_dim_name
849 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN 'eni_item_inv_cat_v'
850 WHEN 'ITEM+ENI_ITEM_ORG' THEN 'eni_item_org_v '
851 WHEN 'ORGANIZATION+ORGANIZATION' THEN '(select organization_id id, name value from hr_all_organization_units_tl where language = userenv(''LANG''))'
852 WHEN 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION' THEN 'opi_inv_unsec_organizations_v'
853 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN 'fii_customers_v'
854 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN 'eni_item_vbh_nodes_v'
855 WHEN 'ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE' THEN 'isc_transportation_mode_v'
856 WHEN 'ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER' THEN 'isc_freight_carrier_v'
857 WHEN 'ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL' THEN 'isc_carrier_service_level_v'
858 WHEN 'ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION' THEN 'isc_shipment_direction_v'
859 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN 'opi_subinventories_v'
860 ELSE ''
861 END);
862
863 RETURN l_table;
864
865 END get_table;
866
867 /* Function: get_join_info
868 */
869 PROCEDURE get_join_info (p_view_by IN varchar2,
870 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map,
871 x_join_tbl OUT NOCOPY
872 poa_dbi_util_pkg.POA_DBI_JOIN_TBL,
873 p_mv_set IN VARCHAR2)
874 IS
875 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
876
877 BEGIN
878
879 -- reinitialize the join table
883 -- there is nothing to join to. Can this ever be true?
880 x_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
881
882 -- If the view by column is not in the bitmap, then
884 IF (NOT p_dim_map.exists(p_view_by)) THEN
885 RETURN;
886 END IF;
887
888 -- Otherwise, join to a table
889 -- The view by table
890 l_join_rec.table_name := p_dim_map(p_view_by).view_by_table;
891 l_join_rec.table_alias := 'v';
892 -- the fact column to join to
893 l_join_rec.fact_column := p_dim_map(p_view_by).col_name;
894
895 -- depending on the dimension level, select the appropriate
896 -- join table column name
897 l_join_rec.column_name :=
898 (CASE p_view_by
899 WHEN 'ORGANIZATION+ORGANIZATION' THEN
900 'id'
901 WHEN 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION' THEN
902 'id'
903 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN
904 'id'
905 WHEN 'ITEM+ENI_ITEM_ORG' THEN
906 'id'
907 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
908 'id'
909 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN
910 'id'
911 WHEN 'ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE' THEN
912 'id'
913 WHEN 'ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER' THEN
914 'id'
915 WHEN 'ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL' THEN
916 'id'
917 WHEN 'ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION' THEN
918 'id'
919 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN
920 'id'
921 ELSE
922 ''
923 END);
924
925 l_join_rec.dim_outer_join :=
926 (CASE p_view_by
927 WHEN 'ORGANIZATION+ORGANIZATION' THEN
928 'N'
929 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN
930 'N'
931 WHEN 'ITEM+ENI_ITEM_ORG' THEN
932 'N'
933 ELSE
934 'N'
935 END);
936
937 l_join_rec.additional_where_clause :=
938 (CASE p_view_by
939 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
940 ' v.parent_id = v.child_id '
941 END);
942
943 -- Add the join table
944 x_join_tbl.extend;
945 x_join_tbl(x_join_tbl.count) := l_join_rec;
946
947 -- Get the uom join
948 IF (p_view_by = 'ITEM+ENI_ITEM_ORG') THEN
949 l_join_rec.table_name := 'mtl_units_of_measure_vl';
950 l_join_rec.table_alias := 'v2';
951 l_join_rec.fact_column :='uom';
952 l_join_rec.column_name := 'uom_code';
953 l_join_rec.dim_outer_join := 'N';
954
955 x_join_tbl.extend;
956 x_join_tbl(x_join_tbl.count) := l_join_rec;
957 END IF;
958
959 END get_join_info;
960
961
962 /*++++++++++++++++++++++++++++++++++++++++*/
963 /* View by information for outer queries
964 /*++++++++++++++++++++++++++++++++++++++++*/
965 /*
966 For the status_sql, get the name of the viewby column.
967 */
968 FUNCTION get_view_by_col_name (p_dim_name VARCHAR2)
969 RETURN VARCHAR2
970 IS
971 l_col_name VARCHAR2(60);
972 BEGIN
973
974 l_col_name :=
975 (CASE p_dim_name
976 WHEN 'ORGANIZATION+ORGANIZATION' THEN
977 ' v.value'
978 WHEN 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION' THEN
979 ' v.value'
980 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN
981 ' v.value'
982 WHEN 'ITEM+ENI_ITEM_ORG' THEN
983 ' v.value'
984 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
985 ' v.value'
986 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN
987 ' v.value'
988 WHEN 'ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE' THEN
989 ' v.value'
990 WHEN 'ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL' THEN
991 ' v.value'
992 WHEN 'ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER' THEN
993 ' v.value'
994 WHEN 'ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION' THEN
995 ' v.value'
996 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN
997 ' v.value'
998 ELSE ' '
999 END);
1000
1001 RETURN l_col_name;
1002 END get_view_by_col_name;
1003
1004 FUNCTION get_view_by_select_clause (p_viewby IN VARCHAR2)
1005 RETURN VARCHAR2
1006 IS
1007 l_view_by_sel VARCHAR2(200);
1008 l_view_by_col VARCHAR2 (100);
1009 BEGIN
1010
1011 l_view_by_col := get_view_by_col_name (p_viewby);
1012
1013 l_view_by_sel :=
1014 (CASE p_viewby
1015 WHEN 'ORGANIZATION+ORGANIZATION' THEN
1016 l_view_by_col || ' VIEWBY,
1017 v.id VIEWBYID, '
1018 WHEN 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION' THEN
1019 l_view_by_col || ' VIEWBY,
1020 v.id VIEWBYID, '
1021 WHEN 'ITEM+ENI_ITEM_INV_CAT' THEN
1022 l_view_by_col || ' VIEWBY,
1023 v.id VIEWBYID, '
1024 WHEN 'ITEM+ENI_ITEM_ORG' THEN
1025 l_view_by_col || ' VIEWBY ,
1026 v.id VIEWBYID, '
1027 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
1028 l_view_by_col || ' VIEWBY,
1029 v.id VIEWBYID, '
1030 WHEN 'CUSTOMER+FII_CUSTOMERS' THEN
1031 l_view_by_col || ' VIEWBY ,
1032 v.id VIEWBYID, '
1033 WHEN 'ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE' THEN
1034 l_view_by_col || ' VIEWBY ,
1035 v.id VIEWBYID, '
1039 WHEN 'ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL' THEN
1036 WHEN 'ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER' THEN
1037 l_view_by_col || ' VIEWBY ,
1038 v.id VIEWBYID, '
1040 l_view_by_col || ' VIEWBY ,
1041 v.id VIEWBYID, '
1042 WHEN 'ISC_SHIPMENT_DIRECTION+ISC_SHIPMENT_DIRECTION' THEN
1043 l_view_by_col || ' VIEWBY ,
1044 v.id VIEWBYID, '
1045 WHEN 'ORGANIZATION+ORGANIZATION_SUBINVENTORY' THEN
1046 l_view_by_col || ' VIEWBY,
1047 v.id VIEWBYID, '
1048 ELSE ''
1049 END);
1050
1051 return l_view_by_sel;
1052
1053 END get_view_by_select_clause;
1054
1055 /*++++++++++++++++++++++++++++++++++++++++*/
1056 /* MV level aggregation flag
1057 /*++++++++++++++++++++++++++++++++++++++++*/
1058
1059 /* get_mv_level_flag
1060
1061 Return the MV level flag based on what is requested in p_mv_flag_type:
1062 'FLAG1' - flag for service_level, unsecured organization, carrier.
1063 'FLAG2' - flag for service_level, carrier.
1064 'FLAG3' - flag for unsecured org, item, prod cat, and customer
1065 'FLAG4' - flag for subinventory, inventory category
1066 'FLAG5' - flag for inventory category, item
1067 */
1068 FUNCTION get_mv_level_flag (p_mv_flag_type VARCHAR2,
1069 p_dim_name VARCHAR2,
1070 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
1071 RETURN VARCHAR2
1072 IS
1073
1074 l_mv_level_flag VARCHAR2 (10);
1075
1076 BEGIN
1077
1078 l_mv_level_flag :=
1079 (CASE p_mv_flag_type
1080 WHEN 'FLAG1' THEN
1081 get_flag_one_val (p_dim_name, p_dim_map)
1082 WHEN 'FLAG2' THEN
1083 get_flag_two_val (p_dim_name, p_dim_map)
1084 WHEN 'FLAG3' THEN
1085 get_flag_three_val (p_dim_name, p_dim_map)
1086 WHEN 'FLAG4' THEN
1087 get_flag_four_val (p_dim_name, p_dim_map)
1088 WHEN 'FLAG5' THEN
1089 get_flag_five_val (p_dim_name, p_dim_map)
1090 ELSE
1091 ''
1092 END);
1093
1094 RETURN l_mv_level_flag;
1095
1096 END get_mv_level_flag;
1097
1098
1099
1100 /* get_flag_one_val
1101
1102 Compute the flag1 value based on the parameters passed to
1103 determine the aggregation level of the MV rows that the query will
1104 have to run against.
1105 */
1106 FUNCTION get_flag_one_val (p_dim_name VARCHAR2,
1107 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
1108 RETURN VARCHAR2
1109 IS
1110
1111 l_flag_one varchar2(1);
1112
1113 l_org_val VARCHAR2 (120);
1114 l_mode_val VARCHAR2 (120);
1115 l_serv_val VARCHAR2 (120);
1116 l_carrier_val VARCHAR2 (120);
1117
1118 l_org_needed boolean;
1119 l_serv_needed boolean;
1120 l_carrier_needed boolean;
1121
1122 BEGIN
1123
1124 l_org_val := NULL;
1125 l_mode_val := NULL;
1126 l_serv_val := NULL;
1127 l_carrier_val := NULL;
1128
1129 l_org_needed :=false;
1130 l_serv_needed :=false;
1131 l_carrier_needed :=false;
1132
1133 -- Get the mode, service_level, unsecured org, and carrier values
1134 IF (p_dim_map.exists ('ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION')) THEN
1135 l_org_val := p_dim_map ('ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION').value;
1136 END IF;
1137
1138 IF (p_dim_map.exists ('ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE')) THEN
1139 l_mode_val := p_dim_map ('ISC_TRANSPORTATION_MODE+ISC_TRANSPORTATION_MODE').value;
1140 END IF;
1141
1142 IF (p_dim_map.exists ('ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL')) THEN
1143 l_serv_val := p_dim_map ('ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL').value;
1144 END IF;
1145
1146 IF (p_dim_map.exists ('ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER')) THEN
1147 l_carrier_val := p_dim_map ('ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER').value;
1148 END IF;
1149
1150 -- Find out which dimensions are needed to hit the proper grouping sets in the MVs
1151 IF ( l_org_val IS NULL OR l_org_val = 'All' ) THEN
1152 l_org_needed := false;
1153 ELSE l_org_needed := true;
1154 END IF;
1155
1156 IF ( (l_serv_val IS NULL OR l_serv_val = 'All')
1157 AND p_dim_name <> 'ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL') THEN
1158 l_serv_needed := false;
1159 ELSE l_serv_needed := true;
1160 END IF;
1161
1162 IF ( (l_carrier_val IS NULL OR l_carrier_val = 'All')
1163 AND p_dim_name <> 'ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER') THEN
1164 l_carrier_needed := false;
1165 ELSE l_carrier_needed := true;
1166 END IF;
1167
1168 -- Calculate the flag values depending on which dimensions are needed
1169 CASE
1170 WHEN ( l_serv_needed AND l_org_needed AND l_carrier_needed) THEN l_flag_one := 0;
1171 WHEN ( l_serv_needed AND l_org_needed AND NOT l_carrier_needed) THEN l_flag_one := 1;
1172 WHEN ( l_serv_needed AND NOT l_org_needed AND l_carrier_needed) THEN l_flag_one := 2;
1173 WHEN ( l_serv_needed AND NOT l_org_needed AND NOT l_carrier_needed) THEN l_flag_one := 3;
1174 WHEN (NOT l_serv_needed AND l_org_needed AND l_carrier_needed) THEN l_flag_one := 4;
1175 WHEN (NOT l_serv_needed AND l_org_needed AND NOT l_carrier_needed) THEN l_flag_one := 5;
1176 WHEN (NOT l_serv_needed AND NOT l_org_needed AND l_carrier_needed) THEN l_flag_one := 6;
1177 WHEN (NOT l_serv_needed AND NOT l_org_needed AND NOT l_carrier_needed) THEN l_flag_one := 7;
1181
1178 END CASE;
1179
1180 RETURN l_flag_one;
1182 END get_flag_one_val;
1183
1184 /* get_flag_two_val
1185
1186 Compute the flag2 value based on the parameters passed to
1187 determine the aggregation level of the MV rows that the query will
1188 have to run against.
1189 */
1190 FUNCTION get_flag_two_val (p_dim_name VARCHAR2,
1191 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
1192 RETURN VARCHAR2
1193 IS
1194
1195 l_flag_two varchar2(1);
1196
1197 l_serv_val VARCHAR2 (120);
1198 l_carrier_val VARCHAR2 (120);
1199
1200 l_serv_needed boolean;
1201 l_carrier_needed boolean;
1202
1203 BEGIN
1204
1205 l_serv_val := NULL;
1206 l_carrier_val := NULL;
1207
1208 l_serv_needed:=false;
1209 l_carrier_needed:=false;
1210
1211 -- Get the service_level, and carrier values
1212 IF (p_dim_map.exists ('ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL')) THEN
1213 l_serv_val := p_dim_map ('ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL').value;
1214 END IF;
1215
1216 IF (p_dim_map.exists ('ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER')) THEN
1217 l_carrier_val := p_dim_map ('ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER').value;
1218 END IF;
1219
1220 -- Find out which dimensions are needed to hit the proper grouping sets in the MVs
1221 IF ( (l_serv_val IS NULL OR l_serv_val = 'All')
1222 AND p_dim_name <> 'ISC_CARRIER_SERVICE_LEVEL+ISC_CARRIER_SERVICE_LEVEL') THEN
1223 l_serv_needed := false;
1224 ELSE l_serv_needed := true;
1225 END IF;
1226
1227 IF ( (l_carrier_val IS NULL OR l_carrier_val = 'All')
1228 AND p_dim_name <> 'ISC_FREIGHT_CARRIER+ISC_FREIGHT_CARRIER') THEN
1229 l_carrier_needed := false;
1230 ELSE l_carrier_needed := true;
1231 END IF;
1232
1233 -- Calculate the flag values depending on which dimensions are needed
1234 CASE
1235 WHEN ( l_serv_needed AND l_carrier_needed) THEN l_flag_two := 0;
1236 WHEN ( l_serv_needed AND NOT l_carrier_needed) THEN l_flag_two := 1;
1237 WHEN (NOT l_serv_needed AND l_carrier_needed) THEN l_flag_two := 2;
1238 WHEN (NOT l_serv_needed AND NOT l_carrier_needed) THEN l_flag_two := 3;
1239 END CASE;
1240
1241 RETURN l_flag_two;
1242
1243 END get_flag_two_val;
1244
1245 /* get_flag_three_val
1246
1247 Compute the flag_three value based on the parameters passed to
1248 determine the aggregation level of the MV rows that the query will
1249 have to run against.
1250 */
1251 FUNCTION get_flag_three_val (p_dim_name VARCHAR2,
1252 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
1253 RETURN VARCHAR2
1254 IS
1255
1256 l_flag_three varchar2(1);
1257
1258 l_org_val VARCHAR2 (120);
1259 l_pcat_val VARCHAR2 (120);
1260 l_item_val VARCHAR2 (120);
1261 l_cust_val VARCHAR2 (120);
1262
1263 l_org_needed boolean;
1264 l_pcat_needed boolean;
1265 l_item_needed boolean;
1266 l_cust_needed boolean;
1267
1268 BEGIN
1269
1270 l_org_val := NULL;
1271 l_pcat_val := NULL;
1272 l_item_val := NULL;
1273 l_cust_val := NULL;
1274
1275 l_org_needed:=false;
1276 l_pcat_needed:=false;
1277 l_item_needed:=false;
1278 l_cust_needed:=false;
1279
1280 -- Get the unsecured org, item, cat, and cust values
1281 IF (p_dim_map.exists ('ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION')) THEN
1282 l_org_val := p_dim_map ('ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION').value;
1283 END IF;
1284
1285 IF (p_dim_map.exists ('ITEM+ENI_ITEM_VBH_CAT')) THEN
1286 l_pcat_val := p_dim_map ('ITEM+ENI_ITEM_VBH_CAT').value;
1287 END IF;
1288
1289 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
1290 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
1291 END IF;
1292
1293 IF (p_dim_map.exists ('CUSTOMER+FII_CUSTOMERS')) THEN
1294 l_cust_val := p_dim_map ('CUSTOMER+FII_CUSTOMERS').value;
1295 END IF;
1296
1297 -- Find out which dimensions are needed to hit the proper grouping sets in the MVs
1298 IF ( (l_org_val IS NULL OR l_org_val = 'All')
1299 AND p_dim_name <> 'ORGANIZATION+OPI_INV_UNSEC_ORGANIZATION') THEN
1300 l_org_needed := false;
1301 ELSE l_org_needed := true;
1302 END IF;
1303
1304 IF ( (l_pcat_val IS NULL OR l_pcat_val = 'All')
1305 AND p_dim_name <> 'ITEM+ENI_ITEM_VBH_CAT') THEN
1306 l_pcat_needed := false;
1307 ELSE l_pcat_needed := true;
1308 END IF;
1309
1310 IF ( (l_item_val IS NULL OR l_item_val = 'All')
1311 AND p_dim_name <> 'ITEM+ENI_ITEM_ORG') THEN
1312 l_item_needed := false;
1313 ELSE l_item_needed := true;
1314 END IF;
1315
1316 IF ( (l_cust_val IS NULL OR l_cust_val = 'All')
1317 AND p_dim_name <> 'CUSTOMER+FII_CUSTOMERS') THEN
1318 l_cust_needed := false;
1319 ELSE l_cust_needed := true;
1320 END IF;
1321
1322 -- Calculate the flag values depending on which dimensions are needed
1323 CASE
1324 WHEN ( l_cust_needed AND l_item_needed AND l_pcat_needed AND l_org_needed) THEN l_flag_three := 0;
1325 WHEN ( l_cust_needed AND l_item_needed AND l_pcat_needed AND NOT l_org_needed) THEN l_flag_three := 0;
1326 WHEN ( l_cust_needed AND l_item_needed AND NOT l_pcat_needed AND l_org_needed) THEN l_flag_three := 0;
1327 WHEN ( l_cust_needed AND l_item_needed AND NOT l_pcat_needed AND NOT l_org_needed) THEN l_flag_three := 0;
1331 WHEN ( l_cust_needed AND NOT l_item_needed AND NOT l_pcat_needed AND NOT l_org_needed) THEN l_flag_three := 2;
1328 WHEN ( l_cust_needed AND NOT l_item_needed AND l_pcat_needed AND l_org_needed) THEN l_flag_three := 0;
1329 WHEN ( l_cust_needed AND NOT l_item_needed AND l_pcat_needed AND NOT l_org_needed) THEN l_flag_three := 0;
1330 WHEN ( l_cust_needed AND NOT l_item_needed AND NOT l_pcat_needed AND l_org_needed) THEN l_flag_three := 2;
1332 WHEN (NOT l_cust_needed AND l_item_needed AND l_pcat_needed AND l_org_needed) THEN l_flag_three := 1;
1333 WHEN (NOT l_cust_needed AND l_item_needed AND l_pcat_needed AND NOT l_org_needed) THEN l_flag_three := 1;
1334 WHEN (NOT l_cust_needed AND l_item_needed AND NOT l_pcat_needed AND l_org_needed) THEN l_flag_three := 1;
1335 WHEN (NOT l_cust_needed AND l_item_needed AND NOT l_pcat_needed AND NOT l_org_needed) THEN l_flag_three := 1;
1336 WHEN (NOT l_cust_needed AND NOT l_item_needed AND l_pcat_needed AND l_org_needed) THEN l_flag_three := 0;
1337 WHEN (NOT l_cust_needed AND NOT l_item_needed AND l_pcat_needed AND NOT l_org_needed) THEN l_flag_three := 1;
1338 WHEN (NOT l_cust_needed AND NOT l_item_needed AND NOT l_pcat_needed AND l_org_needed) THEN l_flag_three := 0;
1339 WHEN (NOT l_cust_needed AND NOT l_item_needed AND NOT l_pcat_needed AND NOT l_org_needed) THEN l_flag_three := 1;
1340 END CASE;
1341
1342 RETURN l_flag_three;
1343
1344 END get_flag_three_val;
1345
1346 /* get_flag_four_val
1347
1348 Compute the flag_four value based on the parameters passed to
1349 determine the aggregation level of the MV rows that the query will
1350 have to run against.
1351 */
1352 FUNCTION get_flag_four_val (p_dim_name VARCHAR2,
1353 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
1354 RETURN VARCHAR2
1355 IS
1356
1357 l_flag_four varchar2(1);
1358
1359 l_sub_val VARCHAR2 (120);
1360 l_cat_val VARCHAR2 (120);
1361 l_item_val VARCHAR2 (120);
1362
1363 BEGIN
1364
1365 l_sub_val := NULL;
1366 l_cat_val := NULL;
1367 l_item_val := NULL;
1368
1369 -- Get the subinventory, category, and item values
1370 IF (p_dim_map.exists ('ORGANIZATION+ORGANIZATION_SUBINVENTORY')) THEN
1371 l_sub_val := p_dim_map ('ORGANIZATION+ORGANIZATION_SUBINVENTORY').value;
1372 END IF;
1373
1374 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
1375 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
1376 END IF;
1377
1378 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
1379 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
1380 END IF;
1381
1382 IF ((l_item_val IS NULL OR l_item_val = 'All') AND (l_cat_val IS NULL OR l_cat_val = 'All'))
1383 THEN
1384 IF (p_dim_name = 'ITEM+ENI_ITEM_ORG' OR p_dim_name = 'ITEM+ENI_ITEM_INV_CAT')
1385 THEN l_flag_four := '0';
1386 ELSIF (p_dim_name = 'ORGANIZATION+ORGANIZATION_SUBINVENTORY')
1387 THEN l_flag_four := '3';
1388 ELSE
1389 IF (l_sub_val IS NULL OR l_sub_val = 'All')
1390 THEN l_flag_four := '7';
1391 ELSE l_flag_four := '3';
1392 END IF;
1393 END IF;
1394 ELSE
1395 l_flag_four := '0';
1396 END IF;
1397
1398 RETURN l_flag_four;
1399
1400 END get_flag_four_val;
1401
1402 /* get_flag_five_val
1403
1404 Compute the flag5 value based on the parameters passed to
1405 determine the aggregation level of the MV rows that the query will
1406 have to run against.
1407 */
1408 FUNCTION get_flag_five_val (p_dim_name VARCHAR2,
1409 p_dim_map IN poa_dbi_util_pkg.poa_dbi_dim_map)
1410 RETURN VARCHAR2
1411 IS
1412
1413 l_flag_five varchar2(1);
1414
1415 l_cat_val VARCHAR2 (120);
1416 l_item_val VARCHAR2 (120);
1417
1418 BEGIN
1419
1420 l_cat_val := NULL;
1421 l_item_val := NULL;
1422
1423 -- Get the category and item values
1424 IF (p_dim_map.exists ('ITEM+ENI_ITEM_INV_CAT')) THEN
1425 l_cat_val := p_dim_map ('ITEM+ENI_ITEM_INV_CAT').value;
1426 END IF;
1427
1428 IF (p_dim_map.exists ('ITEM+ENI_ITEM_ORG')) THEN
1429 l_item_val := p_dim_map ('ITEM+ENI_ITEM_ORG').value;
1430 END IF;
1431
1432
1433 IF (l_item_val IS NULL OR l_item_val = 'All')
1434 THEN
1435 IF (p_dim_name = 'ITEM+ENI_ITEM_ORG')
1436 THEN l_flag_five := '0'; -- item
1437 ELSIF (p_dim_name = 'ITEM+ENI_ITEM_INV_CAT')
1438 THEN l_flag_five := '1'; -- inventory category
1439 ELSE
1440 IF (l_cat_val IS NULL OR l_cat_val = 'All')
1441 THEN l_flag_five := '3'; -- all
1442 ELSE l_flag_five := '1'; -- inventory category
1443 END IF;
1444 END IF;
1445 ELSE
1446 l_flag_five := '0'; -- item
1447 END IF;
1448
1449 RETURN l_flag_five;
1450
1451 END get_flag_five_val;
1452
1453 /* rate_str
1454
1455 Gets the string for percentage/ratio change of two specified strings.
1456 Better than copying CASE statements everywhere
1457 */
1458 FUNCTION rate_str (p_numerator IN VARCHAR2,
1459 p_denominator IN VARCHAR2,
1460 p_rate_type IN VARCHAR2,
1461 p_measure_name IN VARCHAR2)
1462 RETURN VARCHAR2
1463 IS
1464 l_nvl_denominator VARCHAR2 (100);
1465 l_nvl_numerator VARCHAR2 (100);
1466
1467 BEGIN
1468
1469 l_nvl_denominator := 'nvl('||p_denominator||',0)';
1470 l_nvl_numerator := 'nvl('||p_numerator||',0)';
1471
1472 -- if rate is a ratio
1473 if(p_rate_type = 'RATIO') then
1477 ' || p_measure_name || ' ';
1474 return 'CASE WHEN ' || l_nvl_denominator || ' = 0 THEN to_number (NULL)
1475 ' || ' ELSE (' || l_nvl_numerator || '/' || p_denominator || ')
1476 ' || 'END
1478 end if;
1479
1480 -- if rate is a percent
1481 return 'CASE WHEN ' || l_nvl_denominator || ' = 0 THEN to_number (NULL)
1482 ' || ' ELSE (' || l_nvl_numerator || '/' || p_denominator || ') * 100
1483 ' || 'END
1484 ' || p_measure_name || ' ';
1485
1486 END rate_str;
1487
1488 /* pos_denom_percent_str
1489
1490 Gets the string for percentage change of two specified strings if
1491 the denominator is positive and greater than 0.
1492 Better than copying CASE statements everywhere.
1493 */
1494 FUNCTION pos_denom_percent_str (p_numerator IN VARCHAR2,
1495 p_denominator IN VARCHAR2,
1496 p_measure_name IN VARCHAR2)
1497 RETURN VARCHAR2
1498 IS
1499 l_percentage_calc VARCHAR2 (600);
1500 l_nvl_denominator VARCHAR2 (100);
1501 l_nvl_numerator VARCHAR2 (100);
1502
1503 BEGIN
1504
1505 l_nvl_denominator := 'nvl('||p_denominator||',0)';
1506 l_nvl_numerator := 'nvl('||p_numerator||',0)';
1507
1508 l_percentage_calc :=
1509 'CASE WHEN ' || l_nvl_denominator || ' <= 0 THEN to_number (NULL)
1510 ' || ' ELSE (' || l_nvl_numerator || '/' || p_denominator || ') *100
1511 ' || 'END
1512 ' || p_measure_name || ' ';
1513
1514 return l_percentage_calc;
1515
1516 END pos_denom_percent_str;
1517
1518
1519 /* change_str
1520 Get the percentage change string. Better than writing out all the case
1521 statements
1522 */
1523 FUNCTION change_str (p_new_numerator IN VARCHAR2,
1524 p_old_numerator IN VARCHAR2,
1525 p_denominator IN VARCHAR2,
1526 p_measure_name IN VARCHAR2)
1527 RETURN VARCHAR2
1528 IS
1529 l_change_calc VARCHAR2 (1000);
1530 l_nvl_denominator VARCHAR2 (1000);
1531 l_nvl_new_numerator VARCHAR2 (1000);
1532 l_nvl_old_numerator VARCHAR2 (1000);
1533
1534 BEGIN
1535
1536 l_nvl_denominator := 'nvl('||p_denominator||',0)';
1537 l_nvl_new_numerator := 'nvl('||p_new_numerator||',0)';
1538 l_nvl_old_numerator := 'nvl('||p_old_numerator||',0)';
1539
1540 l_change_calc :=
1541 'CASE WHEN ' || l_nvl_denominator || ' = 0 THEN to_number (NULL)
1542 ' || ' ELSE ((' || l_nvl_new_numerator || ' - ' || l_nvl_old_numerator
1543 || ')/ abs (' || p_denominator || ')) * 100
1544 ' || ' END ' || p_measure_name || ' ';
1545
1546 RETURN l_change_calc;
1547 END change_str;
1548
1549
1550 /* change_rate_str
1551 Get the change in percentage/ratio string. Better than writing out all the case
1552 statements
1553 */
1554 FUNCTION change_rate_str (p_new_numerator IN VARCHAR2,
1555 p_new_denominator IN VARCHAR2,
1556 p_old_numerator IN VARCHAR2,
1557 p_old_denominator IN VARCHAR2,
1558 p_rate_type IN VARCHAR2,
1559 p_measure_name IN VARCHAR2)
1560 RETURN VARCHAR2
1561 IS
1562 l_nvl_new_denominator VARCHAR2(1000);
1563 l_nvl_old_denominator VARCHAR2(1000);
1564 l_nvl_new_numerator VARCHAR2(1000);
1565 l_nvl_old_numerator VARCHAR2(1000);
1566
1567 BEGIN
1568
1569 l_nvl_new_denominator := 'nvl('||p_new_denominator||',0)';
1570 l_nvl_old_denominator := 'nvl('||p_old_denominator||',0)';
1571 l_nvl_new_numerator := 'nvl('||p_new_numerator||',0)';
1572 l_nvl_old_numerator := 'nvl('||p_old_numerator||',0)';
1573
1574 -- if rate is a ratio
1575 if(p_rate_type = 'RATIO') then
1576 return 'CASE WHEN ' || l_nvl_old_denominator || ' = 0 THEN to_number (NULL)
1577 ' || 'WHEN ' || l_nvl_new_denominator || ' = 0 THEN to_number (NULL)
1578 ' || ' ELSE ((' || l_nvl_new_numerator || '/'
1579 || l_nvl_new_denominator ||
1580 ') -
1581 ' || '(' || l_nvl_old_numerator || '/'
1582 || l_nvl_old_denominator || '))
1583 ' || 'END
1584 ' || p_measure_name || ' ';
1585 end if;
1586
1587 -- if rate is a percent
1588 return 'CASE WHEN ' || l_nvl_old_denominator || ' = 0 THEN to_number (NULL)
1589 ' || 'WHEN ' || l_nvl_new_denominator || ' = 0 THEN to_number (NULL)
1590 ' || ' ELSE ((' || l_nvl_new_numerator || '/'
1591 || l_nvl_new_denominator ||
1592 ') -
1593 ' || '(' || l_nvl_old_numerator || '/'
1594 || l_nvl_old_denominator || '))*100
1595 ' || 'END
1596 ' || p_measure_name || ' ';
1597
1598 END change_rate_str;
1599
1600
1601 /* Build the fact view by columns string using the join table
1602 for queries using windowing.
1603 */
1604 FUNCTION get_fact_select_columns (p_join_tbl IN
1605 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
1606 RETURN VARCHAR2
1607 IS
1608 l_fact_select_cols VARCHAR2(400);
1609 BEGIN
1610
1611 l_fact_select_cols := '';
1612
1613 FOR l_num IN p_join_tbl.first .. p_join_tbl.last
1614 LOOP
1615 IF (p_join_tbl.exists(l_num)) THEN
1616 l_fact_select_cols := l_fact_select_cols ||
1617 p_join_tbl(l_num).fact_column || ',
1618 ';
1619 END IF;
1620 END LOOP;
1621 -- trim trailing comma and carriage returns, and add a space
1622 l_fact_select_cols := rtrim (l_fact_select_cols, ',
1623 ') || ' ';
1624
1625 return l_fact_select_cols;
1626
1627 END get_fact_select_columns;
1628
1629
1630 /* get_global_weight_uom
1631 Gets the global weight unit of measure
1632 */
1633 FUNCTION get_global_weight_uom RETURN VARCHAR2
1634 IS
1635 l_weight_uom VARCHAR2(400);
1636 BEGIN
1637
1638 select gu_weight_uom into l_weight_uom from wsh_global_parameters;
1639
1640 RETURN l_weight_uom;
1641
1642 END get_global_weight_uom;
1643
1644
1645 /* get_global_volume_uom
1646 Gets the global volume unit of measure
1647 */
1648 FUNCTION get_global_volume_uom RETURN VARCHAR2
1649 IS
1650 l_volume_uom VARCHAR2(400);
1651 BEGIN
1652
1653 select gu_volume_uom into l_volume_uom from wsh_global_parameters;
1654
1655 RETURN l_volume_uom;
1656
1657 END get_global_volume_uom;
1658
1659
1660 /* get_global_distance_uom
1661 Gets the global distance unit of measure
1662 */
1663 FUNCTION get_global_distance_uom RETURN VARCHAR2
1664 IS
1665 l_distance_uom VARCHAR2(400);
1666 BEGIN
1667
1668 select gu_distance_uom into l_distance_uom from wsh_global_parameters;
1669
1670 RETURN l_distance_uom;
1671
1672 END get_global_distance_uom;
1673
1674 END ISC_DBI_SUTIL_PKG;
[Home] [Help]