[Home] [Help]
PACKAGE BODY: APPS.IGI_IMP_IAC_TRANSFER_PKG
Source
1 PACKAGE BODY IGI_IMP_IAC_TRANSFER_PKG AS
2 -- $Header: igiimtdb.pls 120.42 2010/12/20 10:18:20 schakkin ship $
3
4 --
5 -- Define Global Package Variables
6 --
7 GLOBAL_CURRENT_PROC VARCHAR2(50) ;
8 IGI_IMP_TFR_ERROR EXCEPTION ;
9
10
11 --===========================FND_LOG.START=====================================
12
13 g_state_level NUMBER;
14 g_proc_level NUMBER;
15 g_event_level NUMBER;
16 g_excep_level NUMBER;
17 g_error_level NUMBER;
18 g_unexp_level NUMBER;
19 g_path VARCHAR2(100);
20
21 --===========================FND_LOG.END=======================================
22
23
24 PROCEDURE set_interface_ctrl_status( p_book_type_code VARCHAR2 ,
25 p_category_id NUMBER ,
26 p_status VARCHAR2
27 )
28 IS
29 BEGIN
30
31 UPDATE igi_imp_iac_interface_ctrl ct
32 SET ct.transfer_status = p_status
33 WHERE ct.book_type_code = p_book_type_code
34 AND ct.category_id = p_category_id ;
35
36
37 RETURN ;
38
39 EXCEPTION
40 WHEN OTHERS THEN
41 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => g_path||'set_interface_ctrl_status');
42 raise igi_imp_tfr_error ;
43 END;
44
45 FUNCTION Validate_Assets(p_book_type_code in VARCHAR2,
46 p_category_id NUMBER)
47 RETURN BOOLEAN AS
48 CURSOR C_Assets
49 IS
50 SELECT *
51 FROM igi_imp_iac_interface ii
52 WHERE ii.book_type_code = p_book_type_code
53 AND ii.category_id = p_Category_id
54 AND ii.transferred_flag = 'N'
55 AND nvl(ii.valid_flag,'N') = 'N';
56
57 -- Variables
58
59 l_assets_valid BOOLEAN;
60 cumm_reval_rate NUMBER;
61 total_depreciation NUMBER;
62 l_deprn_per_period_hist NUMBER;
63 l_remaining_periods NUMBER;
64 l_elapsed_periods NUMBER;
65 l_elapsed_periods_curr_yr NUMBER;
66 l_max_backlog NUMBER;
67 l_adjusted_cost NUMBER;
68 l_general_fund NUMBER;
69 l_reval_reserve NUMBER;
70 l_deprn_exp_mhca NUMBER;
71 l_ytd_mhca NUMBER;
72 l_min_backlog NUMBER;
73 l_operating_account_cost NUMBER;
74 l_operating_account_backlog NUMBER;
75 l_depreciate VARCHAR2(3);
76 l_salvage_correction NUMBER;
77
78 l_errbuf VARCHAR2(250);
79
80 BEGIN
81 l_assets_valid := TRUE;
82
83 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
84 p_full_path => g_path||'Validate_Assets',
85 p_string => 'Starting Assets Validation');
86
87 fnd_file.put_line(fnd_file.log, 'The following assets are invalid:');
88
89 FOR arec in C_Assets LOOP
90
91 SELECT depreciate_flag
92 INTO l_depreciate
93 FROM fa_books
94 WHERE book_type_code = arec.book_type_code
95 AND asset_id = arec.asset_id
96 AND date_ineffective is NULL; -- Bug 5383551
97
98 cumm_reval_rate := arec.cost_mhca / arec.cost_hist ;
99
100 IF ( arec.hist_salvage_value <> 0) THEN
101 l_salvage_correction := 1 + (arec.hist_salvage_value / (arec.cost_hist - arec.hist_salvage_value ));
102 ELSE
103 l_salvage_correction := 1;
104 END IF;
105
106 total_depreciation := arec.accum_deprn_hist * (cumm_reval_rate-1) * l_salvage_correction + arec.accum_deprn_hist;
107
108 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
109 p_full_path => g_path||'Validate_Assets',
110 p_string => 'Processing Asset: '||arec.asset_id);
111
112 IF upper(l_depreciate) = 'NO' THEN
113
114 IF(arec.backlog_mhca <> 0 OR arec.accum_deprn_mhca <> 0) THEN
115 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
116 p_full_path => g_path||'Validate_Assets',
117 p_string => 'Error: Non_Depreciating asset provided with Depreciation figures');
118
119 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_NON _DEP');
120 l_errbuf := FND_MESSAGE.GET;
121 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
122
123 l_assets_valid := FALSE;
124 goto Next_Record;
125 END IF;
126 --
127 -- Bug 5393607
128 -- ==========
129 -- Downward revaluation forces following check to fail.
130 /**************************************************
131 ELSE
132
133 IF ( ( (arec.backlog_mhca + arec.accum_deprn_mhca) > (total_depreciation + 0.02) )OR
134 ((arec.backlog_mhca + arec.accum_deprn_mhca) < (total_depreciation - 0.02) )) THEN
135
136 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
137 p_full_path => g_path||'Validate_Assets',
138 p_string => 'Error: Sum of Backlog Accumulated Depreciation and'||
139 'Revalued Accumulated Depreciation not correct.');
140
141 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_DEP_RATIO');
142 l_errbuf := FND_MESSAGE.GET;
143 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
144
145 l_assets_valid := FALSE;
146 goto Next_Record;
147 END IF;
148 ********************************************/
149 END IF;
150
151 SELECT decode(
152 fab.conversion_date,
153 NULL,
154 fab.life_in_months - floor(months_between(
155 fdp.CALENDAR_PERIOD_CLOSE_DATE,
156 fab.prorate_date)),
157 fab.life_in_months - floor(months_between(
158 fdp.CALENDAR_PERIOD_CLOSE_DATE,
159 fab.deprn_start_date)))
160 INTO l_remaining_periods
161 FROM fa_books fab, fa_deprn_periods fdp
162 WHERE fab.book_type_code = arec.book_type_code
163 AND fdp.book_type_code = arec.book_type_code
164 AND fab.asset_id = arec.asset_id
165 AND fab.date_ineffective is null
166 AND fdp.PERIOD_CLOSE_DATE is null;
167
168 IF (l_remaining_periods <= 0) THEN
169 l_remaining_periods := 0;
170 l_elapsed_periods := arec.life_in_months;
171 ELSE
172 l_elapsed_periods := arec.life_in_months - l_remaining_periods;
173 END IF;
174
175 l_deprn_per_period_hist := arec.accum_deprn_hist / l_elapsed_periods;
176
177 SELECT (period_num - 1)
178 INTO l_elapsed_periods_curr_yr
179 FROM fa_deprn_periods
180 WHERE book_type_code = arec.book_type_code
181 AND PERIOD_CLOSE_DATE IS NULL;
182
183 IF (cumm_reval_rate > 1) THEN
184
185 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
186 p_full_path => g_path||'Validate_Assets',
187 p_string => 'Revalued cost > Historic Cost');
188
189 /**************************************************
190 IF (arec.backlog_mhca < 0) THEN
191
192 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
193 p_full_path => g_path||'Validate_Assets',
194 p_string => 'Error: Backlog Accumulated Depreciation has to be greater' ||
195 ' than or equal to zero, when revalued cost is greater than historic cost.');
196
197 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_BACKLOG_NEG');
198 l_errbuf := FND_MESSAGE.GET;
199 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
200
201 l_assets_valid := FALSE;
202 goto Next_Record;
203 END IF;
204 ***************************************************/
205
206 IF (arec.operating_account_cost <> 0) THEN
207
208 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
209 p_full_path => g_path||'Validate_Assets',
210 p_string => 'Error: Operating Account must be 0. ');
211
212 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_OP_COST_NOT_ZERO');
213 l_errbuf := FND_MESSAGE.GET;
214 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
215
216 l_assets_valid := FALSE;
217 goto Next_Record;
218 END IF;
219
220 l_max_backlog := (cumm_reval_rate - 1) * (l_elapsed_periods - l_elapsed_periods_curr_yr) * l_deprn_per_period_hist;
221
222 /*************************************************
223 IF (arec.backlog_mhca > (l_max_backlog + 0.1)) THEN
224
225 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
226 p_full_path => g_path||'Validate_Assets',
227 p_string => 'Error: Backlog value greater than the max value permissable');
228
229 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_MAX_BACKLOG');
230 l_errbuf := FND_MESSAGE.GET;
231 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
232
233 l_assets_valid := FALSE;
234 goto Next_Record;
235 END IF;
236 ***************************************************/
237
238 IF (arec.operating_account_backlog = 0) THEN -- Upliftment
239
240 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
241 p_full_path => g_path||'Validate_Assets',
242 p_string => 'Asset has undergone Upliftment.');
243
244 l_adjusted_cost := arec.cost_mhca - arec.cost_hist;
245
246 l_general_fund := arec.accum_deprn_mhca - arec.accum_deprn_hist;
247
248 IF ( (arec.general_fund_mhca > (l_general_fund + 0.1)) OR
249 (arec.general_fund_mhca < (l_general_fund - 0.1)) ) THEN
250
251 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
252 p_full_path => g_path||'Validate_Assets',
253 p_string => 'Error: Invalid value for General Fund');
254
255 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_GEN_FUND');
256 l_errbuf := FND_MESSAGE.GET;
257 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
258
259 l_assets_valid := FALSE;
260 goto Next_Record;
261 END IF;
262
263 l_reval_reserve := l_adjusted_cost - arec.backlog_mhca - arec.general_fund_mhca;
264
265 IF( (arec.reval_reserve_mhca > (l_reval_reserve + 0.1)) OR
266 (arec.reval_reserve_mhca < (l_reval_reserve - 0.1)) )THEN
267
268 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
269 p_full_path => g_path||'Validate_Assets',
270 p_string => 'Error: Invalid value for Reval Reserve');
271
272 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_REVAL_RES');
273 l_errbuf := FND_MESSAGE.GET;
274 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
275
276 l_assets_valid := FALSE;
277 goto Next_Record;
278 END IF;
279 ELSE -- Mixed Revaluation, Cumm Reval Rate > 1
280
281 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
282 p_full_path => g_path||'Validate_Assets',
283 p_string => 'Asset has undergone Mixed Revaluation');
284
285 /***********************************************************
286 IF (arec.operating_account_backlog < 0) THEN
287
288 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
289 p_full_path => g_path||'Validate_Assets',
290 p_string => 'Error: Invalid value for Operating Account Backlog');
291
292 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_OP_BACKLOG_POS');
293 l_errbuf := FND_MESSAGE.GET;
294 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
295
296 l_assets_valid := FALSE;
297 goto Next_Record;
298
299 END IF;
300 ***********************************************************/
301
302 l_general_fund := arec.accum_deprn_mhca - arec.accum_deprn_hist +
303 arec.operating_account_backlog;
304
305 IF ( (arec.general_fund_mhca > (l_general_fund + 0.1))
306 OR (arec.general_fund_mhca < (l_general_fund - 0.1)) ) THEN
307
308 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
309 p_full_path => g_path||'Validate_Assets',
310 p_string => 'Error: Invalid value for General Fund');
311
312 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_GEN_FUND');
313 l_errbuf := FND_MESSAGE.GET;
314 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
315
316 l_assets_valid := FALSE;
317 goto Next_Record;
318 END IF;
319
320 l_reval_reserve := l_adjusted_cost - arec.backlog_mhca -
321 arec.general_fund_mhca + arec.operating_account_backlog;
322
323 IF( (arec.reval_reserve_mhca > (l_reval_reserve + 0.1))
324 OR (arec.reval_reserve_mhca < (l_reval_reserve - 0.1)) )THEN
325
326 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
327 p_full_path => g_path||'Validate_Assets',
328 p_string => 'Error: Invalid value for Reval Reserve');
329
330 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_REVAL_RES');
331 l_errbuf := FND_MESSAGE.GET;
332 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
333
334 l_assets_valid := FALSE;
335 goto Next_Record;
336 END IF;
337 END IF;
338
339 l_deprn_exp_mhca := arec.deprn_exp_hist * cumm_reval_rate;
340
341 -- Commented the following YTD validation logic as part of fix for Bug 5372707
342
343 /********************************
344 l_ytd_mhca := arec.ytd_hist + ( (l_deprn_exp_mhca - arec.deprn_exp_hist) *
345 l_elapsed_periods_curr_yr);
346
347 IF ( (arec.ytd_mhca > (l_ytd_mhca + 0.1)) OR
348 (arec.ytd_mhca < (l_ytd_mhca - 0.1)) ) THEN
349
350 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
351 p_full_path => g_path||'Validate_Assets',
352 p_string => 'Error: Invalid value for Revalued YTD Depreciation');
353
354 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_YTD');
355 l_errbuf := FND_MESSAGE.GET;
356 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
357
358 l_assets_valid := FALSE;
359 goto Next_Record;
360 END IF;
361 ********************************/
362
363 -- End Bug 5372707
364
365 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
366 p_full_path => g_path||'Validate_Assets',
367 p_string => 'Asset has valid values. Setting Valid_Flag to Y');
368
369 UPDATE igi_imp_iac_interface
370 SET deprn_exp_mhca = l_deprn_exp_mhca,
371 general_fund_per_mhca = arec.deprn_exp_mhca - arec.deprn_exp_hist,
372 operating_account_mhca = (arec.operating_account_cost - arec.operating_account_backlog),
373 nbv_mhca = arec.cost_mhca - arec.accum_deprn_mhca - arec.backlog_mhca,
374 valid_flag ='Y'
375 WHERE book_type_code = arec.book_type_code
376 AND asset_id = arec.asset_id;
377
378 ELSIF (cumm_reval_rate < 1) THEN
379
380 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
381 p_full_path => g_path||'Validate_Assets',
382 p_string => 'Revalued cost < Historic Cost');
383
384 /***************************************
385 IF (arec.backlog_mhca > 0) THEN
386
387 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
388 p_full_path => g_path||'Validate_Assets',
389 p_string => 'Error: Backlog Accumulated Depreciation has to be less' ||
390 ' than or equal to zero, when revalued cost is lesser than historic cost.');
391
392 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_BACKLOG_POS');
393 l_errbuf := FND_MESSAGE.GET;
394 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
395
396 l_assets_valid := FALSE;
397 goto Next_Record;
398 END IF;
399 ***************************************/
400
401 IF (arec.reval_reserve_mhca <> 0) THEN
402
403 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
404 p_full_path => g_path||'Validate_Assets',
405 p_string => 'Error: Invalid value for Reval Reserve');
406
407 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_REVAL_RES_NOT_ZERO');
408 l_errbuf := FND_MESSAGE.GET;
409 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
410
411 l_assets_valid := FALSE;
412 goto Next_Record;
413 END IF;
414
415 l_min_backlog := (cumm_reval_rate - 1) * (l_elapsed_periods -
416 l_elapsed_periods_curr_yr) * l_deprn_per_period_hist;
417
418 /*****************************************
419 IF (arec.backlog_mhca < (l_min_backlog - 0.1)) THEN
420
421 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
422 p_full_path => g_path||'Validate_Assets',
423 p_string => 'Error: Backlog value less than permissable value');
424
425 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_MIN_BACKLOG');
426 l_errbuf := FND_MESSAGE.GET;
427 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
428
429 l_assets_valid := FALSE;
430 goto Next_Record;
431 END IF;
432 *******************************************/
433
434 IF ( arec.general_fund_mhca <> 0) THEN --Mixed Revaluation, Cumm Reval Rate < 1
435
436 IF (arec.general_fund_mhca < 0) THEN
437
438 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
439 p_full_path => g_path||'Validate_Assets',
440 p_string => 'Error: Invalid value for General Fund');
441
442 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_GEN_FUND_NEG');
443 l_errbuf := FND_MESSAGE.GET;
444 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
445
446 l_assets_valid := FALSE;
447 goto Next_Record;
448
449 END IF;
450
451 l_operating_account_backlog := arec.backlog_mhca + arec.general_fund_mhca;
452
453 IF ( (arec.operating_account_backlog > (l_operating_account_backlog + 0.1)) OR
454 (arec.operating_account_backlog < (l_operating_account_backlog - 0.1)) ) THEN
455
456 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
457 p_full_path => g_path||'Validate_Assets',
458 p_string => 'Error: Invalid value for Operating Account Backlog');
459
460 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_OP_BACKLOG');
461 l_errbuf := FND_MESSAGE.GET;
462 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
463
464 l_assets_valid := FALSE;
465 goto Next_Record;
466 END IF;
467 ELSE -- Asset Impairment
468 l_operating_account_backlog := arec.backlog_mhca;
469
470 IF ( (arec.operating_account_backlog > (l_operating_account_backlog + 0.1)) OR
471 (arec.operating_account_backlog < (l_operating_account_backlog - 0.1)) ) THEN
472
473 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
474 p_full_path => g_path||'Validate_Assets',
475 p_string => 'Error: Invalid value for Operating Account Backlog');
476
477 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_OP_BACKLOG');
478 l_errbuf := FND_MESSAGE.GET;
479 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
480
481 l_assets_valid := FALSE;
482 goto Next_Record;
483 END IF;
484 END IF;
485
486 l_operating_account_cost := arec.cost_mhca - arec.cost_hist;
487
488 IF ( (arec.operating_account_cost > (l_operating_account_cost + 0.1)) OR
489 (arec.operating_account_cost < (l_operating_account_cost - 0.1)) ) THEN
490
491 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
492 p_full_path => g_path||'Validate_Assets',
493 p_string => 'Error: Invalid value for Operating Account Cost');
494
495 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_OP_COST');
496 l_errbuf := FND_MESSAGE.GET;
497 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
498
499 l_assets_valid := FALSE;
500 goto Next_Record;
501 END IF;
502
503 l_deprn_exp_mhca := arec.deprn_exp_hist * cumm_reval_rate;
504
505 -- Commented the following YTD validation logic as part of fix for Bug 5372707
506 /***********************************
507 l_ytd_mhca := arec.ytd_hist + ( (l_deprn_exp_mhca - arec.deprn_exp_hist) *
508 l_elapsed_periods_curr_yr);
509
510 IF ( (arec.ytd_mhca > (l_ytd_mhca + 0.1)) OR
511 (arec.ytd_mhca < (l_ytd_mhca - 0.1)) ) THEN
512
513 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
514 p_full_path => g_path||'Validate_Assets',
515 p_string => 'Error: Invalid value for Revalued YTD Depreciation');
516
517 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_INV_YTD');
518 l_errbuf := FND_MESSAGE.GET;
519 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
520
521 l_assets_valid := FALSE;
522 goto Next_Record;
523 END IF;
524 ************************************/
525
526 -- End Bug 5372707
527
528 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
529 p_full_path => g_path||'Validate_Assets',
530 p_string => 'Asset has valid values. Setting Valid_Flag to Y');
531
532 UPDATE igi_imp_iac_interface
533 SET deprn_exp_mhca = l_deprn_exp_mhca,
534 general_fund_per_mhca = arec.deprn_exp_mhca - arec.deprn_exp_hist,
535 operating_account_mhca = (arec.operating_account_cost - arec.operating_account_backlog),
536 nbv_mhca = arec.cost_mhca - arec.accum_deprn_mhca - arec.backlog_mhca,
537 valid_flag = 'Y'
538 WHERE book_type_code = arec.book_type_code
539 AND asset_id = arec.asset_id;
540
541 ELSE -- cumm_reval_rate = 1
542 IF (arec.reval_reserve_mhca <> 0) THEN
543
544 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
545 p_full_path => g_path||'Validate_Assets',
546 p_string => 'Error: Invalid value for Reval Reserve');
547
548 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_NET_RES_NOT_ZERO');
549 l_errbuf := FND_MESSAGE.GET;
550 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
551
552 l_assets_valid := FALSE;
553 goto Next_Record;
554 END IF;
555
556 /***************************************
557 IF (arec.backlog_mhca <> 0) THEN
558
559 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
560 p_full_path => g_path||'Validate_Assets',
561 p_string => 'Error: Invalid value for Reval Backlog Depreciation');
562
563 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_ACC_BLOG_NOT_ZERO');
564 l_errbuf := FND_MESSAGE.GET;
565 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
566
567 l_assets_valid := FALSE;
568 goto Next_Record;
569 END IF;
570 ***************************************/
571
572 IF (arec.ytd_mhca <> arec.ytd_hist) THEN
573
574 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
575 p_full_path => g_path||'Validate_Assets',
576 p_string => 'Error: Invalid value for Revalued YTD Depreciation');
577
578 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_YTD_NOT_EQUAL');
579 l_errbuf := FND_MESSAGE.GET;
580 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
581
582 l_assets_valid := FALSE;
583 goto Next_Record;
584 END IF;
585 /**********************
586 IF (arec.accum_deprn_mhca <> arec.accum_deprn_hist) THEN
587
588 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
589 p_full_path => g_path||'Validate_Assets',
590 p_string => 'Error: Invalid value for Revalued Accumulated Depreciation');
591
592 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_ACC_DEP_NOT_EQUAL');
593 l_errbuf := FND_MESSAGE.GET;
594 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
595
596 l_assets_valid := FALSE;
597 goto Next_Record;
598 END IF;
599 ***********************/
600 IF (arec.operating_account_cost <> 0) THEN
601
602 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
603 p_full_path => g_path||'Validate_Assets',
604 p_string => 'Error: Invalid value for Operating Account Cost');
605
606 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_OP_CST_NOT_ZERO');
607 l_errbuf := FND_MESSAGE.GET;
608 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
609
610 l_assets_valid := FALSE;
611 goto Next_Record;
612 END IF;
613 /**************************
614 IF (arec.operating_account_backlog <> 0) THEN
615
616 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
617 p_full_path => g_path||'Validate_Assets',
618 p_string => 'Error: Invalid value for Operating Account Backlog');
619
620 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_OP_BLOG_NOT_ZERO');
621 l_errbuf := FND_MESSAGE.GET;
622 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
623
624 l_assets_valid := FALSE;
625 goto Next_Record;
626 END IF;
627 IF (arec.general_fund_mhca <> 0) THEN
628
629 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
630 p_full_path => g_path||'Validate_Assets',
631 p_string => 'Error: Invalid value for General Fund');
632
633 FND_MESSAGE.SET_NAME('IGI', 'IGI_IMP_IAC_GEN_FUND_NOT_ZERO');
634 l_errbuf := FND_MESSAGE.GET;
635 fnd_file.put_line(fnd_file.log, arec.asset_id||': '||l_errbuf);
636
637 l_assets_valid := FALSE;
638 goto Next_Record;
639 END IF;
640 **************************/
641
642 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
643 p_full_path => g_path||'Validate_Assets',
644 p_string => 'Asset has valid values. Setting Valid_Flag to Y');
645
646 UPDATE igi_imp_iac_interface
647 SET deprn_exp_mhca = arec.deprn_exp_hist,
648 operating_account_mhca = (arec.operating_account_cost - arec.operating_account_backlog),
649 nbv_mhca = arec.nbv_hist,
650 valid_flag = 'Y'
651 WHERE book_type_code = arec.book_type_code
652 AND asset_id = arec.asset_id;
653 END IF;
654 <<NEXT_RECORD>>
655 null;
656 END LOOP;
657
658 COMMIT WORK;
659
660 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
661 p_full_path => g_path||'Validate_Assets',
662 p_string => 'End of Asset Validation');
663
664 RETURN l_assets_valid;
665
666 EXCEPTION
667 WHEN OTHERS THEN
668 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
669 p_full_path => g_path||'Validate_Assets',
670 p_string => 'Error while Validating assets..');
671
672 RETURN FALSE;
673 END Validate_Assets;
674
675 FUNCTION Trxns_In_Open_Period(p_book_type_code in VARCHAR2)
676 RETURN BOOLEAN AS
677
678 CURSOR C_Fa_Period_Counter
679 IS
680 SELECT max(period_counter)
681 from fa_deprn_periods
682 where book_type_code=p_book_type_code;
683
684 CURSOR C_Imp_Period_Counter
685 IS
686 SELECT period_counter
687 FROM igi_imp_iac_controls
688 where book_type_code=p_book_type_code;
689
690 CURSOR C_Trxn
691 IS
692 SELECT count(*)
693 FROM fa_transaction_headers ft ,
694 fa_deprn_periods dp
695 WHERE ft.book_type_Code = P_book_type_code
696 AND dp.book_type_Code = P_book_type_code
697 AND dp.period_close_Date IS NULL
698 AND ft.date_effective >= dp.period_open_date ;
699
700
701 --variables
702
703 l_fa_period_counter NUMBER;
704 l_imp_period_counter NUMBER;
705 l_count NUMBER;
706
707 BEGIN
708
709 OPEN C_Fa_Period_Counter;
710 FETCH C_Fa_Period_Counter INTO l_fa_period_counter;
711 CLOSE C_Fa_Period_Counter;
712
713 OPEN C_Imp_Period_Counter;
714 FETCH C_Imp_Period_Counter INTO l_imp_period_counter;
715 CLOSE C_Imp_Period_Counter;
716
717 IF (l_imp_period_counter<>l_fa_period_counter)
718 THEN
719 RETURN FALSE;
720 ELSE
721 --Check for trxns in open period
722 OPEN c_trxn;
723 FETCH c_trxn INTO l_count;
724 CLOSE c_trxn;
725
726 IF l_count>0 THEN
727 RETURN FALSE;
728 ELSE
729 RETURN TRUE;
730 END IF;
731 END IF;
732
733 EXCEPTION
734 WHEN OTHERS THEN
735 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => g_path||'trxns_in_open_period');
736 RETURN FALSE ;
737 END Trxns_In_Open_Period;
738
739 FUNCTION Prorate_for_Det_Balances (
740 p_book_type_code VARCHAR2 ,
741 p_category_id NUMBER ,
742 p_asset_id NUMBER ,
743 p_net_book_value NUMBER ,
744 p_adjusted_cost NUMBER ,
745 p_operating_acct NUMBER ,
746 p_reval_reserve NUMBER ,
747 p_deprn_amount NUMBER ,
748 p_ytd_deprn NUMBER ,
749 p_deprn_reserve NUMBER ,
750 p_backlog_deprn_reserve NUMBER ,
751 p_general_fund NUMBER ,
752 p_prd_rec igi_iac_types.prd_rec ,
753 p_current_reval_factor NUMBER ,
754 p_cumulative_reval_factor NUMBER ,
755 p_adj_id NUMBER ,
756 p_run_book VARCHAR2 ,
757 p_op_ac_cost NUMBER ,
758 p_op_ac_backlog NUMBER ,
759 p_hist_deprn_amount NUMBER ,
760 p_hist_deprn_ytd NUMBER ,
761 p_hist_deprn_reserve NUMBER ,
762 p_errbuf OUT NOCOPY VARCHAR2 )
763 RETURN BOOLEAN AS
764
765 --
766 -- Variables to store values remaining after allocating to det balances
767 --
768
769 CURSOR C_fiscal_year(p_period_counter in NUMBER)
770 IS
771 SELECT fiscal_year
772 FROM fa_deprn_periods
773 WHERE book_type_code = p_book_type_code AND
774 period_counter = p_period_counter-1;
775
776 CURSOR C_all_dist( p_fiscal_year in NUMBER)
777 IS
778 SELECT
779 dh.units_assigned,
780 dp.calendar_period_open_date,
781 dp.period_counter,
782 dh.distribution_id,
783 dh.code_combination_id,
784 dh.date_ineffective
785 FROM
786 fa_distribution_history dh,
787 fa_deprn_periods dp
788 WHERE
789 dh.asset_id= p_asset_id AND
790 dh.book_type_code=p_book_type_code AND
791 (nvl(dh.date_ineffective,dp.period_open_date)>=dp.period_open_date) AND
792 dp.Book_type_code=p_book_type_code AND
793 dp.fiscal_year=p_fiscal_year AND
794 dp.period_num=(SELECT min(period_num)
795 FROM fa_deprn_periods
796 WHERE fiscal_year=p_fiscal_year and
797 book_type_code=p_book_type_code);
798
799 CURSOR C_period_counter
800 IS
801 SELECT period_counter
802 FROM igi_imp_iac_controls
803 WHERE Book_Type_Code = p_run_book;
804
805 CURSOR C_counter(p_distribution_id in number
806 ,p_fiscal_year in NUMBER)
807 IS
808 SELECT dp.period_counter
809 FROM fa_deprn_periods dp,
810 fa_distribution_history dh
811 WHERE (dh.date_ineffective between dp.period_open_date and dp.period_close_date) AND
812 dp.book_type_code=p_book_type_code AND
813 dp.fiscal_year=p_fiscal_year AND
814 dh.book_type_code=p_book_type_code AND
815 dh.distribution_id=p_distribution_id;
816
817 CURSOR C_ytd_dist(p_distribution_id in NUMBER)
818 IS
819 SELECT ytd_deprn
820 FROM fa_deprn_detail
821 WHERE
822 distribution_id=p_distribution_id AND
823 book_type_code =p_book_type_code AND
824 asset_id =p_asset_id AND
825 -- Bug 3575041 start (1) --
826 period_counter=(select max(period_counter)
827 from fa_deprn_detail
828 where distribution_id = p_distribution_id
829 and book_type_code = p_book_type_code
830 and asset_id = p_asset_id ) ;
831
832 -- Bug 3575041 start (1) --
833
834 CURSOR C_ytd_asset(p_max_period_counter in number)
835 IS
836 SELECT ytd_deprn
837 FROM fa_deprn_summary
838 WHERE asset_id=p_asset_id AND
839 book_type_code=p_book_type_code AND
840 period_counter=p_max_period_counter-1;
841
842 CURSOR C_units
843 IS
844 SELECT current_units
845 FROM fa_additions
846 WHERE asset_id=p_asset_id;
847
848 CURSOR C_active_dists
849 IS
850 SELECT count(*)
851 FROM fa_distribution_history
852 WHERE book_type_code=p_book_type_code AND
853 asset_id=p_asset_id AND
854 date_ineffective IS NULL;
855
856 l_net_book_value NUMBER ;
857 l_adjusted_cost NUMBER ;
858 l_operating_acct NUMBER ;
859 l_reval_reserve NUMBER ;
860 l_deprn_amount NUMBER ;
861 l_ytd_deprn NUMBER ;
862 l_deprn_reserve NUMBER ;
863 l_backlog_deprn_reserve NUMBER ;
864 l_general_fund NUMBER ;
865 l_op_ac_cost NUMBER ;
866 l_op_ac_backlog NUMBER ;
867 l_hist_deprn_amount NUMBER ;
868 l_hist_deprn_ytd NUMBER ;
869 l_hist_deprn_reserve NUMBER ;
870 l_period_counter NUMBER ;
871 --
872 -- Initialize the remaining values
873 --
874 l_rem_net_book_value NUMBER;
875 l_rem_adjusted_cost NUMBER;
876 l_rem_operating_acct NUMBER;
877 l_rem_reval_reserve NUMBER;
878 l_rem_deprn_amount NUMBER;
879 l_rem_deprn_reserve NUMBER;
880 l_rem_backlog_deprn_reserve NUMBER;
881 l_rem_general_fund NUMBER;
882 l_rem_op_ac_cost NUMBER;
883 l_rem_op_ac_backlog NUMBER;
884 l_rem_hist_deprn_amount NUMBER;
885 l_rem_hist_deprn_reserve NUMBER;
886
887 --
888 -- Variables to store values to go into det balances
889 --
890 l_det_adjustment_cost NUMBER;
891 l_det_net_book_value NUMBER;
892 l_det_reval_reserve_cost NUMBER;
893 l_det_reval_reserve_backlog NUMBER;
894 l_det_reval_reserve_gen_fund NUMBER;
895 l_det_reval_reserve_net NUMBER;
896 l_det_operating_acct_cost NUMBER;
897 l_det_operating_acct_backlog NUMBER;
898 l_det_operating_acct_net NUMBER;
899 l_det_operating_acct_ytd NUMBER;
900 l_det_deprn_period NUMBER;
901 l_det_deprn_reserve NUMBER;
902 l_det_deprn_reserve_backlog NUMBER;
903 l_det_general_fund_per NUMBER;
904 l_det_general_fund_acc NUMBER;
905 l_det_last_reval_date DATE ;
906 l_det_current_reval_factor NUMBER;
907 l_det_cumulative_reval_factor NUMBER;
908 l_det_op_ac_cost NUMBER;
909 l_det_op_ac_backlog NUMBER;
910 l_det_hist_deprn_amount NUMBER;
911 l_det_hist_deprn_reserve NUMBER;
912 l_det_deprn_ytd NUMBER;
913 l_det_hist_deprn_ytd NUMBER;
914 --
915 -- Miscellaneous Variables
916 --
917 l_dists_processed NUMBER;
918 l_total_dists NUMBER;
919 l_dists_tab igi_iac_types.dist_amt_tab ;
920 l_prorate_factor NUMBER;
921 l_ytd_prorate_factor NUMBER;
922 l_out_rowid VARCHAR2(240) ;
923 l_fiscal_year NUMBER(4);
924 l_max_period_counter NUMBER;
925 l_dist_ytd_deprn NUMBER;
926 l_asset_ytd_deprn NUMBER;
927 l_total_units NUMBER;
928 l_count NUMBER;
929 l_inactive_counter NUMBER;
930 l_flag VARCHAR2(1);
931 l_all_dist c_all_dist%rowtype;
932 l_index NUMBER;
933
934 l_YTD_prorate_dists_tab igi_iac_types.prorate_dists_tab;
935 l_YTD_prorate_dists_idx binary_integer;
936 idx_YTD binary_integer;
937
938 -- Bug 3575041 start(2) --
939
940 CURSOR C_Get_Deprn_Flag is
941 SELECT depreciate_flag
942 FROM FA_BOOKS
943 WHERE book_type_code = p_book_type_code
944 AND asset_id = p_asset_id
945 AND transaction_header_id_out is NULL;
946
947 l_depreciate_flag FA_BOOKS.depreciate_flag%TYPE;
948
949 -- Bug 3575041 end(2) --
950
951 BEGIN
952
953 l_net_book_value := 0 ;
954 l_adjusted_cost := 0 ;
955 l_operating_acct := 0 ;
956 l_reval_reserve := 0 ;
957 l_deprn_amount := 0 ;
958 l_ytd_deprn := 0 ;
959 l_deprn_reserve := 0 ;
960 l_backlog_deprn_reserve := 0 ;
961 l_general_fund := 0 ;
962 l_op_ac_cost := 0 ;
963 l_op_ac_backlog := 0 ;
964 l_hist_deprn_amount := 0 ;
965 l_hist_deprn_ytd := 0 ;
966 l_hist_deprn_reserve := 0 ;
967 l_period_counter := p_prd_rec.period_counter;
968 --
969 -- Initialize the remaining values
970 --
971 l_rem_net_book_value := p_net_book_value ;
972 l_rem_adjusted_cost := p_adjusted_cost ;
973 l_rem_operating_acct := p_operating_acct ;
974 l_rem_reval_reserve := p_reval_reserve ;
975 l_rem_deprn_amount := p_deprn_amount ;
976 l_rem_deprn_reserve := p_deprn_reserve ;
977 l_rem_backlog_deprn_reserve := p_backlog_deprn_reserve ;
978 l_rem_general_fund := p_general_fund ;
979 l_rem_op_ac_cost := p_op_ac_cost ;
980 l_rem_op_ac_backlog := p_op_ac_backlog ;
981 l_rem_hist_deprn_amount := p_hist_deprn_amount ;
982 l_rem_hist_deprn_reserve := p_hist_deprn_reserve ;
983
984 --
985 -- Variables to store values to go into det balances
986 --
987 l_det_adjustment_cost := 0 ;
988 l_det_net_book_value := 0 ;
989 l_det_reval_reserve_cost := 0 ;
990 l_det_reval_reserve_backlog := 0 ;
991 l_det_reval_reserve_gen_fund := 0 ;
992 l_det_reval_reserve_net := 0 ;
993 l_det_operating_acct_cost := 0 ;
994 l_det_operating_acct_backlog := 0 ;
995 l_det_operating_acct_net := 0 ;
996 l_det_operating_acct_ytd := 0 ;
997 l_det_deprn_period := 0 ;
998 l_det_deprn_reserve := 0 ;
999 l_det_deprn_reserve_backlog := 0 ;
1000 l_det_general_fund_per := 0 ;
1001 l_det_general_fund_acc := 0 ;
1002
1003 l_det_current_reval_factor := 0 ;
1004 l_det_cumulative_reval_factor := 0 ;
1005 l_det_op_ac_cost := 0 ;
1006 l_det_op_ac_backlog := 0 ;
1007 l_det_hist_deprn_amount := 0 ;
1008 l_det_hist_deprn_reserve := 0 ;
1009 l_det_deprn_ytd := 0 ;
1010 l_det_hist_deprn_ytd := 0 ;
1011 --
1012 -- Miscellaneous Variables
1013 --
1014 l_dists_processed := 0 ;
1015 l_total_dists := 0 ;
1016 l_prorate_factor := 0 ;
1017 l_ytd_prorate_factor :=0;
1018
1019 l_index :=1;
1020
1021
1022
1023 l_depreciate_flag := NULL;
1024
1025
1026 OPEN C_PERIOD_COUNTER;
1027 FETCH C_PERIOD_COUNTER into l_max_period_counter;
1028 close c_period_counter;
1029
1030 OPEN C_fiscal_year(l_max_period_counter);
1031 FETCH C_fiscal_year into l_fiscal_year;
1032 CLOSE C_fiscal_year;
1033
1034 OPEN C_units;
1035 FETCH C_units INTO l_total_units;
1036 CLOSE C_units;
1037
1038 OPEN C_active_dists;
1039 FETCH C_active_dists INTO l_total_dists;
1040 CLOSE C_active_dists;
1041
1042 OPEN C_ytd_asset (l_max_period_counter);
1043 FETCH C_ytd_asset INTO l_asset_ytd_deprn;
1044 CLOSE C_ytd_asset;
1045
1046 l_det_last_reval_date := p_prd_rec.period_end_date;
1047 l_det_current_reval_factor := p_current_reval_factor ;
1048 l_det_cumulative_reval_factor := p_cumulative_reval_factor ;
1049
1050 -- Bug 3575041 start (3) --
1051
1052 OPEN C_Get_Deprn_Flag;
1053 FETCH C_Get_Deprn_Flag INTO l_depreciate_flag;
1054 CLOSE C_Get_Deprn_Flag;
1055
1056 -- Bug 3575041 end (3) --
1057
1058
1059 IF NOT IGI_IAC_REVAL_UTILITIES.prorate_all_dists_YTD ( fp_asset_id => p_asset_id
1060 , fp_book_type_code => p_book_type_code
1061 , fp_current_period_counter => l_max_period_counter - 1
1062 , fp_prorate_dists_tab => l_YTD_prorate_dists_tab
1063 , fp_prorate_dists_idx => l_YTD_prorate_dists_idx
1064 )
1065 THEN
1066 igi_iac_debug_pkg.debug_other_string(g_error_level,g_path,'+error IGI_IAC_REVAL_UTILITIES.prorate_all_dists_YTD');
1067 return false;
1068 END IF;
1069
1070 FOR l_all_dist IN C_all_dist (l_fiscal_year )
1071 LOOP
1072 --
1073 -- Initialize the values
1074 --
1075 l_net_book_value := p_net_book_value ;
1076 l_adjusted_cost := p_adjusted_cost ;
1077 l_operating_acct := p_operating_acct ;
1078 l_reval_reserve := p_reval_reserve ;
1079 l_deprn_amount := p_deprn_amount ;
1080 l_deprn_reserve := p_deprn_reserve ;
1081 l_backlog_deprn_reserve := p_backlog_deprn_reserve ;
1082 l_general_fund := p_general_fund ;
1083 l_op_ac_cost := p_op_ac_cost ;
1084 l_op_ac_backlog := p_op_ac_backlog ;
1085 l_hist_deprn_amount := p_hist_deprn_amount ;
1086 l_hist_deprn_reserve := p_hist_deprn_reserve ;
1087 l_ytd_deprn := p_ytd_deprn ;
1088 l_hist_deprn_ytd := p_hist_deprn_ytd ;
1089
1090 --
1091 -- Get the prorate factors
1092 --
1093 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1094 p_full_path => g_path||'prorate_for_det_balances',
1095 p_string => 'Getting Prorate Factor for Active Distributions...');
1096
1097 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1098 p_full_path => g_path||'prorate_for_det_balances',
1099 p_string => 'Start of prorate to detail');
1100
1101 GLOBAL_CURRENT_PROC := 'PRORATE_FOR_DET_BALANCES > ' ;
1102
1103 l_ytd_prorate_factor := 0;
1104 idx_YTD := l_YTD_prorate_dists_tab.FIRST;
1105 WHILE idx_YTD <= l_YTD_prorate_dists_tab.LAST LOOP
1106 IF l_all_dist.distribution_id = l_YTD_prorate_dists_tab(idx_YTD).distribution_id THEN
1107 l_ytd_prorate_factor := l_YTD_prorate_dists_tab(idx_YTD).ytd_prorate_factor;
1108 EXIT;
1109 END IF;
1110 idx_ytd := l_YTD_prorate_dists_tab.Next(idx_ytd);
1111 END LOOP;
1112
1113 IF (l_all_dist.date_ineffective IS NULL) --Active Distribution
1114 THEN
1115
1116 l_flag:=NULL;
1117
1118 --
1119 -- Process each active distribution and create det balances
1120 --
1121 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1122 p_full_path => g_path||'prorate_for_det_balances',
1123 p_string => 'Processing each active distribution ...');
1124
1125 --Calculating the proration factors
1126
1127 IF (nvl(l_total_units,0)=0)
1128 THEN
1129 l_prorate_factor:= 0;
1130 ELSE
1131 l_prorate_factor:= (l_all_dist.units_assigned/l_total_units);
1132 END IF;
1133
1134 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1135 p_full_path => g_path||'prorate_for_det_balances',
1136 p_string => 'Processing distribution ---------> '|| l_all_dist.distribution_id);
1137
1138 IF l_index <> l_total_dists THEN
1139 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1140 p_full_path => g_path||'prorate_for_det_balances',
1141 p_string => 'This is not the last active distribution...');
1142 --
1143 -- Not the last distribution
1144 --
1145 l_det_adjustment_cost := p_adjusted_cost * l_prorate_factor ;
1146 l_det_net_book_value := p_net_book_value * l_prorate_factor ;
1147 l_det_operating_acct_cost := p_op_ac_cost * l_prorate_factor ;
1148 l_det_operating_acct_backlog := p_op_ac_backlog * l_prorate_factor ;
1149 l_det_operating_acct_net := l_det_operating_acct_cost - l_det_operating_acct_backlog ;
1150 l_det_operating_acct_ytd := 0 ; --?? ;
1151 l_det_deprn_period := p_deprn_amount * l_prorate_factor ;
1152 l_det_deprn_reserve := p_deprn_reserve * l_prorate_factor ;
1153 l_det_deprn_reserve_backlog := p_backlog_deprn_reserve * l_prorate_factor ;
1154 l_det_reval_reserve_backlog := nvl(l_det_deprn_reserve_backlog,0) -
1155 nvl(l_det_operating_acct_backlog,0) ;
1156 l_det_reval_reserve_gen_fund := p_general_fund * l_prorate_factor ;
1157 l_det_reval_reserve_net := p_reval_reserve * l_prorate_factor ;
1158 l_det_reval_reserve_cost := l_det_reval_reserve_net + l_det_reval_reserve_gen_fund +
1159 l_det_reval_reserve_backlog ;
1160 l_det_general_fund_acc := p_general_fund * l_prorate_factor ;
1161
1162 IF ( l_det_general_fund_acc <> 0 )
1163 THEN
1164 l_det_general_fund_per := p_deprn_amount * l_prorate_factor ;
1165 ELSE
1166 l_det_general_fund_per := 0 ;
1167 END IF;
1168
1169
1170 l_det_hist_deprn_amount := p_hist_deprn_amount * l_prorate_factor ;
1171 l_det_hist_deprn_reserve := p_hist_deprn_reserve * l_prorate_factor ;
1172
1173
1174 ELSE
1175 --
1176 -- Value allocation for the last distribution
1177 --
1178 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1179 p_full_path => g_path||'prorate_for_det_balances',
1180 p_string => 'This is the last active distribution...');
1181
1182 l_det_adjustment_cost := l_rem_adjusted_cost ;
1183 l_det_net_book_value := l_rem_net_book_value ;
1184 l_det_operating_acct_cost := l_rem_op_ac_cost ;
1185 l_det_operating_acct_backlog := l_rem_op_ac_backlog ;
1186 l_det_operating_acct_net := l_det_operating_acct_cost - l_det_operating_acct_backlog ;
1187 l_det_operating_acct_ytd := 0 ; --?? ;
1188 l_det_deprn_period := l_rem_deprn_amount ;
1189 l_det_deprn_reserve := l_rem_deprn_reserve ;
1190 l_det_deprn_reserve_backlog := l_rem_backlog_deprn_reserve ;
1191 l_det_reval_reserve_backlog := nvl(l_det_deprn_reserve_backlog,0) -
1192 nvl(l_det_operating_acct_backlog,0) ;
1193 l_det_reval_reserve_gen_fund := l_rem_general_fund ;
1194 l_det_reval_reserve_net := l_rem_reval_reserve ;
1195 l_det_reval_reserve_cost := l_det_reval_reserve_net + l_det_reval_reserve_gen_fund +
1196 l_det_reval_reserve_backlog ;
1197 l_det_general_fund_acc := l_rem_general_fund ;
1198
1199 IF ( l_det_general_fund_acc <> 0 )
1200 THEN
1201 l_det_general_fund_per := l_rem_deprn_amount ;
1202 ELSE
1203 l_det_general_fund_per := 0 ;
1204 END IF;
1205
1206 l_det_last_reval_date := p_prd_rec.period_end_date ;
1207 l_det_current_reval_factor := p_current_reval_factor ;
1208 l_det_cumulative_reval_factor := p_cumulative_reval_factor ;
1209 l_det_hist_deprn_amount := l_rem_hist_deprn_amount ;
1210 l_det_hist_deprn_reserve := l_rem_hist_deprn_reserve ;
1211
1212 END IF;
1213
1214 l_det_deprn_ytd := p_ytd_deprn * l_ytd_prorate_factor ;
1215
1216 --Bug 3575041 start (4) --
1217
1218 IF l_depreciate_flag ='YES' THEN
1219 l_det_hist_deprn_ytd := p_hist_deprn_ytd * l_ytd_prorate_factor ;
1220 ELSE
1221 OPEN C_ytd_dist(l_all_dist.distribution_id);
1222 FETCH C_ytd_dist INTO l_det_hist_deprn_ytd;
1223 CLOSE C_ytd_dist;
1224 END IF;
1225
1226 --Bug 3575041 end (4) --
1227
1228
1229
1230 l_index:=l_index+1;
1231
1232 l_rem_net_book_value := l_rem_net_book_value - l_det_net_book_value ;
1233 l_rem_adjusted_cost := l_rem_adjusted_cost - l_det_adjustment_cost ;
1234 l_rem_operating_acct := l_rem_operating_acct - l_det_operating_acct_net ;
1235 l_rem_reval_reserve := l_rem_reval_reserve - l_det_reval_reserve_net ;
1236 l_rem_deprn_amount := l_rem_deprn_amount - l_det_deprn_period ;
1237 l_rem_deprn_reserve := l_rem_deprn_reserve - l_det_deprn_reserve ;
1238 l_rem_backlog_deprn_reserve := l_rem_backlog_deprn_reserve - l_det_deprn_reserve_backlog ;
1239 l_rem_general_fund := l_rem_general_fund - l_det_general_fund_acc ;
1240 l_rem_op_ac_cost := l_rem_op_ac_cost - l_det_op_ac_cost ;
1241 l_rem_op_ac_backlog := l_rem_op_ac_backlog - l_det_op_ac_backlog ;
1242 l_rem_hist_deprn_amount := l_rem_hist_deprn_amount - l_det_hist_deprn_amount ;
1243 l_rem_hist_deprn_reserve := l_rem_hist_deprn_reserve - l_det_hist_deprn_reserve ;
1244
1245 ELSE --Inactive Distribution
1246
1247 l_flag:='N';
1248
1249 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1250 p_full_path => g_path||'prorate_for_det_balances',
1251 p_string => 'Processing inactive distribution ---------> '|| l_all_dist.distribution_id);
1252
1253 l_det_adjustment_cost := 0;
1254 l_det_net_book_value := 0;
1255 l_det_operating_acct_cost := 0 ;
1256 l_det_operating_acct_backlog := 0 ;
1257 l_det_operating_acct_net := 0 ;
1258 l_det_operating_acct_ytd := 0 ;
1259 l_det_deprn_period := 0 ;
1260 l_det_deprn_reserve := 0;
1261 l_det_deprn_reserve_backlog := 0 ;
1262 l_det_reval_reserve_backlog := 0;
1263 l_det_reval_reserve_gen_fund := 0;
1264 l_det_reval_reserve_net := 0;
1265 l_det_reval_reserve_cost := 0;
1266 l_det_general_fund_acc := 0;
1267 l_det_general_fund_per := 0 ;
1268 l_det_hist_deprn_amount := 0 ;
1269 l_det_hist_deprn_reserve := 0 ;
1270 l_det_deprn_ytd := p_ytd_deprn * l_ytd_prorate_factor ;
1271
1272 --Bug 3575041 start (5) --
1273
1274 IF l_depreciate_flag ='YES' THEN
1275 l_det_hist_deprn_ytd := p_hist_deprn_ytd * l_ytd_prorate_factor ;
1276 ELSE
1277 OPEN C_ytd_dist(l_all_dist.distribution_id);
1278 FETCH C_ytd_dist INTO l_det_hist_deprn_ytd;
1279 CLOSE C_ytd_dist;
1280 END IF;
1281
1282 --Bug 3575041 end (5) --
1283
1284 END IF;
1285
1286 --
1287 -- Round the values
1288 --
1289 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_adjustment_cost ,
1290 p_book_type_code )) THEN
1291 null;
1292 END IF;
1293 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_net_book_value ,
1294 p_book_type_code )) THEN
1295 null;
1296 END IF;
1297 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_reval_reserve_cost ,
1298 p_book_type_code )) THEN
1299 null;
1300 END IF;
1301 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_reval_reserve_backlog ,
1302 p_book_type_code )) THEN
1303 null;
1304 END IF;
1305 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_reval_reserve_gen_fund ,
1306 p_book_type_code )) THEN
1307 null;
1308 END IF;
1309 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_reval_reserve_net ,
1310 p_book_type_code )) THEN
1311 null;
1312 END IF;
1313 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_operating_acct_cost ,
1314 p_book_type_code )) THEN
1315 null;
1316 END IF;
1317 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_operating_acct_backlog ,
1318 p_book_type_code )) THEN
1319 null;
1320 END IF;
1321 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_operating_acct_net ,
1322 p_book_type_code )) THEN
1323 null;
1324 END IF;
1325 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_operating_acct_ytd ,
1326 p_book_type_code )) THEN
1327 null;
1328 END IF;
1329 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_deprn_period ,
1330 p_book_type_code )) THEN
1331 null;
1332 END IF;
1333 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_deprn_ytd ,
1334 p_book_type_code )) THEN
1335 null;
1336 END IF;
1337 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_deprn_reserve ,
1338 p_book_type_code )) THEN
1339 null;
1340 END IF;
1341 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_deprn_reserve_backlog ,
1342 p_book_type_code )) THEN
1343 null;
1344 END IF;
1345 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_general_fund_per ,
1346 p_book_type_code )) THEN
1347 null;
1348 END IF;
1349 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_general_fund_acc ,
1350 p_book_type_code )) THEN
1351 null;
1352 END IF;
1353 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_op_ac_cost ,
1354 p_book_type_code )) THEN
1355 null;
1356 END IF;
1357 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_op_ac_backlog ,
1358 p_book_type_code )) THEN
1359 null;
1360 END IF;
1361 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_hist_deprn_amount ,
1362 p_book_type_code )) THEN
1363 null;
1364 END IF;
1365 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_hist_deprn_ytd ,
1366 p_book_type_code )) THEN
1367 null;
1368 END IF;
1369 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_det_hist_deprn_reserve ,
1370 p_book_type_code )) THEN
1371 null;
1372 END IF;
1373
1374
1375 --
1376 -- Create rows in igi_iac_det_balances
1377 --
1378
1379 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1380 p_full_path => g_path||'prorate_for_det_balances',
1381 p_string => ' -------- Detail Balances values --------');
1382
1383 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1384 p_full_path => g_path||'prorate_for_det_balances',
1385 p_string => ' | adjustment_cost => '
1386 || rpad( l_det_adjustment_cost ,20,' ') ||' |');
1387
1388 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1389 p_full_path => g_path||'prorate_for_det_balances',
1390 p_string => ' | net_book_value => '
1391 || rpad( l_det_net_book_value ,20,' ') ||' |');
1392
1393 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1394 p_full_path => g_path||'prorate_for_det_balances',
1395 p_string => ' | reval_reserve_cost => '
1396 || rpad( l_det_reval_reserve_cost ,20,' ') ||' |');
1397
1398 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1399 p_full_path => g_path||'prorate_for_det_balances',
1400 p_string => ' | reval_reserve_backlog => '
1401 || rpad( l_det_reval_reserve_backlog ,20,' ') ||' |');
1402
1403 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1404 p_full_path => g_path||'prorate_for_det_balances',
1405 p_string => ' | reval_reserve_gen_fund => '
1406 || rpad( l_det_reval_reserve_gen_fund ,20,' ') ||' |');
1407
1408 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1409 p_full_path => g_path||'prorate_for_det_balances',
1410 p_string => ' | reval_reserve_net => '
1411 || rpad( l_det_reval_reserve_net ,20,' ') ||' |');
1412
1413 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1414 p_full_path => g_path||'prorate_for_det_balances',
1415 p_string => ' | operating_acct_cost => '
1416 || rpad( l_det_operating_acct_cost ,20,' ') ||' |');
1417
1418 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1419 p_full_path => g_path||'prorate_for_det_balances',
1420 p_string => ' | operating_acct_backlog => '
1421 || rpad( l_det_operating_acct_backlog ,20,' ') ||' |');
1422
1423 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1424 p_full_path => g_path||'prorate_for_det_balances',
1425 p_string => ' | operating_acct_net => '
1426 || rpad( l_det_operating_acct_net ,20,' ') ||' |');
1427
1428 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1429 p_full_path => g_path||'prorate_for_det_balances',
1430 p_string => ' | operating_acct_ytd => '
1431 || rpad( l_det_operating_acct_ytd ,20,' ') ||' |');
1432
1433 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1434 p_full_path => g_path||'prorate_for_det_balances',
1435 p_string => ' | deprn_period => '
1436 || rpad( l_det_deprn_period ,20,' ') ||' |');
1437
1438 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1439 p_full_path => g_path||'prorate_for_det_balances',
1440 p_string => ' | deprn_ytd => '
1441 || rpad( l_det_deprn_ytd ,20,' ') ||' |');
1442
1443 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1444 p_full_path => g_path||'prorate_for_det_balances',
1445 p_string => ' | deprn_reserve => '
1446 || rpad( l_det_deprn_reserve ,20,' ') ||' |');
1447
1448 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1449 p_full_path => g_path||'prorate_for_det_balances',
1450 p_string => ' | deprn_reserve_backlog => '
1451 || rpad( l_det_deprn_reserve_backlog ,20,' ') ||' |');
1452
1453 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1454 p_full_path => g_path||'prorate_for_det_balances',
1455 p_string => ' | general_fund_per => '
1456 || rpad( l_det_general_fund_per ,20,' ') ||' |');
1457
1458 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1459 p_full_path => g_path||'prorate_for_det_balances',
1460 p_string => ' | general_fund_acc => '
1461 || rpad( l_det_general_fund_acc ,20,' ') ||' |');
1462
1463 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1464 p_full_path => g_path||'prorate_for_det_balances',
1465 p_string => ' | last_reval_date => '
1466 || rpad( l_det_last_reval_date ,20,' ') ||' |');
1467
1468
1469 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1470 p_full_path => g_path||'prorate_for_det_balances',
1471 p_string => ' | current_reval_factor => '
1472 || rpad( l_det_current_reval_factor ,20,' ') ||' |');
1473
1474 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1475 p_full_path => g_path||'prorate_for_det_balances',
1476 p_string => ' | cumulative_reval_factor => '
1477 || rpad( l_det_cumulative_reval_factor ,20,' ') ||' |');
1478
1479 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1480 p_full_path => g_path||'prorate_for_det_balances',
1481 p_string => ' ----------------------------------------');
1482
1483 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1484 p_full_path => g_path||'prorate_for_det_balances',
1485 p_string => 'Creating Detail Balances record ....');
1486
1487
1488 igi_iac_det_balances_pkg.insert_row (
1489 x_rowid => l_out_rowid ,
1490 x_adjustment_id => p_adj_id ,
1491 x_asset_id => p_asset_id ,
1492 x_distribution_id => l_all_dist.distribution_id ,
1493 x_book_type_code => p_book_type_code ,
1494 x_period_counter => l_period_counter ,
1495 x_adjustment_cost => l_det_adjustment_cost ,
1496 x_net_book_value => l_det_net_book_value ,
1497 x_reval_reserve_cost => l_det_reval_reserve_cost ,
1498 x_reval_reserve_backlog => l_det_reval_reserve_backlog ,
1499 x_reval_reserve_gen_fund => l_det_reval_reserve_gen_fund ,
1500 x_reval_reserve_net => l_det_reval_reserve_net ,
1501 x_operating_acct_cost => l_det_operating_acct_cost ,
1502 x_operating_acct_backlog => l_det_operating_acct_backlog ,
1503 x_operating_acct_net => l_det_operating_acct_net ,
1504 x_operating_acct_ytd => l_det_operating_acct_ytd ,
1505 x_deprn_period => l_det_deprn_period ,
1506 x_deprn_ytd => l_det_deprn_ytd ,
1507 x_deprn_reserve => l_det_deprn_reserve ,
1508 x_deprn_reserve_backlog => l_det_deprn_reserve_backlog ,
1509 x_general_fund_per => l_det_general_fund_per ,
1510 x_general_fund_acc => l_det_general_fund_acc ,
1511 x_last_reval_date => l_det_last_reval_date ,
1512 x_current_reval_factor => l_det_current_reval_factor ,
1513 x_cumulative_reval_factor => l_det_cumulative_reval_factor ,
1514 x_active_flag => l_flag ,
1515 x_mode => 'R' );
1516
1517 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1518 p_full_path => g_path||'prorate_for_det_balances',
1519 p_string => ' -------- Historic Deprn Balances values --------');
1520
1521 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1522 p_full_path => g_path||'prorate_for_det_balances',
1523 p_string => ' | deprn_period => '
1524 || rpad( l_det_hist_deprn_amount ,20,' ') ||' |');
1525
1526 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1527 p_full_path => g_path||'prorate_for_det_balances',
1528 p_string => ' | deprn_ytd => '
1529 || rpad( l_det_hist_deprn_ytd ,20,' ') ||' |');
1530
1531 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1532 p_full_path => g_path||'prorate_for_det_balances',
1533 p_string => ' | deprn_reserve => '
1534 || rpad( l_det_hist_deprn_reserve ,20,' ') ||' |');
1535
1536 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1537 p_full_path => g_path||'prorate_for_det_balances',
1538 p_string => ' ------------------------------------------------');
1539
1540 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1541 p_full_path => g_path||'prorate_for_det_balances',
1542 p_string => 'Creating historic Depreciation Balances record ....');
1543
1544 l_out_rowid := NULL;
1545 igi_iac_fa_deprn_pkg.insert_row(
1546 x_rowid => l_out_rowid ,
1547 x_book_type_code => p_book_type_code ,
1548 x_asset_id => p_asset_id ,
1549 x_period_counter => p_prd_rec.period_counter ,
1550 x_adjustment_id => p_adj_id ,
1551 x_distribution_id => l_all_dist.distribution_id ,
1552 x_deprn_period => l_det_hist_deprn_amount ,
1553 x_deprn_ytd => l_det_hist_deprn_ytd ,
1554 x_deprn_reserve => l_det_hist_deprn_reserve ,
1555 x_active_flag => l_flag ,
1556 x_mode => 'R' );
1557
1558
1559 END LOOP;
1560 RETURN TRUE ;
1561 EXCEPTION
1562 WHEN OTHERS
1563 THEN
1564 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => g_path||'prorate_for_det_balances');
1565 RETURN FALSE ;
1566 END Prorate_for_Det_Balances; -- end function
1567
1568
1569 FUNCTION Create_Adjustments ( p_book_type_code VARCHAR2 ,
1570 p_asset_id NUMBER ,
1571 p_adj_id NUMBER ,
1572 p_period_counter NUMBER ,
1573 p_errbuf OUT NOCOPY VARCHAR2,
1574 p_event_id number )
1575 RETURN BOOLEAN IS
1576 CURSOR c_dists IS
1577 SELECT *
1578 FROM igi_iac_det_balances db
1579 WHERE db.book_type_Code = p_book_type_code
1580 AND db.asset_id = p_asset_id
1581 AND db.adjustment_id = p_adj_id
1582 AND db.active_flag is NULL;
1583
1584 l_out_rowid VARCHAR2(250) ;
1585 l_account_Type VARCHAR2(50) ;
1586 l_adj_type VARCHAR2(50) ;
1587 l_amount NUMBER ;
1588 l_units NUMBER ;
1589 l_ccid NUMBER ;
1590 l_set_of_books_Id NUMBER ;
1591 l_coa_id NUMBER ;
1592 l_currency VARCHAR2(30) ;
1593 l_precision NUMBER ;
1594 l_dr_cr_flag VARCHAR2(2) ;
1595 l_reval_rsv_ccid NUMBER ;
1596 l_op_exp_ccid NUMBER ;
1597 l_report_ccid NUMBER ;
1598 l_adjustment_offset_type VARCHAR2(50) ;
1599
1600 BEGIN
1601
1602 l_amount := 0 ;
1603 l_units := 0 ;
1604 l_ccid := 0 ;
1605 l_set_of_books_Id := 0 ;
1606 l_coa_id := 0 ;
1607 l_precision := 2 ;
1608 l_reval_rsv_ccid :=null;
1609 l_op_exp_ccid :=null;
1610 l_report_ccid := null;
1611 l_adjustment_offset_type := null;
1612
1613 GLOBAL_CURRENT_PROC := 'CREATE_ADJUSTMENTS > ' ;
1614
1615 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1616 p_full_path => g_path||'create_adjustments',
1617 p_string => 'Getting GL Info for book ...');
1618
1619 IF ( NOT ( Igi_Iac_Common_Utils.Get_Book_GL_Info ( p_book_type_code ,
1620 l_set_of_books_Id ,
1621 l_coa_id ,
1622 l_currency ,
1623 l_precision ))) THEN
1624 FND_MESSAGE.SET_NAME('IGI', 'IGI_IAC_EXCEPTION');
1625 FND_MESSAGE.SET_TOKEN('PACKAGE','igi_imp_iac_transfer_pkg');
1626 FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','Error Getting GL Info.');
1627
1628 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
1629 p_full_path => g_path||'create_adjustments',
1630 p_remove_from_stack => FALSE);
1631 p_errbuf := FND_MESSAGE.GET;
1632 fnd_file.put_line(fnd_file.log, p_errbuf);
1633 RETURN FALSE ;
1634 END IF;
1635
1636 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1637 p_full_path => g_path||'create_adjustments',
1638 p_string => 'Processing Distributions ...');
1639
1640 FOR drec IN c_dists LOOP
1641
1642 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1643 p_full_path => g_path||'create_adjustments',
1644 p_string => '------------------- Processing Distribution ID ->'
1645 ||drec.distribution_id );
1646
1647 SELECT units_assigned
1648 INTO l_units
1649 FROM fa_distribution_history
1650 WHERE distribution_id = drec.distribution_id ;
1651
1652 IF l_units =0
1653 THEN
1654 igi_iac_adjustments_pkg.insert_row
1655 (x_rowid => l_out_rowid ,
1656 x_adjustment_id => p_adj_id ,
1657 x_book_type_code => p_book_type_code ,
1658 x_code_combination_id => -1 ,
1659 x_set_of_books_id => -1 ,
1660 x_dr_cr_flag => ' ' ,
1661 x_amount => 0 ,
1662 x_adjustment_type => ' ' ,
1663 x_transfer_to_gl_flag => 'I' ,
1664 x_units_assigned => l_units ,
1665 x_asset_id => p_asset_id ,
1666 x_distribution_id => drec.distribution_id ,
1667 x_period_counter => p_period_counter ,
1668 x_adjustment_offset_type => Null,
1669 x_report_ccid => Null,
1670 x_mode => 'R',
1671 x_event_id => p_event_id ) ;
1672
1673
1674 ELSE
1675 l_reval_rsv_ccid:=null;
1676 l_op_exp_ccid:=null;
1677
1678 FOR l_index in 1 .. 10 LOOP
1679 l_ccid := null ;
1680 l_report_ccid := null;
1681 IF ( mod(l_index,2) = 1 ) THEN
1682 l_dr_cr_flag := 'DR' ;
1683 ELSE
1684 l_dr_cr_flag := 'CR' ;
1685 END IF;
1686
1687 IF l_index = 1 THEN
1688 IF ( drec.adjustment_cost < 0 ) THEN
1689 l_account_Type := 'OPERATING_EXPENSE_ACCT' ;
1690 l_adj_type := 'OP EXPENSE' ;
1691 ELSE
1692 l_account_Type := 'REVAL_RESERVE_ACCT' ;
1693 l_adj_type := 'REVAL RESERVE' ;
1694 END IF;
1695 l_amount := drec.adjustment_cost ;
1696 l_adjustment_offset_type :='COST';
1697 ELSIF l_index = 2 THEN
1698 l_account_Type := 'ASSET_COST_ACCT' ;
1699 l_adj_type := 'COST' ;
1700 l_amount := drec.adjustment_cost ;
1701 IF ( drec.adjustment_cost < 0 ) THEN
1702 l_adjustment_offset_type :='OP EXPENSE';
1703 l_report_ccid :=l_op_exp_ccid;
1704 ELSE
1705 l_adjustment_offset_type :='REVAL RESERVE';
1706 l_report_ccid :=l_reval_rsv_ccid;
1707 END IF;
1708 ELSIF l_index = 3 THEN
1709 l_account_Type := 'OPERATING_EXPENSE_ACCT' ;
1710 l_adj_type := 'OP EXPENSE' ;
1711 l_amount := drec.operating_acct_backlog ;
1712 l_adjustment_offset_type :='BL RESERVE';
1713 ELSIF l_index = 4 THEN
1714 l_account_Type := 'BACKLOG_DEPRN_RSV_ACCT' ;
1715 l_adj_type := 'BL RESERVE' ;
1716 l_amount := drec.operating_acct_backlog ;
1717 l_adjustment_offset_type :='OP EXPENSE';
1718 l_report_ccid :=l_op_exp_ccid;
1719 ELSIF l_index = 5 THEN
1720 l_account_Type := 'REVAL_RESERVE_ACCT' ;
1721 l_adj_type := 'REVAL RESERVE' ;
1722 l_amount := drec.reval_reserve_backlog ;
1723 l_adjustment_offset_type :='BL RESERVE';
1724 ELSIF l_index = 6 THEN
1725 l_account_Type := 'BACKLOG_DEPRN_RSV_ACCT' ;
1726 l_adj_type := 'BL RESERVE' ;
1727 l_amount := drec.reval_reserve_backlog ;
1728 l_adjustment_offset_type :='REVAL RESERVE';
1729 l_report_ccid :=l_reval_rsv_ccid;
1730 ELSIF l_index = 7 THEN
1731 l_account_Type := 'DEPRN_EXPENSE_ACCT' ;
1732 l_adj_type := 'EXPENSE' ;
1733 l_amount := drec.deprn_reserve ;
1734 l_adjustment_offset_type :='RESERVE';
1735 ELSIF l_index = 8 THEN
1736 l_account_Type := 'DEPRN_RESERVE_ACCT' ;
1737 l_adj_type := 'RESERVE' ;
1738 l_amount := drec.deprn_reserve ;
1739 l_adjustment_offset_type :='EXPENSE';
1740 ELSIF l_index = 9 THEN
1741 l_account_Type := 'REVAL_RESERVE_ACCT' ;
1742 l_adj_type := 'REVAL RESERVE' ;
1743 l_amount := drec.general_fund_acc ;
1744 l_adjustment_offset_type :='GENERAL FUND' ;
1745 ELSIF l_index = 10 THEN
1746 l_account_Type := 'GENERAL_FUND_ACCT' ;
1747 l_adj_type := 'GENERAL FUND' ;
1748 l_amount := drec.general_fund_acc ;
1749 l_adjustment_offset_type :='REVAL RESERVE';
1750 l_report_ccid :=l_reval_rsv_ccid;
1751 END IF;
1752
1753 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1754 p_full_path => g_path||'create_adjustments',
1755 p_string => 'Creating entry for adj '|| l_adj_type
1756 ||' account '|| l_account_type ) ;
1757
1758 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1759 p_full_path => g_path||'create_adjustments',
1760 p_string => '==============Amount = '|| l_amount
1761 ||' DR/CR '|| l_dr_cr_flag ) ;
1762
1763 IF l_amount <> 0 THEN
1764 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1765 p_full_path => g_path||'create_adjustments',
1766 p_string => 'Getting CCID ...') ;
1767
1768 IF ( NOT ( Igi_Iac_Common_Utils.Get_Account_CCID (
1769 p_book_type_code ,
1770 p_asset_id ,
1771 drec.distribution_id ,
1772 l_account_Type ,
1773 l_ccid ))) THEN
1774
1775 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1776 p_full_path => g_path||'create_adjustments',
1777 p_string => 'Error : Common Utils Function Get_Account_CCID failed for asset '|| p_asset_id
1778 ||' distribution_id '|| drec.distribution_id
1779 ||' and account type '|| l_Account_type );
1780 END IF;
1781
1782 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1783 p_full_path => g_path||'create_adjustments',
1784 p_string => 'Creating Adjustment ...') ;
1785
1786 IF l_adj_type='REVAL RESERVE' THEN
1787 l_reval_rsv_ccid:= l_ccid;
1788 ELSIF l_adj_type='OP EXPENSE' THEN
1789 l_op_exp_ccid:=l_ccid;
1790 END IF;
1791
1792
1793 igi_iac_adjustments_pkg.insert_row (
1794 x_rowid => l_out_rowid ,
1795 x_adjustment_id => p_adj_id ,
1796 x_book_type_code => p_book_type_code ,
1797 x_code_combination_id => l_ccid ,
1798 x_set_of_books_id => l_set_of_books_id ,
1799 x_dr_cr_flag => l_dr_cr_flag ,
1800 x_amount => l_amount ,
1801 x_adjustment_type => l_adj_type ,
1802 x_transfer_to_gl_flag => 'I' ,
1803 x_units_assigned => l_units ,
1804 x_asset_id => p_asset_id ,
1805 x_distribution_id => drec.distribution_id ,
1806 x_period_counter => p_period_counter ,
1807 x_adjustment_offset_type => l_adjustment_offset_type,
1808 x_report_ccid => l_report_ccid,
1809 x_mode => 'R' ,
1810 x_event_id => p_event_id) ;
1811 END IF;
1812
1813 END LOOP ;
1814 END IF;
1815 END LOOP ;
1816
1817
1818 RETURN TRUE ;
1819
1820 EXCEPTION
1821 WHEN OTHERS THEN
1822 p_errbuf := 'Error Creating adjustments '|| sqlerrm ;
1823 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => g_path||'create_adjustments');
1824 RETURN FALSE ;
1825 END;
1826
1827
1828 PROCEDURE TRANSFER_DATA ( errbuf OUT NOCOPY VARCHAR2 ,
1829 retcode OUT NOCOPY NUMBER ,
1830 p_book_type_code VARCHAR2 ,
1831 p_category_id NUMBER,
1832 p_event_id number) --R12 uptake
1833 IS
1834 CURSOR c_ctrl IS
1835 SELECT *
1836 FROM igi_imp_iac_interface_ctrl ic
1837 WHERE ic.book_type_code = p_book_type_code
1838 AND ic.category_id = p_Category_id ;
1839
1840 CURSOR c_assets IS
1841 SELECT *
1842 FROM igi_imp_iac_interface ii
1843 WHERE ii.book_type_code = p_book_type_code
1844 AND ii.category_id = p_Category_id
1845 AND ii.transferred_flag = 'N'
1846 AND ii.valid_flag = 'Y'; --Fix for Bug 5137813
1847
1848 Cursor C_book_class is
1849 Select book_class
1850 from fa_booK_controls
1851 where book_type_code = p_booK_type_code;
1852
1853 -- Cursor to fetch the assets from the interface table
1854 CURSOR c_txns(cp_book VARCHAR2) IS
1855 SELECT 'Y'
1856 FROM igi_iac_transaction_headers it
1857 WHERE it.book_type_code = cp_book
1858 AND it.category_id = p_Category_id
1859 AND NOT ( nvl(it.transaction_sub_type,'AA') = 'IMPLEMENTATION')
1860 AND rownum = 1 ;
1861
1862 Cursor c_get_reval_factor( cp_book VARCHAR2,cp_asset_id number) is
1863 select current_reval_factor,cummulative_reval_factor
1864 from igi_imp_iac_interface_py_add
1865 where book_type_code = cp_book
1866 and asset_id =cp_asset_id;
1867
1868
1869 Cursor C_Book_Info(cp_book in varchar2, cp_asset_id in number) Is
1870 Select bk.asset_id,
1871 bk.date_placed_in_service,
1872 bk.life_in_months,
1873 nvl(bk.cost,0) cost,
1874 nvl(bk.adjusted_cost,0) adjusted_cost,
1875 nvl(bk.original_cost,0) original_cost,
1876 nvl(bk.salvage_value,0) salvage_value,
1877 nvl(bk.adjusted_recoverable_cost, 0) adjusted_recoverable_cost,
1878 nvl(bk.recoverable_cost,0) recoverable_cost,
1879 bk.deprn_start_date,
1880 bk.cost_change_flag,
1881 bk.rate_adjustment_factor,
1882 bk.depreciate_flag,
1883 bk.fully_rsvd_revals_counter,
1884 bk.period_counter_fully_reserved,
1885 bk.period_counter_fully_retired
1886 From fa_books bk
1887 Where bk.book_type_code = cp_book
1888 and bk.asset_id = cp_asset_id
1889 and bk.transaction_header_id_out is null;
1890
1891 cursor C_get_deprn_details(cp_book in varchar2, cp_asset_id in number)is
1892 Select period_counter,deprn_reserve
1893 from fa_deprn_summary fds
1894 where book_type_code =cp_book
1895 and asset_id = cp_asset_id
1896 and period_counter = ( select max(period_counter)
1897 from fa_deprn_summary
1898 where book_type_code =fds.book_type_code
1899 and asset_id = fds.asset_id);
1900
1901 --l_errbuf VARCHAR2(1200) ;
1902 l_transfer_status VARCHAR2(1) ;
1903 l_out_rowid VARCHAR2(200) ;
1904 l_reserved_flag VARCHAR2(1);
1905 l_txns_flag VARCHAR2(1);
1906 l_period_counter NUMBER(15) ;
1907 l_ytd_deprn NUMBER(15) ;
1908 l_out_adj_id NUMBER(15) ;
1909 l_prd_rec igi_iac_types.prd_rec ;
1910 l_prev_prd_rec igi_iac_types.prd_rec ;
1911 l_out_reval_id NUMBER(15) ;
1912 l_net_book_value NUMBER ;
1913 l_adjusted_cost NUMBER ;
1914 l_operating_acct NUMBER ;
1915 l_reval_reserve NUMBER ;
1916 l_deprn_amount NUMBER ;
1917 l_deprn_reserve NUMBER ;
1918 l_assetrec c_assets%ROWTYPE ;
1919 l_backlog_deprn_reserve NUMBER ;
1920 l_general_fund NUMBER ;
1921 l_current_reval_factor NUMBER ;
1922 l_cumulative_reval_factor NUMBER ;
1923 l_corporate_book VARCHAR2(30);
1924 l_prev_out_adj_id NUMBER(15) ;
1925 l_period_for_rates NUMBER ;
1926 l_num_per_fiscal_year NUMBER ;
1927 l_period_num_for_catchup NUMBER ;
1928 l_hist_deprn_amount NUMBER ;
1929 l_hist_deprn_ytd NUMBER ;
1930 l_hist_deprn_reserve NUMBER ;
1931 l_book_class fa_book_controls.book_class%type;
1932 --l_book_type_code VARCHAR2(15):=p_book_type_code;
1933 l_book_info_rec C_Book_Info%rowtype;
1934 l_corp_deprn_summary number ;
1935 l_hist_info igi_iac_types.fa_hist_asset_info;
1936 l_corp_last_per_counter number;
1937 l_dpis_period_counter number;
1938 l_hist_deprn_amount_sal_corr Number;
1939 l_assets_valid BOOLEAN; -- For BUG 5137813
1940
1941 BEGIN
1942
1943
1944 l_transfer_status := 'X' ;
1945 l_net_book_value := 0 ;
1946 l_adjusted_cost := 0 ;
1947 l_operating_acct := 0 ;
1948 l_reval_reserve := 0 ;
1949 l_deprn_amount := 0 ;
1950 l_deprn_reserve := 0 ;
1951 l_backlog_deprn_reserve := 0 ;
1952 l_general_fund := 0 ;
1953 l_current_reval_factor := 0 ;
1954 l_cumulative_reval_factor := 0 ;
1955 l_hist_deprn_amount := 0;
1956 l_hist_deprn_ytd := 0;
1957 l_hist_deprn_reserve := 0;
1958 l_corp_deprn_summary :=0;
1959 l_corp_last_per_counter :=0;
1960 l_dpis_period_counter :=0;
1961
1962 retcode := 2 ;
1963
1964
1965 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1966 p_full_path => g_path||'transfer_data',
1967 p_string => '*********** Start of Transfer to IAC... **********');
1968
1969 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1970 p_full_path => g_path||'transfer_data',
1971 p_string => '*********** Start of Transfer to IAC... **********');
1972
1973 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1974 p_full_path => g_path||'transfer_data',
1975 p_string => '------> Book : '|| rpad(p_book_type_code ,15,' ')||' ') ;
1976
1977 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1978 p_full_path => g_path||'transfer_data',
1979 p_string => '------> Category ID : '|| rpad(p_category_id,15,' ') ||' ') ;
1980 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1981 p_full_path => g_path||'transfer_data',
1982 p_string => '-------------------------------------- ');
1983
1984
1985 GLOBAL_CURRENT_PROC := 'TRANSFER_DATA > ' ;
1986
1987
1988 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
1989 p_full_path => g_path||'transfer_data',
1990 p_string => 'Checking availability of book and category for transfer ....');
1991
1992 -- Check if the category has already been transferred
1993 --
1994 l_transfer_status := 'X' ;
1995
1996 FOR ctrlrec IN c_ctrl LOOP
1997 l_transfer_status := ctrlrec.transfer_Status ;
1998 IF ctrlrec.transfer_status = 'C' THEN
1999 fnd_message.set_name ('IGI','IGI_IMP_IAC_TFR_ALREADY_DONE');
2000 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
2001 p_full_path => g_path||'transfer_data',
2002 p_remove_from_stack => FALSE);
2003 errbuf := fnd_message.get;
2004 fnd_file.put_line(fnd_file.log, errbuf);
2005 retcode := 0 ;
2006 RETURN ;
2007 END IF;
2008 END LOOP ;
2009
2010
2011 IF l_transfer_status ='X' THEN
2012 set_interface_ctrl_status( p_book_type_code ,
2013 p_category_id ,
2014 'N' );
2015 fnd_message.set_name ('IGI','IGI_IMP_IAC_NO_PREPARE');
2016 igi_iac_debug_pkg.debug_other_msg(p_level => g_state_level,
2017 p_full_path => g_path||'transfer_data',
2018 p_remove_from_stack => FALSE);
2019 errbuf := fnd_message.get;
2020 fnd_file.put_line(fnd_file.log, errbuf);
2021 retcode := 0 ;
2022 COMMIT WORK;
2023 RETURN ;
2024 END IF;
2025 l_reserved_flag := 'Y' ;
2026
2027
2028 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2029 p_full_path => g_path||'transfer_data',
2030 p_string => ' Getting the period counter and corporate book ...');
2031
2032 -- Get the period counter and corporate book
2033 --
2034 BEGIN
2035 SELECT ic.period_counter , ic.corp_book
2036 INTO l_period_counter , l_corporate_book
2037 FROM igi_imp_iac_controls ic
2038 WHERE ic.book_type_code = p_book_type_code ;
2039 EXCEPTION
2040 WHEN OTHERS THEN
2041 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2042 p_full_path => g_path||'transfer_data',
2043 p_string => 'Error : Fetching period counter from control '|| sqlerrm);
2044 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2045 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2046 p_full_path => g_path||'transfer_data',
2047 p_remove_from_stack => FALSE);
2048 errbuf := fnd_message.get;
2049 fnd_file.put_line(fnd_file.log, errbuf);
2050 raise igi_imp_tfr_error ;
2051 END;
2052
2053
2054 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2055 p_full_path => g_path||'transfer_data',
2056 p_string => 'Checking if there have been transactions in Inflation Accounting ...');
2057 l_txns_flag := 'N' ;
2058 For txnrec in c_txns(l_corporate_book) loop
2059 l_txns_flag := 'Y' ;
2060 end loop ;
2061
2062 If l_txns_flag = 'Y' Then
2063 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2064 p_full_path => g_path||'transfer_data',
2065 p_string => 'Error: There have been transactions in Inflation Accounting .');
2066 set_interface_ctrl_status( p_book_type_code ,
2067 p_category_id ,
2068 'E' );
2069 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_TXNS_IN_IAC');
2070 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2071 p_full_path => g_path||'transfer_data',
2072 p_remove_from_stack => FALSE);
2073 Errbuf := fnd_message.get;
2074 fnd_file.put_line(fnd_file.log, errbuf);
2075 retcode := 2 ;
2076 COMMIT WORK;
2077 RETURN ;
2078 End If;
2079
2080 --Check for transactions in open period
2081
2082 IF( NOT Trxns_In_Open_Period(p_book_type_code))
2083 THEN
2084 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2085 p_full_path => g_path||'transfer_data',
2086 p_string => 'Error: There have been open period transactions on the Book.');
2087 set_interface_ctrl_status( p_book_type_code ,
2088 p_category_id ,
2089 'E' );
2090 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRXNS_IN_OPEN_PERD');
2091 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2092 p_full_path => g_path||'transfer_data',
2093 p_remove_from_stack => FALSE);
2094 Errbuf := fnd_message.get;
2095 fnd_file.put_line(fnd_file.log, errbuf);
2096 retcode := 2 ;
2097 COMMIT WORK;
2098 RETURN ;
2099 END IF;
2100
2101 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2102 p_full_path => g_path||'transfer_data',
2103 p_string => 'Fetching period Info for counter : '|| l_period_counter );
2104 -- Fetch period Info for the counter
2105 --
2106 IF ( NOT( Igi_Iac_Common_Utils.Get_Period_Info_For_Counter (
2107 l_corporate_book ,
2108 l_period_Counter ,
2109 l_prd_rec
2110 ))) THEN
2111 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2112 p_full_path => g_path||'transfer_data',
2113 p_string => 'Fetching period Info for counter : '|| l_period_counter );
2114 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2115 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2116 p_full_path => g_path||'transfer_data',
2117 p_remove_from_stack => FALSE);
2118 errbuf := fnd_message.get;
2119 fnd_file.put_line(fnd_file.log, errbuf);
2120 raise igi_imp_tfr_error ;
2121 END IF;
2122
2123 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2124 p_full_path => g_path||'transfer_data',
2125 p_string => 'Fetching period Info for closed period , counter : '|| (l_period_counter-1) );
2126
2127 -- Fetch period Info for the closed period counter
2128 --
2129 IF ( NOT( Igi_Iac_Common_Utils.Get_Period_Info_For_Counter (
2130 l_corporate_book ,
2131 l_period_Counter-1 ,
2132 l_prev_prd_rec
2133 ))) THEN
2134 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2135 p_full_path => g_path||'transfer_data',
2136 p_string => 'Error: Fetching period Info for counter : '|| (l_period_counter-1) );
2137 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2138 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2139 p_full_path => g_path||'transfer_data',
2140 p_remove_from_stack => FALSE);
2141 errbuf := fnd_message.get;
2142 fnd_file.put_line(fnd_file.log, errbuf);
2143 raise igi_imp_tfr_error ;
2144 END IF;
2145
2146
2147 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2148 p_full_path => g_path||'transfer_data',
2149 p_string => 'Before creating Revaluations record ...');
2150 -- Create row in igi_iac_revaluations
2151 --
2152 igi_iac_revaluations_pkg.insert_row (
2153 X_rowid => l_out_rowid ,
2154 X_revaluation_id => l_out_reval_id ,
2155 X_book_type_code => l_corporate_book ,
2156 X_revaluation_date => l_prd_rec.period_end_date ,
2157 X_revaluation_period => l_prd_rec.period_counter ,
2158 X_status => 'COMPLETE' ,
2159 X_reval_request_id => NULL ,
2160 X_create_request_id => NULL ,
2161 X_calling_program => 'IMPLEMENTATION' ,
2162 X_mode => 'R',
2163 x_event_id => p_event_id
2164 ) ;
2165
2166
2167 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2168 p_full_path => g_path||'transfer_data',
2169 p_string => ' Creating row in igi_iac_Reval_categories ...');
2170 -- Create row in igi_iac_Reval_categories
2171 --
2172 BEGIN
2173 INSERT INTO igi_iac_reval_categories
2174 (
2175 REVALUATION_ID ,
2176 BOOK_TYPE_CODE ,
2177 CATEGORY_ID ,
2178 SELECT_CATEGORY ,
2179 CREATED_BY ,
2180 CREATION_DATE ,
2181 LAST_UPDATE_LOGIN ,
2182 LAST_UPDATE_DATE ,
2183 LAST_UPDATED_BY
2184 )
2185 VALUES
2186 (
2187 l_out_reval_id ,
2188 l_corporate_book ,
2189 p_category_id ,
2190 'Y' ,
2191 fnd_global.user_id ,
2192 sysdate ,
2193 fnd_global.login_id ,
2194 sysdate ,
2195 fnd_global.user_id
2196 );
2197 EXCEPTION
2198 WHEN OTHERS THEN
2199 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2200 p_full_path => g_path||'transfer_data',
2201 p_string => 'Error : Creating record in igi_iac_reval_categories '
2202 || sqlerrm);
2203 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2204 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2205 p_full_path => g_path||'transfer_data',
2206 p_remove_from_stack => FALSE);
2207 errbuf := fnd_message.get;
2208 fnd_file.put_line(fnd_file.log, errbuf);
2209 raise igi_imp_tfr_error ;
2210 END;
2211
2212
2213 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2214 p_full_path => g_path||'transfer_data',
2215 p_string => 'Get all records in the category to process ...');
2216
2217 open c_booK_class;
2218 Fetch c_booK_class into l_booK_class;
2219 Close c_book_class;
2220
2221 -- Validate Assets (fix for BUG 5137813)
2222 l_assets_valid := Validate_Assets(p_book_type_code,p_category_id);
2223
2224 -- Get all records in the category to process.
2225 --
2226 FOR arec IN c_assets LOOP
2227
2228 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2229 p_full_path => g_path||'transfer_data',
2230 p_string => ' ----------------> Processing Asset ID : '|| arec.asset_id ) ;
2231
2232 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2233 p_full_path => g_path||'transfer_data',
2234 p_string => ' Creating row in igi_iac_transaction_headers for closed period...');
2235 -- Create row in igi_iac_transaction_headers for closed period
2236 --
2237 l_prev_out_adj_id := null;
2238 l_out_adj_id := null;
2239
2240 igi_iac_trans_headers_pkg.insert_row (
2241 x_rowid => l_out_rowid ,
2242 x_adjustment_id => l_prev_out_adj_id ,
2243 x_transaction_header_id => null ,
2244 x_adjustment_id_out => null ,
2245 x_transaction_type_code => 'DEPRECIATION' ,
2246 x_transaction_date_entered => l_prev_prd_rec.period_end_date ,
2247 x_mass_refrence_id => l_out_reval_id ,
2248 x_transaction_sub_type => 'IMPLEMENTATION' ,
2249 x_book_type_code => l_corporate_book ,
2250 x_asset_id => arec.asset_id ,
2251 x_category_id => arec.category_id ,
2252 x_adj_deprn_start_date => sysdate ,
2253 x_revaluation_type_flag => 'P' ,
2254 x_adjustment_status => 'COMPLETE' ,
2255 x_period_counter => l_prev_prd_rec.period_counter,
2256 x_mode => 'R',
2257 x_event_id => p_event_id
2258 ) ;
2259 SELECT igi_iac_transaction_headers_s.NEXTVAL
2260 INTO l_out_adj_id
2261 FROM sys.dual;
2262
2263 UPDATE igi_iac_transaction_headers
2264 SET adjustment_id_out = l_out_adj_id
2265 WHERE asset_id = arec.asset_id
2266 AND book_type_code = l_corporate_book
2267 AND adjustment_id_out IS NULL ;
2268
2269
2270 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2271 p_full_path => g_path||'transfer_data',
2272 p_string => ' Creating row in igi_iac_transaction_headers ...');
2273 -- Create row in igi_iac_transaction_headers
2274 --
2275 igi_iac_trans_headers_pkg.insert_row (
2276 x_rowid => l_out_rowid ,
2277 x_adjustment_id => l_out_adj_id ,
2278 x_transaction_header_id => null , -- mass ref id will be populated with reval id
2279 x_adjustment_id_out => null ,
2280 x_transaction_type_code => 'REVALUATION' ,
2281 -- Bug 10180962 start
2282 x_transaction_date_entered => l_prev_prd_rec.period_end_date ,
2283 -- Bug 10180962 end
2284 x_mass_refrence_id => l_out_reval_id ,
2285 x_transaction_sub_type => 'IMPLEMENTATION' ,
2286 x_book_type_code => l_corporate_book ,
2287 x_asset_id => arec.asset_id ,
2288 x_category_id => arec.category_id ,
2289 x_adj_deprn_start_date => sysdate , --????
2290 x_revaluation_type_flag => 'P' , --??? -- Setting to occassional
2291 x_adjustment_status => 'COMPLETE' ,
2292 -- Bug 10180962 start
2293 x_period_counter => l_prev_prd_rec.period_counter,
2294 -- Bug 10180962 end
2295 x_mode => 'R',
2296 x_event_id => p_event_id
2297 ) ;
2298
2299 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2300 p_full_path => g_path||'transfer_data',
2301 p_string => 'Assigning variables to populate asset balances...');
2302 -- Assign variables to populate asset balances
2303 --
2304 l_net_book_value := nvl(arec.nbv_mhca,0) - nvl(arec.nbv_hist,0) ;--stop
2305 l_adjusted_cost := nvl(arec.cost_mhca,0) - nvl(arec.cost_hist,0) ;
2306 l_operating_acct := nvl(arec.operating_account_mhca,0) - nvl(arec.operating_account_hist,0) ;
2307 l_reval_reserve := nvl(arec.reval_reserve_mhca,0) - nvl(arec.reval_reserve_hist,0) ;
2308 l_deprn_amount := nvl(arec.deprn_exp_mhca,0) - nvl(arec.deprn_exp_hist,0) ;
2309 l_ytd_deprn := nvl(arec.ytd_mhca,0) - nvl(arec.ytd_hist,0) ;
2310 l_deprn_reserve := nvl(arec.accum_deprn_mhca,0) - nvl(arec.accum_deprn_hist,0) ;
2311 l_backlog_deprn_reserve := nvl(arec.backlog_mhca,0) - nvl(arec.backlog_hist,0) ;
2312 l_general_fund := nvl(arec.general_fund_mhca,0) - nvl(arec.general_fund_hist,0) ;
2313 l_hist_deprn_amount := nvl(arec.deprn_exp_hist,0);
2314 l_hist_deprn_ytd := nvl(arec.ytd_hist,0);
2315 l_hist_deprn_reserve := nvl(arec.accum_deprn_hist,0);
2316
2317
2318 IF ( nvl( arec.cost_hist,0) = 0 ) THEN
2319 l_current_reval_factor := 1 ;
2320 l_cumulative_reval_factor := 1 ;
2321 ELSE
2322 l_current_reval_factor := arec.cost_mhca/arec.cost_hist ;
2323 l_cumulative_reval_factor := arec.cost_mhca/arec.cost_hist ;
2324 END IF;
2325
2326
2327 /* IF l_book_class = 'CORPORATE' THEN
2328
2329 l_hist_deprn_ytd:= (arec.ytd_mhca-l_hist_deprn_ytd) /(l_cumulative_reval_factor-1);
2330 -- remove salvage value correction
2331 IF ((arec.hist_salvage_value is not Null) or (NOt arec.hist_salvage_value =0)) THEN
2332 l_hist_deprn_ytd := l_hist_deprn_ytd - ((l_hist_deprn_ytd/(arec.cost_hist-arec.hist_salvage_value))*arec.hist_salvage_value);
2333 End if;
2334
2335 END IF;*/
2336
2337 IF l_book_class = 'CORPORATE' THEN
2338
2339 open c_book_info(l_corporate_book,arec.asset_id);
2340 Fetch c_book_info into l_book_info_rec;
2341 close c_book_info;
2342
2343 l_hist_info.cost := l_book_info_rec.cost;
2344 l_hist_info.adjusted_cost := l_book_info_rec.adjusted_cost;
2345 l_hist_info.original_cost := l_book_info_rec.original_cost;
2346 l_hist_info.salvage_value := l_book_info_rec.salvage_value;
2347 l_hist_info.life_in_months := l_book_info_rec.life_in_months;
2348 l_hist_info.rate_adjustment_factor := l_book_info_rec.rate_adjustment_factor;
2349 l_hist_info.period_counter_fully_reserved := l_book_info_rec.period_counter_fully_reserved;
2350 l_hist_info.adjusted_recoverable_cost := l_book_info_rec.adjusted_recoverable_cost;
2351 l_hist_info.recoverable_cost := l_book_info_rec.recoverable_cost;
2352 l_hist_info.date_placed_in_service := l_book_info_rec.date_placed_in_service;
2353 l_hist_info.deprn_start_date := l_book_info_rec.deprn_start_date;
2354 l_hist_info.depreciate_flag := l_book_info_rec.depreciate_flag;
2355
2356 --get latest period counter and deprn_resereve for the asset
2357
2358 /*Open C_get_deprn_details(l_corporate_book,arec.asset_id);
2359 fetch C_get_deprn_details into l_corp_last_per_counter,l_corp_deprn_summary;
2360 close c_get_deprn_details;*/
2361
2362 l_hist_info.last_period_counter := l_corp_last_per_counter;
2363 l_hist_info.gl_posting_allowed_flag := NULL;
2364 l_hist_info.ytd_deprn := 0;
2365 l_hist_info.deprn_reserve :=arec.accum_deprn_hist;
2366 l_hist_info.deprn_amount := 0;
2367 l_corp_last_per_counter:=l_prd_rec.period_counter-1;
2368 l_dpis_period_counter:=null;
2369
2370 IF NOT igi_iac_ytd_engine.Calculate_YTD
2371 ( l_corporate_book,
2372 arec.asset_id,
2373 l_hist_info,
2374 l_dpis_period_counter,
2375 l_corp_last_per_counter,
2376 'UPGRADE') THEN
2377
2378 fnd_message.set_name ('IGI', 'IGI_IMP_IAC_PREP_ERROR');
2379 fnd_message.set_token('ROUTINE','igi_iac_ytd_engine.Calculate_YTD');
2380 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2381 p_full_path => g_path,
2382 p_remove_from_stack => FALSE);
2383 errbuf := fnd_message.get;
2384 Raise IGI_IMP_TFR_ERROR ;
2385 END IF;
2386 l_hist_deprn_ytd:=l_hist_info.ytd_deprn;
2387 l_hist_deprn_amount:=l_hist_info.deprn_amount;
2388 l_hist_deprn_amount_sal_corr:=l_hist_deprn_amount;
2389
2390 IF ( NOT l_hist_info.salvage_value is Null) or (NOT l_hist_info.salvage_value=0) THEN
2391 IF NOT igi_iac_salvage_pkg.correction(arec.asset_id,
2392 l_corporate_book,
2393 l_hist_deprn_amount_sal_corr,
2394 l_hist_info.cost,
2395 l_hist_info.salvage_value,
2396 P_calling_program=>'IMPLEMENTATTION') THEN
2397
2398 fnd_message.set_token('ROUTINE','igi_iac_salvage_pkg.correction');
2399 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2400 p_full_path => g_path,
2401 p_remove_from_stack => FALSE);
2402 errbuf := fnd_message.get;
2403 Raise IGI_IMP_TFR_ERROR ;
2404 END IF;
2405 END IF;
2406 l_deprn_amount:= l_hist_deprn_amount_sal_corr*(l_cumulative_reval_factor-1);
2407 l_ytd_deprn := l_deprn_amount * l_hist_Info.deprn_periods_current_year;
2408 End If;
2409
2410 --
2411 -- Round Values
2412 --
2413 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_net_book_value ,
2414 l_corporate_book )) THEN
2415 null;
2416 END IF;
2417 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_adjusted_cost ,
2418 l_corporate_book )) THEN
2419 null;
2420 END IF;
2421 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_operating_acct ,
2422 l_corporate_book )) THEN
2423 null;
2424 END IF;
2425 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_reval_reserve ,
2426 l_corporate_book )) THEN
2427 null;
2428 END IF;
2429 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_deprn_amount ,
2430 l_corporate_book )) THEN
2431 null;
2432 END IF;
2433 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_ytd_deprn ,
2434 l_corporate_book )) THEN
2435 null;
2436 END IF;
2437 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_deprn_reserve ,
2438 l_corporate_book )) THEN
2439 null;
2440 END IF;
2441 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_backlog_deprn_reserve ,
2442 l_corporate_book )) THEN
2443 null;
2444 END IF;
2445 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_general_fund ,
2446 l_corporate_book )) THEN
2447 null;
2448 END IF;
2449 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_hist_deprn_amount ,
2450 l_corporate_book )) THEN
2451 null;
2452 END IF;
2453 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_hist_deprn_ytd ,
2454 l_corporate_book )) THEN
2455 null;
2456 END IF;
2457 IF NOT( Igi_Iac_Common_Utils.Iac_Round ( l_hist_deprn_reserve ,
2458 l_corporate_book )) THEN
2459 null;
2460 END IF;
2461
2462 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2463 p_full_path => g_path||'transfer_data',
2464 p_string => 'Creating row in igi_iac_asset_balances for closed period ...');
2465
2466 -- Create row in igi_iac_asset_balances for closed period
2467 --
2468 igi_iac_asset_balances_pkg.insert_row (
2469 x_rowid => l_out_rowid ,
2470 x_asset_id => arec.asset_id ,
2471 x_book_type_code => l_corporate_book ,
2472 x_period_counter => l_prev_prd_rec.period_counter ,
2473 x_net_book_value => l_net_book_value ,
2474 x_adjusted_cost => l_adjusted_cost ,
2475 x_operating_acct => l_operating_acct ,
2476 x_reval_reserve => l_reval_reserve ,
2477 x_deprn_amount => l_deprn_amount ,
2478 x_deprn_reserve => l_deprn_reserve ,
2479 x_backlog_deprn_reserve => l_backlog_deprn_reserve ,
2480 x_general_fund => l_general_fund ,
2481 x_last_reval_date => l_prev_prd_rec.period_end_date ,
2482 x_current_reval_factor => l_current_reval_factor ,
2483 x_cumulative_reval_factor => l_cumulative_reval_factor ,
2484 x_mode => 'R'
2485 );
2486
2487
2488 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2489 p_full_path => g_path||'transfer_data',
2490 p_string => 'Creating row in igi_iac_asset_balances for current period ...');
2491 -- Create row in igi_iac_asset_balances
2492 --
2493 igi_iac_asset_balances_pkg.insert_row (
2494 x_rowid => l_out_rowid ,
2495 x_asset_id => arec.asset_id ,
2496 x_book_type_code => l_corporate_book ,
2497 x_period_counter => l_prd_rec.period_counter ,
2498 x_net_book_value => l_net_book_value ,
2499 x_adjusted_cost => l_adjusted_cost ,
2500 x_operating_acct => l_operating_acct ,
2501 x_reval_reserve => l_reval_reserve ,
2502 x_deprn_amount => l_deprn_amount ,
2503 x_deprn_reserve => l_deprn_reserve ,
2504 x_backlog_deprn_reserve => l_backlog_deprn_reserve ,
2505 x_general_fund => l_general_fund ,
2506 x_last_reval_date => l_prd_rec.period_end_date ,
2507 x_current_reval_factor => l_current_reval_factor ,
2508 x_cumulative_reval_factor => l_cumulative_reval_factor ,
2509 x_mode => 'R'
2510 );
2511
2512 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2513 p_full_path => g_path||'transfer_data',
2514 p_string => ' Creating rows in igi_iac_det_balances for closed period...');
2515 -- Create rows in igi_iac_det_balances for closed period
2516 --
2517 IF ( NOT ( Prorate_for_Det_Balances (
2518 p_book_type_code => l_corporate_book ,
2519 p_category_id => p_category_id ,
2520 p_asset_id => arec.asset_id ,
2521 p_net_book_value => l_net_book_value ,
2522 p_adjusted_cost => l_adjusted_cost ,
2523 p_operating_acct => l_operating_acct ,
2524 p_reval_reserve => l_reval_reserve ,
2525 p_deprn_amount => l_deprn_amount ,
2526 p_ytd_deprn => l_ytd_deprn ,
2527 p_deprn_reserve => l_deprn_reserve ,
2528 p_backlog_deprn_reserve => l_backlog_deprn_reserve ,
2529 p_general_fund => l_general_fund ,
2530 p_prd_rec => l_prev_prd_rec ,
2531 p_current_reval_factor => l_current_reval_factor ,
2532 p_cumulative_reval_factor => l_cumulative_reval_factor ,
2533 p_adj_id => l_prev_out_adj_id ,
2534 p_run_book => p_book_type_code ,
2535 p_op_ac_cost => arec.operating_account_cost ,
2536 p_op_ac_backlog => arec.operating_account_backlog ,
2537 p_hist_deprn_amount => l_hist_deprn_amount,
2538 p_hist_deprn_ytd => l_hist_deprn_ytd,
2539 p_hist_deprn_reserve => l_hist_deprn_reserve,
2540 p_errbuf => errbuf )))
2541 THEN
2542 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2543 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2544 p_full_path => g_path||'transfer_data',
2545 p_remove_from_stack => FALSE);
2546 errbuf := fnd_message.get;
2547 fnd_file.put_line(fnd_file.log, errbuf);
2548 raise igi_imp_tfr_error ;
2549 END IF;
2550
2551
2552 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2553 p_full_path => g_path||'transfer_data',
2554 p_string => ' Creating rows in igi_iac_det_balances ...');
2555 -- Create rows in igi_iac_det_balances
2556 --
2557 IF ( NOT ( Prorate_for_Det_Balances (
2558 p_book_type_code => l_corporate_book ,
2559 p_category_id => p_category_id ,
2560 p_asset_id => arec.asset_id ,
2561 p_net_book_value => l_net_book_value ,
2562 p_adjusted_cost => l_adjusted_cost ,
2563 p_operating_acct => l_operating_acct ,
2564 p_reval_reserve => l_reval_reserve ,
2565 p_deprn_amount => l_deprn_amount ,
2566 p_ytd_deprn => l_ytd_deprn ,
2567 p_deprn_reserve => l_deprn_reserve ,
2568 p_backlog_deprn_reserve => l_backlog_deprn_reserve ,
2569 p_general_fund => l_general_fund ,
2570 --p_prd_rec => l_prd_rec , modified for bug 10339584
2571 p_prd_rec =>l_prev_prd_rec,
2572 p_current_reval_factor => l_current_reval_factor ,
2573 p_cumulative_reval_factor => l_cumulative_reval_factor ,
2574 p_adj_id => l_out_adj_id ,
2575 p_run_book => p_book_type_code ,
2576 p_op_ac_cost => arec.operating_account_cost ,
2577 p_op_ac_backlog => arec.operating_account_backlog ,
2578 p_hist_deprn_amount => l_hist_deprn_amount,
2579 p_hist_deprn_ytd => l_hist_deprn_ytd,
2580 p_hist_deprn_reserve => l_hist_deprn_reserve,
2581 p_errbuf => errbuf )))
2582 THEN
2583 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2584 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2585 p_full_path => g_path||'transfer_data',
2586 p_remove_from_stack => FALSE);
2587 errbuf := fnd_message.get;
2588 fnd_file.put_line(fnd_file.log, errbuf);
2589 raise igi_imp_tfr_error ;
2590 END IF;
2591
2592
2593 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2594 p_full_path => g_path||'transfer_data',
2595 p_string => 'Creating rows in igi_iac_adjustments ...');
2596 -- Create rows in igi_iac_adjustments
2597 --
2598
2599 IF ( NOT ( Create_Adjustments (
2600 l_corporate_book ,
2601 arec.asset_id ,
2602 l_out_adj_id ,
2603 l_prev_prd_rec.period_counter ,
2604 errbuf ,
2605 p_event_id ))) THEN
2606 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2607 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2608 p_full_path => g_path||'transfer_data',
2609 p_remove_from_stack => FALSE);
2610 errbuf := fnd_message.get;
2611 fnd_file.put_line(fnd_file.log, errbuf);
2612 raise igi_imp_tfr_error ;
2613 END IF;
2614
2615 IF ( p_book_type_code = l_corporate_book ) THEN
2616 BEGIN
2617 SELECT number_per_fiscal_year
2618 INTO l_num_per_fiscal_year
2619 FROM fa_calendar_types ct ,
2620 fa_book_controls bc
2621 WHERE ct.calendar_type = bc.deprn_calendar
2622 AND bc.book_type_code = l_corporate_book ;
2623 EXCEPTION
2624 WHEN OTHERS THEN
2625 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2626 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2627 p_full_path => g_path||'transfer_data',
2628 p_remove_from_stack => FALSE);
2629 errbuf := fnd_message.get;
2630 fnd_file.put_line(fnd_file.log, errbuf);
2631 raise igi_imp_tfr_error ;
2632 END ;
2633 BEGIN
2634 SELECT period_num_for_catchup
2635 INTO l_period_num_for_catchup
2636 FROM igi_iac_book_controls ib
2637 WHERE ib.book_type_code = l_corporate_book ;
2638 EXCEPTION
2639 WHEN OTHERS THEN
2640 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2641 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2642 p_full_path => g_path||'transfer_data',
2643 p_remove_from_stack => FALSE);
2644 errbuf := fnd_message.get;
2645 fnd_file.put_line(fnd_file.log, errbuf);
2646 raise igi_imp_tfr_error ;
2647 END ;
2648
2649 IF ( l_prev_prd_rec.period_num < l_period_num_for_catchup ) THEN
2650 l_period_for_rates := l_prev_prd_rec.period_counter -
2651 ( l_num_per_fiscal_year + l_prev_prd_rec.period_num - l_period_num_for_catchup ) ;
2652 ELSIF ( l_prev_prd_rec.period_num > l_period_num_for_catchup ) THEN
2653 l_period_for_rates := l_prev_prd_rec.period_counter -
2654 ( l_prev_prd_rec.period_num - l_period_num_for_catchup ) ;
2655 ELSIF ( l_prev_prd_rec.period_num = l_period_num_for_catchup ) THEN
2656 l_period_for_rates := l_prev_prd_rec.period_counter ;
2657 END IF;
2658 ELSE
2659 --
2660 -- IF this was a tax book then the revaluation will have been run till the last closed period
2661 --
2662 l_period_for_rates := l_prev_prd_rec.period_counter ;
2663 END IF;
2664
2665
2666 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2667 p_full_path => g_path||'transfer_data',
2668 p_string => 'Creating rows in igi_iac_revaluation_rates ...');
2669 -- Create rows in igi_iac_revaluation_rates
2670 --
2671 BEGIN
2672 INSERT INTO igi_iac_revaluation_rates
2673 (
2674 ASSET_ID ,
2675 BOOK_TYPE_CODE ,
2676 REVALUATION_ID ,
2677 PERIOD_COUNTER ,
2678 REVAL_TYPE ,
2679 CURRENT_REVAL_FACTOR ,
2680 CUMULATIVE_REVAL_FACTOR ,
2681 PROCESSED_FLAG ,
2682 LATEST_RECORD ,
2683 CREATED_BY ,
2684 CREATION_DATE ,
2685 LAST_UPDATE_LOGIN ,
2686 LAST_UPDATE_DATE ,
2687 LAST_UPDATED_BY ,
2688 ADJUSTMENT_ID
2689 )
2690 VALUES
2691 (
2692 arec.asset_id ,
2693 l_corporate_book ,
2694 l_out_reval_id ,
2695 l_period_for_rates ,
2696 'P' ,
2697 l_current_reval_factor ,
2698 l_cumulative_reval_factor ,
2699 'Y' ,
2700 'Y' ,
2701 fnd_global.user_id ,
2702 sysdate ,
2703 fnd_global.login_id ,
2704 sysdate ,
2705 fnd_global.user_id ,
2706 l_out_adj_id
2707 );
2708 EXCEPTION
2709 WHEN OTHERS THEN
2710 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2711 p_full_path => g_path||'transfer_data',
2712 p_string => 'Error : Creating rows in igi_iac_revaluation_rates ...'
2713 ||sqlerrm);
2714 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2715 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2716 p_full_path => g_path||'transfer_data',
2717 p_remove_from_stack => FALSE);
2718 errbuf := fnd_message.get;
2719 fnd_file.put_line(fnd_file.log, errbuf);
2720 raise igi_imp_tfr_error ;
2721 END;
2722
2723
2724 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2725 p_full_path => g_path||'transfer_data',
2726 p_string => '>>Processed Asset ID : '|| arec.asset_id ) ;
2727
2728 UPDATE igi_imp_iac_interface --Fix for Bug 5137813
2729 SET transferred_flag = 'Y'
2730 WHERE book_type_code = arec.book_type_code
2731 AND asset_id = arec.asset_id;
2732
2733 END LOOP ;
2734
2735 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2736 p_full_path => g_path||'transfer_data',
2737 p_string => 'Updating igi_iac_category_books run details...');
2738 -- Updating igi_iac_category_books run details...
2739 --
2740 UPDATE igi_iac_category_books c
2741 SET c.imp_run_number = nvl(c.imp_run_number ,0) + 1 ,
2742 c.imp_period_counter = l_prd_rec.period_counter ,
2743 c.imp_date = sysdate
2744 WHERE c.book_type_code = l_corporate_book
2745 AND c.category_id = p_category_id ;
2746
2747 IF ( SQL%ROWCOUNT = 0 ) THEN
2748 igi_iac_debug_pkg.debug_other_string(p_level => g_error_level,
2749 p_full_path => g_path||'transfer_data',
2750 p_string => ' ERROR -> Updating igi_iac_category_books ...'|| sqlerrm );
2751 fnd_message.set_name ('IGI','IGI_IMP_IAC_TRF_GENERIC_ERROR');
2752 igi_iac_debug_pkg.debug_other_msg(p_level => g_error_level,
2753 p_full_path => g_path||'transfer_data',
2754 p_remove_from_stack => FALSE);
2755 errbuf := fnd_message.get;
2756 fnd_file.put_line(fnd_file.log, errbuf);
2757 raise igi_imp_tfr_error ;
2758 ELSE
2759 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2760 p_full_path => g_path||'transfer_data',
2761 p_string => SQL%rowcount || ' rows updated.');
2762 END IF;
2763
2764
2765 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2766 p_full_path => g_path||'transfer_data',
2767 p_string => 'Setting Transfer Status to Completed ...');
2768
2769 IF (l_assets_valid) THEN --Fix for Bug 5137813
2770 -- Update the transferred status to COMPLETED 'C'
2771 set_interface_ctrl_status( p_book_type_code ,
2772 p_category_id ,
2773 'C' );
2774
2775 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2776 p_full_path => g_path||'transfer_data',
2777 p_string => '*********** Transfer Successfully Completed ... **********');
2778
2779 retcode := 0 ;
2780
2781 ELSE
2782 set_interface_ctrl_status( p_book_type_code ,
2783 p_category_id ,
2784 'N' );
2785 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2786 p_full_path => g_path||'transfer_data',
2787 p_string => '*********** Transfer Partially Completed ... **********');
2788
2789 retcode := 1 ;
2790
2791 END IF;
2792
2793 COMMIT WORK;
2794
2795 igi_iac_debug_pkg.debug_other_string(p_level => g_state_level,
2796 p_full_path => g_path||'transfer_data',
2797 p_string => '*********** Transfer Successfully Completed ... **********');
2798
2799
2800 RETURN ;
2801
2802 EXCEPTION
2803 WHEN igi_imp_tfr_error THEN
2804 ROLLBACK WORK ;
2805 IF ( l_reserved_flag = 'Y' ) THEN
2806 set_interface_ctrl_status( p_book_type_code ,
2807 p_category_id ,
2808 'E' );
2809 COMMIT WORK;
2810 END IF;
2811 retcode := 2 ;
2812 RETURN ;
2813 WHEN OTHERS THEN
2814 ROLLBACK WORK ;
2815 igi_iac_debug_pkg.debug_unexpected_msg(p_full_path => g_path||'transfer_data');
2816 IF ( l_reserved_flag = 'Y' ) THEN
2817 set_interface_ctrl_status( p_book_type_code ,
2818 p_category_id ,
2819 'E' );
2820 COMMIT WORK;
2821 END IF;
2822 retcode := 0 ;
2823 RETURN ;
2824
2825
2826 END ; -- procedure transfer_data
2827
2828 BEGIN
2829 --===========================FND_LOG.START=====================================
2830
2831 g_state_level := FND_LOG.LEVEL_STATEMENT;
2832 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
2833 g_event_level := FND_LOG.LEVEL_EVENT;
2834 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
2835 g_error_level := FND_LOG.LEVEL_ERROR;
2836 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
2837 g_path := 'IGI.PLSQL.igiimtdb.igi_imp_iac_transfer_pkg.';
2838 --===========================FND_LOG.END=====================================
2839
2840
2841 END ; --package body