[Home] [Help]
PACKAGE BODY: APPS.MTH_ITEM_DIMENSION_PKG
Source
1 Package Body mth_item_dimension_pkg AS
2 /*$Header: mthitemb.pls 120.6.12020000.3 2012/10/17 22:47:57 gtippire ship $ */
3
4 /* ****************************************************************************
5 * Procedure :UPDATE_ITEM_HIER_LVL_IN_DIM_HR *
6 * Description :This procedure updates the number of levels in dim hierarchy *
7 * for item hierarchies existed in item denorm table *
8 * File Name :MTHITEMDB.PLS *
9 * Visibility :Private *
10 * Parameters : *
11 * Modification log : *
12 * Author Date Change *
13 * Yong Feng 03-July--2012 Initial Creation *
14 **************************************************************************** */
15
16 PROCEDURE UPDATE_ITEM_HIER_LVL_IN_DIM_HR
17 IS
18 BEGIN
19 UPDATE MTH_DIM_HIERARCHY DH
20 SET dh.last_update_date = SYSDATE,
21 DH.NUMBER_OF_LEVEL =
22 (SELECT
23 Max(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
24 decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
25 decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
26 decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
27 decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
28 decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
29 decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
30 decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
31 decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1))
32 AS num_levels
33 FROM MTH_ITEM_DENORM_D ID
34 WHERE id.LEVEL_num = 10 and
35 ID.hierarchy_id = DH.hierarchy_id )
36 WHERE DH.NUMBER_OF_LEVEL IS NULL AND
37 DH.dimension_name= 'ITEM' AND
38 EXISTS (SELECT 1 FROM MTH_ITEM_DENORM_D ID
39 WHERE DH.dimension_name= 'ITEM' AND
40 ID.level_num = 10 AND
41 ID.item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
42 ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 );
43 END UPDATE_ITEM_HIER_LVL_IN_DIM_HR;
44
45 /* ****************************************************************************
46 * Procedure :ITEM_DIM_LOAD_DENORM *
47 * Description :This procedure is used to populate the denorm table *
48 * for the item dimension hierarchy in INIT mode *
49 * File Name :MTHITEMDB.PLS *
50 * Visibility :Public *
51 * Parameters : *
52 * Modification log : *
53 * Author Date Change *
54 * Ankit Goyal 29-May--2007 Initial Creation *
55 **************************************************************************** */
56 PROCEDURE ITEM_DIM_LOAD_DENORM
57 IS
58 v_unassigned_key number;
59 v_log_to_date DATE;
60 v_log_from_date DATE;
61 v_unassigned_item_name varchar2(240);
62 v_unassigned_category_name varchar2(240);
63 BEGIN
64 v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
65 v_log_to_date := SYSDATE;
66 /*New additions */
67 -- Call mth_run_log_pre_load
68 mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key,'INITIAL',NULL,0,v_log_to_date);
69 mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date) ;
70
71 --insert the values in the denormalised item denorm table.
72 --use of max function in the select is just to by pass the usage in the
73 --group by clause.
74 --Maximun nuumber of leves supported is 10.
75 --Item will be at lowest level i.e. 10.
76
77 v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
78
79 BEGIN
80 SELECT ITEM_NAME into v_unassigned_item_name
81 from MTH_ITEMS_D
82 where item_PK_KEY = v_unassigned_key;
83 exception
84 when TOO_MANY_ROWS then
85 v_unassigned_item_name := null;
86 when NO_DATA_FOUND then
87 v_unassigned_item_name := null;
88 when others then
89 v_unassigned_item_name := null;
90 end;
91
92 BEGIN
93 select CATEGORY_NAME into v_unassigned_category_name
94 from MTH_ITEM_CATEGORIES_D
95 where CATEGORY_PK_KEY = v_unassigned_key;
96 exception
97 when TOO_MANY_ROWS then
98 v_unassigned_item_name := null;
99 when NO_DATA_FOUND then
100 v_unassigned_item_name := null;
101 when others then
102 v_unassigned_item_name := null;
103 end;
104
105 INSERT
106 INTO MTH_ITEM_DENORM_D
107 (
108 HIERARCHY_ID,
109 ITEM_FK_KEY,
110 LEVEL1_FK_KEY,
111 LEVEL2_FK_KEY,
112 LEVEL3_FK_KEY,
113 LEVEL4_FK_KEY,
114 LEVEL5_FK_KEY,
115 LEVEL6_FK_KEY,
116 LEVEL7_FK_KEY,
117 LEVEL8_FK_KEY,
118 LEVEL9_FK_KEY,
119 LEVEL_NUM,
120 ITEM_NAME,
121 LEVEL1_NAME,
122 LEVEL2_NAME,
123 LEVEL3_NAME,
124 LEVEL4_NAME,
125 LEVEL5_NAME,
126 LEVEL6_NAME,
127 LEVEL7_NAME,
128 LEVEL8_NAME,
129 LEVEL9_NAME
130 )
131 SELECT HIERARCHY_ID,
132 ITEM_FK_KEY,
133 LEVEL1_FK_KEY,
134 LEVEL2_FK_KEY,
135 LEVEL3_FK_KEY,
136 LEVEL4_FK_KEY,
137 LEVEL5_FK_KEY,
138 LEVEL6_FK_KEY,
139 LEVEL7_FK_KEY,
140 LEVEL8_FK_KEY,
141 LEVEL9_FK_KEY,
142 LEVEL_NUM,
143 ITEM_NAME,
144 LEVEL1_NAME,
145 LEVEL2_NAME,
146 LEVEL3_NAME,
147 LEVEL4_NAME,
148 LEVEL5_NAME,
149 LEVEL6_NAME,
150 LEVEL7_NAME,
151 LEVEL8_NAME,
152 LEVEL9_NAME
153 FROM (SELECT HIERARCHY_ID,
154 ITEM_PK_KEY ITEM_FK_KEY,
155 MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
156 MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
157 MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
158 MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
159 MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
160 MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
161 MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
162 MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
163 MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
164 MAX(LEVEL_NUM) LEVEL_NUM,
165 MAX(ITEM_NAME) ITEM_NAME,
166 MAX(LEVEL1_NAME) LEVEL1_NAME,
167 MAX(LEVEL2_NAME) LEVEL2_NAME,
168 MAX(LEVEL3_NAME) LEVEL3_NAME,
169 MAX(LEVEL4_NAME) LEVEL4_NAME,
170 MAX(LEVEL5_NAME) LEVEL5_NAME,
171 MAX(LEVEL6_NAME) LEVEL6_NAME,
172 MAX(LEVEL7_NAME) LEVEL7_NAME,
173 MAX(LEVEL8_NAME) LEVEL8_NAME,
174 MAX(LEVEL9_NAME) LEVEL9_NAME
175 FROM (SELECT HIERARCHY_ID,
176 B.ITEM_PK_KEY,
177 (CASE
178 WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
179 ELSE NULL
180 END) LEVEL9_FK_KEY,
181 (CASE
182 WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
183 ELSE NULL
184 END) LEVEL8_FK_KEY,
185 (CASE
186 WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
187 ELSE NULL
188 END) LEVEL7_FK_KEY,
189 (CASE
190 WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
191 ELSE NULL
192 END) LEVEL6_FK_KEY,
193 (CASE
194 WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
195 ELSE NULL
196 END) LEVEL5_FK_KEY,
197 (CASE
198 WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
199 ELSE NULL
200 END) LEVEL4_FK_KEY,
201 (CASE
202 WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
203 ELSE NULL
204 END) LEVEL3_FK_KEY,
205 (CASE
206 WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
207 ELSE NULL
208 END) LEVEL2_FK_KEY,
209 (CASE
210 WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
211 ELSE NULL
212 END) LEVEL1_FK_KEY,
213 10 LEVEL_NUM,
214 B.ITEM_NAME,
215 (CASE
216 WHEN LEVEL_NUM = 1 THEN PARENT_NAME
217 ELSE NULL
218 END) LEVEL9_NAME,
219 (CASE
220 WHEN LEVEL_NUM = 2 THEN PARENT_NAME
221 ELSE NULL
222 END) LEVEL8_NAME,
223 (CASE
224 WHEN LEVEL_NUM = 3 THEN PARENT_NAME
225 ELSE NULL
226 END) LEVEL7_NAME,
227 (CASE
228 WHEN LEVEL_NUM = 4 THEN PARENT_NAME
229 ELSE NULL
230 END) LEVEL6_NAME,
231 (CASE
232 WHEN LEVEL_NUM = 5 THEN PARENT_NAME
233 ELSE NULL
234 END) LEVEL5_NAME,
235 (CASE
236 WHEN LEVEL_NUM = 6 THEN PARENT_NAME
237 ELSE NULL
238 END) LEVEL4_NAME,
239 (CASE
240 WHEN LEVEL_NUM = 7 THEN PARENT_NAME
241 ELSE NULL
242 END) LEVEL3_NAME,
243 (CASE
244 WHEN LEVEL_NUM = 8 THEN PARENT_NAME
245 ELSE NULL
246 END) LEVEL2_NAME,
247 (CASE
248 WHEN LEVEL_NUM = 9 THEN PARENT_NAME
249 ELSE NULL
250 END) LEVEL1_NAME
251 FROM (SELECT LEVEL LEVEL_NUM,
252 ITEM_PK_KEY,
253 LEVEL_FK_KEY,
254 LEVEL_NAME,
255 PARENT_FK_KEY,
256 PARENT_NAME,
257 SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
258 HIERARCHY_ID
259 FROM MTH_ITEM_HIERARCHY,
260 MTH_ITEMS_D B
261 WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
262 START WITH B.ITEM_PK_KEY IS NOT NULL
263 and MTH_ITEM_HIERARCHY.level_fk_key<> v_unassigned_key
264 AND MTH_ITEM_HIERARCHY.LAST_UPDATE_DATE > v_log_from_date
265 AND MTH_ITEM_HIERARCHY.LAST_UPDATE_DATE <= v_log_to_date
266 CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
267 AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
268 (SELECT ITEM_PK_KEY, ITEM_NAME
269 FROM MTH_ITEMS_D) B
270 WHERE A.PATH = ('/' || B.ITEM_PK_KEY ) OR
271 A.PATH LIKE '/' || B.ITEM_PK_KEY || '/%' )
272
273 --The granuality level is item and this would be done
274 -- for all the hierarchies
275 GROUP BY HIERARCHY_ID,ITEM_PK_KEY
276 UNION
277 select mdh.hierarchy_id,
278 MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key
279 ,v_unassigned_key level1_level_key
283 ,v_unassigned_key level5_level_key
280 ,v_unassigned_key level2_level_key
281 ,v_unassigned_key level3_level_key
282 ,v_unassigned_key level4_level_key
284 ,v_unassigned_key level6_level_key
285 ,v_unassigned_key level7_level_key
286 ,v_unassigned_key level8_level_key
287 ,v_unassigned_key level9_level_key
288 ,10 level_num
289 ,v_unassigned_item_name item_name
290 ,v_unassigned_category_name level1_name
291 ,v_unassigned_category_name level2_name
292 ,v_unassigned_category_name level3_name
293 ,v_unassigned_category_name level4_name
294 ,v_unassigned_category_name level5_name
295 ,v_unassigned_category_name level6_name
296 ,v_unassigned_category_name level7_name
297 ,v_unassigned_category_name level8_name
298 ,v_unassigned_category_name level9_name
299 from dual,
300 mth_dim_hierarchy mdh,
301 (select distinct hierarchy_id
302 from mth_dim_level_lookup) mdll
303 where mdh.dimension_name= 'ITEM' and
304 mdll.hierarchy_id (+) = mdh.hierarchy_id);
305
306 -- Balance the item denorm table
307 mth_util_pkg.mth_hrchy_balance_load('MTH_ITEM_DENORM_D');
308
309 -- Push up and fill the level key and name for the ones with NULL
310
311 UPDATE MTH_ITEM_DENORM_D
312 SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
313 level1_name = decode(level1_fk_key, NULL, v_unassigned_category_name, level1_name),
314 level2_fk_key = nvl(level2_fk_key, v_unassigned_key),
315 level2_name = Decode(level2_fk_key, NULL, v_unassigned_category_name, level2_name),
316 level3_fk_key = nvl(level3_fk_key, v_unassigned_key),
317 level3_name = decode(level3_fk_key, NULL, v_unassigned_category_name, level3_name),
318 level4_fk_key = nvl(level4_fk_key, v_unassigned_key),
319 level4_name = decode(level4_fk_key, NULL, v_unassigned_category_name, level4_name),
320 level5_fk_key = nvl(level5_fk_key, v_unassigned_key),
321 level5_name = decode(level5_fk_key, NULL, v_unassigned_category_name, level5_name),
322 level6_fk_key = nvl(level6_fk_key, v_unassigned_key),
323 level6_name = decode(level6_fk_key, NULL, v_unassigned_category_name, level6_name),
324 level7_fk_key = nvl(level7_fk_key, v_unassigned_key),
325 level7_name = decode(level7_fk_key, NULL, v_unassigned_category_name, level7_name),
326 level8_fk_key = nvl(level8_fk_key, v_unassigned_key),
327 level8_name = decode(level8_fk_key, NULL, v_unassigned_category_name, level8_name),
328 level9_fk_key = nvl(level9_fk_key, v_unassigned_key),
329 level9_name = decode(level9_fk_key, NULL, v_unassigned_category_name, level9_name);
330
331
332 -- Add entries for all the rest of levels
333
334
335 Insert into mth_item_denorm_d
336 (
337 HIERARCHY_ID,
338 ITEM_FK_KEY,
339 LEVEL1_FK_KEY,
340 LEVEL2_FK_KEY,
341 LEVEL3_FK_KEY,
342 LEVEL4_FK_KEY,
343 LEVEL5_FK_KEY,
344 LEVEL6_FK_KEY,
345 LEVEL7_FK_KEY,
346 LEVEL8_FK_KEY,
347 LEVEL9_FK_KEY,
348 LEVEL_NUM,
349 ITEM_NAME,
350 LEVEL1_NAME,
351 LEVEL2_NAME,
352 LEVEL3_NAME,
353 LEVEL4_NAME,
354 LEVEL5_NAME,
355 LEVEL6_NAME,
356 LEVEL7_NAME,
357 LEVEL8_NAME,
358 LEVEL9_NAME
359 )
360 select distinct
361 HIERARCHY_ID,
362 NULL,
363 LEVEL1_FK_KEY,
364 LEVEL2_FK_KEY,
365 LEVEL3_FK_KEY,
366 LEVEL4_FK_KEY,
367 LEVEL5_FK_KEY,
368 LEVEL6_FK_KEY,
369 LEVEL7_FK_KEY,
370 LEVEL8_FK_KEY,
371 LEVEL9_FK_KEY,
372 9,
373 NULL,
374 LEVEL1_NAME,
375 LEVEL2_NAME,
376 LEVEL3_NAME,
377 LEVEL4_NAME,
378 LEVEL5_NAME,
379 LEVEL6_NAME,
380 LEVEL7_NAME,
381 LEVEL8_NAME,
382 LEVEL9_NAME
383 From mth_item_denorm_d
384 Where LEVEL9_FK_KEY is not null and level_NUM = 10;
385
386 -- insert level 8 entries
387 Insert into mth_item_denorm_d
388 (
389 HIERARCHY_ID,
390 ITEM_FK_KEY,
391 LEVEL1_FK_KEY,
392 LEVEL2_FK_KEY,
393 LEVEL3_FK_KEY,
394 LEVEL4_FK_KEY,
395 LEVEL5_FK_KEY,
396 LEVEL6_FK_KEY,
397 LEVEL7_FK_KEY,
398 LEVEL8_FK_KEY,
399 LEVEL9_FK_KEY,
400 LEVEL_NUM,
401 ITEM_NAME,
402 LEVEL1_NAME,
403 LEVEL2_NAME,
404 LEVEL3_NAME,
405 LEVEL4_NAME,
406 LEVEL5_NAME,
407 LEVEL6_NAME,
408 LEVEL7_NAME,
409 LEVEL8_NAME,
410 LEVEL9_NAME
411 )
412 select distinct
413 HIERARCHY_ID,
414 NULL,
415 LEVEL1_FK_KEY,
416 LEVEL2_FK_KEY,
417 LEVEL3_FK_KEY,
418 LEVEL4_FK_KEY,
419 LEVEL5_FK_KEY,
420 LEVEL6_FK_KEY,
421 LEVEL7_FK_KEY,
422 LEVEL8_FK_KEY,
423 NULL,
424 8,
425 NULL,
426 LEVEL1_NAME,
427 LEVEL2_NAME,
428 LEVEL3_NAME,
429 LEVEL4_NAME,
430 LEVEL5_NAME,
431 LEVEL6_NAME,
432 LEVEL7_NAME,
433 LEVEL8_NAME,
434 NULL
435 From mth_item_denorm_d
439 Insert into mth_item_denorm_d
436 Where LEVEL8_FK_KEY is not null and level_NUM = 9;
437
438 -- insert level 7 entries
440 (
441 HIERARCHY_ID,
442 ITEM_FK_KEY,
443 LEVEL1_FK_KEY,
444 LEVEL2_FK_KEY,
445 LEVEL3_FK_KEY,
446 LEVEL4_FK_KEY,
447 LEVEL5_FK_KEY,
448 LEVEL6_FK_KEY,
449 LEVEL7_FK_KEY,
450 LEVEL8_FK_KEY,
451 LEVEL9_FK_KEY,
452 LEVEL_NUM,
453 ITEM_NAME,
454 LEVEL1_NAME,
455 LEVEL2_NAME,
456 LEVEL3_NAME,
457 LEVEL4_NAME,
458 LEVEL5_NAME,
459 LEVEL6_NAME,
460 LEVEL7_NAME,
461 LEVEL8_NAME,
462 LEVEL9_NAME
463 )
464 select distinct
465 HIERARCHY_ID,
466 NULL,
467 LEVEL1_FK_KEY,
468 LEVEL2_FK_KEY,
469 LEVEL3_FK_KEY,
470 LEVEL4_FK_KEY,
471 LEVEL5_FK_KEY,
472 LEVEL6_FK_KEY,
473 LEVEL7_FK_KEY,
474 NULL,
475 NULL,
476 7,
477 NULL,
478 LEVEL1_NAME,
479 LEVEL2_NAME,
480 LEVEL3_NAME,
481 LEVEL4_NAME,
482 LEVEL5_NAME,
483 LEVEL6_NAME,
484 LEVEL7_NAME,
485 NULL,
486 NULL
487 From mth_item_denorm_d
488 Where LEVEL7_FK_KEY is not null and level_NUM = 8;
489
490 -- insert level 6 entries
491 Insert into mth_item_denorm_d
492 (
493 HIERARCHY_ID,
494 ITEM_FK_KEY,
495 LEVEL1_FK_KEY,
496 LEVEL2_FK_KEY,
497 LEVEL3_FK_KEY,
498 LEVEL4_FK_KEY,
499 LEVEL5_FK_KEY,
500 LEVEL6_FK_KEY,
501 LEVEL7_FK_KEY,
502 LEVEL8_FK_KEY,
503 LEVEL9_FK_KEY,
504 LEVEL_NUM,
505 ITEM_NAME,
506 LEVEL1_NAME,
507 LEVEL2_NAME,
508 LEVEL3_NAME,
509 LEVEL4_NAME,
510 LEVEL5_NAME,
511 LEVEL6_NAME,
512 LEVEL7_NAME,
513 LEVEL8_NAME,
514 LEVEL9_NAME
515 )
516 select distinct
517 HIERARCHY_ID,
518 NULL,
519 LEVEL1_FK_KEY,
520 LEVEL2_FK_KEY,
521 LEVEL3_FK_KEY,
522 LEVEL4_FK_KEY,
523 LEVEL5_FK_KEY,
524 LEVEL6_FK_KEY,
525 NULL,
526 NULL,
527 NULL,
528 6,
529 NULL,
530 LEVEL1_NAME,
531 LEVEL2_NAME,
532 LEVEL3_NAME,
533 LEVEL4_NAME,
534 LEVEL5_NAME,
535 LEVEL6_NAME,
536 NULL,
537 NULL,
538 NULL
539 From mth_item_denorm_d
540 Where LEVEL6_FK_KEY is not null and level_NUM = 7;
541
542 -- insert level 5 entries
543 Insert into mth_item_denorm_d
544 (
545 HIERARCHY_ID,
546 ITEM_FK_KEY,
547 LEVEL1_FK_KEY,
548 LEVEL2_FK_KEY,
549 LEVEL3_FK_KEY,
550 LEVEL4_FK_KEY,
551 LEVEL5_FK_KEY,
552 LEVEL6_FK_KEY,
553 LEVEL7_FK_KEY,
554 LEVEL8_FK_KEY,
555 LEVEL9_FK_KEY,
556 LEVEL_NUM,
557 ITEM_NAME,
558 LEVEL1_NAME,
559 LEVEL2_NAME,
560 LEVEL3_NAME,
561 LEVEL4_NAME,
562 LEVEL5_NAME,
563 LEVEL6_NAME,
564 LEVEL7_NAME,
565 LEVEL8_NAME,
566 LEVEL9_NAME
567 )
568 select distinct
569 HIERARCHY_ID,
570 NULL,
571 LEVEL1_FK_KEY,
572 LEVEL2_FK_KEY,
573 LEVEL3_FK_KEY,
574 LEVEL4_FK_KEY,
575 LEVEL5_FK_KEY,
576 NULL,
577 NULL,
578 NULL,
579 NULL,
580 5,
581 NULL,
582 LEVEL1_NAME,
583 LEVEL2_NAME,
584 LEVEL3_NAME,
585 LEVEL4_NAME,
586 LEVEL5_NAME,
587 NULL,
588 NULL,
589 NULL,
590 NULL
591 From mth_item_denorm_d
592 Where LEVEL5_FK_KEY is not null and level_NUM = 6;
593
594 -- insert level 4 entries
595 Insert into mth_item_denorm_d
596 (
597 HIERARCHY_ID,
598 ITEM_FK_KEY,
599 LEVEL1_FK_KEY,
600 LEVEL2_FK_KEY,
601 LEVEL3_FK_KEY,
602 LEVEL4_FK_KEY,
603 LEVEL5_FK_KEY,
604 LEVEL6_FK_KEY,
605 LEVEL7_FK_KEY,
606 LEVEL8_FK_KEY,
607 LEVEL9_FK_KEY,
608 LEVEL_NUM,
609 ITEM_NAME,
610 LEVEL1_NAME,
611 LEVEL2_NAME,
612 LEVEL3_NAME,
613 LEVEL4_NAME,
614 LEVEL5_NAME,
615 LEVEL6_NAME,
616 LEVEL7_NAME,
617 LEVEL8_NAME,
618 LEVEL9_NAME
619 )
620 select distinct
621 HIERARCHY_ID,
622 NULL,
623 LEVEL1_FK_KEY,
624 LEVEL2_FK_KEY,
625 LEVEL3_FK_KEY,
626 LEVEL4_FK_KEY,
627 NULL,
628 NULL,
629 NULL,
630 NULL,
631 NULL,
632 4,
636 LEVEL3_NAME,
633 NULL,
634 LEVEL1_NAME,
635 LEVEL2_NAME,
637 LEVEL4_NAME,
638 NULL,
639 NULL,
640 NULL,
641 NULL,
642 NULL
643 From mth_item_denorm_d
644 Where LEVEL4_FK_KEY is not null and level_NUM = 5;
645
646
647 -- insert level 3 entries
648 Insert into mth_item_denorm_d
649 (
650 HIERARCHY_ID,
651 ITEM_FK_KEY,
652 LEVEL1_FK_KEY,
653 LEVEL2_FK_KEY,
654 LEVEL3_FK_KEY,
655 LEVEL4_FK_KEY,
656 LEVEL5_FK_KEY,
657 LEVEL6_FK_KEY,
658 LEVEL7_FK_KEY,
659 LEVEL8_FK_KEY,
660 LEVEL9_FK_KEY,
661 LEVEL_NUM,
662 ITEM_NAME,
663 LEVEL1_NAME,
664 LEVEL2_NAME,
665 LEVEL3_NAME,
666 LEVEL4_NAME,
667 LEVEL5_NAME,
668 LEVEL6_NAME,
669 LEVEL7_NAME,
670 LEVEL8_NAME,
671 LEVEL9_NAME
672 )
673 select distinct
674 HIERARCHY_ID,
675 NULL,
676 LEVEL1_FK_KEY,
677 LEVEL2_FK_KEY,
678 LEVEL3_FK_KEY,
679 NULL,
680 NULL,
681 NULL,
682 NULL,
683 NULL,
684 NULL,
685 3,
686 NULL,
687 LEVEL1_NAME,
688 LEVEL2_NAME,
689 LEVEL3_NAME,
690 NULL,
691 NULL,
692 NULL,
693 NULL,
694 NULL,
695 NULL
696 From mth_item_denorm_d
697 Where LEVEL3_FK_KEY is not null and level_NUM = 4;
698
699
700 -- insert level 2 entries
701 Insert into mth_item_denorm_d
702 (
703 HIERARCHY_ID,
704 ITEM_FK_KEY,
705 LEVEL1_FK_KEY,
706 LEVEL2_FK_KEY,
707 LEVEL3_FK_KEY,
708 LEVEL4_FK_KEY,
709 LEVEL5_FK_KEY,
710 LEVEL6_FK_KEY,
711 LEVEL7_FK_KEY,
712 LEVEL8_FK_KEY,
713 LEVEL9_FK_KEY,
714 LEVEL_NUM,
715 ITEM_NAME,
716 LEVEL1_NAME,
717 LEVEL2_NAME,
718 LEVEL3_NAME,
719 LEVEL4_NAME,
720 LEVEL5_NAME,
721 LEVEL6_NAME,
722 LEVEL7_NAME,
723 LEVEL8_NAME,
724 LEVEL9_NAME
725 )
726 select distinct
727 HIERARCHY_ID,
728 NULL,
729 LEVEL1_FK_KEY,
730 LEVEL2_FK_KEY,
731 NULL,
732 NULL,
733 NULL,
734 NULL,
735 NULL,
736 NULL,
737 NULL,
738 2,
739 NULL,
740 LEVEL1_NAME,
741 LEVEL2_NAME,
742 NULL,
743 NULL,
744 NULL,
745 NULL,
746 NULL,
747 NULL,
748 NULL
749 From mth_item_denorm_d
750 Where LEVEL2_FK_KEY is not null and level_NUM = 3;
751
752
753
754 -- insert level 1 entries
755 Insert into mth_item_denorm_d
756 (
757 HIERARCHY_ID,
758 ITEM_FK_KEY,
759 LEVEL1_FK_KEY,
760 LEVEL2_FK_KEY,
761 LEVEL3_FK_KEY,
762 LEVEL4_FK_KEY,
763 LEVEL5_FK_KEY,
764 LEVEL6_FK_KEY,
765 LEVEL7_FK_KEY,
766 LEVEL8_FK_KEY,
767 LEVEL9_FK_KEY,
768 LEVEL_NUM,
769 ITEM_NAME,
770 LEVEL1_NAME,
771 LEVEL2_NAME,
772 LEVEL3_NAME,
773 LEVEL4_NAME,
774 LEVEL5_NAME,
775 LEVEL6_NAME,
776 LEVEL7_NAME,
777 LEVEL8_NAME,
778 LEVEL9_NAME
779 )
780 select distinct
781 HIERARCHY_ID,
782 null,
783 LEVEL1_FK_KEY,
784 NULL,
785 NULL,
786 NULL,
787 NULL,
788 NULL,
789 NULL,
790 NULL,
791 NULL,
792 1,
793 null,
794 LEVEL1_NAME,
795 NULL,
796 NULL,
797 NULL,
798 NULL,
799 NULL,
800 NULL,
801 NULL,
802 NULL
803 From mth_item_denorm_d
804 Where LEVEL1_FK_KEY is not null and level_NUM = 2;
805
806
807 -- UPDATE the number of levels in mth_dim_hierarchy
808 -- First reset number of levels in mth_dim_hierarchy for all item hierarchies
809 UPDATE MTH_DIM_HIERARCHY
810 SET NUMBER_OF_LEVEL = NULL
811 WHERE dimension_name = 'ITEM';
812
813 UPDATE_ITEM_HIER_LVL_IN_DIM_HR();
814
815
816 /*New additions */
817 ----Call mth_run_log_post_load
818 mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',v_unassigned_key);
819
820 EXCEPTION
821 WHEN OTHERS THEN
822 ROLLBACK;
823 RAISE;
824 --End of the procedure ITEM_DIM_LOAD_DENORM
825 END ITEM_DIM_LOAD_DENORM;
826
827 /* *****************************************************************************
828 * Procedure :MTH_HRCHY_BALANCE_LOAD *
829 * Description :This procedure is used for the balancing of the *
833 * Visibility :Public *
830 * hierarchy. The algorithm used for the balancing is down balancing *
831 * Please refer to the Item fdd for more details on this. *
832 * File Name :MTHUTILS.PLS *
834 * Parameters :fact table name *
835 * Modification log : *
836 * Author Date Change *
837 * Ankit Goyal 17-Aug-2007 Initial Creation *
838 ****************************************************************************** */
839 PROCEDURE mth_hrchy_balance_load(p_fact_table IN VARCHAR2) is
840
841 v_fact_table VARCHAR2(120);
842
843 --user defined type for array of records
844 TYPE denorm_rec_tab_type IS TABLE OF NUMBER;
845 TYPE denorm_rec_name_tab_type IS TABLE OF VARCHAR2(240);
846
847 --user defined type of record of arrays
848 TYPE denorm_rec_type IS RECORD (level9_fk_key denorm_rec_tab_type,
849 hierarchy_id denorm_rec_tab_type,
850 baselevel_fk_key denorm_rec_tab_type,
851 level7_fk_key denorm_rec_tab_type,
852 level6_fk_key denorm_rec_tab_type,
853 level5_fk_key denorm_rec_tab_type,
854 level4_fk_key denorm_rec_tab_type,
855 level3_fk_key denorm_rec_tab_type,
856 level2_fk_key denorm_rec_tab_type,
857 level1_fk_key denorm_rec_tab_type,
858 level9_name denorm_rec_name_tab_type,
859 level7_name denorm_rec_name_tab_type,
860 level6_name denorm_rec_name_tab_type,
861 level5_name denorm_rec_name_tab_type,
862 level4_name denorm_rec_name_tab_type,
863 level3_name denorm_rec_name_tab_type,
864 level2_name denorm_rec_name_tab_type,
865 level1_name denorm_rec_name_tab_type
866 );
867
868 --instantiation of the user defined type
869 --this will be the placeholder for the records fetched from the denorm table
870 denorm_rec denorm_rec_type;
871
872 --user defined cursor to hold the bulk collection of records
873 item_cur SYS_REFCURSOR;
874
875 --variable for the limit of the bulk collection
876 v_limit NUMBER :=5000;
877
878
879 BEGIN
880
881 --initialize the collection
882 denorm_rec := NULL;
883
884 --initialize the fact table name
885 v_fact_table :=p_fact_table;
886
887 --open the cursor
888 OPEN item_cur FOR 'SELECT --select for the newe levels
889 level9_fk_key,hierarchy_id,item_fk_key,
890 Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
891 Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
892 level6_fk_key_new,
893 Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
894 level9_fk_key) level5_fk_key_new,
895 Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
896 level8_fk_key,level9_fk_key) level4_fk_key_new,
897 Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
898 level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
899 Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
900 level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
901 level2_fk_key_new,
902 Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
903 level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
904 level9_fk_key) level1_fk_key_new,
905 level9_name,
906 Decode(diff_level,1,level8_name,level9_name) level7_name_new,
907 Decode(diff_level,1,level7_name,2,level8_name,level9_name)
908 level6_name_new,
909 Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
910 level9_name) level5_name_new,
911 Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
912 level8_name,level9_name) level4_name_new,
913 Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
914 level7_name,5,level8_name,level9_name) level3_name_new,
915 Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
916 level6_name,5,level7_name,6,level8_name,level9_name)
917 level2_name_new,
918 Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
919 level5_name,5,level6_name,6,level7_name,7,level8_name,
920 level9_name) level1_name_new
921 from
922 (--select the levels to be balanced
923 SELECT hierarchy_id ,item_fk_key,
924 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
925 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
926 level1_fk_key,
927 level9_name,level8_name,level7_name,level6_name,
928 level5_name,level4_name,level3_name,level2_name,
929 level1_name,
930 max_level-c_level diff_level
931 FROM
932 (
933 SELECT hierarchy_id ,item_fk_key,
934 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
935 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
936 level1_fk_key,
937 level9_name,level8_name,level7_name,level6_name,
938 level5_name,level4_name,level3_name,level2_name,
939 level1_name,
940 decode(level9_fk_key,NULL,0,1) +
941 decode(level8_fk_key,NULL,0,1) +
942 decode(level7_fk_key,NULL,0,1) +
943 decode(level6_fk_key,NULL,0,1) +
944 decode(level5_fk_key,NULL,0,1) +
945 decode(level4_fk_key,NULL,0,1) +
946 decode(level3_fk_key,NULL,0,1) +
947 decode(level2_fk_key,NULL,0,1) +
948 decode(level1_fk_key,NULL,0,1) c_level,--current level
949 Max(decode(level9_fk_key,NULL,0,1) +
950 decode(level8_fk_key,NULL,0,1) +
951 decode(level7_fk_key,NULL,0,1) +
952 decode(level6_fk_key,NULL,0,1) +
953 decode(level5_fk_key,NULL,0,1) +
957 decode(level1_fk_key,NULL,0,1)) over(PARTITION BY hierarchy_id)
954 decode(level4_fk_key,NULL,0,1) +
955 decode(level3_fk_key,NULL,0,1) +
956 decode(level2_fk_key,NULL,0,1) +
958 max_level--maximum level in the hierarchy
959 FROM MTH_ITEM_DENORM_D_TMP
960 WHERE item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL
961 )
962 WHERE c_level<max_level
963 AND level9_fk_key IS NOT NULL
964 )';
965 LOOP
966 --fetch the rows in in cursor. Bulk collect
967 FETCH item_cur BULK COLLECT INTO denorm_rec.level9_fk_key,
968 denorm_rec.hierarchy_id,
969 denorm_rec.baselevel_fk_key,denorm_rec.level7_fk_key,
970 denorm_rec.level6_fk_key,
971 denorm_rec.level5_fk_key,denorm_rec.level4_fk_key,
972 denorm_rec.level3_fk_key,denorm_rec.level2_fk_key,
973 denorm_rec.level1_fk_key,
974 denorm_rec.level9_name,
975 denorm_rec.level7_name,
976 denorm_rec.level6_name,
977 denorm_rec.level5_name,
978 denorm_rec.level4_name,
979 denorm_rec.level3_name,
980 denorm_rec.level2_name,
981 denorm_rec.level1_name
982 LIMIT v_limit;
983
984 --terminating condition
985 EXIT WHEN denorm_rec.baselevel_fk_key.count =0;
986
987 --bulk update using forall
988 FORALL i IN
989 denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
990 UPDATE MTH_ITEM_DENORM_D_TMP
991 SET
992 level8_fk_key = denorm_rec.level9_fk_key(i),
993 level7_fk_key = denorm_rec.level7_fk_key(i),
994 level6_fk_key = denorm_rec.level6_fk_key(i),
995 level5_fk_key = denorm_rec.level5_fk_key(i),
996 level4_fk_key = denorm_rec.level4_fk_key(i),
997 level3_fk_key = denorm_rec.level3_fk_key(i),
998 level2_fk_key = denorm_rec.level2_fk_key(i),
999 level1_fk_key = denorm_rec.level1_fk_key(i),
1000 level8_name = denorm_rec.level9_name(i),
1001 level7_name = denorm_rec.level7_name(i),
1002 level6_name = denorm_rec.level6_name(i),
1003 level5_name = denorm_rec.level5_name(i),
1004 level4_name = denorm_rec.level4_name(i),
1005 level3_name = denorm_rec.level3_name(i),
1006 level2_name = denorm_rec.level2_name(i),
1007 level1_name = denorm_rec.level1_name(i)
1008 WHERE
1009 item_fk_key = denorm_rec.baselevel_fk_key(i)
1010 AND hierarchy_id= denorm_rec.hierarchy_id(i);
1011 END LOOP;
1012 --close the cursor
1013 CLOSE item_cur;
1014
1015 --handle exceptions
1016 EXCEPTION
1017 WHEN NO_DATA_FOUND THEN
1018 RAISE_APPLICATION_ERROR (-20001,
1019 'Exception has occured');
1020
1021 END mth_hrchy_balance_load ;
1022
1023 /* ****************************************************************************
1024 * Procedure :ITEM_DIM_LOAD_DENORM_TMP *
1025 * Description :This procedure is used to populate global temporary *
1026 * table for incremental load. *
1027 * File Name :MTHITEMDB.PLS *
1028 * Visibility :Private *
1029 * Parameters : *
1030 * Modification log : *
1031 * Author Date Change *
1032 * Yong Feng 10-July-2008 Initial Creation *
1033 **************************************************************************** */
1034
1035
1036 PROCEDURE ITEM_DIM_LOAD_DENORM_TMP
1037 IS
1038 v_unassigned_key number;
1039 v_unassigned_item_name varchar2(240);
1040 v_unassigned_category_name varchar2(240);
1041 BEGIN
1042
1043 v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
1044
1045 BEGIN
1046 SELECT ITEM_NAME into v_unassigned_item_name
1047 from MTH_ITEMS_D
1048 where item_PK_KEY = v_unassigned_key;
1049 exception
1050 when TOO_MANY_ROWS then
1051 v_unassigned_item_name := null;
1052 when NO_DATA_FOUND then
1053 v_unassigned_item_name := null;
1054 when others then
1055 v_unassigned_item_name := null;
1056 end;
1057
1058 BEGIN
1059 select CATEGORY_NAME into v_unassigned_category_name
1060 from MTH_ITEM_CATEGORIES_D
1061 where CATEGORY_PK_KEY = v_unassigned_key;
1062 exception
1063 when TOO_MANY_ROWS then
1064 v_unassigned_item_name := null;
1065 when NO_DATA_FOUND then
1066 v_unassigned_item_name := null;
1067 when others then
1068 v_unassigned_item_name := null;
1069 end;
1070
1071 INSERT
1072 INTO MTH.MTH_ITEM_DENORM_D_TMP
1073 (
1074 HIERARCHY_ID,
1075 ITEM_FK_KEY,
1076 LEVEL1_FK_KEY,
1077 LEVEL2_FK_KEY,
1078 LEVEL3_FK_KEY,
1079 LEVEL4_FK_KEY,
1080 LEVEL5_FK_KEY,
1081 LEVEL6_FK_KEY,
1082 LEVEL7_FK_KEY,
1083 LEVEL8_FK_KEY,
1084 LEVEL9_FK_KEY,
1085 LEVEL_NUM,
1086 ITEM_NAME,
1087 LEVEL1_NAME,
1088 LEVEL2_NAME,
1089 LEVEL3_NAME,
1090 LEVEL4_NAME,
1091 LEVEL5_NAME,
1092 LEVEL6_NAME,
1096 )
1093 LEVEL7_NAME,
1094 LEVEL8_NAME,
1095 LEVEL9_NAME
1097 SELECT HIERARCHY_ID,
1098 ITEM_FK_KEY,
1099 LEVEL1_FK_KEY,
1100 LEVEL2_FK_KEY,
1101 LEVEL3_FK_KEY,
1102 LEVEL4_FK_KEY,
1103 LEVEL5_FK_KEY,
1104 LEVEL6_FK_KEY,
1105 LEVEL7_FK_KEY,
1106 LEVEL8_FK_KEY,
1107 LEVEL9_FK_KEY,
1108 LEVEL_NUM,
1109 ITEM_NAME,
1110 LEVEL1_NAME,
1111 LEVEL2_NAME,
1112 LEVEL3_NAME,
1113 LEVEL4_NAME,
1114 LEVEL5_NAME,
1115 LEVEL6_NAME,
1116 LEVEL7_NAME,
1117 LEVEL8_NAME,
1118 LEVEL9_NAME
1119 FROM (SELECT HIERARCHY_ID,
1120 ITEM_PK_KEY ITEM_FK_KEY,
1121 MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
1122 MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
1123 MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
1124 MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
1125 MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
1126 MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
1127 MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
1128 MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
1129 MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
1130 MAX(LEVEL_NUM) LEVEL_NUM,
1131 MAX(ITEM_NAME) ITEM_NAME,
1132 MAX(LEVEL1_NAME) LEVEL1_NAME,
1133 MAX(LEVEL2_NAME) LEVEL2_NAME,
1134 MAX(LEVEL3_NAME) LEVEL3_NAME,
1135 MAX(LEVEL4_NAME) LEVEL4_NAME,
1136 MAX(LEVEL5_NAME) LEVEL5_NAME,
1137 MAX(LEVEL6_NAME) LEVEL6_NAME,
1138 MAX(LEVEL7_NAME) LEVEL7_NAME,
1139 MAX(LEVEL8_NAME) LEVEL8_NAME,
1140 MAX(LEVEL9_NAME) LEVEL9_NAME
1141 FROM (SELECT HIERARCHY_ID,
1142 B.ITEM_PK_KEY,
1143 (CASE
1144 WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
1145 ELSE NULL
1146 END) LEVEL9_FK_KEY,
1147 (CASE
1148 WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
1149 ELSE NULL
1150 END) LEVEL8_FK_KEY,
1151 (CASE
1152 WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
1153 ELSE NULL
1154 END) LEVEL7_FK_KEY,
1155 (CASE
1156 WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
1157 ELSE NULL
1158 END) LEVEL6_FK_KEY,
1159 (CASE
1160 WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
1161 ELSE NULL
1162 END) LEVEL5_FK_KEY,
1163 (CASE
1164 WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
1165 ELSE NULL
1166 END) LEVEL4_FK_KEY,
1167 (CASE
1168 WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
1169 ELSE NULL
1170 END) LEVEL3_FK_KEY,
1171 (CASE
1172 WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
1173 ELSE NULL
1174 END) LEVEL2_FK_KEY,
1175 (CASE
1176 WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
1177 ELSE NULL
1178 END) LEVEL1_FK_KEY,
1179 10 LEVEL_NUM,
1180 B.ITEM_NAME,
1181 (CASE
1182 WHEN LEVEL_NUM = 1 THEN PARENT_NAME
1183 ELSE NULL
1184 END) LEVEL9_NAME,
1185 (CASE
1186 WHEN LEVEL_NUM = 2 THEN PARENT_NAME
1187 ELSE NULL
1188 END) LEVEL8_NAME,
1189 (CASE
1190 WHEN LEVEL_NUM = 3 THEN PARENT_NAME
1191 ELSE NULL
1192 END) LEVEL7_NAME,
1193 (CASE
1194 WHEN LEVEL_NUM = 4 THEN PARENT_NAME
1195 ELSE NULL
1196 END) LEVEL6_NAME,
1197 (CASE
1198 WHEN LEVEL_NUM = 5 THEN PARENT_NAME
1199 ELSE NULL
1200 END) LEVEL5_NAME,
1201 (CASE
1202 WHEN LEVEL_NUM = 6 THEN PARENT_NAME
1203 ELSE NULL
1204 END) LEVEL4_NAME,
1205 (CASE
1206 WHEN LEVEL_NUM = 7 THEN PARENT_NAME
1207 ELSE NULL
1208 END) LEVEL3_NAME,
1209 (CASE
1210 WHEN LEVEL_NUM = 8 THEN PARENT_NAME
1211 ELSE NULL
1212 END) LEVEL2_NAME,
1213 (CASE
1214 WHEN LEVEL_NUM = 9 THEN PARENT_NAME
1215 ELSE NULL
1216 END) LEVEL1_NAME
1217 FROM (SELECT LEVEL LEVEL_NUM,
1218 ITEM_PK_KEY,
1219 LEVEL_FK_KEY,
1220 LEVEL_NAME,
1221 PARENT_FK_KEY,
1222 PARENT_NAME,
1226 MTH_ITEMS_D B
1223 SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
1224 HIERARCHY_ID
1225 FROM MTH_ITEM_HIERARCHY,
1227 WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
1228 START WITH B.ITEM_PK_KEY IS NOT NULL
1229 CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
1230 AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
1231 (SELECT ITEM_PK_KEY, ITEM_NAME
1232 FROM MTH_ITEMS_D) B
1233 WHERE A.PATH = ('/' || B.ITEM_PK_KEY ) OR
1234 A.PATH LIKE '/' || B.ITEM_PK_KEY || '/%' )
1235 --The granuality level is item and this would be done
1236 -- for all the hierarchies
1237 GROUP BY HIERARCHY_ID,ITEM_PK_KEY
1238 UNION
1239 select mdh.hierarchy_id,
1240 MTH_UTIL_PKG.MTH_UA_GET_VAL item_fk_key
1241 ,v_unassigned_key level1_level_key
1242 ,v_unassigned_key level2_level_key
1243 ,v_unassigned_key level3_level_key
1244 ,v_unassigned_key level4_level_key
1245 ,v_unassigned_key level5_level_key
1246 ,v_unassigned_key level6_level_key
1247 ,v_unassigned_key level7_level_key
1248 ,v_unassigned_key level8_level_key
1249 ,v_unassigned_key level9_level_key
1250 ,10 level_num
1251 ,v_unassigned_item_name item_name
1252 ,v_unassigned_category_name level1_name
1253 ,v_unassigned_category_name level2_name
1254 ,v_unassigned_category_name level3_name
1255 ,v_unassigned_category_name level4_name
1256 ,v_unassigned_category_name level5_name
1257 ,v_unassigned_category_name level6_name
1258 ,v_unassigned_category_name level7_name
1259 ,v_unassigned_category_name level8_name
1260 ,v_unassigned_category_name level9_name
1261 from dual,
1262 mth_dim_hierarchy mdh,
1263 (select distinct hierarchy_id
1264 from mth_dim_level_lookup) mdll
1265 where mdh.dimension_name= 'ITEM' and
1266 mdll.hierarchy_id (+) = mdh.hierarchy_id);
1267
1268 -- Balance the item denorm table
1269 mth_hrchy_balance_load('MTH.MTH_ITEM_DENORM_D_TMP');
1270 -- Push up and fill the level key and name for the ones with NULL
1271
1272 UPDATE MTH.MTH_ITEM_DENORM_D_TMP
1273 SET level1_fk_key = nvl(level1_fk_key, v_unassigned_key),
1274 level1_name = decode(level1_fk_key, NULL, v_unassigned_category_name, level1_name),
1275 level2_fk_key = nvl(level2_fk_key, v_unassigned_key),
1276 level2_name = Decode(level2_fk_key, NULL, v_unassigned_category_name, level2_name),
1277 level3_fk_key = nvl(level3_fk_key, v_unassigned_key),
1278 level3_name = decode(level3_fk_key, NULL, v_unassigned_category_name, level3_name),
1279 level4_fk_key = nvl(level4_fk_key, v_unassigned_key),
1280 level4_name = decode(level4_fk_key, NULL, v_unassigned_category_name, level4_name),
1281 level5_fk_key = nvl(level5_fk_key, v_unassigned_key),
1282 level5_name = decode(level5_fk_key, NULL, v_unassigned_category_name, level5_name),
1283 level6_fk_key = nvl(level6_fk_key, v_unassigned_key),
1284 level6_name = decode(level6_fk_key, NULL, v_unassigned_category_name, level6_name),
1285 level7_fk_key = nvl(level7_fk_key, v_unassigned_key),
1286 level7_name = decode(level7_fk_key, NULL, v_unassigned_category_name, level7_name),
1287 level8_fk_key = nvl(level8_fk_key, v_unassigned_key),
1288 level8_name = decode(level8_fk_key, NULL, v_unassigned_category_name, level8_name),
1289 level9_fk_key = nvl(level9_fk_key, v_unassigned_key),
1290 level9_name = decode(level9_fk_key, NULL, v_unassigned_category_name, level9_name);
1291
1292
1293 -- Add entries for all the rest of levels
1294
1295
1296 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1297 (
1298 HIERARCHY_ID,
1299 ITEM_FK_KEY,
1300 LEVEL1_FK_KEY,
1301 LEVEL2_FK_KEY,
1302 LEVEL3_FK_KEY,
1303 LEVEL4_FK_KEY,
1304 LEVEL5_FK_KEY,
1305 LEVEL6_FK_KEY,
1306 LEVEL7_FK_KEY,
1307 LEVEL8_FK_KEY,
1308 LEVEL9_FK_KEY,
1309 LEVEL_NUM,
1310 ITEM_NAME,
1311 LEVEL1_NAME,
1312 LEVEL2_NAME,
1313 LEVEL3_NAME,
1314 LEVEL4_NAME,
1315 LEVEL5_NAME,
1316 LEVEL6_NAME,
1317 LEVEL7_NAME,
1318 LEVEL8_NAME,
1319 LEVEL9_NAME
1320 )
1321 select distinct
1322 HIERARCHY_ID,
1323 NULL,
1324 LEVEL1_FK_KEY,
1325 LEVEL2_FK_KEY,
1326 LEVEL3_FK_KEY,
1327 LEVEL4_FK_KEY,
1328 LEVEL5_FK_KEY,
1329 LEVEL6_FK_KEY,
1330 LEVEL7_FK_KEY,
1331 LEVEL8_FK_KEY,
1332 LEVEL9_FK_KEY,
1333 9,
1334 NULL,
1335 LEVEL1_NAME,
1336 LEVEL2_NAME,
1337 LEVEL3_NAME,
1338 LEVEL4_NAME,
1339 LEVEL5_NAME,
1340 LEVEL6_NAME,
1341 LEVEL7_NAME,
1342 LEVEL8_NAME,
1343 LEVEL9_NAME
1344 From MTH.MTH_ITEM_DENORM_D_TMP
1345 Where LEVEL9_FK_KEY is not null and level_NUM = 10;
1346
1347 -- insert level 8 entries
1348 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1349 (
1350 HIERARCHY_ID,
1351 ITEM_FK_KEY,
1352 LEVEL1_FK_KEY,
1353 LEVEL2_FK_KEY,
1354 LEVEL3_FK_KEY,
1355 LEVEL4_FK_KEY,
1356 LEVEL5_FK_KEY,
1357 LEVEL6_FK_KEY,
1358 LEVEL7_FK_KEY,
1359 LEVEL8_FK_KEY,
1360 LEVEL9_FK_KEY,
1361 LEVEL_NUM,
1365 LEVEL3_NAME,
1362 ITEM_NAME,
1363 LEVEL1_NAME,
1364 LEVEL2_NAME,
1366 LEVEL4_NAME,
1367 LEVEL5_NAME,
1368 LEVEL6_NAME,
1369 LEVEL7_NAME,
1370 LEVEL8_NAME,
1371 LEVEL9_NAME
1372 )
1373 select distinct
1374 HIERARCHY_ID,
1375 NULL,
1376 LEVEL1_FK_KEY,
1377 LEVEL2_FK_KEY,
1378 LEVEL3_FK_KEY,
1379 LEVEL4_FK_KEY,
1380 LEVEL5_FK_KEY,
1381 LEVEL6_FK_KEY,
1382 LEVEL7_FK_KEY,
1383 LEVEL8_FK_KEY,
1384 NULL,
1385 8,
1386 NULL,
1387 LEVEL1_NAME,
1388 LEVEL2_NAME,
1389 LEVEL3_NAME,
1390 LEVEL4_NAME,
1391 LEVEL5_NAME,
1392 LEVEL6_NAME,
1393 LEVEL7_NAME,
1394 LEVEL8_NAME,
1395 NULL
1396 From MTH.MTH_ITEM_DENORM_D_TMP
1397 Where LEVEL8_FK_KEY is not null and level_NUM = 9;
1398
1399 -- insert level 7 entries
1400 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1401 (
1402 HIERARCHY_ID,
1403 ITEM_FK_KEY,
1404 LEVEL1_FK_KEY,
1405 LEVEL2_FK_KEY,
1406 LEVEL3_FK_KEY,
1407 LEVEL4_FK_KEY,
1408 LEVEL5_FK_KEY,
1409 LEVEL6_FK_KEY,
1410 LEVEL7_FK_KEY,
1411 LEVEL8_FK_KEY,
1412 LEVEL9_FK_KEY,
1413 LEVEL_NUM,
1414 ITEM_NAME,
1415 LEVEL1_NAME,
1416 LEVEL2_NAME,
1417 LEVEL3_NAME,
1418 LEVEL4_NAME,
1419 LEVEL5_NAME,
1420 LEVEL6_NAME,
1421 LEVEL7_NAME,
1422 LEVEL8_NAME,
1423 LEVEL9_NAME
1424 )
1425 select distinct
1426 HIERARCHY_ID,
1427 NULL,
1428 LEVEL1_FK_KEY,
1429 LEVEL2_FK_KEY,
1430 LEVEL3_FK_KEY,
1431 LEVEL4_FK_KEY,
1432 LEVEL5_FK_KEY,
1433 LEVEL6_FK_KEY,
1434 LEVEL7_FK_KEY,
1435 NULL,
1436 NULL,
1437 7,
1438 NULL,
1439 LEVEL1_NAME,
1440 LEVEL2_NAME,
1441 LEVEL3_NAME,
1442 LEVEL4_NAME,
1443 LEVEL5_NAME,
1444 LEVEL6_NAME,
1445 LEVEL7_NAME,
1446 NULL,
1447 NULL
1448 From MTH.MTH_ITEM_DENORM_D_TMP
1449 Where LEVEL7_FK_KEY is not null and level_NUM = 8;
1450
1451 -- insert level 6 entries
1452 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1453 (
1454 HIERARCHY_ID,
1455 ITEM_FK_KEY,
1456 LEVEL1_FK_KEY,
1457 LEVEL2_FK_KEY,
1458 LEVEL3_FK_KEY,
1459 LEVEL4_FK_KEY,
1460 LEVEL5_FK_KEY,
1461 LEVEL6_FK_KEY,
1462 LEVEL7_FK_KEY,
1463 LEVEL8_FK_KEY,
1464 LEVEL9_FK_KEY,
1465 LEVEL_NUM,
1466 ITEM_NAME,
1467 LEVEL1_NAME,
1468 LEVEL2_NAME,
1469 LEVEL3_NAME,
1470 LEVEL4_NAME,
1471 LEVEL5_NAME,
1472 LEVEL6_NAME,
1473 LEVEL7_NAME,
1474 LEVEL8_NAME,
1475 LEVEL9_NAME
1476 )
1477 select distinct
1478 HIERARCHY_ID,
1479 NULL,
1480 LEVEL1_FK_KEY,
1481 LEVEL2_FK_KEY,
1482 LEVEL3_FK_KEY,
1483 LEVEL4_FK_KEY,
1484 LEVEL5_FK_KEY,
1485 LEVEL6_FK_KEY,
1486 NULL,
1487 NULL,
1488 NULL,
1489 6,
1490 NULL,
1491 LEVEL1_NAME,
1492 LEVEL2_NAME,
1493 LEVEL3_NAME,
1494 LEVEL4_NAME,
1495 LEVEL5_NAME,
1496 LEVEL6_NAME,
1497 NULL,
1498 NULL,
1499 NULL
1500 From MTH.MTH_ITEM_DENORM_D_TMP
1501 Where LEVEL6_FK_KEY is not null and level_NUM = 7;
1502
1503 -- insert level 5 entries
1504 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1505 (
1506 HIERARCHY_ID,
1507 ITEM_FK_KEY,
1508 LEVEL1_FK_KEY,
1509 LEVEL2_FK_KEY,
1510 LEVEL3_FK_KEY,
1511 LEVEL4_FK_KEY,
1512 LEVEL5_FK_KEY,
1513 LEVEL6_FK_KEY,
1514 LEVEL7_FK_KEY,
1515 LEVEL8_FK_KEY,
1516 LEVEL9_FK_KEY,
1517 LEVEL_NUM,
1518 ITEM_NAME,
1519 LEVEL1_NAME,
1520 LEVEL2_NAME,
1521 LEVEL3_NAME,
1522 LEVEL4_NAME,
1523 LEVEL5_NAME,
1524 LEVEL6_NAME,
1525 LEVEL7_NAME,
1526 LEVEL8_NAME,
1527 LEVEL9_NAME
1528 )
1529 select distinct
1530 HIERARCHY_ID,
1531 NULL,
1532 LEVEL1_FK_KEY,
1533 LEVEL2_FK_KEY,
1534 LEVEL3_FK_KEY,
1535 LEVEL4_FK_KEY,
1536 LEVEL5_FK_KEY,
1537 NULL,
1538 NULL,
1539 NULL,
1540 NULL,
1541 5,
1542 NULL,
1543 LEVEL1_NAME,
1544 LEVEL2_NAME,
1545 LEVEL3_NAME,
1546 LEVEL4_NAME,
1547 LEVEL5_NAME,
1548 NULL,
1549 NULL,
1550 NULL,
1551 NULL
1552 From MTH.MTH_ITEM_DENORM_D_TMP
1553 Where LEVEL5_FK_KEY is not null and level_NUM = 6;
1554
1555 -- insert level 4 entries
1556 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1560 LEVEL1_FK_KEY,
1557 (
1558 HIERARCHY_ID,
1559 ITEM_FK_KEY,
1561 LEVEL2_FK_KEY,
1562 LEVEL3_FK_KEY,
1563 LEVEL4_FK_KEY,
1564 LEVEL5_FK_KEY,
1565 LEVEL6_FK_KEY,
1566 LEVEL7_FK_KEY,
1567 LEVEL8_FK_KEY,
1568 LEVEL9_FK_KEY,
1569 LEVEL_NUM,
1570 ITEM_NAME,
1571 LEVEL1_NAME,
1572 LEVEL2_NAME,
1573 LEVEL3_NAME,
1574 LEVEL4_NAME,
1575 LEVEL5_NAME,
1576 LEVEL6_NAME,
1577 LEVEL7_NAME,
1578 LEVEL8_NAME,
1579 LEVEL9_NAME
1580 )
1581 select distinct
1582 HIERARCHY_ID,
1583 NULL,
1584 LEVEL1_FK_KEY,
1585 LEVEL2_FK_KEY,
1586 LEVEL3_FK_KEY,
1587 LEVEL4_FK_KEY,
1588 NULL,
1589 NULL,
1590 NULL,
1591 NULL,
1592 NULL,
1593 4,
1594 NULL,
1595 LEVEL1_NAME,
1596 LEVEL2_NAME,
1597 LEVEL3_NAME,
1598 LEVEL4_NAME,
1599 NULL,
1600 NULL,
1601 NULL,
1602 NULL,
1603 NULL
1604 From MTH.MTH_ITEM_DENORM_D_TMP
1605 Where LEVEL4_FK_KEY is not null and level_NUM = 5;
1606
1607
1608 -- insert level 3 entries
1609 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1610 (
1611 HIERARCHY_ID,
1612 ITEM_FK_KEY,
1613 LEVEL1_FK_KEY,
1614 LEVEL2_FK_KEY,
1615 LEVEL3_FK_KEY,
1616 LEVEL4_FK_KEY,
1617 LEVEL5_FK_KEY,
1618 LEVEL6_FK_KEY,
1619 LEVEL7_FK_KEY,
1620 LEVEL8_FK_KEY,
1621 LEVEL9_FK_KEY,
1622 LEVEL_NUM,
1623 ITEM_NAME,
1624 LEVEL1_NAME,
1625 LEVEL2_NAME,
1626 LEVEL3_NAME,
1627 LEVEL4_NAME,
1628 LEVEL5_NAME,
1629 LEVEL6_NAME,
1630 LEVEL7_NAME,
1631 LEVEL8_NAME,
1632 LEVEL9_NAME
1633 )
1634 select distinct
1635 HIERARCHY_ID,
1636 NULL,
1637 LEVEL1_FK_KEY,
1638 LEVEL2_FK_KEY,
1639 LEVEL3_FK_KEY,
1640 NULL,
1641 NULL,
1642 NULL,
1643 NULL,
1644 NULL,
1645 NULL,
1646 3,
1647 NULL,
1648 LEVEL1_NAME,
1649 LEVEL2_NAME,
1650 LEVEL3_NAME,
1651 NULL,
1652 NULL,
1653 NULL,
1654 NULL,
1655 NULL,
1656 NULL
1657 From MTH.MTH_ITEM_DENORM_D_TMP
1658 Where LEVEL3_FK_KEY is not null and level_NUM = 4;
1659
1660
1661 -- insert level 2 entries
1662 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1663 (
1664 HIERARCHY_ID,
1665 ITEM_FK_KEY,
1666 LEVEL1_FK_KEY,
1667 LEVEL2_FK_KEY,
1668 LEVEL3_FK_KEY,
1669 LEVEL4_FK_KEY,
1670 LEVEL5_FK_KEY,
1671 LEVEL6_FK_KEY,
1672 LEVEL7_FK_KEY,
1673 LEVEL8_FK_KEY,
1674 LEVEL9_FK_KEY,
1675 LEVEL_NUM,
1676 ITEM_NAME,
1677 LEVEL1_NAME,
1678 LEVEL2_NAME,
1679 LEVEL3_NAME,
1680 LEVEL4_NAME,
1681 LEVEL5_NAME,
1682 LEVEL6_NAME,
1683 LEVEL7_NAME,
1684 LEVEL8_NAME,
1685 LEVEL9_NAME
1686 )
1687 select distinct
1688 HIERARCHY_ID,
1689 NULL,
1690 LEVEL1_FK_KEY,
1691 LEVEL2_FK_KEY,
1692 NULL,
1693 NULL,
1694 NULL,
1695 NULL,
1696 NULL,
1697 NULL,
1698 NULL,
1699 2,
1700 NULL,
1701 LEVEL1_NAME,
1702 LEVEL2_NAME,
1703 NULL,
1704 NULL,
1705 NULL,
1706 NULL,
1707 NULL,
1708 NULL,
1709 NULL
1710 From MTH.MTH_ITEM_DENORM_D_TMP
1711 Where LEVEL2_FK_KEY is not null and level_NUM = 3;
1712
1713
1714
1715 -- insert level 1 entries
1716 Insert into MTH.MTH_ITEM_DENORM_D_TMP
1717 (
1718 HIERARCHY_ID,
1719 ITEM_FK_KEY,
1720 LEVEL1_FK_KEY,
1721 LEVEL2_FK_KEY,
1722 LEVEL3_FK_KEY,
1723 LEVEL4_FK_KEY,
1724 LEVEL5_FK_KEY,
1725 LEVEL6_FK_KEY,
1726 LEVEL7_FK_KEY,
1727 LEVEL8_FK_KEY,
1728 LEVEL9_FK_KEY,
1729 LEVEL_NUM,
1730 ITEM_NAME,
1731 LEVEL1_NAME,
1732 LEVEL2_NAME,
1733 LEVEL3_NAME,
1734 LEVEL4_NAME,
1735 LEVEL5_NAME,
1736 LEVEL6_NAME,
1737 LEVEL7_NAME,
1738 LEVEL8_NAME,
1739 LEVEL9_NAME
1740 )
1741 select distinct
1742 HIERARCHY_ID,
1743 null,
1744 LEVEL1_FK_KEY,
1745 NULL,
1746 NULL,
1747 NULL,
1748 NULL,
1749 NULL,
1750 NULL,
1751 NULL,
1752 NULL,
1753 1,
1754 null,
1755 LEVEL1_NAME,
1756 NULL,
1757 NULL,
1758 NULL,
1759 NULL,
1760 NULL,
1761 NULL,
1762 NULL,
1763 NULL
1764 From MTH.MTH_ITEM_DENORM_D_TMP
1765 Where LEVEL1_FK_KEY is not null and level_NUM = 2;
1766
1767
1768 EXCEPTION
1769 WHEN OTHERS THEN
1770 ROLLBACK;
1771 RAISE;
1772 END ITEM_DIM_LOAD_DENORM_TMP;
1773
1774
1775 /* ****************************************************************************
1776 * Procedure :ITEM_DIM_HRCHY_LEVEL_LOAD *
1777 * Description :This procedure will populate the level information *
1778 * for the item - category and category - category *
1779 * relatiopnships in the item hierarchy staging table *
1780 * File Name :MTHITEMDB.PLS *
1781 * Visibility :Public *
1782 * Parameters : *
1783 * Modification log : *
1784 * Author Date Change *
1785 * Ankit Goyal 29-May--2007 Initial Creation *
1786 **************************************************************************** */
1787
1788 PROCEDURE ITEM_DIM_HRCHY_LEVEL_LOAD
1789 IS
1790 BEGIN
1791
1792 --use connect by to find out the level number for the child and update the row
1793 --the root node will be level 1 and the leaf node will have highest level
1794 --having the hierarchy name in the connect query will allow multiple
1795 --hierarchies to be present in the staging table.
1796 --the start condition of the connect by query is the root node for
1797 --which the parent is null.
1798
1799
1800 UPDATE MTH_ITEM_HIERARCHY_STG A
1801 SET LEVEL_NUM =
1802 (SELECT LEVEL
1803 FROM MTH_ITEM_HIERARCHY_STG B
1804 WHERE A.LEVEL_FK = B.LEVEL_FK
1805 AND A.HIERARCHY_NAME = B.HIERARCHY_NAME CONNECT BY PRIOR
1806 B.LEVEL_FK = B.PARENT_FK
1807 AND PRIOR B.HIERARCHY_NAME = A.HIERARCHY_NAME START
1808 WITH B.PARENT_FK IS NULL
1809 );
1810 COMMIT;
1811 EXCEPTION
1812 WHEN OTHERS THEN
1813 ROLLBACK;
1814
1815 --End of the procedure ITEM_DIM_HRCHY_LEVEL_LOAD
1816 END ITEM_DIM_HRCHY_LEVEL_LOAD;
1817
1818 /* ****************************************************************************
1819 * Function :load_items_from_changed_cats *
1820 * Description :This funciton populates the denorm temp table *
1821 * for items found in item denorm table whose parents match with *
1822 * updated item category entries in the item hierarchy table *
1823 * File Name :MTHITEMDB.PLS *
1824 * Visibility :private *
1825 * Parameters : *
1826 * Modification log : *
1827 * Author Date Change *
1828 * 30-June--2012 Initial Creation *
1829 **************************************************************************** */
1830
1831 FUNCTION load_items_from_changed_cats (p_log_from_date IN DATE,
1832 p_log_to_date IN DATE,
1833 p_unassigned_key IN NUMBER) RETURN NUMBER
1834 IS
1835 BEGIN
1836
1837 INSERT
1838 INTO MTH_ITEM_DENORM_D_TMP
1839 (
1840 HIERARCHY_ID,
1841 ITEM_FK_KEY,
1842 LEVEL1_FK_KEY,
1843 LEVEL2_FK_KEY,
1844 LEVEL3_FK_KEY,
1845 LEVEL4_FK_KEY,
1846 LEVEL5_FK_KEY,
1847 LEVEL6_FK_KEY,
1848 LEVEL7_FK_KEY,
1849 LEVEL8_FK_KEY,
1850 LEVEL9_FK_KEY,
1851 LEVEL_NUM,
1852 ITEM_NAME,
1853 LEVEL1_NAME,
1854 LEVEL2_NAME,
1855 LEVEL3_NAME,
1856 LEVEL4_NAME,
1857 LEVEL5_NAME,
1858 LEVEL6_NAME,
1859 LEVEL7_NAME,
1860 LEVEL8_NAME,
1861 LEVEL9_NAME
1862 )
1863
1864 SELECT HIERARCHY_ID,
1865 ITEM_FK_KEY,
1866 LEVEL1_FK_KEY,
1867 LEVEL2_FK_KEY,
1868 LEVEL3_FK_KEY,
1869 LEVEL4_FK_KEY,
1870 LEVEL5_FK_KEY,
1871 LEVEL6_FK_KEY,
1872 LEVEL7_FK_KEY,
1873 LEVEL8_FK_KEY,
1874 LEVEL9_FK_KEY,
1875 LEVEL_NUM,
1876 ITEM_NAME,
1877 LEVEL1_NAME,
1878 LEVEL2_NAME,
1879 LEVEL3_NAME,
1880 LEVEL4_NAME,
1881 LEVEL5_NAME,
1882 LEVEL6_NAME,
1883 LEVEL7_NAME,
1884 LEVEL8_NAME,
1885 LEVEL9_NAME
1886 FROM (SELECT HIERARCHY_ID,
1887 ITEM_PK_KEY ITEM_FK_KEY,
1888 MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
1889 MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
1890 MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
1891 MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
1892 MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
1893 MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
1894 MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
1895 MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
1896 MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
1897 MAX(LEVEL_NUM) LEVEL_NUM,
1898 MAX(ITEM_NAME) ITEM_NAME,
1899 MAX(LEVEL1_NAME) LEVEL1_NAME,
1900 MAX(LEVEL2_NAME) LEVEL2_NAME,
1901 MAX(LEVEL3_NAME) LEVEL3_NAME,
1902 MAX(LEVEL4_NAME) LEVEL4_NAME,
1903 MAX(LEVEL5_NAME) LEVEL5_NAME,
1904 MAX(LEVEL6_NAME) LEVEL6_NAME,
1905 MAX(LEVEL7_NAME) LEVEL7_NAME,
1906 MAX(LEVEL8_NAME) LEVEL8_NAME,
1907 MAX(LEVEL9_NAME) LEVEL9_NAME
1908 FROM (SELECT HIERARCHY_ID,
1909 B.ITEM_PK_KEY,
1910 (CASE
1911 WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
1912 ELSE NULL
1913 END) LEVEL9_FK_KEY,
1914 (CASE
1915 WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
1916 ELSE NULL
1917 END) LEVEL8_FK_KEY,
1918 (CASE
1919 WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
1920 ELSE NULL
1921 END) LEVEL7_FK_KEY,
1922 (CASE
1923 WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
1924 ELSE NULL
1925 END) LEVEL6_FK_KEY,
1926 (CASE
1927 WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
1928 ELSE NULL
1929 END) LEVEL5_FK_KEY,
1930 (CASE
1931 WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
1932 ELSE NULL
1933 END) LEVEL4_FK_KEY,
1934 (CASE
1935 WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
1936 ELSE NULL
1937 END) LEVEL3_FK_KEY,
1938 (CASE
1939 WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
1940 ELSE NULL
1941 END) LEVEL2_FK_KEY,
1942 (CASE
1943 WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
1944 ELSE NULL
1945 END) LEVEL1_FK_KEY,
1946 10 LEVEL_NUM,
1947 B.ITEM_NAME,
1948 (CASE
1949 WHEN LEVEL_NUM = 1 THEN PARENT_NAME
1950 ELSE NULL
1951 END) LEVEL9_NAME,
1952 (CASE
1953 WHEN LEVEL_NUM = 2 THEN PARENT_NAME
1954 ELSE NULL
1955 END) LEVEL8_NAME,
1956 (CASE
1957 WHEN LEVEL_NUM = 3 THEN PARENT_NAME
1958 ELSE NULL
1959 END) LEVEL7_NAME,
1960 (CASE
1961 WHEN LEVEL_NUM = 4 THEN PARENT_NAME
1962 ELSE NULL
1963 END) LEVEL6_NAME,
1964 (CASE
1965 WHEN LEVEL_NUM = 5 THEN PARENT_NAME
1966 ELSE NULL
1967 END) LEVEL5_NAME,
1968 (CASE
1969 WHEN LEVEL_NUM = 6 THEN PARENT_NAME
1970 ELSE NULL
1971 END) LEVEL4_NAME,
1972 (CASE
1973 WHEN LEVEL_NUM = 7 THEN PARENT_NAME
1974 ELSE NULL
1975 END) LEVEL3_NAME,
1976 (CASE
1977 WHEN LEVEL_NUM = 8 THEN PARENT_NAME
1978 ELSE NULL
1979 END) LEVEL2_NAME,
1980 (CASE
1981 WHEN LEVEL_NUM = 9 THEN PARENT_NAME
1982 ELSE NULL
1983 END) LEVEL1_NAME
1984 FROM (SELECT LEVEL LEVEL_NUM,
1985 ITEM_PK_KEY,
1986 LEVEL_FK_KEY,
1987 LEVEL_NAME,
1988 PARENT_FK_KEY,
1989 PARENT_NAME,
1990 SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
1991 HIERARCHY_ID
1992 FROM MTH_ITEM_HIERARCHY,
1993 MTH_ITEMS_D B
1994 WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
1995 START WITH B.ITEM_PK_KEY IS NOT NULL AND
1996 B.ITEM_PK_KEY IN
1997 (SELECT c.item_fk_key
1998 FROM mth_item_hierarchy a ,
1999 mth_item_categories_d bb,
2000 mth_item_denorm_d c
2001 WHERE (a.level_fk_key = c.level9_fk_key OR
2002 a.level_fk_key = c.level8_fk_key OR
2003 a.level_fk_key = c.level7_fk_key OR
2004 a.level_fk_key = c.level6_fk_key OR
2005 a.level_fk_key = c.level5_fk_key OR
2006 a.level_fk_key = c.level4_fk_key OR
2007 a.level_fk_key = c.level3_fk_key OR
2008 a.level_fk_key = c.level2_fk_key OR
2009 a.level_fk_key = c.level1_fk_key) AND
2010 c.level_num = 10 AND
2011 c.item_fk_key <> p_unassigned_key AND
2012 a.LEVEL_FK_KEY <> p_unassigned_key AND
2013 a.hierarchy_id = c.hierarchy_id AND
2014 a.LEVEL_FK_KEY = Bb.CATEGORY_PK_KEY AND
2015 a.LAST_UPDATE_DATE > p_log_from_date AND
2016 a.LAST_UPDATE_DATE <= p_log_to_date)
2017 CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
2018 AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
2019 (SELECT ITEM_PK_KEY, ITEM_NAME
2020 FROM MTH_ITEMS_D) B
2021 WHERE A.PATH = ('/' || B.ITEM_PK_KEY ) OR
2022 A.PATH LIKE '/' || B.ITEM_PK_KEY || '/%' )
2023 GROUP BY HIERARCHY_ID,ITEM_PK_KEY ) ;
2024
2025 RETURN SQL%ROWCOUNT;
2026
2027 END load_items_from_changed_cats;
2028
2029 /* ****************************************************************************
2030 * Function :load_new_items_to_item_dnm_tmp *
2031 * Description :This funciton populates the denorm temp table *
2032 * for new or newly updated items in item hierarchy since last run *
2033 * File Name :MTHITEMDB.PLS *
2034 * Visibility :private *
2035 * Parameters : *
2036 * Modification log : *
2037 * Author Date Change *
2038 * 30-June--2012 Initial Creation *
2039 **************************************************************************** */
2040
2041 FUNCTION load_new_items_to_item_dnm_tmp(p_log_from_date IN DATE,
2042 p_log_to_date IN DATE ) RETURN NUMBER
2043 IS
2044
2045 v_unassigned_val VARCHAR2(30);
2046
2047
2048 BEGIN
2049
2050 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
2051 INSERT
2052 INTO MTH_ITEM_DENORM_D_TMP
2053 (
2054 HIERARCHY_ID,
2055 ITEM_FK_KEY,
2056 LEVEL1_FK_KEY,
2057 LEVEL2_FK_KEY,
2058 LEVEL3_FK_KEY,
2059 LEVEL4_FK_KEY,
2060 LEVEL5_FK_KEY,
2061 LEVEL6_FK_KEY,
2062 LEVEL7_FK_KEY,
2063 LEVEL8_FK_KEY,
2064 LEVEL9_FK_KEY,
2065 LEVEL_NUM,
2066 ITEM_NAME,
2067 LEVEL1_NAME,
2068 LEVEL2_NAME,
2069 LEVEL3_NAME,
2070 LEVEL4_NAME,
2071 LEVEL5_NAME,
2072 LEVEL6_NAME,
2073 LEVEL7_NAME,
2074 LEVEL8_NAME,
2075 LEVEL9_NAME
2076 )
2077 SELECT HIERARCHY_ID,
2078 ITEM_FK_KEY,
2079 LEVEL1_FK_KEY,
2080 LEVEL2_FK_KEY,
2081 LEVEL3_FK_KEY,
2082 LEVEL4_FK_KEY,
2083 LEVEL5_FK_KEY,
2084 LEVEL6_FK_KEY,
2085 LEVEL7_FK_KEY,
2086 LEVEL8_FK_KEY,
2087 LEVEL9_FK_KEY,
2088 LEVEL_NUM,
2089 ITEM_NAME,
2090 LEVEL1_NAME,
2091 LEVEL2_NAME,
2092 LEVEL3_NAME,
2093 LEVEL4_NAME,
2094 LEVEL5_NAME,
2095 LEVEL6_NAME,
2096 LEVEL7_NAME,
2097 LEVEL8_NAME,
2098 LEVEL9_NAME
2099 FROM (SELECT HIERARCHY_ID,
2100 ITEM_PK_KEY ITEM_FK_KEY,
2101 MAX(LEVEL1_FK_KEY) LEVEL1_FK_KEY,
2102 MAX(LEVEL2_FK_KEY) LEVEL2_FK_KEY,
2103 MAX(LEVEL3_FK_KEY) LEVEL3_FK_KEY,
2104 MAX(LEVEL4_FK_KEY) LEVEL4_FK_KEY,
2105 MAX(LEVEL5_FK_KEY) LEVEL5_FK_KEY,
2109 MAX(LEVEL9_FK_KEY) LEVEL9_FK_KEY,
2106 MAX(LEVEL6_FK_KEY) LEVEL6_FK_KEY,
2107 MAX(LEVEL7_FK_KEY) LEVEL7_FK_KEY,
2108 MAX(LEVEL8_FK_KEY) LEVEL8_FK_KEY,
2110 MAX(LEVEL_NUM) LEVEL_NUM,
2111 MAX(ITEM_NAME) ITEM_NAME,
2112 MAX(LEVEL1_NAME) LEVEL1_NAME,
2113 MAX(LEVEL2_NAME) LEVEL2_NAME,
2114 MAX(LEVEL3_NAME) LEVEL3_NAME,
2115 MAX(LEVEL4_NAME) LEVEL4_NAME,
2116 MAX(LEVEL5_NAME) LEVEL5_NAME,
2117 MAX(LEVEL6_NAME) LEVEL6_NAME,
2118 MAX(LEVEL7_NAME) LEVEL7_NAME,
2119 MAX(LEVEL8_NAME) LEVEL8_NAME,
2120 MAX(LEVEL9_NAME) LEVEL9_NAME
2121 FROM (SELECT HIERARCHY_ID,
2122 B.ITEM_PK_KEY,
2123 (CASE
2124 WHEN LEVEL_NUM = 1 THEN PARENT_FK_KEY
2125 ELSE NULL
2126 END) LEVEL9_FK_KEY,
2127 (CASE
2128 WHEN LEVEL_NUM = 2 THEN PARENT_FK_KEY
2129 ELSE NULL
2130 END) LEVEL8_FK_KEY,
2131 (CASE
2132 WHEN LEVEL_NUM = 3 THEN PARENT_FK_KEY
2133 ELSE NULL
2134 END) LEVEL7_FK_KEY,
2135 (CASE
2136 WHEN LEVEL_NUM = 4 THEN PARENT_FK_KEY
2137 ELSE NULL
2138 END) LEVEL6_FK_KEY,
2139 (CASE
2140 WHEN LEVEL_NUM = 5 THEN PARENT_FK_KEY
2141 ELSE NULL
2142 END) LEVEL5_FK_KEY,
2143 (CASE
2144 WHEN LEVEL_NUM = 6 THEN PARENT_FK_KEY
2145 ELSE NULL
2146 END) LEVEL4_FK_KEY,
2147 (CASE
2148 WHEN LEVEL_NUM = 7 THEN PARENT_FK_KEY
2149 ELSE NULL
2150 END) LEVEL3_FK_KEY,
2151 (CASE
2152 WHEN LEVEL_NUM = 8 THEN PARENT_FK_KEY
2153 ELSE NULL
2154 END) LEVEL2_FK_KEY,
2155 (CASE
2156 WHEN LEVEL_NUM = 9 THEN PARENT_FK_KEY
2157 ELSE NULL
2158 END) LEVEL1_FK_KEY,
2159 10 LEVEL_NUM,
2160 B.ITEM_NAME,
2161 (CASE
2162 WHEN LEVEL_NUM = 1 THEN PARENT_NAME
2163 ELSE NULL
2164 END) LEVEL9_NAME,
2165 (CASE
2166 WHEN LEVEL_NUM = 2 THEN PARENT_NAME
2167 ELSE NULL
2168 END) LEVEL8_NAME,
2169 (CASE
2170 WHEN LEVEL_NUM = 3 THEN PARENT_NAME
2171 ELSE NULL
2172 END) LEVEL7_NAME,
2173 (CASE
2174 WHEN LEVEL_NUM = 4 THEN PARENT_NAME
2175 ELSE NULL
2176 END) LEVEL6_NAME,
2177 (CASE
2178 WHEN LEVEL_NUM = 5 THEN PARENT_NAME
2179 ELSE NULL
2180 END) LEVEL5_NAME,
2181 (CASE
2182 WHEN LEVEL_NUM = 6 THEN PARENT_NAME
2183 ELSE NULL
2184 END) LEVEL4_NAME,
2185 (CASE
2186 WHEN LEVEL_NUM = 7 THEN PARENT_NAME
2187 ELSE NULL
2188 END) LEVEL3_NAME,
2189 (CASE
2190 WHEN LEVEL_NUM = 8 THEN PARENT_NAME
2191 ELSE NULL
2192 END) LEVEL2_NAME,
2193 (CASE
2194 WHEN LEVEL_NUM = 9 THEN PARENT_NAME
2195 ELSE NULL
2196 END) LEVEL1_NAME
2197 FROM (SELECT LEVEL LEVEL_NUM,
2198 ITEM_PK_KEY,
2199 LEVEL_FK_KEY,
2200 LEVEL_NAME,
2201 PARENT_FK_KEY,
2202 PARENT_NAME,
2203 SYS_CONNECT_BY_PATH(LEVEL_FK_KEY,'/') PATH,
2204 HIERARCHY_ID
2205 FROM MTH_ITEM_HIERARCHY,
2206 MTH_ITEMS_D B
2207 WHERE LEVEL_FK_KEY = B.ITEM_PK_KEY (+)
2208 START WITH B.ITEM_PK_KEY IS NOT NULL
2209 and MTH_ITEM_HIERARCHY.level_fk_key<> v_unassigned_val
2210 AND MTH_ITEM_HIERARCHY.LAST_UPDATE_DATE > p_log_from_date
2211 AND MTH_ITEM_HIERARCHY.LAST_UPDATE_DATE <= p_log_to_date
2212 CONNECT BY LEVEL_FK_KEY = PRIOR PARENT_FK_KEY
2213 AND HIERARCHY_ID = PRIOR HIERARCHY_ID) A,
2214 (SELECT ITEM_PK_KEY, ITEM_NAME
2215 FROM MTH_ITEMS_D) B
2216 WHERE A.PATH = ('/' || B.ITEM_PK_KEY ) OR
2217 A.PATH LIKE '/' || B.ITEM_PK_KEY || '/%' )
2218 GROUP BY HIERARCHY_ID,ITEM_PK_KEY );
2219
2220 RETURN SQL%ROWCOUNT;
2221
2222
2223 END load_new_items_to_item_dnm_tmp;
2224
2225 /* ****************************************************************************
2226 * Procedure :UPD_ITEM_DENORM_TMP_UNASSIGNED *
2227 * Description :This procedure is used to update the denorm table *
2228 * tmp table to change the level fk key and corresponding level name *
2229 * from NULL to -99999, and 'Unassigned' *
2230 * File Name :MTHITEMDB.PLS *
2231 * Visibility :Private *
2232 * Parameters : *
2233 * Modification log : *
2234 * Author Date Change *
2235 * 30-June--2012 Initial Creation *
2236 **************************************************************************** */
2237
2238 PROCEDURE UPD_ITEM_DENORM_TMP_UNASSIGNED(p_unassigned_key IN NUMBER,
2239 p_unassigned_category_name IN VARCHAR2)
2240 IS
2241 BEGIN
2242 UPDATE MTH.MTH_ITEM_DENORM_D_TMP
2243 SET level1_fk_key = nvl(level1_fk_key, p_unassigned_key),
2244 level1_name = decode(level1_fk_key, NULL, p_unassigned_category_name, level1_name),
2245 level2_fk_key = nvl(level2_fk_key, p_unassigned_key),
2246 level2_name = Decode(level2_fk_key, NULL, p_unassigned_category_name, level2_name),
2247 level3_fk_key = nvl(level3_fk_key, p_unassigned_key),
2248 level3_name = decode(level3_fk_key, NULL, p_unassigned_category_name, level3_name),
2249 level4_fk_key = nvl(level4_fk_key, p_unassigned_key),
2250 level4_name = decode(level4_fk_key, NULL, p_unassigned_category_name, level4_name),
2251 level5_fk_key = nvl(level5_fk_key, p_unassigned_key),
2252 level5_name = decode(level5_fk_key, NULL, p_unassigned_category_name, level5_name),
2253 level6_fk_key = nvl(level6_fk_key, p_unassigned_key),
2254 level6_name = decode(level6_fk_key, NULL, p_unassigned_category_name, level6_name),
2255 level7_fk_key = nvl(level7_fk_key, p_unassigned_key),
2256 level7_name = decode(level7_fk_key, NULL, p_unassigned_category_name, level7_name),
2257 level8_fk_key = nvl(level8_fk_key, p_unassigned_key),
2258 level8_name = decode(level8_fk_key, NULL, p_unassigned_category_name, level8_name),
2259 level9_fk_key = nvl(level9_fk_key, p_unassigned_key),
2260 level9_name = decode(level9_fk_key, NULL, p_unassigned_category_name, level9_name);
2261
2262 END UPD_ITEM_DENORM_TMP_UNASSIGNED;
2263
2264 /* ****************************************************************************
2265 * Procedure :item_denorm_tmp_balance_load *
2266 * Description :This procedure balances the specified hierarchy in item *
2267 * denorm tmp table *
2268 * File Name :MTHITEMDB.PLS *
2269 * Visibility :Private *
2270 * Parameters : *
2271 * Modification log : *
2272 * Author Date Change *
2273 * Yong Feng 30-June--2012 Initial Creation *
2274 **************************************************************************** */
2275
2276 PROCEDURE item_denorm_tmp_balance_load(p_hierarchy_id IN NUMBER,
2277 p_max_level IN NUMBER,
2278 p_min_level IN NUMBER,
2279 p_expected_level IN NUMBER) is
2280
2281
2282 --user defined type for array of records
2283 TYPE denorm_rec_tab_type IS TABLE OF NUMBER;
2284 TYPE denorm_rec_name_tab_type IS TABLE OF VARCHAR2(240);
2285
2286 --user defined type of record of arrays
2287 TYPE denorm_rec_type IS RECORD (level9_fk_key denorm_rec_tab_type,
2288 hierarchy_id denorm_rec_tab_type,
2289 baselevel_fk_key denorm_rec_tab_type,
2290 level7_fk_key denorm_rec_tab_type,
2291 level6_fk_key denorm_rec_tab_type,
2292 level5_fk_key denorm_rec_tab_type,
2293 level4_fk_key denorm_rec_tab_type,
2294 level3_fk_key denorm_rec_tab_type,
2295 level2_fk_key denorm_rec_tab_type,
2296 level1_fk_key denorm_rec_tab_type,
2297 level9_name denorm_rec_name_tab_type,
2298 level7_name denorm_rec_name_tab_type,
2299 level6_name denorm_rec_name_tab_type,
2300 level5_name denorm_rec_name_tab_type,
2301 level4_name denorm_rec_name_tab_type,
2302 level3_name denorm_rec_name_tab_type,
2303 level2_name denorm_rec_name_tab_type,
2304 level1_name denorm_rec_name_tab_type
2305 );
2306
2307 --instantiation of the user defined type
2308 --this will be the placeholder for the records fetched from the denorm table
2309 denorm_rec denorm_rec_type;
2310
2311 --user defined cursor to hold the bulk collection of records
2312
2313 CURSOR c_item_cur (p_hierarchy_id IN NUMBER, p_expected_level IN NUMBER) IS
2314 SELECT --select for the newe levels
2315 level9_fk_key,hierarchy_id,item_fk_key,
2316 Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
2317 Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
2318 level6_fk_key_new,
2319 Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
2320 level9_fk_key) level5_fk_key_new,
2321 Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
2322 level8_fk_key,level9_fk_key) level4_fk_key_new,
2323 Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
2324 level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
2325 Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
2326 level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
2327 level2_fk_key_new,
2328 Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
2329 level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
2330 level9_fk_key) level1_fk_key_new,
2331 level9_name,
2332 Decode(diff_level,1,level8_name,level9_name) level7_name_new,
2333 Decode(diff_level,1,level7_name,2,level8_name,level9_name)
2334 level6_name_new,
2335 Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
2336 level9_name) level5_name_new,
2337 Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
2338 level8_name,level9_name) level4_name_new,
2339 Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
2340 level7_name,5,level8_name,level9_name) level3_name_new,
2341 Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
2342 level6_name,5,level7_name,6,level8_name,level9_name)
2343 level2_name_new,
2344 Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
2345 level5_name,5,level6_name,6,level7_name,7,level8_name,
2346 level9_name) level1_name_new
2347 from
2348 (--select the levels to be balanced
2349 SELECT hierarchy_id ,item_fk_key,
2350 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
2351 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
2352 level1_fk_key,
2353 level9_name,level8_name,level7_name,level6_name,
2354 level5_name,level4_name,level3_name,level2_name,
2355 level1_name,
2356 p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2357 decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2358 decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2359 decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2360 decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2361 decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2362 decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2363 decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2364 decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2365 FROM MTH_ITEM_DENORM_D_TMP
2366 WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2367 hierarchy_id = p_hierarchy_id AND
2368 p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2369 decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2370 decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2371 decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2372 decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2373 decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2374 decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2375 decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2376 decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2377 level9_fk_key IS NOT NULL
2378 );
2379
2380
2381
2382 --variable for the limit of the bulk collection
2383 v_limit NUMBER :=5000;
2384
2385
2386 BEGIN
2387
2388 --initialize the collection
2389 denorm_rec := NULL;
2390
2391 IF (p_expected_level < p_max_level OR
2392 p_expected_level = p_min_level) THEN
2393 RETURN;
2394 END IF;
2395
2396 --open the cursor
2397 OPEN c_item_cur (p_hierarchy_id , p_expected_level);
2398
2399 LOOP
2400 --fetch the rows in in cursor. Bulk collect
2401 FETCH c_item_cur BULK COLLECT INTO denorm_rec.level9_fk_key,
2402 denorm_rec.hierarchy_id,
2403 denorm_rec.baselevel_fk_key,denorm_rec.level7_fk_key,
2404 denorm_rec.level6_fk_key,
2405 denorm_rec.level5_fk_key,denorm_rec.level4_fk_key,
2406 denorm_rec.level3_fk_key,denorm_rec.level2_fk_key,
2407 denorm_rec.level1_fk_key,
2408 denorm_rec.level9_name,
2409 denorm_rec.level7_name,
2410 denorm_rec.level6_name,
2411 denorm_rec.level5_name,
2412 denorm_rec.level4_name,
2413 denorm_rec.level3_name,
2414 denorm_rec.level2_name,
2415 denorm_rec.level1_name
2416 LIMIT v_limit;
2417
2418 --terminating condition
2419 EXIT WHEN denorm_rec.baselevel_fk_key.count =0;
2420
2421 --bulk update using forall
2422 FORALL i IN
2423 denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
2424 UPDATE MTH_ITEM_DENORM_D_TMP
2425 SET
2426 level8_fk_key = denorm_rec.level9_fk_key(i),
2427 level7_fk_key = denorm_rec.level7_fk_key(i),
2428 level6_fk_key = denorm_rec.level6_fk_key(i),
2429 level5_fk_key = denorm_rec.level5_fk_key(i),
2430 level4_fk_key = denorm_rec.level4_fk_key(i),
2431 level3_fk_key = denorm_rec.level3_fk_key(i),
2432 level2_fk_key = denorm_rec.level2_fk_key(i),
2433 level1_fk_key = denorm_rec.level1_fk_key(i),
2434 level8_name = denorm_rec.level9_name(i),
2435 level7_name = denorm_rec.level7_name(i),
2436 level6_name = denorm_rec.level6_name(i),
2437 level5_name = denorm_rec.level5_name(i),
2438 level4_name = denorm_rec.level4_name(i),
2439 level3_name = denorm_rec.level3_name(i),
2440 level2_name = denorm_rec.level2_name(i),
2441 level1_name = denorm_rec.level1_name(i)
2442 WHERE
2443 item_fk_key = denorm_rec.baselevel_fk_key(i)
2444 AND hierarchy_id= denorm_rec.hierarchy_id(i);
2445 END LOOP;
2446 --close the cursor
2447 CLOSE c_item_cur;
2448
2449 --handle exceptions
2450 EXCEPTION
2451 WHEN NO_DATA_FOUND THEN
2452 RAISE_APPLICATION_ERROR (-20001,
2453 'Exception has occured in item_denorm_tmp_balance_load');
2454
2455 END item_denorm_tmp_balance_load ;
2456
2457 /* ****************************************************************************
2458 * Procedure :item_denorm_balance_load *
2459 * Description :This procedure balances the specified hierarchy in item *
2460 * denorm table *
2461 * File Name :MTHITEMDB.PLS *
2462 * Visibility :Private *
2463 * Parameters : *
2464 * Modification log : *
2465 * Author Date Change *
2466 * Yong Feng 30-June--2012 Initial Creation *
2467 **************************************************************************** */
2468
2469
2470 PROCEDURE item_denorm_balance_load(p_hierarchy_id IN NUMBER,
2471 p_expected_level IN NUMBER) is
2472
2473
2474 --user defined type for array of records
2475 TYPE denorm_rec_tab_type IS TABLE OF NUMBER;
2476 TYPE denorm_rec_name_tab_type IS TABLE OF VARCHAR2(240);
2477
2478 --user defined type of record of arrays
2479 TYPE denorm_rec_type IS RECORD (level9_fk_key denorm_rec_tab_type,
2480 hierarchy_id denorm_rec_tab_type,
2481 baselevel_fk_key denorm_rec_tab_type,
2482 level7_fk_key denorm_rec_tab_type,
2483 level6_fk_key denorm_rec_tab_type,
2484 level5_fk_key denorm_rec_tab_type,
2485 level4_fk_key denorm_rec_tab_type,
2486 level3_fk_key denorm_rec_tab_type,
2487 level2_fk_key denorm_rec_tab_type,
2488 level1_fk_key denorm_rec_tab_type,
2489 level9_name denorm_rec_name_tab_type,
2490 level7_name denorm_rec_name_tab_type,
2491 level6_name denorm_rec_name_tab_type,
2492 level5_name denorm_rec_name_tab_type,
2493 level4_name denorm_rec_name_tab_type,
2494 level3_name denorm_rec_name_tab_type,
2495 level2_name denorm_rec_name_tab_type,
2496 level1_name denorm_rec_name_tab_type
2497 );
2498
2499 --instantiation of the user defined type
2500 --this will be the placeholder for the records fetched from the denorm table
2501 denorm_rec denorm_rec_type;
2502
2503 --user defined cursor to hold the bulk collection of records
2504
2505 CURSOR c_item_cur (p_hierarchy_id IN NUMBER, p_expected_level IN NUMBER) IS
2506 SELECT --select for the newe levels
2507 level9_fk_key,hierarchy_id,item_fk_key,
2508 Decode(diff_level,1,level8_fk_key,level9_fk_key) level7_fk_key_new,
2509 Decode(diff_level,1,level7_fk_key,2,level8_fk_key,level9_fk_key)
2510 level6_fk_key_new,
2514 level8_fk_key,level9_fk_key) level4_fk_key_new,
2511 Decode(diff_level,1,level6_fk_key,2,level7_fk_key,3,level8_fk_key,
2512 level9_fk_key) level5_fk_key_new,
2513 Decode(diff_level,1,level5_fk_key,2,level6_fk_key,3,level7_fk_key,4,
2515 Decode(diff_level,1,level4_fk_key,2,level5_fk_key,3,level6_fk_key,4,
2516 level7_fk_key,5,level8_fk_key,level9_fk_key) level3_fk_key_new,
2517 Decode(diff_level,1,level3_fk_key,2,level4_fk_key,3,level5_fk_key,4,
2518 level6_fk_key,5,level7_fk_key,6,level8_fk_key,level9_fk_key)
2519 level2_fk_key_new,
2520 Decode(diff_level,1,level2_fk_key,2,level3_fk_key,3,level4_fk_key,4,
2521 level5_fk_key,5,level6_fk_key,6,level7_fk_key,7,level8_fk_key,
2522 level9_fk_key) level1_fk_key_new,
2523 level9_name,
2524 Decode(diff_level,1,level8_name,level9_name) level7_name_new,
2525 Decode(diff_level,1,level7_name,2,level8_name,level9_name)
2526 level6_name_new,
2527 Decode(diff_level,1,level6_name,2,level7_name,3,level8_name,
2528 level9_name) level5_name_new,
2529 Decode(diff_level,1,level5_name,2,level6_name,3,level7_name,4,
2530 level8_name,level9_name) level4_name_new,
2531 Decode(diff_level,1,level4_name,2,level5_name,3,level6_name,4,
2532 level7_name,5,level8_name,level9_name) level3_name_new,
2533 Decode(diff_level,1,level3_name,2,level4_name,3,level5_name,4,
2534 level6_name,5,level7_name,6,level8_name,level9_name)
2535 level2_name_new,
2536 Decode(diff_level,1,level2_name,2,level3_name,3,level4_name,4,
2537 level5_name,5,level6_name,6,level7_name,7,level8_name,
2538 level9_name) level1_name_new
2539 from
2540 (--select the levels to be balanced
2541 SELECT hierarchy_id ,item_fk_key,
2542 level9_fk_key,level8_fk_key,level7_fk_key,level6_fk_key,
2543 level5_fk_key,level4_fk_key,level3_fk_key,level2_fk_key,
2544 level1_fk_key,
2545 level9_name,level8_name,level7_name,level6_name,
2546 level5_name,level4_name,level3_name,level2_name,
2547 level1_name,
2548 p_expected_level-(decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2549 decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2550 decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2551 decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2552 decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2553 decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2554 decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2555 decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2556 decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) diff_level
2557 FROM MTH_ITEM_DENORM_D
2558 WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
2559 hierarchy_id = p_hierarchy_id AND
2560 p_expected_level> (decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2561 decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2562 decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2563 decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2564 decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2565 decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2566 decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2567 decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
2568 decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)) AND
2569 level9_fk_key IS NOT NULL
2570 );
2571
2572
2573
2574 --variable for the limit of the bulk collection
2575 v_limit NUMBER :=5000;
2576
2577
2578 BEGIN
2579
2580 --initialize the collection
2581 denorm_rec := NULL;
2582
2583 --open the cursor
2584 OPEN c_item_cur (p_hierarchy_id , p_expected_level);
2585
2586 LOOP
2587 --fetch the rows in in cursor. Bulk collect
2588 FETCH c_item_cur BULK COLLECT INTO denorm_rec.level9_fk_key,
2589 denorm_rec.hierarchy_id,
2590 denorm_rec.baselevel_fk_key,denorm_rec.level7_fk_key,
2591 denorm_rec.level6_fk_key,
2592 denorm_rec.level5_fk_key,denorm_rec.level4_fk_key,
2593 denorm_rec.level3_fk_key,denorm_rec.level2_fk_key,
2594 denorm_rec.level1_fk_key,
2595 denorm_rec.level9_name,
2596 denorm_rec.level7_name,
2597 denorm_rec.level6_name,
2598 denorm_rec.level5_name,
2599 denorm_rec.level4_name,
2600 denorm_rec.level3_name,
2601 denorm_rec.level2_name,
2602 denorm_rec.level1_name
2603 LIMIT v_limit;
2604
2605 --terminating condition
2606 EXIT WHEN denorm_rec.baselevel_fk_key.count =0;
2607
2608 --bulk update using forall
2609 FORALL i IN
2610 denorm_rec.baselevel_fk_key.first..denorm_rec.baselevel_fk_key.last
2611 UPDATE MTH_ITEM_DENORM_D
2612 SET
2613 level8_fk_key = denorm_rec.level9_fk_key(i),
2614 level7_fk_key = denorm_rec.level7_fk_key(i),
2615 level6_fk_key = denorm_rec.level6_fk_key(i),
2616 level5_fk_key = denorm_rec.level5_fk_key(i),
2617 level4_fk_key = denorm_rec.level4_fk_key(i),
2618 level3_fk_key = denorm_rec.level3_fk_key(i),
2619 level2_fk_key = denorm_rec.level2_fk_key(i),
2620 level1_fk_key = denorm_rec.level1_fk_key(i),
2621 level8_name = denorm_rec.level9_name(i),
2622 level7_name = denorm_rec.level7_name(i),
2626 level3_name = denorm_rec.level3_name(i),
2623 level6_name = denorm_rec.level6_name(i),
2624 level5_name = denorm_rec.level5_name(i),
2625 level4_name = denorm_rec.level4_name(i),
2627 level2_name = denorm_rec.level2_name(i),
2628 level1_name = denorm_rec.level1_name(i)
2629 WHERE
2630 item_fk_key = denorm_rec.baselevel_fk_key(i)
2631 AND hierarchy_id= denorm_rec.hierarchy_id(i);
2632 END LOOP;
2633 --close the cursor
2634 CLOSE c_item_cur;
2635
2636 --handle exceptions
2637 EXCEPTION
2638 WHEN NO_DATA_FOUND THEN
2639 RAISE_APPLICATION_ERROR (-20001,
2640 'Exception has occured in item_denorm_balance_load');
2641
2642 END item_denorm_balance_load ;
2643
2644 /* ****************************************************************************
2645 * Procedure :INSERT_UNASS_ITEM_DENORM_TMP *
2646 * Description :This procedure inserts unassigned entry into *
2647 * MTH_ITEM_DENORM_D_TMP when the hierarchy does not exist *
2648 * in item denorm table *
2649 * File Name :MTHITEMDB.PLS *
2650 * Visibility :Private *
2651 * Parameters : *
2652 * Modification log : *
2653 * Author Date Change *
2654 * Yong Feng 03-July--2012 Initial Creation *
2655 **************************************************************************** */
2656
2657 PROCEDURE INSERT_UNASS_ITEM_DENORM_TMP(p_hierarchy_id IN NUMBER,
2658 p_unassigned_key IN NUMBER,
2659 p_unassigned_item_name IN VARCHAR2,
2660 p_unassigned_category_name IN VARCHAR2)
2661 IS
2662 BEGIN
2663
2664 INSERT INTO MTH_ITEM_DENORM_D_TMP
2665 ( HIERARCHY_ID, ITEM_FK_KEY, LEVEL9_FK_KEY, LEVEL8_FK_KEY,
2666 LEVEL7_FK_KEY, LEVEL6_FK_KEY, LEVEL5_FK_KEY, LEVEL4_FK_KEY,
2667 LEVEL3_FK_KEY, LEVEL2_FK_KEY, LEVEL1_FK_KEY,
2668 LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
2669 LEVEL6_NAME, LEVEL5_NAME, LEVEL4_NAME, LEVEL3_NAME, LEVEL2_NAME,
2670 LEVEL1_NAME )
2671 VALUES ( p_hierarchy_id,
2672 MTH_UTIL_PKG.MTH_UA_GET_VAL
2673 ,p_unassigned_key
2674 ,p_unassigned_key
2675 ,p_unassigned_key
2676 ,p_unassigned_key
2677 ,p_unassigned_key
2678 ,p_unassigned_key
2679 ,p_unassigned_key
2680 ,p_unassigned_key
2681 ,p_unassigned_key
2682 ,10
2683 ,p_unassigned_item_name
2684 ,p_unassigned_category_name
2685 ,p_unassigned_category_name
2686 ,p_unassigned_category_name
2687 ,p_unassigned_category_name
2688 ,p_unassigned_category_name
2689 ,p_unassigned_category_name
2690 ,p_unassigned_category_name
2691 ,p_unassigned_category_name
2692 ,p_unassigned_category_name);
2693 END INSERT_UNASS_ITEM_DENORM_TMP;
2694
2695 /* ****************************************************************************
2696 * Procedure :BUILD_LEVEL_1_9_item_denorm *
2697 * Description :This procedure builds level 1 to level 9 for the specified *
2698 * hierarchy id in the item denorm table *
2699 * File Name :MTHITEMDB.PLS *
2700 * Visibility :Private *
2701 * Parameters : *
2702 * Modification log : *
2703 * Author Date Change *
2704 * Yong Feng 30-June--2012 Initial Creation *
2705 **************************************************************************** */
2706
2707
2708 PROCEDURE BUILD_LEVEL_1_9_item_denorm (p_hierarchy_id IN NUMBER)
2709 IS
2710 BEGIN
2711
2712 Insert into mth_item_denorm_d
2713 (
2714 HIERARCHY_ID,
2715 ITEM_FK_KEY,
2716 LEVEL1_FK_KEY,
2717 LEVEL2_FK_KEY,
2718 LEVEL3_FK_KEY,
2719 LEVEL4_FK_KEY,
2720 LEVEL5_FK_KEY,
2721 LEVEL6_FK_KEY,
2722 LEVEL7_FK_KEY,
2723 LEVEL8_FK_KEY,
2724 LEVEL9_FK_KEY,
2725 LEVEL_NUM,
2726 ITEM_NAME,
2727 LEVEL1_NAME,
2728 LEVEL2_NAME,
2729 LEVEL3_NAME,
2730 LEVEL4_NAME,
2731 LEVEL5_NAME,
2732 LEVEL6_NAME,
2733 LEVEL7_NAME,
2734 LEVEL8_NAME,
2735 LEVEL9_NAME
2736 )
2737 select distinct
2738 HIERARCHY_ID,
2739 NULL,
2740 LEVEL1_FK_KEY,
2741 LEVEL2_FK_KEY,
2742 LEVEL3_FK_KEY,
2743 LEVEL4_FK_KEY,
2744 LEVEL5_FK_KEY,
2745 LEVEL6_FK_KEY,
2746 LEVEL7_FK_KEY,
2747 LEVEL8_FK_KEY,
2748 LEVEL9_FK_KEY,
2749 9,
2750 NULL,
2751 LEVEL1_NAME,
2752 LEVEL2_NAME,
2753 LEVEL3_NAME,
2754 LEVEL4_NAME,
2755 LEVEL5_NAME,
2756 LEVEL6_NAME,
2757 LEVEL7_NAME,
2758 LEVEL8_NAME,
2759 LEVEL9_NAME
2760 From mth_item_denorm_d
2761 Where LEVEL9_FK_KEY is not null and level_NUM = 10 AND
2762 hierarchy_id = p_hierarchy_id;
2763
2764 -- insert level 8 entries
2765 Insert into mth_item_denorm_d
2766 (
2767 HIERARCHY_ID,
2768 ITEM_FK_KEY,
2772 LEVEL4_FK_KEY,
2769 LEVEL1_FK_KEY,
2770 LEVEL2_FK_KEY,
2771 LEVEL3_FK_KEY,
2773 LEVEL5_FK_KEY,
2774 LEVEL6_FK_KEY,
2775 LEVEL7_FK_KEY,
2776 LEVEL8_FK_KEY,
2777 LEVEL9_FK_KEY,
2778 LEVEL_NUM,
2779 ITEM_NAME,
2780 LEVEL1_NAME,
2781 LEVEL2_NAME,
2782 LEVEL3_NAME,
2783 LEVEL4_NAME,
2784 LEVEL5_NAME,
2785 LEVEL6_NAME,
2786 LEVEL7_NAME,
2787 LEVEL8_NAME,
2788 LEVEL9_NAME
2789 )
2790 select distinct
2791 HIERARCHY_ID,
2792 NULL,
2793 LEVEL1_FK_KEY,
2794 LEVEL2_FK_KEY,
2795 LEVEL3_FK_KEY,
2796 LEVEL4_FK_KEY,
2797 LEVEL5_FK_KEY,
2798 LEVEL6_FK_KEY,
2799 LEVEL7_FK_KEY,
2800 LEVEL8_FK_KEY,
2801 NULL,
2802 8,
2803 NULL,
2804 LEVEL1_NAME,
2805 LEVEL2_NAME,
2806 LEVEL3_NAME,
2807 LEVEL4_NAME,
2808 LEVEL5_NAME,
2809 LEVEL6_NAME,
2810 LEVEL7_NAME,
2811 LEVEL8_NAME,
2812 NULL
2813 From mth_item_denorm_d
2814 Where LEVEL8_FK_KEY is not null and level_NUM = 9 AND
2815 hierarchy_id = p_hierarchy_id;
2816
2817
2818 -- insert level 7 entries
2819 Insert into mth_item_denorm_d
2820 (
2821 HIERARCHY_ID,
2822 ITEM_FK_KEY,
2823 LEVEL1_FK_KEY,
2824 LEVEL2_FK_KEY,
2825 LEVEL3_FK_KEY,
2826 LEVEL4_FK_KEY,
2827 LEVEL5_FK_KEY,
2828 LEVEL6_FK_KEY,
2829 LEVEL7_FK_KEY,
2830 LEVEL8_FK_KEY,
2831 LEVEL9_FK_KEY,
2832 LEVEL_NUM,
2833 ITEM_NAME,
2834 LEVEL1_NAME,
2835 LEVEL2_NAME,
2836 LEVEL3_NAME,
2837 LEVEL4_NAME,
2838 LEVEL5_NAME,
2839 LEVEL6_NAME,
2840 LEVEL7_NAME,
2841 LEVEL8_NAME,
2842 LEVEL9_NAME
2843 )
2844 select distinct
2845 HIERARCHY_ID,
2846 NULL,
2847 LEVEL1_FK_KEY,
2848 LEVEL2_FK_KEY,
2849 LEVEL3_FK_KEY,
2850 LEVEL4_FK_KEY,
2851 LEVEL5_FK_KEY,
2852 LEVEL6_FK_KEY,
2853 LEVEL7_FK_KEY,
2854 NULL,
2855 NULL,
2856 7,
2857 NULL,
2858 LEVEL1_NAME,
2859 LEVEL2_NAME,
2860 LEVEL3_NAME,
2861 LEVEL4_NAME,
2862 LEVEL5_NAME,
2863 LEVEL6_NAME,
2864 LEVEL7_NAME,
2865 NULL,
2866 NULL
2867 From mth_item_denorm_d
2868 Where LEVEL7_FK_KEY is not null and level_NUM = 8 AND
2869 hierarchy_id = p_hierarchy_id;
2870
2871
2872 -- insert level 6 entries
2873 Insert into mth_item_denorm_d
2874 (
2875 HIERARCHY_ID,
2876 ITEM_FK_KEY,
2877 LEVEL1_FK_KEY,
2878 LEVEL2_FK_KEY,
2879 LEVEL3_FK_KEY,
2880 LEVEL4_FK_KEY,
2881 LEVEL5_FK_KEY,
2882 LEVEL6_FK_KEY,
2883 LEVEL7_FK_KEY,
2884 LEVEL8_FK_KEY,
2885 LEVEL9_FK_KEY,
2886 LEVEL_NUM,
2887 ITEM_NAME,
2888 LEVEL1_NAME,
2889 LEVEL2_NAME,
2890 LEVEL3_NAME,
2891 LEVEL4_NAME,
2892 LEVEL5_NAME,
2893 LEVEL6_NAME,
2894 LEVEL7_NAME,
2895 LEVEL8_NAME,
2896 LEVEL9_NAME
2897 )
2898 select distinct
2899 HIERARCHY_ID,
2900 NULL,
2901 LEVEL1_FK_KEY,
2902 LEVEL2_FK_KEY,
2903 LEVEL3_FK_KEY,
2904 LEVEL4_FK_KEY,
2905 LEVEL5_FK_KEY,
2906 LEVEL6_FK_KEY,
2907 NULL,
2908 NULL,
2909 NULL,
2910 6,
2911 NULL,
2912 LEVEL1_NAME,
2913 LEVEL2_NAME,
2914 LEVEL3_NAME,
2915 LEVEL4_NAME,
2916 LEVEL5_NAME,
2917 LEVEL6_NAME,
2918 NULL,
2919 NULL,
2920 NULL
2921 From mth_item_denorm_d
2922 Where LEVEL6_FK_KEY is not null and level_NUM = 7 AND
2923 hierarchy_id = p_hierarchy_id;
2924
2925
2926 -- insert level 5 entries
2927 Insert into mth_item_denorm_d
2928 (
2929 HIERARCHY_ID,
2930 ITEM_FK_KEY,
2931 LEVEL1_FK_KEY,
2932 LEVEL2_FK_KEY,
2933 LEVEL3_FK_KEY,
2934 LEVEL4_FK_KEY,
2935 LEVEL5_FK_KEY,
2936 LEVEL6_FK_KEY,
2937 LEVEL7_FK_KEY,
2938 LEVEL8_FK_KEY,
2939 LEVEL9_FK_KEY,
2940 LEVEL_NUM,
2941 ITEM_NAME,
2942 LEVEL1_NAME,
2943 LEVEL2_NAME,
2944 LEVEL3_NAME,
2945 LEVEL4_NAME,
2946 LEVEL5_NAME,
2947 LEVEL6_NAME,
2948 LEVEL7_NAME,
2949 LEVEL8_NAME,
2950 LEVEL9_NAME
2951 )
2952 select distinct
2953 HIERARCHY_ID,
2954 NULL,
2955 LEVEL1_FK_KEY,
2956 LEVEL2_FK_KEY,
2957 LEVEL3_FK_KEY,
2958 LEVEL4_FK_KEY,
2959 LEVEL5_FK_KEY,
2960 NULL,
2961 NULL,
2962 NULL,
2963 NULL,
2964 5,
2968 LEVEL3_NAME,
2965 NULL,
2966 LEVEL1_NAME,
2967 LEVEL2_NAME,
2969 LEVEL4_NAME,
2970 LEVEL5_NAME,
2971 NULL,
2972 NULL,
2973 NULL,
2974 NULL
2975 From mth_item_denorm_d
2976 Where LEVEL5_FK_KEY is not null and level_NUM = 6 AND
2977 hierarchy_id = p_hierarchy_id;
2978
2979
2980 -- insert level 4 entries
2981 Insert into mth_item_denorm_d
2982 (
2983 HIERARCHY_ID,
2984 ITEM_FK_KEY,
2985 LEVEL1_FK_KEY,
2986 LEVEL2_FK_KEY,
2987 LEVEL3_FK_KEY,
2988 LEVEL4_FK_KEY,
2989 LEVEL5_FK_KEY,
2990 LEVEL6_FK_KEY,
2991 LEVEL7_FK_KEY,
2992 LEVEL8_FK_KEY,
2993 LEVEL9_FK_KEY,
2994 LEVEL_NUM,
2995 ITEM_NAME,
2996 LEVEL1_NAME,
2997 LEVEL2_NAME,
2998 LEVEL3_NAME,
2999 LEVEL4_NAME,
3000 LEVEL5_NAME,
3001 LEVEL6_NAME,
3002 LEVEL7_NAME,
3003 LEVEL8_NAME,
3004 LEVEL9_NAME
3005 )
3006 select distinct
3007 HIERARCHY_ID,
3008 NULL,
3009 LEVEL1_FK_KEY,
3010 LEVEL2_FK_KEY,
3011 LEVEL3_FK_KEY,
3012 LEVEL4_FK_KEY,
3013 NULL,
3014 NULL,
3015 NULL,
3016 NULL,
3017 NULL,
3018 4,
3019 NULL,
3020 LEVEL1_NAME,
3021 LEVEL2_NAME,
3022 LEVEL3_NAME,
3023 LEVEL4_NAME,
3024 NULL,
3025 NULL,
3026 NULL,
3027 NULL,
3028 NULL
3029 From mth_item_denorm_d
3030 Where LEVEL4_FK_KEY is not null and level_NUM = 5 AND
3031 hierarchy_id = p_hierarchy_id;
3032
3033
3034
3035 -- insert level 3 entries
3036 Insert into mth_item_denorm_d
3037 (
3038 HIERARCHY_ID,
3039 ITEM_FK_KEY,
3040 LEVEL1_FK_KEY,
3041 LEVEL2_FK_KEY,
3042 LEVEL3_FK_KEY,
3043 LEVEL4_FK_KEY,
3044 LEVEL5_FK_KEY,
3045 LEVEL6_FK_KEY,
3046 LEVEL7_FK_KEY,
3047 LEVEL8_FK_KEY,
3048 LEVEL9_FK_KEY,
3049 LEVEL_NUM,
3050 ITEM_NAME,
3051 LEVEL1_NAME,
3052 LEVEL2_NAME,
3053 LEVEL3_NAME,
3054 LEVEL4_NAME,
3055 LEVEL5_NAME,
3056 LEVEL6_NAME,
3057 LEVEL7_NAME,
3058 LEVEL8_NAME,
3059 LEVEL9_NAME
3060 )
3061 select distinct
3062 HIERARCHY_ID,
3063 NULL,
3064 LEVEL1_FK_KEY,
3065 LEVEL2_FK_KEY,
3066 LEVEL3_FK_KEY,
3067 NULL,
3068 NULL,
3069 NULL,
3070 NULL,
3071 NULL,
3072 NULL,
3073 3,
3074 NULL,
3075 LEVEL1_NAME,
3076 LEVEL2_NAME,
3077 LEVEL3_NAME,
3078 NULL,
3079 NULL,
3080 NULL,
3081 NULL,
3082 NULL,
3083 NULL
3084 From mth_item_denorm_d
3085 Where LEVEL3_FK_KEY is not null and level_NUM = 4 AND
3086 hierarchy_id = p_hierarchy_id;
3087
3088
3089
3090 -- insert level 2 entries
3091 Insert into mth_item_denorm_d
3092 (
3093 HIERARCHY_ID,
3094 ITEM_FK_KEY,
3095 LEVEL1_FK_KEY,
3096 LEVEL2_FK_KEY,
3097 LEVEL3_FK_KEY,
3098 LEVEL4_FK_KEY,
3099 LEVEL5_FK_KEY,
3100 LEVEL6_FK_KEY,
3101 LEVEL7_FK_KEY,
3102 LEVEL8_FK_KEY,
3103 LEVEL9_FK_KEY,
3104 LEVEL_NUM,
3105 ITEM_NAME,
3106 LEVEL1_NAME,
3107 LEVEL2_NAME,
3108 LEVEL3_NAME,
3109 LEVEL4_NAME,
3110 LEVEL5_NAME,
3111 LEVEL6_NAME,
3112 LEVEL7_NAME,
3113 LEVEL8_NAME,
3114 LEVEL9_NAME
3115 )
3116 select distinct
3117 HIERARCHY_ID,
3118 NULL,
3119 LEVEL1_FK_KEY,
3120 LEVEL2_FK_KEY,
3121 NULL,
3122 NULL,
3123 NULL,
3124 NULL,
3125 NULL,
3126 NULL,
3127 NULL,
3128 2,
3129 NULL,
3130 LEVEL1_NAME,
3131 LEVEL2_NAME,
3132 NULL,
3133 NULL,
3134 NULL,
3135 NULL,
3136 NULL,
3137 NULL,
3138 NULL
3139 From mth_item_denorm_d
3140 Where LEVEL2_FK_KEY is not null and level_NUM = 3 AND
3141 hierarchy_id = p_hierarchy_id;
3142
3143
3144
3145
3146 -- insert level 1 entries
3147 Insert into mth_item_denorm_d
3148 (
3149 HIERARCHY_ID,
3150 ITEM_FK_KEY,
3151 LEVEL1_FK_KEY,
3152 LEVEL2_FK_KEY,
3153 LEVEL3_FK_KEY,
3154 LEVEL4_FK_KEY,
3155 LEVEL5_FK_KEY,
3156 LEVEL6_FK_KEY,
3157 LEVEL7_FK_KEY,
3158 LEVEL8_FK_KEY,
3159 LEVEL9_FK_KEY,
3160 LEVEL_NUM,
3161 ITEM_NAME,
3162 LEVEL1_NAME,
3163 LEVEL2_NAME,
3164 LEVEL3_NAME,
3165 LEVEL4_NAME,
3166 LEVEL5_NAME,
3167 LEVEL6_NAME,
3171 )
3168 LEVEL7_NAME,
3169 LEVEL8_NAME,
3170 LEVEL9_NAME
3172 select distinct
3173 HIERARCHY_ID,
3174 null,
3175 LEVEL1_FK_KEY,
3176 NULL,
3177 NULL,
3178 NULL,
3179 NULL,
3180 NULL,
3181 NULL,
3182 NULL,
3183 NULL,
3184 1,
3185 null,
3186 LEVEL1_NAME,
3187 NULL,
3188 NULL,
3189 NULL,
3190 NULL,
3191 NULL,
3192 NULL,
3193 NULL,
3194 NULL
3195 From mth_item_denorm_d
3196 Where LEVEL1_FK_KEY is not null and level_NUM = 2 AND
3197 hierarchy_id = p_hierarchy_id;
3198
3199
3200 EXCEPTION
3201 WHEN OTHERS THEN
3202 ROLLBACK;
3203 RAISE;
3204 --End of the procedure ITEM_DIM_LOAD_DENORM
3205 END BUILD_LEVEL_1_9_item_denorm;
3206
3207 /* ****************************************************************************
3208 * Procedure :BUILD_LVL_1_9_item_denorm_tmp *
3209 * Description :This procedure builds level 1 to level 9 for the specified *
3210 * hierarchy id in the item denorm TMP table *
3211 * File Name :MTHITEMDB.PLS *
3212 * Visibility :Private *
3213 * Parameters : *
3214 * Modification log : *
3215 * Author Date Change *
3216 * Yong Feng 30-June--2012 Initial Creation *
3217 **************************************************************************** */
3218
3219
3220 PROCEDURE BUILD_LVL_1_9_item_denorm_tmp (p_hierarchy_id IN NUMBER)
3221 IS
3222 BEGIN
3223
3224 Insert into MTH_ITEM_DENORM_D_TMP
3225 (
3226 HIERARCHY_ID,
3227 ITEM_FK_KEY,
3228 LEVEL1_FK_KEY,
3229 LEVEL2_FK_KEY,
3230 LEVEL3_FK_KEY,
3231 LEVEL4_FK_KEY,
3232 LEVEL5_FK_KEY,
3233 LEVEL6_FK_KEY,
3234 LEVEL7_FK_KEY,
3235 LEVEL8_FK_KEY,
3236 LEVEL9_FK_KEY,
3237 LEVEL_NUM,
3238 ITEM_NAME,
3239 LEVEL1_NAME,
3240 LEVEL2_NAME,
3241 LEVEL3_NAME,
3242 LEVEL4_NAME,
3243 LEVEL5_NAME,
3244 LEVEL6_NAME,
3245 LEVEL7_NAME,
3246 LEVEL8_NAME,
3247 LEVEL9_NAME
3248 )
3249 select distinct
3250 HIERARCHY_ID,
3251 NULL,
3252 LEVEL1_FK_KEY,
3253 LEVEL2_FK_KEY,
3254 LEVEL3_FK_KEY,
3255 LEVEL4_FK_KEY,
3256 LEVEL5_FK_KEY,
3257 LEVEL6_FK_KEY,
3258 LEVEL7_FK_KEY,
3259 LEVEL8_FK_KEY,
3260 LEVEL9_FK_KEY,
3261 9,
3262 NULL,
3263 LEVEL1_NAME,
3264 LEVEL2_NAME,
3265 LEVEL3_NAME,
3266 LEVEL4_NAME,
3267 LEVEL5_NAME,
3268 LEVEL6_NAME,
3269 LEVEL7_NAME,
3270 LEVEL8_NAME,
3271 LEVEL9_NAME
3272 From MTH_ITEM_DENORM_D_TMP
3273 Where LEVEL9_FK_KEY is not null and level_NUM = 10 AND
3274 hierarchy_id = p_hierarchy_id;
3275
3276
3277 -- insert level 8 entries
3278 Insert into MTH_ITEM_DENORM_D_TMP
3279 (
3280 HIERARCHY_ID,
3281 ITEM_FK_KEY,
3282 LEVEL1_FK_KEY,
3283 LEVEL2_FK_KEY,
3284 LEVEL3_FK_KEY,
3285 LEVEL4_FK_KEY,
3286 LEVEL5_FK_KEY,
3287 LEVEL6_FK_KEY,
3288 LEVEL7_FK_KEY,
3289 LEVEL8_FK_KEY,
3290 LEVEL9_FK_KEY,
3291 LEVEL_NUM,
3292 ITEM_NAME,
3293 LEVEL1_NAME,
3294 LEVEL2_NAME,
3295 LEVEL3_NAME,
3296 LEVEL4_NAME,
3297 LEVEL5_NAME,
3298 LEVEL6_NAME,
3299 LEVEL7_NAME,
3300 LEVEL8_NAME,
3301 LEVEL9_NAME
3302 )
3303 select distinct
3304 HIERARCHY_ID,
3305 NULL,
3306 LEVEL1_FK_KEY,
3307 LEVEL2_FK_KEY,
3308 LEVEL3_FK_KEY,
3309 LEVEL4_FK_KEY,
3310 LEVEL5_FK_KEY,
3311 LEVEL6_FK_KEY,
3312 LEVEL7_FK_KEY,
3313 LEVEL8_FK_KEY,
3314 NULL,
3315 8,
3316 NULL,
3317 LEVEL1_NAME,
3318 LEVEL2_NAME,
3319 LEVEL3_NAME,
3320 LEVEL4_NAME,
3321 LEVEL5_NAME,
3322 LEVEL6_NAME,
3323 LEVEL7_NAME,
3324 LEVEL8_NAME,
3325 NULL
3326 From MTH_ITEM_DENORM_D_TMP
3327 Where LEVEL8_FK_KEY is not null and level_NUM = 9 AND
3328 hierarchy_id = p_hierarchy_id;
3329
3330
3331 -- insert level 7 entries
3332 Insert into MTH_ITEM_DENORM_D_TMP
3333 (
3334 HIERARCHY_ID,
3335 ITEM_FK_KEY,
3336 LEVEL1_FK_KEY,
3337 LEVEL2_FK_KEY,
3338 LEVEL3_FK_KEY,
3339 LEVEL4_FK_KEY,
3340 LEVEL5_FK_KEY,
3341 LEVEL6_FK_KEY,
3342 LEVEL7_FK_KEY,
3343 LEVEL8_FK_KEY,
3344 LEVEL9_FK_KEY,
3345 LEVEL_NUM,
3346 ITEM_NAME,
3347 LEVEL1_NAME,
3348 LEVEL2_NAME,
3349 LEVEL3_NAME,
3350 LEVEL4_NAME,
3351 LEVEL5_NAME,
3352 LEVEL6_NAME,
3353 LEVEL7_NAME,
3357 select distinct
3354 LEVEL8_NAME,
3355 LEVEL9_NAME
3356 )
3358 HIERARCHY_ID,
3359 NULL,
3360 LEVEL1_FK_KEY,
3361 LEVEL2_FK_KEY,
3362 LEVEL3_FK_KEY,
3363 LEVEL4_FK_KEY,
3364 LEVEL5_FK_KEY,
3365 LEVEL6_FK_KEY,
3366 LEVEL7_FK_KEY,
3367 NULL,
3368 NULL,
3369 7,
3370 NULL,
3371 LEVEL1_NAME,
3372 LEVEL2_NAME,
3373 LEVEL3_NAME,
3374 LEVEL4_NAME,
3375 LEVEL5_NAME,
3376 LEVEL6_NAME,
3377 LEVEL7_NAME,
3378 NULL,
3379 NULL
3380 From MTH_ITEM_DENORM_D_TMP
3381 Where LEVEL7_FK_KEY is not null and level_NUM = 8 AND
3382 hierarchy_id = p_hierarchy_id;
3383
3384
3385 -- insert level 6 entries
3386 Insert into MTH_ITEM_DENORM_D_TMP
3387 (
3388 HIERARCHY_ID,
3389 ITEM_FK_KEY,
3390 LEVEL1_FK_KEY,
3391 LEVEL2_FK_KEY,
3392 LEVEL3_FK_KEY,
3393 LEVEL4_FK_KEY,
3394 LEVEL5_FK_KEY,
3395 LEVEL6_FK_KEY,
3396 LEVEL7_FK_KEY,
3397 LEVEL8_FK_KEY,
3398 LEVEL9_FK_KEY,
3399 LEVEL_NUM,
3400 ITEM_NAME,
3401 LEVEL1_NAME,
3402 LEVEL2_NAME,
3403 LEVEL3_NAME,
3404 LEVEL4_NAME,
3405 LEVEL5_NAME,
3406 LEVEL6_NAME,
3407 LEVEL7_NAME,
3408 LEVEL8_NAME,
3409 LEVEL9_NAME
3410 )
3411 select distinct
3412 HIERARCHY_ID,
3413 NULL,
3414 LEVEL1_FK_KEY,
3415 LEVEL2_FK_KEY,
3416 LEVEL3_FK_KEY,
3417 LEVEL4_FK_KEY,
3418 LEVEL5_FK_KEY,
3419 LEVEL6_FK_KEY,
3420 NULL,
3421 NULL,
3422 NULL,
3423 6,
3424 NULL,
3425 LEVEL1_NAME,
3426 LEVEL2_NAME,
3427 LEVEL3_NAME,
3428 LEVEL4_NAME,
3429 LEVEL5_NAME,
3430 LEVEL6_NAME,
3431 NULL,
3432 NULL,
3433 NULL
3434 From MTH_ITEM_DENORM_D_TMP
3435 Where LEVEL6_FK_KEY is not null and level_NUM = 7 AND
3436 hierarchy_id = p_hierarchy_id;
3437
3438
3439 -- insert level 5 entries
3440 Insert into MTH_ITEM_DENORM_D_TMP
3441 (
3442 HIERARCHY_ID,
3443 ITEM_FK_KEY,
3444 LEVEL1_FK_KEY,
3445 LEVEL2_FK_KEY,
3446 LEVEL3_FK_KEY,
3447 LEVEL4_FK_KEY,
3448 LEVEL5_FK_KEY,
3449 LEVEL6_FK_KEY,
3450 LEVEL7_FK_KEY,
3451 LEVEL8_FK_KEY,
3452 LEVEL9_FK_KEY,
3453 LEVEL_NUM,
3454 ITEM_NAME,
3455 LEVEL1_NAME,
3456 LEVEL2_NAME,
3457 LEVEL3_NAME,
3458 LEVEL4_NAME,
3459 LEVEL5_NAME,
3460 LEVEL6_NAME,
3461 LEVEL7_NAME,
3462 LEVEL8_NAME,
3463 LEVEL9_NAME
3464 )
3465 select distinct
3466 HIERARCHY_ID,
3467 NULL,
3468 LEVEL1_FK_KEY,
3469 LEVEL2_FK_KEY,
3470 LEVEL3_FK_KEY,
3471 LEVEL4_FK_KEY,
3472 LEVEL5_FK_KEY,
3473 NULL,
3474 NULL,
3475 NULL,
3476 NULL,
3477 5,
3478 NULL,
3479 LEVEL1_NAME,
3480 LEVEL2_NAME,
3481 LEVEL3_NAME,
3482 LEVEL4_NAME,
3483 LEVEL5_NAME,
3484 NULL,
3485 NULL,
3486 NULL,
3487 NULL
3488 From MTH_ITEM_DENORM_D_TMP
3489 Where LEVEL5_FK_KEY is not null and level_NUM = 6 AND
3490 hierarchy_id = p_hierarchy_id;
3491
3492
3493 -- insert level 4 entries
3494 Insert into MTH_ITEM_DENORM_D_TMP
3495 (
3496 HIERARCHY_ID,
3497 ITEM_FK_KEY,
3498 LEVEL1_FK_KEY,
3499 LEVEL2_FK_KEY,
3500 LEVEL3_FK_KEY,
3501 LEVEL4_FK_KEY,
3502 LEVEL5_FK_KEY,
3503 LEVEL6_FK_KEY,
3504 LEVEL7_FK_KEY,
3505 LEVEL8_FK_KEY,
3506 LEVEL9_FK_KEY,
3507 LEVEL_NUM,
3508 ITEM_NAME,
3509 LEVEL1_NAME,
3510 LEVEL2_NAME,
3511 LEVEL3_NAME,
3512 LEVEL4_NAME,
3513 LEVEL5_NAME,
3514 LEVEL6_NAME,
3515 LEVEL7_NAME,
3516 LEVEL8_NAME,
3517 LEVEL9_NAME
3518 )
3519 select distinct
3520 HIERARCHY_ID,
3521 NULL,
3522 LEVEL1_FK_KEY,
3523 LEVEL2_FK_KEY,
3524 LEVEL3_FK_KEY,
3525 LEVEL4_FK_KEY,
3526 NULL,
3527 NULL,
3528 NULL,
3529 NULL,
3530 NULL,
3531 4,
3532 NULL,
3533 LEVEL1_NAME,
3534 LEVEL2_NAME,
3535 LEVEL3_NAME,
3536 LEVEL4_NAME,
3537 NULL,
3538 NULL,
3539 NULL,
3540 NULL,
3541 NULL
3542 From MTH_ITEM_DENORM_D_TMP
3543 Where LEVEL4_FK_KEY is not null and level_NUM = 5 AND
3544 hierarchy_id = p_hierarchy_id;
3545
3546
3547
3548 -- insert level 3 entries
3549 Insert into MTH_ITEM_DENORM_D_TMP
3550 (
3551 HIERARCHY_ID,
3552 ITEM_FK_KEY,
3556 LEVEL4_FK_KEY,
3553 LEVEL1_FK_KEY,
3554 LEVEL2_FK_KEY,
3555 LEVEL3_FK_KEY,
3557 LEVEL5_FK_KEY,
3558 LEVEL6_FK_KEY,
3559 LEVEL7_FK_KEY,
3560 LEVEL8_FK_KEY,
3561 LEVEL9_FK_KEY,
3562 LEVEL_NUM,
3563 ITEM_NAME,
3564 LEVEL1_NAME,
3565 LEVEL2_NAME,
3566 LEVEL3_NAME,
3567 LEVEL4_NAME,
3568 LEVEL5_NAME,
3569 LEVEL6_NAME,
3570 LEVEL7_NAME,
3571 LEVEL8_NAME,
3572 LEVEL9_NAME
3573 )
3574 select distinct
3575 HIERARCHY_ID,
3576 NULL,
3577 LEVEL1_FK_KEY,
3578 LEVEL2_FK_KEY,
3579 LEVEL3_FK_KEY,
3580 NULL,
3581 NULL,
3582 NULL,
3583 NULL,
3584 NULL,
3585 NULL,
3586 3,
3587 NULL,
3588 LEVEL1_NAME,
3589 LEVEL2_NAME,
3590 LEVEL3_NAME,
3591 NULL,
3592 NULL,
3593 NULL,
3594 NULL,
3595 NULL,
3596 NULL
3597 From MTH_ITEM_DENORM_D_TMP
3598 Where LEVEL3_FK_KEY is not null and level_NUM = 4 AND
3599 hierarchy_id = p_hierarchy_id;
3600
3601
3602
3603 -- insert level 2 entries
3604 Insert into MTH_ITEM_DENORM_D_TMP
3605 (
3606 HIERARCHY_ID,
3607 ITEM_FK_KEY,
3608 LEVEL1_FK_KEY,
3609 LEVEL2_FK_KEY,
3610 LEVEL3_FK_KEY,
3611 LEVEL4_FK_KEY,
3612 LEVEL5_FK_KEY,
3613 LEVEL6_FK_KEY,
3614 LEVEL7_FK_KEY,
3615 LEVEL8_FK_KEY,
3616 LEVEL9_FK_KEY,
3617 LEVEL_NUM,
3618 ITEM_NAME,
3619 LEVEL1_NAME,
3620 LEVEL2_NAME,
3621 LEVEL3_NAME,
3622 LEVEL4_NAME,
3623 LEVEL5_NAME,
3624 LEVEL6_NAME,
3625 LEVEL7_NAME,
3626 LEVEL8_NAME,
3627 LEVEL9_NAME
3628 )
3629 select distinct
3630 HIERARCHY_ID,
3631 NULL,
3632 LEVEL1_FK_KEY,
3633 LEVEL2_FK_KEY,
3634 NULL,
3635 NULL,
3636 NULL,
3637 NULL,
3638 NULL,
3639 NULL,
3640 NULL,
3641 2,
3642 NULL,
3643 LEVEL1_NAME,
3644 LEVEL2_NAME,
3645 NULL,
3646 NULL,
3647 NULL,
3648 NULL,
3649 NULL,
3650 NULL,
3651 NULL
3652 From MTH_ITEM_DENORM_D_TMP
3653 Where LEVEL2_FK_KEY is not null and level_NUM = 3 AND
3654 hierarchy_id = p_hierarchy_id;
3655
3656
3657
3658
3659 -- insert level 1 entries
3660 Insert into MTH_ITEM_DENORM_D_TMP
3661 (
3662 HIERARCHY_ID,
3663 ITEM_FK_KEY,
3664 LEVEL1_FK_KEY,
3665 LEVEL2_FK_KEY,
3666 LEVEL3_FK_KEY,
3667 LEVEL4_FK_KEY,
3668 LEVEL5_FK_KEY,
3669 LEVEL6_FK_KEY,
3670 LEVEL7_FK_KEY,
3671 LEVEL8_FK_KEY,
3672 LEVEL9_FK_KEY,
3673 LEVEL_NUM,
3674 ITEM_NAME,
3675 LEVEL1_NAME,
3676 LEVEL2_NAME,
3677 LEVEL3_NAME,
3678 LEVEL4_NAME,
3679 LEVEL5_NAME,
3680 LEVEL6_NAME,
3681 LEVEL7_NAME,
3682 LEVEL8_NAME,
3683 LEVEL9_NAME
3684 )
3685 select distinct
3686 HIERARCHY_ID,
3687 null,
3688 LEVEL1_FK_KEY,
3689 NULL,
3690 NULL,
3691 NULL,
3692 NULL,
3693 NULL,
3694 NULL,
3695 NULL,
3696 NULL,
3697 1,
3698 null,
3699 LEVEL1_NAME,
3700 NULL,
3701 NULL,
3702 NULL,
3703 NULL,
3704 NULL,
3705 NULL,
3706 NULL,
3707 NULL
3708 From MTH_ITEM_DENORM_D_TMP
3709 Where LEVEL1_FK_KEY is not null and level_NUM = 2 AND
3710 hierarchy_id = p_hierarchy_id;
3711
3712
3713
3714 EXCEPTION
3715 WHEN OTHERS THEN
3716 ROLLBACK;
3717 RAISE;
3718
3719 END BUILD_LVL_1_9_item_denorm_tmp;
3720
3721 /* ****************************************************************************
3722 * Procedure :insrt_lvl_1_9_to_item_denorm *
3723 * Description :This procedure inserts level 1 to level 9 data in the item *
3724 * denorm tmp table that do not exist in the item denorm table *
3725 * for the specified hierarchy id *
3726 * into the item denorm table *
3727 * File Name :MTHITEMDB.PLS *
3728 * Visibility :Private *
3729 * Parameters : *
3730 * Modification log : *
3731 * Author Date Change *
3732 * Yong Feng 30-June--2012 Initial Creation *
3733 **************************************************************************** */
3734
3735
3736 PROCEDURE insrt_lvl_1_9_to_item_denorm (p_hierarchy_id IN NUMBER)
3737 IS
3738 v_log_to_date DATE ;
3739
3740 v_unassigned_val VARCHAR2(30);
3741 BEGIN
3742
3743 v_log_to_date := sysdate;
3744 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3745
3749 level9_fk_key,
3746 INSERT INTO mth_item_denorm_d
3747 (hierarchy_id,
3748 item_fk_key,
3750 level8_fk_key,
3751 level7_fk_key,
3752 level6_fk_key,
3753 level5_fk_key,
3754 level4_fk_key,
3755 level3_fk_key,
3756 level2_fk_key,
3757 level1_fk_key,
3758 level_num,
3759 item_name,
3760 level9_name,
3761 level8_name,
3762 level7_name,
3763 level6_name,
3764 level5_name,
3765 level4_name,
3766 level3_name,
3767 level2_name,
3768 level1_name,
3769 created_by,
3770 last_updated_by,
3771 last_update_login,
3772 creation_date,
3773 last_update_date)
3774 SELECT tmp.hierarchy_id,
3775 tmp.item_fk_key,
3776 tmp.level9_fk_key,
3777 tmp.level8_fk_key,
3778 tmp.level7_fk_key,
3779 tmp.level6_fk_key,
3780 tmp.level5_fk_key,
3781 tmp.level4_fk_key,
3782 tmp.level3_fk_key,
3783 tmp.level2_fk_key,
3784 tmp.level1_fk_key,
3785 tmp.level_num,
3786 tmp.item_name,
3787 tmp.level9_name,
3788 tmp.level8_name,
3789 tmp.level7_name,
3790 tmp.level6_name,
3791 tmp.level5_name,
3792 tmp.level4_name,
3793 tmp.level3_name,
3794 tmp.level2_name,
3795 tmp.level1_name,
3796 v_unassigned_val,
3797 v_unassigned_val,
3798 v_unassigned_val,
3799 v_log_to_date,
3800 v_log_to_date
3801 FROM mth_item_denorm_d_tmp tmp
3802 WHERE tmp.level_num < 10 AND
3803 tmp.hierarchy_id = p_hierarchy_id AND
3804 NOT EXISTS
3805 (SELECT 1
3806 FROM mth_item_denorm_d mid
3807 WHERE mid.hierarchy_id = tmp.hierarchy_id AND
3808 ((mid.level_num = 9 AND tmp.level_num = 9 AND mid.level9_fk_key = tmp.level9_fk_key)
3809 OR (mid.level_num = 8 AND tmp.level_num = 8 AND mid.level8_fk_key = tmp.level8_fk_key)
3810 OR (mid.level_num = 7 AND tmp.level_num = 7 AND mid.level7_fk_key = tmp.level7_fk_key)
3811 OR (mid.level_num = 6 AND tmp.level_num = 6 AND mid.level6_fk_key = tmp.level6_fk_key)
3812 OR (mid.level_num = 5 AND tmp.level_num = 5 AND mid.level5_fk_key = tmp.level5_fk_key)
3813 OR (mid.level_num = 4 AND tmp.level_num = 4 AND mid.level4_fk_key = tmp.level4_fk_key)
3814 OR (mid.level_num = 3 AND tmp.level_num = 3 AND mid.level3_fk_key = tmp.level3_fk_key)
3815 OR (mid.level_num = 2 AND tmp.level_num = 2 AND mid.level2_fk_key = tmp.level2_fk_key)
3816 OR (mid.level_num = 1 AND tmp.level_num = 1 AND mid.level1_fk_key = tmp.level1_fk_key)) ) ;
3817
3818 END insrt_lvl_1_9_to_item_denorm;
3819
3820 /* ****************************************************************************
3821 * Procedure :merge_lvl_1_9_to_item_denorm *
3822 * Description :This procedure merges level 1 to level 9 data in the item *
3823 * denorm tmp table for the specified hierarchy id *
3824 * into the item denorm table *
3825 * File Name :MTHITEMDB.PLS *
3826 * Visibility :Private *
3827 * Parameters : *
3828 * Modification log : *
3829 * Author Date Change *
3830 * Yong Feng 30-June--2012 Initial Creation *
3831 **************************************************************************** */
3832
3833
3834 PROCEDURE merge_lvl_1_9_to_item_denorm (p_hierarchy_id IN NUMBER)
3835 IS
3836 v_log_to_date DATE ;
3837
3838 v_unassigned_val VARCHAR2(30);
3839 BEGIN
3840
3841 v_log_to_date := sysdate;
3842 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
3843
3844 Merge INTO mth_item_denorm_d mid USING (SELECT hierarchy_id,
3845 item_fk_key,
3846 level9_fk_key,
3847 level8_fk_key,
3848 level7_fk_key,
3849 level6_fk_key,
3850 level5_fk_key,
3851 level4_fk_key,
3852 level3_fk_key,
3853 level2_fk_key,
3854 level1_fk_key,
3855 level_num,
3856 item_name,
3857 level9_name,
3858 level8_name,
3859 level7_name,
3860 level6_name,
3861 level5_name,
3862 level4_name,
3863 level3_name,
3864 level2_name,
3865 level1_name
3866 FROM (SELECT * FROM mth_item_denorm_d_tmp WHERE level_num < 10 )) tmp
3867 ON (mid.hierarchy_id = p_hierarchy_id AND tmp.hierarchy_id = mid.hierarchy_id
3868 AND ((mid.level_num = 9 AND tmp.level_num = 9 AND mid.level9_fk_key = tmp.level9_fk_key)
3869 OR (mid.level_num = 8 AND tmp.level_num = 8 AND mid.level8_fk_key = tmp.level8_fk_key)
3870 OR (mid.level_num = 7 AND tmp.level_num = 7 AND mid.level7_fk_key = tmp.level7_fk_key)
3871 OR (mid.level_num = 6 AND tmp.level_num = 6 AND mid.level6_fk_key = tmp.level6_fk_key)
3872 OR (mid.level_num = 5 AND tmp.level_num = 5 AND mid.level5_fk_key = tmp.level5_fk_key)
3873 OR (mid.level_num = 4 AND tmp.level_num = 4 AND mid.level4_fk_key = tmp.level4_fk_key)
3874 OR (mid.level_num = 3 AND tmp.level_num = 3 AND mid.level3_fk_key = tmp.level3_fk_key)
3875 OR (mid.level_num = 2 AND tmp.level_num = 2 AND mid.level2_fk_key = tmp.level2_fk_key)
3876 OR (mid.level_num = 1 AND tmp.level_num = 1 AND mid.level1_fk_key = tmp.level1_fk_key)) )
3877 WHEN MATCHED THEN
3878 UPDATE SET
3879 mid.last_updated_by = mid.last_updated_by
3880 WHEN NOT MATCHED THEN
3881 INSERT (
3882 mid.hierarchy_id,
3883 mid.item_fk_key,
3884 mid.level9_fk_key,
3888 mid.level5_fk_key,
3885 mid.level8_fk_key,
3886 mid.level7_fk_key,
3887 mid.level6_fk_key,
3889 mid.level4_fk_key,
3890 mid.level3_fk_key,
3891 mid.level2_fk_key,
3892 mid.level1_fk_key,
3893 mid.level_num,
3894 mid.item_name,
3895 mid.level9_name,
3896 mid.level8_name,
3897 mid.level7_name,
3898 mid.level6_name,
3899 mid.level5_name,
3900 mid.level4_name,
3901 mid.level3_name,
3902 mid.level2_name,
3903 mid.level1_name,
3904 mid.created_by,
3905 mid.last_updated_by,
3906 mid.last_update_login,
3907 mid.creation_date,
3908 mid.last_update_date)
3909 VALUES (
3910 tmp.hierarchy_id,
3911 tmp.item_fk_key,
3912 tmp.level9_fk_key,
3913 tmp.level8_fk_key,
3914 tmp.level7_fk_key,
3915 tmp.level6_fk_key,
3916 tmp.level5_fk_key,
3917 tmp.level4_fk_key,
3918 tmp.level3_fk_key,
3919 tmp.level2_fk_key,
3920 tmp.level1_fk_key,
3921 tmp.level_num,
3922 tmp.item_name,
3923 tmp.level9_name,
3924 tmp.level8_name,
3925 tmp.level7_name,
3926 tmp.level6_name,
3927 tmp.level5_name,
3928 tmp.level4_name,
3929 tmp.level3_name,
3930 tmp.level2_name,
3931 tmp.level1_name,
3932 v_unassigned_val,
3933 v_unassigned_val,
3934 v_unassigned_val,
3935 v_log_to_date,
3936 v_log_to_date) ;
3937
3938 END merge_lvl_1_9_to_item_denorm;
3939
3940 /* ****************************************************************************
3941 * Procedure :ITEM_DIM_LOAD_DENORM_INCR *
3942 * Description :This procedure is used to populate the denorm table *
3943 * for the item dimension hierarchy in a incremental fashion *
3944 * File Name :MTHITEMDB.PLS *
3945 * Visibility :Public *
3946 * Parameters : *
3947 * Modification log : *
3948 * Author Date Change *
3949 * Yong Feng 30-June--2012 Initial Creation *
3950 **************************************************************************** */
3951
3952 PROCEDURE ITEM_DIM_LOAD_DENORM_INCR
3953 IS
3954 v_num_rows NUMBER := 0;
3955 v_log_to_date DATE;
3956 v_log_from_date DATE;
3957 v_unassigned_key number;
3958 v_unassigned_item_name varchar2(240);
3959 v_unassigned_category_name varchar2(240);
3960
3961 -- v_hierarchy_id NUMBER;
3962 -- v_max_level NUMBER;
3963 -- v_min_level NUMBER;
3964 v_num_levels NUMBER;
3965 v_rebld_level_1_to_9 BOOLEAN := FALSE;
3966 v_num_rows_deleted BOOLEAN := FALSE;
3967
3968 CURSOR c_tmp_hid_and_levels
3969 IS
3970 SELECT id.hierarchy_id, mdh.NUMBER_OF_LEVEL,
3971 id.max_level, id.min_level
3972 FROM (
3973 SELECT hierarchy_id,
3974 Max(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3975 decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3976 decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3977 decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3978 decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3979 decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3980 decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3981 decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3982 decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) max_level,
3983 Min(decode(level9_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3984 decode(level8_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3985 decode(level7_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3986 decode(level6_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3987 decode(level5_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3988 decode(level4_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3989 decode(level3_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3990 decode(level2_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1) +
3991 decode(level1_fk_key,NULL,0,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,1)) min_level
3992 FROM MTH_ITEM_DENORM_D_TMP
3993 WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL
3994 GROUP BY hierarchy_id) id,
3995 mth_dim_hierarchy mdh
3996 WHERE id.hierarchy_id = mdh.hierarchy_id;
3997
3998
3999 BEGIN
4000 v_log_to_date := sysdate;
4001 v_unassigned_key := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4002
4003
4004 BEGIN
4005 SELECT ITEM_NAME into v_unassigned_item_name
4006 from MTH_ITEMS_D
4007 where item_PK_KEY = v_unassigned_key;
4008 exception
4009 when TOO_MANY_ROWS then
4010 v_unassigned_item_name := null;
4011 when NO_DATA_FOUND then
4012 v_unassigned_item_name := null;
4013 when others then
4014 v_unassigned_item_name := null;
4015 end;
4016
4017 BEGIN
4018 select CATEGORY_NAME into v_unassigned_category_name
4019 from MTH_ITEM_CATEGORIES_D
4020 where CATEGORY_PK_KEY = v_unassigned_key;
4021 exception
4022 when TOO_MANY_ROWS then
4023 v_unassigned_item_name := null;
4024 when NO_DATA_FOUND then
4025 v_unassigned_item_name := null;
4026 when others then
4027 v_unassigned_item_name := null;
4028 end;
4029
4033
4030 --mth_util_pkg.log_msg('ITEM_DIM_LOAD_DENORM_INCR start',
4031 -- mth_util_pkg.G_DBG_PROC_FUN_START);
4032
4034 -- Step 1: If the table is empty, call init procedure
4035 /*
4036 SELECT 1 INTO v_num_rows FROM mth_item_denorm_d WHERE ROWNUM = 1;
4037
4038 IF (v_num_rows IS NULL) THEN
4039 ITEM_DIM_LOAD_DENORM();
4040 RETURN;
4041 END IF;
4042 */
4043
4044 /*Step 2*/
4045 -- Call mth_run_log_pre_load
4046 -- TO DO: Need to find out if 'MTH_ITEM_DENORM_D' has been used for other purpose.
4047 mth_util_pkg.mth_run_log_pre_load('LOAD_MTH_ITEM_DENORM_D',
4048 MTH_UTIL_PKG.MTH_UA_GET_VAL(),'INCR',
4049 NULL,0,v_log_to_date);
4050 mth_util_pkg.GET_RUN_LOG_DATES('LOAD_MTH_ITEM_DENORM_D',NULL,NULL,NULL,
4051 v_log_from_date,v_log_to_date) ;
4052
4053 -- Step 3: Load items impacted by the changed categories entries
4054 -- from item hierarchy since last run into item denorm temp table
4055 v_num_rows := load_items_from_changed_cats(v_log_from_date,
4056 v_log_to_date,
4057 v_unassigned_key);
4058
4059
4060 -- Step 4: Load new or newly updated items
4061 -- from item hierarchy since last run into item denorm temp table
4062 v_num_rows := v_num_rows +
4063 load_new_items_to_item_dnm_tmp (v_log_from_date, v_log_to_date);
4064
4065 IF (v_num_rows = 0) THEN
4066 -- No new data FOUND FOR item denorm TABLE
4067 RETURN;
4068 END IF;
4069
4070
4071 -- Step 5: Update the entries in item denorm tmp table to change NULL level
4072 -- key and corresponding name to -99999, and 'Unassigned'
4073 UPD_ITEM_DENORM_TMP_UNASSIGNED(v_unassigned_key, v_unassigned_category_name);
4074
4075
4076 -- Step 6: Update MTH_DIM_HIERARCHY for item hierarchy where number of level
4077 -- is NULL or 0 with the number of levels found in item denorm table
4078 -- if the item hierarchy exists in item deorm table
4079 UPDATE_ITEM_HIER_LVL_IN_DIM_HR();
4080 /*
4081 UPDATE MTH_DIM_HIERARCHY DH
4082 SET dh.last_update_date = SYSDATE,
4083 DH.NUMBER_OF_LEVEL =
4084 -- (SELECT num_levels
4085 -- FROM
4086 (SELECT
4087 decode(level9_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4088 decode(level8_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4089 decode(level7_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4090 decode(level6_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4091 decode(level5_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4092 decode(level4_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4093 decode(level3_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4094 decode(level2_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1) +
4095 decode(level1_fk_key,MTH_UTIL_PKG.MTH_UA_GET_VAL,0,NULL,0,1)
4096 AS num_levels
4097 FROM MTH_ITEM_DENORM_D ID
4098 WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4099 ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 )
4100 -- WHERE ROWNUM = 1)
4101 WHERE (DH.NUMBER_OF_LEVEL IS NULL OR DH.NUMBER_OF_LEVEL = 0) AND
4102 DH.dimension_name= 'ITEM' AND
4103 EXISTS (SELECT 1 FROM MTH_ITEM_DENORM_D ID
4104 WHERE ID.item_fk_key != MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4105 DH.dimension_name= 'ITEM' AND
4106 ID.hierarchy_id = DH.hierarchy_id AND ROWNUM = 1 );
4107 */
4108
4109
4110 -- Step 7: For each hierarchy do the following:
4111 -- 7.1: Find max and min number of levels in item denorm tmp table
4112 -- and number of levels in item denorm table
4113
4114 -- 7.2: Delete data in the item denorm table for entries existed in tmp
4115 -- table if the item denorm table has data, which can be determined
4116 -- if v_num_levels is not null
4117 --
4118 -- 7.3 If item denrom table does not has any data for this hierarchy
4119 -- iff v_num_levels is NULL and v_num_rows_deleted is false
4120 -- THEN insert an entry for unassigned item into item denorm tmp
4121 --
4122 --
4123 -- 7.4 Balance the data in item denorm tmp table if
4124 -- NOT(v_num_levels in item denorm is not null and
4125 -- max_level in tmp table > v_num_levels in item denorm OR
4126 -- max_level == min_Level and max_level == v_num_levels)
4127 --
4128 -- 7.5: Delete level 1 to level 9 in item denorm IF
4129 -- data has been deleted in step 7.2 due to update OR
4130 -- v_num_levels is not null in item denorm and
4131 -- v_num_levels > 0 and
4132 -- v_num_levels < max_level because of requiring
4133 -- rebalancing in the item denorm
4134 --
4135 -- 7.6: Copy data (level 10 so far) from tmp to item denorm table
4136 --
4137 -- 7.7: Balance the data in the item denorm table IF
4138 -- v_num_levels in item denorm is not null and
4139 -- max_level in tmp table > v_num_levels in item denorm
4140 --
4141 -- 7.8: If there are data being deleted in item denorm in step 6.2
4142 -- THEN
4143 -- rebuild level 1 to level 9 for item denorm table
4144 -- since some entries in those
4145 -- levels for that particular hierarchy might not exist anymore OR
4146 -- missing new entries
4147 -- ELSE
4148 -- Build level1 to level 9 in tmp and insert only new entries
4149 -- into item denorm
4153 -- max_level > v_num_levels)
4150 -- END
4151 --
4152 -- 7.9: IF ( v_num_levels is null or
4154 -- Either the item denorm table does not have any data for the
4155 -- hierarchy or the level in the tmp is larger than item denorm
4156 -- THEN
4157 -- set the number of levels in the dim hierarchy table
4158
4159
4160 FOR r_hid_and_levels IN c_tmp_hid_and_levels LOOP
4161 -- 7.1: get the number of levels for the hierarchy in item denorm table
4162 v_num_levels := r_hid_and_levels.NUMBER_OF_LEVEL;
4163 IF ( v_num_levels IS NULL ) THEN
4164
4165 BEGIN
4166 SELECT
4167 Max(decode(level9_fk_key,v_unassigned_key,0,NULL,0,1) +
4168 decode(level8_fk_key,v_unassigned_key,0,NULL,0,1) +
4169 decode(level7_fk_key,v_unassigned_key,0,NULL,0,1) +
4170 decode(level6_fk_key,v_unassigned_key,0,NULL,0,1) +
4171 decode(level5_fk_key,v_unassigned_key,0,NULL,0,1) +
4172 decode(level4_fk_key,v_unassigned_key,0,NULL,0,1) +
4173 decode(level3_fk_key,v_unassigned_key,0,NULL,0,1) +
4174 decode(level2_fk_key,v_unassigned_key,0,NULL,0,1) +
4175 decode(level1_fk_key,v_unassigned_key,0,NULL,0,1))
4176 INTO v_num_levels
4177 FROM MTH_ITEM_DENORM_D
4178 WHERE item_fk_key <> MTH_UTIL_PKG.MTH_UA_GET_VAL AND
4179 level_num = 10 AND
4180 hierarchy_id = r_hid_and_levels.hierarchy_id;
4181
4182
4183 exception
4184 when NO_DATA_FOUND then
4185 v_num_rows := null;
4186 when others then
4187 v_num_rows := 0;
4188
4189 END;
4190
4191 END IF;
4192
4193
4194 -- 7.2: Delete data in the item denorm table for entries existed in tmp
4195 -- table if the item denorm table has data iff v_num_levels is not null
4196 v_num_rows_deleted := FALSE;
4197 IF ( v_num_levels IS NOT NULL ) THEN
4198 DELETE FROM mth_item_denorm_d id
4199 WHERE id.hierarchy_id = r_hid_and_levels.hierarchy_id AND
4200 level_num = 10 AND
4201 item_fk_key IN (SELECT idt.item_fk_key
4202 FROM mth_item_denorm_d_tmp idt
4203 WHERE idt.hierarchy_id = id.hierarchy_id AND
4204 idt.level_num = 10);
4205 v_num_rows_deleted := (SQL%ROWCOUNT > 0);
4206 END IF;
4207
4208 -- 7.3 If item denrom table does not has any data for this hierarchy
4209 -- iff v_num_levels is NULL and NOT v_num_rows_deleted
4210 -- THEN insert an entry for unassigned item into item denorm tmp table
4211
4212 IF v_num_levels IS NULL AND NOT v_num_rows_deleted THEN
4213
4214 -- Double check if the hierarchy does not exist in item denorm
4215
4216 BEGIN
4217 SELECT 1 INTO v_num_rows
4218 FROM mth_item_denorm_d
4219 WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
4220 level_num = 10 AND
4221 ROWNUM = 1;
4222
4223 exception
4224 when NO_DATA_FOUND then
4225 v_num_rows := null;
4226 when others then
4227 v_num_rows := -1;
4228
4229 END;
4230
4231
4232 IF (v_num_rows IS NULL) THEN
4233 INSERT_UNASS_ITEM_DENORM_TMP(r_hid_and_levels.hierarchy_id,
4234 v_unassigned_key,
4235 v_unassigned_item_name,
4236 v_unassigned_category_name);
4237
4238 END IF;
4239
4240
4241 END IF;
4242
4243 -- 7.4 Balance the data in item denorm tmp table if
4244 -- NOT(v_num_levels in item denorm is not null and
4245 -- is bigger than 0 and
4246 -- max_level in tmp table > v_num_levels in item denorm OR
4247 -- max_level == min_Level and max_level == v_num_levels)
4248
4249
4250 IF NOT ( v_num_levels is not null AND
4251 r_hid_and_levels.max_level > v_num_levels OR
4252 r_hid_and_levels.max_level = r_hid_and_levels.min_Level and
4253 r_hid_and_levels.max_level = v_num_levels ) THEN
4254 IF v_num_levels IS NOT NULL THEN
4255 item_denorm_tmp_balance_load(r_hid_and_levels.hierarchy_id,
4256 r_hid_and_levels.max_level,
4257 r_hid_and_levels.min_level,
4258 r_hid_and_levels.NUMBER_OF_LEVEL);
4259 ELSE
4260 item_denorm_tmp_balance_load(r_hid_and_levels.hierarchy_id,
4261 r_hid_and_levels.max_level,
4262 r_hid_and_levels.min_level,
4263 r_hid_and_levels.max_level);
4264 END IF;
4265
4266 END IF;
4267
4268
4269 -- 7.5: Delete level 1 to level 9 in item denorm IF
4270 -- data has been deleted in step 6.2 due to update OR
4271 -- v_num_levels is not null in item denorm and
4272 -- v_num_levels < max_level because of requiring
4273 -- rebalancing in the item denorm
4274 v_rebld_level_1_to_9 := FALSE;
4275 IF ( v_num_rows_deleted OR
4276 v_num_levels IS NOT NULL AND v_num_levels > 0 AND
4277 r_hid_and_levels.max_level > v_num_levels ) THEN
4278 DELETE FROM mth_item_denorm_d
4279 WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
4280 level_num < 10;
4281 v_rebld_level_1_to_9 := TRUE;
4282 END IF;
4283
4284 --
4288 LEVEL7_FK_KEY, LEVEL6_FK_KEY, LEVEL5_FK_KEY, LEVEL4_FK_KEY,
4285 -- 7.6: Copy data (level 10 so far) from tmp to item denorm table
4286 INSERT INTO MTH_ITEM_DENORM_D
4287 ( HIERARCHY_ID, ITEM_FK_KEY, LEVEL9_FK_KEY, LEVEL8_FK_KEY,
4289 LEVEL3_FK_KEY, LEVEL2_FK_KEY, LEVEL1_FK_KEY, CREATED_BY,
4290 LAST_UPDATE_LOGIN, LAST_UPDATED_BY, CREATION_DATE, LAST_UPDATE_DATE,
4291 LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
4292 LEVEL6_NAME, LEVEL5_NAME, LEVEL4_NAME, LEVEL3_NAME, LEVEL2_NAME,
4293 LEVEL1_NAME )
4294 SELECT DISTINCT HIERARCHY_ID, ITEM_FK_KEY, LEVEL9_FK_KEY, LEVEL8_FK_KEY,
4295 LEVEL7_FK_KEY, LEVEL6_FK_KEY, LEVEL5_FK_KEY, LEVEL4_FK_KEY,
4296 LEVEL3_FK_KEY, LEVEL2_FK_KEY, LEVEL1_FK_KEY, v_unassigned_key,
4297 v_unassigned_key, v_unassigned_key, v_log_to_date, v_log_to_date,
4298 LEVEL_NUM, ITEM_NAME, LEVEL9_NAME, LEVEL8_NAME, LEVEL7_NAME,
4299 LEVEL6_NAME, LEVEL5_NAME, LEVEL4_NAME, LEVEL3_NAME, LEVEL2_NAME,
4300 LEVEL1_NAME
4301 FROM MTH_ITEM_DENORM_D_TMP
4302 WHERE hierarchy_id = r_hid_and_levels.hierarchy_id AND
4303 level_num = 10;
4304
4305
4306 -- 7.7: Balance the data in the item denorm table IF
4307 -- v_num_levels in item denorm is not null and
4308 -- max_level in tmp table > v_num_levels in item denorm
4309 IF ( v_num_levels IS NOT NULL AND
4310 v_num_levels < r_hid_and_levels.max_level ) THEN
4311 item_denorm_balance_load(r_hid_and_levels.hierarchy_id,
4312 r_hid_and_levels.max_level);
4313
4314 END IF;
4315
4316
4317 -- 7.8: If there are data being deleted in item denorm in step 6.2
4318 -- THEN
4319 -- rebuild level 1 to level 9 for item denorm table
4320 -- since some entries in those
4321 -- levels for that particular hierarchy might not exist anymore OR
4322 -- missing new entries
4323 -- ELSE
4324 -- Build level1 to level 9 in tmp and insert new entries into item denorm
4325 -- END
4326 --
4327
4328 IF ( v_num_rows_deleted ) THEN
4329 BUILD_LEVEL_1_9_item_denorm(r_hid_and_levels.hierarchy_id);
4330 ELSE
4331 BUILD_LVL_1_9_item_denorm_tmp(r_hid_and_levels.hierarchy_id);
4332 insrt_lvl_1_9_to_item_denorm(r_hid_and_levels.hierarchy_id);
4333 END IF;
4334
4335
4336 --
4337 -- 6.8: IF ( v_num_levels is null or
4338 -- max_level > v_num_levels)
4339 -- Either the item denorm table does not have any data for the
4340 -- hierarchy or the level in the tmp is larger than item denorm
4341 -- THEN
4342 -- set the number of levels in the dim hierarchy table
4343 IF ( v_num_levels IS NULL OR
4344 v_num_levels < r_hid_and_levels.max_level ) THEN
4345 UPDATE MTH_DIM_HIERARCHY
4346 SET NUMBER_OF_LEVEL = r_hid_and_levels.max_level
4347 WHERE dimension_name= 'ITEM' and
4348 hierarchy_id = r_hid_and_levels.hierarchy_id;
4349 END IF;
4350
4351 END LOOP;
4352
4353
4354
4355
4356
4357 -- Step 8: Run mth_run_log_post_load to update run log entry
4358 mth_util_pkg.mth_run_log_post_load('LOAD_MTH_ITEM_DENORM_D',
4359 MTH_UTIL_PKG.MTH_UA_GET_VAL());
4360
4361
4362 COMMIT;
4363
4364 EXCEPTION
4365 WHEN OTHERS THEN
4366 ROLLBACK;
4367 RAISE;
4368 --End of the procedure ITEM_DIM_LOAD_DENORM_INCR
4369
4370
4371 END ITEM_DIM_LOAD_DENORM_INCR;
4372
4373
4374
4375 /* *****************************************************************************
4376 * Procedure :MTH_ITEM_HRCHY_UNASSIGNED_MAP *
4377 * Description :This procedure is used for the assigning items to *
4378 unassigned parent to different hierrachies of item in which they are not assigned .*
4379 * File Name :MTHITEMB.PLS *
4380 * Visibility :Public *
4381 * Parameters :fact table name *
4382 * Modification log : *
4383 * Author Date Change *
4384 * Amrit Kaur 30-Mar-2012 Initial Creation *
4385 ****************************************************************************** */
4386 PROCEDURE MTH_ITEM_HRCHY_UNASSIGNED_MAP IS
4387 v_log_from_date DATE;
4388 v_log_to_date DATE;
4389 v_unassigned_val VARCHAR2(30);
4390
4391 BEGIN
4392
4393 -- Initialize default parameters
4394 v_log_to_date := sysdate;
4395 v_unassigned_val := MTH_UTIL_PKG.MTH_UA_GET_VAL;
4396
4397 mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP start', mth_util_pkg.G_DBG_PROC_FUN_START);
4398
4399 -- Call mth_run_log_pre_load
4400 mth_util_pkg.mth_run_log_pre_load('MTH_ITEM_UA_D',v_unassigned_val,NULL,NULL,0,v_log_to_date);
4401
4402 -- Call GET_RUN_LOG_DATES
4403 mth_util_pkg.GET_RUN_LOG_DATES('MTH_ITEM_UA_D',NULL,NULL,NULL,v_log_from_date,v_log_to_date);
4404
4405 INSERT INTO MTH_ITEM_HIERARCHY (HIERARCHY_ID,
4406 LEVEL_FK_KEY,
4407 PARENT_FK_KEY,
4408 SYSTEM_FK_KEY,
4409 CREATION_DATE,
4410 LAST_UPDATE_DATE,
4411 CREATION_SYSTEM_ID,
4412 LAST_UPDATE_SYSTEM_ID,
4413 LEVEL_NAME,
4414 PARENT_NAME)
4415 (SELECT UN_REC.HIERARCHY_ID,
4416 UN_REC.LEVEL_FK_KEY,
4417 v_unassigned_val,
4418 v_unassigned_val,
4419 v_log_to_date,
4420 v_log_to_date,
4424 MTH_UTIL_PKG.MTH_UA_GET_MEANING()
4421 v_unassigned_val,
4422 v_unassigned_val,
4423 UN_REC.LEVEL_NAME,
4425 FROM (SELECT MDM.HIERARCHY_ID HIERARCHY_ID,
4426 MID.ITEM_PK_KEY LEVEL_FK_KEY,
4427 MID.ITEM_NAME LEVEL_NAME
4428 FROM
4429 MTH_ITEMS_D MID,
4430 MTH_SYSTEMS_SETUP MSS,
4431 MTH_DIM_HIERARCHY MDM
4432 WHERE MID.CREATION_DATE >v_log_from_date AND
4433 MID.CREATION_DATE <= v_log_to_date AND
4434 MID.ITEM_PK_KEY <> MTH_UTIL_PKG.MTH_UA_GET_VAL() AND
4435 MID.SYSTEM_FK_KEY = MSS.SYSTEM_PK_KEY AND
4436 MDM.DIMENSION_NAME ='ITEM'
4437 MINUS
4438 SELECT MIH.HIERARCHY_ID,
4439 MIH.LEVEL_FK_KEY ,
4440 MIH.LEVEL_NAME
4441 FROM
4442 MTH_ITEM_HIERARCHY MIH )UN_REC
4443 )
4444 ;
4445 mth_util_pkg.log_msg('Number of rows inserted in MTH_ITEM_HIERARCHY - ' || SQL%ROWCOUNT, mth_util_pkg.G_DBG_ROW_CNT);
4446
4447 ----Call mth_run_log_post_load
4448 mth_util_pkg.mth_run_log_post_load('MTH_ITEM_UA_D',v_unassigned_val);
4449 mth_util_pkg.log_msg('MTH_ITEM_HRCHY_UNASSIGNED_MAP end', mth_util_pkg.G_DBG_PROC_FUN_END);
4450
4451 COMMIT;
4452 EXCEPTION
4453 WHEN OTHERS THEN
4454 mth_util_pkg.log_msg('Exception OTHERS in MTH_ITEM_HRCHY_UNASSIGNED_MAP', mth_util_pkg.G_DBG_EXCEPTION);
4455 mth_util_pkg.log_msg(substr(sqlerrm,1,300), mth_util_pkg.G_DBG_EXCEPTION);
4456 mth_util_pkg.log_msg(sqlcode, mth_util_pkg.G_DBG_EXCEPTION);
4457 ROLLBACK;
4458 RAISE;
4459
4460 END MTH_ITEM_HRCHY_UNASSIGNED_MAP;
4461
4462
4463
4464
4465 END MTH_ITEM_DIMENSION_PKG;