[Home] [Help]
PACKAGE BODY: APPS.FII_CC_MGR_SUP_C
Source
1 PACKAGE BODY FII_CC_MGR_SUP_C AS
2 /*$Header: FIICMSCB.pls 115.12 2004/05/21 21:05:56 juding noship $*/
3
4 g_phase VARCHAR2 (240);
5 g_fii_schema VARCHAR2 (120);
6 g_fii_user_id NUMBER(15);
7 g_fii_login_id NUMBER(15);
8 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
9
10 G_AGGREGATION_LEVELS NUMBER(15) :=
11 NVL(fnd_profile.value('FII_MGR_LEVEL'), 999);
12
13 g_debug_flag VARCHAR2(1) :=
14 nvl(fnd_profile.value('FII_DEBUG_MODE'), 'N');
15
16 -----------------------------------------------------------------------
17 -- PROCEDURE TRUNCATE_TABLE
18 -----------------------------------------------------------------------
19 PROCEDURE TRUNCATE_TABLE (p_table_name in varchar2) is
20 l_stmt varchar2(240);
21
22 Begin
23
24 l_stmt:='truncate table '||g_fii_schema||'.'|| p_table_name;
25
26 if g_debug_flag = 'Y' then
27 FII_UTIL.put_line('');
28 FII_UTIL.put_line(l_stmt);
29 end if;
30
31 execute immediate l_stmt;
32
33 Exception
34 WHEN OTHERS THEN
35 FII_UTIL.put_line('
36 Error in Procedure: TRUNCATE_TABLE
37 Message: '||sqlerrm);
38 RAISE;
39 End truncate_Table;
40
41
42 -----------------------------------------------------------------------
43 -- PROCEDURE INIT
44 -----------------------------------------------------------------------
45 PROCEDURE INIT is
46 l_status VARCHAR2(30);
47 l_industry VARCHAR2(30);
48 l_dir VARCHAR2(160);
49
50 BEGIN
51
52 if g_debug_flag = 'Y' then
53 FII_MESSAGE.Func_Ent('FII_CC_MGR_SUP_C.FII_CC_MGR_SUP_C.INIT');
54 end if;
55
56 ----------------------------------------------
57 -- Do set up for log file
58 ----------------------------------------------
59 g_phase := 'Set up for log file';
60
61 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
62 ------------------------------------------------------
63 -- Set default directory in case if the profile option
64 -- BIS_DEBUG_LOG_DIRECTORY is not set up
65 ------------------------------------------------------
66 if l_dir is NULL then
67 l_dir := FII_UTIL.get_utl_file_dir ;
68 end if;
69
70 ----------------------------------------------------------------
71 -- FII_UTIL.initialize will get profile options FII_DEBUG_MODE
72 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
73 -- the log files and output files are written to
74 ----------------------------------------------------------------
75 FII_UTIL.initialize ('FII_CC_MGR_SUP_C.log',
76 'FII_CC_MGR_SUP_C.out',l_dir, 'FII_GL_COMCCH_C');
77
78 ----------------------------------------------------------
79 -- Find the schema owner of FII
80 ----------------------------------------------------------
81 g_fii_schema := FII_UTIL.get_schema_name ('FII');
82
83 g_fii_user_id := FND_GLOBAL.User_Id;
84 g_fii_login_id := FND_GLOBAL.Login_Id;
85 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
86 RAISE G_LOGIN_INFO_NOT_AVABLE;
87 END IF;
88
89 if g_debug_flag = 'Y' then
90 FII_UTIL.put_line('User ID: ' || g_fii_user_id ||
91 ' Login ID: ' || g_fii_login_id);
92 end if;
93
94 if g_debug_flag = 'Y' then
95 FII_MESSAGE.Func_Succ('FII_CC_MGR_SUP_C.FII_CC_MGR_SUP_C.INIT');
96 end if;
97
98 EXCEPTION
99 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
100 FII_UTIL.put_line('Can not get User ID and Login ID, program exit');
101 FII_MESSAGE.Func_Fail('FII_CC_MGR_SUP_C.FII_CC_MGR_SUP_C.INIT');
102 raise;
103
104 WHEN OTHERS THEN
105 FII_UTIL.put_line('
106 Error in Procedure: INIT
107 Phase: '||g_phase||'
108 Message: '||sqlerrm);
109 FII_MESSAGE.Func_Fail('FII_CC_MGR_SUP_C.FII_CC_MGR_SUP_C.INIT');
110 raise;
111 END Init;
112
113 --**********************************************************************
114 -- Populate the TMP table: FII_CC_MGR_HIER_GT
115
116 PROCEDURE Populate_HIER_TMP IS
117
118 l_status VARCHAR2(1);
119
120 BEGIN
121
122 if g_debug_flag = 'Y' then
123 FII_MESSAGE.Func_Ent('FII_CC_MGR_SUP_C.Populate_HIER_TMP');
124 end if;
125
126 g_phase := 'Initialize setups';
127 ------------------------------------------------
128 -- Initialize setups
129 ------------------------------------------------
130 INIT;
131
132 g_phase := 'Populate table FII_CCC_MGR_GT to replace HRI_CS_PER_ORGCC_CT';
133
134 FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR (l_status);
135
136 IF l_status = -1 then
137 FII_UTIL.put_line('Error in FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR ...');
138 FII_UTIL.put_line('Table FII_CCC_MGR_GT is not populated');
139 raise NO_DATA_FOUND;
140 END IF;
141
142 --First, join the fii_ccc_mgr_gt table with hri_cs_suph to
143 --get a distinct list of managers who have cost center responsibility
144 --(i.e. own a cost center or have a subordinate who does).
145 --Insert these records into global temporary table FII_PERSON_ID_TMP
146
147 g_phase := 'Insert into table FII_PERSON_ID_TMP';
148
149 INSERT into FII_PERSON_ID_TMP (person_id)
150 select /*+ leading(ct) full(ct) index(suph HRI_CS_SUPH_N4) use_nl(ct suph) */
151 distinct suph.sup_person_id
152 from fii_ccc_mgr_gt ct,
153 hri_cs_suph suph,
154 per_assignment_status_types ast
155 where ct.manager = suph.sub_person_id
156 and sysdate between suph.effective_start_date
157 and suph.effective_end_date
158 and suph.sup_assignment_status_type_id = ast.assignment_status_type_id
162 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_PERSON_ID_TMP');
159 and ast.per_system_status IN ('ACTIVE_ASSIGN', 'SUSP_ASSIGN');
160
161 if g_debug_flag = 'Y' then
163 end if;
164
165 ----------------------------------------------------------
166 --Begin to populate the table FII_CC_MGR_HIER_GT
167 ----------------------------------------------------------
168 FII_UTIL.start_timer;
169 g_phase := 'Truncate table FII_CC_MGR_HIER_GT';
170
171 TRUNCATE_TABLE ('FII_CC_MGR_HIER_GT');
172
173 g_phase := 'Insert into table FII_CC_MGR_HIER_GT';
174
175 --We populate aggregation_flag to 'N' if child_level is greater
176 --than the profile option for aggregation level
177
178 INSERT INTO FII_CC_MGR_HIER_GT
179 (MGR_ID,
180 MGR_LEVEL,
181 DIRECT_ID,
182 DIRECT_LEVEL,
183 EMP_ID,
184 EMP_LEVEL,
185 NEXT_LEVEL_IS_LEAF,
186 IS_LEAF_FLAG,
187 AGGREGATION_FLAG)
188 select
189 sup.sup_person_id mgr_id,
190 sup.sup_level mgr_level,
191 sup.SUB_PERSON_ID direct_id,
192 sup.sub_level drect_level,
193 sub.sub_person_id emp_id,
194 sub.sub_level emp_level,
195 'N' next_level_is_leaf,
196 'N' is_leaf_flag,
197 decode(SIGN(sub.sub_level-G_AGGREGATION_LEVELS),1,'N','Y') aggregation_flag
198 from hri_cs_suph sup,
199 hri_cs_suph sub
200 where sup.sub_relative_level <= 1
201 and (sup.sub_relative_level = 1 OR sup.sup_level = 1)
202 and sup.sup_invalid_flag_code = 'N'
203 and sup.sub_invalid_flag_code = 'N'
204 and sup.sub_primary_asg_flag_code = 'Y'
205 and sysdate between sup.effective_start_date and sup.effective_end_date
206 and sup.sub_person_id = sub.sup_person_id
207 and sub.sup_invalid_flag_code = 'N'
208 and sub.sub_invalid_flag_code = 'N'
209 and sub.sub_primary_asg_flag_code = 'Y'
210 and sysdate between sub.effective_start_date and sub.effective_end_date
211 and sub.sub_person_id in (select person_id from FII_PERSON_ID_TMP);
212
213 ---------------------------------------------------------------
214
215 if g_debug_flag = 'Y' then
216 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
217 ' rows of data into FII_CC_MGR_HIER_GT table');
218 end if;
219
220 --Set mgr_id to -999 and mgr_level to 0 for records of
221 -- mgr_level = 1 and direct_level = 1
222 g_phase := 'Update MGR_ID and MGR_LEVEL for records of top person';
223
224 Update FII_CC_MGR_HIER_GT
225 Set mgr_id = -999,
226 mgr_level = 0
227 Where mgr_level = 1
228 and direct_level = 1;
229
230 if g_debug_flag = 'Y' then
231 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CC_MGR_HIER_GT');
232 end if;
233
234 --Insert all self records (using mgr_id = -999)
235 g_phase := 'Insert all self records';
236
237 Insert into FII_CC_MGR_HIER_GT
238 (MGR_ID,
239 MGR_LEVEL,
240 DIRECT_ID,
241 DIRECT_LEVEL,
242 EMP_ID,
243 EMP_LEVEL,
244 NEXT_LEVEL_IS_LEAF,
245 IS_LEAF_FLAG,
246 AGGREGATION_FLAG)
247 select
248 EMP_ID,
249 EMP_LEVEL,
250 EMP_ID,
251 EMP_LEVEL,
252 EMP_ID,
253 EMP_LEVEL,
254 'N',
255 'N',
256 AGGREGATION_FLAG
257 from FII_CC_MGR_HIER_GT
258 where mgr_id = -999;
259
260 if g_debug_flag = 'Y' then
261 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_CC_MGR_HIER_GT');
262 end if;
263
264 --Update the column next_level_is_leaf
265 g_phase := 'Update column next_level_is_leaf...';
266
267 Update FII_CC_MGR_HIER_GT tab1
268 Set tab1.next_level_is_leaf = 'Y'
269 Where tab1.direct_id = tab1.emp_id
270 AND tab1.aggregation_flag = 'Y'
271 AND 1 = (select count(*)
272 from FII_CC_MGR_HIER_GT tab2
273 where tab2.mgr_id = tab1.direct_id
274 and tab2.aggregation_flag = 'Y');
275
276 if g_debug_flag = 'Y' then
277 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CC_MGR_HIER_GT');
278 end if;
279
280 --Update the column is_leaf_flag
281 g_phase := 'Update column is_leaf_flag...';
282
283 Update FII_CC_MGR_HIER_GT
284 Set is_leaf_flag = 'Y'
285 Where mgr_id = direct_id
286 and direct_id = emp_id
287 and next_level_is_leaf = 'Y';
288
289 if g_debug_flag = 'Y' then
290 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_CC_MGR_HIER_GT');
291 end if;
292
293 FII_UTIL.stop_timer;
294
295 if g_debug_flag = 'Y' then
296 FII_UTIL.print_timer('Duration');
297 end if;
298
299 if g_debug_flag = 'Y' then
300 FII_MESSAGE.Func_Succ('FII_CC_MGR_SUP_C.Populate_HIER_TMP');
301 end if;
302
303 Exception
304
305 WHEN OTHERS Then
306 FII_UTIL.put_line('Error in phase ' || g_phase ||
307 ' of running FII_CC_MGR_SUP_C.Populate_HIER_TMP; '
308 || 'Message: ' || sqlerrm);
309 ROLLBACK;
310 FII_MESSAGE.Func_Fail('FII_CC_MGR_SUP_C.Populate_HIER_TMP');
311 raise;
312
313 END Populate_HIER_TMP;
314
315 --**********************************************************************
316 -- Incremental Update
317
318 PROCEDURE Incre_Update (errbuf IN OUT NOCOPY VARCHAR2,
319 retcode IN OUT NOCOPY VARCHAR2) IS
320
321 BEGIN
322
323 if g_debug_flag = 'Y' then
324 FII_MESSAGE.Func_Ent('FII_CC_MGR_SUP_C.Incre_Update');
325 end if;
326
327 ----------------------------------------------------------
328 --call to populate the table FII_CC_MGR_HIER_GT
329 ----------------------------------------------------------
333
330 g_phase := 'call to populate the table FII_CC_MGR_HIER_GT';
331
332 Populate_HIER_TMP;
334 ----------------------------------------------------------
335 --Begin to update the table FII_CC_MGR_HIERARCHIES
336 --by diff FII_CC_MGR_HIER_GT and FII_CC_MGR_HIERARCHIES
337 --for incremental update
338 ----------------------------------------------------------
339
340 FII_UTIL.start_timer;
341
342 g_phase := 'DELETE FROM FII_CC_MGR_HIERARCHIES';
343
344 DELETE FROM FII_CC_MGR_HIERARCHIES
345 WHERE (mgr_id, mgr_level, direct_id, direct_level,
346 emp_id, emp_level, next_level_is_leaf, is_leaf_flag) IN
347 (SELECT mgr_id, mgr_level, direct_id, direct_level,
348 emp_id, emp_level, next_level_is_leaf, is_leaf_flag
349 FROM FII_CC_MGR_HIERARCHIES
350 MINUS
351 SELECT mgr_id, mgr_level, direct_id, direct_level,
352 emp_id, emp_level, next_level_is_leaf, is_leaf_flag
353 FROM FII_CC_MGR_HIER_GT
354 WHERE aggregation_flag = 'Y');
355
356 if g_debug_flag = 'Y' then
357 FII_UTIL.Write_Log('Deleted ' || SQL%ROWCOUNT || ' rows from FII_CC_MGR_HIERARCHIES');
358 end if;
359
360 g_phase := 'INSERT INTO FII_CC_MGR_HIERARCHIES';
361
362 INSERT INTO FII_CC_MGR_HIERARCHIES
363 (mgr_id,
364 mgr_level,
365 direct_id,
366 direct_level,
367 emp_id,
368 emp_level,
369 next_level_is_leaf,
370 is_leaf_flag,
371 creation_date,
372 created_by,
373 last_update_date,
374 last_updated_by,
375 last_update_login)
376 (SELECT mgr_id,
377 mgr_level,
378 direct_id,
379 direct_level,
380 emp_id,
381 emp_level,
382 next_level_is_leaf,
383 is_leaf_flag,
384 SYSDATE,
385 G_FII_USER_ID,
386 SYSDATE,
387 G_FII_USER_ID,
388 G_FII_LOGIN_ID
389 FROM FII_CC_MGR_HIER_GT
390 WHERE aggregation_flag = 'Y'
391 MINUS
392 SELECT mgr_id,
393 mgr_level,
394 direct_id,
395 direct_level,
396 emp_id,
397 emp_level,
398 next_level_is_leaf,
399 is_leaf_flag,
400 SYSDATE,
401 G_FII_USER_ID,
402 SYSDATE,
403 G_FII_USER_ID,
404 G_FII_LOGIN_ID
405 FROM FII_CC_MGR_HIERARCHIES);
406
407 ---------------------------------------------------------------
408
409 if g_debug_flag = 'Y' then
410 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
411 ' rows of data into FII_CC_MGR_HIERARCHIES table');
412 end if;
413
414 --Call FND_STATS to collect statistics after re-populating the tables.
415
416 g_phase := 'gather_table_stats for FII_CC_MGR_HIERARCHIES';
417 FND_STATS.gather_table_stats
418 (ownname => g_fii_schema,
419 tabname => 'FII_CC_MGR_HIERARCHIES');
420
421 g_phase := 'gather_table_stats for MLOG$_FII_CC_MGR_HIERARCHI';
422 FND_STATS.gather_table_stats
423 (ownname => g_fii_schema,
424 tabname => 'MLOG$_FII_CC_MGR_HIERARCHI');
425
426 FND_CONCURRENT.Af_Commit;
427
428 FII_UTIL.stop_timer;
429
430 if g_debug_flag = 'Y' then
431 FII_UTIL.print_timer('Duration');
432 end if;
433
434 if g_debug_flag = 'Y' then
435 FII_MESSAGE.Func_Succ('FII_CC_MGR_SUP_C.Incre_Update');
436 end if;
437
438 ----------------------------------------------------------------
439
440 Exception
441
442 WHEN OTHERS Then
443 errbuf := SQLERRM;
444 retcode := SQLCODE;
445 FII_UTIL.put_line('
446 Error in phase ' || g_phase || ' of running FII_CC_MGR_SUP_C.Incre_Update; '
447 || 'Message: ' || sqlerrm);
448 ROLLBACK;
449 FII_MESSAGE.Func_Fail('FII_CC_MGR_SUP_C.Incre_Update');
450 raise;
451
452 END Incre_Update;
453
454
455 --**********************************************************************
456 -- Initial Load
457
458 PROCEDURE Init_Load (errbuf IN OUT NOCOPY VARCHAR2,
459 retcode IN OUT NOCOPY VARCHAR2) IS
460
461 l_status VARCHAR2(1);
462
463 BEGIN
464
465 if g_debug_flag = 'Y' then
466 FII_MESSAGE.Func_Ent('FII_CC_MGR_SUP_C.Init_Load');
467 end if;
468
469 ----------------------------------------------------------
470 --call to populate the table FII_CC_MGR_HIER_GT
471 ----------------------------------------------------------
472 g_phase := 'call to populate the table FII_CC_MGR_HIER_GT';
473
474 Populate_HIER_TMP;
475
476 g_phase := 'Truncate table FII_CC_MGR_HIERARCHIES';
477
478 TRUNCATE_TABLE ('FII_CC_MGR_HIERARCHIES');
479
480 g_phase := 'Insert into table FII_CC_MGR_HIERARCHIES';
481
482 INSERT /*+ APPEND */ INTO FII_CC_MGR_HIERARCHIES
483 (MGR_ID,
484 MGR_LEVEL,
485 DIRECT_ID,
486 DIRECT_LEVEL,
487 EMP_ID,
488 EMP_LEVEL,
489 NEXT_LEVEL_IS_LEAF,
490 IS_LEAF_FLAG,
491 creation_date,
492 created_by,
493 last_update_date,
494 last_updated_by,
495 last_update_login)
496 select
497 MGR_ID,
498 MGR_LEVEL,
499 DIRECT_ID,
500 DIRECT_LEVEL,
501 EMP_ID,
502 EMP_LEVEL,
503 NEXT_LEVEL_IS_LEAF,
504 IS_LEAF_FLAG,
505 SYSDATE,
506 G_FII_USER_ID,
507 SYSDATE,
508 G_FII_USER_ID,
509 G_FII_LOGIN_ID
510 from
511 FII_CC_MGR_HIER_GT
512 where AGGREGATION_FLAG = 'Y';
513
514
515 ---------------------------------------------------------------
516
517 if g_debug_flag = 'Y' then
518 FII_UTIL.put_line('Inserted ' || SQL%ROWCOUNT ||
519 ' rows of data into FII_CC_MGR_HIERARCHIES table');
520 end if;
521
522 --Call FND_STATS to collect statistics after re-populating the tables.
523
524 g_phase := 'gather_table_stats for FII_CC_MGR_HIERARCHIES';
525 FND_STATS.gather_table_stats
526 (ownname => g_fii_schema,
527 tabname => 'FII_CC_MGR_HIERARCHIES');
528
529 g_phase := 'gather_table_stats for MLOG$_FII_CC_MGR_HIERARCHI';
530 FND_STATS.gather_table_stats
531 (ownname => g_fii_schema,
532 tabname => 'MLOG$_FII_CC_MGR_HIERARCHI');
533
534 FND_CONCURRENT.Af_Commit;
535
536 FII_UTIL.stop_timer;
537
538 if g_debug_flag = 'Y' then
539 FII_UTIL.print_timer('Duration');
540 end if;
541
542 if g_debug_flag = 'Y' then
543 FII_MESSAGE.Func_Succ('FII_CC_MGR_SUP_C.Init_Load');
544 end if;
545
546 ----------------------------------------------------------------
547
548 Exception
549 WHEN OTHERS THEN
550 errbuf := SQLERRM;
551 retcode := SQLCODE;
552 FII_UTIL.put_line('
553 Error in phase ' || g_phase || ' of running FII_CC_MGR_SUP_C.Init_Load; '
554 || 'Message: ' || sqlerrm);
555 ROLLBACK;
556 FII_MESSAGE.Func_Fail('FII_CC_MGR_SUP_C.Init_Load');
557 raise;
558
559 END Init_Load;
560
561 END FII_CC_MGR_SUP_C;