[Home] [Help]
PACKAGE BODY: APPS.FII_GL_COMCCH_C
Source
1 PACKAGE BODY FII_GL_COMCCH_C AS
2 /* $Header: FIIGLH1B.pls 120.3 2006/02/21 22:07:30 juding noship $ */
3
4 g_debug_flag Varchar2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5
6 g_retcode VARCHAR2(20) := NULL;
7 g_fii_schema VARCHAR2(30);
8 g_worker_num NUMBER;
9 g_phase VARCHAR2(300);
10 g_mode VARCHAR2(1) := NULL;
11 g_fii_user_id NUMBER;
12 g_fii_login_id NUMBER;
13 g_current_language VARCHAR2(30);
14 G_TABLE_NOT_EXIST EXCEPTION;
15 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
16 G_LOGIN_INFO_NOT_AVABLE EXCEPTION;
17 G_DUPLICATE_PROD_ASGN EXCEPTION;
18 G_NO_PROD_SEG_DEFINED EXCEPTION;
19 G_INVALID_PROD_CODE_EXIST EXCEPTION;
20 G_UNASSIGNED_LOB_ID NUMBER(15);
21 G_AGGREGATION_LEVELS NUMBER(15) := NVL(fnd_profile.value('FII_MGR_LEVEL'), 999);
22 G_DBI50_FOR_LOB BOOLEAN := FALSE;
23
24 -- ---------------------------------------------------------------
25 -- Private procedures and Functions
26 -- ---------------------------------------------------------------
27
28 -----------------------------------------------------------------
29 -- PROCEDURE PRINT_DUP_ORG_IN_TEMP
30 --
31 -- It will print out all (company, cost_center) with multiple orgs
32 -- in table FII_COM_CC_MAPPINGS_GT, it's called as EXCEPTION happens.
33 -- This will help detect a data issue in bug 3122222
34 -----------------------------------------------------------------
35 PROCEDURE PRINT_DUP_ORG_IN_TEMP IS
36
37 l_count NUMBER(15) :=0;
38
39 Cursor c_duplicate_org is
40 select count(*) cnt,
41 company_id,
42 cost_center_id
43 from FII_COM_CC_MAPPINGS_GT
44 where company_cost_center_org_id <> -1
45 group by company_id, cost_center_id
46 having count(*) > 1;
47
48 Cursor c_list_dup_org (p_com_id number, p_cc_id number) is
49 select com.flex_value company,
50 cc.flex_value cost_center,
51 org.name organization,
52 org.organization_id org_id
53 from FII_COM_CC_MAPPINGS_GT gt,
54 hr_all_organization_units org,
55 fnd_flex_values com,
56 fnd_flex_values cc
57 where gt.company_id = p_com_id
58 and gt.cost_center_id = p_cc_id
59 and gt.company_cost_center_org_id = org.organization_id
60 and gt.company_id = com.flex_value_id
61 and gt.cost_center_id = cc.flex_value_id;
62
63 BEGIN
64
65 If g_debug_flag = 'Y' then
66 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.PRINT_DUP_ORG_IN_TEMP');
67 END IF;
68
69 --The information is written to the log file only for debug purpose
70 --called from an EXCEPTION section
71
72 l_count := 0;
73 FOR r_dup_org IN c_duplicate_org LOOP
74 if l_count = 0 then
75 fii_util.write_log('Printing out company cost_center with multiple orgs in table FII_COM_CC_MAPPINGS_GT');
76 fii_util.write_log('');
77 fii_util.write_log ('- Company (ID) --- Cost Center (ID) --- Organization (ID) -');
78 end if;
79
80 l_count := l_count + 1;
81 FOR r_list_org IN c_list_dup_org (r_dup_org.company_id, r_dup_org.cost_center_id) LOOP
82 fii_util.write_log (r_list_org.company || ' (' || r_dup_org.company_id || ') ' ||
83 r_list_org.cost_center || ' (' || r_dup_org.cost_center_id || ') ' ||
84 r_list_org.organization|| ' (' || r_list_org.org_id || ')');
85 END LOOP;
86 END LOOP;
87
88 If g_debug_flag = 'Y' then
89 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.PRINT_DUP_ORG_IN_TEMP');
90 END IF;
91
92 EXCEPTION
93 WHEN OTHERS THEN
94 g_retcode := -1;
95 fii_util.write_log('
96 -----------------------------
97 Error occured in Procedure: PRINT_DUP_ORG_IN_TEMP
98 Message: ' || sqlerrm);
99 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.PRINT_DUP_ORG_IN_TEMP');
100 raise;
101
102 END PRINT_DUP_ORG_IN_TEMP;
103
104
105 -----------------------------------------------------------------
106 -- PROCEDURE PRINT_DUP_MGR_IN_TEMP
107 --
108 -- It will print out all Person IDs with multiple managers at
109 -- the level G_AGGREGATION_LEVELS in table FII_CC_MGR_HIER_GT,
110 -- it's called as EXCEPTION happens.
111 -- This will help detect a data issue in HRI_CS_SUPH
112 -----------------------------------------------------------------
113 PROCEDURE PRINT_DUP_MGR_IN_TEMP IS
114
115 l_count NUMBER(15) :=0;
116
117 Cursor c_duplicate_mgr is
118 select emp_id, count(*)
119 from FII_CC_MGR_HIER_GT
120 where mgr_level = G_AGGREGATION_LEVELS
121 group by emp_id
122 having count(*) > 1;
123
124 Cursor c_list_dup_mgr (p_emp_id number) is
125 select mgr_id, emp_level
126 from FII_CC_MGR_HIER_GT
127 where mgr_level = G_AGGREGATION_LEVELS
128 and emp_id = p_emp_id;
129
130 BEGIN
131
132 If g_debug_flag = 'Y' then
133 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.PRINT_DUP_MGR_IN_TEMP');
134 END IF;
135
136 --The information is written to the log file only for debug purpose
137 --called from an EXCEPTION section
138
139 l_count := 0;
140 FOR r_dup_mgr IN c_duplicate_mgr LOOP
141 if l_count = 0 then
142 fii_util.write_log('Printing out employee with multiple managers in table FII_CC_MGR_HIER_GT at level ' || G_AGGREGATION_LEVELS);
143 fii_util.write_log('');
144 fii_util.write_log ('- Employee ID --- Manager ID --- Employee Level -');
145 end if;
146
147 l_count := l_count + 1;
148 FOR r_list_mgr IN c_list_dup_mgr (r_dup_mgr.emp_id) LOOP
149 fii_util.write_log (r_dup_mgr.emp_id || ' ' ||
150 r_list_mgr.mgr_id || ' ' ||
151 r_list_mgr.emp_level);
152 END LOOP;
153 END LOOP;
154
155 If g_debug_flag = 'Y' then
156 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.PRINT_DUP_MGR_IN_TEMP');
157 END IF;
158
159 EXCEPTION
160 WHEN OTHERS THEN
161 g_retcode := -1;
162 fii_util.write_log('
163 -----------------------------
164 Error occured in Procedure: PRINT_DUP_MGR_IN_TEMP
165 Message: ' || sqlerrm);
166 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.PRINT_DUP_MGR_IN_TEMP');
167 raise;
168
169 END PRINT_DUP_MGR_IN_TEMP;
170
171
172 -- ---------------------------------------------------------------
173 -- PROCEDURE INITIAL_LOAD
174 -- ---------------------------------------------------------------
175 PROCEDURE INITIAL_LOAD is
176
177 BEGIN
178
179 If g_debug_flag = 'Y' then
180 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.INITIAL_LOAD');
181 END IF;
182
183 g_phase := 'Insert into FII_COM_CC_MAPPINGS by INITIAL_LOAD';
184
185 INSERT /*+ append*/ INTO FII_COM_CC_MAPPINGS
186 (COMPANY_COST_CENTER_ORG_ID ,
187 COST_CENTER_ID ,
188 COMPANY_ID ,
189 MANAGER_ID ,
190 VALID_MGR_FLAG,
191 LOB_ID,
192 PARENT_MANAGER_ID,
193 PARENT_LOB_ID,
194 CREATION_DATE ,
195 CREATED_BY ,
196 LAST_UPDATE_DATE ,
197 LAST_UPDATED_BY ,
198 LAST_UPDATE_LOGIN)
199 SELECT DISTINCT
200 dim.COMPANY_COST_CENTER_ORG_ID,
201 dim.COST_CENTER_ID,
202 dim.COMPANY_ID,
203 nvl(ct.manager, -1),
204 decode(ct.manager, NULL, 'N', 'Y'),
205 G_UNASSIGNED_LOB_ID,
206 nvl(ct.manager, -1),
207 G_UNASSIGNED_LOB_ID,
208 sysdate,
209 g_fii_user_id,
210 sysdate,
211 g_fii_user_id,
212 g_fii_login_id
213 FROM FII_COM_CC_MAPPINGS_GT dim,
214 fii_ccc_mgr_gt ct
215 WHERE company_cost_center_org_id <> -1
216 and dim.company_cost_center_org_id = ct.CCC_ORG_ID (+);
217
218 If g_debug_flag = 'Y' then
219 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_CC_MAPPINGS');
220 END IF;
221
222 COMMIT;
223
224 --------------------------------------------------------
225 -- Gather statistics for the use of cost-based optimizer
226 --------------------------------------------------------
227 --Will seed this in RSG
228 -- FND_STATS.gather_table_stats
229 -- (ownname => g_fii_schema,
230 -- tabname => 'FII_COM_CC_MAPPINGS');
231
232 If g_debug_flag = 'Y' then
233 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.INITIAL_LOAD');
234 END IF;
235
236 EXCEPTION
237 WHEN OTHERS THEN
238 FII_UTIL.TRUNCATE_TABLE('FII_COM_CC_MAPPINGS' , g_fii_schema, g_retcode);
239 g_retcode := '-1';
240
241 fii_util.write_log('
242 ---------------------------------
243 Error in Procedure: INITIAL_LOAD
244 Message: '||sqlerrm);
245 fii_util.write_log(g_phase);
246
247 PRINT_DUP_ORG_IN_TEMP;
248
249 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.INITIAL_LOAD');
250
251 RAISE;
252
253 END INITIAL_LOAD;
254
255 -- ---------------------------------------------------------------
256 -- PROCEDURE INCREMENTAL_LOAD
257 -- ---------------------------------------------------------------
258 PROCEDURE INCREMENTAL_LOAD is
259
260 BEGIN
261
262 If g_debug_flag = 'Y' then
263 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.INCREMENTAL_LOAD');
264 END IF;
265
266 g_phase := 'Insert into FII_COM_CC_MAPPINGS1_GT by INCREMENTAL_LOAD';
267
268 INSERT /*+ append*/ INTO FII_COM_CC_MAPPINGS1_GT
269 ( COMPANY_COST_CENTER_ORG_ID ,
270 COST_CENTER_ID ,
271 COMPANY_ID ,
272 MANAGER_ID ,
273 VALID_MGR_FLAG,
274 LOB_ID,
275 PARENT_MANAGER_ID,
276 PARENT_LOB_ID)
277 SELECT DISTINCT
278 dim.COMPANY_COST_CENTER_ORG_ID,
279 dim.COST_CENTER_ID,
280 dim.COMPANY_ID,
281 nvl(ct.manager, -1),
282 decode(ct.manager, NULL, 'N', 'Y'),
283 G_UNASSIGNED_LOB_ID,
284 nvl(ct.manager, -1),
285 G_UNASSIGNED_LOB_ID
286 FROM FII_COM_CC_MAPPINGS_GT dim,
287 fii_ccc_mgr_gt ct
288 WHERE dim.company_cost_center_org_id <> -1
289 and dim.company_cost_center_org_id = ct.CCC_ORG_ID (+);
290
291 If g_debug_flag = 'Y' then
292 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_CC_MAPPINGS1_GT');
293 END IF;
294
295 COMMIT;
296
297 --G_UNASSIGNED_LOB_ID here is the id of the unassigned LOB
298
299 If g_debug_flag = 'Y' then
300 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.INCREMENTAL_LOAD');
301 END IF;
302
303 EXCEPTION
304 WHEN OTHERS THEN
305 g_retcode := '-1';
306
307 fii_util.write_log('
308 ---------------------------------
309 Error in Procedure: INCREMENTAL_LOAD
310 Message: '||sqlerrm);
311 fii_util.write_log(g_phase);
312
313 PRINT_DUP_ORG_IN_TEMP;
314
315 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.INCREMENTAL_LOAD');
316
317 RAISE;
318
319 END INCREMENTAL_LOAD;
320
321 -- ---------------------------------------------------------------
322 -- PROCEDURE INITIAL_LOAD_LOB
323 -- ---------------------------------------------------------------
324
325 PROCEDURE INITIAL_LOAD_LOB is
326
327 BEGIN
328
329 If g_debug_flag = 'Y' then
330 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.INITIAL_LOAD_LOB');
331 END IF;
332
333 g_phase := 'Update LOB by INITIAL_LOAD_LOB';
334
335
336 if G_DBI50_FOR_LOB then
337
338 If g_debug_flag = 'Y' then
339 fii_util.write_log('Update LOB_ID for DBI50');
340 End if;
341
342 --Update LOB if using old lob assignment (DEBUG: we use NVL ?)
343 UPDATE FII_COM_CC_MAPPINGS dim
344 SET dim.LOB_ID = NVL(
345 (SELECT NVL(x.c, -1)
346 FROM
347 (SELECT lob.LINE_OF_BUSINESS a,
348 lob.COMPANY_COST_CENTER_ORG_ID b,
349 flex.FLEX_VALUE_ID c
350 FROM (SELECT findim.MASTER_VALUE_SET_ID FLEX_VALUE_SET_ID
354 fnd_flex_values flex
351 FROM FII_FINANCIAL_DIMENSIONS findim
352 WHERE DIMENSION_SHORT_NAME = 'FII_LOB') vset,
353 fii_lob_assignments lob,
355 WHERE flex.FLEX_VALUE_SET_ID = vset.FLEX_VALUE_SET_ID
356 AND flex.flex_value = lob.LINE_OF_BUSINESS) x
357 WHERE dim.COMPANY_COST_CENTER_ORG_ID <> -1
358 AND x.b = dim.COMPANY_COST_CENTER_ORG_ID), dim.LOB_ID)
359 WHERE dim.lob_id = G_UNASSIGNED_LOB_ID ;
360
361 If g_debug_flag = 'Y' then
362 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS');
363 END IF;
364
365 else
366
367 If g_debug_flag = 'Y' then
368 fii_util.write_log('Update LOB_ID for DBI60 and above');
369 End if;
370
371 --Update LOB if using new lob assignment (DEBUG: we use NVL ?)
372 --Bug 3243824: use in ('GL_BALANCING', 'FA_COST_CTR')
373 -- to replace <> 'GL_GLOBAL'
374
375 --Bug 3407938: the lob_id should be in LOB dimension full hierarchy
376
377 UPDATE FII_COM_CC_MAPPINGS dim
378 SET dim.LOB_ID = NVL(
379 (SELECT decode (lob.b, 'GL_BALANCING', dim.company_id, dim.cost_center_id)
380 FROM FII_COM_CC_MAPPINGS_GT dim1,
381 (select map.chart_of_accounts_id a,
382 fsav.segment_attribute_type b
383 from FND_SEGMENT_ATTRIBUTE_VALUES fsav,
384 fii_dim_mapping_rules map
385 where fsav.application_id = 101
386 and fsav.id_flex_code = 'GL#'
387 and map.dimension_short_name = 'FII_LOB'
388 and map.chart_of_accounts_id = fsav.id_flex_num
389 and map.application_column_name1 = fsav.application_column_name
390 and fsav.attribute_value = 'Y'
391 and fsav.segment_attribute_type in ('GL_BALANCING', 'FA_COST_CTR')) lob
392 WHERE lob.a = dim1.COA_ID
393 AND dim.COMPANY_COST_CENTER_ORG_ID = dim1.COMPANY_COST_CENTER_ORG_ID
394 AND decode (lob.b, 'GL_BALANCING', dim.company_id, dim.cost_center_id) IN
395 (select flob.child_lob_id
396 from fii_full_lob_hiers flob
397 where flob.parent_lob_id = flob.child_lob_id)
398 ), dim.LOB_ID)
399 WHERE dim.lob_id = G_UNASSIGNED_LOB_ID ;
400
401 If g_debug_flag = 'Y' then
402 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS');
403 END IF;
404
405 end if;
406
407 g_phase := 'Update parent_manager_id by INITIAL_LOAD_LOB';
408
409 -- Update parent_manager_id
410 Update FII_COM_CC_MAPPINGS dim
411 Set dim.parent_manager_id =
412 NVL((select mgr.mgr_id
413 from FII_CC_MGR_HIER_GT mgr
414 where mgr.mgr_level = G_AGGREGATION_LEVELS
415 and mgr.emp_id = dim.manager_id), dim.manager_id);
416
417 If g_debug_flag = 'Y' then
418 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS');
419 END IF;
420
421 g_phase := 'Update parent_lob_id by INITIAL_LOAD_LOB';
422
423 -- Update parent_lob_id, we pick the parent with max level
424 -- (the nearest parent in pruned hierarchy)
425 Update FII_COM_CC_MAPPINGS dim
426 Set dim.parent_lob_id =
427 NVL((select v.parent_lob_id
428 from (select flob.parent_lob_id, flob.child_lob_id, lob.child_level
429 from fii_full_lob_hiers flob,
430 fii_lob_hierarchies lob
431 where lob.child_lob_id = flob.parent_lob_id
432 order by lob.child_level DESC) v
433 where v.child_lob_id = dim.lob_id
434 and rownum = 1), dim.lob_id);
435
436 If g_debug_flag = 'Y' then
437 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS');
438 END IF;
439
440 COMMIT;
441
442 If g_debug_flag = 'Y' then
443 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.INITIAL_LOAD_LOB');
444 END IF;
445
446 EXCEPTION
447 WHEN OTHERS THEN
448 FII_UTIL.TRUNCATE_TABLE('FII_COM_CC_MAPPINGS' , g_fii_schema, g_retcode);
449 g_retcode := '-1';
450
451 fii_util.write_log('
452 ---------------------------------
453 Error in Procedure: INITIAL_LOAD_LOB
454 Message: '||sqlerrm);
455 fii_util.write_log(g_phase);
456
457 PRINT_DUP_MGR_IN_TEMP;
458
459 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.INITIAL_LOAD_LOB');
460
461 RAISE;
462
463 END INITIAL_LOAD_LOB;
464
465
466 -- ---------------------------------------------------------------
467 -- PROCEDURE INCREMENTAL_LOAD_LOB_MERGE
468 -- ---------------------------------------------------------------
469
470 PROCEDURE INCREMENTAL_LOAD_LOB_MERGE is
471
472 BEGIN
473
474 If g_debug_flag = 'Y' then
475 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.INCREMENTAL_LOAD_LOB_MERGE');
476 END IF;
477
478 g_phase := 'Update LOB by INCREMENTAL_LOAD_LOB_MERGE';
479
483 If g_debug_flag = 'Y' then
480 if G_DBI50_FOR_LOB then
481 --Update LOB if using old lob assignment (DEBUG: we use NVL ?)
482
484 fii_util.write_log('Update LOB_ID for DBI50');
485 End if;
486
487 UPDATE FII_COM_CC_MAPPINGS1_GT dim
488 SET dim.LOB_ID = NVL(
489 (SELECT NVL(x.c ,-1 )
490 FROM
491 (select lob.LINE_OF_BUSINESS a ,
492 lob.COMPANY_COST_CENTER_ORG_ID b ,
493 flex.FLEX_VALUE_ID c
494 from (SELECT findim.MASTER_VALUE_SET_ID FLEX_VALUE_SET_ID
495 FROM FII_FINANCIAL_DIMENSIONS findim
496 WHERE DIMENSION_SHORT_NAME = 'FII_LOB') vset ,
497 fii_lob_assignments lob ,
498 fnd_flex_values flex
499 where flex.FLEX_VALUE_SET_ID = vset.FLEX_VALUE_SET_ID
500 and flex.flex_value = lob.LINE_OF_BUSINESS) x
501 WHERE dim.COMPANY_COST_CENTER_ORG_ID <> -1
502 AND x.b = dim.COMPANY_COST_CENTER_ORG_ID), dim.LOB_ID)
503 WHERE dim.lob_id = G_UNASSIGNED_LOB_ID ;
504
505 If g_debug_flag = 'Y' then
506 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS1_GT');
507 END IF;
508
509 else
510
511 --Update LOB if using new lob assignment (DEBUG: we use NVL ?)
512 --Bug 3243824: use in ('GL_BALANCING', 'FA_COST_CTR')
513 -- to replace <> 'GL_GLOBAL'
514
515 --Bug 3407938: the lob_id should be in LOB dimension full hierarchy
516
517 If g_debug_flag = 'Y' then
518 fii_util.write_log('Update LOB_ID for DBI60 and above');
519 End if;
520
521 UPDATE FII_COM_CC_MAPPINGS1_GT dim
522 SET dim.LOB_ID = NVL(
523 (select decode (lob.b, 'GL_BALANCING', dim.company_id, dim.cost_center_id)
524 from FII_COM_CC_MAPPINGS_GT dim1,
525 (select map.chart_of_accounts_id a,
526 fsav.segment_attribute_type b
527 from FND_SEGMENT_ATTRIBUTE_VALUES fsav,
528 fii_dim_mapping_rules map
529 where fsav.application_id = 101
530 and fsav.id_flex_code = 'GL#'
531 and map.dimension_short_name = 'FII_LOB'
532 and map.chart_of_accounts_id = fsav.id_flex_num
533 and map.application_column_name1 = fsav.application_column_name
534 and fsav.attribute_value = 'Y'
535 and fsav.segment_attribute_type in ('GL_BALANCING', 'FA_COST_CTR')) lob
536 where lob.a = dim1.COA_ID
537 and dim.COMPANY_COST_CENTER_ORG_ID = dim1.COMPANY_COST_CENTER_ORG_ID
538 and decode (lob.b, 'GL_BALANCING', dim.company_id, dim.cost_center_id) IN
539 (select flob.child_lob_id
540 from fii_full_lob_hiers flob
541 where flob.parent_lob_id = flob.child_lob_id)
542 ), dim.LOB_ID)
543 WHERE dim.lob_id = G_UNASSIGNED_LOB_ID ;
544
545 If g_debug_flag = 'Y' then
546 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS1_GT');
547 END IF;
548
549 end if;
550
551 g_phase := 'Update parent_manager_id by INCREMENTAL_LOAD_LOB_MERGE';
552
553 -- Update parent_manager_id
554 Update FII_COM_CC_MAPPINGS1_GT dim
555 Set dim.parent_manager_id =
556 NVL((select mgr.mgr_id
557 from FII_CC_MGR_HIER_GT mgr
558 where mgr.mgr_level = G_AGGREGATION_LEVELS
559 and mgr.emp_id = dim.manager_id), dim.manager_id);
560
561 If g_debug_flag = 'Y' then
562 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS1_GT');
563 END IF;
564
565 g_phase := 'Update parent_lob_id by INCREMENTAL_LOAD_LOB_MERGE';
566
567 -- Update parent_lob_id, we pick the parent with max level
568 -- (the nearest parent in pruned hierarchy)
569 Update FII_COM_CC_MAPPINGS1_GT dim
570 Set dim.parent_lob_id =
571 NVL((select v.parent_lob_id
572 from (select flob.parent_lob_id, flob.child_lob_id, lob.child_level
573 from fii_full_lob_hiers flob,
574 fii_lob_hierarchies lob
575 where lob.child_lob_id = flob.parent_lob_id
576 order by lob.child_level DESC) v
577 where v.child_lob_id = dim.lob_id
578 and rownum = 1), dim.lob_id);
579
580 If g_debug_flag = 'Y' then
581 FII_UTIL.Write_Log('Updated ' || SQL%ROWCOUNT || ' rows in FII_COM_CC_MAPPINGS1_GT');
582 END IF;
583
584 --Incrementally populate fii_com_cc_mappings for both new and old lob assignments
585
586 g_phase := 'MERGE into fii_com_cc_mappings by INCREMENTAL_LOAD_LOB_MERGE';
587
588 MERGE into fii_com_cc_mappings mapp using
589 (select COMPANY_COST_CENTER_ORG_ID ,
590 COST_CENTER_ID ,
591 COMPANY_ID ,
592 MANAGER_ID ,
593 VALID_MGR_FLAG ,
594 LOB_ID,
595 PARENT_MANAGER_ID,
596 PARENT_LOB_ID
597 from FII_COM_CC_MAPPINGS1_GT
598 minus
599 select COMPANY_COST_CENTER_ORG_ID ,
603 VALID_MGR_FLAG ,
600 COST_CENTER_ID ,
601 COMPANY_ID ,
602 MANAGER_ID ,
604 LOB_ID,
605 PARENT_MANAGER_ID,
606 PARENT_LOB_ID
607 from FII_COM_CC_MAPPINGS
608 ) mappt
609 ON
610 ( -- mapp.COST_CENTER_ID = mappt.COST_CENTER_ID and
611 -- mapp.company_id = mappt.company_id
612 mapp.COMPANY_COST_CENTER_ORG_ID = mappt.COMPANY_COST_CENTER_ORG_ID
613 )
614 when matched then
615 update set
616 -- mapp.COMPANY_COST_CENTER_ORG_ID = mappt.COMPANY_COST_CENTER_ORG_ID,
617 mapp.COST_CENTER_ID = mappt.COST_CENTER_ID,
618 mapp.company_id = mappt.company_id,
619 mapp.MANAGER_ID = mappt.MANAGER_ID ,
620 mapp.VALID_MGR_FLAG = mappt.VALID_MGR_FLAG ,
621 mapp.LOB_ID = mappt.LOB_ID,
622 mapp.PARENT_MANAGER_ID = mappt.PARENT_MANAGER_ID,
623 mapp.PARENT_LOB_ID = mappt.PARENT_LOB_ID,
624 mapp.LAST_UPDATE_DATE = sysdate,
625 mapp.LAST_UPDATED_BY = g_fii_user_id,
626 mapp.LAST_UPDATE_LOGIN = g_fii_login_id
627 when not matched then
628 insert (
629 mapp.COMPANY_COST_CENTER_ORG_ID ,
630 mapp.COST_CENTER_ID ,
631 mapp.COMPANY_ID ,
632 mapp.MANAGER_ID ,
633 mapp.VALID_MGR_FLAG ,
634 mapp.LOB_ID,
635 mapp.PARENT_MANAGER_ID,
636 mapp.PARENT_LOB_ID,
637 mapp.CREATION_DATE ,
638 mapp.CREATED_BY ,
639 mapp.LAST_UPDATE_DATE ,
640 mapp.LAST_UPDATED_BY ,
641 mapp.LAST_UPDATE_LOGIN)
642 values
643 (
644 mappt.COMPANY_COST_CENTER_ORG_ID ,
645 mappt.COST_CENTER_ID ,
646 mappt.COMPANY_ID ,
647 mappt.MANAGER_ID ,
648 mappt.VALID_MGR_FLAG ,
649 mappt.LOB_ID,
650 mappt.PARENT_MANAGER_ID,
651 mappt.PARENT_LOB_ID,
652 sysdate,
653 g_fii_user_id,
654 sysdate,
655 g_fii_user_id,
656 g_fii_login_id);
657
658 If g_debug_flag = 'Y' then
659 FII_UTIL.Write_Log('Merged ' || SQL%ROWCOUNT || ' rows into fii_com_cc_mappings');
660 END IF;
661
662 commit;
663
664 If g_debug_flag = 'Y' then
665 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.INCREMENTAL_LOAD_LOB_MERGE');
666 END IF;
667
668 EXCEPTION
669 WHEN OTHERS THEN
670 rollback;
671 g_retcode := -1;
672
673 fii_util.write_log('
674 ---------------------------------
675 Error in Procedure: INCREMENTAL_LOAD_LOB_MERGE
676 Message: '||sqlerrm);
677 fii_util.write_log(g_phase);
678
679 PRINT_DUP_ORG_IN_TEMP;
680
681 PRINT_DUP_MGR_IN_TEMP;
682
683 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.INCREMENTAL_LOAD_LOB_MERGE');
684
685 RAISE;
686
687 END INCREMENTAL_LOAD_LOB_MERGE;
688
689 --------------------------------------------------------
690 -- PROCEDURE INITIALIZE
691 --------------------------------------------------------
692 PROCEDURE INITIALIZE is
693 l_status VARCHAR2(30);
694 l_industry VARCHAR2(30);
695 l_stmt VARCHAR2(50);
696 l_dir VARCHAR2(400);
697 l_vset_id NUMBER(15);
698 l_flag NUMBER(15) := 0;
699 BEGIN
700
701 If g_debug_flag = 'Y' then
702 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.INITIALIZE');
703 END IF;
704
705 ----------------------------------------------
706 -- Do set up for log file
707 ----------------------------------------------
708 g_phase := 'Set up for log file';
709
710 l_dir := fnd_profile.value('BIS_DEBUG_LOG_DIRECTORY');
711 ------------------------------------------------------
712 -- Set default directory in case if the profile option
713 -- BIS_DEBUG_LOG_DIRECTORY is not set up
714 ------------------------------------------------------
715 if l_dir is NULL then
716 l_dir := FII_UTIL.get_utl_file_dir;
717 end if;
718
719 ----------------------------------------------------------------
720 -- FII_UTIL.initialize will get profile options FII_DEBUG_pmode
721 -- and BIS_DEBUG_LOG_DIRECTORY and set up the directory where
722 -- the log files and output files are written to
723 ----------------------------------------------------------------
724 FII_UTIL.initialize('FII_GL_COMCCH_C.log','FII_GL_COMCCH_C.out',l_dir, 'FII_GL_COMCCH_C');
725
726 -- --------------------------------------------------------
727 -- Find out the user ID ,login ID, and current language
728 -- --------------------------------------------------------
729 g_phase := 'Find User ID ,Login ID, and Current Language';
730
731 g_fii_user_id := FND_GLOBAL.User_Id;
732 g_fii_login_id := FND_GLOBAL.Login_Id;
733 g_current_language := FND_GLOBAL.current_language;
734
735 IF (g_fii_user_id IS NULL OR g_fii_login_id IS NULL) THEN
736 RAISE G_LOGIN_INFO_NOT_AVABLE;
737 END IF;
738 -- --------------------------------------------------------
739 -- Find the schema owner
740 -- --------------------------------------------------------
741 g_phase := 'Find schema owner for FII';
742
746 -- --------------------------------------------------------
743 g_fii_schema := FII_UTIL.get_schema_name ('FII');
744
745
747 -- Find the unassigned LOB ID
748 -- --------------------------------------------------------
749
750 g_phase := 'Find the shipped FII value set id';
751
752 select FLEX_VALUE_SET_ID into l_vset_id
753 from fnd_flex_value_sets
754 where flex_value_set_name = 'Financials Intelligence Internal Value Set';
755
756 g_phase := 'Find the unassigned LOB ID from this value set: ' || l_vset_id;
757
758 select flex_value_id into G_UNASSIGNED_LOB_ID
759 from fnd_flex_values
760 where flex_value_set_id = l_vset_id
761 and flex_value = 'UNASSIGNED';
762
763
764 -- Check if we should use old DBI 5.0 LOB model
765
766 g_phase := 'Check if we should use old DBI 5.0 LOB model';
767
768 begin
769 SELECT 1 INTO l_flag
770 FROM fii_lob_assignments
771 where rownum = 1;
772 exception
773 when NO_DATA_FOUND then
774 l_flag := 0;
775 end;
776
777 if l_flag = 0 then
778 G_DBI50_FOR_LOB := FALSE;
779 else
780 G_DBI50_FOR_LOB := TRUE;
781 end if;
782 -----------------------------------------------
783
784 If g_debug_flag = 'Y' then
785 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.INITIALIZE');
786 END IF;
787
788 EXCEPTION
789 WHEN G_LOGIN_INFO_NOT_AVABLE THEN
790
791 fii_util.write_log(g_phase);
792 fii_util.write_log('
793 Can not get User ID and Login ID, program exit');
794
795 g_retcode := -1;
796 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.INITIALIZE');
797 raise;
798
799 WHEN OTHERS THEN
800 g_retcode := -1;
801
802 fii_util.write_log('
803 ------------------------
804 Error in Procedure: INIT
805 Phase: '||g_phase||'
806 Message: '||sqlerrm);
807
808 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.INITIALIZE');
809 raise;
810
811 END INITIALIZE;
812
813 -----------------------------------------------------------------
814 -- PROCEDURE HANDLE_MISSING_COA
815 -- This procedure will update -1 of coa_id in FII_COM_CC_MAPPINGS_GT
816 -- to a real coa defined in FII_DIM_MAPPING_RULES
817 -----------------------------------------------------------------
818 Procedure HANDLE_MISSING_COA IS
819
820 l_com_vs_id number (15);
821 l_cc_vs_id number (15);
822 l_coa_id number (15);
823 l_count number (10) := 0;
824
825 Cursor c_all_value_sets is
826 select fv1.flex_value_set_id company_vs_id,
827 fv2.flex_value_set_id cost_center_vs_id
828 from FII_COM_CC_MAPPINGS_GT ccc,
829 fnd_flex_values fv1,
830 fnd_flex_values fv2
831 where ccc.coa_id = -1
832 and ccc.company_id = fv1.flex_value_id
833 and ccc.cost_center_id = fv2.flex_value_id
834 for update of ccc.coa_id;
835
836 Begin
837
838 If g_debug_flag = 'Y' then
839 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.HANDLE_MISSING_COA');
840 END IF;
841
842 If g_debug_flag = 'Y' then
843 fii_util.start_timer;
844 End if;
845
846 For r_value_sets IN c_all_value_sets Loop
847
848 l_com_vs_id := r_value_sets.company_vs_id;
849 l_cc_vs_id := r_value_sets.cost_center_vs_id;
850
851 begin
852
853 --Use fnd_id_flex_segments to figure out all charts_of_accounts
854 --related to both company/cost center value set IDs:
855 -- if there is no chart_of_accounts found, no change to coa_id;
856 -- if there are charts_of_accounts found, but none of them appears
857 -- in the mapping rule table; no change to coa_id;
858 -- if there are charts_of_accounts found, and some of them are in
859 -- the mapping rule table; then use one of them to update coa_id
860 -- coa_id in FII_COM_CC_MAPPINGS_GT
861
862 select coa.coa_id into l_coa_id
863 from
864 (select ID_FLEX_NUM coa_id
865 from fnd_id_flex_segments
866 where APPLICATION_ID = 101
867 and ID_FLEX_CODE = 'GL#'
868 and FLEX_VALUE_SET_ID = l_com_vs_id
869 intersect
870 select ID_FLEX_NUM coa_id
871 from fnd_id_flex_segments
872 where APPLICATION_ID = 101
873 and ID_FLEX_CODE = 'GL#'
874 and FLEX_VALUE_SET_ID = l_cc_vs_id
875 intersect
876 select CHART_OF_ACCOUNTS_ID coa_id
877 from fii_dim_mapping_rules
878 where DIMENSION_SHORT_NAME = 'FII_LOB') coa
879 where rownum = 1;
880
881 update FII_COM_CC_MAPPINGS_GT
882 set coa_id = l_coa_id
883 where current of c_all_value_sets;
884
885 l_count := l_count + 1;
886
887 exception
888 when others then
889 null;
890 end;
891
892 End Loop;
893
894 if g_debug_flag = 'Y' then
895 fii_util.put_line('Updated '||l_count||' rows with coa_id = -1 in FII_COM_CC_MAPPINGS_GT');
896 fii_util.stop_timer;
900 If g_debug_flag = 'Y' then
897 fii_util.print_timer('Duration');
898 end if;
899
901 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.HANDLE_MISSING_COA');
902 END IF;
903
904 Exception
905 WHEN OTHERS Then
906 FII_UTIL.put_line('Error in HANDLE_MISSING_COA; ' || 'Message: ' || sqlerrm);
907 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.HANDLE_MISSING_COA');
908 raise;
909
910 End HANDLE_MISSING_COA;
911
912 -----------------------------------------------------------------
913 -- PROCEDURE POPULATE_COM_CCC_TEMP
914 -----------------------------------------------------------------
915 PROCEDURE POPULATE_COM_CCC_TEMP IS
916 l_count NUMBER(15) :=0;
917 l_ret_val BOOLEAN := FALSE;
918
919 --this cursor prints out all (company, cost_center) with
920 --multiple orgs in table fii_ccc_mgr_gt
921 Cursor c_duplicate_org is
922 select count(*) cnt,
923 company_id,
924 cost_center_id
925 from fii_ccc_mgr_gt
926 where company_id is not null
927 and cost_center_id is not null
928 group by company_id, cost_center_id
929 having count(*) > 1;
930
931 --this cursor prints out all org for a given (company, cost_center)
932 Cursor c_list_dup_org (p_com_id number, p_cc_id number) is
933 select com.flex_value company,
934 cc.flex_value cost_center,
935 org.name organization
936 from fii_ccc_mgr_gt gt,
937 hr_all_organization_units org,
938 fnd_flex_values com,
939 fnd_flex_values cc
940 where gt.company_id = p_com_id
941 and gt.cost_center_id = p_cc_id
942 and gt.ccc_org_id = org.organization_id
943 and gt.company_id = com.flex_value_id
944 and gt.cost_center_id = cc.flex_value_id;
945
946 BEGIN
947
948 If g_debug_flag = 'Y' then
949 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.POPULATE_COM_CCC_TEMP');
950 END IF;
951
952 g_phase := 'truncate table FII_COM_CC_MAPPINGS_GT';
953
954 FII_UTIL.TRUNCATE_TABLE('FII_COM_CC_MAPPINGS_GT', g_fii_schema, g_retcode);
955
956 ---------------------------------------------------------------------------------------
957 /* Bug 3700956: stop getting ccc org information from CCID
958
959 --g_phase := 'Populate FII_COM_CC_MAPPINGS_GT from fii_gl_ccid_dimensions';
960
961 -- INSERT INTO FII_COM_CC_MAPPINGS_GT
962 -- (COMPANY_COST_CENTER_ORG_ID,
963 -- COST_CENTER_ID,
964 -- COMPANY_ID,
965 -- COA_ID
966 -- )
967 -- SELECT DISTINCT
968 -- NVL(dim.company_cost_center_org_id, -1),
969 -- dim.cost_center_id,
970 -- dim.company_id,
971 -- dim.chart_of_accounts_id
972 -- FROM fii_gl_ccid_dimensions dim;
973
974 -- If g_debug_flag = 'Y' then
975 -- FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_CC_MAPPINGS_GT');
976 -- END IF;
977
978 End of Bug 3700956 */
979 ---------------------------------------------------------------------------------------
980
981
982 --------------------------------------------
983 --Bug 3560006: Add ccc-org in FII_CCC_MGR_GT
984 -- which are not in CCID
985 --------------------------------------------
986
987 -- First report bad ccc-org in FII_CCC_MGR_GT
988
989 g_phase := 'report bad ccc-org in FII_CCC_MGR_GT';
990
991 l_count := 0;
992 FOR r_dup_org IN c_duplicate_org LOOP
993 if l_count = 0 then
994
995 FII_MESSAGE.write_log (msg_name => 'FII_COM_CC_DUP_ORG',
996 token_num => 0);
997 FII_MESSAGE.write_log (msg_name => 'FII_REFER_TO_OUTPUT',
998 token_num => 0);
999
1000 FII_MESSAGE.write_output (msg_name => 'FII_COM_CC_DUP_ORG',
1001 token_num => 0);
1002 FII_MESSAGE.write_output (msg_name => 'FII_COM_CC_ORG_LIST',
1003 token_num => 0);
1004
1005 -- set the concurrent program to warning status
1006 l_ret_val := FND_CONCURRENT.Set_Completion_Status
1007 (status => 'WARNING',
1008 message => 'There are combinations of company, cost center with more than one organization assigned');
1009 end if;
1010
1011 l_count := l_count + 1;
1012 FOR r_list_org IN c_list_dup_org (r_dup_org.company_id, r_dup_org.cost_center_id) LOOP
1013 FII_UTIL.Write_Output (
1014 r_list_org.organization || ' ' ||
1015 r_list_org.company || ' ' ||
1016 r_list_org.cost_center );
1017 END LOOP;
1018 END LOOP;
1019
1020 --reset l_count to 0
1021 l_count := 0;
1022
1023 g_phase := 'Populate FII_COM_CC_MAPPINGS_GT from fii_ccc_mgr_gt';
1024
1025 -- Insert all "good" (company, cost center, ccc org) from FII_CCC_MGR_GT
1026 INSERT INTO FII_COM_CC_MAPPINGS_GT
1027 (COMPANY_COST_CENTER_ORG_ID,
1028 COMPANY_ID,
1029 COST_CENTER_ID,
1030 COA_ID)
1034 -1
1031 select NVL(max(ccc_org_id), -1),
1032 company_id,
1033 cost_center_id,
1035 from fii_ccc_mgr_gt
1036 where company_id is not null
1037 and cost_center_id is not null
1038 group by company_id, cost_center_id
1039 having count(*) = 1;
1040
1041 If g_debug_flag = 'Y' then
1042 FII_UTIL.Write_Log('Inserted ' || SQL%ROWCOUNT || ' rows into FII_COM_CC_MAPPINGS_GT');
1043 END IF;
1044
1045 -- Call HANDLE_MISSING_COA to update coa_id = -1 records.
1046 -- Do this only if using DBI 6.0 for LOB
1047
1048 If NOT G_DBI50_FOR_LOB then
1049
1050 g_phase := 'Call HANDLE_MISSING_COA';
1051
1052 HANDLE_MISSING_COA;
1053
1054 End If;
1055
1056 --------------------------------------------------------------------
1057
1058 commit;
1059
1060 --Report missing company cost center organizations
1061 --(i.e. company_cost_center_org_id = -1).
1062 --This should not happen after bug 3700956 fix!!!
1063
1064 g_phase := 'Report missing company cost center organizations';
1065
1066 begin
1067 select 1 into l_count
1068 from FII_COM_CC_MAPPINGS_GT
1069 where company_cost_center_org_id = -1
1070 and rownum = 1;
1071 exception
1072 when NO_DATA_FOUND then
1073 l_count := 0;
1074 end;
1075
1076 if l_count > 0 then
1077 FII_MESSAGE.write_log (msg_name => 'FII_MISSING_CCC_ORG_FOUND',
1078 token_num => 0);
1079 FII_MESSAGE.write_output (msg_name => 'FII_MISSING_CCC_ORG_FOUND',
1080 token_num => 0);
1081 l_ret_val := FND_CONCURRENT.Set_Completion_Status
1082 (status => 'WARNING',
1083 message => 'There are null company cost center organizations in HR');
1084 end if;
1085
1086 If g_debug_flag = 'Y' then
1087 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.POPULATE_COM_CCC_TEMP');
1088 END IF;
1089
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 g_retcode := -1;
1093
1094 fii_util.write_log('
1095 -----------------------------
1096 Error occured in Procedure: POPULATE_COM_CCC_TEMP
1097 Phase: ' || g_phase || '
1098 Message: ' || sqlerrm);
1099
1100 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.POPULATE_COM_CCC_TEMP');
1101 raise;
1102 END POPULATE_COM_CCC_TEMP;
1103
1104 -----------------------------------------------------------------
1105 -- Procedure POPULATE_ORG_MGR_MAP
1106 -- To populate table FII_ORG_MGR_MAPPINGS used by PMV
1107 -----------------------------------------------------------------
1108 PROCEDURE POPULATE_ORG_MGR_MAP IS
1109
1110 BEGIN
1111
1112 If g_debug_flag = 'Y' then
1113 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.POPULATE_ORG_MGR_MAP');
1114 END IF;
1115
1116 if g_debug_flag = 'Y' then
1117 fii_util.put_line('Populating FII_ORG_MGR_MAPPINGS table');
1118 end if;
1119
1120 g_phase := 'truncate table FII_ORG_MGR_MAPPINGS';
1121
1122 FII_UTIL.TRUNCATE_TABLE('FII_ORG_MGR_MAPPINGS', g_fii_schema, g_retcode);
1123
1124 g_phase := 'insert into FII_ORG_MGR_MAPPINGS';
1125
1126 --Bug 3750856: should use parent_manager_id when join to fii_com_cc_mappings
1127 INSERT /*+ APPEND */ INTO FII_ORG_MGR_MAPPINGS (
1128 manager_id,
1129 ccc_org_id,
1130 CREATION_DATE ,
1131 CREATED_BY ,
1132 LAST_UPDATE_DATE ,
1133 LAST_UPDATED_BY ,
1134 LAST_UPDATE_LOGIN)
1135 SELECT x.mgr_id,
1136 company_cost_center_org_id,
1137 SYSDATE,
1138 g_fii_user_id,
1139 SYSDATE,
1140 g_fii_user_id,
1141 g_fii_login_id
1142 FROM fii_com_cc_mappings,
1143 (SELECT DISTINCT emp_id,
1144 mgr_id
1145 FROM fii_cc_mgr_hierarchies) x
1146 WHERE x.emp_id = parent_manager_id;
1147
1148 if g_debug_flag = 'Y' then
1149 fii_util.put_line('Inserted '||SQL%ROWCOUNT||' rows to FII_ORG_MGR_MAPPINGS');
1150 end if;
1151
1152 g_phase := 'gather_table_stats for FII_ORG_MGR_MAPPINGS';
1153
1154 FND_STATS.gather_table_stats
1155 (ownname => g_fii_schema,
1156 tabname => 'FII_ORG_MGR_MAPPINGS');
1157
1158 commit;
1159
1160 If g_debug_flag = 'Y' then
1161 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.POPULATE_ORG_MGR_MAP');
1162 END IF;
1163
1164 EXCEPTION
1165
1166 WHEN OTHERS THEN
1167 g_retcode := -1;
1168
1169 FII_UTIL.put_line('
1170 ----------------------------
1171 Error in Function: POPULATE_ORG_MGR_MAP
1172 Message: '||sqlerrm);
1173 fii_util.write_log(g_phase);
1174
1175 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.POPULATE_ORG_MGR_MAP');
1176
1177 RAISE;
1178
1179 END POPULATE_ORG_MGR_MAP;
1180
1181 -----------------------------------------------------------------
1182 -- PROCEDURE MAIN_CCID
1183 -----------------------------------------------------------------
1184 PROCEDURE Main_CCID (pmode IN VARCHAR2) IS
1185
1186 p_status VARCHAR2(1) := NULL;
1187 l_count NUMBER(15) := 0;
1188
1192 END IF;
1189 BEGIN
1190 If g_debug_flag = 'Y' then
1191 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.Main_CCID');
1193
1194 g_mode := pmode;
1195
1196 ---------------------------------------------------
1197 -- Initialize all global variables from profile
1198 -- options and other resources
1199 ---------------------------------------------------
1200
1201 If g_debug_flag = 'Y' then
1202 fii_util.write_log('Calling INITIALIZE');
1203 End if;
1204
1205 INITIALIZE;
1206
1207 ----------------------------------------------------
1208 -- Populate CCC - Mgr mappings temp. table
1209 -----------------------------------------------------
1210
1211 If g_debug_flag = 'Y' then
1212 fii_util.write_log('Calling LOAD_CCC_MGR');
1213 End if;
1214
1215 FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR (p_status);
1216
1217 IF p_status = -1 then
1218 fii_util.write_log('Error in FII_GL_EXTRACTION_UTIL.LOAD_CCC_MGR ...');
1219 fii_util.write_log('Table FII_CCC_MGR_GT is not populated');
1220 raise NO_DATA_FOUND;
1221 END IF;
1222
1223 ----------------------------------------------------
1224 -- Check if FII_CC_MGR_HIER_GT is populated
1225 -- If not, call FII_CC_MGR_SUP_C.Populate_HIER_TMP
1226 ----------------------------------------------------
1227
1228 If g_debug_flag = 'Y' then
1229 fii_util.write_log('Make sure FII_CC_MGR_HIER_GT is populated');
1230 End if;
1231
1232 begin
1233 select 1 into l_count from FII_CC_MGR_HIER_GT
1234 where rownum = 1;
1235 exception
1236 when NO_DATA_FOUND then
1237 l_count := 0;
1238 end;
1239
1240 if l_count = 0 then
1241 FII_CC_MGR_SUP_C.Populate_HIER_TMP;
1242 end if;
1243
1244 ----------------------------------------------------
1245 -- Populate temp table with updated ccids
1246 ----------------------------------------------------
1247
1248 If g_debug_flag = 'Y' then
1249 fii_util.write_log('Calling POPULATE_COM_CCC_TEMP');
1250 End if;
1251
1252 POPULATE_COM_CCC_TEMP;
1253
1254 ---------------------------------------------------
1255 -- pmode is 'L' for initial load and 'I' for incremental load
1256 ---------------------------------------------------
1257 IF (g_mode = 'L') THEN
1258 ------------------------------------------------------
1259 -- Populate fii_com_ccc_mappings in initial pmode
1260 ------------------------------------------------------
1261 FII_UTIL.TRUNCATE_TABLE('FII_COM_CC_MAPPINGS', g_fii_schema, g_retcode);
1262
1263 If g_debug_flag = 'Y' then
1264 fii_util.write_log('Calling INITIAL_LOAD');
1265 End if;
1266
1267 INITIAL_LOAD;
1268
1269 If g_debug_flag = 'Y' then
1270 fii_util.write_log('Calling INITIAL_LOAD_LOB');
1271 End if;
1272
1273 INITIAL_LOAD_LOB;
1274
1275 ELSE
1276 ------------------------------------------------------
1277 -- Populate fii_com_ccc_mappings in incremental pmode
1278 ------------------------------------------------------
1279
1280 If g_debug_flag = 'Y' then
1281 fii_util.write_log('Calling INCREMENTAL_LOAD');
1282 End if;
1283
1284 INCREMENTAL_LOAD;
1285
1286 If g_debug_flag = 'Y' then
1287 fii_util.write_log('Calling INCREMENTAL_LOAD_LOB_MERGE');
1288 End if;
1289
1290 INCREMENTAL_LOAD_LOB_MERGE;
1291
1292 END IF;
1293
1294 COMMIT;
1295
1296 If g_debug_flag = 'Y' then
1297 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.Main_CCID');
1298 END IF;
1299
1300 EXCEPTION
1301 WHEN OTHERS THEN
1302 FII_UTIL.put_line('
1303 ----------------------------
1304 Error in Function: MAIN_CCID
1305 Message: '||sqlerrm);
1306 rollback;
1307 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.Main_CCID');
1308 raise;
1309 END MAIN_CCID;
1310
1311 -----------------------------------------------------------------
1312 -- PROCEDURE MAIN
1313 --
1314 -- Populate two helper tables: FII_COM_CC_MAPPINGS and FII_CC_MGR_SUP
1315 -----------------------------------------------------------------
1316 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2,
1317 retcode IN OUT NOCOPY VARCHAR2,
1318 pmode IN VARCHAR2) IS
1319
1320 l_ret_val BOOLEAN := FALSE;
1321 l_count NUMBER(15);
1322
1323 BEGIN
1324 If g_debug_flag = 'Y' then
1325 FII_MESSAGE.Func_Ent('FII_GL_COMCCH_C.Main');
1326 END IF;
1327
1328 errbuf := NULL;
1329 retcode := 0;
1330 g_mode := pmode;
1331
1335
1332 -- First, populate FII_CC_MGR_SUP
1333 -- We will also populate FII_CC_MGR_HIER_GT
1334 g_phase := 'Calling the procedure in package FII_CC_MGR_SUP_C';
1336 if g_mode = 'L' then
1337 FII_CC_MGR_SUP_C.Init_Load (errbuf, retcode);
1338 else
1339 FII_CC_MGR_SUP_C.Incre_Update (errbuf, retcode);
1340 end if;
1341
1342
1343 -- Then, populate FII_COM_CC_MAPPINGS
1344 g_phase := 'Populate FII_COM_CC_MAPPINGS';
1345
1346 FII_GL_COMCCH_C.MAIN_CCID (g_mode);
1347
1348 -- Call the procedure to populate table FII_ORG_MGR_MAPPINGS used in PMV
1349 g_phase := 'Populate FII_ORG_MGR_MAPPINGS';
1350
1351 POPULATE_ORG_MGR_MAP;
1352
1353 --Finally, check missing ccc mgr (i.e. CCC without MGR assigned)
1354 g_phase := 'Check missing ccc mgr...';
1355
1356 l_count := FII_GL_EXTRACTION_UTIL.CHECK_MISSING_CCC_MGR;
1357 if l_count > 0 then
1358 l_ret_val := FND_CONCURRENT.Set_Completion_Status
1359 (status => 'WARNING',
1360 message => 'Some company cost center organizations have no managers assigned to them.');
1361 end if;
1362
1363 If g_debug_flag = 'Y' then
1364 FII_MESSAGE.Func_Succ('FII_GL_COMCCH_C.Main');
1365 END IF;
1366
1367 EXCEPTION
1368
1369 WHEN OTHERS THEN
1370 errbuf := sqlerrm;
1371 retcode := sqlcode;
1372 FII_UTIL.Write_Log ('FII_GL_COMCCH_C.Main: error in phase '|| g_phase);
1373 FII_UTIL.Write_Log ( substr(sqlerrm,1,180) );
1374 l_ret_val := FND_CONCURRENT.Set_Completion_Status
1375 (status => 'ERROR', message => substr(sqlerrm,1,180));
1376 FII_MESSAGE.Func_Fail('FII_GL_COMCCH_C.Main');
1377 rollback;
1378
1379 END MAIN;
1380
1381 END FII_GL_COMCCH_C;