[Home] [Help]
PACKAGE BODY: APPS.FA_INTERCO_PVT
Source
1 PACKAGE BODY FA_INTERCO_PVT AS
2 /* $Header: FAVINCOB.pls 120.15.12010000.2 2008/07/31 07:02:42 sbhaskar ship $ */
3
4 g_group_reclass boolean;
5
6 FUNCTION do_all_books
7 (p_src_trans_rec in FA_API_TYPES.trans_rec_type,
8 p_src_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
9 p_dest_trans_rec in FA_API_TYPES.trans_rec_type,
10 p_dest_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
11 p_calling_fn in varchar2,
12 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
13
14 l_reporting_flag varchar2(1);
15 l_sob_tbl FA_CACHE_PKG.fazcrsob_sob_tbl_type;
16
17 l_calling_fn varchar2(30) := 'fa_interco_pvt.do_all_books';
18 interco_err EXCEPTION;
19
20 BEGIN
21
22 -- set up the global
23 if (p_log_level_rec.statement_level) then
24 fa_debug_pkg.add(l_calling_fn, 'p_claling_fn', p_calling_fn
25 ,p_log_level_rec => p_log_level_rec);
26 end if;
27 --exit from the function if intercompany posting not allowed fapost enhancement strat
28 if (nvl(fa_cache_pkg.fazcbc_record.intercompany_posting_flag,'Y') = 'N')then
29 if (p_log_level_rec.statement_level) then
30 fa_debug_pkg.add(l_calling_fn, 'Intercompany posting not allowed exiting', p_calling_fn
31 ,p_log_level_rec => p_log_level_rec);
32 end if;
33 return TRUE;
34 end if;
35 --fapost enhancement end
36 if (p_calling_fn = 'fa_group_reclass_pvt.do_grp_reclass' or
37 p_calling_fn = 'fa_group_process_groups_pkg.do_rcl') then
38 if (p_log_level_rec.statement_level) then
39 fa_debug_pkg.add(l_calling_fn, 'group reclass mode', 'TRUE'
40 ,p_log_level_rec => p_log_level_rec);
41 end if;
42 g_group_reclass := TRUE;
43 else
44 if (p_log_level_rec.statement_level) then
45 fa_debug_pkg.add(l_calling_fn, 'group reclass mode', 'FALSE'
46 ,p_log_level_rec => p_log_level_rec);
47 end if;
48 g_group_reclass := FALSE;
49 end if;
50 -- call the sob cache to get the table of sob_ids
51 if not FA_CACHE_PKG.fazcrsob
52 (x_book_type_code => p_src_asset_hdr_rec.book_type_code,
53 x_sob_tbl => l_sob_tbl
54 ,p_log_level_rec => p_log_level_rec) then
55 raise interco_err;
56 end if;
57
58 -- loop through each book starting with the primary and
59 -- call the private API for each
60
61 FOR l_sob_index in 0..l_sob_tbl.count LOOP
62
63 if (l_sob_index = 0) then
64 l_reporting_flag := 'P';
65 else
66 l_reporting_flag := 'R';
67 -- set the sob_id and currency context
68 fnd_profile.put('GL_SET_OF_BKS_ID', l_sob_tbl(l_sob_index));
69 fnd_client_info.set_currency_context (to_char(l_sob_tbl(l_sob_index)));
70 END IF;
71
72 -- call the cache to set the sob_id used for rounding and other lower
73 -- level code for each book.
74 if NOT fa_cache_pkg.fazcbcs(X_book => p_src_asset_hdr_rec.book_type_code
75 ,p_log_level_rec => p_log_level_rec) then
76 raise interco_err;
77 end if;
78
79 -- bug# 5383699 changed l_calling_fn to p_calling_fn
80 if not do_intercompany
81 (p_src_trans_rec => p_src_trans_rec ,
82 p_src_asset_hdr_rec => p_src_asset_hdr_rec ,
83 p_dest_trans_rec => p_dest_trans_rec ,
84 p_dest_asset_hdr_rec => p_dest_asset_hdr_rec ,
85 p_calling_fn => p_calling_fn ,
86 p_mrc_sob_type_code => l_reporting_flag
87 ,p_log_level_rec => p_log_level_rec) then raise interco_err;
88 end if;
89
90 end loop;
91
92 -- reset the gl sob info back to the primary book so fazcbc cache is ok
93 fnd_profile.put('GL_SET_OF_BKS_ID', p_src_asset_hdr_rec.set_of_books_id);
94 fnd_client_info.set_currency_context (p_src_asset_hdr_rec.set_of_books_id);
95
96 return true;
97
98
99 EXCEPTION
100
101 WHEN interco_err THEN
102 fa_srvr_msg.add_message(calling_fn => l_calling_fn
103 ,p_log_level_rec => p_log_level_rec);
104 return false;
105
106 when others then
107 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
108 ,p_log_level_rec => p_log_level_rec);
109 return false;
110
111 END do_all_books;
112
113
114 FUNCTION do_intercompany
115 (p_src_trans_rec in FA_API_TYPES.trans_rec_type,
116 p_src_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
117 p_dest_trans_rec in FA_API_TYPES.trans_rec_type,
118 p_dest_asset_hdr_rec in FA_API_TYPES.asset_hdr_rec_type,
119 p_calling_fn in varchar2,
120 p_mrc_sob_type_code in varchar2,
121 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
122
123 -- used for src
124 l_src_tbl interco_tbl_type;
125 l_src_count number;
126
127 -- used for dest
128 l_dest_trans_rec FA_API_TYPES.trans_rec_type;
129 l_dest_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
130 l_dest_tbl interco_tbl_type;
131 l_dest_count number;
132
133 -- used for summing amounts/account types src and dest
134 -- ok to sum as net amount (+ or -) will tell us src or dest when we allocate it down
135 l_src_summary_tbl interco_tbl_type;
136 l_src_summary_count number;
137
138 l_dest_summary_tbl interco_tbl_type;
139 l_dest_summary_count number;
140
141 -- used for getting overall amounts and determining where to allocate (src/dest)
142 l_summary_tbl interco_tbl_type;
143 l_summary_count number;
144
145 -- used for processing the distributions
146 l_dist_tbl dist_tbl_type;
147 l_dist_count number;
148 l_dist_tbl_count number;
149
150 -- used for faxinaj calls
151 l_adj fa_adjust_type_pkg.fa_adj_row_struct;
152 l_interco_ar_acct varchar2(250);
153 l_interco_ap_acct varchar2(250);
154 l_src_source_type_code varchar2(30);
155 l_dest_source_type_code varchar2(30);
156
157 -- not needed
158 -- l_src_cat_book_rec FA_CATEGORY_BOOKS%RowType;
159 -- l_dest_cat_book_rec FA_CATEGORY_BOOKS%RowType;
160
161
162 -- general variables
163 l_account_flex number;
164 l_column_name varchar2(30);
165 l_bal_segnum number;
166
167 l_seg_name VARCHAR2(30);
168 l_prompt VARCHAR2(80);
169 l_value_set_name VARCHAR2(60);
170
171 l_cursor_id number;
172 l_statement varchar2(2000);
173 l_dummy number;
174 l_found boolean;
175 l_balancing_seg varchar2(250);
176 l_sum_amount number;
177 l_distribution_id number;
178 l_code_combination_id number;
179 l_units number;
180
181 l_total_units number;
182 l_total_prorated_amount number;
183 l_prorated_amount number;
184
185 l_amount number;
186 l_count number;
187 l_loop boolean;
188 l_status boolean;
189
190 l_calling_fn varchar2(40) := 'fa_interco_pvt.do_intercompany';
191 interco_err exception;
192 done_exception exception;
193
194 BEGIN
195
196
197 l_account_flex := fa_cache_pkg.fazcbc_record.ACCOUNTING_FLEX_STRUCTURE;
198 --exit from the function if intercompany posting not allowed fapost enhancement strat
199 if (nvl(fa_cache_pkg.fazcbc_record.intercompany_posting_flag,'Y') <> 'Y')then
200 if (p_log_level_rec.statement_level) then
201 fa_debug_pkg.add(l_calling_fn, 'Intercompany posting not allowed exiting', p_calling_fn
202 ,p_log_level_rec => p_log_level_rec);
203 end if;
204 return TRUE;
205 end if;
206 --fapost enhancement end
207
208 -- VERIFY the following - think this is returning logical segment number not actual column!!!!
209 -- get balancing segment number for the accouting structure
210 /*
211 l_status := fnd_flex_apis.get_qualifier_segnum(appl_id => 101,
212 key_flex_code => 'GL#',
213 structure_number => l_account_flex,
214 flex_qual_name => 'GL_BALANCING',
215 segment_number => l_bal_segnum);
216
217 if not l_status then
218 raise interco_err;
219 end if;
220 */
221 /* Bug 5246620. Wrong segment_number retrieved */
222 SELECT s.segment_num INTO l_bal_segnum
223 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
224 fnd_segment_attribute_types sat
225 WHERE s.application_id = 101
226 AND s.id_flex_code = 'GL#'
227 AND s.id_flex_num = l_account_flex
228 AND s.enabled_flag = 'Y'
229 AND s.application_column_name = sav.application_column_name
230 AND sav.application_id = 101
231 AND sav.id_flex_code = 'GL#'
232 AND sav.id_flex_num = l_account_flex
233 AND sav.attribute_value = 'Y'
234 AND sav.segment_attribute_type = sat.segment_attribute_type
235 AND sat.application_id = 101
236 AND sat.id_flex_code = 'GL#'
237 AND sat.unique_flag = 'Y'
238 AND sat.segment_attribute_type = 'GL_BALANCING';
239
240
241 if (p_log_level_rec.statement_level) then
242 fa_debug_pkg.add(l_calling_fn, 'GL Balancing Segment Number', l_bal_segnum
243 ,p_log_level_rec => p_log_level_rec);
244 end if;
245
246
247 l_status := fnd_flex_apis.get_segment_info(
248 x_application_id => 101,
249 x_id_flex_code => 'GL#',
250 x_id_flex_num => l_account_flex,
251 x_seg_num => l_bal_segnum,
252 x_appcol_name => l_column_name,
253 x_seg_name => l_seg_name,
254 x_prompt => l_prompt,
255 x_value_set_name => l_value_set_name );
256
257 if not l_status then
258 raise interco_err;
259 end if;
260
261 if (p_log_level_rec.statement_level) then
262 fa_debug_pkg.add(l_calling_fn, 'GL Balancing Column Name', l_column_name
263 ,p_log_level_rec => p_log_level_rec);
264 fa_debug_pkg.add(l_calling_fn, 'processing', 'source'
265 ,p_log_level_rec => p_log_level_rec);
266 end if;
267
268
269 -- not needed
270 -- call the ccb cache for src
271 -- if not fa_cache_pkg.fazccb
272 -- (X_book => p_src_asset_hdr_rec.book_type_code,
273 -- X_cat_id => p_src_asset_cat_rec.category_id
274 --) then raise interco_err;
275 -- end if;
276 --
277 -- l_src_cat_book_rec := fa_cache_pkg.fazccb_record;
278
279
280 -- get each balancing segment and the sum of the amounts
281 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
282
283 -- bug# 5383699
284 if (p_calling_fn = 'fa_group_reclass_pvt.do_grp_reclass' or
285 p_calling_fn = 'fa_group_process_groups_pkg.do_rcl') then
286
287 l_statement :=
288 ' select nvl(glcc1.' || l_column_name || ', glcc2.' || l_column_name || '),
289 sum(decode(adjustment_type,
290 ''COST'', decode (debit_credit_flag,
291 ''CR'', adjustment_amount,
292 adjustment_amount * -1),
293 ''CIP COST'', decode (debit_credit_flag,
294 ''CR'', adjustment_amount,
295 adjustment_amount * -1),
296 ''COST CLEARING'', decode (debit_credit_flag,
297 ''CR'', adjustment_amount,
298 adjustment_amount * -1),
299 ''RESERVE'', decode (debit_credit_flag,
300 ''CR'', adjustment_amount,
301 adjustment_amount * -1),
302 ''REVAL RESERVE'', decode (debit_credit_flag,
303 ''CR'', adjustment_amount,
304 adjustment_amount * -1),
305 0)) ' ||
306 ' from fa_adjustments adj,
307 fa_distribution_history dh,
308 gl_code_combinations glcc1,
309 gl_code_combinations glcc2
310 where adj.asset_id = :p_asset_id
311 and adj.book_type_code = :p_book
312 and adj.period_counter_created = :p_period_counter
313 and adj.transaction_header_id = :p_thid
314 and adj.distribution_id = dh.distribution_id
315 and dh.code_combination_id = glcc2.code_combination_id
316 and adj.code_combination_id(+) = glcc1.code_combination_id
317 -- and adj.track_member_flag is null
318 group by nvl(glcc1.' || l_column_name || ', glcc2.' || l_column_name || ')';
319
320 else
321
322 l_statement :=
323 ' select nvl(glcc1.' || l_column_name || ', glcc2.' || l_column_name || '),
324 sum(decode(adjustment_type,
325 ''COST'', decode (debit_credit_flag,
326 ''CR'', adjustment_amount,
327 adjustment_amount * -1),
328 ''CIP COST'', decode (debit_credit_flag,
329 ''CR'', adjustment_amount,
330 adjustment_amount * -1),
331 ''COST CLEARING'', decode (debit_credit_flag,
332 ''CR'', adjustment_amount,
333 adjustment_amount * -1),
334 ''RESERVE'', decode (debit_credit_flag,
335 ''CR'', adjustment_amount,
336 adjustment_amount * -1),
337 ''REVAL RESERVE'', decode (debit_credit_flag,
338 ''CR'', adjustment_amount,
339 adjustment_amount * -1),
340 0)) ' ||
341 ' from fa_adjustments adj,
342 fa_distribution_history dh,
343 gl_code_combinations glcc1,
344 gl_code_combinations glcc2
345 where adj.asset_id = :p_asset_id
346 and adj.book_type_code = :p_book
347 and adj.period_counter_created = :p_period_counter
348 and adj.transaction_header_id = :p_thid
349 and adj.distribution_id = dh.distribution_id
350 and dh.code_combination_id = glcc2.code_combination_id
351 and adj.code_combination_id(+) = glcc1.code_combination_id
352 and adj.track_member_flag is null
353 group by nvl(glcc1.' || l_column_name || ', glcc2.' || l_column_name || ')';
354
355 end if;
356
357 DBMS_SQL.PARSE(l_cursor_id, l_statement, DBMS_SQL.NATIVE);
358
359 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_asset_id', p_src_asset_hdr_rec.asset_id);
360 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_book', p_src_asset_hdr_rec.book_type_code);
364
361 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_period_counter', fa_cache_pkg.fazcbc_record.last_period_counter + 1);
362 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_thid', p_src_trans_rec.transaction_header_id);
363
365 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_balancing_seg, 30);
366 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2, l_sum_amount);
367
368 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
369
370 loop
371
372 l_src_count := l_src_tbl.count;
373
374 if DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
375 exit;
376 end if;
377
378 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_balancing_seg);
379 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 2, l_sum_amount);
380
381 if (p_log_level_rec.statement_level) then
382 fa_debug_pkg.add(l_calling_fn, 'balancing_seg for first source tbl: ', l_balancing_seg
383 ,p_log_level_rec => p_log_level_rec);
384 fa_debug_pkg.add(l_calling_fn, 'sum_amount for first source tbl: ', l_sum_amount
385 ,p_log_level_rec => p_log_level_rec);
386 end if;
387
388
389 -- add these values to the table
390 l_src_tbl(l_src_count + 1).balancing_segment := l_balancing_seg;
391 l_src_tbl(l_src_count + 1).amount := l_sum_amount;
392
393 end loop;
394
395 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
396
397
398 if (p_log_level_rec.statement_level) then
399 fa_debug_pkg.add(l_calling_fn, 'source table count', l_src_tbl.count
400 ,p_log_level_rec => p_log_level_rec);
401 end if;
402
403
404
405 -- and now for each destination
406 if (p_dest_trans_rec.transaction_header_id is not null) then
407
408 if (p_log_level_rec.statement_level) then
409 fa_debug_pkg.add(l_calling_fn, 'processing', 'destination'
410 ,p_log_level_rec => p_log_level_rec);
411 end if;
412
413 l_dest_asset_hdr_rec := p_dest_asset_hdr_rec;
414 l_dest_trans_rec := p_dest_trans_rec;
415
416 -- not needed
417 -- call the ccb cache for src
418 -- if not fa_cache_pkg.fazccb
419 -- (X_book => l_dest_asset_hdr_rec.book_type_code,
420 -- X_cat_id => l_dest_asset_cat_rec.category_id
421 --) then raise interco_err;
422 -- end if;
423 --
424 -- l_dest_cat_book_rec := fa_cache_pkg.fazccb_record;
425
426
427 -- get each balancing segment and the sum of the amounts
428 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
429
430 -- bug# 5383699
431 if (p_calling_fn = 'fa_group_reclass_pvt.do_grp_reclass' or
432 p_calling_fn = 'fa_group_process_groups_pkg.do_rcl') then
433
434 l_statement :=
435 ' select nvl(glcc1.' || l_column_name || ', glcc2.' || l_column_name || '),
436 sum(decode(adjustment_type,
437 ''COST'', decode (debit_credit_flag,
438 ''DR'', adjustment_amount,
439 adjustment_amount * -1),
440 ''CIP COST'', decode (debit_credit_flag,
441 ''DR'', adjustment_amount,
442 adjustment_amount * -1),
443 ''COST CLEARING'', decode (debit_credit_flag,
444 ''DR'', adjustment_amount,
445 adjustment_amount * -1),
446 ''RESERVE'', decode (debit_credit_flag,
447 ''DR'', adjustment_amount,
448 adjustment_amount * -1),
449 ''REVAL RESERVE'', decode (debit_credit_flag,
450 ''DR'', adjustment_amount,
451 adjustment_amount * -1),
452 0)) ' ||
453 ' from fa_adjustments adj,
454 fa_distribution_history dh,
455 gl_code_combinations glcc1,
456 gl_code_combinations glcc2
457 where adj.asset_id = :p_asset_id
458 and adj.book_type_code = :p_book
459 and adj.period_counter_created = :p_period_counter
460 and adj.transaction_header_id = :p_thid
461 and adj.distribution_id = dh.distribution_id
462 and dh.code_combination_id = glcc2.code_combination_id
463 and adj.code_combination_id(+) = glcc1.code_combination_id
464 -- and adj.track_member_flag is null
465 group by nvl(glcc1.' || l_column_name || ', glcc2.' || l_column_name || ')';
466
467 else
468 l_statement :=
469 ' select nvl(glcc1.' || l_column_name || ', glcc2.' || l_column_name || '),
470 sum(decode(adjustment_type,
471 ''COST'', decode (debit_credit_flag,
472 ''DR'', adjustment_amount,
473 adjustment_amount * -1),
474 ''CIP COST'', decode (debit_credit_flag,
475 ''DR'', adjustment_amount,
476 adjustment_amount * -1),
477 ''COST CLEARING'', decode (debit_credit_flag,
478 ''DR'', adjustment_amount,
479 adjustment_amount * -1),
480 ''RESERVE'', decode (debit_credit_flag,
481 ''DR'', adjustment_amount,
482 adjustment_amount * -1),
483 ''REVAL RESERVE'', decode (debit_credit_flag,
484 ''DR'', adjustment_amount,
485 adjustment_amount * -1),
486 0)) ' ||
487 ' from fa_adjustments adj,
488 fa_distribution_history dh,
489 gl_code_combinations glcc1,
490 gl_code_combinations glcc2
491 where adj.asset_id = :p_asset_id
492 and adj.book_type_code = :p_book
493 and adj.period_counter_created = :p_period_counter
494 and adj.transaction_header_id = :p_thid
495 and adj.distribution_id = dh.distribution_id
496 and dh.code_combination_id = glcc2.code_combination_id
497 and adj.code_combination_id(+) = glcc1.code_combination_id
498 and adj.track_member_flag is null
499 group by nvl(glcc1.' || l_column_name || ', glcc2.' || l_column_name || ')';
500 end if;
501
502
503 DBMS_SQL.PARSE(l_cursor_id, l_statement, DBMS_SQL.NATIVE);
504
505 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_asset_id', p_dest_asset_hdr_rec.asset_id);
506 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_book', p_dest_asset_hdr_rec.book_type_code);
507 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_period_counter', fa_cache_pkg.fazcbc_record.last_period_counter + 1);
508 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_thid', p_dest_trans_rec.transaction_header_Id);
509
510 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_balancing_seg, 30);
511 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2, l_sum_amount);
512
513 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
514
515 loop
516
517 l_dest_count := l_dest_tbl.count;
518
519 if DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
520 exit;
521 end if;
522
523 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_balancing_seg);
524 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 2, l_sum_amount);
525
526 if (p_log_level_rec.statement_level) then
527 fa_debug_pkg.add(l_calling_fn, 'balancing_seg for first dest tbl: ', l_balancing_seg
528 ,p_log_level_rec => p_log_level_rec);
529 fa_debug_pkg.add(l_calling_fn, 'sum_amount for first dest tbl: ', l_sum_amount
530 ,p_log_level_rec => p_log_level_rec);
531 end if;
532
533 -- add these values to the table
534 l_dest_tbl(l_dest_count + 1).balancing_segment := l_balancing_seg;
535 l_dest_tbl(l_dest_count + 1).amount := l_sum_amount;
536
537 end loop;
538
539 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
540
541 if (p_log_level_rec.statement_level) then
542 fa_debug_pkg.add(l_calling_fn, 'dest table count', l_dest_tbl.count
543 ,p_log_level_rec => p_log_level_rec);
544 end if;
545
546
547 else
548 -- set dest = src for later use in the distribution processing
549 l_dest_asset_hdr_rec := p_src_asset_hdr_rec;
550 l_dest_trans_rec := p_src_trans_rec;
551
552 if (p_log_level_rec.statement_level) then
553 fa_debug_pkg.add(l_calling_fn, 'not processing', 'destination'
554 ,p_log_level_rec => p_log_level_rec);
555 end if;
556
557 end if;
558
559
560
561 -- sum all accounts into a single amount per balancing segment
562 -- first create a new table indexed by balancing segment
563
564 if (p_log_level_rec.statement_level) then
565 fa_debug_pkg.add(l_calling_fn, 'summing', 'source amounts'
566 ,p_log_level_rec => p_log_level_rec);
567 end if;
568
569 for l_src_count in 1..l_src_tbl.count loop
570
571 l_found := FALSE;
572
573 for l_src_summary_count in 1 .. l_src_summary_tbl.count loop
574
575 if (l_src_tbl(l_src_count).balancing_segment = l_src_summary_tbl(l_src_summary_count).balancing_segment) then
576 l_src_summary_tbl(l_src_summary_count).amount :=
577 l_src_summary_tbl(l_src_summary_count).amount +
578 l_src_tbl(l_src_count).amount;
579 l_found := TRUE;
580 exit;
581 end if;
582
583 end loop;
584
585 -- if not found, add to the summary table
589 l_src_summary_tbl(l_src_summary_count + 1).amount := l_src_tbl(l_src_count).amount;
586 if not l_found then
587 l_src_summary_count := l_src_summary_tbl.count;
588 l_src_summary_tbl(l_src_summary_count + 1).balancing_segment := l_src_tbl(l_src_count).balancing_segment ;
590 end if;
591
592 end loop;
593
594 if (p_log_level_rec.statement_level) then
595 fa_debug_pkg.add(l_calling_fn, 'source summary table count', l_src_summary_tbl.count
596 ,p_log_level_rec => p_log_level_rec);
597 end if;
598
599
600
601 -- now do the same for the destination
602
603 if (p_log_level_rec.statement_level) then
604 fa_debug_pkg.add(l_calling_fn, 'summing', 'dest amounts'
605 ,p_log_level_rec => p_log_level_rec);
606 end if;
607
608 for l_dest_count in 1 ..l_dest_tbl.count loop
609
610 l_found := FALSE;
611
612 for l_dest_summary_count in 1 .. l_dest_summary_tbl.count loop
613
614 if (l_dest_tbl(l_dest_count).balancing_segment = l_dest_summary_tbl(l_dest_summary_count).balancing_segment) then
615 l_dest_summary_tbl(l_dest_summary_count).amount :=
616 l_dest_summary_tbl(l_dest_summary_count).amount +
617 l_dest_tbl(l_dest_count).amount;
618 l_found := TRUE;
619 exit;
620 end if;
621
622 end loop;
623
624
625 -- if not found, add to the summary table
626 if not l_found then
627 l_dest_summary_count := l_dest_summary_tbl.count;
628 l_dest_summary_tbl(l_dest_summary_count + 1).balancing_segment := l_dest_tbl(l_dest_count).balancing_segment ;
629 l_dest_summary_tbl(l_dest_summary_count + 1).amount := l_dest_tbl(l_dest_count).amount;
630 end if;
631
632 end loop;
633
634 if (p_log_level_rec.statement_level) then
635 fa_debug_pkg.add(l_calling_fn, 'dest summary table count', l_dest_summary_tbl.count
636 ,p_log_level_rec => p_log_level_rec);
637 end if;
638
639
640 -- remove all the 0 amount rows
641 if (p_log_level_rec.statement_level) then
642 fa_debug_pkg.add(l_calling_fn, 'removing', 'source 0 amounts'
643 ,p_log_level_rec => p_log_level_rec);
644 end if;
645
646 -- BUG# 3537535
647 -- changing this loop. since its count is changing, that
648 -- cant be the end check condition or it will result in
649 -- NO_DATA_FOUND. Instead, just continue to loop until
650 -- no 0 rows have found in a given execution
651 --
652 -- same fix has been made to dest and final summary tables
653
654 l_loop := TRUE;
655
656 while (l_loop) loop
657
658 l_loop := FALSE;
659 l_count := l_src_summary_tbl.count;
660
661 for l_src_summary_count in 1 .. l_src_summary_tbl.count loop
662
663 if (l_src_summary_count > l_count) then
664 exit;
665 end if;
666
667 if (l_src_summary_tbl(l_src_summary_count).amount = 0) then
668
669 l_loop := true;
670 l_src_summary_tbl.delete(l_src_summary_count);
671
672 -- reset the values so there is no missing member for future use
673 l_count := l_src_summary_tbl.count ;
674
675 if (l_count > 0) then
676
677 for i in l_src_summary_count .. l_count loop
678 -- copy the next member into the current one
679 l_src_summary_tbl(i) := l_src_summary_tbl(i+1);
680 end loop;
681
682 -- delete the last member in the array which is now a duplicate
683 l_src_summary_tbl.delete(l_count + 1);
684 end if;
685
686 end if;
687 end loop;
688 end loop;
689
690 if (p_log_level_rec.statement_level) then
691 fa_debug_pkg.add(l_calling_fn, 'source summary table count', l_src_summary_tbl.count
692 ,p_log_level_rec => p_log_level_rec);
693 end if;
694
695
696
697 -- same for dest
698
699 if (p_log_level_rec.statement_level) then
700 fa_debug_pkg.add(l_calling_fn, 'removing', 'dest 0 amounts'
701 ,p_log_level_rec => p_log_level_rec);
702 end if;
703
704 l_loop := TRUE;
705
706 while (l_loop) loop
707
708 l_loop := FALSE;
709 l_count := l_dest_summary_tbl.count;
710
711 for l_dest_summary_count in 1 .. l_dest_summary_tbl.count loop
712
713 if (l_dest_summary_count > l_count) then
714 exit;
715 end if;
716
717 if (l_dest_summary_tbl(l_dest_summary_count).amount = 0) then
718
719 l_loop := true;
720 l_dest_summary_tbl.delete(l_dest_summary_count );
721
722 -- reset the values so there is no missing member for future use
723 l_count := l_dest_summary_tbl.count ;
724
725 if (l_count > 0) then
726 for i in l_dest_summary_count .. l_count loop
727 -- copy the next member into the current one
728 l_dest_summary_tbl(i) := l_dest_summary_tbl(i+1);
729 end loop;
730
731 -- delete the last member in the array which is now a duplicate
735 end if;
732 l_dest_summary_tbl.delete(l_count + 1);
733 end if;
734
736
737 end loop;
738
739 end loop;
740
741 if (p_log_level_rec.statement_level) then
742 fa_debug_pkg.add(l_calling_fn, 'after', 'removing 0 cost dest rows'
743 ,p_log_level_rec => p_log_level_rec);
744 fa_debug_pkg.add(l_calling_fn, 'dest summary table count', l_dest_summary_tbl.count
745 ,p_log_level_rec => p_log_level_rec);
746 end if;
747
748
749
750 -- now find the matches
751 if (p_log_level_rec.statement_level) then
752 fa_debug_pkg.add(l_calling_fn, 'finding', 'balancing segment matches'
753 ,p_log_level_rec => p_log_level_rec);
754 end if;
755
756 if (l_src_summary_tbl.count = 0 and
757 l_dest_summary_tbl.count = 0) then
758
759 -- no interco effects at all
760 if (p_log_level_rec.statement_level) then
761 fa_debug_pkg.add(l_calling_fn, 'no intercompany impacts found' ,''
762 ,p_log_level_rec => p_log_level_rec);
763 end if;
764
765 raise done_exception;
766
767 elsif (l_dest_summary_tbl.count = 0) then
768
769 -- one sided
770 if (p_log_level_rec.statement_level) then
771 fa_debug_pkg.add(l_calling_fn, 'found: ', 'source intercompany impacts only'
772 ,p_log_level_rec => p_log_level_rec);
773 end if;
774
775 l_summary_tbl := l_src_summary_tbl;
776
777 -- loop through the rows and flip the src on the negative amounts in order to
778 -- process the interco ap an ar effects... note thisgoes against the premise
779 -- of the current interco transfer logic where src always gets the INTERCO AR
780 -- regardless of sign, but there's no better way to do it since we need to
781 -- know the net effects.. (maybe derive cost for the asset and go off that?)
782
783 for l_summary_count in 1..l_summary_tbl.count loop
784
785 if (sign(l_summary_tbl(l_summary_count).amount) < 0) then
786 l_summary_tbl(l_summary_count).amount := -l_summary_tbl(l_summary_count).amount;
787 l_summary_tbl(l_summary_count).type := 'DEST';
788 else
789 l_summary_tbl(l_summary_count).type := 'SRC';
790 end if;
791
792 end loop;
793
794
795 elsif (l_src_summary_tbl.count = 0) then -- THIS SHOULDN'T HAPPEN!!!!!!!!!!
796
797 -- one sided
798 if (p_log_level_rec.statement_level) then
799 fa_debug_pkg.add(l_calling_fn, 'found: ', 'dest intercompany impacts only'
800 ,p_log_level_rec => p_log_level_rec);
801 end if;
802
803 l_summary_tbl := l_dest_summary_tbl;
804
805 -- in this case, do we need to flip the signs???
806
807 else
808 -- cross asset intercompany effects
809 -- need to determine overall impacts
810
811 if (p_log_level_rec.statement_level) then
812 fa_debug_pkg.add(l_calling_fn, 'found: ', 'source and destination intercompany impacts'
813 ,p_log_level_rec => p_log_level_rec);
814 end if;
815
816 l_summary_tbl := l_src_summary_tbl;
817
818 -- set type to src for all lines
819 for i in 1..l_summary_tbl.count loop
820 l_summary_tbl(i).type := 'SRC';
821 end loop;
822
823
824 -- still iffy in interco transfers, we always charce AR to source
825 -- regardless if amount is -ve or +ve
826 --
827 -- believe we can check abs values and post the difference
828 -- to the larger value.. thus driving check is currently on the
829 -- absolute values rather than on the sign of the difference
830 --
831 -- but this premise wouldn't work for intra-asset effects (like add/adj)
832
833 if (p_log_level_rec.statement_level) then
834 fa_debug_pkg.add(l_calling_fn, 'combining: ', 'source and destination impacts'
835 ,p_log_level_rec => p_log_level_rec);
836 end if;
837
838
839 for l_dest_summary_count in 1..l_dest_summary_tbl.count loop
840
841 l_found := false;
842
843 for l_summary_count in 1..l_summary_tbl.count loop
844
845 if (l_dest_summary_tbl(l_dest_summary_count).balancing_segment = l_summary_tbl(l_summary_count).balancing_segment) then
846
847 -- match found - now add the two and place any different with correct sign to
848 -- allocate it to the desired side of the transaction
849
850 -- BUG# 2726345
851 -- changing the following to minus instead of add
852 -- since we're coming up with same signs and amounts
853
854 l_amount := l_summary_tbl(l_summary_count).amount - l_dest_summary_tbl(l_dest_summary_count).amount;
855
856 if (sign(l_amount) = 0) then
857
858 l_summary_tbl(l_summary_count).amount := 0;
859
860 elsif (abs(l_dest_summary_tbl(l_dest_summary_count).amount) >
861 abs(l_summary_tbl(l_summary_count).amount )) then
862 l_amount := -l_amount;
863
864 l_summary_tbl(l_summary_count).amount := l_amount;
865 l_summary_tbl(l_summary_count).type := 'DEST';
866
870
867 else -- source drives
868 l_summary_tbl(l_summary_count).amount := l_amount;
869 end if;
871 l_found := true;
872 exit;
873
874 -- BUG# 3468256 (last part)
875 -- removed the else and put outside loop as we don't want to add a row multiple times
876 -- when more than one receiving segment is involved
877
878 end if;
879
880 end loop;
881
882 if (not l_found) then
883 -- if we reach here, match not found and we didn't exit the loop, add it to table
884 l_count := l_summary_tbl.count + 1;
885 l_summary_tbl(l_count).balancing_segment := l_dest_summary_tbl(l_dest_summary_count).balancing_segment;
886 l_summary_tbl(l_count).amount := l_dest_summary_tbl(l_dest_summary_count).amount;
887 l_summary_tbl(l_count).type := l_dest_summary_tbl(l_dest_summary_count).type;
888 end if;
889
890 end loop;
891
892 if (p_log_level_rec.statement_level) then
893 fa_debug_pkg.add(l_calling_fn, 'summary table count', l_summary_tbl.count
894 ,p_log_level_rec => p_log_level_rec);
895 end if;
896
897
898
899 -- remove all the 0 amount rows
900 if (p_log_level_rec.statement_level) then
901 fa_debug_pkg.add(l_calling_fn, 'removing: ', '0 amount summary intercompany lines'
902 ,p_log_level_rec => p_log_level_rec);
903 end if;
904
905
906 l_loop := TRUE;
907
908 while (l_loop) loop
909
910 l_loop := FALSE;
911 l_count := l_summary_tbl.count;
912
913 for l_summary_count in 1 .. l_summary_tbl.count loop
914
915 if (l_summary_count > l_count) then
916 exit;
917 end if;
918
919 if (l_summary_tbl(l_summary_count).amount = 0) then
920
921 l_loop := true;
922 l_summary_tbl.delete(l_summary_count);
923
924 -- reset the values so there is no missing member for future use
925 l_count := l_summary_tbl.count ;
926
927 if (l_count > 0) then
928 for i in l_summary_count .. l_count loop
929 -- copy the next member into the current one
930 l_summary_tbl(i) := l_summary_tbl(i+1);
931 end loop;
932
933 -- delete the last member in the array which is now a duplicate
934 l_summary_tbl.delete(l_count + 1);
935 end if;
936
937 end if;
938
939 end loop;
940
941 end loop;
942
943 if (p_log_level_rec.statement_level) then
944 fa_debug_pkg.add(l_calling_fn, 'summary table count', l_summary_tbl.count
945 ,p_log_level_rec => p_log_level_rec);
946 end if;
947
948 end if;
949
950 -- load the constant values for each asset
951 if (p_log_level_rec.statement_level) then
952 fa_debug_pkg.add(l_calling_fn, 'setting up for: ', 'faxinaj calls'
953 ,p_log_level_rec => p_log_level_rec);
954 end if;
955
956
957 l_interco_ar_acct := fa_cache_pkg.fazcbc_record.ar_intercompany_acct;
958 l_interco_ap_acct := fa_cache_pkg.fazcbc_record.ap_intercompany_acct;
959
960
961 -- set the source type code...
962 -- currently only transaction that should call this engine
963 -- are non-distirbution ones, such that this value should
964 -- equate to the contents of trx_type_code... would need to
965 -- expand this, if called for UNIT ADJ, etc at any time
966
967 l_src_source_type_code := p_src_trans_rec.transaction_type_code;
968
969
970 if (l_src_source_type_code = 'GROUP ADJUSTMENT' or
971 l_src_source_type_code = 'GROUP ADDITION') then
972 l_src_source_type_code := 'ADJUSTMENT';
973 end if;
974
975 if (p_dest_trans_rec.transaction_header_id is not null) then
976 l_dest_source_type_code := p_dest_trans_rec.transaction_type_code;
977
978 if (l_dest_source_type_code = 'GROUP ADJUSTMENT' or
979 l_dest_source_type_code = 'GROUP ADDITION') then
980 l_dest_source_type_code := 'ADJUSTMENT';
981 end if;
982
983 else
984 l_dest_source_type_code := l_src_source_type_code;
985 end if;
986
987 -- BUG# 3543423
988 -- any impact from group reclass will insure we insert the
989 -- source type as ADJUSTMENT to avoid out-of-balance batches
990 if g_group_reclass then
991 l_src_source_type_code := 'ADJUSTMENT';
992 l_dest_source_type_code := 'ADJUSTMENT';
993 end if;
994
995 if (p_log_level_rec.statement_level) then
996 fa_debug_pkg.add(l_calling_fn, 'src source_type_code', l_src_source_type_code
997 ,p_log_level_rec => p_log_level_rec);
998 fa_debug_pkg.add(l_calling_fn, 'dest source_type_code', l_dest_source_type_code
999 ,p_log_level_rec => p_log_level_rec);
1000 end if;
1001
1002 l_adj.period_counter_created := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1003 l_adj.period_counter_adjusted := fa_cache_pkg.fazcbc_record.last_period_counter + 1;
1007 l_adj.selection_retid := 0;
1004 l_adj.last_update_date := p_src_trans_rec.transaction_date_entered;
1005 l_adj.selection_mode := FA_ADJUST_TYPE_PKG.FA_AJ_SINGLE;
1006 l_adj.selection_thid := 0;
1008 l_adj.leveling_flag := TRUE;
1009 l_adj.flush_adj_flag := TRUE;
1010 l_adj.gen_ccid_flag := TRUE;
1011 l_adj.annualized_adjustment := 0;
1012 l_adj.asset_invoice_id := 0;
1013 l_adj.deprn_override_flag := '';
1014 l_adj.mrc_sob_type_code := p_mrc_sob_type_code;
1015
1016
1017 -- ???
1018 l_adj.current_units := 1;
1019
1020
1021 -- loop through the distributions on each side and post the difference
1022 -- note that in this proposal, there is no distinction between source
1023 -- and destination. If the src and destination share even a portion
1024 -- between the same segment, the interco values will cascade to all of them
1025
1026 if (p_log_level_rec.statement_level) then
1027 fa_debug_pkg.add(l_calling_fn, 'looping: ', 'through summary interco records'
1028 ,p_log_level_rec => p_log_level_rec);
1029 fa_debug_pkg.add(l_calling_fn, 'summary_tbl.count: ', l_summary_tbl.count
1030 ,p_log_level_rec => p_log_level_rec);
1031 end if;
1032
1033 for l_summary_count in 1..l_summary_tbl.count loop
1034
1035 if (p_log_level_rec.statement_level) then
1036 fa_debug_pkg.add(l_calling_fn, 'looping through summary records, count: ', l_summary_count
1037 ,p_log_level_rec => p_log_level_rec);
1038 fa_debug_pkg.add(l_calling_fn, 'summary amount', l_summary_tbl(l_summary_count).amount);
1039 end if;
1040
1041 l_dist_tbl.delete;
1042 l_dist_tbl_count := 0;
1043 l_prorated_amount := 0;
1044 l_total_prorated_amount := 0;
1045
1046
1047 -- get each balancing segment and the sum of the amounts
1048 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1049
1050 l_statement :=
1051 ' select distinct
1052 dh.distribution_id,
1053 dh.code_combination_id,
1054 dh.units_assigned
1055 from fa_adjustments adj,
1056 fa_distribution_history dh,
1057 gl_code_combinations glcc
1058 where adj.asset_id = :p_asset_id
1059 and adj.book_type_code = :p_book_type_code
1060 and adj.period_counter_created = :p_period_counter_created
1061 and adj.transaction_header_id = :p_thid
1062 and adj.distribution_id = dh.distribution_id
1063 and dh.asset_id = :p_asset_id
1064 and dh.code_combination_id = glcc.code_combination_id
1065 and glcc. ' || l_column_name || ' = :p_balancing_segment ';
1066
1067 DBMS_SQL.PARSE(l_cursor_id, l_statement, DBMS_SQL.NATIVE);
1068
1069 -- need to use local for dest variable for intra-asset trxs
1070 if (l_summary_tbl(l_summary_count).type = 'SRC') then
1071 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_asset_id', p_src_asset_hdr_rec.asset_id);
1072 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_book_type_code', p_src_asset_hdr_rec.book_type_code);
1073 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_period_counter_created', fa_cache_pkg.fazcbc_record.last_period_counter + 1);
1074 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_thid', p_src_trans_rec.transaction_header_id);
1075 else
1076 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_asset_id', l_dest_asset_hdr_rec.asset_id);
1077 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_book_type_code', l_dest_asset_hdr_rec.book_type_code);
1078 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_period_counter_created', fa_cache_pkg.fazcbc_record.last_period_counter + 1);
1079 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_thid', l_dest_trans_rec.transaction_header_id);
1080 end if;
1081
1082 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_balancing_segment', l_summary_tbl(l_summary_count).balancing_segment);
1083
1084 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_distribution_id);
1085 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2, l_code_combination_id);
1086 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 3, l_units);
1087
1088 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
1089
1090 loop
1091
1092 if DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
1093
1094
1095 if (p_log_level_rec.statement_level) then
1096 fa_debug_pkg.add(l_calling_fn, 'dist cursor: ', 'no more rows fetched'
1097 ,p_log_level_rec => p_log_level_rec);
1098 end if;
1099
1100 -- get total units
1101 l_total_units := 0;
1102 for l_dist_count in 1..l_dist_tbl.count loop
1103 l_total_units := l_total_units + l_dist_tbl(l_dist_count).units;
1104 end loop;
1105
1106 if (p_log_level_rec.statement_level) then
1107 fa_debug_pkg.add(l_calling_fn, 'dist cursor: ', 'looping through dists'
1108 ,p_log_level_rec => p_log_level_rec);
1109 end if;
1110
1111 for l_dist_count in 1..l_dist_tbl.count loop
1112 -- process the rows into fa_adj for that balancing segment
1113 -- call faxinaj to insert the amounts (flush them too)
1114
1115 if (p_log_level_rec.statement_level) then
1116 fa_debug_pkg.add(l_calling_fn, 'inside ', 'dist loop'
1120 l_adj.code_combination_id := l_dist_tbl(l_dist_count).code_combination_id;
1117 ,p_log_level_rec => p_log_level_rec);
1118 end if;
1119
1121 l_adj.distribution_id := l_dist_tbl(l_dist_count).distribution_id;
1122
1123 if (p_log_level_rec.statement_level) then
1124 fa_debug_pkg.add(l_calling_fn, 'l_summary_tbl(l_summary_count).amount', l_summary_tbl(l_summary_count).amount);
1125 fa_debug_pkg.add(l_calling_fn, 'l_total_prorated_amount',l_total_prorated_amount
1126 ,p_log_level_rec => p_log_level_rec);
1127 fa_debug_pkg.add(l_calling_fn, 'l_total_units', l_total_units
1128 ,p_log_level_rec => p_log_level_rec);
1129 fa_debug_pkg.add(l_calling_fn, 'l_dist_tbl(l_dist_count).units', l_dist_tbl(l_dist_count).units);
1130 end if;
1131
1132 if (l_dist_count = l_dist_tbl.count) then
1133
1134 l_adj.adjustment_amount := l_summary_tbl(l_summary_count).amount - l_total_prorated_amount;
1135 else
1136 l_prorated_amount := l_summary_tbl(l_summary_count).amount * (l_dist_tbl(l_dist_count).units / l_total_units);
1137
1138 if not fa_utils_pkg.faxrnd
1139 (x_amount => l_prorated_amount,
1140 x_book => p_src_asset_hdr_rec.book_type_code
1141 ,p_log_level_rec => p_log_level_rec) then raise interco_err;
1142 end if;
1143
1144 l_total_prorated_amount := l_total_prorated_amount + l_prorated_amount;
1145 l_adj.adjustment_amount := l_prorated_amount;
1146
1147 end if;
1148
1149 if (p_log_level_rec.statement_level) then
1150 fa_debug_pkg.add(l_calling_fn, 'l_adj.adj_amount', l_adj.adjustment_amount
1151 ,p_log_level_rec => p_log_level_rec);
1152 fa_debug_pkg.add(l_calling_fn, 'setting up ', 'local variables'
1153 ,p_log_level_rec => p_log_level_rec);
1154 end if;
1155
1156 if (l_summary_tbl(l_summary_count).type = 'SRC') then
1157 if (p_log_level_rec.statement_level) then
1158 fa_debug_pkg.add(l_calling_fn, 'processing', 'src'
1159 ,p_log_level_rec => p_log_level_rec);
1160 end if;
1161
1162 l_adj.transaction_header_id := p_src_trans_rec.transaction_header_id;
1163 l_adj.asset_id := p_src_asset_hdr_rec.asset_id;
1164 l_adj.book_type_code := p_src_asset_hdr_rec.book_type_code;
1165 l_adj.debit_credit_flag := 'DR';
1166 l_adj.adjustment_type := 'INTERCO AR';
1167 l_adj.account_type := 'AR_INTERCOMPANY_ACCT';
1168 l_adj.account := l_interco_ar_acct;
1169 if (p_log_level_rec.statement_level) then
1170 fa_debug_pkg.add(l_calling_fn, 'source_type_code', l_src_source_type_code
1171 ,p_log_level_rec => p_log_level_rec);
1172 end if;
1173
1174 l_adj.source_type_code := l_src_source_type_code;
1175
1176 else
1177 if (p_log_level_rec.statement_level) then
1178 fa_debug_pkg.add(l_calling_fn, 'processing', 'dest'
1179 ,p_log_level_rec => p_log_level_rec);
1180 end if;
1181
1182 -- need to use locals for intra-assets
1183 l_adj.transaction_header_id := l_dest_trans_rec.transaction_header_id;
1184 l_adj.asset_id := l_dest_asset_hdr_rec.asset_id;
1185 l_adj.book_type_code := l_dest_asset_hdr_rec.book_type_code;
1186
1187 l_adj.debit_credit_flag := 'CR';
1188 l_adj.adjustment_type := 'INTERCO AP';
1189 l_adj.account_type := 'AR_INTERCOMPANY_ACCT';
1190 l_adj.account := l_interco_ap_acct;
1191 l_adj.source_type_code := l_dest_source_type_code;
1192 end if;
1193
1194 if (p_log_level_rec.statement_level) then
1195 fa_debug_pkg.add(l_calling_fn, 'calling: ', 'faxinaj'
1196 ,p_log_level_rec => p_log_level_rec);
1197 end if;
1198
1199 if not FA_INS_ADJUST_PKG.faxinaj
1200 (l_adj,
1201 p_src_trans_rec.who_info.last_update_date,
1202 p_src_trans_rec.who_info.last_updated_by,
1203 p_src_trans_rec.who_info.last_update_login
1204 ,p_log_level_rec => p_log_level_rec) then
1205 raise interco_err;
1206 end if;
1207
1208 end loop;
1209
1210 exit; -- exit distribution loop and continue to next balancing segment
1211
1212 end if;
1213
1214 if (p_log_level_rec.statement_level) then
1218
1215 fa_debug_pkg.add(l_calling_fn, 'populating: ', 'values from dist cursor'
1216 ,p_log_level_rec => p_log_level_rec);
1217 end if;
1219 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_distribution_id);
1220 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 2, l_code_combination_id);
1221 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 3, l_units);
1222
1223
1224 -- add these values to the table
1225 l_dist_tbl_count := l_dist_tbl.count + 1;
1226 l_dist_tbl(l_dist_tbl_count).distribution_id := l_distribution_id;
1227 l_dist_tbl(l_dist_tbl_count).code_combination_id := l_code_combination_id;
1228 l_dist_tbl(l_dist_tbl_count).units := l_units;
1229
1230 end loop;
1231
1232 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1233
1234 end loop;
1235
1236 raise done_exception;
1237
1238 EXCEPTION
1239 WHEN done_exception THEN
1240 return true;
1241
1242 WHEN interco_err THEN
1243 fa_srvr_msg.add_message(calling_fn => l_calling_fn
1244 ,p_log_level_rec => p_log_level_rec);
1245 return false;
1246
1247 when others then
1248 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1249 ,p_log_level_rec => p_log_level_rec);
1250 return false;
1251
1252
1253 END do_intercompany;
1254
1255 --------------------------------------------------------------------------------
1256
1257 function validate_grp_interco
1258 (p_asset_hdr_rec in fa_api_types.asset_hdr_rec_type,
1259 p_trans_rec in fa_api_types.trans_rec_type,
1260 p_asset_type_rec in fa_api_types.asset_type_rec_type,
1261 p_group_asset_id in number,
1262 p_asset_dist_tbl in FA_API_TYPES.asset_dist_tbl_type,
1263 p_calling_fn in varchar2,
1264 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN is
1265
1266 CURSOR c_asset_distributions IS
1267 select code_combination_id
1268 from fa_distribution_history
1269 where asset_id = p_asset_hdr_rec.asset_id
1270 and date_ineffective is null;
1271
1272 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
1273
1274 TYPE l_bal_tbl_type is table of varchar2(30) index by binary_integer;
1275 l_bal_tbl1 l_bal_tbl_type;
1276 l_bal_tbl2 l_bal_tbl_type;
1277 l_bal_count1 number;
1278 l_bal_count2 number;
1279 l_dist_tbl_count number;
1280
1281 l_cursor_id number;
1282 l_statement varchar2(4000);
1283 l_dummy number;
1284 l_found boolean;
1285
1286 l_account_flex number;
1287 l_balancing_seg varchar2(250);
1288 l_bal_segnum number;
1289 l_column_name varchar2(30);
1290 l_seg_name VARCHAR2(30);
1291 l_prompt VARCHAR2(80);
1292 l_value_set_name VARCHAR2(60);
1293 l_ccid_string varchar2(4000) := '';
1294 l_status boolean;
1295
1296 l_ccid number;
1297 l_asset_id number;
1298
1299 l_calling_fn varchar2(35) := 'fa_interco_pvt.validate_grp_interco';
1300 interco_err exception;
1301
1302 begin
1303
1304 if (p_log_level_rec.statement_level) then
1305 fa_debug_pkg.add(l_calling_fn, 'inside', 'validate interco code'
1306 ,p_log_level_rec => p_log_level_rec);
1307 end if;
1308
1309 l_account_flex := fa_cache_pkg.fazcbc_record.ACCOUNTING_FLEX_STRUCTURE;
1310
1311 /*
1312 l_status := fnd_flex_apis.get_qualifier_segnum(appl_id => 101,
1313 key_flex_code => 'GL#',
1314 structure_number => l_account_flex,
1315 flex_qual_name => 'GL_BALANCING',
1316 segment_number => l_bal_segnum);
1317
1318 if not l_status then
1319 raise interco_err;
1320 end if;
1321 */
1322 /* Bug 5246620. Wrong segment_number retrieved */
1323 SELECT s.segment_num INTO l_bal_segnum
1324 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
1325 fnd_segment_attribute_types sat
1326 WHERE s.application_id = 101
1327 AND s.id_flex_code = 'GL#'
1328 AND s.id_flex_num = l_account_flex
1329 AND s.enabled_flag = 'Y'
1330 AND s.application_column_name = sav.application_column_name
1331 AND sav.application_id = 101
1332 AND sav.id_flex_code = 'GL#'
1333 AND sav.id_flex_num = l_account_flex
1334 AND sav.attribute_value = 'Y'
1335 AND sav.segment_attribute_type = sat.segment_attribute_type
1336 AND sat.application_id = 101
1337 AND sat.id_flex_code = 'GL#'
1338 AND sat.unique_flag = 'Y'
1339 AND sat.segment_attribute_type = 'GL_BALANCING';
1340
1341
1342 if (p_log_level_rec.statement_level) then
1343 fa_debug_pkg.add(l_calling_fn, 'GL Balancing Segment Number', l_bal_segnum
1344 ,p_log_level_rec => p_log_level_rec);
1345 fa_debug_pkg.add(l_calling_fn, 'transaction type code', p_trans_rec.transaction_type_code
1346 ,p_log_level_rec => p_log_level_rec);
1347 end if;
1348
1349
1353 x_id_flex_num => l_account_flex,
1350 l_status := fnd_flex_apis.get_segment_info(
1351 x_application_id => 101,
1352 x_id_flex_code => 'GL#',
1354 x_seg_num => l_bal_segnum,
1355 x_appcol_name => l_column_name,
1356 x_seg_name => l_seg_name,
1357 x_prompt => l_prompt,
1358 x_value_set_name => l_value_set_name );
1359
1360 if not l_status then
1361 raise interco_err;
1362 end if;
1363
1364 -- for group reclasses, the incoming dist table is null
1365 -- so we need to load the member asset's distribution info here...
1366 if (p_asset_dist_tbl.count = 0) then
1367
1368
1369 if (p_log_level_rec.statement_level) then
1370 fa_debug_pkg.add(l_calling_fn, 'loading', 'dist table'
1371 ,p_log_level_rec => p_log_level_rec);
1372 end if;
1373
1374 open c_asset_distributions;
1375
1376 loop
1377
1378 fetch c_asset_distributions
1379 into l_ccid;
1380
1381 if c_asset_distributions%NOTFOUND then
1382 exit;
1383 end if;
1384
1385 l_asset_dist_tbl(l_asset_dist_tbl.count + 1).expense_ccid := l_ccid;
1386
1387 end loop;
1388
1389 close c_asset_distributions;
1390
1391 else
1392 if (p_log_level_rec.statement_level) then
1393 fa_debug_pkg.add(l_calling_fn, 'using', 'provided dist table'
1394 ,p_log_level_rec => p_log_level_rec);
1395 end if;
1396
1397 l_asset_dist_tbl := p_asset_dist_tbl;
1398 end if;
1399
1400
1401
1402 -- load the balancing segments for the driving asset
1403 -- using the distirbution table parameter and flex api
1404 for l_dist_tbl_count in 1..l_asset_dist_tbl.count loop
1405
1406 if l_asset_dist_tbl(l_dist_tbl_count).expense_ccid is null then
1407 select code_combination_id
1408 into l_ccid
1409 from fa_distribution_history
1410 where distribution_id = l_asset_dist_tbl(l_dist_tbl_count).distribution_id;
1411 else
1412 l_ccid := l_asset_dist_tbl(l_dist_tbl_count).expense_ccid;
1413 end if;
1414
1415 if (l_dist_tbl_count = 1) then
1416 l_ccid_string := l_ccid_string || to_char(l_ccid);
1417 else
1418 l_ccid_string := l_ccid_string || ',' || to_char(l_ccid);
1419 end if;
1420
1421 end loop;
1422
1423 l_statement :=
1424 'select distinct glcc.' || l_column_name ||
1425 ' from gl_code_combinations glcc ' ||
1426 ' where code_combination_id in (' || l_ccid_string || ')';
1427
1428 if (p_log_level_rec.statement_level) then
1429 fa_debug_pkg.add(l_calling_fn, 'executing', 'first dynamic sql'
1430 ,p_log_level_rec => p_log_level_rec);
1431 end if;
1432
1433
1434 -- execute the statment
1435 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1436 DBMS_SQL.PARSE(l_cursor_id, l_statement, DBMS_SQL.NATIVE);
1437
1438 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_balancing_seg, 30);
1439
1440 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
1441
1442 loop
1443
1444 l_bal_count1 := l_bal_tbl1.count;
1445
1446 if DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
1447 exit;
1448 end if;
1449
1450 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_balancing_seg);
1451
1452 l_bal_tbl1(l_bal_count1 + 1) := l_balancing_seg;
1453
1454 end loop;
1455
1456 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1457
1458
1459
1460 -- now for group, check all member distributions
1461 -- and for members, check the groups distributions
1462 --
1463 -- we have two options... use dynamic sql which
1464 -- would result in much smaller tables to compare
1465 -- or we could use fnd apis on each distinct ccid
1466 -- resulting in additional operations to get only
1467 -- the distinct ccids and there would be more values
1468 -- for which youd have to call the api and then compare
1469
1470 -- for transfers, we check all associated books
1471 -- for the flag... for groups, this is only called
1472 -- from transfers/unit adjustments, not additions
1473
1474
1475 if (p_log_level_rec.statement_level) then
1476 fa_debug_pkg.add(l_calling_fn, 'setting', 'second dynamic sql'
1477 ,p_log_level_rec => p_log_level_rec);
1478 end if;
1479
1480
1481 if (p_asset_type_rec.asset_type = 'GROUP') then
1482
1483 l_statement :=
1484 'select distinct glcc.' || l_column_name ||
1485 ' from gl_code_combinations glcc,
1486 fa_books bk,
1487 fa_book_controls bc,
1488 fa_distribution_history dh
1489 where bk.asset_id = dh.asset_id
1490 and bk.group_asset_id = :p_asset_id
1491 and bk.book_type_code = bc.book_type_code
1492 and bc.distribution_source_book = :p_book
1493 and dh.book_type_code = :p_book
1494 and bc.allow_interco_group_flag = ''N''
1495 and bc.date_ineffective is null
1496 and bk.date_ineffective is null
1497 and dh.date_ineffective is null
1501
1498 and dh.code_combination_id = glcc.code_combination_id' ;
1499
1500 l_asset_id := p_asset_hdr_rec.asset_id;
1502
1503 -- member additions or group reclasses into a destination group
1504 elsif (p_trans_rec.transaction_type_code = 'ADDITION' or
1505 p_trans_rec.transaction_type_code = 'CIP ADDITION' or
1506 p_trans_rec.transaction_type_code = 'ADJUSTMENT' or
1507 p_trans_rec.transaction_type_code = 'CIP ADJUSTMENT') then
1508
1509 -- only care about the book in question
1510
1511 l_statement :=
1512 'select distinct glcc.' || l_column_name ||
1513 ' from gl_code_combinations glcc,
1514 fa_book_controls bc,
1515 fa_distribution_history dh
1516 where dh.asset_id = :p_asset_Id
1517 and dh.date_ineffective is null
1518 and dh.code_combination_id = glcc.code_combination_id
1519 and bc.distribution_source_book = :p_book
1520 and bc.book_type_code = dh.book_type_code
1521 and bc.allow_interco_group_flag = ''N''';
1522
1523 l_asset_id := p_group_asset_id;
1524
1525 else -- member transfer / unit adj
1526
1527 -- need to look at all groups for all books to which its assigned
1528
1529 l_statement :=
1530 'select distinct glcc.' || l_column_name ||
1531 ' from gl_code_combinations glcc,
1532 fa_books bk,
1533 fa_distribution_history dh,
1534 fa_book_controls bc
1535 where dh.asset_id = bk.group_asset_id
1536 and dh.date_ineffective is null
1537 and dh.code_combination_id = glcc.code_combination_id
1538 and bk.asset_id = :p_asset_id
1539 and bk.book_type_code = bc.book_type_code
1540 and bc.distribution_source_book = :p_book
1541 and bc.allow_interco_group_flag = ''N''
1542 and dh.book_type_code = :p_book ';
1543
1544 l_asset_id := p_asset_hdr_rec.asset_id;
1545
1546 end if;
1547
1548 if (p_log_level_rec.statement_level) then
1549 fa_debug_pkg.add(l_calling_fn, 'executing', 'second dynamic sql'
1550 ,p_log_level_rec => p_log_level_rec);
1551 end if;
1552
1553
1554 -- execute the statment
1555 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1556 DBMS_SQL.PARSE(l_cursor_id, l_statement, DBMS_SQL.NATIVE);
1557
1558 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_asset_id', l_asset_id);
1559 DBMS_SQL.BIND_VARIABLE(l_cursor_id, ':p_book', p_asset_hdr_rec.book_type_code);
1560
1561 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_balancing_seg, 30);
1562
1563 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
1564
1565 loop
1566
1567 l_bal_count2 := l_bal_tbl2.count;
1568
1569 if DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
1570 exit;
1571 end if;
1572
1573 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_balancing_seg);
1574
1575 l_bal_tbl2(l_bal_count2 + 1) := l_balancing_seg;
1576
1577 end loop;
1578
1579 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1580
1581
1582 if (p_log_level_rec.statement_level) then
1583 fa_debug_pkg.add(l_calling_fn, 'looking', 'for mismatches'
1584 ,p_log_level_rec => p_log_level_rec);
1585 end if;
1586
1587
1588 -- look for any mismatches
1589 for l_bal_tbl1_count in 1..l_bal_tbl1.count loop
1590
1591 for l_bal_tbl2_count in 1..l_bal_tbl2.count loop
1592
1593 if (l_bal_tbl1(l_bal_tbl1_count) <> l_bal_tbl2(l_bal_tbl2_count)) then
1594 raise interco_err;
1595 end if;
1596
1597 end loop;
1598
1599 end loop;
1600
1601 return true;
1602
1603 exception
1604 when interco_err then
1605 fa_srvr_msg.add_message(name => 'FA_NO_GROUP_INTERCO',
1606 calling_fn => l_calling_fn
1607 ,p_log_level_rec => p_log_level_rec);
1608 return false;
1609
1610 when others then
1611 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1612 ,p_log_level_rec => p_log_level_rec);
1613 return false;
1614
1615
1616 end validate_grp_interco;
1617
1618 --------------------------------------------------------------------------------
1619
1620 function validate_inv_interco
1621 (p_src_asset_hdr_rec in fa_api_types.asset_hdr_rec_type,
1622 p_src_trans_rec in fa_api_types.trans_rec_type,
1623 p_dest_asset_hdr_rec in fa_api_types.asset_hdr_rec_type,
1624 p_dest_trans_rec in fa_api_types.trans_rec_type,
1625 p_calling_fn in varchar2,
1626 x_interco_impact out nocopy boolean,
1627 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN is
1628
1629 CURSOR c_asset_distributions (p_asset_id number) IS
1630 select code_combination_id
1631 from fa_distribution_history
1632 where asset_id = p_asset_id
1633 and date_ineffective is null;
1634
1635 TYPE l_bal_tbl_type is table of varchar2(30) index by binary_integer;
1636 TYPE l_ccid_tbl_type is table of number index by binary_integer;
1637
1641 l_bal_count1 number;
1638 l_ccid_tbl l_ccid_tbl_type;
1639 l_bal_tbl1 l_bal_tbl_type;
1640 l_bal_tbl2 l_bal_tbl_type;
1642 l_bal_count2 number;
1643 l_ccid_tbl_count number;
1644
1645 l_cursor_id number;
1646 l_statement varchar2(4000);
1647 l_dummy number;
1648 l_found boolean;
1649
1650 l_account_flex number;
1651 l_balancing_seg varchar2(250);
1652 l_bal_segnum number;
1653 l_column_name varchar2(30);
1654 l_seg_name VARCHAR2(30);
1655 l_prompt VARCHAR2(80);
1656 l_value_set_name VARCHAR2(60);
1657 l_ccid_string varchar2(4000) := '';
1658 l_status boolean;
1659
1660 l_ccid number;
1661 l_asset_id number;
1662
1663 l_calling_fn varchar2(35) := 'fa_interco_pvt.validate_inv_interco';
1664 interco_err exception;
1665
1666 begin
1667
1668 x_interco_impact := FALSE;
1669
1670 if (p_log_level_rec.statement_level) then
1671 fa_debug_pkg.add(l_calling_fn, 'inside', 'validate interco code'
1672 ,p_log_level_rec => p_log_level_rec);
1673 end if;
1674
1675 l_account_flex := fa_cache_pkg.fazcbc_record.ACCOUNTING_FLEX_STRUCTURE;
1676
1677 /*
1678 l_status := fnd_flex_apis.get_qualifier_segnum(appl_id => 101,
1679 key_flex_code => 'GL#',
1680 structure_number => l_account_flex,
1681 flex_qual_name => 'GL_BALANCING',
1682 segment_number => l_bal_segnum);
1683
1684 if not l_status then
1685 raise interco_err;
1686 end if;
1687 */
1688 /* Bug 5246620. Wrong segment_number retrieved */
1689 SELECT s.segment_num INTO l_bal_segnum
1690 FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
1691 fnd_segment_attribute_types sat
1692 WHERE s.application_id = 101
1693 AND s.id_flex_code = 'GL#'
1694 AND s.id_flex_num = l_account_flex
1695 AND s.enabled_flag = 'Y'
1696 AND s.application_column_name = sav.application_column_name
1697 AND sav.application_id = 101
1698 AND sav.id_flex_code = 'GL#'
1699 AND sav.id_flex_num = l_account_flex
1700 AND sav.attribute_value = 'Y'
1701 AND sav.segment_attribute_type = sat.segment_attribute_type
1702 AND sat.application_id = 101
1703 AND sat.id_flex_code = 'GL#'
1704 AND sat.unique_flag = 'Y'
1705 AND sat.segment_attribute_type = 'GL_BALANCING';
1706
1707
1708 if (p_log_level_rec.statement_level) then
1709 fa_debug_pkg.add(l_calling_fn, 'GL Balancing Segment Number', l_bal_segnum
1710 ,p_log_level_rec => p_log_level_rec);
1711 end if;
1712
1713
1714 l_status := fnd_flex_apis.get_segment_info(
1715 x_application_id => 101,
1716 x_id_flex_code => 'GL#',
1717 x_id_flex_num => l_account_flex,
1718 x_seg_num => l_bal_segnum,
1719 x_appcol_name => l_column_name,
1720 x_seg_name => l_seg_name,
1721 x_prompt => l_prompt,
1722 x_value_set_name => l_value_set_name );
1723
1724 if not l_status then
1725 raise interco_err;
1726 end if;
1727
1728
1729 -- load the balancing segments for the driving asset (src)
1730 -- using the distirbution table parameter and flex api
1731 for i in 1..2 loop
1732
1733 if (p_log_level_rec.statement_level) then
1734 fa_debug_pkg.add(l_calling_fn, 'loading', 'dist table'
1735 ,p_log_level_rec => p_log_level_rec);
1736 end if;
1737
1738 if i = 1 then
1739 l_asset_id := p_src_asset_hdr_rec.asset_id;
1740 else
1741 l_asset_id := p_dest_asset_hdr_rec.asset_id;
1742 end if;
1743
1744 open c_asset_distributions (p_asset_id => l_asset_id);
1745
1746 loop
1747
1748 fetch c_asset_distributions
1749 into l_ccid;
1750
1751 if c_asset_distributions%NOTFOUND then
1752 exit;
1753 end if;
1754
1755 l_ccid_tbl(l_ccid_tbl.count + 1) := l_ccid;
1756
1757 end loop;
1758
1759 close c_asset_distributions;
1760
1761 if (p_log_level_rec.statement_level) then
1762 fa_debug_pkg.add(l_calling_fn, 'building', 'ccid string'
1763 ,p_log_level_rec => p_log_level_rec);
1764 end if;
1765
1766 for l_ccid_tbl_count in 1..l_ccid_tbl.count loop
1767
1768 l_ccid := l_ccid_tbl(l_ccid_tbl_count);
1769
1770 if (l_ccid_tbl_count = 1) then
1771 l_ccid_string := l_ccid_string || to_char(l_ccid);
1772 else
1773 l_ccid_string := l_ccid_string || ',' || to_char(l_ccid);
1774 end if;
1775
1776 end loop;
1777
1778 l_statement :=
1779 'select distinct glcc.' || l_column_name ||
1780 ' from gl_code_combinations glcc ' ||
1781 ' where code_combination_id in (' || l_ccid_string || ')';
1782
1783 if (p_log_level_rec.statement_level) then
1784 fa_debug_pkg.add(l_calling_fn, 'executing', 'first dynamic sql'
1785 ,p_log_level_rec => p_log_level_rec);
1786 end if;
1787
1788 -- execute the statment
1789 l_cursor_id := DBMS_SQL.OPEN_CURSOR;
1790 DBMS_SQL.PARSE(l_cursor_id, l_statement, DBMS_SQL.NATIVE);
1791
1792 DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_balancing_seg, 30);
1793
1794 l_dummy := DBMS_SQL.EXECUTE(l_cursor_id);
1795
1796 loop
1797
1798 l_bal_count1 := l_bal_tbl1.count;
1799
1800 if DBMS_SQL.FETCH_ROWS(l_cursor_id) = 0 then
1801 exit;
1802 end if;
1803
1804 DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_balancing_seg);
1805
1806 l_bal_tbl1(l_bal_count1 + 1) := l_balancing_seg;
1807
1808 end loop;
1809
1810 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
1811
1812 -- copy this table to the second array and delete the original
1813 if (i = 1) then
1814 l_bal_tbl2 := l_bal_tbl1;
1815 l_bal_tbl1.delete;
1816 end if;
1817
1818 end loop; -- this ends the fixed two time loop (src and dest)
1819
1820
1821 if (p_log_level_rec.statement_level) then
1822 fa_debug_pkg.add(l_calling_fn, 'looking', 'for mismatches'
1823 ,p_log_level_rec => p_log_level_rec);
1824 end if;
1825
1826 -- look for any mismatches
1827 for l_bal_tbl1_count in 1..l_bal_tbl1.count loop
1828
1829 for l_bal_tbl2_count in 1..l_bal_tbl2.count loop
1830
1831 if (l_bal_tbl1(l_bal_tbl1_count) <> l_bal_tbl2(l_bal_tbl2_count)) then
1832 x_interco_impact := TRUE;
1833 end if;
1834
1835 end loop;
1836
1837 end loop;
1838
1839 return true;
1840
1841 exception
1842 when interco_err then
1843 fa_srvr_msg.add_message(name => 'FA_NO_GROUP_INTERCO',
1844 calling_fn => l_calling_fn
1845 ,p_log_level_rec => p_log_level_rec);
1846 return false;
1847
1848 when others then
1849 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn
1850 ,p_log_level_rec => p_log_level_rec);
1851 return false;
1852
1853
1854 end validate_inv_interco;
1855
1856
1857 END FA_INTERCO_PVT;