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