[Home] [Help]
213: END IF;
214:
215: SELECT COUNT(*)
216: INTO nRows
217: FROM GL_ACCTS_MAP_INT_GT;
218:
219: -- set statistics on the interface table for the CBO
220: set_stats('GL_ACCTS_MAP_INT_GT',
221: 'GL_ACCTS_MAP_INT_GT_U1',
216: INTO nRows
217: FROM GL_ACCTS_MAP_INT_GT;
218:
219: -- set statistics on the interface table for the CBO
220: set_stats('GL_ACCTS_MAP_INT_GT',
221: 'GL_ACCTS_MAP_INT_GT_U1',
222: nRows);
223: EXCEPTION
224: WHEN GL_INVALID_MAPPING_NAME THEN
217: FROM GL_ACCTS_MAP_INT_GT;
218:
219: -- set statistics on the interface table for the CBO
220: set_stats('GL_ACCTS_MAP_INT_GT',
221: 'GL_ACCTS_MAP_INT_GT_U1',
222: nRows);
223: EXCEPTION
224: WHEN GL_INVALID_MAPPING_NAME THEN
225: raise GL_INVALID_MAPPING_NAME;
598: --
599: -- Procedure
600: -- segment_map
601: -- Purpose
602: -- Maps the accounts in the GL_ACCTS_MAP_INT_GT using the
603: -- segment rules. It creates and runs one dynamic SQL statement that
604: -- applies the mapping rules and populates the to_segment
605: --
606: -- An example of what the SQL statement might look like is given below:
604: -- applies the mapping rules and populates the to_segment
605: --
606: -- An example of what the SQL statement might look like is given below:
607: --
608: -- UPDATE GL_ACCTS_MAP_INT_GT map
609: -- SET (from_summary_flag, to_ccid, to_segment1, to_segment2, to_segment3,
610: -- to_segment4, to_segment5, to_segment6, to_segment7, to_segment8,
611: -- to_segment9, to_segment10) =
612: -- (SELECT from_cc.summary_flag, null,
752:
753: no_rollup_rules BOOLEAN := true;
754:
755: BEGIN
756: update_1 := 'UPDATE GL_ACCTS_MAP_INT_GT map ' ||
757: 'SET (from_summary_flag, to_ccid';
758:
759: select_1 := 'SELECT from_cc.summary_flag, null';
760: from_1 := ' FROM gl_code_combinations from_cc';
889: --
890: -- Procedure
891: -- account_map
892: -- Purpose
893: -- Maps the accounts in the GL_ACCTS_MAP_INT_GT using the
894: -- account rules. It creates and runs one dynamic SQL statement that
895: -- applies the mapping rules.
896: --
897: -- An example of what the SQL statement may look like is given below
895: -- applies the mapping rules.
896: --
897: -- An example of what the SQL statement may look like is given below
898: --
899: -- UPDATE GL_ACCTS_MAP_INT_GT map
900: -- SET (from_summary_flag, to_ccid, to_segment1, to_segment2, to_segment3,
901: -- to_segment4, to_segment5, to_segment6, to_segment7, to_segment8,
902: -- to_segment9, to_segment10) =
903: -- (SELECT from_cc.summary_flag, to_cc.code_combination_id,
995: nsegments => source_nSegments,
996: segments => source_segments
997: );
998:
999: update_1 := 'UPDATE /*+ cardinality(map 1) */ GL_ACCTS_MAP_INT_GT map ' ||
1000: 'SET(from_summary_flag, to_ccid';
1001:
1002: select_1 := 'SELECT from_cc.summary_flag, to_cc.code_combination_id';
1003:
1073: -- The first update statement, which populates to_ccid for code combinations
1074: -- that already exist in gl_code_combinations, and the error_codes
1075: -- 'NO_MAPPING' and 'INVALID_FROM_CCID' is as follows
1076: --
1077: -- UPDATE GL_ACCTS_MAP_INT_GT map
1078: -- SET to_ccid =
1079: -- (SELECT to_cc.code_combination_id
1080: -- FROM GL_CODE_COMBINATIONS to_cc
1081: --
1103: --
1104: -- The second update statement, run only if create_ccid is true, populates
1105: -- to_ccid for all code combinations that had ccids created in this procedure.
1106: --
1107: -- UPDATE GL_ACCTS_MAP_INT_GT map
1108: -- SET to_ccid =
1109: -- (SELECT cc.code_combination_id
1110: -- FROM GL_CODE_COMBINATIONS cc
1111: -- WHERE cc.chart_of_accounts_id = v_to_coa_id
1164: -- combination existed (from_summary_flag not null) but no target code
1165: -- combination could be created
1166: CURSOR c_missing_ccid IS
1167: SELECT from_ccid
1168: FROM GL_ACCTS_MAP_INT_GT map
1169: WHERE map.coa_mapping_id = mapping_id
1170: AND map.to_ccid IS NULL
1171: AND map.from_summary_flag IS NOT NULL;
1172:
1183: -- calls have been made.
1184: dummy BOOLEAN;
1185:
1186: BEGIN
1187: update_1 := 'UPDATE GL_ACCTS_MAP_INT_GT map SET to_ccid = ' ||
1188: '(SELECT to_cc.code_combination_id ' ||
1189: 'FROM gl_code_combinations to_cc ' ||
1190: 'WHERE to_cc.chart_of_accounts_id = ' || TO_CHAR(to_coa_id);
1191:
1188: '(SELECT to_cc.code_combination_id ' ||
1189: 'FROM gl_code_combinations to_cc ' ||
1190: 'WHERE to_cc.chart_of_accounts_id = ' || TO_CHAR(to_coa_id);
1191:
1192: update_2 := 'UPDATE GL_ACCTS_MAP_INT_GT map SET to_ccid = ' ||
1193: '(SELECT cc.code_combination_id ' ||
1194: 'FROM GL_CODE_COMBINATIONS cc ' ||
1195: 'WHERE cc.chart_of_accounts_id = ' || TO_CHAR(to_coa_id);
1196:
1238: IF create_ccid THEN
1239:
1240: -- For each of the code combinations without a corresponding id, we will
1241: -- attempt to create a code combination id, and then populate the
1242: -- GL_ACCTS_MAP_INT_GT table accordingly with those ids, and
1243: -- the appropriate error code for those code combinations for which a
1244: -- code combination id could not be created.
1245: FOR missing_account IN c_missing_ccid LOOP
1246:
1249: -- array.
1250: FOR i IN 1..g_target_nSegments LOOP
1251: EXECUTE IMMEDIATE
1252: 'SELECT TO_' || app_col_name_list(i) ||
1253: ' FROM GL_ACCTS_MAP_INT_GT ' ||
1254: 'WHERE coa_mapping_id = :1 ' ||
1255: ' AND from_ccid = :2 '
1256: INTO new_flex_combination(i)
1257: USING IN mapping_id, IN missing_account.from_ccid;
1295: EXECUTE IMMEDIATE update_2;
1296:
1297: -- populate the error code column for account code combinations for which
1298: -- a code combination could not be created
1299: UPDATE GL_ACCTS_MAP_INT_GT map
1300: SET error_code = 'UNABLE_TO_CREATE_NEW_CCID'
1301: WHERE coa_mapping_id = mapping_id
1302: AND to_ccid IS NULL
1303: AND error_code IS NULL;
1341: END IF;
1342:
1343: -- this gets the code combination ids for those code combinations that are
1344: -- already in the gl_code_combinations table, and populates the
1345: -- gl_accts_map_int_gt table. It also creates new ccids if
1346: -- appropriate and uses those to populate the table as well. This also
1347: -- populates the error codes.
1348: end_map(mapping_id, create_ccid, to_coa_id, from_coa_id);
1349: EXCEPTION