1 PACKAGE BODY FII_PMV_HELPER_TABLES_C AS
2 /* $Header: FIIPMVHB.pls 120.1 2005/10/30 05:05:44 appldev noship $ */
3
4 G_PROGRAM_MODE VARCHAR2(5);
5 G_SUPPORTED_NODES NUMBER;
6 G_NODES NUMBER;
7 G_OPTIMUM_NODES NUMBER;
8 G_FC_TOP_NODE_ID NUMBER(15);
9 G_CO_TOP_NODE_ID NUMBER(15);
10 G_CC_TOP_NODE_ID NUMBER(15);
11 G_UDD1_TOP_NODE_ID NUMBER(15);
12 G_FC_DBI_ENABLED_FLAG VARCHAR2(1);
13 G_CO_DBI_ENABLED_FLAG VARCHAR2(1);
14 G_CC_DBI_ENABLED_FLAG VARCHAR2(1);
15 G_UDD1_DBI_ENABLED_FLAG VARCHAR2(1);
16 G_UNASSIGNED_ID NUMBER(15);
17 g_keep_gain_flag VARCHAR2(1) := 'Y';
18 G_PHASE VARCHAR2(120);
19 g_schema_name VARCHAR2(120) := 'FII';
20 g_retcode VARCHAR2(20) := NULL;
21 g_debug_mode VARCHAR2(1)
22 := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
23
24 p_tab dim_nodes_tab;
25
26
27 -- *******************************************************************
28 -- FUNCTION Dimension_name returns the Dimension name
29 -- Parameter : Dimension Short Name
30 -- *******************************************************************
31
32 FUNCTION Dimension_name (dim_short_name VARCHAR2) RETURN VARCHAR2 IS
33 l_dimension_name VARCHAR2(25) := NULL;
34
35 BEGIN
36
37 IF(dim_short_name = 'GL_FII_FIN_ITEM') THEN
38 l_dimension_name := 'Financial Category';
39 ELSIF (dim_short_name = 'FII_COMPANIES') THEN
40 l_dimension_name := 'Company';
41 ELSIF (dim_short_name = 'HRI_CL_ORGCC') THEN
42 l_dimension_name := 'Cost Center';
43 ELSIF (dim_short_name = 'FII_USER_DEFINED_1') THEN
44 l_dimension_name := 'User Defined Dimension 1';
45 END IF;
46
47 return l_dimension_name;
48
49 END Dimension_name;
50
51 -- *******************************************************************
52 -- Initialize (Get the DBI Enabled flags for all the dimensions)
53 -- *******************************************************************
54
55 PROCEDURE Initialize IS
56 l_dir VARCHAR2(160);
57 l_ret_code number;
58 l_nodes NUMBER;
59 l_count number := 4;
60 l_vset_id number(15);
61 l_bool_ret BOOLEAN;
62
63 BEGIN
64
65 g_phase := 'Do set up for log file';
66 ----------------------------------------------
67 -- Do set up for log file
68 ----------------------------------------------
69 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
70 ------------------------------------------------------
71 -- Set default directory in case if the profile option
72 -- BIS_DEBUG_LOG_DIRECTORY is not set up
73 ------------------------------------------------------
74 if l_dir is NULL then
75 l_dir := FII_UTIL.get_utl_file_dir;
76 end if;
77
78 ----------------------------------------------------------------
79 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
80 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
81 -- the log files and output files are written to
82 ----------------------------------------------------------------
83 FII_UTIL.initialize('FII_PMV_HELPER_TABLES_C.log',
84 'FII_PMV_HELPER_TABLES_C.out',l_dir,'FII_PMV_HELPER_TABLES_C');
85
86 -- Obtain FII schema name
87 g_schema_name := FII_UTIL.get_schema_name ('FII');
88
89 -- Obtain user ID, login ID and initialize package variables
90 g_phase := 'Obtain the User ID and Login ID';
91 FII_USER_ID := FND_GLOBAL.USER_ID;
92 FII_LOGIN_ID := FND_GLOBAL.LOGIN_ID;
93
94 -- If any of the above values is not set, error out
95 IF (FII_User_Id is NULL OR FII_Login_Id is NULL) THEN
96 FII_UTIL.Write_Log ('>>> Failed Intialization');
97 RAISE PMVH_fatal_err;
98 END IF;
99
100 -- Determine if process will be run in debug mode
101 IF (NVL(G_Debug_Mode, 'N') <> 'N') THEN
102 FIIDIM_Debug := TRUE;
103 FII_UTIL.Write_Log (' Debug On');
104 ELSE
105 FIIDIM_Debug := FALSE;
106 FII_UTIL.Write_Log (' Debug Off');
107 END IF;
108
109 -- Turn trace on if process is run in debug mode
110 IF (FIIDIM_Debug) THEN
111 -- Program running in debug mode, turning trace on
112 EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
113 FII_UTIL.Write_Log ('Initialize: Set Trace On');
114 END IF;
115
116 IF (FIIDIM_Debug) THEN
117 FII_UTIL.Write_Log ('Initialize: Now start processing '|| 'Helper Table Population');
118 End If;
119
120 -- --------------------------------------------------------
121 -- Find the unassigned ID
122 -- --------------------------------------------------------
123
124 g_phase := 'Find the shipped FII value set id and the unassigned value id';
125 FII_GL_EXTRACTION_UTIL.get_unassigned_id(G_UNASSIGNED_ID, l_vset_id, l_ret_code);
126 IF(l_ret_code = -1) THEN
127 RAISE PMVH_fatal_err;
128 END IF;
129
130 -- --------------------------------------------------------
131 -- Get the number of nodes which can be aggregated in
132 -- each dimension
133 -- --------------------------------------------------------
134 g_phase := 'Get the profile value for the number of nodes in a dimension';
135 BEGIN
136 -- Bug 4300047. Default no of nodes to be aggregated
137 -- should be 100.
138 l_nodes := NVL(fnd_profile.value('FII_AGGREGATE_NODES'), 100);
139 EXCEPTION
140 WHEN value_error THEN
141 FII_UTIL.Write_Log ('Value for profile FII: Nodes to be Aggregated is not set correctly.
142 Please enter a numeric value for the profile.');
143 raise;
144 END;
145 G_SUPPORTED_NODES := POWER(l_nodes,4);
146
147 IF (FIIDIM_Debug) THEN
148 FII_UTIL.Write_Log('Supported number of nodes : ' || G_SUPPORTED_NODES);
149 END IF;
150
151 -- --------------------------------------------------------
152 -- Get the DBI enabled flag for all the dimensions which
153 -- are to be aggregated
154 -- We might consider building an api for this
155 -- --------------------------------------------------------
156 g_phase := 'Getting the DBI Enabled flag for all the dimensions';
157 -- Get the enabled flag for FC
158 g_phase := 'Get the enabled flag for FC';
159
160 BEGIN
161 SELECT DBI_ENABLED_FLAG, DBI_HIER_TOP_NODE_ID
162 INTO G_FC_DBI_ENABLED_FLAG, G_FC_TOP_NODE_ID
163 FROM FII_FINANCIAL_DIMENSIONS
164 WHERE DIMENSION_SHORT_NAME = 'GL_FII_FIN_ITEM';
165
166 IF(G_FC_DBI_ENABLED_FLAG = 'Y' and G_FC_TOP_NODE_ID is NULL) THEN
167 FII_MESSAGE.write_log (msg_name => 'FII_TNODE_NOT_FOUND',
168 token_num => 1,
169 t1 => 'DIM_NAME',
170 v1 => Dimension_name('GL_FII_FIN_ITEM'));
171 FII_MESSAGE.write_output (msg_name => 'FII_TNODE_NOT_FOUND',
172 token_num => 1,
173 t1 => 'DIM_NAME',
174 v1 => Dimension_name('GL_FII_FIN_ITEM'));
175 raise PMVH_fatal_err;
176
177 END IF;
178
179 EXCEPTION
180 WHEN NO_DATA_FOUND THEN
181 FII_UTIL.Write_Log ('Set up for Financial Category not done');
182 G_FC_DBI_ENABLED_FLAG := 'N';
183 END;
184
185 IF (G_FC_DBI_ENABLED_FLAG = 'N') THEN
186 l_count := l_count - 1;
187 END IF;
188
189 IF (FIIDIM_Debug) THEN
190 FII_UTIL.Write_Log('DBI Enabled flag for Financial Category : ' || G_FC_DBI_ENABLED_FLAG);
191 END IF;
192
193 g_phase := 'Get the enabled flag for Company';
194 -- Get the enabled flag for Company
195 BEGIN
196 SELECT DBI_ENABLED_FLAG, DBI_HIER_TOP_NODE_ID
197 INTO G_CO_DBI_ENABLED_FLAG, G_CO_TOP_NODE_ID
198 FROM FII_FINANCIAL_DIMENSIONS
199 WHERE DIMENSION_SHORT_NAME = 'FII_COMPANIES';
200
201 IF(G_CO_DBI_ENABLED_FLAG = 'Y' and G_CO_TOP_NODE_ID is NULL) THEN
202 FII_MESSAGE.write_log (msg_name => 'FII_TNODE_NOT_FOUND',
203 token_num => 1,
204 t1 => 'DIM_NAME',
205 v1 => Dimension_name('FII_COMPANIES'));
206 FII_MESSAGE.write_output (msg_name => 'FII_TNODE_NOT_FOUND',
207 token_num => 1,
208 t1 => 'DIM_NAME',
209 v1 => Dimension_name('FII_COMPANIES'));
210 raise PMVH_fatal_err;
211
212 END IF;
213
214 EXCEPTION
215 WHEN NO_DATA_FOUND THEN
216 FII_UTIL.Write_Log ('Set up for Company not done');
217 G_CO_DBI_ENABLED_FLAG := 'N';
218 END;
219
220 IF (FIIDIM_Debug) THEN
221 FII_UTIL.Write_Log('DBI Enabled flag for Company : ' || G_CO_DBI_ENABLED_FLAG);
222 END IF;
223
224 IF(G_CO_DBI_ENABLED_FLAG = 'N') THEN
225 l_count := l_count - 1;
226 l_bool_ret := FND_CONCURRENT.Set_Completion_Status(
227 status => 'WARNING',
228 message => 'Company Dimension is not enabled.'
229 );
230 END IF;
231
232 g_phase := 'Get the enabled flag for CC';
233 -- Get the enabled flag for CC
234 BEGIN
235 SELECT DBI_ENABLED_FLAG, DBI_HIER_TOP_NODE_ID
236 INTO G_CC_DBI_ENABLED_FLAG, G_CC_TOP_NODE_ID
237 FROM FII_FINANCIAL_DIMENSIONS
238 WHERE DIMENSION_SHORT_NAME = 'HRI_CL_ORGCC';
239
240 IF(G_CC_DBI_ENABLED_FLAG = 'Y' and G_CC_TOP_NODE_ID is NULL) THEN
241 FII_MESSAGE.write_log (msg_name => 'FII_TNODE_NOT_FOUND',
242 token_num => 1,
243 t1 => 'DIM_NAME',
244 v1 => Dimension_name('HRI_CL_ORGCC'));
245 FII_MESSAGE.write_output (msg_name => 'FII_TNODE_NOT_FOUND',
246 token_num => 1,
247 t1 => 'DIM_NAME',
248 v1 => Dimension_name('HRI_CL_ORGCC'));
249 raise PMVH_fatal_err;
250
251 END IF;
252
253 EXCEPTION
254 WHEN NO_DATA_FOUND THEN
255 FII_UTIL.Write_Log ('Set up for Cost Center not done');
256 G_CC_DBI_ENABLED_FLAG := 'N';
257 END;
258
259 IF (FIIDIM_Debug) THEN
260 FII_UTIL.Write_Log('DBI Enabled flag for Cost Center : ' || G_CC_DBI_ENABLED_FLAG);
261 END IF;
262
263 IF(G_CC_DBI_ENABLED_FLAG = 'N') THEN
264 l_count := l_count - 1;
265 l_bool_ret := FND_CONCURRENT.Set_Completion_Status(
266 status => 'WARNING',
267 message => 'Cost Center Dimension is not enabled.'
268 );
269 END IF;
270
271 g_phase := 'Get the enabled flag for udd1';
272 -- Get the enabled flag for UDD1
273
274 BEGIN
275 SELECT DBI_ENABLED_FLAG, DBI_HIER_TOP_NODE_ID
276 INTO G_UDD1_DBI_ENABLED_FLAG, G_UDD1_TOP_NODE_ID
277 FROM FII_FINANCIAL_DIMENSIONS
278 WHERE DIMENSION_SHORT_NAME = 'FII_USER_DEFINED_1';
279
280 IF(G_UDD1_DBI_ENABLED_FLAG = 'Y' and G_UDD1_TOP_NODE_ID is NULL) THEN
281 FII_MESSAGE.write_log (msg_name => 'FII_TNODE_NOT_FOUND',
282 token_num => 1,
283 t1 => 'DIM_NAME',
284 v1 => Dimension_name('FII_USER_DEFINED_1'));
285 FII_MESSAGE.write_output (msg_name => 'FII_TNODE_NOT_FOUND',
286 token_num => 1,
287 t1 => 'DIM_NAME',
288 v1 => Dimension_name('FII_USER_DEFINED_1'));
289 raise PMVH_fatal_err;
290
291 END IF;
292
293 EXCEPTION
294 WHEN NO_DATA_FOUND THEN
295 FII_UTIL.Write_Log ('Set up for User Defined Dimension1 not done');
296 G_UDD1_DBI_ENABLED_FLAG := 'N';
297 END;
298
299 IF(G_UDD1_DBI_ENABLED_FLAG = 'N') THEN
300 l_count := l_count - 1;
301 END IF;
302
303 IF (FIIDIM_Debug) THEN
304 FII_UTIL.Write_Log('DBI Enabled flag for User Defined Dimension1 : ' || G_UDD1_DBI_ENABLED_FLAG);
305 END IF;
306
307 -- Calculate the initial no of nodes allowed to be aggregated
308 -- in each dimension
309 g_phase := 'Setting the initial number of nodes which can be aggregated for each dimension';
310 IF(l_count <> 0) THEN
311
312 G_NODES := ROUND(POWER(G_SUPPORTED_NODES, 1/l_count));
313
314 END IF;
315
316 IF (FIIDIM_Debug) THEN
317 FII_UTIL.Write_Log('Number of Nodes supported to be aggregated for each dimension ' || G_NODES);
318 END IF;
319
320 Exception
321 When PMVH_fatal_err then
322 FII_UTIL.Write_Log ('FII_PMV_HELPER_TABLES_C.Initialize : '|| 'User defined error');
323 FND_CONCURRENT.Af_Rollback;
324 FII_MESSAGE.Func_Fail(func_name => 'FII_PMV_HELPER_TABLES_C.Initialize');
325 raise;
326
327 When others then
328 FND_CONCURRENT.Af_Rollback;
329 FII_UTIL.Write_Log ('Unexpected error when calling Initialize...');
330 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
331 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
332 RAISE;
333
334 END Initialize;
335
336
337 -- **************************************************************************************
338 -- Populate_Temp procedure ( Populates FII_AGGRT_NODE_GT table with the records from all
339 -- the dimensions) This would store the information about the number of children of the
340 -- parent and the dimension name for an id.
341 -- **************************************************************************************
342
343 Procedure populate_temp IS
344 BEGIN
345
346 IF (FIIDIM_Debug) THEN
347 FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.populate_temp');
348 END IF;
349
350 g_phase := 'Populate FII_AGGRT_NODE_GT';
351 -- For FC Dimension
352 If (G_FC_DBI_ENABLED_FLAG = 'Y') THEN
353 g_phase :='Populate FII_AGGRT_NODE_GT for Financial Category dimension';
354 INSERT INTO FII_AGGRT_NODE_GT (
355 id,
356 no_of_children,
357 dim_short_name)
358
359 select
360 NEXT_LEVEL_FIN_CAT_ID,
361 subtree_freq,
362 'GL_FII_FIN_ITEM'
363 from FII_FIN_ITEM_LEAF_HIERS,
364 (select h.PARENT_FIN_CAT_ID root_id,
365 count(*) subtree_freq
366 from FII_FIN_ITEM_LEAF_HIERS h
367 group by h.PARENT_FIN_CAT_ID) g
368 where parent_fin_cat_id = g.root_id
369 and (PARENT_FIN_CAT_ID <> NEXT_LEVEL_FIN_CAT_ID
370 Or NEXT_LEVEL_FIN_CAT_ID = G_FC_TOP_NODE_ID )
371 group by PARENT_FIN_CAT_ID,
372 NEXT_LEVEL_FIN_CAT_ID,
373 subtree_freq ;
374
375 IF (FIIDIM_Debug) THEN
376 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');
377 END IF;
378
379 -- This is done to treat the top node differently
380 UPDATE FII_AGGRT_NODE_GT
381 SET no_of_children = no_of_children + 1
382 WHERE id = G_FC_TOP_NODE_ID ;
383
384 IF (FIIDIM_Debug) THEN
385 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_AGGRT_NODE_GT');
386 END IF;
387
388 ELSE
389 -- Populate the unassigned node in case the dimension is disabled
390 -- This should never be the case as FC is a mandatory dimension
391 g_phase := 'Populate the unassigned node in case the dimension is disabled';
392 INSERT INTO FII_AGGRT_NODE_GT (
393 id,
394 no_of_children,
395 dim_short_name)
396 VALUES(G_UNASSIGNED_ID, 1, 'GL_FII_FIN_ITEM');
397
398 IF (FIIDIM_Debug) THEN
399 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');
400 END IF;
401 END IF;
402
403 -- For Company Dimension
404 If (G_CO_DBI_ENABLED_FLAG = 'Y') THEN
405 g_phase :='Populate FII_AGGRT_NODE_GT for Company dimension';
406 INSERT INTO FII_AGGRT_NODE_GT (
407 id,
408 no_of_children,
409 dim_short_name)
410
411 select
412 NEXT_LEVEL_COMPANY_ID,
413 subtree_freq,
414 'FII_COMPANIES'
415 from fii_COMPANY_hierarchies,
416 (select h.PARENT_COMPANY_ID root_id,
417 count(*) subtree_freq
418 from fii_COMPANY_hierarchies h
419 group by h.PARENT_COMPANY_ID) g
420 where parent_COMPANY_id = g.root_id
421 and PARENT_COMPANY_ID <> NEXT_LEVEL_COMPANY_ID
422 group by PARENT_COMPANY_ID,
423 NEXT_LEVEL_COMPANY_ID,
424 subtree_freq ;
425
426 IF (FIIDIM_Debug) THEN
427 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');
428 END IF;
429
430 -- This is done to treat the top node differently
431 UPDATE FII_AGGRT_NODE_GT
432 SET no_of_children = no_of_children + 1
433 WHERE id = G_CO_TOP_NODE_ID ;
434
435 IF (FIIDIM_Debug) THEN
436 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_AGGRT_NODE_GT');
437 END IF;
438
439 ELSE
440 -- Populate the unassigned node in case the dimension is disabled
441 g_phase := 'Populate the unassigned node in case the dimension is disabled';
442 INSERT INTO FII_AGGRT_NODE_GT (
443 id,
444 no_of_children,
445 dim_short_name)
446 VALUES(G_UNASSIGNED_ID, 1, 'FII_COMPANIES');
447
448 IF (FIIDIM_Debug) THEN
449 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');
450 END IF;
451
452 END IF;
453
454 -- For Cost Center
455 If (G_CC_DBI_ENABLED_FLAG = 'Y') THEN
456 g_phase :='Populate FII_AGGRT_NODE_GT for Cost Center dimension';
457 INSERT INTO FII_AGGRT_NODE_GT (
458 id,
459 no_of_children,
460 dim_short_name)
461
462 select
463 NEXT_LEVEL_CC_ID,
464 subtree_freq,
465 'HRI_CL_ORGCC'
466 from fii_COST_CTR_hierarchies,
467 (select h.PARENT_CC_ID root_id,
468 count(*) subtree_freq
469 from fii_COST_CTR_hierarchies h
470 group by h.PARENT_CC_ID) g
471 where parent_CC_id = g.root_id
472 and PARENT_CC_ID <> NEXT_LEVEL_CC_ID
473 group by PARENT_CC_ID,
474 NEXT_LEVEL_CC_ID,
475 subtree_freq ;
476
477 IF (FIIDIM_Debug) THEN
478 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');
479 END IF;
480
481 -- This is done to treat the top node differently
482 UPDATE FII_AGGRT_NODE_GT
483 SET no_of_children = no_of_children + 1
484 WHERE id = G_CC_TOP_NODE_ID ;
485
486 IF (FIIDIM_Debug) THEN
487 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_AGGRT_NODE_GT');
488 END IF;
489
490 ELSE
491
492 -- Populate the unassigned node in case the dimension is disabled
493 g_phase := 'Populate the unassigned node in case the dimension is disabled';
494
495 INSERT INTO FII_AGGRT_NODE_GT (
496 id,
497 no_of_children,
498 dim_short_name)
499 VALUES(G_UNASSIGNED_ID, 1, 'HRI_CL_ORGCC');
500
501 IF (FIIDIM_Debug) THEN
502 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');
503 END IF;
504
505 END IF;
506
507 -- For UDD1
508 If (G_UDD1_DBI_ENABLED_FLAG = 'Y') THEN
509 g_phase :='Populate FII_AGGRT_NODE_GT for User Defined dimension1';
510 INSERT INTO FII_AGGRT_NODE_GT (
511 id,
512 no_of_children,
513 dim_short_name)
514
515 select NEXT_LEVEL_VALUE_ID,
516 subtree_freq,
517 'FII_USER_DEFINED_1'
518 from fii_UDD1_hierarchies,
519 (select h.PARENT_VALUE_ID root_id,
520 count(*) subtree_freq
521 from fii_UDD1_hierarchies h
522 group by h.PARENT_VALUE_ID) g
523 where parent_VALUE_id = g.root_id
524 and PARENT_VALUE_ID <> NEXT_LEVEL_VALUE_ID
525 group by PARENT_VALUE_ID,
526 NEXT_LEVEL_VALUE_ID,
527 subtree_freq ;
528
529 IF (FIIDIM_Debug) THEN
530 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');
531 END IF;
532
533 -- This is done to treat the top node differently
534 UPDATE FII_AGGRT_NODE_GT
535 SET no_of_children = no_of_children + 1
536 WHERE id = G_UDD1_TOP_NODE_ID ;
537
538 IF (FIIDIM_Debug) THEN
539 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_AGGRT_NODE_GT');
540 END IF;
541
542 ELSE
543
544 -- Populate the unassigned node in case the dimension is disabled
545 g_phase := 'Populate the unassigned node in case the dimension is disabled';
546
547 INSERT INTO FII_AGGRT_NODE_GT (
548 id,
549 no_of_children,
550 dim_short_name)
551 VALUES(G_UNASSIGNED_ID, 1, 'FII_USER_DEFINED_1');
552
553 IF (FIIDIM_Debug) THEN
554 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_AGGRT_NODE_GT');
555 END IF;
556
557 END IF;
558
559 IF (FIIDIM_Debug) THEN
560 FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.populate_temp');
561 END IF;
562 --FND_CONCURRENT.Af_Commit;
563
564 EXCEPTION
565 When others then
566 FND_CONCURRENT.Af_Rollback;
567 FII_UTIL.Write_Log ('Unexpected error when calling populate_temp...');
568 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
569 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
570 raise;
571
572 END populate_temp;
573
574 -- *************************************************************************************
575 -- This is the calculate_sort_nodes procedure. This is to sort the dimensions based on
576 -- the number of nodes in the dimension
577 -- *************************************************************************************
578
579 Procedure calculate_sort_nodes IS
580
581 -- cursor to get the number of nodes in a dimension
582 CURSOR dim_no_of_nodes is
583 select max (no_of_children) no_of_children, dim_short_name
584 from fii_aggrt_node_gt
585 group by dim_short_name
586 ORDER BY no_of_children;
587
588 I number :=1;
589
590 BEGIN
591
592 IF (FIIDIM_Debug) THEN
593 FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.calculate_sort_nodes');
594 END IF;
595
596 For dim_no_of_nodes_rec in dim_no_of_nodes
597 LOOP
598
599 IF(dim_no_of_nodes_rec.no_of_children >1) THEN
600 ---------------------------------------------------------------------------
601 -- In case the number of children is 1 then there is a single node
602 -- in the hierarchy which is unassigned node
603 -- We subtract 1 because we had added 1 to treat the top node differently
604 -- in populate_temp. In case of dimension being disabled it is not added
605 -- so it should not be subtracted also.
606 ---------------------------------------------------------------------------
607
608 p_tab(I).number_of_nodes := dim_no_of_nodes_rec.no_of_children - 1;
609 ELSE
610 p_tab(I).number_of_nodes := dim_no_of_nodes_rec.no_of_children;
611 END IF;
612
613 p_tab(I).dim_short_name := dim_no_of_nodes_rec.dim_short_name;
614 p_tab(I).gain := 1;
615 -- The no of nodes here includes unassigned node as well except in FC
616 IF (FIIDIM_Debug) THEN
617 FII_UTIL.Write_Log('Dimension : ' || Dimension_name(p_tab(I).dim_short_name) || ' has : ' || p_tab(I).number_of_nodes ||' Nodes.');
618 END IF;
619
620 I := I +1;
621 END LOOP;
622
623 IF (FIIDIM_Debug) THEN
624 FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.calculate_sort_nodes');
625 END IF;
626
627 END calculate_sort_nodes;
628
629 -- *************************************************************************************
630 -- This is the update_hierarchy procedure. This procedure updates the
631 -- next_level_aggregate_flag for the first two levels for UD1, CC, Company and FC.
632 -- *************************************************************************************
633
634 Procedure update_hierarchy(p_dim_short_name varchar2) IS
635 l_max_level number;
636 BEGIN
637
638 IF (FIIDIM_Debug) THEN
639 FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.update_hierarchy');
640 END IF;
641
642 -- The first two levels should always be set to aggregated
643 -- This is to improve the performance of the reports.
644
645 IF (p_dim_short_name = 'GL_FII_FIN_ITEM') THEN
646 -- For FC
647
648 -- Bug 4235853. Treat FC differently since in security api there is an assumption that the level
649 -- just below the top node of any category (Expense/Revenue) will be aggregated so
650 -- we need to take care that the level just below the top nodes is aggregated for FC.
651
652 g_phase := 'Get the lowest level at which the top node is defined for Revenue';
653 select max(child_level) into l_max_level
654 from fii_fin_cat_type_assgns, fii_fin_item_leaf_hiers
655 where top_node_flag = 'Y'
656 and fin_category_id = child_fin_Cat_id
657 and parent_level = child_level
658 and fin_cat_type_code = 'R';
659
660 -- Updating the first two levels in FC. Also aggregate
661 -- atleast one level below the top nodes in FC. The top nodes here would refer to the
662 -- top nodes of Revenue/Expenses.
663
664 g_phase := 'Updating the aggregate_next_level flag for Revenue';
665 update fii_fin_item_leaf_hiers f
666 set aggregate_next_level_flag = 'Y'
667 where aggregate_next_level_flag <> 'Y'
668 and (next_level in (1,2)
669 or (next_level <= l_max_level + 1
670 and f.next_level_fin_cat_id in (select fin_category_id
671 from fii_fin_cat_type_assgns
672 where fin_cat_type_code = 'R')));
673
674 IF (FIIDIM_Debug) THEN
675 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
676 END IF;
677
678 g_phase := 'Get the lowest level at which the top node is defined for Expense';
679 select max(child_level) into l_max_level
680 from fii_fin_cat_type_assgns, fii_fin_item_leaf_hiers
681 where top_node_flag = 'Y'
682 and fin_category_id = child_fin_Cat_id
683 and parent_level = child_level
684 and fin_cat_type_code = 'EXP';
685
686 g_phase := 'Updating the aggregate_next_level flag for Expense';
687 update fii_fin_item_leaf_hiers f
688 set aggregate_next_level_flag = 'Y'
689 where aggregate_next_level_flag <> 'Y'
690 and (f.next_level <= l_max_level + 1
691 and f.next_level_fin_cat_id in (select fin_category_id
692 from fii_fin_cat_type_assgns
693 where fin_cat_type_code = 'EXP'));
694
695 IF (FIIDIM_Debug) THEN
696 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
697 END IF;
698
699 g_phase := 'Updating the FC pmv helper table for the newly aggregated nodes';
700 update fii_fc_pmv_agrt_nodes f
701 set aggregated_flag = (select aggregate_next_level_flag
702 from fii_fin_item_leaf_hiers
703 where next_level_fin_cat_id = f.fin_category_id
704 and parent_level = next_level)
705 where aggregated_flag <> (select aggregate_next_level_flag
706 from fii_fin_item_leaf_hiers
707 where next_level_fin_cat_id = f.fin_category_id
708 and parent_level = next_level);
709
710 IF (FIIDIM_Debug) THEN
711 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FC_PMV_AGRT_NODES');
712 END IF;
713
714 g_phase := 'gather_table_stats for FII_FIN_ITEM_LEAF_HIERS';
715 FND_STATS.gather_table_stats
716 (ownname => g_schema_name,
717 tabname => 'FII_FIN_ITEM_LEAF_HIERS');
718
719 -- Bug 4200473. Not to analyze MLOG in incremental run.
720 -- As per performance teams suggestions.
721
722 -- g_phase := 'gather_table_stats MLOG$_FII_FIN_ITEM_LEAF_HI';
723 -- FND_STATS.gather_table_stats
724 -- (ownname => g_schema_name,
725 -- tabname => 'MLOG$_FII_FIN_ITEM_LEAF_HI');
726
727 ELSIF (p_dim_short_name = 'HRI_CL_ORGCC') THEN
728 -- For CC
729 g_phase := 'Updating the aggregate_next_level flag for first two levels in CC';
730 update fii_cost_ctr_hierarchies f
731 set aggregate_next_level_flag = 'Y'
732 where aggregate_next_level_flag <> 'Y'
733 and next_level in (1,2);
734
735
736 IF (FIIDIM_Debug) THEN
737 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COST_CTR_HIERARCHIES');
738 END IF;
739
740 g_phase := 'Updating the Cost Center pmv helper table for the newly aggregated nodes';
741 update fii_cc_pmv_agrt_nodes f
742 set aggregated_flag = (select aggregate_next_level_flag
743 from fii_cost_ctr_hierarchies
744 where next_level_cc_id = f.cost_center_id
745 and parent_level = next_level)
746 where aggregated_flag <> (select aggregate_next_level_flag
747 from fii_cost_ctr_hierarchies
748 where next_level_cc_id = f.cost_center_id
749 and parent_level = next_level);
750
751 IF (FIIDIM_Debug) THEN
752 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_CC_PMV_AGRT_NODES');
753 END IF;
754
755 g_phase := 'gather_table_stats for FII_COST_CTR_HIERARCHIES';
756 FND_STATS.gather_table_stats
757 (ownname => g_schema_name,
758 tabname => 'FII_COST_CTR_HIERARCHIES');
759
760 -- Bug 4200473. Not to analyze MLOG in incremental run.
761 -- As per performance teams suggestions.
762
763 --g_phase := 'gather_table_stats for MLOG$_FII_COST_CTR_HIERARC';
764 --FND_STATS.gather_table_stats
765 -- (ownname => g_schema_name,
766 -- tabname => 'MLOG$_FII_COST_CTR_HIERARC');
767
768 ELSIF (p_dim_short_name = 'FII_COMPANIES') THEN
769 -- For Company
770 g_phase := 'Updating the aggregate_next_level flag for first two levels in Company';
771 update fii_company_hierarchies f
772 set aggregate_next_level_flag = 'Y'
773 where aggregate_next_level_flag <> 'Y'
774 and next_level in (1,2);
775
776 IF (FIIDIM_Debug) THEN
777 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');
778 END IF;
779
780 g_phase := 'Updating the Company pmv helper table for the newly aggregated nodes';
781 update fii_com_pmv_agrt_nodes f
782 set aggregated_flag = (select aggregate_next_level_flag
783 from fii_company_hierarchies
784 where next_level_company_id = f.company_id
785 and parent_level = next_level)
786 where aggregated_flag <> (select aggregate_next_level_flag
787 from fii_company_hierarchies
788 where next_level_company_id = f.company_id
789 and parent_level = next_level);
790
791 IF (FIIDIM_Debug) THEN
792 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COM_PMV_AGRT_NODES');
793 END IF;
794
795 g_phase := 'gather_table_stats for FII_COMPANY_HIERARCHIES';
796 FND_STATS.gather_table_stats
797 (ownname => g_schema_name,
798 tabname => 'FII_COMPANY_HIERARCHIES');
799
800 -- Bug 4200473. Not to analyze MLOG in incremental run.
801 -- As per performance teams suggestions.
802
803 -- g_phase := 'gather_table_stats for MLOG$_FII_COMPANY_HIERARCH';
804 -- FND_STATS.gather_table_stats
805 -- (ownname => g_schema_name,
806 -- tabname => 'MLOG$_FII_COMPANY_HIERARCH');
807
808 ELSIF (p_dim_short_name = 'FII_USER_DEFINED_1') THEN
809 -- For UD1
810 g_phase := 'Updating the aggregate_next_level flag for first two levels in UD1';
811 update fii_udd1_hierarchies f
812 set aggregate_next_level_flag = 'Y'
813 where aggregate_next_level_flag <> 'Y'
814 and next_level in (1,2);
815
816 IF (FIIDIM_Debug) THEN
817 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_UDD1_HIERARCHIES');
818 END IF;
819
820 g_phase := 'Updating the User Defined Dimension 1 pmv helper table for the newly aggregated nodes';
821 update fii_udd1_pmv_agrt_nodes f
822 set aggregated_flag = (select aggregate_next_level_flag
823 from fii_udd1_hierarchies
824 where next_level_value_id = f.udd1_value_id
825 and parent_level = next_level)
826 where aggregated_flag <> (select aggregate_next_level_flag
827 from fii_udd1_hierarchies
828 where next_level_value_id = f.udd1_value_id
829 and parent_level = next_level);
830
831 IF (FIIDIM_Debug) THEN
832 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_UDD1_PMV_AGRT_NODES');
833 END IF;
834
835 g_phase := 'gather_table_stats for FII_UDD1_HIERARCHIES';
836 FND_STATS.gather_table_stats
837 (ownname => g_schema_name,
838 tabname => 'FII_UDD1_HIERARCHIES');
839
840 -- Bug 4200473. Not to analyze MLOG in incremental run.
841 -- As per performance teams suggestions.
842
843 -- g_phase := 'gather_table_stats for MLOG$_FII_UDD1_HIERARCHIES';
844 -- FND_STATS.gather_table_stats
845 -- (ownname => g_schema_name,
846 -- tabname => 'MLOG$_FII_UDD1_HIERARCHIES');
847
848 END IF;
849
850 IF (FIIDIM_Debug) THEN
851 FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.update_hierarchy');
852 END IF;
853
854 END update_hierarchy;
855
856 -- **************************************************************************************
857 -- This is the Update_pruned_table procedure. This procedure updates the pruned dimension
858 -- table for aggregate_next_level_flag
859 -- **************************************************************************************
860
861 Procedure Update_pruned_table (p_dim_short_name varchar2) IS
862 l_max_agrt_level number;
863 BEGIN
864
865 IF (FIIDIM_Debug) THEN
866 FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.Update_pruned_table');
867 END IF;
868
869 -- For FC Dimension
870 IF (p_dim_short_name = 'GL_FII_FIN_ITEM') THEN
871 IF (G_PROGRAM_MODE = 'INCRE') THEN
872 g_phase := 'In IF Update fii_fin_item_leaf_hiers and set the aggregate_next_level_flag';
873 update FII_FIN_ITEM_LEAF_HIERS f
874 set aggregate_next_level_flag = (select aggregated_flag
875 from fii_fc_pmv_agrt_nodes
876 where fin_category_id = f.next_level_fin_cat_id)
877 where aggregate_next_level_flag <> (select aggregated_flag
878 from fii_fc_pmv_agrt_nodes
879 where fin_category_id = f.next_level_fin_cat_id);
880
881 IF (FIIDIM_Debug) THEN
882 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
883 END IF;
884
885 ELSE
886 g_phase := 'In ELSE Update FII_FIN_ITEM_LEAF_HIERS and set the aggregate_next_level_flag';
887 update FII_FIN_ITEM_LEAF_HIERS f
888 set aggregate_next_level_flag = (select aggregated_flag
889 from fii_fc_pmv_agrt_nodes
890 where fin_category_id = f.next_level_fin_cat_id);
891
892 IF (FIIDIM_Debug) THEN
893 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_FIN_ITEM_LEAF_HIERS');
894 END IF;
895
896 END IF;
897
898 -- Bug 4235853
899 --select max(next_level) into l_max_agrt_level
900 --from fii_fin_item_leaf_hiers
901 --where aggregate_next_level_flag = 'Y';
902
903 -- For Company Dimension
904
905 ELSIF (p_dim_short_name = 'FII_COMPANIES') THEN
906 IF (G_PROGRAM_MODE = 'INCRE') THEN
907 g_phase := 'In IF Update fii_company_hierarchies and set the aggregate_next_level_flag';
908 update FII_COMPANY_HIERARCHIES f
909 set aggregate_next_level_flag = (select aggregated_flag
910 from fii_com_pmv_agrt_nodes
911 where company_id = f.next_level_company_id)
912 where aggregate_next_level_flag <> (select aggregated_flag
913 from fii_com_pmv_agrt_nodes
914 where company_id = f.next_level_company_id);
915
916 IF (FIIDIM_Debug) THEN
917 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');
918 END IF;
919
920 ELSE
921 g_phase := 'In ELSE Update fii_company_hierarchies and set the aggregate_next_level_flag';
922 update fii_company_hierarchies f
923 set aggregate_next_level_flag = (select aggregated_flag
924 from fii_com_pmv_agrt_nodes
925 where company_id = f.next_level_company_id);
926
927 IF (FIIDIM_Debug) THEN
928 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COMPANY_HIERARCHIES');
929 END IF;
930
931 END IF;
932
933 select max(next_level) into l_max_agrt_level
934 from fii_company_hierarchies
935 where aggregate_next_level_flag = 'Y';
936
937 -- For CC Dimension
938
939 ELSIF (p_dim_short_name = 'HRI_CL_ORGCC') THEN
940 IF (G_PROGRAM_MODE = 'INCRE') THEN
941 g_phase := 'In IF Update fii_cost_ctr_hierarchies and set the aggregate_next_level_flag';
942 update fii_cost_ctr_hierarchies f
943 set aggregate_next_level_flag = (select aggregated_flag
944 from fii_cc_pmv_agrt_nodes
945 where cost_center_id = f.next_level_cc_id)
946 where aggregate_next_level_flag <> (select aggregated_flag
947 from fii_cc_pmv_agrt_nodes
948 where cost_center_id = f.next_level_cc_id);
949
950 IF (FIIDIM_Debug) THEN
951 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COST_CTR_HIERARCHIES');
952 END IF;
953
954 ELSE
955 g_phase := 'In ELSE Update fii_cost_ctr_hierarchies and set the aggregate_next_level_flag';
956 update fii_cost_ctr_hierarchies f
957 set aggregate_next_level_flag = (select aggregated_flag
958 from fii_cc_pmv_agrt_nodes
959 where cost_center_id = f.next_level_cc_id);
960
961 IF (FIIDIM_Debug) THEN
962 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_COST_CTR_HIERARCHIES');
963 END IF;
964
965 END IF;
966
967 select max(next_level) into l_max_agrt_level
968 from fii_cost_ctr_hierarchies
969 where aggregate_next_level_flag = 'Y';
970
971 -- For UDD1 Dimension
972
973 ELSIF (p_dim_short_name = 'FII_USER_DEFINED_1') THEN
974 IF (G_PROGRAM_MODE = 'INCRE') THEN
975 g_phase := 'In IF Update fii_udd1_hierarchies and set the aggregate_next_level_flag';
976 update fii_udd1_hierarchies f
977 set aggregate_next_level_flag = (select aggregated_flag
978 from fii_udd1_pmv_agrt_nodes
979 where UDD1_VALUE_ID = f.next_level_value_id)
980 where aggregate_next_level_flag <> (select aggregated_flag
981 from fii_udd1_pmv_agrt_nodes
982 where UDD1_VALUE_ID = f.next_level_value_id);
983
984 IF (FIIDIM_Debug) THEN
985 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_UDD1_HIERARCHIES');
986 END IF;
987
988 ELSE
989 g_phase := 'In ELSE Update fii_udd1_hierarchies and set the aggregate_next_level_flag';
990 update fii_udd1_hierarchies f
991 set aggregate_next_level_flag = (select aggregated_flag
992 from fii_udd1_pmv_agrt_nodes
993 where UDD1_VALUE_ID = f.next_level_value_id);
994
995 IF (FIIDIM_Debug) THEN
996 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows into FII_UDD1_HIERARCHIES');
997 END IF;
998
999 END IF;
1000
1001 select max(next_level) into l_max_agrt_level
1002 from fii_udd1_hierarchies
1003 where aggregate_next_level_flag = 'Y';
1004
1005 END IF;
1006
1007 -- Bug 4235853
1008 IF(p_dim_short_name = 'GL_FII_FIN_ITEM') THEN
1009 -- Treat FC differently since it can have top nodes at deeper levels as well
1010 -- This needs to be called always
1011 update_hierarchy(p_dim_short_name);
1012 ELSE
1013 IF(l_max_agrt_level < 2) THEN
1014 -- Call update_hierarchy to update the first two levels always
1015 -- if not already updated
1016 g_phase := 'Calling update_hierarchy';
1017 update_hierarchy(p_dim_short_name);
1018 END IF;
1019 END IF;
1020
1021 IF (FIIDIM_Debug) THEN
1022 FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.Update_pruned_table');
1023 END IF;
1024
1025 EXCEPTION
1026 When others then
1027 FND_CONCURRENT.Af_Rollback;
1028 FII_UTIL.Write_Log ('Unexpected error when calling Update_pruned_table...');
1029 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1030 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
1031 raise;
1032
1033 END Update_pruned_table;
1034
1035 -- *************************************************************************************
1036 -- This is the update_viewby_flag procedure. This procedure updates the for_viewby_flag
1037 -- in the pmv helper tables of all the dimensions
1038 -- *************************************************************************************
1039
1040 Procedure update_viewby_flag (p_dim_short_name varchar2) IS
1041 BEGIN
1042
1043 IF (FIIDIM_Debug) THEN
1044 FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.update_viewby_flag');
1045 END IF;
1046
1047 -- For FC Dimension
1048 IF (p_dim_short_name = 'GL_FII_FIN_ITEM') THEN
1049
1050 g_phase := 'Update FII_FC_PMV_AGRT_NODES for Financial Category';
1051
1052 UPDATE FII_FC_PMV_AGRT_NODES
1053 set for_viewby_flag = 'Y'
1054 WHERE fin_category_id in ( select next_level_fin_cat_id
1055 from FII_FIN_ITEM_LEAF_HIERS f1
1056 where (f1.is_leaf_flag = 'Y' and f1.aggregate_next_level_flag = 'Y')
1057 or exists ( select aggregate_next_level_flag
1058 from FII_FIN_ITEM_LEAF_HIERS f2
1059 where f1.next_level_fin_cat_id = f2.parent_fin_cat_id
1060 and f2.aggregate_next_level_flag = 'Y'
1061 and f2.parent_fin_cat_id <> f2.next_level_fin_cat_id));
1062 IF (FIIDIM_Debug) THEN
1063 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_FC_PMV_AGRT_NODES');
1064 END IF;
1065
1066
1067 ELSIF (p_dim_short_name = 'FII_COMPANIES') THEN
1068 g_phase := 'Update FII_COM_PMV_AGRT_NODES for Company';
1069
1070 UPDATE FII_COM_PMV_AGRT_NODES
1071 set for_viewby_flag = 'Y'
1072 WHERE company_id in ( select next_level_company_id
1073 from FII_COMPANY_HIERARCHIES f1
1074 where (f1.is_leaf_flag = 'Y' and f1.aggregate_next_level_flag = 'Y')
1075 or exists ( select aggregate_next_level_flag
1076 from FII_COMPANY_HIERARCHIES f2
1077 where f1.next_level_company_id = f2.parent_company_id
1078 and f2.aggregate_next_level_flag = 'Y'
1079 and f2.parent_company_id <> f2.next_level_company_id));
1080
1081 IF (FIIDIM_Debug) THEN
1082 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_PMV_AGRT_NODES');
1083 END IF;
1084
1085
1086 ELSIF (p_dim_short_name = 'HRI_CL_ORGCC') THEN
1087 g_phase := 'Update FII_CC_PMV_AGRT_NODES for Cost Center';
1088
1089 UPDATE FII_CC_PMV_AGRT_NODES
1090 set for_viewby_flag = 'Y'
1091 WHERE cost_center_id in ( select next_level_cc_id
1092 from FII_COST_CTR_HIERARCHIES f1
1093 where (f1.is_leaf_flag = 'Y' and f1.aggregate_next_level_flag = 'Y')
1094 or exists ( select aggregate_next_level_flag
1095 from FII_COST_CTR_HIERARCHIES f2
1096 where f1.next_level_cc_id = f2.parent_cc_id
1097 and f2.aggregate_next_level_flag = 'Y'
1098 and f2.parent_cc_id <> f2.next_level_cc_id));
1099
1100 IF (FIIDIM_Debug) THEN
1101 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CC_PMV_AGRT_NODES');
1102 END IF;
1103
1104 ELSIF (p_dim_short_name = 'FII_USER_DEFINED_1') THEN
1105 g_phase := 'Update FII_UDD1_PMV_AGRT_NODES for User Defined Dimension1';
1106
1107 UPDATE FII_UDD1_PMV_AGRT_NODES
1108 set for_viewby_flag = 'Y'
1109 WHERE udd1_value_id in ( select next_level_value_id
1110 from FII_UDD1_HIERARCHIES f1
1111 where (f1.is_leaf_flag = 'Y' and f1.aggregate_next_level_flag = 'Y')
1112 or exists ( select aggregate_next_level_flag
1113 from FII_UDD1_HIERARCHIES f2
1114 where f1.next_level_value_id = f2.parent_value_id
1115 and f2.aggregate_next_level_flag = 'Y'
1116 and f2.parent_value_id <> f2.next_level_value_id));
1117
1118 IF (FIIDIM_Debug) THEN
1119 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_UDD1_PMV_AGRT_NODES');
1120 END IF;
1121
1122 END IF;
1123
1124 IF (FIIDIM_Debug) THEN
1125 FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.update_viewby_flag');
1126 END IF;
1127
1128 EXCEPTION
1129 When others then
1130 FND_CONCURRENT.Af_Rollback;
1131 FII_UTIL.Write_Log ('Unexpected error when calling update_viewby_flag...');
1132 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1133 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
1134 raise;
1135
1136 END update_viewby_flag;
1137
1138
1139 -- ******************************************************************************************
1140 -- This is the Populate_PMV_Helper_GT procedure. This procedure populates helper tables of
1141 -- all the dimensions.
1142 -- ******************************************************************************************
1143 PROCEDURE Populate_PMV_Helper_GT (p_dim_short_name varchar2,
1144 p_number_of_nodes number, gain out nocopy number) IS
1145 l_subtree_freq number;
1146 l_row_number number;
1147 l_nodes_aggregated number;
1148 l_bool varchar2(1) := 'N';
1149
1150 BEGIN
1151
1152 IF (FIIDIM_Debug) THEN
1153 FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.Populate_PMV_Helper_GT');
1154 END IF;
1155
1156 l_nodes_aggregated := G_OPTIMUM_NODES;
1157
1158 IF (p_number_of_nodes <= G_OPTIMUM_NODES) THEN
1159 -- In this case all the nodes will be aggregated
1160 -- For FC Dimension
1161 IF (p_dim_short_name = 'GL_FII_FIN_ITEM') THEN
1162 g_phase := 'Inserting into fii_fc_pmv_agrt_nodes for FC';
1163 Insert into fii_fc_pmv_agrt_nodes(FIN_CATEGORY_ID,
1164 for_viewby_flag,
1165 aggregated_flag,
1166 LAST_UPDATE_DATE,
1167 LAST_UPDATED_BY,
1168 CREATION_DATE,
1169 CREATED_BY ,
1170 LAST_UPDATE_LOGIN)
1171 (select next_level_fin_cat_id, 'Y','Y',
1172 SYSDATE,
1173 FII_USER_ID,
1174 SYSDATE,
1175 FII_USER_ID,
1176 FII_LOGIN_ID
1177 from FII_FIN_ITEM_LEAF_HIERS
1178 where parent_level= next_level
1179 ) ;
1180
1181 IF (FIIDIM_Debug) THEN
1182 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FC_PMV_AGRT_NODES');
1183 END IF;
1184 --For Company Dimension
1185 ELSIF (p_dim_short_name = 'FII_COMPANIES') THEN
1186 g_phase := 'Inserting into fii_com_pmv_agrt_nodes for Company';
1187 Insert into fii_com_pmv_agrt_nodes(COMPANY_ID,
1188 for_viewby_flag,
1189 aggregated_flag,
1190 LAST_UPDATE_DATE ,
1191 LAST_UPDATED_BY ,
1192 CREATION_DATE ,
1193 CREATED_BY ,
1194 LAST_UPDATE_LOGIN)
1195 (select next_level_company_id, 'Y','Y',
1196 SYSDATE,
1197 FII_USER_ID,
1198 SYSDATE,
1199 FII_USER_ID,
1200 FII_LOGIN_ID
1201 from fii_company_hierarchies
1202 where parent_level= next_level
1203 );
1204
1205 IF (FIIDIM_Debug) THEN
1206 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_PMV_AGRT_NODES');
1207 END IF;
1208
1209 --For CC Dimension
1210 ELSIF (p_dim_short_name = 'HRI_CL_ORGCC') THEN
1211 g_phase := 'Inserting into fii_cc_pmv_agrt_nodes for CC';
1212 Insert into fii_cc_pmv_agrt_nodes(COST_CENTER_ID,
1213 for_viewby_flag,
1214 aggregated_flag,
1215 LAST_UPDATE_DATE ,
1216 LAST_UPDATED_BY,
1217 CREATION_DATE,
1218 CREATED_BY,
1219 LAST_UPDATE_LOGIN)
1220 (select next_level_cc_id, 'Y','Y',
1221 SYSDATE,
1222 FII_USER_ID,
1223 SYSDATE,
1224 FII_USER_ID,
1225 FII_LOGIN_ID
1226 from fii_cost_ctr_hierarchies
1227 where parent_level= next_level
1228 ) ;
1229
1230 IF (FIIDIM_Debug) THEN
1231 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CC_PMV_AGRT_NODES');
1232 END IF;
1233
1234 --For UDD1
1235 ELSIF (p_dim_short_name = 'FII_USER_DEFINED_1') THEN
1236 g_phase := 'Inserting into fii_udd1_pmv_agrt_nodes for UDD1';
1237 Insert into fii_udd1_pmv_agrt_nodes(UDD1_VALUE_ID,
1238 for_viewby_flag,
1239 aggregated_flag,
1240 LAST_UPDATE_DATE ,
1241 LAST_UPDATED_BY,
1242 CREATION_DATE,
1243 CREATED_BY,
1244 LAST_UPDATE_LOGIN)
1245 (select next_level_value_id, 'Y','Y',
1246 SYSDATE,
1247 FII_USER_ID,
1248 SYSDATE,
1249 FII_USER_ID,
1250 FII_LOGIN_ID
1251 from fii_udd1_hierarchies
1252 where parent_level= next_level
1253 ) ;
1254
1255 IF (FIIDIM_Debug) THEN
1256 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD1_PMV_AGRT_NODES');
1257 END IF;
1258
1259 END IF;
1260
1261 ELSE
1262 -- Insert the records which will be aggregated the pmv helper table
1263 g_phase := 'Getting the minimum frequency for which we want to aggregate';
1264 SELECT MIN (no_of_children), MAX (rn)
1265 INTO l_subtree_freq, l_row_number
1266 FROM (SELECT no_of_children,
1267 row_number() over (order by NO_OF_CHILDREN desc) rn
1268 FROM FII_AGGRT_NODE_GT
1269 WHERE dim_short_name = p_dim_short_name)
1270 WHERE rn < = G_OPTIMUM_NODES;
1271
1272 BEGIN
1273 -- Check if we need to add 1 to l_subtree_freq or not
1274 g_phase := 'Checking if we need to add 1 to the minimum frequency';
1275 SELECT 'Y' into l_bool
1276 FROM (SELECT no_of_children,
1277 row_number() over (order by NO_OF_CHILDREN desc) rn
1278 FROM FII_AGGRT_NODE_GT
1279 WHERE dim_short_name = p_dim_short_name)
1280 WHERE rn > l_row_number
1281 AND no_of_children = l_subtree_freq
1282 AND rownum <2;
1283
1284 EXCEPTION
1285 WHEN NO_DATA_FOUND THEN
1286 NULL;
1287 END;
1288
1289 IF l_bool = 'Y' or l_subtree_freq = 0 THEN
1290 l_subtree_freq := l_subtree_freq + 1;
1291 END IF;
1292
1293 IF (p_dim_short_name = 'GL_FII_FIN_ITEM') THEN
1294
1295 g_phase := 'Inserting into fii_fc_pmv_agrt_nodes from fii_aggrt_node_gt';
1296 Insert into FII_FC_PMV_AGRT_NODES(FIN_CATEGORY_ID,
1297 aggregated_flag,
1298 for_viewby_flag,
1299 LAST_UPDATE_DATE ,
1300 LAST_UPDATED_BY,
1301 CREATION_DATE,
1302 CREATED_BY,
1303 LAST_UPDATE_LOGIN)
1304
1305 select ID, 'Y', 'N',
1306 SYSDATE,
1307 FII_USER_ID,
1308 SYSDATE,
1309 FII_USER_ID,
1310 FII_LOGIN_ID
1311 FROM FII_AGGRT_NODE_GT
1312 where dim_short_name = p_dim_short_name
1313 AND no_of_children >= l_subtree_freq;
1314
1315 IF (FIIDIM_Debug) THEN
1316 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FC_PMV_AGRT_NODES');
1317 END IF;
1318 ELSIF (p_dim_short_name = 'FII_COMPANIES') THEN
1319
1320 g_phase := 'Inserting into fii_com_pmv_agrt_nodes from fii_aggrt_node_gt';
1321 Insert into FII_COM_PMV_AGRT_NODES(COMPANY_ID,
1322 aggregated_flag,
1323 for_viewby_flag,
1324 LAST_UPDATE_DATE ,
1325 LAST_UPDATED_BY,
1326 CREATION_DATE,
1327 CREATED_BY,
1328 LAST_UPDATE_LOGIN)
1329
1330 select ID, 'Y', 'N',
1331 SYSDATE,
1332 FII_USER_ID,
1333 SYSDATE,
1334 FII_USER_ID,
1335 FII_LOGIN_ID
1336 FROM FII_AGGRT_NODE_GT
1337 where dim_short_name = p_dim_short_name
1338 AND no_of_children >= l_subtree_freq;
1339
1340 IF (FIIDIM_Debug) THEN
1341 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_PMV_AGRT_NODES');
1342 END IF;
1343
1344 ELSIF (p_dim_short_name = 'HRI_CL_ORGCC') THEN
1345
1346 g_phase := 'Inserting into fii_cc_pmv_agrt_nodes from fii_aggrt_node_gt';
1347 Insert into FII_CC_PMV_AGRT_NODES(COST_CENTER_ID,
1348 aggregated_flag,
1349 for_viewby_flag,
1350 LAST_UPDATE_DATE ,
1351 LAST_UPDATED_BY,
1352 CREATION_DATE,
1353 CREATED_BY,
1354 LAST_UPDATE_LOGIN)
1355
1356 select ID, 'Y', 'N',
1357 SYSDATE,
1358 FII_USER_ID,
1359 SYSDATE,
1360 FII_USER_ID,
1361 FII_LOGIN_ID
1362 FROM FII_AGGRT_NODE_GT
1363 where dim_short_name = p_dim_short_name
1364 AND no_of_children >= l_subtree_freq;
1365
1366 IF (FIIDIM_Debug) THEN
1367 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CC_PMV_AGRT_NODES');
1368 END IF;
1369
1370 ELSIF (p_dim_short_name = 'FII_USER_DEFINED_1') THEN
1371
1372 g_phase := 'Inserting into fii_udd1_pmv_agrt_nodes from fii_aggrt_node_gt';
1373 Insert into FII_UDD1_PMV_AGRT_NODES(UDD1_VALUE_ID,
1374 aggregated_flag,
1375 for_viewby_flag,
1376 LAST_UPDATE_DATE ,
1377 LAST_UPDATED_BY,
1378 CREATION_DATE,
1379 CREATED_BY,
1380 LAST_UPDATE_LOGIN)
1381
1382 select ID, 'Y', 'N',
1383 SYSDATE,
1384 FII_USER_ID,
1385 SYSDATE,
1386 FII_USER_ID,
1387 FII_LOGIN_ID
1388 FROM FII_AGGRT_NODE_GT
1389 where dim_short_name = p_dim_short_name
1390 AND no_of_children >= l_subtree_freq;
1391
1392 IF (FIIDIM_Debug) THEN
1393 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD1_PMV_AGRT_NODES');
1394 END IF;
1395 END IF;
1396
1397 -- Insert the records which will not be aggregated
1398 -- For FC Dimension.
1399
1400 IF (p_dim_short_name = 'GL_FII_FIN_ITEM') THEN
1401 g_phase := 'Inserting records which will not be aggregated into fii_fc_pmv_agrt_nodes for FC';
1402 Insert into fii_fc_pmv_agrt_nodes(FIN_CATEGORY_ID, for_viewby_flag,
1403 aggregated_flag,
1404 LAST_UPDATE_DATE ,
1405 LAST_UPDATED_BY,
1406 CREATION_DATE,
1407 CREATED_BY,
1408 LAST_UPDATE_LOGIN)
1409 (select next_level_fin_cat_id, 'N','N',
1410 SYSDATE,
1411 FII_USER_ID,
1412 SYSDATE,
1413 FII_USER_ID,
1414 FII_LOGIN_ID
1415 from FII_FIN_ITEM_LEAF_HIERS
1416 where parent_level= next_level
1417 minus
1418 select fin_category_id, 'N', 'N',
1419 SYSDATE,
1420 FII_USER_ID,
1421 SYSDATE,
1422 FII_USER_ID,
1423 FII_LOGIN_ID
1424 from fii_fc_pmv_agrt_nodes);
1425
1426 IF (FIIDIM_Debug) THEN
1427 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_FC_PMV_AGRT_NODES');
1428 END IF;
1429
1430 --For Company Dimension
1431 ELSIF (p_dim_short_name = 'FII_COMPANIES') THEN
1432
1433 g_phase := 'Inserting records which will not be aggregated into fii_com_pmv_agrt_nodes for Company';
1434 Insert into fii_com_pmv_agrt_nodes(company_id, for_viewby_flag,
1435 aggregated_flag,
1436 LAST_UPDATE_DATE ,
1437 LAST_UPDATED_BY,
1438 CREATION_DATE,
1439 CREATED_BY,
1440 LAST_UPDATE_LOGIN)
1441 (select next_level_company_id, 'N','N',
1442 SYSDATE,
1443 FII_USER_ID,
1444 SYSDATE,
1445 FII_USER_ID,
1446 FII_LOGIN_ID
1447 from fii_company_hierarchies
1448 where parent_level= next_level
1449 minus
1450 select company_id, 'N', 'N',
1451 SYSDATE,
1452 FII_USER_ID,
1453 SYSDATE,
1454 FII_USER_ID,
1455 FII_LOGIN_ID
1456 from fii_com_pmv_agrt_nodes);
1457
1458 IF (FIIDIM_Debug) THEN
1459 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_PMV_AGRT_NODES');
1460 END IF;
1461
1462 --For CC Dimension
1463 ELSIF (p_dim_short_name = 'HRI_CL_ORGCC') THEN
1464
1465 g_phase := 'Inserting records which will not be aggregated into fii_cc_pmv_agrt_nodes_gt for Cost Center';
1466 Insert into fii_cc_pmv_agrt_nodes(cost_center_id, for_viewby_flag,
1467 aggregated_flag,
1468 LAST_UPDATE_DATE ,
1469 LAST_UPDATED_BY,
1470 CREATION_DATE,
1471 CREATED_BY,
1472 LAST_UPDATE_LOGIN)
1473 (select next_level_cc_id, 'N','N',
1474 SYSDATE,
1475 FII_USER_ID,
1476 SYSDATE,
1477 FII_USER_ID,
1478 FII_LOGIN_ID
1479 from fii_cost_ctr_hierarchies
1480 where parent_level= next_level
1481 minus
1482 select cost_center_id, 'N', 'N',
1483 SYSDATE,
1484 FII_USER_ID,
1485 SYSDATE,
1486 FII_USER_ID,
1487 FII_LOGIN_ID
1488 from fii_cc_pmv_agrt_nodes) ;
1489
1490 IF (FIIDIM_Debug) THEN
1491 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CC_PMV_AGRT_NODES');
1492 END IF;
1493
1494 --For UDD1
1495 ELSIF (p_dim_short_name = 'FII_USER_DEFINED_1') THEN
1496
1497 g_phase := 'Inserting records which will not be aggregated into fii_udd1_pmv_agrt_nodes for UDD1';
1498 Insert into fii_udd1_pmv_agrt_nodes(UDD1_VALUE_ID, for_viewby_flag,
1499 aggregated_flag,
1500 LAST_UPDATE_DATE ,
1501 LAST_UPDATED_BY,
1502 CREATION_DATE,
1503 CREATED_BY,
1504 LAST_UPDATE_LOGIN)
1505 (select next_level_value_id, 'N','N',
1506 SYSDATE,
1507 FII_USER_ID,
1508 SYSDATE,
1509 FII_USER_ID,
1510 FII_LOGIN_ID
1511 from fii_udd1_hierarchies
1512 where parent_level= next_level
1513 minus
1514 select udd1_value_id, 'N', 'N',
1515 SYSDATE,
1516 FII_USER_ID,
1517 SYSDATE,
1518 FII_USER_ID,
1519 FII_LOGIN_ID
1520 from fii_udd1_pmv_agrt_nodes) ;
1521
1522 IF (FIIDIM_Debug) THEN
1523 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_UDD1_PMV_AGRT_NODES');
1524 END IF;
1525
1526 END IF;
1527
1528 END IF;
1529
1530 g_phase := 'Update the Pruned hierarchy table of the dimension';
1531 Update_pruned_table(p_dim_short_name);
1532
1533 g_phase := 'Update the viewby flag in the PMV Helper table of the dimension';
1534 -- Update for view by flag only if required
1535 IF (p_number_of_nodes > G_OPTIMUM_NODES) THEN
1536 update_viewby_flag(p_dim_short_name);
1537 END IF;
1538
1539 --Finally calculate the gain for the larger dimensions
1540 g_phase := 'Calculate the gain if required for the next dimension';
1541
1542 IF(G_KEEP_GAIN_FLAG='Y') THEN
1543
1544 IF(p_dim_short_name = 'GL_FII_FIN_ITEM') THEN
1545 SELECT count(*) into l_nodes_aggregated
1546 FROM FII_FC_PMV_AGRT_NODES
1547 WHERE aggregated_flag = 'Y';
1548 ELSIF(p_dim_short_name = 'FII_COMPANIES') THEN
1549 SELECT count(*) into l_nodes_aggregated
1550 FROM FII_COM_PMV_AGRT_NODES
1551 WHERE aggregated_flag = 'Y';
1552 ELSIF(p_dim_short_name = 'HRI_CL_ORGCC') THEN
1553 SELECT count(*) into l_nodes_aggregated
1554 FROM FII_CC_PMV_AGRT_NODES
1555 WHERE aggregated_flag = 'Y';
1556 ELSIF(p_dim_short_name = 'FII_USER_DEFINED_1') THEN
1557 SELECT count(*) into l_nodes_aggregated
1558 FROM FII_UDD1_PMV_AGRT_NODES
1559 WHERE aggregated_flag = 'Y';
1560 END IF;
1561
1562 -- this case won't arise but to avoid a zero divisor in the next step
1563 IF(l_nodes_aggregated = 0) THEN
1564 l_nodes_aggregated := 1;
1565 END IF;
1566
1567 IF(l_nodes_aggregated > G_OPTIMUM_NODES) THEN
1568 -- This is because if the no of nodes to be aggregated is less then also
1569 -- we always aggregate 2 levels atleast. In such a case the no of
1570 -- nodes actually aggregated is large than the no to be aggregated
1571 -- and so G_OPTIMUM_NODES / l_nodes_aggregated would return 0 in that case
1572 -- which is not required as this would set the gain to be 0 for next dimensions.
1573 l_nodes_aggregated := G_OPTIMUM_NODES;
1574 END IF;
1575
1576 END IF;
1577
1578 gain := G_OPTIMUM_NODES / l_nodes_aggregated ;
1579
1580 IF (FIIDIM_Debug) THEN
1581 FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.Populate_PMV_Helper_GT');
1582 END If;
1583
1584 EXCEPTION
1585 When others then
1586 FND_CONCURRENT.Af_Rollback;
1587 FII_UTIL.Write_Log ('Unexpected error when calling Populate_PMV_Helper_GT...');
1588 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1589 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
1590 raise;
1591
1592 END Populate_PMV_Helper_GT;
1593
1594 -- *************************************************************************************
1595 -- This is the truncate_helper_tables procedure. This is to truncate all the helper
1596 -- tables.
1597 -- *************************************************************************************
1598
1599 Procedure truncate_helper_tables IS
1600 PMVH_lock_err EXCEPTION;
1601 BEGIN
1602
1603 IF (FIIDIM_Debug) THEN
1604 FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.truncate_helper_tables');
1605 END IF;
1606
1607 g_phase := 'Truncate all the helper tables';
1608 FII_UTIL.truncate_table ('FII_FC_PMV_AGRT_NODES', 'FII', g_retcode);
1609 IF(g_retcode = '-1') THEN
1610 raise PMVH_lock_err;
1611 END IF;
1612
1613 FII_UTIL.truncate_table ('FII_COM_PMV_AGRT_NODES', 'FII', g_retcode);
1614 IF(g_retcode = '-1') THEN
1615 raise PMVH_lock_err;
1616 END IF;
1617
1618 FII_UTIL.truncate_table ('FII_CC_PMV_AGRT_NODES', 'FII', g_retcode);
1619 IF(g_retcode = '-1') THEN
1620 raise PMVH_lock_err;
1621 END IF;
1622
1623 FII_UTIL.truncate_table ('FII_UDD1_PMV_AGRT_NODES','FII', g_retcode);
1624 IF(g_retcode = '-1') THEN
1625 raise PMVH_lock_err;
1626 END IF;
1627
1628 IF (FIIDIM_Debug) THEN
1629 FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.truncate_helper_tables');
1630 END If;
1631
1632 EXCEPTION
1633 WHEN PMVH_lock_err THEN
1634 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1635 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
1636 FII_MESSAGE.Func_Fail(func_name => 'FII_PMV_HELPER_TABLES_C.'||
1637 'truncate_helper_tables');
1638 raise;
1639
1640 WHEN OTHERS THEN
1641 FII_UTIL.Write_Log ( 'G_PHASE: ' || G_PHASE);
1642 FII_UTIL.Write_Log ('Error Message: '|| substr(sqlerrm,1,180));
1643 FII_MESSAGE.Func_Fail(func_name => 'FII_PMV_HELPER_TABLES_C.'||
1644 'truncate_helper_tables');
1645 raise;
1646
1647 END;
1648
1649 -- *************************************************************************************
1650 -- This is the Dim_number_nodes procedure. This is to call the populate_pmv_helper_gt
1651 -- procedure with the number of nodes in the dimension
1652 -- *************************************************************************************
1653
1654 Procedure Dim_number_nodes IS
1655 BEGIN
1656
1657 IF (FIIDIM_Debug) THEN
1658 FII_MESSAGE.Func_Ent ('FII_PMV_HELPER_TABLES_C.Dim_number_nodes');
1659 END IF;
1660
1661 -- First truncate all the helper tables.
1662 g_phase := 'Truncate all the helper tables';
1663 truncate_helper_tables;
1664
1665 -- G_NODES is equal to the number of nodes calculated in
1666 -- initialize.
1667
1668 -- For the smallest dimension
1669 g_phase := 'Processing for the smallest Dimension';
1670 G_OPTIMUM_NODES := G_NODES;
1671
1672 IF (FIIDIM_Debug) THEN
1673 FII_UTIL.Write_Log('No. of Nodes which can be aggregated in ' || Dimension_name(p_tab(1).dim_short_name) || ' is : ' || G_OPTIMUM_NODES );
1674 END IF;
1675
1676 If(p_tab(1).number_of_nodes< G_OPTIMUM_NODES) THEN
1677 g_keep_gain_flag := 'N';
1678 Populate_PMV_Helper_GT (p_tab(1).dim_short_name, p_tab(1).number_of_nodes,p_tab(1).gain);
1679 p_tab(1).gain := G_OPTIMUM_NODES / p_tab(1).number_of_nodes;
1680 ELSE
1681 g_keep_gain_flag := 'Y';
1682 Populate_PMV_Helper_GT (p_tab(1).dim_short_name,
1683 p_tab(1).number_of_nodes,
1684 p_tab(1).gain);
1685 End if;
1686
1687 IF (FIIDIM_Debug) THEN
1688 FII_UTIL.Write_Log('No. of Nodes which can be aggregated in ' || Dimension_name(p_tab(2).dim_short_name) || ' is : ' || G_OPTIMUM_NODES );
1689 END IF;
1690 g_phase := 'Processing for the second smallest Dimension';
1691 -- For the second smallest dimension
1692 If(p_tab(2).number_of_nodes < G_OPTIMUM_NODES) THEN
1693 g_keep_gain_flag := 'N';
1694 Populate_PMV_Helper_GT (p_tab(2).dim_short_name,
1695 p_tab(2).number_of_nodes,
1696 p_tab(2).gain);
1697
1698 p_tab(2).gain := G_OPTIMUM_NODES / p_tab(2).number_of_nodes;
1699 ELSE
1700 g_keep_gain_flag := 'Y';
1701 Populate_PMV_Helper_GT (p_tab(2).dim_short_name,
1702 p_tab(2).number_of_nodes,
1703 p_tab(2).gain);
1704 End if;
1705
1706 -- For the second largest dimension
1707 g_phase := 'Processing for the second Largest Dimension';
1708
1709 G_OPTIMUM_NODES := ROUND(G_NODES * p_tab(2).gain);
1710
1711 IF (FIIDIM_Debug) THEN
1712 FII_UTIL.Write_Log('No. of Nodes which can be aggregated in ' || Dimension_name(p_tab(3).dim_short_name) || ' is : ' || G_OPTIMUM_NODES );
1713 END IF;
1714
1715 If(p_tab(3).number_of_nodes < G_OPTIMUM_NODES) THEN
1716 g_keep_gain_flag := 'N';
1717 Populate_PMV_Helper_GT (p_tab(3).dim_short_name,
1718 p_tab(3).number_of_nodes,
1719 p_tab(3).gain);
1720
1721 p_tab(3).gain := G_OPTIMUM_NODES / p_tab(3).number_of_nodes;
1722 ELSE
1723 g_keep_gain_flag := 'Y';
1724 Populate_PMV_Helper_GT (p_tab(3).dim_short_name,
1725 p_tab(3).number_of_nodes,
1726 p_tab(3).gain);
1727 End if;
1728
1729
1730 -- For the largest dimension
1731 g_phase := 'Processing for the largest Dimension';
1732 G_OPTIMUM_NODES := ROUND(G_NODES * p_tab(3).gain *p_tab(1).gain);
1733
1734 IF (FIIDIM_Debug) THEN
1735 FII_UTIL.Write_Log('No. of Nodes which can be aggregated in ' || Dimension_name(p_tab(4).dim_short_name) || ' is : ' || G_OPTIMUM_NODES );
1736 END IF;
1737
1738 If(p_tab(4).number_of_nodes < G_OPTIMUM_NODES) THEN
1739 g_keep_gain_flag := 'N';
1740 Populate_PMV_Helper_GT (p_tab(4).dim_short_name,
1741 p_tab(4).number_of_nodes,
1742 p_tab(4).gain);
1743
1744
1745 ELSE
1746 g_keep_gain_flag := 'Y';
1747 Populate_PMV_Helper_GT (p_tab(4).dim_short_name,
1748 p_tab(4).number_of_nodes,
1749 p_tab(4).gain);
1750 End if;
1751
1752 IF (FIIDIM_Debug) THEN
1753 FII_MESSAGE.Func_Succ ('FII_PMV_HELPER_TABLES_C.Dim_number_nodes');
1754 END IF;
1755
1756 EXCEPTION
1757 WHEN OTHERS THEN
1758 raise;
1759
1760 END Dim_number_nodes;
1761
1762 -- *************************************************************************************
1763 -- This is the main procedure of PMV Helper Table population program (initial populate).
1764 -- *************************************************************************************
1765
1766 PROCEDURE Load_Main (errbuf OUT NOCOPY VARCHAR2,
1767 retcode OUT NOCOPY VARCHAR2,
1768 p_load_mode IN VARCHAR2) IS
1769
1770 ret_val BOOLEAN := FALSE;
1771
1772 BEGIN
1773
1774 IF (FIIDIM_Debug) THEN
1775 FII_MESSAGE.Func_Ent(func_name => 'FII_PMV_HELPER_TABLES_C.Load_Main');
1776 END IF;
1777
1778 g_phase := 'Set the Load mode';
1779 G_PROGRAM_MODE := p_load_mode;
1780
1781 --First do the initialization
1782 g_phase := 'Calling Initialize';
1783 Initialize;
1784
1785 --Secondly populate the table FII_AGGRT_NODE_GT
1786 g_phase := 'Populate FII_AGGRT_NODE_GT table' ;
1787 populate_temp;
1788
1789 --Calling Calculate_Sort_Nodes
1790 g_phase := 'Calling Calculate_sort_nodes';
1791 calculate_sort_nodes;
1792
1793 Dim_number_nodes;
1794
1795 -- Gather table statistics
1796 -- This will be by seeding in RSG
1797 -- Need to remove this when RSG SEED data is done.
1798 FND_STATS.gather_table_stats
1799 (ownname => g_schema_name,
1800 tabname => 'FII_FC_PMV_AGRT_NODES');
1801
1802 g_phase := 'gather_table_stats for FII_COM_PMV_AGRT_NODES';
1803
1804 FND_STATS.gather_table_stats
1805 (ownname => g_schema_name,
1806 tabname => 'FII_COM_PMV_AGRT_NODES');
1807
1808 g_phase := 'gather_table_stats for FII_CC_PMV_AGRT_NODES';
1809
1810 FND_STATS.gather_table_stats
1811 (ownname => g_schema_name,
1812 tabname => 'FII_CC_PMV_AGRT_NODES');
1813
1814 g_phase := 'gather_table_stats for FII_UDD1_PMV_AGRT_NODES';
1815
1816 FND_STATS.gather_table_stats
1817 (ownname => g_schema_name,
1818 tabname => 'FII_UDD1_PMV_AGRT_NODES');
1819
1820 --==============================================================--
1821
1822 FND_CONCURRENT.Af_Commit;
1823
1824 IF (FIIDIM_Debug) THEN
1825 FII_MESSAGE.Func_Succ(func_name => 'FII_PMV_HELPER_TABLES_C.Load_Main');
1826 END IF;
1827
1828 -- Exception handling
1829
1830 EXCEPTION
1831
1832 WHEN PMVH_fatal_err THEN
1833
1834 FII_UTIL.Write_Log ('FII_PMV_HELPER_TABLES_C.Load_Main: '||
1835 'User defined error');
1836
1837 FND_CONCURRENT.Af_Rollback;
1838 FII_MESSAGE.Func_Fail(func_name => 'FII_PMV_HELPER_TABLES_C.Load_Main');
1839 retcode := sqlcode;
1840 ret_val := FND_CONCURRENT.Set_Completion_Status
1841 (status => 'ERROR', message => substr(sqlerrm,1,180));
1842
1843 WHEN OTHERS THEN
1844 FII_UTIL.Write_Log ('Init_Load -> phase: '|| g_phase);
1845 FII_UTIL.Write_Log (
1846 'Other error in FII_PMV_HELPER_TABLES_C.Load_Main: ' || substr(sqlerrm,1,180));
1847
1848 FND_CONCURRENT.Af_Rollback;
1849 FII_MESSAGE.Func_Fail(func_name => 'FII_PMV_HELPER_TABLES_C.Load_Main');
1850 retcode := sqlcode;
1851 ret_val := FND_CONCURRENT.Set_Completion_Status
1852 (status => 'ERROR', message => substr(sqlerrm,1,180));
1853
1854 END Load_Main;
1855
1856 END FII_PMV_HELPER_TABLES_C;
[Home] [Help]