[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_REVAL_VALIDATION
Source
1 PACKAGE BODY IGI_IAC_REVAL_VALIDATION AS
2 -- $Header: igiiarvb.pls 120.11.12000000.1 2007/08/01 16:18:26 npandya ship $
3 --===========================FND_LOG.START=====================================
4
5 g_state_level NUMBER;
6 g_proc_level NUMBER;
7 g_event_level NUMBER;
8 g_excep_level NUMBER;
9 g_error_level NUMBER;
10 g_unexp_level NUMBER;
11 g_path VARCHAR2(100);
12
13 --===========================FND_LOG.END=======================================
14
15 l_rec igi_iac_revaluation_rates%rowtype; -- create this for quicker access via sql navigator
16
17 function validate_period_counter ( fp_asset_id number
18 , fp_book_type_code varchar2
19 , fp_period_counter number
20 )
21 return boolean is
22 cursor c_not_valid is
23 select max(period_counter) period_counter
24 from fa_deprn_summary
25 where asset_id = fp_asset_id
26 and book_type_code = fp_book_type_code
27 ;
28 l_is_valid boolean ;
29 l_path varchar2(100);
30 begin
31
32 l_is_valid := false;
33 l_path := g_path||'validate_period_counter';
34
35 /* if the user attempts to process an asset which has had ocassional revaluation
36 more than once per period, then return false */
37 l_is_valid := false;
38 for l_nv in c_not_valid loop
39 if l_nv.period_counter > fp_period_counter then
40 return false;
41 else
42 return true;
43 end if;
44 end loop;
45
46 return l_is_valid;
47 exception when others then
48 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
49 return false;
50 end;
51
52
53 function validate_cost ( fp_asset_id number
54 , fp_book_type_code varchar2
55 , fp_revaluation_id number
56 )
57 return boolean is
58 cursor c_not_valid is
59 select revaluation_factor
60 from igi_iac_reval_asset_rules
61 where asset_id = fp_asset_id
62 and book_type_code = fp_book_type_code
63 and revaluation_id = fp_revaluation_id;
64 l_path varchar2(100);
65 begin
66 l_path := g_path||'validate_cost';
67 for l_nv in c_not_valid loop
68 if l_nv.revaluation_factor = 1 then
69 return false;
70 else
71 return true;
72 end if;
73 end loop;
74 exception when others then
75 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
76 return false;
77 end;
78
79
80 function validate_fully_retired ( fp_asset_id number
81 , fp_book_type_code varchar2
82 )
83 return boolean is
84 cursor c_not_valid is
85 select 'x' valid_rec
86 from fa_books
87 where asset_id = fp_asset_id
88 and book_type_code = fp_book_type_code
89 and cost = 0
90 and transaction_header_id_out is null
91 ;
92 l_is_valid boolean;
93 l_path varchar2(100);
94 begin
95
96 l_is_valid := false;
97 l_path := g_path||'validate_fully_retired';
98
99 for l_nv in c_not_valid loop
100 return false;
101 end loop;
102
103 return true;
104 exception when others then
105 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
106 return false;
107 end;
108
109
110 function validate_reval_type ( fp_asset_id number
111 , fp_revaluation_id number
112 , fp_book_type_code varchar2
113 , fp_reval_type varchar2
114 , fp_period_counter number
115 )
116 return boolean is
117
118 -- Bug 3013442 (Tpradhan) ... Begin
119 -- Added the condition to check for status not OBSOLETE to the cursor below
120
121 cursor c_not_valid is
122 select 'x'
123 from igi_iac_transaction_headers
124 where asset_id = fp_asset_id
125 and book_type_code = fp_book_type_code
126 and period_counter = fp_period_counter
127 and mass_Reference_id <> fp_revaluation_id
128 and revaluation_type_flag in ( 'O', 'P' )
129 and fp_reval_type = 'O'
130 and adjustment_status <> 'OBSOLETE'
131 ;
132
133 -- Bug 3013442 (Tpradhan) ... End
134
135 l_is_valid boolean;
136 l_path varchar2(100);
137 begin
138
139 l_is_valid := true;
140 l_path := g_path||'validate_reval_type';
141
142 /* if the user attempts to process an asset which has had ocassional revaluation
143 more than once per period, then return false */
144 l_is_valid := true;
145 for l_nv in c_not_valid loop
146 l_is_valid := false;
147 exit;
148 end loop;
149
150 return l_is_valid;
151 exception when others then
152 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
153 return false;
154 end;
155
156 function validate_fa_revals ( fp_asset_id number
157 , fp_book_type_code varchar2
158 )
159 return boolean is
160 begin
161 if igi_iac_common_utils.Any_Reval_in_Corp_Book ( P_book_type_Code => fp_book_type_code ,
162 P_Asset_id => fp_asset_id
163 )
164 then
165 return false;
166 else
167 return true;
168 end if;
169 exception when others then
170 return true;
171 end;
172
173 function validate_new_fa_txns ( fp_asset_id number
174 , fp_book_type_code varchar2
175 , fp_period_counter number
176 )
177 return boolean is
178 l_path varchar2(100);
179 begin
180
181 l_path := g_path||'validate_new_fa_txns';
182
183 if igi_iac_common_utils.Any_Txns_In_Open_Period( P_book_type_Code => fp_book_type_code ,
184 P_Asset_id => fp_asset_id
185 )
186 then
187 return false;
188 else
189 return true;
190 end if;
191 exception when others then
192 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
193 return false;
194 end;
195
196
197 function not_retired_in_curr_year ( fp_asset_id number
198 , fp_book_type_code varchar2
199 )
200 return boolean is
201 l_retirements varchar2(1);
202 l_path varchar2(100);
203 begin
204
205 l_retirements := 'X';
206 l_path := g_path||'not_retired_in_curr_year';
207
208 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Begin retirements check');
209 if not igi_iac_common_utils.any_ret_in_curr_yr(p_book_type_code => fp_book_type_code
210 ,p_asset_id => fp_asset_id
211 ,p_retirements => l_retirements
212 )
213 then
214 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Error retirements check');
215 end if;
216
217 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Retirements '|| l_retirements);
218
219 if l_retirements = 'Y' then
220 return false;
221 else
222 return true;
223 end if;
224 exception when others then
225 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
226 return false;
227 end;
228
229 function not_adjusted_asset ( fp_asset_id number
230 , fp_book_type_code varchar2
231 )
232 return boolean is
233 begin
234 return true;
235 /****
236 if not igi_iac_common_utils.ANY_ADJ_IN_BOOK(p_book_type_code => fp_book_type_code
237 ,p_asset_id => fp_asset_id
238 )
239 then
240 return true;
241 end if;
242 ***/
243 return false;
244 exception when others then
245 return true;
246 end;
247
248 FUNCTION Validate_Multiple_Previews( fp_asset_id number
249 , fp_book_type_code varchar2
250 , fp_period_counter number
251 , fp_revaluation_id number
252 , fp_preview_reval_id OUT NOCOPY number
253 )
254 RETURN BOOLEAN IS
255 CURSOR c_preview_in_curr_period IS
256 SELECT mass_reference_id revaluation_id
257 FROM igi_iac_transaction_headers
258 WHERE asset_id = fp_asset_id
259 AND book_type_code = fp_book_type_code
260 AND period_counter = fp_period_counter
261 AND transaction_type_code = 'REVALUATION'
262 AND adjustment_status = 'PREVIEW'
263 AND mass_Reference_id <> fp_revaluation_id;
264
265 l_path VARCHAR2(100);
266 l_muliple_previews BOOLEAN;
267
268 BEGIN
269 l_path := g_path||'is_Asset_in_Preview';
270 l_muliple_previews := FALSE;
271 FOR l_preview IN c_preview_in_curr_period LOOP
272 l_muliple_previews := TRUE;
273 fp_preview_reval_id := l_preview.revaluation_id;
274 EXIT;
275 END LOOP;
276
277 IF l_muliple_previews THEN
278 RETURN FALSE;
279 ELSE
280 RETURN TRUE;
281 END IF;
282 EXCEPTION WHEN others THEN
283 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
284 RETURN FALSE;
285 END Validate_Multiple_Previews;
286
287 function validate_asset ( fp_asset_id number
288 , fp_book_type_code varchar2
289 , fp_period_counter number
290 , fp_reval_type varchar2
291 , fp_revaluation_id number
292 )
293 return boolean is
294 begin
295 if not_adjusted_asset ( fp_asset_id => fp_asset_id
296 , fp_book_type_code => fp_book_type_code
297 )
298 and not_retired_in_curr_year ( fp_asset_id => fp_asset_id
299 , fp_book_type_code => fp_book_type_code
300 )
301 and validate_new_fa_txns ( fp_asset_id => fp_asset_id
302 , fp_book_type_code => fp_book_type_code
303 , fp_period_counter => fp_period_counter
304 )
305 and validate_fa_revals ( fp_asset_id => fp_asset_id
306 , fp_book_type_code => fp_book_type_code
307 )
308 and validate_reval_type ( fp_asset_id => fp_asset_id
309 , fp_revaluation_id => fp_revaluation_id
310 , fp_book_type_code => fp_book_type_code
311 , fp_reval_type => fp_reval_type
312 , fp_period_counter => fp_period_counter
313 )
314 then
315 return true;
316 else
317 return false;
318 end if;
319
320 end;
321
322 function validate_asset ( fp_asset_id number
323 , fp_book_type_code varchar2
324 , fp_period_counter number
325 , fp_reval_type varchar2
326 , fp_revaluation_id number
327 , fp_exceptions IN OUT NOCOPY IGI_IAC_TYPES.iac_reval_exceptions
328 , fp_exceptions_idx IN OUT NOCOPY IGI_IAC_TYPES.iac_reval_exceptions_idx
329 )
330 return boolean is
331 l_success_ct number;
332 l_failure_ct number;
333 l_asset_num varchar2(100);
334 fp_exceptions_old IGI_IAC_TYPES.iac_reval_exceptions;
335 fp_exceptions_idx_old IGI_IAC_TYPES.iac_reval_exceptions_idx;
336 l_path varchar2(100);
337 l_preview_reval_id NUMBER;
338
339 procedure add_exception (p_mesg_code in varchar2) is
340 l_mesg varchar2(2000);
341 l_path varchar2(100);
342 begin
343
344 l_path := g_path||'add_exception';
345
346 igi_iac_debug_pkg.debug_other_string(g_excep_level,l_path,'+adding exception');
347 begin
348 fnd_message.set_name( 'IGI', p_mesg_code );
349 igi_iac_debug_pkg.debug_other_msg(g_error_level,l_path,FALSE);
350 l_mesg := fnd_message.get;
351 exception when others then
352 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
353 l_mesg := p_mesg_code;
354 end;
355 if l_mesg is null then
356 l_mesg := p_mesg_code;
357 end if;
358 igi_iac_debug_pkg.debug_other_string(g_excep_level,l_path,'+mesg is '|| l_mesg);
359 fp_exceptions_idx := nvl(fp_exceptions_idx,0) + 1;
360 fp_exceptions ( fp_exceptions_idx).asset_id := fp_asset_id;
361 fp_exceptions ( fp_exceptions_idx).book_type_code := fp_book_type_code;
362 fp_exceptions ( fp_exceptions_idx).reason := l_mesg ;
363 end;
364
365 procedure add_exception (p_mesg_code in varchar2,
366 p_token in number) is
367 l_mesg varchar2(2000);
368 l_path varchar2(100);
369 begin
370
371 l_path := g_path||'add_exception';
372
373 igi_iac_debug_pkg.debug_other_string(g_excep_level,l_path,'+adding exception');
374 begin
375 fnd_message.set_name( 'IGI', p_mesg_code );
376 fnd_message.set_token('REVALUATION_ID',p_token);
377 igi_iac_debug_pkg.debug_other_msg(g_error_level,l_path,FALSE);
378 l_mesg := fnd_message.get;
379 exception when others then
380 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
381 l_mesg := p_mesg_code;
382 end;
383 if l_mesg is null then
384 l_mesg := p_mesg_code;
385 end if;
386 igi_iac_debug_pkg.debug_other_string(g_excep_level,l_path,'+mesg is '|| l_mesg);
387 fp_exceptions_idx := nvl(fp_exceptions_idx,0) + 1;
388 fp_exceptions ( fp_exceptions_idx).asset_id := fp_asset_id;
389 fp_exceptions ( fp_exceptions_idx).book_type_code := fp_book_type_code;
390 fp_exceptions ( fp_exceptions_idx).reason := l_mesg ;
391 end;
392
393 begin
394
395 l_success_ct := 0;
396 l_failure_ct := 0;
397 l_path := g_path||'validate_asset';
398
399 fp_exceptions_old := fp_exceptions;
400 fp_exceptions_idx_old := fp_exceptions_idx;
401
402 select asset_number
403 into l_asset_num
404 from fa_additions
405 where asset_id = fp_asset_id
406 ;
407
408 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Validating the asset '|| l_asset_num);
409
410 if validate_period_counter
411 ( fp_asset_id => fp_asset_id
412 , fp_book_type_code => fp_book_type_code
413 , fp_period_counter => fp_period_counter
414 )
415 then
416 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+The period counter is ok');
417 l_success_ct := l_success_ct + 1;
418 else
419 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
420 add_exception('IGI_IAC_REVAL_EXCEP_PERIOD_CTR');
421 l_failure_ct := l_failure_ct + 1;
422 end if;
423
424 if not_adjusted_asset ( fp_asset_id => fp_asset_id
425 , fp_book_type_code => fp_book_type_code
426 )
427 then
428 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+The asset has not been adjusted');
429 l_success_ct := l_success_ct + 1;
430 else
431 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
432 add_exception('IGI_IAC_REVAL_EXCEP_ASSET_ADJ');
433 l_failure_ct := l_failure_ct + 1;
434 end if;
435
436 if validate_cost( fp_asset_id => fp_asset_id
437 , fp_book_type_code => fp_book_type_code
438 , fp_revaluation_id => fp_revaluation_id
439 )
440 then
441 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+The current cost and new cost of the asset are different');
442 l_success_ct := l_success_ct + 1;
443 else
444 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
445 add_exception('IGI_IAC_REVAL_EXCEP_SAME_COSTS');
446 l_failure_ct := l_failure_ct + 1;
447 end if;
448
449 if not_retired_in_curr_year ( fp_asset_id => fp_asset_id
450 , fp_book_type_code => fp_book_type_code
451 )
452 then
453 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+The asset has no pending retirements/reinstatements');
454 l_success_ct := l_success_ct + 1;
455 else
456 /*For bug no 2647561 changed the name of the message to the right name*/
457 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
458 add_exception('IGI_IAC_REVAL_EXCEP_PEND_TXNS');
459 l_failure_ct := l_failure_ct + 1;
460 end if;
461
462 if validate_fully_retired ( fp_asset_id => fp_asset_id
463 , fp_book_type_code => fp_book_type_code
464 )
465 then
466 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+The asset has no pending retirements/reinstatements');
467 l_success_ct := l_success_ct + 1;
468 else
469 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
470 add_exception('IGI_IAC_REVAL_EXCEP_PEND_TXNS');
471 l_failure_ct := l_failure_ct + 1;
472 end if;
473
474 if validate_new_fa_txns ( fp_asset_id => fp_asset_id
475 , fp_book_type_code => fp_book_type_code
476 , fp_period_counter => fp_period_counter
477 )
478 then
479 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'the asset has no new transactions that would affect revaluation');
480 l_success_ct := l_success_ct + 1;
481 else
482 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
483 add_exception('IGI_IAC_REVAL_EXCEP_NEW_TXNS');
484 l_failure_ct := l_failure_ct + 1;
485 end if;
486
487 if validate_fa_revals ( fp_asset_id => fp_asset_id
488 , fp_book_type_code => fp_book_type_code
489 )
490 then
491 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'the asset has no CORE FA revaluations');
492 l_success_ct := l_success_ct + 1;
493 else
494 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
495 add_exception('IGI_IAC_REVAL_EXCEP_PEND_TXNS');
496 l_failure_ct := l_failure_ct + 1;
497 end if;
498
499 if validate_reval_type ( fp_asset_id => fp_asset_id
500 , fp_revaluation_id => fp_revaluation_id
501 , fp_book_type_code => fp_book_type_code
502 , fp_reval_type => fp_reval_type
503 , fp_period_counter => fp_period_counter
504 )
505 then
506 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'the revaluation type is valid');
507 l_success_ct := l_success_ct + 1;
508 else
509 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
510 add_exception('IGI_IAC_REVAL_EXCEP_REVAL_TYPE');
511 l_failure_ct := l_failure_ct + 1;
512 end if;
513
514 IF Validate_Multiple_Previews ( fp_asset_id => fp_asset_id
515 , fp_revaluation_id => fp_revaluation_id
516 , fp_book_type_code => fp_book_type_code
517 , fp_period_counter => fp_period_counter
518 , fp_preview_reval_id => l_preview_reval_id
519 )
520 then
521 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'asset not in any other preview revaluation');
522 l_success_ct := l_success_ct + 1;
523 else
524 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling Add Exception');
525 add_exception('IGI_IAC_REVAL_EXCEP_PREVIEW',l_preview_reval_id);
526 l_failure_ct := l_failure_ct + 1;
527 end if;
528
529 if l_failure_ct = 0 then
530 if l_success_ct = 0 then
531 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+validation did not do anything');
532 else
533 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+validation checks passed');
534 end if;
535 return true ;
536 else
537 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'+validation checks failed');
538 return false;
539 end if;
540
541 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'End validation of the asset');
542 exception when others then
543 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
544 fp_exceptions := fp_exceptions_old;
545 fp_exceptions_idx := fp_exceptions_idx_old;
546 return false; -- Bug No. 2647561 (Tpradhan) - Replaced "Raise" with "return false" since Raise just raises the OTHERS exception which then propagates
547 -- to Do_Revaluation_Asset where the exception is handled and that procedure returns TRUE instead of FALSE
548
549 end;
550
551 BEGIN
552
553 --===========================FND_LOG.START=====================================
554
555 g_state_level := FND_LOG.LEVEL_STATEMENT;
556 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
557 g_event_level := FND_LOG.LEVEL_EVENT;
558 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
559 g_error_level := FND_LOG.LEVEL_ERROR;
560 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
561 g_path := 'IGI.PLSQL.igiiarvb.IGI_IAC_REVAL_VALIDATION.';
562
563 --===========================FND_LOG.END=====================================
564
565 END;