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