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