[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_COMMON_UTILS
Source
1 PACKAGE BODY IGI_IAC_COMMON_UTILS AS
2 -- $Header: igiiacub.pls 120.23.12010000.2 2008/09/01 09:18:18 gaprasad 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 -- DMahajan Start
17
18 Function Get_Period_Info_for_Counter( P_book_type_Code IN VARCHAR2 ,
19 P_period_Counter IN NUMBER ,
20 P_prd_rec OUT NOCOPY igi_iac_types.prd_rec
21 )
22 RETURN BOOLEAN AS
23 l_path_name VARCHAR2(150);
24 BEGIN
25 l_path_name := g_path||'get_period_info_for_counter';
26 BEGIN
27 SELECT dp.period_num, dp.period_name, dp.calendar_period_open_date ,
28 dp.calendar_period_close_date , p_period_counter, dp.fiscal_year
29 INTO p_prd_rec.period_num, p_prd_rec.period_name, p_prd_rec.period_start_date,
30 p_prd_rec.period_end_date, p_prd_rec.period_counter , p_prd_rec.fiscal_year
31 FROM fa_deprn_periods dp
32 WHERE dp.book_type_code = P_book_type_code
33 AND dp.period_counter = P_period_counter ;
34
35 EXCEPTION
36 WHEN NO_DATA_FOUND THEN
37 SELECT cp.period_num, cp.period_name, cp.start_date ,
38 cp.end_Date , p_period_counter, fy.fiscal_year
39 INTO p_prd_rec.period_num, p_prd_rec.period_name, p_prd_rec.period_start_date,
40 p_prd_rec.period_end_date, p_prd_rec.period_counter , p_prd_rec.fiscal_year
41 FROM fa_fiscal_year fy ,
42 fa_calendar_types ct ,
43 fa_calendar_periods cp,
44 fa_book_controls bc
45 WHERE ct.fiscal_year_name = fy.fiscal_year_name
46 AND bc.book_type_code = P_book_type_code
47 AND ct.calendar_type = bc.deprn_Calendar
48 AND fy.fiscal_year = decode( mod ( P_period_counter , ct.number_per_fiscal_year ) , 0 ,
49 trunc ( P_period_counter / ct.number_per_fiscal_year ) -1 ,
50 trunc ( P_period_counter / ct.number_per_fiscal_year ) )
51 AND cp.calendar_type = ct.calendar_type
52 AND cp.start_Date >= fy.start_date
53 AND cp.end_Date <= fy.end_Date
54 AND cp.period_num = decode( mod ( P_period_counter , ct.number_per_fiscal_year ),0 ,ct.number_per_fiscal_year,
55 mod ( P_period_counter , ct.number_per_fiscal_year ));
56 RETURN TRUE;
57 END;
58
59 RETURN TRUE;
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
64 RETURN FALSE;
65 END;
66
67
68
69 Function Get_Period_Info_for_Date( P_book_type_Code IN VARCHAR2 ,
70 P_date IN DATE ,
71 p_prd_rec OUT NOCOPY igi_iac_types.prd_rec
72 )
73 RETURN BOOLEAN AS
74 l_path_name VARCHAR2(150);
75 BEGIN
76 l_path_name := g_path||'get_period_info_for_date';
77 BEGIN
78 SELECT dp.period_num, dp.period_name, dp.calendar_period_open_date ,
79 dp.calendar_period_close_date , dp.period_counter, dp.fiscal_year
80 INTO p_prd_rec.period_num, p_prd_rec.period_name, p_prd_rec.period_start_date,
81 p_prd_rec.period_end_date, p_prd_rec.period_counter , p_prd_rec.fiscal_year
82 FROM fa_deprn_periods dp
83 WHERE dp.book_type_code = P_book_type_code
84 AND P_date between dp.calendar_period_open_Date and dp.calendar_period_close_date;
85 EXCEPTION
86 WHEN NO_DATA_FOUND THEN
87 SELECT cp.period_num, cp.period_name, cp.start_date ,
88 cp.end_Date ,
89 ((fy.fiscal_year*ct.number_per_fiscal_year)+cp.period_num) p_period_counter,
90 fy.fiscal_year
91 INTO p_prd_rec.period_num, p_prd_rec.period_name, p_prd_rec.period_start_date,
92 p_prd_rec.period_end_date, p_prd_rec.period_counter , p_prd_rec.fiscal_year
93 FROM fa_fiscal_year fy ,
94 fa_calendar_types ct ,
95 fa_calendar_periods cp ,
96 fa_book_controls bc
97 WHERE ct.fiscal_year_name = fy.fiscal_year_name
98 AND bc.book_type_code = P_book_type_code
99 AND ct.calendar_type = bc.deprn_Calendar
100 AND P_date between cp.start_date and cp.end_Date
101 AND P_date between fy.start_date and fy.end_Date
102 AND cp.calendar_type = ct.calendar_type
103 AND cp.start_Date >= fy.start_date
104 AND cp.end_Date <= fy.end_Date;
105 END;
106
107 RETURN TRUE;
108
109 EXCEPTION
110 WHEN OTHERS THEN
111 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
112 RETURN FALSE;
113 END;
114
115
116
117 Function Get_Period_Info_for_Name( P_book_type_Code IN VARCHAR2 ,
118 P_Prd_Name IN VARCHAR2 ,
119 p_prd_rec OUT NOCOPY igi_iac_types.prd_rec
120 )
121 RETURN BOOLEAN AS
122 l_path_name VARCHAR2(150);
123 BEGIN
124 l_path_name := g_path||'get_period_info_for_name';
125 BEGIN
126 SELECT dp.period_num, dp.period_name, dp.calendar_period_open_date ,
127 dp.calendar_period_close_date , dp.period_counter, dp.fiscal_year
128 INTO p_prd_rec.period_num, p_prd_rec.period_name, p_prd_rec.period_start_date,
129 p_prd_rec.period_end_date, p_prd_rec.period_counter , p_prd_rec.fiscal_year
130 FROM fa_deprn_periods dp
131 WHERE dp.book_type_code = P_book_type_code
132 AND dp.period_name = P_prd_name ;
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 SELECT cp.period_num, cp.period_name, cp.start_date ,
136 cp.end_Date ,
137 ((fy.fiscal_year*ct.number_per_fiscal_year)+cp.period_num) p_period_counter,
138 fy.fiscal_year
139 INTO p_prd_rec.period_num, p_prd_rec.period_name, p_prd_rec.period_start_date,
140 p_prd_rec.period_end_date, p_prd_rec.period_counter , p_prd_rec.fiscal_year
141 FROM fa_fiscal_year fy ,
142 fa_calendar_types ct ,
143 fa_calendar_periods cp ,
144 fa_book_controls bc
145 WHERE ct.fiscal_year_name = fy.fiscal_year_name
146 AND bc.book_type_code = P_book_type_code
147 AND ct.calendar_type = bc.deprn_Calendar
148 AND cp.calendar_type = ct.calendar_type
149 AND cp.period_name = P_prd_name
150 AND cp.start_Date >= fy.start_date
151 AND cp.end_Date <= fy.end_Date;
152 END;
153
154 RETURN TRUE;
155
156 EXCEPTION
157 WHEN OTHERS THEN
158 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
159 RETURN FALSE;
160 END;
161
162
163
164 Function Get_Open_Period_Info ( P_book_type_Code IN VARCHAR2 ,
165 p_prd_rec OUT NOCOPY igi_iac_types.prd_rec
166 )
167 RETURN BOOLEAN AS
168 l_path_name VARCHAR2(150);
169 BEGIN
170 l_path_name := g_path||'get_open_period_info';
171 SELECT dp.Period_Name, dp.Period_Counter, dp.Period_Num,
172 dp.Fiscal_Year, dp.calendar_Period_open_Date, dp.calendar_Period_close_Date
173 INTO p_prd_rec.Period_Name, p_prd_rec.Period_Counter, p_prd_rec.Period_Num,
174 p_prd_rec.Fiscal_Year, p_prd_rec.Period_Start_Date, p_prd_rec.Period_End_Date
175 FROM fa_deprn_periods dp
176 WHERE dp.book_type_code = P_book_type_code
177 AND dp.period_close_date IS NULL ;
178
179 RETURN TRUE;
180
181 EXCEPTION
182 WHEN OTHERS THEN
183 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
184 RETURN FALSE;
185 END;
186
187
188
189
190
191 Function Get_Retirement_Info ( P_Retirement_Id IN NUMBER ,
192 P_Retire_Info OUT NOCOPY fa_retirements%ROWTYPE
193 )
194 RETURN BOOLEAN AS
195
196 l_index NUMBER;
197 l_path_name VARCHAR2(150);
198
199 CURSOR c_ret IS
200 SELECT *
201 FROM fa_retirements
202 WHERE retirement_id = P_Retirement_Id ;
203
204 BEGIN
205 l_index := 0 ;
206 l_path_name := g_path||'get_retirement_info';
207
208 OPEN c_ret ;
209 LOOP
210 l_index := l_index + 1 ;
211 FETCH c_ret INTO P_Retire_Info ;
212 EXIT WHEN c_ret%NOTFOUND ;
213 END LOOP ;
214 CLOSE c_ret ;
215
216 IF ( l_index ) <= 0 THEN
217 RAISE NO_DATA_FOUND ;
218 END IF;
219 RETURN TRUE ;
220
221 EXCEPTION
222 WHEN OTHERS THEN
223 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
224 RETURN FALSE;
225 END ;
226
227
228
229 Function Get_Units_Info_for_Gain_Loss (P_asset_id IN NUMBER ,
230 P_Book_type_code IN VARCHAR2 ,
231 P_Retirement_Id IN NUMBER ,
232 -- ssmales P_Calling_txn IN NUMBER ,
233 P_Calling_txn IN VARCHAR2,
234 P_Units_Before OUT NOCOPY NUMBER ,
235 P_Units_After OUT NOCOPY NUMBER
236 )
237 RETURN BOOLEAN AS
238 l_Retire_Info fa_retirements%ROWTYPE ;
239 l_txn_id_before NUMBER ;
240 l_path_name VARCHAR2(150);
241 BEGIN
242 l_path_name := g_path||'get_units_info_for_gain_loss';
243 IF ( NOT ( Get_Retirement_Info ( P_Retirement_Id ,
244 l_Retire_Info
245 ) )) THEN
246 RETURN FALSE ;
247 END IF;
248
249 IF( P_Calling_txn = 'RETIREMENT' ) THEN
250 l_txn_id_before := l_retire_info.transaction_header_id_in ;
251 ELSE
252 l_txn_id_before := l_retire_info.transaction_header_id_out ;
253 END IF;
254
255 SELECT h.units
256 INTO p_units_before
257 FROM fa_asset_history h
258 WHERE h.asset_id = P_asset_id
259 AND h.transaction_header_id_out = l_txn_id_before ;
260
261 SELECT h.units
262 INTO p_units_after
263 FROM fa_asset_history h
264 WHERE h.asset_id = P_asset_id
265 AND h.transaction_header_id_in = l_txn_id_before ;
266
267 RETURN TRUE ;
268
269 EXCEPTION
270 WHEN OTHERS THEN
271 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
272 RETURN FALSE;
273 END ;
274
275
276
277
278
279 Function Get_Cost_Retirement_Factor ( P_Book_Type_code IN VARCHAR2 ,
280 P_Asset_id IN NUMBER ,
281 P_Retirement_Id IN NUMBER ,
282 P_Factor OUT NOCOPY NUMBER
283 )
284 RETURN BOOLEAN AS
285 l_cost_retired NUMBER ;
286 l_cost NUMBER ;
287 l_path_name VARCHAR2(150);
288 BEGIN
289 l_path_name := g_path||'get_cost_retirement_factor';
290
291 SELECT b.cost, r.cost_retired
292 INTO l_cost, l_cost_retired
293 FROM fa_books b, fa_retirements r
294 WHERE b.book_type_code = P_book_type_Code
295 AND b.asset_id = P_asset_id
296 AND r.retirement_id = P_retirement_id
297 AND r.retirement_id = b.retirement_id ;
298
299 P_Factor := l_cost_retired / l_cost ;
300
301 RETURN TRUE;
302 EXCEPTION
303 WHEN OTHERS THEN
304 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
305 RETURN FALSE;
306 END;
307
308
309 -- ssmales Function Is_Part_Ret_Unit_or_Cost( P_book_type_Code IN VARCHAR2 ,
310 Function Get_Retirement_Type ( P_book_type_Code IN VARCHAR2 ,
311 P_Asset_id IN NUMBER ,
312 -- ssmales P_Transaction_header_id IN NUMBER ,
313 P_Retirement_Id IN NUMBER ,
314 P_Type OUT NOCOPY VARCHAR2
315 )
316 RETURN BOOLEAN AS
317 l_units NUMBER;
318 l_transaction_type fa_transaction_headers.transaction_type_code%TYPE ;
319 l_path_name VARCHAR2(150);
320 BEGIN
321 l_units := 0 ;
322 l_path_name := g_path||'get_retirement_type';
323
324 SELECT t.transaction_type_code
325 INTO l_transaction_type
326 FROM fa_transaction_headers t, fa_retirements r
327 WHERE t.transaction_header_id = r.transaction_header_id_in
328 AND r.retirement_id = P_retirement_id ;
329
330 SELECT r.units
331 INTO l_units
332 FROM fa_retirements r
333 --ssmales WHERE r.transaction_header_id_in = P_transaction_header_id
334 WHERE r.retirement_id = P_retirement_id
335 AND r.book_type_code = P_book_type_code
336 AND r.asset_id = P_asset_id ;
337
338 IF l_transaction_type = 'FULL RETIREMENT' THEN
339 P_Type := 'FULL' ;
340
341 ELSIF ( nvl(l_units,0) > 0 ) THEN
342 P_Type := 'UNIT';
343 ELSE
344 P_Type := 'COST';
345 END IF;
346 RETURN TRUE;
347 EXCEPTION
348 WHEN OTHERS THEN
349 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
350 RETURN FALSE;
351 END;
352
353
354
355 Function Prorate_Amt_to_Active_Dists( P_book_type_Code IN VARCHAR2 ,
356 P_Asset_id IN NUMBER ,
357 P_Amount IN NUMBER ,
358 P_out_tab OUT NOCOPY igi_iac_types.dist_amt_tab
359 )
360 RETURN BOOLEAN AS
361 l_tot_units NUMBER;
362 l_dist_amount NUMBER;
363 l_remain_amount NUMBER;
364 l_units_processed NUMBER;
365 l_factor NUMBER;
366 l_index NUMBER ;
367 l_path_name VARCHAR2(150);
368
369 CURSOR c_active_dists IS
370 SELECT *
371 FROM fa_distribution_history
372 WHERE asset_id = P_Asset_Id
373 AND book_type_code = P_book_type_code
374 AND date_ineffective IS NULL ;
375 BEGIN
376 l_tot_units := 0 ;
377 l_dist_amount := 0 ;
378 l_remain_amount := p_amount ;
379 l_units_processed := 0 ;
380 l_factor := 1 ;
381 l_path_name := g_path||'prorate_amt_to_active_dists';
382
383 SELECT h.units
384 INTO l_tot_units
385 FROM fa_asset_history h
386 WHERE asset_id = P_asset_id
387 AND date_ineffective IS NULL ;
388
389 l_remain_amount := P_amount ;
390 l_index := 0 ;
391
392 FOR distrec IN c_active_dists LOOP
393 l_index := l_index + 1 ;
394 l_units_processed := l_units_processed + distrec.units_assigned ;
395 IF (l_units_processed <> l_tot_units ) THEN
396 l_factor := distrec.units_assigned / l_tot_units ;
397 l_dist_amount := P_Amount * l_factor ;
398 l_remain_amount := l_remain_amount - l_dist_amount ;
399 ELSE
400 l_factor := distrec.units_assigned / l_tot_units;
401 l_dist_amount := l_remain_amount ;
402 END IF;
403 P_out_tab(l_index).distribution_id := distrec.distribution_id ;
404 P_out_tab(l_index).amount := l_dist_amount ;
405 P_out_tab(l_index).units := distrec.units_assigned;
406 P_out_tab(l_index).prorate_factor := l_factor;
407 END LOOP ;
408
409 RETURN TRUE;
410 EXCEPTION
411 WHEN OTHERS THEN
412 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
413 RETURN FALSE;
414 END;
415
416
417
418 Function Get_Active_Distributions ( P_book_type_Code IN VARCHAR2 ,
419 P_Asset_id IN NUMBER ,
420 P_dh_tab OUT NOCOPY igi_iac_types.dh_tab
421 )
422 RETURN BOOLEAN AS
423
424 l_index NUMBER;
425 l_path_name VARCHAR2(150);
426
427 CURSOR c_dh IS
428 SELECT dh.*
429 FROM fa_distribution_history dh
430 WHERE dh.asset_id = P_Asset_Id
431 AND dh.book_type_code = P_book_type_Code
432 AND dh.date_ineffective IS NULL ;
433
434 BEGIN
435 l_index := 0 ;
436 l_path_name := g_path||'get_active_distributions';
437
438 OPEN c_dh ;
439 LOOP
440 l_index := l_index + 1 ;
441 FETCH c_dh INTO P_dh_tab(l_index);
442 EXIT WHEN c_dh%NOTFOUND ;
443 END LOOP ;
444 CLOSE c_dh ;
445
446 IF ( P_dh_tab.count ) <= 0 THEN
447 RAISE NO_DATA_FOUND ;
448 END IF;
449
450 RETURN TRUE;
451 EXCEPTION
452 WHEN OTHERS THEN
453 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
454 RETURN FALSE;
455 END ;
456
457
458
459
460
461 Function Get_CY_PY_Factors( P_book_type_Code IN VARCHAR2 ,
462 P_Asset_id IN NUMBER ,
463 P_Period_Name IN VARCHAR2 ,
464 P_PY_Ratio OUT NOCOPY NUMBER ,
465 P_CY_Ratio OUT NOCOPY NUMBER
466 )
467 RETURN BOOLEAN AS
468 l_curr_prd_tab igi_iac_types.prd_rec ;
469 l_dpis_prd_tab igi_iac_types.prd_rec ;
470 l_dpis DATE ;
471 l_path_name VARCHAR2(150);
472 BEGIN
473 l_path_name := g_path||'get_cy_py_factors';
474
475 SELECT b.date_placed_in_service
476 INTO l_dpis
477 FROM fa_books b
478 WHERE b.book_type_code = P_book_type_code
479 AND b.asset_id = P_Asset_Id
480 AND b.date_ineffective IS NULL ;
481
482
483 IF ( NOT ( Get_Period_Info_for_Date( P_book_type_Code ,
484 l_dpis ,
485 l_dpis_prd_tab
486 ))) THEN
487 RETURN FALSE ;
488 END IF;
489
490 IF ( NOT ( Get_Period_Info_for_Name( P_book_type_Code ,
491 P_Period_Name ,
492 l_curr_prd_tab
493 ))) THEN
494 RETURN FALSE ;
495 END IF;
496
497 P_CY_Ratio := (l_curr_prd_tab.Period_Num)/
498 (l_curr_prd_tab.period_counter-l_dpis_prd_tab.period_counter) ;
499 P_PY_Ratio := 1 - P_CY_Ratio ;
500
501 RETURN TRUE;
502
503 EXCEPTION
504 WHEN OTHERS THEN
505 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
506 RETURN FALSE;
507 END ;
508
509
510
511
512 Function Is_Asset_Rvl_in_curr_Period( P_book_type_Code IN VARCHAR2 ,
513 P_Asset_id IN NUMBER
514 )
515 RETURN BOOLEAN AS
516 l_curr_prd_Counter NUMBER ;
517 l_prev_prd_Counter NUMBER ;
518 l_tot_records NUMBER ;
519 l_path_name VARCHAR2(150) ;
520 BEGIN
521 l_path_name := g_path||'is_asset_rvl_in_curr_period';
522 SELECT dp.period_counter
523 INTO l_curr_prd_Counter
524 FROM fa_deprn_periods dp
525 WHERE dp.book_type_Code = P_book_type_code
526 AND dp.period_close_date IS NULL;
527
528 l_prev_prd_counter := l_curr_prd_Counter - 1;
529
530 SELECT count(*)
531 INTO l_tot_records
532 FROM igi_iac_transaction_headers it
533 WHERE it.book_type_Code = P_book_type_code
534 AND it.asset_id = P_asset_id
535 AND it.transaction_type_Code = 'REVALUATION'
536 AND it.period_counter = l_prev_prd_counter
537 AND it.adjustment_status = 'R' ;
538
539 IF l_tot_records > 0 THEN
540 RETURN TRUE ;
541 END IF;
542
543 RETURN FALSE ;
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
548 RETURN FALSE;
549 END ;
550
551
552
553 Function Any_Txns_In_Open_Period( P_book_type_Code IN VARCHAR2 ,
554 P_Asset_id IN NUMBER
555 )
556 RETURN BOOLEAN AS
557 l_tot_records NUMBER;
558 l_path_name VARCHAR2(150);
559 BEGIN
560 l_tot_records := 0 ;
561 l_path_name := g_path||'any_txns_in_open_period';
562
563 SELECT count(*)
564 INTO l_tot_records
565 FROM fa_transaction_headers ft ,
566 fa_deprn_periods dp
567 WHERE ft.book_type_Code = P_book_type_code
568 AND ft.asset_id = P_asset_id
569 AND dp.book_type_Code = P_book_type_code
570 AND dp.period_close_Date IS NULL
571 AND ft.date_effective >= dp.period_open_date ;
572
573
574 IF l_tot_records > 0 THEN
575 RETURN TRUE ;
576 END IF;
577
578 RETURN FALSE ;
579
580 EXCEPTION
581 WHEN OTHERS THEN
582 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
583 RETURN FALSE;
584 END;
585
586
587
588
589
590 Function Any_Adj_In_Book( P_book_type_Code IN VARCHAR2 ,
591 P_Asset_id IN NUMBER
592 )
593 RETURN BOOLEAN AS
594 l_tot_records NUMBER;
595 l_path_name VARCHAR2(150);
596 BEGIN
597 l_tot_records := 0 ;
598 l_path_name := g_path||'any_adj_in_book';
599
600 SELECT count(*)
601 INTO l_tot_records
602 FROM fa_transaction_headers ft
603 WHERE ft.book_type_Code = P_book_type_code
604 AND ft.asset_id = P_asset_id
605 AND ft.transaction_type_Code = 'ADJUSTMENT';
606
607
608 IF l_tot_records > 0 THEN
609 RETURN TRUE ;
610 END IF;
611
612 RETURN FALSE ;
613
614 EXCEPTION
615 WHEN OTHERS THEN
616 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
617 RETURN FALSE;
618 END;
619
620
621
622
623 Function Any_Reval_in_Corp_Book( P_book_type_Code IN VARCHAR2 ,
624 P_Asset_id IN NUMBER
625 )
626 RETURN BOOLEAN AS
627 l_tot_records NUMBER;
628 l_path_name VARCHAR2(150);
629 BEGIN
630 l_tot_records := 0 ;
631 l_path_name := g_path||'any_reval_in_corp_book';
632
633 SELECT count(*)
634 INTO l_tot_records
635 FROM fa_transaction_headers ft
636 WHERE ft.book_type_Code = P_book_type_code
637 AND ft.asset_id = P_asset_id
638 AND ft.transaction_type_Code = 'REVALUATION';
639
640
641 IF l_tot_records > 0 THEN
642 RETURN TRUE ;
643 END IF;
644
645 RETURN FALSE ;
646
647 EXCEPTION
648 WHEN OTHERS THEN
649 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
650 RETURN FALSE;
651 END;
652
653
654
655
656
657 Function Any_Ret_In_Curr_Yr ( P_book_type_Code IN VARCHAR2 ,
658 P_Asset_id IN NUMBER ,
659 P_retirements OUT NOCOPY VARCHAR2
660 )
661 RETURN BOOLEAN AS
662 l_prd_tab igi_iac_types.prd_rec ;
663 l_calendar VARCHAR2(30);
664 l_fiscal_year_name VARCHAR2(30);
665 l_fy_start_date DATE ;
666 l_tot_retirements NUMBER ;
667 l_path_name VARCHAR2(150);
668 BEGIN
669 l_path_name := g_path||'any_ret_in_curr_yr';
670
671 IF ( NOT ( Get_Open_Period_Info ( P_book_type_Code ,
672 l_prd_tab
673 ))) THEN
674 RETURN FALSE ;
675 END IF;
676
677 SELECT ct.calendar_type , ct.fiscal_year_name
678 INTO l_calendar , l_fiscal_year_name
679 FROM fa_Calendar_types ct , fa_book_controls bc
680 WHERE ct.calendar_type = bc.deprn_calendar
681 AND bc.book_type_Code = P_book_type_Code ;
682
683 SELECT fy.start_Date
684 INTO l_fy_start_date
685 FROM fa_fiscal_year fy
686 WHERE fy.fiscal_year_name = l_fiscal_year_name
687 AND fy.fiscal_year = l_prd_tab.fiscal_year ;
688
689 -- bug 2452521, start (1)
690
691 /* SELECT count(*)
692 INTO l_tot_retirements
693 FROM fa_retirements r
694 WHERE r.book_type_code = P_book_type_Code
695 AND r.asset_id = P_asset_id
696 AND r.status IN ('PROCESSED' , 'PENDING', 'PARTIAL' , 'REINSTATE' )
697 AND r.date_retired >= l_fy_start_date ;
698 */
699
700 SELECT count(*)
701 INTO l_tot_retirements
702 FROM fa_retirements r
703 WHERE r.book_type_code = P_book_type_Code
704 AND r.transaction_header_id_out IS NULL
705 AND r.asset_id = P_asset_id
706 AND r.status IN ('PROCESSED' , 'PENDING', 'PARTIAL' , 'REINSTATE' )
707 AND r.date_retired >= l_fy_start_date
708 AND EXISTS ( SELECT 'x'
709 FROM fa_transaction_headers t
710 WHERE t.transaction_header_id = r.transaction_header_id_in
711 AND t.transaction_type_code = 'FULL RETIREMENT' );
712
713 -- bug 2452521, end (1)
714
715 IF ( l_tot_Retirements > 0 ) THEN
716 P_retirements := 'Y' ;
717 ELSE
718 P_retirements := 'N' ;
719 END IF;
720
721
722 RETURN TRUE ;
723
724 EXCEPTION
725 WHEN OTHERS THEN
726 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
727 RETURN FALSE;
728 END;
729
730 -- DMahajan End
731
732
733 -- Niyer Start
734
735 FUNCTION Is_Asset_Proc (
736 X_book_type_code IN VARCHAR2,
737 X_asset_id IN VARCHAR2 ) RETURN BOOLEAN IS
738
739 Cursor C1 is select asset_id
740 from igi_iac_asset_balances
741 where asset_id = X_asset_id
742 and book_type_code = X_book_type_code
743 and rownum = 1 ;
744
745 l_dummy NUMBER;
746 l_path_name VARCHAR2(150);
747
748 BEGIN
749 l_path_name := g_path||'is_asset_proc';
750
751 OPEN C1;
752 FETCH C1 INTO l_dummy;
753 IF C1%FOUND THEN
754 CLOSE C1;
755 RETURN TRUE;
756 ELSE
757 CLOSE C1;
758 RETURN FALSE;
759 END IF;
760 EXCEPTION
761 WHEN OTHERS THEN
762 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
763 RETURN FALSE ;
764 END;
765
766 FUNCTION Get_Dpis_Period_Counter (
767 X_book_type_code IN Varchar2,
768 X_asset_id IN Varchar2,
769 X_Period_Counter OUT NOCOPY Varchar2 ) RETURN BOOLEAN IS
770
771 Cursor C1 is select date_placed_in_service
772 from fa_books fb ,
773 igi_iac_book_controls ibc
774 Where fb.book_type_code = ibc.book_type_code
775 AND fb.book_type_code = X_book_type_code
776 AND fb.asset_id = X_asset_id
777 AND fb.date_ineffective is null;
778
779 l_prd_rec igi_iac_types.prd_rec;
780 l_dpis DATE;
781 l_path_name VARCHAR2(150);
782
783 Begin
784 l_path_name := g_path||'get_dpis_period_counter';
785
786 OPEN C1;
787 FETCH C1 INTO l_dpis;
788 IF C1%FOUND THEN
789 Begin
790 SELECT dp.period_num, dp.period_name, dp.calendar_period_open_date ,
791 dp.calendar_period_close_date , dp.period_counter, dp.fiscal_year
792 INTO l_prd_rec.period_num, l_prd_rec.period_name, l_prd_rec.period_start_date,
793 l_prd_rec.period_end_date, l_prd_rec.period_counter , l_prd_rec.fiscal_year
794 FROM fa_deprn_periods dp
795 WHERE dp.book_type_code = X_book_type_code
796 AND l_dpis between dp.calendar_period_open_Date and dp.calendar_period_close_date;
797 EXCEPTION
798 WHEN NO_DATA_FOUND THEN
799 SELECT cp.period_num, cp.period_name, cp.start_date ,
800 cp.end_Date ,
801 ((fy.fiscal_year*ct.number_per_fiscal_year)+cp.period_num) p_period_counter,
802 fy.fiscal_year
803 INTO l_prd_rec.period_num, l_prd_rec.period_name, l_prd_rec.period_start_date,
804 l_prd_rec.period_end_date, l_prd_rec.period_counter , l_prd_rec.fiscal_year
805 FROM fa_fiscal_year fy ,
806 fa_calendar_types ct ,
807 fa_calendar_periods cp ,
808 fa_book_controls bc
809 WHERE ct.fiscal_year_name = fy.fiscal_year_name
810 AND bc.book_type_code = X_book_type_code
811 AND ct.calendar_type = bc.deprn_Calendar
812 AND l_dpis between cp.start_date and cp.end_Date
813 AND l_dpis between fy.start_date and fy.end_Date
814 AND cp.calendar_type = ct.calendar_type
815 AND cp.start_Date >= fy.start_date
816 AND cp.end_Date <= fy.end_Date;
817 END;
818 X_Period_Counter := l_prd_rec.period_counter;
819 CLOSE C1;
820
821 RETURN TRUE;
822 ELSE
823 CLOSE C1;
824 RETURN FALSE;
825 END IF;
826 EXCEPTION
827 WHEN OTHERS THEN
828 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
829 RETURN FALSE ;
830 END;
831
832
833
834 FUNCTION Get_Price_Index (
835 X_book_type_code IN VARCHAR2,
836 X_asset_id IN Varchar2,
837 X_Price_Index_Id OUT NOCOPY NUMBER,
838 X_Price_Index_Name OUT NOCOPY VARCHAR2 ) RETURN BOOLEAN IS
839
840 Cursor cat is
841 select asset_category_id
842 from fa_additions
843 where asset_id = X_asset_id;
844
845 Cursor prc_idx(p_cat number) is
846 select price_index_id
847 from igi_iac_category_books
848 where book_type_code = X_book_type_code
849 and category_id = p_cat;
850
851 Cursor idx_nam(p_prc_idx number) is
852 select price_index_name , price_index_id
853 from igi_iac_price_indexes
854 where price_index_id = p_prc_idx;
855
856 l_path_name VARCHAR2(150);
857
858 Begin
859 l_path_name := g_path||'get_price_index';
860
861 for l_cat in cat LOOP
862 for l_prc_idx in prc_idx(l_cat.asset_category_id) LOOP
863 for l_idx_nam in idx_nam(l_prc_idx.price_index_id) LOOP
864 X_Price_Index_Name := l_idx_nam.price_index_name;
865 X_Price_Index_Id := l_idx_nam.price_index_id;
866 end loop;
867 end loop;
868 end loop;
869
870 RETURN TRUE;
871
872 EXCEPTION
873 WHEN OTHERS THEN
874 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
875 RETURN FALSE ;
876
877 End;
878
879
880
881 FUNCTION Get_Price_Index_Value (
882 X_book_type_code IN VARCHAR2,
883 X_asset_id IN Varchar2,
884 X_Period_Name IN VARCHAR2,
885 X_Price_Index_Value OUT NOCOPY NUMBER ) RETURN BOOLEAN IS
886
887 Cursor cat is
888 select asset_category_id
889 from fa_additions
890 where asset_id = X_asset_id;
891
892 Cursor cal_price_idx_id(p_cat number ) is
893 select cal_price_index_link_id
894 from igi_iac_category_books
895 where category_id = p_cat
896 and book_type_code = X_book_type_code;
897
898 p_dep_cal varchar2(15);
899 p_cal_prc_idx_id number;
900 p_start_date date;
901 p_end_date date;
902 p_cur_prc_idx_val number;
903 l_path_name VARCHAR2(150) ;
904
905 Cursor dep_cal is
906 select deprn_calendar
907 from fa_book_controls
908 where book_type_code = X_book_type_code;
909
910 Cursor start_end_dat(cp_dep_cal varchar2) is
911 select start_date , end_date
912 from fa_calendar_periods
913 where period_name = X_Period_Name
914 and calendar_type = cp_dep_cal;
915
916 Cursor c_price_index_value(cp_cal_prc_idx_id number,cp_start_date date,cp_end_date date) IS
917 select current_price_index_value
918 from igi_iac_cal_idx_values
919 where cal_price_index_link_id = cp_cal_prc_idx_id
920 and date_from = cp_start_date
921 and date_to = cp_end_date;
922
923
924 Begin
925 l_path_name := g_path||'get_price_index_value';
926 Begin
927
928 open dep_cal;
929 fetch dep_cal into p_dep_cal;
930 IF dep_cal%FOUND THEN
931
932 close dep_cal;
933 else
934 close dep_cal;
935 End If;
936 Exception
937 WHEN OTHERS THEN
938 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
939 Raise_application_Error (-20000, SQLERRM);
940 End;
941
942 for l_cat in cat LOOP
943 for l_cal_prc_idx_id in cal_price_idx_id(l_cat.asset_category_id) LOOP
944 p_cal_prc_idx_id := l_cal_prc_idx_id.cal_price_index_link_id;
945 End Loop;
946 End Loop;
947
948 for l_start_end_dat in start_end_dat(p_dep_cal) LOOP
949 p_start_date := l_start_end_dat.start_date;
950 p_end_date := l_start_end_dat.end_date;
951 End Loop;
952
953 open c_price_index_value(p_cal_prc_idx_id,p_start_date,p_end_date);
954 fetch c_price_index_value into p_cur_prc_idx_val;
955 close c_price_index_value;
956
957
958 X_Price_Index_Value := p_cur_prc_idx_val;
959 RETURN TRUE;
960
961 EXCEPTION
962 WHEN OTHERS THEN
963 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
964 RETURN FALSE ;
965
966 End;
967
968 -- Niyer End
969
970
971 -- Shekar Start
972
973 FUNCTION Is_IAC_Book ( X_book_type_code IN VARCHAR2 )
974 RETURN BOOLEAN IS
975 CURSOR IAC_book IS
976 SELECT 'X'
977 FROM igi_iac_book_Controls ibc ,
978 fa_book_controls bc
979 WHERE bc.book_type_code = ibc.book_type_code
980 AND bc.date_ineffective IS NULL
981 AND bc.book_type_code = X_book_type_code;
982
983 l_found BOOLEAN;
984 l_dummy VARCHAR2(1);
985 l_path_name VARCHAR2(150);
986 BEGIN
987 l_found := FALSE ;
988 l_path_name := g_path||'is_iac_book';
989 OPEN iac_book;
990 FETCH iac_book INTO l_dummy;
991 IF iac_book%FOUND THEN
992 CLOSE iac_book;
993 RETURN TRUE;
994 ELSE
995 CLOSE iac_book;
996 RETURN FALSE;
997 END IF;
998 EXCEPTION
999 WHEN OTHERS THEN
1000 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1001 RETURN FALSE ;
1002 END; -- is_iac_book
1003
1004
1005 /*
1006 The Get_lastest transaction function retrives the latest non-preview transaction
1007 x_adjustment_id returns latest transaction id
1008 (This transaction should be closed by new transaction)
1009 x_prev_adjustment_id returns latest non preview transaction
1010 (This det_balances for this transaction should be considered for calculations).
1011 */
1012
1013 FUNCTION Get_Latest_Transaction (
1014 X_book_type_code IN Varchar2
1015 , X_asset_id IN Number
1016 , X_Transaction_Type_Code IN OUT NOCOPY Varchar2
1017 , X_Transaction_Id IN OUT NOCOPY Number
1018 , X_Mass_Reference_ID IN OUT NOCOPY Number
1019 , X_Adjustment_Id OUT NOCOPY Number
1020 , X_Prev_Adjustment_Id OUT NOCOPY Number
1021 , X_Adjustment_Status OUT NOCOPY Varchar2
1022 )
1023 RETURN BOOLEAN IS
1024 CURSOR get_adj IS
1025 SELECT adjustment_id,
1026 transaction_type_code,
1027 transaction_header_id,
1028 nvl(mass_reference_id,0),
1029 adjustment_status
1030 FROM igi_iac_transaction_headers
1031 WHERE asset_id = x_asset_id
1032 AND book_type_code = x_book_type_code
1033 AND adjustment_id_out is null;
1034
1035 CURSOR get_prev_adj(c_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE) IS
1036 SELECT adjustment_id,
1037 transaction_type_code,
1038 transaction_header_id,
1039 nvl(mass_reference_id,0),
1040 adjustment_status
1041 FROM igi_iac_transaction_headers
1042 WHERE asset_id = x_asset_id
1043 AND book_type_code = x_book_type_code
1044 AND adjustment_id_out = c_adjustment_id;
1045
1046 CURSOR get_adj_Trans_Type IS
1047 SELECT max(adjustment_id)
1048 FROM igi_iac_transaction_headers
1049 WHERE asset_id = x_asset_id
1050 AND book_type_code = x_book_type_code
1051 AND transaction_type_code = X_TRANSACTION_TYPE_CODE;
1052
1053 CURSOR get_trans (p_adjustment_id Number) IS
1054 SELECT transaction_header_id, nvl(mass_reference_id,0), adjustment_status
1055 FROM igi_iac_transaction_headers
1056 WHERE asset_id = x_asset_id
1057 AND book_type_code = x_book_type_code
1058 AND adjustment_id = p_adjustment_id;
1059
1060 l_non_preview_trans BOOLEAN;
1061 l_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE;
1062 l_path_name VARCHAR2(150);
1063 BEGIN
1064 l_non_preview_trans := FALSE;
1065 l_path_name := g_path||'get_latest_transaction';
1066
1067 IF X_transaction_type_code IS NULL THEN
1068 OPEN get_adj;
1069 FETCH get_adj Into x_adjustment_id,
1070 x_transaction_type_code,
1071 x_transaction_id,
1072 x_mass_reference_id,
1073 x_adjustment_status;
1074 IF get_adj%FOUND THEN
1075 CLOSE get_adj;
1076 IF NOT(x_transaction_type_code = 'REVALUATION' AND x_adjustment_status IN ('PREVIEW','OBSOLETE')) THEN
1077 l_non_preview_trans := TRUE;
1078 x_prev_adjustment_id := x_adjustment_id;
1079 return TRUE;
1080 ELSE
1081 l_adjustment_id := x_adjustment_id;
1082
1083 WHILE NOT l_non_preview_trans LOOP
1084 OPEN get_prev_adj(l_adjustment_id);
1085 FETCH get_prev_adj INTO x_prev_adjustment_id,
1086 x_transaction_type_code,
1087 x_transaction_id,
1088 x_mass_reference_id,
1089 x_adjustment_status;
1090 IF get_prev_adj%NOTFOUND THEN
1091 CLOSE get_prev_adj;
1092 return FALSE;
1093 ELSE
1094 CLOSE get_prev_adj;
1095 END IF;
1096
1097 IF NOT(x_transaction_type_code = 'REVALUATION' AND x_adjustment_status IN ('PREVIEW','OBSOLETE')) THEN
1098 l_non_preview_trans := TRUE;
1099 ELSE
1100 l_adjustment_id := x_prev_adjustment_id;
1101 END IF;
1102 END LOOP;
1103 return TRUE;
1104 END IF;
1105 ELSE
1106 CLOSE get_adj;
1107 return FALSE;
1108 END IF;
1109 ELSE -- X_transaction_type_code is not null
1110 OPEN get_adj_trans_type;
1111 FETCH get_adj_trans_type INTO x_adjustment_id;
1112 IF get_adj_trans_type%FOUND THEN
1113 OPEN get_trans(x_adjustment_id);
1114 FETCH get_trans into x_transaction_id,x_mass_reference_id, x_adjustment_status;
1115 IF get_trans%FOUND THEN
1116 x_prev_adjustment_id := x_adjustment_id;
1117 CLOSE get_trans;
1118 CLOSE get_adj_trans_type;
1119 return TRUE;
1120 ELSE
1121 CLOSE get_trans;
1122 CLOSE get_adj_trans_type;
1123 return FALSE;
1124 END IF;
1125 END IF;
1126 END IF;
1127 return FALSE;
1128 EXCEPTION
1129 WHEN OTHERS THEN
1130 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1131 return FALSE;
1132 END Get_Latest_Transaction;
1133
1134
1135 /*
1136 Get_Book_GL_Info gets the general ledger info for the book type
1137 */
1138
1139 FUNCTION Get_Book_GL_Info ( X_book_type_code IN VARCHAR2
1140 ,Set_Of_Books_Id IN OUT NOCOPY NUMBER
1141 ,Chart_Of_Accounts_Id IN OUT NOCOPY NUMBER
1142 ,Currency IN OUT NOCOPY VARCHAR2
1143 ,Precision IN OUT NOCOPY NUMBER
1144 )
1145 RETURN BOOLEAN IS
1146 Cursor get_gl_info(p_book_type_code Varchar2) is
1147 SELECT bc.accounting_flex_structure, bc.set_of_books_id
1148 FROM fa_book_controls bc
1149 WHERE bc.book_type_code = p_book_type_code
1150 AND bc.date_ineffective IS NULL ;
1151 Cursor get_curr (p_set_of_books_id Number) is
1152 SELECT currency_code
1153 FROM gl_sets_of_books
1154 WHERE set_of_books_id = p_set_of_books_id;
1155 Cursor get_precision ( p_currency_code varchar2) is
1156 SELECT Precision
1157 FROM fnd_currencies
1158 WHERE currency_code = p_currency_code;
1159 l_book_type_code varchar2(255);
1160 l_chart_of_accounts_id Number;
1161 l_set_of_books_id Number;
1162 l_currency_code VARCHAR2(10);
1163 l_precision Number;
1164 l_path_name VARCHAR2(150);
1165 BEGIN
1166 l_path_name := g_path||'get_book_gl_info';
1167 Open get_gl_info (X_book_type_code);
1168 Fetch get_gl_info into l_chart_of_accounts_id,
1169 l_set_of_books_id;
1170 IF get_gl_info%FOUND THEN
1171 Chart_of_accounts_id := l_chart_of_accounts_id;
1172 set_of_books_id := l_set_of_books_id;
1173 Open get_curr(l_set_of_books_id);
1174 Fetch get_curr into l_currency_code;
1175 IF get_curr%FOUND THEN
1176 Currency := l_currency_code;
1177 open get_precision (l_currency_code);
1178 Fetch get_precision into l_precision;
1179 IF get_precision%FOUND THEN
1180 Precision := l_precision;
1181 Close get_precision;
1182 Close get_curr;
1183 Close get_gl_info;
1184 Return TRUE;
1185 ELSE
1186 Precision :=Null;
1187 Close get_precision;
1188 Close get_curr;
1189 Close get_gl_info;
1190 RETURN FALSE;
1191 END IF;
1192 ELSE
1193 Currency := Null;
1194 Close get_curr;
1195 Close get_gl_info;
1196 RETURN FALSE;
1197 END IF;
1198 ELSE
1199 Chart_of_accounts_id := Null;
1200 set_of_books_id := Null;
1201 close get_gl_info;
1202 RETURN FALSE;
1203 End if;
1204 EXCEPTION
1205 WHEN OTHERS THEN
1206 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1207 RETURN FALSE;
1208 END;-- Get_Book_GL_Info;
1209
1210 /*
1211 This functions RETURNs the value contained in the GL_ACCOUNT segment of a code combination account
1212 Parameters possible segment types are 'GL_ACCOUNT', 'GL_BALANCING', 'FA_COST_CTR'
1213 */
1214
1215 FUNCTION Get_Account_Segment_Value (
1216 X_sob_id IN gl_sets_of_books.set_of_books_id%TYPE,
1217 X_code_combination_id IN fa_distribution_history.code_combination_id%TYPE,
1218 X_segment_type IN VARCHAR2 ,
1219 X_segment_value IN OUT NOCOPY VARCHAR2 )
1220 RETURN BOOLEAN IS
1221 l_account_value VARCHAR2(30);
1222 l_segment VARCHAR2(30);
1223 l_cursor_hANDle NUMBER;
1224 l_sel_stmt VARCHAR2(1024) ;
1225 l_sel_cursor NUMBER;
1226 l_sel_column VARCHAR2(30);
1227 l_sel_rows NUMBER;
1228 l_sel_execute VARCHAR2(1024);
1229 l_path_name VARCHAR2(150);
1230 BEGIN
1231 l_path_name := g_path||'get_account_segment_value';
1232 SELECT application_column_name
1233 INTO l_segment
1234 FROM fnd_segment_attribute_values ,
1235 gl_sets_of_books sob
1236 WHERE id_flex_code = 'GL#'
1237 AND attribute_value = 'Y'
1238 AND segment_attribute_type = X_segment_type
1239 AND application_id = 101
1240 AND sob.chart_of_accounts_id = id_flex_num
1241 AND sob.set_of_books_id = X_sob_id;
1242
1243 EXECUTE IMMEDIATE ' SELECT '||l_segment ||
1244 ' FROM gl_code_combinations '||
1245 ' WHERE code_combination_id = :X_ccid '
1246 INTO l_sel_column USING IN X_code_combination_id;
1247
1248 X_segment_value := l_sel_column;
1249 RETURN TRUE ;
1250 EXCEPTION
1251 WHEN OTHERS THEN
1252 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1253 RETURN FALSE ;
1254 END Get_Account_Segment_Value;
1255
1256 FUNCTION Get_Distribution_Ccid (
1257 X_book_type_code IN VARCHAR2,
1258 X_asset_id IN NUMBER,
1259 X_Distribution_Id IN NUMBER,
1260 Dist_CCID IN OUT NOCOPY NUMBER )
1261 RETURN BOOLEAN IS
1262 Cursor get_dist_ccid (p_book_type_code Varchar2,
1263 P_asset_id Number,
1264 p_distribution_id Number) IS
1265 SELECT code_combination_id
1266 FROM fa_distribution_history
1267 WHERE Book_type_code = p_book_type_code
1268 AND asset_id = p_asset_id
1269 AND distribution_id = p_distribution_id;
1270
1271 l_path_name VARCHAR2(150);
1272 BEGIN
1273 l_path_name := g_path||'get_distribution_ccid';
1274
1275 Open get_dist_ccid ( X_book_type_code,
1276 X_asset_id,
1277 X_distribution_id);
1278 Fetch get_dist_ccid into dist_CCID;
1279 IF get_dist_ccid%FOUND THEN
1280 Close get_dist_ccid;
1281 RETURN TRUE;
1282 END IF;
1283 IF get_dist_ccid%ISOPEN THEN
1284 Close get_dist_ccid;
1285 END IF;
1286 RETURN FALSE;
1287 EXCEPTION
1288 WHEN OTHERS THEN
1289 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1290 RETURN FALSE ;
1291 END;-- Get_Distribution_Ccid;
1292
1293 FUNCTION Get_Default_Account (
1294 X_book_type_code IN VARCHAR2,
1295 Default_Account IN OUT NOCOPY NUMBER )
1296 RETURN BOOLEAN IS
1297 Cursor get_default (p_book_type_code Varchar2) IS
1298 SELECT flexbuilder_defaults_ccid
1299 FROM fa_book_controls
1300 WHERE book_type_code = p_book_type_code;
1301 l_path_name VARCHAR2(150);
1302 BEGIN
1303 l_path_name := g_path||'get_default_account';
1304 open get_default ( x_book_type_code);
1305 Fetch get_default into default_account;
1306 IF get_default%FOUND THEN
1307 close get_default;
1308 RETURN TRUE;
1309 END IF;
1310 IF get_default%ISOPEN THEN
1311 close get_default;
1312 END IF;
1313 RETURN FALSE;
1314 EXCEPTION
1315 WHEN OTHERS THEN
1316 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
1317 RETURN FALSE ;
1318 END;-- Get_Default_Account;
1319
1320 -- For FND logging, this function is aliased as get_account_ccid1 since this is an overloaded
1321 -- function
1322 FUNCTION Get_Account_CCID (
1323 X_book_type_code IN VARCHAR2,
1324 X_asset_id IN NUMBER,
1325 X_Distribution_ID IN NUMBER,
1326 X_Account_Type IN VARCHAR2,
1327 Account_CCID IN OUT NOCOPY NUMBER )
1328 RETURN BOOLEAN IS
1329 CURSOR get_accounts IS
1330 SELECT nvl(ASSET_COST_ACCOUNT_CCID, -1),
1331 nvl(ASSET_CLEARING_ACCOUNT_CCID, -1),
1332 nvl(DEPRN_EXPENSE_ACCOUNT_CCID, -1),
1333 nvl(DEPRN_RESERVE_ACCOUNT_CCID, -1),
1334 nvl(CIP_COST_ACCOUNT_CCID, -1),
1335 nvl(CIP_CLEARING_ACCOUNT_CCID, -1),
1336 nvl(NBV_RETIRED_GAIN_CCID,-1),
1337 nvl(NBV_RETIRED_LOSS_CCID,-1),
1338 nvl(PROCEEDS_SALE_GAIN_CCID,-1),
1339 nvl(PROCEEDS_SALE_LOSS_CCID,-1),
1340 nvl(COST_REMOVAL_GAIN_CCID,-1),
1341 nvl(COST_REMOVAL_LOSS_CCID,-1),
1342 nvl(COST_REMOVAL_CLEARING_CCID,-1),
1343 nvl(PROCEEDS_SALE_CLEARING_CCID,-1),
1344 --nvl(REVAL_RSV_ACCOUNT_CCID,-1),
1345 --nvl(REVAL_RSV_GAIN_ACCOUNT_CCID,-1),
1346 -- nvl(REVAL_RSV_LOSS_ACCOUNT_CCID,-1),
1347 bc.accounting_flex_structure
1348 FROM FA_DISTRIBUTION_ACCOUNTS da,
1349 FA_BOOK_CONTROLS bc
1350 WHERE bc.book_type_code = X_book_type_code
1351 AND da.book_type_code = bc.book_type_code
1352 AND da.distribution_id = X_distribution_id;
1353
1354 CURSOR validate_ccid IS
1355 SELECT 'VALID'
1356 FROM gl_code_combinations glcc
1357 WHERE glcc.code_combination_id = Account_ccid
1358 AND glcc.enabled_flag = 'Y'
1359 AND nvl(glcc.end_date_active, sysdate) >= sysdate;
1360
1361 CURSOR get_category_id IS
1362 SELECT asset_category_id
1363 FROM fa_additions
1364 WHERE asset_id = X_asset_id;
1365
1366 Cursor get_iac_category_id is
1367 SELECT category_id
1368 FROM igi_iac_transaction_headers
1369 Where asset_id = X_asset_id
1370 and book_type_code = X_book_type_code
1371 and adjustment_id_out is null;
1372
1373
1374 CURSOR get_category_accounts (p_asset_category_Id Number) IS
1375 SELECT nvl(BACKLOG_DEPRN_RSV_CCID,-1),
1376 nvl(GENERAL_FUND_CCID,-1),
1377 nvl(OPERATING_EXPENSE_CCID,-1),
1378 nvl(REVAL_RSV_CCID,-1),
1379 nvl(REVAL_RSV_RET_CCID,-1)
1380 FROM igi_iac_category_books
1381 WHERE book_type_code = X_book_type_code
1382 AND category_id = p_asset_category_id;
1383
1384 CURSOR get_reval_rsv_ccid (p_asset_category_id number) is
1385 SELECT nvl(REVAL_RESERVE_ACCOUNT_CCID,-1)
1386 FROM fa_category_books
1387 WHERE book_type_Code = X_book_type_code
1388 AND category_id = p_asset_category_id;
1389
1390 CURSOR c_get_intercompany is
1391 SELECT AP_INTERCOMPANY_ACCT,AR_INTERCOMPANY_ACCT
1392 FROM FA_BOOK_CONTROLS
1393 WHERE book_type_code = X_book_TYPE_Code;
1394
1395 CURSOR get_accounts_from_FA is
1396 SELECT deprn_reserve_acct, asset_cost_acct
1397 FROM FA_CATEGORY_BOOKS
1398 WHERE book_type_code = X_book_type_code
1399 AND category_id IN
1400 (SELECT asset_category_id
1401 FROM fa_additions
1402 WHERE asset_id = X_asset_id);
1403
1404 CURSOR c_get_nbv_accounts is
1405 SELECT nbv_retired_gain_acct,nbv_retired_loss_acct
1406 FROM FA_BOOK_CONTROLS
1407 WHERE book_type_code = X_book_type_code;
1408
1409 l_cost_ccid number ;
1410 l_clearing_ccid number ;
1411 l_expense_ccid number ;
1412 l_reserve_ccid number ;
1413 l_cip_cost_ccid number ;
1414 l_cip_clearing_ccid number ;
1415 l_nbv_retired_gain_ccid number ;
1416 l_nbv_retired_loss_ccid number ;
1417 l_pos_gain_ccid number ;
1418 l_pos_loss_ccid number ;
1419 l_cost_removal_gain_ccid number ;
1420 l_cost_removal_loss_ccid number ;
1421 l_cor_clearing_ccid number ;
1422 l_pos_clearing_ccid number ;
1423 l_reval_reserve_ccid number ;
1424 l_reval_rsv_retired_gain_ccid number ;
1425 l_reval_rsv_retired_loss_ccid number ;
1426 l_reval_reserve_retired_ccid number ;
1427 l_flex_num number ;
1428 l_ccid_valid varchar2(10) ;
1429 n_segs number;
1430 all_segments fnd_flex_ext.SegmentArray;
1431 delim varchar2(1);
1432 get_segs_success boolean;
1433 l_ret_value boolean ;
1434 l_distribution_ccid Number ;
1435 l_default_ccid Number ;
1436 l_set_of_books_id Number ;
1437 l_chart_of_accounts_id Number ;
1438 l_currency Varchar2(5);
1439 l_precision Number;
1440 l_back_deprn_rsv_ccid Number ;
1441 l_general_fund_ccid Number ;
1442 l_operation_expense_ccid Number ;
1443 l_account_value Varchar2(250);
1444 l_deprn_reserve_acct Varchar2(250);
1445 l_asset_cost_acct Varchar2(250);
1446 l_nbv_retired_gain_acct Varchar2(250);
1447 l_nbv_retired_loss_acct Varchar2(250);
1448 l_asset_category_id Number;
1449 l_account_ccid Number ;
1450 l_path_name VARCHAR2(150) ;
1451 l_interco_ap_acct number ;
1452 l_interco_ar_acct number ;
1453 x_concat_segs VARCHAR2(780);
1454 x_return_value NUMBER;
1455
1456 BEGIN
1457 l_cost_ccid :=0;
1458 l_clearing_ccid :=0;
1459 l_expense_ccid :=0;
1460 l_reserve_ccid :=0;
1461 l_cip_cost_ccid :=0;
1462 l_cip_clearing_ccid :=0;
1463 l_nbv_retired_gain_ccid :=0;
1464 l_nbv_retired_loss_ccid :=0;
1465 l_pos_gain_ccid :=0;
1466 l_pos_loss_ccid :=0;
1467 l_cost_removal_gain_ccid :=0;
1468 l_cost_removal_loss_ccid :=0;
1469 l_cor_clearing_ccid :=0;
1470 l_pos_clearing_ccid :=0;
1471 l_reval_reserve_ccid :=0;
1472 l_reval_rsv_retired_gain_ccid :=0;
1473 l_reval_rsv_retired_loss_ccid :=0;
1474 l_reval_reserve_retired_ccid :=0;
1475 l_flex_num := null;
1476 l_ccid_valid := NULL;
1477 l_ret_value := FALSE;
1478 l_distribution_ccid := 0;
1479 l_default_ccid :=0;
1480 l_set_of_books_id :=0;
1481 l_chart_of_accounts_id :=0;
1482 l_precision :=0;
1483 l_back_deprn_rsv_ccid := 0;
1484 l_general_fund_ccid :=0;
1485 l_operation_expense_ccid :=0;
1486 l_asset_category_id :=0;
1487 l_account_ccid :=0;
1488 l_path_name := g_path||'get_account_ccid1';
1489 l_interco_ap_acct :=0;
1490 l_interco_ar_acct :=0;
1491
1492 /* IAC Category account then call IAC work flow to generate the work flow */
1493 /* get and set all parameters required for start proces of workflow */
1494 /* get the distribution ccid */
1495 --log('+ get ccid call');
1496
1497 IF get_distribution_ccid(X_book_type_code,
1498 X_asset_id,
1499 X_distribution_id,
1500 l_distribution_ccid) THEN
1501 Null;
1502 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1503 p_full_path => l_path_name,
1504 p_string => 'get distribution sucessfull ..'|| l_distribution_ccid);
1505 ELSE
1506 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1507 p_full_path => l_path_name,
1508 p_string => 'get distribution failed ..'|| l_distribution_ccid);
1509 Account_ccid := -1;
1510 RETURN FALSE;
1511 END IF;
1512 --log('+ get ccid call get_distribution_ccid'||to_char(l_distribution_ccid));
1513 /* get the deault ccid from fa book controls */
1514 IF get_default_account ( X_book_type_code,
1515 l_default_ccid)THEN
1516
1517 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1518 p_full_path => l_path_name,
1519 p_string => 'get default account sucessfull ..'|| l_default_ccid);
1520 Null;
1521 ELSE
1522 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1523 p_full_path => l_path_name,
1524 p_string => 'get default account failed..'|| l_default_ccid);
1525 Account_ccid := -1;
1526 RETURN FALSE;
1527 END IF;
1528 -- log('+ get ccid call get_default_account'||to_char(l_default_ccid));
1529 /* get the chart of accounts id */
1530 IF get_book_gl_info (X_book_type_code,
1531 l_set_of_books_id,
1532 l_chart_of_accounts_id,
1533 l_currency,
1534 l_precision ) THEN
1535 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1536 p_full_path => l_path_name,
1537 p_string => 'get gl info sucessfull ..'|| l_chart_of_accounts_id);
1538 Null;
1539 ELSE
1540 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1541 p_full_path => l_path_name,
1542 p_string => 'get gl info failed ..'|| l_chart_of_accounts_id);
1543 Account_ccid := -1;
1544 RETURN FALSE;
1545 END IF;
1546 --log('+ get ccid call get_gl_info'||to_char(l_chart_of_accounts_id));
1547
1548
1549 IF X_ACCOUNT_TYPE IN ('BACKLOG_DEPRN_RSV_ACCT',
1550 'OPERATING_EXPENSE_ACCT',
1551 'GENERAL_FUND_ACCT',
1552 'REVAL_RESERVE_ACCT',
1553 'REVAL_RESERVE_RETIRED_ACCT') THEN
1554
1555 /*get the account ccid and segment value */
1556 OPEN get_iac_category_id;
1557 FETCH get_iac_category_id into l_asset_category_id;
1558 IF NOT get_iac_category_id%FOUND THEN
1559 OPEN get_category_id;
1560 FETCH get_category_id into l_asset_category_id;
1561 IF NOT get_category_id%FOUND THEN
1562 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1563 p_full_path => l_path_name,
1564 p_string => 'get asset id failed ..'||l_asset_category_id);
1565 Close get_category_id;
1566 Account_ccid := -1;
1567 RETURN FALSE;
1568 END IF;
1569 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1570 p_full_path => l_path_name,
1571 p_string => 'get asset id success..'||l_asset_category_id);
1572 CLOSE get_category_id;
1573 END IF;
1574 Close get_iac_category_id;
1575
1576 OPEN get_category_accounts(l_asset_category_id);
1577 FETCH get_category_accounts INTO l_back_deprn_rsv_ccid,
1578 l_general_fund_ccid,
1579 l_operation_expense_ccid,
1580 l_reval_reserve_ccid,
1581 l_reval_reserve_retired_ccid;
1582 IF NOT get_category_accounts%FOUND THEN
1583 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1584 p_full_path => l_path_name,
1585 p_string => 'get account ccid form iac books failed ..'||l_account_ccid);
1586 CLOSE get_category_accounts;
1587 Account_ccid := -1;
1588 RETURN FALSE;
1589 END IF;
1590
1591 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1592 p_full_path => l_path_name,
1593 p_string => '+ get ccid call account type'||x_account_type);
1594
1595 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1596 p_full_path => l_path_name,
1597 p_string => '+ get ccid call account type'||x_account_type);
1598 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1599 p_full_path => l_path_name,
1600 p_string => '+ get ccid call get_category_accounts back log '||to_char(l_back_deprn_rsv_ccid));
1601 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1602 p_full_path => l_path_name,
1603 p_string => '+get ccid call get_category_accounts general fund '||to_char(l_general_fund_ccid));
1604 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1605 p_full_path => l_path_name,
1606 p_string => '+ get ccid call get_category_accounts oper exp'||to_char(l_operation_expense_ccid));
1607
1608 IF X_account_Type = 'BACKLOG_DEPRN_RSV_ACCT' THEN
1609 l_account_ccid := l_back_deprn_rsv_ccid;
1610 ELSIF X_account_Type = 'OPERATING_EXPENSE_ACCT' THEN
1611 l_account_ccid := l_operation_expense_ccid;
1612 ELSIF X_account_Type = 'GENERAL_FUND_ACCT' THEN
1613 l_account_ccid := l_general_fund_ccid;
1614 ELSIF X_account_Type = 'REVAL_RESERVE_ACCT' THEN
1615 l_account_ccid := l_reval_reserve_ccid;
1616 ELSIF X_account_Type = 'REVAL_RESERVE_RETIRED_ACCT' THEN
1617 l_account_ccid := l_reval_reserve_retired_ccid;
1618 END IF;
1619
1620 /* get the segment value for the account ccid */
1621 IF NOT Get_account_segment_value (l_set_of_books_id,
1622 l_account_ccid,
1623 'GL_ACCOUNT',
1624 l_account_value) THEN
1625 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1626 p_full_path => l_path_name,
1627 p_string => '+ get gl-account failed '|| l_account_value);
1628 Account_ccid := -1;
1629 RETURN FALSE;
1630 END IF;
1631 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1632 p_full_path => l_path_name,
1633 p_string => '+ get gl-account success '|| l_account_value);
1634
1635 IF NOT IGI_IAC_WF_PKG.Start_process (
1636 X_flex_account_type => X_account_type,
1637 X_book_type_code => X_book_type_code,
1638 X_chart_of_accounts_id => l_chart_of_accounts_id,
1639 X_dist_ccid => l_distribution_ccid,
1640 X_acct_segval => l_account_value,
1641 X_default_ccid => l_default_ccid,
1642 X_account_ccid => l_account_ccid,
1643 X_distribution_id => X_distribution_id,
1644 X_workflowprocess => 'IGIIACWF',
1645 X_RETURN_ccid => account_ccid ) THEN
1646
1647 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1648 p_full_path => l_path_name,
1649 p_string => '+ get ccid call wf failed '||to_char(account_ccid));
1650
1651 Account_ccid := -1;
1652 RETURN FALSE;
1653 ELSE
1654 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1655 p_full_path => l_path_name,
1656 p_string => '+ get ccid call wf sucess '||to_char(account_ccid));
1657 RETURN TRUE;
1658 END IF;
1659 -- account_ccid := l_default_ccid;
1660 -- return true;
1661 ELSIF X_ACCOUNT_TYPE in ('INTERCO_AP_ACCT','INTERCO_AR_ACCT') THEN
1662
1663 -- get the inter company account segements from fa_book
1664
1665 open c_get_intercompany;
1666 Fetch c_get_intercompany into l_interco_ap_acct,l_interco_ar_acct;
1667 If c_get_intercompany%Notfound THEN
1668 close c_get_intercompany;
1669 Account_ccid := -1;
1670 RETURN FALSE;
1671 Else
1672 If x_account_type = 'INTERCO_AP_ACCT' THEN
1673 l_account_value := l_interco_ap_acct;
1674 elsif x_account_type = 'INTERCO_AR_ACCT' THEN
1675 l_account_value := l_interco_ar_acct;
1676 end if ;
1677
1678 IF NOT IGI_IAC_WF_PKG.Start_process (
1679 X_flex_account_type => X_account_type,
1680 X_book_type_code => X_book_type_code,
1681 X_chart_of_accounts_id => l_chart_of_accounts_id,
1682 X_dist_ccid => l_distribution_ccid,
1683 X_acct_segval => l_account_value,
1684 X_default_ccid => l_default_ccid,
1685 X_account_ccid => l_default_ccid,
1686 X_distribution_id => X_distribution_id,
1687 X_workflowprocess => 'IGIIACWF',
1688 X_RETURN_ccid => account_ccid ) THEN
1689
1690 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1691 p_full_path => l_path_name,
1692 p_string => '+ get ccid call wf failed '||to_char(account_ccid));
1693
1694 Account_ccid := -1;
1695 RETURN FALSE;
1696 ELSE
1697 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1698 p_full_path => l_path_name,
1699 p_string => '+ get ccid call wf sucess '||to_char(account_ccid));
1700 RETURN TRUE;
1701 END IF;
1702 END IF;
1703 ELSE /* FA Account type get the ccid form the FA Distribution */
1704 OPEN get_accounts;
1705 FETCH get_accounts into
1706 l_cost_ccid,
1707 l_clearing_ccid,
1708 l_expense_ccid,
1709 l_reserve_ccid,
1710 l_cip_cost_ccid,
1711 l_cip_clearing_ccid,
1712 l_nbv_retired_gain_ccid,
1713 l_nbv_retired_loss_ccid,
1714 l_pos_gain_ccid,
1715 l_pos_loss_ccid,
1716 l_cost_removal_gain_ccid,
1717 l_cost_removal_loss_ccid,
1718 l_cor_clearing_ccid,
1719 l_pos_clearing_ccid,
1720 --l_reval_reserve_ccid,
1721 --l_reval_rsv_retired_gain_ccid,
1722 --l_reval_rsv_retired_loss_ccid,
1723 l_flex_num;
1724
1725 if (get_accounts%FOUND) then
1726 if (X_account_type = 'ASSET_COST_ACCT') then
1727 account_ccid := l_cost_ccid;
1728 elsif (X_account_type = 'ASSET_CLEARING_ACCT') then
1729 account_ccid := l_clearing_ccid;
1730 elsif (X_account_type = 'DEPRN_RESERVE_ACCT') then
1731 account_ccid := l_reserve_ccid;
1732 elsif (X_account_type = 'DEPRN_EXPENSE_ACCT') then
1733 account_ccid := l_expense_ccid;
1734 elsif (X_account_type = 'CIP_COST_ACCT') then
1735 account_ccid := l_cip_cost_ccid;
1736 elsif (X_account_type = 'CIP_CLEARING_ACCT') then
1737 account_ccid := l_cip_clearing_ccid;
1738 elsif (X_account_type = 'NBV_RETIRED_GAIN_ACCT') then
1739 account_ccid := l_nbv_retired_gain_ccid;
1740 elsif (X_account_type = 'NBV_RETIRED_LOSS_ACCT') then
1741 account_ccid := l_nbv_retired_loss_ccid;
1742 elsif (X_account_type = 'PROCEEDS_OF_SALE_GAIN_ACCT') then
1743 account_ccid := l_pos_gain_ccid;
1744 elsif (X_account_type = 'PROCEEDS_OF_SALE_LOSS_ACCT') then
1745 account_ccid := l_pos_loss_ccid;
1746 elsif (X_account_type = 'COST_OF_REMOVAL_GAIN_ACCT') then
1747 account_ccid := l_cost_removal_gain_ccid;
1748 elsif (X_account_type = 'COST_OF_REMOVAL_LOSS_ACCT') then
1749 account_ccid := l_cost_removal_gain_ccid;
1750 elsif (X_account_type = 'COST_OF_REMOVAL_CLEARING_ACCT') then
1751 account_ccid := l_cor_clearing_ccid;
1752 elsif (X_account_type = 'PROCEEDS_OF_SALE_CLEARING_ACCT') then
1753 account_ccid := l_pos_clearing_ccid;
1754 --elsif (X_account_type = 'REVAL_RESERVE_ACCT') then
1755 ---- account_ccid := l_reval_reserve_ccid;
1756 --elsif (X_account_type = 'REVAL_RSV_RETIRED_GAIN_ACCT') then
1757 --- account_ccid := l_reval_rsv_retired_gain_ccid;
1758 --- elsif (X_account_type = 'REVAL_RSV_RETIRED_LOSS_ACCT') then
1759 --- account_ccid := l_reval_rsv_retired_loss_ccid;
1760
1761 end if;
1762 if account_ccid > 0 then
1763 l_ret_value := TRUE;
1764 end if;
1765
1766 else
1767 account_ccid := -1;
1768 end if;
1769
1770 if(account_ccid = -1) then
1771 -- Call FA package to get the Valid CCID.
1772
1773
1774 IF X_account_type in ('DEPRN_RESERVE_ACCT','ASSET_COST_ACCT') THEN
1775 OPEN get_accounts_from_FA;
1776 FETCH get_accounts_from_FA INTO
1777 l_deprn_reserve_acct,
1778 l_asset_cost_acct;
1779
1780 IF (get_accounts_from_FA%FOUND) THEN
1781 IF (X_account_type = 'DEPRN_RESERVE_ACCT') THEN
1782 l_account_value := l_deprn_reserve_acct;
1783 ELSIF (X_account_type = 'ASSET_COST_ACCT') THEN
1784 l_account_value := l_asset_cost_acct;
1785 END IF;
1786 END IF;
1787 CLOSE get_accounts_from_FA;
1788 ELSIF X_account_type in ('NBV_RETIRED_GAIN_ACCT','NBV_RETIRED_LOSS_ACCT') THEN
1789 OPEN c_get_nbv_accounts;
1790 FETCH c_get_nbv_accounts INTO l_nbv_retired_gain_acct,l_nbv_retired_loss_acct;
1791
1792 IF c_get_nbv_accounts%FOUND THEN
1793 IF (X_account_type = 'NBV_RETIRED_GAIN_ACCT') THEN
1794 l_account_value := l_nbv_retired_gain_acct;
1795 ELSIF (X_account_type = 'NBV_RETIRED_LOSS_ACCT') THEN
1796 l_account_value := l_nbv_retired_loss_acct;
1797 END IF;
1798 END IF;
1799 CLOSE c_get_nbv_accounts;
1800 END IF;
1801
1802 IF (X_account_type = 'DEPRN_EXPENSE_ACCT') THEN
1803 l_account_value := null;
1804 l_account_ccid := l_distribution_ccid;
1805 ELSE
1806 l_account_ccid := null;
1807 END IF;
1808
1809 FA_GCCID_PKG.fafbgcc_proc
1810 (X_book_type_code => X_book_type_code,
1811 X_fn_trx_code => X_account_type,
1812 X_dist_ccid => l_distribution_ccid,
1813 X_acct_segval => l_account_value,
1814 X_account_ccid => l_account_ccid,
1815 X_distribution_id => X_distribution_id,
1816 X_rtn_ccid => account_ccid,
1817 X_concat_segs => x_concat_segs,
1818 X_return_value => x_return_value);
1819
1820 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1821 p_full_path => l_path_name,
1822 p_string => '+ get ccid call FA wf success = >'||to_char(account_ccid));
1823 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1824 p_full_path => l_path_name,
1825 p_string => '+ get ccid call FA wf return value = >'||to_char(x_return_value));
1826
1827 IF (x_return_value = 0) then
1828 account_ccid := -1;
1829 END IF;
1830 end if;
1831 CLOSE get_accounts;
1832
1833 if (account_ccid > 0) then
1834
1835 open validate_ccid;
1836 fetch validate_ccid into l_ccid_valid;
1837 if (validate_ccid%NOTFOUND) then
1838 get_segs_success := FND_FLEX_EXT.get_segments(
1839 application_short_name => 'SQLGL',
1840 key_flex_code => 'GL#',
1841 structure_number => l_flex_num,
1842 combination_id => account_ccid,
1843 n_segments => n_segs,
1844 segments => all_segments);
1845 delim := FND_FLEX_EXT.get_delimiter(
1846 application_short_name => 'SQLGL',
1847 key_flex_code => 'GL#',
1848 structure_number => l_flex_num);
1849 FA_GCCID_PKG.global_concat_segs :=
1850 FND_FLEX_EXT.concatenate_segments(
1851 n_segments => n_segs,
1852 segments => all_segments,
1853 delimiter => delim);
1854 l_ret_value := FALSE;
1855 else
1856 l_ret_value := TRUE;
1857 end if;
1858 close validate_ccid;
1859 else
1860 l_ret_value := FALSE;
1861 end if;
1862
1863 RETURN (l_ret_value);
1864 END IF;
1865 RETURN FALSE;
1866
1867
1868 END;-- Get_Account_CCID;
1869
1870
1871 -- For FND logging, this function is alised as get_account_ccid2 since this is an overloaded
1872 -- function
1873 FUNCTION Get_Account_CCID (
1874 X_book_type_code IN VARCHAR2,
1875 X_asset_id IN NUMBER,
1876 X_Distribution_ID IN NUMBER,
1877 X_Account_Type IN VARCHAR2,
1878 X_Transaction_Header_ID IN NUMBER,
1879 X_Calling_function IN VARCHAR2,
1880 Account_CCID IN OUT NOCOPY NUMBER )
1881 RETURN BOOLEAN IS
1882
1883 CURSOR get_ccid_adjustment(c_adjustment_type Varchar2) is
1884 SELECT nvl(code_combination_id,-1)
1885 FROM fa_adjustments
1886 WHERE book_type_code = X_book_type_code
1887 AND asset_id = X_asset_id
1888 AND distribution_id = X_distribution_id
1889 AND Transaction_header_id = X_TRANSACTION_HEADER_ID
1890 AND Source_type_code = X_calling_function
1891 AND Adjustment_type = c_adjustment_type;
1892
1893
1894 l_return_ccid Number;
1895 l_default_ccid Number;
1896 l_adjustment_type fa_adjustments.adjustment_type%type;
1897 l_path_name VARCHAR2(150) ;
1898
1899 BEGIN
1900 l_path_name := g_path||'get_account_ccid2';
1901 -- calling the function get_account_ccid
1902 IF X_ACCOUNT_TYPE NOT IN ( 'INTERCO_AP_ACCT','INTERCO_AR_ACCT') THEN -- not inter company acct
1903
1904
1905 l_return_ccid := -1;
1906 IF (X_account_type = 'NBV_RETIRED_GAIN_ACCT') THEN
1907
1908 l_adjustment_type := 'NBV RETIRED';
1909 Open get_ccid_adjustment(l_adjustment_type);
1910 fetch get_ccid_adjustment into l_return_ccid;
1911 IF get_ccid_adjustment%FOUND THEN
1912 Account_CCID := l_return_ccid;
1913 IF Account_CCID > 0 THEN
1914 return true;
1915 END IF;
1916 ELSE
1917 l_return_ccid := -1;
1918 END IF;
1919 close get_ccid_adjustment;
1920 END IF;
1921
1922 IF l_return_ccid = -1 THEN
1923
1924 IF NOT GET_ACCOUNT_CCID(X_book_type_code => X_book_type_code,
1925 X_asset_id=>X_asset_id ,
1926 X_Distribution_ID=>X_Distribution_ID,
1927 X_Account_Type=>X_Account_Type,
1928 Account_CCID => l_return_ccid) THEN
1929 Return False;
1930 END IF;
1931
1932 /* get the deault ccid from fa book controls */
1933 IF get_default_account ( X_book_type_code,
1934 l_default_ccid)THEN
1935
1936 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1937 p_full_path => l_path_name,
1938 p_string => 'get default account sucessfull ..'|| l_default_ccid);
1939 Null;
1940 ELSE
1941 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1942 p_full_path => l_path_name,
1943 p_string => 'get default account failed..'|| l_default_ccid);
1944 Account_ccid := -1;
1945 RETURN FALSE;
1946 END IF;
1947
1948
1949
1950 IF l_default_ccid = l_return_ccid Then
1951
1952 /* get the mapping of adjustment type with the account types */
1953
1954 if (X_account_type = 'ASSET_COST_ACCT') then
1955 l_adjustment_type := 'COST';
1956 elsif (X_account_type = 'ASSET_CLEARING_ACCT') then
1957 l_adjustment_type:= 'COST CLEARING';
1958 elsif (X_account_type = 'DEPRN_RESERVE_ACCT') then
1959 l_adjustment_type := 'RESERVE';
1960 elsif (X_account_type = 'DEPRN_EXPENSE_ACCT') then
1961 l_adjustment_type := 'EXPENSE';
1962 elsif (X_account_type = 'REVAL_RSV_RETIRED_GAIN_ACCT') then
1963 l_adjustment_type := 'REVAL RSV RET';
1964 elsif (X_account_type = 'REVAL_RSV_RETIRED_LOSS_ACCT') then
1965 l_adjustment_type := 'REVAL RSV RET';
1966 elsif (X_account_type = 'NBV_RETIRED_GAIN_ACCT') then
1967 l_adjustment_type := 'NBV RETIRED';
1968 elsif (X_account_type = 'NBV_RETIRED_LOSS_ACCT') then
1969 l_adjustment_type := 'NBV RETIRED';
1970 /* elsif (X_account_type = 'NBV_RETIRED_GAIN_ACCT') then
1971 l_adjustment_type := l_nbv_retired_gain_ccid;
1972 elsif (X_account_type = 'NBV_RETIRED_LOSS_ACCT') then
1973 l_adjustment_type := l_nbv_retired_loss_ccid;
1974 elsif (X_account_type = 'PROCEEDS_OF_SALE_GAIN_ACCT') then
1975 l_adjustment_type := l_pos_gain_ccid;
1976 elsif (X_account_type = 'PROCEEDS_OF_SALE_LOSS_ACCT') then
1977 l_adjustment_type := l_pos_loss_ccid;
1978 elsif (X_account_type = 'COST_OF_REMOVAL_GAIN_ACCT') then
1979 l_adjustment_type := l_cost_removal_gain_ccid;
1980 elsif (X_account_type = 'COST_OF_REMOVAL_LOSS_ACCT') then
1981 l_adjustment_type := l_cost_removal_gain_ccid;
1982 elsif (X_account_type = 'COST_OF_REMOVAL_CLEARING_ACCT') then
1983 l_adjustment_type := l_cor_clearing_ccid;
1984 elsif (X_account_type = 'PROCEEDS_OF_SALE_CLEARING_ACCT') then
1985 l_adjustment_type := l_pos_clearing_ccid;
1986 elsif (X_account_type = 'REVAL_RSV_RETIRED_GAIN_ACCT') then
1987 l_adjustment_type := l_reval_rsv_retired_gain_ccid;
1988 elsif (X_account_type = 'REVAL_RSV_RETIRED_LOSS_ACCT') then
1989 l_adjustment_type := l_reval_rsv_retired_loss_ccid;*/
1990 end if;
1991
1992 Open get_ccid_adjustment(l_adjustment_type);
1993 fetch get_ccid_adjustment into l_return_ccid;
1994 If get_ccid_adjustment%NotFOUND THEN
1995 l_return_ccid := l_default_ccid;
1996 end if;
1997 close get_ccid_adjustment;
1998 Account_CCID := l_return_ccid;
1999 IF Account_CCID > 0 THEN
2000 return true;
2001 END IF;
2002 else
2003 Account_CCID := l_return_ccid;
2004 IF Account_CCID > 0 THEN
2005 return true;
2006 END IF;
2007 end if;
2008 END IF;
2009 ELSE -- inter company accts
2010
2011
2012 if (X_account_type = 'INTERCO_AP_ACCT') then
2013 l_adjustment_type := 'INTERCO AP';
2014 elsif (X_account_type = 'INTERCO_AR_ACCT') then
2015 l_adjustment_type:= 'INTERCO AR';
2016 end if;
2017
2018 Open get_ccid_adjustment(l_adjustment_type);
2019 fetch get_ccid_adjustment into l_return_ccid;
2020 If get_ccid_adjustment%FOUND THEN
2021 Account_CCID := l_return_ccid;
2022 return true;
2023 ELSE
2024 -- call workflow to generate the intercompany acct
2025 IF NOT GET_ACCOUNT_CCID(X_book_type_code => X_book_type_code,
2026 X_asset_id=>X_asset_id ,
2027 X_Distribution_ID=>X_Distribution_ID,
2028 X_Account_Type=>X_Account_Type,
2029 Account_CCID => l_return_ccid) THEN
2030 Return False;
2031 END IF;
2032 IF Nvl(l_return_ccid,-1)= -1 Then
2033 Account_CCID := -1;
2034 return false;
2035 else
2036 Account_CCID:=l_return_ccid;
2037 IF Account_CCID > 0 THEN
2038 return true;
2039 END IF;
2040 end if;
2041 END IF;
2042
2043 END IF;
2044
2045 IF Account_CCID = -1 THEN
2046 IF NOT GET_ACCOUNT_CCID(X_book_type_code => X_book_type_code,
2047 X_asset_id=>X_asset_id ,
2048 X_Distribution_ID=>X_Distribution_ID,
2049 X_Account_Type=>X_Account_Type,
2050 Account_CCID => l_return_ccid) THEN
2051 Return False;
2052 ELSE
2053 Return true;
2054 END IF;
2055 ELSE
2056 Return true;
2057 END IF;
2058
2059 END; -- get account ccid
2060
2061 -- FND log changes, stubbed out the following procedures since they shouldnt be used any more
2062 -- Procedures have not been removed since they might have an impact on other files.
2063
2064 PROCEDURE debug_on is
2065 Begin
2066 NULL;
2067 End;
2068
2069 PROCEDURE debug_off is
2070 Begin
2071 NULL;
2072 End;
2073
2074 PROCEDURE debug_write(stmt varchar2) is
2075 begin
2076 Null;
2077 end;
2078
2079 -- FND log changes stubbing of procedures .. End
2080
2081 -- Shekar End
2082
2083 -- M Hazarika, 07-05-2002 start
2084
2085 -- This will function will round the currency amount
2086 -- based on the asset book information
2087 FUNCTION Iac_Round(
2088 X_Amount IN OUT NOCOPY NUMBER,
2089 X_Book IN VARCHAR2)
2090 RETURN BOOLEAN
2091 IS
2092 l_path_name VARCHAR2(150);
2093 BEGIN
2094 l_path_name := g_path||'iac_round';
2095 /*RETURN FA_UTILS_PKG.faxrnd(X_Amount,
2096 X_Book);*/
2097 FA_ROUND_PKG.fa_round(X_Amount,
2098 X_Book);
2099 return TRUE;
2100 EXCEPTION
2101 WHEN OTHERS THEN
2102 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
2103 return FALSE ;
2104 END Iac_Round;
2105
2106 -- M Hazarika, 07-05-2002 end
2107
2108 FUNCTION Populate_iac_fa_deprn_data(
2109 X_book_type_code VARCHAR2,
2110 X_calling_mode VARCHAR2 )
2111 RETURN BOOLEAN IS
2112
2113 CURSOR c_get_iac_fa_data IS
2114 SELECT 'X'
2115 FROM igi_iac_fa_deprn
2116 WHERE book_type_code LIKE x_book_type_code
2117 AND rownum = 1;
2118
2119 l_dummy_char VARCHAR2(1);
2120 l_errbuf VARCHAR2(2000);
2121 l_retcode NUMBER;
2122 l_path_name VARCHAR2(150);
2123
2124 BEGIN
2125 l_path_name := g_path||'populate_iac_fa_deprn_data';
2126 OPEN c_get_iac_fa_data;
2127 FETCH c_get_iac_fa_data INTO l_dummy_char;
2128 IF c_get_iac_fa_data%FOUND THEN
2129 CLOSE c_get_iac_fa_data;
2130 RETURN TRUE;
2131 ELSE
2132 CLOSE c_get_iac_fa_data;
2133 igi_iac_ytd_pre_process_pkg.populate_iac_fa_deprn_data(l_errbuf
2134 , l_retcode
2135 , x_book_type_code
2136 , x_calling_mode);
2137 IF (l_retcode = 0) THEN
2138 RETURN TRUE;
2139 ELSE
2140 RETURN FALSE;
2141 END IF;
2142 END IF;
2143
2144 EXCEPTION WHEN OTHERS THEN
2145 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => l_path_name);
2146 RETURN FALSE;
2147 END populate_iac_fa_deprn_data;
2148
2149
2150 /* Added for Bug 5846861 by Venkataramanan S on 02-Feb-2007
2151 FUNCTION NAME: Is_Asset_Adjustment_Done
2152 PARAMETERS: Book Type Code and Asset Id
2153 RETURN TYPE: BOOLEAN
2154 DESCRIPTION: This function checks whether adjustments have been made in the
2155 open period for the given Asset and Book combination. A "BOOLEAN TRUE" is
2156 returned if adjustments have been done. A "BOOLEAN FALSE" is returned otherwise
2157 */
2158 FUNCTION Is_Asset_Adjustment_Done(
2159 X_book_type_code IN VARCHAR2,
2160 X_asset_id IN NUMBER)
2161 RETURN BOOLEAN IS
2162 CURSOR c_get_asset_adj (p_book_type_code fa_transaction_headers.book_type_code%TYPE,
2163 p_asset_id fa_transaction_headers.asset_id%TYPE,
2164 p_period_counter number) is
2165 SELECT 1
2166 FROM dual
2167 WHERE EXISTS
2168 (SELECT 1
2169 FROM igi_iac_adjustments_history
2170 WHERE book_type_code = p_book_type_code
2171 AND period_counter = p_period_counter
2172 AND asset_id = p_asset_id
2173 AND nvl(current_period_amortization,'N') <> 'Y'
2174 AND nvl(active_flag, 'N') = 'N')
2175 ;
2176
2177 l_get_asset_adj c_get_asset_adj%rowtype;
2178 l_prd_rec IGI_IAC_TYPES.prd_rec;
2179 l_path_name VARCHAR2(250);
2180
2181 BEGIN
2182 l_path_name := g_path||'Is_Asset_Adjustment_Done';
2183
2184 --Fetch the current open period
2185 IF igi_iac_common_utils.get_open_period_info(X_book_type_code,l_prd_rec) THEN
2186 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2187 p_full_path => l_path_name,
2188 p_string => ' + Current Open Period counter'
2189 ||l_prd_rec.period_counter );
2190 ELSE
2191 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2192 p_full_path => l_path_name,
2193 p_string => ' + Error while fetching open period information
2194 + Current Open Period counter' ||l_prd_rec.period_counter );
2195 END IF;
2196
2197 OPEN c_get_asset_adj( X_book_type_code,
2198 X_asset_id ,
2199 l_prd_rec.period_counter);
2200 FETCH c_get_asset_adj INTO l_get_asset_adj;
2201
2202 --If Record exists then this implies adjustment have been done in the
2203 --current open period; return TRUE
2204 --otherwise return FALSE
2205 IF c_get_asset_adj%FOUND THEN
2206 CLOSE c_get_asset_adj;
2207 RETURN TRUE;
2208 ELSE
2209 CLOSE c_get_asset_adj;
2210 RETURN FALSE;
2211 END IF;
2212 END Is_Asset_Adjustment_Done;
2213
2214
2215 BEGIN
2216 --===========================FND_LOG.START=====================================
2217
2218 g_state_level := FND_LOG.LEVEL_STATEMENT;
2219 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
2220 g_event_level := FND_LOG.LEVEL_EVENT;
2221 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
2222 g_error_level := FND_LOG.LEVEL_ERROR;
2223 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
2224 g_path := 'IGI.PLSQL.igiiacub.igi_iac_common_utils.';
2225
2226 --===========================FND_LOG.END=====================================
2227
2228
2229 END; --package body