[Home] [Help]
PACKAGE BODY: APPS.IGI_IAC_TRANSFERS_PKG
Source
1 PACKAGE BODY IGI_IAC_TRANSFERS_PKG AS
2 -- $Header: igiiatfb.pls 120.23.12000000.3 2007/11/28 11:19:36 pakumare ship $
3
4
5 --===========================FND_LOG.START=====================================
6
7 g_state_level NUMBER;
8 g_proc_level NUMBER;
9 g_event_level NUMBER;
10 g_excep_level NUMBER;
11 g_error_level NUMBER;
12 g_unexp_level NUMBER;
13 g_path VARCHAR2(100);
14
15 --===========================FND_LOG.END=======================================
16
17 -- package level variables
18 p_trans_rec FA_API_TYPES.trans_rec_type;
19 p_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
20 p_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
21
22 g_prior_period BOOLEAN;
23
24 -- define asset rec type
25 TYPE asset_rec_type IS RECORD (asset_id NUMBER,
26 book_type_code VARCHAR2(15),
27 period_counter NUMBER,
28 net_book_value NUMBER,
29 adjusted_cost NUMBER,
30 operating_acct NUMBER,
31 reval_reserve NUMBER,
32 deprn_amount NUMBER,
33 deprn_reserve NUMBER,
34 backlog_deprn_reserve NUMBER,
35 general_fund NUMBER,
36 last_reval_date DATE,
37 current_reval_factor NUMBER,
38 cumulative_reval_factor NUMBER,
39 reval_reserve_backlog NUMBER,
40 operating_acct_backlog NUMBER,
41 general_fund_per NUMBER,
42 ytd_deprn NUMBER,
43 dep_expense_catchup NUMBER,
44 op_expense_catchup NUMBER
45 );
46
47 TYPE iac_fa_deprn_rec_type IS RECORD (asset_id NUMBER,
48 book_type_code VARCHAR2(15),
49 period_counter NUMBER,
50 deprn_period NUMBER,
51 deprn_reserve NUMBER,
52 deprn_ytd NUMBER,
53 dep_expense_catchup NUMBER
54 );
55
56 -- ===============================================================================
57 -- Procedure to insert data into IGI_IAC_ADJUSTMENTS
58 -- ===============================================================================
59 PROCEDURE insert_data_adj(p_adjustment_id in igi_iac_adjustments.adjustment_id%type,
60 p_book_type_code in igi_iac_adjustments.book_type_code%type,
61 p_code_combination_id in igi_iac_adjustments.code_combination_id%type,
62 p_set_of_books_id in igi_iac_adjustments.set_of_books_id%type,
63 p_dr_cr_flag in igi_iac_adjustments.dr_cr_flag%type,
64 p_amount in igi_iac_adjustments.amount%type,
65 p_adjustment_type in igi_iac_adjustments.adjustment_type%type,
66 p_units_assigned in igi_iac_adjustments.units_assigned%type,
67 p_asset_id in igi_iac_adjustments.asset_id%type,
68 p_distribution_id in igi_iac_adjustments.distribution_id%type,
69 p_period_counter in igi_iac_adjustments.period_counter%type,
70 p_adj_offset_type IN igi_iac_adjustments.adjustment_offset_type%TYPE,
71 p_report_ccid IN igi_iac_adjustments.report_ccid%TYPE,
72 p_event_id IN number
73 )
74 IS
75 l_rowid ROWID;
76
77 l_mesg VARCHAR2(500);
78 l_path varchar2(150);
79 BEGIN
80
81 l_path := g_path||'insert_data_adj';
82
83 IF p_amount <> 0 THEN
84 -- Call to TBH for insert into IGI_IAC_ADJUSTMENTS
85 IGI_IAC_ADJUSTMENTS_PKG.insert_row(
86 x_rowid =>l_rowid,
87 x_adjustment_id =>p_adjustment_id,
88 x_book_type_code =>p_book_type_code,
89 x_code_combination_id =>p_code_combination_id,
90 x_set_of_books_id =>p_set_of_books_id,
91 x_dr_cr_flag =>p_dr_cr_flag,
92 x_amount =>p_amount,
93 x_adjustment_type =>p_adjustment_type,
94 x_adjustment_offset_type =>p_adj_offset_type,
95 x_transfer_to_gl_flag =>'Y',
96 x_units_assigned =>p_units_assigned,
97 x_asset_id =>p_asset_id,
98 x_distribution_id =>p_distribution_id,
99 x_period_counter =>p_period_counter,
100 x_report_ccid =>p_report_ccid,
101 x_mode =>'R',
102 x_event_id => p_event_id
103 );
104
105 END IF;
106
107 EXCEPTION
108 WHEN OTHERS THEN
109 l_mesg:=SQLERRM;
110 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
111 FA_SRVR_MSG.Add_Message(
112 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
113 Name => 'IGI_IAC_EXCEPTION',
114 TOKEN1 => 'PACKAGE',
115 VALUE1 => 'Transfers',
116 TOKEN2 => 'ERROR_MESSAGE',
117 VALUE2 => l_mesg);
118 ROLLBACK;
119 END insert_data_adj;
120
121 -- ===============================================================================
122 -- Procedure to insert data into IGI_IAC_DET_BALANCES
123 -- ===============================================================================
124 PROCEDURE insert_data_det(p_adjustment_id in IGI_IAC_DET_BALANCES.adjustment_id%type,
125 p_asset_id in IGI_IAC_DET_BALANCES.asset_id%type,
126 p_distribution_id in IGI_IAC_DET_BALANCES.distribution_id%type,
127 p_period_counter in IGI_IAC_DET_BALANCES.period_counter%type,
128 p_book_type_code in IGI_IAC_DET_BALANCES.book_type_code%type,
129 p_adjusted_cost in IGI_IAC_DET_BALANCES.adjustment_cost%type,
130 p_net_book_value in IGI_IAC_DET_BALANCES.net_book_value%type,
131 p_reval_reserve in IGI_IAC_DET_BALANCES.reval_reserve_cost%type,
132 p_reval_reserve_gen_fund in IGI_IAC_DET_BALANCES.reval_reserve_gen_fund%type,
133 p_reval_reserve_backlog in IGI_IAC_DET_BALANCES.reval_reserve_backlog%type,
134 p_op_acct in IGI_IAC_DET_BALANCES.operating_acct_cost%type,
135 p_deprn_reserve in IGI_IAC_DET_BALANCES.deprn_reserve%type,
136 p_deprn_reserve_backlog in IGI_IAC_DET_BALANCES.deprn_reserve_backlog%type,
137 p_deprn_ytd in IGI_IAC_DET_BALANCES.deprn_ytd%type,
138 p_deprn_period in IGI_IAC_DET_BALANCES.deprn_period%type,
139 p_gen_fund_acc in IGI_IAC_DET_BALANCES.general_fund_acc%type,
140 p_gen_fund_per in IGI_IAC_DET_BALANCES.general_fund_acc%type,
141 p_current_reval_factor in IGI_IAC_DET_BALANCES.current_reval_factor%type,
142 p_cumulative_reval_factor in IGI_IAC_DET_BALANCES.cumulative_reval_factor%type,
143 p_reval_flag in IGI_IAC_DET_BALANCES.active_flag%type,
144 p_op_acct_ytd in IGI_IAC_DET_BALANCES.operating_acct_ytd%type,
145 p_operating_acct_backlog in IGI_IAC_DET_BALANCES.operating_acct_backlog%type,
146 p_last_reval_date in IGI_IAC_DET_BALANCES.last_reval_date%type
147 )
148 IS
149
150 l_rowid VARCHAR2(25);
151 l_mesg VARCHAR2(500);
152 l_path varchar2(150);
153 BEGIN
154
155 l_path := g_path||'insert_data_det';
156
157 -- Call to TBH for insert into IGI_IAC_DET_BALANCES
158 IGI_IAC_DET_BALANCES_PKG.insert_row(
159 x_rowid =>l_rowid,
160 x_adjustment_id =>p_adjustment_id,
161 x_asset_id =>p_asset_id,
162 x_distribution_id =>p_distribution_id,
163 x_book_type_code =>p_book_type_code,
164 x_period_counter =>p_period_counter,
165 x_adjustment_cost =>p_adjusted_cost,
166 x_net_book_value =>p_net_book_value,
167 x_reval_reserve_cost =>(p_reval_reserve+p_reval_reserve_backlog+p_reval_reserve_gen_fund),
168 x_reval_reserve_backlog =>p_reval_reserve_backlog,
169 x_reval_reserve_gen_fund =>p_reval_reserve_gen_fund,
170 x_reval_reserve_net =>p_reval_reserve,
171 x_operating_acct_cost =>(p_op_acct+p_operating_acct_backlog),
172 x_operating_acct_backlog =>p_operating_acct_backlog,
173 x_operating_acct_net =>p_op_acct,
174 x_operating_acct_ytd =>p_op_acct_ytd,
175 x_deprn_period =>p_deprn_period,
176 x_deprn_ytd =>p_deprn_ytd,
177 x_deprn_reserve =>p_deprn_reserve,
178 x_deprn_reserve_backlog =>p_deprn_reserve_backlog,
179 x_general_fund_per =>p_gen_fund_per,
180 x_general_fund_acc =>p_gen_fund_acc,
181 x_last_reval_date =>p_last_reval_date,
182 x_current_reval_factor =>p_current_reval_factor,
183 x_cumulative_reval_factor =>p_cumulative_reval_factor,
184 x_active_flag =>p_reval_flag,
185 x_mode =>'R'
186 );
187
188 EXCEPTION
189 WHEN OTHERS THEN
190 l_mesg:=SQLERRM;
191 FA_SRVR_MSG.Add_Message(
192 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
193 Name => 'IGI_IAC_EXCEPTION',
194 TOKEN1 => 'PACKAGE',
195 VALUE1 => 'Transfers',
196 TOKEN2 => 'ERROR_MESSAGE',
197 VALUE2 => l_mesg);
198 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
199 rollback;
200 END insert_data_det;
201
202 -- ===============================================================================+
203 -- Procedure to prorate the amounts based on the units assigned |
204 -- ================================================================================+
205 PROCEDURE Prorate_amount_for_dist(
206 p_dist_id IN fa_distribution_history.distribution_id%TYPE,
207 p_units_dist IN fa_distribution_history.units_assigned%TYPE,
208 p_units_total IN NUMBER,
209 p_ab_amounts IN asset_rec_type,
210 p_reval_reserve out NOCOPY number,
211 p_general_fund out NOCOPY number,
212 P_backlog_deprn out NOCOPY number,
213 P_deprn_reserve out NOCOPY number,
214 P_adjusted_cost out NOCOPY number,
215 P_net_book_value out NOCOPY number,
216 P_deprn_per out NOCOPY number,
217 P_op_acct out NOCOPY number,
218 p_ytd_deprn out NOCOPY number,
219 p_op_acct_ytd out NOCOPY number,
220 p_rr_blog out NOCOPY number,
221 p_op_blog out NOCOPY number,
222 p_gf_per out NOCOPY number
223 )
224 IS
225
226 prorate_factor number;
227 l_mesg VARCHAR2(500);
228
229 l_path varchar2(150);
230 BEGIN
231
232 l_path := g_path||'Prorate_amount_for_dist';
233
234 -- Prorate the various amounts between for the given distribution
235 prorate_factor := p_units_dist/p_units_total;
236
237 p_reval_reserve := p_ab_amounts.reval_reserve* Prorate_factor ;
238 P_general_fund := P_ab_amounts.general_fund * Prorate_factor ;
239 P_backlog_deprn := p_ab_amounts.backlog_deprn_reserve* Prorate_factor ;
240 P_deprn_reserve := p_ab_amounts.deprn_reserve* Prorate_factor ;
241 P_adjusted_cost := p_ab_amounts.adjusted_cost* Prorate_factor ;
242 P_net_book_value := p_ab_amounts.net_book_value* Prorate_factor ;
243 P_deprn_per := p_ab_amounts.deprn_amount* Prorate_factor;
244 P_op_acct := p_ab_amounts.operating_acct*Prorate_factor;
245 P_ytd_deprn := p_ab_amounts.ytd_deprn*Prorate_factor;
246 P_op_acct_ytd := 0; --p_ab_op_acct_ytd*Prorate_factor;
247
248 p_rr_blog := p_ab_amounts.reval_reserve_backlog*Prorate_factor;
249 p_op_blog := p_ab_amounts.operating_acct_backlog*Prorate_factor;
250 p_gf_per := p_ab_amounts.general_fund_per*Prorate_factor;
251
252 EXCEPTION
253 WHEN OTHERS THEN
254 l_mesg:=SQLERRM;
255 P_reval_reserve := 0;
256 P_general_fund := 0;
257 P_backlog_deprn := 0;
258 P_deprn_reserve := 0;
259 P_adjusted_cost := 0;
260 P_net_book_value := 0;
261 P_deprn_per := 0;
262 P_op_acct := 0;
263 p_ytd_deprn := 0;
264 p_op_acct_ytd := 0;
265
266 p_rr_blog := 0;
267 p_op_blog := 0;
268 p_gf_per := 0;
269
270 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
271 FA_SRVR_MSG.Add_Message(
272 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
273 Name => 'IGI_IAC_EXCEPTION',
274 TOKEN1 => 'PACKAGE',
275 VALUE1 => 'Transfers',
276 TOKEN2 => 'ERROR_MESSAGE',
277 VALUE2 => l_mesg);
278
279 END prorate_amount_for_dist;
280
281 -- ===============================================================================
282 -- Procedure to prorate the amounts based on the units assigned
283 -- ===============================================================================
284 PROCEDURE Prorate_amount_for_fa_dist(P_dist_id in FA_DISTRIBUTION_HISTORY.DISTRIBUTION_ID%type,
285 P_units_dist in number,
286 P_units_total in number,
287 P_ab_amounts IN iac_fa_deprn_rec_type,
288 P_deprn_period OUT NOCOPY number,
289 P_deprn_ytd OUT NOCOPY number,
290 P_deprn_reserve OUT NOCOPY number
291 )
292 IS
293
294 l_prorate_factor number;
295 l_mesg VARCHAR2(500);
296 P_deprn_period_old number;
297 P_deprn_ytd_old number;
298 P_deprn_reserve_old number;
299
300 l_path varchar2(150);
301 BEGIN
302
303 l_path := g_path||'Prorate_amount_for_fa_dist';
304
305 P_deprn_period_old := P_ab_amounts.deprn_period;
306 P_deprn_ytd_old := P_ab_amounts.deprn_ytd;
307 P_deprn_reserve_old := P_ab_amounts.deprn_reserve;
308
309 -- Prorate the various amounts between for the given distribution
310
311 l_Prorate_factor := p_units_dist/p_units_total;
312
313 P_deprn_period := P_ab_amounts.deprn_period * l_Prorate_factor ;
314 P_deprn_ytd := P_ab_amounts.deprn_ytd * l_Prorate_factor ;
315 P_deprn_reserve := P_ab_amounts.deprn_reserve * l_Prorate_factor ;
316
317 EXCEPTION
318 WHEN OTHERS THEN
319 l_mesg:=SQLERRM;
320
321 -- reverting back to old values.
322 P_deprn_period := P_deprn_period_old;
323 P_deprn_ytd := P_deprn_ytd_old;
324 P_deprn_reserve := P_deprn_reserve_old;
325
326 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
327
328 FA_SRVR_MSG.Add_Message(
329 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
330 Name => 'IGI_IAC_EXCEPTION',
331 TOKEN1 => 'PACKAGE',
332 VALUE1 => 'Transfers',
333 TOKEN2 => 'ERROR_MESSAGE',
334 VALUE2 => l_mesg);
335
336 END prorate_amount_for_fa_dist;
337
338 -- ===============================================================================
339 -- PROCEDURE Prorate_Catchup_Amounts: Procedure for prorating catchup amounts like
340 -- deprn_expense and op_expense
341 -- ===============================================================================
342 PROCEDURE Prorate_Catchup_Amounts(p_dist_id IN fa_distribution_history.distribution_id%TYPE,
343 p_units_dist IN fa_distribution_history.units_assigned%TYPE,
344 p_transfer_units IN NUMBER,
345 p_ab_dep_exp IN NUMBER,
346 p_ab_op_exp IN NUMBER,
347 p_fa_ab_dep_exp IN NUMBER,
348 p_dist_dep_exp OUT NOCOPY NUMBER,
349 p_dist_op_exp OUT NOCOPY NUMBER,
350 p_fa_dist_dep_exp OUT NOCOPY NUMBER
351 )
352 IS
353
354 prorate_factor number;
355 l_mesg VARCHAR2(500);
356
357 l_path varchar2(150);
358 BEGIN
359
360 l_path := g_path||'Prorate_amount_for_dist';
361
362 -- Prorate the various amounts between for the given distribution
363 prorate_factor := p_units_dist/p_transfer_units;
364
365 p_dist_dep_exp := 0;
366 p_dist_op_exp := 0;
367 p_fa_dist_dep_exp := 0;
368
369 IF g_prior_period THEN
370 p_dist_dep_exp := p_ab_dep_exp*Prorate_factor;
371 p_dist_op_exp := p_ab_op_exp*Prorate_factor;
372 p_fa_dist_dep_exp := p_fa_ab_dep_exp*Prorate_factor;
373 END IF;
374
375 EXCEPTION
376 WHEN OTHERS THEN
377 l_mesg:=SQLERRM;
378 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
379 FA_SRVR_MSG.Add_Message(
380 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
381 Name => 'IGI_IAC_EXCEPTION',
382 TOKEN1 => 'PACKAGE',
383 VALUE1 => 'Transfers',
384 TOKEN2 => 'ERROR_MESSAGE',
385 VALUE2 => l_mesg);
386
387 END Prorate_Catchup_Amounts;
388
389 -- ===============================================================================
390 -- Procedure to find the distribution ccid's for various types of accounts
391 -- ===============================================================================
392 PROCEDURE find_ccid(p_book_type_code in FA_BOOKS.book_type_code%TYPE,
393 p_asset_id in FA_BOOKS.asset_id%TYPE,
394 p_transaction_header_id in FA_DISTRIBUTION_HISTORY.transaction_header_id_in%TYPE,
395 p_dist_id in number,
396 p_reval_ccid out NOCOPY number,
397 p_Gen_fund_ccid out NOCOPY number,
398 p_Backlog_ccid out NOCOPY number,
399 p_deprn_ccid out NOCOPY number,
400 p_cost_ccid out NOCOPY number,
401 p_op_expense_ccid out NOCOPY NUMBER,
402 p_expense_ccid out NOCOPY NUMBER
403 )
404 IS
405 l_return_value BOOLEAN;
406 l_mesg VARCHAR2(500);
407 l_path varchar2(150);
408 BEGIN
409
410 l_path := g_path||'find_ccid';
411
412 -- For reval reserve get the ccid into p_reval_ccid
413 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_book_type_code,
414 p_asset_id,
415 p_dist_id,
416 'REVAL_RESERVE_ACCT',
417 p_transaction_header_id,
418 'TRANSFER',
419 p_reval_ccid);
420
421
422 -- For general fund get the ccid into p_gen_fund_ccid
423 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_book_type_code,
424 p_asset_id,
425 p_dist_id,
426 'GENERAL_FUND_ACCT',
427 p_transaction_header_id,
428 'TRANSFER',
429 p_Gen_fund_ccid);
430
431 -- For backlog deprn reserve get the ccid into p_backlog_ccid
432 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_book_type_code,
433 p_asset_id,
434 p_dist_id,
435 'BACKLOG_DEPRN_RSV_ACCT',
436 p_transaction_header_id,
437 'TRANSFER',
438 p_backlog_ccid);
439
440 -- For deprn reserve get the ccid into p_deprn_ccid
441 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_book_type_code,
442 p_asset_id,
443 p_dist_id,
444 'DEPRN_RESERVE_ACCT',
445 p_transaction_header_id,
446 'TRANSFER',
447 p_deprn_ccid);
448
449 -- For asset cost get the ccid into p_cost_ccid
450 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_book_type_code,
451 p_asset_id,
452 p_dist_id,
453 'ASSET_COST_ACCT',
454 p_transaction_header_id,
455 'TRANSFER',
456 p_cost_ccid);
457
458 -- For operating expense get the ccid into p_op_expense_ccid
459 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_book_type_code,
460 p_asset_id,
461 p_dist_id,
462 'OPERATING_EXPENSE_ACCT',
463 p_transaction_header_id,
464 'TRANSFER',
465 p_op_expense_ccid);
466
467 -- get the account info for the DEPRN_EXPENSE_ACCT
468 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_book_type_code,
469 p_asset_id,
470 p_dist_id,
471 'DEPRN_EXPENSE_ACCT',
472 p_transaction_header_id,
473 'TRANSFER',
474 p_expense_ccid
475 );
476 EXCEPTION
477 WHEN OTHERS THEN
478 l_mesg:=SQLERRM;
479
480 p_reval_ccid := NULL;
481 P_gen_fund_ccid := NULL;
482 P_backlog_ccid := NULL;
483 p_deprn_ccid := NULL;
484 p_cost_ccid := NULL;
485 p_op_expense_ccid := NULL;
486
487 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
488 FA_SRVR_MSG.Add_Message(
489 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
490 Name => 'IGI_IAC_EXCEPTION',
491 TOKEN1 => 'PACKAGE',
492 VALUE1 => 'Transfers',
493 TOKEN2 => 'ERROR_MESSAGE',
494 VALUE2 => l_mesg);
495
496 END find_ccid;
497
498 -- ===============================================================================
499 -- FUNCTION Calc_Asset_Amounts: Calculate the asset level amounts for the asset
500 -- ===============================================================================
501 FUNCTION Calc_Asset_Amounts(p_adjustment_id IN igi_iac_transaction_headers.adjustment_id%TYPE,
502 p_asset_id IN igi_iac_transaction_headers.asset_id%TYPE,
503 p_book_type_code IN igi_iac_transaction_headers.book_type_code%TYPE,
504 p_curr_prd_cntr IN igi_iac_transaction_headers.period_counter%TYPE,
505 p_tfr_prd_cntr IN igi_iac_transaction_headers.period_counter%TYPE,
506 p_hist_cost IN fa_books.cost%TYPE,
507 p_salvage_value IN fa_books.salvage_value%TYPE,
508 p_trx_header_id IN igi_iac_transaction_headers.transaction_header_id%TYPE,
509 p_asset_units IN fa_distribution_history.units_assigned%TYPE,
510 p_transfer_units IN fa_distribution_history.units_assigned%TYPE,
511 l_asset_rec OUT NOCOPY asset_rec_type,
512 l_iac_fa_dep_rec OUT NOCOPY iac_fa_deprn_rec_type
513 )
514 RETURN BOOLEAN
515 IS
516 -- Get the asset amounts that need to be transferred to the new dist
517 -- created by transfer (keep)
518 CURSOR c_amounts
519 IS
520 SELECT iab.asset_id,
521 iab.book_type_code,
522 iab.period_counter,
523 iab.net_book_value,
524 iab.adjusted_cost,
525 iab.operating_acct,
526 iab.reval_reserve,
527 iab.deprn_amount,
528 iab.deprn_reserve,
529 iab.backlog_deprn_reserve,
530 iab.general_fund,
531 iab.last_reval_date,
532 iab.current_reval_factor,
533 iab.cumulative_reval_factor
534 FROM igi_iac_asset_balances iab,
535 (SELECT a.book_type_code,
536 a.asset_id,
537 max(a.period_counter) period_counter
538 FROM igi_iac_asset_balances a
539 WHERE a.asset_id= p_asset_id
540 AND a.book_type_code= p_book_type_code
541 GROUP BY a.book_type_code, a.asset_id) mpc
542 WHERE iab.asset_id= p_asset_id
543 AND iab.book_type_code= p_book_type_code
544 AND iab.asset_id = mpc.asset_id
545 AND iab.book_type_code = mpc.book_type_code
546 AND iab.period_counter = mpc.period_counter;
547
548 -- Calculate the reval reserve backlog,op acct backlog, gen fund per and YTD deprn
549 -- for the adjustment
550 -- (for asset level amounts)
551 CURSOR c_backlog_data(cp_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE)
552 IS
553 SELECT nvl(sum(iadb.reval_reserve_backlog), 0) reval_reserve_backlog,
554 nvl(sum(iadb.operating_acct_backlog), 0) operating_acct_backlog,
555 nvl(sum(iadb.general_fund_per), 0) general_fund_per,
556 nvl(sum(iadb.deprn_ytd), 0) ytd_deprn
557 FROM igi_iac_det_balances iadb
558 WHERE iadb.adjustment_id = cp_adjustment_id
559 AND iadb.active_flag IS NULL;
560
561 -- Calculate the iac fa depreciation data for an adjustment
562 CURSOR c_iac_fa_deprn(cp_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE)
563 IS
564 SELECT nvl(sum(iadb.deprn_period), 0) deprn_period,
565 nvl(sum(iadb.deprn_reserve), 0) deprn_reserve,
566 nvl(sum(iadb.deprn_ytd), 0) deprn_ytd
567 FROM igi_iac_fa_deprn iadb
568 WHERE iadb.adjustment_id = cp_adjustment_id
569 AND iadb.active_flag IS NULL;
570
571 --To fetch depreciation expense from fa_adjustments for the transfer transaction (keep)
572 CURSOR c_get_deprn_expense(cp_period_counter fa_deprn_detail.period_counter%TYPE)
573 IS
574 SELECT sum(deprn_amount-deprn_adjustment_amount ) deprn_amount
575 FROM fa_deprn_detail
576 WHERE book_type_code = p_book_type_code
577 AND period_counter = cp_period_counter
578 AND asset_id = p_asset_id;
579
580 -- cursor to get the operating expense amount
581 CURSOR c_op_expense_amt(cp_asset_id igi_iac_adjustments.asset_id%TYPE,
582 cp_book_type_code igi_iac_adjustments.book_type_code%TYPE,
583 cp_tfr_prd_counter igi_iac_adjustments.period_counter%TYPE,
584 cp_cur_prd_counter igi_iac_adjustments.period_counter%TYPE
585 )
586 IS
587 SELECT nvl(sum(decode(aj.dr_cr_flag, 'CR', 1, -1) *AJ.Amount), 0) op_expense_amt
588 FROM igi_iac_adjustments aj,
589 igi_iac_transaction_headers ith
590 WHERE aj.asset_id = cp_asset_id
591 AND aj.book_type_code = cp_book_type_code
592 AND aj.period_counter BETWEEN cp_tfr_prd_counter AND cp_cur_prd_counter
593 AND aj.adjustment_type = 'OP EXPENSE'
594 AND aj.adjustment_id = ith.adjustment_id
595 AND ith.transaction_type_code = 'REVALUATION'
596 AND ith.adjustment_status NOT IN ('PREVIEW', 'OBSOLETE');
597
598 -- local variables
599 l_ab_amounts c_amounts%ROWTYPE;
600 l_ab_backlog_data c_backlog_data%ROWTYPE;
601 l_iac_fa_deprn c_iac_fa_deprn%ROWTYPE;
602
603 l_historic_deprn_expense fa_deprn_summary.deprn_amount%TYPE;
604 l_expense_diff igi_iac_det_balances.deprn_period%TYPE;
605 l_ab_op_exp igi_iac_asset_balances.operating_acct%TYPE;
606
607 l_mesg VARCHAR2(500);
608 l_path varchar2(150);
609
610 -- exceptions
611 e_salvage_val_corr_err EXCEPTION;
612 BEGIN
613
614 l_path := g_path||'Calc_Asset_Amounts';
615
616 -- Get the IAC position for the asset for the current period counter
617 -- from igi_iac_asset_balances
618 OPEN c_amounts;
619 FETCH c_amounts INTO l_ab_amounts;
620 IF c_amounts%NOTFOUND THEN
621 RAISE NO_DATA_FOUND;
622 END IF;
623 CLOSE c_amounts;
624
625 -- fetch asset level backlog and general fund periodic amounts
626 -- for the latest adjustment_id
627 OPEN c_backlog_data(p_adjustment_id);
628 FETCH c_backlog_data INTO l_ab_backlog_data;
629 IF c_backlog_data%NOTFOUND THEN
630 RAISE NO_DATA_FOUND;
631 END IF;
632 CLOSE c_backlog_data;
633
634 -- set the asset info
635 l_asset_rec.asset_id := l_ab_amounts.asset_id;
636 l_asset_rec.book_type_code := l_ab_amounts.book_type_code;
637 l_asset_rec.period_counter := l_ab_amounts.period_counter;
638 l_asset_rec.net_book_value := l_ab_amounts.net_book_value;
639 l_asset_rec.adjusted_cost := l_ab_amounts.adjusted_cost;
640 l_asset_rec.operating_acct := l_ab_amounts.operating_acct;
641 l_asset_rec.reval_reserve := l_ab_amounts.reval_reserve;
642 l_asset_rec.deprn_amount := l_ab_amounts.deprn_amount;
643 l_asset_rec.deprn_reserve := l_ab_amounts.deprn_reserve;
644 l_asset_rec.backlog_deprn_reserve := l_ab_amounts.backlog_deprn_reserve;
645 l_asset_rec.general_fund := l_ab_amounts.general_fund;
646 l_asset_rec.last_reval_date := l_ab_amounts.last_reval_date;
647 l_asset_rec.current_reval_factor := l_ab_amounts.current_reval_factor;
648 l_asset_rec.cumulative_reval_factor := l_ab_amounts.cumulative_reval_factor;
649 l_asset_rec.reval_reserve_backlog := l_ab_backlog_data.reval_reserve_backlog;
650 l_asset_rec.operating_acct_backlog := l_ab_backlog_data.operating_acct_backlog;
651 l_asset_rec.general_fund_per := l_ab_backlog_data.general_fund_per;
652 l_asset_rec.ytd_deprn := l_ab_backlog_data.ytd_deprn;
653
654 -- calculate the asset level catchup amounts
655 l_asset_rec.dep_expense_catchup := 0;
656 l_asset_rec.op_expense_catchup := 0;
657 IF g_prior_period THEN
658 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Processing prior period data ');
659 -- l_historic_deprn_expense will be calculated from fa_adjustments
660 -- OPEN c_get_deprn_expense (p_trans_rec.transaction_header_id);
661 OPEN c_get_deprn_expense(p_curr_prd_cntr - 1);
662 FETCH c_get_deprn_expense INTO l_historic_deprn_expense;
663
664 IF c_get_deprn_expense%NOTFOUND THEN
665 l_historic_deprn_expense:=0;
666 CLOSE c_get_deprn_expense;
667 ELSE
668 -- do salvage value correction for l_historic_deprn_expense
669 IF NOT IGI_IAC_SALVAGE_PKG.Correction(
670 P_asset_id => p_asset_id,
671 P_book_type_code => p_book_type_code,
672 P_value => l_historic_deprn_expense,
673 P_cost => p_hist_cost,
674 P_salvage_value => p_salvage_value,
675 p_calling_program => 'IGI_IAC_TRANSFERS_PKG.Do_transfer'
676 )
677 THEN
678 RAISE e_salvage_val_corr_err;
679 END IF;
680 CLOSE c_get_deprn_expense;
681 END IF;
682 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Historic Expense Amount :'||l_historic_deprn_expense);
683 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Cumulative Reval Factor :'||l_ab_amounts.cumulative_reval_factor);
684
685 -- calculate the additional IAC expense
686 l_asset_rec.dep_expense_catchup :=l_historic_deprn_expense*(l_ab_amounts.cumulative_reval_factor - 1);
687 l_asset_rec.dep_expense_catchup := l_asset_rec.dep_expense_catchup*(p_curr_prd_cntr - p_tfr_prd_cntr);
688 l_asset_rec.dep_expense_catchup := l_asset_rec.dep_expense_catchup*(p_transfer_units/p_asset_units);
689
690 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Additional IAC Expense :'||l_expense_diff);
691 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'No of Catch up periods :'||(p_curr_prd_cntr-p_tfr_prd_cntr));
692 IF IGI_IAC_COMMON_UTILS.Iac_round(l_asset_rec.dep_expense_catchup,
693 p_book_type_code) THEN
694 NULL;
695 END IF;
696
697 -- if asset has been revalued between the transfer period and current period
698 -- the calculate the operating expense amount
699 OPEN c_op_expense_amt(cp_asset_id => p_asset_id,
700 cp_book_type_code => p_book_type_code,
701 cp_tfr_prd_counter => p_tfr_prd_cntr,
702 cp_cur_prd_counter => p_curr_prd_cntr
703 );
704 FETCH c_op_expense_amt INTO l_asset_rec.op_expense_catchup;
705 IF c_op_expense_amt%NOTFOUND THEN
706 RAISE NO_DATA_FOUND;
707 END IF;
708 CLOSE c_op_expense_amt;
709
710 l_asset_rec.op_expense_catchup := l_asset_rec.op_expense_catchup*(p_transfer_units/p_asset_units);
711 IF IGI_IAC_COMMON_UTILS.Iac_round(l_asset_rec.op_expense_catchup,
712 p_asset_hdr_rec.book_type_code) THEN
713 NULL;
714 END IF;
715 END IF;
716 -- end calculation of catchup amounts
717
718 -- calculate asset iac fa deprn amounts
719 OPEN c_iac_fa_deprn(p_adjustment_id);
720 FETCH c_iac_fa_deprn INTO l_iac_fa_deprn;
721 IF c_iac_fa_deprn%NOTFOUND THEN
722 RAISE NO_DATA_FOUND;
723 END IF;
724 CLOSE c_iac_fa_deprn;
725
726 l_iac_fa_dep_rec.asset_id := l_ab_amounts.asset_id;
727 l_iac_fa_dep_rec.book_type_code := l_ab_amounts.book_type_code;
728 l_iac_fa_dep_rec.period_counter := l_ab_amounts.period_counter;
729 l_iac_fa_dep_rec.deprn_period := l_iac_fa_deprn.deprn_period;
730 l_iac_fa_dep_rec.deprn_reserve := l_iac_fa_deprn.deprn_reserve;
731 l_iac_fa_dep_rec.deprn_ytd := l_iac_fa_deprn.deprn_ytd;
732
733 -- calculate the historic depreciation catchup amount
734 l_iac_fa_dep_rec.dep_expense_catchup := l_historic_deprn_expense*(p_curr_prd_cntr - p_tfr_prd_cntr);
735 l_iac_fa_dep_rec.dep_expense_catchup := l_iac_fa_dep_rec.dep_expense_catchup*(p_transfer_units/p_asset_units);
736 IF IGI_IAC_COMMON_UTILS.Iac_round(l_iac_fa_dep_rec.dep_expense_catchup,
737 p_book_type_code) THEN
738 NULL;
739 END IF;
740
741 RETURN TRUE;
742 EXCEPTION
743 WHEN e_salvage_val_corr_err THEN
744 FA_SRVR_MSG.Add_Message(
745 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
746 Name => 'IGI_IAC_SALVAGE_CORR_ERR',
747 TOKEN1 => 'PROCESS',
748 VALUE1 => 'IGI_IAC_TRANSFER_PKG.Do_Transfer');
749 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'Salvage Value correction error');
750 RETURN FALSE;
751
752 WHEN OTHERS THEN
753 l_mesg:=SQLERRM;
754 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
755 FA_SRVR_MSG.Add_Message(
756 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Calc_Asset_Amounts',
757 Name => 'IGI_IAC_EXCEPTION',
758 TOKEN1 => 'PACKAGE',
759 VALUE1 => 'Transfers',
760 TOKEN2 => 'ERROR_MESSAGE',
761 VALUE2 => l_mesg);
762 RETURN FALSE;
763 END Calc_Asset_Amounts;
764
765 -- ================================================================================
766 -- PROCEDURE Roll_Inactive_Forward: This procedure will roll all the inactive
767 -- distributions of the current adjustment_id to the new transfers adjustment_id
768 -- ================================================================================
769 PROCEDURE Roll_Inactive_Forward(p_adjustment_id IN igi_iac_det_balances.adjustment_id%TYPE,
770 p_new_adj_id IN igi_iac_det_balances.adjustment_id%TYPE,
771 p_book_type_code IN igi_iac_det_balances.book_type_code%TYPE,
772 p_asset_id IN igi_iac_det_balances.asset_id%TYPE,
773 p_curr_prd_cntr IN igi_iac_det_balances.period_counter%TYPE
774 )
775 IS
776 -- Select all inactive distributions created by the previous transaction (keep)
777 CURSOR c_inactive_dist(c_adjustment_id IGI_IAC_TRANSACTION_HEADERS.adjustment_id%TYPE)
778 IS
779 SELECT *
780 FROM igi_iac_det_balances
781 WHERE adjustment_id=c_adjustment_id
782 AND book_type_code = p_book_type_code
783 AND asset_id = p_asset_id
784 AND active_flag='N';
785
786 -- get fa inactive rows for a distribution (keep)
787 CURSOR c_fa_inactive_dist(cp_adjustment_id igi_iac_transaction_headers.adjustment_id%TYPE)
788 IS
789 SELECT *
790 FROM igi_iac_fa_deprn
791 WHERE adjustment_id = cp_adjustment_id
792 AND book_type_code = p_book_type_code
793 AND asset_id = p_asset_id
794 AND active_flag = 'N';
795
796 l_rowid ROWID;
797
798 BEGIN
799 -- Carry forward the inactive distributions from the previous period
800 FOR l_inactive_dist IN c_inactive_dist(p_adjustment_id)
801 LOOP
802 insert_data_det(p_new_adj_id,
803 l_inactive_dist.asset_id,
804 l_inactive_dist.distribution_id,
805 p_curr_prd_cntr,
806 l_inactive_dist.book_type_code,
807 l_inactive_dist.adjustment_cost,
808 l_inactive_dist.net_book_value,
809 l_inactive_dist.reval_reserve_net,
810 l_inactive_dist.general_fund_acc,
811 l_inactive_dist.reval_reserve_backlog,
812 l_inactive_dist.operating_acct_net,
813 l_inactive_dist.deprn_reserve,
814 l_inactive_dist.deprn_reserve_backlog,
815 l_inactive_dist.deprn_ytd,
816 l_inactive_dist.deprn_period,
817 l_inactive_dist.general_fund_acc,
818 l_inactive_dist.general_fund_per,
819 l_inactive_dist.current_reval_factor,
820 l_inactive_dist.cumulative_reval_factor,
821 l_inactive_dist.active_flag,
822 l_inactive_dist.operating_acct_ytd,
823 l_inactive_dist.operating_acct_backlog,
824 l_inactive_dist.last_reval_date
825 );
826 END LOOP;
827
828 FOR l_fa_inactive_dist IN c_fa_inactive_dist(p_adjustment_id)
829 LOOP
830 l_rowid := NULL;
831 igi_iac_fa_deprn_pkg.insert_row(
832 x_rowid => l_rowid,
833 x_book_type_code => l_fa_inactive_dist.book_type_code,
834 x_asset_id => l_fa_inactive_dist.asset_id,
835 x_distribution_id => l_fa_inactive_dist.distribution_id,
836 x_period_counter => p_curr_prd_cntr,
837 x_adjustment_id => p_new_adj_id,
838 x_deprn_period => l_fa_inactive_dist.deprn_period,
839 x_deprn_ytd => l_fa_inactive_dist.deprn_ytd ,
840 x_deprn_reserve => l_fa_inactive_dist.deprn_reserve,
841 x_active_flag => l_fa_inactive_dist.active_flag,
842 x_mode => 'R');
843 END LOOP;
844 EXCEPTION
845 WHEN OTHERS THEN
846 igi_iac_debug_pkg.debug_unexpected_msg(sqlerrm);
847 FA_SRVR_MSG.Add_Message(
848 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Roll_Inactive_Forward',
849 Name => 'IGI_IAC_EXCEPTION',
850 TOKEN1 => 'PACKAGE',
851 VALUE1 => 'Transfers',
852 TOKEN2 => 'ERROR_MESSAGE',
853 VALUE2 => sqlerrm);
854
855 END Roll_Inactive_Forward;
856
857 /************************************MAIN***********************************/
858 -- =================================================================================
859 -- Function Do_transfer transfers the data from the old distribution to the new
860 -- distribution(s).Along with the transfer of old distribution to the new ,it
861 -- maintains the other active distributions of the asset with the latest adjustment
862 -- id.
863 -- ==================================================================================
864
865 FUNCTION Do_Transfer(p_trans_rec FA_API_TYPES.trans_rec_type,
866 p_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type,
867 p_asset_cat_rec FA_API_TYPES.asset_cat_rec_type,
868 p_calling_function varchar2,
869 p_event_id number --R12 uptake
870 )
871 RETURN BOOLEAN
872 IS
873 -- local cursors
874 -- Select all distributions for an asset that are active and the distribution
875 -- impacted by the transfer (keep)
876 CURSOR c_all_dist
877 IS
878 SELECT distribution_id,
879 transaction_header_id_in,
880 units_assigned,
881 transaction_header_id_out,
882 transaction_units
883 FROM fa_distribution_history
884 WHERE asset_id=p_asset_hdr_rec.asset_id
885 AND book_type_code=p_asset_hdr_rec.book_type_code
886 AND (transaction_header_id_out IS NULL OR
887 transaction_header_id_out=p_trans_rec.transaction_header_id);
888
889 -- Cursor to find out the number of ACtive Distributions (keep)
890 CURSOR c_no_of_imp IS
891 SELECT count(*) counter
892 FROM fa_distribution_history
893 WHERE asset_id=p_asset_hdr_rec.asset_id
894 AND book_type_code=p_asset_hdr_rec.book_type_code
895 -- AND transaction_header_id_in=p_trans_rec.transaction_header_id;
896 AND transaction_header_id_out IS NULL;
897
898 -- Cursor to find out the number of ACtive Distributions created by the Transfer(keep)
899 CURSOR c_no_of_active IS
900 SELECT count(*) counter
901 FROM fa_distribution_history
902 WHERE asset_id=p_asset_hdr_rec.asset_id
903 AND book_type_code=p_asset_hdr_rec.book_type_code
904 AND transaction_header_id_in=p_trans_rec.transaction_header_id;
905
906 -- Cursor to find out the number of Inative Distributions created by the Transfer(keep)
907 CURSOR c_no_of_inactive IS
908 SELECT count(*) counter
909 FROM fa_distribution_history
910 WHERE asset_id=p_asset_hdr_rec.asset_id
911 AND book_type_code=p_asset_hdr_rec.book_type_code
912 AND transaction_header_id_out=p_trans_rec.transaction_header_id;
913
914 -- Find period counter of transfer period in case of prior period transfer
915 -- (keep)
916 CURSOR c_prior_period_counter(c_trx_date IN FA_DEPRN_PERIODS.period_open_date%TYPE)
917 IS
918 SELECT period_counter
919 FROM fa_deprn_periods
920 WHERE c_trx_date BETWEEN period_open_date AND period_close_date
921 AND book_type_code=p_asset_hdr_rec.book_type_code;
922
923 -- find the total number of units for the asset itself ( active) (keep)
924 CURSOR c_units
925 IS
926 SELECT units
927 FROM fa_asset_history
928 WHERE asset_id = p_asset_hdr_rec.asset_id
929 AND transaction_header_id_out IS NULL;
930
931 -- find the number of units involved in the transfer
932 CURSOR c_transfer_units
933 IS
934 SELECT sum(units_assigned)
935 FROM fa_distribution_history
936 WHERE asset_id=p_asset_hdr_rec.asset_id
937 AND book_type_code=p_asset_hdr_rec.book_type_code
938 AND transaction_header_id_in=p_trans_rec.transaction_header_id;
939
940 -- select the start period number for a given fiscal year (keep)
941 CURSOR c_start_period_counter(c_fiscal_year FA_DEPRN_PERIODS.fiscal_year%TYPE)
942 IS
943 SELECT (number_per_fiscal_year*c_fiscal_year)+1
944 FROM fa_calendar_types
945 WHERE calendar_type=(SELECT deprn_calendar
946 FROM fa_book_controls
947 WHERE book_type_code=p_asset_hdr_rec.book_type_code);
948
949 -- Find the asset number for the asset_id (for exception messages) (keep)
950 CURSOR c_asset_num
951 IS
952 SELECT asset_number
953 FROM fa_additions
954 WHERE asset_id=p_asset_hdr_rec.asset_id;
955
956 -- retrieve salvage value and cost of the asset from fa_books (keep)
957 CURSOR c_get_asset_book(p_asset_id fa_books.asset_id%TYPE,
958 p_book_type_code fa_books.book_type_code%TYPE)
959 IS
960 SELECT cost,
961 salvage_value,
962 period_counter_fully_reserved
963 FROM fa_books
964 WHERE asset_id = p_asset_id
965 AND book_type_code = p_book_type_code
966 AND date_ineffective IS NULL
967 AND transaction_header_id_out IS NULL;
968
969 -- check to see if intercompany accounting entries have been created by FA
970 -- for the transaction
971 CURSOR c_get_interco (p_book_type_code varchar2,
972 p_asset_id number,
973 p_distribution_id number,
974 p_transaction_header_id number)
975 IS
976 SELECT count(*) interco_count
977 FROM FA_ADJUSTMENTS adj,
978 FA_BOOK_CONTROLS bc
979 WHERE adj.book_type_code = p_book_type_code
980 AND adj.ASSET_ID = p_asset_id
981 AND adj.SOURCE_TYPE_CODE = 'TRANSFER'
982 AND adj.ADJUSTMENT_TYPE IN ('INTERCO AP','INTERCO AR')
983 AND adj.TRANSACTION_HEADER_ID=p_transaction_header_id
984 AND bc.book_type_code = p_book_type_code
985 AND nvl(bc.intercompany_posting_flag,'Y') = 'Y';
986
987 -- Cursor to select the fa amounts for a distribution (keep)
988 CURSOR c_fa_dist_data(cp_adjustment_id igi_iac_fa_deprn.adjustment_id%TYPE,
989 cp_dist_id igi_iac_fa_deprn.distribution_id%TYPE)
990 IS
991 SELECT *
992 FROM igi_iac_fa_deprn
993 WHERE book_type_code = p_asset_hdr_rec.book_type_code
994 AND adjustment_id = cp_adjustment_id
995 AND asset_id = p_asset_hdr_rec.asset_id
996 AND distribution_id = cp_dist_id;
997
998 -- retrieve a row from igi_iac_det_balances
999 CURSOR c_iac_dist(cp_dist_id igi_iac_det_balances.distribution_id%TYPE,
1000 cp_adj_id igi_iac_det_balances.adjustment_id%TYPE)
1001 IS
1002 SELECT *
1003 FROM igi_iac_det_balances
1004 WHERE adjustment_id = cp_adj_id
1005 AND distribution_id = cp_dist_id;
1006
1007 -- local variables
1008 l_rowid ROWID;
1009 i NUMBER ;
1010 act_cnt NUMBER ;
1011 inact_cnt NUMBER ;
1012 l_return_value BOOLEAN;
1013
1014 l_adj_id_out igi_iac_transaction_headers.adjustment_id_out%TYPE;
1015 l_new_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
1016 l_adj_id igi_iac_transaction_headers.adjustment_id%TYPE;
1017 l_current_period_counter fa_deprn_periods.period_counter%TYPE;
1018 l_start_period_counter fa_deprn_periods.period_counter%TYPE;
1019 l_prior_period_counter fa_deprn_periods.period_counter%TYPE;
1020 l_prd_rec igi_iac_types.prd_rec;
1021 l_prd_rec_prior igi_iac_types.prd_rec;
1022 l_all_dist c_all_dist%ROWTYPE;
1023 l_iac_dist c_iac_dist%ROWTYPE;
1024 l_old_dist fa_distribution_history.distribution_id%TYPE;
1025 l_impacted_dist fa_distribution_history.distribution_id%TYPE;
1026 l_impacted_units fa_distribution_history.units_assigned%TYPE;
1027 l_no_of_imp c_no_of_imp%ROWTYPE;
1028 l_no_of_active NUMBER;
1029 l_no_of_inactive NUMBER;
1030
1031 l_ab_amounts asset_rec_type;
1032 l_iac_fa_dep_amounts iac_fa_deprn_rec_type;
1033
1034 l_asset_num FA_ADDITIONS.asset_number%TYPE;
1035 l_units FA_DISTRIBUTION_HISTORY.units_assigned%TYPE;
1036 l_transfer_units FA_DISTRIBUTION_HISTORY.units_assigned%TYPE;
1037
1038 l_interco_count NUMBER;
1039 l_interco_amount NUMBER ;
1040 l_interco_ccid igi_iac_adjustments.code_combination_id%TYPE;
1041 l_interco_drcr_flag igi_iac_adjustments.dr_cr_flag%TYPE;
1042
1043 l_reval_reserve IGI_IAC_DET_BALANCES.reval_reserve_cost%type;
1044 l_general_fund IGI_IAC_DET_BALANCES.general_fund_acc%type;
1045 l_Backlog_deprn_reserve IGI_IAC_DET_BALANCES.deprn_reserve_backlog%type;
1046 l_deprn_reserve IGI_IAC_DET_BALANCES.deprn_reserve%type;
1047 l_adjusted_cost IGI_IAC_DET_BALANCES.adjustment_cost%type;
1048 l_net_book_value IGI_IAC_DET_BALANCES.net_book_value%type;
1049 l_deprn_per IGI_IAC_DET_BALANCES.deprn_period%type;
1050 l_ytd_deprn IGI_IAC_DET_BALANCES.deprn_ytd%type;
1051 l_op_acct IGI_IAC_DET_BALANCES.operating_acct_ytd%type;
1052 l_op_acct_ytd IGI_IAC_DET_BALANCES.operating_acct_ytd%type;
1053 l_general_fund_per IGI_IAC_DET_BALANCES.general_fund_per%type;
1054 l_reval_reserve_backlog IGI_IAC_DET_BALANCES.reval_reserve_backlog%type;
1055 l_operating_acct_backlog IGI_IAC_DET_BALANCES.operating_acct_backlog%type;
1056
1057 l_transaction_header_id IGI_IAC_TRANSACTION_HEADERS.transaction_header_id%type;
1058 l_transaction_type_code IGI_IAC_TRANSACTION_HEADERS.transaction_type_code%type;
1059 l_mass_reference_id IGI_IAC_TRANSACTION_HEADERS.mass_reference_id%type;
1060 l_adjustment_id IGI_IAC_TRANSACTION_HEADERS.adjustment_id%type;
1061 l_prev_adjustment_id IGI_IAC_TRANSACTION_HEADERS.adjustment_id%type;
1062 l_adjustment_status IGI_IAC_TRANSACTION_HEADERS.adjustment_status%type;
1063
1064 l_reval_reserve_sum igi_iac_det_balances.reval_reserve_cost%TYPE ;
1065 l_general_fund_sum igi_iac_det_balances.general_fund_acc%TYPE ;
1066 l_Backlog_deprn_reserve_sum igi_iac_det_balances.deprn_reserve_backlog%TYPE ;
1067 l_deprn_reserve_sum igi_iac_det_balances.deprn_reserve%TYPE ;
1068 l_adjusted_cost_sum igi_iac_det_balances.adjustment_cost%TYPE ;
1069 l_net_book_value_sum igi_iac_det_balances.net_book_value%TYPE ;
1070 l_deprn_per_sum igi_iac_det_balances.deprn_period%TYPE ;
1071 l_ytd_deprn_sum igi_iac_det_balances.deprn_ytd%TYPE ;
1072 l_op_acct_sum igi_iac_det_balances.operating_acct_ytd%TYPE ;
1073 l_op_acct_ytd_sum igi_iac_det_balances.operating_acct_ytd%TYPE ;
1074 l_general_fund_per_sum igi_iac_det_balances.general_fund_per%TYPE ;
1075 l_reval_reserve_backlog_sum igi_iac_det_balances.reval_reserve_backlog%TYPE ;
1076 l_operating_acct_backlog_sum igi_iac_det_balances.operating_acct_backlog%TYPE;
1077
1078 l_reval_ccid igi_iac_adjustments.code_combination_id%TYPE;
1079 l_gen_fund_ccid igi_iac_adjustments.code_combination_id%TYPE;
1080 l_backlog_ccid igi_iac_adjustments.code_combination_id%TYPE;
1081 l_deprn_ccid igi_iac_adjustments.code_combination_id%TYPE;
1082 l_cost_ccid igi_iac_adjustments.code_combination_id%TYPE;
1083 l_op_expense_ccid igi_iac_adjustments.code_combination_id%TYPE;
1084 l_expense_ccid igi_iac_adjustments.code_combination_id%TYPE;
1085
1086 l_sob_id igi_iac_adjustments.set_of_books_id%TYPE;
1087 l_coa_id NUMBER;
1088 l_currency VARCHAR2(15);
1089 l_precision NUMBER;
1090
1091 l_prorate_factor NUMBER;
1092 l_deprn_expense NUMBER;
1093 l_deprn_expense_old_sum NUMBER;
1094 l_deprn_expense_imp_sum NUMBER;
1095 l_dist_op_exp igi_iac_adjustments.amount%TYPE;
1096 l_dist_op_exp_old_sum igi_iac_adjustments.amount%TYPE;
1097 l_dist_op_exp_imp_sum igi_iac_adjustments.amount%TYPE;
1098 l_fa_deprn_expense NUMBER;
1099 l_fa_deprn_expense_old_sum NUMBER;
1100 l_fa_deprn_expense_imp_sum NUMBER;
1101
1102 l_fa_dist_data igi_iac_fa_deprn%ROWTYPE;
1103 l_fa_deprn_period igi_iac_fa_deprn.deprn_period%TYPE;
1104 l_fa_deprn_ytd igi_iac_fa_deprn.deprn_ytd%TYPE;
1105 l_fa_deprn_reserve igi_iac_fa_deprn.deprn_reserve%TYPE;
1106 l_fa_deprn_period_sum igi_iac_fa_deprn.deprn_period%TYPE;
1107 l_fa_deprn_ytd_sum igi_iac_fa_deprn.deprn_ytd%TYPE;
1108 l_fa_deprn_reserve_sum igi_iac_fa_deprn.deprn_reserve%TYPE;
1109
1110 l_get_asset_book c_get_asset_book%ROWTYPE;
1111 l_iac_deprn_period_amount NUMBER;
1112 l_fa_deprn_period_amount NUMBER;
1113 l_deprn_amount igi_iac_asset_balances.deprn_amount%TYPE;
1114
1115 l_adj_offset_type igi_iac_adjustments.adjustment_offset_type%TYPE;
1116 l_report_ccid igi_iac_adjustments.report_ccid%TYPE;
1117
1118 l_path VARCHAR2(150);
1119 l_mesg VARCHAR2(500);
1120
1121 -- exceptions
1122 e_iac_not_enabled EXCEPTION;
1123 e_not_iac_book EXCEPTION;
1124 e_asset_not_revalued EXCEPTION;
1125 e_no_gl_info EXCEPTION;
1126
1127 BEGIN
1128
1129 i := 0;
1130 act_cnt := 0;
1131 inact_cnt := 0;
1132 l_interco_amount := 0;
1133 l_reval_reserve_sum := 0;
1134 l_general_fund_sum := 0;
1135 l_Backlog_deprn_reserve_sum := 0;
1136 l_deprn_reserve_sum := 0;
1137 l_adjusted_cost_sum := 0;
1138 l_net_book_value_sum := 0;
1139 l_deprn_per_sum := 0;
1140 l_ytd_deprn_sum := 0;
1141 l_op_acct_sum := 0;
1142 l_op_acct_ytd_sum := 0;
1143 l_general_fund_per_sum := 0;
1144 l_reval_reserve_backlog_sum := 0;
1145 l_operating_acct_backlog_sum := 0;
1146 l_deprn_expense_old_sum := 0;
1147 l_deprn_expense_imp_sum := 0;
1148 l_dist_op_exp_old_sum := 0;
1149 l_dist_op_exp_imp_sum := 0;
1150 l_fa_deprn_expense_old_sum := 0;
1151 l_fa_deprn_expense_imp_sum := 0;
1152 l_fa_deprn_period_sum := 0;
1153 l_fa_deprn_ytd_sum := 0;
1154 l_fa_deprn_reserve_sum := 0;
1155 l_path := g_path||'Do_Transfer';
1156
1157 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************************');
1158 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Processing Transfers on book '||p_asset_hdr_rec.book_type_code);
1159 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Asset Id :'||p_asset_hdr_rec.asset_id);
1160 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Transaction Header Id :'||p_trans_rec.transaction_header_id);
1161 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Mass Reference Id :'||p_trans_rec.mass_reference_id);
1162 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Transaction type code :'||p_trans_rec.transaction_type_code);
1163 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Transaction Date :'||p_trans_rec.transaction_date_entered);
1164 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Category Id :'||p_asset_cat_rec.category_id);
1165 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Transaction date entered :'||p_trans_rec.transaction_date_entered);
1166 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Mass Reference Id entered :'||p_trans_rec.mass_reference_id);
1167 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Mass Reference Id entered :'||p_asset_hdr_rec.set_of_books_id);
1168 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'******************************************************************************');
1169
1170 -- Check If IAC is enabled
1171 IF NOT igi_gen.is_req_installed('IAC') THEN
1172 RAISE e_iac_not_enabled;
1173 END IF;
1174
1175 -- Check If Book is an IAC book
1176 IF NOT igi_iac_common_utils.is_iac_book(p_asset_hdr_rec.book_type_code) THEN
1177 RAISE e_not_iac_book;
1178 END IF;
1179
1180 -- check if the asset has been revalued atleast once
1181 IF NOT igi_iac_common_utils.is_asset_proc(p_asset_hdr_rec.book_type_code,
1182 p_asset_hdr_rec.asset_id)
1183 THEN
1184 RAISE e_asset_not_revalued;
1185 END IF;
1186
1187 -- get the GL set of books id if p_asset_hdr_rec.set_of_books_id is null
1188 IF p_asset_hdr_rec.set_of_books_id is NULL THEN
1189 IF NOT igi_iac_common_utils.get_book_GL_info(p_asset_hdr_rec.book_type_code,
1190 l_sob_id,
1191 l_coa_id,
1192 l_currency,
1193 l_precision)
1194 THEN
1195 RAISE e_no_gl_info;
1196 END IF;
1197 END IF;
1198
1199 -- populate igi_iac_fa_deprn table with asset detials if no rows exist for that
1200 -- asset
1201 IF NOT igi_iac_common_utils.populate_iac_fa_deprn_data(p_asset_hdr_rec.book_type_code,
1202 'TRANSFER') THEN
1203 igi_iac_debug_pkg.debug_other_string(g_error_level,l_path,'*** Error in Synchronizing Depreciation Data ***');
1204 RETURN FALSE;
1205 END IF;
1206
1207 --Fetch the latest transaction = prev trans if latest is revaluation-preview or obsolete
1208 IF igi_iac_common_utils.get_latest_transaction (p_asset_hdr_rec.book_type_code,
1209 p_asset_hdr_rec.asset_id,
1210 l_transaction_type_code,
1211 l_transaction_header_id,
1212 l_mass_reference_id,
1213 l_adjustment_id,
1214 l_prev_adjustment_id,
1215 l_adjustment_status
1216 )
1217 THEN
1218 NULL;
1219 END IF;
1220 -- set the adjustment_id_out
1221 l_adj_id_out := l_adjustment_id;
1222
1223 -- check if latest adjustment is a REVALUATION in PREVIEW or OBSOLETE status
1224 -- and set
1225 /* not reqd as l_prev_adjustment_id is always the last active adjustment for the asset
1226 IF (l_transaction_type_code = 'REVALUATION'
1227 AND l_adjustment_status IN ('PREVIEW', 'OBSOLETE')) THEN
1228 l_adjustment_id := l_prev_adjustment_id;
1229 END IF; */
1230 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Prev adj: '||l_prev_adjustment_id||' Current: '||l_adjustment_id);
1231
1232 -- Get the current open period counter
1233 IF igi_iac_common_utils.get_open_period_info(p_asset_hdr_rec.book_type_code,
1234 l_prd_rec)
1235 THEN
1236 l_current_period_counter:=l_prd_rec.period_counter;
1237 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Current Period Counter :'||l_current_period_counter);
1238 END IF;
1239
1240 -- Check whether adjustments exist in the open period
1241 -- If Adjustments exists then stop the Transfer
1242 IF IGI_IAC_COMMON_UTILS.Is_Asset_Adjustment_Done(p_asset_hdr_rec.book_type_code,
1243 p_asset_hdr_rec.asset_id) THEN
1244
1245 FA_SRVR_MSG.Add_Message(
1246 Calling_FN => 'IGI_IAC_TRANSFERS_PKG.Do_Transfer' ,
1247 Name => 'IGI_IAC_ADJUSTMENT_EXCEPTION',
1248 TRANSLATE => TRUE,
1249 APPLICATION => 'IGI');
1250 RETURN FALSE;
1251 END IF;
1252
1253 -- check if this is a prior period transfer
1254 g_prior_period := FALSE;
1255 -- get the period counter for the transfer date period
1256 IF igi_iac_common_utils.get_period_info_for_date(p_asset_hdr_rec.book_type_code,
1257 p_trans_rec.transaction_date_entered,
1258 l_prd_rec_prior)
1259 THEN
1260 l_prior_period_counter:=l_prd_rec_prior.period_counter;
1261 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Prior Period Counter :'||l_prior_period_counter);
1262 END IF;
1263
1264 -- set the Prior_Period flag
1265 IF (l_prior_period_counter < l_current_period_counter) THEN
1266 g_prior_period:= TRUE;
1267 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Need to Process prior period transfers');
1268 ELSE
1269 g_prior_period:= FALSE;
1270 END IF;
1271
1272 -- create a new row in igi_iac_transaction_headers with transaction type code
1273 -- TRANSFERS
1274 -- initailise the new adjustment id
1275 l_adj_id := null;
1276
1277 IGI_IAC_TRANS_HEADERS_PKG.Insert_Row(
1278 x_rowid => l_rowid,
1279 x_adjustment_id => l_adj_id, -- out NOCOPY parameter
1280 x_transaction_header_id => p_trans_rec.transaction_header_id, -- bug 3391000 null,
1281 x_adjustment_id_out => NULL,
1282 x_transaction_type_code => p_trans_rec.transaction_type_code,
1283 x_transaction_date_entered => p_trans_rec.transaction_date_entered,
1284 x_mass_refrence_id => p_trans_rec.mass_reference_id,
1285 x_transaction_sub_type => NULL,
1286 x_book_type_code => p_asset_hdr_rec.book_type_code,
1287 x_asset_id => p_asset_hdr_rec.Asset_id,
1288 x_category_id => p_asset_cat_rec.category_id,
1289 x_adj_deprn_start_date => NULL,
1290 x_revaluation_type_flag => NULL,
1291 x_adjustment_status => 'COMPLETE',
1292 x_period_counter => l_current_period_counter,
1293 x_event_id => p_event_id
1294 );
1295 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data inserted into transaction headers');
1296
1297 -- Get the start period counter for the fiscal year
1298 OPEN c_start_period_counter(l_prd_rec.fiscal_year);
1299 FETCH c_start_period_counter INTO l_start_period_counter;
1300 IF c_start_period_counter%NOTFOUND THEN
1301 RAISE NO_DATA_FOUND;
1302 END IF;
1303 CLOSE c_start_period_counter;
1304
1305 -- fetch the Distribution(s) involved in the transfer (old and new) and the non
1306 -- impacted ones
1307 OPEN c_no_of_imp;
1308 FETCH c_no_of_imp INTO l_no_of_imp;
1309 IF c_no_of_imp%NOTFOUND THEN
1310 RAISE NO_DATA_FOUND;
1311 END IF;
1312 CLOSE c_no_of_imp;
1313
1314 OPEN c_no_of_active;
1315 FETCH c_no_of_active INTO l_no_of_active;
1316 IF c_no_of_active%NOTFOUND THEN
1317 RAISE NO_DATA_FOUND;
1318 END IF;
1319 CLOSE c_no_of_active;
1320
1321 OPEN c_no_of_inactive;
1322 FETCH c_no_of_inactive INTO l_no_of_inactive;
1323 IF c_no_of_inactive%NOTFOUND THEN
1324 RAISE NO_DATA_FOUND;
1325 END IF;
1326 CLOSE c_no_of_inactive;
1327
1328 -- Get the total number of units for the asset
1329 OPEN c_units;
1330 FETCH c_units INTO l_units;
1331 IF c_units%NOTFOUND THEN
1332 RAISE NO_DATA_FOUND;
1333 END IF;
1334 CLOSE c_units;
1335 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset total units: '||l_units);
1336
1337 -- get the number of units involved in the transfer
1338 OPEN c_transfer_units;
1339 FETCH c_transfer_units INTO l_transfer_units;
1340 IF c_transfer_units%NOTFOUND THEN
1341 RAISE NO_DATA_FOUND;
1342 END IF;
1343 CLOSE c_transfer_units;
1344
1345 -- fetch historic salvage value and cost for the asset
1346 OPEN c_get_asset_book(p_asset_hdr_rec.asset_id,
1347 p_asset_hdr_rec.book_type_code);
1348 FETCH c_get_asset_book INTO l_get_asset_book;
1349 IF c_get_asset_book%NOTFOUND THEN
1350 RAISE NO_DATA_FOUND;
1351 END IF;
1352 CLOSE c_get_asset_book;
1353
1354 -- get asset amounts
1355 IF NOT Calc_Asset_Amounts(l_prev_adjustment_id,
1356 p_asset_hdr_rec.asset_id,
1357 p_asset_hdr_rec.book_type_code,
1358 l_current_period_counter,
1359 l_prior_period_counter,
1360 l_get_asset_book.cost,
1361 l_get_asset_book.salvage_value,
1362 p_trans_rec.transaction_header_id,
1363 l_units,
1364 l_transfer_units,
1365 l_ab_amounts,
1366 l_iac_fa_dep_amounts)
1367 THEN
1368 RAISE NO_DATA_FOUND;
1369 END IF;
1370 -- process all the distributions involved in the transfer
1371 i := 0;
1372 FOR l_all_dist in c_all_dist
1373 LOOP
1374 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Distribution id :'||l_all_dist.distribution_id);
1375 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Units for dist:'||l_all_dist.units_assigned);
1376
1377 -- this distribution_id value is set when transaction_header_id_out is the
1378 -- p_trans_rec.transaction_header_id
1379 IF (l_all_dist.transaction_header_id_out = p_trans_rec.transaction_header_id) THEN
1380 l_old_dist := l_all_dist.distribution_id;
1381 inact_cnt := inact_cnt + 1;
1382 ELSE
1383 l_old_dist := NULL;
1384 END IF;
1385
1386 -- Get the impacted distribution values
1387 -- this distribution_id value is set when transaction_header_id_in is the
1388 -- p_trans_rec.transaction_header_id
1389 IF (l_all_dist.transaction_header_id_in = p_trans_rec.transaction_header_id) THEN
1390 l_impacted_dist := l_all_dist.distribution_id;
1391 l_impacted_units := l_all_dist.units_assigned;
1392 -- increment counter for impacted active dists
1393 act_cnt := act_cnt + 1;
1394 ELSIF (l_all_dist.transaction_header_id_in <> p_trans_rec.transaction_header_id
1395 AND l_all_dist.transaction_header_id_out IS NULL) THEN
1396 -- dist is active but was not involved in the transfer
1397 l_impacted_dist := NULL;
1398 l_impacted_units := NULL;
1399 END IF;
1400
1401 -- keep a count of the active rows for rounding difference
1402 IF (l_all_dist.transaction_header_id_out IS NULL) THEN
1403 i := i + 1;
1404 END IF;
1405
1406 -- prorate asset balance and ytd values for the distribution
1407 -- in parameters are asset level values
1408 -- out params are prorated dist values
1409 IF (l_all_dist.distribution_id = l_impacted_dist) THEN
1410 -- proration is for an impacted active new distribution
1411 -- created by the Transfer process
1412 Prorate_amount_for_dist(
1413 p_dist_id => l_all_dist.distribution_id,
1414 p_units_dist => l_all_dist.units_assigned,
1415 p_units_total => l_units,
1416 p_ab_amounts => l_ab_amounts,
1417 p_reval_reserve => l_reval_reserve,
1418 p_general_fund => l_general_fund,
1419 p_backlog_deprn => l_Backlog_deprn_reserve,
1420 p_deprn_reserve => l_deprn_reserve,
1421 p_adjusted_cost => l_adjusted_cost,
1422 p_net_book_value => l_net_book_value,
1423 p_deprn_per => l_deprn_per,
1424 p_op_acct => l_op_acct,
1425 p_ytd_deprn => l_ytd_deprn,
1426 p_op_acct_ytd => l_op_acct_ytd,
1427 p_rr_blog => l_reval_reserve_backlog,
1428 p_op_blog => l_operating_acct_backlog,
1429 p_gf_per => l_general_fund_per
1430 );
1431
1432 -- now prorate for the impacted dist in igi_iac_fa_deprn
1433 Prorate_amount_for_fa_dist(P_dist_id => l_all_dist.distribution_id,
1434 P_units_dist => l_all_dist.units_assigned,
1435 P_units_total => l_units,
1436 P_ab_amounts => l_iac_fa_dep_amounts,
1437 P_deprn_period => l_fa_deprn_period,
1438 P_deprn_ytd => l_fa_deprn_ytd,
1439 P_deprn_reserve => l_fa_deprn_reserve);
1440
1441 ELSE
1442 -- for other distributions which are active but not affected by
1443 -- Transfers and the distributions which will be made ineffective by
1444 -- the Transfers process, retrieve the amounts for the distribution from
1445 -- igi_iac_det_balances table
1446 OPEN c_iac_dist(l_all_dist.distribution_id,
1447 l_prev_adjustment_id
1448 );
1449 FETCH c_iac_dist INTO l_iac_dist;
1450 IF c_iac_dist%NOTFOUND THEN
1451 RAISE NO_DATA_FOUND;
1452 END IF;
1453 CLOSE c_iac_dist;
1454
1455 l_reval_reserve := l_iac_dist.reval_reserve_net;
1456 l_general_fund := l_iac_dist.general_fund_acc;
1457 l_backlog_deprn_reserve := l_iac_dist.deprn_reserve_backlog;
1458 l_deprn_reserve := l_iac_dist.deprn_reserve;
1459 l_adjusted_cost := l_iac_dist.adjustment_cost;
1460 l_net_book_value := l_iac_dist.net_book_value;
1461 l_deprn_per := l_iac_dist.deprn_period;
1462 l_ytd_deprn := l_iac_dist.deprn_ytd;
1463 l_op_acct := l_iac_dist.operating_acct_net;
1464 l_op_acct_ytd := l_iac_dist.operating_acct_ytd;
1465 l_general_fund_per := l_iac_dist.general_fund_per;
1466 l_reval_reserve_backlog := l_iac_dist.reval_reserve_backlog;
1467 l_operating_acct_backlog:= l_iac_dist.operating_acct_backlog;
1468
1469 -- retrieve the values from igi_iac_fa_deprn
1470 OPEN c_fa_dist_data(l_prev_adjustment_id,
1471 l_all_dist.distribution_id
1472 );
1473 FETCH c_fa_dist_data INTO l_fa_dist_data;
1474 IF c_fa_dist_data%NOTFOUND THEN
1475 RAISE NO_DATA_FOUND;
1476 END IF;
1477 CLOSE c_fa_dist_data;
1478
1479 l_fa_deprn_period := l_fa_dist_data.deprn_period;
1480 l_fa_deprn_reserve := l_fa_dist_data.deprn_reserve;
1481 l_fa_deprn_ytd := l_fa_dist_data.deprn_ytd;
1482 END IF; -- end proration
1483
1484 -- calculate dist catchup amounts
1485 IF (g_prior_period AND
1486 l_all_dist.distribution_id IN (l_impacted_dist, l_old_dist)) THEN
1487 Prorate_Catchup_Amounts(p_dist_id => l_all_dist.distribution_id,
1488 p_units_dist => l_all_dist.units_assigned,
1489 p_transfer_units => l_transfer_units,
1490 p_ab_dep_exp => l_ab_amounts.dep_expense_catchup,
1491 p_ab_op_exp => l_ab_amounts.op_expense_catchup,
1492 p_fa_ab_dep_exp => l_iac_fa_dep_amounts.dep_expense_catchup,
1493 p_dist_dep_exp => l_deprn_expense,
1494 p_dist_op_exp => l_dist_op_exp,
1495 p_fa_dist_dep_exp => l_fa_deprn_expense
1496 );
1497 ELSE
1498 l_deprn_expense := 0;
1499 l_dist_op_exp := 0;
1500 l_fa_deprn_expense := 0;
1501 END IF;
1502
1503 -- find all the account ccids for the distribution
1504 find_ccid(p_asset_hdr_rec.book_type_code,
1505 p_asset_hdr_rec.asset_id,
1506 p_trans_rec.transaction_header_id,
1507 l_all_dist.distribution_id,
1508 l_reval_ccid,
1509 l_gen_fund_ccid,
1510 l_backlog_ccid,
1511 l_deprn_ccid,
1512 l_cost_ccid,
1513 l_op_expense_ccid,
1514 l_expense_ccid
1515 );
1516
1517 -- prepare distribution data for igi_iac_det_balances
1518 -- igi_iac_fa_deprn and igi_iac_adjustments
1519 -- Rounding All amounts
1520 IF IGI_IAC_COMMON_UTILS.Iac_round(l_reval_reserve,
1521 p_asset_hdr_rec.book_type_code) THEN
1522 null;
1523 END IF;
1524 IF IGI_IAC_COMMON_UTILS.Iac_round(l_general_fund,
1525 p_asset_hdr_rec.book_type_code) THEN
1526 null;
1527 END IF;
1528 IF IGI_IAC_COMMON_UTILS.Iac_round(l_backlog_deprn_reserve,
1529 p_asset_hdr_rec.book_type_code) THEN
1530 null;
1531 END IF;
1532 IF IGI_IAC_COMMON_UTILS.Iac_round(l_deprn_reserve,
1533 p_asset_hdr_rec.book_type_code) THEN
1534 null;
1535 END IF;
1536 IF IGI_IAC_COMMON_UTILS.Iac_round(l_adjusted_cost,
1537 p_asset_hdr_rec.book_type_code) THEN
1538 null;
1539 END IF;
1540 IF IGI_IAC_COMMON_UTILS.Iac_round(l_op_acct,
1541 p_asset_hdr_rec.book_type_code) THEN
1542 null;
1543 END IF;
1544 IF IGI_IAC_COMMON_UTILS.Iac_round(l_net_book_value,
1545 p_asset_hdr_rec.book_type_code) THEN
1546 null;
1547 END IF;
1548 IF IGI_IAC_COMMON_UTILS.Iac_round(l_deprn_per,
1549 p_asset_hdr_rec.book_type_code) THEN
1550 null;
1551 END IF;
1552 IF IGI_IAC_COMMON_UTILS.Iac_round(l_ytd_deprn,
1553 p_asset_hdr_rec.book_type_code) THEN
1554 null;
1555 END IF;
1556 IF IGI_IAC_COMMON_UTILS.Iac_round(l_op_acct_ytd,
1557 p_asset_hdr_rec.book_type_code) THEN
1558 null;
1559 END IF;
1560 IF IGI_IAC_COMMON_UTILS.Iac_round(l_reval_reserve_backlog,
1561 p_asset_hdr_rec.book_type_code) THEN
1562 null;
1563 END IF;
1564 IF IGI_IAC_COMMON_UTILS.Iac_round(l_operating_acct_backlog,
1565 p_asset_hdr_rec.book_type_code) THEN
1566 null;
1567 END IF;
1568 IF IGI_IAC_COMMON_UTILS.Iac_round(l_general_fund_per,
1569 p_asset_hdr_rec.book_type_code) THEN
1570 null;
1571 END IF;
1572 IF IGI_IAC_COMMON_UTILS.Iac_round(l_fa_deprn_period,
1573 p_asset_hdr_rec.book_type_code) THEN
1574 null;
1575 END IF;
1576 IF IGI_IAC_COMMON_UTILS.Iac_round(l_fa_deprn_reserve,
1577 p_asset_hdr_rec.book_type_code) THEN
1578 null;
1579 END IF;
1580 IF IGI_IAC_COMMON_UTILS.Iac_round(l_fa_deprn_ytd,
1581 p_asset_hdr_rec.book_type_code) THEN
1582 null;
1583 END IF;
1584
1585 IF g_prior_period THEN
1586 -- do currency rounding for the catchup amounts
1587 IF IGI_IAC_COMMON_UTILS.Iac_round(l_deprn_expense,
1588 p_asset_hdr_rec.book_type_code) THEN
1589 NULL;
1590 END IF;
1591 IF IGI_IAC_COMMON_UTILS.Iac_round(l_dist_op_exp,
1592 p_asset_hdr_rec.book_type_code) THEN
1593 NULL;
1594 END IF;
1595 END IF; -- prior period
1596
1597 IF l_all_dist.distribution_id <> nvl(l_old_dist, -1) THEN
1598 -- maintain running total for the active distributions
1599 l_reval_reserve_sum :=l_reval_reserve+l_reval_reserve_sum;
1600 l_general_fund_sum :=l_general_fund+l_general_fund_sum;
1601 l_Backlog_deprn_reserve_sum :=l_Backlog_deprn_reserve+l_Backlog_deprn_reserve_sum;
1602 l_deprn_reserve_sum :=l_deprn_reserve+l_deprn_reserve_sum;
1603 l_adjusted_cost_sum :=l_adjusted_cost+l_adjusted_cost_sum;
1604 l_net_book_value_sum :=l_net_book_value+l_net_book_value_sum;
1605 l_deprn_per_sum :=l_deprn_per+l_deprn_per_sum;
1606 l_ytd_deprn_sum :=l_ytd_deprn+l_ytd_deprn_sum;
1607 l_op_acct_sum :=l_op_acct+l_op_acct_sum;
1608 l_general_fund_per_sum :=l_general_fund_per+l_general_fund_per_sum;
1609 l_reval_reserve_backlog_sum :=l_reval_reserve_backlog+l_reval_reserve_backlog_sum;
1610 l_operating_acct_backlog_sum :=l_operating_acct_backlog+l_operating_acct_backlog_sum;
1611 l_general_fund_per_sum :=l_general_fund_per+l_general_fund_per_sum;
1612
1613 l_fa_deprn_period_sum := l_fa_deprn_period_sum + l_fa_deprn_period;
1614 l_fa_deprn_reserve_sum := l_fa_deprn_reserve_sum + l_fa_deprn_reserve;
1615 l_fa_deprn_ytd_sum := l_fa_deprn_ytd_sum + l_fa_deprn_ytd;
1616
1617 -- add rounding diff to the last distribution
1618 IF (i = l_no_of_imp.counter) THEN
1619 l_reval_reserve:= l_reval_reserve +
1620 (l_ab_amounts.reval_reserve - l_reval_reserve_sum);
1621 l_general_fund:=l_general_fund +
1622 (l_ab_amounts.general_fund -l_general_fund_sum);
1623 l_Backlog_deprn_reserve:=l_backlog_deprn_reserve +
1624 (l_ab_amounts.backlog_deprn_reserve-l_Backlog_deprn_reserve_sum);
1625 l_deprn_reserve:=l_deprn_reserve +
1626 (l_ab_amounts.deprn_reserve -l_deprn_reserve_sum);
1627 l_adjusted_cost:=l_adjusted_cost +
1628 (l_ab_amounts.adjusted_cost -l_adjusted_cost_sum);
1629 l_net_book_value:=l_net_book_value +
1630 (l_ab_amounts.net_book_value -l_net_book_value_sum);
1631 l_deprn_per:=l_deprn_per +
1632 (l_ab_amounts.deprn_amount -l_deprn_per_sum);
1633 l_op_acct:=l_op_acct +
1634 (l_ab_amounts.operating_acct -l_op_acct_sum);
1635 l_reval_reserve_backlog:=l_reval_reserve_backlog +
1636 (l_ab_amounts.reval_reserve_backlog -l_reval_reserve_backlog_sum);
1637 l_operating_acct_backlog:=l_operating_acct_backlog +
1638 (l_ab_amounts.operating_acct_backlog -l_operating_acct_backlog_sum);
1639 l_general_fund_per:=l_general_fund_per +
1640 (l_ab_amounts.general_fund_per -l_general_fund_per_sum);
1641 l_ytd_deprn:=l_ytd_deprn +
1642 (l_ab_amounts.ytd_deprn -l_ytd_deprn_sum);
1643
1644 l_fa_deprn_period :=l_fa_deprn_period +
1645 (l_iac_fa_dep_amounts.deprn_period -l_fa_deprn_period_sum);
1646 l_fa_deprn_reserve :=l_fa_deprn_reserve +
1647 (l_iac_fa_dep_amounts.deprn_reserve -l_fa_deprn_reserve_sum);
1648 l_fa_deprn_ytd :=l_fa_deprn_ytd +
1649 (l_iac_fa_dep_amounts.deprn_ytd -l_fa_deprn_ytd_sum);
1650 END IF;
1651 END IF; -- active dist rounding
1652
1653 -- catchup roundings
1654 IF g_prior_period THEN
1655 IF (l_all_dist.distribution_id = l_old_dist) THEN
1656 l_deprn_expense_old_sum:=l_deprn_expense+l_deprn_expense_old_sum;
1657 l_dist_op_exp_old_sum:= l_dist_op_exp_old_sum + l_dist_op_exp;
1658 l_fa_deprn_expense_old_sum:=l_fa_deprn_expense + l_fa_deprn_expense_old_sum;
1659 IF (inact_cnt = l_no_of_inactive) THEN
1660 l_deprn_expense := l_deprn_expense +
1661 (l_ab_amounts.dep_expense_catchup - l_deprn_expense_old_sum);
1662 l_dist_op_exp := l_dist_op_exp +
1663 (l_ab_amounts.op_expense_catchup - l_dist_op_exp_old_sum);
1664 l_fa_deprn_expense := l_fa_deprn_expense +
1665 (l_iac_fa_dep_amounts.dep_expense_catchup - l_fa_deprn_expense_old_sum);
1666 END IF;
1667 ELSIF (l_all_dist.distribution_id = l_impacted_dist) THEN
1668 l_deprn_expense_imp_sum:=l_deprn_expense+l_deprn_expense_imp_sum;
1669 l_dist_op_exp_imp_sum:= l_dist_op_exp_imp_sum + l_dist_op_exp;
1670 l_fa_deprn_expense_imp_sum:=l_fa_deprn_expense + l_fa_deprn_expense_imp_sum;
1671 IF (act_cnt = l_no_of_active) THEN
1672 l_deprn_expense := l_deprn_expense +
1673 (l_ab_amounts.dep_expense_catchup - l_deprn_expense_imp_sum);
1674 l_dist_op_exp := l_dist_op_exp +
1675 (l_ab_amounts.op_expense_catchup - l_dist_op_exp_imp_sum);
1676 l_fa_deprn_expense := l_fa_deprn_expense +
1677 (l_iac_fa_dep_amounts.dep_expense_catchup - l_fa_deprn_expense_imp_sum);
1678 END IF;
1679 END IF;
1680 ELSE
1681 l_deprn_expense := 0;
1682 l_dist_op_exp := 0;
1683 l_fa_deprn_expense := 0;
1684 END IF; -- catchup roundings end
1685
1686 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Amounts after rounding for dist id: '||l_all_dist.distribution_id);
1687 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjusted Cost :'||l_adjusted_cost);
1688 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Net Book Value :'||l_net_book_value);
1689 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Reserve :'||l_reval_reserve);
1690 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'General Fund :'||l_general_fund);
1691 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Reval Reserve Backlog :'||l_reval_reserve_backlog);
1692 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Operating Account :'||l_op_acct);
1693 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Reserve :'||l_deprn_reserve);
1694 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Backlog Deprn Reserce :'||l_backlog_deprn_reserve);
1695 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deprn Reserve :'||l_deprn_reserve);
1696 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'YTD Deprn :'||l_ytd_deprn);
1697 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Generl Fund :'||l_general_fund);
1698
1699 -- create the catchup accounting entries
1700 IF g_prior_period THEN
1701 -- create the accounting entries for prior period
1702 IF l_impacted_dist = l_all_dist.distribution_id THEN
1703 insert_data_adj(l_adj_id,
1704 p_asset_hdr_rec.book_type_code,
1705 l_expense_ccid,
1706 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1707 'DR',
1708 l_deprn_expense,
1709 'EXPENSE',
1710 l_all_dist.units_assigned,
1711 p_asset_hdr_rec.asset_id,
1712 l_all_dist.distribution_id,
1713 l_current_period_counter,
1714 null,
1715 null,
1716 p_event_id => p_event_id
1717 );
1718 -- IF (l_ab_amounts.cumulative_reval_factor < 1) THEN
1719 -- operating expense
1720 insert_data_adj(l_adj_id,
1721 p_asset_hdr_rec.book_type_code,
1722 l_op_expense_ccid,
1723 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1724 'CR',
1725 l_dist_op_exp,
1726 'OP EXPENSE',
1727 l_all_dist.units_assigned,
1728 p_asset_hdr_rec.asset_id,
1729 l_all_dist.distribution_id,
1730 l_current_period_counter,
1731 null,
1732 null,
1733 p_event_id => p_event_id
1734 );
1735 -- END IF;
1736 -- set the periodic deprn values
1737 ELSIF l_old_dist = l_all_dist.distribution_id THEN
1738 -- inactive distribution
1739 insert_data_adj(l_adj_id,
1740 p_asset_hdr_rec.book_type_code,
1741 l_expense_ccid,
1742 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1743 'CR',
1744 l_deprn_expense,
1745 'EXPENSE',
1746 l_all_dist.units_assigned,
1747 p_asset_hdr_rec.asset_id,
1748 l_all_dist.distribution_id,
1749 l_current_period_counter,
1750 null,
1751 null,
1752 p_event_id => p_event_id
1753 );
1754 -- IF (l_ab_amounts.cumulative_reval_factor < 1) THEN
1755 -- operating expense
1756 insert_data_adj(l_adj_id,
1757 p_asset_hdr_rec.book_type_code,
1758 l_op_expense_ccid,
1759 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1760 'DR',
1761 l_dist_op_exp,
1762 'OP EXPENSE',
1763 l_all_dist.units_assigned,
1764 p_asset_hdr_rec.asset_id,
1765 l_all_dist.distribution_id,
1766 l_current_period_counter,
1767 null,
1768 null,
1769 p_event_id => p_event_id
1770 );
1771 -- END IF;
1772 END IF;-- catchup acct entries
1773 END IF; -- end of prior period catchup calc
1774
1775 -- create accounting netries for the new distributions
1776 -- offset the old dist(s)
1777 IF l_all_dist.distribution_id = l_impacted_dist THEN
1778 -- For Reval reserve
1779 insert_data_adj(l_adj_id,
1780 p_asset_hdr_rec.book_type_code,
1781 l_reval_ccid,
1782 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1783 'CR',
1784 l_reval_reserve,
1785 'REVAL RESERVE',
1786 l_all_dist.units_assigned,
1787 p_asset_hdr_rec.asset_id,
1788 l_all_dist.distribution_id,
1789 l_current_period_counter,
1790 null,
1791 null,
1792 p_event_id => p_event_id
1793 );
1794
1795 -- For General fund
1796 insert_data_adj(l_adj_id,
1797 p_asset_hdr_rec.book_type_code,
1798 l_gen_fund_ccid,
1799 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1800 'CR',
1801 l_general_fund,
1802 'GENERAL FUND',
1803 l_all_dist.units_assigned,
1804 p_asset_hdr_rec.asset_id,
1805 l_all_dist.distribution_id,
1806 l_current_period_counter,
1807 'REVAL RESERVE',
1808 l_reval_ccid, --null
1809 p_event_id => p_event_id
1810 );
1811
1812 -- For backlog depreciation reserve
1813 insert_data_adj(l_adj_id,
1814 p_asset_hdr_rec.book_type_code,
1815 l_backlog_ccid,
1816 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1817 'CR',
1818 l_reval_reserve_backlog, -- l_backlog_deprn_reserve,
1819 'BL RESERVE',
1820 l_all_dist.units_assigned,
1821 p_asset_hdr_rec.asset_id,
1822 l_all_dist.distribution_id,
1823 l_current_period_counter,
1824 'REVAL RESERVE',
1825 l_reval_ccid,
1826 p_event_id => p_event_id
1827 );
1828
1829 insert_data_adj(l_adj_id,
1830 p_asset_hdr_rec.book_type_code,
1831 l_backlog_ccid,
1832 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1833 'CR',
1834 l_operating_acct_backlog, --l_backlog_deprn_reserve,
1835 'BL RESERVE',
1836 l_all_dist.units_assigned,
1837 p_asset_hdr_rec.asset_id,
1838 l_all_dist.distribution_id,
1839 l_current_period_counter,
1840 'OP EXPENSE',
1841 l_op_expense_ccid,
1842 p_event_id => p_event_id
1843 );
1844
1845 -- depriciation reserve
1846 insert_data_adj(l_adj_id,
1847 p_asset_hdr_rec.book_type_code,
1848 l_deprn_ccid,
1849 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1850 'CR',
1851 l_deprn_reserve,
1852 'RESERVE',
1853 l_all_dist.units_assigned,
1854 p_asset_hdr_rec.asset_id,
1855 l_all_dist.distribution_id,
1856 l_current_period_counter,
1857 null,
1858 null,
1859 p_event_id => p_event_id
1860 );
1861 -- For cost account
1862 IF (l_ab_amounts.cumulative_reval_factor >= 1) THEN
1863 l_adj_offset_type := 'REVAL RESERVE';
1864 l_report_ccid := l_reval_ccid;
1865 ELSE
1866 l_adj_offset_type := 'OP EXPENSE';
1867 l_report_ccid := l_op_expense_ccid;
1868 END IF;
1869
1870 insert_data_adj(l_adj_id,
1871 p_asset_hdr_rec.book_type_code,
1872 l_cost_ccid,
1873 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1874 'DR',
1875 l_adjusted_cost,
1876 'COST',
1877 l_all_dist.units_assigned,
1878 p_asset_hdr_rec.asset_id,
1879 l_all_dist.distribution_id,
1880 l_current_period_counter,
1881 l_adj_offset_type,
1882 l_report_ccid,
1883 p_event_id => p_event_id
1884 );
1885
1886 ELSIF l_all_dist.distribution_id=l_old_dist THEN
1887
1888 -- For Reval reserve
1889 insert_data_adj(l_adj_id,
1890 p_asset_hdr_rec.book_type_code,
1891 l_reval_ccid,
1892 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1893 'DR',
1894 l_reval_reserve,
1895 'REVAL RESERVE',
1896 l_all_dist.units_assigned,
1897 p_asset_hdr_rec.asset_id,
1898 l_all_dist.distribution_id,
1899 l_current_period_counter,
1900 null,
1901 null,
1902 p_event_id => p_event_id
1903 );
1904 -- For General fund
1905 insert_data_adj(l_adj_id,
1906 p_asset_hdr_rec.book_type_code,
1907 l_gen_fund_ccid,
1908 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1909 'DR',
1910 l_general_fund,
1911 'GENERAL FUND',
1912 l_all_dist.units_assigned,
1913 p_asset_hdr_rec.asset_id,
1914 l_all_dist.distribution_id,
1915 l_current_period_counter,
1916 'REVAL RESERVE',
1917 l_reval_ccid, -- null
1918 p_event_id => p_event_id
1919 );
1920 -- For backlog deprn reserve
1921 insert_data_adj(l_adj_id,
1922 p_asset_hdr_rec.book_type_code,
1923 l_backlog_ccid,
1924 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1925 'DR',
1926 l_reval_reserve_backlog, --l_backlog_deprn_reserve,
1927 'BL RESERVE',
1928 l_all_dist.units_assigned,
1929 p_asset_hdr_rec.asset_id,
1930 l_all_dist.distribution_id,
1931 l_current_period_counter,
1932 'REVAL RESERVE',
1933 l_reval_ccid,
1934 p_event_id => p_event_id
1935 );
1936
1937 insert_data_adj(l_adj_id,
1938 p_asset_hdr_rec.book_type_code,
1939 l_backlog_ccid,
1940 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1941 'DR',
1942 l_operating_acct_backlog, --l_backlog_deprn_reserve,
1943 'BL RESERVE',
1944 l_all_dist.units_assigned,
1945 p_asset_hdr_rec.asset_id,
1946 l_all_dist.distribution_id,
1947 l_current_period_counter,
1948 'OP EXPENSE',
1949 l_op_expense_ccid,
1950 p_event_id => p_event_id
1951 );
1952
1953 -- For deprn reserve
1954 insert_data_adj(l_adj_id,
1955 p_asset_hdr_rec.book_type_code,
1956 l_deprn_ccid,
1957 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1958 'DR',
1959 l_deprn_reserve,
1960 'RESERVE',
1961 l_all_dist.units_assigned,
1962 p_asset_hdr_rec.asset_id,
1963 l_all_dist.distribution_id,
1964 l_current_period_counter,
1965 null,
1966 null,
1967 p_event_id => p_event_id
1968 );
1969 -- For cost account
1970 IF (l_ab_amounts.cumulative_reval_factor >= 1) THEN
1971 l_adj_offset_type := 'REVAL RESERVE';
1972 l_report_ccid := l_reval_ccid;
1973 ELSE
1974 l_adj_offset_type := 'OP EXPENSE';
1975 l_report_ccid := l_op_expense_ccid;
1976 END IF;
1977
1978 insert_data_adj(l_adj_id,
1979 p_asset_hdr_rec.book_type_code,
1980 l_cost_ccid,
1981 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
1982 'CR',
1983 l_adjusted_cost,
1984 'COST',
1985 l_all_dist.units_assigned,
1986 p_asset_hdr_rec.asset_id,
1987 l_all_dist.distribution_id,
1988 l_current_period_counter,
1989 l_adj_offset_type,
1990 l_report_ccid,
1991 p_event_id => p_event_id
1992 );
1993 END IF; --end of if impacted loop
1994
1995 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data inserted into adjustments for various accounts');
1996 -- Check if it the transfer is inter company
1997 l_interco_amount := 0;
1998 l_interco_count := 0;
1999 OPEN c_get_interco(p_asset_hdr_rec.book_type_code,
2000 p_asset_hdr_rec.asset_id,
2001 l_all_dist.distribution_id,
2002 p_trans_rec.transaction_header_id);
2003 FETCH c_get_interco INTO l_interco_count;
2004 CLOSE c_get_interco;
2005
2006 IF l_interco_count > 0 THEN
2007 -- calculate the intercompany amount
2008 l_interco_amount := l_adjusted_cost - l_deprn_reserve - l_backlog_deprn_reserve
2009 - l_reval_reserve - l_general_fund + l_deprn_expense
2010 - l_dist_op_exp ;
2011 IF (l_all_dist.distribution_id = l_old_dist) THEN
2012 -- old distribution
2013 -- get ccid
2014 -- get the ccod for interco ar
2015 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_asset_hdr_rec.book_type_code,
2016 p_asset_hdr_rec.asset_id,
2017 l_all_dist.distribution_id,
2018 'INTERCO_AR_ACCT',
2019 p_trans_rec.transaction_header_id,
2020 'TRANSFER',
2021 l_interco_ccid);
2022
2023 -- insert the accounting entry
2024 insert_data_adj(l_adj_id,
2025 p_asset_hdr_rec.book_type_code,
2026 l_interco_ccid, -- l_interco_data.code_combination_id,
2027 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
2028 'DR', -- l_interco_data.debit_credit_flag,
2029 l_interco_amount,
2030 'INTERCO AR', --l_interco_data.adjustment_type,
2031 l_all_dist.units_assigned,
2032 p_asset_hdr_rec.asset_id,
2033 l_all_dist.distribution_id,
2034 l_current_period_counter,
2035 null,
2036 null,
2037 p_event_id => p_event_id
2038 );
2039 ELSIF (l_all_dist.distribution_id = l_impacted_dist) THEN
2040 -- new distribution
2041 -- get the ccod for interco ap
2042 l_return_value:=IGI_IAC_COMMON_UTILS.get_account_ccid(p_asset_hdr_rec.book_type_code,
2043 p_asset_hdr_rec.asset_id,
2044 l_all_dist.distribution_id,
2045 'INTERCO_AP_ACCT',
2046 p_trans_rec.transaction_header_id,
2047 'TRANSFER',
2048 l_interco_ccid);
2049
2050 -- accounting entry
2051 insert_data_adj(l_adj_id,
2052 p_asset_hdr_rec.book_type_code,
2053 l_interco_ccid, -- l_interco_data.code_combination_id,
2054 nvl(p_asset_hdr_rec.set_of_books_id,l_sob_id),
2055 'CR', -- l_interco_data.debit_credit_flag,
2056 l_interco_amount,
2057 'INTERCO AP', --l_interco_data.adjustment_type,
2058 l_all_dist.units_assigned,
2059 p_asset_hdr_rec.asset_id,
2060 l_all_dist.distribution_id,
2061 l_current_period_counter,
2062 null,
2063 null,
2064 p_event_id => p_event_id
2065 );
2066 END IF; -- dist old or impacted
2067 END IF; -- l_interco_data
2068
2069 -- inserting data into the detail balances tables
2070 IF l_all_dist.distribution_id = l_old_dist THEN
2071 IF g_prior_period THEN
2072 l_ytd_deprn := l_ytd_deprn - l_deprn_expense;
2073 l_fa_deprn_ytd := l_fa_deprn_ytd - l_fa_deprn_expense;
2074 END IF;
2075 -- Impacted old distribution
2076 -- create inactive distribution
2077 insert_data_det(l_adj_id,
2078 p_asset_hdr_rec.asset_id,
2079 l_all_dist.distribution_id,
2080 l_current_period_counter,
2081 p_asset_hdr_rec.book_type_code,
2082 0,
2083 0,
2084 0,
2085 0,
2086 0,
2087 0, -- l_op_acct,
2088 0,
2089 0,
2090 l_ytd_deprn,
2091 0,
2092 0,
2093 0,
2094 l_ab_amounts.current_reval_factor,
2095 l_ab_amounts.cumulative_reval_factor,
2096 'N',
2097 0, -- l_op_acct_ytd,
2098 0, --l_operating_acct_backlog,
2099 l_ab_amounts.last_reval_date
2100 );
2101
2102 l_rowid := NULL;
2103 igi_iac_fa_deprn_pkg.insert_row(
2104 x_rowid => l_rowid,
2105 x_book_type_code => p_asset_hdr_rec.book_type_code,
2106 x_asset_id => p_asset_hdr_rec.asset_id,
2107 x_distribution_id => l_all_dist.distribution_id,
2108 x_period_counter => l_current_period_counter,
2109 x_adjustment_id => l_adj_id,
2110 x_deprn_period => 0,
2111 x_deprn_ytd => l_fa_deprn_ytd ,
2112 x_deprn_reserve => 0,
2113 x_active_flag => 'N',
2114 x_mode => 'R');
2115
2116 ELSIF l_all_dist.distribution_id= l_impacted_dist THEN
2117
2118 IF g_prior_period THEN
2119 l_deprn_per:=l_deprn_expense;
2120 l_general_fund_per:= l_deprn_per;
2121 l_ytd_deprn:=l_deprn_per;
2122 l_fa_deprn_ytd := l_fa_deprn_expense;
2123 ELSE
2124 --This check will reset values to zero in case of new distributions for
2125 --current period transfers
2126 l_deprn_per:=0;
2127 l_general_fund_per:=0;
2128 l_ytd_deprn:=0;
2129 l_fa_deprn_ytd := 0;
2130 END IF;
2131 -- new distribution, create an active row
2132 IF l_get_asset_book.period_counter_fully_reserved IS NULL THEN
2133 l_iac_deprn_period_amount := l_deprn_per;
2134 ELSE
2135 l_iac_deprn_period_amount := 0;
2136 END IF;
2137
2138 insert_data_det(l_adj_id,
2139 p_asset_hdr_rec.asset_id,
2140 l_all_dist.distribution_id,
2141 l_current_period_counter,
2142 p_asset_hdr_rec.book_type_code,
2143 l_adjusted_cost,
2144 l_net_book_value,
2145 l_reval_reserve,
2146 l_general_fund,
2147 l_reval_reserve_backlog,
2148 l_op_acct, --0,
2149 l_deprn_reserve,
2150 l_backlog_deprn_reserve,
2151 l_ytd_deprn, --0,
2152 l_iac_deprn_period_amount,
2153 l_general_fund,
2154 l_general_fund_per,
2155 l_ab_amounts.current_reval_factor,
2156 l_ab_amounts.cumulative_reval_factor,
2157 null,
2158 0, -- l_op_acct_ytd is no longer maintained
2159 l_operating_acct_backlog, --0,
2160 l_ab_amounts.last_reval_date
2161 );
2162
2163 -- insert into igi_iac_fa_deprn
2164 IF l_get_asset_book.period_counter_fully_reserved IS NULL THEN
2165 l_fa_deprn_period_amount := l_fa_deprn_period;
2166 ELSE
2167 l_fa_deprn_period_amount := 0;
2168 END IF;
2169
2170 l_rowid := NULL;
2171 igi_iac_fa_deprn_pkg.insert_row(
2172 x_rowid => l_rowid,
2173 x_book_type_code => p_asset_hdr_rec.book_type_code,
2174 x_asset_id => p_asset_hdr_rec.asset_id,
2175 x_distribution_id => l_all_dist.distribution_id,
2176 x_period_counter => l_current_period_counter,
2177 x_adjustment_id => l_adj_id,
2178 x_deprn_period => l_fa_deprn_period_amount,
2179 x_deprn_ytd => l_fa_deprn_ytd, -- 0,
2180 x_deprn_reserve => l_fa_deprn_reserve,
2181 x_active_flag => NULL,
2182 x_mode => 'R');
2183
2184 ELSIF l_all_dist.distribution_id NOT IN (nvl(l_impacted_dist, -1),nvl(l_old_dist, -1)) THEN
2185
2186 -- active dist, not involved in transfer, being rolled forward
2187 IF l_get_asset_book.period_counter_fully_reserved IS NULL THEN
2188 l_iac_deprn_period_amount := l_deprn_per;
2189 ELSE
2190 l_iac_deprn_period_amount := 0;
2191 END IF;
2192
2193 insert_data_det(l_adj_id,
2194 p_asset_hdr_rec.asset_id,
2195 l_all_dist.distribution_id,
2196 l_current_period_counter,
2197 p_asset_hdr_rec.book_type_code,
2198 l_adjusted_cost,
2199 l_net_book_value,
2200 l_reval_reserve,
2201 l_general_fund,
2202 l_reval_reserve_backlog,
2203 l_op_acct,
2204 l_deprn_reserve,
2205 l_backlog_deprn_reserve,
2206 l_ytd_deprn,
2207 l_iac_deprn_period_amount,
2208 l_general_fund,
2209 l_general_fund_per,
2210 l_ab_amounts.current_reval_factor,
2211 l_ab_amounts.cumulative_reval_factor,
2212 null,
2213 0, -- l_op_acct_ytd, iac no longer maintains this value
2214 l_operating_acct_backlog,
2215 l_ab_amounts.last_reval_date
2216 );
2217
2218 -- insert into igi_iac_fa_deprn
2219 IF l_get_asset_book.period_counter_fully_reserved IS NULL THEN
2220 l_fa_deprn_period_amount := l_fa_dist_data.deprn_period;
2221 ELSE
2222 l_fa_deprn_period_amount := 0;
2223 END IF;
2224
2225 l_rowid := NULL;
2226 igi_iac_fa_deprn_pkg.insert_row(
2227 x_rowid => l_rowid,
2228 x_book_type_code => p_asset_hdr_rec.book_type_code,
2229 x_asset_id => p_asset_hdr_rec.asset_id,
2230 x_distribution_id => l_all_dist.distribution_id,
2231 x_period_counter => l_current_period_counter,
2232 x_adjustment_id => l_adj_id,
2233 x_deprn_period => l_fa_deprn_period_amount,
2234 x_deprn_ytd => l_fa_deprn_ytd ,
2235 x_deprn_reserve => l_fa_deprn_reserve,
2236 x_active_flag => NULL,
2237 x_mode => 'R');
2238
2239 END IF;
2240 -- End of loop for insert into det_balances table
2241 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data inserted into detail balances');
2242 END LOOP;
2243 -- End of loop for all active distributions
2244 -- Roll the inactive distributions forward for the new adjustment id
2245 Roll_Inactive_Forward(p_adjustment_id => l_prev_adjustment_id,
2246 p_new_adj_id => l_adj_id,
2247 p_book_type_code => p_asset_hdr_rec.book_type_code,
2248 p_asset_id => p_asset_hdr_rec.asset_id,
2249 p_curr_prd_cntr => l_current_period_counter
2250 );
2251
2252 -- update asset balances table with transfer info
2253 IF l_ab_amounts.period_counter = l_current_period_counter THEN
2254 -- this is a current period transfer
2255 IF (g_Prior_period) THEN
2256 IF l_get_asset_book.period_counter_fully_reserved IS NULL THEN
2257 l_iac_deprn_period_amount := l_ab_amounts.dep_expense_catchup;
2258 ELSE
2259 l_iac_deprn_period_amount := 0;
2260 END IF;
2261
2262 IGI_IAC_ASSET_BALANCES_PKG.update_row (
2263 x_asset_id =>p_asset_hdr_rec.asset_id,
2264 x_book_type_code =>p_asset_hdr_rec.book_type_code,
2265 x_period_counter =>l_current_period_counter,
2266 x_net_book_value =>l_ab_amounts.net_book_value,
2267 x_adjusted_cost =>l_ab_amounts.adjusted_cost,
2268 x_operating_acct =>l_ab_amounts.operating_acct,
2269 x_reval_reserve =>l_ab_amounts.reval_reserve,
2270 x_deprn_amount =>l_iac_deprn_period_amount,
2271 x_deprn_reserve =>l_ab_amounts.deprn_reserve,
2272 x_backlog_deprn_reserve =>l_ab_amounts.backlog_deprn_reserve,
2273 x_general_fund =>l_ab_amounts.general_fund,
2274 x_last_reval_date =>l_ab_amounts.last_reval_date,
2275 x_current_reval_factor =>l_ab_amounts.current_reval_factor,
2276 x_cumulative_reval_factor =>l_ab_amounts.cumulative_reval_factor,
2277 x_mode =>'R'
2278 );
2279
2280 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data Updated in Asset balances for the catch up');
2281 END IF;
2282
2283 ELSE
2284 -- this is a prior period transfer
2285 IF (g_Prior_period) THEN
2286 l_deprn_amount := l_ab_amounts.dep_expense_catchup;
2287 ELSE
2288 l_deprn_amount := l_ab_amounts.deprn_amount;
2289 END IF;
2290
2291 -- set deprn amount
2292 IF l_get_asset_book.period_counter_fully_reserved IS NULL THEN
2293 l_iac_deprn_period_amount := l_deprn_amount;
2294 ELSE
2295 l_iac_deprn_period_amount := 0;
2296 END IF;
2297 l_rowid := NULL;
2298 IGI_IAC_ASSET_BALANCES_PKG.insert_row (
2299 x_rowid => l_rowid,
2300 x_asset_id =>p_asset_hdr_rec.asset_id,
2301 x_book_type_code =>p_asset_hdr_rec.book_type_code,
2302 x_period_counter =>l_current_period_counter,
2303 x_net_book_value =>l_ab_amounts.net_book_value,
2304 x_adjusted_cost =>l_ab_amounts.adjusted_cost,
2305 x_operating_acct =>l_ab_amounts.operating_acct,
2306 x_reval_reserve =>l_ab_amounts.reval_reserve,
2307 x_deprn_amount =>l_iac_deprn_period_amount,
2308 x_deprn_reserve =>l_ab_amounts.deprn_reserve,
2309 x_backlog_deprn_reserve =>l_ab_amounts.backlog_deprn_reserve,
2310 x_general_fund =>l_ab_amounts.general_fund,
2311 x_last_reval_date =>l_ab_amounts.last_reval_date,
2312 x_current_reval_factor =>l_ab_amounts.current_reval_factor,
2313 x_cumulative_reval_factor =>l_ab_amounts.cumulative_reval_factor,
2314 x_mode =>'R'
2315 );
2316 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Data Inserted in Asset balances');
2317 END IF;
2318
2319 -- update the previous active row for the asset in igi_iac_transaction_headers
2320 -- in order to make it inactive by setting adjustment_id_out= adjustment_id of
2321 -- the active row in igi_iac_transaction_headers
2322 IGI_IAC_TRANS_HEADERS_PKG.Update_Row(
2323 x_prev_adjustment_id => l_adj_id_out,
2324 x_adjustment_id => l_adj_id
2325 );
2326
2327 -- transfer processing completed successfully
2328 RETURN TRUE;
2329
2330 EXCEPTION
2331 WHEN e_iac_not_enabled THEN
2332 FA_SRVR_MSG.Add_Message(
2333 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
2334 Name => 'IGI_IAC_NOT_INSTALLED'
2335 );
2336 RETURN TRUE;
2337
2338 WHEN e_not_iac_book THEN
2339 FA_SRVR_MSG.Add_Message(
2340 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
2341 Name => 'IGI_IAC_NOT_IAC_BOOK'
2342 );
2343 RETURN TRUE;
2344
2345 WHEN e_asset_not_revalued THEN
2346 OPEN c_asset_num;
2347 FETCH c_asset_num INTO l_asset_num;
2348 CLOSE c_asset_num;
2349 FA_SRVR_MSG.Add_Message(
2350 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
2351 Name => 'IGI_IAC_ASSET_NOT_REVALUED',
2352 TOKEN1 => 'ASSET_NUM',
2353 VALUE1 => l_asset_num);
2354 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'This asset has not been revalued');
2355
2356 RETURN(TRUE);
2357
2358 WHEN e_no_gl_info THEN
2359
2360 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2361 p_full_path => l_path,
2362 p_string => 'Could not retrive GL information for Book');
2363
2364 FA_SRVR_MSG.add_message(
2365 Calling_Fn => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
2366 Name => 'IGI_IAC_NO_GL_INFO'
2367 );
2368 RETURN FALSE;
2369
2370
2371 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN -- This occurs when distribution doesn't have IMPACTED or OLD link
2372 RETURN(FALSE); -- Ensures that data is rolled back;calling procedure rolls back on error, no need of rollback here
2373
2374 WHEN others THEN
2375 l_mesg:=SQLERRM;
2376 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
2377 FA_SRVR_MSG.Add_Message(
2378 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
2379 Name => 'IGI_IAC_EXCEPTION',
2380 TOKEN1 => 'PACKAGE',
2381 VALUE1 => 'Transfers',
2382 TOKEN2 => 'ERROR_MESSAGE',
2383 VALUE2 => l_mesg);
2384 RETURN(FALSE);
2385 END Do_transfer;
2386
2387 --+=======================================================================================+
2388 --| Function Do_prior_transfer is called from the FA deprn Run .This would inturn |
2389 --| call the do_transfer to insert data for current period counter.The additional |
2390 --| catch up (deprn expense) is calculated in this function and the detail balances |
2391 --| are updated accordingly |
2392 -- ========================================================================================+
2393
2394 FUNCTION Do_prior_transfer(p_book_type_code fa_books.book_type_code%type,
2395 p_asset_id fa_additions_b.asset_id%type,
2396 p_category_id fa_categories.category_id%type,
2397 p_transaction_header_id fa_transaction_headers.transaction_header_id%type,
2398 p_cost fa_books.cost%type,
2399 p_adjusted_cost fa_books.adjusted_cost%type,
2400 p_salvage_value fa_books.salvage_value%type,
2401 p_current_units fa_additions_b.current_units%type,
2402 p_life_in_months fa_books.life_in_months%type,
2403 p_calling_function varchar2,
2404 p_event_id number --R12 uptake
2405 )
2406 RETURN BOOLEAN
2407 IS
2408 -- Cursor to select transaction data for transaction header_rec type */
2409
2410 CURSOR c_trans_data IS
2411 SELECT transaction_date_entered,
2412 mass_reference_id,
2413 transaction_type_code
2414 FROM fa_transaction_headers
2415 WHERE transaction_header_id=p_transaction_header_id;
2416
2417 /* Cursor to select asset data for asset header rec */
2418
2419 CURSOR c_asset_data IS
2420 SELECT set_of_books_id
2421 FROM fa_book_controls
2422 WHERE book_type_code=p_book_type_code;
2423
2424 l_trans_rec FA_API_TYPES.trans_rec_type;
2425 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
2426 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
2427
2428 l_trans_data c_trans_data%rowtype;
2429 l_asset_data c_asset_data%rowtype;
2430
2431 l_mesg VARCHAR2(500);
2432
2433 l_path varchar2(150);
2434 BEGIN
2435
2436 l_path := g_path||'Do_prior_transfer';
2437
2438 /* Retreive data for transaction header record (not available as input parameter)*/
2439 Open c_trans_data;
2440 Fetch c_trans_data into l_trans_data;
2441 close c_trans_data;
2442
2443 /* Retreive data for asset header record(not available as input parameter) */
2444 Open c_asset_data;
2445 Fetch c_asset_data into l_asset_data;
2446 close c_asset_data;
2447
2448 l_trans_rec.transaction_header_id:=p_transaction_header_id;
2449 l_trans_rec.transaction_date_entered:=l_trans_data.transaction_date_entered;
2450 l_trans_rec.transaction_type_code:=l_trans_data.transaction_type_code;
2451 l_trans_rec.mass_reference_id:=l_trans_data.mass_reference_id;
2452
2453 l_asset_hdr_rec.book_type_code:=p_book_type_code;
2454 l_asset_hdr_rec.asset_id:=p_asset_id;
2455 l_asset_hdr_rec.set_of_books_id:=l_asset_data.set_of_books_id;
2456
2457 l_asset_cat_rec.category_id:=p_category_id;
2458
2459 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2460 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Start Of Prior Period Processing');
2461 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2462 /* Call the Do_Transfer process to insert data for current open period */
2463 IF NOT(Do_transfer(l_trans_rec,
2464 l_asset_hdr_rec,
2465 l_asset_cat_rec,
2466 'IGI_IAC_TRANSFERS_PKG.Do_prior_transfer',
2467 p_event_id))THEN
2468
2469 app_exception.raise_exception;
2470 END IF;
2471
2472 RETURN(TRUE);
2473
2474 EXCEPTION
2475 WHEN OTHERS THEN
2476
2477 l_mesg:=SQLERRM;
2478 FA_SRVR_MSG.Add_Message(
2479 Calling_FN => 'IGI_IAC_TRANSFER_PKG.Do_Transfer',
2480 Name => 'IGI_IAC_EXCEPTION',
2481 TOKEN1 => 'PACKAGE',
2482 VALUE1 => 'Transfers',
2483 TOKEN2 => 'ERROR_MESSAGE',
2484 VALUE2 => l_mesg);
2485
2486 igi_iac_debug_pkg.debug_unexpected_msg(l_path);
2487 RETURN(FALSE);
2488
2489 END Do_prior_transfer;
2490
2491 /*
2492 +=======================================================================================+
2493 | Function Do_Rollback_Deprn is called from the FA Rollback deprn.This would |
2494 | rollback the data inserted by prior transfers in the latest depreciation run if |
2495 | any in that depreciation run . |
2496 ========================================================================================+
2497 */
2498 FUNCTION Do_Rollback_Deprn(
2499 p_book_type_code VARCHAR2,
2500 p_period_counter NUMBER,
2501 p_calling_function VARCHAR2
2502 ) return BOOLEAN IS
2503 /* Cursor to select data from transaction headers which need to be rolled back */
2504
2505 CURSOR c_trans_headers IS
2506 SELECT *
2507 FROM igi_iac_transaction_headers
2508 WHERE book_type_code=p_book_type_code
2509 AND period_counter=p_period_counter
2510 AND transaction_type_code='TRANSFER';
2511
2512
2513 CURSOR c_deprn_expense(cp_asset_id igi_iac_det_balances.asset_id%TYPE,
2514 cp_adjustment_id IGI_IAC_DET_BALANCES.adjustment_id%type) IS
2515 SELECT sum(deprn_period)
2516 FROM igi_iac_det_balances
2517 WHERE book_type_code = p_book_type_code
2518 AND asset_id = cp_asset_id
2519 AND adjustment_id = cp_adjustment_id;
2520
2521 /* Cursor to select the previous data */
2522
2523 CURSOR c_prev_data(c_adjustment_id igi_iac_transaction_headers.adjustment_id%type) IS
2524 SELECT *
2525 FROM igi_iac_transaction_headers
2526 WHERE adjustment_id_out=c_adjustment_id;
2527
2528 /* Cursor to find the amounts that need to be transferred to the new dist
2529 created by transfer */
2530
2531 CURSOR c_amounts(c_period_counter in IGI_IAC_ASSET_BALANCES.period_counter%type,
2532 cp_asset_id IGI_IAC_ASSET_BALANCES.asset_id%TYPE) IS
2533 SELECT *
2534 FROM igi_iac_asset_balances
2535 WHERE asset_id = cp_asset_id
2536 AND book_type_code = p_book_type_code
2537 AND period_counter = p_period_counter;
2538
2539 /* Cursor to select impacted distributions for roll back */
2540
2541 CURSOR c_dist(c_adjustment_id igi_iac_adjustments.adjustment_id%type) IS
2542 SELECT distribution_id
2543 FROM igi_iac_det_balances
2544 WHERE adjustment_id=c_adjustment_id;
2545
2546 CURSOR c_fa_dist(cp_adjustment_id igi_iac_adjustments.adjustment_id%type,
2547 cp_asset_id igi_iac_fa_deprn.asset_id%TYPE) IS
2548 SELECT book_type_code,asset_id,period_counter,distribution_id,adjustment_id
2549 FROM igi_iac_fa_deprn
2550 WHERE asset_id = cp_asset_id
2551 AND book_type_code = p_book_type_code
2552 AND adjustment_id = cp_adjustment_id;
2553
2554
2555
2556 l_trans_headers c_trans_headers%rowtype;
2557 l_amounts c_amounts%rowtype;
2558 l_prd_rec_prior igi_iac_types.prd_rec;
2559
2560 l_deprn_expense FA_DEPRN_SUMMARY.deprn_amount%type;
2561 l_Expense_diff IGI_IAC_DET_BALANCES.deprn_period%type;
2562 l_prior_period_counter IGI_IAC_DET_BALANCES.period_counter%type;
2563 l_current_period_counter IGI_IAC_DET_BALANCES.period_counter%type;
2564 l_prev_adjustment igi_iac_transaction_headers%ROWTYPE;
2565
2566 l_path varchar2(150);
2567 BEGIN
2568
2569 l_path := g_path||'Do_Rollback_Deprn';
2570
2571 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************');
2572 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Rollback of Transactions ');
2573 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************');
2574
2575 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Processing For book '||p_book_type_code);
2576 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Current Period Counter :'||p_period_counter);
2577
2578
2579 FOR l_trans_headers in c_trans_headers
2580 LOOP
2581 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction header Id :'||l_trans_headers.transaction_header_id);
2582 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Transaction Date Entered :'||l_trans_headers.transaction_date_entered);
2583 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Adjustment Id :'||l_trans_headers.adjustment_id);
2584 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Asset Id :'||l_trans_headers.asset_id);
2585
2586
2587 /*Check If it is Prior Period Transfer*/
2588 IF (IGI_IAC_COMMON_UTILS.get_period_info_for_date(p_book_type_code,
2589 l_trans_headers.transaction_date_entered,
2590 l_prd_rec_prior)) THEN
2591 l_prior_period_counter:=l_prd_rec_prior.period_counter;
2592 END IF;
2593
2594
2595 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Period counter :'||l_prior_period_counter);
2596
2597 IF l_prior_period_counter is not null AND (L_prior_period_counter< p_period_counter) THEN
2598
2599 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Processing prior period data on this asset');
2600
2601 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2602 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleting From detail balances...');
2603 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2604
2605 /*DELETE from igi_iac_det_balances
2606 WHERE book_type_code=p_book_type_code
2607 AND period_counter=p_period_counter
2608 AND adjustment_id=l_trans_headers.adjustment_id;*/
2609
2610
2611 FOR l_dist in c_dist(l_trans_headers.adjustment_id)
2612 loop
2613 IGI_IAC_DET_BALANCES_PKG.delete_row(
2614 x_adjustment_id =>l_trans_headers.adjustment_id,
2615 x_asset_id =>l_trans_headers.asset_id,
2616 x_distribution_id =>l_dist.distribution_id,
2617 x_book_type_code =>l_trans_headers.book_type_code,
2618 x_period_counter =>p_period_counter
2619 );
2620 End loop;
2621
2622 FOR l_fa_dist IN c_fa_dist(l_trans_headers.adjustment_id,l_trans_headers.asset_id) LOOP
2623 IGI_IAC_FA_DEPRN_PKG.delete_row(
2624 x_adjustment_id =>l_fa_dist.adjustment_id,
2625 x_asset_id =>l_fa_dist.asset_id,
2626 x_distribution_id =>l_fa_dist.distribution_id,
2627 x_book_type_code =>l_fa_dist.book_type_code,
2628 x_period_counter =>l_fa_dist.period_counter
2629 );
2630 END LOOP;
2631
2632 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2633 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleting From adjustments...');
2634 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2635
2636 /* DELETE from igi_iac_adjustments
2637 WHERE book_type_code=p_book_type_code
2638 AND period_counter=p_period_counter
2639 AND adjustment_id=l_trans_headers.adjustment_id; */
2640
2641 IGI_IAC_ADJUSTMENTS_PKG.delete_row(
2642 x_adjustment_id =>l_trans_headers.adjustment_id
2643 );
2644
2645 OPEN c_prev_data(l_trans_headers.adjustment_id);
2646 FETCH c_prev_data INTO l_prev_adjustment;
2647 CLOSE c_prev_data;
2648
2649 open c_deprn_expense(l_trans_headers.asset_id,l_prev_adjustment.adjustment_id);
2650 fetch c_deprn_expense into l_deprn_expense;
2651 close c_deprn_expense;
2652
2653 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Expense Amount to be adjusted :'||l_deprn_expense);
2654
2655 open c_amounts(p_period_counter,l_trans_headers.asset_id);
2656 fetch c_amounts into l_amounts;
2657 close c_amounts;
2658
2659 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2660 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updating Asset balances ...');
2661 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2662
2663 IGI_IAC_ASSET_BALANCES_PKG.update_row (
2664 x_asset_id =>l_trans_headers.asset_id,
2665 x_book_type_code =>p_book_type_code,
2666 x_period_counter =>p_period_counter,
2667 x_net_book_value =>l_amounts.net_book_value,
2668 x_adjusted_cost =>l_amounts.adjusted_cost,
2669 x_operating_acct =>l_amounts.operating_acct,
2670 x_reval_reserve =>l_amounts.reval_reserve,
2671 x_deprn_amount =>l_deprn_expense,
2672 x_deprn_reserve =>l_amounts.deprn_reserve,
2673 x_backlog_deprn_reserve =>l_amounts.backlog_deprn_reserve,
2674 x_general_fund =>l_amounts.general_fund,
2675 x_last_reval_date =>l_amounts.last_reval_date,
2676 x_current_reval_factor =>l_amounts.current_reval_factor,
2677 x_cumulative_reval_factor =>l_amounts.cumulative_reval_factor,
2678 x_mode =>'R'
2679 );
2680
2681
2682 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2683 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Updating transaction headers...');
2684 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2685
2686 FOR l_prev_data in c_prev_data(l_trans_headers.adjustment_id)
2687 LOOP
2688 IGI_IAC_TRANS_HEADERS_PKG.update_row (
2689 x_prev_adjustment_id =>l_prev_data.adjustment_id,
2690 x_adjustment_id =>null,
2691 x_mode =>'R'
2692 );
2693 END LOOP;
2694
2695
2696 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2697 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'Deleting From transaction headers...');
2698 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'============================================================');
2699
2700 IGI_IAC_TRANS_HEADERS_PKG.delete_row (
2701 x_adjustment_id =>l_trans_headers.adjustment_id
2702 );
2703
2704 END IF;
2705
2706
2707 END LOOP; -- End of First for
2708
2709 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************');
2710 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,' Rollback completed successfully ');
2711 igi_iac_debug_pkg.debug_other_string(g_state_level,l_path,'*****************************************************************');
2712
2713 RETURN(TRUE);
2714 END Do_rollback_deprn;
2715
2716 BEGIN
2717
2718 --===========================FND_LOG.START=====================================
2719
2720 g_state_level := FND_LOG.LEVEL_STATEMENT;
2721 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
2722 g_event_level := FND_LOG.LEVEL_EVENT;
2723 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
2724 g_error_level := FND_LOG.LEVEL_ERROR;
2725 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
2726 g_path := 'IGI.PLSQL.igiiatfb.IGI_IAC_TRANSFERS_PKG.';
2727
2728 --===========================FND_LOG.END=====================================
2729
2730
2731
2732 END IGI_IAC_TRANSFERS_PKG;