1 PACKAGE BODY FA_ASSET_VAL_PVT as
2 /* $Header: FAVVALB.pls 120.69.12010000.6 2009/02/05 09:20:04 mswetha ship $ */
3
4
5 FUNCTION validate
6 (p_trans_rec IN FA_API_TYPES.trans_rec_type,
7 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
8 p_asset_desc_rec IN FA_API_TYPES.asset_desc_rec_type,
9 p_asset_type_rec IN FA_API_TYPES.asset_type_rec_type,
10 p_asset_cat_rec IN FA_API_TYPES.asset_cat_rec_type,
11 p_asset_fin_rec IN FA_API_TYPES.asset_fin_rec_type,
12 p_asset_deprn_rec IN FA_API_TYPES.asset_deprn_rec_type,
13 p_asset_dist_tbl IN FA_API_TYPES.asset_dist_tbl_type,
14 p_inv_tbl IN FA_API_TYPES.inv_tbl_type,
15 p_calling_fn IN VARCHAR2,
16 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
17 ) RETURN boolean IS
18
19 l_distribution_count number;
20 val_err exception;
21 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
22
23 l_curr_index number;
24
25 BEGIN
26
27 if ((p_trans_rec.transaction_type_code = 'ADDITION') OR
28 (p_trans_rec.transaction_type_code = 'CIP ADDITION') OR
29 (p_trans_rec.transaction_type_code = 'GROUP ADDITION')) then
30
31 if not validate_asset_book (
32 p_transaction_type_code => p_trans_rec.transaction_type_code,
33 p_book_type_code => p_asset_hdr_rec.book_type_code,
34 p_asset_id => p_asset_hdr_rec.asset_id,
35 p_calling_fn => p_calling_fn,
36 p_log_level_rec => p_log_level_rec
37 ) then
38 raise val_err;
39 end if;
40
41 if not validate_cost (
42 p_transaction_type_code => p_trans_rec.transaction_type_code,
43 p_cost => p_asset_fin_rec.cost,
44 p_asset_type => p_asset_type_rec.asset_type,
45 p_num_invoices => p_inv_tbl.COUNT,
46 p_calling_fn => p_calling_fn,
47 p_log_level_rec => p_log_level_rec
48 ) then
49 raise val_err;
50 end if;
51
52 -- Bug No#7296545
53 -- Addding validation for current units
54 if not validate_current_units (
55 p_transaction_type_code => p_trans_rec.transaction_type_code,
56 p_current_units => p_asset_desc_rec.current_units
57 ) then
58 raise val_err;
59 end if;
60
61
62 if (fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE') then
63
64 if not validate_asset_number (
65 p_transaction_type_code => p_trans_rec.transaction_type_code,
66 p_asset_number => p_asset_desc_rec.asset_number,
67 p_asset_id => p_asset_hdr_rec.asset_id,
68 p_calling_fn => p_calling_fn,
69 p_log_level_rec => p_log_level_rec
70 ) then
71 raise val_err;
72 end if;
73
74 if not validate_owned_leased (
75 p_transaction_type_code => p_trans_rec.transaction_type_code,
76 p_owned_leased => p_asset_desc_rec.owned_leased,
77 p_calling_fn => p_calling_fn,
78 p_log_level_rec => p_log_level_rec
79 ) then
80 raise val_err;
81 end if;
82
83 if not validate_tag_number (
84 p_tag_number => p_asset_desc_rec.tag_number,
85 p_mass_addition_id => NULL,
86 p_calling_fn => p_calling_fn,
87 p_log_level_rec => p_log_level_rec
88 ) then
89 raise val_err;
90 end if;
91
92 if not validate_category (
93 p_transaction_type_code => p_trans_rec.transaction_type_code,
94 p_category_id => p_asset_cat_rec.category_id,
95 p_book_type_code => p_asset_hdr_rec.book_type_code,
96 p_calling_fn => p_calling_fn,
97 p_log_level_rec => p_log_level_rec
98 ) then
99 raise val_err;
100 end if;
101
102 if not validate_category_df (
103 p_transaction_type_code => p_trans_rec.transaction_type_code,
104 p_cat_desc_flex => p_asset_cat_rec.desc_flex,
105 p_calling_fn => p_calling_fn,
106 p_log_level_rec => p_log_level_rec
107 ) then
108 raise val_err;
109 end if;
110
111 if not validate_serial_number (
112 p_transaction_type_code => p_trans_rec.transaction_type_code,
113 p_serial_number => p_asset_desc_rec.serial_number,
114 p_calling_fn => p_calling_fn,
115 p_log_level_rec => p_log_level_rec
116 ) then
117 raise val_err;
118 end if;
119
120 if not validate_asset_key (
121 p_transaction_type_code => p_trans_rec.transaction_type_code,
122 p_asset_key_ccid => p_asset_desc_rec.asset_key_ccid,
123 p_calling_fn => p_calling_fn,
124 p_log_level_rec => p_log_level_rec
125 ) then
126 raise val_err;
127 end if;
128
129 if not validate_asset_type (
130 p_transaction_type_code => p_trans_rec.transaction_type_code,
131 p_asset_type => p_asset_type_rec.asset_type,
132 p_book_type_code => p_asset_hdr_rec.book_type_code,
133 p_category_id => p_asset_cat_rec.category_id,
134 p_calling_fn => p_calling_fn,
135 p_log_level_rec => p_log_level_rec
136 ) then
137 raise val_err;
138 end if;
139
140 if not validate_supplier_name (
141 p_transaction_type_code => p_trans_rec.transaction_type_code,
142 p_calling_fn => p_calling_fn,
143 p_log_level_rec => p_log_level_rec
144 ) then
145 raise val_err;
146 end if;
147
148 if not validate_supplier_number (
149 p_transaction_type_code => p_trans_rec.transaction_type_code,
150 p_calling_fn => p_calling_fn,
151 p_log_level_rec => p_log_level_rec
152 ) then
153 raise val_err;
154 end if;
155
156 if not validate_lease (
157 p_asset_id => p_asset_hdr_rec.asset_id,
158 p_lease_id => p_asset_desc_rec.lease_id,
159 p_log_level_rec => p_log_level_rec
160 ) then
161 raise val_err;
162 end if;
163
164 if not validate_warranty (
165 p_warranty_id => p_asset_desc_rec.warranty_id,
166 p_date_placed_in_service
167 => p_asset_fin_rec.date_placed_in_service,
168 p_book_type_code => p_asset_hdr_rec.book_type_code,
169 p_log_level_rec => p_log_level_rec
170 ) then
171 raise val_err;
172 end if;
173
174 if not validate_property_type (
175 p_property_type_code => p_asset_desc_rec.property_type_code,
176 p_log_level_rec => p_log_level_rec
177 ) then
178 raise val_err;
179 end if;
180
181 if not validate_1245_1250_code (
182 p_1245_1250_code => p_asset_desc_rec.property_1245_1250_code,
183 p_log_level_rec => p_log_level_rec
184 ) then
185 raise val_err;
186 end if;
187
188 l_distribution_count := p_asset_dist_tbl.COUNT;
189 l_asset_dist_tbl := p_asset_dist_tbl;
190
191 for i in 1..l_distribution_count loop
192
193 if not validate_assigned_to (
194 p_transaction_type_code => p_trans_rec.transaction_type_code,
195 p_assigned_to => p_asset_dist_tbl(i).assigned_to,
196 p_calling_fn => p_calling_fn,
197 p_log_level_rec => p_log_level_rec
198 ) then
199 raise val_err;
200 end if;
201
202 if not validate_expense_ccid (
203 p_expense_ccid => p_asset_dist_tbl(i).expense_ccid,
204 p_gl_chart_id => fa_cache_pkg.fazcbc_record.accounting_flex_structure,
205 p_calling_fn => p_calling_fn,
206 p_log_level_rec => p_log_level_rec
207 ) then
208 raise val_err;
209 end if;
210
211 if not validate_location_ccid (
212 p_transaction_type_code => p_trans_rec.transaction_type_code,
213 p_location_ccid => p_asset_dist_tbl(i).location_ccid,
214 p_calling_fn => p_calling_fn,
215 p_log_level_rec => p_log_level_rec
216 ) then
217 raise val_err;
218 end if;
219
220 -- bugfix 2846357
221 l_curr_index := i;
222 if not validate_duplicate_dist (
223 p_transaction_type_code => p_trans_rec.transaction_type_code,
224 p_asset_dist_tbl => l_asset_dist_tbl,
225 p_curr_index => l_curr_index,
226 p_log_level_rec => p_log_level_rec ) then
227 raise val_err;
228 end if;
229
230 end loop;
231
232 end if; -- corporate
233
234 if (p_asset_fin_rec.group_asset_id is not null and
235 p_asset_fin_rec.group_asset_id <> FND_API.G_MISS_NUM and
236 nvl(fa_cache_pkg.fazcbc_record.allow_interco_group_flag, 'N') <> 'Y') then
237 if not fa_interco_pvt.validate_grp_interco
238 (p_asset_hdr_rec => p_asset_hdr_rec,
239 p_trans_rec => p_trans_rec,
240 p_asset_type_rec => p_asset_type_rec,
241 p_group_asset_id => p_asset_fin_rec.group_asset_id,
242 p_asset_dist_tbl => p_asset_dist_tbl,
243 p_calling_fn => p_calling_fn) then
244 raise val_err;
245 end if;
246 end if;
247
248 end if; -- ADDITION only
249
250 return TRUE;
251
252 EXCEPTION
253 when val_err then
254 fa_srvr_msg.add_message(calling_fn => 'fa_asset_val_pvt.validate');
255 return FALSE;
256
257 END;
258
259 FUNCTION validate_asset_number
260 (p_transaction_type_code IN VARCHAR2,
261 p_asset_number IN VARCHAR2,
262 p_asset_id IN NUMBER DEFAULT NULL,
263 p_calling_fn IN VARCHAR2,
264 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
265 ) RETURN BOOLEAN IS
266
267 l_count number;
268 l_asset_number number(15) := 0;
269
270 BEGIN
271
272 if ((p_transaction_type_code = 'ADDITION') OR
273 (p_transaction_type_code = 'CIP ADDITION')) then
274
275 if (p_asset_number is not null) then
276
277 -- Asset number must be unique.
278 select count(*)
279 into l_count
280 from fa_additions_b
281 where asset_number = upper(p_asset_number);
282
283 if (l_count > 0) then
284 fa_srvr_msg.add_message(
285 calling_fn => 'fa_asset_val_pvt.validate_asset_number',
286 name => 'FA_ADD_ASSET_NUMBER_USED');
287 return FALSE;
288 end if;
289
290 -- Need to check fa_mass_additions also. Only do this validation
291 -- if it is being called from Prepare Mass Additions. Otherwise,
292 -- it fails during Post Mass Additions.
293 if (p_calling_fn = 'MASS_ADDITIONS_7.Check_S_Asset_Number') then
294 select count(*)
295 into l_count
296 from fa_mass_additions
297 where asset_number = p_asset_number
298 and queue_name = 'POST'; -- fix for bug 3433702
299
300 --if (l_count > 1) then
301 if (l_count > 0) then -- fix for bug 3433702
302 fa_srvr_msg.add_message(
303 calling_fn => 'fa_asset_val_pvt.validate_asset_number',
304 name => 'FA_ADD_ASSET_NUMBER_USED');
305 return FALSE;
306 end if;
307 end if;
308
309 if (p_asset_number <> to_char(nvl(p_asset_id, -999))) then
310
311 -- Check that numeric asset numbers are less than those used for
312 -- automatic asset numbering.
313 select count(*)
314 into l_count
315 from dual
316 where nvl(substr(p_asset_number, 1,1), '0') between '0' and '9'
317 and nvl(substr(p_asset_number, 2,1), '0') between '0' and '9'
318 and nvl(substr(p_asset_number, 3,1), '0') between '0' and '9'
319 and nvl(substr(p_asset_number, 4,1), '0') between '0' and '9'
320 and nvl(substr(p_asset_number, 5,1), '0') between '0' and '9'
321 and nvl(substr(p_asset_number, 6,1), '0') between '0' and '9'
322 and nvl(substr(p_asset_number, 7,1), '0') between '0' and '9'
323 and nvl(substr(p_asset_number, 8,1), '0') between '0' and '9'
324 and nvl(substr(p_asset_number, 9,1), '0') between '0' and '9'
325 and nvl(substr(p_asset_number,10,1), '0') between '0' and '9'
326 and nvl(substr(p_asset_number,11,1), '0') between '0' and '9'
327 and nvl(substr(p_asset_number,12,1), '0') between '0' and '9'
328 and nvl(substr(p_asset_number,13,1), '0') between '0' and '9'
329 and nvl(substr(p_asset_number,14,1), '0') between '0' and '9'
330 and nvl(substr(p_asset_number,15,1), '0') between '0' and '9';
331
332 if (l_count > 0) then
333 begin
334 l_asset_number := to_number(p_asset_number);
335 exception
336 when value_error then
340 calling_fn => 'fa_asset_val_pvt.validate_asset_number',
337 null;
338 when others then
339 fa_srvr_msg.add_message(
341 name => 'FA_ASSET_NUMBER',
342 token1 => 'ASSET_NUMBER',
343 value1 => p_asset_number);
344 return FALSE;
345 end;
346
347 if not fa_cache_pkg.fazsys() then
348 fa_srvr_msg.add_message (
349 calling_fn => 'fa_asset_val_pvt.validate_asset_number');
350 end if;
351
352 -- Fix for Bug #2585811. You don't need to validate if they
353 -- are using custom asset numbering.
354 if ((l_asset_number >=
355 fa_cache_pkg.fazsys_record.initial_asset_id) and
356 (nvl(fa_cache_pkg.fazsys_record.use_custom_asset_numbers_flag, 'N') <> 'Y')
357 ) then
358 fa_srvr_msg.add_message(
359 calling_fn => 'fa_asset_val_pvt.validate_asset_number',
360 name => 'FA_ADD_AUTOMATIC_NUMBER');
361 return FALSE;
362 end if;
363 end if;
364 end if;
365 else -- Asset Number is NULL
366 if not fa_cache_pkg.fazsys() then
367 fa_srvr_msg.add_message (
368 calling_fn => 'fa_asset_val_pvt.validate_asset_number');
369 end if;
370
371 -- Fix for Bug #2585811. If they are using custom asset numbering,
372 -- they must populate asset number.
373 if (nvl(fa_cache_pkg.fazsys_record.use_custom_asset_numbers_flag, 'N')
374 = 'Y')
375 then
376 fa_srvr_msg.add_message(
377 calling_fn => 'fa_asset_val_pvt.validate_asset_number',
378 name => 'FA_NULL_CUSTOM_ASSET_NUMBER');
379 return FALSE;
380 end if;
381 end if;
382 end if;
383
384 return TRUE;
385
386 END validate_asset_number;
387
388 FUNCTION validate_owned_leased
389 (p_transaction_type_code IN VARCHAR2,
390 p_owned_leased IN VARCHAR2,
391 p_calling_fn IN VARCHAR2,
392 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
393 ) RETURN BOOLEAN IS
394
395 BEGIN
396
397 if (p_owned_leased not in ('OWNED', 'LEASED')) then
398 fa_srvr_msg.add_message(
399 calling_fn => 'fa_asset_val_pvt.validate_owned_leased',
400 name => 'FA_INVALID_PARAMETER',
401 token1 => 'OWNED_LEASED',
402 value1 => nvl(p_owned_leased, '-999'));
403
404 return FALSE;
405 end if;
406
407 return TRUE;
408
409 END validate_owned_leased;
410
411 FUNCTION validate_category
412 (p_transaction_type_code IN VARCHAR2,
413 p_category_id IN NUMBER,
414 p_book_type_code IN VARCHAR2 DEFAULT NULL,
415 p_calling_fn IN VARCHAR2,
416 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
417 ) RETURN BOOLEAN IS
418
419 BEGIN
420 -- Check that the category exists.
421 if not fa_cache_pkg.fazcat (
422 X_cat_id => p_category_id,
423 p_log_level_rec => p_log_level_rec
424 ) then
425 fa_srvr_msg.add_message(
426 calling_fn => 'fa_asset_val_pvt.validate_asset_category');
427 return FALSE;
428 end if;
429
430 -- Check that the category is enabled.
431 if (fa_cache_pkg.fazcat_record.enabled_flag <> 'Y') then
432 fa_srvr_msg.add_message(
433 calling_fn => 'fa_asset_val_pvt.validate_asset_category',
434 name => 'FA_INCORRECT_CATEGORY_ID',
435 token1 => 'CATEGORY_ID',
436 value1 => p_category_id);
437 return FALSE;
438 end if;
439
440 -- removed check on capitalize flag as this isn't an asset level thing - bmr
441 if (p_book_type_code is not null) then
442
443 -- Make sure that the category/book exists.
444 if not (fa_cache_pkg.fazccb (
445 X_Book => p_book_type_code,
446 X_Cat_Id => p_category_id,
447 p_log_level_rec => p_log_level_rec
448 )) then
449
450 if (fa_cache_pkg.fazcbc_record.book_class = 'TAX') then
451 fa_srvr_msg.add_message(
452 calling_fn => 'fa_asset_val_pvt.validate_asset_category',
453 name => 'FA_MCP_CAT_NOT_IN_TAX');
454 else
455 fa_srvr_msg.add_message(
456 calling_fn => 'fa_asset_val_pvt.validate_asset_category',
457 name => 'FA_BOOK_CAT_NOT_SET_UP');
458 end if;
459
460 return FALSE;
461 end if;
462 end if;
463
464 return TRUE;
465 END validate_category;
466
467 -- Bug No#7296545
468 -- Addding validation for current units
469 --current units cannot be in fractions
470
471 FUNCTION validate_current_units
472 (p_transaction_type_code IN VARCHAR2,
473 p_current_units IN NUMBER
477
474 ) RETURN BOOLEAN IS
475 BEGIN
476
478 if ((p_transaction_type_code = 'ADDITION') OR
479 (p_transaction_type_code = 'CIP ADDITION')) then
480
481 --Checking if the current units contain fractional value
482 if instr(nvl(p_current_units,0),'.')=0 then
483 return TRUE;
484 else
485 fa_srvr_msg.add_message(
486 calling_fn => 'fa_asset_val_pvt.validate_current_units',
487 name => 'FA_NO_FRAC_UNITS');
488 return FALSE;
489 end if;
490 end if;
491 return TRUE;
492 END validate_current_units;
493
494
495 FUNCTION validate_category_df
496 (p_transaction_type_code IN VARCHAR2,
497 p_cat_desc_flex IN FA_API_TYPES.desc_flex_rec_type,
498 p_calling_fn IN VARCHAR2,
499 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
500 ) RETURN BOOLEAN IS
501
502 BEGIN
503 -- Check that the flexfield value is valid.
504
505 return TRUE;
506 END validate_category_df;
507
508 FUNCTION validate_serial_number
509 (p_transaction_type_code IN VARCHAR2,
510 p_serial_number IN VARCHAR2,
511 p_calling_fn IN VARCHAR2,
512 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
513 ) RETURN BOOLEAN IS
514
515 BEGIN
516 -- CRL check?
517
518 return TRUE;
519 END validate_serial_number;
520
521 FUNCTION validate_asset_key
522 (p_transaction_type_code IN VARCHAR2,
523 p_asset_key_ccid IN NUMBER,
524 p_calling_fn IN VARCHAR2,
525 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
526 ) RETURN BOOLEAN IS
527
528 l_required number;
529 l_is_asset_key_valid number;
530
531 cursor c_asset_key_req is
532 select 1
533 from fnd_id_flex_segments
534 where application_id = 140
535 and id_flex_code = 'KEY#'
536 and id_flex_num = fa_cache_pkg.fazsys_record.asset_key_flex_structure
537 and required_flag = 'Y';
538
539 BEGIN
540 -- check if the flexfield has any required segments
541 if G_asset_key_required is null then
542 if not fa_cache_pkg.fazsys then
543 fa_srvr_msg.add_message(
544 calling_fn => 'fa_asset_val_pvt.validate_asset_key');
545 return false;
546 end if;
547
548 open c_asset_key_req;
549 fetch c_asset_key_req into l_required;
550 if c_asset_key_req%notfound then
551 G_asset_key_required := FALSE;
552 else
553 G_asset_key_required := TRUE;
554 end if;
555 close c_asset_key_req;
556
557 end if;
558
559
560 -- check if the combination is null and required
561 if G_asset_key_required and p_asset_key_ccid is null then
562 fa_srvr_msg.add_message(
563 calling_fn => 'fa_asset_val_pvt.validate_asset_key',
564 name => 'FA_NULL_ASSET_KEY',
565 token1 => 'ASSET_KEY',
566 value1 => NULL);
567 return false;
568 end if;
569
570 -- check if the combination is valid
571 if (p_asset_key_ccid is not null) then
572
573 select count(*)
574 into l_is_asset_key_valid
575 from fa_asset_keywords
576 where code_combination_id = p_asset_key_ccid
577 and enabled_flag = 'Y';
578
579 if (l_is_asset_key_valid = 0) then
580 fa_srvr_msg.add_message(
581 calling_fn => 'fa_asset_val_pvt.validate_asset_key',
582 name => 'FA_INCORRECT_ASSET_KEY',
583 token1 => 'ASSET_KEY_CCID',
584 value1 => p_asset_key_ccid);
585 return false;
586 end if;
587 end if;
588
589 return TRUE;
590 END validate_asset_key;
591
592 FUNCTION validate_asset_type
593 (p_transaction_type_code IN VARCHAR2,
594 p_asset_type IN VARCHAR2,
595 p_book_type_code IN VARCHAR2,
596 p_category_id IN NUMBER,
597 p_calling_fn IN VARCHAR2,
598 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
599 ) RETURN BOOLEAN IS
600
601 l_capitalize_flag varchar2(3);
602
603 BEGIN
604
605 -- Check that it can only be capitalized, cip, or expensed.
606 if not ((p_asset_type = 'CAPITALIZED') or
607 (p_asset_type = 'CIP') or
608 (p_asset_type = 'EXPENSED') or
609 (p_asset_type = 'GROUP')) then
610
611 fa_srvr_msg.add_message(
612 calling_fn => 'fa_asset_val_pvt.validate_asset_type',
613 name => 'FA_DPR_BAD_ASSET_TYPE');
614 return FALSE;
615 end if;
616
617 -- Check for invalid asset_type/category combinations.
618 if ((fa_cache_pkg.fazcat_record.capitalize_flag = 'YES') and
619 (p_asset_type = 'EXPENSED')) then
620
621 fa_srvr_msg.add_message(
622 calling_fn => 'fa_asset_val_pvt.validate_asset_type',
623 name => 'FA_INCORRECT_ASSET_TYPE');
624 return FALSE;
625 end if;
629 (p_asset_type = 'CIP') or
626
627 if ((fa_cache_pkg.fazcat_record.capitalize_flag = 'NO') and
628 ((p_asset_type = 'CAPITALIZED') or
630 (p_asset_type = 'GROUP'))) then
631
632 fa_srvr_msg.add_message(
633 calling_fn => 'fa_asset_val_pvt.validate_asset_type',
634 name => 'FA_INCORRECT_ASSET_TYPE');
635 return FALSE;
636 end if;
637
638 -- If asset is CIP, check the CIP accounts.
639 if (p_asset_type = 'CIP') then
640 if ((fa_cache_pkg.fazccb_record.cip_clearing_acct is null) OR
641 (fa_cache_pkg.fazccb_record.cip_cost_acct is null)) then
642
643 fa_srvr_msg.add_message(
644 calling_fn => 'fa_asset_val_pvt.validate_asset_type',
645 name => 'FA_SHARED_NO_CIP_ACCOUNTS');
646 return FALSE;
647 end if;
648 end if;
649
650 -- do not allow group if not enabled
651 if (p_asset_type = 'GROUP' and
652 nvl(fa_cache_pkg.fazcbc_record.allow_group_deprn_flag, 'N') <> 'Y') then
653 fa_srvr_msg.add_message(
654 calling_fn => 'fa_asset_val_pvt.validate_asset_type',
655 name => '***FA_GROUP_NOT_ALLOWED***');
656 return FALSE;
657 end if;
658
659 return TRUE;
660
661 END validate_asset_type;
662
663 FUNCTION validate_depreciate_flag
664 (p_depreciate_flag IN VARCHAR2,
665 p_calling_fn IN VARCHAR2,
666 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
667 ) RETURN BOOLEAN IS
668
669 BEGIN
670
671 if ((p_depreciate_flag <> 'YES' and
672 p_depreciate_flag <> 'NO') or
673 p_depreciate_flag IS NULL) then
674 fa_srvr_msg.add_message(
675 calling_fn => 'fa_asset_val_pvt.val_depreciate_flag',
676 name => 'FA_INCORRECT_DEPRECIATE_FLAG');
677 return FALSE;
678 end if;
679
680 return TRUE;
681
682 END validate_depreciate_flag;
683
684 FUNCTION validate_supplier_name
685 (p_transaction_type_code IN VARCHAR2,
686 p_calling_fn IN VARCHAR2,
687 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
688 ) RETURN BOOLEAN IS
689
690 BEGIN
691 -- From vendor_name_q lov in asset workbench.
692
693 -- Check any dependencies w/ asset type.
694
695 -- Check any dependencies w/ supplier number.
696
697 return TRUE;
698 END validate_supplier_name;
699
700 FUNCTION validate_supplier_number
701 (p_transaction_type_code IN VARCHAR2,
702 p_calling_fn IN VARCHAR2,
703 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
704 ) RETURN BOOLEAN IS
705
706 BEGIN
707 -- From vendor_number_q lov in asset workbench.
708
709 -- Check any dependencies w/ asset type.
710
711 -- Check any dependencies w/ supplier name.
712
713 return TRUE;
714 END validate_supplier_number;
715
716 FUNCTION validate_asset_book
717 (p_transaction_type_code IN VARCHAR2,
718 p_book_type_code IN VARCHAR2,
719 p_asset_id IN NUMBER,
720 p_calling_fn IN VARCHAR2,
721 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
722 ) RETURN BOOLEAN IS
723
724 l_exists number;
725
726 BEGIN
727
728 -- book controls cache should have already been loaded by calling api
729 -- so this is obsolete: Validate that book exists.
730
731 -- Validate that book is active.
732 if (fa_cache_pkg.fazcbc_record.date_ineffective is not null) then
733
734 fa_srvr_msg.add_message(
735 calling_fn => 'fa_asset_val_pvt.validate_asset_book',
736 name => 'FA_BOOK_INEFFECTIVE_BOOK');
737 return FALSE;
738 end if;
739
740 if ((p_transaction_type_code = 'ADDITION') OR
741 (p_transaction_type_code = 'CIP ADDITION') OR
742 (p_transaction_type_code = 'GROUP ADDITION')) then
743
744 -- Validate that asset does not already exist in book.
745 select count(*)
746 into l_exists
747 from fa_books
748 where book_type_code = p_book_type_code
749 and asset_id = p_asset_id
750 and rownum <= 1;
751
752 if (l_exists > 0) then
753 fa_srvr_msg.add_message(
754 calling_fn => 'fa_asset_val_pvt.validate_asset_book',
755 name => 'FA_MCP_IN_TAX_BOOK',
756 token1 => 'ASSET',
757 value1 => to_char (p_asset_id),
758 token2 => 'BOOK',
759 value2 => p_book_type_code);
760 return FALSE;
761 end if;
762
763 -- For Addition transactions, asset must exist in the Corporate book.
764 if (fa_cache_pkg.fazcbc_record.book_class <> 'CORPORATE') then
765
766 select count(*)
767 into l_exists
768 from fa_books bks
769 where exists
770 (
771 select 'X'
772 from fa_book_controls bc
773 where bc.book_type_code = p_book_type_code
774 and bc.distribution_source_book = bks.book_type_code
775 )
776 and bks.asset_id = p_asset_id;
780 calling_fn => 'fa_asset_val_pvt.validate_asset_book',
777
778 if (l_exists = 0) then
779 fa_srvr_msg.add_message(
781 name => 'FA_MASSCHG_NOT_IN_CORP');
782 return FALSE;
783 end if;
784 end if;
785 else
786 -- for non-addition trxs verify asset does exist in book
787 select count(*)
788 into l_exists
789 from fa_books
790 where book_type_code = p_book_type_code
791 and asset_id = p_asset_id
792 and rownum <= 1;
793
794 if (l_exists = 0) then
795 fa_srvr_msg.add_message(
796 calling_fn => 'fa_asset_val_pvt.validate_asset_book',
797 name => 'FA_EXP_GET_ASSET_INFO');
798 return false;
799 end if;
800
801 if ((p_transaction_type_code = 'TRANSFER' or
802 p_transaction_type_code = 'RECLASS' or
803 p_transaction_type_code = 'UNIT ADJUSTMENT') and
804 fa_cache_pkg.fazcbc_record.book_class <> 'CORPORATE') then
805 fa_srvr_msg.add_message(
806 calling_fn => 'fa_asset_val_pvt.validate_asset_book',
807 name => '***FA_BOOK_NOT_CORP***');
808 return false;
809 end if;
810 end if;
811
812 return TRUE;
813 END validate_asset_book;
814
815 FUNCTION validate_cost
816 (p_transaction_type_code IN VARCHAR2,
817 p_cost IN NUMBER,
818 p_asset_type IN VARCHAR2,
819 p_num_invoices IN NUMBER DEFAULT 0,
820 p_calling_fn IN VARCHAR2,
821 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
822 ) RETURN BOOLEAN IS
823
824 BEGIN
825 -- If asset type is CIP, cost should be zero.
826 if ((fa_cache_pkg.fazcbc_record.book_class = 'CORPORATE') AND
827 (p_asset_type = 'CIP') AND (p_cost <> 0) AND (p_num_invoices = 0)) then
828 fa_srvr_msg.add_message(
829 calling_fn => 'fa_asset_val_pvt.validate_cost',
830 name => 'FA_BOOK_CIP_COST');
831 return FALSE;
832 elsif (p_asset_type = 'GROUP' and
833 p_cost <> 0) then
834 fa_srvr_msg.add_message(
835 calling_fn => 'fa_asset_val_pvt.validate_cost',
836 name => '***FA_BOOK_GROUP_COST***');
837 return FALSE;
838 end if;
839
840
841
842 return TRUE;
843 END validate_cost;
844
845 FUNCTION validate_assigned_to
846 (p_transaction_type_code IN VARCHAR2,
847 p_assigned_to IN NUMBER,
848 p_date IN DATE DEFAULT sysdate,
849 p_calling_fn IN VARCHAR2,
850 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
851 ) RETURN BOOLEAN IS
852
853 l_rowcount number;
854
855 BEGIN
856
857 -- checks to see if employee is valid
858 -- checks against p_date, p_date is default to sysdate
859
860 if p_assigned_to is not null then
861
862 select count(*)
863 into l_rowcount
864 from per_periods_of_service s, per_people_f p
865 where p.person_id = s.person_id
866 and trunc(p_date) between
867 p.effective_start_date and p.effective_end_date
868 and nvl(s.actual_termination_date,p_date) >= p_date
869 and p.person_id = p_assigned_to;
870
871 if (l_rowcount = 0) then
872 -- bugfix 3854700
873 fa_srvr_msg.add_message(
874 calling_fn => 'fa_asset_val_pvt.validate_assigned_to',
875 name => 'FA_EMP_NOT_VALID' );
876 return FALSE;
877 end if;
878 end if;
879
880 return TRUE;
881
882 END validate_assigned_to;
883
884 FUNCTION validate_location_ccid
885 (p_transaction_type_code IN VARCHAR2,
886 p_location_ccid IN NUMBER,
887 p_calling_fn IN VARCHAR2,
888 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
889 ) RETURN BOOLEAN IS
890
891 l_is_location_valid number;
892
893 BEGIN
894
895 -- The location ccid cannot be null.
896 if (p_location_ccid is null) then
897 fa_srvr_msg.add_message(
898 calling_fn => 'fa_asset_val_pvt.validate_location_ccid',
899 name => 'FA_NULL_LOCATION');
900 return FALSE;
901 end if;
902
903 -- Check that location exists.
904 select count(*)
905 into l_is_location_valid
906 from fa_locations
907 where location_id = p_location_ccid
908 and enabled_flag = 'Y';
909
910 if (l_is_location_valid = 0) then
911 fa_srvr_msg.add_message(
912 calling_fn => 'fa_asset_val_pvt.validate_location_ccid',
913 name => 'FA_INCORRECT_LOCATION',
914 token1 => 'LOCATION_ID',
915 value1 => p_location_ccid);
916 return FALSE;
917 end if;
918
919 return TRUE;
920
921 END validate_location_ccid;
922
923 --bug 5501090: Added parameter p_asset_type
924 FUNCTION validate_dpis
925 (p_transaction_type_code IN VARCHAR2,
929 p_old_date_placed_in_service IN VARCHAR2 DEFAULT NULL,
926 p_book_type_code IN VARCHAR2,
927 p_date_placed_in_service IN DATE,
928 p_prorate_convention_code IN VARCHAR2 DEFAULT NULL,
930 p_asset_id IN NUMBER DEFAULT NULL,
931 p_db_rule_name IN VARCHAR2 DEFAULT NULL, -- ENERGY
932 p_rate_source_rule IN VARCHAR2 DEFAULT NULL, -- ENERGY
933 p_calling_interface IN VARCHAR2 DEFAULT NULL,
934 p_calling_fn IN VARCHAR2,
935 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null,
936 p_transaction_subtype IN VARCHAR2 DEFAULT 'EXPENSED',
937 p_asset_type IN VARCHAR2 DEFAULT NULL
938 ) RETURN BOOLEAN IS
939 --
940 -- NOTE: ENERGY ENHANCEMENT
941 -- p_db_rule_name and p_rate_source_rule are added so that prior
942 -- period date will not be allowed. p_db_rule_name is depreciable basis
943 -- rule name.
944 --
945 -- NOTE: Bug:3724207
946 -- p_old_date_placed_in_service and p_asset_id are populated from
947 -- calc_fin_info(FAVCALB.pls) and not when this is called from otherplaces
948 --
949 -- Following cursor will fetch record if there is a transaction between addition and
950 -- new dpis. However following transaction will be excluded.
951 -- ADDITION, ADDITION/VOID, GROUP ADDITION/VOID, GROUP ADDITION, REINSTATEMENT,
952 -- TRANSFER IN, TRANSFER IN/VOID, and any retirement which has been reinstated
953 --
954 CURSOR c_chk_trx_before_dpis is
955 SELECT TH.TRANSACTION_HEADER_ID
956 FROM FA_TRANSACTION_HEADERS TH
957 WHERE TH.ASSET_ID = p_asset_id
958 AND TH.BOOK_TYPE_CODE = p_book_type_code
959 AND TH.TRANSACTION_TYPE_CODE IN ('ADJUSTMENT', 'GROUP ADJUSTMENT', 'REVALUATION', 'TAX')
960 AND NVL(TH.AMORTIZATION_START_DATE, TH.TRANSACTION_DATE_ENTERED)
961 < p_date_placed_in_service
962 UNION
963 SELECT TH.TRANSACTION_HEADER_ID
964 FROM FA_TRANSACTION_HEADERS TH,
965 FA_RETIREMENTS RET
966 WHERE TH.ASSET_ID = p_asset_id
967 AND TH.BOOK_TYPE_CODE = p_book_type_code
968 AND TH.TRANSACTION_TYPE_CODE IN
969 ('FULL RETIREMENT', 'PARTIAL RETIREMENT')
970 AND NVL(TH.AMORTIZATION_START_DATE, TH.TRANSACTION_DATE_ENTERED)
971 < p_date_placed_in_service
972 AND RET.ASSET_ID = TH.ASSET_ID
973 AND RET.BOOK_TYPE_CODE = TH.BOOK_TYPE_CODE
974 AND RET.TRANSACTION_HEADER_ID_IN = TH.TRANSACTION_HEADER_ID
975 AND RET.TRANSACTION_HEADER_ID_OUT IS NULL;
976
977 --bug# 5501090: following cursor and 1 varible
978 CURSOR c_check_member_exists is
979 select 'Y'
980 from fa_books
981 where group_asset_id = p_asset_id
982 and book_type_code = p_book_type_code
983 and transaction_header_id_out is null;
984
985 l_member_exists varchar(1) := NULL;
986
987 l_dpis_jdate number;
988 l_dpis_fy number;
989 l_dpis_per_num number;
990 l_start_jdate number;
991
992 l_earliest_dpis date;
993 l_count number;
994 l_period_rec FA_API_TYPES.period_rec_type;
995
996 l_prorate_date date;
997 l_check_prorate_date varchar2(1);
998 l_temp_num number;
999
1000 BEGIN
1001 -- Need to call the cache for book
1002 if (NOT fa_cache_pkg.fazcbc (
1003 X_book => p_book_type_code
1004 )) then
1005 fa_srvr_msg.add_message(
1006 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1007 name => 'FA_POST_INVALID_BOOK');
1008 return FALSE;
1009 end if;
1010
1011 /*
1012 -- bug# 5501090: DPIS change is not allowed for group asset
1013 -- if any member exists
1014 if (nvl(p_asset_type,'CAPITALIZED') = 'GROUP' and
1015 p_date_placed_in_service
1016 <> nvl(p_old_date_placed_in_service,p_date_placed_in_service)) then
1017 open c_check_member_exists;
1018 fetch c_check_member_exists into l_member_exists;
1019
1020 if (c_check_member_exists%NOTFOUND) then
1021 l_member_exists := 'N';
1022 end if;
1023
1024 close c_check_member_exists;
1025
1026 if (l_member_exists = 'Y') then
1027 fa_srvr_msg.add_message(
1028 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1029 name => 'FA_GROUP_ADJ_NOT_ALLOWED',
1030 token1 => 'ADJUSTMENT_TYPE',
1031 value1 => 'Date Placed In Service');
1032 return FALSE;
1033 end if;
1034 end if;
1035 --end bug# 5501090
1036 */
1037 -- Validate that dpis passes the LOW_RANGE criteria (fa_date.validate)
1038 if (p_date_placed_in_service < to_date('1000/01/01', 'YYYY/MM/DD')) then
1039 fa_srvr_msg.add_message(
1040 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1041 name => 'FA_YEAR_GREATER_THAN');
1042 return FALSE;
1043 end if;
1044
1045 -- Check that dpis is not too old.
1046 if not fa_cache_pkg.fazsys() then
1047 fa_srvr_msg.add_message(calling_fn => 'fa_asset_val_pvt.validate_dpis');
1048 return false;
1049 else
1053 if (p_date_placed_in_service < l_earliest_dpis) then
1050 l_earliest_dpis := fa_cache_pkg.fazsys_record.date_placed_in_service;
1051 end if;
1052
1054 fa_srvr_msg.add_message(
1055 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1056 name => 'FA_BOOK_DPIS_TOO_OLD');
1057 return FALSE;
1058 end if;
1059
1060 -- Not prior period transaction allowed for asset with
1061 -- Energy UOP
1062 if (p_db_rule_name = 'ENERGY PERIOD END BALANCE' and
1063 p_rate_source_rule = 'PRODUCTION') then
1064
1065 if not FA_UTIL_PVT.get_period_rec
1066 (p_book => p_book_type_code,
1067 p_effective_date => NULL,
1068 x_period_rec => l_period_rec,
1069 p_log_level_rec => p_log_level_rec) then
1070
1071 fa_srvr_msg.add_message(calling_fn => 'fa_asset_val_pvt.validate_dpis');
1072 return false;
1073 end if;
1074
1075 --Bug# 7198185 - Removed modified condition to allow backdated addition for energy
1076 if (p_date_placed_in_service <> nvl(p_old_date_placed_in_service, p_date_placed_in_service) ) then
1077
1078 if (p_log_level_rec.statement_level) then
1079 fa_debug_pkg.add('fa_asset_val_pvt.validate_dpis', 'Error', p_date_placed_in_service, p_log_level_rec);
1080 end if;
1081
1082 fa_srvr_msg.add_message(
1083 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1084 name => 'FA_CURRENT_DATE_ONLY');
1085 return FALSE;
1086 end if;
1087
1088 end if; -- (p_db_rule_name = 'ENERGY PERIOD END BALANCE' and
1089
1090 /* BUG# 875160 and 2144557 and 4146025
1091 * we allow future adds in masscp, and cip-in-tax
1092 * lifting this restriction from the apis. will need to
1093 * place this in form, etc if we wish to keep it there. --bmr
1094 */
1095
1096 -- Fix for Bug #2621438. Only validate this from FAXASSET
1097 if ((p_calling_fn in ('faxasset.fa_books_val2.dpis_val',
1098 'faxasset.fa_addition_books.date_placed_in_service')
1099 or (p_calling_interface = 'FAMAPT') )
1100 ) then
1101
1102 -- Check that dpis is not in a future period.
1103 if not FA_UTIL_PVT.get_period_rec
1104 (p_book => p_book_type_code,
1105 p_effective_date => NULL,
1106 x_period_rec => l_period_rec,
1107 p_log_level_rec => p_log_level_rec
1108 ) then
1109 fa_srvr_msg.add_message(calling_fn => 'fa_asset_val_pvt.validate_dpis');
1110 return false;
1111 end if;
1112
1113 if (p_date_placed_in_service > l_period_rec.calendar_period_close_date) then
1114 fa_srvr_msg.add_message(
1115 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1116 name => 'FA_BOOK_FUTURE_PERIOD_DPIS');
1117 return FALSE;
1118 end if;
1119
1120
1121 -- Check that prorate calendars and conventions are setup for this dpis.
1122 -- Will only be called from faxasset since it can be a performance
1123 -- issue in mass processes and is kind of redundant with validations
1124 -- that occur in the calculation engine.
1125 if (p_prorate_convention_code is not null) then
1126
1127 begin
1128 select prorate_date
1129 into l_prorate_date
1130 from fa_conventions
1131 where prorate_convention_code = p_prorate_convention_code
1132 and p_date_placed_in_service between start_date and end_date;
1133
1134 exception
1135 when others then
1136 fa_srvr_msg.add_message(
1137 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1138 name => 'FA_BOOK_CANT_GEN_PRORATE_DATE');
1139 return FALSE;
1140 end;
1141
1142 -- Check that prorate date is defined for given dpis.
1143 begin
1144 select 'x'
1145 into l_check_prorate_date
1146 from fa_calendar_periods cp,
1147 fa_book_controls bc
1148 where bc.book_type_code = p_book_type_code
1149 and bc.prorate_calendar = cp.calendar_type
1150 and l_prorate_date between cp.start_date and cp.end_date;
1151
1152 exception
1153 when others then
1154 fa_srvr_msg.add_message(
1155 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1156 name => 'FA_BKS_INVALID_PRORATE_DATE');
1157 return FALSE;
1158 end;
1159
1160 end if;
1161 end if;
1162
1163 -- Check to see if calendar periods are setup
1164 l_dpis_jdate := to_number(to_char(p_date_placed_in_service,'J'));
1165
1166 if (not fa_cache_pkg.fazccp (
1167 X_target_calendar => fa_cache_pkg.fazcbc_record.deprn_calendar,
1168 X_target_fy_name => fa_cache_pkg.fazcbc_record.fiscal_year_name,
1169 X_target_jdate => l_dpis_jdate,
1170 X_period_num => l_dpis_per_num,
1171 X_fiscal_year => l_dpis_fy,
1172 X_start_jdate => l_start_jdate,
1173 p_log_level_rec => p_log_level_rec
1177 name => 'FA_PROD_INCORRECT_DATE');
1174 )) then
1175 fa_srvr_msg.add_message(
1176 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1178 return FALSE;
1179 end if;
1180
1181 -- Bug:3724207
1182 -- We should not allow dpis change when there is a transaction between addition and
1183 -- new dpis. However following transaction will be excluded.
1184 -- ADDITION, ADDITION/VOID, GROUP ADDITION/VOID, GROUP ADDITION, REINSTATEMENT,
1185 -- TRANSFER IN, TRANSFER IN/VOID, and any retirement which has been reinstated
1186 -- Bug 4246638: Adding dist-related trxs to the exclusion list
1187 --
1188 if p_transaction_subtype <> 'EXPENSED' and (p_date_placed_in_service <>
1189 nvl(p_old_date_placed_in_service, p_date_placed_in_service)) then
1190 OPEN c_chk_trx_before_dpis;
1191 FETCH c_chk_trx_before_dpis INTO l_temp_num;
1192
1193 if (c_chk_trx_before_dpis%FOUND) then
1194 CLOSE c_chk_trx_before_dpis;
1195
1196 -- Use message FA_AMORT_DATE_INVALID until new message
1197 -- FA_INVALID_DPIS is available
1198 fa_srvr_msg.add_message(
1199 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1200 -- name => 'FA_INVALID_DPIS');
1201 name => 'FA_AMORT_DATE_INVALID');
1202 return FALSE;
1203 end if;
1204
1205 CLOSE c_chk_trx_before_dpis;
1206
1207 end if; -- (p_date_placed_in_service <>
1208
1209 return TRUE;
1210
1211 END validate_dpis;
1212
1213 FUNCTION validate_rec_cost_reserve
1214 (p_transaction_type_code IN VARCHAR2,
1215 p_recoverable_cost IN NUMBER,
1216 p_deprn_reserve IN NUMBER,
1217 p_calling_fn IN VARCHAR2,
1218 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1219 ) RETURN BOOLEAN IS
1220
1221 BEGIN
1222
1223 if (p_deprn_reserve <> 0 and
1224 (abs(p_recoverable_cost) < abs(p_deprn_reserve) or
1225 (sign(p_recoverable_cost) <> 0 and
1226 sign(p_recoverable_cost) = -sign(p_deprn_reserve)))) then
1227 fa_srvr_msg.add_message(
1228 calling_fn => 'validate_rec_cost_reserve',
1229 name => 'FA_BOOK_INVALID_RESERVE');
1230 return FALSE;
1231 end if;
1232
1233 return TRUE;
1234
1235 END validate_rec_cost_reserve;
1236
1237 FUNCTION validate_adj_rec_cost
1238 (p_adjusted_recoverable_cost IN NUMBER,
1239 p_deprn_reserve IN NUMBER,
1240 p_calling_fn IN VARCHAR2,
1241 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1242 ) RETURN BOOLEAN IS
1243
1244 BEGIN
1245
1246 if (abs(p_adjusted_recoverable_cost) < abs(p_deprn_reserve) or
1247 (sign(p_adjusted_recoverable_cost) <> 0 and
1248 sign(p_adjusted_recoverable_cost) = -sign(p_deprn_reserve))) then
1249 fa_srvr_msg.add_message(
1250 calling_fn => 'fa_asset_val_pvt.val_adj_rec_cost',
1251 name => 'FA_BOOK_INVALID_RESERVE');
1252 return FALSE;
1253 end if;
1254
1255 return TRUE;
1256
1257 END validate_adj_rec_cost;
1258
1259 FUNCTION validate_ytd_reserve
1260 (p_book IN VARCHAR2,
1261 p_date_placed_in_service IN DATE,
1262 p_asset_type IN VARCHAR2,
1263 p_ytd_deprn IN NUMBER,
1264 p_deprn_reserve IN NUMBER,
1265 p_bonus_ytd_deprn IN NUMBER,
1266 p_bonus_deprn_reserve IN NUMBER,
1267 p_reval_reserve IN NUMBER,
1268 p_ytd_reval_deprn_expense IN NUMBER,
1269 p_reval_amortization_basis IN NUMBER,
1270 p_fully_rsvd_revals_counter IN NUMBER,
1271 p_period_rec IN FA_API_TYPES.period_rec_type,
1272 p_calling_fn IN VARCHAR2,
1273 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1274 ) return boolean IS
1275
1276 l_current_fiscal_year FA_BOOK_CONTROLS.current_fiscal_year%TYPE;
1277 l_fiscal_year_name FA_BOOK_CONTROLS.fiscal_year_name%TYPE;
1278
1279 l_same_fiscal_year NUMBER;
1280 l_abs_deprn_reserve NUMBER;
1281 l_abs_ytd_deprn NUMBER;
1282
1283 BEGIN
1284
1285 -- no need to load book controls cache as it's loaded
1286 l_current_fiscal_year := fa_cache_pkg.fazcbc_record.current_fiscal_year;
1287 l_fiscal_year_name := fa_cache_pkg.fazcbc_record.fiscal_year_name;
1288
1289 -- Get absolute values.
1290 l_abs_deprn_reserve := abs (nvl(p_deprn_reserve, 0));
1291 l_abs_ytd_deprn := abs (nvl(p_ytd_deprn, 0));
1292
1293 -- no reserve for non capitalized assets
1294 if ((p_asset_type <> 'CAPITALIZED') and
1295 (nvl(p_deprn_reserve, 0) <> 0 or
1296 nvl(p_ytd_deprn, 0) <> 0 or
1297 nvl(p_bonus_ytd_deprn, 0) <> 0 or
1298 nvl(p_bonus_deprn_reserve, 0) <> 0 or
1299 nvl(p_reval_reserve, 0) <> 0 or
1300 nvl(p_ytd_reval_deprn_expense, 0) <> 0 or
1301 nvl(p_reval_amortization_basis, 0) <> 0 or
1302 nvl(p_fully_rsvd_revals_counter, 0) <> 0)) then
1303 fa_srvr_msg.add_message(
1304 calling_fn => 'fa_asset_val_pvt.validate_ytd_reserve',
1308
1305 name => 'FA_BOOK_INVALID_RESERVE');
1306 return FALSE;
1307 end if;
1309
1310 -- ytd, ltd deprn validations from fa_books_trx3.when_validate_record
1311 -- in FAXASSET.
1312
1313 /* Fix for Bug #2429665. Should not have this validation.
1314 -- verify no reserve for asset in first period of life
1315 if (p_date_placed_in_service >= p_period_rec.calendar_period_open_date and
1316 (p_deprn_reserve <> 0 or
1317 p_ytd_deprn <> 0)) then
1318 fa_srvr_msg.add_message(
1319 calling_fn => 'fa_asset_val_pvt.validate_ytd_reserve',
1320 name => 'FA_NO_RSV_IN_FIRST_PERIOD');
1321 return false;
1322 end if;
1323 */
1324
1325 -- in first year of life ytd must equal reserve
1326 if (p_date_placed_in_service >= p_period_rec.fy_start_date and
1327 p_date_placed_in_service <= p_period_rec.fy_end_date) then
1328 if (p_ytd_deprn <> p_deprn_reserve) then
1329 fa_srvr_msg.add_message(
1330 calling_fn => 'fa_asset_val_pvt.validate_ytd_reserve',
1331 name => 'FA_BOOK_RSV_EQL_YTD');
1332 return FALSE;
1333 end if;
1334 else
1335 -- BUG# 2341201
1336 -- need to consider the signs as well since due to historical
1337 -- data it is posible to have a negative ytd larger than the
1338 -- positive reserve - BMR
1339
1340 if (((sign(p_ytd_deprn) = sign(p_deprn_reserve)) or
1341 (sign(p_ytd_deprn) = 0) or
1342 (sign(p_deprn_reserve) = 0)) and
1343 (l_abs_ytd_deprn > l_abs_deprn_reserve)) then
1344 fa_srvr_msg.add_message(
1345 calling_fn => 'fa_asset_val_pvt.validate_ytd_reserve',
1346 name => 'FA_BOOK_YTD_EXCEED_RSV');
1347 return FALSE;
1348 end if;
1349 end if;
1350
1351 return TRUE;
1352
1353 EXCEPTION
1354 when others then
1355 fa_srvr_msg.add_sql_error(
1356 calling_fn => 'fa_asset_val_pvt.validate_ytd_reserve');
1357 return false;
1358
1359 END validate_ytd_reserve;
1360
1361 FUNCTION validate_short_tax_year
1362 (p_book_type_code IN VARCHAR2,
1363 p_transaction_type_code IN VARCHAR2,
1364 p_asset_type IN VARCHAR2,
1365 p_short_fiscal_year_flag IN VARCHAR2,
1366 p_conversion_date IN DATE,
1367 px_orig_deprn_start_date IN OUT NOCOPY DATE,
1368 p_date_placed_in_service IN DATE,
1369 p_ytd_deprn IN NUMBER,
1370 p_deprn_reserve IN NUMBER,
1371 p_period_rec IN FA_API_TYPES.period_rec_type,
1372 p_calling_fn IN VARCHAR2,
1373 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1374 ) RETURN BOOLEAN IS
1375
1376 l_abs_ytd_deprn number;
1377 l_abs_deprn_reserve number;
1378
1379 BEGIN
1380
1381 -- The short_fiscal_year_flag should be YES or NO.
1382 if not ((p_short_fiscal_year_flag = 'YES') OR
1383 (p_short_fiscal_year_flag = 'NO')) then
1384 fa_srvr_msg.add_message(
1385 calling_fn => 'fa_asset_val_pvt.validate_short_tax_year',
1386 name => 'FA_INCORRECT_SHORT_FY_FLAG',
1387 token1 => 'SHORT_FY_FLAG',
1388 value1 => p_short_fiscal_year_flag);
1389 return FALSE;
1390 end if;
1391
1392 -- The short_fiscal_year_flag cannot be YES is the asset is not CAPITALIZED.
1393 if ((p_asset_type <> 'CAPITALIZED') AND
1394 (p_short_fiscal_year_flag = 'YES')) then
1395 fa_srvr_msg.add_message(
1396 calling_fn => 'fa_asset_val_pvt.validate_short_tax_year',
1397 name => 'FA_CANT_SET_SHORT_FY_FLAG');
1398 return FALSE;
1399 end if;
1400
1401 -- The conversion date cannot be null if the short_fiscal_year_flag is YES.
1402 if ((p_short_fiscal_year_flag = 'YES') AND
1403 (p_conversion_date is NULL)) then
1404 fa_srvr_msg.add_message(
1405 calling_fn => 'fa_asset_val_pvt.validate_short_tax_year',
1406 name => 'FA_MUST_SET_CONV_DATE');
1407 return FALSE;
1408 end if;
1409
1410 -- May default orig_deprn_start_date if short_fiscal_year_flag is YES.
1411 if ((p_short_fiscal_year_flag = 'YES') AND
1412 (px_orig_deprn_start_date is NULL)) then
1413 px_orig_deprn_start_date := p_date_placed_in_service;
1414 end if;
1415
1416 -- For creating new asset (non-add_to_asset) transactions, conversion
1417 -- date cannot have a value if short_fiscal_year_flag is not YES.
1418 if (((p_transaction_type_code <> 'ADDITION') OR
1419 (p_transaction_type_code <> 'CIP ADDITION')) AND
1420 (p_short_fiscal_year_flag <> 'YES') AND
1421 (p_conversion_date is not NULL)) then
1422 fa_srvr_msg.add_message(
1423 calling_fn => 'fa_asset_val_pvt.validate_short_tax_year',
1424 name => 'FA_CONV_DATE_NO_VAL',
1425 token1 => 'CONV_DATE',
1426 value1 => p_conversion_date);
1427 return FALSE;
1428 end if;
1429
1433 (p_transaction_type_code <> 'CIP ADDITION')) AND
1430 -- For creating new asset (non-add_to_asset) transactions, orig deprn start
1431 -- date cannot have a value if short_fiscal_year_flag is not YES.
1432 if (((p_transaction_type_code <> 'ADDITION') OR
1434 (p_short_fiscal_year_flag <> 'YES') AND
1435 (px_orig_deprn_start_date is not NULL)) then
1436 fa_srvr_msg.add_message(
1437 calling_fn => 'fa_asset_val_pvt.validate_short_tax_year',
1438 name => 'FA_CONV_DATE_NO_VAL',
1439 token1 => 'CONV_DATE',
1440 value1 => px_orig_deprn_start_date);
1441 return FALSE;
1442 end if;
1443
1444 -- Conversion date must fall in the current open period which also means
1445 -- reserve up until conversion must be provided.
1446 if (p_conversion_date is not null) then
1447 if (p_conversion_date < p_period_rec.calendar_period_open_date or
1448 p_conversion_date > p_period_rec.calendar_period_close_date) then
1449 fa_srvr_msg.add_message(
1450 calling_fn => 'fa_asset_val_pvt.validate_short_tax_year',
1451 name => 'FA_INCORRECT_CONV_DATE',
1452 token1 => 'CONV_DATE',
1453 value1 => p_conversion_date);
1454 return FALSE;
1455 end if;
1456
1457 -- Validate conversion_date <> current fiscal year end date.
1458 if (p_conversion_date = p_period_rec.fy_end_date) then
1459 fa_srvr_msg.add_message(
1460 calling_fn => 'fa_asset_val_pvt.validate_short_tax_year',
1461 name => 'FA_CONV_DATE_EQU_CURR_FYEND');
1462 return FALSE;
1463 end if;
1464 end if;
1465
1466 return TRUE;
1467
1468 END validate_short_tax_year;
1469
1470 FUNCTION validate_trx_date_entered
1471 (p_transaction_type_code IN VARCHAR2,
1472 p_book_type_code IN VARCHAR2,
1473 p_transaction_date_entered IN DATE,
1474 p_period_rec IN FA_API_TYPES.period_rec_type,
1475 p_calling_fn IN VARCHAR2,
1476 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1477 ) RETURN BOOLEAN IS
1478
1479 begin
1480
1481 if (p_transaction_date_entered > p_period_rec.calendar_period_close_date
1482 ) then
1483 fa_srvr_msg.add_message(
1484 calling_fn => 'fa_asset_val_pvt.validate_trx_date_entered',
1485 name => 'FA_SHARED_CANNOT_FUTURE');
1486 return FALSE;
1487 end if;
1488
1489 return TRUE;
1490
1491 end validate_trx_date_entered;
1492
1493 FUNCTION validate_amort_start_date
1494 (p_transaction_type_code IN VARCHAR2,
1495 p_asset_id IN NUMBER,
1496 p_book_type_code IN VARCHAR2,
1497 p_date_placed_in_service IN DATE DEFAULT NULL,
1498 p_conversion_date IN DATE DEFAULT NULL,
1499 p_period_rec IN FA_API_TYPES.period_rec_type,
1500 p_amortization_start_date IN DATE,
1501 p_db_rule_name IN VARCHAR2 DEFAULT NULL, -- ENERGY
1502 p_rate_source_rule IN VARCHAR2 DEFAULT NULL, -- ENERGY
1503 p_transaction_key IN VARCHAR2 DEFAULT 'XX',
1504 x_amortization_start_date OUT NOCOPY DATE,
1505 x_trxs_exist OUT NOCOPY VARCHAR2,
1506 p_calling_fn IN VARCHAR2,
1507 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1508 ) RETURN BOOLEAN IS
1509
1510 l_transaction_date date;
1511 l_period_close_date date;
1512 l_period_open_date date;
1513 l_prior_transaction_date date;
1514 l_prior_date_effective date;
1515 l_amort_date date;
1516 l_avail_date date;
1517 l_count number;
1518 l_dpis_jdate number;
1519 l_amort_jdate number;
1520 l_dpis_fy number;
1521 l_amort_fy number;
1522 l_dpis_per_num number;
1523 l_amort_per_num number;
1524 l_fy_name varchar2(45);
1525 l_cal_type varchar2(15);
1526 l_start_jdate number;
1527 l_period_rec FA_API_TYPES.period_rec_type;
1528 l_calling_fn varchar2(40) := 'fa_asset_val_pvt.val_amort_date';
1529 error_found exception;
1530
1531 begin
1532
1533 x_amortization_start_date := p_amortization_start_date;
1534
1535 if (p_amortization_start_date is not null) then
1536
1537 -- sets to Y if any txn exist between current period
1538 -- and amortization period
1539 x_trxs_exist := 'N';
1540
1541 -- x_amortization_start_date cannot be future period
1542 l_transaction_date := greatest(p_period_rec.calendar_period_open_date,
1543 least(sysdate,
1544 p_period_rec.calendar_period_close_date));
1545 l_period_close_date := p_period_rec.calendar_period_close_date;
1546 l_period_open_date := p_period_rec.calendar_period_open_date;
1547
1548 if (x_amortization_start_date > l_period_close_date) then
1549 fa_srvr_msg.add_message(
1553 end if;
1550 calling_fn => l_calling_fn,
1551 name => 'FA_SHARED_CANNOT_FUTURE');
1552 return FALSE;
1554
1555 -- x_amortization_start_date cannot be less than DPIS
1556 if (p_amortization_start_date < p_date_placed_in_service) then
1557 x_amortization_start_date := p_date_placed_in_service;
1558 x_trxs_exist := 'Y';
1559 end if;
1560
1561 -- get book controls info from cache
1562 -- assumes cache has been called
1563 l_fy_name := fa_cache_pkg.fazcbc_record.fiscal_year_name;
1564 l_cal_type := fa_cache_pkg.fazcbc_record.deprn_calendar;
1565
1566 -- checks if amort start date is valid
1567 l_amort_jdate := to_number(to_char(x_amortization_start_date,'J'));
1568 if (not fa_cache_pkg.fazccp
1569 (l_cal_type,
1570 l_fy_name,
1571 l_amort_jdate,
1572 l_amort_per_num,
1573 l_amort_fy,
1574 l_start_jdate)) then
1575 fa_srvr_msg.add_message(
1576 calling_fn => l_calling_fn,
1577 name => 'FA_PROD_INCORRECT_DATE');
1578 return FALSE;
1579 end if;
1580
1581 /****
1582 ** Bug3218011
1583 ** output parameter x_trxs_exist is not used so comment out follwoing
1584 ** 2 sql
1585 **
1586 -- removed section comparing fys as it was commented out
1587 -- check if amort start date is eariler than
1588 -- previous txn date, set txns_exist
1589 select MAX(transaction_date_entered),
1590 MAX(date_effective)
1591 into l_prior_transaction_date,
1592 l_prior_date_effective
1593 from fa_transaction_headers
1594 where asset_id = p_asset_id
1595 and book_type_code = p_book_type_code;
1596
1597 if (x_amortization_start_date < l_prior_transaction_date) then
1598 x_trxs_exist := 'Y';
1599 end if;
1600
1601 select count(*)
1602 into l_count
1603 from fa_deprn_periods pdp,
1604 fa_deprn_periods adp
1605 where pdp.book_type_code = p_book_type_code
1606 and pdp.book_type_code = adp.book_type_code
1607 and pdp.period_counter > adp.period_counter
1608 and l_prior_date_effective between pdp.period_open_date
1609 and nvl(pdp.period_close_date, to_date('31-12-4712','DD-MM-YYYY'))
1610 and x_amortization_start_date between
1611 adp.calendar_period_open_date and adp.calendar_period_close_date;
1612
1613 if (l_count > 0) then
1614 x_trxs_exist := 'Y';
1615 end if;
1616
1617 **
1618 ** End of Bug3218011
1619 ****/
1620
1621 -- Not prior period transaction allowed for asset with
1622 -- Energy UOP
1623 if (p_db_rule_name = 'ENERGY PERIOD END BALANCE' and
1624 p_rate_source_rule = 'PRODUCTION' and
1625 p_transaction_key <> 'MS') then
1626
1627 if (p_amortization_start_date < p_period_rec.calendar_period_open_date) then
1628 if (p_log_level_rec.statement_level) then
1629 fa_debug_pkg.add(l_calling_fn, 'Error', p_date_placed_in_service);
1630 end if;
1631 fa_srvr_msg.add_message(
1632 calling_fn => 'fa_asset_val_pvt.validate_dpis',
1633 name => 'FA_CURRENT_DATE_ONLY');
1634 return FALSE;
1635 end if;
1636
1637 end if; -- (p_db_rule_name = 'ENERGY PERIOD END BALANCE' and
1638
1639 -- check to see if any retire/reinstate/reval txn is in between
1640 -- x_new_amort_start_date and current_period.
1641 -- this check covers for the prior period retire/reinste/reval
1642 -- set x_new_amort_start_date := latest txn date
1643
1644 -- bug 3188779
1645 -- do not redefault at this point, but error and force
1646 -- user to pick a new date. also no need to prevent
1647 -- overlaps to a retirement either except in the case of
1648 -- group reclass which is done seperately in FAVCALB.pls.
1649 --
1650 -- changing logic to compare max(trx_date) to the amort date here too.
1651
1652 select MAX(transaction_date_entered) -- date_effective
1653 into l_prior_transaction_date -- l_prior_date_effective
1654 from fa_transaction_headers
1655 where asset_id = p_asset_id
1656 and book_type_code = p_book_type_code
1657 and transaction_type_code in
1658 ('REVALUATION');
1659 --('PARTIAL RETIREMENT','REINSTATEMENT','REVALUATION');
1660
1661
1662 if (x_amortization_start_date < l_prior_transaction_date) then
1663 fa_srvr_msg.add_message(
1664 calling_fn => l_calling_fn,
1665 name => 'FA_SHARED_OTHER_TRX_FOLLOW');
1666 raise error_found;
1667 end if;
1668
1669 /*
1670 if (l_prior_date_effective is not null) then
1671
1672 -- get the latest available date
1673 -- use get_period rec and period cache
1674
1675 if not FA_UTIL_PVT.get_period_rec
1676 (p_book => p_book_type_code,
1677 p_effective_date => l_prior_date_effective,
1681 end if;
1678 x_period_rec => l_period_rec,
1679 p_log_level_rec => p_log_level_rec) then
1680 raise error_found;
1682
1683 l_amort_date := greatest(l_period_rec.calendar_period_open_date,
1684 least(SYSDATE,
1685 l_period_rec.calendar_period_close_date));
1686
1687 if (x_amortization_start_date < l_amort_date) then
1688 x_amortization_start_date := l_amort_date;
1689 end if;
1690 end if;
1691 */
1692
1693 -- NOTE: code for validating amort date to conversion date has been removed
1694
1695 end if; -- amort date not null
1696
1697 return TRUE;
1698
1699 exception
1700 when error_found then
1701 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
1702 return FALSE;
1703
1704 when others then
1705 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
1706 return false;
1707
1708 end validate_amort_start_date;
1709
1710
1711
1712 FUNCTION validate_life
1713 (p_deprn_method IN VARCHAR2,
1714 p_rate_source_rule IN VARCHAR2,
1715 p_life_in_months IN NUMBER,
1716 p_lim IN NUMBER,
1717 p_user_id IN NUMBER,
1718 p_curr_date IN DATE,
1719 px_new_life IN OUT NOCOPY NUMBER,
1720 p_calling_fn IN VARCHAR2,
1721 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1722 ) RETURN BOOLEAN IS
1723
1724 l_method_id number;
1725 l_rowid rowid;
1726
1727 l_method_id_old number;
1728 l_method_name fa_methods.name%type;
1729 l_deprn_basis_rule varchar2(4);
1730 l_stl_method_flag varchar2(3);
1731 l_dep_last_year_flag varchar2(3);
1732 l_exclude_sal_flag varchar2(3);
1733
1734 l_formula_actual varchar2(4000);
1735 l_formula_displayed varchar2(4000);
1736 l_formula_parsed varchar2(4000);
1737
1738 -- note due to formula changes, we are joining
1739 -- to the life of the category. Since we would not
1740 -- be entering this function is the method and life
1741 -- existed, the current method in cache will be that
1742 -- of the category. Thus we'll use that life in order
1743 -- to determine the correct formula to pull for new method.
1744
1745 CURSOR METHOD_DEF (p_deprn_method varchar2,
1746 p_life_in_months number) IS
1747 SELECT DISTINCT
1748 method_id,
1749 name,
1750 deprn_basis_rule,
1751 depreciate_lastyear_flag,
1752 stl_method_flag,
1753 exclude_salvage_value_flag
1754 FROM FA_METHODS
1755 WHERE METHOD_CODE = p_deprn_method
1756 AND LIFE_IN_MONTHS = p_life_in_months;
1757
1758 CURSOR C_FORMULA (p_method_id number) IS
1759 SELECT formula_actual,
1760 formula_displayed,
1761 formula_parsed
1762 FROM FA_FORMULAS
1763 WHERE method_id = p_method_id;
1764
1765 l_calling_fn varchar2(35) := 'fa_asset_val_pvt.validate_life';
1766 error_found exception;
1767
1768 BEGIN
1769
1770 if (p_log_level_rec.statement_level) then
1771 fa_debug_pkg.add(l_calling_fn, 'calling', 'fazccmt');
1772 end if;
1773
1774 if not fa_cache_pkg.fazccmt
1775 (X_method => p_deprn_method,
1776 X_life => p_lim,
1777 p_log_level_rec => p_log_level_rec) then -- method not found
1778
1779 if (p_log_level_rec.statement_level) then
1780 fa_debug_pkg.add(l_calling_fn, 'after fazccmt', 'method not found');
1781 end if;
1782
1783 if (p_rate_source_rule = 'TABLE') then
1784
1785 if (p_life_in_months <> 0) then
1786 px_new_life := p_life_in_months;
1787 else
1788 fa_srvr_msg.add_message(
1789 CALLING_FN => 'fa_asset_val_pvt.validate_life',
1790 NAME => 'FA_LIM_TDM_NOTDEF');
1791 raise error_found;
1792 end if;
1793
1794 else -- not table
1795
1796 select FA_METHODS_S.NEXTVAL
1797 into l_method_id
1798 from sys.dual;
1799
1800 -- need to derive more values to distinguish between
1801 -- STL and Formula methods. Can't use cache as life
1802 -- is unknown so like the function in calc engine,
1803 -- we'll use cursor here, other option would be to
1804 -- pass the values as parameter into this function
1805 -- creating dependancies...
1806
1807 OPEN METHOD_DEF(p_deprn_method => p_deprn_method,
1808 p_life_in_months => fa_cache_pkg.fazccbd_record.life_in_months);
1809 FETCH METHOD_DEF
1810 INTO l_method_id_old,
1811 l_method_name,
1812 l_deprn_basis_rule,
1813 l_dep_last_year_flag,
1814 l_stl_method_flag,
1815 l_exclude_sal_flag;
1816
1820 CALLING_FN => l_calling_fn,
1817 if (METHOD_DEF%NOTFOUND) then
1818 CLOSE METHOD_DEF;
1819 fa_srvr_msg.add_message(
1821 NAME => 'FA_SHARED_OBJECT_NOT_DEF',
1822 TOKEN1 => 'OBJECT',
1823 VALUE1 => 'Method');
1824 raise error_found;
1825 else
1826 CLOSE METHOD_DEF;
1827 end if;
1828
1829 if (p_log_level_rec.statement_level) then
1830 fa_debug_pkg.add(l_calling_fn, 'inserting', 'new method');
1831 end if;
1832
1833 FA_METHODS_PKG.Insert_Row(
1834 X_Rowid => l_rowid,
1835 X_Method_Id => l_method_id,
1836 X_Method_Code => p_deprn_method,
1837 X_Life_In_Months => p_lim,
1838 X_Depreciate_Lastyear_Flag => l_dep_last_year_flag, -- 'YES',
1839 X_STL_Method_Flag => l_stl_method_flag, -- 'YES'
1840 X_Rate_Source_Rule => p_rate_source_rule, -- 'CALCULATED',
1841 X_Deprn_Basis_Rule => l_deprn_basis_rule, -- 'COST',
1842 X_Prorate_Periods_Per_Year => NULL,
1843 X_Name => l_method_name,
1844 X_Last_Update_Date => p_curr_date,
1845 X_Last_Updated_By => p_user_id,
1846 X_Created_By => p_user_id,
1847 X_Creation_Date => p_curr_date,
1848 X_Last_Update_Login => -1,
1849 X_Attribute1 => null,
1850 X_Attribute2 => null,
1851 X_Attribute3 => null,
1852 X_Attribute4 => null,
1853 X_Attribute5 => null,
1854 X_Attribute6 => null,
1855 X_Attribute7 => null,
1856 X_Attribute8 => null,
1857 X_Attribute9 => null,
1858 X_Attribute10 => null,
1859 X_Attribute11 => null,
1860 X_Attribute12 => null,
1861 X_Attribute13 => null,
1862 X_Attribute14 => null,
1863 X_Attribute15 => null,
1864 X_Attribute_Category_Code => null,
1865 X_Exclude_Salvage_Value_Flag => l_exclude_sal_flag,
1866 X_Calling_Fn => 'fa_asset_val_pvt.validate_life');
1867
1868 -- if formula based, we need to copy the formula too
1869 if (p_rate_source_rule = 'FORMULA') then
1870 OPEN C_FORMULA (p_method_id => l_method_id_old);
1871 FETCH C_FORMULA
1872 INTO l_formula_actual,
1873 l_formula_displayed,
1874 l_formula_parsed;
1875
1876 IF C_FORMULA%NOTFOUND then
1877 CLOSE C_FORMULA;
1878 fa_srvr_msg.add_message(
1879 CALLING_FN => 'fa_asset_val_pvt.validate_life',
1880 NAME => 'FA_FORMULA_RATE_NO_DATA_FOUND');
1881 raise error_found;
1882 else
1883 CLOSE C_FORMULA;
1884 end if;
1885
1886 FA_FORMULAS_PKG.insert_row
1887 (X_ROWID => l_rowid,
1888 X_METHOD_ID => l_method_id,
1889 X_FORMULA_ACTUAL => l_formula_actual,
1890 X_FORMULA_DISPLAYED => l_formula_displayed,
1891 X_FORMULA_PARSED => l_formula_parsed,
1892 X_CREATION_DATE => p_curr_date,
1893 X_CREATED_BY => p_user_id,
1894 X_LAST_UPDATE_DATE => p_curr_date,
1895 X_LAST_UPDATED_BY => p_user_id,
1896 X_LAST_UPDATE_LOGIN => -1);
1897
1898 end if;
1899
1900 end if; -- table based
1901
1902 -- default the new life in months to the remaining life in months of
1903 -- parent.
1904 if (p_lim <> 0) then
1905 px_new_life := p_lim;
1906 end if;
1907
1908 else
1909 if (p_log_level_rec.statement_level) then
1910 fa_debug_pkg.add(l_calling_fn, 'after fazccmt', 'method found');
1911 end if;
1912 end if;
1913
1914 return true;
1915
1916 EXCEPTION
1917 when error_found then
1918 FA_SRVR_MSG.Add_Message(
1919 CALLING_FN => 'fa_asset_val_pvt.validate_life');
1920 return false;
1921
1922 when others then
1923 FA_SRVR_MSG.Add_SQL_Error(
1924 CALLING_FN => 'fa_asset_val_pvt.validate_life');
1925 return false;
1926
1927 END validate_life;
1928
1929
1930
1931 FUNCTION validate_payables_ccid
1932 (px_payables_ccid IN OUT NOCOPY NUMBER,
1933 p_gl_chart_id IN NUMBER,
1934 p_calling_fn IN VARCHAR2,
1935 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1936 ) RETURN BOOLEAN IS
1937
1938 l_is_valid_payables_ccid number;
1939
1940 BEGIN
1941
1942 if (px_payables_ccid is not NULL) then
1943
1944 -- Validate payables ccid exists.
1945 select count(*)
1949 and chart_of_accounts_id = p_gl_chart_id
1946 into l_is_valid_payables_ccid
1947 from gl_code_combinations
1948 where code_combination_id = px_payables_ccid
1950 and enabled_flag = 'Y'
1951 and summary_flag = 'N'
1952 and detail_posting_allowed_flag = 'Y';
1953
1954 if (l_is_valid_payables_ccid = 0) then
1955 fa_srvr_msg.add_message(
1956 calling_fn => 'fa_asset_val_pvt.validate_payables_ccid',
1957 name => 'FA_INCORRECT_PAYABLES_ID');
1958 return FALSE;
1959 end if;
1960 else
1961 -- Bug 885429 Payables CCID cannot be NULL, so set it to ZERO.
1962 -- Will generate the ccid based on default category if ccid is ZERO.
1963 fa_srvr_msg.add_message(
1964 calling_fn => 'fa_asset_val_pvt.validate_payables_ccid',
1965 name => 'FA_NULL_PAYABLES_CCID');
1966 px_payables_ccid := 0;
1967
1968 return FALSE;
1969 end if;
1970
1971 return TRUE;
1972
1973 END validate_payables_ccid;
1974
1975 FUNCTION validate_expense_ccid
1976 (p_expense_ccid IN NUMBER,
1977 p_gl_chart_id IN NUMBER,
1978 p_calling_fn IN VARCHAR2,
1979 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
1980 ) RETURN BOOLEAN IS
1981
1982 l_is_valid_expense_ccid number;
1983
1984 BEGIN
1985
1986 -- Expense ccid cannot be null.
1987 if (p_expense_ccid is null) then
1988 fa_srvr_msg.add_message(
1989 calling_fn => 'fa_asset_val_pvt.validate_expense_ccid',
1990 name => 'FA_NULL_EXPENSE_CCID');
1991 return FALSE;
1992 end if;
1993
1994 -- Check that expense ccid exists.
1995 select count(*)
1996 into l_is_valid_expense_ccid
1997 from gl_code_combinations
1998 where code_combination_id = p_expense_ccid
1999 and chart_of_accounts_id = p_gl_chart_id
2000 and enabled_flag = 'Y'
2001 and account_type = 'E'
2002 and summary_flag = 'N'
2003 and detail_posting_allowed_flag = 'Y';
2004
2005 if (l_is_valid_expense_ccid = 0) then
2006 fa_srvr_msg.add_message(
2007 calling_fn => 'fa_asset_val_pvt.validate_expense_ccid',
2008 name => 'FA_INCORRECT_EXPENSE_ID',
2009 token1 => 'EXPENSE_ID',
2010 value1 => p_expense_ccid);
2011 return FALSE;
2012 end if;
2013
2014 return TRUE;
2015
2016 END validate_expense_ccid;
2017
2018 FUNCTION validate_fixed_assets_cost
2019 (p_fixed_assets_cost IN NUMBER,
2020 p_calling_fn IN VARCHAR2,
2021 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2022 ) RETURN BOOLEAN IS
2023
2024 BEGIN
2025
2026 -- Fixed assets cost cannot be null.
2027 if (p_fixed_assets_cost is null) then
2028 fa_srvr_msg.add_message(
2029 calling_fn => 'fa_asset_val_pvt.validate_fixed_assets_cost',
2030 name => 'FA_NULL_FA_COST');
2031 return FALSE;
2032 end if;
2033
2034 return TRUE;
2035
2036 END validate_fixed_assets_cost;
2037
2038 FUNCTION validate_fixed_assets_units
2039 (p_fixed_assets_units IN NUMBER,
2040 p_calling_fn IN VARCHAR2,
2041 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2042 ) RETURN BOOLEAN IS
2043
2044 BEGIN
2045
2046 -- Fixed assets units cannot be null.
2047 if (p_fixed_assets_units is null) then
2048 fa_srvr_msg.add_message(
2049 calling_fn => 'fa_asset_val_pvt.validate_fixed_assets_units',
2050 name => 'FA_NULL_FA_UNITS');
2051 return FALSE;
2052 else
2053
2054 -- Fixed assets units cannot be zero.
2055 if (p_fixed_assets_units = 0) then
2056 fa_srvr_msg.add_message(
2057 calling_fn => 'fa_asset_val_pvt.validate_fixed_assets_units',
2058 name => 'FA_ZERO_FA_UNITS');
2059 return FALSE;
2060 end if;
2061 end if;
2062
2063 return TRUE;
2064
2065 END validate_fixed_assets_units;
2066
2067 FUNCTION validate_payables_cost
2068 (p_payables_cost IN NUMBER,
2069 p_calling_fn IN VARCHAR2,
2070 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2071 ) RETURN BOOLEAN IS
2072
2073 BEGIN
2074
2075 -- Payables cost cannot be null.
2076 if (p_payables_cost is null) then
2077 fa_srvr_msg.add_message(
2078 calling_fn => 'fa_asset_val_pvt.validate_payables_cost',
2079 name => 'FA_NULL_PA_COST');
2080 return FALSE;
2081 end if;
2082
2083 return TRUE;
2084
2085 END validate_payables_cost;
2086
2087 FUNCTION validate_payables_units
2088 (p_payables_units IN NUMBER,
2089 p_calling_fn IN VARCHAR2,
2090 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2091 ) RETURN BOOLEAN IS
2092
2093 BEGIN
2094
2095 -- Payables units cannot be null.
2096 if (p_payables_units is null) then
2097 fa_srvr_msg.add_message(
2101 end if;
2098 calling_fn => 'fa_asset_val_pvt.validate_payables_units',
2099 name => 'FA_NULL_PA_UNITS');
2100 return FALSE;
2102
2103 return TRUE;
2104
2105 END validate_payables_units;
2106
2107 FUNCTION validate_po_vendor_id
2108 (p_po_vendor_id IN NUMBER,
2109 p_calling_fn IN VARCHAR2,
2110 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2111 ) RETURN BOOLEAN IS
2112
2113 l_is_valid_vendor_id number;
2114
2115 BEGIN
2116
2117 if (p_po_vendor_id is not null) then
2118
2119 -- Validate po_vendor_id exists.
2120 select count(*)
2121 into l_is_valid_vendor_id
2122 from po_vendors
2123 where vendor_id = p_po_vendor_id;
2124
2125 if (l_is_valid_vendor_id = 0) then
2126 fa_srvr_msg.add_message(
2127 calling_fn => 'fa_asset_val_pvt.validate_po_vendor_id',
2128 name => 'FA_INCORRECT_PO_VENDOR_ID');
2129 return FALSE;
2130 end if;
2131 end if;
2132
2133 return TRUE;
2134
2135 END validate_po_vendor_id;
2136
2137 FUNCTION validate_unit_of_measure
2138 (p_unit_of_measure IN VARCHAR2,
2139 p_calling_fn IN VARCHAR2,
2140 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2141 ) RETURN BOOLEAN IS
2142
2143 l_is_valid_uom number;
2144
2145 BEGIN
2146
2147 if (p_unit_of_measure is not null) then
2148
2149 -- Validate unit of measure exists.
2150 select count(*)
2151 into l_is_valid_uom
2152 from mtl_units_of_measure
2153 where unit_of_measure = p_unit_of_measure
2154 and nvl(disable_date, sysdate+1) > sysdate;
2155
2156 if (l_is_valid_uom = 0) then
2157 fa_srvr_msg.add_message(
2158 calling_fn => 'fa_asset_val_pvt.validate_unit_of_measure',
2159 name => 'FA_INCORRECT_UOM');
2160 return FALSE;
2161 end if;
2162 end if;
2163
2164 return TRUE;
2165
2166 END validate_unit_of_measure;
2167
2168 FUNCTION validate_salvage_value
2169 (p_salvage_value IN NUMBER,
2170 p_cost IN NUMBER,
2171 p_calling_fn IN VARCHAR2,
2172 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2173 ) RETURN BOOLEAN IS
2174
2175 l_abs_salvage_value number;
2176 l_abs_cost number;
2177
2178 BEGIN
2179
2180 if (p_cost is not null) then
2181
2182 -- Get the absolute values of salvage value and cost.
2183 l_abs_salvage_value := abs (p_salvage_value);
2184 l_abs_cost := abs (p_cost);
2185
2186 -- Salvage value cannot exceed cost (fixed assets cost).
2187 if (l_abs_salvage_value > l_abs_cost) then
2188 fa_srvr_msg.add_message(
2189 calling_fn => 'fa_asset_val_pvt.validate_salvage_value',
2190 name => 'FA_INCORRECT_SALVAGE_VALUE');
2191 return FALSE;
2192 end if;
2193 end if;
2194
2195 return TRUE;
2196
2197 END validate_salvage_value;
2198
2199 FUNCTION validate_tag_number
2200 (p_tag_number IN VARCHAR2,
2201 p_mass_addition_id IN NUMBER DEFAULT NULL,
2202 p_calling_fn IN VARCHAR2,
2203 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2204 ) RETURN BOOLEAN IS
2205
2206 l_tag_number_count number;
2207
2208 BEGIN
2209
2210 if (p_tag_number is not null) then
2211
2212 -- Make sure that tag_number does not already exist in fa_additions.
2213 select count(*)
2214 into l_tag_number_count
2215 from fa_additions_b
2216 where tag_number = p_tag_number;
2217
2218 if (l_tag_number_count > 0) then
2219 fa_srvr_msg.add_message(
2220 calling_fn => 'fa_asset_val_pvt.validate_tag_number',
2221 name => 'FA_ADD_TAG_NUMBER_EXISTS',
2222 token1 => 'TAG_NUMBER', -- Fix for Bug#5015917. Passed tag number token to display actual tag number in the log.
2223 value1 => p_tag_number);
2224 return FALSE;
2225 end if;
2226
2227 /*
2228 -- we can't perform this logic here since it will be called from
2229 -- the additions api from mass additions - bug 2246577
2230 -- note that the unique index would catch any corner case
2231 -- violations resulting from this not caught by prepare form
2232
2233 -- Make sure that tag_number does not already exist in fa_mass_additions.
2234 -- If this is called from mass additions post, we don't want to select
2235 -- the asset we're currently trying to post.
2236
2237 select count(*)
2238 into l_tag_number_count
2239 from fa_mass_additions
2240 where tag_number = p_tag_number
2241 and mass_addition_id <> nvl (p_mass_addition_id, -999);
2242
2243 if (l_tag_number_count > 0) then
2244 fa_srvr_msg.add_message(
2245 calling_fn => 'fa_asset_val_pvt.validate_tag_number',
2249
2246 name => 'FA_ADD_TAG_NUMBER_EXISTS');
2247 return FALSE;
2248 end if;
2250 */
2251
2252 end if;
2253
2254 return TRUE;
2255
2256 END validate_tag_number;
2257
2258 FUNCTION validate_split_merged_code
2259 (p_split_merged_code IN VARCHAR2,
2260 p_calling_fn IN VARCHAR2,
2261 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2262 ) RETURN BOOLEAN IS
2263
2264 BEGIN
2265
2266 -- Split merged code cannot be zero.
2267 if (p_split_merged_code = 0) then
2268 fa_srvr_msg.add_message(
2269 calling_fn => 'fa_asset_val_pvt.validate_split_merged_code',
2270 name => 'FA_INCORRECT_SPLIT_MERGED_CODE');
2271 return FALSE;
2272 end if;
2273
2274 return TRUE;
2275
2276 END validate_split_merged_code;
2277
2278
2279 FUNCTION validate_exp_after_amort
2280 (p_asset_id IN number,
2281 p_book IN varchar2,
2282 p_extended_flag IN BOOLEAN DEFAULT FALSE,
2283 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2284 ) RETURN BOOLEAN IS
2285
2286 l_count number;
2287
2288 BEGIN
2289
2290 /* Bug 2407786 - This is the consolidated select stmnt */
2291 /*
2292 select count(*)
2293 into l_count
2294 from fa_books bk
2295 where bk.book_type_code = p_book
2296 and bk.asset_id = p_asset_id
2297 and (bk.rate_Adjustment_factor <> 1 OR
2298 (bk.rate_adjustment_factor = 1 and
2299 exists (select 'YES' -- and amortized before.
2300 from fa_transaction_headers th,
2301 fa_methods mt
2302 where th.book_type_code = bk.book_type_code
2303 and th.asset_id = bk.asset_id
2304 and th.transaction_type_code = 'ADJUSTMENT'
2305 and (th.transaction_subtype = 'AMORTIZED' OR th.transaction_key = 'UA')
2306 and th.transaction_header_id = bk.transaction_header_id_in
2307 and mt.method_code = bk.deprn_method_code
2308 and mt.rate_source_rule IN ('TABLE','FLAT','PRODUCTION'))));*/
2309 /*bug fix 2772517 */
2310
2311 -- perf issue 6348506: Insead of getting the actual count, just check for existence
2312 -- It will return 1 if recs exists and 0 if not. It will not return no-data-found
2313
2314 /* Japan Tax phase3 -- For extended assets consider transactions
2315 after extended transaction */
2316
2317 if p_extended_flag then
2318 select count(1)
2319 into l_count
2320 from dual
2321 where exists (
2322 select 1
2323 from fa_transaction_headers th
2324 where th.book_type_code = p_book
2325 and th.asset_id = p_asset_id
2326 and (th.transaction_subtype = 'AMORTIZED' OR th.transaction_key = 'UA')
2327 and th.transaction_header_id > (select max(th2.transaction_header_id)
2328 from fa_transaction_headers th2
2329 where th2.book_type_code = p_book
2330 and th2.asset_id = p_asset_id
2331 and th2.transaction_key = 'ES'));
2332 else
2333 select count(1)
2334 into l_count
2335 from dual
2336 where exists (
2337 select 1 from fa_transaction_headers
2338 where book_type_code = p_book
2339 and asset_id = p_asset_id
2340 and (transaction_subtype = 'AMORTIZED' OR transaction_key = 'UA'));
2341
2342 end if;
2343
2344 if (l_count > 0) then
2345 fa_srvr_msg.add_message(
2346 calling_fn => 'fa_asset_val_pvt.validate_exp_after_amort',
2347 name => 'FA_BOOK_CANT_EXP_AFTER_AMORT');
2348 return FALSE;
2349 end if;
2350
2351 return true;
2352
2353 EXCEPTION
2354 when others then
2355 fa_srvr_msg.add_sql_error(
2356 calling_fn => 'fa_asset_val_pvt.validate_exp_after_amort');
2357
2358 return false;
2359
2360 END validate_exp_after_amort;
2361
2362 FUNCTION validate_unplanned_exists
2363 (p_asset_id IN number,
2364 p_book IN varchar2,
2365 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2366 ) RETURN BOOLEAN IS
2367
2368 l_count number;
2369
2370 BEGIN
2371
2372 SELECT count(*)
2373 INTO l_count
2374 FROM fa_transaction_headers
2375 WHERE book_type_code = p_book
2376 AND asset_id = p_asset_id
2377 AND transaction_key like 'U%';
2378
2379 if (l_count > 0) then
2380 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcbsx',
2381 name => '***FA_UNP_EXISTS***');
2382 return TRUE;
2383 else
2384 return FALSE;
2385 end if;
2386
2387 END validate_unplanned_exists;
2388
2389
2390
2391 FUNCTION validate_period_of_addition
2392 (p_asset_id IN number,
2393 p_book IN varchar2,
2397 ) RETURN BOOLEAN IS
2394 p_mode IN varchar2 DEFAULT 'ABSOLUTE',
2395 px_period_of_addition IN OUT NOCOPY varchar2,
2396 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2398
2399 l_last_pc number;
2400 l_count number;
2401 l_period_rec FA_API_TYPES.period_rec_type;
2402
2403 BEGIN
2404
2405 -- no need to load book controls cache here as it's loaded
2406 l_last_pc := FA_CACHE_PKG.fazcbc_record.last_period_counter;
2407
2408 if (p_mode = 'ABSOLUTE') then
2409 SELECT count(*)
2410 INTO l_count
2411 FROM fa_deprn_summary
2412 WHERE book_type_code = p_book
2413 AND asset_id = p_asset_id
2414 AND deprn_source_code = 'BOOKS'
2415 AND period_counter = l_last_pc;
2416
2417 if (l_count <> 0) then
2418 px_period_of_addition := 'Y';
2419 else
2420 px_period_of_addition := 'N';
2421 end if;
2422
2423 elsif (p_mode = 'CAPITALIZED') then
2424
2425 if not FA_UTIL_PVT.get_period_rec
2426 (p_book => p_book,
2427 p_effective_date => NULL,
2428 x_period_rec => l_period_rec,
2429 p_log_level_rec => p_log_level_rec
2430 ) then
2431 fa_srvr_msg.add_message (
2432 calling_fn => 'fa_asset_val_pvt.validate_period_of_addition');
2433 return false;
2434 end if;
2435
2436 SELECT count(*)
2437 INTO l_count
2438 FROM fa_transaction_headers th
2439 WHERE th.asset_id = p_asset_id
2440 AND th.book_type_code = p_book
2441 AND th.transaction_type_code = 'ADDITION'
2442 AND th.date_effective > l_period_rec.period_open_date;
2443
2444 if (l_count <> 0) then
2445 px_period_of_addition := 'Y';
2446 else
2447 px_period_of_addition := 'N';
2448 end if;
2449
2450 else
2451 fa_srvr_msg.add_message (
2452 calling_fn => 'fa_asset_val_pvt.validate_period_of_addition',
2453 name => 'FA_CACHE_UNSUPPORTED_MODE');
2454 return false;
2455 end if;
2456
2457 return true;
2458
2459 EXCEPTION
2460 when others then
2461 fa_srvr_msg.add_sql_error(
2462 calling_fn => 'fa_asset_val_pvt.validate_period_of_addition');
2463 return false;
2464
2465 END validate_period_of_addition;
2466
2467 FUNCTION validate_fully_retired
2468 (p_asset_id IN number,
2469 p_book IN varchar2,
2470 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2471 ) RETURN BOOLEAN IS
2472
2473 l_count number;
2474
2475 BEGIN
2476
2477 select count(*)
2478 into l_count
2479 FROM FA_BOOKS BK
2480 WHERE BK.ASSET_ID = p_asset_id
2481 AND BK.PERIOD_COUNTER_FULLY_RETIRED IS NOT NULL
2482 AND BK.DATE_INEFFECTIVE IS NULL
2483 AND BK.BOOK_TYPE_CODE = p_book
2484 AND rownum < 2;
2485
2486 if (l_count <> 0) then
2487 RETURN TRUE;
2488 else
2489 RETURN FALSE;
2490 end if;
2491
2492 EXCEPTION
2493 when others then
2494 fa_srvr_msg.add_sql_error(
2495 calling_fn => 'fa_asset_val_pvt.validate_period_of_addition');
2496 return false;
2497
2498 END validate_fully_retired;
2499
2500
2501 FUNCTION validate_add_to_asset_pending
2502 (p_asset_id in number
2503 ,p_book in varchar2,
2504 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2505 ) return BOOLEAN IS
2506
2507 l_count number;
2508
2509 BEGIN
2510
2511 select count(*)
2512 into l_count
2513 from fa_mass_additions
2514 where book_type_code = p_book
2515 and add_to_asset_id = p_asset_id
2516 and posting_status not in ('POSTED','MERGED','SPLIT','DELETE')
2517 and rownum < 2;
2518
2519 if (l_count <> 0) then
2520 return TRUE;
2521 else
2522 return FALSE;
2523 end if;
2524
2525 EXCEPTION
2526 when others then
2527 fa_srvr_msg.add_sql_error(
2528 calling_fn => 'fa_asset_val_pvt.validate_add_to_asset_pending');
2529 return false;
2530
2531 END validate_add_to_asset_pending;
2532
2533
2534 FUNCTION validate_asset_id_exist
2535 (p_asset_id in number,
2536 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2537 ) return BOOLEAN IS
2538
2539 l_count number;
2540
2541 BEGIN
2542
2543 select count(*)
2544 into l_count
2545 from fa_additions
2546 where asset_id = p_asset_id
2547 and rownum < 2;
2548
2549 if (l_count <> 0) then
2550 return TRUE;
2551 else
2552 return FALSE;
2553 end if;
2554
2555 EXCEPTION
2556 when others then
2557 fa_srvr_msg.add_sql_error(
2558 calling_fn => 'fa_asset_val_pvt.validate_asset_id_exist');
2559 return FALSE;
2563 FUNCTION validate_ret_rst_pending
2560
2561 END validate_asset_id_exist;
2562
2564 (p_asset_id in number
2565 ,p_book in varchar2 ,
2566 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2567 ) return BOOLEAN IS
2568
2569 l_count number;
2570
2571 BEGIN
2572
2573 select count(*)
2574 into l_count
2575 from fa_retirements
2576 where book_type_code = p_book
2577 and asset_id = p_asset_id
2578 and status in ('PENDING','REINSTATE');
2579
2580 if (l_count <> 0) then
2581 return TRUE;
2582 else
2583 return FALSE;
2584 end if;
2585
2586 EXCEPTION
2587
2588 when others then
2589 fa_srvr_msg.add_sql_error(
2590 calling_fn => 'fa_asset_val_pvt.validate_ret_rst_pending');
2591
2592 return FALSE;
2593
2594 END validate_ret_rst_pending;
2595
2596 FUNCTION validate_fa_lookup_code
2597 (p_lookup_type in varchar2
2598 ,p_lookup_code in varchar2,
2599 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2600 ) return BOOLEAN IS
2601
2602 l_count number;
2603
2604 BEGIN
2605
2606 select count(*)
2607 into l_count
2608 from fa_lookups_b
2609 where lookup_type = p_lookup_type
2610 and lookup_code = p_lookup_code;
2611
2612 if (l_count <> 0) then
2613 return TRUE;
2614 else
2615 fa_srvr_msg.add_message
2616 (calling_fn => 'fa_asset_val_pvt.validate_fa_lookup_code',
2617 name => '***FA_BAD_LOOKUP_CODE***');
2618 return FALSE;
2619 end if;
2620
2621 EXCEPTION
2622
2623 when others then
2624 fa_srvr_msg.add_sql_error(
2625 calling_fn => 'fa_asset_val_pvt.validate_fa_lookup_code');
2626 return false;
2627
2628 END validate_fa_lookup_code;
2629
2630 FUNCTION validate_dist_id
2631 (p_asset_id in number
2632 -- ,p_book in varchar2
2633 ,p_dist_id in number,
2634 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2635 ) return BOOLEAN IS
2636
2637 l_count number;
2638
2639 BEGIN
2640
2641 select count(*)
2642 into l_count
2643 from fa_distribution_history
2644 where asset_id = p_asset_id
2645 -- and book_type_code = p_book
2646 and distribution_id = p_dist_id;
2647
2648 if (l_count <> 0) then
2649 return TRUE;
2650 else
2651 fa_srvr_msg.add_message (
2652 calling_fn => 'fa_asset_val_pvt.validate_dist_id');
2653 return FALSE;
2654 end if;
2655
2656 EXCEPTION
2657
2658 when others then
2659 fa_srvr_msg.add_sql_error(
2660 calling_fn => 'fa_asset_val_pvt.validate_dist_id');
2661 return FALSE;
2662
2663 END validate_dist_id;
2664
2665 FUNCTION validate_corp_pending_ret
2666 (p_asset_id in number
2667 ,p_book in varchar2
2668 ,p_transaction_header_id_in in number,
2669 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2670 ) RETURN BOOLEAN IS
2671
2672 l_count1 number := 0;
2673 l_count2 number := 0;
2674
2675 v_xfr_out_thid number;
2676
2677 BEGIN
2678
2679 /*---------------------------------------------------------------+
2680 | Bug 1577955. |
2681 | We need to check if there are any partial Unit Retirements in |
2682 | the Corporate book. Then we check if there were any cost |
2683 | adjustments or if depreciation was run on any of the |
2684 | associated Tax books before running Gain/Loss in the Corporate |
2685 | book. If that is the case, we will not allow |
2686 | the use of the 'Undo Retirement' function. |
2687 | Instead, Gain/Loss must be run on the Corp book first |
2688 | and then you may reinstate the asset. |
2689 +---------------------------------------------------------------+*/
2690 begin
2691 select distinct transaction_header_id
2692 into v_xfr_out_thid
2693 from fa_transaction_headers thd
2694 where thd.asset_id = p_Asset_Id
2695 and thd.TRANSACTION_TYPE_CODE = 'TRANSFER OUT'
2696 and thd.book_type_code = p_book
2697 and thd.transaction_header_id > p_Transaction_Header_Id_In
2698 and rownum = 1;
2699 exception
2700 when others then null;
2701 end;
2702
2703
2704 begin
2705 select count(*)
2706 into l_count1
2707 from fa_adjustments adj,
2708 fa_distribution_history dh
2709 where adj.asset_id = p_asset_id
2710 and adj.asset_id = dh.asset_id
2711 and adj.distribution_id = dh.distribution_id
2712 and dh.transaction_header_id_in = v_xfr_out_thid
2713 and adj.transaction_header_id <> v_xfr_out_thid;
2714 exception
2715 when others then null;
2716 end;
2717
2718
2719 begin
2720 select count(*)
2721 into l_count2
2722 from fa_deprn_detail dd,
2726 and dd.distribution_id = dh.distribution_id
2723 fa_distribution_history dh
2724 where dd.asset_id = p_asset_id
2725 and dd.asset_id = dh.asset_id
2727 and dh.transaction_header_id_in = v_xfr_out_thid;
2728 exception
2729 when others then null;
2730 end;
2731
2732 if (l_count1 <> 0 or l_count2 <> 0) then
2733 return TRUE;
2734 else
2735 return FALSE;
2736 end if;
2737
2738 EXCEPTION
2739
2740 when others then
2741 fa_srvr_msg.add_sql_error(
2742 calling_fn => 'fa_asset_val_pvt.validate_corp_pending_ret');
2743
2744 return false;
2745
2746 END validate_corp_pending_ret;
2747
2748 FUNCTION validate_parent_asset(
2749 p_parent_asset_id IN number,
2750 p_asset_id IN number,
2751 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null ) return boolean IS
2752 l_count number:=0;
2753 l_corp_book varchar2(15);
2754 BEGIN
2755 -- always do this check for corp book
2756 if NOT FA_UTIL_PVT.get_corp_book(
2757 p_asset_id => p_asset_id,
2758 p_corp_book => l_corp_book,
2759 p_log_level_rec => p_log_level_rec ) then
2760 return FALSE;
2761 end if;
2762
2763 select count(1)
2764 into l_count
2765 from fa_books
2766 where book_type_code = l_corp_book
2767 and asset_id = p_parent_asset_id
2768 and date_ineffective is null;
2769
2770 if l_count = 0 then
2771 fa_srvr_msg.add_message(
2772 calling_fn => 'validate_parent_asset',
2773 name => 'FA_INCORRECT_PARENT_ASSET');
2774 return FALSE;
2775 end if;
2776
2777 return TRUE;
2778
2779 END validate_parent_asset;
2780
2781 FUNCTION validate_warranty (
2782 p_warranty_id IN NUMBER,
2783 p_date_placed_in_service IN DATE,
2784 p_book_type_code IN VARCHAR2,
2785 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2786 ) return boolean IS
2787
2788 l_count number := 0;
2789
2790 BEGIN
2791
2792 if p_warranty_id is not null then
2793
2794 -- Validate warranty is in valid date
2795 SELECT count(w.warranty_id) INTO l_count
2796 FROM fa_warranties w
2797 WHERE w.warranty_id = p_warranty_id
2798 AND p_date_placed_in_service between
2799 nvl (w.start_date, p_date_placed_in_service) and
2800 nvl (w.end_date, p_date_placed_in_service);
2801
2802 if l_count = 0 then
2803 fa_srvr_msg.add_message(
2804 calling_fn => 'validate_warranty',
2805 name => 'FA_INVALID_WARRANTY');
2806 return FALSE;
2807 end if;
2808
2809 -- Validate warranty currency is correct
2810 SELECT count(w.warranty_id) INTO l_count
2811 FROM gl_sets_of_books glsob,
2812 fa_book_controls bc,
2813 fa_warranties w
2814 WHERE w.warranty_id = p_warranty_id
2815 AND bc.book_type_code = p_book_type_code
2816 AND bc.set_of_books_id = glsob.set_of_books_id
2817 AND glsob.currency_code =
2818 nvl(w.currency_code, glsob.currency_code);
2819
2820 if l_count = 0 then
2821 fa_srvr_msg.add_message(
2822 calling_fn => 'validate_warranty',
2823 name => 'FA_SHARED_GET_CURRENCY_CODE');
2824 return FALSE;
2825 end if;
2826 end if;
2827
2828 return TRUE;
2829 END validate_warranty;
2830
2831 FUNCTION validate_lease(
2832 p_asset_id IN number,
2833 p_lease_id IN number,
2834 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null ) return boolean IS
2835
2836 CURSOR get_cat_type IS
2837 select category_type
2838 from fa_categories_b
2839 where category_id = ( select asset_category_id
2840 from fa_additions_b
2841 where asset_id = p_asset_id );
2842 CURSOR C1 IS
2843 select currency_code
2844 from gl_sets_of_books sob,
2845 fa_book_controls bc,
2846 fa_books bk
2847 where bk.asset_id = p_asset_id
2848 and bk.date_ineffective is null
2849 and bk.book_type_code = bc.book_type_code
2850 and bc.set_of_books_id = sob.set_of_books_id;
2851
2852 l_cat_type varchar2(30);
2853 l_count number:=0;
2854 l_lease_currency varchar2(15);
2855 lease_error EXCEPTION;
2856 BEGIN
2857
2858 if p_lease_id is not null then
2859
2860 -- check if lease is valid
2861 select count(1)
2862 into l_count
2863 from fa_leases
2864 where lease_id = p_lease_id;
2865 if l_count = 0 then
2866 fa_srvr_msg.add_message(
2867 calling_fn => 'validate_lease',
2868 name => 'FA_INVALID_LEASE');
2869 return FALSE;
2870 end if;
2871
2872 -- check if lease is allowed
2873 OPEN get_cat_type;
2874 FETCH get_cat_type INTO l_cat_type;
2875 CLOSE get_cat_type;
2876 if l_cat_type NOT IN ( 'LEASE', 'LEASEHOLD IMPROVEMENT') then
2880 return FALSE;
2877 fa_srvr_msg.add_message(
2878 calling_fn => 'validate_lease',
2879 name => 'FA_CANT_ADD_LEASE');
2881 end if;
2882
2883 -- check if lease_currency same
2884 select currency_code
2885 into l_lease_currency
2886 from fa_leases
2887 where lease_id = p_lease_id;
2888 FOR c1_rec in c1 loop
2889 if (l_lease_currency <> c1_rec.currency_code) then
2890 raise lease_error;
2891 end if;
2892 END LOOP;
2893 end if;
2894
2895 return TRUE;
2896
2897 EXCEPTION
2898 when lease_error then
2899 fa_srvr_msg.add_message(
2900 calling_fn => 'validate_lease',
2901 name => 'FA_CURRENCY_NOT_MATCH');
2902 return FALSE;
2903
2904 END validate_lease;
2905
2906 FUNCTION validate_property_type(p_property_type_code in VARCHAR2,
2907 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
2908 l_count number:= 0;
2909 BEGIN
2910 if p_property_type_code is not null then
2911 select count(1)
2912 into l_count
2913 from fa_lookups_b
2914 where lookup_type = 'PROPERTY TYPE'
2915 and lookup_code = p_property_type_code;
2916
2917 if l_count = 0 then
2918 fa_srvr_msg.add_message(
2919 calling_fn => 'validate_property_type',
2920 name => 'FA_PROPERTY_TYPE_NOT_EXIST');
2921 return FALSE;
2922 end if;
2923 end if;
2924
2925 return TRUE;
2926
2927 END validate_property_type;
2928
2929
2930 FUNCTION validate_1245_1250_code(p_1245_1250_code in VARCHAR2,
2931 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
2932 l_count number:= 0;
2933 BEGIN
2934 if p_1245_1250_code is not null then
2935 select count(1)
2936 into l_count
2937 from fa_lookups_b
2938 where lookup_type = '1245/1250 PROPERTY'
2939 and lookup_code = p_1245_1250_code;
2940
2941 if l_count = 0 then
2942 fa_srvr_msg.add_message(
2943 calling_fn => 'validate_1245_1250_code',
2944 name => 'FA_1245_1250_NOT_EXIST');
2945 return FALSE;
2946 end if;
2947 end if;
2948
2949 return TRUE;
2950
2951 END validate_1245_1250_code;
2952
2953 FUNCTION validate_group_asset
2954 (p_group_asset_id in NUMBER,
2955 p_book_type_code in VARCHAR2,
2956 p_asset_type in VARCHAR2,
2957 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
2958 ) return boolean is
2959
2960 l_count number;
2961
2962 BEGIN
2963
2964 if (p_log_level_rec.statement_level) then
2965 fa_debug_pkg.add('val api', 'group', p_group_asset_id);
2966 fa_debug_pkg.add('val api', 'book', p_book_type_code);
2967 end if;
2968
2969 --
2970 -- Following sql is too expensive
2971 --
2972 -- select count(*)
2973 -- into l_count
2974 -- from FA_BOOKS
2975 -- where ASSET_ID = p_group_asset_id
2976 -- and BOOK_TYPE_CODE = p_book_type_code;
2977 l_count := null;
2978 select 1
2979 into l_count
2980 from dual
2981 where exists (select 'X'
2982 from FA_BOOKS
2983 where ASSET_ID = p_group_asset_id
2984 and BOOK_TYPE_CODE = p_book_type_code);
2985
2986 if l_count is null then
2987 fa_srvr_msg.add_message(
2988 calling_fn => 'fa_asset_val_pvt.validate_group_asset',
2989 name => 'FA_GROUP_NOT_IN_BOOK');
2990 return false;
2991 end if;
2992
2993
2994 if (p_asset_type <> 'CAPITALIZED' and
2995 p_asset_type <> 'CIP') then
2996 fa_srvr_msg.add_message(
2997 calling_fn => 'fa_asset_val_pvt.validate_group_asset',
2998 name => 'FA_INV_ASSET_TYPE');
2999 return false;
3000 end if;
3001
3002 return true;
3003
3004 END validate_group_asset;
3005
3006 --HH group enable/disable
3007 FUNCTION validate_disabled_flag
3008 (p_group_asset_id in NUMBER,
3009 p_book_type_code in VARCHAR2,
3010 p_old_flag IN VARCHAR2,
3011 p_new_flag IN VARCHAR2,
3012 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3013 ) return boolean is
3014
3015 l_calling_fn varchar2(40) :='fa_asset_val_pvt.validate_disabled_flag';
3016
3017 BEGIN
3018
3019 IF ((NVL(p_old_flag,'N') Not IN ('Y','N')) OR
3020 (NVL(p_new_flag,'N') Not IN ('Y','N'))) THEN
3021 -- Garbage value for flag.
3022 fa_srvr_msg.add_message(
3023 calling_fn => l_calling_fn,
3024 name => 'FA_INCORRECT_DISABLED_FLAG');
3025 return FALSE;
3026
3027 ELSIF (nvl(p_old_flag,'N')='Y' AND nvl(p_new_flag,'N')='Y') THEN
3028 --Disabled group.
3029 fa_srvr_msg.add_message(
3030 calling_fn => l_calling_fn,
3031 name => 'FA_DISABLED_GROUP');
3032 return FALSE;
3033 ELSIF (nvl(p_old_flag,'N')='N' AND nvl(p_new_flag,'N')='Y') THEN
3037 --Group doesn't meet criteria for disabling.
3034 if NOT validate_group_info(p_group_asset_id => p_group_asset_id,
3035 p_book_type_code => p_book_type_code,
3036 p_calling_fn => l_calling_fn) THEN
3038 return FALSE;
3039 end if;
3040 END IF;
3041
3042 RETURN TRUE;
3043
3044 END validate_disabled_flag;
3045
3046 FUNCTION validate_group_info
3047 (p_group_asset_id in NUMBER,
3048 p_book_type_code in VARCHAR2,
3049 p_calling_fn in VARCHAR2,
3050 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3051 ) return boolean is
3052
3053 l_has_members number;
3054 l_disabled number;
3055 l_calling_fn varchar2(40) :='fa_asset_val_pvt.validate_group_info';
3056
3057 BEGIN
3058
3059 SELECT count(1)
3060 INTO l_disabled
3061 FROM fa_books
3062 WHERE asset_id = p_group_asset_id
3063 AND book_type_code = p_book_type_code
3064 AND disabled_flag = 'Y'
3065 AND transaction_header_id_out is null;
3066
3067 if (l_disabled = 0) then
3068 if p_calling_fn <> 'fa_asset_val_pvt.validate_disabled_flag' then
3069 return true;
3070 else
3071 SELECT count(1)
3072 INTO l_has_members
3073 FROM fa_books
3074 WHERE group_asset_id = p_group_asset_id
3075 AND book_type_code = p_book_type_code
3076 AND transaction_header_id_out is null
3077 AND period_counter_fully_retired is null;
3078 end if;
3079 elsif (l_disabled > 0) then
3080 fa_srvr_msg.add_message(
3081 calling_fn => l_calling_fn,
3082 name => 'FA_DISABLED_GROUP');
3083 return false;
3084 end if;
3085
3086 if l_has_members > 0 then
3087 fa_srvr_msg.add_message(
3088 calling_fn => l_calling_fn,
3089 name => 'FA_CANT_DISABLE_GROUP');
3090 return false;
3091 end if;
3092
3093 return true;
3094
3095 END validate_group_info; -- End HH.
3096
3097 FUNCTION validate_over_depreciate
3098 (p_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type,
3099 p_asset_type VARCHAR2,
3100 p_over_depreciate_option VARCHAR2 default null,
3101 p_adjusted_recoverable_cost NUMBER default null,
3102 p_recoverable_cost NUMBER default null,
3103 p_deprn_reserve_new NUMBER default null,
3104 p_rate_source_rule VARCHAR2 default null,
3105 p_deprn_basis_rule VARCHAR2 default null,
3106 p_recapture_reserve_flag VARCHAR2 default null,
3107 p_deprn_limit_type VARCHAR2 default null,
3108 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3109 ) return boolean is
3110
3111 l_calling_fn varchar2(50) := 'fa_asset_val_pvt.validate_over_depreciate';
3112
3113 l_member_count binary_integer := 0;
3114
3115 BEGIN
3116
3117
3118 if (p_asset_type = 'GROUP') then
3119
3120 if (nvl(p_adjusted_recoverable_cost, p_recoverable_cost) = 0) then
3121 --
3122 -- Check to see if there is no member belongs to this group asset
3123 -- If no member asset exists, terminal gain loss will take care
3124 -- remaining reserve handling
3125 --
3126 select count(transaction_header_id_in)
3127 into l_member_count
3128 from fa_books
3129 where group_asset_id = p_asset_hdr_rec.asset_id
3130 and book_type_code = p_asset_hdr_rec.book_type_code
3131 and transaction_header_id_out is null;
3132 else
3133 -- Group has a cost so set dummy 1
3134 l_member_count := 1;
3135 end if;
3136
3137
3138 if (l_member_count > 0) then
3139 -- Check to see if new reserve exceeds (adjusted )recoverable cost
3140 -- even thought asset is not suppsed to be over depreciated
3141 if (p_deprn_reserve_new is not null) and
3142 (nvl(p_adjusted_recoverable_cost, p_recoverable_cost) is not null) then
3143
3144 if (nvl(p_over_depreciate_option, fa_std_types.FA_OVER_DEPR_NO) =
3145 fa_std_types.FA_OVER_DEPR_NO) then
3146 if (nvl(p_adjusted_recoverable_cost, p_recoverable_cost) > 0 and
3147 nvl(p_adjusted_recoverable_cost, p_recoverable_cost) < p_deprn_reserve_new) or
3148 (nvl(p_adjusted_recoverable_cost, p_recoverable_cost) < 0 and
3149 nvl(p_adjusted_recoverable_cost, p_recoverable_cost) > p_deprn_reserve_new) then
3150 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3151 name => 'FA_TOO_MUCH_RESERVE');
3152 return FALSE;
3153 end if;
3154
3155 end if;
3156 end if;
3157
3158 -- Over Depreciate cannot be DEPRN if method is flat-nbv
3159 if (p_rate_source_rule is not null) and
3160 (p_deprn_basis_rule is not null) then
3161 if (p_rate_source_rule = 'FLAT') and
3162 (p_deprn_basis_rule = 'NBV') and
3163 (nvl(p_over_depreciate_option, fa_std_types.FA_OVER_DEPR_NO) =
3164 fa_std_types.FA_OVER_DEPR_DEPRN) then
3165
3166 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3170 end if;
3167 name => 'FA_NO_OVER_DEPRN_ALLOWED');
3168 return false;
3169
3171
3172 end if;
3173
3174 /* BUG# 2941674
3175 * removing this validation for now...
3176 * as we need it to account for CRL behavior
3177
3178 if (p_deprn_limit_type is not null) then
3179 if (p_deprn_limit_type <> 'NONE') and
3180 (nvl(p_over_depreciate_option, fa_std_types.FA_OVER_DEPR_NO) <>
3181 fa_std_types.FA_OVER_DEPR_NO) then
3182
3183 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3184 name => 'FA_NO_OVER_DEPRN_ALLOWED');
3185 return false;
3186 end if;
3187
3188 end if;
3189
3190 */
3191
3192 if (nvl(p_recapture_reserve_flag, 'N') <> 'N') and
3193 (nvl(p_over_depreciate_option, fa_std_types.FA_OVER_DEPR_NO) <>
3194 fa_std_types.FA_OVER_DEPR_NO) then
3195
3196 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3197 name => 'FA_NO_OVER_DEPRN_ALLOWED');
3198 return false;
3199 end if;
3200 end if; -- (l_member_count > 0)
3201
3202 end if; -- (p_asset_type = 'GROUP')
3203
3204
3205 return TRUE;
3206
3207 END validate_over_depreciate;
3208
3209 FUNCTION validate_cost_change (
3210 p_asset_id number,
3211 p_group_asset_id number,
3212 p_book_type_code varchar2,
3213 p_asset_type varchar2,
3214 p_transaction_header_id number,
3215 p_transaction_date date,
3216 p_cost number default 0,
3217 p_cost_adj number default 0,
3218 p_salvage_value number default 0,
3219 p_salvage_value_adj number default 0,
3220 p_deprn_limit_amount number default 0,
3221 p_deprn_limit_amount_adj number default 0,
3222 p_mrc_sob_type_code varchar2,
3223 p_over_depreciate_option varchar2,
3224 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3225 ) return boolean is
3226
3227 l_calling_fn varchar2(50) := 'fa_asset_val_pvt.validate_cost_change';
3228
3229 CURSOR c_get_current_amts IS
3230 select sum(inbk.cost - nvl(outbk.cost, 0))
3231 , sum(inbk.salvage_value - nvl(outbk.salvage_value, 0))
3232 , sum(nvl(inbk.allowed_deprn_limit_amount, 0) -
3233 nvl(outbk.allowed_deprn_limit_amount, 0))
3234 from fa_transaction_headers th,
3235 fa_books inbk,
3236 fa_books outbk
3237 where inbk.asset_id = p_asset_id
3238 and inbk.book_type_code = p_book_type_code
3239 and outbk.asset_id(+) = p_asset_id
3240 and outbk.book_type_code(+) = p_book_type_code
3241 and inbk.transaction_header_id_in = th.transaction_header_id
3242 and decode(th.transaction_type_code, 'ADDITION', to_number(null),
3243 'CIP ADDITION', to_number(null),
3244 outbk.transaction_header_id_out(+)) = th.transaction_header_id
3245 and th.asset_id = p_asset_id
3246 and th.book_type_code = p_book_type_code
3247 and th.transaction_type_code not in ('TRANSFER OUT', 'TRANSFER IN',
3248 'TRANSFER', 'TRANSFER IN/VOID',
3249 'RECLASS', 'UNIT ADJUSTMENT',
3250 'REINSTATEMENT', 'ADDITION/VOID',
3251 'CIP ADDITION/VOID')
3252 and th.transaction_header_id <> p_transaction_header_id
3253 and decode(th.transaction_type_code,
3254 'ADDITION', inbk.date_placed_in_service,
3255 'CIP ADDITION', inbk.date_placed_in_service,
3256 decode(th.transaction_subtype,
3257 'EXPENSED', inbk.date_placed_in_service,
3258 nvl(th.amortization_start_date,
3259 th.transaction_date_entered))) <= p_transaction_date
3260 and not exists(select 'Exclude Retirement which reinstatement exists'
3261 from fa_retirements ret,
3262 fa_transaction_headers reith
3263 where ret.transaction_header_id_in = th.transaction_header_id
3264 and ret.transaction_header_id_out = reith.transaction_header_id
3265 and nvl(reith.amortization_start_date,
3266 reith.transaction_date_entered) <= p_transaction_date);
3267
3268 CURSOR c_get_current_mc_amts IS
3269 select sum(inbk.cost - nvl(outbk.cost, 0))
3270 , sum(inbk.salvage_value - nvl(outbk.salvage_value, 0))
3271 , sum(nvl(inbk.allowed_deprn_limit_amount, 0) -
3272 nvl(outbk.allowed_deprn_limit_amount, 0))
3273 from fa_transaction_headers th,
3274 fa_books_mrc_v inbk,
3275 fa_books_mrc_v outbk
3276 where inbk.asset_id = p_asset_id
3277 and inbk.book_type_code = p_book_type_code
3278 and outbk.asset_id(+) = p_asset_id
3279 and outbk.book_type_code(+) = p_book_type_code
3283 outbk.transaction_header_id_out(+)) = th.transaction_header_id
3280 and inbk.transaction_header_id_in = th.transaction_header_id
3281 and decode(th.transaction_type_code, 'ADDITION', to_number(null),
3282 'CIP ADDITION', to_number(null),
3284 and th.asset_id = p_asset_id
3285 and th.book_type_code = p_book_type_code
3286 and th.transaction_type_code not in ('TRANSFER OUT', 'TRANSFER IN',
3287 'TRANSFER', 'TRANSFER IN/VOID',
3288 'RECLASS', 'UNIT ADJUSTMENT',
3289 'REINSTATEMENT', 'ADDITION/VOID',
3290 'CIP ADDITION/VOID')
3291 and th.transaction_header_id <> p_transaction_header_id
3292 and decode(th.transaction_type_code,
3293 'ADDITION', inbk.date_placed_in_service,
3294 'CIP ADDITION', inbk.date_placed_in_service,
3295 decode(th.transaction_subtype,
3296 'EXPENSED', inbk.date_placed_in_service,
3297 nvl(th.amortization_start_date,
3298 th.transaction_date_entered))) <= p_transaction_date
3299 and not exists(select 'Exclude Retirement which reinstatement exists'
3300 from fa_retirements ret,
3301 fa_transaction_headers reith
3302 where ret.transaction_header_id_in = th.transaction_header_id
3303 and ret.transaction_header_id_out = reith.transaction_header_id
3304 and nvl(reith.amortization_start_date,
3305 reith.transaction_date_entered) <= p_transaction_date);
3306
3307 l_cost number;
3308 l_salvage_value number;
3309 l_deprn_limit_amount number;
3310
3311 val_err EXCEPTION;
3312
3313 BEGIN
3314
3315 --
3316 -- Perform the check only if
3317 -- Current cost is not 0
3318 -- and sign of current cost and delta cost is different. OR
3319 -- Current salvage_value is not 0
3320 -- and sign of current salvage_value and delta salvage_value is different
3321 -- and asset type is GROUP. OR
3322 -- Current deprn_limit_amount is not 0
3323 -- and sign of current deprn_limit_amount and delta deprn_limit_amount is different.
3324 --
3325 if (( p_cost <> 0 and p_cost_adj <> 0) and sign(p_cost_adj) <> sign(p_cost)) or
3326 (( p_salvage_value <> 0 and p_salvage_value_adj <> 0) and
3327 (sign(p_salvage_value_adj) <> sign(p_salvage_value)) and
3328 (p_asset_type <> 'GROUP')) or
3329 ((p_deprn_limit_amount <> 0 and p_deprn_limit_amount_adj <> 0) and
3330 sign(p_deprn_limit_amount_adj) <> sign(p_deprn_limit_amount)) then
3331
3332 /* commented for bugfix# 5131759
3333 if (p_mrc_sob_type_code = 'R') then
3334 OPEN c_get_current_mc_amts;
3335 FETCH c_get_current_mc_amts INTO l_cost, l_salvage_value, l_deprn_limit_amount;
3336 CLOSE c_get_current_mc_amts;
3337 else
3338 OPEN c_get_current_amts;
3339 FETCH c_get_current_amts INTO l_cost, l_salvage_value, l_deprn_limit_amount;
3340 CLOSE c_get_current_amts;
3341 end if; -- (p_mrc_sob_type_code = 'R') */
3342
3343 --HH
3344 --Bug 3528634. Check cost change flag and other conditions in bug.
3345 --Only do for cost and salvage amounts. For deprn limit I think we always want
3346 --to check that. See bug for more details.
3347 --Also, using the already loaded cache structures here since this is only
3348 --called from calc now. Should this change, we may need to consider
3349 --changing the params passed in to this proc.
3350
3351 if (fa_cache_pkg.fazccmt_record.rate_source_rule NOT IN ('CALCULATED','FLAT')) OR
3352 (fa_cache_pkg.fazccmt_record.deprn_basis_rule = 'NBV') OR
3353 (NVL(fa_cache_pkg.fazcbc_record.ALLOW_COST_SIGN_CHANGE_FLAG,'N') = 'N') then
3354
3355 --Members must also belong to a group that has the Over Depreciate Option as
3356 --"Allow and Depreciate"
3357
3358 if ((p_group_asset_id is not null) and
3359 (nvl(p_over_depreciate_option, fa_std_types.FA_OVER_DEPR_NO) =
3360 fa_std_types.FA_OVER_DEPR_DEPRN) and
3361 (NVL(fa_cache_pkg.fazcbc_record.ALLOW_COST_SIGN_CHANGE_FLAG,'N') = 'Y')) then
3362 if (p_log_level_rec.statement_level) then
3363 fa_debug_pkg.add(l_calling_fn, 'Member cost sign','can be changed',p_log_level_rec);
3364 fa_debug_pkg.add(l_calling_fn, 'over depreciate option',p_over_depreciate_option,p_log_level_rec);
3365 fa_debug_pkg.add(l_calling_fn, 'group_asset_id',p_group_asset_id,p_log_level_rec);
3366 end if;
3367 else
3368 if (p_log_level_rec.statement_level) then
3369 fa_debug_pkg.add(l_calling_fn, 'validating cost and salvage','',p_log_level_rec);
3370 fa_debug_pkg.add(l_calling_fn, 'sign change flag',
3371 fa_cache_pkg.fazcbc_record.ALLOW_COST_SIGN_CHANGE_FLAG,p_log_level_rec);
3372 fa_debug_pkg.add(l_calling_fn, 'over depr option',p_over_depreciate_option,p_log_level_rec);
3373 fa_debug_pkg.add(l_calling_fn, 'group_asset_id',p_group_asset_id,p_log_level_rec);
3374 end if;
3375
3379 FETCH c_get_current_mc_amts INTO l_cost, l_salvage_value, l_deprn_limit_amount;
3376 -- Added following if condition for bugfix# 5131759
3377 if (p_mrc_sob_type_code = 'R') then
3378 OPEN c_get_current_mc_amts;
3380 CLOSE c_get_current_mc_amts;
3381 else
3382 OPEN c_get_current_amts;
3383 FETCH c_get_current_amts INTO l_cost, l_salvage_value, l_deprn_limit_amount;
3384 CLOSE c_get_current_amts;
3385 end if; -- (p_mrc_sob_type_code = 'R')
3386
3387 if (p_cost_adj <> 0) and
3388 (sign(nvl(l_cost, 0) + p_cost_adj) <> 0) and
3389 (sign(nvl(l_cost, 0) + p_cost_adj) <> sign(p_cost)) then
3390 if (p_log_level_rec.statement_level) then
3391 fa_debug_pkg.add(l_calling_fn, 'Delta Cost', 'Invalid',p_log_level_rec);
3392 end if;
3393 raise val_err;
3394 end if;
3395
3396 if (p_salvage_value_adj <> 0) and
3397 (sign(nvl(l_salvage_value, 0) + p_salvage_value_adj) <> 0) and
3398 (sign(nvl(l_salvage_value, 0) + p_salvage_value_adj) <>
3399 sign(p_salvage_value)) then
3400 if (p_log_level_rec.statement_level) then
3401 fa_debug_pkg.add(l_calling_fn, 'Delta Salvage Value', 'Invalid',p_log_level_rec);
3402 end if;
3403 raise val_err;
3404 end if;
3405 end if; --group_asset_id not null...
3406
3407 end if; --Cost change condition. End HH.
3408
3409 if (p_deprn_limit_amount_adj <> 0) and
3410 (sign(nvl(l_deprn_limit_amount, 0) + p_deprn_limit_amount_adj) <> 0) and
3411 (sign(nvl(l_deprn_limit_amount, 0) + p_deprn_limit_amount_adj) <>
3412 sign(p_deprn_limit_amount)) then
3413 if (p_log_level_rec.statement_level) then
3414 fa_debug_pkg.add(l_calling_fn, 'Delta Deprn Limit Amount', 'Invalid',p_log_level_rec);
3415 end if;
3416 raise val_err;
3417 end if;
3418
3419 end if; -- ( p_cost <> 0)
3420
3421 return true;
3422
3423 EXCEPTION
3424 WHEN val_err THEN
3425 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3426 name => 'FA_INVALID_AMOUNT_ADJUSTMENT');
3427 return false;
3428
3429 WHEN OTHERS THEN
3430 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
3431 return false;
3432
3433 END validate_cost_change;
3434
3435
3436 -- New function due for bug2846357
3437 --
3438 -- check if duplicate distribution info exist in p_asset_dist_tbl
3439 -- current row( p_curr_index) of p_asset_dist_tbl is compared to
3440 -- all of previous rows of p_asset_dist_tbl
3441 -- to check for duplicates
3442
3443 FUNCTION validate_duplicate_dist (
3444 p_transaction_type_code IN VARCHAR2,
3445 p_asset_dist_tbl IN OUT NOCOPY FA_API_TYPES.asset_dist_tbl_type,
3446 p_curr_index IN NUMBER,
3447 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null ) return boolean IS
3448
3449 l_high_bound number;
3450 dup_err exception;
3451 l_calling_fn varchar2(50) := 'fa_asset_val_pvt.validate_duplicate_dist';
3452
3453 BEGIN
3454
3455 l_high_bound := p_curr_index - 1;
3456 FOR k in p_asset_dist_tbl.first..l_high_bound LOOP
3457
3458 -- if TRANSFER check if transfering to same line
3459 if p_transaction_type_code = 'TRANSFER' then
3460 if ( p_asset_dist_tbl(k).distribution_id is not null and
3461 p_asset_dist_tbl(p_curr_index).distribution_id is not null) then
3462 if ( p_asset_dist_tbl(k).distribution_id =
3463 p_asset_dist_tbl(p_curr_index).distribution_id)
3464 then
3465 raise dup_err;
3466 end if;
3467 end if;
3468 end if;
3469
3470 -- Check for duplicate lines
3471 if ( nvl(p_asset_dist_tbl(k).assigned_to,-99) = nvl(p_asset_dist_tbl(p_curr_index).assigned_to,-99) and
3472 p_asset_dist_tbl(k).expense_ccid = p_asset_dist_tbl(p_curr_index).expense_ccid and
3473 p_asset_dist_tbl(k).location_ccid = p_asset_dist_tbl(p_curr_index).location_ccid)
3474 then
3475 raise dup_err;
3476 end if;
3477 END LOOP;
3478
3479 return true;
3480
3481 EXCEPTION
3482
3483 WHEN dup_err THEN
3484 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3485 name => 'FA_TFR_SAME_LINE');
3486 return false;
3487
3488 WHEN OTHERS THEN
3489
3490 fa_srvr_msg.add_sql_error(
3491 calling_fn => l_calling_fn );
3492
3493 return false;
3494
3495 END validate_duplicate_dist;
3496
3497 FUNCTION validate_polish
3498 (p_transaction_type_code IN VARCHAR2,
3499 p_method_code IN VARCHAR2,
3500 p_life_in_months IN NUMBER DEFAULT NULL,
3501 p_asset_type IN VARCHAR2 DEFAULT NULL,
3502 p_bonus_rule IN VARCHAR2 DEFAULT NULL,
3503 p_ceiling_name IN VARCHAR2 DEFAULT NULL,
3504 p_deprn_limit_type IN VARCHAR2 DEFAULT NULL,
3508 IN DATE DEFAULT NULL,
3505 p_group_asset_id IN NUMBER DEFAULT NULL,
3506 p_date_placed_in_service IN DATE DEFAULT NULL,
3507 p_calendar_period_open_date
3509 p_ytd_deprn IN NUMBER DEFAULT NULL,
3510 p_deprn_reserve IN NUMBER DEFAULT NULL,
3511 p_calling_fn IN VARCHAR2,
3512 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3513 ) RETURN BOOLEAN AS
3514
3515 l_deprn_basis_rule varchar2(80);
3516 l_polish_rule number;
3517 l_calling_fn varchar2(35) := 'fa_asset_val_pvt.validate_polish';
3518
3519 BEGIN
3520
3521 -- First find out if we have a polish mechanism here
3522 if not fa_cache_pkg.fazccmt (
3523 X_method => p_method_code,
3524 X_life => p_life_in_months,
3525 p_log_level_rec => p_log_level_rec
3526 ) then
3527 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
3528
3529 return FALSE;
3530 end if;
3531
3532 if (fa_cache_pkg.fazccmt_record.deprn_basis_rule_id is not null) then
3533 l_polish_rule := fa_cache_pkg.fazcdbr_record.polish_rule;
3534 else
3535 -- No deprn basis rule attached to this method, so not Polish either
3536 return TRUE;
3537 end if;
3538
3539 if (nvl(l_polish_rule, FA_STD_TYPES.FAD_DBR_POLISH_NONE) not in (
3540 FA_STD_TYPES.FAD_DBR_POLISH_1,
3541 FA_STD_TYPES.FAD_DBR_POLISH_2,
3542 FA_STD_TYPES.FAD_DBR_POLISH_3,
3543 FA_STD_TYPES.FAD_DBR_POLISH_4,
3544 FA_STD_TYPES.FAD_DBR_POLISH_5)) then
3545 -- Not Polish rule
3546 return TRUE;
3547 end if;
3548
3549 -- This is a Polish rule, so start validations.
3550 /*
3551 -- No adjustments allowed on Polish mechanisms.
3552 if (p_transaction_type_code in ('ADJUSTMENT', 'CIP ADJUSTMENT',
3553 'GROUP ADJUSTMENT')) then
3554 fa_srvr_msg.add_message(
3555 calling_fn => l_calling_fn,
3556 name => 'FA_POLISH_NO_ADJ');
3557 return FALSE;
3558 end if;
3559
3560 -- No partial retirements allowed on Polish mechanisms.
3561 if (p_transaction_type_code = 'PARTIAL RETIREMENT') then
3562 fa_srvr_msg.add_message(
3563 calling_fn => l_calling_fn,
3564 name => 'FA_POLISH_NO_PARTIAL_RET');
3565 return FALSE;
3566 end if;
3567 */
3568 -- No revaluations allowed on Polish mechanisms.
3569 if (p_transaction_type_code = 'REVALUATION') then
3570 fa_srvr_msg.add_message(
3571 calling_fn => l_calling_fn,
3572 name => 'FA_POLISH_NO_REVAL');
3573 return FALSE;
3574 end if;
3575
3576 -- No tax reserve adjustments allowed on Polish mechanisms.
3577 if (p_transaction_type_code = 'TAX RESERVE ADJUSTMENT') then
3578 fa_srvr_msg.add_message(
3579 calling_fn => l_calling_fn,
3580 name => 'FA_POLISH_NO_TAX_RSV_ADJ');
3581 return FALSE;
3582 end if;
3583
3584 -- No adding an Polish asset with reserve.
3585 if (p_transaction_type_code in ('ADDITION',
3586 'CIP ADDITION',
3587 'GROUP ADDITION')) then
3588
3589 -- No backdated additions for Polish.
3590 if (p_date_placed_in_service < p_calendar_period_open_date) then
3591 fa_srvr_msg.add_message(
3592 calling_fn => l_calling_fn,
3593 name => 'FA_POLISH_NO_PRIOR_ADD');
3594 return FALSE;
3595 end if;
3596
3597 -- No adding an Polish asset with reserve.
3598 if ((nvl(p_ytd_deprn,0) <> 0) OR (nvl(p_deprn_reserve,0) <> 0)) then
3599 fa_srvr_msg.add_message(
3600 calling_fn => l_calling_fn,
3601 name => 'FA_POLISH_NO_ADD_RSV');
3602 return FALSE;
3603 end if;
3604 end if;
3605
3606 if (p_transaction_type_code in ('ADDITION',
3607 'CIP ADDITION',
3608 'GROUP ADDITION',
3609 'ADJUSTMENT',
3610 'CIP ADJUSTMENT',
3611 'GROUP ADJUSTMENT')) then
3612
3613 -- Mechanisms 1, 3, 4, 5 must have bonus rules attached
3614 if ((p_bonus_rule is null) and
3615 (l_polish_rule in (FA_STD_TYPES.FAD_DBR_POLISH_1,
3616 FA_STD_TYPES.FAD_DBR_POLISH_3,
3617 FA_STD_TYPES.FAD_DBR_POLISH_4,
3618 FA_STD_TYPES.FAD_DBR_POLISH_5))) then
3619
3620 fa_srvr_msg.add_message(
3621 calling_fn => l_calling_fn,
3622 name => 'FA_POLISH_NO_BONUS_RULE');
3623 return FALSE;
3624 end if;
3625
3626 -- No ceilings allowed on Polish rules
3627 if (p_ceiling_name is not null) then
3628 fa_srvr_msg.add_message(
3629 calling_fn => l_calling_fn,
3630 name => 'FA_POLISH_NO_CEILING');
3631 return FALSE;
3632 end if;
3633
3634 -- No depreciation limits allowed on Polish rules
3635 if (nvl(p_deprn_limit_type, 'NONE') <> 'NONE') then
3639 return FALSE;
3636 fa_srvr_msg.add_message(
3637 calling_fn => l_calling_fn,
3638 name => 'FA_POLISH_NO_LIMIT');
3640 end if;
3641
3642 -- No group assets or members of group assets allowed to have Polish
3643 if (p_asset_type = 'GROUP') OR (p_group_asset_id is not null) then
3644 fa_srvr_msg.add_message(
3645 calling_fn => l_calling_fn,
3646 name => 'FA_POLISH_NO_GROUP');
3647 return FALSE;
3648 end if;
3649 end if;
3650
3651 return TRUE;
3652
3653 EXCEPTION
3654 WHEN OTHERS THEN
3655
3656 fa_srvr_msg.add_sql_error( calling_fn => l_calling_fn );
3657
3658 return FALSE;
3659
3660 END validate_polish;
3661
3662 FUNCTION validate_super_group (
3663 p_book_type_code IN VARCHAR2,
3664 p_old_super_group_id IN NUMBER,
3665 p_new_super_group_id IN NUMBER,
3666 p_calling_fn IN VARCHAR2,
3667 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
3668
3669 l_calling_fn varchar2(40) := 'fa_asset_val_pvt.validate_super_group';
3670
3671 CURSOR c_check_used is
3672 select 'Y'
3673 from fa_super_group_rules
3674 where super_group_id = p_new_super_group_id
3675 and book_type_code = p_book_type_code
3676 and used_flag = 'Y';
3677
3678 l_used_flag varchar2(1);
3679
3680 BEGIN
3681
3682 if (not(nvl(p_old_super_group_id, -99) = nvl(p_new_super_group_id, -99))) then
3683 if (p_old_super_group_id is null) and
3684 (p_new_super_group_id is not null) then
3685
3686 OPEN c_check_used;
3687 FETCH c_check_used INTO l_used_flag;
3688
3689 if (c_check_used%FOUND) then
3690
3691 CLOSE c_check_used;
3692
3693 if (p_log_level_rec.statement_level) then
3694 fa_debug_pkg.add(l_calling_fn, 'p_book_type_code', p_book_type_code);
3695 fa_debug_pkg.add(l_calling_fn, 'p_new_super_group_id', p_new_super_group_id);
3696 fa_debug_pkg.add(l_calling_fn, 'This Super group is used', 'TRUE');
3697 end if;
3698
3699 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3700 name => 'FA_SUPER_GROUP_USED');
3701
3702 return FALSE;
3703 end if; -- (c_check_used%FOUND)
3704
3705 CLOSE c_check_used;
3706
3707 end if; -- (p_old_super_group_id is null) and
3708
3709 end if;
3710
3711 return true;
3712
3713 EXCEPTION
3714 WHEN OTHERS THEN
3715 if (p_log_level_rec.statement_level) then
3716 fa_debug_pkg.add(l_calling_fn, 'p_book_type_code', p_book_type_code);
3717 fa_debug_pkg.add(l_calling_fn, 'p_old_super_group_id', p_old_super_group_id);
3718 fa_debug_pkg.add(l_calling_fn, 'p_new_super_group_id', p_new_super_group_id);
3719 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION: OTHERS', sqlerrm);
3720 end if;
3721
3722 fa_srvr_msg.add_sql_error( calling_fn => l_calling_fn );
3723
3724 return FALSE;
3725 END validate_super_group;
3726
3727 FUNCTION validate_member_dpis
3728 (p_book_type_code IN VARCHAR2,
3729 p_date_placed_in_service IN DATE,
3730 p_group_asset_Id IN NUMBER,
3731 p_calling_fn IN VARCHAR2,
3732 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3733 ) RETURN BOOLEAN IS
3734
3735 CURSOR c_group_dpis (p_group_asset_id NUMBER,
3736 p_book_type_code VARCHAR2) is
3737 select date_placed_in_service
3738 from fa_books
3739 where asset_id = p_group_asset_id
3740 and book_type_code = p_book_type_code
3741 and transaction_header_id_out is null;
3742
3743 l_group_dpis date;
3744 l_calling_fn varchar2(50) := 'fa_asset_val_pvt.validate_member_dpis';
3745
3746 BEGIN
3747
3748 open c_group_dpis(p_group_asset_Id, p_book_type_code);
3749 fetch c_group_dpis
3750 into l_group_dpis;
3751 close c_group_dpis;
3752
3753 if (p_date_placed_in_service < l_group_dpis) then
3754 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
3755 name => 'FA_INVALID_MEMBER_DPIS',
3756 token1 => 'DATE',
3757 value1 => l_group_dpis);
3758 return false;
3759 else
3760 return true;
3761 end if;
3762
3763 EXCEPTION
3764 WHEN OTHERS THEN
3765 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
3766 return false;
3767
3768 END validate_member_dpis;
3769
3770 FUNCTION validate_egy_prod_date (
3771 p_calendar_period_start_date IN DATE,
3772 p_transaction_date IN DATE,
3773 p_transaction_key IN VARCHAR2,
3774 p_rate_source_rule IN VARCHAR2,
3775 p_rule_name IN VARCHAR2,
3776 p_calling_fn IN VARCHAR2,
3777 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
3778
3779 l_calling_fn varchar2(50) := 'FA_ASSET_VAL_PVT.validate_egy_prod_date';
3780
3781 BEGIN
3782
3786
3783 if (p_log_level_rec.statement_level) then
3784 fa_debug_pkg.add(l_calling_fn, 'p_transaction_date', 'p_transaction_date');
3785 end if;
3787 --Bug# 7198185 - deleted condition to allow backdated addition for energy
3788
3789 if (p_log_level_rec.statement_level) then
3790 fa_debug_pkg.add(l_calling_fn, 'End', ' ');
3791 end if;
3792
3793 return true;
3794
3795 EXCEPTION
3796 WHEN OTHERS THEN
3797 if (p_log_level_rec.statement_level) then
3798 fa_debug_pkg.add(l_calling_fn, 'p_calendar_period_start_date', p_calendar_period_start_date);
3799 fa_debug_pkg.add(l_calling_fn, 'p_transaction_date', p_transaction_date);
3800 fa_debug_pkg.add(l_calling_fn, 'p_rate_source_rule', p_rate_source_rule);
3801 fa_debug_pkg.add(l_calling_fn, 'p_rule_name', p_rule_name);
3802 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION: OTHERS', sqlerrm);
3803 end if;
3804
3805 fa_srvr_msg.add_sql_error( calling_fn => l_calling_fn );
3806
3807 return FALSE;
3808 END validate_egy_prod_date;
3809
3810 -- Bug:5154035
3811 FUNCTION validate_reval_exists (
3812 p_book_type_code IN VARCHAR2,
3813 p_asset_Id IN NUMBER,
3814 p_calling_fn IN VARCHAR2,
3815 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3816 ) RETURN BOOLEAN IS
3817
3818 CURSOR c_reval_exists (l_asset_id NUMBER,
3819 l_book_type_code VARCHAR2) is
3820 select 'x'
3821 from FA_Transaction_Headers
3822 where Asset_ID = l_asset_id
3823 and Book_type_Code = l_book_type_code
3824 and Transaction_Type_Code = 'REVALUATION';
3825
3826 l_calling_fn varchar2(50) := 'fa_asset_val_pvt.validate_reval_exists';
3827 l_reval_exists varchar2(1);
3828
3829 BEGIN
3830
3831 open c_reval_exists(p_asset_id, p_book_type_code);
3832 fetch c_reval_exists into l_reval_exists;
3833
3834 if (c_reval_exists%NOTFOUND) then
3835 close c_reval_exists;
3836 return false;
3837 end if;
3838
3839 close c_reval_exists;
3840
3841 return true;
3842
3843 EXCEPTION
3844 WHEN OTHERS THEN
3845 if (p_log_level_rec.statement_level) then
3846 fa_debug_pkg.add(l_calling_fn, 'p_asset_id', p_asset_id);
3847 fa_debug_pkg.add(l_calling_fn, 'p_book_type_code', p_book_type_code);
3848 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION: OTHERS', sqlerrm);
3849 end if;
3850
3851 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn);
3852
3853 return false;
3854 END validate_reval_exists;
3855
3856 /* Japan Tax Phase3 -- Prevent transactions on assets in extended depreciation */
3857 FUNCTION validate_extended_asset (
3858 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
3859 p_asset_fin_rec_old IN FA_API_TYPES.asset_fin_rec_type,
3860 p_asset_fin_rec_adj IN FA_API_TYPES.asset_fin_rec_type,
3861 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3862 ) RETURN BOOLEAN IS
3863
3864 l_calling_fn varchar2(50) := 'FA_ASSET_VAL_PVT.validate_extended_asset';
3865 l_mrc_count number := 0;
3866
3867 BEGIN
3868
3869 if (p_log_level_rec.statement_level) then
3870 fa_debug_pkg.add(l_calling_fn, 'Enter ', p_asset_hdr_rec.asset_id);
3871 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.cost', p_asset_fin_rec_adj.cost);
3872 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.life_in_months', p_asset_fin_rec_adj.life_in_months);
3873 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.deprn_method_code',
3874 p_asset_fin_rec_adj.deprn_method_code);
3875 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.date_placed_in_service',
3876 p_asset_fin_rec_adj.date_placed_in_service);
3877 end if;
3878
3879 if (nvl(p_asset_fin_rec_adj.cost,0) <> 0) then
3880 fa_srvr_msg.add_message(
3881 calling_fn => l_calling_fn,
3882 name => 'FA_JP_COST_CHG_NOT_ALLOWED');
3883 return FALSE;
3884 end if;
3885
3886 if (p_asset_fin_rec_old.deprn_method_code <> nvl(p_asset_fin_rec_adj.deprn_method_code,
3887 p_asset_fin_rec_old.deprn_method_code)) then
3888 fa_srvr_msg.add_message(
3889 calling_fn => l_calling_fn,
3890 name => 'FA_JP_METHOD_CHG_NOT_ALLOWED');
3891 return FALSE;
3892 end if;
3893
3894 select count(*)
3895 into l_mrc_count
3896 from fa_mc_book_controls
3897 where book_type_code = p_asset_hdr_rec.book_type_code
3898 and enabled_flag = 'Y';
3899
3900 -- Cannot use extended deprn with MRC books.
3901 if (l_mrc_count > 0) then
3902
3903 fa_srvr_msg.add_message(
3904 calling_fn => l_calling_fn,
3905 name => 'FA_JP_MRC_NOT_ALLOWED');
3906
3907 return FALSE;
3908 end if;
3909
3910 -- prevent salvage value change
3911 if (nvl(p_asset_fin_rec_adj.percent_salvage_value, 0) <> 0 or
3912 nvl(p_asset_fin_rec_adj.salvage_value, 0) <> 0)then
3913 fa_srvr_msg.add_message(
3914 calling_fn => l_calling_fn,
3915 name => 'FA_JP_SALVAGE_CHG_NOT_ALLOWED');
3916 return FALSE;
3917 end if;
3918
3919 -- prevent deprn_limit change
3920 if (nvl(p_asset_fin_rec_adj.allowed_deprn_limit_amount, 0) <> 0 or
3921 nvl(p_asset_fin_rec_adj.allowed_deprn_limit, 0) <> 0) then
3922 fa_srvr_msg.add_message(
3923 calling_fn => l_calling_fn,
3924 name => 'FA_JP_LIMIT_CHG_NOT_ALLOWED');
3928 -- prevent dpis change
3925 return FALSE;
3926 end if;
3927
3929 if (p_asset_fin_rec_old.date_placed_in_service <> nvl(p_asset_fin_rec_adj.date_placed_in_service,
3930 p_asset_fin_rec_old.date_placed_in_service)) then
3931 fa_srvr_msg.add_message(
3932 calling_fn => l_calling_fn,
3933 name => 'FA_JP_DPIS_CHG_NOT_ALLOWED');
3934 return FALSE;
3935 end if;
3936
3937 if (p_log_level_rec.statement_level) then
3938 fa_debug_pkg.add(l_calling_fn, 'End', ' ');
3939 end if;
3940
3941 return true;
3942
3943 EXCEPTION
3944 WHEN OTHERS THEN
3945 if (p_log_level_rec.statement_level) then
3946 fa_debug_pkg.add(l_calling_fn, 'p_asset_hdr_rec.asset_id', p_asset_hdr_rec.asset_id);
3947 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.cost', p_asset_fin_rec_adj.cost);
3948 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.deprn_method_code',
3949 p_asset_fin_rec_adj.deprn_method_code);
3950 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION: OTHERS', sqlerrm);
3951 end if;
3952
3953 fa_srvr_msg.add_sql_error( calling_fn => l_calling_fn );
3954
3955 return FALSE;
3956 END validate_extended_asset;
3957
3958 /* Bug#7693266- To validate change of salvage_type or deprn_limit_type of group asset */
3959 FUNCTION validate_sal_deprn_sum (
3960 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
3961 p_asset_fin_rec_old IN FA_API_TYPES.asset_fin_rec_type,
3962 p_asset_fin_rec_adj IN FA_API_TYPES.asset_fin_rec_type,
3963 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null
3964 ) RETURN BOOLEAN IS
3965
3966 CURSOR c_mem_exists IS
3967 select 1
3968 from fa_books
3969 where group_asset_id = p_asset_hdr_rec.asset_id
3970 and book_type_code = p_asset_hdr_rec.book_type_code
3971 and transaction_header_id_out is null;
3972
3973 l_calling_fn varchar2(50) := 'FA_ASSET_VAL_PVT.validate_sal_deprn_sum';
3974
3975 l_dummy NUMBER;
3976 l_valid_parameter BOOLEAN := TRUE;
3977
3978 BEGIN
3979 /* Checking for salvage_type and deprn_limit_type changed to SUM for group asset with member */
3980
3981 if (((p_asset_fin_rec_adj.salvage_type = 'SUM') and
3982 (p_asset_fin_rec_adj.salvage_type <> nvl(p_asset_fin_rec_old.salvage_type,
3983 p_asset_fin_rec_adj.salvage_type))) or
3984 ((p_asset_fin_rec_adj.deprn_limit_type = 'SUM') and
3985 (p_asset_fin_rec_adj.deprn_limit_type <> nvl(p_asset_fin_rec_old.deprn_limit_type,
3986 p_asset_fin_rec_adj.deprn_limit_type)))) then
3987
3988 if (p_asset_fin_rec_adj.cost = 0) then
3989 OPEN c_mem_exists;
3990 FETCH c_mem_exists INTO l_dummy;
3991 CLOSE c_mem_exists;
3992
3993 if (l_dummy > 0) then
3994 fa_srvr_msg.add_message(
3995 calling_fn => l_calling_fn,
3996 name =>'FA_MEMBER_EXIST_IN_GROUP');
3997 l_valid_parameter := FALSE;
3998 else
3999 return TRUE;
4000 end if;
4001
4002 else
4003 l_valid_parameter := FALSE;
4004 end if;
4005
4006 if (not l_valid_parameter) then
4007 if (p_asset_fin_rec_adj.salvage_type = 'SUM') then
4008 fa_srvr_msg.add_message(
4009 calling_fn => l_calling_fn,
4010 name => 'FA_INVALID_PARAMETER',
4011 token1 => 'VALUE',
4012 value1 => p_asset_fin_rec_adj.salvage_type,
4013 token2 => 'PARAM',
4014 value2 => 'SALVAGE_TYPE',
4015 p_log_level_rec => p_log_level_rec);
4016 else
4017 fa_srvr_msg.add_message(
4018 calling_fn => l_calling_fn,
4019 name => 'FA_INVALID_PARAMETER',
4020 token1 => 'VALUE',
4021 value1 => p_asset_fin_rec_adj.deprn_limit_type,
4022 token2 => 'PARAM',
4023 value2 => 'DEPRN_LIMIT_TYPE',
4024 p_log_level_rec => p_log_level_rec);
4025 end if;
4026 return FALSE;
4027 end if;
4028
4029 end if; -- End of SUM loop
4030
4031 if (p_log_level_rec.statement_level) then
4032 fa_debug_pkg.add(l_calling_fn, 'End', ' ');
4033 end if;
4034
4035 return TRUE;
4036
4037 EXCEPTION
4038 WHEN OTHERS THEN
4039 if (p_log_level_rec.statement_level) then
4040 fa_debug_pkg.add(l_calling_fn, 'p_asset_hdr_rec.asset_id', p_asset_hdr_rec.asset_id);
4041 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.salvage_type', p_asset_fin_rec_adj.salvage_type);
4042 fa_debug_pkg.add(l_calling_fn, 'p_asset_fin_rec_adj.deprn_limit_type',
4043 p_asset_fin_rec_adj.deprn_limit_type);
4044 fa_debug_pkg.add(l_calling_fn, 'EXCEPTION: OTHERS', sqlerrm);
4045 end if;
4046
4047 fa_srvr_msg.add_sql_error( calling_fn => l_calling_fn );
4048
4049 return FALSE;
4050 END validate_sal_deprn_sum;
4051
4052 END FA_ASSET_VAL_PVT;
[Home] [Help]