[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_FA_DEPRN_ADJ_PKG
Source
1 PACKAGE BODY jl_zz_fa_deprn_adj_pkg AS
2 /* $Header: jlzzfdab.pls 120.8 2006/09/20 17:05:04 abuissa ship $ */
3
4 /* ======================================================================*
5 | FND Logging infrastructure |
6 * ======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_ZZ_FA_DEPRN_ADJ_PKG';
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
10 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
11 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
12 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
13 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
14 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
15 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'JL.PLSQL.JL_ZZ_FA_DEPRN_ADJ_PKG.';
16
17 /*+=========================================================================+
18 | PUBLIC PROCEDURE |
19 | deprn_adj_ret_assets |
20 | p_book_type_code Book Type Code |
21 | |
22 | NOTES |
23 | Once the asset is retired, journal entries are posted to reverse the |
24 | accumulated depreciation. The inflation adjusted depreciation account |
25 | remains unchanged until the end of the fiscal year, when it is used to |
26 | calculate the FY's result and then its balance is zeroed. But that |
27 | balance is not in constant units of money to the time of the FY's end, |
28 | so we must adjust it for inflation during that FY's periods. |
29 | |
30 +=========================================================================+*/
31 PROCEDURE deprn_adj_ret_assets (errbuf OUT NOCOPY VARCHAR2
32 , retcode OUT NOCOPY VARCHAR2
33 , p_book_type_code IN VARCHAR2) IS
34
35 ------------------------------------------------------------
36 -- Procedure Global Variables --
37 ------------------------------------------------------------
38 g_current_period_to_date1 DATE;
39 g_current_period_from_date1 DATE;
40 g_current_period_to_date2 DATE;
41 g_current_period_from_date2 DATE;
42 g_previous_period_to_date DATE;
43 g_current_fiscal_year NUMBER(4);
44 g_current_month_number NUMBER(3);
45 g_previous_period_counter NUMBER(15);
46 g_set_of_books_id NUMBER(15);
47 g_chart_of_accounts_id NUMBER(15);
48 g_currency_code VARCHAR2(15);
49 g_curr_precision NUMBER;
50 g_step VARCHAR2(30);
51 g_user_je_category_name GL_INTERFACE.USER_JE_CATEGORY_NAME%TYPE;
52 g_user_je_source_name GL_INTERFACE.USER_JE_SOURCE_NAME%TYPE;
53 g_calendar_type FA_CALENDAR_TYPES.CALENDAR_TYPE%TYPE;
54 g_je_retirement_category FA_BOOK_CONTROLS.JE_RETIREMENT_CATEGORY%TYPE;
55 g_last_period_counter FA_BOOK_CONTROLS.LAST_PERIOD_COUNTER%TYPE;
56 g_distribution_source_book FA_BOOK_CONTROLS.DISTRIBUTION_SOURCE_BOOK%TYPE;
57 g_reserve_acct NUMBER(15);
58 g_ccid_deprn_exp_co VARCHAR2(150);
59 g_ccid_deprn_mon_co VARCHAR2(150);
60 g_gl_je_source XLA_SUBLEDGERS.JE_SOURCE_NAME%TYPE; -- Bug 5136047
61 g_conc_segs VARCHAR2(2000);
62 g_period_name FA_DEPRN_PERIODS.PERIOD_NAME%TYPE;
63 g_precision NUMBER;
64 g_period_counter1 FA_DEPRN_PERIODS.PERIOD_COUNTER%TYPE;
65 g_period_counter2 FA_DEPRN_PERIODS.PERIOD_COUNTER%TYPE;
66 g_number_per_fy NUMBER := 0;
67 x_country_code VARCHAR2(2);
68 x_last_updated_by NUMBER(15);
69 x_created_by NUMBER(15);
70 x_last_update_login NUMBER(15);
71 x_request_id NUMBER(15);
72 x_program_application_id NUMBER(15);
73 x_program_id NUMBER(15);
74 g_period_num NUMBER := 0;
75 x_sysdate DATE;
76 call_status BOOLEAN;
77 err_num NUMBER;
78 err_msg VARCHAR2(200);
79 x_char VARCHAR2 (200);
80
81
82 ------------------------------------------------------------
83 -- Procedure: get_who_columns --
84 -- --
85 -- Get values for who columns --
86 ------------------------------------------------------------
87 PROCEDURE get_who_columns IS
88
89 l_api_name CONSTANT VARCHAR2(30) := 'GET_WHO_COLUMNS';
90
91 BEGIN
92
93 x_last_updated_by := fnd_global.user_id;
94 x_created_by := fnd_global.user_id;
95 x_last_update_login := fnd_global.login_id;
96 x_request_id := fnd_global.conc_request_id;
97 x_program_application_id := fnd_global.prog_appl_id;
98 x_program_id := fnd_global.conc_program_id;
99 x_sysdate := SYSDATE;
100
101 -------------------------------------------------------------------------
102 -- BUG 4650081. Profile for country is replaced by call to JG Shared pkg.
103 -------------------------------------------------------------------------
104 x_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY;
105 FND_PROFILE.GET('JLZZ_INF_RATIO_PRECISION',g_precision);
106
107
108 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
109 x_char := 'Start Debugging';
110 fnd_file.put_line (FND_FILE.LOG, x_char);
111 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
112 x_char := ' ';
113 fnd_file.put_line (FND_FILE.LOG, x_char);
114 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
115 x_char := 'Country :'||x_country_code;
116 fnd_file.put_line (FND_FILE.LOG, x_char);
117 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
118 x_char := 'Precision :'||g_precision;
119 fnd_file.put_line (FND_FILE.LOG, x_char);
120 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
121 END IF;
122
123 IF g_precision IS NULL OR g_precision = 0 THEN
124 g_precision := 38;
125 END IF;
126
127 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
128 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
129 END IF;
130
131 END get_who_columns;
132
133
134 ------------------------------------------------------------
135 -- Procedure: get_details --
136 -- --
137 -- Get all the book related information needed to --
138 -- calculate the revaluation rates. --
139 ------------------------------------------------------------
140
141 PROCEDURE get_details IS
142
143 l_api_name CONSTANT VARCHAR2(30) := 'GET_DETAILS';
144
145 BEGIN
146
147 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
148 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
149 END IF;
150
151
152 g_step := 'BOOK INFO';
153
154 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
155 x_char := ' ';
156 fnd_file.put_line (FND_FILE.LOG, x_char);
157 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
158 x_char := 'Procedure get_details';
159 fnd_file.put_line (FND_FILE.LOG, x_char);
160 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
161 END IF;
162
163 ------------------------------------------------------------
164 -- Details for book given --
165 ------------------------------------------------------------
166
167 /* Bug 5136047:
168 In R12 due to SLA uptake, core FA no longer allows "GL journal entry source"
169 to be entered using Book Controls window.
170 "GL journal entry source" is set at the application level in SLA.
171 Though, gl_je_source value is available in fa_book_controls
172 for books upgraded from R11i, the source of truth is always as follows.
173 The "GL journal entry source" will be derived from JE_SOURCE_NAME from
174 XLA_SUBLEDGERS for application_id = 140.
175 Hence, there is no need to look at fa_book_controls for books upgraded
176 from R1i.
177
178 SELECT a.set_of_books_id ,
179 a.global_attribute6,
180 a.gl_je_source,
181 b.currency_code,
182 b.chart_of_accounts_id,
183 a.deprn_calendar,
184 a.last_period_counter,
185 a.distribution_source_book
186 INTO g_set_of_books_id,
187 g_je_retirement_category,
188 g_gl_je_source,
189 g_currency_code,
190 g_chart_of_accounts_id,
191 g_calendar_type,
192 g_last_period_counter,
193 g_distribution_source_book
194 FROM fa_book_controls a,
195 gl_sets_of_books b
196 WHERE a.book_type_code = p_book_type_code
197 AND a.set_of_books_id = b.set_of_books_id;
198 */
199
200 SELECT a.set_of_books_id ,
201 a.global_attribute6,
202 b.currency_code,
203 b.chart_of_accounts_id,
204 a.deprn_calendar,
205 a.last_period_counter,
206 a.distribution_source_book
207 INTO g_set_of_books_id,
208 g_je_retirement_category,
209 g_currency_code,
210 g_chart_of_accounts_id,
211 g_calendar_type,
212 g_last_period_counter,
213 g_distribution_source_book
214 FROM fa_book_controls a,
215 gl_sets_of_books b
216 WHERE a.book_type_code = p_book_type_code
217 AND a.set_of_books_id = b.set_of_books_id;
218
219 SELECT xs.je_source_name,
220 js.user_je_source_name
221 INTO g_gl_je_source,
222 g_user_je_source_name
223 FROM gl_je_sources js,
224 xla_subledgers xs
225 WHERE js.je_source_name = xs.je_source_name
226 AND xs.application_id = 140;
227
228 -- Get currency precision
229
230 SELECT precision
231 INTO g_curr_precision
232 FROM fnd_currencies_vl
233 WHERE UPPER(currency_code) = UPPER(g_currency_code);
234
235
236 ------------------------------------------------------------
237 -- Next two selects bring category y source name to be --
238 -- inserted in gl_interface table. --
239 ------------------------------------------------------------
240
241
242 SELECT user_je_category_name
243 INTO g_user_je_category_name
244 FROM gl_je_categories
245 WHERE je_category_name = g_je_retirement_category;
246
247 ------------------------------------------------------------
248 -- Fetches the depreciation parameters for the current --
249 -- period. --
250 ------------------------------------------------------------
251 g_step := 'CURR DPRN INFO';
252
253
254 SELECT a.calendar_period_open_date,
255 a.calendar_period_close_date,
256 a.fiscal_year,
257 a.period_num,
258 a.period_counter,
259 a.period_name
260 INTO
261 g_current_period_from_date1,
262 g_current_period_to_date1,
263 g_current_fiscal_year,
264 g_current_month_number,
265 g_period_counter1,
266 g_period_name
267 FROM fa_deprn_periods a
268 WHERE a.book_type_code = p_book_type_code
269 AND a.period_counter = g_last_period_counter;
270
271
272
273 SELECT number_per_fiscal_year
274 INTO g_number_per_fy
275 FROM fa_calendar_types
276 WHERE calendar_type = g_calendar_type;
277
278
279 ------------------------------------------------------------
280 -- If country is not Chili then variables g_period_counter2--
281 -- and g_period_counter1 should contain the same value. --
282 ------------------------------------------------------------
283
284
285 g_period_counter2 := g_period_counter1;
286 g_current_period_to_date2 := g_current_period_to_date1;
287 g_current_period_from_date2 := g_current_period_from_date1;
288
289 ------------------------------------------------------------
290 -- If country_code = 'CL' (Chili) then the current period --
291 -- becomes the previous one and so on for previous period --
292 -- information. --
293 ------------------------------------------------------------
294
295 IF x_country_code = 'CL' THEN
296
297
298 g_period_counter2 := g_period_counter1 - 1;
299
300 SELECT period_num
301 INTO g_period_num
302 FROM fa_calendar_periods
303 WHERE calendar_type = g_calendar_type
304 AND start_date = g_current_period_from_date1;
305
306 SELECT start_date,end_date
307 INTO g_current_period_from_date2,
308 g_current_period_to_date2
309 FROM fa_calendar_periods
310 WHERE calendar_type = g_calendar_type
311 AND period_num = decode(g_period_num,1,g_number_per_fy,g_period_num-1)
312 AND end_date = g_current_period_from_date1 - 1;
313
314
315 END IF;
316
317
318 g_previous_period_counter := g_period_counter1 - 1;
319 g_previous_period_to_date := g_current_period_from_date2 - 1;
320
321 ------------------------------------------------------------
322 -- Following commands will write the ouput report heading.--
323 ------------------------------------------------------------
324
325 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',132,'-'));
326 fnd_file.new_line(FND_FILE.OUTPUT,1);
327 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_HEAD');
328 fnd_file.put_line( FND_FILE.OUTPUT,LPAD(' ',27,' ')||RPAD(fnd_message.get,87,' ')||to_char(SYSDATE,'DD-MM-YYYY HH:MI'));
329 fnd_message.set_name('JL', 'JL_CO_FA_BOOK');
330 fnd_message.set_token('BOOK', p_book_type_code);
331 fnd_file.put_line( FND_FILE.OUTPUT, fnd_message.get);
332 fnd_message.set_name('JL', 'JL_ZZ_FA_PERIOD_NAME');
333 fnd_message.set_token('PERIOD_NAME', g_period_name);
334 fnd_file.put_line( FND_FILE.OUTPUT, fnd_message.get);
335 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',132,'-'));
336 fnd_file.new_line(FND_FILE.OUTPUT,1);
337
338
339 ------------------------------------------------------------
340 -- End of report heading --
341 ------------------------------------------------------------
342
343 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
344
345 x_char := 'Period counter :'||TO_CHAR(g_period_counter1);
346 fnd_file.put_line (FND_FILE.LOG, x_char);
347 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
348 x_char := 'Period Name :'||g_period_name;
349 fnd_file.put_line (FND_FILE.LOG, x_char);
350 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
351
352 IF x_country_code = 'CL' THEN
353 x_char := 'Period counter to get indexes :'||TO_CHAR(g_period_counter2);
354 fnd_file.put_line (FND_FILE.LOG, x_char);
355 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
356 END IF;
357
358 x_char := ' ';
359 fnd_file.put_line (FND_FILE.LOG, x_char);
360 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
361 x_char := 'End of procedure get_details';
362 fnd_file.put_line (FND_FILE.LOG, x_char);
363 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
364 END IF;
365
366 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
367 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
368 END IF;
369
370 END get_details;
371
372 ------------------------------------------------------------
373 -- Procedure: format_amount_out --
374 -- Formats the amount given acording to the precision to --
375 -- be shown in the output report. --
376 ------------------------------------------------------------
377
378 PROCEDURE format_amount_out ( num_amount IN NUMBER,
379 char_amount OUT NOCOPY VARCHAR2) IS
380
381 tmp_amnt varchar(20);
382 result varchar2(100);
383 BEGIN
384 if g_curr_precision <= 0 then
385 SELECT TO_CHAR(round(num_amount,g_curr_precision))
386 INTO char_amount
387 FROM DUAL;
388 else
389 SELECT TO_CHAR(ROUND(num_amount,g_curr_precision))
390 INTO tmp_amnt
391 FROM DUAL;
392
393 select rpad(tmp_amnt,
394 decode(sign(length(tmp_amnt)+g_curr_precision-(length(tmp_amnt) - instr(tmp_amnt,'.')))
395 ,-1,length(tmp_amnt)
396 ,0 ,length(tmp_amnt)
397 ,length(tmp_amnt) + g_curr_precision - (length(tmp_amnt) -
398 decode(instr(tmp_amnt,'.'),0,1,instr(tmp_amnt,'.')))),'0')
399 into char_amount
400 from dual;
401 end if;
402
403
404 END format_amount_out;
405
406 ------------------------------------------------------------
407 -- Procedure: get_segs --
408 -- Gets the concatenated segment values for the key flex --
409 -- field combination. --
410 ------------------------------------------------------------
411 PROCEDURE get_segs ( p_dist_ccid in NUMBER) IS
412 BEGIN
413
414 g_conc_segs := FND_FLEX_EXT.GET_SEGS ('SQLGL',
415 'GL#',
416 g_chart_of_accounts_id,
417 p_dist_ccid);
418 SELECT rpad(g_conc_segs,45,' ')
419 INTO g_conc_segs
420 FROM DUAL;
421
422 END get_segs;
423
424
425 ------------------------------------------------------------
426 -- Procedure: get_price_index_rate --
427 -- --
428 -- Gets the index value for a certain price index and a --
429 -- particular date. --
430 ------------------------------------------------------------
431 PROCEDURE get_price_index_rate (p_index_id IN NUMBER
432 , p_period_date IN DATE
433 , p_index_value IN OUT NOCOPY NUMBER) IS
434 BEGIN
435 SELECT price_index_value
436 INTO p_index_value
437 FROM fa_price_index_values
438 WHERE price_index_id = p_index_id
439 AND p_period_date BETWEEN from_date AND to_date;
440
441 END get_price_index_rate;
442
443
444 ------------------------------------------------------------
445 -- Procedure: get_index_rate --
446 -- --
447 -- Gets the price index associated to the category --
448 -- Bug 1420414 : Changed the query in the procedure --
449 -- "get_index_rate" to cater for the situation --
450 -- where different price indexes are defined for --
451 -- different ranges of date placed in service. --
452 -- Bug 1488156 : Changed the query in the procedure --
453 -- "get_index_rate" to deal with Items with --
454 -- date_place_of_service is ahead of system time --
455 ------------------------------------------------------------
456 PROCEDURE get_index_rate(p_current_category IN VARCHAR2,
457 p_date_placed_in_service IN DATE,
458 p_adjustment_rate IN OUT NOCOPY NUMBER) IS
459
460 l_price_index fa_price_indexes.price_index_id%TYPE;
461 l_current_index_value NUMBER := 0;
462 l_previous_index_value NUMBER:= 0;
463 l_api_name CONSTANT VARCHAR2(30) := 'GET_INDEX_RATE';
464
465 --
466 BEGIN
467 ------------------------------------------------------------
468 -- Fetch the index for the book type and category --
469 ------------------------------------------------------------
470 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
471 x_char := ' ';
472 fnd_file.put_line (FND_FILE.LOG, x_char);
473 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
474 x_char := 'Procedure get_index_rate';
475 fnd_file.put_line (FND_FILE.LOG, x_char);
476 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
477 x_char := ' ';
478 fnd_file.put_line (FND_FILE.LOG, x_char);
479 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
480 x_char := 'Getting index for Category :'||p_current_category;
481 fnd_file.put_line (FND_FILE.LOG, x_char);
482 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
483 END IF;
484
485
486 SELECT b.price_index_id
487 INTO l_price_index
488 FROM fa_category_book_defaults a, fa_price_indexes b
489 WHERE a.book_type_code = p_book_type_code
490 AND a.category_id = p_current_category
491 AND p_date_placed_in_service >= a.start_dpis
492 AND p_date_placed_in_service <= NVL(a.end_dpis,p_date_placed_in_service)
493 AND a.price_index_name = b.price_index_name;
494
495
496
497 get_price_index_rate(l_price_index,g_current_period_to_date2,
498 l_current_index_value);
499 get_price_index_rate(l_price_index,g_previous_period_to_date,
500 l_previous_index_value);
501
502
503 p_adjustment_rate := trunc((l_current_index_value / l_previous_index_value),g_precision);
504
505
506 g_step := '';
507 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
508 x_char := ' ';
509 fnd_file.put_line (FND_FILE.LOG, x_char);
510 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
511 x_char := 'End of procedure get_index_rate';
512 fnd_file.put_line (FND_FILE.LOG, x_char);
513 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
514 END IF;
515
516 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
517 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
518 END IF;
519
520 END get_index_rate;
521
522 ------------------------------------------------------------
523 -- Procedure: current_period_retirements --
524 -- --
525 ------------------------------------------------------------
526 PROCEDURE current_period_retirements IS
527
528
529 CURSOR fa_ret IS
530 SELECT a.retirement_id,a.asset_id asset_id,
531 b.transaction_header_id transaction_header_id,
532 rpad(c.asset_number||'-'||substr(c.description,1,30),45,' ') asset_desc
533 FROM fa_books d,
534 fa_additions c,
535 fa_transaction_headers b,
536 fa_retirements a
537 WHERE a.book_type_code = p_book_type_code
538 AND a.transaction_header_id_in = b.transaction_header_id
539 AND b.transaction_date_entered BETWEEN g_current_period_from_date1
540 AND g_current_period_to_date1
541 AND b.transaction_type_code = 'FULL RETIREMENT'
542 AND c.asset_id = a.asset_id
543 AND c.asset_type <> 'CIP'
544 AND d.book_type_code = a.book_type_code
545 AND d.asset_id = a.asset_id
546 AND d.date_ineffective IS NULL
547 AND NVL(d.global_attribute1,'N') = 'Y'
548 ORDER BY c.asset_number;
549
550 CURSOR fa_cat(l_asset_id in number) IS
551 SELECT distribution_id,code_combination_id
552 FROM fa_distribution_history
553 WHERE book_type_code = g_distribution_source_book
554 AND asset_id = l_asset_id
555 AND transaction_header_id_out is null;
556
557
558 l_accum_deprn fa_deprn_summary.deprn_reserve%TYPE := 0;
559 l_dist_ccid fa_distribution_history.code_combination_id%TYPE;
560 l_deprn_amount fa_deprn_summary.deprn_amount%TYPE := 0;
561 l_amount_out VARCHAR2(20);
562 l_api_name CONSTANT VARCHAR2(30) := 'CURRENT_PERIOD_RETIREMENTS';
563
564
565 BEGIN
566
567 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
568 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
569 END IF;
570
571 g_step := '';
572
573 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
574 x_char := ' ';
575 fnd_file.put_line (FND_FILE.LOG, x_char);
576 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
577 x_char := 'Procedure Current_period_retirements';
578 fnd_file.put_line (FND_FILE.LOG, x_char);
579 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
580 END IF;
581
582
583 FOR fa_ret_rec IN fa_ret
584 LOOP
585 ------------------------------------------------------------
586 -- Report output --
587 ------------------------------------------------------------
588 IF fa_ret%ROWCOUNT = 1 THEN
589 fnd_file.new_line(FND_FILE.OUTPUT,2);
590 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_TIT3');
591 fnd_file.put_line( FND_FILE.OUTPUT, RPAD(fnd_message.get,50,' '));
592 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1A');
593 fnd_file.put( FND_FILE.OUTPUT, RPAD(RTRIM(fnd_message.get),47,' '));
594 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1B');
595 fnd_file.put( FND_FILE.OUTPUT, RPAD(RTRIM(fnd_message.get),47,' '));
596 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1C');
597 fnd_file.put( FND_FILE.OUTPUT, LPAD(fnd_message.get,32,' '));
598 fnd_file.put_line( FND_FILE.OUTPUT,'');
599 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',45,'-'));
600 fnd_file.put(FND_FILE.OUTPUT,LPAD(' ',2,' '));
601 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',45,'-'));
602 fnd_file.put(FND_FILE.OUTPUT,LPAD(' ',2,' '));
603 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',32,'-'));
604 fnd_file.put_line(FND_FILE.OUTPUT,'');
605 END IF;
606 ------------------------------------------------------------
607 -- End Report output --
608 ------------------------------------------------------------
609
610
611 g_step := 'DIST HIST';
612
613 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
614 x_char := 'Processing asset retired :'||TO_CHAR(fa_ret_rec.asset_id);
615 fnd_file.put_line (FND_FILE.LOG, x_char);
616 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
617 END IF;
618
619
620 FOR fa_cat_rec IN fa_cat(fa_ret_rec.asset_id)
621 LOOP
622
623
624 g_step := 'CURR DPRN INFO';
625
626 ------------------------------------------------------------
627 -- Next select gets the amount for the period and the --
628 -- accumulated depreciation. --
629 ------------------------------------------------------------
630
631
632 SELECT sum(ytd_deprn),sum(deprn_amount)
633 INTO l_accum_deprn,l_deprn_amount
634 FROM fa_deprn_detail
635 WHERE book_type_code = p_book_type_code
636 AND asset_id = fa_ret_rec.asset_id
637 AND period_counter = g_period_counter1
638 AND distribution_id = fa_cat_rec.distribution_id;
639
640 g_step := '';
641
642 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
643 x_char := 'YTD Depreciation and Current Period Depreciation are:'||TO_CHAR(l_accum_deprn)||','||TO_CHAR(l_deprn_amount);
644 fnd_file.put_line (FND_FILE.LOG, x_char);
645 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
646 END IF;
647
648
649 INSERT INTO jl_zz_fa_retiremnt_adjs
650 (retirement_id,
651 period_counter,
652 distribution_id,
653 book_type_code,
654 asset_id,
655 transaction_header_id,
656 je_line_id,
657 original_ytd_depreciation,
658 total_adjustment_amount,
659 period_adjustment_amount,
660 status,
661 retire_reinst_flag,
662 last_update_date,
663 last_updated_by,
664 creation_date,
665 created_by,
666 last_update_login)
667
668 VALUES
669 (fa_ret_rec.retirement_id,
670 g_period_counter1,
671 fa_cat_rec.distribution_id,
672 p_book_type_code,
673 fa_ret_rec.asset_id,
674 fa_ret_rec.transaction_header_id,
675 null,
676 round(l_accum_deprn,g_curr_precision),
677 round(l_accum_deprn,g_curr_precision),
678 0,
679 'Y',
680 'RET',
681 x_sysdate,
682 x_last_updated_by,
683 x_sysdate,
684 x_created_by,
685 x_last_update_login);
686
687
688 ------------------------------------------------------------
689 -- Get account concatenated segments --
690 ------------------------------------------------------------
691 get_segs(fa_cat_rec.code_combination_id);
692
693 ------------------------------------------------------------
694 -- Report output --
695 ------------------------------------------------------------
696
697 format_amount_out(l_deprn_amount,l_amount_out);
698
699 fnd_file.put_line( FND_FILE.OUTPUT, RPAD(LTRIM(fa_ret_rec.asset_desc),47,' ')||
700 RPAD(LTRIM(g_conc_segs),47,' ')||
701 LPAD(l_amount_out,32,' ') );
702 END LOOP;
703 END LOOP;
704 g_step := '';
705
706 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
707 x_char := ' ';
708 fnd_file.put_line (FND_FILE.LOG, x_char);
709 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
710 x_char := 'End of Procedure current_period_retirements';
711 fnd_file.put_line (FND_FILE.LOG, x_char);
712 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
713 END IF;
714
715 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
716 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
717 END IF;
718
719 END current_period_retirements;
720
721
722 ------------------------------------------------------------
723 -- Procedure: current_period_reinstatements --
724 -- --
725 -- Gets the reistatements for current period --
726 -- and inserts a new record with the summary of --
727 -- inflation adjustments applied in previous periods --
728 ------------------------------------------------------------
729 PROCEDURE current_period_reinstatements IS
730
731
732 CURSOR fa_ret IS
733 SELECT a.retirement_id retirement_id,a.asset_id asset_id,
734 b.transaction_header_id transaction_header_id,
735 rpad(c.asset_number||'-'||substr(c.description,1,30),45,' ') asset_desc
736 FROM fa_books d,
737 fa_additions c,
738 fa_transaction_headers b,
739 fa_retirements a
740 WHERE a.book_type_code = p_book_type_code
741 AND a.transaction_header_id_out = b.transaction_header_id
742 AND b.transaction_type_code = 'REINSTATEMENT'
743 AND b.transaction_date_entered BETWEEN g_current_period_from_date1
744 AND g_current_period_to_date1
745 AND c.asset_id = a.asset_id
746 AND d.book_type_code = a.book_type_code
747 AND d.asset_id = a.asset_id
748 AND d.date_ineffective IS NULL
749 AND NVL(d.global_attribute1,'N') = 'Y'
750 ORDER BY c.asset_number;
751
752 CURSOR fa_cat(l_asset_id in number) IS
753 SELECT b.distribution_id,b.code_combination_id
754 FROM fa_distribution_history b, fa_distribution_history a
755 WHERE a.book_type_code = g_distribution_source_book
756 AND a.asset_id = l_asset_id
757 AND a.transaction_header_id_out is null
758 AND b.book_type_code = a.book_type_code
759 AND b.asset_id = a.asset_id
760 AND b.transaction_header_id_out = a.transaction_header_id_in;
761
762 l_accum_deprn fa_deprn_summary.deprn_reserve%TYPE;
763 l_dist_ccid fa_distribution_history.code_combination_id%TYPE;
764 l_deprn_amount fa_deprn_summary.deprn_amount%TYPE;
765 l_ytd_deprn fa_deprn_summary.deprn_amount%TYPE;
766 l_total_amount fa_deprn_summary.deprn_amount%TYPE;
767 l_amount_out VARCHAR2(20);
768 l_api_name CONSTANT VARCHAR2(30) := 'CURRENT_PERIOD_REINSTATEMENTS';
769
770 --
771 BEGIN
772
773 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
774 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
775 END IF;
776
777 g_step := '';
778
779 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
780 x_char := ' ';
781 fnd_file.put_line (FND_FILE.LOG, x_char);
782 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
783 x_char := 'Procedure Current_period_reinstatements';
784 fnd_file.put_line (FND_FILE.LOG, x_char);
785 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
786 END IF;
787
788 FOR fa_ret_rec IN fa_ret
789 LOOP
790
791 ------------------------------------------------------------
792 -- Report output --
793 ------------------------------------------------------------
794 IF fa_ret%ROWCOUNT = 1 THEN
795 fnd_file.new_line(FND_FILE.OUTPUT,2);
796 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_TIT2');
797 fnd_file.put_line( FND_FILE.OUTPUT, RPAD(fnd_message.get,50,' '));
798 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1A');
799 fnd_file.put( FND_FILE.OUTPUT, RPAD(fnd_message.get,47,' '));
800 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1B');
801 fnd_file.put( FND_FILE.OUTPUT, RPAD(fnd_message.get,47,' '));
802 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1C');
803 fnd_file.put( FND_FILE.OUTPUT, LPAD(fnd_message.get,32,' '));
804 fnd_file.put_line( FND_FILE.OUTPUT,'');
805 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',45,'-'));
806 fnd_file.put(FND_FILE.OUTPUT,LPAD(' ',2,' '));
807 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',45,'-'));
808 fnd_file.put(FND_FILE.OUTPUT,LPAD(' ',2,' '));
809 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',32,'-'));
810 fnd_file.put_line(FND_FILE.OUTPUT,'');
811 END IF;
812 ------------------------------------------------------------
813 -- End Report output --
814 ------------------------------------------------------------
815
816 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
817 x_char := 'Processing asset reinstated :'||TO_CHAR(fa_ret_rec.asset_id);
818 fnd_file.put_line (FND_FILE.LOG, x_char);
819 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
820 END IF;
821
822
823 FOR fa_cat_rec IN fa_cat(fa_ret_rec.asset_id)
824 LOOP
825
826
827 SELECT nvl(sum(period_adjustment_amount) * -1,0),
828 max(original_ytd_depreciation) ,
829 max(total_adjustment_amount) - sum(period_adjustment_amount)
830 INTO l_deprn_amount,l_ytd_deprn,l_total_amount
831 FROM jl_zz_fa_retiremnt_adjs
832 WHERE retirement_id = fa_ret_rec.retirement_id
833 AND distribution_id = fa_cat_rec.distribution_id;
834
835 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
836 x_char := 'Amount accumulated for this asset :'||TO_CHAR(l_deprn_amount);
837 fnd_file.put_line (FND_FILE.LOG, x_char);
838 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
839 END IF;
840
841
842 INSERT INTO jl_zz_fa_retiremnt_adjs
843 (retirement_id, period_counter, distribution_id,
844 book_type_code, asset_id, transaction_header_id,
845 je_line_id, original_ytd_depreciation, total_adjustment_amount,
846 period_adjustment_amount, status, retire_reinst_flag,
847 last_update_date, last_updated_by, creation_date,
848 created_by, last_update_login)
849
850 VALUES
851 (fa_ret_rec.retirement_id,
852 g_period_counter1,
853 fa_cat_rec.distribution_id,
854 p_book_type_code,
855 fa_ret_rec.asset_id,
856 fa_ret_rec.transaction_header_id,
857 null,
858 round(l_ytd_deprn,g_curr_precision),
859 round(l_total_amount,g_curr_precision),
860 round(l_deprn_amount,g_curr_precision),
861 'N',
862 'REI',
863 x_sysdate,
864 x_last_updated_by,
865 x_sysdate,
866 x_created_by,
867 x_last_update_login);
868
869
870 ------------------------------------------------------------
871 -- Get account concatenated segments --
872 ------------------------------------------------------------
873 get_segs(fa_cat_rec.code_combination_id);
874
875 ------------------------------------------------------------
876 -- Report output --
877 ------------------------------------------------------------
878
879 format_amount_out(l_deprn_amount,l_amount_out);
880
881 fnd_file.put_line( FND_FILE.OUTPUT, RPAD(LTRIM(fa_ret_rec.asset_desc),47,' ')||
882 RPAD(LTRIM(g_conc_segs),47,' ')||
883 LPAD(l_amount_out,32,' ') );
884
885 END LOOP;
886 END LOOP;
887 g_step := '';
888
889 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
890 x_char := ' ';
891 fnd_file.put_line (FND_FILE.LOG, x_char);
892 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
893 x_char := 'End of Procedure current_period_reistatements';
894 fnd_file.put_line (FND_FILE.LOG, x_char);
895 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
896 END IF;
897
898 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
899 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
900 END IF;
901
902 END current_period_reinstatements;
903
904
905 ------------------------------------------------------------
906 -- Procedure: adjust_previous_retirements --
907 -- --
908 ------------------------------------------------------------
909 PROCEDURE adjust_previous_retirements IS
910
911 l_current_category NUMBER(15) :=0;
912 l_date_placed_in_service DATE :=NULL;
913 l_period_adj_amount NUMBER :=0;
914 l_adj_accum_deprn NUMBER :=0;
915 l_deprn_ccid NUMBER(15);
916 l_adjustment_rate NUMBER :=0;
917 l_dist_ccid fa_distribution_history.code_combination_id%TYPE;
918 l_amount_out VARCHAR2(20);
919 l_api_name CONSTANT VARCHAR2(30) := 'ADJUST_PREVIOUS_RETIREMENTS';
920
921
922 cur_date_placed_in_service DATE;
923
924 CURSOR fa_adjst IS
925 SELECT a.retirement_id retirement_id, a.asset_id asset_id,
926 a.distribution_id distribution_id,
927 a.transaction_header_id transaction_header_id,
928 a.original_ytd_depreciation orig_deprn,
929 a.total_adjustment_amount accum_deprn,
930 a.period_adjustment_amount adjst_amount,
931 b.asset_category_id asset_category,
932 rpad(b.asset_number||'-'||substr(b.description,1,30),45,' ') asset_desc
933 FROM jl_zz_fa_retiremnt_adjs a,
934 fa_additions b
935 WHERE a.book_type_code = p_book_type_code
936 AND a.period_counter = g_previous_period_counter
937 AND a.asset_id = b.asset_id
938 AND NOT EXISTS (SELECT 1
939 FROM jl_zz_fa_retiremnt_adjs c
940 WHERE c.retirement_id = a.retirement_id
941 AND c.period_counter = g_period_counter1
942 AND c.retire_reinst_flag = 'REI')
943
944 GROUP BY b.asset_category_id,
945 rpad(b.asset_number||'-'||substr(b.description,1,30),45,' '),
946 a.retirement_id,
947 a.asset_id , a.distribution_id,
948 a.transaction_header_id,
949 a.original_ytd_depreciation,
950 a.total_adjustment_amount,
951 a.period_adjustment_amount;
952
953 BEGIN
954
955 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
956 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
957 END IF;
958
959 g_step := 'CURR DPRN INFO';
960
961 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
962 x_char := ' ';
963 fnd_file.put_line (FND_FILE.LOG, x_char);
964 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
965 x_char := 'Procedure Adjust_previous_retirements';
966 fnd_file.put_line (FND_FILE.LOG, x_char);
967 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
968 END IF;
969
970
971 FOR fa_adjst_rec IN fa_adjst
972 LOOP
973
974 ------------------------------------------------------------
975 -- Report output --
976 ------------------------------------------------------------
977 IF fa_adjst%ROWCOUNT = 1 THEN
978 fnd_file.new_line(FND_FILE.OUTPUT,2);
979 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_TIT4');
980 fnd_file.put_line( FND_FILE.OUTPUT, RPAD(fnd_message.get,50,' '));
981 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1A');
982 fnd_file.put( FND_FILE.OUTPUT, RPAD(fnd_message.get,47,' '));
983 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1B');
984 fnd_file.put( FND_FILE.OUTPUT, RPAD(fnd_message.get,47,' '));
985 fnd_message.set_name('JL', 'JL_ZZ_FA_INF_ADJ_DEP_EXP_T1C');
986 fnd_file.put( FND_FILE.OUTPUT, LPAD(fnd_message.get,32,' '));
987 fnd_file.put_line( FND_FILE.OUTPUT,'');
988 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',45,'-'));
989 fnd_file.put(FND_FILE.OUTPUT,LPAD(' ',2,' '));
990 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',45,'-'));
991 fnd_file.put(FND_FILE.OUTPUT,LPAD(' ',2,' '));
992 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',32,'-'));
993 fnd_file.put_line(FND_FILE.OUTPUT,'');
994 END IF;
995 ------------------------------------------------------------
996 -- End Report output --
997 ------------------------------------------------------------
998 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
999 x_char := ' ';
1000 fnd_file.put_line (FND_FILE.LOG, x_char);
1001 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1002 x_char := 'Before finding the DPIS : asset_id = '||TO_CHAR(fa_adjst_rec.asset_id);
1003 fnd_file.put_line (FND_FILE.LOG, x_char);
1004 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1005 x_char := 'retirement_id = '||TO_CHAR(fa_adjst_rec.retirement_id);
1006 fnd_file.put_line (FND_FILE.LOG, x_char);
1007 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1008 x_char := 'book_type_code = '||p_book_type_code;
1009 fnd_file.put_line (FND_FILE.LOG, x_char);
1010 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1011 END IF;
1012
1013 SELECT b.date_placed_in_service
1014 INTO cur_date_placed_in_service
1015 FROM fa_books b
1016 WHERE b.book_type_code = p_book_type_code
1017 AND b.asset_id = fa_adjst_rec.asset_id
1018 AND b.retirement_id = fa_adjst_rec.retirement_id;
1019
1020 IF (l_current_category <> fa_adjst_rec.asset_category) OR
1021 (cur_date_placed_in_service <> l_date_placed_in_service) THEN
1022
1023 l_current_category := fa_adjst_rec.asset_category;
1024 l_date_placed_in_service := cur_date_placed_in_service;
1025
1026 g_step := 'CURR INDX';
1027
1028 get_index_rate(fa_adjst_rec.asset_category,
1029 cur_date_placed_in_service,
1030 l_adjustment_rate);
1031
1032
1033 g_step := '';
1034
1035
1036 END IF;
1037
1038
1039 l_adj_accum_deprn := fa_adjst_rec.accum_deprn * l_adjustment_rate;
1040
1041 l_period_adj_amount := l_adj_accum_deprn - fa_adjst_rec.accum_deprn;
1042
1043 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1044 x_char := 'Processing asset :'||TO_CHAR(fa_adjst_rec.asset_id);
1045 fnd_file.put_line (FND_FILE.LOG, x_char);
1046 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1047 x_char := 'Accumulated depreciation for this asset :'||TO_CHAR(l_adj_accum_deprn);
1048 fnd_file.put_line (FND_FILE.LOG, x_char);
1049 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1050 END IF;
1051
1052
1053 INSERT INTO jl_zz_fa_retiremnt_adjs
1054 (retirement_id,
1055 period_counter,
1056 distribution_id,
1057 book_type_code,
1058 asset_id,
1059 transaction_header_id,
1060 je_line_id,
1061 original_ytd_depreciation,
1062 total_adjustment_amount,
1063 period_adjustment_amount,
1064 status,
1065 retire_reinst_flag,
1066 last_update_date,
1067 last_updated_by,
1068 creation_date,
1069 created_by,
1070 last_update_login)
1071
1072 VALUES
1073 (fa_adjst_rec.retirement_id,
1074 g_period_counter1,
1075 fa_adjst_rec.distribution_id,
1076 p_book_type_code,
1077 fa_adjst_rec.asset_id,
1078 fa_adjst_rec.transaction_header_id,
1079 null,
1080 round(fa_adjst_rec.orig_deprn,g_curr_precision),
1081 round(l_adj_accum_deprn,g_curr_precision),
1082 round(l_period_adj_amount,g_curr_precision),
1083 'N',
1084 'INF',
1085 x_sysdate,
1086 x_last_updated_by,
1087 x_sysdate,
1088 x_created_by,
1089 x_last_update_login);
1090
1091
1092
1093 ------------------------------------------------------------
1094 -- Get account concatenated segments --
1095 ------------------------------------------------------------
1096 SELECT code_combination_id
1097 INTO l_dist_ccid
1098 FROM fa_distribution_history
1099 WHERE distribution_id = fa_adjst_rec.distribution_id;
1100
1101 get_segs(l_dist_ccid);
1102
1103 ------------------------------------------------------------
1104 -- Report output --
1105 ------------------------------------------------------------
1106
1107 format_amount_out(l_period_adj_amount,l_amount_out);
1108
1109 fnd_file.put_line( FND_FILE.OUTPUT, RPAD(LTRIM(fa_adjst_rec.asset_desc),47,' ')||
1110 RPAD(LTRIM(g_conc_segs),47,' ')||
1111 LPAD(l_amount_out,32,' ') );
1112 END LOOP;
1113
1114 g_step := '';
1115
1116 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1117 x_char := ' ';
1118 fnd_file.put_line (FND_FILE.LOG, x_char);
1119 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1120 x_char := 'End of Procedure adjust_previous_retirements';
1121 fnd_file.put_line (FND_FILE.LOG, x_char);
1122 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1123 END IF;
1124
1125 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1126 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1127 END IF;
1128
1129 END adjust_previous_retirements;
1130
1131 ------------------------------------------------------------
1132 -- Procedure: insert_retiremnt_jes --
1133 -- --
1134 ------------------------------------------------------------
1135 PROCEDURE insert_retiremnt_jes IS
1136
1137 CURSOR fa_adjst IS
1138 SELECT nvl(sum(a.period_adjustment_amount),0) adjst_amount,
1139 b.code_combination_id ccid, 1 ident
1140 FROM fa_distribution_history b,
1141 jl_zz_fa_retiremnt_adjs a
1142 WHERE a.book_type_code = p_book_type_code
1143 AND a.period_counter = g_period_counter1
1144 AND a.status = 'N'
1145 AND a.distribution_id = b.distribution_id
1146 GROUP BY b.code_combination_id,1
1147 UNION
1148 SELECT nvl(sum(a.period_adjustment_amount),0) adjst_amount,
1149 c.reval_reserve_account_ccid ccid, 2 ident
1150 FROM fa_category_books c,
1151 fa_additions b,
1152 jl_zz_fa_retiremnt_adjs a
1153 WHERE a.book_type_code = p_book_type_code
1154 AND a.period_counter = g_period_counter1
1155 AND a.status = 'N'
1156 AND b.asset_id = a.asset_id
1157 AND c.book_type_code = p_book_type_code
1158 AND c.category_id = b.asset_category_id
1159 GROUP BY c.reval_reserve_account_ccid,2;
1160
1161
1162 l_current_category NUMBER(15):=0;
1163 l_deprn_ccid NUMBER(15):=0;
1164 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RETIREMENT_JES';
1165
1166
1167 BEGIN
1168
1169 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1170 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1171 END IF;
1172
1173 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1174 x_char := ' ';
1175 fnd_file.put_line (FND_FILE.LOG, x_char);
1176 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1177 x_char := 'Procedure Insert_retiremnt_jes';
1178 fnd_file.put_line (FND_FILE.LOG, x_char);
1179 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1180 END IF;
1181
1182 FOR fa_adjst_rec IN fa_adjst
1183 LOOP
1184
1185 INSERT INTO jl_zz_fa_retiremnt_jes
1186 (je_line_id, book_type_code, period_counter,
1187 code_combination_id, set_of_books_id, request_id,
1188 currency_code, adjustment_amount, debit_credit_flag,
1189 posting_flag, last_update_date, last_updated_by,
1190 creation_date, created_by, last_update_login)
1191
1192 VALUES (
1193 jl_zz_fa_retiremnt_jes_s.nextval,
1194 p_book_type_code,
1195 g_period_counter1,
1196 fa_adjst_rec.ccid,
1197 g_set_of_books_id,
1198 x_request_id,
1199 g_currency_code,
1200 ABS(fa_adjst_rec.adjst_amount),
1201 decode (fa_adjst_rec.ident,1,decode(sign(fa_adjst_rec.adjst_amount),-1,'CR','DR'),
1202 2,decode(sign(fa_adjst_rec.adjst_amount),-1,'DR','CR')),
1203 null,
1204 x_sysdate,
1205 x_last_updated_by,
1206 x_sysdate,
1207 x_last_updated_by,
1208 x_last_update_login);
1209
1210 UPDATE jl_zz_fa_retiremnt_adjs
1211 SET je_line_id = jl_zz_fa_retiremnt_jes_s.currval,
1212 status = 'Y'
1213 WHERE rowid in (
1214 SELECT a.rowid
1215 FROM fa_distribution_history b,
1216 jl_zz_fa_retiremnt_adjs a
1217 WHERE a.book_type_code = p_book_type_code
1218 AND a.period_counter = g_period_counter1
1219 AND a.status = 'N'
1220 AND a.distribution_id = b.distribution_id
1221 AND b.code_combination_id = fa_adjst_rec.ccid);
1222
1223 END LOOP; --Cursor
1224 g_step := '';
1225
1226 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1227 x_char := ' ';
1228 fnd_file.put_line (FND_FILE.LOG, x_char);
1229 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1230 x_char := 'End of Procedure insert_retiremnt_jes';
1231 fnd_file.put_line (FND_FILE.LOG, x_char);
1232 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1233 END IF;
1234
1235 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1236 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1237 END IF;
1238
1239 END insert_retiremnt_jes;
1240
1241
1242 ------------------------------------------------------------
1243 -- Procedure: insert_gl_interface --
1244 -- --
1245 ------------------------------------------------------------
1246 PROCEDURE insert_gl_interface IS
1247 --
1248 CURSOR jes_lines IS
1249 SELECT set_of_books_id,currency_code,adjustment_amount,
1250 code_combination_id,je_line_id,debit_credit_flag flag
1251 FROM jl_zz_fa_retiremnt_jes
1252 WHERE request_id = x_request_id
1253 FOR UPDATE OF posting_flag;
1254
1255 l_group_id GL_INTERFACE.GROUP_ID%TYPE;
1256 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GL_INTERFACE';
1257
1258 BEGIN
1259
1260 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1261 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1262 END IF;
1263
1264 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1265 x_char := ' ';
1266 fnd_file.put_line (FND_FILE.LOG, x_char);
1267 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1268 x_char := 'Procedure insert_gl_interface';
1269 fnd_file.put_line (FND_FILE.LOG, x_char);
1270 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1271 END IF;
1272
1273
1274 SELECT gl_interface_control_s.nextval
1275 INTO l_group_id
1276 FROM sys.dual;
1277
1278
1279 FOR jes_lin_rec IN jes_lines
1280 LOOP
1281 INSERT INTO gl_interface (
1282 status,
1283 set_of_books_id,
1284 accounting_date,
1285 currency_code,
1286 date_created,
1287 created_by,
1288 actual_flag,
1289 user_je_category_name,
1290 user_je_source_name,
1291 entered_dr,
1292 entered_cr,
1293 period_name,
1294 code_combination_id,
1295 reference25,
1296 group_id)
1297
1298 VALUES (
1299 'NEW',
1300 jes_lin_rec.set_of_books_id,
1301 g_current_period_to_date1,
1302 jes_lin_rec.currency_code,
1303 x_sysdate,
1304 x_last_updated_by,
1305 'A',
1306 g_user_je_category_name,
1307 g_user_je_source_name,
1308 decode (jes_lin_rec.flag,'DR',jes_lin_rec.adjustment_amount,'0'),
1309 decode (jes_lin_rec.flag,'CR',jes_lin_rec.adjustment_amount,'0'),
1310 G_PERIOD_name,
1311 jes_lin_rec.code_combination_id,
1312 jes_lin_rec.je_line_id,
1313 l_group_id);
1314
1315 END LOOP;
1316
1317 UPDATE jl_zz_fa_retiremnt_jes
1318 SET posting_flag = 'Y'
1319 WHERE request_id = x_request_id;
1320
1321 g_step := '';
1322
1323 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1324 x_char := ' ';
1325 fnd_file.put_line (FND_FILE.LOG, x_char);
1326 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1327 x_char := 'End of procedure insert_gl_interface';
1328 fnd_file.put_line (FND_FILE.LOG, x_char);
1329 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, x_char);
1330 END IF;
1331
1332 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1333 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1334 END IF;
1335
1336 END insert_gl_interface;
1337
1338 ------------------------------------------------------------
1339 -- --
1340 -- Main Procedure - Code --
1341 -- --
1342 ------------------------------------------------------------
1343
1344 BEGIN
1345 get_who_columns;
1346 get_details;
1347 current_period_reinstatements;
1348 current_period_retirements;
1349 adjust_previous_retirements;
1350 insert_retiremnt_jes;
1351 insert_gl_interface;
1352 COMMIT;
1353 retcode := '0';
1354
1355 ------------------------------------------------------------
1356 -- Report output --
1357 ------------------------------------------------------------
1358 fnd_file.put(FND_FILE.OUTPUT,LPAD('-',132,'-'));
1359 fnd_file.new_line(FND_FILE.OUTPUT,1);
1360 fnd_file.new_line(FND_FILE.OUTPUT,2);
1361 fnd_message.set_name('JL','JL_ZZ_END_OF_REPORT');
1362 fnd_file.put_line( FND_FILE.OUTPUT,LPAD(' ',60,' ')||'***** '||fnd_message.get||' *****');
1363
1364
1365 EXCEPTION
1366 WHEN OTHERS THEN
1367
1368 rollback;
1369
1370 IF g_step = 'BOOK INFO' THEN
1371 fnd_message.set_name ('JL', 'JL_AR_FA_BOOK_INFO_NOT_DEFINED');
1372 fnd_file.put_line (fnd_file.log, fnd_message.get);
1373 call_status := fnd_concurrent.set_completion_status('ERROR','');
1374 /*
1375 jl_zz_fa_utilities_pkg.raise_error ('JL',
1376 'JL_AR_FA_BOOK_INFO_NOT_DEFINED','APPS');
1377 */
1378 ELSIF g_step = 'DIST HIST' THEN
1379 fnd_message.set_name ('JL', 'JL_ZZ_FA_NO_DISTRIBUTION_INFO');
1380 fnd_file.put_line (fnd_file.log, fnd_message.get);
1381 call_status := fnd_concurrent.set_completion_status('ERROR','');
1382 /*
1383 jl_zz_fa_utilities_pkg.raise_error ('JL',
1384 'JL_ZZ_FA_NO_DISTRIBUTION_INFO','APPS');
1385 */
1386 ELSIF g_step = 'CURR INDX' THEN
1387 fnd_message.set_name ('JL', 'JL_AR_FA_CURR_INDX_VAL_NOT_DEF');
1388 fnd_file.put_line (fnd_file.log, fnd_message.get);
1389 call_status := fnd_concurrent.set_completion_status('ERROR','');
1390 /*
1391 jl_zz_fa_utilities_pkg.raise_error ('JL',
1392 'JL_AR_FA_CURR_INDX_VAL_NOT_DEF','APPS');
1393 */
1394 ELSIF g_step = 'CURR DPRN INFO' THEN
1395 fnd_message.set_name ('JL', 'JL_AR_FA_CUR_FY_DEP_PER_NOTDEF');
1396 fnd_file.put_line (fnd_file.log, fnd_message.get);
1397 call_status := fnd_concurrent.set_completion_status('ERROR','');
1398 /*
1399 jl_zz_fa_utilities_pkg.raise_error ('JL',
1400 'JL_AR_FA_CUR_FY_DEP_PER_NOTDEF','APPS');
1401 */
1402 ELSIF g_step = 'PREV DPRN INFO' THEN
1403 fnd_message.set_name ('JL', 'JL_AR_FA_PRV_FY_DEP_PER_NOTDEF');
1404 fnd_file.put_line (fnd_file.log, fnd_message.get);
1405 call_status := fnd_concurrent.set_completion_status('ERROR','');
1406 /*
1407 jl_zz_fa_utilities_pkg.raise_error ('JL',
1408 'JL_AR_FA_PRV_FY_DEP_PER_NOTDEF','APPS');
1409 */
1410 ELSE
1411 jl_zz_fa_utilities_pkg.raise_ora_error;
1412
1413
1414
1415 END IF;
1416
1417 END deprn_adj_ret_assets;
1418
1419 END jl_zz_fa_deprn_adj_pkg;