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