[Home] [Help]
PACKAGE BODY: APPS.FA_DISTRIBUTION_PVT
Source
1 PACKAGE BODY FA_DISTRIBUTION_PVT AS
2 /* $Header: FAVDISTB.pls 120.34.12020000.3 2013/02/04 11:18:40 adaluru ship $ */
3
4 g_release number := fa_cache_pkg.fazarel_release;
5
6 FUNCTION do_distribution(
7 px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
8 px_asset_hdr_rec IN OUT NOCOPY FA_API_TYPES.asset_hdr_rec_type,
9 px_asset_cat_rec_new IN OUT NOCOPY FA_API_TYPES.asset_cat_rec_type,
10 px_asset_dist_tbl IN OUT NOCOPY FA_API_TYPES.asset_dist_tbl_type,
11 p_validation_level IN NUMBER :=
12 FND_API.G_VALID_LEVEL_FULL
13 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) RETURN BOOLEAN IS
14
15 l_trans_rec FA_API_TYPES.trans_rec_type;
16 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
17
18 l_asset_type_rec_old FA_API_TYPES.asset_type_rec_type;
19 l_asset_cat_rec_old FA_API_TYPES.asset_cat_rec_type;
20 l_asset_desc_rec_new FA_API_TYPES.asset_desc_rec_type;
21 l_period_rec FA_API_TYPES.period_rec_type;
22 l_src_trans_rec FA_API_TYPES.trans_rec_type;
23 l_src_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
24 l_dest_trans_rec FA_API_TYPES.trans_rec_type;
25 l_dest_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
26
27 l_txn_date_entered date;
28 l_cal_period_close_date date;
29 l_current_pc number;
30 l_period_addition varchar2(1);
31 l_book varchar2(30);
32 l_old_units number := 0;
33 l_total_txn_units number := 0;
34 l_primary_sob_id number;
35 l_mrc_sob_type_code varchar2(1);
36
37 l_index number;
38 l_init_null_trx_date boolean;
39 l_backdated_xfr boolean;
40
41 error_found exception;
42
43 --Secondary Changes
44 l_secondary_trans_rec FA_API_TYPES.trans_rec_type;
45 l_secondary_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
46 l_secondary_sob_id number;
47
48 -- SLA Uptake
49 -- rewriting this to pick up all tax books
50 -- as each needs a seperate transaction
51
52 CURSOR c_books (l_book_type_code varchar2,
53 l_asset_id number) IS
54 SELECT bc.book_type_code
55 FROM fa_books bk,
56 fa_book_controls bc
57 WHERE bc.distribution_source_book = l_book_type_code
58 AND bk.book_type_code = bc.book_type_code
59 AND bk.asset_id = l_asset_id
60 AND bk.transaction_header_id_out is null
61 AND bc.date_ineffective is null
62 ORDER BY bc.book_class,
63 bc.book_type_code;
64
65 CURSOR n_sob_id (p_psob_id IN NUMBER,
66 p_book_type_code IN VARCHAR2) is
67 SELECT p_psob_id AS sob_id,
68 1 AS index_id
69 FROM dual
70 UNION
71 SELECT set_of_books_id AS sob_id,
72 2 AS index_id
73 FROM fa_mc_book_controls
74 WHERE book_type_code = p_book_type_code
75 AND primary_set_of_books_id = p_psob_id
76 AND enabled_flag = 'Y'
77 ORDER BY 2;
78
79 --Added for 8759611
80 CURSOR cur_dists_eff (c_asset_id NUMBER,c_transaction_id NUMBER,c_book VARCHAR2) IS
81 SELECT NULL distribution_id,units_assigned,units_assigned transaction_units,
82 code_combination_id expense_ccid,location_id location_ccid,assigned_to
83 FROM fa_distribution_history
84 WHERE asset_id = c_asset_id
85 AND transaction_header_id_in = c_transaction_id
86 AND book_type_code = c_book
87 UNION ALL
88 SELECT distribution_id,units_assigned,transaction_units,
89 code_combination_id expense_ccid,location_id location_ccid,assigned_to
90 FROM fa_distribution_history
91 WHERE asset_id = c_asset_id
92 AND transaction_header_id_out = c_transaction_id
93 AND book_type_code = c_book
94 ORDER BY distribution_id;
95
96 l_dum_dist_table fa_api_types.asset_dist_tbl_type;
97 l_dist_cntr NUMBER;
98 --End of addition for 8759611
99 BEGIN
100
101 l_trans_rec := px_trans_rec;
102
103 -- populate category_id for the asset
104 if not FA_UTIL_PVT.get_asset_cat_rec(px_asset_hdr_rec,
105 l_asset_cat_rec_old
106 ,p_log_level_rec => p_log_level_rec) then
107 raise error_found;
108 end if;
109
110 -- populate new_category_id with old category if non-reclass
111 if px_trans_rec.transaction_type_code in ('UNIT ADJUSTMENT','TRANSFER OUT',
112 'TRANSFER') then
113 px_asset_cat_rec_new.category_id := l_asset_cat_rec_old.category_id;
114 elsif (px_trans_rec.transaction_type_code = 'RECLASS') then
115 if px_asset_cat_rec_new.category_id is null then
116 fa_srvr_msg.add_message(
117 calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution',
118 name => 'FA_SHARED_UNDEFINE_CATEGORY'
119 ,p_log_level_rec => p_log_level_rec);
120 raise error_found;
121 end if;
122 end if;
123
124 -- populate old asset type for the asset
125 if not FA_UTIL_PVT.get_asset_type_rec(px_asset_hdr_rec,
126 l_asset_type_rec_old
127 ,p_log_level_rec => p_log_level_rec) then
128 raise error_found;
129 end if;
130
131
132 -- get current unit
133 select sum(units_assigned - nvl(transaction_units, 0))
134 into l_old_units
135 from fa_distribution_history
136 where asset_id = px_asset_hdr_rec.asset_id
137 and book_type_code = px_asset_hdr_rec.book_type_code
138 and date_ineffective IS NULL;
139
140 -- validate input data
141 if not do_validation(px_trans_rec,
142 px_asset_hdr_rec,
143 px_asset_cat_rec_new,
144 px_asset_dist_tbl,
145 l_old_units,
146 l_total_txn_units,
147 p_validation_level
148 ,p_log_level_rec => p_log_level_rec) then
149 raise error_found;
150 end if;
151
152 -- populate new units
153 l_asset_desc_rec_new.current_units := l_old_units;
154 if (px_trans_rec.transaction_type_code in ('TRANSFER OUT','UNIT ADJUSTMENT')) then
155 l_asset_desc_rec_new.current_units := l_old_units + l_total_txn_units;
156 end if;
157
158 l_index := 0;
159 l_asset_hdr_rec := px_asset_hdr_rec;
160
161 -- loop through corp and tax books
162 open c_books(px_asset_hdr_rec.book_type_code,
163 px_asset_hdr_rec.asset_id);
164 loop
165
166 l_index := l_index + 1;
167
168 fetch c_books into l_book;
169 exit when c_books%NOTFOUND;
170
171 l_trans_rec := px_trans_rec;
172 l_asset_hdr_rec.book_type_code := l_book;
173
174 if not FA_UTIL_PVT.get_period_rec
175 (p_book => l_book,
176 x_period_rec => l_period_rec
177 ,p_log_level_rec => p_log_level_rec) then
178 raise error_found;
179 end if;
180
181 if not FA_CACHE_PKG.fazcbc(X_book => l_book
182 ,p_log_level_rec => p_log_level_rec) then
183 fa_srvr_msg.add_message(calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution'
184 ,p_log_level_rec => p_log_level_rec);
185 raise error_found;
186 end if;
187
188 -- if (l_index = 1) then -- for bug fix 4969369
189 -- populate transaction date if null
190 if (px_trans_rec.transaction_date_entered is null) then
191 l_init_null_trx_date := TRUE;
192
193 px_trans_rec.transaction_date_entered :=
194 greatest(l_period_rec.calendar_period_open_date,
195 least(sysdate,l_period_rec.calendar_period_close_date));
196 px_trans_rec.transaction_date_entered :=
197 to_date(to_char(px_trans_rec.transaction_date_entered,'DD/MM/YYYY'),'DD/MM/YYYY');
198 end if;
199 /*Bug - 13401993 ,For TAX book need to have event date in current open period of TAX book */
200 if (l_index > 1) then
201 px_trans_rec.transaction_date_entered :=
202 least(px_trans_rec.transaction_date_entered,
203 least(sysdate,l_period_rec.calendar_period_close_date));
204 px_trans_rec.transaction_date_entered :=
205 to_date(to_char(px_trans_rec.transaction_date_entered,'DD/MM/YYYY'),'DD/MM/YYYY');
206 end if;
207 -- else -- for bug fix 4969369
208 -- check if date is backdated
209 if (px_trans_rec.transaction_date_entered < l_period_rec.calendar_period_open_date) then
210 l_backdated_xfr := true;
211 else
212 l_backdated_xfr := false;
213 end if;
214 -- end if; -- for bug fix 4969369
215
216 l_trans_rec := px_trans_rec;
217
218 -- end if; -- for bug fix 4969369
219
220 -- Bug 9916378
221 if (l_backdated_xfr) then
222 if not FA_ASSET_VAL_PVT.validate_prev_period_amort
223 (p_asset_hdr_rec => l_asset_hdr_rec,
224 p_trans_rec => l_trans_rec,
225 p_period_rec => l_period_rec,
226 p_asset_retire_rec => null,
227 p_log_level_rec => p_log_level_rec) then
228 raise error_found;
229 end if;
230 end if;
231
232 -- populate period_of_addition
233 -- SLA: always populate for all trx including TRANSFER OUT
234 if not fa_asset_val_pvt.validate_period_of_addition
235 (l_asset_hdr_rec.asset_id,
236 l_asset_hdr_rec.book_type_code,
237 'ABSOLUTE',
238 l_asset_hdr_rec.period_of_addition
239 ,p_log_level_rec => p_log_level_rec) then
240 raise error_found;
241 end if;
242
243 /*Bug#8478435 - set_of_books_id issue - POST MRC change */
244 l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
245 -- SLA UPTAKE
246 -- assign an event for the transaction
247 -- at this point key info asset/book/trx info is known from initialize
248 -- call and the above code (i.e. trx_type, etc)
249 --
250 -- Note changing this so even though we have a single TH,
251 -- we are creating an event/entity for each book (VM)
252
253 if not fa_xla_events_pvt.create_transaction_event
254 (p_asset_hdr_rec => l_asset_hdr_rec,
255 p_asset_type_rec=> l_asset_type_rec_old,
256 px_trans_rec => l_trans_rec,
257 p_event_status => NULL,
258 p_calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution'
259 ,p_log_level_rec => p_log_level_rec) then
260 raise error_found;
261 end if;
262 /*=================================================================
263 Secondary Changes Start
264 If primary and secondary sob_id is different then we need to
265 create event for secondary ledger*/
266 l_secondary_sob_id := FA_XLA_EVENTS_PVT.get_secondary_sob_id(l_book);
267
268 if(l_secondary_sob_id is not null)then
269
270 l_secondary_trans_rec := l_trans_rec;
271 l_secondary_asset_hdr_rec := l_asset_hdr_rec;
272 l_secondary_asset_hdr_rec.set_of_books_id := l_secondary_sob_id;
273 if not fa_xla_events_pvt.create_transaction_event
274 (p_asset_hdr_rec => l_secondary_asset_hdr_rec,
275 p_asset_type_rec=> l_asset_type_rec_old,
276 px_trans_rec => l_secondary_trans_rec,
277 p_event_status => NULL,
278 p_calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution'
279 ,p_log_level_rec => p_log_level_rec) then
280 raise error_found;
281 end if;
282 end if;
283 /*Secondary Changes End
284 ==================================================================*/
285 --Bug6391045
286 --Assigned the generated event_id into the px_trans_rec.event_id
287 px_trans_rec.event_id := l_trans_rec.event_id;
288
289 if (l_index = 1) then
290 -- insert fa_transaction_headers
291 if not insert_txn_headers(l_trans_rec,
292 l_asset_hdr_rec
293 ,p_log_level_rec => p_log_level_rec) then
294 raise error_found;
295 end if;
296
297 -- update fa_asset_history
298 if px_trans_rec.transaction_type_code <> 'TRANSFER' then
299 if not update_asset_history(l_trans_rec,
300 l_asset_hdr_rec,
301 px_asset_cat_rec_new,
302 l_asset_desc_rec_new
303 ,p_log_level_rec => p_log_level_rec) then
304 raise error_found;
305 end if;
306 end if;
307
308
309 -- update fa_additions
310 if not update_additions(l_trans_rec,
311 l_asset_hdr_rec,
312 px_asset_cat_rec_new,
313 l_asset_desc_rec_new
314 ,p_log_level_rec => p_log_level_rec) then
315 raise error_found;
316 end if;
317
318 end if;
319
320 -- update fa_books for prior period transfer
321 if (px_trans_rec.transaction_type_code = 'TRANSFER' and
322 G_release = 11) then
323 if not update_books(l_trans_rec,
324 l_asset_hdr_rec,
325 l_period_rec
326 ,p_log_level_rec => p_log_level_rec) then
327 raise error_found;
328 end if;
329 end if;
330
331
332 if (l_index = 1) then
333 -- update fa_distribution_history
334 if not update_dist_history(l_trans_rec,
335 l_asset_hdr_rec,
336 px_asset_dist_tbl
337 ,p_log_level_rec => p_log_level_rec) then
338 raise error_found;
339 end if;
340
341 -- check to make sure units are in sync after the updates
342 if not units_in_sync(px_asset_hdr_rec, p_log_level_rec) then
343 raise error_found;
344 end if;
345 end if;
346
347 -- R12 conditional handling
348 if (px_asset_hdr_rec.period_of_addition = 'Y' and
349 G_release = 11) then
350 l_primary_sob_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
351 -- loop thru primary and its reporting books
352 for c_rec in n_sob_id(l_primary_sob_id, px_asset_hdr_rec.book_type_code) loop
353 if c_rec.index_id = 1 then
354 l_mrc_sob_type_code := 'P';
355 else
356 l_mrc_sob_type_code := 'R';
357 end if;
358
359 -- Bug:5915051
360 if NOT fa_cache_pkg.fazcbcs(X_book => l_asset_hdr_rec.book_type_code,
361 X_set_of_books_id => c_rec.sob_id) then
362 return FALSE;
363 end if;
364
365 if not FA_INS_DETAIL_PKG.FAXINDD(
366 X_book_type_code => px_asset_hdr_rec.book_type_code,
367 X_asset_id => px_asset_hdr_rec.asset_id,
368 X_period_counter => NULL,
369 X_cost => NULL,
370 X_deprn_reserve => NULL,
371 X_reval_reserve => NULL,
372 X_ytd => NULL,
373 X_ytd_reval_dep_exp => NULL,
374 X_init_message_flag => 'NO',
375 X_mrc_sob_type_code => l_mrc_sob_type_code,
376 X_set_of_books_id => c_rec.sob_id,
377 p_log_level_rec => p_log_level_rec) then
378 fa_srvr_msg.add_message(
379 calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution');
380 return FALSE;
381 end if;
382 end loop;
383 end if;
384
385 l_current_pc := FA_CACHE_PKG.fazcbc_record.last_period_counter + 1;
386
387 if not FA_TRANSFER_XIT_PKG.fautfr(
388 X_thid => l_trans_rec.transaction_header_id,
389 X_asset_id => l_asset_hdr_rec.asset_id,
390 X_book => l_asset_hdr_rec.book_type_code,
391 X_txn_type_code => l_trans_rec.transaction_type_code,
392 X_period_ctr => l_current_pc,
393 X_curr_units => l_asset_desc_rec_new.current_units,
394 X_today => l_trans_rec.who_info.last_update_date,
395 X_old_cat_id => l_asset_cat_rec_old.category_id,
396 X_new_cat_id => px_asset_cat_rec_new.category_id,
397 X_asset_type => l_asset_type_rec_old.asset_type,
398 X_last_update_date => l_trans_rec.who_info.last_update_date,
399 X_last_updated_by => l_trans_rec.who_info.last_updated_by,
400 X_last_update_login =>l_trans_rec.who_info.last_update_login,
401 X_init_message_flag => 'NO'
402 ,p_log_level_rec => p_log_level_rec) then
403 raise error_found;
404 end if;
405
406 -- Bug 4739563 call the catchup logic only when the transfer ia a
407 -- backdated one
408 -- added the check on l_backdated_xfr
409
410 if (G_release <> 11 and
411 l_backdated_xfr and
412 l_trans_rec.transaction_type_code = 'TRANSFER' and
413 (l_asset_type_rec_old.asset_type = 'GROUP' or
414 l_asset_type_rec_old.asset_type = 'CAPITALIZED'))then
415 --Change for 8759611
416 l_dist_cntr := 1;
417 FOR rec_dists_eff IN cur_dists_eff(l_asset_hdr_rec.asset_id,
418 l_trans_rec.transaction_header_id,
419 l_asset_hdr_rec.book_type_code)
420 LOOP
421 l_dum_dist_table(l_dist_cntr).distribution_id := rec_dists_eff.distribution_id;
422 l_dum_dist_table(l_dist_cntr).units_assigned := rec_dists_eff.units_assigned;
423 l_dum_dist_table(l_dist_cntr).transaction_units := rec_dists_eff.transaction_units;
424 l_dum_dist_table(l_dist_cntr).assigned_to := rec_dists_eff.assigned_to;
425 l_dum_dist_table(l_dist_cntr).expense_ccid := rec_dists_eff.expense_ccid;
426 l_dum_dist_table(l_dist_cntr).location_ccid := rec_dists_eff.location_ccid;
427
428 l_dist_cntr := l_dist_cntr + 1;
429 END LOOP;
430 --End of change for 8759611
431 if not FA_TRANSFER_PVT.fadppt
432 (p_trans_rec => l_trans_rec,
433 p_asset_hdr_rec => l_asset_hdr_rec,
434 p_asset_desc_rec => l_asset_desc_rec_new,
435 p_asset_cat_rec => px_asset_cat_rec_new,
436 -- p_asset_dist_tbl => px_asset_dist_tbl changed for 8759611
437 p_asset_dist_tbl => l_dum_dist_table -- Added for 8759611
438 ,p_log_level_rec => p_log_level_rec) then
439 raise error_found;
440 end if;
441 end if;
442
443 -- fix for bug 2725999 - call INTERCO private API to create
444 -- INTERCO AP/AR rows in fa_adjustments table
445
446 if (px_asset_hdr_rec.period_of_addition <> 'Y' and
447 G_release = 11) then
448
449 l_src_trans_rec := px_trans_rec;
450 l_src_asset_hdr_rec := px_asset_hdr_rec;
451 l_src_trans_rec.transaction_type_code := 'TRANSFER';
452 l_dest_trans_rec := NULL;
453 l_dest_asset_hdr_rec := NULL;
454
455 if (px_trans_rec.transaction_type_code in ('TRANSFER', 'UNIT ADJUSTMENT')) then
456
457 if not FA_INTERCO_PVT.do_all_books(
458 p_src_trans_rec => l_src_trans_rec,
459 p_src_asset_hdr_rec => l_src_asset_hdr_rec,
460 p_dest_trans_rec => l_dest_trans_rec,
461 p_dest_asset_hdr_rec => l_dest_asset_hdr_rec,
462 p_calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution', p_log_level_rec => p_log_level_rec) then
463 fa_srvr_msg.add_message(
464 calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution', p_log_level_rec => p_log_level_rec);
465 return FALSE;
466 end if;
467 end if;
468 end if;
469 end loop;
470
471 close c_books;
472
473 -- reset cache to incoming corp book instead of last tax
474 if not FA_CACHE_PKG.fazcbc(X_book => px_asset_hdr_rec.book_type_code
475 ,p_log_level_rec => p_log_level_rec) then
476 fa_srvr_msg.add_message(calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution'
477 ,p_log_level_rec => p_log_level_rec);
478 raise error_found;
479 end if;
480
481 return TRUE;
482
483 EXCEPTION
484 when others then
485 fa_srvr_msg.add_sql_error(
486 calling_fn => 'FA_DISTRIBUTION_PVT.do_distribution', p_log_level_rec => p_log_level_rec);
487 return FALSE;
488
489 END do_distribution;
490
491
492 FUNCTION do_validation(p_trans_rec IN FA_API_TYPES.trans_rec_type,
493 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
494 p_asset_cat_rec_new IN FA_API_TYPES.asset_cat_rec_type,
495 px_asset_dist_tbl IN OUT NOCOPY FA_API_TYPES.asset_dist_tbl_type,
496 p_old_units IN NUMBER,
497 x_total_txn_units OUT NOCOPY NUMBER,
498 p_validation_level IN NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
499 return boolean is
500
501 l_tot_txn number := 0; --total txn units
502 l_prev_txn number := 0;
503 l_count number;
504 l_curr_index number;
505 l_msg_name varchar2(100);
506 l_asset_id number;
507 d_distribution_id number;
508 d_transaction_units number;
509 d_assigned_to number;
510 d_expense_ccid number;
511 d_location_ccid number;
512 l_minus_rec number := 0;
513 -- l_match_units is used to make sure source and destination units
514 -- match, for transfer and reclass txn
515 l_match_unit number := 0;
516
517 l_asset_type_rec_old fa_api_types.asset_type_rec_type;
518 l_group_asset_id number;
519
520 cursor DH_C1 is
521 select distribution_id,
522 units_assigned
523 from fa_distribution_history
524 where asset_id = l_asset_id
525 and nvl(assigned_to,-9999) = nvl(d_assigned_to,-9999)
526 and code_combination_id = d_expense_ccid
527 and location_id = d_location_ccid
528 and date_ineffective is null;
529
530 cursor DH_C2 is
531 select units_assigned
532 from fa_distribution_history
533 where asset_id = l_asset_id
534 and distribution_id = d_distribution_id;
535 -- and nvl(assigned_to,-9999) = nvl(d_assigned_to,-9999)
536 -- and code_combination_id = nvl(d_expense_ccid,code_combination_id)
537 -- and location_id = nvl(d_location_ccid,location_id);
538
539 DIST_DATA_ERROR EXCEPTION;
540 begin
541
542 -- validate if units are in sync before proceeding
543
544 if not units_in_sync(p_asset_hdr_rec, p_log_level_rec) then
545 return FALSE;
546 end if;
547
548 -- check period_of_addition is correctly set
549 if (p_asset_hdr_rec.period_of_addition not in ('Y','N')) then
550 fa_srvr_msg.add_message(
551 calling_fn => 'FA_DISTRIBUTION_PVT.do_validation',
552 name => 'FA_API_SHARED_INVALID_YN',
553 token1 => 'VALUE',
554 value1 => p_asset_hdr_rec.period_of_addition,
555 token2 => 'XMLTAG',
556 value2 => 'PERIOD_OF_ADDITION', p_log_level_rec => p_log_level_rec);
557 return FALSE;
558 end if;
559
560
561 -- validate all the dist_tbl records
562 l_asset_id := p_asset_hdr_rec.asset_id;
563 FOR i in px_asset_dist_tbl.first..px_asset_dist_tbl.last LOOP
564
565 l_curr_index := i;
566 d_distribution_id := px_asset_dist_tbl(i).distribution_id;
567 d_transaction_units := px_asset_dist_tbl(i).transaction_units;
568 d_assigned_to := px_asset_dist_tbl(i).assigned_to;
569 d_expense_ccid := px_asset_dist_tbl(i).expense_ccid;
570 d_location_ccid := px_asset_dist_tbl(i).location_ccid;
571
572 -- make sure distribution_id or 3-tuple columns is provided
573 if (d_distribution_id is null and
574 (d_expense_ccid is null or d_location_ccid is null)) then
575 l_msg_name := 'FA_WHATIF_ASSET_DIST_INFO';
576 raise DIST_DATA_ERROR;
577 end if;
578 -- BUG# 6936546
579
580 -- if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
581
582 -- make sure transaction_units are populated
583 if (d_transaction_units is null or d_transaction_units = 0) then
584 l_msg_name := 'FA_INVALID_TXN_UNITS';
585 raise DIST_DATA_ERROR;
586 end if;
587
588 -- end if;
589
590 if (p_trans_rec.transaction_type_code in ('TRANSFER','RECLASS','TRANSFER OUT')) then
591 if (d_distribution_id is null) then
592 if (d_transaction_units < 0) then
593 open DH_C1;
594 fetch DH_C1 into px_asset_dist_tbl(i).distribution_id,
595 px_asset_dist_tbl(i).units_assigned;
596 if (DH_C1%NOTFOUND) then
597 l_msg_name := 'FA_WHATIF_ASSET_DIST_INFO';
598 raise DIST_DATA_ERROR;
599 end if;
600 close DH_C1;
601 l_tot_txn := l_tot_txn + d_transaction_units;
602 l_match_unit := l_match_unit + d_transaction_units;
603 l_minus_rec := l_minus_rec + 1;
604 else
605 if (p_trans_rec.transaction_type_code = 'TRANSFER OUT') then
606 l_msg_name := 'FA_INVALID_TXN_UNITS';
607 raise DIST_DATA_ERROR;
608 elsif (p_trans_rec.transaction_type_code = 'TRANSFER') then
609 -- BUG# 6936546
610 if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
611 open DH_C1;
612 fetch DH_C1 into px_asset_dist_tbl(i).distribution_id,
613 px_asset_dist_tbl(i).units_assigned;
614 close DH_C1;
615 end if;
616 end if;
617
618 if not valid_dist_data(p_trans_rec,
619 p_asset_hdr_rec,
620 px_asset_dist_tbl,
621 l_curr_index,
622 p_validation_level,
623 p_log_level_rec) then
624 raise DIST_DATA_ERROR;
625 end if;
626 l_match_unit := l_match_unit + d_transaction_units;
627 end if;
628 else -- d_distribution_id not null
629 if (d_transaction_units <0) then
630 open DH_C2;
631 fetch DH_C2 into px_asset_dist_tbl(i).units_assigned;
632 if DH_C2%NOTFOUND then
633 l_msg_name := 'FA_WHATIF_ASSET_DIST_INFO';
634 raise DIST_DATA_ERROR;
635 end if;
636 close DH_C2;
637 l_tot_txn := l_tot_txn + d_transaction_units;
638 l_match_unit := l_match_unit + d_transaction_units;
639 l_minus_rec := l_minus_rec + 1;
640 else
641 if (p_trans_rec.transaction_type_code = 'TRANSFER') then
642 open DH_C2;
643 fetch DH_C2 into px_asset_dist_tbl(i).units_assigned;
644 if DH_C2%NOTFOUND then
645 l_msg_name := 'FA_WHATIF_ASSET_DIST_INFO';
646 raise DIST_DATA_ERROR;
647 end if;
648 close DH_C2;
649 l_match_unit := l_match_unit + d_transaction_units;
650 else
651 l_msg_name := 'FA_INVALID_TXN_UNITS';
652 raise DIST_DATA_ERROR;
653 end if;
654 end if;
655 end if;
656
657 elsif (p_trans_rec.transaction_type_code = 'UNIT ADJUSTMENT') then
658 if (d_distribution_id is null) then
659 open DH_C1;
660 fetch DH_C1 into px_asset_dist_tbl(i).distribution_id,
661 px_asset_dist_tbl(i).units_assigned;
662 if DH_C1%NOTFOUND then
663 if (d_transaction_units < 0) then
664 l_msg_name := 'FA_WHATIF_ASSET_DIST_INFO';
665 raise DIST_DATA_ERROR;
666 end if;
667 end if;
668 close DH_C1;
669 else
670 open DH_C2;
671 fetch DH_C2 into px_asset_dist_tbl(i).units_assigned;
672 if DH_C2%NOTFOUND then
673 l_msg_name := 'FA_WHATIF_ASSET_DIST_INFO';
674 raise DIST_DATA_ERROR;
675 end if;
676 close DH_C2;
677 end if;
678 l_tot_txn := l_tot_txn + d_transaction_units;
679 if ABS(l_tot_txn) < ABS(l_prev_txn) then
680 l_msg_name := 'FA_INVALID_TXN_UNITS';
681 raise DIST_DATA_ERROR;
682 end if;
683 l_prev_txn := l_tot_txn;
684 end if;
685
686 -- check to make sure txn unit don't exceed units_assigned
687 if (d_transaction_units < 0) then
688 if ABS(d_transaction_units) > px_asset_dist_tbl(i).units_assigned then
689 l_msg_name := 'FA_TFR_UNITS_TFRED_EXCEEDED';
690 raise DIST_DATA_ERROR;
691 end if;
692 end if;
693
694 -- txn_units and units_assigned must be same for reclass
695 if (p_trans_rec.transaction_type_code = 'RECLASS') then
696 if (d_transaction_units < 0 and
697 ABS(d_transaction_units) <> px_asset_dist_tbl(i).units_assigned) then
698 l_msg_name := 'FA_INVALID_TXN_UNITS';
699 raise DIST_DATA_ERROR;
700 end if;
701 end if;
702
703 END LOOP;
704
705 -- BUG# 6936546
706 -- if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
707
708 -- check if txn_units are valid for transfer and reclass
709 -- sum of txn units in dist_tbl should be zeroed out
710 -- for transfer and reclass case
711 if (p_trans_rec.transaction_type_code in ('TRANSFER','RECLASS')) then
712 if l_match_unit <> 0 then
713 l_msg_name := 'FA_INVALID_TXN_UNITS';
714 raise DIST_DATA_ERROR;
715 end if;
716 -- allow only one transfer
717 if (p_trans_rec.transaction_type_code = 'TRANSFER' and
718 l_minus_rec > 1) then
719 l_msg_name := 'FA_INVALID_TXN_UNITS';
720 raise DIST_DATA_ERROR;
721 elsif (p_trans_rec.transaction_type_code = 'RECLASS' and
722 p_old_units <> ABS(l_tot_txn)) then
723 l_msg_name := 'FA_INVALID_TXN_UNITS';
724 raise DIST_DATA_ERROR;
725 end if;
726 -- check if unit balance goes to 0
727 elsif (p_trans_rec.transaction_type_code in ('TRANSFER OUT','UNIT ADJUSTMENT')) then
728 if ((p_old_units + l_tot_txn) < 1 ) then
729 l_msg_name := 'FA_INS_ADJ_ZERO_UNITS';
730 raise DIST_DATA_ERROR;
731 end if;
732 end if;
733
734 -- make sure total txn units are whole number except transfer txn
735 if (p_trans_rec.transaction_type_code <> 'TRANSFER') then
736 if (ABS(l_tot_txn) > trunc(ABS(l_tot_txn))) then
737 l_msg_name := 'FA_INVALID_TXN_UNITS';
738 raise DIST_DATA_ERROR;
739 end if;
740 end if;
741
742 -- end if;
743
744
745 x_total_txn_units := l_tot_txn;
746
747 if (p_trans_rec.transaction_type_code in ('TRANSFER', 'UNIT ADJUSTMENT') and
748 nvl(fa_cache_pkg.fazcbc_record.allow_interco_group_flag, 'N') <> 'Y') then
749
750 if not FA_UTIL_PVT.get_asset_type_rec(p_asset_hdr_rec,
751 l_asset_type_rec_old, p_log_level_rec => p_log_level_rec) then
752 fa_srvr_msg.add_message(calling_fn => 'FA_DISTRIBUTION_PVT.do_validation', p_log_level_rec => p_log_level_rec);
753 return FALSE;
754 end if;
755
756 if (l_asset_type_rec_old.asset_type <> 'GROUP') then
757 select group_asset_id
758 into l_group_asset_id
759 from fa_books
760 where asset_id = p_asset_hdr_rec.asset_id
761 and book_type_code = p_asset_hdr_rec.book_type_code
762 and date_ineffective is null;
763 end if;
764
765 if (l_group_asset_id is not null or
766 l_asset_type_rec_old.asset_type = 'GROUP') then
767
768 if not fa_interco_pvt.validate_grp_interco
769 (p_asset_hdr_rec => p_asset_hdr_rec,
770 p_trans_rec => p_trans_rec,
771 p_asset_type_rec => l_asset_type_rec_old,
772 p_group_asset_id => 1,
773 p_asset_dist_tbl => px_asset_dist_tbl,
774 p_calling_fn => 'FA_DISTRIBUTION_PVT.do_validation', p_log_level_rec => p_log_level_rec) then
775
776 fa_srvr_msg.add_message(calling_fn => 'FA_DISTRIBUTION_PVT.do_validation', p_log_level_rec => p_log_level_rec);
777 return FALSE;
778 end if;
779
780 end if;
781 end if;
782
783 return TRUE;
784
785 EXCEPTION
786 when DIST_DATA_ERROR then
787
788 if DH_C1%ISOPEN then
789 close DH_C1;
790 end if;
791 if DH_C2%ISOPEN then
792 close DH_C2;
793 end if;
794
795 fa_srvr_msg.add_message(
796 calling_fn => 'FA_DISTRIBUTION_PVT.do_validation',
797 name => l_msg_name, p_log_level_rec => p_log_level_rec);
798 return FALSE;
799
800 when others then
801 fa_srvr_msg.add_sql_error(
802 calling_fn => 'FA_DISTRIBUTION_PVT.do_validation', p_log_level_rec => p_log_level_rec);
803 return FALSE;
804 END do_validation;
805
806
807 FUNCTION valid_dist_data(p_trans_rec IN FA_API_TYPES.trans_rec_type,
808 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
809 p_asset_dist_tbl IN OUT NOCOPY FA_API_TYPES.asset_dist_tbl_type,
810 p_curr_index IN NUMBER,
811 p_validation_level IN NUMBER, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
812
813 RETURN BOOLEAN IS
814
815
816 l_count NUMBER;
817 l_high_bound number;
818 l_gl_chart_id number;
819
820 BEGIN
821
822 -- validate assigned_to
823 if (p_asset_dist_tbl(p_curr_index).assigned_to is not null) then
824 select count(*)
825 into l_count
826 from per_periods_of_service s, per_people_f p
827 where p.person_id = s.person_id
828 and trunc(sysdate) between
829 p.effective_start_date and p.effective_end_date
830 and nvl(s.actual_termination_date,sysdate) >= sysdate
831 and p.person_id = p_asset_dist_tbl(p_curr_index).assigned_to;
832 if (l_count = 0) then
833 fa_srvr_msg.add_message(
834 calling_fn => 'FA_DISTRIBUTION_PVT.valid_dist_data',
835 name => 'FA_INCORRECT_ASSIGNED_TO',
836 token1 => 'ASSIGNED_TO',
837 value1 => p_asset_dist_tbl(p_curr_index).assigned_to,
838 p_log_level_rec => p_log_level_rec);
839 return FALSE;
840 end if;
841 end if;
842
843 if not FA_CACHE_PKG.fazcbc(X_book => p_asset_hdr_rec.book_type_code, p_log_level_rec => p_log_level_rec) then
844 return FALSE;
845 else
846 l_gl_chart_id := FA_CACHE_PKG.fazcbc_record.accounting_flex_structure;
847 end if;
848
849 -- validate expense ccid
850 if not FA_ASSET_VAL_PVT.validate_expense_ccid(p_asset_dist_tbl(p_curr_index).expense_ccid,
851 l_gl_chart_id,
852 'FA_DISTRIBUTION_PVT.valid_dist_data', p_log_level_rec) then
853 return FALSE;
854 end if;
855
856
857 -- validate location id
858 if not FA_ASSET_VAL_PVT.validate_location_ccid(
859 p_trans_rec.transaction_type_code,
860 p_asset_dist_tbl(p_curr_index).location_ccid,
861 'FA_DISTRIBUTION_PVT.valid_dist_data',
862 p_log_level_rec) then
863 return FALSE;
864 end if;
865
866
867
868 -- check for duplicate lines
869 if (p_trans_rec.transaction_type_code = 'TRANSFER') then
870
871 -- bugfix 2846357
872 -- BUG# 6936546
873 if (p_validation_level = FND_API.G_VALID_LEVEL_FULL) then
874 if not FA_ASSET_VAL_PVT.validate_duplicate_dist (
875 p_transaction_type_code => p_trans_rec.transaction_type_code,
876 p_asset_dist_tbl => p_asset_dist_tbl,
877 p_curr_index => p_curr_index ,
878 p_calling_function => 'FA_DISTRIBUTION_PVT.valid_dist_data',
879 p_log_level_rec => p_log_level_rec) then
880
881 fa_srvr_msg.add_message(
882 calling_fn => 'FA_DISTRIBUTION_PVT.valid_dist_data', p_log_level_rec => p_log_level_rec);
883 return FALSE;
884 end if;
885 end if;
886 end if; /* if txn_type */
887
888 return TRUE;
889
890
891 EXCEPTION
892 when others then
893 fa_srvr_msg.add_sql_error(calling_fn => 'FA_DISTRIBUTION_PVT.valid_dist_data', p_log_level_rec => p_log_level_rec);
894 return FALSE;
895 END valid_dist_data;
896
897
898 FUNCTION units_in_sync(p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
899
900 RETURN BOOLEAN IS
901
902 l_ad_units number;
903 l_ah_units number;
904 l_dh_units number;
905
906 BEGIN
907 select current_units
908 into l_ad_units
909 from fa_additions
910 where asset_id = p_asset_hdr_rec.asset_id;
911
912 select units
913 into l_ah_units
914 from fa_asset_history
915 where asset_id = p_asset_hdr_rec.asset_id
916 and date_ineffective IS NULL;
917
918 select sum(units_assigned - nvl(transaction_units, 0))
919 into l_dh_units
920 from fa_distribution_history
921 where asset_id = p_asset_hdr_rec.asset_id
922 and book_type_code = p_asset_hdr_rec.book_type_code
923 and date_ineffective IS NULL;
924
925 if (l_ad_units <> l_ah_units or
926 l_ad_units <> l_dh_units or
927 l_ah_units <> l_dh_units) then
928
929 fa_srvr_msg.add_message(
930 calling_fn => 'FA_DISTRIBUTION_PVT.units_in_sync',
931 name => 'FA_UNITS_DIFFERENT', p_log_level_rec => p_log_level_rec);
932
933 return FALSE;
934 end if;
935
936 return TRUE;
937
938 EXCEPTION
939 when others then
940 fa_srvr_msg.add_sql_error
941 (calling_fn => 'FA_DISTRIBUTION_PVT.units_in_sync', p_log_level_rec => p_log_level_rec);
942 return FALSE;
943
944 END units_in_sync;
945
946
947
948 FUNCTION insert_txn_headers(px_trans_rec IN OUT NOCOPY FA_API_TYPES.trans_rec_type,
949 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
950 RETURN BOOLEAN IS
951
952 l_transaction_type_code px_trans_rec.transaction_type_code%TYPE;
953 l_transaction_subtype px_trans_rec.transaction_subtype%TYPE;
954 l_success boolean;
955 l_rowid ROWID;
956 l_txn_head_id px_trans_rec.transaction_header_id%TYPE;
957
958 BEGIN
959
960 l_txn_head_id := px_trans_rec.transaction_header_id; --for 3188851
961 l_transaction_type_code := px_trans_rec.transaction_type_code;
962 l_transaction_subtype := px_trans_rec.transaction_subtype;
963
964 -- if period of addition, update fa_transaction_headers with transfer in/void
965 if (p_asset_hdr_rec.period_of_addition = 'Y' and
966 G_release = 11) then
967
968 update fa_transaction_headers
969 set transaction_type_code = 'TRANSFER IN/VOID',
970 transaction_subtype = decode(l_transaction_type_code,
971 'RECLASS',l_transaction_subtype,
972 transaction_subtype)
973 where asset_id = p_asset_hdr_rec.asset_id
974 and book_type_code = p_asset_hdr_rec.book_type_code
975 and transaction_type_code = 'TRANSFER IN';
976
977 l_transaction_type_code := 'TRANSFER IN';
978 end if;
979
980 -- insert new row with transaction type associated with this transaction
981 FA_TRANSACTION_HEADERS_PKG.INSERT_ROW(
982 X_Rowid => l_rowid,
983 X_Transaction_Header_Id => l_txn_head_id,
984 X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
985 X_Asset_Id => p_asset_hdr_rec.asset_id,
986 X_Transaction_Type_Code => l_transaction_type_code,
987 X_Transaction_Date_Entered => px_trans_rec.transaction_date_entered,
988 X_Date_Effective => px_trans_rec.who_info.last_update_date,
989 X_Last_Update_Date => px_trans_rec.who_info.last_update_date,
990 X_Last_Updated_By => px_trans_rec.who_info.last_updated_by,
991 X_Transaction_Name => px_trans_rec.transaction_name,
992 X_Invoice_Transaction_Id => NULL,
993 X_Source_Transaction_Header_Id => px_trans_rec.source_transaction_header_id,
994 X_Mass_Reference_Id => px_trans_rec.mass_reference_id,
995 X_Last_Update_Login => px_trans_rec.who_info.last_update_login,
996 X_Transaction_Subtype => px_trans_rec.transaction_subtype,
997 X_Attribute1 => px_trans_rec.desc_flex.attribute1,
998 X_Attribute2 => px_trans_rec.desc_flex.attribute2,
999 X_Attribute3 => px_trans_rec.desc_flex.attribute3,
1000 X_Attribute4 => px_trans_rec.desc_flex.attribute4,
1001 X_Attribute5 => px_trans_rec.desc_flex.attribute5,
1002 X_Attribute6 => px_trans_rec.desc_flex.attribute6,
1003 X_Attribute7 => px_trans_rec.desc_flex.attribute7,
1004 X_Attribute8 => px_trans_rec.desc_flex.attribute8,
1005 X_Attribute9 => px_trans_rec.desc_flex.attribute9,
1006 X_Attribute10 => px_trans_rec.desc_flex.attribute10,
1007 X_Attribute11 => px_trans_rec.desc_flex.attribute11,
1008 X_Attribute12 => px_trans_rec.desc_flex.attribute12,
1009 X_Attribute13 => px_trans_rec.desc_flex.attribute13,
1010 X_Attribute14 => px_trans_rec.desc_flex.attribute14,
1011 X_Attribute15 => px_trans_rec.desc_flex.attribute15,
1012 X_Attribute_Category_Code=> px_trans_rec.desc_flex.attribute_category_code,
1013 X_Transaction_Key => px_trans_rec.transaction_key,
1014 X_Amortization_Start_Date
1015 => NULL,
1016 X_Calling_Interface => px_trans_rec.calling_interface,
1017 X_Mass_Transaction_ID => px_trans_rec.mass_transaction_id,
1018 X_Event_ID => px_trans_rec.event_id,
1019 X_Return_status => l_success,
1020 X_calling_FN =>'FA_DISTRIBUTION_PVT.insert_txn_headers', p_log_level_rec => p_log_level_rec);
1021
1022 if (not l_success) then
1023 return FALSE;
1024 end if;
1025
1026 px_trans_rec.transaction_header_id := l_txn_head_id;
1027 return TRUE;
1028
1029 EXCEPTION
1030 when others then
1031 fa_srvr_msg.add_sql_error(
1032 calling_fn => 'FA_DISTRIBUTION_PVT.insert_txn_headers', p_log_level_rec => p_log_level_rec);
1033 return FALSE;
1034
1035 END insert_txn_headers;
1036
1037
1038
1039 FUNCTION update_asset_history(p_trans_rec IN FA_API_TYPES.trans_rec_type,
1040 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1041 p_asset_cat_rec_new IN FA_API_TYPES.asset_cat_rec_type,
1042 p_asset_desc_rec_new IN FA_API_TYPES.asset_desc_rec_type, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1043
1044 RETURN BOOLEAN IS
1045
1046 CURSOR ah_cur (l_asset_id in NUMBER) IS
1047 select ah.rowid row_id,
1048 asset_id,
1049 category_id,
1050 asset_type,
1051 units,
1052 date_effective,
1053 date_ineffective,
1054 transaction_header_id_in,
1055 transaction_header_id_out,
1056 last_update_date,
1057 last_updated_by,
1058 last_update_login
1059 from fa_asset_history ah
1060 where asset_id = l_asset_id
1061 and date_ineffective is null;
1062
1063 ah_rec ah_cur%ROWTYPE;
1064 l_rowid ROWID;
1065 l_success boolean;
1066 l_asset_id number;
1067 l_txn_id number;
1068
1069 BEGIN
1070
1071 if (p_trans_rec.transaction_type_code = 'TRANSFER OUT') then
1072 select transaction_header_id_in
1073 into l_txn_id
1074 from fa_retirements
1075 where asset_id = p_asset_hdr_rec.asset_id
1076 and book_type_code = p_asset_hdr_rec.book_type_code
1077 and status = 'PENDING';
1078 --l_txn_id := 9999;
1079 else
1080 l_txn_id := p_trans_rec.transaction_header_id;
1081 end if;
1082
1083 open ah_cur(p_asset_hdr_rec.asset_id);
1084 fetch ah_cur into ah_rec;
1085 close ah_cur;
1086 --
1087 if (p_asset_hdr_rec.period_of_addition = 'Y' and
1088 G_release = 11) then
1089
1090 FA_ASSET_HISTORY_PKG.Update_Row
1091 (X_Rowid => ah_rec.Row_Id,
1092 X_Asset_Id => ah_rec.Asset_Id,
1093 X_Category_Id => p_asset_cat_rec_new.category_id,
1094 X_Asset_Type => ah_rec.Asset_Type,
1095 X_Units => p_asset_desc_rec_new.current_units,
1096 X_Date_Effective => ah_rec.Date_Effective,
1097 X_Date_Ineffective => ah_rec.date_ineffective,
1098 X_Transaction_Header_Id_In =>
1099 ah_rec.Transaction_Header_Id_In,
1100 X_Transaction_Header_Id_Out=> ah_rec.transaction_header_id_out,
1101 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1102 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1103 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
1104 X_Return_Status => l_success,
1105 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_asset_history', p_log_level_rec => p_log_level_rec);
1106 if (not l_success) then
1107 return FALSE;
1108 end if;
1109
1110 else
1111
1112 -- terminate old_asset_history
1113 FA_ASSET_HISTORY_PKG.Update_Row
1114 (X_Rowid => ah_rec.Row_Id,
1115 X_Asset_Id => ah_rec.Asset_Id,
1116 X_Category_Id => ah_rec.Category_Id,
1117 X_Asset_Type => ah_rec.Asset_Type,
1118 X_Units => ah_rec.Units,
1119 X_Date_Effective => ah_rec.Date_Effective,
1120 X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
1121 X_Transaction_Header_Id_In =>
1122 ah_rec.Transaction_Header_Id_In,
1123 X_Transaction_Header_Id_Out=> l_txn_id,
1124 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1125 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1126 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
1127 X_Return_Status => l_success,
1128 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_asset_history', p_log_level_rec => p_log_level_rec);
1129 if (not l_success) then
1130 return FALSE;
1131 end if;
1132
1133
1134 -- insert new row with new units and new category if applicable
1135 FA_ASSET_HISTORY_PKG.Insert_Row
1136 (X_Rowid => l_rowid,
1137 X_Asset_Id => p_asset_hdr_rec.asset_id,
1138 X_Category_Id => p_asset_cat_rec_new.category_id,
1139 X_Asset_Type => ah_rec.asset_type,
1140 X_Units => p_asset_desc_rec_new.current_units,
1141 X_Date_Effective => p_trans_rec.who_info.last_update_date,
1142 X_Date_Ineffective => NULL,
1143 X_Transaction_Header_Id_In=> l_txn_id,
1144 X_Transaction_Header_Id_Out=> NULL,
1145 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1146 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1147 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
1148 X_Return_Status => l_success,
1149 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_asset_history', p_log_level_rec => p_log_level_rec);
1150 if (not l_success) then
1151 return FALSE;
1152 end if;
1153
1154 end if;
1155 return TRUE;
1156
1157 EXCEPTION
1158 WHEN Others THEN
1159 fa_srvr_msg.add_sql_error(
1160 calling_fn => 'FA_DISTRIBUTION_PVT.update_asset_history', p_log_level_rec => p_log_level_rec);
1161 return FALSE;
1162 END update_asset_history;
1163
1164
1165 FUNCTION update_additions(p_trans_rec IN FA_API_TYPES.trans_rec_type,
1166 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1167 p_asset_cat_rec_new IN FA_API_TYPES.asset_cat_rec_type,
1168 p_asset_desc_rec_new IN FA_API_TYPES.asset_desc_rec_type, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1169 RETURN BOOLEAN IS
1170
1171 BEGIN
1172 if (p_trans_rec.transaction_type_code in ('UNIT ADJUSTMENT','TRANSFER OUT')) then
1173 update fa_additions_b
1174 set current_units = p_asset_desc_rec_new.current_units,
1175 last_update_date = p_trans_rec.who_info.last_update_date,
1176 last_update_login = p_trans_rec.who_info.last_update_login,
1177 last_updated_by = p_trans_rec.who_info.last_updated_by
1178 where asset_id = p_asset_hdr_rec.asset_id;
1179
1180 elsif (p_trans_rec.transaction_type_code = 'RECLASS') then
1181 if not fa_cache_pkg.fazcat(p_asset_cat_rec_new.category_id, p_log_level_rec => p_log_level_rec) then
1182 return FALSE;
1183 end if;
1184
1185 -- Bug 3148518 : Assign context with context value not attribute_category_code
1186
1187 update fa_additions_b
1188 set asset_category_id = p_asset_cat_rec_new.category_id,
1189 property_type_code = fa_cache_pkg.fazcat_record.property_type_code,
1190 property_1245_1250_code = fa_cache_pkg.fazcat_record.property_1245_1250_code,
1191 owned_leased = fa_cache_pkg.fazcat_record.owned_leased,
1192 attribute1 = p_asset_cat_rec_new.desc_flex.attribute1,
1193 attribute2 = p_asset_cat_rec_new.desc_flex.attribute2,
1194 attribute3 = p_asset_cat_rec_new.desc_flex.attribute3,
1195 attribute4 = p_asset_cat_rec_new.desc_flex.attribute4,
1196 attribute5 = p_asset_cat_rec_new.desc_flex.attribute5,
1197 attribute6 = p_asset_cat_rec_new.desc_flex.attribute6,
1198 attribute7 = p_asset_cat_rec_new.desc_flex.attribute7,
1199 attribute8 = p_asset_cat_rec_new.desc_flex.attribute8,
1200 attribute9 = p_asset_cat_rec_new.desc_flex.attribute9,
1201 attribute10 = p_asset_cat_rec_new.desc_flex.attribute10,
1202 attribute11 = p_asset_cat_rec_new.desc_flex.attribute11,
1203 attribute12 = p_asset_cat_rec_new.desc_flex.attribute12,
1204 attribute13 = p_asset_cat_rec_new.desc_flex.attribute13,
1205 attribute14 = p_asset_cat_rec_new.desc_flex.attribute14,
1206 attribute15 = p_asset_cat_rec_new.desc_flex.attribute15,
1207 attribute16 = p_asset_cat_rec_new.desc_flex.attribute16,
1208 attribute17 = p_asset_cat_rec_new.desc_flex.attribute17,
1209 attribute18 = p_asset_cat_rec_new.desc_flex.attribute18,
1210 attribute19 = p_asset_cat_rec_new.desc_flex.attribute19,
1211 attribute20 = p_asset_cat_rec_new.desc_flex.attribute20,
1212 attribute21 = p_asset_cat_rec_new.desc_flex.attribute21,
1213 attribute22 = p_asset_cat_rec_new.desc_flex.attribute22,
1214 attribute23 = p_asset_cat_rec_new.desc_flex.attribute23,
1215 attribute24 = p_asset_cat_rec_new.desc_flex.attribute24,
1216 attribute25 = p_asset_cat_rec_new.desc_flex.attribute25,
1217 attribute26 = p_asset_cat_rec_new.desc_flex.attribute26,
1218 attribute27 = p_asset_cat_rec_new.desc_flex.attribute27,
1219 attribute28 = p_asset_cat_rec_new.desc_flex.attribute28,
1220 attribute29 = p_asset_cat_rec_new.desc_flex.attribute29,
1221 attribute30 = p_asset_cat_rec_new.desc_flex.attribute30,
1222 attribute_category_code =
1223 p_asset_cat_rec_new.desc_flex.attribute_category_code,
1224 context = p_asset_cat_rec_new.desc_flex.context,
1225 last_update_date = p_trans_rec.who_info.last_update_date,
1226 last_update_login = p_trans_rec.who_info.last_update_login,
1227 last_updated_by = p_trans_rec.who_info.last_updated_by
1228 where asset_id = p_asset_hdr_rec.asset_id;
1229 end if;
1230
1231 if (p_trans_rec.transaction_type_code in ('TRANSFER','UNIT ADJUSTMENT')) then
1232 update fa_additions_b
1233 set unit_adjustment_flag = 'NO'
1234 where asset_id = p_asset_hdr_rec.asset_id
1235 and unit_adjustment_flag = 'YES';
1236 end if;
1237 return TRUE;
1238
1239 EXCEPTION
1240 when others then
1241 fa_srvr_msg.add_sql_error(
1242 calling_fn => 'FA_DISTRIBUTION_PVT.update_additions', p_log_level_rec => p_log_level_rec);
1243 return FALSE;
1244 END update_additions;
1245
1246
1247 FUNCTION update_books(p_trans_rec IN FA_API_TYPES.trans_rec_type,
1248 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1249 p_period_rec IN FA_API_TYPES.period_rec_type, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1250 RETURN BOOLEAN IS
1251
1252 cursor prior_period_tax_books(l_asset_id in number,
1253 l_book_type_code in varchar2) is
1254 select bc.book_type_code,nvl(bc.allow_backdated_transfers_flag,'Y')
1255 from fa_book_controls bc,
1256 fa_books bk,
1257 fa_deprn_summary ds
1258 where bk.asset_id = l_asset_id
1259 and bk.book_type_code = bc.book_type_code
1260 and bk.date_ineffective is null
1261 and nvl(bc.date_ineffective, sysdate+1) > sysdate
1262 and bc.distribution_source_book = l_book_type_code
1263 and bc.book_class = 'TAX'
1264 -- bug# 2152033 only updates assets which have been depreciated
1265 and ds.asset_id = bk.asset_id
1266 and ds.book_type_code = bc.book_type_code
1267 and ds.period_counter < bc.last_period_counter
1268 and ds.deprn_source_code = 'BOOKS';
1269 --bug fix 2186234 starts
1270 cursor prior_period_tfr_tax_books (l_asset_id in number,
1271 l_book_type_code in varchar2) is
1272 select distinct transaction_header_id
1273 from fa_adjustments adj
1274 where adj.asset_id = l_asset_id
1275 and adj.book_type_code = l_book_type_code
1276 and source_type_code = 'TRANSFER'
1277 and adj.period_counter_created =
1278 (select period_counter from fa_deprn_periods where book_type_code =
1279 l_book_type_code and period_close_date is null);
1280 --bug fix 2186234 ends
1281
1282
1283 l_cal_period_close_date DATE;
1284 l_tax_cal_period_close_date DATE;
1285 l_tax_cal_period_open_date DATE;
1286 l_tax_book_type_code p_asset_hdr_rec.book_type_code%TYPE;
1287 l_asset_id number;
1288 l_allow_backdated_transfers varchar2(1);
1289 --bug fix 2186234 starts
1290 l_trx_id number;
1291 trx_date_entered date;
1292 l_tax_flag number;
1293 --bug fix 2186234 ends
1294
1295 BEGIN
1296
1297 l_tax_flag:= 1;--bug fix 2186234
1298 -- if prior period transfer, update books
1299 if (p_trans_rec.transaction_date_entered < p_period_rec.calendar_period_open_date) then
1300
1301 update fa_books fabk
1302 set adjustment_required_status = 'TFR',
1303 annual_deprn_rounding_flag = 'TFR'
1304 where fabk.asset_id = p_asset_hdr_rec.asset_id
1305 and fabk.book_type_code = p_asset_hdr_rec.book_type_code
1306 and fabk.date_ineffective is NULL;
1307
1308 update fa_mc_books fabk
1309 set adjustment_required_status = 'TFR',
1310 annual_deprn_rounding_flag = 'TFR'
1311 where fabk.asset_id = p_asset_hdr_rec.asset_id
1312 and fabk.book_type_code = p_asset_hdr_rec.book_type_code
1313 and fabk.date_ineffective is NULL;
1314
1315 end if;
1316
1317 open prior_period_tax_books(p_asset_hdr_rec.asset_id,
1318 p_asset_hdr_rec.book_type_code);
1319 loop
1320 fetch prior_period_tax_books into l_tax_book_type_code,l_allow_backdated_transfers;
1321
1322 if (prior_period_tax_books%NOTFOUND) then
1323 exit;
1324 end if;
1325
1326 -- get the current open period dates
1327 -- did not use common routine get_period_rec
1328 -- as it does extra select on fiscal yr which
1329 -- is redundant.
1330 select calendar_period_open_date
1331 into l_tax_cal_period_open_date
1332 from fa_deprn_periods
1333 where book_type_code = l_tax_book_type_code
1334 and period_close_date is null;
1335
1336 --bug fix 2186234 starts
1337 begin
1338 open prior_period_tfr_tax_books (p_asset_hdr_rec.asset_id,
1339 l_tax_book_type_code);
1340
1341 loop
1342
1343 fetch prior_period_tfr_tax_books into l_trx_id;
1344
1345 exit when prior_period_tfr_tax_books%notfound;
1346
1347 select TRANSACTION_DATE_ENTERED into trx_date_entered from
1348 fa_transaction_headers where transaction_header_id = l_trx_id;
1349 if (trx_date_entered < l_tax_cal_period_open_date)
1350 then
1351 l_tax_flag:= 0;
1352
1353 update fa_books fabk
1354 set
1355 adjustment_required_status = 'NONE',
1356 annual_deprn_rounding_flag = NULL
1357 where fabk.asset_id = p_asset_hdr_rec.asset_id
1358 and fabk.book_type_code = l_tax_book_type_code
1359 and fabk.date_ineffective is NULL;
1360
1361 update fa_mc_books fabk
1362 set
1363 adjustment_required_status = 'NONE',
1364 annual_deprn_rounding_flag = NULL
1365 where fabk.asset_id = p_asset_hdr_rec.asset_id
1366 and fabk.book_type_code = l_tax_book_type_code
1367 and fabk.date_ineffective is NULL;
1368 exit;
1369
1370 end if;
1371 end loop;
1372
1373 close prior_period_tfr_tax_books;
1374 EXCEPTION
1375 when no_data_found then
1376 l_tax_flag:= 1;
1377 close prior_period_tfr_tax_books;
1378 end;
1379
1380 --bug fix 2186234 ends
1381
1382
1383 if (p_trans_rec.transaction_date_entered < l_tax_cal_period_open_date) then
1384
1385 update fa_books fabk
1386 set adjustment_required_status = decode(l_allow_backdated_transfers,'N','NONE','TFR'),
1387 annual_deprn_rounding_flag = decode(l_allow_backdated_transfers,'N',NULL,'TFR')
1388 where fabk.asset_id = p_asset_hdr_rec.asset_id
1389 and fabk.book_type_code = l_tax_book_type_code
1390 and fabk.date_ineffective is NULL;
1391
1392 update fa_mc_books fabk
1393 set adjustment_required_status = decode(l_allow_backdated_transfers,'N','NONE','TFR'),
1394 annual_deprn_rounding_flag = decode(l_allow_backdated_transfers,'N',NULL,'TFR')
1395 where fabk.asset_id = p_asset_hdr_rec.asset_id
1396 and fabk.book_type_code = l_tax_book_type_code
1397 and fabk.date_ineffective is NULL;
1398
1399 end if;
1400
1401 end loop;
1402 close prior_period_tax_books;
1403
1404 return TRUE;
1405
1406 EXCEPTION
1407 when others then
1408 fa_srvr_msg.add_sql_error(
1409 calling_fn => 'FA_DISTRIBUTION_PVT.update_books', p_log_level_rec => p_log_level_rec);
1410 return FALSE;
1411
1412 END update_books;
1413
1414 FUNCTION update_dist_history(p_trans_rec IN FA_API_TYPES.trans_rec_type,
1415 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
1416 p_asset_dist_tbl IN FA_API_TYPES.asset_dist_tbl_type, p_log_level_rec IN FA_API_TYPES.log_level_rec_type)
1417 RETURN BOOLEAN IS
1418
1419 CURSOR dh_cur (l_asset_id IN NUMBER, l_distribution_id IN NUMBER) IS
1420 select dh.rowid row_id, dh.*
1421 from fa_distribution_history dh
1422 where asset_id = l_asset_id
1423 and distribution_id = l_distribution_id
1424 and date_ineffective is null;
1425
1426 dh_rec dh_cur%ROWTYPE;
1427 l_rowid ROWID;
1428 l_asset_id number;
1429 l_distribution_id number;
1430 l_book_header_id NUMBER;
1431 l_tfr_det_dist_id NUMBER;
1432 l_retirement_id NUMBER;
1433 l_ret_id NUMBER;
1434
1435 BEGIN
1436
1437 select transaction_header_id_in
1438 into l_Book_Header_Id
1439 from fa_books
1440 where asset_id = p_asset_hdr_rec.asset_id
1441 and book_type_code = p_asset_hdr_rec.book_type_code
1442 and date_ineffective is null;
1443
1444 if p_trans_rec.transaction_type_code = 'TRANSFER OUT' then
1445 select retirement_id
1446 into l_retirement_id
1447 from fa_retirements
1448 where asset_id = p_asset_hdr_rec.asset_id
1449 and book_type_code = p_asset_hdr_rec.book_type_code
1450 and status = 'PENDING';
1451 --l_retirement_id := 8888;
1452 end if;
1453
1454
1455 FOR i in p_asset_dist_tbl.first..p_asset_dist_tbl.last LOOP
1456
1457 l_rowid := NULL;
1458 l_distribution_id := NULL;
1459 if (p_asset_dist_tbl(i).distribution_id is not null) then
1460
1461 open dh_cur(p_asset_hdr_rec.asset_id,
1462 p_asset_dist_tbl(i).distribution_id);
1463 fetch dh_cur into dh_rec;
1464 close dh_cur;
1465
1466 if (p_trans_rec.transaction_type_code = 'TRANSFER OUT') then
1467 l_ret_id := l_retirement_id;
1468 else
1469 l_ret_id := dh_rec.retirement_id;
1470 end if;
1471
1472 -- partial unit change
1473 if ((p_asset_dist_tbl(i).transaction_units +
1474 p_asset_dist_tbl(i).units_assigned) > 0) then
1475
1476 -- terminate old row
1477 FA_DISTRIBUTION_HISTORY_PKG.UPDATE_ROW
1478 (X_Rowid => dh_rec.row_id,
1479 X_Distribution_Id => dh_rec.distribution_id,
1480 X_Book_Type_Code => dh_rec.book_type_code,
1481 X_Asset_Id => dh_rec.asset_id,
1482 X_Units_Assigned => dh_rec.units_assigned,
1483 X_Date_Effective => dh_rec.date_effective,
1484 X_Code_Combination_Id => dh_rec.code_combination_id,
1485 X_Location_Id => dh_rec.location_id,
1486 X_Transaction_Header_Id_In => dh_rec.transaction_header_id_in,
1487 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1488 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1489 X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
1490 X_Assigned_To => dh_rec.assigned_to,
1491 X_Transaction_Header_Id_Out =>p_trans_rec.transaction_header_id,
1492 X_Transaction_Units => p_asset_dist_tbl(i).transaction_units,
1493 X_Retirement_Id => l_ret_id,
1494 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
1495 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history', p_log_level_rec => p_log_level_rec);
1496
1497 -- create new row with new units
1498 FA_DISTRIBUTION_HISTORY_PKG.INSERT_ROW
1499 (X_Rowid => l_rowid,
1500 X_Distribution_Id => l_distribution_id,
1501 X_Book_Type_Code => dh_rec.book_type_code,
1502 X_Asset_Id => dh_rec.asset_id,
1503 X_Units_Assigned => p_asset_dist_tbl(i).units_assigned +
1504 p_asset_dist_tbl(i).transaction_units,
1505 X_Date_Effective => p_trans_rec.who_info.last_update_date,
1506 X_Code_Combination_Id => dh_rec.code_combination_id,
1507 X_Location_Id => dh_rec.location_id,
1508 X_Transaction_Header_Id_In => p_trans_rec.transaction_header_id,
1509 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1510 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1511 X_Date_Ineffective => NULL,
1512 X_Assigned_To => dh_rec.assigned_to,
1513 X_Transaction_Header_Id_Out => NULL,
1514 X_Transaction_Units => NULL,
1515 X_Retirement_Id => NULL,
1516 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
1517 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history', p_log_level_rec => p_log_level_rec);
1518
1519 else -- full unit change, then terminate the row only
1520
1521 FA_DISTRIBUTION_HISTORY_PKG.UPDATE_ROW
1522 (X_Rowid => dh_rec.row_id,
1523 X_Distribution_Id => dh_rec.distribution_id,
1524 X_Book_Type_Code => dh_rec.book_type_code,
1525 X_Asset_Id => dh_rec.asset_id,
1526 X_Units_Assigned => dh_rec.units_assigned,
1527 X_Date_Effective => dh_rec.date_effective,
1528 X_Code_Combination_id => dh_rec.code_combination_id,
1529 X_Location_Id => dh_rec.location_id,
1530 X_Transaction_Header_Id_In => dh_rec.transaction_header_id_in,
1531 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1532 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1533 X_Date_Ineffective => p_trans_rec.who_info.last_update_date,
1534 X_Assigned_To => dh_rec.assigned_to,
1535 X_Transaction_Header_Id_Out =>p_trans_rec.transaction_header_id,
1536 X_Transaction_Units => p_asset_dist_tbl(i).transaction_units,
1537 X_Retirement_Id => l_ret_id,
1538 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
1539 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history', p_log_level_rec => p_log_level_rec);
1540 end if;
1541 l_tfr_det_dist_id := p_asset_dist_tbl(i).distribution_id;
1542 else
1543
1544 -- create new row with new units
1545 FA_DISTRIBUTION_HISTORY_PKG.INSERT_ROW
1546 (X_Rowid => l_rowid,
1547 X_Distribution_Id => l_distribution_id,
1548 X_Book_Type_Code => p_asset_hdr_rec.book_type_code,
1549 X_Asset_Id => p_asset_hdr_rec.asset_id,
1550 X_Units_Assigned => p_asset_dist_tbl(i).transaction_units,
1551 X_Date_Effective => p_trans_rec.who_info.last_update_date,
1552 X_Code_Combination_Id => p_asset_dist_tbl(i).expense_ccid,
1553 X_Location_Id => p_asset_dist_tbl(i).location_ccid,
1554 X_Transaction_Header_Id_In => p_trans_rec.transaction_header_id,
1555 X_Last_Update_Date => p_trans_rec.who_info.last_update_date,
1556 X_Last_Updated_By => p_trans_rec.who_info.last_updated_by,
1557 X_Date_Ineffective => NULL,
1558 X_Assigned_To => p_asset_dist_tbl(i).assigned_to,
1559 X_Transaction_Header_Id_Out => NULL,
1560 X_Transaction_Units => NULL,
1561 X_Retirement_Id => NULL,
1562 X_Last_Update_Login => p_trans_rec.who_info.last_update_login,
1563 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history', p_log_level_rec => p_log_level_rec);
1564 l_tfr_det_dist_id := l_distribution_id;
1565 end if;
1566
1567
1568 if (p_trans_rec.transaction_type_code <> 'RECLASS') then
1569 FA_TRANSFER_DETAILS_PKG.INSERT_ROW
1570 (X_Rowid => l_rowid,
1571 X_Transfer_Header_Id => p_trans_rec.transaction_header_id,
1572 X_Distribution_Id => l_tfr_det_dist_id,
1573 X_Book_Header_Id => l_book_header_id,
1574 X_Calling_Fn => 'FA_DISTRIBUTION_PVT.update_dist_history', p_log_level_rec => p_log_level_rec);
1575 end if;
1576
1577 END LOOP;
1578
1579 return TRUE;
1580
1581 EXCEPTION
1582 when others then
1583 fa_srvr_msg.add_sql_error(
1584 calling_fn => 'FA_DISTRIBUTION_PVT.update_dist_history', p_log_level_rec => p_log_level_rec);
1585 return FALSE;
1586 END update_dist_history;
1587
1588
1589
1590 END FA_DISTRIBUTION_PVT;