[Home] [Help]
PACKAGE BODY: APPS.FUN_BAL_PKG
Source
1 PACKAGE BODY FUN_BAL_PKG AS
2 /* $Header: funbalpkgb.pls 120.31.12010000.6 2009/02/19 12:56:32 srampure ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FUN_BAL_PKG';
4 G_FILE_NAME CONSTANT VARCHAR2(30) := 'FUNBALPKGB.PLS';
5 G_PRODUCT_CODE VARCHAR2(3);
6 G_DEBUG VARCHAR2(1);
7 G_FUN_SCHEMA VARCHAR2(30);
8 G_DEBUG_LEVEL NUMBER;
9
10 --Bug # 7141663 Created the table type to cache the ouput of get_ccid procedure
11 TYPE ccid_cache_tab IS TABLE OF NUMBER INDEX BY VARCHAR2(4000);
12 g_ccid_cache_tab ccid_cache_tab;
13
14
15 -- This function is not currently used
16 PROCEDURE debug
17 ( p_message IN VARCHAR2
18 ) IS
19 BEGIN
20 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)) THEN
21 FND_MESSAGE.SET_NAME('FUN', p_message);
22 FND_MSG_PUB.Add;
23 END IF;
24 END debug;
25
26 PROCEDURE truncate_tables
27 IS
28 cur_hdl int;
29 rows_processed int;
30 BEGIN
31 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
32 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.truncate_tables', 'begin');
33 END IF;
34
35 DELETE FROM fun_bal_results_gt;
36 DELETE FROM fun_bal_errors_gt;
37 DELETE FROM fun_bal_le_bsv_map_gt;
38 DELETE FROM fun_bal_inter_int_gt;
39 DELETE FROM fun_bal_intra_int_gt;
40 /* Using delete rather than truncate as shown in the code below. The reason is that truncate (or any DDL operations)
41 perform an implicit commit => need to use autonomous transaction to perform such operation. However, we would
42 like to make sure the calling program does not see the rows that gets deleted, therefore truncate is not used.
43 In addition, the truncate operation might not be able to delete the rows that the calling program has not commited yet,
44 which could result in that we think the rows got deleted but they still exist.
45 cur_hdl := dbms_sql.open_cursor;
46 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_RESULTS_GT', dbms_sql.native);
47 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_ERRORS_GT', dbms_sql.native);
48 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_LE_BSV_MAP_GT', dbms_sql.native);
49 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_INT_GT', dbms_sql.native);
50 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_INT_GT', dbms_sql.native);
51 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_RESULTS_T', dbms_sql.native);
52 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_ERRORS_T', dbms_sql.native);
53 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_LINES_T', dbms_sql.native);
54 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_HEADERS_T', dbms_sql.native);
55 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_BSV_MAP_T', dbms_sql.native);
56 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_BSV_MAP_T', dbms_sql.native);
57 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_INT_T', dbms_sql.native);
58 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_INT_T', dbms_sql.native);
59 dbms_sql.close_cursor(cur_hdl); -- close cursor
60 */
61 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
62 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.truncate_tables', 'end');
63 END IF;
64
65 RETURN;
66 END truncate_tables;
67
68
69 PROCEDURE update_inter_seg_val IS
70 stmt_str varchar2(1000);
71 cur_hdl int;
72 rows_processed int;
73 l_bal_seg_column_name VARCHAR2(25);
74 CURSOR bal_seg_val_cur IS
75 SELECT DISTINCT bal_seg_column_name
76 FROM fun_bal_headers_gt headers
77 WHERE headers.status = 'OK';
78 BEGIN
79 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
80 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.update_inter_seg_val.begin', 'begin');
81 END IF;
82
83 OPEN bal_seg_val_cur;
84 LOOP
85 FETCH bal_seg_val_cur INTO l_bal_seg_column_name;
86 EXIT WHEN bal_seg_val_cur%NOTFOUND;
87 cur_hdl := dbms_sql.open_cursor;
88 stmt_str := 'UPDATE fun_bal_inter_int_gt inter_int ' ||
89 ' SET rec_bsv = ' ||
90 ' (SELECT ' || l_bal_seg_column_name ||
91 ' FROM gl_code_combinations ' ||
92 ' WHERE code_combination_id = inter_int.rec_acct ' ||
93 ' AND inter_int.bal_seg_column_name = ''' || l_bal_seg_column_name || ''') ' ||
94 ' WHERE inter_int.rec_acct IS NOT NULL AND inter_int.rec_acct <> -1';
95 dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);
96 rows_processed := dbms_sql.execute(cur_hdl);
97 dbms_sql.close_cursor(cur_hdl); -- close cursor
98
99 cur_hdl := dbms_sql.open_cursor;
100 stmt_str := 'UPDATE fun_bal_inter_int_gt inter_int ' ||
101 ' SET pay_bsv = ' ||
102 ' (SELECT ' || l_bal_seg_column_name ||
103 ' FROM gl_code_combinations ' ||
104 ' WHERE code_combination_id = inter_int.pay_acct ' ||
105 ' AND inter_int.bal_seg_column_name = ''' || l_bal_seg_column_name || ''') ' ||
106 ' WHERE inter_int.pay_acct IS NOT NULL AND inter_int.pay_acct <> -1';
107 dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);
108 rows_processed := dbms_sql.execute(cur_hdl);
109 dbms_sql.close_cursor(cur_hdl); -- close cursor
110 END LOOP;
111 CLOSE bal_seg_val_cur;
112 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
113 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.update_inter_seg_val.end', 'end');
114 END IF;
115
116 RETURN;
117 END update_inter_seg_val;
118
119 FUNCTION get_segment_index (p_chart_of_accounts_id IN NUMBER,
120 p_segment_type VARCHAR2)
121 RETURN NUMBER
122 IS
123 CURSOR c_segments (p_chart_of_accounts_id NUMBER) IS
124 SELECT s.segment_num, sav.segment_attribute_type
125 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav
126 WHERE s.application_id = 101
127 AND s.id_flex_code = 'GL#'
128 AND s.id_flex_num = p_chart_of_accounts_id
129 AND s.enabled_flag = 'Y'
130 AND s.application_column_name = sav.application_column_name
131 AND sav.application_id = 101
132 AND sav.id_flex_code = 'GL#'
133 AND sav.id_flex_num = p_chart_of_accounts_id
134 AND sav.attribute_value = 'Y'
135 ORDER BY s.segment_num ASC;
136
137 l_ic_seg_num NUMBER;
138 l_bal_seg_num NUMBER;
139 l_index NUMBER;
140 l_prev_seg_num NUMBER;
141
142 BEGIN
143 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
144 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.get_segment_index', 'start');
145 END IF;
146
147 l_index := 0;
148 l_prev_seg_num := 0;
149
150 FOR r_segments IN c_segments (p_chart_of_accounts_id)
151 LOOP
152 IF l_prev_seg_num <> r_segments.segment_num
153 THEN
154 l_index := l_index + 1;
155 END IF;
156
157 IF r_segments.segment_attribute_type = p_segment_type
158 AND p_segment_type = 'GL_BALANCING'
159 THEN
160 l_bal_seg_num := l_index;
161 RETURN l_bal_seg_num;
162 END IF;
163
164 IF r_segments.segment_attribute_type = p_segment_type
165 AND p_segment_type = 'GL_INTERCOMPANY'
166 THEN
167 l_ic_seg_num := l_index;
168 RETURN l_ic_seg_num;
169 END IF;
170
171 l_prev_seg_num := r_segments.segment_num;
172 END LOOP;
173
174 RETURN NULL;
175
176 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
177 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.get_segment_index', 'end');
178 END IF;
179
180 END get_segment_index;
181
182 FUNCTION get_ccid
183 ( ccid IN NUMBER,
184 chart_of_accounts_id IN NUMBER,
185 bal_seg_val IN VARCHAR2,
186 intercompany_seg_val IN VARCHAR2,
187 bal_seg_column_number IN NUMBER,
188 intercompany_column_number IN NUMBER,
189 gl_date IN DATE) RETURN NUMBER IS
190 l_segment_array FND_FLEX_EXT.SEGMENTARRAY;
191 l_flag BOOLEAN;
192 l_no_of_segments NUMBER;
193 l_ccid NUMBER;
194 l_rule VARCHAR2(1000);
195 l_where_clause VARCHAR2(30);
196 l_get_column VARCHAR2(30);
197 l_delimiter VARCHAR2(1);
198 l_cat_segs VARCHAR2(2000);
199 l_error_message VARCHAR2(2000);
200
201 -- Bug # 7141663 Key for the cache table created
202 l_ccid_key VARCHAR2(2000);
203
204 BEGIN
205
206 -- Bug # 7141663 Key for the cache table created
207 -- Bug # 7321887 Replaced TO_DATE() with TO_CHAR()
208 l_ccid_key := ccid || '~' || chart_of_accounts_id || '~' ||
209 bal_seg_val || '~' || intercompany_seg_val || '~' ||
210 bal_seg_column_number|| '~' || intercompany_column_number || '~' ||
211 TO_CHAR(gl_date,'DD-MM-YYYY');
212
213 -- Bug # 7141663 If the key does not exists in the cache table,
214 -- then get the ccid as done previously, else fetch the same from
215 -- the cache table.
216 IF ( g_ccid_cache_tab.EXISTS( l_ccid_key ) = FALSE ) THEN
217 l_rule := '\nSUMMARY_FLAG\nI\n' ||
218 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
219 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED' ||
220 '\nI\n' ||
221 'APPL=SQLGL;NAME=GL_JE_POSTING_NOT_ALLOWED\nY';
222 l_delimiter := fnd_flex_ext.get_delimiter('SQLGL', 'GL#', chart_of_accounts_id);
223 l_flag := fnd_flex_ext.get_segments('SQLGL', 'GL#', chart_of_accounts_id, ccid,
224 l_no_of_segments, l_segment_array);
225 IF l_flag = FALSE THEN
226 IF (FND_LOG.LEVEL_ERROR >= g_debug_level) THEN
227 l_error_message := FND_FLEX_KEYVAL.ERROR_MESSAGE;
228 IF l_error_message IS NOT NULL THEN
229 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.do_inter_bal.get_segment', l_error_message);
230 END IF;
231 END IF;
232 FND_MESSAGE.SET_NAME('FUN', 'FUN_BAL_GET_CCID_ERROR');
233 FND_MESSAGE.SET_TOKEN('GL_ERROR', FND_FLEX_KEYVAL.ERROR_MESSAGE);
234 FND_MSG_PUB.Add;
235 RETURN -ccid;
236 END IF;
237 l_segment_array(bal_seg_column_number) := bal_seg_val;
238 IF intercompany_column_number IS NOT NULL
239 AND bal_seg_column_number <> intercompany_column_number
240 THEN
241 l_segment_array(intercompany_column_number) := intercompany_seg_val;
242 END IF;
243 l_cat_segs := fnd_flex_ext.concatenate_segments(l_no_of_segments, l_segment_array, l_delimiter);
244 l_flag := fnd_flex_keyval.validate_segs('CREATE_COMBINATION','SQLGL',
245 'GL#', chart_of_accounts_id, l_cat_segs,
246 'V', gl_date, 'ALL', NULL, l_rule, l_where_clause,
247 l_get_column, FALSE, FALSE,
248 NULL, NULL, NULL, NULL, NULL, NULL);
249 IF l_flag = FALSE THEN
250 IF (FND_LOG.LEVEL_ERROR >= g_debug_level) THEN
251 l_error_message := FND_FLEX_KEYVAL.ERROR_MESSAGE;
252 IF l_error_message IS NOT NULL THEN
253 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.do_inter_bal.validate_segs', l_error_message);
254 END IF;
255 END IF;
256 FND_MESSAGE.SET_NAME('FUN', 'FUN_BAL_GET_CCID_ERROR');
257 FND_MESSAGE.SET_TOKEN('GL_ERROR', FND_FLEX_KEYVAL.ERROR_MESSAGE);
258 FND_MSG_PUB.Add;
259 RETURN -ccid;
260 END IF;
261
262 -- Bug # 7141663 Add the result to the cache table
263 g_ccid_cache_tab(l_ccid_key) := fnd_flex_keyval.combination_id;
264 END IF;
265
266 -- Bug # 7141663 fetch the ccid from the cache table and return the same
267 return g_ccid_cache_tab(l_ccid_key);
268 END get_ccid;
269
270 FUNCTION get_ccid_concat_disp
271 ( ccid IN NUMBER,
272 chart_of_accounts_id IN NUMBER,
273 bal_seg_val IN VARCHAR2,
274 intercompany_seg_val IN VARCHAR2,
275 bal_seg_column_number IN NUMBER,
276 intercompany_column_number IN NUMBER) RETURN VARCHAR2 IS
277 l_segment_array FND_FLEX_EXT.SEGMENTARRAY;
278 l_flag BOOLEAN;
279 l_no_of_segments NUMBER;
280 l_delimiter VARCHAR2(1);
281 l_cat_segs VARCHAR2(2000);
282 l_ccid NUMBER;
283 BEGIN
284 IF ccid = 0 OR ccid IS NULL THEN
285 RETURN NULL;
286 ELSIF ccid < 0 THEN
287 l_ccid := -ccid;
288 ELSE
289 l_ccid := ccid;
290 END IF;
291 l_delimiter := fnd_flex_ext.get_delimiter('SQLGL', 'GL#', chart_of_accounts_id);
292 l_flag := fnd_flex_ext.get_segments('SQLGL', 'GL#', chart_of_accounts_id, l_ccid,
293 l_no_of_segments, l_segment_array);
294 IF ccid < 0 THEN
295 l_segment_array(bal_seg_column_number) := bal_seg_val;
296 IF intercompany_column_number IS NOT NULL THEN
297 l_segment_array(intercompany_column_number) := intercompany_seg_val;
298 END IF;
299 END IF;
300 l_cat_segs := fnd_flex_ext.concatenate_segments(l_no_of_segments, l_segment_array, l_delimiter);
301 RETURN l_cat_segs;
302 END get_ccid_concat_disp;
303
304 PROCEDURE ins_headers_t(headers_tab IN headers_tab_type, headers_count IN NUMBER) IS
305 cur_hdl int;
306 BEGIN
307
308 cur_hdl := dbms_sql.open_cursor;
309 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_HEADERS_T', dbms_sql.native);
310 dbms_sql.close_cursor(cur_hdl); -- close cursor
311 IF headers_count > 0 THEN
312 FORALL i IN headers_tab.first..headers_tab.last
313 INSERT INTO fun_bal_headers_t
314 VALUES headers_tab(i);
315 END IF;
316 END ins_headers_t;
317
318
319 PROCEDURE ins_lines_t(lines_tab IN lines_tab_type, lines_count IN NUMBER) IS
320 cur_hdl int;
321 BEGIN
322 cur_hdl := dbms_sql.open_cursor;
323 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_LINES_T', dbms_sql.native);
324 dbms_sql.close_cursor(cur_hdl); -- close cursor
325 IF lines_count > 0 THEN
326 FORALL i IN lines_tab.first..lines_tab.last
327 INSERT INTO fun_bal_lines_t
328 VALUES lines_tab(i);
329 END IF;
330 END ins_lines_t;
331
332 PROCEDURE ins_results_t(results_tab IN results_tab_type, results_count IN NUMBER) IS
333 cur_hdl int;
334 BEGIN
335 cur_hdl := dbms_sql.open_cursor;
336 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_RESULTS_T', dbms_sql.native);
337 dbms_sql.close_cursor(cur_hdl); -- close cursor
338 IF results_count > 0 THEN
339 FORALL i IN results_tab.first..results_tab.last
340 INSERT INTO fun_bal_results_t
341 VALUES results_tab(i);
342 END IF;
343 END ins_results_t;
344
345 PROCEDURE ins_errors_t(errors_tab IN errors_tab_type, errors_count IN NUMBER) IS
346 cur_hdl int;
347 BEGIN
348 cur_hdl := dbms_sql.open_cursor;
349 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_ERRORS_T', dbms_sql.native);
350 dbms_sql.close_cursor(cur_hdl); -- close cursor
351 IF errors_count > 0 THEN
352 FORALL i IN errors_tab.first..errors_tab.last
353 INSERT INTO fun_bal_errors_t
354 VALUES errors_tab(i);
355 END IF;
356 END ins_errors_t;
357
358 PROCEDURE ins_inter_le_bsv_map_t(le_bsv_map_tab IN inter_le_bsv_map_tab_type, inter_le_bsv_map_count IN NUMBER) IS
359 cur_hdl int;
360 BEGIN
361 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
362 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'ins_inter_le_bsv_map_t.begin');
363 END IF;
364 cur_hdl := dbms_sql.open_cursor;
365 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_BSV_MAP_T', dbms_sql.native);
366 dbms_sql.close_cursor(cur_hdl); -- close cursor
367 IF inter_le_bsv_map_count > 0 THEN
368 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
369 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'ins_inter_le_bsv_map_t.insert_begin');
370 END IF;
371
372 FORALL i IN le_bsv_map_tab.first..le_bsv_map_tab.last
373 INSERT INTO fun_bal_inter_bsv_map_t
374 VALUES le_bsv_map_tab(i);
375
376 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
377 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'ins_inter_le_bsv_map_t.insert_end');
378 END IF;
379 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
380 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'ins_inter_le_bsv_map_t.end');
381 END IF;
382 END IF;
383 END ins_inter_le_bsv_map_t;
384
385 PROCEDURE ins_inter_int_t(inter_int_tab IN inter_int_tab_type, inter_int_count IN NUMBER) IS
386 cur_hdl int;
387 BEGIN
388 cur_hdl := dbms_sql.open_cursor;
389 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_INT_T', dbms_sql.native);
390 dbms_sql.close_cursor(cur_hdl); -- close cursor
391 IF inter_int_count > 0 THEN
392 FORALL i IN inter_int_tab.first..inter_int_tab.last
393 INSERT INTO fun_bal_inter_int_t
394 VALUES inter_int_tab(i);
395 END IF;
396 END ins_inter_int_t;
397
398
399 PROCEDURE ins_intra_le_bsv_map_t(le_bsv_map_tab IN intra_le_bsv_map_tab_type, intra_le_bsv_map_count IN NUMBER) IS
400 cur_hdl int;
401 BEGIN
402 cur_hdl := dbms_sql.open_cursor;
403 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_BSV_MAP_T', dbms_sql.native);
404 dbms_sql.close_cursor(cur_hdl); -- close cursor
405 IF intra_le_bsv_map_count > 0 THEN
406 FORALL i IN le_bsv_map_tab.first..le_bsv_map_tab.last
407 INSERT INTO fun_bal_intra_bsv_map_t
408 VALUES le_bsv_map_tab(i);
409 END IF;
410 END ins_intra_le_bsv_map_t;
411
412
413 PROCEDURE ins_intra_int_t(intra_int_tab IN intra_int_tab_type, intra_int_count IN NUMBER) IS
414 cur_hdl int;
415 BEGIN
416 cur_hdl := dbms_sql.open_cursor;
417 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_INT_T', dbms_sql.native);
418 dbms_sql.close_cursor(cur_hdl); -- close cursor
419 IF intra_int_count > 0 THEN
420 FORALL i IN intra_int_tab.first..intra_int_tab.last
421 INSERT INTO fun_bal_intra_int_t
422 VALUES intra_int_tab(i);
423 END IF;
424 END ins_intra_int_t;
425
426
427 PROCEDURE ins_t_tables_in_error_auto(headers_tab IN headers_tab_type,
428 lines_tab IN lines_tab_type,
429 headers_count IN NUMBER,
430 lines_count IN NUMBER) IS
431 PRAGMA AUTONOMOUS_TRANSACTION;
432 BEGIN
433 ins_headers_t(headers_tab, headers_count);
434 ins_lines_t(lines_tab, lines_count);
435 COMMIT;
436 END ins_t_tables_in_error_auto;
437
438
439 PROCEDURE ins_t_tables_final_auto(headers_tab IN headers_tab_type,
440 lines_tab IN lines_tab_type,
441 results_tab IN results_tab_type,
442 errors_tab IN errors_tab_type,
443 headers_count IN NUMBER,
444 lines_count IN NUMBER,
445 results_count IN NUMBER,
446 errors_count IN NUMBER) IS
447 PRAGMA AUTONOMOUS_TRANSACTION;
448 BEGIN
449 ins_headers_t(headers_tab, headers_count);
450 ins_lines_t(lines_tab, lines_count);
451 ins_results_t(results_tab, results_count);
452 ins_errors_t(errors_tab, errors_count);
453 /*
454 INSERT INTO fun_bal_headers_t
455 SELECT * FROM fun_bal_headers_gt;
456 INSERT INTO fun_bal_lines_t
457 SELECT * FROM fun_bal_lines_gt;
458 INSERT INTO fun_bal_results_t
459 SELECT * FROM fun_bal_results_gt;
460 INSERT INTO fun_bal_errors_t
461 SELECT * FROM fun_bal_errors_gt;
462 */
463 COMMIT;
464 END ins_t_tables_final_auto;
465
466 PROCEDURE ins_t_tables_inter_1_auto(le_bsv_map_tab IN inter_le_bsv_map_tab_type, le_bsv_map_count IN NUMBER) IS
467 PRAGMA AUTONOMOUS_TRANSACTION;
468 BEGIN
469
470 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
471 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'auton_begin');
472 END IF;
473 ins_inter_le_bsv_map_t(le_bsv_map_tab, le_bsv_map_count);
474 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
475 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'auton_end');
476 END IF;
477
478 COMMIT;
479 END ins_t_tables_inter_1_auto;
480
481 PROCEDURE ins_t_tables_inter_2_auto(inter_int_tab IN inter_int_tab_type, inter_int_count IN NUMBER) IS
482 PRAGMA AUTONOMOUS_TRANSACTION;
483 BEGIN
484
485 ins_inter_int_t(inter_int_tab, inter_int_count);
486 COMMIT;
487 END ins_t_tables_inter_2_auto;
488
489 PROCEDURE ins_t_tables_intra_1_auto(le_bsv_map_tab IN intra_le_bsv_map_tab_type, le_bsv_map_count IN NUMBER) IS
490 PRAGMA AUTONOMOUS_TRANSACTION;
491 BEGIN
492 ins_intra_le_bsv_map_t(le_bsv_map_tab, le_bsv_map_count);
493 COMMIT;
494 END ins_t_tables_intra_1_auto;
495
496 PROCEDURE ins_t_tables_intra_2_auto(intra_int_tab IN intra_int_tab_type, intra_int_count IN NUMBER) IS
497 PRAGMA AUTONOMOUS_TRANSACTION;
498 BEGIN
499 -- INSERT INTO fun_bal_intra_int_t
500 -- SELECT * FROM fun_bal_intra_int_gt;
501 ins_intra_int_t(intra_int_tab, intra_int_count);
502 COMMIT;
503 END ins_t_tables_intra_2_auto;
504
505 FUNCTION do_init RETURN VARCHAR2 IS
506 l_return_val VARCHAR2(1) ;
507 l_boolean BOOLEAN;
508 l_status VARCHAR2(1);
509 l_industry VARCHAR2(1);
510 BEGIN
511
512 l_return_val := FND_API.G_RET_STS_SUCCESS;
513
514 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
515 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_init.begin', 'begin');
516 END IF;
517 -- Retrieve the actual FUN schema name from the current installation
518 l_boolean := FND_INSTALLATION.GET_APP_INFO('FUN', l_status, l_industry, g_fun_schema);
519 IF g_debug = FND_API.G_TRUE THEN
520 -- Delete data stored in temporary tables
521 truncate_tables;
522 END IF;
523
524
525 -- Note: bal_seg_column_number is different from SUBSTR(bal_seg_column_name, 8),
526 -- since bal_seg_column_name refers to the naming in GL_CODE_COMBINATIONS,
527 -- but bal_seg_column_number refers to the position relative to the COA.
528 -- These 2 values are used in different context (name when dealing with GL_CODE_COMB table
529 -- and number when dealing with AOL routines. Do not be confused.
530 -- Problem here: Later on when we deal with performance and decided to check whether the
531 -- code combination already exists in gl_code_combinations table, we would also need to
532 -- retrieve the correct segment_name for intercompany segment.
533 -- update balancing segment column, chart of accounts
534 --ASLAI_INIT_01
535 UPDATE fun_bal_headers_gt headers
536 SET (bal_seg_column_name, chart_of_accounts_id) =
537 (SELECT bal_seg_column_name, chart_of_accounts_id
538 FROM gl_ledgers ledgers
539 WHERE headers.ledger_id = ledgers.ledger_id);
540
541 UPDATE fun_bal_headers_gt headers
542 SET bal_seg_column_number = get_segment_index ( headers.chart_of_accounts_id,
543 'GL_BALANCING'),
544 intercompany_column_number = get_segment_index ( headers.chart_of_accounts_id,
545 'GL_INTERCOMPANY');
546
547 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_HEADERS_GT');
548 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LINES_GT');
549
550 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
551 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_init.end', 'end');
552 END IF;
553
554
555 RETURN l_return_val;
556 END do_init;
557
558
559 PROCEDURE do_save_in_error IS
560 l_headers_tab headers_tab_type;
561 l_lines_tab lines_tab_type;
562 l_headers_count NUMBER;
563 l_lines_count NUMBER;
564 CURSOR l_headers_cursor IS
565 SELECT * FROM fun_bal_headers_gt;
566 CURSOR l_lines_cursor IS
567 SELECT * FROM fun_bal_lines_gt;
568 BEGIN
569 OPEN l_headers_cursor;
570 FETCH l_headers_cursor BULK COLLECT INTO l_headers_tab;
571 l_headers_count := l_headers_cursor%ROWCOUNT;
572 CLOSE l_headers_cursor;
573 OPEN l_lines_cursor;
574 FETCH l_lines_cursor BULK COLLECT INTO l_lines_tab;
575 l_lines_count := l_lines_cursor%ROWCOUNT;
576 CLOSE l_lines_cursor;
577
578 ins_t_tables_in_error_auto(l_headers_tab, l_lines_tab,
579 l_headers_count, l_lines_count);
580 END do_save_in_error;
581
582
583 FUNCTION do_finalize RETURN VARCHAR2 IS
584 l_return_val VARCHAR2(1) ;
585 --l_error_count NUMBER(15) := 0;
586 l_headers_tab headers_tab_type;
587 l_lines_tab lines_tab_type;
588 l_results_tab results_tab_type;
589 l_errors_tab errors_tab_type;
590 l_headers_count NUMBER;
591 l_lines_count NUMBER;
592 l_results_count NUMBER;
593 l_errors_count NUMBER;
594 CURSOR l_headers_cursor IS
595 SELECT * FROM fun_bal_headers_gt;
596 CURSOR l_lines_cursor IS
597 SELECT * FROM fun_bal_lines_gt;
598 CURSOR l_results_cursor IS
599 SELECT * FROM fun_bal_results_gt;
600 CURSOR l_errors_cursor IS
601 SELECT * FROM fun_bal_errors_gt;
602 BEGIN
603 l_return_val := FND_API.G_RET_STS_SUCCESS;
604
605 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
606 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_finalize.begin', 'begin');
607 END IF;
608
609 -- Insert lines generated for Intercompany balancing from FUN_BAL_LINES_GT to FUN_BAL_RESULTS_GT
610 INSERT INTO fun_bal_results_gt results(group_id, bal_seg_val, entered_amt_dr,
611 entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
612 accounted_amt_dr, accounted_amt_cr, ccid, balancing_type)
613 SELECT lines.group_id, lines.bal_seg_val, lines.entered_amt_dr,
614 lines.entered_amt_cr, lines.entered_currency_code, lines.exchange_date, lines.exchange_rate,
615 lines.exchange_rate_type, lines.accounted_amt_dr, lines.accounted_amt_cr,
616 lines.ccid, 'E'
617 FROM fun_bal_lines_gt lines
618 WHERE lines.generated = 'Y';
619
620 -- Bug 3167894
621 UPDATE fun_bal_results_gt results
622 SET entered_amt_dr = DECODE(entered_amt_dr, NULL, DECODE(accounted_amt_dr, NULL, entered_amt_dr, 0), entered_amt_dr),
623 entered_amt_cr = DECODE(entered_amt_cr, NULL, DECODE(accounted_amt_cr, NULL, entered_amt_cr, 0), entered_amt_cr),
624 accounted_amt_dr = DECODE(accounted_amt_dr, NULL, DECODE(entered_amt_dr, NULL, accounted_amt_dr, 0), accounted_amt_dr),
625 accounted_amt_cr = DECODE(accounted_amt_cr, NULL, DECODE(entered_amt_cr, NULL, accounted_amt_cr, 0), accounted_amt_cr);
626
627 IF g_debug = FND_API.G_TRUE THEN
628 OPEN l_headers_cursor;
629 FETCH l_headers_cursor BULK COLLECT INTO l_headers_tab;
630 l_headers_count := l_headers_cursor%ROWCOUNT;
631 CLOSE l_headers_cursor;
632 OPEN l_lines_cursor;
633 FETCH l_lines_cursor BULK COLLECT INTO l_lines_tab;
634 l_lines_count := l_lines_cursor%ROWCOUNT;
635 CLOSE l_lines_cursor;
636 OPEN l_results_cursor;
637 FETCH l_results_cursor BULK COLLECT INTO l_results_tab;
638 l_results_count := l_results_cursor%ROWCOUNT;
639 CLOSE l_results_cursor;
640 OPEN l_errors_cursor;
641 FETCH l_errors_cursor BULK COLLECT INTO l_errors_tab;
642 l_errors_count := l_errors_cursor%ROWCOUNT;
643 CLOSE l_errors_cursor;
644
645 ins_t_tables_final_auto(l_headers_tab, l_lines_tab, l_results_tab, l_errors_tab,
646 l_headers_count, l_lines_count, l_results_count, l_errors_count);
647 ELSE
648 SELECT COUNT(*) INTO l_errors_count
649 FROM fun_bal_errors_gt;
650 END IF;
651
652 IF l_errors_count > 0 THEN
653 l_return_val := FND_API.G_RET_STS_ERROR;
654 END IF;
655
656 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
657 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_finalize.end', 'end');
658 END IF;
659 RETURN l_return_val;
660 END do_finalize;
661
662
663 FUNCTION do_inter_bal RETURN VARCHAR2 IS
664 l_le_bsv_map_tab inter_le_bsv_map_tab_type;
665 l_inter_int_tab inter_int_tab_type;
666 CURSOR l_le_bsv_map_cursor IS
667 SELECT * FROM fun_bal_le_bsv_map_gt;
668 CURSOR l_inter_int_cursor IS
669 SELECT * FROM fun_bal_inter_int_gt;
670 l_le_bsv_map_count NUMBER;
671 l_inter_int_count NUMBER;
672 BEGIN
673 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
674 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.begin', 'begin');
675 END IF;
676
677 /* Replaced by sql below from performance review
678 INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date)
679 SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date
680 FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger,
681 gl_ledger_configurations config
682 WHERE hdrs.group_id = lines.group_id
683 AND hdrs.ledger_id = ledger.ledger_id
684 AND ledger.configuration_id = config.configuration_id
685 AND ledger.bal_seg_value_option_code = 'I';
686 -- Only possible scenario to perform Intercompany is when bal_seg_value_option_code is 'I' and acct_env_code is 'SHARED'
687
688 -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT
689
690 -- Legal entity can only be either null or has a specific value
691 UPDATE fun_bal_le_bsv_map_gt bsv_le_map
692 SET le_id =
693 NVL((SELECT vals.legal_entity_id
694 FROM gl_ledger_le_bsv_specific_v vals
695 WHERE bsv_le_map.bal_seg_val = vals.segment_value
696 AND (TRUNC(bsv_le_map.gl_date) BETWEEN TRUNC(NVL(vals.start_date, bsv_le_map.gl_date)) AND
697 TRUNC(NVL(vals.end_date, bsv_le_map.gl_date)))
698 AND bsv_le_map.ledger_id = vals.ledger_id
699 ), -99);
700
701 */
702
703 -- Bug 3310453
704 INSERT INTO fun_bal_errors_gt(error_code, group_id, bal_seg_val)
705 SELECT 'FUN_BSV_INVALID', main.group_id, main.bal_seg_val
706 FROM (SELECT DISTINCT hdrs.group_id, lines.bal_seg_val, hdrs.gl_date, hdrs.ledger_id
707 FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger
708 WHERE hdrs.group_id = lines.group_id
709 AND hdrs.ledger_id = ledger.ledger_id(+)
710 AND ledger.bal_seg_value_option_code = 'I') main
711 WHERE main.bal_seg_val NOT IN (SELECT vals.segment_value
712 FROM gl_ledger_le_bsv_specific_v vals
713 WHERE main.ledger_id = vals.ledger_id
714 AND TRUNC(main.gl_date) BETWEEN
715 TRUNC(NVL(vals.start_date, main.gl_date)) AND
716 TRUNC(NVL(vals.end_date, main.gl_date)));
717 -- Bug 3310453
718 UPDATE fun_bal_headers_gt headers
719 SET status = 'ERROR',
720 error_code = 'FUN_BSV_INVALID'
721 WHERE EXISTS (SELECT 'Invalid BSV Error'
722 FROM FUN_BAL_ERRORS_GT errors
723 WHERE headers.group_id = errors.group_id
724 AND error_code IN ('FUN_BSV_INVALID'))
725 AND headers.status = 'OK';
726
727 -- Select the distinct combination of GROUP_ID, LEDGER_ID and BAL_SEG_VAL into
728 -- FUN_BAL_LE_BSV_MAP_GT. We are only inserting the journals with ledgers in shared
729 -- mode configuration, as intercompany balancing should only be performed in shared mode.
730 -- Doing so should decrease the amount of processing time required at a later stage.
731
732 -- Only possible scenario to perform Intercompany is when bal_seg_value_option_code is 'I' and acct_env_code is 'SHARED'
733 -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT
734 -- Legal entity can only be either null or has a specific value
735
736 INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date, le_id)
737 SELECT main.group_id, main.ledger_id, main.bal_seg_val, main.gl_date, NVL(vals.legal_entity_id, -99)
738 FROM (SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date
739 FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger,
740 gl_ledger_configurations config
741 WHERE hdrs.status = 'OK' -- Bug 3310453
742 AND hdrs.group_id = lines.group_id
743 AND hdrs.ledger_id = ledger.ledger_id(+)
744 AND ledger.configuration_id = config.configuration_id
745 AND ledger.configuration_id <> -2 -- Bug 3271446
746 AND ledger.bal_seg_value_option_code = 'I') main,
747 gl_ledger_le_bsv_specific_v vals
748 WHERE main.bal_seg_val = vals.segment_value(+)
749 AND (TRUNC(main.gl_date) BETWEEN TRUNC(NVL(vals.start_date, main.gl_date)) AND
750 TRUNC(NVL(vals.end_date, main.gl_date)))
751 AND main.ledger_id = vals.ledger_id(+);
752
753
754 UPDATE fun_bal_headers_gt headers
755 SET (le_id, le_count) =
756 (SELECT MIN(le_bsv_map.le_id), SUM(COUNT(DISTINCT le_bsv_map.le_id))
757 FROM fun_bal_le_bsv_map_gt le_bsv_map
758 WHERE headers.group_id = le_bsv_map.group_id
759 AND le_bsv_map.le_id <> -99
760 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
761
762
763 UPDATE fun_bal_headers_gt headers
764 SET status = DECODE(le_id, NULL, status, 'ERROR'),
765 error_code = DECODE(le_id, NULL, error_code, 'FUN_INTER_BSV_NOT_ASSIGNED'),
766 unmapped_bsv_le_id = -99
767 WHERE EXISTS (SELECT 'Unmapped BSV exists'
768 FROM fun_bal_le_bsv_map_gt le_bsv_map
769 WHERE le_bsv_map.group_id = headers.group_id
770 AND le_bsv_map.le_id = -99);
771
772
773 -- Error out if error out bsv is provided and either one of the following conditions are true:
774 -- I) more than one le count
775 -- II) one le count and non-mapped count
776 -- III) the clearing BSV entered does not belong to the LE or to the ledger
777 -- or one le count and non-mapped count
778 -- IV) le_count IS NULL and unmapped_bsv_le_id IS NULL if not shared configuration
779 -- and BSV validation set to specific
780 UPDATE fun_bal_headers_gt headers
781 SET status = 'ERROR',
782 error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR'
783 WHERE headers.status = 'OK'
784 AND headers.clearing_bsv IS NOT NULL
785 AND NOT (headers.le_count IS NULL AND headers.unmapped_bsv_le_id IS NULL) -- Bug 3278912
786 AND (headers.le_count > 1
787 OR
788 (headers.le_count = 1 AND headers.unmapped_bsv_le_id = -99)
789 OR
790 (headers.le_id IS NOT NULL
791 AND NOT EXISTS (SELECT 'BSV belongs to the LE'
792 FROM gl_ledger_le_bsv_specific_v vals
793 WHERE vals.segment_value = headers.clearing_bsv
794 AND vals.ledger_id = headers.ledger_id
795 AND vals.legal_entity_id = headers.le_id))
796 OR
797 (headers.le_id IS NULL
798 AND EXISTS (SELECT 'BSV belongs to Ledger'
799 FROM gl_ledger_le_bsv_specific_v vals
800 WHERE vals.segment_value = headers.clearing_bsv
801 AND vals.ledger_id = headers.ledger_id
802 AND vals.legal_entity_id IS NOT NULL))); -- Bug 3278912
803
804 -- Bug 3310453
805 UPDATE fun_bal_headers_gt hdrs
806 SET status = 'ERROR',
807 error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR'
808 WHERE hdrs.status = 'OK'
809 AND hdrs.clearing_bsv IS NOT NULL
810 AND NOT (hdrs.ledger_id IN (SELECT ledgers.ledger_id
811 FROM gl_ledgers ledgers
812 WHERE ledgers.bal_seg_value_option_code = 'A')
813 OR
814 hdrs.clearing_bsv IN (SELECT vals.segment_value
815 FROM gl_ledger_le_bsv_specific_v vals
816 WHERE hdrs.ledger_id = vals.ledger_id
817 AND TRUNC(hdrs.gl_date) BETWEEN
818 TRUNC(NVL(vals.start_date, hdrs.gl_date))
819 AND
820 TRUNC(NVL(vals.end_date, hdrs.gl_date))));
821
822 INSERT INTO fun_bal_errors_gt(error_code, group_id, clearing_bsv)
823 SELECT 'FUN_INTRA_OVERRIDE_BSV_ERROR', hdrs.group_id, hdrs.clearing_bsv
824 FROM fun_bal_headers_gt hdrs
825 WHERE hdrs.error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR';
826
827 INSERT INTO fun_bal_errors_gt(error_code, group_id, bal_seg_val)
828 SELECT 'FUN_INTER_BSV_NOT_ASSIGNED', hdrs.group_id, le_bsv_map.bal_seg_val
829 FROM fun_bal_headers_gt hdrs, fun_bal_le_bsv_map_gt le_bsv_map
830 WHERE hdrs.group_id = le_bsv_map.group_id
831 AND hdrs.error_code = 'FUN_INTER_BSV_NOT_ASSIGNED'
832 AND le_bsv_map.le_id = -99;
833
834 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LE_BSV_MAP_GT');
835
836 IF g_debug = FND_API.G_TRUE THEN
837 OPEN l_le_bsv_map_cursor;
838 FETCH l_le_bsv_map_cursor BULK COLLECT INTO l_le_bsv_map_tab;
839 l_le_bsv_map_count := l_le_bsv_map_cursor%ROWCOUNT;
840 CLOSE l_le_bsv_map_cursor;
841 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
842 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'begin');
843 END IF;
844 ins_t_tables_inter_1_auto(l_le_bsv_map_tab, l_le_bsv_map_count);
845 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
846 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'end');
847 END IF;
848
849 END IF;
850
851
852 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
853 WHERE group_id = (SELECT group_id
854 FROM fun_bal_headers_gt headers
855 WHERE headers.status = 'ERROR'
856 AND le_bsv_map.group_id = headers.group_id);
857
858 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
859 WHERE EXISTS (SELECT 'LE already balanced'
860 FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
861 WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
862 AND le_bsv_map_del.le_id = le_bsv_map.le_id
863 AND le_bsv_map.group_id = lines.group_id
864 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
865 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
866 HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) =
867 SUM(NVL(lines.accounted_amt_cr, 0)))
868 AND
869 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0))=
870 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)));
871
872
873 -- Determine driving_dr_le_id, intercompany mode
874 UPDATE fun_bal_headers_gt hdrs
875 SET (driving_dr_le_id, intercompany_mode) =
876 (SELECT MIN(le_bsv_map.le_id),
877 SUM(COUNT(DISTINCT(le_bsv_map.le_id)))
878 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
879 WHERE hdrs.group_id = le_bsv_map.group_id
880 AND le_bsv_map.group_id = lines.group_id
881 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
882 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
883 HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) >
884 SUM(NVL(lines.accounted_amt_cr, 0)))
885 OR
886 ((SUM(NVL(lines.accounted_amt_dr, 0)) =
887 SUM(NVL(lines.accounted_amt_cr, 0))) AND
888 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) >
889 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0))))
890 WHERE status = 'OK';
891
892
893 -- Deleting the records that do not require intercompany balancing.
894 -- Deleting these records first should make the code perform better,
895 -- as there won't be any more join to these lines.
896 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
897 WHERE EXISTS
898 (SELECT 'Intercompany balancing is not required'
899 FROM fun_bal_headers_gt hdrs
900 WHERE le_bsv_map.group_id = hdrs.group_id
901 AND hdrs.status = 'OK'
902 AND hdrs.intercompany_mode IS NULL);
903
904 -- Determine driving_cr_le_id, intercompany_mode
905 UPDATE fun_bal_headers_gt hdrs
906 SET (driving_cr_le_id, intercompany_mode) =
907 (SELECT MIN(le_bsv_map.le_id),
908 DECODE(SUM(COUNT(DISTINCT(le_bsv_map.le_id))), 1,
909 DECODE(hdrs.intercompany_mode, 1, 1, 3),
910 DECODE(hdrs.intercompany_mode, 1, 2, 4))
911 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
912 WHERE hdrs.group_id = le_bsv_map.group_id
913 AND le_bsv_map.group_id = lines.group_id
914 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
915 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
916 HAVING (SUM(NVL(lines.accounted_amt_cr, 0)) >
917 SUM(NVL(lines.accounted_amt_dr, 0)))
918 OR
919 ((SUM(NVL(lines.accounted_amt_dr, 0)) =
920 SUM(NVL(lines.accounted_amt_cr, 0))) AND
921 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) >
922 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))))
923 WHERE status = 'OK';
924
925 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
926 WHERE EXISTS (SELECT 'No Driving DR LE or Driving CR LE'
927 FROM fun_bal_headers_gt headers
928 WHERE headers.group_id = le_bsv_map.group_id
929 AND headers.status = 'OK'
930 AND (headers.driving_dr_le_id IS NULL
931 OR
932 headers.driving_cr_le_id IS NULL));
933
934 -- Insert into FUN_BAL_INTER_INT_GT with all lines that require Intercompany balancing
935 INSERT INTO fun_bal_inter_int_gt(group_id, ledger_id, gl_date, status, driving_dr_le_id, driving_cr_le_id,
936 intercompany_mode, le_id, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
937 accounted_amt_cr, accounted_amt_dr, entered_amt_cr, entered_amt_dr, bal_seg_column_name, type)
938 SELECT hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
939 hdrs.intercompany_mode, le_bsv_map.le_id, lines.entered_currency_code,
940 lines.exchange_date, lines.exchange_rate, lines.exchange_rate_type,
941 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
942 1, ABS(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))), NULL)
943 accounted_amt_cr,
944 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
945 -1, ABS(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))), NULL)
946 accounted_amt_dr,
947 DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))),
948 1, ABS(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))), NULL)
949 entered_amt_cr,
950 DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))),
951 -1, ABS(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))), NULL)
952 entered_amt_dr,
953 hdrs.bal_seg_column_name,
954 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0))-SUM(NVL(lines.accounted_amt_dr,0))),
955 1, 'C',
956 -1, 'D',
957 0, DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
958 1, 'C',
959 'D')) type
960 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
961 fun_bal_headers_gt hdrs
962 WHERE hdrs.group_id = lines.group_id
963 AND lines.group_id = le_bsv_map.group_id
964 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
965 AND hdrs.intercompany_mode IN (1,2,3,4)
966 AND hdrs.status = 'OK'
967 GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
968 hdrs.intercompany_mode, le_bsv_map.le_id, lines.entered_currency_code, lines.exchange_date,
969 lines.exchange_rate, lines.exchange_rate_type,
970 hdrs.bal_seg_column_name, hdrs.intercompany_column_number
971 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
972 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
973 AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
974 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)));
975
976 -- Balancing API changes, Feb 22 2005, Start
977 -- We now need to find the the payables and receivables account using
978 -- the Legal Entity and BSV value. Initially we found the account using
979 -- only the LE id.
980 -- Find out the balancing segment values for the dr le id
981 -- This will set the value correctly where mode is 1 : 1 or 1 : M
982 UPDATE fun_bal_inter_int_gt upd
983 SET driving_dr_le_bsv =
984 (SELECT DECODE((COUNT(DISTINCT le_bsv_map.bal_seg_val)),
985 1, MIN(le_bsv_map.bal_seg_val),
986 'Many')
987 FROM fun_bal_le_bsv_map_gt le_bsv_map
988 WHERE upd.group_id = le_bsv_map.group_id
989 AND upd.driving_dr_le_id = le_bsv_map.le_id
990 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
991
992 -- Find out the balancing segment values for the cr le id
993 -- This will set the value correctly where mode is 1 : 1 or M : 1
994 UPDATE fun_bal_inter_int_gt upd
995 SET driving_cr_le_bsv =
996 (SELECT DECODE((COUNT(DISTINCT le_bsv_map.bal_seg_val)),
997 1, MIN(le_bsv_map.bal_seg_val),
998 'Many')
999 FROM fun_bal_le_bsv_map_gt le_bsv_map
1000 WHERE upd.group_id = le_bsv_map.group_id
1001 AND upd.driving_cr_le_id = le_bsv_map.le_id
1002 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
1003
1004 -- Find out the balancing segment values for the cr le id
1005 -- This will set the value correctly where mode is 1 : 1 or M : 1
1006 UPDATE fun_bal_inter_int_gt upd
1007 SET line_le_bsv =
1008 (SELECT DECODE((COUNT(DISTINCT le_bsv_map.bal_seg_val)),
1009 1, MIN(le_bsv_map.bal_seg_val),
1010 'Many')
1011 FROM fun_bal_le_bsv_map_gt le_bsv_map
1012 WHERE upd.group_id = le_bsv_map.group_id
1013 AND upd.le_id = le_bsv_map.le_id
1014 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
1015
1016 -- Update receivables account for specific LE and BSV values
1017 -- For 1:1 -
1018 -- if driving le is Cr And type = Cr
1019 -- driving cr le id = from le; driving dr le id = to le;
1020 -- if driving le is Dr And type = Cr,
1021 -- driving Dr le id = from le; driving cr le id = to le;
1022 -- Else leave receivables account as null (basically when type = Dr ?)
1023 -- For 1: Many -
1024 -- if type = Cr,
1025 -- line le id = from le; Driving dr le id = to le
1026 -- if type = Dr,
1027 -- driving Dr le id = from le; line le id = to le
1028 -- For Many : 1 -
1029 -- if type = Cr,
1030 -- line le id = from le; driving Cr le id = to le
1031 -- if type = Dr,
1032 -- driving Cr le id = from le; line le id = to le
1033 -- For Many : Many -
1034 -- Dont get receives account.
1035
1036 -- Rules of precedence is to find matching records using
1037 -- 1) From LE, From BSV => To LE, To BSV
1038 -- 2) From LE, From BSV => To LE
1039 -- 3) From LE => To LE, To BSV
1040 -- 4) From LE => To LE
1041 -- 4) From LE => To All Others
1042
1043 -- For 1:1, search from rule 1 and progress through to rule 5 if not found
1044 UPDATE fun_bal_inter_int_gt inter_int
1045 SET rec_acct =
1046 (SELECT ccid
1047 FROM fun_inter_accounts accts
1048 WHERE inter_int.ledger_id = accts.ledger_id
1049 AND DECODE(inter_int.intercompany_mode,
1050 1, DECODE(le_id, driving_cr_le_id,
1051 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1052 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1053 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1054 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1055 NULL) = accts.from_le_id
1056 AND DECODE(inter_int.intercompany_mode,
1057 1, DECODE(le_id, driving_cr_le_id,
1058 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
1059 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
1060 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1061 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1062 NULL) = accts.to_le_id
1063 AND DECODE(inter_int.intercompany_mode,
1064 1, DECODE(le_id, driving_cr_le_id,
1065 DECODE(inter_int.type, 'C', driving_cr_le_bsv ,NULL),
1066 DECODE(inter_int.type, 'C', driving_dr_le_bsv),NULL),
1067 2, DECODE(inter_int.type, 'C', line_le_bsv, driving_dr_le_bsv),
1068 3, DECODE(inter_int.type, 'C', line_le_bsv,driving_cr_le_bsv),
1069 NULL) = accts.trans_bsv -- From BSV
1070
1071 AND DECODE(inter_int.intercompany_mode,
1072 1, DECODE(le_id, driving_cr_le_id,
1073 DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL),
1074 DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL)),
1075 2, DECODE(inter_int.type, 'C', driving_dr_le_bsv,line_le_bsv),
1076 3, DECODE(inter_int.type, 'C', driving_cr_le_bsv,line_le_bsv),
1077 NULL) = accts.tp_bsv -- To BSV
1078 AND accts.type = 'R'
1079 AND accts.default_flag = 'Y'
1080 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1081 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1082 WHERE inter_int.intercompany_mode IN (1,2,3)
1083 AND driving_dr_le_bsv <> 'Many'
1084 AND driving_cr_le_bsv <> 'Many';
1085
1086 -- For 1:M, search for rule 2
1087 UPDATE fun_bal_inter_int_gt inter_int
1088 SET rec_acct =
1089 (SELECT ccid
1090 FROM fun_inter_accounts accts
1091 WHERE inter_int.ledger_id = accts.ledger_id
1092 AND DECODE(inter_int.intercompany_mode,
1093 1, DECODE(le_id, driving_cr_le_id,
1094 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1095 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1096 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1097 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1098 NULL) = accts.from_le_id
1099 AND DECODE(inter_int.intercompany_mode,
1100 1, DECODE(le_id, driving_cr_le_id,
1101 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
1102 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
1103 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1104 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1105 NULL) = accts.to_le_id
1106 AND DECODE(inter_int.intercompany_mode,
1107 1, DECODE(le_id, driving_cr_le_id,
1108 DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL),
1109 DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL)),
1110 2, DECODE(inter_int.type, 'C', line_le_bsv,driving_dr_le_bsv),
1111 3, DECODE(inter_int.type, 'C', line_le_bsv,driving_cr_le_bsv),
1112 NULL) = accts.trans_bsv -- From BSV
1113
1114 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
1115 AND accts.type = 'R'
1116 AND accts.default_flag = 'Y'
1117 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1118 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1119 WHERE inter_int.intercompany_mode IN (1,2,3)
1120 AND inter_int.rec_acct IS NULL;
1121
1122 -- For M:1, search from rule 3 and progress through to rule 5 if not found
1123 UPDATE fun_bal_inter_int_gt inter_int
1124 SET rec_acct =
1125 (SELECT ccid
1126 FROM fun_inter_accounts accts
1127 WHERE inter_int.ledger_id = accts.ledger_id
1128 AND DECODE(inter_int.intercompany_mode,
1129 1, DECODE(le_id, driving_cr_le_id,
1130 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1131 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1132 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1133 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1134 NULL) = accts.from_le_id
1135 AND DECODE(inter_int.intercompany_mode,
1136 1, DECODE(le_id, driving_cr_le_id,
1137 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
1138 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
1139 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1140 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1141 NULL) = accts.to_le_id
1142 AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
1143 AND DECODE(inter_int.intercompany_mode,
1144 1, DECODE(le_id, driving_cr_le_id,
1145 DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL),
1146 DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL)),
1147 2, DECODE(inter_int.type, 'C', driving_dr_le_bsv,line_le_bsv),
1148 3, DECODE(inter_int.type, 'C', driving_cr_le_bsv,line_le_bsv),
1149 NULL) = accts.tp_bsv -- To BSV
1150
1151 AND accts.type = 'R'
1152 AND accts.default_flag = 'Y'
1153 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1154 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1155 WHERE inter_int.intercompany_mode IN (1,2,3)
1156 AND inter_int.rec_acct IS NULL;
1157
1158 -- The above will take care of rules 1 to 3.
1159 -- The account has not been found, the following will deal with rule 4
1160 -- ie it looks at specific LE without checking for the BSV
1161 UPDATE fun_bal_inter_int_gt inter_int
1162 SET rec_acct =
1163 (SELECT ccid
1164 FROM fun_inter_accounts accts
1165 WHERE inter_int.ledger_id = accts.ledger_id
1166 AND DECODE(inter_int.intercompany_mode,
1167 1, DECODE(le_id, driving_cr_le_id,
1168 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1169 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1170 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1171 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1172 NULL) = accts.from_le_id
1173 AND DECODE(inter_int.intercompany_mode,
1174 1, DECODE(le_id, driving_cr_le_id,
1175 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
1176 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
1177 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1178 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1179 NULL) = accts.to_le_id
1180 AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
1181 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
1182 AND accts.type = 'R'
1183 AND accts.default_flag = 'Y'
1184 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1185 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1186 WHERE inter_int.intercompany_mode IN (1,2,3)
1187 AND inter_int.rec_acct IS NULL;
1188 -- End, Balancing API Changes, Feb 2005
1189
1190 -- Update receivables account for other LE if no account specified for specific LE
1191 -- This will handle rule 5
1192 UPDATE fun_bal_inter_int_gt inter_int
1193 SET rec_acct =
1194 (SELECT ccid
1195 FROM fun_inter_accounts accts
1196 WHERE inter_int.ledger_id = accts.ledger_id
1197 AND inter_int.rec_acct IS NULL
1198 AND DECODE(inter_int.intercompany_mode,
1199 1, DECODE(le_id, driving_cr_le_id,
1200 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1201 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1202 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1203 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1204 4, DECODE(inter_int.type, 'C', le_id, NULL),
1205 NULL) = accts.from_le_id
1206 AND accts.to_le_id = -99 -- To LE "Other"
1207 AND accts.type = 'R'
1208 AND accts.default_flag = 'Y'
1209 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
1210 TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1211 WHERE inter_int.rec_acct IS NULL;
1212
1213 -- Update payables account for specific LE
1214 -- 1:1 mapping to begin with
1215 UPDATE fun_bal_inter_int_gt inter_int
1216 SET pay_acct =
1217 (SELECT ccid
1218 FROM fun_inter_accounts accts
1219 WHERE inter_int.ledger_id = accts.ledger_id
1220 AND DECODE(inter_int.intercompany_mode,
1221 1, DECODE(le_id, driving_dr_le_id,
1222 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
1223 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
1224 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
1225 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
1226 NULL) = accts.from_le_id
1227 AND DECODE(inter_int.intercompany_mode,
1228 1, DECODE(le_id, driving_dr_le_id,
1229 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
1230 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
1231 2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
1232 3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
1233 NULL) = accts.to_le_id
1234 AND DECODE(inter_int.intercompany_mode,
1235 1, DECODE(le_id,driving_dr_le_id,
1236 DECODE(inter_int.type, 'D', driving_dr_le_bsv,NULL),
1237 DECODE(inter_int.type, 'D', driving_cr_le_bsv, NULL)),
1238 2, DECODE(inter_int.type, 'D', line_le_bsv,driving_dr_le_bsv),
1239 3, DECODE(inter_int.type, 'D', line_le_bsv,driving_cr_le_bsv),
1240 NULL) = accts.trans_bsv
1241 AND DECODE(inter_int.intercompany_mode,
1242 1, DECODE(le_id,driving_dr_le_id,
1243 DECODE(inter_int.type, 'D', driving_cr_le_bsv,NULL),
1244 DECODE(inter_int.type, 'D', driving_dr_le_bsv, NULL)),
1245 2, DECODE(inter_int.type, 'D', driving_dr_le_bsv,line_le_bsv),
1246 3, DECODE(inter_int.type, 'D', driving_cr_le_bsv,line_le_bsv),
1247 NULL) = accts.tp_bsv -- To BSV
1248 AND accts.type = 'P'
1249 AND accts.default_flag = 'Y'
1250 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1251 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1252 WHERE inter_int.intercompany_mode IN (1,2,3)
1253 AND driving_cr_le_bsv <> 'Many'
1254 AND driving_dr_le_bsv <> 'Many';
1255
1256 -- 1:M - next
1257 UPDATE fun_bal_inter_int_gt inter_int
1258 SET pay_acct =
1259 (SELECT ccid
1260 FROM fun_inter_accounts accts
1261 WHERE inter_int.ledger_id = accts.ledger_id
1262 AND DECODE(inter_int.intercompany_mode,
1263 1, DECODE(le_id, driving_dr_le_id,
1264 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
1265 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
1266 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
1267 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
1268 NULL) = accts.from_le_id
1269 AND DECODE(inter_int.intercompany_mode,
1270 1, DECODE(le_id, driving_dr_le_id,
1271 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
1272 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
1273 2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
1274 3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
1275 NULL) = accts.to_le_id
1276 AND DECODE(inter_int.intercompany_mode,
1277 1, DECODE(le_id,driving_dr_le_id,
1278 DECODE(inter_int.type, 'D', driving_dr_le_bsv,NULL),
1279 DECODE(inter_int.type, 'D', driving_cr_le_bsv, NULL)),
1280 2, DECODE(inter_int.type, 'D', line_le_bsv,driving_dr_le_bsv),
1281 3, DECODE(inter_int.type, 'D', line_le_bsv,driving_cr_le_bsv),
1282 NULL) = accts.trans_bsv
1283 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
1284 AND accts.type = 'P'
1285 AND accts.default_flag = 'Y'
1286 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1287 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1288 WHERE inter_int.intercompany_mode IN (1,2,3)
1289 AND inter_int.pay_acct IS NULL;
1290
1291 -- M:1 - next
1292 UPDATE fun_bal_inter_int_gt inter_int
1293 SET pay_acct =
1294 (SELECT ccid
1295 FROM fun_inter_accounts accts
1296 WHERE inter_int.ledger_id = accts.ledger_id
1297 AND DECODE(inter_int.intercompany_mode,
1298 1, DECODE(le_id, driving_dr_le_id,
1299 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
1300 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
1301 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
1302 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
1303 NULL) = accts.from_le_id
1304 AND DECODE(inter_int.intercompany_mode,
1305 1, DECODE(le_id, driving_dr_le_id,
1306 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
1307 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
1308 2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
1309 3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
1310 NULL) = accts.to_le_id
1311 AND 'OTHER1234567890123456789012345' = accts.trans_bsv
1312 AND DECODE(inter_int.intercompany_mode,
1313 1, DECODE(le_id,driving_dr_le_id,
1314 DECODE(inter_int.type, 'D', driving_cr_le_bsv,NULL),
1315 DECODE(inter_int.type, 'D', driving_dr_le_bsv, NULL)),
1316 2, DECODE(inter_int.type, 'D', driving_dr_le_bsv,line_le_bsv),
1317 3, DECODE(inter_int.type, 'D', driving_cr_le_bsv,line_le_bsv),
1318 NULL) = accts.tp_bsv -- To BSV
1319 AND accts.type = 'P'
1320 AND accts.default_flag = 'Y'
1321 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1322 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1323 WHERE inter_int.intercompany_mode IN (1,2,3)
1324 AND inter_int.pay_acct IS NULL ;
1325
1326 -- If the payables account was not found, look for an account as per rule 4
1327 -- ie from le to te
1328 UPDATE fun_bal_inter_int_gt inter_int
1329 SET pay_acct =
1330 (SELECT ccid
1331 FROM fun_inter_accounts accts
1332 WHERE inter_int.ledger_id = accts.ledger_id
1333 AND DECODE(inter_int.intercompany_mode,
1334 1, DECODE(le_id, driving_dr_le_id,
1335 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
1336 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
1337 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
1338 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
1339 NULL) = accts.from_le_id
1340 AND DECODE(inter_int.intercompany_mode,
1341 1, DECODE(le_id, driving_dr_le_id,
1342 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
1343 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
1344 2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
1345 3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
1346 NULL) = accts.to_le_id
1347 AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
1348 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
1349 AND accts.type = 'P'
1350 AND accts.default_flag = 'Y'
1351 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
1352 TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1353 WHERE inter_int.intercompany_mode IN (1,2,3)
1354 AND inter_int.pay_acct IS NULL;
1355 -- End, Balancing API changes
1356
1357 -- Update payables account for other LE if no account specified for specific LE
1358 -- This will deal with rule 5, From LE to All Others
1359 UPDATE fun_bal_inter_int_gt inter_int
1360 SET pay_acct =
1361 (SELECT ccid
1362 FROM fun_inter_accounts accts
1363 WHERE inter_int.ledger_id = accts.ledger_id
1364 AND inter_int.pay_acct IS NULL
1365 AND DECODE(inter_int.intercompany_mode,
1366 1, DECODE(le_id, driving_dr_le_id,
1367 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
1368 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
1369 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
1370 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
1371 4, DECODE(inter_int.type, 'D', le_id, NULL),
1372 NULL) = accts.from_le_id
1373 AND accts.to_le_id = -99 -- To LE "All Other"
1374 AND accts.type = 'P'
1375 AND accts.default_flag = 'Y'
1376 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
1377 TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1378 WHERE inter_int.pay_acct IS NULL;
1379
1380
1381 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_INTER_INT_GT');
1382
1383 UPDATE fun_bal_inter_int_gt inter_int
1384 SET rec_acct = -1
1385 WHERE rec_acct IS NULL AND
1386 EXISTS (SELECT 'Receivables Accounts exist but not defaulted'
1387 FROM fun_inter_accounts accts
1388 WHERE inter_int.ledger_id = accts.ledger_id
1389 AND accts.type = 'R'
1390 AND DECODE(inter_int.intercompany_mode,
1391 1, DECODE(le_id,
1392 driving_cr_le_id, DECODE(inter_int.type, 'C', driving_cr_le_id,
1393 NULL),
1394 DECODE(inter_int.type, 'C', driving_dr_le_id,
1395 NULL)),
1396 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1397 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1398 4, DECODE(inter_int.type, 'C', le_id, NULL),
1399 NULL) = accts.from_le_id
1400 AND (DECODE(inter_int.intercompany_mode,
1401 1, DECODE(le_id,
1402 driving_cr_le_id, DECODE(inter_int.type, 'C', driving_dr_le_id,
1403 NULL),
1404 DECODE(inter_int.type, 'C', driving_cr_le_id,
1405 NULL)),
1406 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1407 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1408 NULL) = accts.to_le_id
1409 OR
1410 accts.to_le_id = -99));
1411
1412 UPDATE fun_bal_inter_int_gt inter_int
1413 SET pay_acct = -1
1414 WHERE pay_acct IS NULL AND
1415 EXISTS (SELECT 'Payables Accounts exist but not defaulted'
1416 FROM fun_inter_accounts accts
1417 WHERE inter_int.ledger_id = accts.ledger_id
1418 AND accts.type = 'P'
1419 AND DECODE(inter_int.intercompany_mode,
1420 1, DECODE(le_id,
1421 driving_dr_le_id, DECODE(inter_int.type, 'D', driving_dr_le_id,
1422 NULL),
1423 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
1424 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
1425 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
1426 4, DECODE(inter_int.type, 'C', le_id, NULL),
1427 NULL) = accts.from_le_id
1428 AND (DECODE(inter_int.intercompany_mode,
1429 1, DECODE(le_id,
1430 driving_dr_le_id, DECODE(inter_int.type, 'D', driving_cr_le_id,
1431 NULL),
1432 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
1433 2, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
1434 3, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
1435 NULL) = accts.to_le_id
1436 OR
1437 accts.to_le_id = -99));
1438
1439 IF g_debug = FND_API.G_TRUE THEN
1440 OPEN l_inter_int_cursor;
1441 FETCH l_inter_int_cursor BULK COLLECT INTO l_inter_int_tab;
1442 l_inter_int_count := l_inter_int_cursor%ROWCOUNT;
1443 CLOSE l_inter_int_cursor;
1444 ins_t_tables_inter_2_auto(l_inter_int_tab, l_inter_int_count);
1445 END IF;
1446
1447
1448 -- Insert errors into FUN_BAL_ERRORS_GT
1449 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
1450 acct_type, ccid_concat_display,
1451 dr_bsv, cr_bsv)
1452 SELECT DISTINCT DECODE(inter_int.rec_acct, NULL, 'FUN_INTER_REC_NOT_ASSIGNED',
1453 -1, 'FUN_INTER_REC_NO_DEFAULT',
1454 'FUN_INTER_REC_NOT_VALID'),
1455 inter_int.group_id,
1456 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
1457 2, inter_int.le_id,
1458 3, inter_int.driving_cr_le_id,
1459 4, inter_int.le_id),
1460 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
1461 2, inter_int.driving_dr_le_id,
1462 3, inter_int.le_id,
1463 4, NULL),
1464 DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), 'R',
1465 get_ccid_concat_disp(DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), hdrs.chart_of_accounts_id,
1466 NULL, NULL, NULL, NULL),
1467 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
1468 FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
1469 WHERE inter_int.group_id = hdrs.group_id AND
1470 ((inter_int.intercompany_mode = 1 AND
1471 inter_int.type = 'C')
1472 OR
1473 (inter_int.intercompany_mode = 2 AND
1474 inter_int.le_id <> inter_int.driving_dr_le_id)
1475 OR
1476 (inter_int.intercompany_mode = 3 AND
1477 inter_int.le_id <> inter_int.driving_cr_le_id)
1478 OR
1479 (inter_int.intercompany_mode = 4 AND
1480 inter_int.type = 'C'))
1481 AND (inter_int.rec_acct IS NULL
1482 OR
1483 inter_int.rec_acct = -1
1484 OR
1485 (inter_int.rec_acct IS NOT NULL AND
1486 NOT EXISTS (SELECT 'Receivables account not valid'
1487 FROM gl_code_combinations cc
1488 WHERE inter_int.rec_acct = cc.code_combination_id
1489 AND cc.detail_posting_allowed_flag = 'Y'
1490 AND cc.enabled_flag = 'Y'
1491 AND cc.summary_flag = 'N'
1492 AND cc.template_id IS NULL
1493 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
1494 AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
1495
1496 -- Insert errors into FUN_BAL_ERRORS_GT
1497 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
1498 acct_type, ccid_concat_display,
1499 dr_bsv, cr_bsv)
1500 SELECT DISTINCT DECODE(inter_int.pay_acct, NULL, 'FUN_INTER_PAY_NOT_ASSIGNED',
1501 -1, 'FUN_INTER_PAY_NO_DEFAULT',
1502 'FUN_INTER_PAY_NOT_VALID'),
1503 inter_int.group_id,
1504 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
1505 2, inter_int.driving_dr_le_id,
1506 3, inter_int.le_id,
1507 4, inter_int.le_id),
1508 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
1509 2, inter_int.le_id,
1510 3, inter_int.driving_cr_le_id,
1511 4, NULL),
1512 DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), 'P',
1513 get_ccid_concat_disp(DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), hdrs.chart_of_accounts_id,
1514 NULL, NULL, NULL, NULL),
1515 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
1516 FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
1517 WHERE inter_int.group_id = hdrs.group_id AND
1518 ((inter_int.intercompany_mode = 1 AND
1519 inter_int.type = 'D')
1520 OR
1521 (inter_int.intercompany_mode = 2 AND
1522 inter_int.le_id <> inter_int.driving_dr_le_id)
1523 OR
1524 (inter_int.intercompany_mode = 3 AND
1525 inter_int.le_id <> inter_int.driving_cr_le_id)
1526 OR
1527 (inter_int.intercompany_mode = 4 AND
1528 inter_int.type = 'D'))
1529 AND (inter_int.pay_acct IS NULL
1530 OR
1531 inter_int.pay_acct = -1
1532 OR
1533 (inter_int.pay_acct IS NOT NULL AND
1534 NOT EXISTS (SELECT 'Payables account not valid'
1535 FROM gl_code_combinations cc
1536 WHERE inter_int.pay_acct = cc.code_combination_id
1537 AND cc.detail_posting_allowed_flag = 'Y'
1538 AND cc.enabled_flag = 'Y'
1539 AND cc.summary_flag = 'N'
1540 AND cc.template_id IS NULL
1541 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
1542 AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
1543
1544
1545 UPDATE fun_bal_headers_gt headers
1546 SET status = 'ERROR'
1547 WHERE EXISTS (SELECT 'Errors for Rec and Pay Accts'
1548 FROM FUN_BAL_ERRORS_GT errors
1549 WHERE headers.group_id = errors.group_id
1550 AND error_code IN ('FUN_INTER_PAY_NOT_ASSIGNED',
1551 'FUN_INTER_REC_NOT_ASSIGNED',
1552 'FUN_INTER_PAY_NO_DEFAULT',
1553 'FUN_INTER_REC_NO_DEFAULT',
1554 'FUN_INTER_PAY_NOT_VALID',
1555 'FUN_INTER_REC_NOT_VALID'))
1556 AND headers.status = 'OK';
1557
1558 DELETE FROM fun_bal_inter_int_gt inter_int
1559 WHERE EXISTS (SELECT group_id
1560 FROM fun_bal_headers_gt headers
1561 WHERE headers.status = 'ERROR'
1562 AND inter_int.group_id = headers.group_id);
1563
1564 --Enhancement 7520196 Start
1565 -- Update the Payable and receivable BSV with the minimum unbalanced bsv
1566 -- for each of the transacting Legal Entity.
1567
1568 Update fun_bal_inter_int_gt bal_inter_int
1569 set Rec_BSV = (select min_bal_seg_val from (
1570 select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
1571 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
1572 fun_bal_headers_gt hdrs
1573 WHERE hdrs.group_id = lines.group_id
1574 AND lines.group_id = le_bsv_map.group_id
1575 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
1576 AND hdrs.intercompany_mode IN (1,2,3)
1577 AND hdrs.status = 'OK'
1578 GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
1579 hdrs.intercompany_mode, le_bsv_map.le_id, lines.entered_currency_code, lines.exchange_date,
1580 lines.exchange_rate, lines.exchange_rate_type,
1581 hdrs.bal_seg_column_name, hdrs.intercompany_column_number
1582 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
1583 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
1584 AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
1585 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
1586 where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_CR_LE_ID,
1587 2, (decode (bal_inter_int.type, 'C', bal_inter_int.LE_ID, bal_inter_int.DRIVING_DR_LE_ID)),
1588 3, (decode (bal_inter_int.type, 'D', bal_inter_int.DRIVING_CR_LE_ID, bal_inter_int.LE_ID)), NULL)
1589 and min_bsv.group_id = bal_inter_int.group_id
1590 and bal_inter_int.status = 'OK');
1591
1592 Update fun_bal_inter_int_gt bal_inter_int
1593 set Pay_BSV = (select min_bal_seg_val from (
1594 select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
1595 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
1596 fun_bal_headers_gt hdrs
1597 WHERE hdrs.group_id = lines.group_id
1598 AND lines.group_id = le_bsv_map.group_id
1599 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
1600 AND hdrs.intercompany_mode IN (1,2,3)
1601 AND hdrs.status = 'OK'
1602 GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
1603 hdrs.intercompany_mode, le_bsv_map.le_id, lines.entered_currency_code, lines.exchange_date,
1604 lines.exchange_rate, lines.exchange_rate_type,
1605 hdrs.bal_seg_column_name, hdrs.intercompany_column_number
1606 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
1607 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
1608 AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
1609 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
1610 where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_DR_LE_ID,
1611 2, (decode (bal_inter_int.type, 'C', bal_inter_int.DRIVING_DR_LE_ID, bal_inter_int.LE_ID)),
1612 3, (decode (bal_inter_int.type, 'D', bal_inter_int.LE_ID, bal_inter_int.DRIVING_CR_LE_ID)), NULL)
1613 and min_bsv.group_id = bal_inter_int.group_id
1614 and bal_inter_int.status = 'OK');
1615
1616 -- Switch the Intercompany and Balancing segment value for the
1617 -- Payables and Receivables accounts. And update the table with
1618 -- the new account numbers.
1619 Update fun_bal_inter_int_gt bal_inter_int
1620 Set (REC_ACCT, PAY_ACCT) =
1621 (select get_ccid (bal_inter_int.REC_ACCT,
1622 hdrs.CHART_OF_ACCOUNTS_ID,
1623 bal_inter_int.REC_BSV,
1624 bal_inter_int.PAY_BSV,
1625 hdrs.BAL_SEG_COLUMN_NUMBER,
1626 hdrs.INTERCOMPANY_COLUMN_NUMBER,
1627 bal_inter_int.GL_DATE
1628 ),
1629 get_ccid (bal_inter_int.PAY_ACCT,
1630 hdrs.CHART_OF_ACCOUNTS_ID,
1631 bal_inter_int.PAY_BSV,
1632 bal_inter_int.REC_BSV,
1633 hdrs.BAL_SEG_COLUMN_NUMBER,
1634 hdrs.INTERCOMPANY_COLUMN_NUMBER,
1635 bal_inter_int.GL_DATE
1636 )
1637 from fun_bal_headers_gt hdrs
1638 where bal_inter_int.group_id = hdrs.group_id)
1639 where bal_inter_int.intercompany_mode in (1, 2, 3)
1640 and bal_inter_int.status = 'OK';
1641
1642 -- Enhancement 7520196 End
1643
1644 /* Changes for Bug # 8212023 Start */
1645 -- Insert errors into FUN_BAL_ERRORS_GT
1646 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
1647 acct_type, ccid_concat_display,
1648 dr_bsv, cr_bsv)
1649 SELECT DISTINCT DECODE(inter_int.rec_acct, NULL, 'FUN_INTER_REC_NOT_ASSIGNED',
1650 -1, 'FUN_INTER_REC_NO_DEFAULT',
1651 'FUN_INTER_REC_NOT_VALID'),
1652 inter_int.group_id,
1653 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
1654 2, inter_int.le_id,
1655 3, inter_int.driving_cr_le_id,
1656 4, inter_int.le_id),
1657 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
1658 2, inter_int.driving_dr_le_id,
1659 3, inter_int.le_id,
1660 4, NULL),
1661 DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), 'R',
1662 get_ccid_concat_disp(DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), hdrs.chart_of_accounts_id,
1663 NULL, NULL, NULL, NULL),
1664 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
1665 FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
1666 WHERE inter_int.group_id = hdrs.group_id AND
1667 ((inter_int.intercompany_mode = 1 AND
1668 inter_int.type = 'C')
1669 OR
1670 (inter_int.intercompany_mode = 2 AND
1671 inter_int.le_id <> inter_int.driving_dr_le_id)
1672 OR
1673 (inter_int.intercompany_mode = 3 AND
1674 inter_int.le_id <> inter_int.driving_cr_le_id)
1675 OR
1676 (inter_int.intercompany_mode = 4 AND
1677 inter_int.type = 'C'))
1678 AND (inter_int.rec_acct IS NULL
1679 OR
1680 inter_int.rec_acct = -1
1681 OR
1682 (inter_int.rec_acct IS NOT NULL AND
1683 NOT EXISTS (SELECT 'Receivables account not valid'
1684 FROM gl_code_combinations cc
1685 WHERE inter_int.rec_acct = cc.code_combination_id
1686 AND cc.detail_posting_allowed_flag = 'Y'
1687 AND cc.enabled_flag = 'Y'
1688 AND cc.summary_flag = 'N'
1689 AND cc.template_id IS NULL
1690 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
1691 AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
1692
1693 -- Insert errors into FUN_BAL_ERRORS_GT
1694 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
1695 acct_type, ccid_concat_display,
1696 dr_bsv, cr_bsv)
1697 SELECT DISTINCT DECODE(inter_int.pay_acct, NULL, 'FUN_INTER_PAY_NOT_ASSIGNED',
1698 -1, 'FUN_INTER_PAY_NO_DEFAULT',
1699 'FUN_INTER_PAY_NOT_VALID'),
1700 inter_int.group_id,
1701 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
1702 2, inter_int.driving_dr_le_id,
1703 3, inter_int.le_id,
1704 4, inter_int.le_id),
1705 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
1706 2, inter_int.le_id,
1707 3, inter_int.driving_cr_le_id,
1708 4, NULL),
1709 DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), 'P',
1710 get_ccid_concat_disp(DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), hdrs.chart_of_accounts_id,
1711 NULL, NULL, NULL, NULL),
1712 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
1713 FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
1714 WHERE inter_int.group_id = hdrs.group_id AND
1715 ((inter_int.intercompany_mode = 1 AND
1716 inter_int.type = 'D')
1717 OR
1718 (inter_int.intercompany_mode = 2 AND
1719 inter_int.le_id <> inter_int.driving_dr_le_id)
1720 OR
1721 (inter_int.intercompany_mode = 3 AND
1722 inter_int.le_id <> inter_int.driving_cr_le_id)
1723 OR
1724 (inter_int.intercompany_mode = 4 AND
1725 inter_int.type = 'D'))
1726 AND (inter_int.pay_acct IS NULL
1727 OR
1728 inter_int.pay_acct = -1
1729 OR
1730 (inter_int.pay_acct IS NOT NULL AND
1731 NOT EXISTS (SELECT 'Payables account not valid'
1732 FROM gl_code_combinations cc
1733 WHERE inter_int.pay_acct = cc.code_combination_id
1734 AND cc.detail_posting_allowed_flag = 'Y'
1735 AND cc.enabled_flag = 'Y'
1736 AND cc.summary_flag = 'N'
1737 AND cc.template_id IS NULL
1738 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
1739 AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
1740
1741
1742 UPDATE fun_bal_headers_gt headers
1743 SET status = 'ERROR'
1744 WHERE EXISTS (SELECT 'Errors for Rec and Pay Accts'
1745 FROM FUN_BAL_ERRORS_GT errors
1746 WHERE headers.group_id = errors.group_id
1747 AND error_code IN ('FUN_INTER_PAY_NOT_ASSIGNED',
1748 'FUN_INTER_REC_NOT_ASSIGNED',
1749 'FUN_INTER_PAY_NO_DEFAULT',
1750 'FUN_INTER_REC_NO_DEFAULT',
1751 'FUN_INTER_PAY_NOT_VALID',
1752 'FUN_INTER_REC_NOT_VALID'))
1753 AND headers.status = 'OK';
1754
1755 DELETE FROM fun_bal_inter_int_gt inter_int
1756 WHERE EXISTS (SELECT group_id
1757 FROM fun_bal_headers_gt headers
1758 WHERE headers.status = 'ERROR'
1759 AND inter_int.group_id = headers.group_id);
1760
1761 /* Changes for Bug # 8212023 End */
1762
1763 -- Retrieve balancing segment value from the receivables and payables accounts
1764 update_inter_seg_val;
1765 /* 8200511 */
1766
1767 INSERT INTO FUN_INTER_ACCOUNTS_ADDL
1768 (FROM_LE_ID,
1769 LEDGER_ID,
1770 TO_LE_ID,
1771 CCID,
1772 TYPE,
1773 START_DATE,
1774 OBJECT_VERSION_NUMBER,
1775 CREATED_BY,
1776 CREATION_DATE,
1777 LAST_UPDATED_BY,
1778 LAST_UPDATE_DATE,
1779 LAST_UPDATE_LOGIN,
1780 TRANS_BSV,
1781 TP_BSV)
1782 SELECT DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
1783 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
1784 BAL_INTER_INT.DRIVING_DR_LE_ID)),
1785 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
1786 BAL_INTER_INT.LE_ID)),
1787 NULL),
1788 BAL_INTER_INT.LEDGER_ID,
1789 DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
1790 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
1791 BAL_INTER_INT.LE_ID)),
1792 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
1793 BAL_INTER_INT.DRIVING_CR_LE_ID)),
1794 NULL),
1795 BAL_INTER_INT.REC_ACCT,
1796 'R',
1797 SYSDATE,
1798 '1',
1799 FND_GLOBAL.USER_ID,
1800 SYSDATE,
1801 FND_GLOBAL.USER_ID,
1802 SYSDATE,
1803 fnd_global.login_id,
1804 BAL_INTER_INT.REC_BSV,
1805 BAL_INTER_INT.PAY_BSV
1806 FROM FUN_BAL_INTER_INT_GT BAL_INTER_INT
1807 WHERE BAL_INTER_INT.STATUS = 'OK'
1808 AND BAL_INTER_INT.REC_ACCT IS NOT NULL
1809 AND BAL_INTER_INT.PAY_BSV IS NOT NULL
1810 AND BAL_INTER_INT.REC_BSV IS NOT NULL
1811 AND NOT EXISTS(
1812 SELECT 'X'
1813 FROM FUN_INTER_ACCOUNTS_V ACCTV
1814 WHERE ACCTV.FROM_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
1815 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
1816 BAL_INTER_INT.DRIVING_DR_LE_ID)),
1817 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
1818 BAL_INTER_INT.LE_ID)),
1819 NULL)
1820 AND ACCTV.LEDGER_ID = BAL_INTER_INT.LEDGER_ID
1821 AND ACCTV.TO_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
1822 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
1823 BAL_INTER_INT.LE_ID)),
1824 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
1825 BAL_INTER_INT.DRIVING_CR_LE_ID)),
1826 NULL)
1827 AND ACCTV.CCID = BAL_INTER_INT.REC_ACCT
1828 AND ACCTV.TYPE = 'R'
1829 AND ACCTV.TRANS_BSV = BAL_INTER_INT.REC_BSV
1830 AND ACCTV.TP_BSV = BAL_INTER_INT.PAY_BSV
1831 );
1832
1833
1834
1835 INSERT INTO FUN_INTER_ACCOUNTS_ADDL
1836 (FROM_LE_ID,
1837 LEDGER_ID,
1838 TO_LE_ID,
1839 CCID,
1840 TYPE,
1841 START_DATE,
1842 OBJECT_VERSION_NUMBER,
1843 CREATED_BY,
1844 CREATION_DATE,
1845 LAST_UPDATED_BY,
1846 LAST_UPDATE_DATE,
1847 LAST_UPDATE_LOGIN,
1848 TRANS_BSV,
1849 TP_BSV)
1850 SELECT DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
1851 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
1852 BAL_INTER_INT.LE_ID)),
1853 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
1854 BAL_INTER_INT.DRIVING_CR_LE_ID)),
1855 NULL),
1856 BAL_INTER_INT.LEDGER_ID,
1857 DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
1858 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
1859 BAL_INTER_INT.DRIVING_DR_LE_ID)),
1860 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
1861 BAL_INTER_INT.LE_ID)),
1862 NULL),
1863 BAL_INTER_INT.PAY_ACCT,
1864 'P',
1865 SYSDATE,
1866 '1',
1867 FND_GLOBAL.USER_ID,
1868 SYSDATE,
1869 FND_GLOBAL.USER_ID,
1870 SYSDATE,
1871 fnd_global.login_id,
1872 BAL_INTER_INT.PAY_BSV,
1873 BAL_INTER_INT.REC_BSV
1874 FROM FUN_BAL_INTER_INT_GT BAL_INTER_INT
1875 WHERE BAL_INTER_INT.STATUS = 'OK'
1876 AND BAL_INTER_INT.PAY_ACCT IS NOT NULL
1877 AND BAL_INTER_INT.PAY_BSV IS NOT NULL
1878 AND BAL_INTER_INT.REC_BSV IS NOT NULL
1879 AND NOT EXISTS (
1880 SELECT 'X'
1881 FROM FUN_INTER_ACCOUNTS_V ACCTV
1882 WHERE ACCTV.FROM_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
1883 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
1884 BAL_INTER_INT.LE_ID)),
1885 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
1886 BAL_INTER_INT.DRIVING_CR_LE_ID)),
1887 NULL)
1888 AND ACCTV.LEDGER_ID = BAL_INTER_INT.LEDGER_ID
1889 AND ACCTV.TO_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
1890 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
1891 BAL_INTER_INT.DRIVING_DR_LE_ID)),
1892 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
1893 BAL_INTER_INT.LE_ID)),
1894 NULL)
1895 AND ACCTV.CCID = BAL_INTER_INT.PAY_ACCT
1896 AND ACCTV.TYPE = 'P'
1897 AND ACCTV.TRANS_BSV = BAL_INTER_INT.PAY_BSV
1898 AND ACCTV.TP_BSV = BAL_INTER_INT.REC_BSV
1899 );
1900
1901
1902 /* 8200511 */
1903
1904 -- Insert intercompany balancing lines into the FUN_BAL_LINES_GT table. These resulting lines
1905 -- are not yet inserted into the results table as intracompany balancing might need to be performed
1906 -- for these lines also.
1907 INSERT INTO fun_bal_lines_gt lines (group_id, bal_seg_val, entered_amt_dr,
1908 entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
1909 accounted_amt_dr, accounted_amt_cr, ccid, generated)
1910 SELECT sum_lines.group_id,
1911 DECODE(gen.value, 'D', sum_lines.rec_bsv,
1912 'C', sum_lines.pay_bsv,
1913 NULL),
1914 DECODE(gen.value, 'D', DECODE(sum_lines.type, 'C', sum_lines.entered_amt_cr,
1915 'D', sum_lines.entered_amt_dr),
1916 NULL),
1917 DECODE(gen.value, 'C', DECODE(sum_lines.type, 'C', sum_lines.entered_amt_cr,
1918 'D', sum_lines.entered_amt_dr),
1919 NULL),
1920 sum_lines.entered_currency_code,
1921 sum_lines.exchange_date, sum_lines.exchange_rate, sum_lines.exchange_rate_type,
1922 DECODE(gen.value, 'D', DECODE(sum_lines.type, 'C', sum_lines.accounted_amt_cr,
1923 'D', sum_lines.accounted_amt_dr),
1924 NULL),
1925 DECODE(gen.value, 'C', DECODE(sum_lines.type, 'C', sum_lines.accounted_amt_cr,
1926 'D', sum_lines.accounted_amt_dr),
1927 NULL),
1928 DECODE(gen.value, 'C', sum_lines.pay_acct, 'D', sum_lines.rec_acct, NULL),
1929 'Y'
1930 FROM fun_bal_inter_int_gt sum_lines, fun_bal_generate_lines gen
1931 WHERE gen.value = DECODE(sum_lines.intercompany_mode,
1932 1, DECODE(sum_lines.type, gen.value, 'X', gen.value),
1933 2, DECODE(sum_lines.le_id, sum_lines.driving_dr_le_id, 'X', gen.value),
1934 3, DECODE(sum_lines.le_id, sum_lines.driving_cr_le_id, 'X', gen.value),
1935 4, DECODE(sum_lines.type, gen.value, 'X', gen.value));
1936 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1937 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.end', 'end');
1938 END IF;
1939
1940 RETURN FND_API.G_RET_STS_SUCCESS;
1941 END do_inter_bal;
1942
1943 FUNCTION do_intra_bal RETURN VARCHAR2 IS
1944 l_le_bsv_map_tab intra_le_bsv_map_tab_type;
1945 l_intra_int_tab intra_int_tab_type;
1946 l_le_bsv_map_count NUMBER;
1947 l_intra_int_count NUMBER;
1948 CURSOR l_le_bsv_map_cursor IS
1949 SELECT * FROM fun_bal_le_bsv_map_gt;
1950 CURSOR l_intra_int_cursor IS
1951 SELECT * FROM fun_bal_intra_int_gt;
1952 BEGIN
1953 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1954 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.begin', 'begin');
1955 END IF;
1956
1957
1958 -- Delete all records from FUN_BAL_LE_BSV_MAP_GT for reuse.
1959 DELETE FROM fun_bal_le_bsv_map_gt;
1960
1961 -- Insert records into FUN_BAL_LE_BSV_MAP_GT
1962 INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date,
1963 je_source_name, je_category_name, clearing_bsv,
1964 chart_of_accounts_id, bal_seg_column_number,intercompany_column_number)
1965 SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date,
1966 hdrs.je_source_name, hdrs.je_category_name, hdrs.clearing_bsv,
1967 hdrs.chart_of_accounts_id, hdrs.bal_seg_column_number, hdrs.intercompany_column_number
1968 FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines
1969 WHERE hdrs.group_id = lines.group_id
1970 AND hdrs.status = 'OK';
1971
1972
1973 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1974 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.insert_le_bsv_map.finish', 'finish');
1975 END IF;
1976
1977
1978 -- Update Legal entity for each ledger, BSV combination. Legal entity can only be either null or has a specific value
1979 UPDATE fun_bal_le_bsv_map_gt bsv_le_map
1980 SET le_id =
1981 NVL((SELECT vals.legal_entity_id
1982 FROM gl_ledger_le_bsv_specific_v vals
1983 WHERE bsv_le_map.bal_seg_val = vals.segment_value
1984 AND (TRUNC(bsv_le_map.gl_date) BETWEEN TRUNC(NVL(vals.start_date, bsv_le_map.gl_date)) AND
1985 TRUNC(NVL(vals.end_date, bsv_le_map.gl_date)))
1986 AND bsv_le_map.ledger_id = vals.ledger_id
1987 ), -99);
1988
1989
1990 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
1991 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_le.finish', 'finish');
1992 END IF;
1993
1994 -- Determine intracompany mode, driving_dr_bsv and driving_cr_bsv
1995 -- improve performance for not updating the lines of LE that uses clearing company
1996 UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
1997 SET (driving_dr_bsv, intracompany_mode) =
1998 (SELECT MIN(le_bsv_map.bal_seg_val), SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val)))
1999 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
2000 WHERE le_bsv_map.group_id = lines.group_id
2001 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
2002 AND le_bsv_map.group_id = le_bsv_map_upd.group_id
2003 AND le_bsv_map.le_id = le_bsv_map_upd.le_id
2004 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
2005 HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) >
2006 SUM(NVL(lines.accounted_amt_cr, 0)))
2007 OR
2008 ((SUM(NVL(lines.accounted_amt_dr, 0)) =
2009 SUM(NVL(lines.accounted_amt_cr, 0))) AND
2010 (SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) >
2011 SUM(DECODE(lines.exchange_rate, NULL,NVL(lines.entered_amt_cr,0), 0)))))
2012 WHERE le_bsv_map_upd.intracompany_mode IS NULL; -- OR le_bsv_map_upd.intracompany_mode <> 5;
2013
2014
2015 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2016 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_intracompany_mode_1.finish', 'finish');
2017 END IF;
2018
2019
2020 --Delete records that has intracompany mode NULL
2021 DELETE FROM fun_bal_le_bsv_map_gt
2022 WHERE intracompany_mode IS NULL;
2023
2024 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
2025 WHERE EXISTS (SELECT 'BSV already balanced'
2026 FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
2027 WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
2028 AND le_bsv_map_del.le_id = le_bsv_map.le_id
2029 AND le_bsv_map_del.bal_seg_val = le_bsv_map.bal_seg_val
2030 AND le_bsv_map.group_id = lines.group_id
2031 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
2032 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
2033 HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) =
2034 SUM(NVL(lines.accounted_amt_cr, 0)))
2035 AND
2036 (SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) =
2037 SUM(DECODE(lines.exchange_rate, NULL,NVL(lines.entered_amt_cr,0),0))));
2038
2039 UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
2040 SET (driving_cr_bsv, intracompany_mode) =
2041 (SELECT MIN(le_bsv_map.bal_seg_val), DECODE(SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val))),
2042 1, DECODE(le_bsv_map_upd.intracompany_mode, 1, 1, 3),
2043 DECODE(le_bsv_map_upd.intracompany_mode, 1, 2, 4))
2044 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
2045 WHERE le_bsv_map.group_id = lines.group_id
2046 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
2047 AND le_bsv_map.group_id = le_bsv_map_upd.group_id
2048 AND le_bsv_map.le_id = le_bsv_map_upd.le_id
2049 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
2050 --HAVING (le_bsv_map.clearing_option = '1D' OR le_bsv_map.clearing_option = '4M')
2051 -- No need for this having clause as it has brought to the higher level to check
2052 HAVING (SUM(NVL(lines.accounted_amt_cr, 0)) >
2053 SUM(NVL(lines.accounted_amt_dr, 0)))
2054 OR
2055 ((SUM(NVL(lines.accounted_amt_dr, 0)) =
2056 SUM(NVL(lines.accounted_amt_cr, 0))) AND
2057 (SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) >
2058 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)))))
2059 WHERE le_bsv_map_upd.intracompany_mode IS NOT NULL;
2060 -- AND le_bsv_map_upd.intracompany_mode <> 5;
2061
2062 -- Don't balance for journals that does not have a credit or debit side
2063 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
2064 WHERE le_bsv_map.driving_dr_bsv IS NULL OR le_bsv_map.driving_cr_bsv IS NULL;
2065
2066
2067 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2068 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_intracompany_mode.finish', 'finish');
2069 END IF;
2070
2071
2072 -- Update intra_template_id in FUN_BAL_LE_BSV_MAP
2073 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2074 SET template_id =
2075 (SELECT opts.template_id
2076 FROM fun_balance_options opts
2077 WHERE le_bsv_map.ledger_id = opts.ledger_id
2078 AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
2079 AND le_bsv_map.je_source_name = opts.je_source_name
2080 AND le_bsv_map.je_category_name = opts.je_category_name
2081 AND opts.status_flag = 'Y')
2082 WHERE le_bsv_map.template_id IS NULL;
2083
2084 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2085 SET template_id =
2086 (SELECT opts.template_id
2087 FROM fun_balance_options opts
2088 WHERE le_bsv_map.ledger_id = opts.ledger_id
2089 AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
2090 AND le_bsv_map.je_source_name = opts.je_source_name
2091 AND opts.je_category_name = 'Other'
2092 AND opts.status_flag = 'Y')
2093 WHERE le_bsv_map.template_id IS NULL;
2094
2095 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2096 SET template_id =
2097 (SELECT opts.template_id
2098 FROM fun_balance_options opts
2099 WHERE le_bsv_map.ledger_id = opts.ledger_id
2100 AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
2101 AND opts.je_source_name = 'Other'
2102 AND le_bsv_map.je_category_name = opts.je_category_name
2103 AND opts.status_flag = 'Y')
2104 WHERE le_bsv_map.template_id IS NULL;
2105
2106 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2107 SET template_id =
2108 (SELECT opts.template_id
2109 FROM fun_balance_options opts
2110 WHERE le_bsv_map.ledger_id = opts.ledger_id
2111 AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
2112 -- No error here if null, since both le_id is -99 if no legal entity is specified
2113 AND opts.je_source_name = 'Other'
2114 AND opts.je_category_name = 'Other'
2115 AND opts.status_flag = 'Y')
2116 WHERE le_bsv_map.template_id IS NULL;
2117
2118 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, dr_bsv, cr_bsv)
2119 SELECT 'FUN_INTRA_RULE_NOT_ASSIGNED',
2120 le_bsv_map.group_id, le_bsv_map.template_id,
2121 DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
2122 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
2123 FROM fun_bal_le_bsv_map_gt le_bsv_map
2124 WHERE le_bsv_map.template_id IS NULL;
2125
2126 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2127 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_template.finish', 'finish');
2128 END IF;
2129
2130
2131 -- Logic to update balancing segment values are shown as follows:
2132 -- 1. Summary mode, No clearing
2133 -- Require the debit, credit accounts from one template only
2134 -- 2. Summary mode, clearing
2135 -- Require the debit, credit accounts from both templates
2136 -- 3. Detail mode, No clearing
2137 -- Require the debit, credit accounts from both templates
2138 -- 4. Detail mode, clearing
2139 -- Require the debit, credit accounts from both templates
2140 -- Retrieve the debit account ccid and credit account ccid with bal_seg_val in debit side
2141
2142
2143 -- Update balance_by, clearing_option, clearing_bsv in FUN_BAL_LE_BSV_MAP
2144
2145 -- Balancing API Changes, Start, Feb 2005
2146 -- Modified the following for the introduction of 'Enter Manually on Journal' option
2147 -- as a valid value for many_to_many_option (2E). This used to first be a value
2148 -- for the clearing_option
2149
2150 -- clearing_option many_to_many_option Jrnl Type CBSV From CBSV Reqd
2151 ---------------------------------------------------------------------------------
2152 -- 1A 2E Any journal Y
2153 -- 1A 1C Any journal/options Y
2154 ---------------------------------------------------------------------------------
2155 -- 3M 2E M:M (4) journal Y
2156 -- 3M 2E 1,2,3 journal N
2157 -- 3M 1C M:M (4) journal/options Y
2158 -- 3M 1C 1,2,3 journal N
2159 -- 3M 2D M:M (4) None N
2160 -- 3M 2D 1,2,3 journal N
2161 ---------------------------------------------------------------------------------
2162 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2163 SET (balance_by, clearing_option, clearing_bsv, many_to_many_option) =
2164 (SELECT opts.balance_by_flag, opts.clearing_option,
2165 DECODE (opts.clearing_option,
2166 '1A', DECODE (opts.many_to_many_option,
2167 '2E', le_bsv_map.clearing_bsv,
2168 '1C', Nvl(le_bsv_map.clearing_bsv,opts.clearing_bsv)),
2169 '3M', DECODE (opts.many_to_many_option,
2170 '2E', le_bsv_map.clearing_bsv,
2171 '1C', DECODE (le_bsv_map.intracompany_mode,
2172 4, Nvl(le_bsv_map.clearing_bsv,opts.clearing_bsv),
2173 le_bsv_map.clearing_bsv),
2174 '2D', DECODE (le_bsv_map.intracompany_mode,
2175 4, NULL,
2176 le_bsv_map.clearing_bsv)),
2177 NULL),
2178 opts.many_to_many_option
2179 FROM fun_balance_options opts
2180 WHERE le_bsv_map.template_id = opts.template_id
2181 AND opts.status_flag = 'Y');
2182
2183
2184 -- Note: A new intracompany mode 5 is introduced. Intracompany mode is 5 if clearing BSV is used
2185 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2186 SET intracompany_mode = 5
2187 WHERE le_bsv_map.clearing_bsv IS NOT NULL;
2188
2189 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LE_BSV_MAP_GT');
2190
2191 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, dr_bsv, cr_bsv)
2192 SELECT 'FUN_INTRA_NO_CLEARING_BSV',
2193 le_bsv_map.group_id, le_bsv_map.template_id,
2194 DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
2195 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
2196 FROM fun_bal_le_bsv_map_gt le_bsv_map
2197 WHERE le_bsv_map.clearing_bsv IS NULL
2198 AND ((le_bsv_map.clearing_option = '1A') OR
2199 (le_bsv_map.clearing_option = '3M' AND
2200 le_bsv_map.intracompany_mode = 4 AND
2201 le_bsv_map.many_to_many_option IN ('2E', '1C')));
2202
2203 -- Check if the clearing BSV is valid (Bug 3345457)
2204 -- Perform this validation only if the Ledger BSV mapping is set to
2205 -- 'Specific' ('I')
2206 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, clearing_bsv,
2207 dr_bsv, cr_bsv)
2208 SELECT 'FUN_INTRA_CLEAR_BSV_INVALID',
2209 le_bsv_map.group_id, le_bsv_map.template_id,
2210 DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
2211 le_bsv_map.clearing_bsv,
2212 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
2213 FROM fun_bal_le_bsv_map_gt le_bsv_map,
2214 gl_ledgers ledger
2215 WHERE le_bsv_map.clearing_bsv IS NOT NULL
2216 AND ledger.ledger_id = le_bsv_map.ledger_id
2217 AND ledger.bal_seg_value_option_code = 'I'
2218 AND NOT EXISTS
2219 (SELECT 'X'
2220 FROM gl_ledger_le_bsv_specific_v gl_seg
2221 WHERE gl_seg.ledger_id = le_bsv_map.ledger_id
2222 AND gl_seg.segment_value = le_bsv_map.clearing_bsv
2223 AND TRUNC(le_bsv_map.gl_date) BETWEEN TRUNC(NVL(gl_seg.start_date, le_bsv_map.gl_date))
2224 AND TRUNC(NVL(gl_seg.end_date, le_bsv_map.gl_date)));
2225
2226 -- Balancing API Changes, End , Feb 2005
2227
2228 UPDATE fun_bal_headers_gt headers
2229 SET STATUS = 'ERROR'
2230 WHERE EXISTS (SELECT 'Errors for no template or no clearing bsv or clearing bsv invalid'
2231 FROM FUN_BAL_ERRORS_GT errors
2232 WHERE headers.group_id = errors.group_id
2233 AND error_code IN ('FUN_INTRA_RULE_NOT_ASSIGNED',
2234 'FUN_INTRA_NO_CLEARING_BSV',
2235 'FUN_INTRA_CLEAR_BSV_INVALID'))
2236 AND headers.status = 'OK';
2237
2238 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
2239 WHERE EXISTS (SELECT group_id
2240 FROM fun_bal_headers_gt headers
2241 WHERE headers.status = 'ERROR'
2242 AND le_bsv_map.group_id = headers.group_id);
2243
2244 -- Update ccid for each DB BSV and CR BSV
2245 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2246 SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
2247 (SELECT dr_ccid, cr_ccid, 'Y', 'Y'
2248 FROM fun_balance_accounts accts
2249 WHERE le_bsv_map.template_id = accts.template_id
2250 AND ((le_bsv_map.intracompany_mode = 5
2251 AND le_bsv_map.bal_seg_val = accts.dr_bsv
2252 AND le_bsv_map.clearing_bsv = accts.cr_bsv)
2253 OR (le_bsv_map.intracompany_mode = 1
2254 AND le_bsv_map.bal_seg_val = accts.dr_bsv
2255 AND DECODE(le_bsv_map.bal_seg_val,
2256 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
2257 le_bsv_map.driving_dr_bsv) = accts.cr_bsv)
2258 OR (le_bsv_map.intracompany_mode = 2
2259 AND le_bsv_map.bal_seg_val = accts.dr_bsv
2260 AND le_bsv_map.driving_dr_bsv = accts.cr_bsv)
2261 OR (le_bsv_map.intracompany_mode = 3
2262 AND le_bsv_map.bal_seg_val = accts.dr_bsv
2263 AND le_bsv_map.driving_cr_bsv = accts.cr_bsv)));
2264
2265
2266 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2267 SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
2268 (SELECT dr_ccid, cr_ccid, DECODE(le_bsv_map.intercompany_column_number,
2269 NULL, 'Y', 'N'), 'N'
2270 FROM fun_balance_accounts accts
2271 WHERE le_bsv_map.template_id = accts.template_id
2272 AND ((le_bsv_map.intracompany_mode = 5
2273 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
2274 AND le_bsv_map.bal_seg_val = accts.dr_bsv)
2275 OR (le_bsv_map.intracompany_mode IN (1,2,3)
2276 AND le_bsv_map.bal_seg_val = accts.dr_bsv
2277 AND 'OTHER1234567890123456789012345' = accts.cr_bsv)))
2278 WHERE dr_cr_debit_ccid IS NULL; --OR dr_cr_credit_ccid IS NULL; No need to check both
2279
2280 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2281 SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
2282 (SELECT dr_ccid, cr_ccid, 'N', DECODE(le_bsv_map.intercompany_column_number,
2283 NULL, 'Y', 'N')
2284 FROM fun_balance_accounts accts
2285 WHERE le_bsv_map.template_id = accts.template_id
2286 AND ((le_bsv_map.intracompany_mode = 5
2287 AND le_bsv_map.clearing_bsv = accts.cr_bsv
2288 AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
2289 OR (le_bsv_map.intracompany_mode = 1
2290 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
2291 AND DECODE(le_bsv_map.bal_seg_val,
2292 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
2293 le_bsv_map.driving_dr_bsv) = accts.cr_bsv)
2294 OR (le_bsv_map.intracompany_mode = 2
2295 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
2296 AND le_bsv_map.driving_dr_bsv = accts.cr_bsv)
2297 OR (le_bsv_map.intracompany_mode = 3
2298 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
2299 AND le_bsv_map.driving_cr_bsv = accts.cr_bsv)))
2300 WHERE dr_cr_debit_ccid IS NULL;
2301
2302 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2303 SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
2304 (SELECT dr_ccid, cr_ccid, 'N', 'N'
2305 FROM fun_balance_accounts accts
2306 WHERE le_bsv_map.template_id = accts.template_id
2307 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
2308 AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
2309 WHERE dr_cr_debit_ccid IS NULL ;
2310
2311 -- Upating cr_dr_debit_ccid, cr_dr_credit_ccid
2312 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2313 SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
2314 (SELECT dr_ccid, cr_ccid, 'Y', 'Y'
2315 FROM fun_balance_accounts accts
2316 WHERE le_bsv_map.template_id = accts.template_id
2317 AND ((le_bsv_map.intracompany_mode = 5
2318 AND le_bsv_map.bal_seg_val = accts.cr_bsv
2319 AND le_bsv_map.clearing_bsv = accts.dr_bsv)
2320 OR (le_bsv_map.intracompany_mode = 1
2321 AND le_bsv_map.bal_seg_val = accts.cr_bsv
2322 AND DECODE(le_bsv_map.bal_seg_val,
2323 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
2324 le_bsv_map.driving_dr_bsv) = accts.dr_bsv)
2325 OR (le_bsv_map.intracompany_mode = 2
2326 AND le_bsv_map.bal_seg_val = accts.cr_bsv
2327 AND le_bsv_map.driving_dr_bsv = accts.dr_bsv)
2328 OR (le_bsv_map.intracompany_mode = 3
2329 AND le_bsv_map.bal_seg_val = accts.cr_bsv
2330 AND le_bsv_map.driving_cr_bsv = accts.dr_bsv)));
2331
2332 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2333 SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
2334 (SELECT dr_ccid, cr_ccid, DECODE(le_bsv_map.intercompany_column_number,
2335 NULL, 'Y', 'N'), 'N'
2336 FROM fun_balance_accounts accts
2337 WHERE le_bsv_map.template_id = accts.template_id
2338 AND ((le_bsv_map.intracompany_mode = 5
2339 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
2340 AND le_bsv_map.clearing_bsv = accts.dr_bsv)
2341 OR (le_bsv_map.intracompany_mode = 1
2342 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
2343 AND DECODE(le_bsv_map.bal_seg_val,
2344 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
2345 le_bsv_map.driving_dr_bsv) = accts.dr_bsv)
2346 OR (le_bsv_map.intracompany_mode = 2
2347 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
2348 AND le_bsv_map.driving_dr_bsv = accts.dr_bsv)
2349 OR (le_bsv_map.intracompany_mode = 3
2350 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
2351 AND le_bsv_map.driving_cr_bsv = accts.dr_bsv)))
2352 WHERE cr_dr_debit_ccid IS NULL;
2353
2354 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2355 SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
2356 (SELECT dr_ccid, cr_ccid, 'N', DECODE(le_bsv_map.intercompany_column_number,
2357 NULL, 'Y', 'N')
2358 FROM fun_balance_accounts accts
2359 WHERE le_bsv_map.template_id = accts.template_id
2360 AND ((le_bsv_map.intracompany_mode = 5
2361 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
2362 AND le_bsv_map.bal_seg_val = accts.cr_bsv)
2363 OR (le_bsv_map.intracompany_mode IN (1,2,3)
2364 AND le_bsv_map.bal_seg_val = accts.cr_bsv
2365 AND 'OTHER1234567890123456789012345' = accts.dr_bsv)))
2366 WHERE cr_dr_debit_ccid IS NULL;
2367
2368 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2369 SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
2370 (SELECT dr_ccid, cr_ccid, 'N', 'N'
2371 FROM fun_balance_accounts accts
2372 WHERE le_bsv_map.template_id = accts.template_id
2373 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
2374 AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
2375 WHERE cr_dr_debit_ccid IS NULL;
2376
2377 /* Not done for checking ccid valid through gl_code_combinations directly
2378 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2379 SET (dr_cr_debit_ccid, dr_cr_debit_complete) =
2380 (SELECT code_combination_id, DECODE(ccid, NULL, 'N', 'Y')
2381 FROM gl_code_combinations cc1,
2382 gl_code_combinations cc2
2383 WHERE le_bsv_map.dr_cr_debit_ccid = cc1.code_combination_id
2384 AND cc1.segment1 = DECODE(le_bsv_map.bal_seg_column_no, 1, le_bsv_)
2385 WHERE dr_cr_debit_complete = 'N'
2386 */
2387
2388 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2389 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid.begin', 'begin');
2390 END IF;
2391
2392 -- Retrieve correct ccid by replacing balancing segment and intercompany segment
2393 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
2394 SET dr_cr_debit_ccid =
2395 DECODE(le_bsv_map.dr_cr_debit_complete, 'Y', le_bsv_map.dr_cr_debit_ccid,
2396 get_ccid(le_bsv_map.dr_cr_debit_ccid, le_bsv_map.chart_of_accounts_id, le_bsv_map.bal_seg_val,
2397 DECODE(le_bsv_map.intracompany_mode,
2398 1, DECODE(le_bsv_map.bal_seg_val, le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv, le_bsv_map.driving_dr_bsv),
2399 2, le_bsv_map.driving_dr_bsv,
2400 3, le_bsv_map.driving_cr_bsv,
2401 4, le_bsv_map.bal_seg_val,
2402 5, le_bsv_map.clearing_bsv,
2403 NULL),
2404 le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
2405 le_bsv_map.gl_date)),
2406 dr_cr_credit_ccid =
2407 DECODE(le_bsv_map.dr_cr_credit_complete, 'Y', le_bsv_map.dr_cr_credit_ccid,
2408 get_ccid(le_bsv_map.dr_cr_credit_ccid, le_bsv_map.chart_of_accounts_id,
2409 DECODE(le_bsv_map.intracompany_mode,
2410 1, DECODE(le_bsv_map.bal_seg_val, le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv, le_bsv_map.driving_dr_bsv),
2411 2, le_bsv_map.driving_dr_bsv,
2412 3, le_bsv_map.driving_cr_bsv,
2413 4, le_bsv_map.bal_seg_val,
2414 5, le_bsv_map.clearing_bsv,
2415 NULL),
2416 le_bsv_map.bal_seg_val,
2417 le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
2418 le_bsv_map.gl_date)),
2419 cr_dr_debit_ccid =
2420 DECODE(le_bsv_map.cr_dr_debit_complete, 'Y', le_bsv_map.cr_dr_debit_ccid,
2421 get_ccid(le_bsv_map.cr_dr_debit_ccid, le_bsv_map.chart_of_accounts_id,
2422 DECODE(le_bsv_map.intracompany_mode,
2423 1, DECODE(le_bsv_map.bal_seg_val, le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv, le_bsv_map.driving_dr_bsv),
2424 2, le_bsv_map.driving_dr_bsv,
2425 3, le_bsv_map.driving_cr_bsv,
2426 4, le_bsv_map.bal_seg_val,
2427 5, le_bsv_map.clearing_bsv,
2428 NULL),
2429 le_bsv_map.bal_seg_val,
2430 le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
2431 le_bsv_map.gl_date)),
2432 cr_dr_credit_ccid =
2433 DECODE(le_bsv_map.cr_dr_credit_complete, 'Y', le_bsv_map.cr_dr_credit_ccid,
2434 get_ccid(le_bsv_map.cr_dr_credit_ccid, le_bsv_map.chart_of_accounts_id, le_bsv_map.bal_seg_val,
2435 DECODE(le_bsv_map.intracompany_mode,
2436 1, DECODE(le_bsv_map.bal_seg_val, le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv, le_bsv_map.driving_dr_bsv),
2437 2, le_bsv_map.driving_dr_bsv,
2438 3, le_bsv_map.driving_cr_bsv,
2439 4, le_bsv_map.bal_seg_val,
2440 5, le_bsv_map.clearing_bsv,
2441 NULL),
2442 le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
2443 le_bsv_map.gl_date));
2444
2445 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2446 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid.end', 'end');
2447 END IF;
2448
2449 IF g_debug = FND_API.G_TRUE THEN
2450 OPEN l_le_bsv_map_cursor;
2451 FETCH l_le_bsv_map_cursor BULK COLLECT INTO l_le_bsv_map_tab;
2452 l_le_bsv_map_count := l_le_bsv_map_cursor%ROWCOUNT;
2453 CLOSE l_le_bsv_map_cursor;
2454 ins_t_tables_intra_1_auto(l_le_bsv_map_tab, l_le_bsv_map_count);
2455 END IF;
2456
2457 -- Insert into FUN_BAL_INTRA_INT_GT for lines that require Intracompany Balancing
2458 INSERT INTO fun_bal_intra_int_gt(group_id, gl_date, driving_dr_bsv, driving_cr_bsv,
2459 intracompany_mode, balance_by, clearing_option, bal_seg_val, le_id, template_id, entered_currency_code,
2460 exchange_date, exchange_rate, exchange_rate_type, accounted_amt_cr, accounted_amt_dr,
2461 entered_amt_cr, entered_amt_dr,
2462 dr_cr_debit_ccid, dr_cr_credit_ccid, cr_dr_debit_ccid, cr_dr_credit_ccid,
2463 type, clearing_bsv)
2464 (SELECT hdrs.group_id, hdrs.gl_date, le_bsv_map.driving_dr_bsv,
2465 le_bsv_map.driving_cr_bsv, le_bsv_map.intracompany_mode, le_bsv_map.balance_by,
2466 le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
2467 le_bsv_map.le_id, le_bsv_map.template_id, lines.entered_currency_code,
2468 lines.exchange_date, lines.exchange_rate, lines.exchange_rate_type,
2469 -- Bug 3223147 DECODE(SIGN(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
2470 -- 1, ABS(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)), NULL)
2471 lines.accounted_amt_cr,
2472 -- Bug 3223147 DECODE(SIGN(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
2473 -- -1, ABS(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)), NULL)
2474 lines.accounted_amt_dr,
2475 -- Bug 3223147 DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)),
2476 -- 1, ABS(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)), NULL)
2477 lines.entered_amt_cr,
2478 -- Bug 3223147 DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)),
2479 -- -1, ABS(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)), NULL)
2480 lines.entered_amt_dr,
2481 le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
2482 le_bsv_map.cr_dr_credit_ccid,
2483
2484 /* Bug 3223147
2485 DECODE(SIGN(NVL(lines.accounted_amt_cr, 0)-NVL(lines.accounted_amt_dr,0)),
2486 1, 'C',
2487 -1, 'D',
2488 0, DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
2489 1, 'C',
2490 'D')) type,
2491 */
2492 DECODE(lines.accounted_amt_cr, NULL, DECODE(lines.entered_amt_cr, NULL, 'D', 'C'), 'C') type,
2493 le_bsv_map.clearing_bsv
2494 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines, fun_bal_headers_gt hdrs
2495 WHERE hdrs.group_id = lines.group_id
2496 AND lines.group_id = le_bsv_map.group_id
2497 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
2498 AND hdrs.status = 'OK'
2499 AND le_bsv_map.balance_by = 'D'
2500 UNION ALL
2501 SELECT hdrs.group_id, hdrs.gl_date, le_bsv_map.driving_dr_bsv,
2502 le_bsv_map.driving_cr_bsv, le_bsv_map.intracompany_mode, le_bsv_map.balance_by,
2503 le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
2504 le_bsv_map.le_id, le_bsv_map.template_id, lines.entered_currency_code,
2505 lines.exchange_date, lines.exchange_rate, lines.exchange_rate_type,
2506 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
2507 1, ABS(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))), NULL)
2508 accounted_amt_cr,
2509 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
2510 -1, ABS(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))), NULL)
2511 accounted_amt_dr,
2512 DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))),
2513 1, ABS(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))), NULL)
2514 entered_amt_cr,
2515 DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))),
2516 -1, ABS(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))), NULL)
2517 entered_amt_dr,
2518 le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
2519 le_bsv_map.cr_dr_credit_ccid,
2520 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0))-SUM(NVL(lines.accounted_amt_dr,0))),
2521 1, 'C',
2522 -1, 'D',
2523 0, DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
2524 1, 'C',
2525 'D')) type, le_bsv_map.clearing_bsv
2526 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
2527 fun_bal_headers_gt hdrs
2528 WHERE hdrs.group_id = lines.group_id
2529 AND lines.group_id = le_bsv_map.group_id
2530 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
2531 AND hdrs.status = 'OK'
2532 AND le_bsv_map.balance_by = 'S'
2533 GROUP BY hdrs.group_id, hdrs.gl_date, hdrs.status, le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
2534 le_bsv_map.intracompany_mode, le_bsv_map.balance_by, le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
2535 le_bsv_map.le_id, lines.entered_currency_code, lines.exchange_date, lines.exchange_rate,
2536 lines.exchange_rate_type, le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
2537 le_bsv_map.cr_dr_credit_ccid, le_bsv_map.clearing_bsv, le_bsv_map.template_id
2538 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
2539 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
2540 AND
2541 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
2542 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))));
2543
2544 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_INTRA_INT_GT');
2545
2546 IF g_debug = FND_API.G_TRUE THEN
2547 OPEN l_intra_int_cursor;
2548 FETCH l_intra_int_cursor BULK COLLECT INTO l_intra_int_tab;
2549 l_intra_int_count := l_intra_int_cursor%ROWCOUNT;
2550 CLOSE l_intra_int_cursor;
2551 ins_t_tables_intra_2_auto(l_intra_int_tab, l_intra_int_count);
2552 END IF;
2553
2554 -- Insert intracompany balancing lines into the FUN_BAL_RESULTS_GT table.
2555 -- These resulting lines would be directly inserted into the results table
2556 -- 'C' normally means that a credit line should be created, but when run in detail
2557 -- mode, it could mean a debit line.
2558 INSERT INTO fun_bal_results_gt lines(group_id, bal_seg_val, entered_amt_dr,
2559 entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
2560 accounted_amt_dr, accounted_amt_cr, ccid, dr_bsv, cr_bsv, acct_type, le_id, template_id, balancing_type)
2561 SELECT intra_lines.group_id,
2562 DECODE(intra_lines.intracompany_mode,
2563 1, bal_seg_val,
2564 2, DECODE(gen.value, 'C', intra_lines.driving_dr_bsv, intra_lines.bal_seg_val),
2565 3, DECODE(gen.value, 'C', intra_lines.bal_seg_val, intra_lines.driving_cr_bsv),
2566 4, bal_seg_val,
2567 5, DECODE(gen.value, intra_lines.type, intra_lines.clearing_bsv, intra_lines.bal_seg_val),
2568 NULL),
2569 DECODE(intra_lines.intracompany_mode,
2570 1, intra_lines.entered_amt_cr,
2571 2, DECODE(gen.value, 'C', intra_lines.entered_amt_dr,
2572 'D', intra_lines.entered_amt_cr,
2573 -1),
2574 3, DECODE(gen.value, 'C', intra_lines.entered_amt_cr,
2575 'D', intra_lines.entered_amt_dr,
2576 -1),
2577 4, intra_lines.entered_amt_cr,
2578 5, DECODE(gen.value, intra_lines.type, intra_lines.entered_amt_dr,
2579 intra_lines.entered_amt_cr)),
2580 DECODE(intra_lines.intracompany_mode,
2581 1, intra_lines.entered_amt_dr,
2582 2, DECODE(gen.value, 'C', intra_lines.entered_amt_cr,
2583 'D', intra_lines.entered_amt_dr,
2584 -1),
2585 3, DECODE(gen.value, 'C', intra_lines.entered_amt_dr,
2586 'D', intra_lines.entered_amt_cr,
2587 -1),
2588 4, intra_lines.entered_amt_dr,
2589 5, DECODE(gen.value, intra_lines.type, intra_lines.entered_amt_cr,
2590 intra_lines.entered_amt_dr)),
2591 intra_lines.entered_currency_code,
2592 intra_lines.exchange_date, intra_lines.exchange_rate, intra_lines.exchange_rate_type,
2593 DECODE(intra_lines.intracompany_mode,
2594 1, intra_lines.accounted_amt_cr,
2595 2, DECODE(gen.value, 'C', intra_lines.accounted_amt_dr,
2596 'D', intra_lines.accounted_amt_cr,
2597 -1),
2598 3, DECODE(gen.value, 'C', intra_lines.accounted_amt_cr,
2599 'D', intra_lines.accounted_amt_dr,
2600 -1),
2601 4, intra_lines.accounted_amt_cr,
2602 5, DECODE(gen.value, intra_lines.type, intra_lines.accounted_amt_dr,
2603 intra_lines.accounted_amt_cr)),
2604 DECODE(intra_lines.intracompany_mode,
2605 1, intra_lines.accounted_amt_dr,
2606 2, DECODE(gen.value, 'C', intra_lines.accounted_amt_cr,
2607 'D', intra_lines.accounted_amt_dr,
2608 -1),
2609 3, DECODE(gen.value, 'C', intra_lines.accounted_amt_dr,
2610 'D', intra_lines.accounted_amt_cr,
2611 -1),
2612 4, intra_lines.accounted_amt_dr,
2613 5, DECODE(gen.value, intra_lines.type, intra_lines.accounted_amt_cr,
2614 intra_lines.accounted_amt_dr)),
2615 DECODE(intra_lines.intracompany_mode,
2616 1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', cr_dr_credit_ccid, -- bal_seg_val
2617 'C', dr_cr_debit_ccid,
2618 -1),
2619 'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
2620 'D', cr_dr_credit_ccid,
2621 -1),
2622 -1),
2623 2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
2624 'D', cr_dr_credit_ccid,
2625 -1),
2626 'C', DECODE(intra_lines.type, 'C', dr_cr_credit_ccid, -- other_seg_val
2627 'D', cr_dr_debit_ccid,
2628 -1),
2629 -1),
2630 3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
2631 'D', cr_dr_credit_ccid,
2632 -1),
2633 'D', DECODE(intra_lines.type, 'D', cr_dr_debit_ccid, -- other_seg_val
2634 'C', dr_cr_credit_ccid,
2635 -1),
2636 -1),
2637 4, DECODE(gen.value, 'C', cr_dr_credit_ccid,
2638 'D', dr_cr_debit_ccid,
2639 -1),
2640 5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', cr_dr_credit_ccid, -- bal_seg_val
2641 'C', dr_cr_credit_ccid, -- other_seg_val
2642 -1),
2643 'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
2644 'D', cr_dr_debit_ccid, -- other_seg_val
2645 -1),
2646 -1)),
2647 DECODE(intra_lines.intracompany_mode,
2648 1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', driving_cr_bsv,
2649 'C', bal_seg_val,
2650 -1),
2651 'D', DECODE(intra_lines.type, 'C', bal_seg_val,
2652 'D', driving_dr_bsv,
2653 -1),
2654 -1),
2655 2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', bal_seg_val,
2656 'D', driving_dr_bsv,
2657 -1),
2658 'C', DECODE(intra_lines.type, 'C', bal_seg_val,
2659 'D', driving_dr_bsv,
2660 -1),
2661 -1),
2662 3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', bal_seg_val,
2663 'D', driving_cr_bsv,
2664 -1),
2665 'D', DECODE(intra_lines.type, 'D', driving_cr_bsv,
2666 'C', bal_seg_val,
2667 -1),
2668 -1),
2669 4, bal_seg_val,
2670 5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', clearing_bsv,
2671 'C', bal_seg_val,
2672 -1),
2673 'D', DECODE(intra_lines.type, 'C', bal_seg_val,
2674 'D', clearing_bsv,
2675 -1),
2676 -1)),
2677 DECODE(intra_lines.intracompany_mode,
2678 1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', bal_seg_val,
2679 'C', driving_cr_bsv,
2680 -1),
2681 'D', DECODE(intra_lines.type, 'C', driving_dr_bsv,
2682 'D', bal_seg_val,
2683 -1),
2684 -1),
2685 2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', driving_dr_bsv,
2686 'D', bal_seg_val,
2687 -1),
2688 'C', DECODE(intra_lines.type, 'C', driving_dr_bsv,
2689 'D', bal_seg_val,
2690 -1),
2691 -1),
2692 3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', driving_cr_bsv,
2693 'D', bal_seg_val,
2694 -1),
2695 'D', DECODE(intra_lines.type, 'D', bal_seg_val,
2696 'C', driving_cr_bsv,
2697 -1),
2698 -1),
2699 4, bal_seg_val,
2700 5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', bal_seg_val,
2701 'C', clearing_bsv,
2702 -1),
2703 'D', DECODE(intra_lines.type, 'C', clearing_bsv,
2704 'D', bal_seg_val,
2705 -1),
2706 -1)),
2707 DECODE(intra_lines.intracompany_mode,
2708 1, DECODE(intra_lines.type, 'D', 'C',
2709 'C', 'D', -1),
2710 2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', 'D',
2711 'D', 'C',
2712 -1),
2713 'C', DECODE(intra_lines.type, 'C', 'C',
2714 'D', 'D',
2715 -1),
2716 -1),
2717 3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', 'D',
2718 'D', 'C',
2719 -1),
2720 'D', DECODE(intra_lines.type, 'D', 'D',
2721 'C', 'C',
2722 -1),
2723 -1),
2724 4, gen.value,
2725 5, gen.value,
2726 -1),
2727 intra_lines.le_id, intra_lines.template_id, 'R'
2728 FROM FUN_BAL_INTRA_INT_GT intra_lines, FUN_BAL_GENERATE_LINES gen
2729 WHERE gen.value = DECODE(intra_lines.intracompany_mode,
2730 1, DECODE(gen.value, 'C', DECODE(intra_lines.bal_seg_val,
2731 intra_lines.driving_cr_bsv, 'X', gen.value),
2732 'D', DECODE(intra_lines.bal_seg_val,
2733 intra_lines.driving_dr_bsv, 'X', gen.value),
2734 'X'),
2735 2, DECODE(intra_lines.bal_seg_val, intra_lines.driving_dr_bsv, 'X', gen.value),
2736 3, DECODE(intra_lines.bal_seg_val, intra_lines.driving_cr_bsv, 'X', gen.value),
2737 4, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', 'X', gen.value),
2738 'D', DECODE(intra_lines.type, 'D', 'X', gen.value)),
2739 5, DECODE(bal_seg_val, clearing_bsv, 'X', gen.value), -- bug 3203634
2740 'X');
2741
2742 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2743 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid_concat_disp.begin', 'begin');
2744 END IF;
2745
2746 INSERT INTO fun_bal_errors_gt(error_code, group_id, template_id, le_id,
2747 dr_bsv, cr_bsv, acct_type, ccid_concat_display)
2748 SELECT DISTINCT DECODE(SIGN(NVL(results.ccid, 0)),
2749 -1, 'FUN_INTRA_CC_NOT_CREATED',
2750 0, 'FUN_INTRA_CC_NOT_CREATED',
2751 DECODE(cc.summary_flag,
2752 'Y', 'FUN_INTRA_CC_NOT_VALID',
2753 DECODE(cc.template_id,
2754 NULL, 'FUN_INTRA_CC_NOT_ACTIVE',
2755 'FUN_INTRA_CC_NOT_VALID'))),
2756 headers.group_id, results.template_id,
2757 DECODE(results.le_id, -99, NULL, results.le_id),
2758 results.dr_bsv, results.cr_bsv,
2759 results.acct_type, get_ccid_concat_disp(results.ccid, headers.chart_of_accounts_id,
2760 DECODE(results.acct_type, 'C', results.cr_bsv, results.dr_bsv),
2761 DECODE(results.acct_type, 'C', results.dr_bsv, results.cr_bsv),
2762 headers.bal_seg_column_number, headers.intercompany_column_number)
2763 FROM fun_bal_headers_gt headers, fun_bal_results_gt results, gl_code_combinations cc
2764 WHERE headers.group_id = results.group_id
2765 AND headers.status = 'OK'
2766 AND results.ccid = cc.code_combination_id(+)
2767 AND (results.ccid < 0
2768 OR results.ccid IS NULL -- NULL case should not happen, but just in case
2769 OR NOT (cc.detail_posting_allowed_flag = 'Y'
2770 AND cc.enabled_flag = 'Y'
2771 AND cc.summary_flag = 'N'
2772 AND cc.template_id IS NULL
2773 AND (TRUNC(headers.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, headers.gl_date))
2774 AND TRUNC(NVL(cc.end_date_active, headers.gl_date)))));
2775
2776 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2777 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid_concat_disp.end', 'end');
2778 END IF;
2779
2780 UPDATE fun_bal_headers_gt headers
2781 SET STATUS = 'ERROR'
2782 WHERE EXISTS (SELECT 'Invalid CCID error'
2783 FROM FUN_BAL_ERRORS_GT errors
2784 WHERE headers.group_id = errors.group_id
2785 AND error_code IN ('FUN_INTRA_CC_NOT_VALID',
2786 'FUN_INTRA_CC_NOT_CREATED',
2787 'FUN_INTRA_CC_NOT_ACTIVE'));
2788
2789
2790 DELETE FROM fun_bal_results_gt results
2791 WHERE EXISTS (SELECT group_id
2792 FROM fun_bal_headers_gt headers
2793 WHERE headers.status = 'ERROR'
2794 AND results.group_id = headers.group_id);
2795
2796 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2797 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.end', 'end');
2798 END IF;
2799
2800 RETURN FND_API.G_RET_STS_SUCCESS;
2801 END do_intra_bal;
2802
2803
2804 PROCEDURE journal_balancing
2805 ( p_api_version IN NUMBER,
2806 p_init_msg_list IN VARCHAR2 ,
2807 p_validation_level IN NUMBER,
2808 p_debug IN VARCHAR2 ,
2809 x_return_status OUT NOCOPY VARCHAR2,
2810 x_msg_count OUT NOCOPY NUMBER,
2811 x_msg_data OUT NOCOPY VARCHAR2,
2812 p_product_code IN VARCHAR2 -- Valid values are GL and SLA for this release
2813 ) IS
2814
2815 l_api_name CONSTANT VARCHAR2(30) := 'JOURNAL_BALANCING';
2816 l_api_version CONSTANT NUMBER := 1.0;
2817 l_return_status VARCHAR2(1);
2818
2819 BEGIN
2820
2821 -- variable p_validation_level is not used .
2822 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
2823
2824 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2825 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing.begin', 'begin');
2826 END IF;
2827
2828 -- Standard call to check for call compatibility.
2829 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2830 p_api_version,
2831 l_api_name,
2832 G_PKG_NAME )
2833 THEN
2834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2835 END IF;
2836
2837 -- Initialize message list if p_init_msg_list is set to TRUE.
2838 IF FND_API.to_Boolean( nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
2839 FND_MSG_PUB.initialize;
2840 END IF;
2841
2842 -- Initialize API return status to success
2843 x_return_status := FND_API.G_RET_STS_SUCCESS;
2844 -- API body
2845 g_product_code := p_product_code;
2846 g_debug := nvl(p_debug,FND_API.G_FALSE);
2847 l_return_status := do_init;
2848
2849 l_return_status := do_inter_bal;
2850
2851 l_return_status := do_intra_bal;
2852
2853 x_return_status := do_finalize;
2854
2855 -- End of API body.
2856 -- Standard call to get message count and if count is 1, get message info.
2857 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2858 p_data => x_msg_data);
2859
2860 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2861 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing.end', 'end');
2862 END IF;
2863
2864 EXCEPTION
2865 WHEN FND_API.G_EXC_ERROR THEN
2866 do_save_in_error;
2867 IF (FND_LOG.LEVEL_ERROR>= g_debug_level) THEN
2868 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.journal_balancing.error', SUBSTR(SQLERRM,1, 4000));
2869 END IF;
2870 x_return_status := FND_API.G_RET_STS_ERROR ;
2871 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2872 p_data => x_msg_data);
2873 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2874 do_save_in_error;
2875 IF (FND_LOG.LEVEL_ERROR>= g_debug_level) THEN
2876 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.journal_balancing.unexpected_error_norm', SUBSTR(SQLCODE ||
2877 ' : ' || SQLERRM,1, 4000));
2878 END IF;
2879 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2880 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2881 p_data => x_msg_data);
2882 WHEN OTHERS THEN
2883 do_save_in_error;
2884 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2885 IF (FND_LOG.LEVEL_ERROR>= g_debug_level) THEN
2886 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.journal_balancing.unexpected_error_others', SUBSTR(SQLCODE ||
2887 ' : ' || SQLERRM,1, 4000));
2888 END IF;
2889 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2890 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
2891 END IF;
2892 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2893 p_data => x_msg_data);
2894 END journal_balancing;
2895
2896
2897
2898 END FUN_BAL_PKG;