[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_FA_REVAL_RULES_PKG
Source
1 PACKAGE BODY jl_zz_fa_reval_rules_pkg AS
2 /* $Header: jlzzfrrb.pls 120.16 2006/12/22 18:59:54 abuissa ship $ */
3
4 /* ======================================================================*
5 | FND Logging infrastructure |
6 * ======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_ZZ_FA_REVAL_RULES_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_REVAL_RULES_PKG.';
16
17
18 /*+=========================================================================+
19 | PUBLIC PROCEDURE |
20 | reval_rules_generator |
21 | p_book_type_code Book Type Code |
22 | p_mass_reval_id Mass Revaluation Id |
23 | |
24 | NOTES |
25 | This procedure calculates the rates that have to be provided to the |
26 | revaluation process in order to calculate the inflation adjustment. |
27 | The rate is calculated in different ways depending on the type of book.|
28 | For CIP assets, the rate must consider the fact that current period |
29 | modifications to the asset cost must not be inflation adjusted. |
30 | |
31 | MODIFICATION HISTORY |
32 | 12-SEP-97 G. Bertot Created |
33 | 21-DEC-98 G. Leyva Major changes to include Chile. |
34 | Added logic for date_placed_in_service and |
35 | reinstatements. |
36 | 27-JAN-99 G. Leyva Changes in decimal numbers truncated to fix bug |
37 | 808404. |
38 +=========================================================================+*/
39 PROCEDURE reval_rules_generator (errbuf OUT NOCOPY VARCHAR2
40 , retcode OUT NOCOPY VARCHAR2
41 , p_book_type_code IN VARCHAR2
42 , p_mass_reval_id IN NUMBER) IS
43
44 ------------------------------------------------------------
45 -- Procedure Global Variables --
46 ------------------------------------------------------------
47 g_step VARCHAR2(30);
48 g_current_period_counter1 NUMBER (15);
49 g_current_period_from_date1 DATE;
50 g_current_period_from_date2 DATE;
51 g_current_period_to_date1 DATE;
52 g_current_period_to_date2 DATE;
53 g_current_fiscal_year NUMBER(4);
54 g_previous_period_counter NUMBER(15);
55 g_previous_period_to_date DATE;
56 g_use_middle_month_table VARCHAR2(3);
57 g_calendar_type FA_CALENDAR_TYPES.CALENDAR_TYPE%TYPE;
58 g_price_index NUMBER;
59 g_country_code VARCHAR2(2);
60 g_precision NUMBER;
61 g_total_records NUMBER := 0;
62 g_period_num NUMBER := 0;
63 g_number_per_fy NUMBER := 0;
64 g_char VARCHAR2 (200);
65 g_revalue_cip_assets_flag VARCHAR2(1);
66 --Bug3466346
67 -- g_reval_fully_rsvd_flag VARCHAR2(1);
68 g_reval_fully_rsvd_flag FA_MASS_REVALUATIONS.DEFAULT_REVAL_FULLY_RSVD_FLAG%TYPE;
69 g_life_extension_factor FA_MASS_REVALUATIONS.DEFAULT_LIFE_EXTENSION_FACTOR%TYPE;
70 g_life_extension_ceiling FA_MASS_REVALUATIONS.DEFAULT_LIFE_EXTENSION_CEILING%TYPE;
71
72 g_period_open_date DATE;
73 TOO_MANY_COSTS EXCEPTION;
74
75
76 call_status BOOLEAN;
77
78 ------------------------------------------------------------
79 -- Local Variables --
80 ------------------------------------------------------------
81 l_date_placed_in_service DATE;
82 l_current_category NUMBER(15);
83 l_revaluation_rate NUMBER;
84 l_err_msg VARCHAR2(1000);
85 l_last_request_id NUMBER;
86
87 ------------------------------------------------------------
88 -- Cursors --
89 ------------------------------------------------------------
90
91 ------------------------------------------------------------
92 -- Cursor: adjustable_assets --
93 -- --
94 -- Fetch all the assets which can be inflation adjusted, --
95 -- those with fa_books.global_attribute1 = 'Y'. --
96 -- --
97 -- fa_additions.global_attribute1 stores the date when the--
98 -- asset has to start to be adjusted. --
99 ------------------------------------------------------------
100
101 CURSOR adjustable_assets(l_country_code IN VARCHAR2) IS
102 -- SELECT /*+ leading(b) index(th FA_TRANSACTION_HEADERS_U1) */ a.asset_id,
103 SELECT a.asset_id,
104 a.asset_category_id,
105 a.asset_type,
106 NVL(FND_DATE.CANONICAL_TO_DATE(a.global_attribute1),b.date_placed_in_service) revaluation_start_date,
107 b.date_placed_in_service,
108 b.cost cost,
109 rownum counter
110 FROM
111 fa_category_books c
112 , fa_additions a
113 , fa_books b
114 , fa_transaction_headers th
115 WHERE b.book_type_code = p_book_type_code
116 AND b.cost <> 0
117 AND b.global_attribute1 = 'Y'
118 AND b.date_ineffective IS NULL
119 AND b.transaction_header_id_out IS NULL
120 AND a.asset_id = b.asset_id
121 AND c.category_id = a.asset_category_id
122 AND c.book_type_code = p_book_type_code
123 AND c.global_attribute1 = 'Y'
124 AND th.book_type_code = p_book_type_code
125 AND th.asset_id = a.asset_id
126 -- AND th.transaction_type_code in ('ADDITION','CIP ADDITION')
127 AND (th.transaction_type_code = 'ADDITION'
128 OR (th.transaction_type_code = 'CIP ADDITION' AND a.asset_type <> 'CAPITALIZED'))
129 AND th.date_effective < g_period_open_date
130 AND th.transaction_header_id <= b.transaction_header_id_in
131 AND ((g_revalue_cip_assets_flag is NULL AND a.asset_type = 'CAPITALIZED')
132 OR (g_revalue_cip_assets_flag is NOT NULL))
133 AND not exists (select 'X' from FA_TRANSACTION_HEADERS th_2
134 where th_2.book_type_code = p_book_type_code
135 AND th_2.asset_id = a.asset_id
136 AND th_2.transaction_type_code = 'REVALUATION'
137 AND th_2.date_effective >= g_period_open_date)
138 ORDER BY a.asset_category_id;
139
140 ------------------------------------------------------------
141 -- Procedure: initialize_process --
142 -- --
143 -- Get Generic information for the package. --
144 -- Clean the rules table for the mass_reval_id given. --
145 -- Get all the book related information needed to --
146 -- calculate the revaluation rates. --
147 ------------------------------------------------------------
148
149 PROCEDURE initialize_process IS
150
151 l_api_name CONSTANT VARCHAR2(30) := 'INITIALIZE_PROCESS';
152
153 BEGIN
154
155 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
156 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
157 END IF;
158 ------------------------------------------------------------
159 -- Gets the country code where the program is executed. --
160 ------------------------------------------------------------
161 -------------------------------------------------------------------------
162 -- BUG 4650081. Profile for country is replaced by call to JG Shared pkg.
163 -------------------------------------------------------------------------
164 g_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY;
165 FND_PROFILE.GET('JLZZ_INF_RATIO_PRECISION',g_precision);
166
167 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
168 g_char := 'Start Debugging';
169 fnd_file.put_line (FND_FILE.LOG, g_char);
170 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
171
172 g_char := ' ';
173 fnd_file.put_line (FND_FILE.LOG, g_char);
174 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
175
176 g_char := 'Procedure initialize_process';
177 fnd_file.put_line (FND_FILE.LOG, g_char);
178 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
179
180 g_char := 'Country :'||g_country_code;
181 fnd_file.put_line (FND_FILE.LOG, g_char);
182 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
183
184 g_char := 'Precision :'||g_precision;
185 fnd_file.put_line (FND_FILE.LOG, g_char);
186 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
187 END IF;
188
189
190 IF g_precision IS NULL THEN
191 g_precision := 38;
192 END IF;
193
194 ------------------------------------------------------------
195 -- Deletes rows that already exists for the mass reval id --
196 -- to give way to the new rules --
197 ------------------------------------------------------------
198 g_step := 'DELETE_RULES';
199
200 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
201 g_char := 'Deleting fa_mass_revaluation_rules table';
202 fnd_file.put_line (FND_FILE.LOG, g_char);
203 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
204 END IF;
205
206 DELETE FROM fa_mass_revaluation_rules
207 Where mass_reval_id = p_mass_reval_id;
208
209 ------------------------------------------------------------
210 -- Gives commit to delete transaction to release rollback --
211 -- segment. --
212 ------------------------------------------------------------
213
214 COMMIT;
215
216 ------------------------------------------------------------
217 -- Fetches the depreciation parameters for the current --
218 -- period. --
219 ------------------------------------------------------------
220 g_step := '';
221
222 SELECT calendar_period_open_date
223 , calendar_period_close_date
224 , fiscal_year
225 , period_counter
226 , period_open_date
227 INTO g_current_period_from_date1
228 , g_current_period_to_date1
229 , g_current_fiscal_year
230 , g_current_period_counter1
231 , g_period_open_date
232 FROM fa_deprn_periods
233 WHERE book_type_code = p_book_type_code
234 AND period_close_date IS NULL;
235
236
237 SELECT deprn_calendar
238 INTO g_calendar_type
239 FROM fa_book_controls
240 WHERE book_type_code = p_book_type_code;
241
242
243 SELECT number_per_fiscal_year
244 INTO g_number_per_fy
245 FROM fa_calendar_types
246 WHERE calendar_type = g_calendar_type;
247
248 g_current_period_from_date2 := g_current_period_from_date1;
249 g_current_period_to_date2 := g_current_period_to_date1;
250
251 /* BUG # 3211526
252 Moved the logic of determining cip assets revaluation flag from the main
253 cursor for performance reasons */
254
255 BEGIN
256 SELECT nvl(rr.revalue_cip_assets_flag,'N') revalue_cip_assets_flag,
257 nvl(rr.default_reval_fully_rsvd_flag,'NO') reval_fully_rsvd_flag,
258 rr.default_life_extension_factor life_extension_factor,
259 rr.default_life_extension_ceiling life_extension_ceiling
260 INTO
261 g_revalue_cip_assets_flag,
262 g_reval_fully_rsvd_flag,
263 g_life_extension_factor,
264 g_life_extension_ceiling
265 FROM fa_mass_revaluations rr
266 WHERE rr.mass_reval_id = p_mass_reval_id;
267 EXCEPTION WHEN OTHERS THEN
268 g_char := 'Error: Mass Revaluation Id : '||to_char(p_mass_reval_id)||SQLERRM;
269 fnd_file.put_line (FND_FILE.LOG, g_char);
270 END;
271
272
273 -----------------------------------------------------------------
274 -- If the country is 'CL'(Chile), Fetch the depreciation --
275 -- parameters for the previous period and make it current. --
276 -- Reason : The calculation for chilean rate is different --
277 -- from other countries. --
278 -- Eg. When the current period is october : --
279 -- For countries other than chile, the rate calculation is --
280 -- october rate / september rate --
281 -- current period = october previous period = september --
282 -- For chile, the rate calculation should be --
283 -- september rate / august rate --
284 -- current period should be changed from october to september,--
285 -- which helps the subsequent SQL statement to use --
286 -- august as previous period --
287 -----------------------------------------------------------------
288 IF g_country_code = 'CL' THEN
289
290 g_step := 'PREVIOUS PERIOD';
291
292 SELECT period_num
293 INTO g_period_num
294 FROM fa_calendar_periods
295 WHERE calendar_type = g_calendar_type
296 AND start_date = g_current_period_from_date1;
297
298 SELECT start_date,end_date
299 INTO g_current_period_from_date2,
300 g_current_period_to_date2
301 FROM fa_calendar_periods
302 WHERE calendar_type = g_calendar_type
303 AND period_num = decode(g_period_num,1,g_number_per_fy,g_period_num-1)
304 AND end_date = g_current_period_from_date1 - 1;
305
306 g_step := '';
307
308 END IF;
309
310 ------------------------------------------------------------
311 -- Fetch Period_Counter and calendar open and close date --
312 -- For previous period --
313 ------------------------------------------------------------
314
315 g_previous_period_counter := g_current_period_counter1 - 1;
316 g_previous_period_to_date := g_current_period_from_date2 - 1;
317
318 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
319
320 g_char := 'Period counter :'||TO_CHAR(g_current_period_counter1);
321 fnd_file.put_line (FND_FILE.LOG, g_char);
322 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
323
324 g_char := ' ';
325 fnd_file.put_line (FND_FILE.LOG, g_char);
326 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
327 g_char := 'End of procedure initialize_process';
328 fnd_file.put_line (FND_FILE.LOG, g_char);
329 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
330 END IF;
331
332 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
333 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
334 END IF;
335
336 END initialize_process;
337
338
339 ------------------------------------------------------------
340 -- Procedure: initialize_category --
341 -- --
342 -- Gets the price index associated to the category --
343 -- Bug 1415119 : Changed the query in the procedure --
344 -- "initialize_category" to cater for the situation --
345 -- where different price indexes are defined for --
346 -- different ranges of date placed in service. --
347 -- Bug 1488156 : Changed the query in the procedure --
348 -- "initialize_category" to deal with Items with --
349 -- date placed in service ahead of system time. --
350 ------------------------------------------------------------
351 PROCEDURE initialize_category (p_category_id IN NUMBER,
352 p_date_placed_in_service IN DATE) IS
353
354 l_api_name CONSTANT VARCHAR2(30) := 'INITIALIZE_CATEGORY';
355
356 BEGIN
357
358 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
359 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
360 END IF;
361
362 ------------------------------------------------------------
363 -- Fetch the index for the depreciation book and category --
364 ------------------------------------------------------------
365
366 g_step := 'PRICE INDEX ID';
367
368 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
369 g_char := 'Procedure initialize_category';
370 fnd_file.put_line (FND_FILE.LOG, g_char);
371 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
372 END IF;
373
374 SELECT price_index_id
375 INTO g_price_index
376 FROM fa_category_book_defaults a, fa_price_indexes b
377 WHERE a.book_type_code = p_book_type_code
378 AND a.category_id = p_category_id
379 AND p_date_placed_in_service >= a.start_dpis
380 AND p_date_placed_in_service <= NVL(a.end_dpis,p_date_placed_in_service)
381 AND a.price_index_name = b.price_index_name;
382
383 g_step := '';
384
385 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
386 g_char := 'Price index :'||TO_CHAR(g_price_index);
387 fnd_file.put_line (FND_FILE.LOG, g_char);
388 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
389 END IF;
390
391 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
392 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
393 END IF;
394
395 END initialize_category;
396
397 ------------------------------------------------------------
398 -- Procedure: get_close_date --
399 -- --
400 -- Gets the period_close_date from fa_deprn_periods for --
401 -- the book_type_code and a given date. If country is --
402 -- Chile then gets the calendar_period_close_date for --
403 -- previous period at the retired or placed in service. --
404 -- Bug 1974991 : The algorithm of figuring out period ---
405 -- close date is replaced.
406 ------------------------------------------------------------
407 PROCEDURE get_close_date (p_date IN DATE
408 , l_close_date IN OUT NOCOPY DATE) IS
409
410 l_api_name CONSTANT VARCHAR2(30) := 'GET_CLOSE_DATE';
411
412 BEGIN
413
414 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
415 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
416 END IF;
417
418 g_step := 'MISSING PERIOD';
419
420 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
421 g_char := 'Procedure get_close_date parameter p_date is '||p_date||
422 ' and must be in canonical format YYYY/MM/DD HH24:MI:SS';
423 fnd_file.put_line (FND_FILE.LOG, g_char);
424 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
425 END IF;
426
427
428 SELECT decode(g_country_code, 'CL',(start_date-1), end_date)
429 INTO l_close_date
430 FROM fa_calendar_periods
431 WHERE calendar_type = g_calendar_type
432 AND trunc(p_date) BETWEEN start_date AND end_date;
433
434
435 g_step := '';
436
437 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
438 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
439 END IF;
440
441 END get_close_date;
442
443
444 ------------------------------------------------------------
445 -- Procedure: get_price_index_rate --
446 -- --
447 -- Gets the index value for a certain price index and a --
448 -- particular date. --
449 ------------------------------------------------------------
450 PROCEDURE get_price_index_rate (p_period_date IN DATE
451 , p_index_value IN OUT NOCOPY NUMBER) IS
452 BEGIN
453 g_step := 'PRICE INDEX VALUE';
454
455 SELECT price_index_value
456 INTO p_index_value
457 FROM fa_price_index_values
458 WHERE price_index_id = g_price_index
459 AND p_period_date BETWEEN from_date AND nvl(to_date,p_period_date);
460
461 g_step := '';
462
463 END get_price_index_rate;
464
465 ------------------------------------------------------------
466 -- Procedure: get_costs --
467 -- --
468 -- Will find the adjusted cost of previous period on which--
469 -- inflation adjustment has to be applied. Will also find --
470 -- change in cost between the current period and previous --
471 -- period
472 ------------------------------------------------------------
473 PROCEDURE get_costs (p1_asset_id IN NUMBER
474 , p1_previous_period_counter IN NUMBER
475 , p1_current_cost IN NUMBER
476 , p1_previous_period_cost IN OUT NOCOPY NUMBER
477 , p1_change_in_cost IN OUT NOCOPY NUMBER) IS
478
479 BEGIN
480
481 IF jl_zz_fa_functions_pkg.asset_cost (p_book_type_code
482 , p1_asset_id
483 , p1_previous_period_counter
484 , p1_previous_period_cost) <> 0 THEN
485 RAISE TOO_MANY_COSTS;
486 END IF;
487
488 p1_change_in_cost := p1_current_cost - p1_previous_period_cost;
489
490 END get_costs;
491
492 ------------------------------------------------------------
493 -- Function: first_deprn --
494 -- --
495 -- Returns TRUE if this will be the first depreciation for--
496 -- the given asset. If TRUE then date_placed_in_service --
497 -- should be taken to obtain the "previous period index". --
498 ------------------------------------------------------------
499 FUNCTION first_deprn (p_asset_id IN NUMBER
500 , p_current_period_counter IN NUMBER) RETURN BOOLEAN IS
501
502 dummy NUMBER;
503 l_api_name CONSTANT VARCHAR2(30) := 'FIRST_DEPRN';
504
505 BEGIN
506
507 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
508 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
509 END IF;
510
511 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
512 g_char := 'Function first_deprn';
513 fnd_file.put_line (FND_FILE.LOG, g_char);
514 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
515 END IF;
516
517 --
518 --BUGS 2808946/2872684. Logic to identify First Depreciation period
519 -- has been changed.
520 --
521 BEGIN
522 SELECT min(dp.period_counter)
523 INTO dummy
524 FROM fa_deprn_periods dp
525 , fa_transaction_headers th
526 , fa_asset_history ah
527 WHERE ah.asset_id = th.asset_id
528 AND dp.book_type_code = th.book_type_code
529 AND th.transaction_header_id >= ah.transaction_header_id_in
530 AND th.transaction_header_id < nvl(ah.transaction_header_id_out, th.transaction_header_id + 1)
531 AND th.date_effective between dp.period_open_date
532 and nvl(dp.period_close_date, th.date_effective)
533 AND dp.book_type_code = p_book_type_code
534 AND ah.asset_id = p_asset_id;
535 EXCEPTION
536 WHEN OTHERS THEN
537 RETURN(FALSE);
538 END;
539
540 IF (dummy = p_current_period_counter - 1) THEN
541 RETURN(TRUE);
542 ELSE
543 RETURN(FALSE);
544 END IF;
545
546 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
548 END IF;
549
550 END first_deprn;
551
552
553 ------------------------------------------------------------
554 -- Function: eval_reinstatements --
555 -- Evaluates if the asset has been reinstanted and if this--
556 -- is true then returns the cost and date of retirement. --
557 ------------------------------------------------------------
558 FUNCTION eval_reinstatements (p_asset_id IN NUMBER,
559 p_date_ini IN DATE,
560 p_date_end IN DATE,
561 p_amount OUT NOCOPY NUMBER,
562 p_date_retired OUT NOCOPY DATE) RETURN BOOLEAN IS
563
564 BEGIN
565
566 SELECT cost_retired,date_retired
567 INTO p_amount,p_date_retired
568 FROM fa_transaction_headers b,
569 fa_retirements a
570 WHERE a.book_type_code = p_book_type_code
571 AND a.asset_id = p_asset_id
572 AND a.status = 'DELETED'
573 AND b.book_type_code = a.book_type_code
574 AND b.asset_id = a.asset_id
575 AND b.transaction_header_id = a.transaction_header_id_out
576 AND b.transaction_date_entered BETWEEN p_date_ini AND
577 p_date_end
578 ------------------------------------------------------
579 -- BUG 4345686. Added to filter out assets retired and
580 -- reinstated in the same period. We will consider
581 -- those assets as if they were not retired at all
582 -- for the purposes of reval rules generator.
583 ------------------------------------------------------
584 AND b.transaction_type_code = 'REINSTATEMENT'
585 AND b.transaction_date_entered <> a.date_retired;
586
587
588 RETURN(TRUE);
589 EXCEPTION
590 WHEN OTHERS THEN
591 RETURN(FALSE);
592 END;
593
594 ------------------------------------------------------------
595 -- Function: get_revaluation_rates --
596 -- --
597 -- Will compute the inflation adjustment rate depending on--
598 -- the start date of the asset. Will call get_costs --
599 -- procedure to get the change in cost and the previous --
600 -- period cost --
601 ------------------------------------------------------------
602 FUNCTION get_revaluation_rates (p_asset_id IN NUMBER
603 , p_asset_type IN VARCHAR2
604 , p_current_cost IN NUMBER
605 , p_asset_start_date IN DATE) RETURN NUMBER IS
606
607 p_cost_retired NUMBER;
608 p_date_retired DATE;
609 l_close_date DATE;
610 l_current_index_value NUMBER;
611 l_index_retired1 NUMBER;
612 l_index_retired2 NUMBER;
613 l_previous_index_value NUMBER;
614 l_previous_period_cost NUMBER;
615 l_rate NUMBER;
616 l_rate1 NUMBER;
617 l_rate2 NUMBER;
618 l_change_in_cost NUMBER;
619 l_api_name CONSTANT VARCHAR2(30) := 'GET_REVALUATION_RATES';
620
621
622 BEGIN
623
624 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
625 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
626 END IF;
627
628 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
629 g_char := 'Function get_revaluation_rates ';
630 fnd_file.put_line (FND_FILE.LOG, g_char);
631 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
632 g_char := 'Asset id :'||TO_CHAR(p_asset_id);
633 fnd_file.put_line (FND_FILE.LOG, g_char);
634 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
635 g_char := 'Asset start date :'||TO_CHAR(p_asset_start_date);
636 fnd_file.put_line (FND_FILE.LOG, g_char);
637 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
638 END IF;
639
640 ------------------------------------------------------------
641 -- Inflation adjustment rate will be computed here --
642 -- depending on start date. --
643 -- Those assets with start date in or after the current --
644 -- period do not have to be inflation adjusted. --
645 ------------------------------------------------------------
646
647 IF p_asset_start_date > g_current_period_to_date1 OR
648 p_asset_start_date BETWEEN g_current_period_from_date1 AND
649 g_current_period_to_date1 THEN
650 l_rate := 0;
651
652 ELSE
653
654 ------------------------------------------------------------
655 -- Obtain current period price index value --
656 ------------------------------------------------------------
657 get_price_index_rate (g_current_period_to_date2
658 , l_current_index_value);
659
660
661 ------------------------------------------------------------
662 -- Obtain previous period price index value --
663 -- If status = 'DELETED' that indicates that is a --
664 -- REINSTANTED ASSET and the rate should be obtained from--
665 -- both current_index and previous_index (previous means --
666 -- when the RETIREMENT occured). --
667 -- Same case if the first depreciation for the asset. --
668 ------------------------------------------------------------
669
670 IF eval_reinstatements ( p_asset_id ,
671 g_current_period_from_date1 ,
672 g_current_period_to_date1 ,
673 p_cost_retired ,
674 p_date_retired ) THEN
675
676 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
677 g_char := 'In eval_reinstatements';
678 fnd_file.put_line (FND_FILE.LOG, g_char);
679 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
680 END IF;
681
682 get_close_date(p_date_retired,l_close_date);
683
684 get_price_index_rate (l_close_date
685 , l_index_retired1);
686
687
688 get_price_index_rate (g_previous_period_to_date
689 , l_index_retired2);
690
691 l_rate1 := l_current_index_value/l_index_retired1 - 1;
692 l_rate2 := l_current_index_value/l_index_retired2 - 1;
693
694
695 l_rate := ((p_current_cost + p_cost_retired * l_rate1 +
696 (p_current_cost-p_cost_retired) *l_rate2 )/p_current_cost)-1;
697
698 ELSIF first_deprn(p_asset_id , g_current_period_counter1) THEN
699
700 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
701 g_char := 'In first_deprn';
702 fnd_file.put_line (FND_FILE.LOG, g_char);
703 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
704 END IF;
705
706 get_close_date(p_asset_start_date,l_close_date);
707
708 get_price_index_rate (l_close_date
709 , l_previous_index_value);
710 IF p_asset_type = 'CIP' THEN
711 l_rate := TRUNC((l_current_index_value / l_previous_index_value) - 1,g_precision);
712 ELSE
713 l_rate := (l_current_index_value / l_previous_index_value) - 1;
714 END IF;
715
716 ELSE
717
718 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
719 g_char := 'Normal Indexes';
720 fnd_file.put_line (FND_FILE.LOG, g_char);
721 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
722 END IF;
723
724 get_price_index_rate (g_previous_period_to_date
725 , l_previous_index_value);
726
727
728 IF p_asset_type = 'CIP' THEN
729 l_rate := TRUNC((l_current_index_value / l_previous_index_value) - 1,g_precision);
730 ELSE
731 l_rate := (l_current_index_value / l_previous_index_value) - 1;
732 END IF;
733
734 END IF;
735
736 END IF;
737
738
739
740 IF p_asset_type = 'CIP' THEN
741
742
743 get_costs (p_asset_id, g_previous_period_counter
744 , p_current_cost, l_previous_period_cost
745 , l_change_in_cost);
746
747 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
748 g_char := 'Asset CIP, previous cost, current cost :'||TO_CHAR(l_previous_period_cost)||','||TO_CHAR(p_current_cost);
749 fnd_file.put_line (FND_FILE.LOG, g_char);
750 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
751 END IF;
752
753 l_rate := ((p_current_cost + l_previous_period_cost * l_rate)/p_current_cost)-1;
754
755 l_rate := l_rate * 100;
756
757 ELSE
758
759 l_rate := TRUNC(l_rate,g_precision) * 100;
760
761 END IF;
762
763
764 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
765 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
766 END IF;
767
768 RETURN(l_rate);
769
770 END get_revaluation_rates;
771
772 ------------------------------------------------------------
773 -- Procedure: create_revaluation_rule --
774 -- --
775 -- Creates a row in FA_MASS_REVALUATION_RULES that later --
776 -- will be the input to the Mass Revaluation Process. --
777 ------------------------------------------------------------
778 PROCEDURE create_revaluation_rule (p_mass_reval_id IN NUMBER
779 , p_asset_id IN NUMBER
780 , p_revaluation_rate IN NUMBER) IS
781
782 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_REVALUATION_RULE';
783
784 BEGIN
785 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
786 g_char := 'Procedure create_revaluation_rule(+)';
787 fnd_file.put_line (FND_FILE.LOG, g_char);
788 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
789 g_char := 'values inserted into fa_mass_revaluation_rules:';
790 fnd_file.put_line (FND_FILE.LOG, g_char);
791 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
792 g_char := 'asset id:'||to_char(p_asset_id)||
793 ' override_defaults_flag:'||'No'||
794 ' revalue_cip_assets_flag:'||g_revalue_cip_assets_flag||
795 ' reval_fully_rsvd_flag:'||g_reval_fully_rsvd_flag||
796 ' life_extension_factor:'||to_char(g_life_extension_factor);
797 fnd_file.put_line (FND_FILE.LOG, g_char);
798 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
799 END IF;
800
801 --
802 --Bug 3073126. As indicated by FA masethur.in we have put following
803 -- conditions.
804 IF p_revaluation_rate > 0 AND g_reval_fully_rsvd_flag = 'YES' THEN
805 IF nvl(g_life_extension_ceiling,0) <= 0 THEN
806 g_char := 'Error: Invalid life extension ceiling:'||to_char(g_life_extension_ceiling);
807 fnd_file.put_line (FND_FILE.LOG, g_char);
808 END IF;
809 END IF;
810
811 INSERT INTO fa_mass_revaluation_rules
812 (mass_reval_id
813 , category_id
814 , asset_id
815 , reval_percent
816 , override_defaults_flag
817 , revalue_cip_assets_flag
818 , reval_fully_rsvd_flag
819 , life_extension_factor
820 , life_extension_ceiling
821 , last_updated_by
822 , last_update_date)
823 VALUES(p_mass_reval_id
824 , null
825 , p_asset_id
826 , p_revaluation_rate
827 , 'NO'
828 , g_revalue_cip_assets_flag
829 , g_reval_fully_rsvd_flag
830 , g_life_extension_factor
831 , g_life_extension_ceiling
832 , fnd_global.user_id
833 , sysdate);
834
835
836 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
837 g_char := 'Procedure create_revaluation_rule(-)';
838 fnd_file.put_line (FND_FILE.LOG, g_char);
839 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
840 END IF;
841
842 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
843 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
844 END IF;
845
846 END create_revaluation_rule;
847
848 ------------------------------------------------------------
849 -- Procedure: update_control_tables --
850 -- --
851 -- Update depreciation book to indicate that was revalued --
852 -- in the current period. --
853 -- It also updates fa_mass_revaluations table to indicate --
854 -- in wich period revaluation has been run. --
855 ------------------------------------------------------------
856 PROCEDURE update_control_tables (p_mass_reval_id IN NUMBER) IS
857
858 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_CONTROL_TABLES';
859
860 BEGIN
861
862 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
863 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
864 END IF;
865
866 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
867 g_char := 'Procedure update_control_tables';
868 fnd_file.put_line (FND_FILE.LOG, g_char);
869 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
870 END IF;
871 UPDATE fa_book_controls
872 SET global_attribute2 = g_current_period_counter1,
873 global_attribute3 = p_mass_reval_id
874 WHERE book_type_code = p_book_type_code;
875
876 -- Update commented to fix bug 1013530
877 /*
878 UPDATE fa_mass_revaluations
879 SET global_attribute1 = g_current_period_counter1
880 WHERE mass_reval_id = p_mass_reval_id;
881 */
882
883 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
884 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
885 END IF;
886
887 END update_control_tables;
888
889 ------------------------------------------------------------
890 -- Procedure: preview_report --
891 -- --
892 -- This procedure submits the Preview Report that was --
893 -- being called from the FA core form (now disabled by --
894 -- Style function in JL library with style "Overwrite") --
895 ------------------------------------------------------------
896 PROCEDURE preview_report IS
897 l_request_id NUMBER(15);
898 l_message_text VARCHAR2(1000);
899 l_api_name CONSTANT VARCHAR2(30) := 'PREVIEW_REPORT';
900
901
902 BEGIN
903
904 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
905 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
906 END IF;
907
908 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
909 g_char := 'Submiting Preview Report.';
910 fnd_file.put_line (FND_FILE.LOG, g_char);
911 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, g_char);
912 END IF;
913
914 l_request_id := FND_REQUEST.SUBMIT_REQUEST('OFA',
915 'FASRVPVW','','', FALSE,
916 'P_MASS_REVAL_ID='||to_char(p_mass_reval_id),
917 fnd_global.local_chr(0),'','','','','','','','',
918 '','','','','','','','','','',
919 '','','','','','','','','','',
920 '','','','','','','','','','',
921 '','','','','','','','','','',
922 '','','','','','','','','','',
923 '','','','','','','','','','',
924 '','','','','','','','','','',
925 '','','','','','','','','','',
926 '','','','','','','','','','');
927
928 IF l_request_id = 0 THEN
929 FND_FILE.PUT_LINE(FND_FILE.log,'CONC-REQUEST SUBMISSION FAILED');
930 ELSE
931 FND_MESSAGE.SET_NAME('SQLGL','GL_REQUEST_SUBMITTED');
932 FND_MESSAGE.SET_TOKEN('REQUEST_ID',l_request_id,FALSE);
933 l_message_text := FND_MESSAGE.GET;
934 FND_FILE.PUT_LINE(FND_FILE.log, l_message_text);
935 END IF;
936
937 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
938 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
939 END IF;
940
941 END preview_report;
942
943
944 ------------------------------------------------------------
945 -- Main Program --
946 ------------------------------------------------------------
947 BEGIN
948 initialize_process;
949
950 l_current_category := 0;
951 l_date_placed_in_service :=NULL;
952
953 FOR adjustable_assets_rec IN adjustable_assets(g_country_code) LOOP
954
955 IF g_total_records < adjustable_assets_rec.counter THEN
956 g_total_records := adjustable_assets_rec.counter;
957 END IF;
958
959 ------------------------------------------------------------
960 -- Initialize the category properly --
961 ------------------------------------------------------------
962
963 IF (adjustable_assets_rec.asset_category_id <> l_current_category) OR
964 (adjustable_assets_rec.date_placed_in_service <> l_date_placed_in_service) THEN
965
966 initialize_category (adjustable_assets_rec.asset_category_id,
967 adjustable_assets_rec.date_placed_in_service);
968
969 l_current_category := adjustable_assets_rec.asset_category_id;
970 l_date_placed_in_service := adjustable_assets_rec.date_placed_in_service;
971
972 END IF;
973
974 --------------------------------------------------------------
975 -- Obtain the revaluation rate using the --
976 -- get_revaluation_rates function --
977 --------------------------------------------------------------
978
979 l_revaluation_rate := get_revaluation_rates
980 (adjustable_assets_rec.asset_id
981 , adjustable_assets_rec.asset_type
982 , adjustable_assets_rec.cost
983 , adjustable_assets_rec.revaluation_start_date);
984
985 --------------------------------------------------------------
986 -- Insert a row in fa_mass_revaluation_rules for every asset--
987 -- that can be inflation adjusted --
988 --------------------------------------------------------------
989
990 create_revaluation_rule (p_mass_reval_id
991 , adjustable_assets_rec.asset_id
992 , l_revaluation_rate);
993 END LOOP;
994
995 --------------------------------------------------------------
996 -- If everything went fine, then commit :) --
997 --------------------------------------------------------------
998 update_control_tables (p_mass_reval_id);
999
1000
1001
1002 preview_report;
1003
1004 COMMIT;
1005 --------------------------------------------------------------
1006 -- Will notify the user records processed --
1007 --------------------------------------------------------------
1008 fnd_file.put(FND_FILE.LOG,'+');
1009 fnd_file.put(FND_FILE.LOG,LPAD('-',75,'-'));
1010 fnd_file.put(FND_FILE.LOG,'+');
1011 fnd_file.put_line( FND_FILE.LOG, '');
1012 IF g_total_records > 0 THEN
1013 fnd_message.set_name('JL', 'JL_CO_FA_PURGE_MESG');
1014 fnd_message.set_token('OPTION', g_total_records);
1015 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
1016 fnd_file.put_line( FND_FILE.LOG, '');
1017 ELSE
1018 fnd_message.set_name('JL', 'JL_CO_FA_NOTHING_TO_PROCESS');
1019 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
1020 END IF;
1021 fnd_file.put(FND_FILE.LOG,'+');
1022 fnd_file.put(FND_FILE.LOG,LPAD('-',75,'-'));
1023 fnd_file.put(FND_FILE.LOG,'+');
1024 fnd_file.put_line( FND_FILE.LOG, '');
1025 retcode := 0; -- Return normal status
1026
1027
1028 EXCEPTION
1029 WHEN TOO_MANY_COSTS THEN
1030 retcode := '2';
1031 fnd_message.set_name ('JL', 'JL_AR_FA_PREV_ADJ_COST_NAVL');
1032 fnd_file.put_line (fnd_file.log, fnd_message.get);
1033 call_status := fnd_concurrent.set_completion_status('ERROR','');
1034 /*
1035 jl_zz_fa_utilities_pkg.raise_error ('JL'
1036 , 'JL_AR_FA_PREV_ADJ_COST_NAVL', 'APP');
1037 */
1038 WHEN OTHERS THEN
1039 retcode := '2';
1040 IF g_step = 'PRICE INDEX ID' THEN
1041 fnd_message.set_name ('JL', 'JL_AR_FA_INDX_NOT_DEF_FOR_CATG');
1042 fnd_file.put_line (fnd_file.log, fnd_message.get);
1043 call_status := fnd_concurrent.set_completion_status('ERROR','');
1044 /*
1045 jl_zz_fa_utilities_pkg.raise_error ('JL'
1046 , 'JL_AR_FA_INDX_NOT_DEF_FOR_CATG', 'APP');
1047 */
1048 ELSIF g_step = 'PREVIOUS PERIOD' THEN
1049 fnd_message.set_name ('JL', 'JL_AR_FA_PERIODS_NOT_DEF_SEQUN');
1050 fnd_file.put_line (fnd_file.log, fnd_message.get);
1051 call_status := fnd_concurrent.set_completion_status('ERROR','');
1052 /*
1053 jl_zz_fa_utilities_pkg.raise_error ('JL'
1054 , 'JL_AR_FA_PERIODS_NOT_DEF_SEQUN', 'APP');
1055 */
1056 ELSIF g_step = 'MISSING PERIOD' THEN
1057 fnd_message.set_name ('JL', 'JL_AR_FA_DEPRN_PERIOD_NOT_OPEN');
1058 fnd_file.put_line (fnd_file.log, fnd_message.get);
1059 call_status := fnd_concurrent.set_completion_status('ERROR','');
1060 /*
1061 jl_zz_fa_utilities_pkg.raise_error ('JL'
1062 , 'JL_AR_FA_DEPRN_PERIOD_NOT_OPEN', 'APP');
1063 */
1064 ELSIF g_step = 'PRICE INDEX VALUE' THEN
1065 fnd_message.set_name ('JL', 'JL_AR_FA_CURR_INDX_VAL_NOT_DEF');
1066 fnd_file.put_line (fnd_file.log, fnd_message.get);
1067 call_status := fnd_concurrent.set_completion_status('ERROR','');
1068 /*
1069 jl_zz_fa_utilities_pkg.raise_error ('JL'
1070 , 'JL_AR_FA_CURR_INDX_VAL_NOT_DEF', 'APP');
1071 */
1072 ELSE
1073 jl_zz_fa_utilities_pkg.raise_ora_error;
1074 END IF;
1075
1076
1077 END reval_rules_generator;
1078
1079 END jl_zz_fa_reval_rules_pkg;