[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_REVAL_CONCURRENT
Source
1 PACKAGE BODY IGI_IAC_REVAL_CONCURRENT AS
2 -- $Header: igiiarcb.pls 120.11.12000000.2 2007/10/03 13:16:43 npandya ship $
3
4 --===========================FND_LOG.START=====================================
5
6 g_state_level NUMBER ;
7 g_proc_level NUMBER ;
8 g_event_level NUMBER ;
9 g_excep_level NUMBER ;
10 g_error_level NUMBER ;
11 g_unexp_level NUMBER ;
12 g_path VARCHAR2(100) ;
13
14 --===========================FND_LOG.END=======================================
15
16 l_rec igi_iac_revaluation_rates%rowtype; -- create this for quicker access via sql navigator
17 /*
18 -- Commit if not in debug mode.
19 */
20 procedure do_commit is
21 begin
22 if IGI_IAC_REVAL_UTILITIES.debug then
23 rollback;
24 else
25 commit;
26 end if;
27 end;
28
29 /*
30 -- Submit Revaluation Report
31 */
32 procedure submit_revaluation_report ( p_revaluation_id in number
33 , p_revaluation_mode in varchar2
34 )
35 is
36 cursor c_reval is
37 select revaluation_id, book_type_code, revaluation_period
38 from igi_iac_revaluations
39 where revaluation_id = p_revaluation_id
40 ;
41 l_report_request_id number;
42 l_retcode number;
43 l_errbuf varchar2(2000);
44 l_reval_res c_reval%ROWTYPE;
45 l_path varchar2(100) ;
46 begin
47 l_path := g_path||'submit_revaluation_report';
48
49 /* Sekhar The request i ssubmitted only for preview mode*/
50 if p_revaluation_mode not in ( 'P' ) then
51 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Do not submit_revaluation_report');
52 return;
53 end if;
54
55 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'submit_revaluation_report');
56
57 for l_rev in c_reval loop
58 l_reval_res := l_rev;
59 end loop;
60
61 /* Sekhar The request is submitted only for preview mode
62 commented the call for submmiting the asset balance report
63 now only preview report is called*/
64 /* igi_iac_submit_Asset_balance.submit_report
65 ( ERRBUF => l_errbuf,
66 RETCODE => l_retcode ,
67 p_book_type_code => l_reval_res.book_type_code ,
68 p_period_counter => l_reval_res.revaluation_period ,
69 p_mode => 'A' ,
70 p_category_struct_id => null ,
71 p_category_id => null ,
72 p_called_from => 'IGIIAIAR' ) ;*/
73
74 /*Summary preview report*/
75 l_report_Request_id := FND_REQUEST.SUBMIT_REQUEST ( 'IGI'
76 , 'IGIIARPS'
77 , null
78 , null
79 , FALSE -- Is a sub request
80 , 'P_BOOK_TYPE_CODE='||l_reval_res.book_type_code
81 , 'P_REVALUATION_ID='||p_revaluation_id
82 , 'P_PERIOD_COUNTER='||l_reval_res.revaluation_period
83 );
84 igi_iac_debug_pkg.debug_other_string(g_event_level,l_path,'Asset Revlaution Preview Summary report .... ');
85 IF Not l_report_Request_id > 0 Then
86 igi_iac_debug_pkg.debug_other_string(g_excep_level,l_path,'Error in Asset Revlaution Preview Summary report .... ');
87 END IF;
88 /*Detail preview report */
89
90 l_report_Request_id := FND_REQUEST.SUBMIT_REQUEST ( 'IGI'
91 , 'IGIIARPR'
92 , null
93 , null
94 , FALSE -- Is a sub request
95 , 'P_BOOK_TYPE_CODE='||l_reval_res.book_type_code
96 , 'P_REVALUATION_ID='||p_revaluation_id
97 , 'P_PERIOD_COUNTER='||l_reval_res.revaluation_period
98 );
99 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset Revlaution Preveiw Detail report .... ');
100 IF Not l_report_Request_id > 0 Then
101 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Error in Asset Revlaution Preview Detail report .... ');
102 END IF;
103
104 commit;
105
106 return;
107 exception when others then
108 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
109 rollback;
110 end;
111
112 /*
113 -- convert from preview to live
114 */
115 function preview_mode_hist_transform ( fp_revaluation_id in number
116 , fp_book_type_code in varchar2
117 , fp_period_counter in number
118 )
119 return boolean is
120
121 /*
122 -- Revaluation form must set the status to 'PREVIEW' before the 'NEW' or 'PREVIEWED'
123 -- record is processed again.
124 */
125 cursor c_revaluations is
126 select iir.revaluation_id, iir.book_type_code
127 from igi_iac_revaluations iir
128 where iir.revaluation_id = fp_revaluation_id
129 and iir.book_type_code = fp_book_type_code
130 ;
131
132 cursor c_reval_categories (cp_revaluation_id in number
133 , cp_book_type_code in varchar2
134 ) is
135 select category_id
136 from igi_iac_reval_categories
137 where revaluation_id = cp_revaluation_id
138 and book_type_code = cp_book_type_code
139 and nvl(select_category,'X') = 'Y';
140
141 cursor c_get_assets ( cp_revaluation_id in number
142 , cp_book_type_code in varchar2
143 , cp_category_id in number
144 ) is
145 select ac.asset_id, ac.revaluation_type, ac.revaluation_factor, fadd.asset_number
146 from igi_iac_reval_asset_rules ac, fa_additions fadd
147 where ac.revaluation_id = cp_revaluation_id
148 and ac.book_type_code = cp_book_type_code
149 and ac.category_id = cp_category_id
150 and ac.asset_id = fadd.asset_id
151 and nvl(ac.selected_for_reval_flag,'X') = 'Y'
152 and exists ( select 1
153 from igi_iac_transaction_headers
154 where asset_id = ac.asset_id
155 and book_type_code = ac.book_type_code
156 and mass_reference_id = ac.revaluation_id
157 and adjustment_status = 'PREVIEW'
158 )
159 ;
160 l_success_ct number ;
161 l_failure_ct number ;
162 l_path varchar2(100) ;
163
164 -- bulk changes
165 TYPE asset_id_tbl_type IS TABLE OF IGI_IAC_REVAL_ASSET_RULES.ASSET_ID%TYPE
166 INDEX BY BINARY_INTEGER;
167 TYPE reval_type_tbl_type IS TABLE OF IGI_IAC_REVAL_ASSET_RULES. REVALUATION_TYPE%TYPE
168 INDEX BY BINARY_INTEGER;
169 TYPE reval_factor_tbl_type IS TABLE OF IGI_IAC_REVAL_ASSET_RULES.REVALUATION_FACTOR%TYPE
170 INDEX BY BINARY_INTEGER;
171 TYPE asset_no_tbl_type IS TABLE OF FA_ADDITIONS.ASSET_NUMBER%TYPE
172 INDEX BY BINARY_INTEGER;
173
174 l_asset_id asset_id_tbl_type;
175 l_reval_type reval_type_tbl_type;
176 l_reval_factor reval_factor_tbl_type;
177 l_asset_no asset_no_tbl_type;
178
179 l_loop_count number;
180 l_event_id number; --Added for SLA uptake
181
182 --bulk fetch changes
183
184 begin
185 l_success_ct := 0;
186 l_failure_ct := 0;
187 l_path := g_path||'preview_mode_hist_transform';
188
189 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin preview_mode_hist_transform');
190 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Status '|| IGI_IAC_TYPES.gc_running_status);
191
192 savepoint sp;
193 for l_reval in c_revaluations loop -- get the revaluation for preview
194 -- found record to process.
195 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>found revaluation record to process');
196 for l_reval_cats in c_reval_categories -- get the category information
197 ( cp_revaluation_id => l_reval.revaluation_id
198 , cp_book_type_code => l_reval.book_type_code )
199 loop
200 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>found revaluation category record to process '
201 || l_reval_cats.category_id);
202
203 /* for l_assets in c_assets -- get assets
204 ( cp_revaluation_id => l_reval.revaluation_id
205 , cp_book_type_code => l_reval.book_type_code
206 , cp_category_id => l_reval_cats.category_id
207 )*/
208 -- bulk fetch changes
209 OPEN c_get_assets( cp_revaluation_id => l_reval.revaluation_id
210 , cp_book_type_code => l_reval.book_type_code
211 , cp_category_id => l_reval_cats.category_id
212 );
213 FETCH c_get_assets BULK COLLECT INTO
214 l_asset_id,
215 l_reval_type,
216 l_reval_factor,
217 l_asset_no;
218 CLOSE c_get_assets;
219
220 FOR l_loop_count IN 1.. l_asset_id.count
221 loop
222 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>found asset record to process : asset number '|| l_asset_no(l_loop_count) );
223 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> asset id '|| l_asset_id(l_loop_count));
224 --
225 -- update balance information
226 --
227 if IGI_IAC_REVAL_CRUD.update_balances
228 ( fp_reval_id => l_reval.revaluation_id
229 , fp_asset_id => l_asset_id(l_loop_count)
230 , fp_period_counter => fp_period_counter
231 , fp_book_type_code => l_reval.book_type_code
232 )
233 then
234 l_success_ct := l_success_ct + 1;
235 else
236 l_failure_ct := l_failure_ct + 1;
237 end if;
238 --
239 -- Adjustment_status of igi_iac_transaction_headers is updated
240 -- from 'PREVIEW' to 'RUN'.
241 --
242 IF IGI_IAC_REVAL_CRUD.adjustment_status_to_run
243 ( fp_reval_id => l_reval.revaluation_id
244 , fp_asset_id => l_asset_id(l_loop_count))
245 then
246 l_success_ct := l_success_ct + 1;
247 else
248 l_failure_ct := l_failure_ct + 1;
249 end if;
250
251 IF IGI_IAC_REVAL_CRUD.allow_transfer_to_gl
252 ( fp_reval_id => l_reval.revaluation_id
253 , fp_book_type_code => l_reval.book_type_code
254 , fp_asset_id => l_asset_id(l_loop_count)
255 )
256 then
257 l_success_ct := l_success_ct + 1;
258 else
259 l_failure_ct := l_failure_ct + 1;
260 end if;
261
262 declare
263 l_adjustment_id number;
264 begin
265 /* here the assumption is that the latest adjustment id
266 belongs to revaluation
267 */
268 begin
269 select adjustment_id
270 into l_adjustment_id
271 from igi_iac_transaction_headers
272 where book_type_code = l_reval.book_type_code
273 and asset_id = l_asset_id(l_loop_count)
274 and adjustment_id_out is null
275 ;
276 exception when no_data_found then
277 l_adjustment_id := -1;
278 end;
279
280 if l_adjustment_id = -1 then
281 l_failure_ct := l_failure_ct + 1;
282 else
283 if not igi_iac_reval_crud.update_reval_rates
284 ( fp_adjustment_id => l_adjustment_id )
285 then
286 l_failure_ct := l_failure_ct + 1;
287 else
288 l_success_ct := l_success_ct + 1;
289 end if;
290 end if;
291
292 exception when others then null;
293 end;
294
295 end loop; -- get assets
296
297 end loop; -- get the category information
298
299
300 /* Added for SLA uptake.
301 Following code will create SLA event and stamp it in revaluation and adjustment tables*/
302
303 IF l_failure_ct = 0 then
304 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling event capture routine');
305 If IGI_IAC_XLA_EVENTS_PKG.create_revaluation_event(l_reval.revaluation_id,l_event_id) then
306 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Event created successfully, Event_id = ' ||l_event_id);
307 else
308 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Event creation failed');
309 l_failure_ct := l_failure_ct + 1;
310 end if;
311 End if;
312
313 /* Added For SLA uptake*/
314 IF l_failure_ct = 0 then
315 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_completed ( l_reval.revaluation_id,l_event_id )
316 THEN
317 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>failed reval_status_to_completed');
318 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>not preview_mode_hist_transform');
319 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end preview_mode_hist_transform');
320 return false;
321 ELSE
322 --Stamp sla event to tables
323 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>start stamp_sla_event');
324 IF NOT IGI_IAC_REVAL_CRUD.stamp_sla_event ( l_reval.revaluation_id,
325 l_reval.book_type_code,
326 l_event_id)
327 THEN
328 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>failed stamp_sla_event');
329 return false;
330 end if;
331 --Stamp sla event to tables
332 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>success reval_status_to_run');
333 END IF;
334 ELSE
335 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_failed_run ( l_reval.revaluation_id )
336 THEN
337 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>failed reval_status_to_run');
338 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>not preview_mode_hist_transform');
339 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end preview_mode_hist_transform');
340 return false;
341 ELSE
342 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>success reval_status_to_failed_run');
343 END IF;
344 END IF;
345
346 /* Added for SLA uptake.
347 Following code will delete SLA event in case of failure*/
348 if l_failure_ct = 0 and l_success_ct > 0 then
349 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Revaluation is complete...');
350 else
351 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Calling event deletion routine');
352 If IGI_IAC_XLA_EVENTS_PKG.delete_revaluation_event(l_reval.revaluation_id) then
353 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Event deleted successfully');
354 else
355 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Event deletion failed');
356 end if;
357 end if;
358 /* Added For SLA uptake*/
359
360 end loop; -- get the revaluation for preview
361
362 if l_failure_ct = 0 and l_success_ct > 0 then
363 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>preview_mode_hist_transform');
364 else
365 rollback to sp;
366 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>not preview_mode_hist_transform');
367 return false;
368 end if;
369
370 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end preview_mode_hist_transform');
371
372 return true;
373 end;
374 /*
375 -- Generate data in preview mode
376 */
377
378 function preview_mode_hist_generate ( fp_revaluation_id in number
379 , fp_book_type_code in varchar2
380 , fp_period_counter in number
381 , fp_wait_request_id in number
382 )
383 return boolean is
384
385 /*
386 -- Revaluation form must set the status to 'PREVIEW' before the 'NEW' or 'PREVIEWED'
387 -- record is processed again.
388 */
389 l_number number ;
390 cursor c_revaluations is
391 select iir.revaluation_id, iir.book_type_code
392 from igi_iac_revaluations iir
393 where iir.revaluation_id = fp_revaluation_id
394 and iir.book_type_code = fp_book_type_code
395 -- and upper(iir.status) = IGI_IAC_TYPES.gc_previewed_status
396 ;
397
398 cursor c_reval_categories (cp_revaluation_id in number
399 , cp_book_type_code in varchar2
400 ) is
401 select category_id
402 from igi_iac_reval_categories
403 where revaluation_id = cp_revaluation_id
404 and book_type_code = cp_book_type_code
405 and nvl(select_category,'X') = 'Y'
406 ;
407 -- modify this later to ensure selected categories are retrieved
408
409 cursor c_get_assets ( cp_revaluation_id in number
410 , cp_book_type_code in varchar2
411 , cp_category_id in number
412 ) is
413 select r.asset_id, r.revaluation_type, r.revaluation_factor, fadd.asset_number
414 from igi_iac_reval_asset_rules r, fa_additions fadd
415 where r.revaluation_id = cp_revaluation_id
416 and r.book_type_code = cp_book_type_code
417 and r.category_id = cp_category_id
418 and nvl(r.selected_for_reval_flag,'X') = 'Y'
419 and r.asset_id = fadd.asset_id
420 and not exists ( select 'x'
421 from igi_iac_transaction_headers
422 where asset_id = r.asset_id
423 and book_type_code = r.book_type_code
424 and mass_reference_id = r.revaluation_id
425 and transaction_type_code = 'REVALUATION'
426 )
427 ;
428 l_failure_ct number ;
429 l_success_ct number ;
430
431 l_path varchar2(100) ;
432
433 l_reval_messages IGI_IAC_TYPES.iac_reval_mesg;
434 l_reval_messages_idx IGI_IAC_TYPES.iac_reval_mesg_idx ;
435 l_reval_exceptions IGI_IAC_TYPES.iac_reval_exceptions;
436 l_reval_exceptions_idx IGI_IAC_TYPES.iac_reval_exceptions_idx ;
437
438 /* Bulk Fetch */
439 TYPE asset_id_tbl_type IS TABLE OF IGI_IAC_REVAL_ASSET_RULES.ASSET_ID%TYPE
440 INDEX BY BINARY_INTEGER;
441 TYPE reval_type_tbl_type IS TABLE OF IGI_IAC_REVAL_ASSET_RULES. REVALUATION_TYPE%TYPE
442 INDEX BY BINARY_INTEGER;
443 TYPE reval_factor_tbl_type IS TABLE OF IGI_IAC_REVAL_ASSET_RULES.REVALUATION_FACTOR%TYPE
444 INDEX BY BINARY_INTEGER;
445 TYPE asset_no_tbl_type IS TABLE OF FA_ADDITIONS.ASSET_NUMBER%TYPE
446 INDEX BY BINARY_INTEGER;
447
448 l_asset_id asset_id_tbl_type;
449 l_reval_type reval_type_tbl_type;
450 l_reval_factor reval_factor_tbl_type;
451 l_asset_no asset_no_tbl_type;
452
453 l_loop_count number;
454
455 /* Bulk Fetch */
456 begin
457 l_number := fp_revaluation_id;
458 l_failure_ct := 0;
459 l_success_ct := 0;
460 l_path := g_path||'preview_mode_hist_generate';
461 l_reval_messages_idx := 1;
462 l_reval_exceptions_idx := 1;
463
464 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'begin preview_mode_hist_generate');
465 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'status '|| IGI_IAC_TYPES.gc_previewed_status);
466
467 savepoint sp;
468 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+after savepoint');
469 for l_reval in c_revaluations loop -- get the revaluation for preview
470
471 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>found revaluation record to process');
472 for l_reval_cats in c_reval_categories -- get the category information
473 ( cp_revaluation_id => l_reval.revaluation_id
474 , cp_book_type_code => l_reval.book_type_code )
475 loop
476 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>found revaluation category record to process '
477 || l_reval_cats.category_id);
478 /* Bulk fetch*/
479 OPEN c_get_assets ( cp_revaluation_id => l_reval.revaluation_id
480 , cp_book_type_code => l_reval.book_type_code
481 , cp_category_id => l_reval_cats.category_id
482 );
483 FETCH c_get_assets BULK COLLECT INTO
484 l_asset_id,
485 l_reval_type,
486 l_reval_factor,
487 l_asset_no;
488 CLOSE c_get_assets;
489
490
491 /* for l_assets in c_assets -- get assets
492 ( cp_revaluation_id => l_reval.revaluation_id
493 , cp_book_type_code => l_reval.book_type_code
494 , cp_category_id => l_reval_cats.category_id
495 )*/
496
497 FOR l_loop_count IN 1.. l_asset_id.count
498 LOOP
499
500
501 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>found asset record to process '|| l_asset_id(l_loop_count));
502 -- now call the reval wrapper!
503 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- ------------------------------------------- ');
504 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- Parameters for do_revaluation_asset ');
505 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- ------------------------------------------- ');
506 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- Revaluation id : '|| l_reval.revaluation_id );
507 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- Book Type code : '|| l_reval.book_type_code);
508 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- Reval mode : P ');
509 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- Period counter : '|| fp_period_counter);
510 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- calling program: IGIIARVC');
511 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- ------------------------------------------- ');
512 declare
513 l_reval_output_asset IGI_IAC_TYPES.iac_reval_output_asset;
514 begin
515 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- ------------------------------------------- ');
516 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- Asset number : '|| l_asset_no(l_loop_count));
517 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- Asset id : '|| l_asset_id(l_loop_count) );
518 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- reval rate : '|| l_reval_factor(l_loop_count));
519 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> -- ------------------------------------------- ');
520 if not IGI_IAC_REVAL_WRAPPER.do_revaluation_asset
521 ( fp_revaluation_id => l_reval.revaluation_id
522 , fp_asset_id => l_asset_id(l_loop_count)
523 , fp_book_type_code => l_reval.book_type_code
524 , fp_reval_mode => 'P'
525 , fp_reval_rate => l_reval_factor(l_loop_count)
526 , fp_period_counter => fp_period_counter
527 , fp_calling_program => 'IGIIARVC'
528 , fp_reval_messages => l_reval_messages
529 , fp_reval_output_asset => l_reval_output_asset
530 , fp_reval_messages_idx => l_reval_messages_idx
531 , fp_reval_exceptions => l_reval_exceptions
532 , fp_reval_exceptions_idx => l_reval_exceptions_idx )
533 then
534 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>failed do_revaluation');
535 l_failure_ct := l_failure_ct + 1;
536 else
537 l_success_ct := l_success_ct + 1;
538 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>success do_revaluation');
539 end if;
540 -- return true;
541 exception when others then
542 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_failed_pre ( l_reval.revaluation_id )
543 THEN
544 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>failure reval_status_to_previewed');
545 ELSE
546 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>success reval_status_to_previewed');
547 END IF;
548 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
549 l_failure_ct := l_failure_ct + 1;
550 end;
551
552 end loop; -- get assets
553 end loop; -- get the category information
554
555 end loop; -- get the revaluation for preview
556
557 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> failure count '|| l_failure_ct);
558 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>> success count '|| l_success_ct);
559 IF NOT igi_iac_reval_utilities.synchronize_accounts(p_book_type_code => fp_book_type_code,
560 p_period_counter => fp_period_counter,
561 p_calling_function => 'REVALUATION') THEN
562 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_ACCOUNT_NOT_FOUND');
563 FND_MESSAGE.SET_TOKEN('PROCESS','Revaluation',TRUE);
564 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
565 p_full_path => l_path,
566 p_remove_from_stack => FALSE);
567 fnd_file.put_line(fnd_file.log, fnd_message.get);
568
569 l_failure_ct := l_failure_ct + 1;
570 END IF;
571
572 IF l_failure_ct = 0 THEN
573 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_previewed ( l_number )
574 THEN
575 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>failed reval_status_to_previewed');
576 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>not preview_mode_hist_generate');
577 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end preview_mode_hist_generate');
578 ELSE
579 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>success reval_status_to_previewed');
580 END IF;
581 return true;
582 ELSE
583 rollback to sp;
584 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_failed_pre ( l_number )
585 THEN
586 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>failed reval_status_to_failed_pre');
587 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>not preview_mode_hist_generate');
588 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'end preview_mode_hist_generate');
589 ELSE
590 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'>>success reval_status_to_failed_pre');
591 END IF;
592 return false;
593 END IF;
594
595 end;
596 /*
597 -- Delete preview history completely
598 */
599
600 function preview_mode_hist_delete ( fp_revaluation_id in number)
601 return boolean is
602 cursor c_reval_rates is
603 select distinct asset_id, book_type_code, period_counter
604 from igi_iac_transaction_headers
605 where mass_reference_id = fp_revaluation_id
606 and transaction_type_code = 'REVALUATION'
607 ;
608 cursor c_txn_headers (cp_book_type_code in varchar2
609 ,cp_asset_id in number
610 ,cp_period_counter in number
611 ) is
612 select adjustment_id
613 from igi_iac_transaction_headers t
614 where book_type_code = cp_book_type_code
615 and asset_id = cp_asset_id
616 and transaction_type_code = 'REVALUATION'
617 and period_counter = cp_period_counter
618 and mass_reference_id = fp_revaluation_id
619 and exists ( select 'x'
620 from igi_iac_reval_asset_rules
621 where asset_id = t.asset_id
622 and book_type_code = t.book_type_code
623 and revaluation_id = t.mass_reference_id
624 and nvl(allow_prof_update,'X') = 'Y'
625 )
626 ;
627 l_delete_flag boolean;
628 l_prev_adj_id number;
629
630 l_path varchar2(100);
631 begin
632 l_path := g_path||'preview_mode_hist_delete';
633
634 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Begin preview_mode_hist_delete');
635 for l_rates in c_reval_rates loop
636 l_delete_flag := false;
637 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+found reval records to process');
638 for l_headers in c_txn_headers ( cp_book_type_code => l_rates.book_type_code
639 , cp_asset_id => l_rates.asset_id
640 , cp_period_counter => l_rates.period_counter
641 )
642 loop
643 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+adjustment id '||l_headers.adjustment_id);
644 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from exceptions table');
645 l_delete_flag := true;
646 delete from igi_iac_exceptions
647 where asset_id = l_rates.asset_id
648 and revaluation_id = fp_revaluation_id
649 and book_type_code = l_rates.book_type_code
650 ;
651 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from the transaction headers table');
652 delete igi_iac_transaction_headers
653 where adjustment_id = l_headers.adjustment_id
654 and adjustment_id_out is null
655 /* and not exists ( select 'x'
656 from igi_iac_transaction_headers
657 where adjustment_id_out = l_headers.adjustment_id
658 )*/
659 ;
660 if sql%notfound then
661 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+there have been additional transactions after this');
662 rollback;
663 return false;
664 end if;
665 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+update previous transaction row if one exists');
666 declare
667 cursor c_exist is
668 select 'x'
669 from igi_iac_transaction_headers
670 where adjustment_id_out = l_headers.adjustment_id
671 ;
672 l_exists_prev boolean := false;
673 begin
674 for l_exist in c_exist loop
675 l_exists_prev := true;
676 end loop;
677 if l_exists_prev then
678 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+previous transaction row exists');
679 update igi_iac_transaction_headers
680 set adjustment_id_out = null
681 where adjustment_id_out = l_headers.adjustment_id
682 and book_type_code = l_rates.book_type_code
683 and asset_id = l_rates.asset_id
684 ;
685 if sql%notfound then
686 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+error occurred in igi_iac_transaction_headers');
687 rollback;
688 return false;
689 end if;
690 end if;
691 end;
692 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from the adjustments table');
693 delete igi_iac_adjustments
694 where adjustment_id = l_headers.adjustment_id
695 ;
696 if sql%found then
697 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+deleted the iac adjustments');
698 end if;
699
700 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from the det balances table');
701 delete igi_iac_det_balances
702 where adjustment_id = l_headers.adjustment_id
703 ;
704 if sql%found then
705 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+deleted the det balances info');
706 end if;
707
708 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+delete from the revaluation rates table');
709 delete from igi_iac_revaluation_rates
710 where adjustment_id = l_headers.adjustment_id
711 ;
712 if sql%found then
713 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+deleted the revaluation rates info');
714 end if;
715 end loop;
716
717
718
719 end loop;
720
721 if not l_delete_flag then
722 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'no preview_mode_hist_delete');
723 else
724 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'yes preview_mode_hist_delete');
725 end if;
726
727 return true;
728 exception when others then
729 return false;
730 end;
731
732 /*
733 -- Test whether preview has been run again
734 */
735
736 function preview_mode_hist_available ( fp_revaluation_id in number )
737 return boolean is
738 cursor c_pmha is
739 select distinct asset_id, book_type_code, period_counter
740 from igi_iac_transaction_headers
741 where mass_reference_id = fp_revaluation_id
742 and transaction_type_code = 'REVALUATION'
743 and adjustment_status = 'PREVIEW'
744 ;
745
746 l_path varchar2(100) ;
747 begin
748 l_path := g_path||'preview_mode_hist_available';
749 for l_pmha in c_pmha loop
750 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'yes preview_mode_hist_available');
751 return true;
752 end loop;
753 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'no preview_mode_hist_available');
754 return false;
755 end;
756
757 /*
758 -- Test whether Run mode has processed successfully.
759 */
760
761 function run_mode_hist_available ( fp_revaluation_id in number )
762 return boolean is
763 cursor c_rmha is
764 select distinct asset_id, book_type_code, period_counter
765 from igi_iac_transaction_headers
766 where mass_reference_id = fp_revaluation_id
767 and transaction_type_code = 'REVALUATION'
768 and adjustment_status = 'RUN'
769 ;
770
771 l_path varchar2(100) ;
772 begin
773 l_path := g_path||'run_mode_hist_available';
774 for l_rmha in c_rmha loop
775 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'yes run_mode_hist_available');
776 return true;
777 end loop;
778 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'no run_mode_hist_available');
779 return false;
780 end;
781
782 procedure revaluation
783 ( errbuf out NOCOPY varchar2
784 , retcode out NOCOPY number
785 , revaluation_id in number
786 , book_type_code in varchar2
787 , revaluation_mode in varchar2 -- 'P' preview, 'R' run
788 , period_counter in number
789 , create_request_id in number
790 )
791 is
792 l_number number ;
793 l_path varchar2(100) ;
794 begin
795 l_number := revaluation_id ;
796 l_path := g_path||'revaluation';
797
798 /* Bug 2480915 this function synchronizes igi_iac_fa_depr table data */
799 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Synchronizing Depreciation Data ');
800 IF NOT igi_iac_common_utils.populate_iac_fa_deprn_data(book_type_code,
801 'REVALUATION') THEN
802 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failure in Synchronizing Depreciation Data ');
803 rollback;
804 errbuf := 'Failure in Synchronizing Depreciation Data. Submit Synchronize Depreciation Data request.';
805 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Failure in Synchronizing Depreciation Data. Submit Synchronize Depreciation Data request.');
806 retcode := 2;
807 return ;
808 END IF;
809 /*
810 -- conditions
811 -- **********
812 -- if this is in preview mode, create entries in igi_iac_transaction_headers
813 -- if the user resubmits the record in the preview mode, delete the existing records
814 -- and re-generate!
815 -- if the user is in run mode and there are no run mode records, only preview records,
816 -- then update the preview information to show that it is live.
817 -- if the user is in run mode and there are run mode records, directly print the
818 -- Asset Balance report.
819 --
820 --
821 */
822 declare
823 l_phase varchar2(240);
824 l_status varchar2(240);
825 l_dev_status varchar2(240);
826 l_dev_phase varchar2(240);
827 l_message varchar2(240);
828
829 begin
830
831 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Create request id is '|| create_request_id);
832 if create_request_id is not null then
833 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+begin wait for create request id '|| create_request_id);
834 if not fnd_concurrent.wait_for_request (
835 request_id => create_request_id
836 ,phase => l_phase
837 ,status => l_status
838 ,dev_phase => l_dev_status
839 ,dev_status => l_dev_phase
840 ,message => l_message
841 )
842 then
843 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+failed wait for create request id '|| create_request_id);
844 end if;
845 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'+end wait for create request id '|| create_request_id || ' status '|| l_status);
846 end if;
847 end;
848 commit; -- save changes
849 if run_mode_hist_available ( fp_revaluation_id => revaluation_id ) then
850 /** already in run mode **/
851 if revaluation_mode = 'P' then
852 rollback;
853 errbuf := 'Already in run mode.';
854 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Already in run mode.');
855 retcode := 2;
856 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_failed_run ( l_number )
857 THEN
858 null;
859 END IF;
860 return;
861 end if;
862
863 if revaluation_mode = 'R' then
864 submit_revaluation_report ( p_revaluation_id => revaluation_id
865 , p_revaluation_mode => revaluation_mode
866 );
867 errbuf := 'Normal completion';
868 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Normal completion');
869 retcode := 0;
870 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_completed ( l_number,null )
871 THEN
872 null;
873 END IF;
874 return;
875 end if;
876
877 errbuf := 'Incorrect status passed.';
878 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Incorrect status passed.');
879 retcode := 1;
880 return;
881
882 end if; /** already in run mode **/
883
884 if preview_mode_hist_available ( fp_revaluation_id => revaluation_id) then
885 if revaluation_mode = 'P' and
886 preview_mode_hist_delete ( fp_revaluation_id => revaluation_id ) then
887 if not preview_mode_hist_generate ( fp_revaluation_id => revaluation_id ,
888 fp_book_type_code => book_type_code ,
889 fp_period_counter => period_counter ,
890 fp_wait_request_id => create_request_id
891 ) then
892 rollback;
893 errbuf := 'Unable to generate history for Revaluation Preview.';
894 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Unable to generate history for Revaluation Preview.');
895 retcode := 2;
896 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_previewed ( l_number )
897 THEN
898 null;
899 END IF;
900 return;
901 end if;
902 end if;
903
904 if revaluation_mode = 'R' then
905 if not preview_mode_hist_transform ( fp_revaluation_id => revaluation_id
906 , fp_book_type_code => book_type_code
907 , fp_period_counter => period_counter
908 ) then
909 rollback;
910 errbuf := 'Unable to generate history for Revaluation Preview.';
911 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Unable to generate history for Revaluation Preview.');
912 retcode := 2;
913 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_failed_run ( l_number )
914 THEN
915 null;
916 END IF;
917 return;
918 end if;
919
920 end if;
921 else -- PReview mode history not available!!
922 if revaluation_mode = 'P' then
923 if not preview_mode_hist_generate ( fp_revaluation_id => revaluation_id
924 , fp_book_type_code => book_type_code
925 , fp_period_counter => period_counter
926 , fp_wait_request_id => create_request_id
927 ) then
928 rollback;
929 errbuf := 'Unable to generate history for Revaluation Preview.';
930 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Unable to generate history for Revaluation Preview.');
931 retcode := 2;
932 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_failed_pre ( l_number )
933 THEN
934 null;
935 END IF;
936 return;
937 end if;
938 end if;
939
940
941 if revaluation_mode = 'R' then
942 rollback;
943 errbuf := 'No preview records';
944 retcode := 0;
945 IF NOT IGI_IAC_REVAL_CRUD.reval_status_to_completed ( l_number,null )
946 THEN
947 null;
948 END IF;
949 return;
950 end if;
951 end if;
952
953 do_commit; -- save changes.
954
955 submit_revaluation_report ( p_revaluation_id => revaluation_id
956 , p_revaluation_mode => revaluation_mode
957 );
958
959 errbuf := 'Normal completion';
960 retcode := 0;
961 exception when others then
962 errbuf := SQLERRM;
963 retcode := 2;
964
965 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
966
967 end;
968
969 BEGIN
970 --===========================FND_LOG.START=====================================
971
972 g_state_level := FND_LOG.LEVEL_STATEMENT;
973 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
974 g_event_level := FND_LOG.LEVEL_EVENT;
975 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
976 g_error_level := FND_LOG.LEVEL_ERROR;
977 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
978 g_path := 'IGI.PLSQL.igiiarcb.IGI_IAC_REVAL_CONCURRENT.';
979
980 --===========================FND_LOG.END=======================================
981 END;
982