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