[Home] [Help]
PACKAGE BODY: APPS.JL_CO_FA_TA_REVAL_PKG
Source
1 PACKAGE BODY jl_co_fa_ta_reval_pkg AS
2 /* $Header: jlcoftrb.pls 120.4 2006/09/20 17:04:42 abuissa ship $ */
3
4
5 /* ======================================================================*
6 | FND Logging infrastructure |
7 * ======================================================================*/
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'JL_CO_FA_TA_REVAL_PKG';
9 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
10 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
11 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
12 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
13 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
14 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
15 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
16 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'JL.PLSQL.JL_CO_FA_TA_REVAL_PKG.';
17
18 x_last_updated_by NUMBER(15);
19 x_last_update_login NUMBER(15);
20 x_request_id NUMBER(15);
21 x_program_application_id NUMBER(15);
22 x_program_id NUMBER(15);
23 x_sysdate DATE;
24 x_statement VARCHAR2(20);
25
26 PROCEDURE track_asset( p_book_type_code VARCHAR2,
27 p_appraisal_id number);
28
29
30 PROCEDURE insert_row( p_adjustment_type VARCHAR2,
31 p_debit_credit_flag VARCHAR2,
32 p_code_combination_id NUMBER,
33 p_book_type_code VARCHAR2,
34 p_asset_id NUMBER,
35 p_adjustment_amount NUMBER,
36 p_period_counter NUMBER,
37 p_distribution_id fa_distribution_history.distribution_id%TYPE,
38 p_je_category_name VARCHAR2,
39 p_reference VARCHAR2);
40
41 PROCEDURE find_who_columns;
42
43
44 ----------------------------------------------------------------------------
45 -- PROCEDURE --
46 -- revaluate --
47 -- --
48 -- DESCRIPTION --
49 -- Use this procedure to revaluate technical appraisals --
50 -- --
51 -- PURPOSE: --
52 -- Oracle Applications Rel 11.0 --
53 -- --
54 -- PARAMETERS: --
55 -- p_book --
56 -- p_appraisal_id --
57 -- --
58 -- HISTORY: --
59 -- 08/12/98 Sujit Dalai Created --
60 -- 10/21/98 Sujit Dalai Changed the messages. --
61 ----------------------------------------------------------------------------
62
63 PROCEDURE revaluate( ERRBUF OUT NOCOPY VARCHAR2,
64 RETCODE OUT NOCOPY VARCHAR2,
65 p_book VARCHAR2,
66 p_appraisal_id NUMBER) IS
67
68 x_set_of_book_id fa_book_controls.set_of_books_id%TYPE;
69 x_deprn_calendar fa_book_controls.deprn_calendar%TYPE;
70 x_book_class fa_book_controls.book_class%TYPE;
71 x_gl_posting_allowed_flag fa_book_controls.gl_posting_allowed_flag%TYPE;
72 x_current_fiscal_year fa_book_controls.current_fiscal_year%TYPE;
73 x_accounting_flex_structure fa_book_controls.accounting_flex_structure%TYPE;
74 x_gl_je_source xla_subledgers.je_source_name%TYPE; -- Bug 5136047
75 x_distribution_source_book fa_book_controls.distribution_source_book%TYPE;
76 x_deprn_status fa_book_controls.deprn_status%TYPE;
77 x_je_category_name fa_book_controls.global_attribute13%TYPE;
78 x_currency_code gl_sets_of_books.currency_code%TYPE;
79 x_period_name fa_deprn_periods.period_name%TYPE;
80 x_user_je_source_name gl_je_sources.user_je_source_name%TYPE;
81 x_period_counter fa_book_controls.last_period_counter%TYPE;
82 x_count1 NUMBER;
83 x_end_date fa_calendar_periods.end_date%TYPE;
84 x_appr_revaluation jl_co_fa_asset_apprs.appraisal_value%TYPE := 0;
85 x_prev_revaluation jl_co_fa_asset_apprs.appraisal_value%TYPE := 0;
86 x_net_revaluation jl_co_fa_asset_apprs.appraisal_value%TYPE := 0;
87 x_category_id fa_additions.asset_category_id%TYPE := 0;
88 x_revaluation_account fa_category_books.global_attribute11%TYPE := 0;
89 x_surplus_account fa_category_books.global_attribute12%TYPE := 0;
90 x_reserve_account fa_category_books.global_attribute13%TYPE := 0;
91 x_expense_account fa_category_books.global_attribute14%TYPE := 0;
92 x_recovery_account fa_category_books.global_attribute15%TYPE := 0;
93 x_asset_cost_account_ccid fa_category_books.asset_cost_account_ccid%TYPE;
94 x_book varchar2(15);
95 x_actual_cost_acct_ccid NUMBER(15);
96 x_cost_account_code VARCHAR2(30);
97 x_rtn_value NUMBER;
98 x_asset_cost_acct_segval fa_category_books.asset_cost_acct%TYPE;
99 x_ccid NUMBER(15);
100 x_appl_id NUMBER := 101;
101 x_apps_short_name VARCHAR2(15) := 'SQLGL';
102 x_key_flex_code VARCHAR2(15) := 'GL#';
103 x_account_qualifier VARCHAR2(20) := 'GL_ACCOUNT';
104 x_account_segment_no NUMBER;
105 x_account_segment VARCHAR2(30);
106 x_temporal BOOLEAN;
107 x_delimiter VARCHAR2(30);
108 x_error_ccid BOOLEAN;
109 x_category VARCHAR2(210);
110 x_precision NUMBER(15);
111 x_ext_precision NUMBER(15);
112 x_min_acct_unit NUMBER(15);
113 err_num NUMBER;
114 err_msg VARCHAR2(2000);
115 call_status BOOLEAN;
116 NOT_A_TAX_BOOK EXCEPTION;
117 INVALID_CCID EXCEPTION;
118 CCID_NOT_FOUND EXCEPTION;
119 GL_POSTING_NOT_ALLOWED EXCEPTION;
120 -- DIFF_FISCAL_YEAR EXCEPTION;
121 DEPRN_STATUS_NOT_C EXCEPTION;
122 -- REVALUATION_RUN EXCEPTION;
123 INVALID_CURRENCY_CODE EXCEPTION;
124 JE_CAT_NOT_DEFINED EXCEPTION;
125 l_api_name CONSTANT VARCHAR2(30) := 'REVALUATE';
126
127
128 CURSOR c_appraisal IS
129 SELECT appraisal_id,
130 currency_code,
131 appraisal_date,
132 fiscal_year
133 FROM jl_co_fa_appraisals
134 WHERE appraisal_id = p_appraisal_id
135 FOR UPDATE OF appraisal_status;
136
137 CURSOR c_asset(p_appr_id NUMBER,
138 p_period_counter NUMBER ) IS
139
140 SELECT ap.asset_number,
141 ap.appraisal_value,
142 ad.asset_category_id,
143 ad.asset_id,
144 ad.current_units,
145 ab.cost,
146 fnd_number.canonical_to_number(nvl(ab.global_attribute2,0)) prev_revaluation,
147 dr.deprn_reserve,
148 ab.date_placed_in_service
149 FROM jl_co_fa_asset_apprs ap,
150 fa_additions ad,
151 fa_books ab,
152 fa_deprn_summary dr
153 WHERE ap.asset_number = ad.asset_number
154 AND ap.appraisal_id = p_appr_id
155 AND ad.asset_id = ab.asset_id
156 AND ab.book_type_code = p_book
157 AND dr.book_type_code (+) = p_book
158 AND dr.asset_id (+) = ad.asset_id
159 AND dr.period_counter (+) = p_period_counter
160 AND ab.transaction_header_id_out IS NULL
161 AND ab.date_ineffective IS NULL
162 ORDER BY ad.asset_category_id
163 FOR UPDATE OF ap.status;
164
165 CURSOR c_distribution(p_asset_id fa_additions.asset_id%TYPE,
166 p_book_type VARCHAR2) IS
167
168 SELECT distribution_id,
169 units_assigned,
170 code_combination_id,
171 transaction_units
172 FROM fa_distribution_history
173 WHERE book_type_code = p_book_type
174 AND transaction_header_id_out IS NULL
175 AND date_ineffective IS NULL
176 AND asset_id = p_asset_id;
177
178
179 BEGIN
180
181 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
182 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
183 END IF;
184
185
186 fnd_message.set_name('JL', 'JL_CO_FA_PARAMETER');
187 fnd_file.put_line( FND_FILE.LOG, fnd_message.get);
188 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
189 fnd_message.set_name('JL', 'JL_CO_FA_BOOK');
190 fnd_message.set_token('BOOK', p_book);
191 fnd_file.put_line( 1, fnd_message.get);
192 fnd_message.set_name('JL', 'JL_CO_FA_APPR_NUMBER');
193 fnd_message.set_token('APPRAISAL_NUMBER', p_appraisal_id);
194 fnd_file.put_line( 1, fnd_message.get);
195 fnd_file.put_line(FND_FILE.LOG, '----------------------------------------');
196 ---------------------------------------------------------
197 -- Find who_columns values --
198 ---------------------------------------------------------
199
200
201 find_who_columns;
202
203 ---------------------------------------------------------
204 -- get information regarding the book from --
205 -- fa_book_of_controls, gl_sets_of_books, --
206 -- gl_je_sources, fa_deprn_period and --
207 -- fa_calender_periods --
208 ---------------------------------------------------------
209 x_statement := 'BOOK_INFO';
210
211 /* Bug 5136047:
212 In R12 due to SLA uptake, core FA no longer allows "GL journal entry source"
213 to be entered using Book Controls window.
214 "GL journal entry source" is set at the application level in SLA.
215 Though, gl_je_source value is available in fa_book_controls
216 for books upgraded from R11i, the source of truth is always as follows.
217 The "GL journal entry source" will be derived from JE_SOURCE_NAME from
218 XLA_SUBLEDGERS for application_id = 140.
219 Hence, there is no need to look at fa_book_controls for books upgraded
220 from R1i.
221
222 SELECT bc.set_of_books_id,
223 bc.deprn_calendar,
224 bc.book_class,
225 bc.gl_posting_allowed_flag,
226 bc.current_fiscal_year,
227 bc.accounting_flex_structure,
228 bc.gl_je_source,
229 bc.distribution_source_book,
230 bc.last_period_counter,
231 bc.deprn_status,
232 bc.global_attribute13,
233 sb.currency_code,
234 js.user_je_source_name,
235 dp.period_name,
236 cp.end_date
237 INTO x_set_of_book_id,
238 x_deprn_calendar,
239 x_book_class,
240 x_gl_posting_allowed_flag,
241 x_current_fiscal_year,
242 x_accounting_flex_structure,
243 x_gl_je_source,
244 x_distribution_source_book,
245 x_period_counter,
246 x_deprn_status,
247 x_je_category_name,
248 x_currency_code,
249 x_user_je_source_name,
250 x_period_name,
251 x_end_date
252 FROM fa_book_controls bc,
253 gl_sets_of_books sb,
254 gl_je_sources js,
255 fa_deprn_periods dp,
256 fa_calendar_periods cp
257 WHERE bc.book_type_code = p_book
258 AND sb.set_of_books_id = bc.set_of_books_id
259 AND js.je_source_name = bc.gl_je_source
260 AND dp.book_type_code = p_book
261 AND dp.period_counter = bc.last_period_counter
262 AND cp.calendar_type = bc.deprn_calendar
263 AND cp.period_name = dp.period_name;
264 */
265
266 SELECT bc.set_of_books_id,
267 bc.deprn_calendar,
268 bc.book_class,
269 bc.gl_posting_allowed_flag,
270 bc.current_fiscal_year,
271 bc.accounting_flex_structure,
272 bc.distribution_source_book,
273 bc.last_period_counter,
274 bc.deprn_status,
275 bc.global_attribute13,
276 sb.currency_code,
277 dp.period_name,
278 cp.end_date
279 INTO x_set_of_book_id,
280 x_deprn_calendar,
281 x_book_class,
282 x_gl_posting_allowed_flag,
283 x_current_fiscal_year,
284 x_accounting_flex_structure,
285 x_distribution_source_book,
286 x_period_counter,
287 x_deprn_status,
288 x_je_category_name,
289 x_currency_code,
290 x_period_name,
291 x_end_date
292 FROM fa_book_controls bc,
293 gl_sets_of_books sb,
294 fa_deprn_periods dp,
295 fa_calendar_periods cp
296 WHERE bc.book_type_code = p_book
297 AND sb.set_of_books_id = bc.set_of_books_id
298 AND dp.book_type_code = p_book
299 AND dp.period_counter = bc.last_period_counter
300 AND cp.calendar_type = bc.deprn_calendar
301 AND cp.period_name = dp.period_name;
302
303 SELECT xs.je_source_name,
304 js.user_je_source_name
305 INTO x_gl_je_source,
306 x_user_je_source_name
307 FROM gl_je_sources js,
308 xla_subledgers xs
309 WHERE js.je_source_name = xs.je_source_name
310 AND xs.application_id = 140;
311
312
313 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
314
315 fnd_file.put_line( 1, 'set_of_book_id :'||to_char(x_set_of_book_id) );
316 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'set_of_book_id :'||to_char(x_set_of_book_id) );
317 fnd_file.put_line( 1, 'deprn_calendar:'||x_deprn_calendar);
318 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'deprn_calendar:'||x_deprn_calendar);
319 fnd_file.put_line( 1, 'x_book_class :'||x_book_class);
320 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_book_class :'||x_book_class);
321 fnd_file.put_line( 1, 'x_gl_posting_allowed_flag :'||x_gl_posting_allowed_flag);
322 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_gl_posting_allowed_flag :'||x_gl_posting_allowed_flag);
323 fnd_file.put_line( 1, 'x_current_fiscal_year :'||to_char(x_current_fiscal_year) );
324 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_current_fiscal_year :'||to_char(x_current_fiscal_year) );
325 fnd_file.put_line( 1, 'x_accounting_flex_structure :'||to_char(x_accounting_flex_structure) );
326 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_accounting_flex_structure :'||to_char(x_accounting_flex_structure) );
327 fnd_file.put_line( 1, 'x_gl_je_source :'||x_gl_je_source);
328 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_gl_je_source :'||x_gl_je_source);
329 fnd_file.put_line( 1, 'x_distribution_source_book :'||x_distribution_source_book);
330 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_distribution_source_book :'||x_distribution_source_book);
331 fnd_file.put_line( 1, 'x_period_counter :'||to_char(x_period_counter) );
332 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_period_counter :'||to_char(x_period_counter) );
333 fnd_file.put_line( 1, 'x_deprn_status :'||x_deprn_status) ;
334 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_deprn_status :'||x_deprn_status) ;
335 fnd_file.put_line( 1, 'x_je_category_name :'||x_je_category_name) ;
336 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_je_category_name :'||x_je_category_name) ;
337 fnd_file.put_line( 1, 'x_currency_code :'||x_currency_code);
338 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_currency_code :'||x_currency_code);
339 fnd_file.put_line( 1, 'x_user_je_source_name :'||x_user_je_source_name) ;
340 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_user_je_source_name :'||x_user_je_source_name);
341 fnd_file.put_line( 1, 'x_period_name :'||x_period_name);
342 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_period_name :'||x_period_name);
343 fnd_file.put_line( 1, 'x_end_date :'||to_char(x_end_date));
344 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_end_date :'||to_char(x_end_date));
345
346
347 END IF;
348
349
350 ---------------------------------------------------------
351 -- Show the error conditions and finish the procedure --
352 -- if any of the following conditions not satisfied --
353 ---------------------------------------------------------
354
355
356 /* IF (x_gl_posting_allowed_flag <> 'YES') THEN
357 RAISE GL_POSTING_NOT_ALLOWED;
358 END IF; */
359
360
361 IF (x_deprn_status <> 'C') THEN
362 RAISE DEPRN_STATUS_NOT_C;
363 END IF;
364
365 IF (x_je_category_name IS NULL) THEN
366 RAISE JE_CAT_NOT_DEFINED;
367 END IF;
368
369 ---------------------------------------------------------
370 -- Get ccid segment information --
371 ---------------------------------------------------------
372 x_statement := 'FLEX_INFO';
373 x_delimiter := fnd_flex_ext.get_delimiter (x_apps_short_name,
374 x_key_flex_code,
375 x_accounting_flex_structure
376 );
377
378 x_temporal := fnd_flex_apis.get_qualifier_segnum
379 (x_appl_id,
380 x_key_flex_code,
381 x_accounting_flex_structure,
382 x_account_qualifier,
383 x_account_segment_no);
384
385
386 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
387
388 fnd_file.put_line( 1, 'Balancing Qualifier :'||x_account_qualifier );
389 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Balancing Qualifier :'||x_account_qualifier);
390 fnd_file.put_line( 1, 'Balancing segment no :'||to_char(x_account_segment_no));
391 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Balancing segment no :'||to_char(x_account_segment_no));
392 END IF;
393
394 ---------------------------------------------------------
395 -- Get Currency Information --
396 ---------------------------------------------------------
397 x_statement := 'CURR_INFO';
398 fnd_currency.get_info( currency_code => x_currency_code,
399 precision => x_precision,
400 ext_precision => x_ext_precision,
401 min_acct_unit => x_min_acct_unit);
402 ---------------------------------------------------------
403 -- Start processing appraisals --
404 ---------------------------------------------------------
405
406 <<appraisals>>
407
408 FOR rec_appraisal IN c_appraisal LOOP
409
410 fnd_message.set_name('JL', 'JL_CO_FA_APPR_MESG');
411 fnd_message.set_token('APPRAISAL_NUMBER', rec_appraisal.appraisal_id);
412 fnd_file.put_line( 1, fnd_message.get);
413
414 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
415 fnd_file.put_line( 1, 'Currency code in appraisal :'||rec_appraisal.currency_code);
416 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Currency code in appraisal :'||rec_appraisal.currency_code);
417 fnd_file.put_line( 1, 'Appraisal Date :'||rec_appraisal.appraisal_date);
418 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal Date :'||rec_appraisal.appraisal_date);
419 END IF;
420
421 /* IF (x_current_fiscal_year <> rec_appraisal.fiscal_year) THEN
422 RAISE DIFF_FISCAL_YEAR;
423 END IF;
424
425 SELECT count(*)
426 INTO x_count1
427 FROM jl_co_fa_adjustments
428 WHERE book_type_code = p_book
429 AND reference = rec_appraisal.appraisal_id
430 AND rownum < 2; */
431
432
433 ---------------------------------------------------------
434 -- Discard the appraisals in case of following --
435 -- conditions ie. revaluation is done for the appraisal--
436 -- and currency_code is different --
437 ---------------------------------------------------------
438
439 /*IF (x_count1 > 0) THEN
440
441 RAISE REVALUATION_RUN; */
442
443 IF (rec_appraisal.currency_code <> x_currency_code) THEN
444
445 RAISE INVALID_CURRENCY_CODE;
446
447 ELSE
448
449 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
450 fnd_file.put_line( 1, 'appraisal_id : '||to_char(rec_appraisal.appraisal_id));
451 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'appraisal_id : '||to_char(rec_appraisal.appraisal_id));
452 fnd_file.put_line( 1, 'End_date : '||x_end_date);
453 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'End_date : '||x_end_date);
454 END IF;
455
456 ---------------------------------------------------------
457 -- Get the assets for the appraisals. select the assets--
458 -- those are open for the depreciation book and not --
459 -- retired. --
460 ---------------------------------------------------------
461
462 <<assets>>
463
464 FOR rec_asset IN c_asset(rec_appraisal.appraisal_id, x_period_counter) LOOP
465
466 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
467
468 fnd_file.put_line( 1, 'Asset_Number :'||rec_asset.asset_number);
469 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Asset_Number :'||rec_asset.asset_number);
470 fnd_file.put_line( 1, 'Appraisal value :'||to_char(rec_asset.appraisal_value));
471 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal value :'||to_char(rec_asset.appraisal_value));
472 fnd_file.put_line( 1, 'Asset Category Id :'||to_char(rec_asset.asset_category_id));
473 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Asset Category Id :'||to_char(rec_asset.asset_category_id));
474 fnd_file.put_line( 1, 'Asset Id :'||to_char(rec_asset.asset_id));
475 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Asset Id :'||to_char(rec_asset.asset_id));
476 fnd_file.put_line( 1, 'Asset Cost :'||to_char(rec_asset.cost));
477 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Asset Cost :'||to_char(rec_asset.cost));
478 fnd_file.put_line( 1, 'Previous revaluation :'||rec_asset.prev_revaluation);
479 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Previous revaluation :'||rec_asset.prev_revaluation);
480 fnd_file.put_line( 1, 'Depreciation reserve :'||to_char(rec_asset.deprn_reserve));
481 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Depreciation reserve :'||to_char(rec_asset.deprn_reserve));
482 END IF;
483
484 ---------------------------------------------------------
485 -- If asset cost is zero or has a future date placed in--
486 -- service then do not process it --
487
488 ---------------------------------------------------------
489
490 IF (NVL(rec_asset.cost, 0) = 0) THEN
491
492 fnd_message.set_name('JL', 'JL_ZZ_FA_ASSET_RETIRED');
493 fnd_message.set_token('ASSET_NUMBER', rec_asset.asset_number);
494 fnd_message.set_token('BOOK', p_book);
495 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
496 x_temporal := fnd_concurrent.set_completion_status( status => 'WARNING',
497 message => '');
498 null;
499 /* ELSIF (rec_asset.date_placed_in_service > x_end_date) THEN
500
501 fnd_message.set_name('JL', 'JL_ZZ_FA_FUTURE_DATE');
502 fnd_message.set_token('ASSET_NUMBER', rec_asset.asset_number);
503 fnd_message.set_token('BOOK', p_book);
504 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
505 x_temporal := fnd_concurrent.set_completion_status( status => 'WARNING',
506 message => '');*/
507 ELSE
508
509 ---------------------------------------------------------
510 -- Calculate Appraisal Revaluation, previous --
511 -- revaluation, net revaluation for the asset --
512 ---------------------------------------------------------
513
514 x_appr_revaluation:= rec_asset.appraisal_value - (rec_asset.cost - rec_asset.deprn_reserve);
515 x_prev_revaluation := rec_asset.prev_revaluation;
516 x_net_revaluation := x_appr_revaluation - x_prev_revaluation;
517
518 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
519
520 fnd_file.put_line( 1, 'Appraisal Revaluation :'||to_char(x_appr_revaluation));
521 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Appraisal Revaluation :'||to_char(x_appr_revaluation));
522 fnd_file.put_line( 1, 'Previous Revaluation :'||to_char(x_prev_revaluation));
523 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Previous Revaluation :'||to_char(x_prev_revaluation));
524 fnd_file.put_line( 1, 'Net Revaluation :'||to_char(x_net_revaluation));
525 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Net Revaluation :'||to_char(x_net_revaluation));
526
527 END IF;
528
529 ---------------------------------------------------------
530 -- Find accounts and natural account segment values --
531 -- from FA_CATEGORY_BOOKS --
532 ---------------------------------------------------------
533
534 IF (x_category_id <> rec_asset.asset_category_id) THEN
535 x_category_id := rec_asset.asset_category_id;
536 x_statement := 'ACCT_INFO';
537 SELECT asset_cost_account_ccid,
538 asset_cost_acct,
539 NVL(global_attribute11, 0),
540 NVL(global_attribute12, 0),
541 NVL(global_attribute13, 0),
542 NVL(global_attribute14, 0),
543 NVL(global_attribute15, 0)
544 INTO x_asset_cost_account_ccid,
545 x_asset_cost_acct_segval,
546 x_revaluation_account,
547 x_surplus_account,
548 x_reserve_account,
549 x_expense_account,
550 x_recovery_account
551 FROM fa_category_books
552 WHERE category_id = x_category_id
553 AND book_type_code = p_book;
554
555 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
556 fnd_file.put_line( 1, 'For Category ID '||to_char(x_category_id)||' and book '||p_book||' :');
557 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'For Category ID '||to_char(x_category_id)||' and book '||p_book||' :');
558 fnd_file.put_line( 1, 'CCID of revaluation account :'||x_revaluation_account);
559 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'CCID of revaluation account :'||x_revaluation_account);
560 fnd_file.put_line( 1, 'CCID of surplus account account :'||x_surplus_account);
561 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'CCID of surplus account account :'||x_surplus_account);
562 fnd_file.put_line( 1, 'CCID of reserve account :'||x_reserve_account);
563 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'CCID of reserve account :'||x_reserve_account);
564 fnd_file.put_line( 1, 'CCID of expense account :'||x_expense_account);
565 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'CCID of expense account :'||x_expense_account);
566 fnd_file.put_line( 1, 'CCID of recovery account :'||x_recovery_account);
567 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'CCID of recovery account :'||x_recovery_account);
568 fnd_file.put_line( 1, 'Default cost account ccid :'||to_char(x_asset_cost_account_ccid));
569 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Default cost account ccid :'||to_char(x_asset_cost_account_ccid));
570 fnd_file.put_line( 1, 'Account segment Value of cost acct :'||x_asset_cost_acct_segval);
571 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Account segment Value of cost acct :'||x_asset_cost_acct_segval);
572 END IF;
573
574 ---------------------------------------------------------
575 -- Finish the procedure with error if CCID is less than--
576 -- equal zero --
577 ---------------------------------------------------------
578
579 IF ( x_revaluation_account <= 0 OR
580 x_surplus_account <= 0 OR
581 x_reserve_account <= 0 OR
582 x_expense_account <= 0 OR
583 x_recovery_account <= 0 ) THEN
584
585 RAISE INVALID_CCID;
586 END IF;
587 END IF;
588 ---------------------------------------------------------
589 -- Calculate accounts for each distribution of asset --
590 ---------------------------------------------------------
591
592 IF (x_book_class = 'TAX') THEN
593 x_book := x_distribution_source_book;
594 ELSE
595 x_book := p_book;
596 END IF;
597
598 << distribution>>
599
600 FOR rec_dist IN c_distribution(rec_asset.asset_id, x_book) LOOP
601
602
603 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
604 fnd_file.put_line( 1, 'Distribution ID :'||to_char(rec_dist.distribution_id));
605 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Distribution ID :'||to_char(rec_dist.distribution_id));
606 fnd_file.put_line( 1, 'Assigned Unit :'||to_char(rec_dist.units_assigned));
607 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Assigned Unit :'||to_char(rec_dist.units_assigned));
608 fnd_file.put_line( 1, 'CCID :'||to_char(rec_dist.code_combination_id));
609 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'CCID :'||to_char(rec_dist.code_combination_id));
610 END IF;
611
612 ---------------------------------------------------------
613 -- Get value of actual cost_account_ccid --
614 ---------------------------------------------------------
615 x_statement := 'COST_ACCT_INFO';
616 FA_GCCID_PKG.fafbgcc_proc (X_book_type_code => p_book,
617 X_fn_trx_code => 'ASSET_COST_ACCT',
618 X_dist_ccid => rec_dist.code_combination_id,
619 X_acct_segval => x_asset_cost_acct_segval,
620 X_account_ccid => x_asset_cost_account_ccid,
621 X_distribution_id => rec_dist.distribution_id,
622 X_rtn_ccid => x_actual_cost_acct_ccid,
623 X_concat_segs => x_cost_account_code,
624 X_return_value => x_rtn_value);
625
626 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
627 fnd_file.put_line( 1, 'actual cost acct ccid :'||to_char(x_actual_cost_acct_ccid));
628 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'actual cost acct ccid :'||to_char(x_actual_cost_acct_ccid));
629 END IF;
630
631
632 ---------------------------------------------------------
633 -- Account for different cases in Technical Appraisals --
634 ---------------------------------------------------------
635 x_statement := 'INSERT_ADJ';
636 IF (x_appr_revaluation >= 0 AND
637 x_prev_revaluation >= 0 AND
638 x_net_revaluation >= 0) THEN
639
640
641
642
643
644 jl_co_fa_accounting_pkg.change_account(
645 p_chart_of_accounts_id => x_accounting_flex_structure,
646 p_apps_short_name => x_apps_short_name,
647 p_key_flex_code => x_key_flex_code,
648 p_num_segment => x_account_segment_no,
649 p_account_ccid => x_actual_cost_acct_ccid,
650 p_account_segment => x_revaluation_account,
651 p_delimiter => x_delimiter,
652 p_returned_ccid => x_ccid,
653 p_error_ccid => x_error_ccid);
654
655 IF NOT (x_error_ccid) THEN
656 RAISE CCID_NOT_FOUND;
657 END IF;
658
659 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
660 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
661 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
662 END IF;
663
664 insert_row( p_adjustment_type => 'APPR_REVAL',
665 p_debit_credit_flag => 'DR',
666 p_code_combination_id => x_ccid,
667 p_book_type_code => p_book,
668 p_asset_id => rec_asset.asset_id,
669 p_adjustment_amount => ROUND((x_net_revaluation *
670 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
671 p_period_counter => x_period_counter,
672 p_distribution_id => rec_dist.distribution_id,
673 p_je_category_name => x_je_category_name,
674 p_reference => to_char(rec_appraisal.appraisal_id));
675
676
677
678 jl_co_fa_accounting_pkg.change_account(
679 p_chart_of_accounts_id => x_accounting_flex_structure,
680 p_apps_short_name => x_apps_short_name,
681 p_key_flex_code => x_key_flex_code,
682 p_num_segment => x_account_segment_no,
683 p_account_ccid => x_actual_cost_acct_ccid,
684 p_account_segment => x_surplus_account,
685 p_delimiter => x_delimiter,
686 p_returned_ccid => x_ccid,
687 p_error_ccid => x_error_ccid);
688
689 IF NOT (x_error_ccid) THEN
690 RAISE CCID_NOT_FOUND;
691 END IF;
692
693
694 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
695 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
696 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
697 END IF;
698
699
700 insert_row( p_adjustment_type => 'APPR_SURPL',
701 p_debit_credit_flag => 'CR',
702 p_code_combination_id => x_ccid,
703 p_book_type_code => p_book,
704 p_asset_id => rec_asset.asset_id,
705 p_adjustment_amount => ROUND((x_net_revaluation * (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
706 p_period_counter => x_period_counter,
707 p_distribution_id => rec_dist.distribution_id,
708 p_je_category_name => x_je_category_name,
709 p_reference => to_char(rec_appraisal.appraisal_id));
710
711
712 ELSIF (x_appr_revaluation >= 0 AND
713 x_prev_revaluation >= 0 AND
714 x_net_revaluation < 0) THEN
715
716
717 jl_co_fa_accounting_pkg.change_account(
718 p_chart_of_accounts_id => x_accounting_flex_structure,
719 p_apps_short_name => x_apps_short_name,
720 p_key_flex_code => x_key_flex_code,
721 p_num_segment => x_account_segment_no,
722 p_account_ccid => x_actual_cost_acct_ccid,
723 p_account_segment => x_surplus_account,
724 p_delimiter => x_delimiter,
725 p_returned_ccid => x_ccid,
726 p_error_ccid => x_error_ccid);
727
728 IF NOT (x_error_ccid) THEN
729 RAISE CCID_NOT_FOUND;
730 END IF;
731
732 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
733 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
734 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
735 END IF;
736
737
738 insert_row( p_adjustment_type => 'APPR_SURPL',
739 p_debit_credit_flag => 'DR',
740 p_code_combination_id => x_ccid,
741 p_book_type_code => p_book,
742 p_asset_id => rec_asset.asset_id,
743 p_adjustment_amount => ROUND((x_net_revaluation *
744 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
745 p_period_counter => x_period_counter,
746 p_distribution_id => rec_dist.distribution_id,
747 p_je_category_name => x_je_category_name,
748 p_reference => to_char(rec_appraisal.appraisal_id));
749
750
751 jl_co_fa_accounting_pkg.change_account(
752 p_chart_of_accounts_id => x_accounting_flex_structure,
753 p_apps_short_name => x_apps_short_name,
754 p_key_flex_code => x_key_flex_code,
755 p_num_segment => x_account_segment_no,
756 p_account_ccid => x_actual_cost_acct_ccid,
757 p_account_segment => x_revaluation_account,
758 p_delimiter => x_delimiter,
759 p_returned_ccid => x_ccid,
760 p_error_ccid => x_error_ccid);
761
762 IF NOT (x_error_ccid) THEN
763 RAISE CCID_NOT_FOUND;
764 END IF;
765
766 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
767 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
768 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
769 END IF;
770
771
772 insert_row( p_adjustment_type => 'APPR_REVAL',
773 p_debit_credit_flag => 'CR',
774 p_code_combination_id => x_ccid,
775 p_book_type_code => p_book,
776 p_asset_id => rec_asset.asset_id,
777 p_adjustment_amount => ROUND((x_net_revaluation *
778 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
779 p_period_counter => x_period_counter,
780 p_distribution_id => rec_dist.distribution_id,
781 p_je_category_name => x_je_category_name,
782 p_reference => to_char(rec_appraisal.appraisal_id));
783
784
785 ELSIF (x_appr_revaluation >= 0 AND
786 x_prev_revaluation < 0 ) THEN
787
788
789 jl_co_fa_accounting_pkg.change_account(
790 p_chart_of_accounts_id => x_accounting_flex_structure,
791 p_apps_short_name => x_apps_short_name,
792 p_key_flex_code => x_key_flex_code,
793 p_num_segment => x_account_segment_no,
794 p_account_ccid => x_actual_cost_acct_ccid,
795 p_account_segment => x_reserve_account,
796 p_delimiter => x_delimiter,
797 p_returned_ccid => x_ccid,
798 p_error_ccid => x_error_ccid);
799
800 IF NOT (x_error_ccid) THEN
801 RAISE CCID_NOT_FOUND;
802 END IF;
803
804
805 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
806 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
807 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
808 END IF;
809
810
811 insert_row( p_adjustment_type => 'APPR_RESRV',
812 p_debit_credit_flag => 'DR',
813 p_code_combination_id => x_ccid,
814 p_book_type_code => p_book,
815 p_asset_id => rec_asset.asset_id,
816 p_adjustment_amount => ROUND((x_prev_revaluation *
817 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
818 p_period_counter => x_period_counter,
819 p_distribution_id => rec_dist.distribution_id,
820 p_je_category_name => x_je_category_name,
821 p_reference => to_char(rec_appraisal.appraisal_id));
822
823 jl_co_fa_accounting_pkg.change_account(
824 p_chart_of_accounts_id => x_accounting_flex_structure,
825 p_apps_short_name => x_apps_short_name,
826 p_key_flex_code => x_key_flex_code,
827 p_num_segment => x_account_segment_no,
828 p_account_ccid => x_actual_cost_acct_ccid,
829 p_account_segment => x_recovery_account,
830 p_delimiter => x_delimiter,
831 p_returned_ccid => x_ccid,
832 p_error_ccid => x_error_ccid);
833
834 IF NOT (x_error_ccid) THEN
835 RAISE CCID_NOT_FOUND;
836 END IF;
837
838 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
839 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
840 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
841 END IF;
842
843
844 insert_row( p_adjustment_type => 'APPR_RESRV_REC',
845 p_debit_credit_flag => 'CR',
846 p_code_combination_id => x_ccid,
847 p_book_type_code => p_book,
848 p_asset_id => rec_asset.asset_id,
849 p_adjustment_amount => ROUND((x_prev_revaluation *
850 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
851 p_period_counter => x_period_counter,
852 p_distribution_id => rec_dist.distribution_id,
853 p_je_category_name => x_je_category_name,
854 p_reference => to_char(rec_appraisal.appraisal_id));
855
856 jl_co_fa_accounting_pkg.change_account(
857 p_chart_of_accounts_id => x_accounting_flex_structure,
858 p_apps_short_name => x_apps_short_name,
859 p_key_flex_code => x_key_flex_code,
860 p_num_segment => x_account_segment_no,
861 p_account_ccid => x_actual_cost_acct_ccid,
862 p_account_segment => x_revaluation_account,
863 p_delimiter => x_delimiter,
864 p_returned_ccid => x_ccid,
865 p_error_ccid => x_error_ccid);
866
867 IF NOT (x_error_ccid) THEN
868 RAISE CCID_NOT_FOUND;
869 END IF;
870
871 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
872 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
873 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
874 END IF;
875
876
877 insert_row( p_adjustment_type => 'APPR_REVAL',
878 p_debit_credit_flag => 'DR',
879 p_code_combination_id => x_ccid,
880 p_book_type_code => p_book,
881 p_asset_id => rec_asset.asset_id,
882 p_adjustment_amount => ROUND((x_appr_revaluation *
883 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
884 p_period_counter => x_period_counter,
885 p_distribution_id => rec_dist.distribution_id,
886 p_je_category_name => x_je_category_name,
887 p_reference => to_char(rec_appraisal.appraisal_id));
888
889
890 jl_co_fa_accounting_pkg.change_account(
891 p_chart_of_accounts_id => x_accounting_flex_structure,
892 p_apps_short_name => x_apps_short_name,
893 p_key_flex_code => x_key_flex_code,
894 p_num_segment => x_account_segment_no,
895 p_account_ccid => x_actual_cost_acct_ccid,
896 p_account_segment => x_surplus_account,
897 p_delimiter => x_delimiter,
898 p_returned_ccid => x_ccid,
899 p_error_ccid => x_error_ccid);
900
901 IF NOT (x_error_ccid) THEN
902 RAISE CCID_NOT_FOUND;
903 END IF;
904
905 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
906 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
907 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
908 END IF;
909
910
911
912 insert_row( p_adjustment_type => 'APPR_SURPL',
913 p_debit_credit_flag => 'CR',
914 p_code_combination_id => x_ccid,
915 p_book_type_code => p_book,
916 p_asset_id => rec_asset.asset_id,
917 p_adjustment_amount => ROUND((x_appr_revaluation *
918 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
919 p_period_counter => x_period_counter,
920 p_distribution_id => rec_dist.distribution_id,
921 p_je_category_name => x_je_category_name,
922 p_reference => to_char(rec_appraisal.appraisal_id));
923
924
925
926 ELSIF (x_appr_revaluation < 0 AND
927 x_prev_revaluation >= 0) THEN
928
929
930 jl_co_fa_accounting_pkg.change_account(
931 p_chart_of_accounts_id => x_accounting_flex_structure,
932 p_apps_short_name => x_apps_short_name,
933 p_key_flex_code => x_key_flex_code,
934 p_num_segment => x_account_segment_no,
935 p_account_ccid => x_actual_cost_acct_ccid,
936 p_account_segment => x_surplus_account,
937 p_delimiter => x_delimiter,
938 p_returned_ccid => x_ccid,
939 p_error_ccid => x_error_ccid);
940
941 IF NOT (x_error_ccid) THEN
942 RAISE CCID_NOT_FOUND;
943 END IF;
944
945
946 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
947 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
948 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
949 END IF;
950
951
952 insert_row( p_adjustment_type => 'APPR_SURPL',
953 p_debit_credit_flag => 'DR',
954 p_code_combination_id => x_ccid,
955 p_book_type_code => p_book,
956 p_asset_id => rec_asset.asset_id,
957 p_adjustment_amount => ROUND((x_prev_revaluation *
958 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
959 p_period_counter => x_period_counter,
960 p_distribution_id => rec_dist.distribution_id,
961 p_je_category_name => x_je_category_name,
962 p_reference => to_char(rec_appraisal.appraisal_id));
963
964 jl_co_fa_accounting_pkg.change_account(
965 p_chart_of_accounts_id => x_accounting_flex_structure,
966 p_apps_short_name => x_apps_short_name,
967 p_key_flex_code => x_key_flex_code,
968 p_num_segment => x_account_segment_no,
969 p_account_ccid => x_actual_cost_acct_ccid,
970 p_account_segment => x_revaluation_account,
971 p_delimiter => x_delimiter,
972 p_returned_ccid => x_ccid,
973 p_error_ccid => x_error_ccid);
974
975 IF NOT (x_error_ccid) THEN
976 RAISE CCID_NOT_FOUND;
977 END IF;
978
979 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
980 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
981 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
982 END IF;
983
984
985 insert_row( p_adjustment_type => 'APPR_REVAL',
986 p_debit_credit_flag => 'CR',
987 p_code_combination_id => x_ccid,
988 p_book_type_code => p_book,
989 p_asset_id => rec_asset.asset_id,
990 p_adjustment_amount => ROUND((x_prev_revaluation *
991 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
992 p_period_counter => x_period_counter,
993 p_distribution_id => rec_dist.distribution_id,
994 p_je_category_name => x_je_category_name,
995 p_reference => to_char(rec_appraisal.appraisal_id));
996
997
998 jl_co_fa_accounting_pkg.change_account(
999 p_chart_of_accounts_id => x_accounting_flex_structure,
1000 p_apps_short_name => x_apps_short_name,
1001 p_key_flex_code => x_key_flex_code,
1002 p_num_segment => x_account_segment_no,
1003 p_account_ccid => x_actual_cost_acct_ccid,
1004 p_account_segment => x_expense_account,
1005 p_delimiter => x_delimiter,
1006 p_returned_ccid => x_ccid,
1007 p_error_ccid => x_error_ccid);
1008
1009 IF NOT (x_error_ccid) THEN
1010 RAISE CCID_NOT_FOUND;
1011 END IF;
1012
1013 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1014 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
1015 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
1016 END IF;
1017
1018
1019 insert_row( p_adjustment_type => 'APPR_RESRV_EXP',
1020 p_debit_credit_flag => 'DR',
1021 p_code_combination_id => x_ccid,
1022 p_book_type_code => p_book,
1023 p_asset_id => rec_asset.asset_id,
1024 p_adjustment_amount => ROUND((x_appr_revaluation *
1025 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
1026 p_period_counter => x_period_counter,
1027 p_distribution_id => rec_dist.distribution_id,
1028 p_je_category_name => x_je_category_name,
1029 p_reference => to_char(rec_appraisal.appraisal_id));
1030
1031 jl_co_fa_accounting_pkg.change_account(
1032 p_chart_of_accounts_id => x_accounting_flex_structure,
1033 p_apps_short_name => x_apps_short_name,
1034 p_key_flex_code => x_key_flex_code,
1035 p_num_segment => x_account_segment_no,
1036 p_account_ccid => x_actual_cost_acct_ccid,
1037 p_account_segment => x_reserve_account,
1038 p_delimiter => x_delimiter,
1039 p_returned_ccid => x_ccid,
1040 p_error_ccid => x_error_ccid);
1041
1042 IF NOT (x_error_ccid) THEN
1043 RAISE CCID_NOT_FOUND;
1044 END IF;
1045
1046 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1047 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
1048 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
1049 END IF;
1050
1051
1052 insert_row( p_adjustment_type => 'APPR_RESRV',
1053 p_debit_credit_flag => 'CR',
1054 p_code_combination_id => x_ccid,
1055 p_book_type_code => p_book,
1056 p_asset_id => rec_asset.asset_id,
1057 p_adjustment_amount => ROUND((x_appr_revaluation * (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
1058 p_period_counter => x_period_counter,
1059 p_distribution_id => rec_dist.distribution_id,
1060 p_je_category_name => x_je_category_name,
1061 p_reference => to_char(rec_appraisal.appraisal_id));
1062
1063
1064 ELSIF (x_appr_revaluation < 0 AND
1065 x_prev_revaluation < 0 AND
1066 x_net_revaluation >= 0) THEN
1067
1068
1069 jl_co_fa_accounting_pkg.change_account(
1070 p_chart_of_accounts_id => x_accounting_flex_structure,
1071 p_apps_short_name => x_apps_short_name,
1072 p_key_flex_code => x_key_flex_code,
1073 p_num_segment => x_account_segment_no,
1074 p_account_ccid => x_actual_cost_acct_ccid,
1075 p_account_segment => x_reserve_account,
1076 p_delimiter => x_delimiter,
1077 p_returned_ccid => x_ccid,
1078 p_error_ccid => x_error_ccid);
1079
1080 IF NOT (x_error_ccid) THEN
1081 RAISE CCID_NOT_FOUND;
1082 END IF;
1083
1084 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1085 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
1086 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
1087 END IF;
1088
1089
1090 insert_row( p_adjustment_type => 'APPR_RESRV',
1091 p_debit_credit_flag => 'DR',
1092 p_code_combination_id => x_ccid,
1093 p_book_type_code => p_book,
1094 p_asset_id => rec_asset.asset_id,
1095 p_adjustment_amount => ROUND((x_net_revaluation *
1096 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
1097 p_period_counter => x_period_counter,
1098 p_distribution_id => rec_dist.distribution_id,
1099 p_je_category_name => x_je_category_name,
1100 p_reference => to_char(rec_appraisal.appraisal_id));
1101
1102
1103 jl_co_fa_accounting_pkg.change_account(
1104 p_chart_of_accounts_id => x_accounting_flex_structure,
1105 p_apps_short_name => x_apps_short_name,
1106 p_key_flex_code => x_key_flex_code,
1107 p_num_segment => x_account_segment_no,
1108 p_account_ccid => x_actual_cost_acct_ccid,
1109 p_account_segment => x_recovery_account,
1110 p_delimiter => x_delimiter,
1111 p_returned_ccid => x_ccid,
1112 p_error_ccid => x_error_ccid);
1113
1114 IF NOT (x_error_ccid) THEN
1115 RAISE CCID_NOT_FOUND;
1116 END IF;
1117
1118 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1119 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
1120 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
1121 END IF;
1122
1123
1124 insert_row( p_adjustment_type => 'APPR_RESRV_REC',
1125 p_debit_credit_flag => 'CR',
1126 p_code_combination_id => x_ccid,
1127 p_book_type_code => p_book,
1128 p_asset_id => rec_asset.asset_id,
1129 p_adjustment_amount => ROUND((x_net_revaluation *
1130 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
1131 p_period_counter => x_period_counter,
1132 p_distribution_id => rec_dist.distribution_id,
1133 p_je_category_name => x_je_category_name,
1134 p_reference => to_char(rec_appraisal.appraisal_id));
1135
1136
1137 ELSIF (x_appr_revaluation < 0 AND
1138 x_prev_revaluation < 0 AND
1139 x_net_revaluation < 0) THEN
1140
1141
1142 jl_co_fa_accounting_pkg.change_account(
1143 p_chart_of_accounts_id => x_accounting_flex_structure,
1144 p_apps_short_name => x_apps_short_name,
1145 p_key_flex_code => x_key_flex_code,
1146 p_num_segment => x_account_segment_no,
1147 p_account_ccid => x_actual_cost_acct_ccid,
1148 p_account_segment => x_expense_account,
1149 p_delimiter => x_delimiter,
1150 p_returned_ccid => x_ccid,
1151 p_error_ccid => x_error_ccid);
1152
1153 IF NOT (x_error_ccid) THEN
1154 RAISE CCID_NOT_FOUND;
1155 END IF;
1156
1157 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1158 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
1159 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
1160 END IF;
1161
1162
1163 insert_row( p_adjustment_type => 'APPR_RESRV_EXP',
1164 p_debit_credit_flag => 'DR',
1165 p_code_combination_id => x_ccid,
1166 p_book_type_code => p_book,
1167 p_asset_id => rec_asset.asset_id,
1168 p_adjustment_amount => ROUND((x_net_revaluation *
1169 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
1170 p_period_counter => x_period_counter,
1171 p_distribution_id => rec_dist.distribution_id,
1172 p_je_category_name => x_je_category_name,
1173 p_reference => to_char(rec_appraisal.appraisal_id));
1174
1175
1176 jl_co_fa_accounting_pkg.change_account(
1177 p_chart_of_accounts_id => x_accounting_flex_structure,
1178 p_apps_short_name => x_apps_short_name,
1179 p_key_flex_code => x_key_flex_code,
1180 p_num_segment => x_account_segment_no,
1181 p_account_ccid => x_actual_cost_acct_ccid,
1182 p_account_segment => x_reserve_account,
1183 p_delimiter => x_delimiter,
1184 p_returned_ccid => x_ccid,
1185 p_error_ccid => x_error_ccid);
1186
1187 IF NOT (x_error_ccid) THEN
1188 RAISE CCID_NOT_FOUND;
1189 END IF;
1190
1191 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1192 fnd_file.put_line( 1, 'New CCID:'||to_char(x_ccid));
1193 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'New CCID:'||to_char(x_ccid));
1194 END IF;
1195
1196
1197 insert_row( p_adjustment_type => 'APPR_RESRV',
1198 p_debit_credit_flag => 'CR',
1199 p_code_combination_id => x_ccid,
1200 p_book_type_code => p_book,
1201 p_asset_id => rec_asset.asset_id,
1202 p_adjustment_amount => ROUND((x_net_revaluation *
1203 (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
1204 p_period_counter => x_period_counter,
1205 p_distribution_id => rec_dist.distribution_id,
1206 p_je_category_name => x_je_category_name,
1207 p_reference => to_char(rec_appraisal.appraisal_id));
1208
1209 END IF;
1210
1211 END LOOP distribution;
1212
1213 /* ---------------------------------------------------------
1214 -- Update jl_co_fa_appr_assts. For each asset set --
1215 -- status = 'P' ie. processed --
1216 ---------------------------------------------------------
1217 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1218 fnd_file.put_line( 1, 'Updating JL_CO_FA_ASSET_APPRS');
1219 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_ASSET_APPRS');
1220 END IF;
1221
1222 UPDATE jl_co_fa_asset_apprs SET status = 'P',
1223 last_update_date = x_sysdate,
1224 last_updated_by = x_last_updated_by,
1225 last_update_login = x_last_update_login
1226 WHERE CURRENT OF c_asset;
1227
1228 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1229 fnd_file.put_line( 1, 'Updated JL_CO_FA_ASSET_APPRS');
1230 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_ASSET_APPRS');
1231 END IF;*/
1232 ---------------------------------------------------------
1233 -- Update fa_book for each asset --
1234 ---------------------------------------------------------
1235
1236 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1237 fnd_file.put_line( 1, 'Updating FA_BOOKS');
1238 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating FA_BOOKS');
1239 END IF;
1240
1241 UPDATE fa_books SET
1242 global_attribute2 = fnd_number.number_to_canonical(x_appr_revaluation),
1243 global_attribute3 = fnd_number.number_to_canonical(x_prev_revaluation),
1244 global_attribute4 = rec_appraisal.appraisal_id,
1245 global_attribute5 = fnd_date.date_to_canonical(rec_appraisal.appraisal_date),
1246 global_attribute6 = fnd_number.number_to_canonical(rec_asset.appraisal_value),
1247 last_update_date = x_sysdate,
1248 last_updated_by = x_last_updated_by,
1249 last_update_login = x_last_update_login
1250 WHERE book_type_code = p_book
1251 AND asset_id = rec_asset.asset_id
1252 AND transaction_header_id_out IS NULL
1253 AND date_ineffective IS NULL ;
1254
1255 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1256 fnd_file.put_line( 1, 'Updated FA_BOOKS');
1257 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated FA_BOOKS');
1258 END IF;
1259
1260 ---------------------------------------------------------
1261 -- Initialize parameters to zero --
1262 ---------------------------------------------------------
1263
1264 x_revaluation_account := 0;
1265 x_surplus_account := 0;
1266 x_reserve_account := 0;
1267 x_expense_account := 0;
1268 x_recovery_account := 0;
1269 x_appr_revaluation := 0;
1270 x_prev_revaluation := 0 ;
1271 x_net_revaluation := 0;
1272 x_category_id := 0;
1273
1274 END IF;
1275 END LOOP assets;
1276 ---------------------------------------------------------
1277 -- Update jl_co_fa_appraisals table --
1278 ---------------------------------------------------------
1279
1280 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1281 fnd_file.put_line( 1, 'Updating JL_CO_FA_APPRAISALS');
1282 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updating JL_CO_FA_APPRAISALS');
1283 END IF;
1284
1285 UPDATE jl_co_fa_appraisals SET appraisal_status = 'P',
1286 last_update_date = x_sysdate,
1287 last_updated_by = x_last_updated_by,
1288 last_update_login = x_last_update_login
1289
1290 WHERE CURRENT OF c_appraisal;
1291
1292 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1293 fnd_file.put_line( 1, 'Updated JL_CO_FA_APPRAISALS');
1294 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_APPRAISALS');
1295 END IF;
1296
1297 ---------------------------------------------------------
1298 -- Insert a row into table JL_CO_FA_APPRAISAL_BOOKS --
1299 ---------------------------------------------------------
1300
1301 INSERT INTO jl_co_fa_appraisal_books (appraisal_id,
1302 book_type_code,
1303 last_update_date,
1304 last_updated_by,
1305 creation_date,
1306 created_by,
1307 last_update_login,
1308 request_id,
1309 program_application_id,
1310 program_id,
1311 program_update_date)
1312 VALUES (rec_appraisal.appraisal_id,
1313 p_book,
1314 x_sysdate,
1315 x_last_updated_by,
1316 x_sysdate,
1317 x_last_updated_by,
1318 x_last_update_login,
1319 x_request_id,
1320 x_program_application_id,
1321 x_program_id,
1322 x_sysdate);
1323
1324 ---------------------------------------------------------
1325 -- Report all assets not revalued for the appraisal_id --
1326 ---------------------------------------------------------
1327 track_asset(p_book,
1328 rec_appraisal.appraisal_id);
1329
1330 END IF;
1331 END LOOP appraisals;
1332 COMMIT;
1333
1334 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1335 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1336 END IF;
1337
1338 EXCEPTION
1339
1340 WHEN NOT_A_TAX_BOOK THEN
1341 fnd_message.set_name('JL', 'JL_CO_FA_INVALID_TAX_BOOK');
1342 fnd_message.set_token('BOOK', p_book);
1343 err_msg := fnd_message.get;
1344 fnd_file.put_line(fnd_file.log, err_msg);
1345 call_status := fnd_concurrent.set_completion_status('ERROR','');
1346 /*
1347 fnd_message.raise_error;
1348 */
1349 WHEN JE_CAT_NOT_DEFINED THEN
1350 fnd_message.set_name('JL', 'JL_ZZ_FA_JE_CAT_NOT_DEFINED');
1351 fnd_message.set_token('BOOK', p_book);
1352 err_msg := fnd_message.get;
1353 ROLLBACK;
1354 fnd_file.put_line(fnd_file.log, err_msg);
1355 call_status := fnd_concurrent.set_completion_status('ERROR','');
1356 /*
1357 fnd_message.raise_error;
1358 */
1359 WHEN GL_POSTING_NOT_ALLOWED THEN
1360 fnd_message.set_name('JL', 'JL_CO_FA_POSTING_NOT_ALLOWED');
1361 fnd_message.set_token('BOOK', p_book);
1362 err_msg := fnd_message.get;
1363 fnd_file.put_line(fnd_file.log, err_msg);
1364 call_status := fnd_concurrent.set_completion_status('ERROR','');
1365 /*
1366 fnd_message.raise_error;
1367 */
1368 /* WHEN DIFF_FISCAL_YEAR THEN
1369 fnd_message.set_name('JL', 'JL_CO_FA_INVALID_FISCAL_YEAR');
1370 err_msg := fnd_message.get;
1371 app_exception.raise_exception (exception_type => 'APP',
1372 exception_code =>
1373 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_FA_INVALID_FISCAL_YEAR'),
1374 exception_text => err_msg);
1375 */
1376
1377 WHEN DEPRN_STATUS_NOT_C THEN
1378 fnd_message.set_name('JL', 'JL_CO_FA_DEPRECIATION_STATUS');
1379 fnd_message.set_token('BOOK', p_book);
1380 err_msg := fnd_message.get;
1381 fnd_file.put_line(fnd_file.log, err_msg);
1382 call_status := fnd_concurrent.set_completion_status('ERROR','');
1383 /*
1384 fnd_message.raise_error;
1385 */
1386 WHEN INVALID_CCID THEN
1387 SELECT LTRIM(RTRIM(segment1))||LTRIM(RTRIM(segment2))||
1388 LTRIM(RTRIM(segment3))||LTRIM(RTRIM(segment4))||
1389 LTRIM(RTRIM(segment5))||LTRIM(RTRIM(segment6))||
1390 LTRIM(RTRIM(segment7))
1391 INTO x_category
1392 FROM fa_categories
1393 WHERE category_id = x_category_id;
1394 ROLLBACK;
1395 fnd_message.set_name('JL', 'JL_CO_FA_CCID_NOT_DEFINED');
1396 fnd_message.set_token('CATEGORY', x_category);
1397 err_msg := fnd_message.get;
1398 fnd_file.put_line(fnd_file.log, err_msg);
1399 call_status := fnd_concurrent.set_completion_status('ERROR','');
1400 /*
1401 fnd_message.raise_error;
1402 */
1403
1404 WHEN CCID_NOT_FOUND THEN
1405 SELECT LTRIM(RTRIM(segment1))||LTRIM(RTRIM(segment2))||
1406 LTRIM(RTRIM(segment3))||LTRIM(RTRIM(segment4))||
1407 LTRIM(RTRIM(segment5))||LTRIM(RTRIM(segment6))||
1408 LTRIM(RTRIM(segment7))
1409 INTO x_category
1410 FROM fa_categories
1411 WHERE category_id = x_category_id;
1412 ROLLBACK;
1413 fnd_message.set_name('JL', 'JL_CO_FA_CCID_NOT_DEFINED');
1414 fnd_message.set_token('CATEGORY', x_category);
1415 err_msg := fnd_message.get;
1416 fnd_file.put_line(fnd_file.log, err_msg);
1417 call_status := fnd_concurrent.set_completion_status('ERROR','');
1418 /*
1419 fnd_message.raise_error;
1420 */
1421
1422 /* WHEN REVALUATION_RUN THEN
1423 fnd_message.set_name('JL', 'JL_CO_FA_REVALUATION_RUN');
1424 fnd_message.set_token('APPRAISAL_NUMBER', to_char(p_appraisal_id));
1425 fnd_message.set_token('BOOK', p_book);
1426 err_msg := fnd_message.get;
1427 app_exception.raise_exception (exception_type => 'APP',
1428 exception_code =>
1429 jl_zz_fa_utilities_pkg.get_app_errnum('JL', 'JL_CO_FA_REVALUATION_RUN'),
1430 exception_text => err_msg);
1431 */
1432 WHEN INVALID_CURRENCY_CODE THEN
1433 fnd_message.set_name('JL', 'JL_CO_FA_DIFFERENT_CURRENCY');
1434 fnd_message.set_token('APPRAISAL_NUMBER', to_char(p_appraisal_id));
1435 fnd_message.set_token('BOOK', p_book);
1436 err_msg := fnd_message.get;
1437 fnd_file.put_line(fnd_file.log, err_msg);
1438 call_status := fnd_concurrent.set_completion_status('ERROR','');
1439 /*
1440 fnd_message.raise_error;
1441 */
1442
1443 WHEN OTHERS THEN
1444 IF x_statement = 'BOOK_INFO' THEN
1445 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
1446 fnd_message.set_token('NUMBER', '1');
1447 err_msg := fnd_message.get;
1448 fnd_file.put_line(fnd_file.log, err_msg);
1449 call_status := fnd_concurrent.set_completion_status('ERROR','');
1450 /*
1451 fnd_message.raise_error;
1452 */
1453
1454 ELSIF x_statement = 'FLEX_INFO' THEN
1455 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
1456 fnd_message.set_token('NUMBER', '2');
1457 err_msg := fnd_message.get;
1458 fnd_file.put_line(fnd_file.log, err_msg);
1459 call_status := fnd_concurrent.set_completion_status('ERROR','');
1460 /*
1461 fnd_message.raise_error;
1462 */
1463
1464 ELSIF x_statement = 'CURR_INFO' THEN
1465 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
1466 fnd_message.set_token('NUMBER', '3');
1467 err_msg := fnd_message.get;
1468 fnd_file.put_line(fnd_file.log, err_msg);
1469 call_status := fnd_concurrent.set_completion_status('ERROR','');
1470 /*
1471 fnd_message.raise_error;
1472 */
1473
1474 ELSIF x_statement = 'ACCT_INFO' THEN
1475 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
1476 fnd_message.set_token('NUMBER', '4');
1477 err_msg := fnd_message.get;
1478 ROLLBACK;
1479 fnd_file.put_line(fnd_file.log, err_msg);
1480 call_status := fnd_concurrent.set_completion_status('ERROR','');
1481 /*
1482 fnd_message.raise_error;
1483 */
1484
1485 ELSIF x_statement = 'COST_ACCT_INFO' THEN
1486 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
1487 fnd_message.set_token('NUMBER', '5');
1488 err_msg := fnd_message.get;
1489 ROLLBACK;
1490 fnd_file.put_line(fnd_file.log, err_msg);
1491 call_status := fnd_concurrent.set_completion_status('ERROR','');
1492 /*
1493 fnd_message.raise_error;
1494 */
1495
1496 ELSIF x_statement = 'INSERT_ADJ' THEN
1497 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
1498 fnd_message.set_token('NUMBER', '6');
1499 err_msg := fnd_message.get;
1500 ROLLBACK;
1501 fnd_file.put_line(fnd_file.log, err_msg);
1502 call_status := fnd_concurrent.set_completion_status('ERROR','');
1503 /*
1504 fnd_message.raise_error;
1505 */
1506
1507 ELSIF x_statement = 'PERIOD_INFO' THEN
1508 fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
1509 fnd_message.set_token('NUMBER', '7');
1510 err_msg := fnd_message.get;
1511 ROLLBACK;
1512 fnd_file.put_line(fnd_file.log, err_msg);
1513 call_status := fnd_concurrent.set_completion_status('ERROR','');
1514 /*
1515 fnd_message.raise_error;
1516 */
1517
1518 ELSE
1519 fnd_message.set_name('JL', 'JL_CO_FA_GENERAL_ERROR');
1520 fnd_file.put_line( fnd_file.log, fnd_message.get);
1521 err_num := SQLCODE;
1522 err_msg := substr(SQLERRM, 1, 200);
1523 ROLLBACK;
1524 RAISE_APPLICATION_ERROR( err_num, err_msg);
1525 END IF;
1526 END revaluate;
1527
1528 ----------------------------------------------------------------------------
1529 -- PROCEDURE --
1530 -- insert_row --
1531 -- --
1532 -- DESCRIPTION --
1533 -- Use this procedure to insert row into JL_CO_FA_ADJUSTMENTS. --
1534 -- --
1535 -- PURPOSE: --
1536 -- Oracle Applications Rel 11.0 --
1537 -- --
1538 -- PARAMETERS: --
1539 -- p_adjustment_type --
1540 -- p_debit_credit_flag --
1541 -- p_code_combination_id --
1542 -- p_book_type_code --
1543 -- p_asset_id --
1544 -- p_adjustment_amount --
1545 -- p_period_counter --
1546 -- p_distribution_id --
1547 -- HISTORY: --
1548 -- 08/12/98 Sujit Dalai Created --
1549 ----------------------------------------------------------------------------
1550
1551 PROCEDURE insert_row(
1552 p_adjustment_type VARCHAR2,
1553 p_debit_credit_flag VARCHAR2,
1554 p_code_combination_id NUMBER,
1555 p_book_type_code VARCHAR2,
1556 p_asset_id NUMBER,
1557 p_adjustment_amount NUMBER,
1558 p_period_counter NUMBER,
1559 p_distribution_id fa_distribution_history.distribution_id%TYPE,
1560 P_je_category_name VARCHAR2,
1561 p_reference VARCHAR2) IS
1562
1563 x_period_counter NUMBER;
1564 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
1565
1566
1567 BEGIN
1568
1569 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1570 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1571 END IF;
1572
1573
1574 x_statement := 'PERIOD_INFO';
1575 SELECT period_counter
1576 INTO x_period_counter
1577 FROM fa_deprn_periods
1578 WHERE book_type_code = p_book_type_code
1579 AND period_close_date IS NULL;
1580
1581
1582 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1583 fnd_file.put_line( 1, 'Inserting Row into JL_CO_FA_ADJUSTMENTS');
1584 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserting Row into JL_CO_FA_ADJUSTMENTS');
1585 END IF;
1586
1587
1588 IF (p_adjustment_amount <> 0) THEN
1589 INSERT INTO jl_co_fa_adjustments(
1590 source_type_code,
1591 je_category_name,
1592 adjustment_type,
1593 debit_credit_flag,
1594 code_combination_id,
1595 book_type_code,
1596 asset_id,
1597 adjustment_amount,
1598 distribution_id,
1599 period_counter_adjusted,
1600 period_counter_created,
1601 reference,
1602 last_update_date,
1603 last_updated_by,
1604 creation_date,
1605 created_by,
1606 last_update_login,
1607 request_id,
1608 program_application_id,
1609 program_id,
1610 program_update_date)
1611 VALUES(
1612 'TECH_APPR_REVAL',
1613 p_je_category_name,
1614 p_adjustment_type,
1615 p_debit_credit_flag,
1616 p_code_combination_id,
1617 p_book_type_code,
1618 p_asset_id,
1619 ABS(p_adjustment_amount),
1620 p_distribution_id,
1621 x_period_counter,
1622 x_period_counter,
1623 p_reference,
1624 x_sysdate,
1625 x_last_updated_by,
1626 x_sysdate,
1627 x_last_updated_by,
1628 x_last_update_login,
1629 x_request_id,
1630 x_program_application_id,
1631 x_program_id,
1632 x_sysdate);
1633 END IF;
1634
1635 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1636 fnd_file.put_line( 1, 'Inseted Row into JL_CO_FA_ADJUSTMENTS');
1637 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserted Row into JL_CO_FA_ADJUSTMENTS');
1638 END IF;
1639
1640 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1641 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1642 END IF;
1643
1644 END insert_row;
1645
1646 ----------------------------------------------------------------------------
1647 -- PROCEDURE --
1648 -- find_who_columns --
1649 -- --
1650 -- DESCRIPTION --
1651 -- Use this procedure to find the values for WHO columns. --
1652 -- --
1653 -- PURPOSE: --
1654 -- Oracle Applications Rel 11.0 --
1655 -- --
1656 -- PARAMETERS: --
1657 -- HISTORY: --
1658 -- 08/12/98 Sujit Dalai Created --
1659 ----------------------------------------------------------------------------
1660
1661 PROCEDURE find_who_columns IS
1662
1663 l_api_name CONSTANT VARCHAR2(30) := 'FIND_WHO_COLUMNS';
1664
1665 BEGIN
1666
1667 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1668 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.BEGIN',G_PKG_NAME||': '||l_api_name||'(+)');
1669 END IF;
1670
1671 x_last_updated_by := fnd_global.user_id;
1672 x_last_update_login := fnd_global.login_id;
1673 x_request_id := fnd_global.conc_request_id;
1674 x_program_application_id := fnd_global.prog_appl_id;
1675 x_program_id := fnd_global.conc_program_id;
1676 x_sysdate := SYSDATE;
1677
1678 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1679 fnd_file.put_line( 1, 'last_update_login:'||to_char(x_last_update_login));
1680 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_update_login:'||to_char(x_last_update_login));
1681 fnd_file.put_line( 1, 'last_updated_by:'||to_char(x_last_updated_by));
1682 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_updated_by:'||to_char(x_last_updated_by));
1683 fnd_file.put_line( 1, 'last_request_id:'||to_char(x_request_id));
1684 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_request_id:'||to_char(x_request_id));
1685 fnd_file.put_line( 1, 'x_program_application_id :'||to_char(x_program_application_id ));
1686 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_application_id :'||to_char(x_program_application_id ));
1687 fnd_file.put_line( 1, 'x_program_id :'||to_char(x_program_id ));
1688 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_program_id :'||to_char(x_program_id ));
1689 fnd_file.put_line( 1, 'x_sysdate :'||to_char(x_sysdate ));
1690 FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'x_sysdate :'||to_char(x_sysdate ));
1691
1692 END IF;
1693
1694 IF ( G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL) THEN
1695 FND_LOG.STRING(G_LEVEL_PROCEDURE,G_MODULE_NAME||l_api_name||'.END',G_PKG_NAME||': '||l_api_name||'(-)');
1696 END IF;
1697
1698 END find_who_columns;
1699
1700
1701 ----------------------------------------------------------------------------
1702 -- PROCEDURE --
1703 -- track_asset --
1704 -- --
1705 -- DESCRIPTION --
1706 -- Use this procedure to get assets not revaluated. --
1707 -- --
1708 -- PURPOSE: --
1709 -- Oracle Applications Rel 11.0 --
1710 -- --
1711 -- PARAMETERS: --
1712 -- p_appraisal_id --
1713 -- p_book_type_code --
1714 -- HISTORY: --
1715 -- 11/10/98 Sujit Dalai Created --
1716 ----------------------------------------------------------------------------
1717
1718 PROCEDURE track_asset( p_book_type_code VARCHAR2,
1719 p_appraisal_id number) IS
1720
1721 x_count number := 0;
1722 x_status boolean;
1723 CURSOR c_asset IS
1724 SELECT asset_number
1725 FROM jl_co_fa_asset_apprs
1726 WHERE appraisal_id = p_appraisal_id
1727 AND asset_number NOT IN (SELECT ap.asset_number
1728 FROM jl_co_fa_asset_apprs ap,
1729 fa_additions ad,
1730 fa_books ab
1731 WHERE ap.asset_number = ad.asset_number
1732 AND ap.appraisal_id = p_appraisal_id
1733 AND ad.asset_id = ab.asset_id
1734 AND ab.book_type_code = p_book_type_code
1735 AND ab.transaction_header_id_out IS NULL
1736 AND ab.date_ineffective IS NULL );
1737
1738 BEGIN
1739
1740 FOR rec_asset IN c_asset LOOP
1741 fnd_message.set_name('JL', 'JL_CO_FA_ASSET_NOT_REVALUED');
1742 -- fnd_message.set_token('BOOK', P_book_type_code);
1743 fnd_message.set_token('ASSET_NUMBER', rec_asset.asset_number);
1744 fnd_file.put_line(FND_FILE.LOG, fnd_message.get);
1745
1746 x_count := x_count + 1;
1747 END LOOP;
1748
1749 IF x_count <> 0 THEN
1750 x_status := fnd_concurrent.set_completion_status( status => 'WARNING',
1751 message => '');
1752 END IF;
1753 END track_asset;
1754
1755 END jl_co_fa_ta_reval_pkg;