[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:
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:
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:
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
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
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