[Home] [Help]
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:
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:
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
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:
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 ,
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 ,
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:
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:
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;
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: ---------------------------------
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 ,
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 ,
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:
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:
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,
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:
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,
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
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:
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
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:
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,
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:
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: ---------------------------------
483: If g_debug_flag = 'Y' then
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 ,
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:
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,
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
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:
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
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:
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,
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:
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
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 ,
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 ,
600: COST_CENTER_ID ,
601: COMPANY_ID ,
603: VALID_MGR_FLAG ,
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
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:
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:
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
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
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;
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;
897: fii_util.print_timer('Duration');
898: end if;
899:
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: ---------------------------------------------------------------------------------------
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:
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,
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
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: ---------------------------------------------------------------------------------------
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,
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)
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
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
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 ,
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;
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;
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
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:
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