1 PACKAGE BODY GL_ACCOUNTS_MAP_GRP as
2 /* $Header: glgcmapb.pls 120.10.12010000.3 2008/10/15 11:55:04 schaava ship $ */
3
4
5 --
6 -- Private Global Variables
7 --
8
9 -- whether or not debug messages should be printed
10 g_debug_flag BOOLEAN;
11
12 -- information on the flexfield. This is the same for the source and target
13 -- charts of accounts.
14 g_flexfield fnd_flex_key_api.flexfield_type;
15
16 -- information on the target chart of accounts structure
17 g_target_structure fnd_flex_key_api.structure_type;
18
19 -- list of segments for the target chart of accounts
20 g_target_segments fnd_flex_key_api.segment_list;
21
22 -- number of segments for the target chart of accounts
23 g_target_nSegments NUMBER;
24
25 -- number of segment mapping rules defined
26 g_num_segment_rules NUMBER;
27
28 -- number of account mapping rules defined
29 g_num_account_rules NUMBER;
30
31 -- the following are constants used throughout the package body
32 g_application_id CONSTANT NUMBER := 101;
33 g_id_flex_code CONSTANT VARCHAR2(10) := 'GL#';
34 g_application_short_name CONSTANT VARCHAR2(10) := 'SQLGL';
35
36
37 --
38 -- Private Functions and Procedures
39 --
40
41
42 --
43 -- Procedure
44 -- writeToLog
45 -- Purpose
46 -- Writes a debug message to the log file.
47 -- Copied from GCS_UTILITIES_PKG.
48 -- History
49 -- 17-FEB-03 M. Ward Created
50 -- Arguments
51 -- buf debug message to write
52 procedure writeToLog (buf IN Varchar2 := NULL) is
53 errBuf Varchar2(2000);
54 begin
55
56 -- May be a message on the stack or
57 -- a string passed in via the arg
58 if buf IS NULL then
59 errBuf := substr( FND_MESSAGE.get, 1, 2000 );
60 else
61 errBuf := substr( buf, 1, 2000 );
62 end if;
63
64 -- Do nothing if there is no message waiting
65 if errBuf IS NOT NULL then
66 FND_FILE.new_line( FND_FILE.log, 1 );
67 FND_FILE.put_line( FND_FILE.log, errBuf );
68 end if;
69
70 end writeToLog;
71
72
73 --
74 -- Procedure
75 -- writeDebug
76 -- Purpose
77 -- Writes a debug message to the log file if the debug flag is true
78 -- Copied from GCS_UTILITIES_PKG.
79 -- History
80 -- 17-FEB-03 M. Ward Created
81 -- Arguments
82 -- buf debug message to write
83 PROCEDURE writeDebug (buf IN Varchar2) is
84 begin
85 if buf IS NOT NULL then
86 if g_debug_flag then
87 writeToLog(buf);
88 end if;
89 end if;
90 end writeDebug;
91
92 --
93 -- Procedure
94 -- set_stats
95 -- Purpose
96 -- Set table and index stats in an autonomous transaction so that a
97 -- commit does not take place.
98 -- History
99 -- 13-NOV-03 M. Ward Created
100 -- Arguments
101 -- table_name Name of the table
102 -- index_name Name of the index
103 -- num_rows Number of rows in the table and index
104 --
105 PROCEDURE set_stats( table_name VARCHAR2,
106 index_name VARCHAR2,
107 num_rows NUMBER) IS
108 PRAGMA AUTONOMOUS_TRANSACTION;
109 BEGIN
110 FND_STATS.set_table_stats('GL', table_name, num_rows, NULL, NULL);
111 FND_STATS.set_index_stats('GL', index_name, num_rows,
112 NULL, NULL, NULL, NULL, NULL, NULL);
113 END set_stats;
114
115 --
116 -- Procedure
117 -- init_map
118 -- Purpose
119 -- Retrieves, validates, and stores the chart of accounts mapping
120 -- information for the given mapping name
121 -- History
122 -- 09-MAY-02 M. Ward Created
123 -- Arguments
124 -- mapping_name Name of the chart of accounts mapping to use
125 -- mapping_id ID of the chart of accounts mapping to use
126 -- to_coa_id To Chart of Accounts ID
127 -- from_coa_id From Chart of Accounts ID
128 --
129 PROCEDURE init_map(mapping_name IN VARCHAR2,
130 mapping_id OUT NOCOPY NUMBER,
131 to_coa_id OUT NOCOPY NUMBER,
132 from_coa_id OUT NOCOPY NUMBER
133 ) IS
134
135 -- these are used for simple validation tests on the mapping
136 start_date_active DATE;
137 end_date_active DATE;
138 flex_rule_total NUMBER;
139 seg_rule_total NUMBER;
140
141 -- cursor to gather information on the mapping with the given name
142 CURSOR c_mapping_info IS
143 SELECT coa_mapping_id,
144 from_coa_id,
145 to_coa_id,
146 start_date_active,
147 end_date_active
148 FROM gl_coa_mappings
149 WHERE name = mapping_name;
150
151 -- used to store the number of rows in the interface table
152 nRows NUMBER;
153
154 BEGIN
155 OPEN c_mapping_info;
156 FETCH c_mapping_info INTO mapping_id, from_coa_id, to_coa_id,
157 start_date_active, end_date_active;
158 IF c_mapping_info%NOTFOUND THEN
159 CLOSE c_mapping_info;
160 raise GL_INVALID_MAPPING_NAME;
161 END IF;
162 CLOSE c_mapping_info;
163
164 -- This compares SYSDATE to start/end_date_active to see if the mapping
165 -- is active. If not, this raises an exception.
166 IF (start_date_active IS NOT NULL AND start_date_active > SYSDATE) OR
167 (end_date_active IS NOT NULL AND end_date_active < SYSDATE) THEN
168 raise GL_DISABLED_MAPPING;
169 END IF;
170
171 -- Get the number of account rules
172 SELECT COUNT(*)
173 INTO g_num_account_rules
174 FROM gl_cons_flexfield_map
175 WHERE coa_mapping_id = mapping_id;
176
177 -- Get the number of segments taken care of in all the segment rules
178 SELECT COUNT(DISTINCT to_application_column_name)
179 INTO g_num_segment_rules
180 FROM gl_cons_segment_map
181 WHERE coa_mapping_id = mapping_id;
182
183 -- This statement is necessary to use the fnd_flex_key_api package's
184 -- subroutines.
185 fnd_flex_key_api.set_session_mode('customer_data');
186
187 -- gets information on the target flexfield
188 g_flexfield := fnd_flex_key_api.find_flexfield
189 (appl_short_name => g_application_short_name,
190 flex_code => g_id_flex_code
191 );
192
193 -- gets information on the structure of the target chart of accounts
194 g_target_structure := fnd_flex_key_api.find_structure
195 (flexfield => g_flexfield,
196 structure_number => to_coa_id
197 );
198
199 -- gets the list of segments for the chart of accounts
200 fnd_flex_key_api.get_segments(flexfield => g_flexfield,
201 structure => g_target_structure,
202 nsegments => g_target_nSegments,
203 segments => g_target_segments
204 );
205
206
207 -- if there are no account rules and no segment rules, or if the
208 -- segment rules do not span the entire set of segments, this must
209 -- be an invalid mapping.
210 IF (g_num_account_rules=0 AND g_num_segment_rules=0) OR
211 (g_num_segment_rules>0 AND g_num_segment_rules<g_target_nSegments) THEN
212 raise GL_INVALID_MAPPING_RULES;
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',
222 nRows);
223 EXCEPTION
224 WHEN GL_INVALID_MAPPING_NAME THEN
225 raise GL_INVALID_MAPPING_NAME;
226 WHEN GL_DISABLED_MAPPING THEN
227 raise GL_DISABLED_MAPPING;
228 WHEN GL_INVALID_MAPPING_RULES THEN
229 raise GL_INVALID_MAPPING_RULES;
230 WHEN OTHERS THEN
231 raise GL_MAP_UNEXPECTED_ERROR;
232 END init_map;
233
234
235 --
236 -- Procedure
237 -- interim_rollup_map
238 -- Purpose
239 -- Creates interim information in the global temporary table,
240 -- GL_ACCTS_MAP_SEG<x>_GT tables used by the rollup
241 -- segment rules. The interim information is a mapping between
242 -- the source flex segment values and the target flex segment
243 -- values. It also contains a summary flag to indicate if it is a
244 -- summary accounts mapping or a regular accounts mapping. The
245 -- interim information is used to improve performance with the
246 -- rollup segment rules.
247 --
248 -- Examples of the insert statment that inputs all the data to the interim
249 -- table follow. The first is for a segment that is not table validated. The
250 -- second is for a table validated segment.
251 --
252 -- Segments that are not table validated --
253 --
254 -- INSERT INTO GL_ACCTS_MAP_SEG<x>_GT(
255 --
256 -- SELECT -- Detail Rollup Via Parent --
257 -- GL_FV.flex_value source_flex_value,
258 -- GL_CSM.single_value target_flex_value,
259 -- 'N' summary_flag
260 -- FROM fnd_flex_values GL_FV,
261 -- fnd_flex_value_hierarchies FVH,
262 -- gl_cons_segment_map GL_CSM
263 -- WHERE GL_CSM.coa_mapping_id = v_mapping_id
264 -- AND GL_CSM.to_application_column_name =
265 -- v_to_application_column_name
266 -- AND GL_CSM.parent_rollup_value = FVH.parent_flex_value
267 -- AND GL_CSM.segment_map_type = 'P'
268 -- AND FVH.flex_value_set_id = GL_CSM.from_value_set_id
269 -- AND GL_FV.flex_value_set_id = GL_CSM.from_value_set_id
270 -- AND GL_FV.summary_flag = 'N'
271 -- AND GL_FV.flex_value BETWEEN FVH.child_flex_value_low
272 -- AND FVH.child_flex_value_high
273 --
274 -- UNION -- Detail Rollup Ranges --
275 --
276 -- SELECT GL_FV.flex_value source_flex_value,
277 -- GL_CSM.single_value target_flex_value,
278 -- 'N' summary_flag
279 -- FROM fnd_flex_values GL_FV,
280 -- gl_cons_flex_hierarchies CFH,
281 -- gl_cons_segment_map GL_CSM
282 -- WHERE GL_CSM.coa_mapping_id = v_mapping_id
283 -- AND GL_CSM.to_application_column_name =
284 -- v_to_application_column_name
285 -- AND GL_CSM.segment_map_id = CFH.segment_map_id
286 -- AND GL_CSM.single_value = CFH.parent_flex_value
287 -- AND GL_CSM.segment_map_type = 'R'
288 -- AND GL_FV.flex_value_set_id = GL_CSM.from_value_set_id
289 -- AND GL_FV.summary_flag = 'N'
290 -- AND GL_FV.flex_value BETWEEN CFH.child_flex_value_low
291 -- AND CFH.child_flex_value_high
292 --
293 -- UNION -- Parent Value --
294 --
295 -- SELECT GL_CSM.parent_rollup_value source_flex_value,
296 -- GL_CSM.single_value target_flex_value,
297 -- 'Y' summary_flag
298 -- FROM gl_cons_segment_map GL_CSM
299 -- WHERE GL_CSM.coa_mapping_id = v_mapping_id
300 -- AND GL_CSM.to_application_column_name =
301 -- v_to_application_column_name
302 -- AND GL_CSM.segment_map_type = 'V'
303 --
304 -- UNION -- Parent Rollup Ranges --
305 --
306 -- SELECT GL_FV.flex_value source_flex_value,
307 -- GL_CSM.single_value target_flex_value,
308 -- 'Y' summary_flag
309 -- FROM fnd_flex_values GL_FV,
310 -- gl_cons_flex_hierarchies CFH,
311 -- gl_cons_segment_map GL_CSM
312 -- WHERE GL_CSM.coa_mapping_id = v_mapping_id
313 -- AND GL_CSM.to_application_column_name =
314 -- v_to_application_column_name
315 -- AND GL_CSM.segment_map_id = CFH.segment_map_id
316 -- AND GL_CSM.single_value = CFH.parent_flex_value
317 -- AND GL_CSM.segment_map_type = 'U'
318 -- AND GL_FV.flex_value_set_id = GL_CSM.from_value_set_id
319 -- AND GL_FV.summary_flag = 'Y'
320 -- AND GL_FV.flex_value BETWEEN CFH.child_flex_value_low
321 -- AND CFH.child_flex_value_high)
322 --
323 -- Segments that are table validated --
324 --
325 -- INSERT INTO GL_ACCTS_MAP_SEG<x>_GT(
326 --
327 -- SELECT DISTINCT -- Detail Rollup Via Parent --
328 -- GL_FV.:val_column_name source_flex_value,
329 -- GL_CSM.single_value target_flex_value,
330 -- 'N' summary_flag
331 -- FROM :val_tab_name GL_FV,
332 -- fnd_flex_value_hierarchies FVH,
333 -- gl_cons_segment_map GL_CSM
334 -- WHERE GL_CSM.coa_mapping_id = v_mapping_id
335 -- AND GL_CSM.to_application_column_name =
336 -- v_to_application_column_name
337 -- AND GL_CSM.parent_rollup_value = FVH.parent_flex_value
338 -- AND GL_CSM.segment_map_type = 'P'
339 -- AND FVH.flex_value_set_id = GL_CSM.from_value_set_id
340 -- AND GL_FV.:val_col_name
341 -- BETWEEN FVH.child_flex_value_low
342 -- AND FVH.child_flex_value_high
343 --
344 -- UNION -- Detail Rollup Ranges --
345 --
346 -- SELECT DISTINCT GL_FV.:val_col_name source_flex_value,
347 -- GL_CSM.single_value target_flex_value,
348 -- 'N' summary_flag
349 -- FROM :val_tab_name GL_FV,
350 -- gl_cons_flex_hierarchies CFH,
351 -- gl_cons_segment_map GL_CSM
352 -- WHERE GL_CSM.coa_mapping_id = v_mapping_id
353 -- AND GL_CSM.to_application_column_name =
354 -- v_to_application_column_name
355 -- AND GL_CSM.segment_map_id = CFH.segment_map_id
356 -- AND GL_CSM.single_value = CFH.parent_flex_value
357 -- AND GL_CSM.segment_map_type = 'R'
358 -- AND GL_FV.:val_col_name
359 -- BETWEEN CFH.child_flex_value_low
360 -- AND CFH.child_flex_value_high
361 --
362 -- UNION -- Parent Value --
363 --
364 -- SELECT GL_CSM.parent_rollup_value source_flex_value,
365 -- GL_CSM.single_value target_flex_value,
366 -- 'Y' summary_flag
367 -- FROM gl_cons_segment_map GL_CSM
368 -- WHERE GL_CSM.coa_mapping_id = v_mapping_id
369 -- AND GL_CSM.to_application_column_name =
370 -- v_to_application_column_name
371 -- AND GL_CSM.segment_map_type = 'V'
372 --
373 -- UNION -- Parent Rollup Ranges --
374 --
375 -- SELECT GL_FV.flex_value source_flex_value,
376 -- GL_CSM.single_value target_flex_value,
377 -- 'Y' summary_flag
378 -- FROM fnd_flex_values GL_FV,
379 -- gl_cons_flex_hierarchies CFH,
380 -- gl_cons_segment_map GL_CSM
381 -- WHERE GL_CSM.coa_mapping_id = v_mapping_id
382 -- AND GL_CSM.to_application_column_name =
383 -- v_to_application_column_name
384 -- AND GL_CSM.segment_map_id = CFH.segment_map_id
385 -- AND GL_CSM.single_value = CFH.parent_flex_value
386 -- AND GL_CSM.segment_map_type = 'U'
387 -- AND GL_FV.flex_value_set_id = GL_CSM.from_value_set_id
388 -- AND GL_FV.summary_flag = 'Y'
389 -- AND GL_FV.flex_value BETWEEN CFH.child_flex_value_low
390 -- AND CFH.child_flex_value_high)
391 --
392 --
393 -- History
394 -- 15-MAY-02 M. Ward Created
395 -- Arguments
396 -- mapping_id ID of the chart of accounts mapping to use
397 -- to_application_column_name application column name for the target
398 -- from_value_set_id value set id for the source
399 --
400 PROCEDURE interim_rollup_map(mapping_id IN NUMBER,
401 to_application_column_name IN VARCHAR2,
402 from_value_set_id IN NUMBER
403 ) IS
404 val_table_name fnd_flex_validation_tables.application_table_name%TYPE;
405 val_column_name fnd_flex_validation_tables.value_column_name%TYPE;
406
407 detail_parent_flag VARCHAR2(1);
408 detail_ranges_flag VARCHAR2(1);
409 summary_ranges_flag VARCHAR2(1);
410 summary_parent_flag VARCHAR2(1);
411
412 insert_1 VARCHAR2(3000);
413
414 insert_1_col fnd_flex_validation_tables.application_table_name%TYPE;
415 insert_1_table fnd_flex_validation_tables.value_column_name%TYPE;
416 insert_1_join VARCHAR2(100);
417 insert_1_select_type VARCHAR2(40);
418
419 -- selects the table and column name used for table validation for the
420 -- source chart of accounts, if applicable.
421 CURSOR c_check_table_validated IS
422 SELECT fvt.application_table_name,
423 fvt.value_column_name
424 FROM fnd_flex_validation_tables fvt,
425 fnd_flex_value_sets fvs
426 WHERE fvs.flex_value_set_id = from_value_set_id
427 AND fvs.validation_type = 'F'
428 AND fvs.flex_value_set_id = fvt.flex_value_set_id;
429
430 -- number of source segment values that are duplicates of values already in
431 -- the interim table
432 num_duplicates NUMBER;
433
434 -- used to store the number of rows in the interface table
435 nRows NUMBER;
436
437 BEGIN
438 -- get information on the validation table and column, if applicable.
439 -- Otherwise, they will be null.
440 OPEN c_check_table_validated;
441 FETCH c_check_table_validated INTO val_table_name, val_column_name;
442 CLOSE c_check_table_validated;
443
444 -- get information for the flags specified below. These specify the types
445 -- of rollup rules used in this mapping
446 SELECT decode(max(decode(map.segment_map_type, 'P', 1, 0)),
447 1, 'Y', 'N'),
448 decode(max(decode(map.segment_map_type, 'R', 1, 0)),
449 1, 'Y', 'N'),
450 decode(max(decode(map.segment_map_type, 'U', 1, 0)),
451 1, 'Y', 'N'),
452 decode(max(decode(map.segment_map_type, 'V', 1, 0)),
453 1, 'Y', 'N')
454 INTO detail_parent_flag,
455 detail_ranges_flag,
456 summary_ranges_flag,
457 summary_parent_flag
458 FROM GL_CONS_SEGMENT_MAP map
459 WHERE map.coa_mapping_id = mapping_id
460 AND map.to_application_column_name =
461 interim_rollup_map.to_application_column_name;
462
463 -- clear out the interim global temporary table in case it had data in it
464 -- before.
465 EXECUTE IMMEDIATE 'DELETE FROM GL_ACCTS_MAP_SEG' ||
466 substr(to_application_column_name,8,2) || '_GT';
467
468 insert_1 := 'INSERT INTO GL_ACCTS_MAP_SEG' ||
469 substr(to_application_column_name,8,2) || '_GT (';
470
471 -- These specify the column name and table name to be used in the select
472 -- statements for populating data in the insert statement. Since the column
473 -- and table are dependent on whether the segment is table-validated, I
474 -- specify it here. These fields are used in both the select for the
475 -- detail parent and detail ranges.
476 IF val_table_name IS NOT NULL THEN
477 insert_1_col := val_column_name;
478 insert_1_table := val_table_name;
479 insert_1_join := '';
480 insert_1_select_type := 'SELECT DISTINCT';
481 ELSE
482 insert_1_col := 'flex_value';
483 insert_1_table := 'fnd_flex_values';
484 insert_1_join := 'AND GL_FV.flex_value_set_id = GL_CSM.from_value_set_id ' ||
485 'AND GL_FV.summary_flag = ''N'' ';
486 insert_1_select_type := 'SELECT DISTINCT';
487 END IF;
488
489 -- below, build the insert statement based on the types of rollup rules that
490 -- are used for this particular segment
491 IF detail_parent_flag = 'Y' THEN
492 insert_1 := insert_1 || insert_1_select_type || ' GL_FV.' || insert_1_col ||
493 ' source_flex_value, ' ||
494 'GL_CSM.single_value target_flex_value, ''N'' summary_flag ' ||
495 'FROM '|| insert_1_table || ' GL_FV, ' ||
496 'fnd_flex_value_hierarchies FVH, gl_cons_segment_map GL_CSM ' ||
497 'WHERE GL_CSM.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
498 ' AND GL_CSM.to_application_column_name = ''' ||
499 to_application_column_name || ''' ' ||
500 'AND GL_CSM.parent_rollup_value = FVH.parent_flex_value ' ||
501 'AND GL_CSM.segment_map_type = ''P'' ' ||
502 'AND FVH.flex_value_set_id = GL_CSM.from_value_set_id ' ||
503 insert_1_join ||
504 'AND GL_FV.' || insert_1_col || ' BETWEEN ' ||
505 'FVH.child_flex_value_low AND FVH.child_flex_value_high';
506 END IF;
507
508 IF detail_ranges_flag = 'Y' THEN
509 IF detail_parent_flag = 'Y' THEN
510 insert_1 := insert_1 || ' UNION ';
511 END IF;
512 insert_1 := insert_1 || insert_1_select_type || ' GL_FV.' || insert_1_col ||
513 ' source_flex_value, ' ||
514 'GL_CSM.single_value target_flex_value, ''N'' summary_flag ' ||
515 'FROM ' || insert_1_table || ' GL_FV, ' ||
516 'gl_cons_flex_hierarchies CFH, gl_cons_segment_map GL_CSM ' ||
517 'WHERE GL_CSM.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
518 ' AND GL_CSM.to_application_column_name = ''' ||
519 to_application_column_name || ''' ' ||
520 'AND GL_CSM.segment_map_id = CFH.segment_map_id ' ||
521 'AND GL_CSM.single_value = CFH.parent_flex_value ' ||
522 'AND GL_CSM.segment_map_type = ''R'' ' ||
523 insert_1_join ||
524 'AND GL_FV.' || insert_1_col || ' BETWEEN ' ||
525 'CFH.child_flex_value_low AND CFH.child_flex_value_high';
526
527
528
529 END IF;
530
531 IF summary_parent_flag = 'Y' THEN
532 IF detail_parent_flag = 'Y' OR detail_ranges_flag = 'Y' THEN
533 insert_1 := insert_1 || ' UNION ';
534 END IF;
535 insert_1 := insert_1 ||
536 'SELECT GL_CSM.parent_rollup_value source_flex_value, ' ||
537 'GL_CSM.single_value target_flex_value, ''Y'' summary_flag ' ||
538 'FROM gl_cons_segment_map GL_CSM ' ||
539 'WHERE GL_CSM.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
540 ' AND GL_CSM.to_application_column_name = ''' ||
541 to_application_column_name || ''' ' ||
542 'AND GL_CSM.segment_map_type = ''V''';
543
544
545 END IF;
546
547 IF summary_ranges_flag = 'Y' THEN
548 IF detail_parent_flag = 'Y' OR detail_ranges_flag = 'Y' OR
549 summary_parent_flag = 'Y' THEN
550 insert_1 := insert_1 || ' UNION ';
551 END IF;
552 insert_1 := insert_1 || 'SELECT GL_FV.flex_value source_flex_value, ' ||
553 'GL_CSM.single_value target_flex_value, ''Y'' summary_flag ' ||
554 'FROM fnd_flex_values GL_FV, gl_cons_flex_hierarchies CFH, ' ||
555 'gl_cons_segment_map GL_CSM ' ||
556 'WHERE GL_CSM.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
557 ' AND GL_CSM.to_application_column_name = ''' ||
558 to_application_column_name || ''' ' ||
559 'AND GL_CSM.segment_map_id = CFH.segment_map_id ' ||
560 'AND GL_CSM.single_value = CFH.parent_flex_value ' ||
561 'AND GL_CSM.segment_map_type = ''U'' ' ||
562 'AND GL_FV.flex_value_set_id = GL_CSM.from_value_set_id ' ||
563 'AND GL_FV.summary_flag = ''Y'' ' ||
564 'AND GL_FV.flex_value BETWEEN CFH.child_flex_value_low ' ||
565 'AND CFH.child_flex_value_high';
566 END IF;
567
568
569 insert_1 := insert_1 || ')';
570
571 -- DEBUG print the contents of the insert statement to the screen.
572 writedebug('insert statement for rollup rules is: ');
573 FOR i IN 0..(lengthb(insert_1)-1)/2000 LOOP
574 writedebug(SUBSTRB(insert_1, i*2000+1, 2000));
575 END LOOP;
576 writedebug(' ');
577
578 EXECUTE IMMEDIATE insert_1;
579
580 -- retrieves the total number of rows in the table (since the table was
581 -- empty before the insert statement)
582 nRows := SQL%ROWCOUNT;
583
584 -- set statistics on this interim table for the CBO
585 set_stats('GL_ACCTS_MAP_SEG' || substr(to_application_column_name,8,2) || '_GT',
586 'GL_ACCTS_MAP_SEG' ||
587 SUBSTR(to_application_column_name,8,2) || '_GT_U1',
588 nRows);
589 EXCEPTION
590 WHEN OTHERS THEN
591 IF SQLCODE = -1 THEN
592 raise GL_INVALID_MAPPING_RULES;
593 END IF;
594 raise GL_MAP_UNEXPECTED_ERROR;
595 END interim_rollup_map;
596
597
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<x> columns.
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,
613 -- '01', from_cc.segment1,
614 -- int_segment3.target_flex_value, int_segment4.target_flex_value,
615 -- from_cc.segment3, int_segment6.target_flex_value,
616 -- '0000','0000', '000','000'
617 -- FROM gl_code_combinations from_cc,
618 -- GL_ACCTS_MAP_SEG3_GT int_segment3,
619 -- GL_ACCTS_MAP_SEG4_GT int_segment4,
620 -- GL_ACCTS_MAP_SEG6_GT int_segment6
621 -- WHERE map.from_ccid = from_cc.code_combination_id
622 -- AND from_cc.chart_of_accounts_id = v_from_coa_id
623 -- AND from_cc.segment2 = int_segment3.source_flex_value
624 -- AND from_cc.segment2 = int_segment4.source_flex_value
625 -- AND from_cc.segment3 = int_segment6.source_flex_value
626 -- AND from_cc.SEGMENT4 = DECODE (NVL(from_cc.template_id,-1),
627 -- -1, from_cc.SEGMENT4, 'T')
628 -- AND from_cc.SEGMENT5 = DECODE (NVL(from_cc.template_id,-1),
629 -- -1,from_cc.SEGMENT5, 'T')
630 -- AND NVL(from_cc.template_id, -1) =
631 -- DECODE (int_segment3.summary_flag, 'Y', from_cc.template_id,
632 -- DECODE (int_segment4.summary_flag, 'Y', from_cc.template_id,
633 -- DECODE (int_segment6.summary_flag, 'Y', from_cc.template_id,-1)))
634 -- AND (from_cc.template_id IS NULL OR
635 -- (EXISTS (SELECT 'X'
636 -- FROM GL_SUMMARY_TEMPLATES st
637 -- WHERE st.template_id = from_cc.template_id
638 -- AND ST.SEGMENT1_TYPE = 'D'
639 -- AND ST.SEGMENT3_TYPE = 'D')))
640 -- )
641 -- WHERE map.coa_mapping_id = v_mapping_id
642 -- AND map.from_summary_flag IS NULL
643 --
644 -- History
645 -- 14-MAY-02 M. Ward Created
646 -- Arguments
647 -- mapping_id ID of the chart of accounts mapping to use
648 -- to_coa_id To Chart of Accounts ID
649 -- from_coa_id From Chart of Accounts ID
650 --
651 PROCEDURE segment_map(mapping_id IN NUMBER,
652 to_coa_id IN NUMBER,
653 from_coa_id IN NUMBER
654 ) IS
655
656 -- variable that is used because it must hold the return from a FETCH,
657 -- but is otherwise unused
658 dummy VARCHAR2(1);
659
660 -- buffers for keeping parts of the final update statement while it is
661 -- being built
662 update_1 VARCHAR2(600);
663 select_1 VARCHAR2(1500);
664 from_1 VARCHAR2(2000);
665 where_1 VARCHAR2(10000);
666 where_2 VARCHAR2(100);
667
668 -- bits and pieces of the where statement. All are appended to the where_1
669 -- variable after the loop.
670 where_template_buf VARCHAR2(1500) := null;
671 where_rollup_buf_1 VARCHAR2(2500) := null;
672 where_rollup_buf_2 VARCHAR2(50) := null;
673
674 parent_rule_exist VARCHAR2(1) := null;
675
676 to_app_col_name fnd_id_flex_segments.application_column_name%TYPE;
677 from_app_col_name fnd_id_flex_segments.application_column_name%TYPE;
678
679 -- this query will return a single row with 'Y' in it if there is segment
680 -- mapping that involves summary accounts
681 CURSOR rule_exist IS
682 SELECT 'Y'
683 FROM gl_cons_segment_map
684 WHERE coa_mapping_id = mapping_id
685 AND segment_map_type IN ('V','U');
686
687 -- this query returns information concerning the segment rules, including
688 -- the source and target segments, and mapping type
689 CURSOR c_to_segment_rules IS
690 SELECT max(map.segment_map_id) SEGMENT_MAP_ID,
691 decode(max(map.segment_map_type), '', 'N',
692 'C', 'C',
693 'S', 'S',
694 'P', 'R',
695 'R', 'R',
696 'V', 'R',
697 'U', 'R',
698 'N')
699 SEGMENT_MAP_TYPE,
700 max(ffs1.application_column_name) TO_APPLICATION_COLUMN_NAME,
701 ffs1.segment_num TO_SEGMENT_NUM,
702 ffs1.flex_value_set_id TO_VALUE_SET_ID,
703 ffs2.application_column_name FROM_APPLICATION_COLUMN_NAME,
704 ffs2.segment_num FROM_SEGMENT_NUM,
705 ffs2.flex_value_set_id FROM_VALUE_SET_ID,
706 decode(max(map.segment_map_type), 'S', max(map.single_value),
707 NULL)
708 SINGLE_VALUE
709
710 FROM FND_ID_FLEX_SEGMENTS ffs2,
711 GL_CONS_SEGMENT_MAP map,
712 FND_ID_FLEX_SEGMENTS ffs1
713
714 WHERE ffs1.application_id = g_application_id
715 AND ffs1.id_flex_code = g_id_flex_code
716 AND ffs1.enabled_flag = 'Y'
717 AND ffs1.id_flex_num = to_coa_id
718 AND map.to_value_set_id (+)= ffs1.flex_value_set_id
719 AND map.to_application_column_name (+)= ffs1.application_column_name
720 AND map.coa_mapping_id (+)= mapping_id
721 AND ffs2.application_id (+)= g_application_id
722 AND ffs2.id_flex_code (+)= g_id_flex_code
723 AND ffs2.enabled_flag (+)= 'Y'
724 AND ffs2.id_flex_num (+)= from_coa_id
725 AND ffs2.application_column_name (+)=
726 nvl(map.from_application_column_name, -1)
727 AND ffs2.flex_value_set_id (+)= nvl(map.from_value_set_id, -1)
728
729 GROUP BY map.coa_mapping_id,
730 ffs1.segment_num,
731 ffs1.flex_value_set_id,
732 ffs2.application_column_name,
733 ffs2.segment_num,
734 ffs2.flex_value_set_id
735 ORDER BY ffs1.segment_num;
736
737 -- This gets all the source segments that were not involved in the segment
738 -- mappings
739 CURSOR c_unmapped_from_segments IS
740 SELECT ffs.application_column_name UNMAPPED_FROM_SEGMENT
741 FROM FND_ID_FLEX_SEGMENTS ffs
742 WHERE ffs.application_id = g_application_id
743 AND ffs.id_flex_code = g_id_flex_code
744 AND ffs.enabled_flag = 'Y'
745 AND ffs.id_flex_num = from_coa_id
746 AND ffs.application_column_name NOT IN (
747 SELECT map.from_application_column_name
748 FROM GL_CONS_SEGMENT_MAP map
749 WHERE map.coa_mapping_id = mapping_id
750 AND map.from_application_column_name IS NOT NULL
751 );
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';
761 where_1 := ' WHERE map.from_ccid = from_cc.code_combination_id' ||
762 ' AND from_cc.chart_of_accounts_id = ' || TO_CHAR(from_coa_id);
763
764 where_2 := 'WHERE map.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
765 ' AND map.from_summary_flag IS NULL';
766
767 -- Loops through each of the target segment rules. There are as many
768 -- target segment rules as there are target segments.
769 FOR seg_rule IN c_to_segment_rules LOOP
770 to_app_col_name := seg_rule.to_application_column_name;
771 from_app_col_name := seg_rule.from_application_column_name;
772
773 update_1 := update_1 || ', to_' || to_app_col_name;
774
775 -- This handles the copy value case
776 IF seg_rule.segment_map_type = 'C' THEN
777 select_1 := select_1 || ', from_cc.' || from_app_col_name;
778
779 -- see if the mapping has rollup rules using summary accounts
780 IF parent_rule_exist IS NULL THEN
781 OPEN rule_exist;
782 -- we fetch into a dummy variable because we are interested in whether
783 -- a value was returned at all, rather than the value that was returned
784 FETCH rule_exist INTO dummy;
785 IF (rule_exist%FOUND) THEN
786 parent_rule_exist := 'Y';
787 where_template_buf := ' AND (from_cc.template_id IS NULL OR ' ||
788 '(EXISTS (SELECT ''X'' FROM GL_SUMMARY_TEMPLATES st ' ||
789 'WHERE st.template_id = from_cc.template_id';
790 ELSE
791 parent_rule_exist := 'N';
792 END IF;
793 CLOSE rule_exist;
794 END IF;
795
796 -- if a rollup rule with summary accounts is used and copy value is
797 -- used on one or more segments, accounts with parent values on those
798 -- segments must be excluded
799 IF parent_rule_exist = 'Y' THEN
800 where_template_buf := where_template_buf || ' AND st.' ||
801 from_app_col_name || '_TYPE = ''D''';
802
803 END IF;
804
805 -- This handles the single value case
806 ELSIF seg_rule.segment_map_type = 'S' THEN
807 select_1 := select_1 || ', ''' || seg_rule.single_value || '''';
808
809 -- This handles the case in which there are rollup rules
810 ELSIF seg_rule.segment_map_type = 'R' THEN
811 no_rollup_rules := false;
812
813 interim_rollup_map(mapping_id, to_app_col_name,
814 seg_rule.from_value_set_id);
815
816 select_1 := select_1 || ', int_' || to_app_col_name ||
817 '.target_flex_value';
818 from_1 := from_1 || ', GL_ACCTS_MAP_SEG' || substr(to_app_col_name,8,2) ||
819 '_GT int_' || to_app_col_name;
820 where_1 := where_1 || ' AND from_cc.' || from_app_col_name ||
821 ' = int_' || to_app_col_name || '.source_flex_value';
822
823 -- If this is the first rollup rule processed, initialize the appropriate
824 -- variables
825 IF where_rollup_buf_1 IS NULL THEN
826 where_rollup_buf_1 := ' AND NVL(from_cc.template_id, -1) = ';
827 where_rollup_buf_2 := '-1';
828 END IF;
829 where_rollup_buf_1 := where_rollup_buf_1 || 'DECODE(int_' ||
830 to_app_col_name ||
831 '.summary_flag, ''Y'', from_cc.template_id, ';
832 where_rollup_buf_2 := where_rollup_buf_2 || ')';
833
834 -- Otherwise, the mapping type is 'N', which is an error
835 ELSE
836 raise GL_INVALID_MAPPING_RULES;
837 END IF;
838
839 END LOOP;
840
841 -- all unmapped source segments must either have 'T' in any template. The
842 -- following test added to the where clause checks for that.
843 FOR unmapped_seg IN c_unmapped_from_segments LOOP
844 where_1 := where_1 || ' AND from_cc.' ||
845 unmapped_seg.unmapped_from_segment ||
846 ' = DECODE(NVL(from_cc.template_id, -1), -1, from_cc.' ||
847 unmapped_seg.unmapped_from_segment || ', ''T'')';
848
849 END LOOP;
850
851 update_1 := update_1 || ') ';
852
853 -- if rollup rules were used, add the appropriate part of the where
854 -- section of the query
855 IF where_rollup_buf_1 IS NOT NULL THEN
856 where_1 := where_1 || where_rollup_buf_1 || where_rollup_buf_2;
857 END IF;
858
859 -- if summary accounts were used, add the appropriate text to the query.
860 IF parent_rule_exist = 'Y' THEN
861 where_template_buf := where_template_buf || ')))';
862 where_1 := where_1 || where_template_buf;
863 END IF;
864
865 IF no_rollup_rules THEN
866 where_1 := where_1 || ' AND from_cc.template_id IS NULL';
867 END IF;
868
869 -- DEBUG prints the contents of the update statement to the screen
870 writedebug('This is the update statement for segment map: ');
871 FOR i IN 0..(lengthb(update_1 || '= ('|| select_1 || from_1 || where_1 || ') ' || where_2)-1)/2000 LOOP
872 writedebug(SUBSTRB(update_1 || '= ('|| select_1 || from_1 || where_1 || ') ' || where_2, i*2000+1, 2000));
873 END LOOP;
874 writedebug(' ');
875
876 -- execute the update statement that applies the segment mapping rules to
877 -- the source code combinations
878 EXECUTE IMMEDIATE
879 update_1 || '= ('|| select_1 || from_1 || where_1 || ') ' || where_2;
880
881 EXCEPTION
882 WHEN GL_INVALID_MAPPING_RULES THEN
883 raise GL_INVALID_MAPPING_RULES;
884 WHEN OTHERS THEN
885 raise GL_MAP_UNEXPECTED_ERROR;
886 END segment_map;
887
888
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
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,
904 -- to_cc.SEGMENT1, to_cc.SEGMENT2, to_cc.SEGMENT3, to_cc.SEGMENT4,
905 -- to_cc.SEGMENT5, to_cc.SEGMENT6, to_cc.SEGMENT7, to_cc.SEGMENT8,
906 -- to_cc.SEGMENT9, to_cc.SEGMENT10
907 -- FROM gl_cons_flexfield_map f,
908 -- gl_code_combinations from_cc,
909 -- gl_code_combinations to_cc
910 -- WHERE map.from_ccid = from_cc.code_combination_id
911 -- and f.coa_mapping_id = v_mapping_id
912 -- and from_cc.SEGMENT1 between f.SEGMENT1_low and f.SEGMENT1_high
913 -- and from_cc.SEGMENT2 between f.SEGMENT2_low and f.SEGMENT2_high
914 -- and from_cc.SEGMENT3 between f.SEGMENT3_low and f.SEGMENT3_high
915 -- and from_cc.SEGMENT4 between f.SEGMENT4_low and f.SEGMENT4_high
916 -- and from_cc.SEGMENT5 between f.SEGMENT5_low and f.SEGMENT5_high
917 -- and f.to_code_combination_id = to_cc.code_combination_id
918 -- and from_cc.chart_of_accounts_id = v_from_coa_id
919 -- and from_cc.template_id IS NULL
920 -- and to_cc.chart_of_accounts_id = v_to_coa_id)
921 -- WHERE map.coa_mapping_id = v_mapping_id
922 -- AND EXISTS (SELECT 'X'
923 -- FROM gl_cons_flexfield_map cons_flex, gl_code_combinations cc1
924 -- WHERE cc1.SEGMENT1 between cons_flex.SEGMENT1_low and cons_flex.SEGMENT1_high
925 -- AND cc1.SEGMENT2 between cons_flex.SEGMENT2_low and cons_flex.SEGMENT2_high
926 -- AND cc1.SEGMENT3 between cons_flex.SEGMENT3_low and cons_flex.SEGMENT3_high
927 -- AND cc1.SEGMENT4 between cons_flex.SEGMENT4_low and cons_flex.SEGMENT4_high
928 -- AND cc1.SEGMENT5 between cons_flex.SEGMENT5_low and cons_flex.SEGMENT5_high
929 -- AND cons_flex.coa_mapping_id = v_mapping_id
930 -- AND cc1.chart_of_accounts_id = v_from_coa_id
931 -- AND map.from_ccid = cc1.code_combination_id
932 -- AND cc1.template_id IS NULL)
933 --
934 -- History
935 -- 16-MAY-02 M. Ward Created
936 -- Arguments
937 -- mapping_id ID of the chart of accounts mapping to use
938 -- to_coa_id To Chart of Accounts ID
939 -- from_coa_id From Chart of Accounts ID
940 --
941 PROCEDURE account_map(mapping_id IN NUMBER,
942 to_coa_id IN NUMBER,
943 from_coa_id IN NUMBER
944 ) IS
945
946 -- buffers for keeping parts of the final update statement while it is
947 -- being built
948 update_1 VARCHAR2(500);
949 select_1 VARCHAR2(1000);
950 where_1 VARCHAR2(4000);
951 where_2 VARCHAR2(4000);
952
953 -- information on the source chart of accounts structure
954 source_structure fnd_flex_key_api.structure_type;
955
956 -- list of segments for the source chart of accounts
957 source_segments fnd_flex_key_api.segment_list;
958
959 -- number of segments for the source chart of accounts
960 source_nSegments NUMBER;
961
962 -- for holding the application column name
963 app_col_name fnd_id_flex_segments.application_column_name%TYPE;
964
965 -- segments in the target chart of accounts
966 CURSOR c_to_segments IS
967 SELECT ffs1.application_column_name TO_APPLICATION_COLUMN_NAME
968 FROM FND_ID_FLEX_SEGMENTS ffs1
969 WHERE ffs1.application_id = g_application_id
970 AND ffs1.id_flex_code = g_id_flex_code
971 AND ffs1.enabled_flag = 'Y'
972 AND ffs1.id_flex_num = to_coa_id
973 ORDER BY ffs1.segment_num;
974
975 -- segments in the source chart of accounts
976 CURSOR c_from_segments IS
977 SELECT ffs1.application_column_name FROM_APPLICATION_COLUMN_NAME
978 FROM FND_ID_FLEX_SEGMENTS ffs1
979 WHERE ffs1.application_id = g_application_id
980 AND ffs1.id_flex_code = g_id_flex_code
981 AND ffs1.enabled_flag = 'Y'
982 AND ffs1.id_flex_num = from_coa_id
983 ORDER BY ffs1.segment_num;
984
985 BEGIN
986 -- gets information on the structure of the source chart of accounts
987 source_structure := fnd_flex_key_api.find_structure
988 (flexfield => g_flexfield,
989 structure_number => from_coa_id
990 );
991
992 -- gets the list of segments for the source chart of accounts
993 fnd_flex_key_api.get_segments(flexfield => g_flexfield,
994 structure => source_structure,
995 nsegments => source_nSegments,
996 segments => source_segments
997 );
998
999 update_1 := 'UPDATE 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
1004 where_1 := 'WHERE map.from_ccid = from_cc.code_combination_id ' ||
1005 'AND f.coa_mapping_id = ' || TO_CHAR(mapping_id);
1006
1007 where_2 := 'WHERE map.coa_mapping_id = ' || TO_CHAR(mapping_id) ||
1008 ' AND EXISTS (SELECT ''X'' ' ||
1009 'FROM gl_cons_flexfield_map cons_flex, ' ||
1010 'gl_code_combinations cc1 WHERE ';
1011
1012 -- Loop through each of the target segments to populate the update and
1013 -- select clauses
1014 FOR col_name IN c_to_segments LOOP
1015 app_col_name := col_name.TO_APPLICATION_COLUMN_NAME;
1016
1017 update_1 := update_1 || ', to_' || app_col_name;
1018 select_1 := select_1 || ', to_cc.' || app_col_name;
1019 END LOOP;
1020
1021 -- Loop through each of the source segments to populate the where clauses
1022 FOR col_name IN c_from_segments LOOP
1023 app_col_name := col_name.FROM_APPLICATION_COLUMN_NAME;
1024
1025 where_1 := where_1 || ' AND from_cc.' || app_col_name || ' BETWEEN f.' ||
1026 app_col_name || '_low AND f.' || app_col_name || '_high';
1027 where_2 := where_2||'cc1.'||app_col_name||' BETWEEN cons_flex.'||
1028 app_col_name||'_low AND cons_flex.'||app_col_name||'_high AND ';
1029 END LOOP;
1030
1031
1032 update_1 := update_1 || ') = ';
1033
1034 select_1 := select_1 || ' FROM gl_cons_flexfield_map f, ' ||
1035 'gl_code_combinations from_cc, ' ||
1036 'gl_code_combinations to_cc ';
1037
1038 where_1 := where_1 || ' AND f.to_code_combination_id = ' ||
1039 'to_cc.code_combination_id ' ||
1040 'AND from_cc.chart_of_accounts_id = ' || TO_CHAR(from_coa_id) ||
1041 ' AND from_cc.template_id IS NULL ' ||
1042 'AND to_cc.chart_of_accounts_id = ' || TO_CHAR(to_coa_id);
1043
1044 where_2 := where_2 || 'cons_flex.coa_mapping_id = ' ||
1045 TO_CHAR(mapping_id) ||
1046 ' AND cc1.chart_of_accounts_id = ' || TO_CHAR(from_coa_id) ||
1047 ' AND map.from_ccid = cc1.code_combination_id' ||
1048 ' AND cc1.template_id IS NULL)';
1049
1050 -- DEBUG prints the contents of the update statement to the screen
1051 writedebug('This is the update statement for account map: ');
1052 FOR i IN 0..(lengthb(update_1 || '(' || select_1 || where_1 || ') ' || where_2)-1)/2000 LOOP
1053 writedebug(SUBSTRB(update_1 || '(' || select_1 || where_1 || ') ' || where_2, i*2000+1, 2000));
1054 END LOOP;
1055 writedebug(' ');
1056
1057 -- execute the update statement that performs the account mappings
1058 EXECUTE IMMEDIATE update_1 || '(' || select_1 || where_1 || ') ' || where_2;
1059
1060 EXCEPTION
1061 WHEN OTHERS THEN
1062 raise GL_MAP_UNEXPECTED_ERROR;
1063 END account_map;
1064
1065
1066 --
1067 -- Procedure
1068 -- end_map
1069 -- Purpose
1070 -- Handles creating the new code combination IDs where required, and
1071 -- populating error codes
1072 --
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 --
1082 -- WHERE to_cc.chart_of_accounts_id = v_to_coa_id
1083 -- AND to_cc.SEGMENT1 (+) = map.TO_SEGMENT1
1084 -- AND to_cc.SEGMENT2 (+) = map.TO_SEGMENT2
1085 -- AND to_cc.SEGMENT3 (+) = map.TO_SEGMENT3
1086 -- AND to_cc.SEGMENT4 (+) = map.TO_SEGMENT4
1087 -- AND to_cc.SEGMENT5 (+) = map.TO_SEGMENT5
1088 -- AND to_cc.SEGMENT6 (+) = map.TO_SEGMENT6
1089 -- AND to_cc.SEGMENT7 (+) = map.TO_SEGMENT7
1090 -- AND to_cc.SEGMENT8 (+) = map.TO_SEGMENT8
1091 -- AND to_cc.SEGMENT9 (+) = map.TO_SEGMENT9
1092 -- AND to_cc.SEGMENT10 (+) = map.TO_SEGMENT10),
1093 -- error_code =
1094 -- (SELECT decode(NVL(map.from_summary_flag, 'X'),
1095 -- 'X', decode(COUNT(*), 0, 'INVALID_FROM_CCID',
1096 -- 'NO_MAPPING'))
1097 -- FROM GL_CODE_COMBINATIONS from_cc
1098 -- WHERE from_cc.code_combination_id = map.from_ccid
1099 -- AND from_cc.chart_of_accounts_id = v_from_coa_id)
1100 -- WHERE map.coa_mapping_id = v_mapping_id
1101 -- AND map.to_ccid IS NULL
1102 -- AND map.from_summary_flag IS NULL
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
1112 -- AND cc.SEGMENT1 = map.TO_SEGMENT1
1113 -- AND cc.SEGMENT2 = map.TO_SEGMENT2
1114 -- AND cc.SEGMENT3 = map.TO_SEGMENT3
1115 -- AND cc.SEGMENT4 = map.TO_SEGMENT4
1116 -- AND cc.SEGMENT5 = map.TO_SEGMENT5
1117 -- AND cc.SEGMENT6 = map.TO_SEGMENT6
1118 -- AND cc.SEGMENT7 = map.TO_SEGMENT7
1119 -- AND cc.SEGMENT8 = map.TO_SEGMENT8
1120 -- AND cc.SEGMENT9 = map.TO_SEGMENT9
1121 -- AND cc.SEGMENT10 = map.TO_SEGMENT10)
1122 -- WHERE map.coa_mapping_id = v_mapping_id
1123 -- AND map.to_ccid IS NULL
1124 --
1125 -- History
1126 -- 23-MAY-02 M. Ward Created
1127 -- Arguments
1128 -- mapping_id The mapping ID for this run
1129 -- create_ccid Whether or not to create new ccids if necessary
1130 -- to_coa_id To Chart of Accounts ID
1131 -- from_coa_id From Chart of Accounts ID
1132 --
1133 PROCEDURE end_map(mapping_id IN NUMBER,
1134 create_ccid IN BOOLEAN,
1135 to_coa_id IN NUMBER,
1136 from_coa_id IN NUMBER
1137 ) IS
1138
1139 -- this will hold the update statement for setting the code combination ids
1140 -- for those code combinations which are already defined in the
1141 -- gl_code_combinations table, and for setting the error codes for those
1142 -- source accounts that were invalid or had no mapping defined for them.
1143 update_1 VARCHAR2(8000);
1144
1145 -- this will hold the update statement for setting the code combination ids
1146 -- for those code combinations which had ids created for them in this
1147 -- procedure.
1148 update_2 VARCHAR2(4000);
1149
1150 -- used for holding the application column name 'SEGMENT<X>'
1151 app_col_name fnd_id_flex_segments.application_column_name%TYPE;
1152
1153 -- returns the list of segments in the target chart of accounts
1154 CURSOR c_to_segments IS
1155 SELECT ffs1.application_column_name TO_APPLICATION_COLUMN_NAME
1156 FROM FND_ID_FLEX_SEGMENTS ffs1
1157 WHERE ffs1.application_id = g_application_id
1158 AND ffs1.id_flex_code = g_id_flex_code
1159 AND ffs1.enabled_flag = 'Y'
1160 AND ffs1.id_flex_num = to_coa_id
1161 ORDER BY ffs1.segment_num;
1162
1163 -- returns the list of source code combinations for which a target code
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
1173 -- app_col_name_list will keep a list of the application column names
1174 -- sorted by the segment number for the target segment
1175 app_col_name_list fnd_flex_ext.SegmentArray;
1176
1177 -- will keep the segment values for the new code combination
1178 new_flex_combination fnd_flex_ext.SegmentArray;
1179
1180 -- used for keeping information from the validate_segs function. It is not
1181 -- used otherwise because the case of invalid target code combinations is
1182 -- handled in a single update statement after all validate_segs function
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
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
1197 FOR seg_rule IN c_to_segments LOOP
1198 app_col_name := seg_rule.TO_APPLICATION_COLUMN_NAME;
1199 update_1 := update_1 || ' AND to_cc.' || app_col_name ||
1200 ' (+) = map.TO_' || app_col_name;
1201 update_2 := update_2 || ' AND cc.' || app_col_name ||
1202 ' = map.TO_' || app_col_name;
1203
1204 -- append the application column name to the list
1205 app_col_name_list(NVL(app_col_name_list.last,0) + 1) := app_col_name;
1206 END LOOP;
1207
1208 update_1 := update_1 || '), error_code = ' ||
1209 '(SELECT decode(NVL(map.from_summary_flag, ''X''), ''X'', ' ||
1210 'decode(COUNT(*), 0, ''INVALID_FROM_CCID'', ' ||
1211 '''NO_MAPPING'')) ' ||
1212 'FROM GL_CODE_COMBINATIONS from_cc ' ||
1213 'WHERE from_cc.code_combination_id = map.from_ccid ' ||
1214 'AND from_cc.chart_of_accounts_id = ' ||
1215 TO_CHAR(from_coa_id) || ') ' ||
1216 'WHERE map.coa_mapping_id = ' || to_char(mapping_id) ||
1217 ' AND map.to_ccid IS NULL ' ||
1218 'AND map.from_summary_flag IS NULL';
1219
1220 update_2 := update_2 || ') ' ||
1221 'WHERE map.coa_mapping_id = ' || to_char(mapping_id) ||
1222 ' AND map.to_ccid IS NULL';
1223
1224 -- DEBUG prints the contents of the update statement to screen
1225 writedebug('This updates the error codes as necessary: ');
1226 FOR i IN 0..(lengthb(update_1)-1)/2000 LOOP
1227 writedebug(SUBSTRB(update_1, i*2000+1, 2000));
1228 END LOOP;
1229 writedebug(' ');
1230
1231 -- populate the error code column for accounts that had no mapping or were
1232 -- invalid code combinations. This also sets the code combination ids for
1233 -- those code combinations that are already defined in gl_code_combinations.
1234 EXECUTE IMMEDIATE update_1;
1235
1236 -- if create_ccid is false, there is no need to do any more, so we can skip
1237 -- the steps to creating code combinations, etc.
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
1247 -- this loop goes through each of the segments in the target code
1248 -- combination and puts the segment values into the new_flex_combination
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;
1258
1259 END LOOP;
1260
1261
1262 -- create ccid for the missing account code combination specified. The
1263 -- handling of invalid segments is done in one update statement outside
1264 -- this loop, so the return value is not needed, and therefore is placed
1265 -- in the dummy variable.
1266 -- In the validate_segs function, I do not specify a responsibility or
1267 -- user id. These are defaulted by the function using FND_GLOBAL, so it
1268 -- is not necessary for me to fill them in.
1269 dummy := FND_FLEX_KEYVAL.validate_segs(
1270 operation => 'CREATE_COMBINATION',
1271 appl_short_name => g_application_short_name,
1272 key_flex_code => g_id_flex_code,
1273 structure_number => to_coa_id,
1274 concat_segments => FND_FLEX_EXT.concatenate_segments(
1275 g_target_nSegments,
1276 new_flex_combination,
1277 FND_FLEX_EXT.get_delimiter(
1278 g_application_short_name,
1279 g_id_flex_code,
1280 to_coa_id
1281 )
1282 )
1283 );
1284 END LOOP;
1285
1286 -- DEBUG prints the contents of the update statement to screen
1287 writedebug('This update statement populates the target ccids: ');
1288 FOR i IN 0..(lengthb(update_2)-1)/2000 LOOP
1289 writedebug(SUBSTRB(update_2, i*2000+1, 2000));
1290 END LOOP;
1291 writedebug(' ');
1292
1293 -- insert the created ccids
1294 EXECUTE IMMEDIATE update_2;
1295
1296 -- populate the error code column for account code combinations for which
1297 -- a code combination could not be created
1298 UPDATE GL_ACCTS_MAP_INT_GT map
1299 SET error_code = 'UNABLE_TO_CREATE_NEW_CCID'
1300 WHERE coa_mapping_id = mapping_id
1301 AND to_ccid IS NULL
1302 AND error_code IS NULL;
1303 END IF;
1304
1305 EXCEPTION
1306 WHEN OTHERS THEN
1307 raise GL_MAP_UNEXPECTED_ERROR;
1308 END end_map;
1309
1310 --
1311 -- Public Methods
1312 --
1313 PROCEDURE map(mapping_name IN VARCHAR2,
1314 create_ccid IN BOOLEAN DEFAULT TRUE,
1315 debug IN BOOLEAN DEFAULT FALSE
1316 ) IS
1317 mapping_id NUMBER;
1318 to_coa_id NUMBER;
1319 from_coa_id NUMBER;
1320 BEGIN
1321 g_debug_flag := debug;
1322
1323 -- this will find the relevent mapping information from the name, and run
1324 -- simple validation tests.
1325 init_map(mapping_name, mapping_id, to_coa_id, from_coa_id);
1326
1327 -- if there are not account rules, don't need to go through the map procedure
1328 IF g_num_account_rules > 0 THEN
1329 -- account mapping is performed before segment mapping for performance
1330 -- reasons. account mapping will always override segment mapping.
1331 account_map(mapping_id, to_coa_id, from_coa_id);
1332 END IF;
1333
1334 -- if there are any segment rules at all, go through segment mapping
1335 IF g_num_segment_rules > 0 THEN
1336 -- this performs the segment mappings. It will not overwrite any target
1337 -- accounts handled by the account mappings, since the account mappings
1338 -- take precedence over segment mappings
1339 segment_map(mapping_id, to_coa_id, from_coa_id);
1340 END IF;
1341
1342 -- this gets the code combination ids for those code combinations that are
1343 -- already in the gl_code_combinations table, and populates the
1344 -- gl_accts_map_int_gt table. It also creates new ccids if
1345 -- appropriate and uses those to populate the table as well. This also
1346 -- populates the error codes.
1347 end_map(mapping_id, create_ccid, to_coa_id, from_coa_id);
1348 EXCEPTION
1349 WHEN GL_INVALID_MAPPING_NAME THEN
1350 raise GL_INVALID_MAPPING_NAME;
1351 WHEN GL_DISABLED_MAPPING THEN
1352 raise GL_DISABLED_MAPPING;
1353 WHEN GL_INVALID_MAPPING_RULES THEN
1354 raise GL_INVALID_MAPPING_RULES;
1355 WHEN OTHERS THEN
1356 raise GL_MAP_UNEXPECTED_ERROR;
1357 END map;
1358
1359
1360
1361 PROCEDURE Map_Account(p_api_version IN NUMBER,
1362 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1363 x_return_status OUT NOCOPY VARCHAR2,
1364 x_msg_count OUT NOCOPY NUMBER,
1365 x_msg_data OUT NOCOPY VARCHAR2,
1366 p_mapping_name IN VARCHAR2,
1367 p_create_ccid IN VARCHAR2 DEFAULT FND_API.G_TRUE,
1368 p_debug IN VARCHAR2 DEFAULT FND_API.G_FALSE
1369 ) IS
1370 l_api_name CONSTANT VARCHAR2(30) := 'Map_Account';
1371 l_api_version CONSTANT NUMBER := 1.0;
1372 BEGIN
1373 -- create my own savepoint here in case of error
1374 SAVEPOINT Map_Account_GRP;
1375
1376 -- Check for call compatibility
1377 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1378 l_api_name, 'GL_ACCOUNTS_MAP_GRP') THEN
1379 raise GL_MAP_UNEXPECTED_ERROR;
1380 END IF;
1381
1382 -- Initialize message list if p_init_msg_list is set to TRUE.
1383 IF FND_API.to_Boolean(p_init_msg_list) THEN
1384 FND_MSG_PUB.initialize;
1385 END IF;
1386
1387 -- initialize the API return status to success
1388 x_return_status := FND_API.G_RET_STS_SUCCESS;
1389
1390 -- this procedure does the work. Map_Account is simply a wrapper
1391 map(p_mapping_name, FND_API.to_Boolean(p_create_ccid),
1392 FND_API.to_Boolean(p_debug));
1393
1394 -- never commit changes
1395
1396 -- get message count, and also message if there is only one
1397 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1398 p_data => x_msg_data);
1399
1400 EXCEPTION
1401 WHEN GL_INVALID_MAPPING_NAME THEN
1402 ROLLBACK TO Map_Account_GRP;
1403 x_return_status := FND_API.G_RET_STS_ERROR;
1404 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1405 p_data => x_msg_data);
1406 WHEN GL_DISABLED_MAPPING THEN
1407 ROLLBACK TO Map_Account_GRP;
1408 x_return_status := FND_API.G_RET_STS_ERROR;
1409 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1410 p_data => x_msg_data);
1411 WHEN GL_INVALID_MAPPING_RULES THEN
1412 ROLLBACK TO Map_Account_GRP;
1413 x_return_status := FND_API.G_RET_STS_ERROR;
1414 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1415 p_data => x_msg_data);
1416 WHEN OTHERS THEN
1417 ROLLBACK TO Map_Account_GRP;
1418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1419 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1420 p_data => x_msg_data);
1421 END Map_Account;
1422
1423
1424 --
1425 -- BSV Mapping Private Methods
1426 --
1427
1428 PROCEDURE Get_Segment_Mapping_Info( p_mapping_name IN VARCHAR2,
1429 p_qualifier IN VARCHAR2,
1430 p_debug IN BOOLEAN,
1431 x_mapping_rule OUT NOCOPY VARCHAR2,
1432 x_to_segment OUT NOCOPY VARCHAR2,
1433 x_single_value OUT NOCOPY VARCHAR2) IS
1434 -- these are used for simple validation tests on the mapping
1435 start_date_active DATE;
1436 end_date_active DATE;
1437
1438 from_coa_id NUMBER;
1439 to_coa_id NUMBER;
1440 mapping_id NUMBER;
1441
1442 from_segment VARCHAR2(30);
1443 from_balancing_segment VARCHAR2(30);
1444
1445
1446 -- cursor to gather information on the mapping with the given name
1447 CURSOR c_mapping_info IS
1448 SELECT coa_mapping_id,
1449 from_coa_id,
1450 to_coa_id,
1451 start_date_active,
1452 end_date_active
1453 FROM gl_coa_mappings
1454 WHERE name = p_mapping_name;
1455
1456 -- cursor to figure out the balancing segment for a chart of accounts
1457 CURSOR c_balancing_segment(c_coa_id NUMBER) IS
1458 SELECT application_column_name
1459 FROM fnd_segment_attribute_values
1460 WHERE application_id = 101
1461 AND id_flex_code = 'GL#'
1462 AND id_flex_num = c_coa_id
1463 AND segment_attribute_type = p_qualifier
1464 AND attribute_value = 'Y';
1465
1466 -- cursor to gather information on the segment mapping for the target
1467 -- balancing segment
1468 CURSOR c_segment_map_info( c_mapping_id NUMBER,
1469 c_to_segment VARCHAR2) IS
1470 SELECT csm.segment_map_type,
1471 csm.from_application_column_name,
1472 csm.single_value
1473 FROM gl_cons_segment_map csm
1474 WHERE csm.coa_mapping_id = c_mapping_id
1475 AND csm.to_application_column_name = c_to_segment;
1476
1477 BEGIN
1478 OPEN c_mapping_info;
1479 FETCH c_mapping_info INTO mapping_id, from_coa_id, to_coa_id,
1480 start_date_active, end_date_active;
1481 IF c_mapping_info%NOTFOUND THEN
1482 CLOSE c_mapping_info;
1483 raise GL_INVALID_MAPPING_NAME;
1484 END IF;
1485 CLOSE c_mapping_info;
1486
1487 -- This compares SYSDATE to start/end_date_active to see if the mapping
1488 -- is active. If not, this raises an exception.
1489 IF (start_date_active IS NOT NULL AND start_date_active > SYSDATE) OR
1490 (end_date_active IS NOT NULL AND end_date_active < SYSDATE) THEN
1491 raise GL_DISABLED_MAPPING;
1492 END IF;
1493
1494 -- Get the balancing segments for the target and source charts of accounts
1495 OPEN c_balancing_segment(from_coa_id);
1496 FETCH c_balancing_segment INTO from_balancing_segment;
1497 IF c_balancing_segment%NOTFOUND THEN
1498 CLOSE c_balancing_segment;
1499 raise GL_BSV_MAP_NO_SOURCE_BAL_SEG;
1500 END IF;
1501 CLOSE c_balancing_segment;
1502
1503 OPEN c_balancing_segment(to_coa_id);
1504 FETCH c_balancing_segment INTO x_to_segment;
1505 IF c_balancing_segment%NOTFOUND THEN
1506 CLOSE c_balancing_segment;
1507 raise GL_BSV_MAP_NO_TARGET_BAL_SEG;
1508 END IF;
1509 CLOSE c_balancing_segment;
1510
1511 -- Get the segment mapping information for the target balancing segment
1512 OPEN c_segment_map_info(mapping_id, x_to_segment);
1513 FETCH c_segment_map_info INTO x_mapping_rule, from_segment, x_single_value;
1514 IF c_segment_map_info%NOTFOUND THEN
1515 CLOSE c_segment_map_info;
1516 raise GL_BSV_MAP_NO_SEGMENT_MAP;
1517 END IF;
1518 CLOSE c_segment_map_info;
1519
1520 -- Now perform some checks on the segment mapping:
1521 -- 1. If Single Value is selected, a single value must be specified
1522 -- 2. If Copy or Rollup is selected, a source segment must be specified
1523 -- 3. If Copy or Rollup is selected, the source segment must be the
1524 -- balancing segment for the source chart of accounts
1525 IF x_mapping_rule = 'S' THEN
1526 IF x_single_value IS NULL THEN
1527 raise GL_BSV_MAP_NO_SINGLE_VALUE;
1528 END IF;
1529 ELSIF from_segment IS NULL THEN
1530 raise GL_BSV_MAP_NO_FROM_SEGMENT;
1531 ELSIF from_segment <> from_balancing_segment THEN
1532 raise GL_BSV_MAP_NOT_BSV_DERIVED;
1533 END IF;
1534
1535 EXCEPTION
1536 WHEN GL_INVALID_MAPPING_NAME THEN
1537 raise GL_INVALID_MAPPING_NAME;
1538 WHEN GL_DISABLED_MAPPING THEN
1539 raise GL_DISABLED_MAPPING;
1540 WHEN GL_BSV_MAP_NO_SOURCE_BAL_SEG THEN
1541 raise GL_BSV_MAP_NO_SOURCE_BAL_SEG;
1542 WHEN GL_BSV_MAP_NO_TARGET_BAL_SEG THEN
1543 raise GL_BSV_MAP_NO_TARGET_BAL_SEG;
1544 WHEN GL_BSV_MAP_NO_SEGMENT_MAP THEN
1545 raise GL_BSV_MAP_NO_SEGMENT_MAP;
1546 WHEN GL_BSV_MAP_NO_SINGLE_VALUE THEN
1547 raise GL_BSV_MAP_NO_SINGLE_VALUE;
1548 WHEN GL_BSV_MAP_NO_FROM_SEGMENT THEN
1549 raise GL_BSV_MAP_NO_FROM_SEGMENT;
1550 WHEN GL_BSV_MAP_NOT_BSV_DERIVED THEN
1551 raise GL_BSV_MAP_NOT_BSV_DERIVED;
1552 WHEN OTHERS THEN
1553 raise GL_BSV_MAP_SETUP_ERROR;
1554 END Get_Segment_Mapping_Info;
1555
1556
1557 PROCEDURE Perform_Segment_Mapping( p_mapping_rule IN VARCHAR2,
1558 p_to_segment IN VARCHAR2,
1559 p_single_value IN VARCHAR2,
1560 p_debug IN BOOLEAN) IS
1561 l_rollup_stmt VARCHAR2(2000);
1562 BEGIN
1563 IF p_mapping_rule = 'S' THEN
1564 UPDATE GL_ACCTS_MAP_BSV_GT
1565 SET target_bsv = p_single_value;
1566 ELSIF p_mapping_rule = 'C' THEN
1567 UPDATE GL_ACCTS_MAP_BSV_GT
1568 SET target_bsv = source_bsv;
1569 ELSE -- mapping rule is a rollup
1570 EXECUTE IMMEDIATE
1571 'UPDATE GL_ACCTS_MAP_BSV_GT bm ' ||
1572 'SET target_bsv = ' ||
1573 '(SELECT ami.target_flex_value ' ||
1574 'FROM GL_ACCTS_MAP_SEG' || substr(p_to_segment,8,2) || '_GT ami ' ||
1575 'WHERE ami.source_flex_value = bm.source_bsv)';
1576 END IF;
1577 EXCEPTION
1578 WHEN OTHERS THEN
1579 raise GL_BSV_MAP_MAPPING_ERROR;
1580 END Perform_Segment_Mapping;
1581
1582 --
1583 -- BSV Mapping Public Methods
1584 --
1585
1586 PROCEDURE map_bsvs( p_mapping_name IN VARCHAR2,
1587 p_debug IN BOOLEAN) IS
1588 l_mapping_rule VARCHAR2(1);
1589 l_to_segment VARCHAR2(30);
1590 l_single_value VARCHAR2(25);
1591 BEGIN
1592 -- get the information required for this chart of accounts mapping
1593 get_segment_mapping_info( p_mapping_name => p_mapping_name,
1594 p_qualifier => 'GL_BALANCING',
1595 p_debug => p_debug,
1596 x_mapping_rule => l_mapping_rule,
1597 x_to_segment => l_to_segment,
1598 x_single_value => l_single_value);
1599
1600 -- perform the required mapping
1601 perform_segment_mapping( p_mapping_rule => l_mapping_rule,
1602 p_to_segment => l_to_segment,
1603 p_single_value => l_single_value,
1604 p_debug => p_debug);
1605 EXCEPTION
1606 WHEN GL_INVALID_MAPPING_NAME THEN
1607 raise GL_INVALID_MAPPING_NAME;
1608 WHEN GL_DISABLED_MAPPING THEN
1609 raise GL_DISABLED_MAPPING;
1610 WHEN GL_BSV_MAP_NO_SOURCE_BAL_SEG THEN
1611 raise GL_BSV_MAP_NO_SOURCE_BAL_SEG;
1612 WHEN GL_BSV_MAP_NO_TARGET_BAL_SEG THEN
1613 raise GL_BSV_MAP_NO_TARGET_BAL_SEG;
1614 WHEN GL_BSV_MAP_NO_SEGMENT_MAP THEN
1615 raise GL_BSV_MAP_NO_SEGMENT_MAP;
1616 WHEN GL_BSV_MAP_NO_SINGLE_VALUE THEN
1617 raise GL_BSV_MAP_NO_SINGLE_VALUE;
1618 WHEN GL_BSV_MAP_NO_FROM_SEGMENT THEN
1619 raise GL_BSV_MAP_NO_FROM_SEGMENT;
1620 WHEN GL_BSV_MAP_NOT_BSV_DERIVED THEN
1621 raise GL_BSV_MAP_NOT_BSV_DERIVED;
1622 WHEN GL_BSV_MAP_SETUP_ERROR THEN
1623 raise GL_BSV_MAP_SETUP_ERROR;
1624 WHEN GL_BSV_MAP_MAPPING_ERROR THEN
1625 raise GL_BSV_MAP_MAPPING_ERROR;
1626 WHEN OTHERS THEN
1627 raise GL_BSV_MAP_UNEXPECTED_ERROR;
1628 END;
1629
1630
1631 PROCEDURE Populate_BSV_Targets
1632 (p_api_version IN NUMBER,
1633 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1634 x_return_status OUT NOCOPY VARCHAR2,
1635 x_msg_count OUT NOCOPY NUMBER,
1636 x_msg_data OUT NOCOPY VARCHAR2,
1637 p_mapping_name IN VARCHAR2,
1638 p_debug IN VARCHAR2 DEFAULT FND_API.G_FALSE
1639 ) IS
1640 l_api_name CONSTANT VARCHAR2(30) := 'Populate_BSV_Targets';
1641 l_api_version CONSTANT NUMBER := 1.0;
1642 BEGIN
1643 -- create my own savepoint here in case of error
1644 SAVEPOINT Populate_BSV_Targets_Start;
1645
1646 -- Check for call compatibility
1647 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1648 l_api_name, 'GL_ACCOUNTS_MAP_GRP') THEN
1649 raise GL_BSV_MAP_UNEXPECTED_ERROR;
1650 END IF;
1651
1652 -- Initialize message list if p_init_msg_list is set to TRUE.
1653 IF FND_API.to_Boolean(p_init_msg_list) THEN
1654 FND_MSG_PUB.initialize;
1655 END IF;
1656
1657 -- initialize the API return status to success
1658 x_return_status := FND_API.G_RET_STS_SUCCESS;
1659
1660 -- Now go to the mapping procedure
1661 map_bsvs( p_mapping_name => p_mapping_name,
1662 p_debug => FND_API.to_boolean(p_debug));
1663
1664 -- never commit changes
1665
1666 -- get message count, and also message if there is only one
1667 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1668 p_data => x_msg_data);
1669
1670 EXCEPTION
1671 WHEN GL_INVALID_MAPPING_NAME THEN
1672 ROLLBACK TO Map_Account_GRP;
1673 x_return_status := FND_API.G_RET_STS_ERROR;
1674 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1675 p_data => x_msg_data);
1676 WHEN GL_DISABLED_MAPPING THEN
1677 ROLLBACK TO Map_Account_GRP;
1678 x_return_status := FND_API.G_RET_STS_ERROR;
1679 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1680 p_data => x_msg_data);
1681 WHEN GL_BSV_MAP_NO_SOURCE_BAL_SEG THEN
1682 ROLLBACK TO Map_Account_GRP;
1683 x_return_status := FND_API.G_RET_STS_ERROR;
1684 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1685 p_data => x_msg_data);
1686 WHEN GL_BSV_MAP_NO_TARGET_BAL_SEG THEN
1687 ROLLBACK TO Map_Account_GRP;
1688 x_return_status := FND_API.G_RET_STS_ERROR;
1689 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1690 p_data => x_msg_data);
1691 WHEN GL_BSV_MAP_NO_SEGMENT_MAP THEN
1692 ROLLBACK TO Map_Account_GRP;
1693 x_return_status := FND_API.G_RET_STS_ERROR;
1694 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1695 p_data => x_msg_data);
1696 WHEN GL_BSV_MAP_NO_SINGLE_VALUE THEN
1697 ROLLBACK TO Map_Account_GRP;
1698 x_return_status := FND_API.G_RET_STS_ERROR;
1699 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1700 p_data => x_msg_data);
1701 WHEN GL_BSV_MAP_NO_FROM_SEGMENT THEN
1702 ROLLBACK TO Map_Account_GRP;
1703 x_return_status := FND_API.G_RET_STS_ERROR;
1704 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1705 p_data => x_msg_data);
1706 WHEN GL_BSV_MAP_NOT_BSV_DERIVED THEN
1707 ROLLBACK TO Map_Account_GRP;
1708 x_return_status := FND_API.G_RET_STS_ERROR;
1709 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1710 p_data => x_msg_data);
1711 WHEN GL_BSV_MAP_SETUP_ERROR THEN
1712 ROLLBACK TO Map_Account_GRP;
1713 x_return_status := FND_API.G_RET_STS_ERROR;
1714 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1715 p_data => x_msg_data);
1716 WHEN GL_BSV_MAP_MAPPING_ERROR THEN
1717 ROLLBACK TO Map_Account_GRP;
1718 x_return_status := FND_API.G_RET_STS_ERROR;
1719 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1720 p_data => x_msg_data);
1721 WHEN OTHERS THEN
1722 ROLLBACK TO Populate_BSV_Targets_Start;
1723 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1724 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1725 p_data => x_msg_data);
1726 END Populate_BSV_Targets;
1727
1728
1729 --
1730 -- BSV Mapping Public Methods
1731 --
1732
1733 PROCEDURE map_qualified_segment( p_mapping_name IN VARCHAR2,
1734 p_qualifier IN VARCHAR2,
1735 p_debug IN BOOLEAN) IS
1736 l_mapping_rule VARCHAR2(1);
1737 l_to_segment VARCHAR2(30);
1738 l_single_value VARCHAR2(25);
1739 BEGIN
1740 -- get the information required for this chart of accounts mapping
1741 get_segment_mapping_info( p_mapping_name => p_mapping_name,
1742 p_qualifier => p_qualifier,
1743 p_debug => p_debug,
1744 x_mapping_rule => l_mapping_rule,
1745 x_to_segment => l_to_segment,
1746 x_single_value => l_single_value);
1747
1748 -- perform the required mapping
1749 perform_segment_mapping( p_mapping_rule => l_mapping_rule,
1750 p_to_segment => l_to_segment,
1751 p_single_value => l_single_value,
1752 p_debug => p_debug);
1753 EXCEPTION
1754 WHEN GL_INVALID_MAPPING_NAME THEN
1755 raise GL_INVALID_MAPPING_NAME;
1756 WHEN GL_DISABLED_MAPPING THEN
1757 raise GL_DISABLED_MAPPING;
1758 WHEN GL_BSV_MAP_NO_SOURCE_BAL_SEG THEN
1759 raise GL_BSV_MAP_NO_SOURCE_BAL_SEG;
1760 WHEN GL_BSV_MAP_NO_TARGET_BAL_SEG THEN
1761 raise GL_BSV_MAP_NO_TARGET_BAL_SEG;
1762 WHEN GL_BSV_MAP_NO_SEGMENT_MAP THEN
1763 raise GL_BSV_MAP_NO_SEGMENT_MAP;
1764 WHEN GL_BSV_MAP_NO_SINGLE_VALUE THEN
1765 raise GL_BSV_MAP_NO_SINGLE_VALUE;
1766 WHEN GL_BSV_MAP_NO_FROM_SEGMENT THEN
1767 raise GL_BSV_MAP_NO_FROM_SEGMENT;
1768 WHEN GL_BSV_MAP_NOT_BSV_DERIVED THEN
1769 raise GL_BSV_MAP_NOT_BSV_DERIVED;
1770 WHEN GL_BSV_MAP_SETUP_ERROR THEN
1771 raise GL_BSV_MAP_SETUP_ERROR;
1772 WHEN GL_BSV_MAP_MAPPING_ERROR THEN
1773 raise GL_BSV_MAP_MAPPING_ERROR;
1774 WHEN OTHERS THEN
1775 raise GL_BSV_MAP_UNEXPECTED_ERROR;
1776 END;
1777
1778
1779 PROCEDURE Populate_Qual_Segment_Targets
1780 (p_api_version IN NUMBER,
1781 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1782 x_return_status OUT NOCOPY VARCHAR2,
1783 x_msg_count OUT NOCOPY NUMBER,
1784 x_msg_data OUT NOCOPY VARCHAR2,
1785 p_mapping_name IN VARCHAR2,
1786 p_qualifier IN VARCHAR2,
1787 p_debug IN VARCHAR2 DEFAULT FND_API.G_FALSE
1788 ) IS
1789 l_api_name CONSTANT VARCHAR2(30) := 'Populate_Qual_Segment_Targets';
1790 l_api_version CONSTANT NUMBER := 1.0;
1791 BEGIN
1792 -- create my own savepoint here in case of error
1793 SAVEPOINT Populate_QS_Targets_Start;
1794
1795 -- Check for call compatibility
1796 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1797 l_api_name, 'GL_ACCOUNTS_MAP_GRP') THEN
1798 raise GL_BSV_MAP_UNEXPECTED_ERROR;
1799 END IF;
1800
1801 -- Initialize message list if p_init_msg_list is set to TRUE.
1802 IF FND_API.to_Boolean(p_init_msg_list) THEN
1803 FND_MSG_PUB.initialize;
1804 END IF;
1805
1806 -- initialize the API return status to success
1807 x_return_status := FND_API.G_RET_STS_SUCCESS;
1808
1809 -- Now go to the mapping procedure
1810 map_qualified_segment( p_mapping_name => p_mapping_name,
1811 p_qualifier => p_qualifier,
1812 p_debug => FND_API.to_boolean(p_debug));
1813
1814 -- never commit changes
1815
1816 -- get message count, and also message if there is only one
1817 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1818 p_data => x_msg_data);
1819
1820 EXCEPTION
1821 WHEN GL_INVALID_MAPPING_NAME THEN
1822 ROLLBACK TO Map_Account_GRP;
1823 x_return_status := FND_API.G_RET_STS_ERROR;
1824 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1825 p_data => x_msg_data);
1826 WHEN GL_DISABLED_MAPPING THEN
1827 ROLLBACK TO Map_Account_GRP;
1828 x_return_status := FND_API.G_RET_STS_ERROR;
1829 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1830 p_data => x_msg_data);
1831 WHEN GL_BSV_MAP_NO_SOURCE_BAL_SEG THEN
1832 ROLLBACK TO Map_Account_GRP;
1833 x_return_status := FND_API.G_RET_STS_ERROR;
1834 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1835 p_data => x_msg_data);
1836 WHEN GL_BSV_MAP_NO_TARGET_BAL_SEG THEN
1837 ROLLBACK TO Map_Account_GRP;
1838 x_return_status := FND_API.G_RET_STS_ERROR;
1839 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1840 p_data => x_msg_data);
1841 WHEN GL_BSV_MAP_NO_SEGMENT_MAP THEN
1842 ROLLBACK TO Map_Account_GRP;
1843 x_return_status := FND_API.G_RET_STS_ERROR;
1844 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1845 p_data => x_msg_data);
1846 WHEN GL_BSV_MAP_NO_SINGLE_VALUE THEN
1847 ROLLBACK TO Map_Account_GRP;
1848 x_return_status := FND_API.G_RET_STS_ERROR;
1849 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1850 p_data => x_msg_data);
1851 WHEN GL_BSV_MAP_NO_FROM_SEGMENT THEN
1852 ROLLBACK TO Map_Account_GRP;
1853 x_return_status := FND_API.G_RET_STS_ERROR;
1854 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1855 p_data => x_msg_data);
1856 WHEN GL_BSV_MAP_NOT_BSV_DERIVED THEN
1857 ROLLBACK TO Map_Account_GRP;
1858 x_return_status := FND_API.G_RET_STS_ERROR;
1859 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1860 p_data => x_msg_data);
1861 WHEN GL_BSV_MAP_SETUP_ERROR THEN
1862 ROLLBACK TO Map_Account_GRP;
1863 x_return_status := FND_API.G_RET_STS_ERROR;
1864 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1865 p_data => x_msg_data);
1866 WHEN GL_BSV_MAP_MAPPING_ERROR THEN
1867 ROLLBACK TO Map_Account_GRP;
1868 x_return_status := FND_API.G_RET_STS_ERROR;
1869 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1870 p_data => x_msg_data);
1871 WHEN OTHERS THEN
1872 ROLLBACK TO Populate_QS_Targets_Start;
1873 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1874 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1875 p_data => x_msg_data);
1876 END Populate_Qual_Segment_Targets;
1877
1878 END GL_ACCOUNTS_MAP_GRP;
[Home] [Help]