[Home] [Help]
PACKAGE BODY: APPS.FUN_BAL_PKG
Source
1 PACKAGE BODY FUN_BAL_PKG AS
2 /* $Header: funbalpkgb.pls 120.82.12020000.7 2013/03/20 19:19:06 jmary 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 Function getintersegvalue(ccid IN NUMBER,intercompany_column_number IN Number) return varchar2
27 is
28
29 icvalue varchar2(25);
30
31 l_segment_array FND_FLEX_EXT.SEGMENTARRAY;
32 l_number_of_segments NUMBER;
33 l_delimiter VARCHAR2(1);
34 l_chart_of_accounts_id NUMBER;
35 l_concatenated_segments gl_code_combinations_kfv.concatenated_segments%TYPE;
36
37 begin
38
39 if (intercompany_column_number > 0 and intercompany_column_number < 31) then
40
41 SELECT concatenated_segments, chart_of_accounts_id
42 INTO l_concatenated_segments, l_chart_of_accounts_id
43 FROM gl_code_combinations_kfv
44 WHERE code_combination_id = ccid;
45
46 l_delimiter := fnd_flex_ext.get_delimiter('SQLGL', 'GL#', l_chart_of_accounts_id);
47
48 l_number_of_segments := fnd_flex_ext.breakup_segments(l_concatenated_segments,
49 l_delimiter,
50 l_segment_array);
51 icvalue := l_segment_array(intercompany_column_number);
52 return icvalue;
53 end if;
54 return null;
55 end;
56
57
58 PROCEDURE truncate_tables
59 IS
60 cur_hdl int;
61 rows_processed int;
62 BEGIN
63 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
64 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.truncate_tables', 'begin');
65 END IF;
66
67 DELETE FROM fun_bal_results_gt;
68 DELETE FROM fun_bal_errors_gt;
69 DELETE FROM fun_bal_le_bsv_map_gt;
70 DELETE FROM fun_bal_inter_int_gt;
71 DELETE FROM fun_bal_intra_int_gt;
72 /* Using delete rather than truncate as shown in the code below. The reason is that truncate (or any DDL operations)
73 perform an implicit commit => need to use autonomous transaction to perform such operation. However, we would
74 like to make sure the calling program does not see the rows that gets deleted, therefore truncate is not used.
75 In addition, the truncate operation might not be able to delete the rows that the calling program has not commited yet,
76 which could result in that we think the rows got deleted but they still exist.
77 cur_hdl := dbms_sql.open_cursor;
78 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_RESULTS_GT', dbms_sql.native);
79 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_ERRORS_GT', dbms_sql.native);
80 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_LE_BSV_MAP_GT', dbms_sql.native);
81 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_INT_GT', dbms_sql.native);
82 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_INT_GT', dbms_sql.native);
83 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_RESULTS_T', dbms_sql.native);
84 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_ERRORS_T', dbms_sql.native);
85 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_LINES_T', dbms_sql.native);
86 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_HEADERS_T', dbms_sql.native);
87 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_BSV_MAP_T', dbms_sql.native);
88 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_BSV_MAP_T', dbms_sql.native);
89 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_INT_T', dbms_sql.native);
90 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_INT_T', dbms_sql.native);
91 dbms_sql.close_cursor(cur_hdl); -- close cursor
92 */
93 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
94 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.truncate_tables', 'end');
95 END IF;
96
97 RETURN;
98 END truncate_tables;
99
100
101 PROCEDURE update_inter_seg_val IS
102 stmt_str varchar2(1000);
103 cur_hdl int;
104 rows_processed int;
105 l_bal_seg_column_name VARCHAR2(25);
106 CURSOR bal_seg_val_cur IS
107 SELECT DISTINCT bal_seg_column_name
108 FROM fun_bal_headers_gt headers
109 WHERE headers.status = 'OK';
110 BEGIN
111 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
112 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.update_inter_seg_val.begin', 'begin');
113 END IF;
114
115 OPEN bal_seg_val_cur;
116 LOOP
117 FETCH bal_seg_val_cur INTO l_bal_seg_column_name;
118 EXIT WHEN bal_seg_val_cur%NOTFOUND;
119 cur_hdl := dbms_sql.open_cursor;
120 stmt_str := 'UPDATE fun_bal_inter_int_gt inter_int ' ||
121 ' SET rec_bsv = ' ||
122 ' (SELECT ' || l_bal_seg_column_name ||
123 ' FROM gl_code_combinations ' ||
124 ' WHERE code_combination_id = inter_int.rec_acct ' ||
125 ' AND inter_int.bal_seg_column_name = ''' || l_bal_seg_column_name || ''') ' ||
126 ' WHERE inter_int.rec_acct IS NOT NULL AND inter_int.rec_acct <> -1';
127 dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);
128 rows_processed := dbms_sql.execute(cur_hdl);
129 dbms_sql.close_cursor(cur_hdl); -- close cursor
130
131 cur_hdl := dbms_sql.open_cursor;
132 stmt_str := 'UPDATE fun_bal_inter_int_gt inter_int ' ||
133 ' SET pay_bsv = ' ||
134 ' (SELECT ' || l_bal_seg_column_name ||
135 ' FROM gl_code_combinations ' ||
136 ' WHERE code_combination_id = inter_int.pay_acct ' ||
137 ' AND inter_int.bal_seg_column_name = ''' || l_bal_seg_column_name || ''') ' ||
138 ' WHERE inter_int.pay_acct IS NOT NULL AND inter_int.pay_acct <> -1';
139 dbms_sql.parse(cur_hdl, stmt_str, dbms_sql.native);
140 rows_processed := dbms_sql.execute(cur_hdl);
141 dbms_sql.close_cursor(cur_hdl); -- close cursor
142 END LOOP;
143 CLOSE bal_seg_val_cur;
144 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
145 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.update_inter_seg_val.end', 'end');
146 END IF;
147
148 RETURN;
149 END update_inter_seg_val;
150
151 FUNCTION get_segment_index (p_chart_of_accounts_id IN NUMBER,
152 p_segment_type VARCHAR2)
153 RETURN NUMBER
154 IS
155 CURSOR c_segments (p_chart_of_accounts_id NUMBER) IS
156 SELECT s.segment_num, sav.segment_attribute_type
157 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav
158 WHERE s.application_id = 101
159 AND s.id_flex_code = 'GL#'
160 AND s.id_flex_num = p_chart_of_accounts_id
161 AND s.enabled_flag = 'Y'
162 AND s.application_column_name = sav.application_column_name
163 AND sav.application_id = 101
164 AND sav.id_flex_code = 'GL#'
165 AND sav.id_flex_num = p_chart_of_accounts_id
166 AND sav.attribute_value = 'Y'
167 ORDER BY s.segment_num ASC;
168
169 l_ic_seg_num NUMBER;
170 l_bal_seg_num NUMBER;
171 l_index NUMBER;
172 l_prev_seg_num NUMBER;
173
174 BEGIN
175 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
176 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.get_segment_index', 'start');
177 END IF;
178
179 l_index := 0;
180 l_prev_seg_num := 0;
181
182 FOR r_segments IN c_segments (p_chart_of_accounts_id)
183 LOOP
184 IF l_prev_seg_num <> r_segments.segment_num
185 THEN
186 l_index := l_index + 1;
187 END IF;
188
189 IF r_segments.segment_attribute_type = p_segment_type
190 AND p_segment_type = 'GL_BALANCING'
191 THEN
192 l_bal_seg_num := l_index;
193 RETURN l_bal_seg_num;
194 END IF;
195
196 IF r_segments.segment_attribute_type = p_segment_type
197 AND p_segment_type = 'GL_INTERCOMPANY'
198 THEN
199 l_ic_seg_num := l_index;
200 RETURN l_ic_seg_num;
201 END IF;
202
203 l_prev_seg_num := r_segments.segment_num;
204 END LOOP;
205
206 RETURN NULL;
207
208 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
209 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.get_segment_index', 'end');
210 END IF;
211
212 END get_segment_index;
213
214 FUNCTION get_ccid
215 ( ccid IN NUMBER,
216 chart_of_accounts_id IN NUMBER,
217 bal_seg_val IN VARCHAR2,
218 intercompany_seg_val IN VARCHAR2,
219 bal_seg_column_number IN NUMBER,
220 intercompany_column_number IN NUMBER,
221 gl_date IN DATE) RETURN NUMBER IS
222 l_segment_array FND_FLEX_EXT.SEGMENTARRAY;
223 l_flag BOOLEAN;
224 l_no_of_segments NUMBER;
225 l_ccid NUMBER;
226 l_rule VARCHAR2(1000);
227 l_where_clause VARCHAR2(30);
228 l_get_column VARCHAR2(30);
229 l_delimiter VARCHAR2(1);
230 l_cat_segs VARCHAR2(2000);
231 l_error_message VARCHAR2(2000);
232
233 -- Bug # 7141663 Key for the cache table created
234 l_ccid_key VARCHAR2(2000);
235
236 BEGIN
237
238 IF (FND_LOG.LEVEL_ERROR >= g_debug_level) THEN
239
240 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.get_ccid', 'Begin');
241
242 END IF;
243
244 -- Bug # 7141663 Key for the cache table created
245 -- Bug # 7321887 Replaced TO_DATE() with TO_CHAR()
246 l_ccid_key := ccid || '~' || chart_of_accounts_id || '~' ||
247 bal_seg_val || '~' || intercompany_seg_val || '~' ||
248 bal_seg_column_number|| '~' || intercompany_column_number || '~' ||
249 TO_CHAR(gl_date,'DD-MM-YYYY');
250
251 IF (FND_LOG.LEVEL_ERROR >= g_debug_level) THEN
252
253 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.get_ccid', 'l_ccid_key : '|| l_ccid_key);
254
255 END IF;
256
257 -- Bug # 7141663 If the key does not exists in the cache table,
258 -- then get the ccid as done previously, else fetch the same from
259 -- the cache table.
260 IF ( g_ccid_cache_tab.EXISTS( l_ccid_key ) = FALSE ) THEN
261 l_rule := '\nSUMMARY_FLAG\nI\n' ||
262 'APPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN\0' ||
263 'GL_GLOBAL\nDETAIL_POSTING_ALLOWED' ||
264 '\nI\n' ||
265 'APPL=SQLGL;NAME=GL_JE_POSTING_NOT_ALLOWED\nY';
266 l_delimiter := fnd_flex_ext.get_delimiter('SQLGL', 'GL#', chart_of_accounts_id);
267 l_flag := fnd_flex_ext.get_segments('SQLGL', 'GL#', chart_of_accounts_id, ccid,
268 l_no_of_segments, l_segment_array);
269 IF l_flag = FALSE THEN
270 IF (FND_LOG.LEVEL_ERROR >= g_debug_level) THEN
271 l_error_message := FND_FLEX_KEYVAL.ERROR_MESSAGE;
272 IF l_error_message IS NOT NULL THEN
273 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.get_ccid.get_segments', l_error_message);
274 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.get_ccid.get_segments', 'l_ccid_key : '|| l_ccid_key);
275 END IF;
276 END IF;
277 FND_MESSAGE.SET_NAME('FUN', 'FUN_BAL_GET_CCID_ERROR');
278 FND_MESSAGE.SET_TOKEN('GL_ERROR', FND_FLEX_KEYVAL.ERROR_MESSAGE);
279 FND_MSG_PUB.Add;
280 RETURN -ccid;
281 END IF;
282 l_segment_array(bal_seg_column_number) := bal_seg_val;
283 IF intercompany_column_number IS NOT NULL
284 AND bal_seg_column_number <> intercompany_column_number
285 THEN
286 l_segment_array(intercompany_column_number) := intercompany_seg_val;
287 END IF;
288 l_cat_segs := fnd_flex_ext.concatenate_segments(l_no_of_segments, l_segment_array, l_delimiter);
289 l_flag := fnd_flex_keyval.validate_segs('CREATE_COMBINATION','SQLGL',
290 'GL#', chart_of_accounts_id, l_cat_segs,
291 'V', gl_date, 'ALL', NULL, l_rule, l_where_clause,
292 l_get_column, FALSE, FALSE,
293 NULL, NULL, NULL, NULL, NULL, NULL);
294 IF l_flag = FALSE THEN
295 IF (FND_LOG.LEVEL_ERROR >= g_debug_level) THEN
296 l_error_message := FND_FLEX_KEYVAL.ERROR_MESSAGE;
297 IF l_error_message IS NOT NULL THEN
298 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.get_ccid.validate_segs', l_error_message);
299 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.get_ccid.validate_segs', 'l_ccid_key : '|| l_ccid_key);
300 END IF;
301 END IF;
302 FND_MESSAGE.SET_NAME('FUN', 'FUN_BAL_GET_CCID_ERROR');
303 FND_MESSAGE.SET_TOKEN('GL_ERROR', FND_FLEX_KEYVAL.ERROR_MESSAGE);
304 FND_MSG_PUB.Add;
305 RETURN -ccid;
306 END IF;
307
308 -- Bug # 7141663 Add the result to the cache table
309 g_ccid_cache_tab(l_ccid_key) := fnd_flex_keyval.combination_id;
310 END IF;
311
312 -- Bug # 7141663 fetch the ccid from the cache table and return the same
313 return g_ccid_cache_tab(l_ccid_key);
314 END get_ccid;
315
316 FUNCTION get_ccid_concat_disp
317 ( ccid IN NUMBER,
318 chart_of_accounts_id IN NUMBER,
319 bal_seg_val IN VARCHAR2,
320 intercompany_seg_val IN VARCHAR2,
321 bal_seg_column_number IN NUMBER,
322 intercompany_column_number IN NUMBER) RETURN VARCHAR2 IS
323 l_segment_array FND_FLEX_EXT.SEGMENTARRAY;
324 l_flag BOOLEAN;
325 l_no_of_segments NUMBER;
326 l_delimiter VARCHAR2(1);
327 l_cat_segs VARCHAR2(2000);
328 l_ccid NUMBER;
329 BEGIN
330 IF bal_seg_column_number IS NOT NULL AND bal_seg_val IS NOT NULL THEN
331 IF ccid = 0 OR ccid IS NULL THEN
332 RETURN NULL;
333 ELSIF ccid < 0 THEN
334 l_ccid := -ccid;
335 ELSE
336 l_ccid := ccid;
337 END IF;
338 l_delimiter := fnd_flex_ext.get_delimiter('SQLGL', 'GL#', chart_of_accounts_id);
339 l_flag := fnd_flex_ext.get_segments('SQLGL', 'GL#', chart_of_accounts_id, l_ccid,
340 l_no_of_segments, l_segment_array);
341 IF ccid < 0 THEN
342 l_segment_array(bal_seg_column_number) := bal_seg_val;
343 IF intercompany_column_number IS NOT NULL THEN
344 l_segment_array(intercompany_column_number) := intercompany_seg_val;
345 END IF;
346 END IF;
347 l_cat_segs := fnd_flex_ext.concatenate_segments(l_no_of_segments, l_segment_array, l_delimiter);
348 RETURN l_cat_segs;
349 ELSE
350 RETURN NULL;
351 END IF;
352
353 END get_ccid_concat_disp;
354
355 PROCEDURE ins_headers_t(headers_tab IN headers_tab_type, headers_count IN NUMBER) IS
356 cur_hdl int;
357 BEGIN
358
359 cur_hdl := dbms_sql.open_cursor;
360 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_HEADERS_T', dbms_sql.native);
361 dbms_sql.close_cursor(cur_hdl); -- close cursor
362 IF headers_count > 0 THEN
363 FORALL i IN headers_tab.first..headers_tab.last
364 INSERT INTO fun_bal_headers_t
365 VALUES headers_tab(i);
366 END IF;
367 END ins_headers_t;
368
369
370 PROCEDURE ins_lines_t(lines_tab IN lines_tab_type, lines_count IN NUMBER) IS
371 cur_hdl int;
372 BEGIN
373 cur_hdl := dbms_sql.open_cursor;
374 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_LINES_T', dbms_sql.native);
375 dbms_sql.close_cursor(cur_hdl); -- close cursor
376 IF lines_count > 0 THEN
377 FORALL i IN lines_tab.first..lines_tab.last
378 INSERT INTO fun_bal_lines_t
379 VALUES lines_tab(i);
380 END IF;
381 END ins_lines_t;
382
383 PROCEDURE ins_results_t(results_tab IN results_tab_type, results_count IN NUMBER) IS
384 cur_hdl int;
385 BEGIN
386 cur_hdl := dbms_sql.open_cursor;
387 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_RESULTS_T', dbms_sql.native);
388 dbms_sql.close_cursor(cur_hdl); -- close cursor
389 IF results_count > 0 THEN
390 FORALL i IN results_tab.first..results_tab.last
391 INSERT INTO fun_bal_results_t
392 VALUES results_tab(i);
393 END IF;
394 END ins_results_t;
395
396 PROCEDURE ins_errors_t(errors_tab IN errors_tab_type, errors_count IN NUMBER) IS
397 cur_hdl int;
398 BEGIN
399 cur_hdl := dbms_sql.open_cursor;
400 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_ERRORS_T', dbms_sql.native);
401 dbms_sql.close_cursor(cur_hdl); -- close cursor
402 IF errors_count > 0 THEN
403 FORALL i IN errors_tab.first..errors_tab.last
404 INSERT INTO fun_bal_errors_t
405 VALUES errors_tab(i);
406 END IF;
407 END ins_errors_t;
408
409 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
410 cur_hdl int;
411 BEGIN
412 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
413 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');
414 END IF;
415 cur_hdl := dbms_sql.open_cursor;
416 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_BSV_MAP_T', dbms_sql.native);
417 dbms_sql.close_cursor(cur_hdl); -- close cursor
418 IF inter_le_bsv_map_count > 0 THEN
419 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
420 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');
421 END IF;
422
423 FORALL i IN le_bsv_map_tab.first..le_bsv_map_tab.last
424 INSERT INTO fun_bal_inter_bsv_map_t
425 VALUES le_bsv_map_tab(i);
426
427 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
428 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');
429 END IF;
430 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
431 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');
432 END IF;
433 END IF;
434 END ins_inter_le_bsv_map_t;
435
436 PROCEDURE ins_inter_int_t(inter_int_tab IN inter_int_tab_type, inter_int_count IN NUMBER) IS
437 cur_hdl int;
438 BEGIN
439 cur_hdl := dbms_sql.open_cursor;
440 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTER_INT_T', dbms_sql.native);
441 dbms_sql.close_cursor(cur_hdl); -- close cursor
442
443 IF inter_int_count > 0 THEN
444 FORALL i IN inter_int_tab.first..inter_int_tab.last
445 INSERT INTO fun_bal_inter_int_t
446 VALUES inter_int_tab(i);
447
448 END IF;
449 END ins_inter_int_t;
450
451 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
452 cur_hdl int;
453 BEGIN
454 cur_hdl := dbms_sql.open_cursor;
455 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_BSV_MAP_T', dbms_sql.native);
456 dbms_sql.close_cursor(cur_hdl); -- close cursor
457 IF intra_le_bsv_map_count > 0 THEN
458 FORALL i IN le_bsv_map_tab.first..le_bsv_map_tab.last
459 INSERT INTO fun_bal_intra_bsv_map_t
460 VALUES le_bsv_map_tab(i);
461 END IF;
462 END ins_intra_le_bsv_map_t;
463
464
465 PROCEDURE ins_intra_int_t(intra_int_tab IN intra_int_tab_type, intra_int_count IN NUMBER) IS
466 cur_hdl int;
467 BEGIN
468 cur_hdl := dbms_sql.open_cursor;
469 dbms_sql.parse(cur_hdl, 'TRUNCATE TABLE ' || g_fun_schema || '.FUN_BAL_INTRA_INT_T', dbms_sql.native);
470 dbms_sql.close_cursor(cur_hdl); -- close cursor
471 IF intra_int_count > 0 THEN
472 FORALL i IN intra_int_tab.first..intra_int_tab.last
473 INSERT INTO fun_bal_intra_int_t
474 VALUES intra_int_tab(i);
475 END IF;
476 END ins_intra_int_t;
477
478
479 PROCEDURE ins_t_tables_in_error_auto(headers_tab IN headers_tab_type,
480 lines_tab IN lines_tab_type,
481 headers_count IN NUMBER,
482 lines_count IN NUMBER) IS
483 PRAGMA AUTONOMOUS_TRANSACTION;
484 BEGIN
485 ins_headers_t(headers_tab, headers_count);
486 ins_lines_t(lines_tab, lines_count);
487 COMMIT;
488 END ins_t_tables_in_error_auto;
489
490
491 PROCEDURE ins_t_tables_final_auto(headers_tab IN headers_tab_type,
492 lines_tab IN lines_tab_type,
493 results_tab IN results_tab_type,
494 errors_tab IN errors_tab_type,
495 headers_count IN NUMBER,
496 lines_count IN NUMBER,
497 results_count IN NUMBER,
498 errors_count IN NUMBER) IS
499 PRAGMA AUTONOMOUS_TRANSACTION;
500 BEGIN
501 ins_headers_t(headers_tab, headers_count);
502 ins_lines_t(lines_tab, lines_count);
503 ins_results_t(results_tab, results_count);
504 ins_errors_t(errors_tab, errors_count);
505 /*
506 INSERT INTO fun_bal_headers_t
507 SELECT * FROM fun_bal_headers_gt;
508 INSERT INTO fun_bal_lines_t
509 SELECT * FROM fun_bal_lines_gt;
510 INSERT INTO fun_bal_results_t
511 SELECT * FROM fun_bal_results_gt;
512 INSERT INTO fun_bal_errors_t
513 SELECT * FROM fun_bal_errors_gt;
514 */
515 COMMIT;
516 END ins_t_tables_final_auto;
517
518 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
519 PRAGMA AUTONOMOUS_TRANSACTION;
520 BEGIN
521
522 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
523 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'auton_begin');
524 END IF;
525 ins_inter_le_bsv_map_t(le_bsv_map_tab, le_bsv_map_count);
526 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
527 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'auton_end');
528 END IF;
529
530 COMMIT;
531 END ins_t_tables_inter_1_auto;
532
533 PROCEDURE ins_t_tables_inter_2_auto(inter_int_tab IN inter_int_tab_type, inter_int_count IN NUMBER) IS
534 PRAGMA AUTONOMOUS_TRANSACTION;
535 BEGIN
536
537 ins_inter_int_t(inter_int_tab, inter_int_count);
538 COMMIT;
539 END ins_t_tables_inter_2_auto;
540
541 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
542 PRAGMA AUTONOMOUS_TRANSACTION;
543 BEGIN
544 ins_intra_le_bsv_map_t(le_bsv_map_tab, le_bsv_map_count);
545 COMMIT;
546 END ins_t_tables_intra_1_auto;
547
548 PROCEDURE ins_t_tables_intra_2_auto(intra_int_tab IN intra_int_tab_type, intra_int_count IN NUMBER) IS
549 PRAGMA AUTONOMOUS_TRANSACTION;
550 BEGIN
551 -- INSERT INTO fun_bal_intra_int_t
552 -- SELECT * FROM fun_bal_intra_int_gt;
553 ins_intra_int_t(intra_int_tab, intra_int_count);
554 COMMIT;
555 END ins_t_tables_intra_2_auto;
556
557 FUNCTION do_init RETURN VARCHAR2 IS
558 l_return_val VARCHAR2(1) ;
559 l_boolean BOOLEAN;
560 l_status VARCHAR2(1);
561 l_industry VARCHAR2(1);
562 BEGIN
563
564 l_return_val := FND_API.G_RET_STS_SUCCESS;
565
566 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
567 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_init.begin', 'begin');
568 END IF;
569 -- Retrieve the actual FUN schema name from the current installation
570 l_boolean := FND_INSTALLATION.GET_APP_INFO('FUN', l_status, l_industry, g_fun_schema);
571 IF g_debug = FND_API.G_TRUE THEN
572 -- Delete data stored in temporary tables
573 truncate_tables;
574 END IF;
575
576
577 -- Note: bal_seg_column_number is different from SUBSTR(bal_seg_column_name, 8),
578 -- since bal_seg_column_name refers to the naming in GL_CODE_COMBINATIONS,
579 -- but bal_seg_column_number refers to the position relative to the COA.
580 -- These 2 values are used in different context (name when dealing with GL_CODE_COMB table
581 -- and number when dealing with AOL routines. Do not be confused.
582 -- Problem here: Later on when we deal with performance and decided to check whether the
583 -- code combination already exists in gl_code_combinations table, we would also need to
584 -- retrieve the correct segment_name for intercompany segment.
585 -- update balancing segment column, chart of accounts
586 --ASLAI_INIT_01
587 UPDATE fun_bal_headers_gt headers
588 SET (bal_seg_column_name, chart_of_accounts_id) =
589 (SELECT bal_seg_column_name, chart_of_accounts_id
590 FROM gl_ledgers ledgers
591 WHERE headers.ledger_id = ledgers.ledger_id);
592
593 UPDATE fun_bal_headers_gt headers
594 SET bal_seg_column_number = get_segment_index ( headers.chart_of_accounts_id,
595 'GL_BALANCING'),
596 intercompany_column_number = get_segment_index ( headers.chart_of_accounts_id,
597 'GL_INTERCOMPANY');
598 --Bug: 12606188
599 UPDATE fun_bal_lines_gt lines
600 SET entered_amt_dr = accounted_amt_dr,
601 entered_amt_cr = accounted_amt_cr,
602 entered_currency_code = (select gl.currency_code
603 from fun_bal_headers_gt headers, gl_ledgers gl
604 where gl.ledger_id = headers.ledger_id
605 and headers.group_id = lines.group_id),
606 exchange_date = null,
607 exchange_rate = null,
608 exchange_rate_type = null
609 WHERE 1 < (select count(distinct entered_currency_code)
610 from fun_bal_lines_gt lines2
611 where lines2.group_id = lines.group_id);
612
613 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_HEADERS_GT');
614 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LINES_GT');
615
616 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
617 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_init.end', 'end');
618 END IF;
619
620
621 RETURN l_return_val;
622 END do_init;
623
624
625 PROCEDURE do_save_in_error IS
626 l_headers_tab headers_tab_type;
627 l_lines_tab lines_tab_type;
628 l_headers_count NUMBER;
629 l_lines_count NUMBER;
630 CURSOR l_headers_cursor IS
631 SELECT * FROM fun_bal_headers_gt;
632 CURSOR l_lines_cursor IS
633 SELECT * FROM fun_bal_lines_gt;
634 BEGIN
635 OPEN l_headers_cursor;
636 FETCH l_headers_cursor BULK COLLECT INTO l_headers_tab;
637 l_headers_count := l_headers_cursor%ROWCOUNT;
638 CLOSE l_headers_cursor;
639 OPEN l_lines_cursor;
640 FETCH l_lines_cursor BULK COLLECT INTO l_lines_tab;
641 l_lines_count := l_lines_cursor%ROWCOUNT;
642 CLOSE l_lines_cursor;
643
644 ins_t_tables_in_error_auto(l_headers_tab, l_lines_tab,
645 l_headers_count, l_lines_count);
646 END do_save_in_error;
647
648
649 FUNCTION do_finalize RETURN VARCHAR2 IS
650 l_return_val VARCHAR2(1) ;
651 --l_error_count NUMBER(15) := 0;
652 l_headers_tab headers_tab_type;
653 l_lines_tab lines_tab_type;
654 l_results_tab results_tab_type;
655 l_errors_tab errors_tab_type;
656 l_headers_count NUMBER;
657 l_lines_count NUMBER;
658 l_results_count NUMBER;
659 l_errors_count NUMBER;
660 CURSOR l_headers_cursor IS
661 SELECT * FROM fun_bal_headers_gt;
662 CURSOR l_lines_cursor IS
663 SELECT * FROM fun_bal_lines_gt;
664 CURSOR l_results_cursor IS
665 SELECT * FROM fun_bal_results_gt;
666 CURSOR l_errors_cursor IS
667 SELECT * FROM fun_bal_errors_gt;
668 BEGIN
669 l_return_val := FND_API.G_RET_STS_SUCCESS;
670
671 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
672 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_finalize.begin', 'begin');
673 END IF;
674
675 -- Insert lines generated for Intercompany balancing from FUN_BAL_LINES_GT to FUN_BAL_RESULTS_GT
676 INSERT INTO fun_bal_results_gt results(group_id, bal_seg_val, entered_amt_dr,
677 entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
678 accounted_amt_dr, accounted_amt_cr, ccid, balancing_type)
679 SELECT lines.group_id, lines.bal_seg_val, lines.entered_amt_dr,
680 lines.entered_amt_cr, lines.entered_currency_code, lines.exchange_date, lines.exchange_rate,
681 lines.exchange_rate_type, lines.accounted_amt_dr, lines.accounted_amt_cr,
682 lines.ccid, 'E'
683 FROM fun_bal_lines_gt lines
684 WHERE lines.generated = 'Y';
685
686 -- Bug 3167894
687 --Bug: 12887806, 13590695
688 UPDATE fun_bal_results_gt results
689 SET entered_amt_dr = DECODE(entered_amt_dr, NULL, DECODE(accounted_amt_dr, NULL, entered_amt_dr, 0), entered_amt_dr),
690 entered_amt_cr = DECODE(entered_amt_cr, NULL, DECODE(accounted_amt_cr, NULL, entered_amt_cr, 0), entered_amt_cr),
691 accounted_amt_dr = DECODE(accounted_amt_dr, NULL, DECODE(entered_amt_dr, NULL, accounted_amt_dr, 0), accounted_amt_dr),
692 accounted_amt_cr = DECODE(accounted_amt_cr, NULL, DECODE(entered_amt_cr, NULL, accounted_amt_cr, 0), accounted_amt_cr),
693 exchange_rate = DECODE(EXCHANGE_RATE, NULL, NULL,
694 DECODE (NVL(ACCOUNTED_AMT_CR, 0), 0, DECODE(NVL(ENTERED_AMT_DR, 0), 0, 1, ACCOUNTED_AMT_DR / ENTERED_AMT_DR)
695 , DECODE(NVL(ENTERED_AMT_CR, 0), 0, 1, ACCOUNTED_AMT_CR / ENTERED_AMT_CR)));
696 IF g_debug = FND_API.G_TRUE THEN
697 OPEN l_headers_cursor;
698 FETCH l_headers_cursor BULK COLLECT INTO l_headers_tab;
699 l_headers_count := l_headers_cursor%ROWCOUNT;
700 CLOSE l_headers_cursor;
701 OPEN l_lines_cursor;
702 FETCH l_lines_cursor BULK COLLECT INTO l_lines_tab;
703 l_lines_count := l_lines_cursor%ROWCOUNT;
704 CLOSE l_lines_cursor;
705 OPEN l_results_cursor;
706 FETCH l_results_cursor BULK COLLECT INTO l_results_tab;
707 l_results_count := l_results_cursor%ROWCOUNT;
708 CLOSE l_results_cursor;
709 OPEN l_errors_cursor;
710 FETCH l_errors_cursor BULK COLLECT INTO l_errors_tab;
711 l_errors_count := l_errors_cursor%ROWCOUNT;
712 CLOSE l_errors_cursor;
713
714 ins_t_tables_final_auto(l_headers_tab, l_lines_tab, l_results_tab, l_errors_tab,
715 l_headers_count, l_lines_count, l_results_count, l_errors_count);
716 ELSE
717 SELECT COUNT(*) INTO l_errors_count
718 FROM fun_bal_errors_gt;
719 END IF;
720
721 IF l_errors_count > 0 THEN
722 l_return_val := FND_API.G_RET_STS_ERROR;
723 END IF;
724
725 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
726 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_finalize.end', 'end');
727 END IF;
728 RETURN l_return_val;
729 END do_finalize;
730
731
732 FUNCTION do_inter_bal RETURN VARCHAR2 IS
733 l_le_bsv_map_tab inter_le_bsv_map_tab_type;
734 l_inter_int_tab inter_int_tab_type;
735 CURSOR l_le_bsv_map_cursor IS
736 SELECT * FROM fun_bal_le_bsv_map_gt;
737 CURSOR l_inter_int_cursor IS
738 SELECT * FROM fun_bal_inter_int_gt;
739 l_le_bsv_map_count NUMBER;
740 l_inter_int_count NUMBER;
741
742 BEGIN
743 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
744 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.begin', 'begin');
745 END IF;
746
747 /* Replaced by sql below from performance review
748 INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date)
749 SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date
750 FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger,
751 gl_ledger_configurations config
752 WHERE hdrs.group_id = lines.group_id
753 AND hdrs.ledger_id = ledger.ledger_id
754 AND ledger.configuration_id = config.configuration_id
755 AND ledger.bal_seg_value_option_code = 'I';
756 -- Only possible scenario to perform Intercompany is when bal_seg_value_option_code is 'I' and acct_env_code is 'SHARED'
757
758 -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT
759
760 -- Legal entity can only be either null or has a specific value
761 UPDATE fun_bal_le_bsv_map_gt bsv_le_map
762 SET le_id =
763 NVL((SELECT vals.legal_entity_id
764 FROM gl_ledger_le_bsv_specific_v vals
765 WHERE bsv_le_map.bal_seg_val = vals.segment_value
766 AND (TRUNC(bsv_le_map.gl_date) BETWEEN TRUNC(NVL(vals.start_date, bsv_le_map.gl_date)) AND
767 TRUNC(NVL(vals.end_date, bsv_le_map.gl_date)))
768 AND bsv_le_map.ledger_id = vals.ledger_id
769 ), -99);
770
771 */
772
773 -- Bug 3310453
774 INSERT INTO fun_bal_errors_gt(error_code, group_id, bal_seg_val)
775 SELECT 'FUN_BSV_INVALID', main.group_id, main.bal_seg_val
776 FROM (SELECT DISTINCT hdrs.group_id, lines.bal_seg_val, hdrs.gl_date, hdrs.ledger_id
777 FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger
778 WHERE hdrs.group_id = lines.group_id
779 AND hdrs.ledger_id = ledger.ledger_id(+)
780 AND ledger.bal_seg_value_option_code = 'I') main
781 WHERE main.bal_seg_val NOT IN (SELECT vals.segment_value
782 FROM gl_ledger_le_bsv_specific_v vals
783 WHERE main.ledger_id = vals.ledger_id
784 AND TRUNC(main.gl_date) BETWEEN
785 TRUNC(NVL(vals.start_date, main.gl_date)) AND
786 TRUNC(NVL(vals.end_date, main.gl_date)));
787 -- Bug 3310453
788 UPDATE fun_bal_headers_gt headers
789 SET status = 'ERROR',
790 error_code = 'FUN_BSV_INVALID'
791 WHERE EXISTS (SELECT 'Invalid BSV Error'
792 FROM FUN_BAL_ERRORS_GT errors
793 WHERE headers.group_id = errors.group_id
794 AND error_code IN ('FUN_BSV_INVALID'))
795 AND headers.status = 'OK';
796
797 -- Select the distinct combination of GROUP_ID, LEDGER_ID and BAL_SEG_VAL into
798 -- FUN_BAL_LE_BSV_MAP_GT. We are only inserting the journals with ledgers in shared
799 -- mode configuration, as intercompany balancing should only be performed in shared mode.
800 -- Doing so should decrease the amount of processing time required at a later stage.
801
802 -- Only possible scenario to perform Intercompany is when bal_seg_value_option_code is 'I' and acct_env_code is 'SHARED'
803 -- Update legal entity column in FUN_BAL_LE_BSV_MAP_GT
804 -- Legal entity can only be either null or has a specific value
805
806 --ER: 8588074
807 --Bug: 9183927
808
809 INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date, le_id, je_source_name, je_category_name)
810 SELECT main.group_id, main.ledger_id, main.bal_seg_val, main.gl_date, NVL(vals.legal_entity_id, -99),
811 main.je_source_name, main.je_category_name
812 FROM (SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date, hdrs.je_source_name, hdrs.je_category_name
813 FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines, gl_ledgers ledger,
814 gl_ledger_configurations config
815 WHERE hdrs.status = 'OK' -- Bug 3310453
816 AND hdrs.group_id = lines.group_id
817 AND hdrs.ledger_id = ledger.ledger_id(+)
818 AND ledger.configuration_id = config.configuration_id
819 AND ledger.configuration_id <> -2 -- Bug 3271446
820 AND ledger.bal_seg_value_option_code = 'I') main,
821 gl_ledger_le_bsv_specific_v vals
822 WHERE main.bal_seg_val = vals.segment_value(+)
823 AND (TRUNC(main.gl_date) BETWEEN TRUNC(NVL(vals.start_date, main.gl_date)) AND
824 TRUNC(NVL(vals.end_date, main.gl_date)))
825 AND main.ledger_id = vals.ledger_id(+);
826
827 --Bug: 12834036.
828 UPDATE fun_bal_headers_gt headers
829 SET (le_id, le_count) =
830 (SELECT MIN(le_bsv_map.le_id), SUM(COUNT(DISTINCT le_bsv_map.le_id))
831 FROM fun_bal_le_bsv_map_gt le_bsv_map
832 WHERE headers.group_id = le_bsv_map.group_id
833 AND le_bsv_map.le_id <> -99
834 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id);
835
836 UPDATE fun_bal_headers_gt headers
837 SET status = DECODE(le_id, NULL, status, 'ERROR'),
838 error_code = DECODE(le_id, NULL, error_code, 'FUN_INTER_BSV_NOT_ASSIGNED'),
839 unmapped_bsv_le_id = -99
840 WHERE EXISTS (SELECT 'Unmapped BSV exists'
841 FROM fun_bal_le_bsv_map_gt le_bsv_map
842 WHERE le_bsv_map.group_id = headers.group_id
843 AND le_bsv_map.le_id = -99);
844
845
846 -- Error out if error out bsv is provided and either one of the following conditions are true:
847 -- I) more than one le count
848 -- II) one le count and non-mapped count
849 -- III) the clearing BSV entered does not belong to the LE or to the ledger
850 -- or one le count and non-mapped count
851 -- IV) le_count IS NULL and unmapped_bsv_le_id IS NULL if not shared configuration
852 -- and BSV validation set to specific
853 UPDATE fun_bal_headers_gt headers
854 SET status = 'ERROR',
855 error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR'
856 WHERE headers.status = 'OK'
857 AND headers.clearing_bsv IS NOT NULL
858 AND NOT (headers.le_count IS NULL AND headers.unmapped_bsv_le_id IS NULL) -- Bug 3278912
859 AND (headers.le_count > 1
860 OR
861 (headers.le_count = 1 AND headers.unmapped_bsv_le_id = -99)
862 OR
863 (headers.le_id IS NOT NULL
864 AND NOT EXISTS (SELECT 'BSV belongs to the LE'
865 FROM gl_ledger_le_bsv_specific_v vals
866 WHERE vals.segment_value = headers.clearing_bsv
867 AND vals.ledger_id = headers.ledger_id
868 AND vals.legal_entity_id = headers.le_id))
869 OR
870 (headers.le_id IS NULL
871 AND EXISTS (SELECT 'BSV belongs to Ledger'
872 FROM gl_ledger_le_bsv_specific_v vals
873 WHERE vals.segment_value = headers.clearing_bsv
874 AND vals.ledger_id = headers.ledger_id
875 AND vals.legal_entity_id IS NOT NULL))); -- Bug 3278912
876
877 -- Bug 3310453
878 UPDATE fun_bal_headers_gt hdrs
879 SET status = 'ERROR',
880 error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR'
881 WHERE hdrs.status = 'OK'
882 AND hdrs.clearing_bsv IS NOT NULL
883 AND NOT (hdrs.ledger_id IN (SELECT ledgers.ledger_id
884 FROM gl_ledgers ledgers
885 WHERE ledgers.bal_seg_value_option_code = 'A')
886 OR
887 hdrs.clearing_bsv IN (SELECT vals.segment_value
888 FROM gl_ledger_le_bsv_specific_v vals
889 WHERE hdrs.ledger_id = vals.ledger_id
890 AND TRUNC(hdrs.gl_date) BETWEEN
891 TRUNC(NVL(vals.start_date, hdrs.gl_date))
892 AND
893 TRUNC(NVL(vals.end_date, hdrs.gl_date))));
894
895 INSERT INTO fun_bal_errors_gt(error_code, group_id, clearing_bsv)
896 SELECT 'FUN_INTRA_OVERRIDE_BSV_ERROR', hdrs.group_id, hdrs.clearing_bsv
897 FROM fun_bal_headers_gt hdrs
898 WHERE hdrs.error_code = 'FUN_INTRA_OVERRIDE_BSV_ERROR';
899
900 INSERT INTO fun_bal_errors_gt(error_code, group_id, bal_seg_val)
901 SELECT 'FUN_INTER_BSV_NOT_ASSIGNED', hdrs.group_id, le_bsv_map.bal_seg_val
902 FROM fun_bal_headers_gt hdrs, fun_bal_le_bsv_map_gt le_bsv_map
903 WHERE hdrs.group_id = le_bsv_map.group_id
904 AND hdrs.error_code = 'FUN_INTER_BSV_NOT_ASSIGNED'
905 AND le_bsv_map.le_id = -99;
906
907 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LE_BSV_MAP_GT');
908
909 IF g_debug = FND_API.G_TRUE THEN
910 OPEN l_le_bsv_map_cursor;
911 FETCH l_le_bsv_map_cursor BULK COLLECT INTO l_le_bsv_map_tab;
912 l_le_bsv_map_count := l_le_bsv_map_cursor%ROWCOUNT;
913 CLOSE l_le_bsv_map_cursor;
914 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
915 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'begin');
916 END IF;
917 ins_t_tables_inter_1_auto(l_le_bsv_map_tab, l_le_bsv_map_count);
918 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
919 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.ins_t_tables_inter_1_auto', 'end');
920 END IF;
921
922 END IF;
923
924
925 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
926 WHERE group_id = (SELECT group_id
927 FROM fun_bal_headers_gt headers
928 WHERE headers.status = 'ERROR'
929 AND le_bsv_map.group_id = headers.group_id);
930
931 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
932 WHERE EXISTS (SELECT 'LE already balanced'
933 FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
934 WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
935 AND le_bsv_map_del.le_id = le_bsv_map.le_id
936 AND le_bsv_map.group_id = lines.group_id
937 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
938 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
939 HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) =
940 SUM(NVL(lines.accounted_amt_cr, 0)))
941 AND
942 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0))=
943 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)));
944
945
946 -- Determine driving_dr_le_id, intercompany mode
947 UPDATE fun_bal_headers_gt hdrs
948 SET (driving_dr_le_id, intercompany_mode) =
949 (SELECT MIN(le_bsv_map.le_id),
950 SUM(COUNT(DISTINCT(le_bsv_map.le_id)))
951 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
952 WHERE hdrs.group_id = le_bsv_map.group_id
953 AND le_bsv_map.group_id = lines.group_id
954 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
955 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
956 HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) >
957 SUM(NVL(lines.accounted_amt_cr, 0)))
958 OR
959 ((SUM(NVL(lines.accounted_amt_dr, 0)) =
960 SUM(NVL(lines.accounted_amt_cr, 0))) AND
961 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) >
962 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0))))
963 WHERE status = 'OK';
964
965
966 -- Deleting the records that do not require intercompany balancing.
967 -- Deleting these records first should make the code perform better,
968 -- as there won't be any more join to these lines.
969 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
970 WHERE EXISTS
971 (SELECT 'Intercompany balancing is not required'
972 FROM fun_bal_headers_gt hdrs
973 WHERE le_bsv_map.group_id = hdrs.group_id
974 AND hdrs.status = 'OK'
975 AND hdrs.intercompany_mode IS NULL);
976
977 -- Determine driving_cr_le_id, intercompany_mode
978 UPDATE fun_bal_headers_gt hdrs
979 SET (driving_cr_le_id, intercompany_mode) =
980 (SELECT MIN(le_bsv_map.le_id),
981 DECODE(SUM(COUNT(DISTINCT(le_bsv_map.le_id))), 1,
982 DECODE(hdrs.intercompany_mode, 1, 1, 3),
983 DECODE(hdrs.intercompany_mode, 1, 2, 4))
984 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
985 WHERE hdrs.group_id = le_bsv_map.group_id
986 AND le_bsv_map.group_id = lines.group_id
987 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
988 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id
989 HAVING (SUM(NVL(lines.accounted_amt_cr, 0)) >
990 SUM(NVL(lines.accounted_amt_dr, 0)))
991 OR
992 ((SUM(NVL(lines.accounted_amt_dr, 0)) =
993 SUM(NVL(lines.accounted_amt_cr, 0))) AND
994 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) >
995 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))))
996 WHERE status = 'OK';
997
998 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
999 WHERE EXISTS (SELECT 'No Driving DR LE or Driving CR LE'
1000 FROM fun_bal_headers_gt headers
1001 WHERE headers.group_id = le_bsv_map.group_id
1002 AND headers.status = 'OK'
1003 AND (headers.driving_dr_le_id IS NULL
1004 OR
1005 headers.driving_cr_le_id IS NULL));
1006
1007 -- Insert into FUN_BAL_INTER_INT2_GT with all lines that require Intercompany balancing
1008 -- Changed the query for bug 9433610
1009 --Bug: 11657458
1010 INSERT INTO FUN_BAL_INTER_INT2_GT
1011 (GROUP_ID,
1012 LEDGER_ID,
1013 GL_DATE,
1014 STATUS,
1015 DRIVING_DR_LE_ID,
1016 DRIVING_CR_LE_ID,
1017 INTERCOMPANY_MODE,
1018 LE_ID,
1019 ENTERED_CURRENCY_CODE,
1020 EXCHANGE_DATE,
1021 EXCHANGE_RATE,
1022 EXCHANGE_RATE_TYPE,
1023 ACCOUNTED_AMT_CR,
1024 ACCOUNTED_AMT_DR,
1025 ENTERED_AMT_CR,
1026 ENTERED_AMT_DR,
1027 BAL_SEG_COLUMN_NAME,
1028 LINE_LE_BSV,
1029 TYPE)
1030 SELECT GROUP_ID,
1031 LEDGER_ID,
1032 GL_DATE,
1033 STATUS,
1034 DRIVING_DR_LE_ID,
1035 DRIVING_CR_LE_ID,
1036 INTERCOMPANY_MODE,
1037 LE_ID,
1038 ENTERED_CURRENCY_CODE,
1039 SYSDATE,
1040 EXCHANGE_RATE,
1041 'User',
1042 ACCOUNTED_AMT_CR,
1043 ACCOUNTED_AMT_DR,
1044 ENTERED_AMT_CR,
1045 ENTERED_AMT_DR,
1046 BAL_SEG_COLUMN_NAME,
1047 BAL_SEG_VAL,
1048 TYPE
1049 FROM (SELECT HDRS.GROUP_ID GROUP_ID,
1050 HDRS.LEDGER_ID LEDGER_ID,
1051 HDRS.GL_DATE GL_DATE,
1052 HDRS.STATUS STATUS,
1053 HDRS.DRIVING_DR_LE_ID DRIVING_DR_LE_ID,
1054 HDRS.DRIVING_CR_LE_ID DRIVING_CR_LE_ID,
1055 HDRS.INTERCOMPANY_MODE INTERCOMPANY_MODE,
1056 LE_BSV_MAP.LE_ID LE_ID,
1057 LINES.ENTERED_CURRENCY_CODE ENTERED_CURRENCY_CODE,
1058 MAX(LINES.EXCHANGE_DATE) EXCHANGE_DATE,
1059 DECODE(MAX(LINES.EXCHANGE_RATE), NULL, NULL, 1) EXCHANGE_RATE, --Bug: 12887806
1060 MAX(LINES.EXCHANGE_RATE_TYPE) EXCHANGE_RATE_TYPE,
1061 DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1062 1, ABS(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1063 NULL) ACCOUNTED_AMT_CR,
1064 DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1065 -1, ABS(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1066 NULL) ACCOUNTED_AMT_DR,
1067 DECODE(SIGN(SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0))),
1068 1, ABS(SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0))),
1069 NULL) ENTERED_AMT_CR,
1070 DECODE(SIGN(SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0))),
1071 -1, ABS(SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0))),
1072 NULL) ENTERED_AMT_DR,
1073 HDRS.BAL_SEG_COLUMN_NAME BAL_SEG_COLUMN_NAME,
1074 LINES.BAL_SEG_VAL BAL_SEG_VAL,
1075 DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1076 1, 'C',
1077 -1, 'D',
1078 0, DECODE(SIGN((( SUM(NVL(LINES.ENTERED_AMT_CR, 0)) - SUM(NVL(LINES.ENTERED_AMT_DR, 0)) )) -
1079 ( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) )), 1, 'C', 'D')) TYPE
1080 FROM FUN_BAL_LE_BSV_MAP_GT LE_BSV_MAP,
1081 FUN_BAL_LINES_GT LINES,
1082 FUN_BAL_HEADERS_GT HDRS
1083 WHERE HDRS.GROUP_ID = LINES.GROUP_ID
1084 AND LINES.GROUP_ID = LE_BSV_MAP.GROUP_ID
1085 AND LINES.BAL_SEG_VAL = LE_BSV_MAP.BAL_SEG_VAL
1086 AND HDRS.INTERCOMPANY_MODE IN ( 1, 2, 3, 4 )
1087 AND HDRS.STATUS = 'OK'
1088 GROUP BY HDRS.GROUP_ID,
1089 HDRS.LEDGER_ID,
1090 HDRS.GL_DATE,
1091 HDRS.STATUS,
1092 HDRS.DRIVING_DR_LE_ID,
1093 HDRS.DRIVING_CR_LE_ID,
1094 HDRS.INTERCOMPANY_MODE,
1095 LE_BSV_MAP.LE_ID,
1096 LINES.ENTERED_CURRENCY_CODE,
1097 -- LINES.EXCHANGE_DATE,
1098 -- lines.exchange_rate,
1099 -- LINES.EXCHANGE_RATE_TYPE,
1100 HDRS.BAL_SEG_COLUMN_NAME,
1101 HDRS.INTERCOMPANY_COLUMN_NUMBER,
1102 LINES.BAL_SEG_VAL
1103 HAVING SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) <> SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))
1104 OR ( SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) = SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))
1105 AND SUM(DECODE(LINES.EXCHANGE_RATE, NULL, NVL(LINES.ENTERED_AMT_CR, 0),
1106 0)) <> SUM(DECODE(LINES.EXCHANGE_RATE, NULL, NVL(LINES.ENTERED_AMT_DR, 0),
1107 0)) ));
1108
1109
1110 -- Balancing API changes, Feb 22 2005, Start
1111 -- We now need to find the the payables and receivables account using
1112 -- the Legal Entity and BSV value. Initially we found the account using
1113 -- only the LE id.
1114 -- Find out the balancing segment values for the dr le id
1115 -- This will set the value correctly where mode is 1 : 1 or 1 : M
1116 --Bug:12554299. Implemented the changes to handle the case for exchange gain/loss.
1117
1118 delete fun_bal_inter_int3_gt;
1119
1120 INSERT INTO fun_bal_inter_int3_gt(
1121 GROUP_ID,
1122 LEDGER_ID,
1123 LE_ID,
1124 ACCOUNTED_AMT_CR,
1125 ACCOUNTED_AMT_DR,
1126 TYPE,
1127 DRIVING_DR_LE_BSV,
1128 DRIVING_CR_LE_BSV,
1129 LINE_LE_BSV)
1130 SELECT
1131 GROUP_ID,
1132 LEDGER_ID,
1133 LE_ID,
1134 sum(ACCOUNTED_AMT_CR ),
1135 sum(ACCOUNTED_AMT_DR ),
1136 DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1137 1, 'C',
1138 -1, 'D',
1139 0, DECODE(SIGN((( SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) )) -
1140 ( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) )), 1, 'C', 'D')) type,
1141
1142 null DR_LE_BSV,
1143 null CR_LE_BSV,
1144 LINE_LE_BSV
1145 FROM fun_bal_inter_int2_gt lines
1146 GROUP BY
1147 GROUP_ID,
1148 LEDGER_ID,
1149 LE_ID,
1150 LINE_LE_BSV;
1151
1152 UPDATE fun_bal_inter_int3_gt upd
1153 SET driving_dr_le_bsv =
1154 DECODE(upd.type, 'D', upd.line_le_bsv,
1155 'C', DECODE((SELECT COUNT(DISTINCT cmp.line_le_bsv)
1156 FROM fun_bal_inter_int3_gt cmp
1157 WHERE upd.group_id = cmp.group_id
1158 AND cmp.type='D'
1159 ),
1160 1, (SELECT DISTINCT cmp1.line_le_bsv
1161 FROM fun_bal_inter_int3_gt cmp1
1162 WHERE upd.group_id = cmp1.group_id
1163 AND cmp1.type='D'
1164 ),'Many'));
1165
1166
1167 UPDATE fun_bal_inter_int3_gt upd
1168 SET driving_cr_le_bsv =
1169 DECODE(upd.type, 'C', upd.line_le_bsv,
1170 'D', DECODE((SELECT COUNT(DISTINCT cmp.line_le_bsv)
1171 FROM fun_bal_inter_int3_gt cmp
1172 WHERE upd.group_id = cmp.group_id
1173 AND cmp.type='C'
1174 ),
1175 1, (SELECT DISTINCT cmp1.line_le_bsv
1176 FROM fun_bal_inter_int3_gt cmp1
1177 WHERE upd.group_id = cmp1.group_id
1178 AND cmp1.type='C'
1179 ),'Many'));
1180
1181 update fun_bal_inter_int2_gt t2
1182 set driving_dr_le_bsv = (select driving_dr_le_bsv
1183 from fun_bal_inter_int3_gt dr_t3
1184 where dr_t3.group_id = t2.group_id
1185 and dr_t3.le_id = t2.le_id
1186 and dr_t3.line_le_bsv = t2.line_le_bsv
1187 );
1188
1189 update fun_bal_inter_int2_gt t2
1190 set driving_cr_le_bsv = (select driving_cr_le_bsv
1191 from fun_bal_inter_int3_gt dr_t3
1192 where dr_t3.group_id = t2.group_id
1193 and dr_t3.le_id = t2.le_id
1194 and dr_t3.line_le_bsv = t2.line_le_bsv
1195 );
1196
1197 --updating the driving_dr_le_bsv ,driving_cr_le_bsv for the Many to many case #9392684
1198 Update fun_bal_inter_int2_gt
1199 SET (driving_dr_le_bsv, driving_cr_le_bsv)=(Select 'Many','Many' from Dual)
1200 where group_id IN (Select upd1.group_id from (select count(DISTINCT inter_int2.line_le_bsv) count1, group_id from fun_bal_inter_int2_gt inter_int2
1201 where type='D'
1202 GROUP by group_id) upd1,
1203 (select count(DISTINCT inter_int2.line_le_bsv) count1, group_id from fun_bal_inter_int2_gt inter_int2
1204 WHERE type='C'
1205 GROUP by group_id) upd2
1206 WHERE upd1.group_id=upd2.group_id
1207 AND upd1.count1 > 1
1208 AND upd2.count1 > 1) ;
1209
1210 --change in Driving Debit and driving credit leid
1211
1212 UPDATE fun_bal_inter_int2_gt upd1
1213 SET driving_dr_le_id = DECODE(upd1.TYPE, 'D', upd1.LE_ID, DECODE((select count(DISTINCT upd2.le_id ) from fun_bal_inter_int2_gt UPD2
1214 WHERE type = 'D' AND upd2.group_id=upd1.group_id), 1, (SELECT DISTINCT LE_ID FROM fun_bal_inter_int2_gt upd3 WHERE TYPE = 'D' AND upd3.group_id= upd1.group_id), -1))
1215 WHERE intercompany_mode IN (1,2,3)
1216 AND ( driving_dr_le_bsv <>'Many'
1217 OR driving_cr_le_bsv <>'Many');
1218
1219
1220 UPDATE fun_bal_inter_int2_gt upd1
1221 SET driving_cr_le_id = DECODE(upd1.TYPE, 'C', upd1.LE_ID, DECODE((select count(DISTINCT upd2.le_id) from fun_bal_inter_int2_gt UPD2
1222 WHERE type = 'C' AND upd2.group_id=upd1.group_id), 1, (SELECT DISTINCT LE_ID FROM fun_bal_inter_int2_gt upd3 WHERE TYPE = 'C' AND upd3.group_id= upd1.group_id), -1))
1223 WHERE intercompany_mode IN (1,2,3)
1224 AND ( driving_dr_le_bsv <>'Many'
1225 OR driving_cr_le_bsv <>'Many');
1226
1227 -- marking the lines to be deletd which need intracompany balancing #9392684
1228 UPDATE fun_bal_inter_int2_gt inter_int1 SET status='DEL'
1229 WHERE (inter_int1.type, inter_int1.le_id,inter_int1.group_id) IN
1230 (SELECT DECODE(SIGN(NVL(cr_sum, 0) - NVL(dr_sum, 0)), 1, 'D', -1, 'C', 'X'),
1231 le_id,
1232 group_id
1233 FROM
1234 (SELECT SUM(entered_amt_cr) cr_sum,
1235 SUM(entered_amt_dr) dr_sum,
1236 le_id,
1237 group_id
1238 FROM fun_bal_inter_int2_gt inter_int2
1239 WHERE (inter_int2.le_id,inter_int2.group_id) IN
1240 (SELECT inter_int3.le_id,
1241 inter_int3.group_id
1242 FROM fun_bal_inter_int2_gt inter_int3
1243 WHERE inter_int3.intercompany_mode IN (1,2,3)
1244 AND ( inter_int3.driving_dr_le_bsv <>'Many'
1245 OR inter_int3.driving_cr_le_bsv <>'Many')
1246 HAVING COUNT(*) > 1
1247 GROUP BY inter_int3.group_id,
1248 inter_int3.le_id
1249 )
1250 HAVING SUM(NVL(entered_amt_cr,0)) <> 0
1251 AND SUM(NVL(entered_amt_dr,0)) <> 0
1252 GROUP BY le_id,
1253 group_id
1254 )
1255 ) ;
1256 -- Bug: 12354478
1257
1258 UPDATE fun_bal_inter_int2_gt inter_int2
1259 SET inter_int2.status='DEL'
1260 where
1261 not exists (select 1 from fun_bal_inter_int2_gt inter_int3
1262 where inter_int2.group_id = inter_int3.group_id
1263 and inter_int2.le_id <> inter_int3.le_id
1264 and inter_int2.type <> inter_int3.type)
1265 AND inter_int2.intercompany_mode IN (1,2,3)
1266 AND ( inter_int2.driving_dr_le_bsv <>'Many'
1267 OR inter_int2.driving_cr_le_bsv <>'Many');
1268 -- End Bug: 12354478
1269
1270 /*UPDATE fun_bal_inter_int2_gt inter_int1 SET status='DEL'
1271 WHERE (inter_int1.type, inter_int1.le_id,inter_int1.group_id) IN
1272 (SELECT DECODE(SIGN(NVL(cr_sum, 0) - NVL(dr_sum, 0)), 1, 'D', -1, 'C', 'X'),
1273 le_id,
1274 group_id
1275 FROM
1276 (SELECT SUM(entered_amt_cr) cr_sum,
1277 SUM(entered_amt_dr) dr_sum,
1278 le_id,
1279 group_id
1280 FROM fun_bal_inter_int2_gt inter_int2
1281 WHERE (inter_int2.le_id,inter_int2.group_id) IN
1282 (SELECT le_id,
1283 group_id
1284 FROM fun_bal_inter_int2_gt
1285 WHERE intercompany_mode IN (1,2,3)
1286 AND ((driving_cr_le_bsv ='Many'
1287 AND driving_dr_le_bsv <>'Many')
1288 OR (driving_dr_le_bsv ='Many'
1289 AND driving_cr_le_bsv <>'Many'))
1290 HAVING COUNT(*) > 1
1291 GROUP BY group_id,
1292 le_id
1293 )
1294 GROUP BY le_id,
1295 group_id
1296 )
1297 ) ;*/
1298
1299 --updating the entered dr and cr values of the lines that do not need to be deleted.#9392684
1300 UPDATE fun_bal_inter_int2_gt inter_int1
1301 SET
1302 (
1303 inter_int1.entered_amt_cr
1304 )
1305 =
1306 (SELECT NVL(inter_int1.entered_amt_cr,0) - NVL(SUM(NVL(inter_int2.entered_amt_dr,0)),0)
1307 FROM fun_bal_inter_int2_gt inter_int2
1308 WHERE inter_int2.type='D'
1309 AND inter_int2.le_id= inter_int1.le_id
1310 AND inter_int2.group_id = inter_int1.group_id
1311 group by inter_int2.group_id)
1312 WHERE inter_int1.type='C'
1313 AND 'D'=(Select DISTINCT type from fun_bal_inter_int2_gt inter_int3
1314 WHERE inter_int3.group_id= inter_int1.group_id
1315 AND inter_int3.le_id= inter_int1.le_id
1316 AND inter_int3.status='DEL');
1317
1318 --9692257
1319 UPDATE fun_bal_inter_int2_gt inter_int1
1320 SET
1321 (
1322 inter_int1.accounted_amt_cr
1323 )
1324 =
1325 (SELECT NVL(inter_int1.accounted_amt_cr,0) - NVL(SUM(NVL(inter_int2.accounted_amt_dr,0)),0)
1326 FROM fun_bal_inter_int2_gt inter_int2
1327 WHERE inter_int2.type='D'
1328 AND inter_int2.le_id= inter_int1.le_id
1329 AND inter_int2.group_id = inter_int1.group_id
1330 )
1331 WHERE inter_int1.type='C'
1332 AND 'D'=(Select DISTINCT type from fun_bal_inter_int2_gt inter_int3
1333 WHERE inter_int3.group_id= inter_int1.group_id
1334 AND inter_int3.le_id= inter_int1.le_id
1335 AND inter_int3.status='DEL');
1336
1337
1338 -- End 9692257
1339
1340 UPDATE fun_bal_inter_int2_gt inter_int1
1341 SET
1342 (
1343 inter_int1.entered_amt_dr
1344 )
1345 =
1346 (SELECT NVL(inter_int1.entered_amt_dr,0) - NVL(SUM(NVL(inter_int2.entered_amt_cr,0)),0)
1347 FROM fun_bal_inter_int2_gt inter_int2
1348 WHERE inter_int2.type='C'
1349 AND inter_int2.le_id= inter_int1.le_id
1350 AND inter_int2.group_id = inter_int1.group_id)
1351 WHERE inter_int1.type='D'
1352 AND 'C'=(Select distinct type from fun_bal_inter_int2_gt inter_int3
1353 WHERE inter_int3.group_id= inter_int1.group_id
1354 AND inter_int3.le_id= inter_int1.le_id
1355 AND inter_int3.status='DEL');
1356
1357 -- 9692257
1358 UPDATE fun_bal_inter_int2_gt inter_int1
1359 SET
1360 (
1361 inter_int1.accounted_amt_dr
1362 )
1363 =
1364 (SELECT NVL(inter_int1.accounted_amt_dr,0) - NVL(SUM(NVL(inter_int2.accounted_amt_cr,0)),0)
1365 FROM fun_bal_inter_int2_gt inter_int2
1366 WHERE inter_int2.type='C'
1367 AND inter_int2.le_id= inter_int1.le_id
1368 AND inter_int2.group_id = inter_int1.group_id)
1369 WHERE inter_int1.type='D'
1370 AND 'C'=(Select distinct type from fun_bal_inter_int2_gt inter_int3
1371 WHERE inter_int3.group_id= inter_int1.group_id
1372 AND inter_int3.le_id= inter_int1.le_id
1373 AND inter_int3.status='DEL');
1374
1375 --End 9692257
1376
1377 --Bug:11823507, 12554299
1378 --Deleting those lines which do not need intercompany balancing #9392684
1379
1380 DELETE from fun_bal_inter_int2_gt
1381 where status='DEL'
1382 OR (NVL(entered_amt_cr,0)=0
1383 AND NVL(entered_amt_dr,0)=0
1384 AND NVL(accounted_amt_cr,0)=0
1385 AND NVL(accounted_amt_dr,0)=0
1386 );
1387
1388 --Deleting those BSVs from the fun_bal_le_bsv_map_gt which do not need intercompany balancing #9392684
1389 DELETE from fun_bal_le_bsv_map_gt le_bsv_map
1390 WHERE bal_seg_val NOT IN (Select line_le_bsv from fun_bal_inter_int2_gt
1391 WHERE group_id= le_bsv_map.group_id
1392 AND le_id= le_bsv_map.le_id
1393 ) ;
1394
1395 /*
1396 --updating the driving_dr_le_id, driving_dr_le_bsv and pay_bsv for the 1:M and M:1 case #9392684
1397 UPDATE fun_bal_inter_int2_gt upd
1398 SET (driving_dr_le_id, driving_dr_le_bsv,pay_bsv) =
1399 (SELECT DISTINCT le_bsv_map.le_id,le_bsv_map.bal_seg_val,le_bsv_map.bal_seg_val
1400 FROM fun_bal_le_bsv_map_gt le_bsv_map
1401 WHERE le_bsv_map.group_id= upd.group_id
1402 AND upd.line_le_bsv= le_bsv_map.bal_seg_val
1403 )
1404 WHERE intercompany_mode IN (1,2,3)
1405 AND upd.driving_dr_le_bsv='Many'
1406 AND upd.driving_cr_le_bsv <>'Many'
1407 AND upd.driving_cr_le_bsv<>upd.line_le_bsv
1408 AND status = 'OK'
1409 AND upd.type='D';
1410 --updating the driving_cr_le_id, driving_cr_le_bsv and rec_bsv for the 1:M and M:1 case #9392684
1411 UPDATE fun_bal_inter_int2_gt upd
1412 SET (driving_cr_le_id, driving_cr_le_bsv,rec_bsv) =
1413 (SELECT DISTINCT le_bsv_map.le_id,le_bsv_map.bal_seg_val,le_bsv_map.bal_seg_val
1414 FROM fun_bal_le_bsv_map_gt le_bsv_map
1415 WHERE le_bsv_map.group_id= upd.group_id
1416 AND upd.line_le_bsv= le_bsv_map.bal_seg_val
1417 )
1418 WHERE intercompany_mode IN (1,2,3)
1419 AND upd.driving_cr_le_bsv='Many'
1420 AND upd.driving_dr_le_bsv <>'Many'
1421 AND upd.driving_dr_le_bsv<>upd.line_le_bsv
1422 AND status = 'OK'
1423 AND upd.type='C';
1424
1425
1426 --change in bsv level mode:
1427 UPDATE fun_bal_inter_int2_gt upd1
1428 SET driving_dr_le_bsv= DECODE(upd1.driving_dr_le_id,-1,'Many',
1429 le_id, line_le_bsv,
1430 (select bal_seg_val
1431 from fun_bal_le_bsv_map_gt
1432 where group_id=upd1.group_id
1433 and le_id= upd1.driving_dr_le_id)),
1434 driving_cr_le_bsv= DECODE(upd1.driving_cr_le_id,-1,'Many',
1435 le_id, line_le_bsv,
1436 (select bal_seg_val
1437 from fun_bal_le_bsv_map_gt
1438 where group_id=upd1.group_id
1439 and le_id= upd1.driving_cr_le_id))
1440 Where intercompany_mode in (1,2,3)
1441 AND ( driving_dr_le_bsv <>'Many'
1442 OR driving_cr_le_bsv <>'Many'); */
1443
1444 --Bug:12554299. Implemented the changes to handle the case for exchange gain/loss.
1445 delete fun_bal_inter_int3_gt;
1446
1447 INSERT INTO fun_bal_inter_int3_gt(
1448 GROUP_ID,
1449 LEDGER_ID,
1450 LE_ID,
1451 ACCOUNTED_AMT_CR,
1452 ACCOUNTED_AMT_DR,
1453 TYPE,
1454 DRIVING_DR_LE_BSV,
1455 DRIVING_CR_LE_BSV,
1456 LINE_LE_BSV)
1457 SELECT
1458 GROUP_ID,
1459 LEDGER_ID,
1460 LE_ID,
1461 sum(ACCOUNTED_AMT_CR ),
1462 sum(ACCOUNTED_AMT_DR ),
1463 DECODE(SIGN(SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0))),
1464 1, 'C',
1465 -1, 'D',
1466 0, DECODE(SIGN((( SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) )) -
1467 ( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0)) )), 1, 'C', 'D')) type,
1468
1469 null DR_LE_BSV,
1470 null CR_LE_BSV,
1471 LINE_LE_BSV
1472 FROM fun_bal_inter_int2_gt lines
1473 GROUP BY
1474 GROUP_ID,
1475 LEDGER_ID,
1476 LE_ID,
1477 LINE_LE_BSV;
1478
1479 UPDATE fun_bal_inter_int3_gt upd
1480 SET driving_dr_le_bsv =
1481 DECODE(upd.type, 'D', upd.line_le_bsv,
1482 'C', DECODE((SELECT COUNT(DISTINCT cmp.line_le_bsv)
1483 FROM fun_bal_inter_int3_gt cmp
1484 WHERE upd.group_id = cmp.group_id
1485 AND cmp.type='D'
1486 ),
1487 1, (SELECT DISTINCT cmp1.line_le_bsv
1488 FROM fun_bal_inter_int3_gt cmp1
1489 WHERE upd.group_id = cmp1.group_id
1490 AND cmp1.type='D'
1491 ),'Many'));
1492
1493
1494 UPDATE fun_bal_inter_int3_gt upd
1495 SET driving_cr_le_bsv =
1496 DECODE(upd.type, 'C', upd.line_le_bsv,
1497 'D', DECODE((SELECT COUNT(DISTINCT cmp.line_le_bsv)
1498 FROM fun_bal_inter_int3_gt cmp
1499 WHERE upd.group_id = cmp.group_id
1500 AND cmp.type='C'
1501 ),
1502 1, (SELECT DISTINCT cmp1.line_le_bsv
1503 FROM fun_bal_inter_int3_gt cmp1
1504 WHERE upd.group_id = cmp1.group_id
1505 AND cmp1.type='C'
1506 ),'Many'));
1507
1508 update fun_bal_inter_int2_gt t2
1509 set driving_dr_le_bsv = (select driving_dr_le_bsv
1510 from fun_bal_inter_int3_gt dr_t3
1511 where dr_t3.group_id = t2.group_id
1512 and dr_t3.le_id = t2.le_id
1513 and dr_t3.line_le_bsv = t2.line_le_bsv
1514 );
1515
1516 update fun_bal_inter_int2_gt t2
1517 set driving_cr_le_bsv = (select driving_cr_le_bsv
1518 from fun_bal_inter_int3_gt dr_t3
1519 where dr_t3.group_id = t2.group_id
1520 and dr_t3.le_id = t2.le_id
1521 and dr_t3.line_le_bsv = t2.line_le_bsv
1522 );
1523
1524
1525 --updating the driving_dr_le_bsv ,driving_cr_le_bsv for the Many to many case #9392684
1526 Update fun_bal_inter_int2_gt
1527 SET (driving_dr_le_bsv, driving_cr_le_bsv)=(Select 'Many','Many' from Dual)
1528 where group_id IN (Select upd1.group_id from (select count(DISTINCT inter_int2.line_le_bsv) count1, group_id from fun_bal_inter_int2_gt inter_int2
1529 where type='D'
1530 GROUP by group_id) upd1,
1531 (select count(DISTINCT inter_int2.line_le_bsv) count1, group_id from fun_bal_inter_int2_gt inter_int2
1532 WHERE type='C'
1533 GROUP by group_id) upd2
1534 WHERE upd1.group_id=upd2.group_id
1535 AND upd1.count1 > 1
1536 AND upd2.count1 > 1) ;
1537
1538 -- updating the pay_bsv and rec_bsv for one-many and many-one cases
1539 UPDATE fun_bal_inter_int2_gt SET REC_BSV = driving_cr_le_bsv,PAY_BSV = driving_dr_le_bsv
1540 WHERE intercompany_mode IN (1,2,3)
1541 AND driving_dr_le_bsv <>'Many'
1542 OR driving_cr_le_bsv <>'Many';
1543
1544 --Bug: 12354478
1545 UPDATE fun_bal_inter_int2_gt t1
1546 SET driving_dr_le_id = decode(driving_dr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv
1547 where le_bsv.group_id = t1.group_id
1548 and le_bsv.bal_seg_val = t1.driving_dr_le_bsv)),
1549 driving_cr_le_id = decode(driving_cr_le_bsv, 'Many', -1, (select distinct le_id from FUN_BAL_LE_BSV_MAP_GT le_bsv1
1550 where le_bsv1.group_id = t1.group_id
1551 and le_bsv1.bal_seg_val = t1.driving_cr_le_bsv))
1552 WHERE intercompany_mode IN (1,2,3)
1553 AND driving_dr_le_bsv <>'Many'
1554 AND driving_cr_le_bsv <>'Many';
1555
1556 UPDATE fun_bal_inter_int2_gt
1557 SET REC_BSV = driving_dr_le_bsv,
1558 PAY_BSV = driving_cr_le_bsv
1559 WHERE intercompany_mode IN (1,2,3)
1560 and driving_dr_le_bsv <> line_le_bsv
1561 and TYPE = 'D';
1562
1563 UPDATE fun_bal_inter_int2_gt
1564 SET REC_BSV = driving_dr_le_bsv,
1565 PAY_BSV = driving_cr_le_bsv
1566 WHERE intercompany_mode IN (1,2,3)
1567 and driving_cr_le_bsv <> line_le_bsv
1568 and TYPE = 'C';
1569
1570
1571 --inserting the lines of 1:1 into fun_bal_inter_int_gt #9392684
1572 INSERT into fun_bal_inter_int_gt
1573 (Select * from fun_bal_inter_int2_gt
1574 where driving_cr_le_bsv<>'Many'
1575 and driving_dr_le_bsv<>'Many') ;
1576 --inserting the lines of M:M into fun_bal_gt by summarizing at LE level #9392684
1577 INSERT
1578 INTO fun_bal_inter_int_gt
1579 (
1580 group_id,
1581 ledger_id,
1582 gl_date,
1583 status,
1584 driving_dr_le_id,
1585 driving_cr_le_id,
1586 intercompany_mode,
1587 le_id,
1588 entered_currency_code,
1589 exchange_date,
1590 exchange_rate,
1591 exchange_rate_type,
1592 accounted_amt_cr,
1593 accounted_amt_dr,
1594 entered_amt_cr,
1595 entered_amt_dr,
1596 bal_seg_column_name,
1597 type,
1598 driving_dr_le_bsv,
1599 driving_cr_le_bsv
1600 )
1601 SELECT upd1.group_id,
1602 upd1.ledger_id,
1603 upd1.gl_date,
1604 upd1.status,
1605 upd1.driving_dr_le_id,
1606 upd1.driving_cr_le_id,
1607 upd1.intercompany_mode,
1608 upd1.le_id,
1609 upd1.entered_currency_code,
1610 upd1.exchange_date,
1611 upd1.exchange_rate,
1612 upd1.exchange_rate_type,
1613 DECODE(SIGN(SUM(NVL(upd1.accounted_amt_cr, 0)) - SUM(NVL(upd1.accounted_amt_dr, 0))), 1, ABS(SUM(NVL(upd1.accounted_amt_cr, 0)) - SUM(NVL(upd1.accounted_amt_dr, 0))), NULL) accounted_amt_cr,
1614 DECODE(SIGN(SUM(NVL(upd1.accounted_amt_cr, 0)) - SUM(NVL(upd1.accounted_amt_dr, 0))), -1, ABS(SUM(NVL(upd1.accounted_amt_cr, 0)) - SUM(NVL(upd1.accounted_amt_dr, 0))), NULL) accounted_amt_dr,
1615 DECODE(SIGN(SUM(NVL(upd1.entered_amt_cr, 0)) - SUM(NVL(upd1.entered_amt_dr, 0))), 1, ABS(SUM(NVL(upd1.entered_amt_cr, 0)) - SUM(NVL(upd1.entered_amt_dr, 0))), NULL) entered_amt_cr,
1616 DECODE(SIGN(SUM(NVL(upd1.entered_amt_cr, 0)) - SUM(NVL(upd1.entered_amt_dr, 0))), -1, ABS(SUM(NVL(upd1.entered_amt_cr, 0)) - SUM(NVL(upd1.entered_amt_dr, 0))), NULL) entered_amt_dr,
1617 upd1.bal_seg_column_name,
1618 DECODE(SIGN(SUM(NVL(upd1.accounted_amt_cr, 0))-SUM(NVL(upd1.accounted_amt_dr,0))), 1, 'C', -1, 'D', 0,
1619 DECODE(SIGN(((SUM(NVL(upd1.entered_amt_cr,0)) - SUM(NVL(upd1.entered_amt_dr,0)))) - (SUM(NVL(upd1.accounted_amt_dr,0)) - SUM(NVL(upd1.accounted_amt_cr,0)))), 1, 'C', 'D')) type,
1620 'Many',
1621 'Many'
1622 FROM fun_bal_inter_int2_gt upd1
1623 WHERE upd1.driving_dr_le_bsv='Many'
1624 AND upd1.driving_cr_le_bsv='Many'
1625 AND upd1.intercompany_mode IN (1,2,3,4)
1626 GROUP BY upd1.group_id,
1627 upd1.ledger_id,
1628 upd1.gl_date,
1629 upd1.status,
1630 upd1.driving_dr_le_id,
1631 upd1.driving_cr_le_id,
1632 upd1.intercompany_mode,
1633 upd1.le_id,
1634 upd1.entered_currency_code,
1635 upd1.exchange_date,
1636 upd1.exchange_rate,
1637 upd1.exchange_rate_type,
1638 upd1.bal_seg_column_name
1639 HAVING SUM(NVL(upd1.accounted_amt_cr, 0)) <> SUM(NVL(upd1.accounted_amt_dr,0))
1640 OR (SUM(NVL(upd1.accounted_amt_cr, 0)) = SUM(NVL(upd1.accounted_amt_dr,0))
1641 AND SUM(DECODE(upd1.exchange_rate, NULL, NVL(upd1.entered_amt_cr, 0), 0)) <> SUM(DECODE(upd1.exchange_rate, NULL, NVL(upd1.entered_amt_dr, 0), 0)));
1642
1643 --Bug:11883614
1644
1645 Update fun_bal_inter_int_gt bal_inter_int
1646 set REC_BSV = (select min_bal_seg_val from (
1647 select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
1648 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
1649 fun_bal_headers_gt hdrs
1650 WHERE hdrs.group_id = lines.group_id
1651 AND lines.group_id = le_bsv_map.group_id
1652 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
1653 AND hdrs.intercompany_mode IN (1,2,3)
1654 AND hdrs.status = 'OK'
1655 GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
1656 hdrs.intercompany_mode, le_bsv_map.le_id,
1657 hdrs.bal_seg_column_name, hdrs.intercompany_column_number
1658 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
1659 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
1660 AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
1661 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
1662 where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_CR_LE_ID,
1663 2, (decode (bal_inter_int.type, 'C', bal_inter_int.LE_ID, bal_inter_int.DRIVING_DR_LE_ID)),
1664 3, (decode (bal_inter_int.type, 'D', bal_inter_int.DRIVING_CR_LE_ID, bal_inter_int.LE_ID)), NULL)
1665 and min_bsv.group_id = bal_inter_int.group_id
1666 and bal_inter_int.status = 'OK')
1667
1668 where bal_inter_int.driving_dr_le_bsv='Many'
1669 AND bal_inter_int.driving_cr_le_bsv='Many';
1670
1671 Update fun_bal_inter_int_gt bal_inter_int
1672 set PAY_BSV = (select min_bal_seg_val from (
1673 select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
1674 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
1675 fun_bal_headers_gt hdrs
1676 WHERE hdrs.group_id = lines.group_id
1677 AND lines.group_id = le_bsv_map.group_id
1678 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
1679 AND hdrs.intercompany_mode IN (1,2,3)
1680 AND hdrs.status = 'OK'
1681 GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
1682 hdrs.intercompany_mode, le_bsv_map.le_id,
1683 hdrs.bal_seg_column_name, hdrs.intercompany_column_number
1684 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
1685 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
1686 AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
1687 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
1688 where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_DR_LE_ID,
1689 2, (decode (bal_inter_int.type, 'C', bal_inter_int.DRIVING_DR_LE_ID, bal_inter_int.LE_ID)),
1690 3, (decode (bal_inter_int.type, 'D', bal_inter_int.LE_ID, bal_inter_int.DRIVING_CR_LE_ID)), NULL)
1691 and min_bsv.group_id = bal_inter_int.group_id
1692 and bal_inter_int.status = 'OK')
1693 where bal_inter_int.driving_dr_le_bsv='Many'
1694 AND bal_inter_int.driving_cr_le_bsv='Many';
1695
1696
1697 Update fun_bal_inter_int_gt bal_inter_int
1698 set driving_cr_le_bsv = REC_BSV,
1699 driving_dr_le_bsv = PAY_BSV ,
1700 LINE_LE_BSV = decode(TYPE, 'D',PAY_BSV,REC_BSV)
1701 where driving_dr_le_bsv='Many'
1702 AND driving_cr_le_bsv='Many'
1703 and group_id in (select group_id from fun_bal_headers_gt where intercompany_mode IN (1,2,3));
1704
1705 --End Bug:11883614
1706
1707 --inserting lines into fun_bal_inter_int_gt for the 1:M and M:1 case by redistributing amounts #9392684
1708 INSERT into fun_bal_inter_int_gt (GROUP_ID,
1709 LEDGER_ID,
1710 GL_DATE,
1711 STATUS,
1712 DRIVING_DR_LE_ID,
1713 DRIVING_CR_LE_ID,
1714 INTERCOMPANY_MODE,
1715 LE_ID,
1716 ENTERED_CURRENCY_CODE,
1717 ACCOUNTED_AMT_CR,
1718 ACCOUNTED_AMT_DR,
1719 ENTERED_AMT_CR,
1720 ENTERED_AMT_DR,
1721 REC_ACCT,
1722 PAY_ACCT,
1723 BAL_SEG_COLUMN_NAME,
1724 REC_BSV,
1725 PAY_BSV,
1726 EXCHANGE_DATE,
1727 EXCHANGE_RATE,
1728 EXCHANGE_RATE_TYPE,
1729 TYPE,
1730 DRIVING_DR_LE_BSV,
1731 DRIVING_CR_LE_BSV,
1732 LINE_LE_BSV)
1733
1734 Select upd1.GROUP_ID,
1735 upd1.LEDGER_ID,
1736 upd1.GL_DATE,
1737 upd1.STATUS,
1738 upd1.DRIVING_DR_LE_ID,
1739 upd1.DRIVING_CR_LE_ID,
1740 upd1.INTERCOMPANY_MODE,
1741 upd2.LE_ID,
1742 upd1.ENTERED_CURRENCY_CODE,
1743 upd1.ACCOUNTED_AMT_DR,
1744 upd1.ACCOUNTED_AMT_CR,
1745 upd1.ENTERED_AMT_DR,
1746 upd1.ENTERED_AMT_CR,
1747 upd1.REC_ACCT,
1748 upd1.PAY_ACCT,
1749 upd1.BAL_SEG_COLUMN_NAME,
1750 upd1.REC_BSV,
1751 upd1.PAY_BSV,
1752 upd1.EXCHANGE_DATE,
1753 upd1.EXCHANGE_RATE,
1754 upd1.EXCHANGE_RATE_TYPE,
1755 DECODE(upd1.TYPE,'C','D','C'),
1756 upd1.DRIVING_DR_LE_BSV,
1757 upd1.DRIVING_CR_LE_BSV,
1758 upd2.LINE_LE_BSV
1759 from fun_bal_inter_int2_gt upd1,
1760 fun_bal_inter_int2_gt upd2
1761 where upd2.group_id=upd1.group_id
1762 AND upd1.intercompany_mode in (1,2,3)
1763 AND upd2.intercompany_mode in (1,2,3)
1764 AND NVL(upd1.entered_currency_code,'0')= NVL(upd2.entered_currency_code,'0')
1765 AND ((upd2.driving_dr_le_bsv='Many' and upd2.driving_cr_le_bsv<>'Many' and upd1.driving_dr_le_bsv<>upd2.driving_dr_le_bsv)
1766 or (upd2.driving_cr_le_bsv='Many' and upd2.driving_dr_le_bsv<>'Many' and upd1.driving_cr_le_bsv<>upd2.driving_cr_le_bsv)
1767 );
1768
1769 -- Update receivables account for specific LE and BSV values
1770 -- For 1:1 -
1771 -- if driving le is Cr And type = Cr
1772 -- driving cr le id = from le; driving dr le id = to le;
1773 -- if driving le is Dr And type = Cr,
1774 -- driving Dr le id = from le; driving cr le id = to le;
1775 -- Else leave receivables account as null (basically when type = Dr ?)
1776 -- For 1: Many -
1777 -- if type = Cr,
1778 -- line le id = from le; Driving dr le id = to le
1779 -- if type = Dr,
1780 -- driving Dr le id = from le; line le id = to le
1781 -- For Many : 1 -
1782 -- if type = Cr,
1783 -- line le id = from le; driving Cr le id = to le
1784 -- if type = Dr,
1785 -- driving Cr le id = from le; line le id = to le
1786 -- For Many : Many -
1787 -- Dont get receives account.
1788
1789 -- Rules of precedence is to find matching records using
1790 -- 1) From LE, From BSV => To LE, To BSV
1791 -- 2) From LE, From BSV => To LE
1792 -- 3) From LE => To LE, To BSV
1793 -- 4) From LE => To LE
1794 -- 4) From LE => To All Others
1795
1796 -- For 1:1, search from rule 1 and progress through to rule 5 if not found
1797 UPDATE fun_bal_inter_int_gt inter_int
1798 SET rec_acct =
1799 (SELECT ccid
1800 FROM fun_inter_accounts accts
1801 WHERE inter_int.ledger_id = accts.ledger_id
1802 AND DECODE(inter_int.intercompany_mode,
1803 1, DECODE(le_id, driving_cr_le_id,
1804 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1805 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1806 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1807 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1808 NULL) = accts.from_le_id
1809 AND DECODE(inter_int.intercompany_mode,
1810 1, DECODE(le_id, driving_cr_le_id,
1811 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
1812 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
1813 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1814 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1815 NULL) = accts.to_le_id
1816 AND DECODE(inter_int.intercompany_mode,
1817 1, DECODE(le_id, driving_cr_le_id,
1818 DECODE(inter_int.type, 'C', driving_cr_le_bsv ,NULL),
1819 DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL)),
1820 2, DECODE(inter_int.type, 'C', line_le_bsv, driving_dr_le_bsv),
1821 3, DECODE(inter_int.type, 'C', line_le_bsv,driving_cr_le_bsv),
1822 NULL) = accts.trans_bsv -- From BSV
1823
1824 AND DECODE(inter_int.intercompany_mode,
1825 1, DECODE(le_id, driving_cr_le_id,
1826 DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL),
1827 DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL)),
1828 2, DECODE(inter_int.type, 'C', driving_dr_le_bsv,line_le_bsv),
1829 3, DECODE(inter_int.type, 'C', driving_cr_le_bsv,line_le_bsv),
1830 NULL) = accts.tp_bsv -- To BSV
1831 AND accts.type = 'R'
1832 AND accts.default_flag = 'Y'
1833 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1834 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1835 WHERE inter_int.intercompany_mode IN (1,2,3)
1836 AND driving_dr_le_bsv <> 'Many'
1837 AND driving_cr_le_bsv <> 'Many';
1838
1839 -- For 1:M, search for rule 2
1840 UPDATE fun_bal_inter_int_gt inter_int
1841 SET rec_acct =
1842 (SELECT ccid
1843 FROM fun_inter_accounts accts
1844 WHERE inter_int.ledger_id = accts.ledger_id
1845 AND DECODE(inter_int.intercompany_mode,
1846 1, DECODE(le_id, driving_cr_le_id,
1847 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1848 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1849 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1850 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1851 NULL) = accts.from_le_id
1852 AND DECODE(inter_int.intercompany_mode,
1853 1, DECODE(le_id, driving_cr_le_id,
1854 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
1855 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
1856 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1857 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1858 NULL) = accts.to_le_id
1859 AND DECODE(inter_int.intercompany_mode,
1860 1, DECODE(le_id, driving_cr_le_id,
1861 DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL),
1862 DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL)),
1863 2, DECODE(inter_int.type, 'C', line_le_bsv,driving_dr_le_bsv),
1864 3, DECODE(inter_int.type, 'C', line_le_bsv,driving_cr_le_bsv),
1865 NULL) = accts.trans_bsv -- From BSV
1866
1867 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
1868 AND accts.type = 'R'
1869 AND accts.default_flag = 'Y'
1870 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1871 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1872 WHERE inter_int.intercompany_mode IN (1,2,3)
1873 AND inter_int.rec_acct IS NULL;
1874
1875 -- For M:1, search from rule 3 and progress through to rule 5 if not found
1876 UPDATE fun_bal_inter_int_gt inter_int
1877 SET rec_acct =
1878 (SELECT ccid
1879 FROM fun_inter_accounts accts
1880 WHERE inter_int.ledger_id = accts.ledger_id
1881 AND DECODE(inter_int.intercompany_mode,
1882 1, DECODE(le_id, driving_cr_le_id,
1883 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1884 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1885 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1886 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1887 NULL) = accts.from_le_id
1888 AND DECODE(inter_int.intercompany_mode,
1889 1, DECODE(le_id, driving_cr_le_id,
1890 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
1891 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
1892 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1893 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1894 NULL) = accts.to_le_id
1895 AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
1896 AND DECODE(inter_int.intercompany_mode,
1897 1, DECODE(le_id, driving_cr_le_id,
1898 DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL),
1899 DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL)),
1900 2, DECODE(inter_int.type, 'C', driving_dr_le_bsv,line_le_bsv),
1901 3, DECODE(inter_int.type, 'C', driving_cr_le_bsv,line_le_bsv),
1902 NULL) = accts.tp_bsv -- To BSV
1903
1904 AND accts.type = 'R'
1905 AND accts.default_flag = 'Y'
1906 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1907 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1908 WHERE inter_int.intercompany_mode IN (1,2,3)
1909 AND inter_int.rec_acct IS NULL;
1910
1911 -- The above will take care of rules 1 to 3.
1912 -- The account has not been found, the following will deal with rule 4
1913 -- ie it looks at specific LE without checking for the BSV
1914 UPDATE fun_bal_inter_int_gt inter_int
1915 SET rec_acct =
1916 (SELECT ccid
1917 FROM fun_inter_accounts accts
1918 WHERE inter_int.ledger_id = accts.ledger_id
1919 AND DECODE(inter_int.intercompany_mode,
1920 1, DECODE(le_id, driving_cr_le_id,
1921 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
1922 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
1923 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
1924 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
1925 NULL) = accts.from_le_id
1926 AND DECODE(inter_int.intercompany_mode,
1927 1, DECODE(le_id, driving_cr_le_id,
1928 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL),
1929 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL)),
1930 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
1931 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
1932 NULL) = accts.to_le_id
1933 AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
1934 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
1935 AND accts.type = 'R'
1936 AND accts.default_flag = 'Y'
1937 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
1938 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
1939 WHERE inter_int.intercompany_mode IN (1,2,3)
1940 AND inter_int.rec_acct IS NULL;
1941 -- End, Balancing API Changes, Feb 2005
1942
1943 -- Update receivables account for other LE if no account specified for specific LE
1944 -- This will handle rule 5
1945 --Bug: 9183927
1946 Update Fun_bal_inter_int_gt Inter_int
1947 Set Rec_acct = (Select Ccid
1948 From fun_inter_accounts Accts
1949 Where Inter_int.Ledger_id = Accts.Ledger_id
1950 And Inter_int.Rec_acct Is Null
1951 And Decode(Inter_int.Intercompany_mode, 1,
1952 Decode(Le_id, Driving_cr_le_id,
1953 Decode(Inter_int.Type, 'C', Driving_cr_le_id, Null),
1954 Decode(Inter_int.Type, 'C', Driving_dr_le_id, Null)), 2,
1955 Decode (Inter_int.Type, 'C', Le_id, Driving_dr_le_id), 3,
1956 Decode(Inter_int.Type, 'C', Le_id, Driving_cr_le_id), 4,
1957 Decode(Inter_int.Type, 'C', Le_id, Null), Null) =Accts.From_le_id
1958 And Accts.To_le_id = -99
1959 AND DECODE(inter_int.intercompany_mode,
1960 1, DECODE(le_id, driving_cr_le_id,
1961 DECODE(inter_int.type, 'C', driving_cr_le_bsv,NULL),
1962 DECODE(inter_int.type, 'C', driving_dr_le_bsv,NULL)),
1963 2, DECODE(inter_int.type, 'C', line_le_bsv,driving_dr_le_bsv),
1964 3, DECODE(inter_int.type, 'C', line_le_bsv,driving_cr_le_bsv),
1965 NULL) = accts.trans_bsv -- From BSV
1966 AND 'OTHER1234567890123456789012345' = accts.tp_bsv
1967 And Accts.Type = 'R'
1968 And Accts.Default_flag = 'Y'
1969 And (Trunc(Inter_int.Gl_date) Between Trunc(Nvl(Accts.Start_date, Inter_int.Gl_date))
1970 And Trunc(Nvl(Accts.End_date, Inter_int.Gl_date))))
1971 Where Inter_int.Rec_acct Is Null ;
1972
1973
1974 Update Fun_bal_inter_int_gt Inter_int
1975 Set Rec_acct = (Select Ccid
1976 From fun_inter_accounts Accts
1977 Where Inter_int.Ledger_id = Accts.Ledger_id
1978 And Inter_int.Rec_acct Is Null
1979 And Decode(Inter_int.Intercompany_mode, 1,
1980 Decode(Le_id, Driving_cr_le_id,
1981 Decode(Inter_int.Type, 'C', Driving_cr_le_id, Null),
1982 Decode(Inter_int.Type, 'C', Driving_dr_le_id, Null)), 2,
1983 Decode (Inter_int.Type, 'C', Le_id, Driving_dr_le_id), 3,
1984 Decode(Inter_int.Type, 'C', Le_id, Driving_cr_le_id), 4,
1985 Decode(Inter_int.Type, 'C', Le_id, Null), Null) =Accts.From_le_id
1986 And Accts.To_le_id = -99
1987 AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
1988 AND 'OTHER1234567890123456789012345' = accts.tp_bsv
1989 And Accts.Type = 'R'
1990 And Accts.Default_flag = 'Y'
1991 And (Trunc(Inter_int.Gl_date) Between Trunc(Nvl(Accts.Start_date, Inter_int.Gl_date))
1992 And Trunc(Nvl(Accts.End_date, Inter_int.Gl_date))))
1993 Where Inter_int.Rec_acct Is Null ;
1994
1995 --ER: 8588074
1996 --Bug: 9183927
1997 UPDATE fun_bal_inter_int_gt inter_int
1998 SET rec_acct = (SELECT dr_ccid
1999 FROM fun_balance_accounts accts
2000 WHERE
2001 'OTHER1234567890123456789012345' = accts.cr_bsv
2002 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
2003 AND accts.template_id = (SELECT NVL((SELECT opts.template_id
2004 FROM fun_balance_options opts
2005 WHERE opts.ledger_id = inter_int.ledger_id
2006 AND opts.le_id = DECODE(inter_int.intercompany_mode,
2007 1, DECODE(inter_int.le_id, driving_cr_le_id,
2008 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
2009 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2010 2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
2011 3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
2012 4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
2013 NULL)
2014 AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2015 where le_bsv_map.group_id = inter_int.group_id)
2016 AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2017 where le_bsv_map.group_id = inter_int.group_id)
2018 AND opts.status_flag = 'Y'),
2019 NVL((SELECT opts.template_id
2020 FROM fun_balance_options opts
2021 WHERE opts.ledger_id = inter_int.ledger_id
2022 AND opts.le_id = DECODE(inter_int.intercompany_mode,
2023 1, DECODE(inter_int.le_id, driving_cr_le_id,
2024 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
2025 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2026 2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
2027 3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
2028 4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
2029 NULL)
2030 AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2031 where le_bsv_map.group_id = inter_int.group_id)
2032 AND opts.je_category_name = 'Other'
2033 AND opts.status_flag = 'Y'),
2034 NVL((SELECT opts.template_id
2035 FROM fun_balance_options opts
2036 WHERE opts.ledger_id = inter_int.ledger_id
2037 AND opts.le_id = DECODE(inter_int.intercompany_mode,
2038 1, DECODE(inter_int.le_id, driving_cr_le_id,
2039 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
2040 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2041 2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
2042 3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
2043 4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
2044 NULL)
2045 AND opts.je_source_name = 'Other'
2046 AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2047 where le_bsv_map.group_id = inter_int.group_id)
2048 AND opts.status_flag = 'Y'),
2049 (SELECT opts.template_id
2050 FROM fun_balance_options opts
2051 WHERE opts.ledger_id = inter_int.ledger_id
2052 AND opts.le_id = DECODE(inter_int.intercompany_mode,
2053 1, DECODE(inter_int.le_id, driving_cr_le_id,
2054 DECODE(inter_int.type, 'C', driving_cr_le_id, NULL),
2055 DECODE(inter_int.type, 'C', driving_dr_le_id, NULL)),
2056 2, DECODE(inter_int.type, 'C', inter_int.le_id, driving_dr_le_id),
2057 3, DECODE(inter_int.type, 'C', inter_int.le_id, driving_cr_le_id),
2058 4, DECODE(inter_int.type, 'C', inter_int.le_id, NULL),
2059 NULL)
2060 AND opts.je_source_name = 'Other'
2061 AND opts.je_category_name = 'Other'
2062 AND opts.status_flag = 'Y')))) template_id
2063 From Dual))
2064 WHERE inter_int.rec_acct IS NULL and fun_trx_pvt.get_inter_intra()='Y';
2065
2066 ---- end ER: 8588074
2067
2068 -- Update payables account for specific LE
2069 -- 1:1 mapping to begin with
2070 UPDATE fun_bal_inter_int_gt inter_int
2071 SET pay_acct =
2072 (SELECT ccid
2073 FROM fun_inter_accounts accts
2074 WHERE inter_int.ledger_id = accts.ledger_id
2075 AND DECODE(inter_int.intercompany_mode,
2076 1, DECODE(le_id, driving_dr_le_id,
2077 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2078 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2079 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
2080 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
2081 NULL) = accts.from_le_id
2082 AND DECODE(inter_int.intercompany_mode,
2083 1, DECODE(le_id, driving_dr_le_id,
2084 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
2085 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2086 2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
2087 3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
2088 NULL) = accts.to_le_id
2089 AND DECODE(inter_int.intercompany_mode,
2090 1, DECODE(le_id,driving_dr_le_id,
2091 DECODE(inter_int.type, 'D', driving_dr_le_bsv,NULL),
2092 DECODE(inter_int.type, 'D', driving_cr_le_bsv, NULL)),
2093 2, DECODE(inter_int.type, 'D', line_le_bsv,driving_dr_le_bsv),
2094 3, DECODE(inter_int.type, 'D', line_le_bsv,driving_cr_le_bsv),
2095 NULL) = accts.trans_bsv
2096 AND DECODE(inter_int.intercompany_mode,
2097 1, DECODE(le_id,driving_dr_le_id,
2098 DECODE(inter_int.type, 'D', driving_cr_le_bsv,NULL),
2099 DECODE(inter_int.type, 'D', driving_dr_le_bsv, NULL)),
2100 2, DECODE(inter_int.type, 'D', driving_dr_le_bsv,line_le_bsv),
2101 3, DECODE(inter_int.type, 'D', driving_cr_le_bsv,line_le_bsv),
2102 NULL) = accts.tp_bsv -- To BSV
2103 AND accts.type = 'P'
2104 AND accts.default_flag = 'Y'
2105 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
2106 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
2107 WHERE inter_int.intercompany_mode IN (1,2,3)
2108 AND driving_cr_le_bsv <> 'Many'
2109 AND driving_dr_le_bsv <> 'Many';
2110
2111 -- 1:M - next
2112 UPDATE fun_bal_inter_int_gt inter_int
2113 SET pay_acct =
2114 (SELECT ccid
2115 FROM fun_inter_accounts accts
2116 WHERE inter_int.ledger_id = accts.ledger_id
2117 AND DECODE(inter_int.intercompany_mode,
2118 1, DECODE(le_id, driving_dr_le_id,
2119 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2120 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2121 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
2122 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
2123 NULL) = accts.from_le_id
2124 AND DECODE(inter_int.intercompany_mode,
2125 1, DECODE(le_id, driving_dr_le_id,
2126 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
2127 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2128 2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
2129 3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
2130 NULL) = accts.to_le_id
2131 AND DECODE(inter_int.intercompany_mode,
2132 1, DECODE(le_id,driving_dr_le_id,
2133 DECODE(inter_int.type, 'D', driving_dr_le_bsv,NULL),
2134 DECODE(inter_int.type, 'D', driving_cr_le_bsv, NULL)),
2135 2, DECODE(inter_int.type, 'D', line_le_bsv,driving_dr_le_bsv),
2136 3, DECODE(inter_int.type, 'D', line_le_bsv,driving_cr_le_bsv),
2137 NULL) = accts.trans_bsv
2138 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
2139 AND accts.type = 'P'
2140 AND accts.default_flag = 'Y'
2141 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
2142 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
2143 WHERE inter_int.intercompany_mode IN (1,2,3)
2144 AND inter_int.pay_acct IS NULL;
2145
2146 -- M:1 - next
2147 UPDATE fun_bal_inter_int_gt inter_int
2148 SET pay_acct =
2149 (SELECT ccid
2150 FROM fun_inter_accounts accts
2151 WHERE inter_int.ledger_id = accts.ledger_id
2152 AND DECODE(inter_int.intercompany_mode,
2153 1, DECODE(le_id, driving_dr_le_id,
2154 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2155 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2156 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
2157 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
2158 NULL) = accts.from_le_id
2159 AND DECODE(inter_int.intercompany_mode,
2160 1, DECODE(le_id, driving_dr_le_id,
2161 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
2162 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2163 2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
2164 3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
2165 NULL) = accts.to_le_id
2166 AND 'OTHER1234567890123456789012345' = accts.trans_bsv
2167 AND DECODE(inter_int.intercompany_mode,
2168 1, DECODE(le_id,driving_dr_le_id,
2169 DECODE(inter_int.type, 'D', driving_cr_le_bsv,NULL),
2170 DECODE(inter_int.type, 'D', driving_dr_le_bsv, NULL)),
2171 2, DECODE(inter_int.type, 'D', driving_dr_le_bsv,line_le_bsv),
2172 3, DECODE(inter_int.type, 'D', driving_cr_le_bsv,line_le_bsv),
2173 NULL) = accts.tp_bsv -- To BSV
2174 AND accts.type = 'P'
2175 AND accts.default_flag = 'Y'
2176 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date))
2177 AND TRUNC(NVL(accts.end_date, inter_int.gl_date))))
2178 WHERE inter_int.intercompany_mode IN (1,2,3)
2179 AND inter_int.pay_acct IS NULL ;
2180
2181 -- If the payables account was not found, look for an account as per rule 4
2182 -- ie from le to te
2183 UPDATE fun_bal_inter_int_gt inter_int
2184 SET pay_acct =
2185 (SELECT ccid
2186 FROM fun_inter_accounts accts
2187 WHERE inter_int.ledger_id = accts.ledger_id
2188 AND DECODE(inter_int.intercompany_mode,
2189 1, DECODE(le_id, driving_dr_le_id,
2190 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2191 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2192 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
2193 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
2194 NULL) = accts.from_le_id
2195 AND DECODE(inter_int.intercompany_mode,
2196 1, DECODE(le_id, driving_dr_le_id,
2197 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL),
2198 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2199 2, DECODE(inter_int.type, 'D', driving_dr_le_id, le_id),
2200 3, DECODE(inter_int.type, 'D', driving_cr_le_id,le_id),
2201 NULL) = accts.to_le_id
2202 AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
2203 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
2204 AND accts.type = 'P'
2205 AND accts.default_flag = 'Y'
2206 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
2207 TRUNC(NVL(accts.end_date, inter_int.gl_date))))
2208 WHERE inter_int.intercompany_mode IN (1,2,3)
2209 AND inter_int.pay_acct IS NULL;
2210 -- End, Balancing API changes
2211
2212 -- Update payables account for other LE if no account specified for specific LE
2213 -- This will deal with rule 5, From LE to All Others
2214 --Bug: 9183927
2215 UPDATE fun_bal_inter_int_gt inter_int
2216 SET pay_acct =
2217 (SELECT ccid
2218 FROM fun_inter_accounts accts
2219 WHERE inter_int.ledger_id = accts.ledger_id
2220 AND inter_int.pay_acct IS NULL
2221 AND DECODE(inter_int.intercompany_mode,
2222 1, DECODE(le_id, driving_dr_le_id,
2223 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2224 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2225 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
2226 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
2227 4, DECODE(inter_int.type, 'D', le_id, NULL),
2228 NULL) = accts.from_le_id
2229 AND accts.to_le_id = -99 -- To LE "All Other"
2230 AND DECODE(inter_int.intercompany_mode,
2231 1, DECODE(le_id,driving_dr_le_id,
2232 DECODE(inter_int.type, 'D', driving_dr_le_bsv,NULL),
2233 DECODE(inter_int.type, 'D', driving_cr_le_bsv, NULL)),
2234 2, DECODE(inter_int.type, 'D', line_le_bsv,driving_dr_le_bsv),
2235 3, DECODE(inter_int.type, 'D', line_le_bsv,driving_cr_le_bsv),
2236 NULL) = accts.trans_bsv
2237 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
2238 AND accts.type = 'P'
2239 AND accts.default_flag = 'Y'
2240 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
2241 TRUNC(NVL(accts.end_date, inter_int.gl_date))))
2242 WHERE inter_int.pay_acct IS NULL;
2243
2244 UPDATE fun_bal_inter_int_gt inter_int
2245 SET pay_acct =
2246 (SELECT ccid
2247 FROM fun_inter_accounts accts
2248 WHERE inter_int.ledger_id = accts.ledger_id
2249 AND inter_int.pay_acct IS NULL
2250 AND DECODE(inter_int.intercompany_mode,
2251 1, DECODE(le_id, driving_dr_le_id,
2252 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2253 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2254 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
2255 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
2256 4, DECODE(inter_int.type, 'D', le_id, NULL),
2257 NULL) = accts.from_le_id
2258 AND accts.to_le_id = -99 -- To LE "All Other"
2259 AND 'OTHER1234567890123456789012345' = accts.trans_bsv -- From BSV
2260 AND 'OTHER1234567890123456789012345' = accts.tp_bsv -- To BSV
2261 AND accts.type = 'P'
2262 AND accts.default_flag = 'Y'
2263 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(accts.start_date, inter_int.gl_date)) AND
2264 TRUNC(NVL(accts.end_date, inter_int.gl_date))))
2265 WHERE inter_int.pay_acct IS NULL;
2266 -- ER: 8588074
2267 --Bug: 9183927
2268 UPDATE fun_bal_inter_int_gt inter_int
2269 SET pay_acct = (SELECT cr_ccid
2270 FROM fun_balance_accounts accts
2271 WHERE
2272 'OTHER1234567890123456789012345' = accts.cr_bsv
2273 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
2274 AND accts.template_id = (SELECT NVL((SELECT opts.template_id
2275 FROM fun_balance_options opts
2276 WHERE opts.ledger_id = inter_int.ledger_id
2277 AND opts.le_id = DECODE(inter_int.intercompany_mode,
2278 1, DECODE(inter_int.le_id, driving_dr_le_id,
2279 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2280 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2281 2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
2282 3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
2283 4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
2284 NULL)
2285 AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2286 where le_bsv_map.group_id = inter_int.group_id)
2287 AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2288 where le_bsv_map.group_id = inter_int.group_id)
2289 AND opts.status_flag = 'Y'),
2290 NVL((SELECT opts.template_id
2291 FROM fun_balance_options opts
2292 WHERE opts.ledger_id = inter_int.ledger_id
2293 AND opts.le_id = DECODE(inter_int.intercompany_mode,
2294 1, DECODE(inter_int.le_id, driving_dr_le_id,
2295 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2296 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2297 2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
2298 3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
2299 4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
2300 NULL)
2301 AND opts.je_source_name = (select distinct je_source_name from fun_bal_le_bsv_map_gt le_bsv_map
2302 where le_bsv_map.group_id = inter_int.group_id)
2303 AND opts.je_category_name = 'Other'
2304 AND opts.status_flag = 'Y'),
2305 NVL((SELECT opts.template_id
2306 FROM fun_balance_options opts
2307 WHERE opts.ledger_id = inter_int.ledger_id
2308 AND opts.le_id = DECODE(inter_int.intercompany_mode,
2309 1, DECODE(inter_int.le_id, driving_dr_le_id,
2310 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2311 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2312 2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
2313 3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
2314 4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
2315 NULL)
2316 AND opts.je_source_name = 'Other'
2317 AND opts.je_category_name = (select distinct je_category_name from fun_bal_le_bsv_map_gt le_bsv_map
2318 where le_bsv_map.group_id = inter_int.group_id)
2319 AND opts.status_flag = 'Y'),
2320 (SELECT opts.template_id
2321 FROM fun_balance_options opts
2322 WHERE opts.ledger_id = inter_int.ledger_id
2323 AND opts.le_id = DECODE(inter_int.intercompany_mode,
2324 1, DECODE(inter_int.le_id, driving_dr_le_id,
2325 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL),
2326 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2327 2, DECODE(inter_int.type, 'D', inter_int.le_id, driving_dr_le_id),
2328 3, DECODE(inter_int.type, 'D', inter_int.le_id, driving_cr_le_id),
2329 4, DECODE(inter_int.type, 'D', inter_int.le_id, NULL),
2330 NULL)
2331 AND opts.je_source_name = 'Other'
2332 AND opts.je_category_name = 'Other'
2333 AND opts.status_flag = 'Y')))) template_id
2334 From Dual))
2335 WHERE inter_int.pay_acct IS NULL and fun_trx_pvt.get_inter_intra()='Y';
2336 --- END ER: 8588074
2337 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_INTER_INT_GT');
2338
2339 UPDATE fun_bal_inter_int_gt inter_int
2340 SET rec_acct = -1
2341 WHERE rec_acct IS NULL AND
2342 EXISTS (SELECT 'Receivables Accounts exist but not defaulted'
2343 FROM fun_inter_accounts accts
2344 WHERE inter_int.ledger_id = accts.ledger_id
2345 AND accts.type = 'R'
2346 AND DECODE(inter_int.intercompany_mode,
2347 1, DECODE(le_id,
2348 driving_cr_le_id, DECODE(inter_int.type, 'C', driving_cr_le_id,
2349 NULL),
2350 DECODE(inter_int.type, 'C', driving_dr_le_id,
2351 NULL)),
2352 2, DECODE(inter_int.type, 'C', le_id, driving_dr_le_id),
2353 3, DECODE(inter_int.type, 'C', le_id, driving_cr_le_id),
2354 4, DECODE(inter_int.type, 'C', le_id, NULL),
2355 NULL) = accts.from_le_id
2356 AND (DECODE(inter_int.intercompany_mode,
2357 1, DECODE(le_id,
2358 driving_cr_le_id, DECODE(inter_int.type, 'C', driving_dr_le_id,
2359 NULL),
2360 DECODE(inter_int.type, 'C', driving_cr_le_id,
2361 NULL)),
2362 2, DECODE(inter_int.type, 'C', driving_dr_le_id, le_id),
2363 3, DECODE(inter_int.type, 'C', driving_cr_le_id, le_id),
2364 NULL) = accts.to_le_id
2365 OR
2366 accts.to_le_id = -99));
2367
2368 UPDATE fun_bal_inter_int_gt inter_int
2369 SET pay_acct = -1
2370 WHERE pay_acct IS NULL AND
2371 EXISTS (SELECT 'Payables Accounts exist but not defaulted'
2372 FROM fun_inter_accounts accts
2373 WHERE inter_int.ledger_id = accts.ledger_id
2374 AND accts.type = 'P'
2375 AND DECODE(inter_int.intercompany_mode,
2376 1, DECODE(le_id,
2377 driving_dr_le_id, DECODE(inter_int.type, 'D', driving_dr_le_id,
2378 NULL),
2379 DECODE(inter_int.type, 'D', driving_cr_le_id, NULL)),
2380 2, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
2381 3, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
2382 4, DECODE(inter_int.type, 'C', le_id, NULL),
2383 NULL) = accts.from_le_id
2384 AND (DECODE(inter_int.intercompany_mode,
2385 1, DECODE(le_id,
2386 driving_dr_le_id, DECODE(inter_int.type, 'D', driving_cr_le_id,
2387 NULL),
2388 DECODE(inter_int.type, 'D', driving_dr_le_id, NULL)),
2389 2, DECODE(inter_int.type, 'D', le_id, driving_cr_le_id),
2390 3, DECODE(inter_int.type, 'D', le_id, driving_dr_le_id),
2391 NULL) = accts.to_le_id
2392 OR
2393 accts.to_le_id = -99));
2394
2395 IF g_debug = FND_API.G_TRUE THEN
2396 OPEN l_inter_int_cursor;
2397 FETCH l_inter_int_cursor BULK COLLECT INTO l_inter_int_tab;
2398 l_inter_int_count := l_inter_int_cursor%ROWCOUNT;
2399 CLOSE l_inter_int_cursor;
2400 ins_t_tables_inter_2_auto(l_inter_int_tab, l_inter_int_count);
2401 END IF;
2402
2403
2404 -- Insert errors into FUN_BAL_ERRORS_GT
2405 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
2406 acct_type, ccid_concat_display,
2407 dr_bsv, cr_bsv)
2408 SELECT DISTINCT DECODE(inter_int.rec_acct, NULL, 'FUN_INTER_REC_NOT_ASSIGNED',
2409 -1, 'FUN_INTER_REC_NO_DEFAULT',
2410 'FUN_INTER_REC_NOT_VALID'),
2411 inter_int.group_id,
2412 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
2413 2, inter_int.le_id,
2414 3, inter_int.driving_cr_le_id,
2415 4, inter_int.le_id),
2416 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
2417 2, inter_int.driving_dr_le_id,
2418 3, inter_int.le_id,
2419 4, NULL),
2420 DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), 'R',
2421 get_ccid_concat_disp(DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), hdrs.chart_of_accounts_id,
2422 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv, bal_seg_column_number, intercompany_column_number),
2423 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
2424 FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
2425 WHERE inter_int.group_id = hdrs.group_id AND
2426 ((inter_int.intercompany_mode = 1 AND
2427 inter_int.type = 'C')
2428 OR
2429 (inter_int.intercompany_mode = 2 AND
2430 inter_int.le_id <> inter_int.driving_dr_le_id)
2431 OR
2432 (inter_int.intercompany_mode = 3 AND
2433 inter_int.le_id <> inter_int.driving_cr_le_id)
2434 OR
2435 (inter_int.intercompany_mode = 4 AND
2436 inter_int.type = 'C'))
2437 AND (inter_int.rec_acct IS NULL
2438 OR
2439 inter_int.rec_acct = -1
2440 OR
2441 (inter_int.rec_acct IS NOT NULL AND
2442 NOT EXISTS (SELECT 'Receivables account not valid'
2443 FROM gl_code_combinations cc
2444 WHERE inter_int.rec_acct = cc.code_combination_id
2445 AND cc.detail_posting_allowed_flag = 'Y'
2446 AND cc.enabled_flag = 'Y'
2447 AND cc.summary_flag = 'N'
2448 AND nvl(cc.reference3, 'N') IN ('N', 'R')
2449 AND cc.template_id IS NULL
2450 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
2451 AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
2452
2453 -- Insert errors into FUN_BAL_ERRORS_GT
2454 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
2455 acct_type, ccid_concat_display,
2456 dr_bsv, cr_bsv)
2457 SELECT DISTINCT DECODE(inter_int.pay_acct, NULL, 'FUN_INTER_PAY_NOT_ASSIGNED',
2458 -1, 'FUN_INTER_PAY_NO_DEFAULT',
2459 'FUN_INTER_PAY_NOT_VALID'),
2460 inter_int.group_id,
2461 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
2462 2, inter_int.driving_dr_le_id,
2463 3, inter_int.le_id,
2464 4, inter_int.le_id),
2465 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
2466 2, inter_int.le_id,
2467 3, inter_int.driving_cr_le_id,
2468 4, NULL),
2469 DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), 'P',
2470 get_ccid_concat_disp(DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), hdrs.chart_of_accounts_id,
2471 inter_int.driving_cr_le_bsv, inter_int.driving_dr_le_bsv, bal_seg_column_number, intercompany_column_number),
2472 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
2473 FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
2474 WHERE inter_int.group_id = hdrs.group_id AND
2475 ((inter_int.intercompany_mode = 1 AND
2476 inter_int.type = 'D')
2477 OR
2478 (inter_int.intercompany_mode = 2 AND
2479 inter_int.le_id <> inter_int.driving_dr_le_id)
2480 OR
2481 (inter_int.intercompany_mode = 3 AND
2482 inter_int.le_id <> inter_int.driving_cr_le_id)
2483 OR
2484 (inter_int.intercompany_mode = 4 AND
2485 inter_int.type = 'D'))
2486 AND (inter_int.pay_acct IS NULL
2487 OR
2488 inter_int.pay_acct = -1
2489 OR
2490 (inter_int.pay_acct IS NOT NULL AND
2491 NOT EXISTS (SELECT 'Payables account not valid'
2492 FROM gl_code_combinations cc
2493 WHERE inter_int.pay_acct = cc.code_combination_id
2494 AND cc.detail_posting_allowed_flag = 'Y'
2495 AND cc.enabled_flag = 'Y'
2496 AND cc.summary_flag = 'N'
2497 AND nvl(cc.reference3, 'N') IN ('N', 'R')
2498 AND cc.template_id IS NULL
2499 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
2500 AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
2501
2502
2503 UPDATE fun_bal_headers_gt headers
2504 SET status = 'ERROR'
2505 WHERE EXISTS (SELECT 'Errors for Rec and Pay Accts'
2506 FROM FUN_BAL_ERRORS_GT errors
2507 WHERE headers.group_id = errors.group_id
2508 AND error_code IN ('FUN_INTER_PAY_NOT_ASSIGNED',
2509 'FUN_INTER_REC_NOT_ASSIGNED',
2510 'FUN_INTER_PAY_NO_DEFAULT',
2511 'FUN_INTER_REC_NO_DEFAULT',
2512 'FUN_INTER_PAY_NOT_VALID',
2513 'FUN_INTER_REC_NOT_VALID'))
2514 AND headers.status = 'OK';
2515
2516 DELETE FROM fun_bal_inter_int_gt inter_int
2517 WHERE EXISTS (SELECT group_id
2518 FROM fun_bal_headers_gt headers
2519 WHERE headers.status = 'ERROR'
2520 AND inter_int.group_id = headers.group_id);
2521
2522 --Enhancement 7520196 Start
2523 -- Update the Payable and receivable BSV with the minimum unbalanced bsv
2524 -- for each of the transacting Legal Entity.
2525 --bug: 9008776
2526 Update fun_bal_inter_int_gt bal_inter_int
2527 set Rec_BSV = (select min_bal_seg_val from (
2528 select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
2529 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
2530 fun_bal_headers_gt hdrs
2531 WHERE hdrs.group_id = lines.group_id
2532 AND lines.group_id = le_bsv_map.group_id
2533 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
2534 AND hdrs.intercompany_mode IN (1,2,3)
2535 AND hdrs.status = 'OK'
2536 GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
2537 hdrs.intercompany_mode, le_bsv_map.le_id,
2538 hdrs.bal_seg_column_name, hdrs.intercompany_column_number
2539 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
2540 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
2541 AND 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))) ) min_bsv
2543 where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_CR_LE_ID,
2544 2, (decode (bal_inter_int.type, 'C', bal_inter_int.LE_ID, bal_inter_int.DRIVING_DR_LE_ID)),
2545 3, (decode (bal_inter_int.type, 'D', bal_inter_int.DRIVING_CR_LE_ID, bal_inter_int.LE_ID)), NULL)
2546 and min_bsv.group_id = bal_inter_int.group_id
2547 and bal_inter_int.status = 'OK')
2548 where REC_BSV IS NULL;
2549
2550 Update fun_bal_inter_int_gt bal_inter_int
2551 set Pay_BSV = (select min_bal_seg_val from (
2552 select min(lines.bal_seg_val) min_bal_seg_val, le_bsv_map.le_id le_id, hdrs.group_id group_id
2553 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
2554 fun_bal_headers_gt hdrs
2555 WHERE hdrs.group_id = lines.group_id
2556 AND lines.group_id = le_bsv_map.group_id
2557 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
2558 AND hdrs.intercompany_mode IN (1,2,3)
2559 AND hdrs.status = 'OK'
2560 GROUP BY hdrs.group_id, hdrs.ledger_id, hdrs.gl_date, hdrs.status, hdrs.driving_dr_le_id, hdrs.driving_cr_le_id,
2561 hdrs.intercompany_mode, le_bsv_map.le_id,
2562 hdrs.bal_seg_column_name, hdrs.intercompany_column_number
2563 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
2564 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
2565 AND SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
2566 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))) ) min_bsv
2567 where min_bsv.le_id = Decode (bal_inter_int.Intercompany_mode, 1, bal_inter_int.DRIVING_DR_LE_ID,
2568 2, (decode (bal_inter_int.type, 'C', bal_inter_int.DRIVING_DR_LE_ID, bal_inter_int.LE_ID)),
2569 3, (decode (bal_inter_int.type, 'D', bal_inter_int.LE_ID, bal_inter_int.DRIVING_CR_LE_ID)), NULL)
2570 and min_bsv.group_id = bal_inter_int.group_id
2571 and bal_inter_int.status = 'OK')
2572 where Pay_BSV IS NULL;
2573
2574 -- Switch the Intercompany and Balancing segment value for the
2575 -- Payables and Receivables accounts. And update the table with
2576 -- the new account numbers.
2577 Update fun_bal_inter_int_gt bal_inter_int
2578 Set (REC_ACCT, PAY_ACCT) =
2579 (select get_ccid (bal_inter_int.REC_ACCT,
2580 hdrs.CHART_OF_ACCOUNTS_ID,
2581 bal_inter_int.REC_BSV,
2582 bal_inter_int.PAY_BSV,
2583 hdrs.BAL_SEG_COLUMN_NUMBER,
2584 hdrs.INTERCOMPANY_COLUMN_NUMBER,
2585 bal_inter_int.GL_DATE
2586 ),
2587 get_ccid (bal_inter_int.PAY_ACCT,
2588 hdrs.CHART_OF_ACCOUNTS_ID,
2589 bal_inter_int.PAY_BSV,
2590 bal_inter_int.REC_BSV,
2591 hdrs.BAL_SEG_COLUMN_NUMBER,
2592 hdrs.INTERCOMPANY_COLUMN_NUMBER,
2593 bal_inter_int.GL_DATE
2594 )
2595 from fun_bal_headers_gt hdrs
2596 where bal_inter_int.group_id = hdrs.group_id)
2597 where bal_inter_int.intercompany_mode in (1, 2, 3)
2598 and bal_inter_int.status = 'OK';
2599
2600 -- Enhancement 7520196 End
2601
2602 /* Changes for Bug # 8212023 Start */
2603 -- Insert errors into FUN_BAL_ERRORS_GT
2604 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
2605 acct_type, ccid_concat_display,
2606 dr_bsv, cr_bsv)
2607 SELECT DISTINCT DECODE(inter_int.rec_acct, NULL, 'FUN_INTER_REC_NOT_ASSIGNED',
2608 -1, 'FUN_INTER_REC_NO_DEFAULT',
2609 'FUN_INTER_REC_NOT_VALID'),
2610 inter_int.group_id,
2611 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
2612 2, inter_int.le_id,
2613 3, inter_int.driving_cr_le_id,
2614 4, inter_int.le_id),
2615 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
2616 2, inter_int.driving_dr_le_id,
2617 3, inter_int.le_id,
2618 4, NULL),
2619 DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), 'R',
2620 get_ccid_concat_disp(DECODE(inter_int.rec_acct, -1, NULL, inter_int.rec_acct), hdrs.chart_of_accounts_id,
2621 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv, bal_seg_column_number, intercompany_column_number),
2622 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
2623 FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
2624 WHERE inter_int.group_id = hdrs.group_id AND
2625 ((inter_int.intercompany_mode = 1 AND
2626 inter_int.type = 'C')
2627 OR
2628 (inter_int.intercompany_mode = 2 AND
2629 inter_int.le_id <> inter_int.driving_dr_le_id)
2630 OR
2631 (inter_int.intercompany_mode = 3 AND
2632 inter_int.le_id <> inter_int.driving_cr_le_id)
2633 OR
2634 (inter_int.intercompany_mode = 4 AND
2635 inter_int.type = 'C'))
2636 AND (inter_int.rec_acct IS NULL
2637 OR
2638 inter_int.rec_acct = -1
2639 OR
2640 (inter_int.rec_acct IS NOT NULL AND
2641 NOT EXISTS (SELECT 'Receivables account not valid'
2642 FROM gl_code_combinations cc
2643 WHERE inter_int.rec_acct = cc.code_combination_id
2644 AND cc.detail_posting_allowed_flag = 'Y'
2645 AND cc.enabled_flag = 'Y'
2646 AND cc.summary_flag = 'N'
2647 AND nvl(cc.reference3, 'N') IN ('N', 'R')
2648 AND cc.template_id IS NULL
2649 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
2650 AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
2651
2652 -- Insert errors into FUN_BAL_ERRORS_GT
2653 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, from_le_id, to_le_id, ccid,
2654 acct_type, ccid_concat_display,
2655 dr_bsv, cr_bsv)
2656 SELECT DISTINCT DECODE(inter_int.pay_acct, NULL, 'FUN_INTER_PAY_NOT_ASSIGNED',
2657 -1, 'FUN_INTER_PAY_NO_DEFAULT',
2658 'FUN_INTER_PAY_NOT_VALID'),
2659 inter_int.group_id,
2660 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_dr_le_id,
2661 2, inter_int.driving_dr_le_id,
2662 3, inter_int.le_id,
2663 4, inter_int.le_id),
2664 DECODE(inter_int.intercompany_mode, 1, inter_int.driving_cr_le_id,
2665 2, inter_int.le_id,
2666 3, inter_int.driving_cr_le_id,
2667 4, NULL),
2668 DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), 'P',
2669 get_ccid_concat_disp(DECODE(inter_int.pay_acct, -1, NULL, inter_int.pay_acct), hdrs.chart_of_accounts_id,
2670 inter_int.driving_cr_le_bsv, inter_int.driving_dr_le_bsv, bal_seg_column_number, intercompany_column_number),
2671 inter_int.driving_dr_le_bsv, inter_int.driving_cr_le_bsv
2672 FROM fun_bal_inter_int_gt inter_int, fun_bal_headers_gt hdrs
2673 WHERE inter_int.group_id = hdrs.group_id AND
2674 ((inter_int.intercompany_mode = 1 AND
2675 inter_int.type = 'D')
2676 OR
2677 (inter_int.intercompany_mode = 2 AND
2678 inter_int.le_id <> inter_int.driving_dr_le_id)
2679 OR
2680 (inter_int.intercompany_mode = 3 AND
2681 inter_int.le_id <> inter_int.driving_cr_le_id)
2682 OR
2683 (inter_int.intercompany_mode = 4 AND
2684 inter_int.type = 'D'))
2685 AND (inter_int.pay_acct IS NULL
2686 OR
2687 inter_int.pay_acct = -1
2688 OR
2689 (inter_int.pay_acct IS NOT NULL AND
2690 NOT EXISTS (SELECT 'Payables account not valid'
2691 FROM gl_code_combinations cc
2692 WHERE inter_int.pay_acct = cc.code_combination_id
2693 AND cc.detail_posting_allowed_flag = 'Y'
2694 AND cc.enabled_flag = 'Y'
2695 AND cc.summary_flag = 'N'
2696 AND nvl(cc.reference3, 'N') IN ('N', 'R')
2697 AND cc.template_id IS NULL
2698 AND (TRUNC(inter_int.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, inter_int.gl_date))
2699 AND TRUNC(NVL(cc.end_date_active, inter_int.gl_date))))));
2700
2701
2702 UPDATE fun_bal_headers_gt headers
2703 SET status = 'ERROR'
2704 WHERE EXISTS (SELECT 'Errors for Rec and Pay Accts'
2705 FROM FUN_BAL_ERRORS_GT errors
2706 WHERE headers.group_id = errors.group_id
2707 AND error_code IN ('FUN_INTER_PAY_NOT_ASSIGNED',
2708 'FUN_INTER_REC_NOT_ASSIGNED',
2709 'FUN_INTER_PAY_NO_DEFAULT',
2710 'FUN_INTER_REC_NO_DEFAULT',
2711 'FUN_INTER_PAY_NOT_VALID',
2712 'FUN_INTER_REC_NOT_VALID'))
2713 AND headers.status = 'OK';
2714
2715 DELETE FROM fun_bal_inter_int_gt inter_int
2716 WHERE EXISTS (SELECT group_id
2717 FROM fun_bal_headers_gt headers
2718 WHERE headers.status = 'ERROR'
2719 AND inter_int.group_id = headers.group_id);
2720
2721 /* Changes for Bug # 8212023 End */
2722
2723 -- Retrieve balancing segment value from the receivables and payables accounts
2724 update_inter_seg_val;
2725 /* 8200511 */
2726
2727 INSERT INTO FUN_INTER_ACCOUNTS_ADDL
2728 (FROM_LE_ID,
2729 LEDGER_ID,
2730 TO_LE_ID,
2731 CCID,
2732 TYPE,
2733 START_DATE,
2734 OBJECT_VERSION_NUMBER,
2735 CREATED_BY,
2736 CREATION_DATE,
2737 LAST_UPDATED_BY,
2738 LAST_UPDATE_DATE,
2739 LAST_UPDATE_LOGIN,
2740 TRANS_BSV,
2741 TP_BSV)
2742 SELECT DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
2743 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
2744 BAL_INTER_INT.DRIVING_DR_LE_ID)),
2745 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
2746 BAL_INTER_INT.LE_ID)),
2747 NULL),
2748 BAL_INTER_INT.LEDGER_ID,
2749 DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
2750 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
2751 BAL_INTER_INT.LE_ID)),
2752 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
2753 BAL_INTER_INT.DRIVING_CR_LE_ID)),
2754 NULL),
2755 BAL_INTER_INT.REC_ACCT,
2756 'R',
2757 SYSDATE,
2758 '1',
2759 FND_GLOBAL.USER_ID,
2760 SYSDATE,
2761 FND_GLOBAL.USER_ID,
2762 SYSDATE,
2763 fnd_global.login_id,
2764 BAL_INTER_INT.REC_BSV,
2765 BAL_INTER_INT.PAY_BSV
2766 FROM FUN_BAL_INTER_INT_GT BAL_INTER_INT
2767 WHERE BAL_INTER_INT.STATUS = 'OK'
2768 AND BAL_INTER_INT.REC_ACCT IS NOT NULL
2769 AND BAL_INTER_INT.PAY_BSV IS NOT NULL
2770 AND BAL_INTER_INT.REC_BSV IS NOT NULL
2771 AND NOT EXISTS(
2772 SELECT 'X'
2773 FROM FUN_INTER_ACCOUNTS_V ACCTV
2774 WHERE ACCTV.FROM_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
2775 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
2776 BAL_INTER_INT.DRIVING_DR_LE_ID)),
2777 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
2778 BAL_INTER_INT.LE_ID)),
2779 NULL)
2780 AND ACCTV.LEDGER_ID = BAL_INTER_INT.LEDGER_ID
2781 AND ACCTV.TO_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
2782 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
2783 BAL_INTER_INT.LE_ID)),
2784 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
2785 BAL_INTER_INT.DRIVING_CR_LE_ID)),
2786 NULL)
2787 AND ACCTV.CCID = BAL_INTER_INT.REC_ACCT
2788 AND ACCTV.TYPE = 'R'
2789 AND ACCTV.TRANS_BSV = BAL_INTER_INT.REC_BSV
2790 AND ACCTV.TP_BSV = BAL_INTER_INT.PAY_BSV
2791 );
2792
2793
2794
2795 INSERT INTO FUN_INTER_ACCOUNTS_ADDL
2796 (FROM_LE_ID,
2797 LEDGER_ID,
2798 TO_LE_ID,
2799 CCID,
2800 TYPE,
2801 START_DATE,
2802 OBJECT_VERSION_NUMBER,
2803 CREATED_BY,
2804 CREATION_DATE,
2805 LAST_UPDATED_BY,
2806 LAST_UPDATE_DATE,
2807 LAST_UPDATE_LOGIN,
2808 TRANS_BSV,
2809 TP_BSV)
2810 SELECT DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
2811 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
2812 BAL_INTER_INT.LE_ID)),
2813 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
2814 BAL_INTER_INT.DRIVING_CR_LE_ID)),
2815 NULL),
2816 BAL_INTER_INT.LEDGER_ID,
2817 DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
2818 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
2819 BAL_INTER_INT.DRIVING_DR_LE_ID)),
2820 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
2821 BAL_INTER_INT.LE_ID)),
2822 NULL),
2823 BAL_INTER_INT.PAY_ACCT,
2824 'P',
2825 SYSDATE,
2826 '1',
2827 FND_GLOBAL.USER_ID,
2828 SYSDATE,
2829 FND_GLOBAL.USER_ID,
2830 SYSDATE,
2831 fnd_global.login_id,
2832 BAL_INTER_INT.PAY_BSV,
2833 BAL_INTER_INT.REC_BSV
2834 FROM FUN_BAL_INTER_INT_GT BAL_INTER_INT
2835 WHERE BAL_INTER_INT.STATUS = 'OK'
2836 AND BAL_INTER_INT.PAY_ACCT IS NOT NULL
2837 AND BAL_INTER_INT.PAY_BSV IS NOT NULL
2838 AND BAL_INTER_INT.REC_BSV IS NOT NULL
2839 AND NOT EXISTS (
2840 SELECT 'X'
2841 FROM FUN_INTER_ACCOUNTS_V ACCTV
2842 WHERE ACCTV.FROM_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_DR_LE_ID,
2843 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.DRIVING_DR_LE_ID,
2844 BAL_INTER_INT.LE_ID)),
2845 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.LE_ID,
2846 BAL_INTER_INT.DRIVING_CR_LE_ID)),
2847 NULL)
2848 AND ACCTV.LEDGER_ID = BAL_INTER_INT.LEDGER_ID
2849 AND ACCTV.TO_LE_ID = DECODE(BAL_INTER_INT.INTERCOMPANY_MODE,1,BAL_INTER_INT.DRIVING_CR_LE_ID,
2850 2,(DECODE(BAL_INTER_INT.TYPE,'C',BAL_INTER_INT.LE_ID,
2851 BAL_INTER_INT.DRIVING_DR_LE_ID)),
2852 3,(DECODE(BAL_INTER_INT.TYPE,'D',BAL_INTER_INT.DRIVING_CR_LE_ID,
2853 BAL_INTER_INT.LE_ID)),
2854 NULL)
2855 AND ACCTV.CCID = BAL_INTER_INT.PAY_ACCT
2856 AND ACCTV.TYPE = 'P'
2857 AND ACCTV.TRANS_BSV = BAL_INTER_INT.PAY_BSV
2858 AND ACCTV.TP_BSV = BAL_INTER_INT.REC_BSV
2859 );
2860
2861
2862 /* 8200511 */
2863
2864 -- Insert intercompany balancing lines into the FUN_BAL_LINES_GT table. These resulting lines
2865 -- are not yet inserted into the results table as intracompany balancing might need to be performed
2866 -- for these lines also.
2867 INSERT INTO fun_bal_lines_gt lines (group_id, bal_seg_val, entered_amt_dr,
2868 entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
2869 accounted_amt_dr, accounted_amt_cr, ccid, generated)
2870 SELECT sum_lines.group_id,
2871 DECODE(gen.value, 'D', sum_lines.rec_bsv,
2872 'C', sum_lines.pay_bsv,
2873 NULL),
2874 DECODE(gen.value, 'D', DECODE(sum_lines.type, 'C', sum_lines.entered_amt_cr,
2875 'D', sum_lines.entered_amt_dr),
2876 NULL),
2877 DECODE(gen.value, 'C', DECODE(sum_lines.type, 'C', sum_lines.entered_amt_cr,
2878 'D', sum_lines.entered_amt_dr),
2879 NULL),
2880 sum_lines.entered_currency_code,
2881 sum_lines.exchange_date, sum_lines.exchange_rate, sum_lines.exchange_rate_type,
2882 DECODE(gen.value, 'D', DECODE(sum_lines.type, 'C', sum_lines.accounted_amt_cr,
2883 'D', sum_lines.accounted_amt_dr),
2884 NULL),
2885 DECODE(gen.value, 'C', DECODE(sum_lines.type, 'C', sum_lines.accounted_amt_cr,
2886 'D', sum_lines.accounted_amt_dr),
2887 NULL),
2888 DECODE(gen.value, 'C', sum_lines.pay_acct, 'D', sum_lines.rec_acct, NULL),
2889 'Y'
2890 FROM fun_bal_inter_int_gt sum_lines, fun_bal_generate_lines gen
2891 WHERE gen.value = DECODE(sum_lines.intercompany_mode,
2892 1, DECODE(sum_lines.type, gen.value, 'X', gen.value),
2893 2, DECODE(sum_lines.le_id, sum_lines.driving_dr_le_id, 'X', gen.value),
2894 3, DECODE(sum_lines.le_id, sum_lines.driving_cr_le_id, 'X', gen.value),
2895 4, DECODE(sum_lines.type, gen.value, 'X', gen.value));
2896 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2897 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_inter_bal.end', 'end');
2898 END IF;
2899
2900 RETURN FND_API.G_RET_STS_SUCCESS;
2901 END do_inter_bal;
2902
2903 FUNCTION do_intra_bal RETURN VARCHAR2 IS
2904 l_le_bsv_map_tab intra_le_bsv_map_tab_type;
2905 l_intra_int_tab intra_int_tab_type;
2906 l_le_bsv_map_count NUMBER;
2907 l_intra_int_count NUMBER;
2908 CURSOR l_le_bsv_map_cursor IS
2909 SELECT * FROM fun_bal_le_bsv_map_gt;
2910 CURSOR l_intra_int_cursor IS
2911 SELECT * FROM fun_bal_intra_int_gt;
2912 BEGIN
2913 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2914 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.begin', 'begin');
2915 END IF;
2916
2917
2918 -- Delete all records from FUN_BAL_LE_BSV_MAP_GT for reuse.
2919 DELETE FROM fun_bal_le_bsv_map_gt;
2920
2921 -- Insert records into FUN_BAL_LE_BSV_MAP_GT
2922 INSERT INTO fun_bal_le_bsv_map_gt(group_id, ledger_id, bal_seg_val, gl_date,
2923 je_source_name, je_category_name, clearing_bsv,
2924 chart_of_accounts_id, bal_seg_column_number,intercompany_column_number)
2925 SELECT DISTINCT hdrs.group_id, hdrs.ledger_id, lines.bal_seg_val, hdrs.gl_date,
2926 hdrs.je_source_name, hdrs.je_category_name, hdrs.clearing_bsv,
2927 hdrs.chart_of_accounts_id, hdrs.bal_seg_column_number, hdrs.intercompany_column_number
2928 FROM fun_bal_headers_gt hdrs, fun_bal_lines_gt lines
2929 WHERE hdrs.group_id = lines.group_id
2930 AND hdrs.status = 'OK';
2931
2932
2933 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2934 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.insert_le_bsv_map.finish', 'finish');
2935 END IF;
2936
2937
2938 -- Update Legal entity for each ledger, BSV combination. Legal entity can only be either null or has a specific value
2939 UPDATE fun_bal_le_bsv_map_gt bsv_le_map
2940 SET le_id =
2941 NVL((SELECT vals.legal_entity_id
2942 FROM gl_ledger_le_bsv_specific_v vals
2943 WHERE bsv_le_map.bal_seg_val = vals.segment_value
2944 AND (TRUNC(bsv_le_map.gl_date) BETWEEN TRUNC(NVL(vals.start_date, bsv_le_map.gl_date)) AND
2945 TRUNC(NVL(vals.end_date, bsv_le_map.gl_date)))
2946 AND bsv_le_map.ledger_id = vals.ledger_id
2947 ), -99);
2948
2949
2950 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2951 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_le.finish', 'finish');
2952 END IF;
2953
2954 -- Determine intracompany mode, driving_dr_bsv and driving_cr_bsv
2955 -- improve performance for not updating the lines of LE that uses clearing company
2956 UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
2957 SET (driving_dr_bsv, intracompany_mode) =
2958 (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
2959 index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
2960 MIN(le_bsv_map.bal_seg_val), SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val)))
2961 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
2962 WHERE le_bsv_map.group_id = lines.group_id
2963 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
2964 AND le_bsv_map.group_id = le_bsv_map_upd.group_id
2965 AND le_bsv_map.le_id = le_bsv_map_upd.le_id
2966 AND LE_BSV_MAP_UPD.LEDGER_ID = LE_BSV_MAP.LEDGER_ID
2967 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
2968 HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) >
2969 SUM(NVL(lines.accounted_amt_cr, 0)))
2970 OR
2971 ((SUM(NVL(lines.accounted_amt_dr, 0)) =
2972 SUM(NVL(lines.accounted_amt_cr, 0))) AND
2973 (SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) >
2974 SUM(DECODE(lines.exchange_rate, NULL,NVL(lines.entered_amt_cr,0), 0)))))
2975 WHERE le_bsv_map_upd.intracompany_mode IS NULL; -- OR le_bsv_map_upd.intracompany_mode <> 5;
2976
2977
2978 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
2979 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_intracompany_mode_1.finish', 'finish');
2980 END IF;
2981
2982
2983 --Delete records that has intracompany mode NULL
2984 DELETE FROM fun_bal_le_bsv_map_gt
2985 WHERE intracompany_mode IS NULL;
2986
2987 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map_del
2988 WHERE EXISTS (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
2989 index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
2990 'BSV already balanced'
2991 FROM fun_bal_lines_gt lines, fun_bal_le_bsv_map_gt le_bsv_map
2992 WHERE le_bsv_map_del.group_id = le_bsv_map.group_id
2993 AND le_bsv_map_del.le_id = le_bsv_map.le_id
2994 AND le_bsv_map_del.bal_seg_val = le_bsv_map.bal_seg_val
2995 AND le_bsv_map.group_id = lines.group_id
2996 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
2997 AND le_bsv_map_del.LEDGER_ID = le_bsv_map.LEDGER_ID
2998 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
2999 HAVING (SUM(NVL(lines.accounted_amt_dr, 0)) =
3000 SUM(NVL(lines.accounted_amt_cr, 0)))
3001 AND
3002 (SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)) =
3003 SUM(DECODE(lines.exchange_rate, NULL,NVL(lines.entered_amt_cr,0),0))));
3004
3005 UPDATE fun_bal_le_bsv_map_gt le_bsv_map_upd
3006 SET (driving_cr_bsv, intracompany_mode) =
3007 (SELECT /*+ leading(LE_BSV_MAP) use_nl(LINES)
3008 index(LE_BSV_MAP,FUN_BAL_LE_BSV_MAP_GT_N1) index(LINES,FUN_BAL_LINES_GT_N1) */
3009 MIN(le_bsv_map.bal_seg_val), DECODE(SUM(COUNT(DISTINCT(le_bsv_map.bal_seg_val))),
3010 1, DECODE(le_bsv_map_upd.intracompany_mode, 1, 1, 3),
3011 DECODE(le_bsv_map_upd.intracompany_mode, 1, 2, 4))
3012 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines
3013 WHERE le_bsv_map.group_id = lines.group_id
3014 AND le_bsv_map.bal_seg_val = lines.bal_seg_val
3015 AND le_bsv_map.group_id = le_bsv_map_upd.group_id
3016 AND le_bsv_map.le_id = le_bsv_map_upd.le_id
3017 AND LE_BSV_MAP_UPD.LEDGER_ID = LE_BSV_MAP.LEDGER_ID
3018 GROUP BY le_bsv_map.group_id, le_bsv_map.le_id, le_bsv_map.bal_seg_val
3019 --HAVING (le_bsv_map.clearing_option = '1D' OR le_bsv_map.clearing_option = '4M')
3020 -- No need for this having clause as it has brought to the higher level to check
3021 HAVING (SUM(NVL(lines.accounted_amt_cr, 0)) >
3022 SUM(NVL(lines.accounted_amt_dr, 0)))
3023 OR
3024 ((SUM(NVL(lines.accounted_amt_dr, 0)) =
3025 SUM(NVL(lines.accounted_amt_cr, 0))) AND
3026 (SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) >
3027 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0)))))
3028 WHERE le_bsv_map_upd.intracompany_mode IS NOT NULL;
3029 -- AND le_bsv_map_upd.intracompany_mode <> 5;
3030
3031 -- Don't balance for journals that does not have a credit or debit side
3032 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
3033 WHERE le_bsv_map.driving_dr_bsv IS NULL OR le_bsv_map.driving_cr_bsv IS NULL;
3034
3035
3036 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3037 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_intracompany_mode.finish', 'finish');
3038 END IF;
3039
3040
3041 -- Update intra_template_id in FUN_BAL_LE_BSV_MAP
3042 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3043 SET template_id =
3044 (SELECT opts.template_id
3045 FROM fun_balance_options opts
3046 WHERE le_bsv_map.ledger_id = opts.ledger_id
3047 AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
3048 AND le_bsv_map.je_source_name = opts.je_source_name
3049 AND le_bsv_map.je_category_name = opts.je_category_name
3050 AND opts.status_flag = 'Y')
3051 WHERE le_bsv_map.template_id IS NULL;
3052
3053 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3054 SET template_id =
3055 (SELECT opts.template_id
3056 FROM fun_balance_options opts
3057 WHERE le_bsv_map.ledger_id = opts.ledger_id
3058 AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
3059 AND le_bsv_map.je_source_name = opts.je_source_name
3060 AND opts.je_category_name = 'Other'
3061 AND opts.status_flag = 'Y')
3062 WHERE le_bsv_map.template_id IS NULL;
3063
3064 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3065 SET template_id =
3066 (SELECT opts.template_id
3067 FROM fun_balance_options opts
3068 WHERE le_bsv_map.ledger_id = opts.ledger_id
3069 AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
3070 AND opts.je_source_name = 'Other'
3071 AND le_bsv_map.je_category_name = opts.je_category_name
3072 AND opts.status_flag = 'Y')
3073 WHERE le_bsv_map.template_id IS NULL;
3074
3075 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3076 SET template_id =
3077 (SELECT opts.template_id
3078 FROM fun_balance_options opts
3079 WHERE le_bsv_map.ledger_id = opts.ledger_id
3080 AND Nvl(le_bsv_map.le_id, -99) = Nvl(opts.le_id,-99)
3081 -- No error here if null, since both le_id is -99 if no legal entity is specified
3082 AND opts.je_source_name = 'Other'
3083 AND opts.je_category_name = 'Other'
3084 AND opts.status_flag = 'Y')
3085 WHERE le_bsv_map.template_id IS NULL;
3086
3087 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, dr_bsv, cr_bsv)
3088 SELECT 'FUN_INTRA_RULE_NOT_ASSIGNED',
3089 le_bsv_map.group_id, le_bsv_map.template_id,
3090 DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
3091 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
3092 FROM fun_bal_le_bsv_map_gt le_bsv_map
3093 WHERE le_bsv_map.template_id IS NULL;
3094
3095 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3096 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.update_template.finish', 'finish');
3097 END IF;
3098
3099
3100 -- Logic to update balancing segment values are shown as follows:
3101 -- 1. Summary mode, No clearing
3102 -- Require the debit, credit accounts from one template only
3103 -- 2. Summary mode, clearing
3104 -- Require the debit, credit accounts from both templates
3105 -- 3. Detail mode, No clearing
3106 -- Require the debit, credit accounts from both templates
3107 -- 4. Detail mode, clearing
3108 -- Require the debit, credit accounts from both templates
3109 -- Retrieve the debit account ccid and credit account ccid with bal_seg_val in debit side
3110
3111
3112 -- Update balance_by, clearing_option, clearing_bsv in FUN_BAL_LE_BSV_MAP
3113
3114 -- Balancing API Changes, Start, Feb 2005
3115 -- Modified the following for the introduction of 'Enter Manually on Journal' option
3116 -- as a valid value for many_to_many_option (2E). This used to first be a value
3117 -- for the clearing_option
3118
3119 -- clearing_option many_to_many_option Jrnl Type CBSV From CBSV Reqd
3120 ---------------------------------------------------------------------------------
3121 -- 1A 2E Any journal Y
3122 -- 1A 1C Any journal/options Y
3123 ---------------------------------------------------------------------------------
3124 -- 3M 2E M:M (4) journal Y
3125 -- 3M 2E 1,2,3 journal N
3126 -- 3M 1C M:M (4) journal/options Y
3127 -- 3M 1C 1,2,3 journal N
3128 -- 3M 2D M:M (4) None N
3129 -- 3M 2D 1,2,3 journal N
3130 ---------------------------------------------------------------------------------
3131 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3132 SET (balance_by, clearing_option, clearing_bsv, many_to_many_option) =
3133 (SELECT opts.balance_by_flag, opts.clearing_option,
3134 DECODE (opts.clearing_option,
3135 '1A', DECODE (opts.many_to_many_option,
3136 '2E', le_bsv_map.clearing_bsv,
3137 '1C', Nvl(le_bsv_map.clearing_bsv,opts.clearing_bsv)),
3138 '3M', DECODE (opts.many_to_many_option,
3139 '2E', le_bsv_map.clearing_bsv,
3140 '1C', DECODE (le_bsv_map.intracompany_mode,
3141 4, Nvl(le_bsv_map.clearing_bsv,opts.clearing_bsv),
3142 le_bsv_map.clearing_bsv),
3143 '2D', DECODE (le_bsv_map.intracompany_mode,
3144 4, NULL,
3145 le_bsv_map.clearing_bsv)),
3146 NULL),
3147 opts.many_to_many_option
3148 FROM fun_balance_options opts
3149 WHERE le_bsv_map.template_id = opts.template_id
3150 AND opts.status_flag = 'Y');
3151
3152
3153 -- Note: A new intracompany mode 5 is introduced. Intracompany mode is 5 if clearing BSV is used
3154 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3155 SET intracompany_mode = 5
3156 WHERE le_bsv_map.clearing_bsv IS NOT NULL
3157 AND ((le_bsv_map.clearing_option = '1A') OR
3158 (le_bsv_map.clearing_option = '3M' AND
3159 le_bsv_map.intracompany_mode = 4 AND
3160 le_bsv_map.many_to_many_option IN ('2E', '1C')));
3161
3162 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_LE_BSV_MAP_GT');
3163
3164 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, dr_bsv, cr_bsv)
3165 SELECT 'FUN_INTRA_NO_CLEARING_BSV',
3166 le_bsv_map.group_id, le_bsv_map.template_id,
3167 DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
3168 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
3169 FROM fun_bal_le_bsv_map_gt le_bsv_map
3170 WHERE le_bsv_map.clearing_bsv IS NULL
3171 AND ((le_bsv_map.clearing_option = '1A') OR
3172 (le_bsv_map.clearing_option = '3M' AND
3173 le_bsv_map.intracompany_mode = 4 AND
3174 le_bsv_map.many_to_many_option IN ('2E', '1C')));
3175
3176 -- Check if the clearing BSV is valid (Bug 3345457)
3177 -- Perform this validation only if the Ledger BSV mapping is set to
3178 -- 'Specific' ('I')
3179 INSERT INTO FUN_BAL_ERRORS_GT(error_code, group_id, template_id, le_id, clearing_bsv,
3180 dr_bsv, cr_bsv)
3181 SELECT 'FUN_INTRA_CLEAR_BSV_INVALID',
3182 le_bsv_map.group_id, le_bsv_map.template_id,
3183 DECODE(le_bsv_map.le_id, -99, NULL, le_bsv_map.le_id),
3184 le_bsv_map.clearing_bsv,
3185 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv
3186 FROM fun_bal_le_bsv_map_gt le_bsv_map,
3187 gl_ledgers ledger
3188 WHERE le_bsv_map.clearing_bsv IS NOT NULL
3189 AND ledger.ledger_id = le_bsv_map.ledger_id
3190 AND ledger.bal_seg_value_option_code = 'I'
3191 AND NOT EXISTS
3192 (SELECT 'X'
3193 FROM gl_ledger_le_bsv_specific_v gl_seg
3194 WHERE gl_seg.ledger_id = le_bsv_map.ledger_id
3195 AND gl_seg.segment_value = le_bsv_map.clearing_bsv
3196 AND TRUNC(le_bsv_map.gl_date) BETWEEN TRUNC(NVL(gl_seg.start_date, le_bsv_map.gl_date))
3197 AND TRUNC(NVL(gl_seg.end_date, le_bsv_map.gl_date)));
3198
3199 -- Balancing API Changes, End , Feb 2005
3200
3201 UPDATE fun_bal_headers_gt headers
3202 SET STATUS = 'ERROR'
3203 WHERE EXISTS (SELECT 'Errors for no template or no clearing bsv or clearing bsv invalid'
3204 FROM FUN_BAL_ERRORS_GT errors
3205 WHERE headers.group_id = errors.group_id
3206 AND error_code IN ('FUN_INTRA_RULE_NOT_ASSIGNED',
3207 'FUN_INTRA_NO_CLEARING_BSV',
3208 'FUN_INTRA_CLEAR_BSV_INVALID'))
3209 AND headers.status = 'OK';
3210
3211 DELETE FROM fun_bal_le_bsv_map_gt le_bsv_map
3212 WHERE EXISTS (SELECT group_id
3213 FROM fun_bal_headers_gt headers
3214 WHERE headers.status = 'ERROR'
3215 AND le_bsv_map.group_id = headers.group_id);
3216
3217 -- Update ccid for each DB BSV and CR BSV /* change here */
3218 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3219 SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
3220 (SELECT dr_ccid, cr_ccid, 'N', 'N'
3221 FROM fun_balance_accounts accts
3222 WHERE le_bsv_map.template_id = accts.template_id
3223 AND ((le_bsv_map.intracompany_mode = 5
3224 AND le_bsv_map.bal_seg_val = accts.dr_bsv
3225 AND le_bsv_map.clearing_bsv = accts.cr_bsv)
3226 OR (le_bsv_map.intracompany_mode = 1
3227 AND le_bsv_map.bal_seg_val = accts.dr_bsv
3228 AND DECODE(le_bsv_map.bal_seg_val,
3229 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
3230 le_bsv_map.driving_dr_bsv) = accts.cr_bsv)
3231 OR (le_bsv_map.intracompany_mode = 2
3232 AND le_bsv_map.bal_seg_val = accts.dr_bsv
3233 AND le_bsv_map.driving_dr_bsv = accts.cr_bsv)
3234 OR (le_bsv_map.intracompany_mode = 3
3235 AND le_bsv_map.bal_seg_val = accts.dr_bsv
3236 AND le_bsv_map.driving_cr_bsv = accts.cr_bsv)));
3237
3238
3239 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3240 SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
3241 (SELECT dr_ccid, cr_ccid, 'N','N' /* Bug 14565212 Set _complete to N for all cases */
3242 FROM fun_balance_accounts accts
3243 WHERE le_bsv_map.template_id = accts.template_id
3244 AND ((le_bsv_map.intracompany_mode = 5
3245 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
3246 AND le_bsv_map.bal_seg_val = accts.dr_bsv)
3247 OR (le_bsv_map.intracompany_mode IN (1,2,3)
3248 AND le_bsv_map.bal_seg_val = accts.dr_bsv
3249 AND 'OTHER1234567890123456789012345' = accts.cr_bsv)))
3250 WHERE dr_cr_debit_ccid IS NULL; --OR dr_cr_credit_ccid IS NULL; No need to check both
3251
3252 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3253 SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
3254 (SELECT dr_ccid, cr_ccid, 'N','N' /* Bug 14565212 Set _complete to N for all cases */
3255 FROM fun_balance_accounts accts
3256 WHERE le_bsv_map.template_id = accts.template_id
3257 AND ((le_bsv_map.intracompany_mode = 5
3258 AND le_bsv_map.clearing_bsv = accts.cr_bsv
3259 AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
3260 OR (le_bsv_map.intracompany_mode = 1
3261 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
3262 AND DECODE(le_bsv_map.bal_seg_val,
3263 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
3264 le_bsv_map.driving_dr_bsv) = accts.cr_bsv)
3265 OR (le_bsv_map.intracompany_mode = 2
3266 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
3267 AND le_bsv_map.driving_dr_bsv = accts.cr_bsv)
3268 OR (le_bsv_map.intracompany_mode = 3
3269 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
3270 AND le_bsv_map.driving_cr_bsv = accts.cr_bsv)))
3271 WHERE dr_cr_debit_ccid IS NULL;
3272
3273 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3274 SET (dr_cr_debit_ccid, dr_cr_credit_ccid, dr_cr_debit_complete, dr_cr_credit_complete) =
3275 (SELECT dr_ccid, cr_ccid, 'N', 'N'
3276 FROM fun_balance_accounts accts
3277 WHERE le_bsv_map.template_id = accts.template_id
3278 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
3279 AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
3280 WHERE dr_cr_debit_ccid IS NULL ;
3281
3282 -- Upating cr_dr_debit_ccid, cr_dr_credit_ccid
3283 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3284 SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
3285 (SELECT dr_ccid, cr_ccid, 'N', 'N'
3286 FROM fun_balance_accounts accts
3287 WHERE le_bsv_map.template_id = accts.template_id
3288 AND ((le_bsv_map.intracompany_mode = 5
3289 AND le_bsv_map.bal_seg_val = accts.cr_bsv
3290 AND le_bsv_map.clearing_bsv = accts.dr_bsv)
3291 OR (le_bsv_map.intracompany_mode = 1
3292 AND le_bsv_map.bal_seg_val = accts.cr_bsv
3293 AND DECODE(le_bsv_map.bal_seg_val,
3294 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
3295 le_bsv_map.driving_dr_bsv) = accts.dr_bsv)
3296 OR (le_bsv_map.intracompany_mode = 2
3297 AND le_bsv_map.bal_seg_val = accts.cr_bsv
3298 AND le_bsv_map.driving_dr_bsv = accts.dr_bsv)
3299 OR (le_bsv_map.intracompany_mode = 3
3300 AND le_bsv_map.bal_seg_val = accts.cr_bsv
3301 AND le_bsv_map.driving_cr_bsv = accts.dr_bsv)));
3302
3303 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3304 SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
3305 (SELECT dr_ccid, cr_ccid,'N','N'/* Bug 14565212 Set _complete to N for all cases */
3306 FROM fun_balance_accounts accts
3307 WHERE le_bsv_map.template_id = accts.template_id
3308 AND ((le_bsv_map.intracompany_mode = 5
3309 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
3310 AND le_bsv_map.clearing_bsv = accts.dr_bsv)
3311 OR (le_bsv_map.intracompany_mode = 1
3312 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
3313 AND DECODE(le_bsv_map.bal_seg_val,
3314 le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
3315 le_bsv_map.driving_dr_bsv) = accts.dr_bsv)
3316 OR (le_bsv_map.intracompany_mode = 2
3317 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
3318 AND le_bsv_map.driving_dr_bsv = accts.dr_bsv)
3319 OR (le_bsv_map.intracompany_mode = 3
3320 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
3321 AND le_bsv_map.driving_cr_bsv = accts.dr_bsv)))
3322 WHERE cr_dr_debit_ccid IS NULL;
3323
3324 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3325 SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
3326 (SELECT dr_ccid, cr_ccid, 'N', 'N'/* SN */
3327 FROM fun_balance_accounts accts
3328 WHERE le_bsv_map.template_id = accts.template_id
3329 AND ((le_bsv_map.intracompany_mode = 5
3330 AND 'OTHER1234567890123456789012345' = accts.dr_bsv
3331 AND le_bsv_map.bal_seg_val = accts.cr_bsv)
3332 OR (le_bsv_map.intracompany_mode IN (1,2,3)
3333 AND le_bsv_map.bal_seg_val = accts.cr_bsv
3334 AND 'OTHER1234567890123456789012345' = accts.dr_bsv)))
3335 WHERE cr_dr_debit_ccid IS NULL;
3336
3337 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3338 SET (cr_dr_debit_ccid, cr_dr_credit_ccid, cr_dr_debit_complete, cr_dr_credit_complete) =
3339 (SELECT dr_ccid, cr_ccid, 'N', 'N' /* SN */
3340 FROM fun_balance_accounts accts
3341 WHERE le_bsv_map.template_id = accts.template_id
3342 AND 'OTHER1234567890123456789012345' = accts.cr_bsv
3343 AND 'OTHER1234567890123456789012345' = accts.dr_bsv)
3344 WHERE cr_dr_debit_ccid IS NULL;
3345
3346 /* Not done for checking ccid valid through gl_code_combinations directly
3347 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3348 SET (dr_cr_debit_ccid, dr_cr_debit_complete) =
3349 (SELECT code_combination_id, DECODE(ccid, NULL, 'N', 'Y')
3350 FROM gl_code_combinations cc1,
3351 gl_code_combinations cc2
3352 WHERE le_bsv_map.dr_cr_debit_ccid = cc1.code_combination_id
3353 AND cc1.segment1 = DECODE(le_bsv_map.bal_seg_column_no, 1, le_bsv_)
3354 WHERE dr_cr_debit_complete = 'N'
3355 */
3356
3357 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3358 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid.begin', 'begin');
3359 END IF;
3360
3361 --Bug: 11665072.
3362 -- Retrieve correct ccid by replacing balancing segment and intercompany segment
3363 UPDATE fun_bal_le_bsv_map_gt le_bsv_map
3364 SET dr_cr_debit_ccid =
3365 /*remove decode */ get_ccid(le_bsv_map.dr_cr_debit_ccid, le_bsv_map.chart_of_accounts_id, le_bsv_map.bal_seg_val,
3366 DECODE(le_bsv_map.intracompany_mode,
3367 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),
3368 2, le_bsv_map.driving_dr_bsv,
3369 3, le_bsv_map.driving_cr_bsv,
3370 5, le_bsv_map.clearing_bsv,
3371 4,getintersegvalue(le_bsv_map.dr_cr_debit_ccid,le_bsv_map.intercompany_column_number),NULL),
3372 le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
3373 le_bsv_map.gl_date),
3374 dr_cr_credit_ccid =
3375
3376 /* remove decode */ get_ccid(le_bsv_map.dr_cr_credit_ccid, le_bsv_map.chart_of_accounts_id,
3377 DECODE(le_bsv_map.intracompany_mode,
3378 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),
3379 2, le_bsv_map.driving_dr_bsv,
3380 3, le_bsv_map.driving_cr_bsv,
3381 4, le_bsv_map.bal_seg_val,
3382 5, le_bsv_map.clearing_bsv,
3383 NULL),
3384 DECODE(le_bsv_map.intracompany_mode, 4,getintersegvalue(le_bsv_map.dr_cr_credit_ccid,le_bsv_map.intercompany_column_number), le_bsv_map.bal_seg_val),
3385 le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
3386 le_bsv_map.gl_date),
3387 cr_dr_debit_ccid =
3388 /*remove decode*/ get_ccid(le_bsv_map.cr_dr_debit_ccid, le_bsv_map.chart_of_accounts_id,
3389 DECODE(le_bsv_map.intracompany_mode,
3390 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),
3391 2, le_bsv_map.driving_dr_bsv,
3392 3, le_bsv_map.driving_cr_bsv,
3393 4, le_bsv_map.bal_seg_val,
3394 5, le_bsv_map.clearing_bsv,
3395 NULL),
3396 DECODE(le_bsv_map.intracompany_mode, 4, getintersegvalue(le_bsv_map.cr_dr_debit_ccid,le_bsv_map.intercompany_column_number), le_bsv_map.bal_seg_val),
3397 le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
3398 le_bsv_map.gl_date),
3399 cr_dr_credit_ccid =
3400 /* decode*/
3401 get_ccid(le_bsv_map.cr_dr_credit_ccid, le_bsv_map.chart_of_accounts_id, le_bsv_map.bal_seg_val,
3402 DECODE(le_bsv_map.intracompany_mode,
3403 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),
3404 2, le_bsv_map.driving_dr_bsv,
3405 3, le_bsv_map.driving_cr_bsv,
3406 5, le_bsv_map.clearing_bsv,
3407 4,getintersegvalue(le_bsv_map.cr_dr_credit_ccid,le_bsv_map.intercompany_column_number),
3408 NULL),
3409 le_bsv_map.bal_seg_column_number, le_bsv_map.intercompany_column_number,
3410 le_bsv_map.gl_date);
3411
3412 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3413 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid.end', 'end');
3414 END IF;
3415
3416 IF g_debug = FND_API.G_TRUE THEN
3417 OPEN l_le_bsv_map_cursor;
3418 FETCH l_le_bsv_map_cursor BULK COLLECT INTO l_le_bsv_map_tab;
3419 l_le_bsv_map_count := l_le_bsv_map_cursor%ROWCOUNT;
3420 CLOSE l_le_bsv_map_cursor;
3421 ins_t_tables_intra_1_auto(l_le_bsv_map_tab, l_le_bsv_map_count);
3422 END IF;
3423
3424 -- Insert into FUN_BAL_INTRA_INT_GT for lines that require Intracompany Balancing
3425 INSERT INTO fun_bal_intra_int_gt(group_id, gl_date, driving_dr_bsv, driving_cr_bsv,
3426 intracompany_mode, balance_by, clearing_option, bal_seg_val, le_id, template_id, entered_currency_code,
3427 exchange_date, exchange_rate, exchange_rate_type, accounted_amt_cr, accounted_amt_dr,
3428 entered_amt_cr, entered_amt_dr,
3429 dr_cr_debit_ccid, dr_cr_credit_ccid, cr_dr_debit_ccid, cr_dr_credit_ccid,
3430 type, clearing_bsv)
3431 (SELECT hdrs.group_id, hdrs.gl_date, le_bsv_map.driving_dr_bsv,
3432 le_bsv_map.driving_cr_bsv, le_bsv_map.intracompany_mode, le_bsv_map.balance_by,
3433 le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
3434 le_bsv_map.le_id, le_bsv_map.template_id, lines.entered_currency_code,
3435 SYSDATE,DECODE(LINES.EXCHANGE_RATE, NULL, NULL,DECODE (DECODE(SIGN(NVL(LINES.ACCOUNTED_AMT_CR, 0) - NVL(LINES.ACCOUNTED_AMT_DR, 0)),
3436 1, 1,-1, -1,0, DECODE(SIGN((( NVL(LINES.ENTERED_AMT_CR, 0) - NVL(LINES.ENTERED_AMT_DR, 0)) ) - ( NVL(LINES.ACCOUNTED_AMT_DR, 0) - NVL(LINES.ACCOUNTED_AMT_CR, 0)) ),
3437 1, 1, -1)), -1, DECODE(LINES.ENTERED_AMT_DR,0,1,LINES.ACCOUNTED_AMT_DR / LINES.ENTERED_AMT_DR), DECODE(LINES.ENTERED_AMT_CR,0,1,LINES.ACCOUNTED_AMT_CR / LINES.ENTERED_AMT_CR))) EXCHANGE_RATE,'User', --10249721
3438 -- Bug 3223147 DECODE(SIGN(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
3439 -- 1, ABS(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)), NULL)
3440 lines.accounted_amt_cr,
3441 -- Bug 3223147 DECODE(SIGN(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
3442 -- -1, ABS(NVL(lines.accounted_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)), NULL)
3443 lines.accounted_amt_dr,
3444 -- Bug 3223147 DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)),
3445 -- 1, ABS(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)), NULL)
3446 lines.entered_amt_cr,
3447 -- Bug 3223147 DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)),
3448 -- -1, ABS(NVL(lines.entered_amt_cr, 0) - NVL(lines.entered_amt_dr, 0)), NULL)
3449 lines.entered_amt_dr,
3450 le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
3451 le_bsv_map.cr_dr_credit_ccid,
3452
3453 /* Bug 3223147
3454 DECODE(SIGN(NVL(lines.accounted_amt_cr, 0)-NVL(lines.accounted_amt_dr,0)),
3455 1, 'C',
3456 -1, 'D',
3457 0, DECODE(SIGN(NVL(lines.entered_amt_cr, 0) - NVL(lines.accounted_amt_dr, 0)),
3458 1, 'C',
3459 'D')) type,
3460 */
3461 DECODE(lines.accounted_amt_cr, NULL, DECODE(lines.entered_amt_cr, NULL, 'D', 'C'), 'C') type,
3462 le_bsv_map.clearing_bsv
3463 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines, fun_bal_headers_gt hdrs
3464 WHERE hdrs.group_id = lines.group_id
3465 AND lines.group_id = le_bsv_map.group_id
3466 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
3467 AND hdrs.status = 'OK'
3468 AND le_bsv_map.balance_by = 'D'
3469 UNION ALL
3470 SELECT hdrs.group_id, hdrs.gl_date, le_bsv_map.driving_dr_bsv,
3471 le_bsv_map.driving_cr_bsv, le_bsv_map.intracompany_mode, le_bsv_map.balance_by,
3472 le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
3473 le_bsv_map.le_id, le_bsv_map.template_id, lines.entered_currency_code,
3474 SYSDATE,DECODE(MAX(LINES.EXCHANGE_RATE), NULL, NULL,DECODE(( SUM(NVL(LINES.ENTERED_AMT_DR, 0)) - SUM(NVL(LINES.ENTERED_AMT_CR, 0))),0,1,
3475 ( SUM(NVL(LINES.ACCOUNTED_AMT_DR, 0)) - SUM(NVL(LINES.ACCOUNTED_AMT_CR, 0))) / (( SUM(NVL(LINES.ENTERED_AMT_DR, 0)) - SUM(NVL(LINES.ENTERED_AMT_CR, 0))) )) ) EXCHANGE_RATE,'User',
3476 --10249721
3477 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
3478 1, ABS(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))), NULL)
3479 accounted_amt_cr,
3480 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
3481 -1, ABS(SUM(NVL(lines.accounted_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))), NULL)
3482 accounted_amt_dr,
3483 DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))),
3484 1, ABS(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))), NULL)
3485 entered_amt_cr,
3486 DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))),
3487 -1, ABS(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.entered_amt_dr, 0))), NULL)
3488 entered_amt_dr,
3489 le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
3490 le_bsv_map.cr_dr_credit_ccid,
3491 DECODE(SIGN(SUM(NVL(lines.accounted_amt_cr, 0))-SUM(NVL(lines.accounted_amt_dr,0))),
3492 1, 'C',
3493 -1, 'D',
3494 0, DECODE(SIGN(SUM(NVL(lines.entered_amt_cr, 0)) - SUM(NVL(lines.accounted_amt_dr, 0))),
3495 1, 'C',
3496 'D')) type, le_bsv_map.clearing_bsv
3497 FROM fun_bal_le_bsv_map_gt le_bsv_map, fun_bal_lines_gt lines,
3498 fun_bal_headers_gt hdrs
3499 WHERE hdrs.group_id = lines.group_id
3500 AND lines.group_id = le_bsv_map.group_id
3501 AND lines.bal_seg_val = le_bsv_map.bal_seg_val
3502 AND hdrs.status = 'OK'
3503 AND le_bsv_map.balance_by = 'S'
3504 GROUP BY hdrs.group_id, hdrs.gl_date, hdrs.status, le_bsv_map.driving_dr_bsv, le_bsv_map.driving_cr_bsv,
3505 le_bsv_map.intracompany_mode, le_bsv_map.balance_by, le_bsv_map.clearing_option, le_bsv_map.bal_seg_val,
3506 le_bsv_map.le_id, lines.entered_currency_code, le_bsv_map.dr_cr_debit_ccid, le_bsv_map.dr_cr_credit_ccid, le_bsv_map.cr_dr_debit_ccid,
3507 le_bsv_map.cr_dr_credit_ccid, le_bsv_map.clearing_bsv, le_bsv_map.template_id
3508 HAVING SUM(NVL(lines.accounted_amt_cr, 0)) <> SUM(NVL(lines.accounted_amt_dr,0))
3509 OR (SUM(NVL(lines.accounted_amt_cr, 0)) = SUM(NVL(lines.accounted_amt_dr,0))
3510 AND
3511 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_cr, 0), 0)) <>
3512 SUM(DECODE(lines.exchange_rate, NULL, NVL(lines.entered_amt_dr, 0), 0))));
3513
3514 --FND_STATS.GATHER_TABLE_STATS(g_fun_schema, 'FUN_BAL_INTRA_INT_GT');
3515
3516 IF g_debug = FND_API.G_TRUE THEN
3517 OPEN l_intra_int_cursor;
3518 FETCH l_intra_int_cursor BULK COLLECT INTO l_intra_int_tab;
3519 l_intra_int_count := l_intra_int_cursor%ROWCOUNT;
3520 CLOSE l_intra_int_cursor;
3521 ins_t_tables_intra_2_auto(l_intra_int_tab, l_intra_int_count);
3522 END IF;
3523
3524 -- Insert intracompany balancing lines into the FUN_BAL_RESULTS_GT table.
3525 -- These resulting lines would be directly inserted into the results table
3526 -- 'C' normally means that a credit line should be created, but when run in detail
3527 -- mode, it could mean a debit line.
3528 INSERT INTO fun_bal_results_gt lines(group_id, bal_seg_val, entered_amt_dr,
3529 entered_amt_cr, entered_currency_code, exchange_date, exchange_rate, exchange_rate_type,
3530 accounted_amt_dr, accounted_amt_cr, ccid, dr_bsv, cr_bsv, acct_type, le_id, template_id, balancing_type)
3531 SELECT intra_lines.group_id,
3532 DECODE(intra_lines.intracompany_mode,
3533 1, bal_seg_val,
3534 2, DECODE(gen.value, 'C', intra_lines.driving_dr_bsv, intra_lines.bal_seg_val),
3535 3, DECODE(gen.value, 'C', intra_lines.bal_seg_val, intra_lines.driving_cr_bsv),
3536 4, bal_seg_val,
3537 5, DECODE(gen.value, intra_lines.type, intra_lines.clearing_bsv, intra_lines.bal_seg_val),
3538 NULL),
3539 DECODE(intra_lines.intracompany_mode,
3540 1, intra_lines.entered_amt_cr,
3541 2, DECODE(gen.value, 'C', intra_lines.entered_amt_dr,
3542 'D', intra_lines.entered_amt_cr,
3543 -1),
3544 3, DECODE(gen.value, 'C', intra_lines.entered_amt_cr,
3545 'D', intra_lines.entered_amt_dr,
3546 -1),
3547 4, intra_lines.entered_amt_cr,
3548 5, DECODE(gen.value, intra_lines.type, intra_lines.entered_amt_dr,
3549 intra_lines.entered_amt_cr)),
3550 DECODE(intra_lines.intracompany_mode,
3551 1, intra_lines.entered_amt_dr,
3552 2, DECODE(gen.value, 'C', intra_lines.entered_amt_cr,
3553 'D', intra_lines.entered_amt_dr,
3554 -1),
3555 3, DECODE(gen.value, 'C', intra_lines.entered_amt_dr,
3556 'D', intra_lines.entered_amt_cr,
3557 -1),
3558 4, intra_lines.entered_amt_dr,
3559 5, DECODE(gen.value, intra_lines.type, intra_lines.entered_amt_cr,
3560 intra_lines.entered_amt_dr)),
3561 intra_lines.entered_currency_code,
3562 intra_lines.exchange_date, intra_lines.exchange_rate, intra_lines.exchange_rate_type,
3563 DECODE(intra_lines.intracompany_mode,
3564 1, intra_lines.accounted_amt_cr,
3565 2, DECODE(gen.value, 'C', intra_lines.accounted_amt_dr,
3566 'D', intra_lines.accounted_amt_cr,
3567 -1),
3568 3, DECODE(gen.value, 'C', intra_lines.accounted_amt_cr,
3569 'D', intra_lines.accounted_amt_dr,
3570 -1),
3571 4, intra_lines.accounted_amt_cr,
3572 5, DECODE(gen.value, intra_lines.type, intra_lines.accounted_amt_dr,
3573 intra_lines.accounted_amt_cr)),
3574 DECODE(intra_lines.intracompany_mode,
3575 1, intra_lines.accounted_amt_dr,
3576 2, DECODE(gen.value, 'C', intra_lines.accounted_amt_cr,
3577 'D', intra_lines.accounted_amt_dr,
3578 -1),
3579 3, DECODE(gen.value, 'C', intra_lines.accounted_amt_dr,
3580 'D', intra_lines.accounted_amt_cr,
3581 -1),
3582 4, intra_lines.accounted_amt_dr,
3583 5, DECODE(gen.value, intra_lines.type, intra_lines.accounted_amt_cr,
3584 intra_lines.accounted_amt_dr)),
3585 DECODE(intra_lines.intracompany_mode,
3586 1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', cr_dr_credit_ccid, -- bal_seg_val
3587 'C', dr_cr_debit_ccid,
3588 -1),
3589 'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
3590 'D', cr_dr_credit_ccid,
3591 -1),
3592 -1),
3593 2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
3594 'D', cr_dr_credit_ccid,
3595 -1),
3596 'C', DECODE(intra_lines.type, 'C', dr_cr_credit_ccid, -- other_seg_val
3597 'D', cr_dr_debit_ccid,
3598 -1),
3599 -1),
3600 3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
3601 'D', cr_dr_credit_ccid,
3602 -1),
3603 'D', DECODE(intra_lines.type, 'D', cr_dr_debit_ccid, -- other_seg_val
3604 'C', dr_cr_credit_ccid,
3605 -1),
3606 -1),
3607 4, DECODE(gen.value, 'C', cr_dr_credit_ccid,
3608 'D', dr_cr_debit_ccid,
3609 -1),
3610 5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', cr_dr_credit_ccid, -- bal_seg_val
3611 'C', dr_cr_credit_ccid, -- other_seg_val
3612 -1),
3613 'D', DECODE(intra_lines.type, 'C', dr_cr_debit_ccid, -- bal_seg_val
3614 'D', cr_dr_debit_ccid, -- other_seg_val
3615 -1),
3616 -1)),
3617 DECODE(intra_lines.intracompany_mode,
3618 1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', driving_cr_bsv,
3619 'C', bal_seg_val,
3620 -1),
3621 'D', DECODE(intra_lines.type, 'C', bal_seg_val,
3622 'D', driving_dr_bsv,
3623 -1),
3624 -1),
3625 2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', bal_seg_val,
3626 'D', driving_dr_bsv,
3627 -1),
3628 'C', DECODE(intra_lines.type, 'C', bal_seg_val,
3629 'D', driving_dr_bsv,
3630 -1),
3631 -1),
3632 3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', bal_seg_val,
3633 'D', driving_cr_bsv,
3634 -1),
3635 'D', DECODE(intra_lines.type, 'D', driving_cr_bsv,
3636 'C', bal_seg_val,
3637 -1),
3638 -1),
3639 4, bal_seg_val,
3640 5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', clearing_bsv,
3641 'C', bal_seg_val,
3642 -1),
3643 'D', DECODE(intra_lines.type, 'C', bal_seg_val,
3644 'D', clearing_bsv,
3645 -1),
3646 -1)),
3647 DECODE(intra_lines.intracompany_mode,
3648 1, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', bal_seg_val,
3649 'C', driving_cr_bsv,
3650 -1),
3651 'D', DECODE(intra_lines.type, 'C', driving_dr_bsv,
3652 'D', bal_seg_val,
3653 -1),
3654 -1),
3655 2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', driving_dr_bsv,
3656 'D', bal_seg_val,
3657 -1),
3658 'C', DECODE(intra_lines.type, 'C', driving_dr_bsv,
3659 'D', bal_seg_val,
3660 -1),
3661 -1),
3662 3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', driving_cr_bsv,
3663 'D', bal_seg_val,
3664 -1),
3665 'D', DECODE(intra_lines.type, 'D', bal_seg_val,
3666 'C', driving_cr_bsv,
3667 -1),
3668 -1),
3669 4, bal_seg_val,
3670 5, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'D', bal_seg_val,
3671 'C', clearing_bsv,
3672 -1),
3673 'D', DECODE(intra_lines.type, 'C', clearing_bsv,
3674 'D', bal_seg_val,
3675 -1),
3676 -1)),
3677 DECODE(intra_lines.intracompany_mode,
3678 1, DECODE(intra_lines.type, 'D', 'C',
3679 'C', 'D', -1),
3680 2, DECODE(gen.value, 'D', DECODE(intra_lines.type, 'C', 'D',
3681 'D', 'C',
3682 -1),
3683 'C', DECODE(intra_lines.type, 'C', 'C',
3684 'D', 'D',
3685 -1),
3686 -1),
3687 3, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', 'D',
3688 'D', 'C',
3689 -1),
3690 'D', DECODE(intra_lines.type, 'D', 'D',
3691 'C', 'C',
3692 -1),
3693 -1),
3694 4, gen.value,
3695 5, gen.value,
3696 -1),
3697 intra_lines.le_id, intra_lines.template_id, 'R'
3698 FROM FUN_BAL_INTRA_INT_GT intra_lines, FUN_BAL_GENERATE_LINES gen
3699 WHERE gen.value = DECODE(intra_lines.intracompany_mode,
3700 1, DECODE(gen.value, 'C', DECODE(intra_lines.bal_seg_val,
3701 intra_lines.driving_cr_bsv, 'X', gen.value),
3702 'D', DECODE(intra_lines.bal_seg_val,
3703 intra_lines.driving_dr_bsv, 'X', gen.value),
3704 'X'),
3705 2, DECODE(intra_lines.bal_seg_val, intra_lines.driving_dr_bsv, 'X', gen.value),
3706 3, DECODE(intra_lines.bal_seg_val, intra_lines.driving_cr_bsv, 'X', gen.value),
3707 4, DECODE(gen.value, 'C', DECODE(intra_lines.type, 'C', 'X', gen.value),
3708 'D', DECODE(intra_lines.type, 'D', 'X', gen.value)),
3709 5, DECODE(bal_seg_val, clearing_bsv, 'X', gen.value), -- bug 3203634
3710 'X');
3711
3712 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3713 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid_concat_disp.begin', 'begin');
3714 END IF;
3715
3716 INSERT INTO fun_bal_errors_gt(error_code, group_id, template_id, le_id,
3717 dr_bsv, cr_bsv, acct_type, ccid_concat_display)
3718 SELECT DISTINCT DECODE(SIGN(NVL(results.ccid, 0)),
3719 -1, 'FUN_INTRA_CC_NOT_CREATED',
3720 0, 'FUN_INTRA_CC_NOT_CREATED',
3721 DECODE(cc.summary_flag,
3722 'Y', 'FUN_INTRA_CC_NOT_VALID',
3723 DECODE(cc.template_id,
3724 NULL, 'FUN_INTRA_CC_NOT_ACTIVE',
3725 'FUN_INTRA_CC_NOT_VALID'))),
3726 headers.group_id, results.template_id,
3727 DECODE(results.le_id, -99, NULL, results.le_id),
3728 results.dr_bsv, results.cr_bsv,
3729 results.acct_type, get_ccid_concat_disp(results.ccid, headers.chart_of_accounts_id,
3730 DECODE(results.acct_type, 'C', results.cr_bsv, results.dr_bsv),
3731 DECODE(results.acct_type, 'C', results.dr_bsv, results.cr_bsv),
3732 headers.bal_seg_column_number, headers.intercompany_column_number)
3733 FROM fun_bal_headers_gt headers, fun_bal_results_gt results, gl_code_combinations cc
3734 WHERE headers.group_id = results.group_id
3735 AND headers.status = 'OK'
3736 AND results.ccid = cc.code_combination_id(+)
3737 AND (results.ccid < 0
3738 OR results.ccid IS NULL -- NULL case should not happen, but just in case
3739 OR NOT (cc.detail_posting_allowed_flag = 'Y'
3740 AND cc.enabled_flag = 'Y'
3741 AND cc.summary_flag = 'N'
3742 AND nvl(cc.reference3, 'N') IN ('N', 'R')
3743 AND cc.template_id IS NULL
3744 AND (TRUNC(headers.gl_date) BETWEEN TRUNC(NVL(cc.start_date_active, headers.gl_date))
3745 AND TRUNC(NVL(cc.end_date_active, headers.gl_date)))));
3746
3747 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3748 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.get_ccid_concat_disp.end', 'end');
3749 END IF;
3750
3751 UPDATE fun_bal_headers_gt headers
3752 SET STATUS = 'ERROR'
3753 WHERE EXISTS (SELECT 'Invalid CCID error'
3754 FROM FUN_BAL_ERRORS_GT errors
3755 WHERE headers.group_id = errors.group_id
3756 AND error_code IN ('FUN_INTRA_CC_NOT_VALID',
3757 'FUN_INTRA_CC_NOT_CREATED',
3758 'FUN_INTRA_CC_NOT_ACTIVE'));
3759
3760
3761 DELETE FROM fun_bal_results_gt results
3762 WHERE EXISTS (SELECT group_id
3763 FROM fun_bal_headers_gt headers
3764 WHERE headers.status = 'ERROR'
3765 AND results.group_id = headers.group_id);
3766
3767 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3768 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.do_intra_bal.end', 'end');
3769 END IF;
3770
3771 RETURN FND_API.G_RET_STS_SUCCESS;
3772 END do_intra_bal;
3773
3774 PROCEDURE unexpected_error
3775 ( ERR_MESSAGE IN VARCHAR2)
3776 IS
3777 BEGIN
3778 INSERT INTO fun_bal_errors_gt(error_code, group_id, error_message)
3779 select 'FUN_INTER_UNEXPECTED_ERROR', FH.group_id, ERR_MESSAGE FROM FUN_BAL_HEADERS_GT FH;
3780 END unexpected_error;
3781
3782 PROCEDURE journal_balancing
3783 ( p_api_version IN NUMBER,
3784 p_init_msg_list IN VARCHAR2 ,
3785 p_validation_level IN NUMBER,
3786 p_debug IN VARCHAR2 ,
3787 x_return_status OUT NOCOPY VARCHAR2,
3788 x_msg_count OUT NOCOPY NUMBER,
3789 x_msg_data OUT NOCOPY VARCHAR2,
3790 p_product_code IN VARCHAR2 -- Valid values are GL and SLA for this release
3791 ) IS
3792
3793 l_api_name CONSTANT VARCHAR2(30) := 'JOURNAL_BALANCING';
3794 l_api_version CONSTANT NUMBER := 1.0;
3795 l_return_status VARCHAR2(1);
3796 l_balancing_custom_api fnd_profile_option_values.profile_option_value%TYPE := NULL;
3797 l_errors_count NUMBER;
3798
3799 BEGIN
3800
3801 -- variable p_validation_level is not used .
3802 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
3803
3804 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3805 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing.begin', 'begin');
3806 END IF;
3807
3808 -- Standard call to check for call compatibility.
3809 IF NOT FND_API.Compatible_API_Call ( l_api_version,
3810 p_api_version,
3811 l_api_name,
3812 G_PKG_NAME )
3813 THEN
3814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3815 END IF;
3816
3817 -- Initialize message list if p_init_msg_list is set to TRUE.
3818 IF FND_API.to_Boolean( nvl(p_init_msg_list,FND_API.G_FALSE)) THEN
3819 FND_MSG_PUB.initialize;
3820 END IF;
3821
3822 -- Initialize API return status to success
3823 x_return_status := FND_API.G_RET_STS_SUCCESS;
3824 -- API body
3825 g_product_code := p_product_code;
3826 g_debug := nvl(p_debug,FND_API.G_FALSE);
3827 l_return_status := do_init;
3828
3829 l_return_status := do_inter_bal;
3830
3831 l_return_status := do_intra_bal;
3832
3833 x_return_status := do_finalize;
3834
3835
3836 --Begin Bug: 16266089.
3837
3838 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3839
3840 fnd_profile.get('FUN_ENABLE_BALANCING_CUSTOM_API', l_balancing_custom_api);
3841
3842 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3843 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing', 'l_balancing_custom_api : '|| l_balancing_custom_api);
3844 END IF;
3845
3846 IF (l_balancing_custom_api IS NOT NULL) THEN
3847 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3848 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing', 'Calling custom balancing API');
3849 END IF;
3850
3851 execute immediate 'begin '|| l_balancing_custom_api || '; end;';
3852
3853 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3854 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing', 'End of custom balancing API');
3855 END IF;
3856
3857 SELECT COUNT(*) INTO l_errors_count
3858 FROM fun_bal_errors_gt;
3859
3860 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3861 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing', 'l_errors_count : '|| l_errors_count);
3862 END IF;
3863
3864 IF l_errors_count > 0 THEN
3865 x_return_status := FND_API.G_RET_STS_ERROR;
3866 END IF;
3867
3868 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing', 'x_return_status : '|| x_return_status);
3870 END IF;
3871
3872 END IF;
3873 END IF;
3874 --End Bug: 16266089.
3875
3876 -- End of API body.
3877 -- Standard call to get message count and if count is 1, get message info.
3878 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3879 p_data => x_msg_data);
3880
3881 IF (FND_LOG.LEVEL_PROCEDURE >= g_debug_level) THEN
3882 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'fun.plsql.fun_bal_pkg.journal_balancing.end', 'end');
3883 END IF;
3884
3885 EXCEPTION
3886 WHEN FND_API.G_EXC_ERROR THEN
3887 IF g_debug = FND_API.G_TRUE THEN
3888 unexpected_error(SQLERRM);
3889 -- Delete and save data in temporary tables
3890 do_save_in_error;
3891 END IF;
3892 IF (FND_LOG.LEVEL_ERROR>= g_debug_level) THEN
3893 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.journal_balancing.error', SUBSTR(SQLERRM,1, 4000));
3894 END IF;
3895 x_return_status := FND_API.G_RET_STS_ERROR ;
3896 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3897 p_data => x_msg_data);
3898 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3899 unexpected_error(SQLERRM);
3900 IF g_debug = FND_API.G_TRUE THEN
3901 -- Delete and save data in temporary tables
3902 do_save_in_error;
3903 END IF;
3904 IF (FND_LOG.LEVEL_ERROR>= g_debug_level) THEN
3905 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.journal_balancing.unexpected_error_norm', SUBSTR(SQLCODE ||
3906 ' : ' || SQLERRM,1, 4000));
3907 END IF;
3908 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3909 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3910 p_data => x_msg_data);
3911 WHEN OTHERS THEN
3912 unexpected_error(SQLERRM);
3913 IF g_debug = FND_API.G_TRUE THEN
3914 -- Delete and save data in temporary tables
3915 do_save_in_error;
3916 END IF;
3917 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3918 IF (FND_LOG.LEVEL_ERROR>= g_debug_level) THEN
3919 FND_LOG.STRING(FND_LOG.LEVEL_ERROR, 'fun.plsql.fun_bal_pkg.journal_balancing.unexpected_error_others', SUBSTR(SQLCODE ||
3920 ' : ' || SQLERRM,1, 4000));
3921 END IF;
3922 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3923 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3924 END IF;
3925 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3926 p_data => x_msg_data);
3927 END journal_balancing;
3928
3929
3930
3931 END FUN_BAL_PKG;