[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FA_MI_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_FA_MI_EXPORT_PKG AS
2 --$Header: JACNAMIB.pls 120.6 2010/12/03 09:28:13 chuansha noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNAMIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export Asset Modification Information |
13 --| |
14 --| PROCEDURE LIST |
15 --| PROCEDURE Add_Asset_Modification_Info |
16 --| |
17 --| |
18 --| HISTORY |
19 --| 23-Apr-2010 Chongwu Li created |
20 --| 03-Dec-2010 ChuanLing Shao updated |
21 --+======================================================================*/
22 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_MI_EXPORT_PKG';
23
24 --==========================================================================
25 -- PROCEDURE NAME:
26 --
27 -- Add_Electronic_Accounting_Book Public
28 --
29 -- DESCRIPTION:
30 --
31 -- This procedure is to export Asset Modification Information
32 --
33 -- PARAMETERS:
34 -- In: pn_legal_entity_id legal_entity ID
35 -- pn_ledger_id ledger ID
36 -- pv_accounting_year accounting year
37 -- pv_period_from period from
38 -- pv_period_to period to
39 -- DESIGN REFERENCES:
40 -- CNAO_V2_FA_TD.doc
41 --
42 -- CHANGE HISTORY:
43 --
44 -- 23-Apr-2010 Chongwu Li created
45
46 PROCEDURE Add_Asset_Modification_Info(pn_legal_entity_id NUMBER
47 ,pn_ledger_id NUMBER
48 ,pv_accounting_year VARCHAR2
49 ,pv_period_from VARCHAR2
50 ,pv_period_to VARCHAR2) IS
51 lv_procedure_name VARCHAR2(40) := 'Add_Asset_Modification_Info';
52 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
53 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
54 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
55 ln_row_count NUMBER;
56 ld_start_date DATE;
57 ld_end_date DATE;
58 lv_period_name VARCHAR2(100);
59 lv_change_reason VARCHAR2(200);
60 ln_factor NUMBER;
61 ld_dp_close_date DATE;
62 ln_assigned_unit NUMBER;
63 ln_total_unit NUMBER;
64 ln_factor_before NUMBER;
65 ln_assigned_unit_before NUMBER;
66 ln_total_unit_before NUMBER;
67 TYPE lv_values IS TABLE OF VARCHAR2(311) INDEX BY BINARY_INTEGER;
68 -- 311 the largest value of the change contents
69 lv_before lv_values;
70 lv_after lv_values;
71 lv_change_content lv_values;
72 lv_delimiter VARCHAR2(1);
73
74
75 CURSOR asset_modification_cur(ln_legal_entity_id NUMBER
76 ,ln_ledger_id NUMBER
77 ,ld_date_from DATE
78 ,ld_date_to DATE) IS
79 SELECT DISTINCT fth.transaction_header_id,
80 greatest(greatest(fdp.calendar_period_open_date,
81 least(sysdate, fdp.calendar_period_close_date)),
82 fdp.calendar_period_open_date) transaction_date,
83 fth.transaction_type_code,
84 fa.asset_id,
85 fa.asset_number,
86 fa.description,
87 fa.tag_number,
88 fth.transaction_name,
89 fth.transaction_key,
90 fth.book_type_code
91 FROM fa_transaction_headers fth,
92 fa_additions fa,
93 fa_books fb,
94 fa_book_controls_sec fbcs,
95 fa_distribution_history fdh,
96 fa_deprn_periods fdp
97 --gl_code_combinations gcc
98 WHERE fth.transaction_type_code IN
99 ('ADDITION', 'ADJUSTMENT', 'RECLASS', 'REINSTATEMENT',
100 'REVALUATION', 'TRANSFER', 'UNIT ADJUSTMENT',
101 'RESERVE ADJUSTMENT')
102 AND fth.asset_id = fa.asset_id
103 AND fdh.asset_id = fa.asset_id
104 AND fb.asset_id = fa.asset_id
105 AND fa.asset_type = 'CAPITALIZED'
106 AND fth.book_type_code = fbcs.book_type_code
107 --AND fdh.book_type_code = fbcs.book_type_code
108 AND fth.book_type_code = fdP.book_type_code
109 AND (fbcs.book_class = 'CORPORATE' OR fbcs.book_class = 'TAX')
110 AND fbcs.gl_posting_allowed_flag = 'YES'
111 --AND fdh.code_combination_id = gcc.code_combination_id
112 AND fth.date_effective BETWEEN fdp.period_open_date AND
113 nvl(fdp.period_close_date, SYSDATE)
114 AND fdh.date_effective < nvl(fdp.period_close_date, SYSDATE)
115 AND (fdh.date_ineffective > fdp.period_open_date OR
116 fdh.date_ineffective IS NULL)
117 --AND gcc.segment1 IN (SELECT segment_value
118 -- FROM gl_ledger_norm_seg_vals
119 -- WHERE legal_entity_id = ln_legal_entity_id
120 -- AND ledger_id = ln_ledger_id)
121 AND EXISTS
122 (SELECT jclllbg.bal_seg_value
123 FROM ja_cn_ledger_le_bsv_gt jclllbg
124 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
125 jclllbg.bal_seg_value
126 AND jclllbg.Ledger_Id = ln_ledger_id
127 AND jclllbg.Legal_Entity_Id = ln_legal_entity_id)
128 AND fbcs.set_of_books_id = ln_ledger_id
129 AND greatest(greatest(fdp.calendar_period_open_date,
130 least(SYSDATE, fdp.calendar_period_close_date))) BETWEEN
131 ld_date_from AND ld_date_to
132 ORDER BY transaction_date, fth.transaction_header_id;
133
134 BEGIN
135 --logging for debug
136 FND_FILE.PUT_LINE(FND_FILE.log, lv_procedure_name);
137 IF (ln_proc_level >= ln_dbg_level)
138 THEN
139 FND_LOG.STRING(ln_proc_level,
140 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
141 '.begin',
142 'Enter procedure');
143 FND_LOG.STRING(ln_proc_level,
144 lv_procedure_name ||
145 '.parameters',
146 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
147 'pn_ledger_id=' || pn_ledger_id || ',' ||
148 'pv_accounting_year=' || pv_accounting_year || ',' ||
149 'pv_period_from=' || pv_period_from || ',' ||
150 'pv_period_to=' || pv_period_to);
151
152 END IF; --ln_proc_level>=ln_dbg_level
153 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
154 '.parameters:' ||
155 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
156 'pn_ledger_id=' || pn_ledger_id || ',' ||
157 'pv_accounting_year=' || pv_accounting_year || ',' ||
158 'pv_period_from=' || pv_period_from || ',' ||
159 'pv_period_to=' || pv_period_to);
160
161 --Fetch start data and end date
162 SELECT start_date
163 INTO ld_start_date
164 FROM GL_PERIOD_STATUSES
165 WHERE ledger_id = pn_ledger_id
166 AND application_id = 101
167 AND period_name = pv_period_from
168 AND period_year = pv_accounting_year;
169
170 SELECT end_date
171 INTO ld_end_date
172 FROM GL_PERIOD_STATUSES
173 WHERE ledger_id = pn_ledger_id
174 AND application_id = 101
175 AND period_name = pv_period_to
176 AND period_year = pv_accounting_year;
177
178
179 --logging the variables
180 IF (ln_statement_level >= ln_dbg_level)
181 THEN
182 FND_LOG.STRING(ln_statement_level,
183 lv_procedure_name,
184 'ld_start_date=' || ld_start_date || ',' ||
185 'ld_end_date=' || ld_end_date);
186 END IF; --(ln_statement_level >= ln_dbg_level)
187 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
188 '.variable:' ||
189 'ld_start_date=' || ld_start_date || ',' ||
190 'ld_end_date=' || ld_end_date);
191
192
193 ln_row_count := 0;
194 FOR v_row IN asset_modification_cur(pn_legal_entity_id
195 ,pn_ledger_id
196 ,ld_start_date
197 ,ld_end_date)
198 LOOP
199 ln_row_count := ln_row_count + 1;
200
201 SELECT period_num
202 INTO lv_period_name
203 FROM gl_period_statuses gp
204 WHERE
205 --period_year = pv_accounting_year fixed #bug 10365688
206 --AND
207 application_id = 101
208 AND ledger_id = pn_ledger_id
209 AND gp.adjustment_period_flag = 'N'
210 AND v_row.Transaction_Date BETWEEN gp.start_date AND
211 gp.end_date;
212 SELECT max(fdp.period_close_date)
213 INTO ld_dp_close_date
214 FROM fa_deprn_periods fdp
215 WHERE greatest(greatest(fdp.calendar_period_open_date,
216 least(SYSDATE, fdp.calendar_period_close_date)),
217 fdp.calendar_period_open_date) <= v_row.transaction_date;
218 --get the total unit at the transaction time
219 SELECT SUM(fdh.units_assigned)
220 INTO ln_total_unit
221 FROM fa_distribution_history fdh
222 WHERE fdh.asset_id = v_row.asset_id
223 AND fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
224 AND (fdh.date_ineffective > nvl(ld_dp_close_date, SYSDATE) OR
225 fdh.date_ineffective IS NULL);
226 --get the assigned unit at the transaction time
227 SELECT SUM(fdh.units_assigned)
228 INTO ln_assigned_unit
229 FROM fa_distribution_history fdh
230 WHERE fdh.asset_id = v_row.asset_id
231 AND fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
232 AND (fdh.date_ineffective > nvl(ld_dp_close_date, SYSDATE) OR
233 fdh.date_ineffective IS NULL)
234 AND EXISTS
235 (SELECT jclllbg.bal_seg_value
236 FROM ja_cn_ledger_le_bsv_gt jclllbg
237 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
238 jclllbg.bal_seg_value
239 AND jclllbg.Ledger_Id = pn_ledger_id
240 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id);
241 -- calculate the assignment factor
242 IF(ln_total_unit IS NOT NULL AND ln_total_unit <> 0)
243 THEN
244 ln_factor := ln_assigned_unit/ln_total_unit;
245 ELSE
246 ln_factor := 1;
247 END IF; --(IF(ln_total_unit IS NOT NULL AND ln_total_unit <> 0))
248 FND_FILE.put_line(FND_FILE.log, lv_procedure_name ||
249 'ln_factor = ' || ln_factor || ' for transaction_header_id' || v_row.transaction_header_id);
250
251
252 lv_before(1) := '';
253 lv_before(2) := '';
254 lv_before(3) := '';
255 lv_before(4) := '';
256 lv_before(5) := '';
257 lv_before(6) := '';
258 lv_before(7) := '';
259 lv_before(8) := '';
260 lv_before(9) := '';
261 lv_before(10) := '';
262 lv_before(11) := '';
263 lv_before(12) := '';
264 lv_before(13) := '';
265
266 lv_after(1) := '';
267 lv_after(2) := '';
268 lv_after(3) := '';
269 lv_after(4) := '';
270 lv_after(5) := '';
271 lv_after(6) := '';
272 lv_after(7) := '';
273 lv_after(8) := '';
274 lv_after(9) := '';
275 lv_after(10) := '';
276 lv_after(11) := '';
277 lv_after(12) := '';
278 lv_after(13) := '';
279 IF (v_row.transaction_type_code = 'ADDITION') --ADDITION
280 THEN
281 lv_change_reason := NVL(v_row.transaction_name, 'Addition');
282 lv_change_content(1) := 'Current Cost: ';
283 lv_before(1) := 0;
284 SELECT current_cost
285 INTO lv_after(1)
286 FROM fa_transaction_history_books_v
287 WHERE (key = v_row.transaction_header_id)
288 AND transaction_type = 'Addition';
289 lv_after(1) := ja_cn_utility.get_rounding_value(to_number(lv_after(1)) * ln_factor
290 ,pn_legal_entity_id);
291 ELSIF (v_row.transaction_type_code = 'UNIT ADJUSTMENT') --UNIT ADJUSTMENT
292 THEN
293 --get the total unit at the transaction time, before the unit adjustment
294 SELECT SUM(fdh.units_assigned)
295 INTO ln_total_unit_before
296 FROM fa_distribution_history fdh
297 WHERE fdh.asset_id = v_row.asset_id
298 AND fdh.transaction_header_id_in < v_row.transaction_header_id
299 AND fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
300 AND (fdh.date_ineffective > nvl(ld_dp_close_date, SYSDATE) OR
301 fdh.date_ineffective IS NULL);
302 --get the assigned unit at the transaction time, before the unit adjustment
303 SELECT SUM(fdh.units_assigned)
304 INTO ln_assigned_unit_before
305 FROM fa_distribution_history fdh
306 WHERE fdh.asset_id = v_row.asset_id
307 AND fdh.transaction_header_id_in < v_row.transaction_header_id
308 AND fdh.date_effective < nvl(ld_dp_close_date, SYSDATE)
309 AND (fdh.date_ineffective > nvl(ld_dp_close_date, SYSDATE) OR
310 fdh.date_ineffective IS NULL)
311 AND EXISTS
312 (SELECT jclllbg.bal_seg_value
313 FROM ja_cn_ledger_le_bsv_gt jclllbg
314 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
315 jclllbg.bal_seg_value
316 AND jclllbg.Ledger_Id = pn_ledger_id
317 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id);
318 -- calculate the assignment factor, before the unit adjustment
319 IF(ln_total_unit_before IS NOT NULL AND ln_total_unit_before <> 0)
320 THEN
321 ln_factor_before := ln_assigned_unit_before/ln_total_unit_before;
322 ELSE
323 ln_factor_before := 1;
324 END IF; --(IF(ln_total_unit_before IS NOT NULL AND ln_total_unit_before <> 0))
325 lv_change_reason := NVL(v_row.transaction_name, 'Unit adjustment');
326 lv_change_content(1) := 'Unit: ';
327 SELECT units
328 INTO lv_before(1)
329 FROM (
330 SELECT fahv.units units
331 FROM fa_asset_history_v fahv, fa_categories_b fcb
332 WHERE (fahv.key = v_row.transaction_header_id)
333 AND fahv.category_id = fcb.category_id
334 ORDER BY fahv.transaction_header_id_in)
335 WHERE ROWNUM = 1;
336
337 SELECT units
338 INTO lv_after(1)
339 FROM (
340 SELECT fahv.units units
341 FROM fa_asset_history_v fahv, fa_categories_b fcb
342 WHERE (fahv.key = v_row.transaction_header_id)
343 AND fahv.category_id = fcb.category_id
344 ORDER BY fahv.transaction_header_id_in DESC)
345 WHERE ROWNUM = 1;
346 lv_before(1) := ja_cn_utility.get_rounding_value(to_number(lv_before(1)) * ln_factor_before
347 ,pn_legal_entity_id);
348 lv_after(1) := ja_cn_utility.get_rounding_value(to_number(lv_after(1)) * ln_factor
349 ,pn_legal_entity_id);
350 ELSIF (v_row.transaction_type_code = 'RECLASS') --RECLASS
351 THEN
352 lv_change_reason := NVL(v_row.transaction_name, 'Reclass');
353 lv_change_content(1) := 'Class: ';
354 SELECT category
355 INTO lv_before(1)
356 FROM (
357 SELECT fcb.concatenated_segments category
358 FROM fa_asset_history_v fahv, fa_categories_b_kfv fcb
359 WHERE (fahv.key = v_row.transaction_header_id)
360 AND fahv.category_id = fcb.category_id
361 ORDER BY fahv.transaction_header_id_in)
362 WHERE ROWNUM = 1;
363
364 SELECT category
365 INTO lv_after(1)
366 FROM (
367 SELECT fcb.concatenated_segments category
368 FROM fa_asset_history_v fahv, fa_categories_b_kfv fcb
369 WHERE (fahv.key = v_row.transaction_header_id)
370 AND fahv.category_id = fcb.category_id
371 ORDER BY fahv.transaction_header_id_in DESC)
372 WHERE ROWNUM = 1;
373
374 SELECT concatenated_segment_delimiter
375 INTO lv_delimiter
376 FROM fnd_id_flex_structures
377 WHERE id_flex_code = 'CAT#';
378 lv_before(1) := replace(lv_before(1), lv_delimiter, '');
379 lv_after(1) := replace(lv_after(1), lv_delimiter, '');
380 ELSIF (v_row.transaction_type_code = 'REINSTATEMENT') --REINSTATEMENT
381 THEN
382 lv_change_reason := NVL(v_row.transaction_name, 'Reinstatement');
383 lv_change_content(1) := 'Cost_retired: ';
384 lv_before(1) := 0;
385 SELECT cost_retired
386 INTO lv_after(1)
387 FROM fa_retirements_v
388 WHERE 'REINSTATEMENT' = 'REINSTATEMENT'
389 AND transaction_header_id_out = v_row.transaction_header_id;
390 lv_after(1) := ja_cn_utility.get_rounding_value(to_number(lv_after(1)) * ln_factor
391 ,pn_legal_entity_id);
392 ELSIF (v_row.transaction_type_code = 'REVALUATION') --REVALUATION
393 THEN
394 lv_change_reason := NVL(v_row.transaction_name, 'Revaluation');
395 lv_change_content(1) := 'Current_cost: ';
396 SELECT current_cost
397 INTO lv_before(1)
398 FROM (
399 SELECT current_cost
400 FROM fa_transaction_history_books_v
401 WHERE key = v_row.transaction_header_id
402 ORDER BY transaction_header_id_in)
403 WHERE ROWNUM = 1;
404
405 SELECT current_cost
406 INTO lv_after(1)
407 FROM (
408 SELECT current_cost
409 FROM fa_transaction_history_books_v
410 WHERE key = v_row.transaction_header_id
411 ORDER BY transaction_header_id_in DESC)
412 WHERE ROWNUM = 1;
413 lv_before(1) := ja_cn_utility.get_rounding_value(to_number(lv_before(1)) * ln_factor
414 ,pn_legal_entity_id);
415 lv_after(1) := ja_cn_utility.get_rounding_value(to_number(lv_after(1)) * ln_factor
416 ,pn_legal_entity_id);
417 ELSIF (v_row.transaction_type_code = 'TRANSFER') --TRANSFER
418 THEN
419 lv_change_reason := NVL(v_row.transaction_name, 'Transfer');
420 lv_change_content(1) := 'Trans Units:';
421 lv_change_content(2) := 'Assign To:';
422 lv_change_content(3) := 'Location:';
423 lv_change_content(4) := 'CCID:';
424 SELECT trans_units,
425 full_name,
426 location,
427 ccid
428 INTO lv_before(1), lv_before(2), lv_before(3), lv_before(4)
429 FROM (
430 SELECT fdi.trans_units,
431 pp.full_name,
432 fl.concatenated_segments location,
433 gcc.concatenated_segments ccid
434 FROM fa_distribution_inquiry_v fdi,
435 gl_code_combinations_kfv gcc,
436 per_people_x pp,
437 fa_locations_kfv fl
438 WHERE (transaction_header_id_key = v_row.transaction_header_id)
439 AND fdi.assigned_to = pp.person_id(+)
440 AND fdi.location_id = fl.location_id(+)
441 AND fdi.code_combination_id = gcc.code_combination_id(+)
442 ORDER BY transaction_header_id)
443 WHERE ROWNUM = 1;
444
445 SELECT trans_units,
446 full_name,
447 location,
448 ccid
449 INTO lv_after(1), lv_after(2), lv_after(3), lv_after(4)
450 FROM (
451 SELECT fdi.trans_units,
452 pp.full_name,
453 fl.concatenated_segments location,
454 gcc.concatenated_segments ccid
455 FROM fa_distribution_inquiry_v fdi,
456 gl_code_combinations_kfv gcc,
457 per_people_x pp,
458 fa_locations_kfv fl
459 WHERE (transaction_header_id_key = v_row.transaction_header_id)
460 AND fdi.assigned_to = pp.person_id(+)
461 AND fdi.location_id = fl.location_id(+)
462 AND fdi.code_combination_id = gcc.code_combination_id(+)
463 ORDER BY transaction_header_id DESC)
464 WHERE ROWNUM = 1;
465 ELSIF (v_row.transaction_type_code = 'ADJUSTMENT'
466 AND (v_row.transaction_key <> 'UA'
467 AND v_row.transaction_key <> 'IM'
468 OR v_row.transaction_key IS NULL)) --ADJUSTMENT, not Unplaned adjustment
469 THEN
470 lv_change_reason := NVL(v_row.transaction_name, 'Adjustment');
471 lv_change_content(1) := 'current_cost:';
472 lv_change_content(2) := 'salvage_value:';
473 lv_change_content(3) := 'revaluation_ceiling:';
474 lv_change_content(4) := 'deprn_method_code:';
475 lv_change_content(5) := 'life:';
476 lv_change_content(6) := 'adjusted_rate:';
477 lv_change_content(7) := 'production_capacity:';
478 lv_change_content(8) := 'unit_of_measure:';
479 lv_change_content(9) := 'prorate_convention_code:';
480 lv_change_content(10) := 'date_placed_in_service:';
481 lv_change_content(11) := 'depreciate_flag:';
482 lv_change_content(12) := 'bonus_rule:';
483 lv_change_content(13) := 'ceiling_name:';
484 SELECT current_cost,
485 salvage_value,
486 revaluation_ceiling,
487 deprn_method_code,
488 life,
489 adjusted_rate,
490 production_capacity,
491 unit_of_measure,
492 prorate_convention_code,
493 date_placed_in_service,
494 depreciate_flag,
495 bonus_rule,
496 ceiling_name
497 INTO lv_before(1), lv_before(2), lv_before(3), lv_before(4),
498 lv_before(5), lv_before(6), lv_before(7), lv_before(8),
499 lv_before(9), lv_before(10), lv_before(11), lv_before(12),
500 lv_before(13)
501 FROM (
502 SELECT current_cost,
503 salvage_value,
504 revaluation_ceiling,
505 deprn_method_code,
506 life,
507 adjusted_rate,
508 production_capacity,
509 unit_of_measure,
510 prorate_convention_code,
511 date_placed_in_service,
512 depreciate_flag,
513 bonus_rule,
514 ceiling_name
515 FROM fa_transaction_history_books_v
516 WHERE (key = v_row.transaction_header_id)
517 ORDER BY transaction_header_id_in)
518 WHERE ROWNUM = 1;
519
520 SELECT current_cost,
521 salvage_value,
522 revaluation_ceiling,
523 deprn_method_code,
524 life,
525 adjusted_rate,
526 production_capacity,
527 unit_of_measure,
528 prorate_convention_code,
529 date_placed_in_service,
530 depreciate_flag,
531 bonus_rule,
532 ceiling_name
533 INTO lv_after(1), lv_after(2), lv_after(3), lv_after(4),
534 lv_after(5), lv_after(6), lv_after(7), lv_after(8),
535 lv_after(9), lv_after(10), lv_after(11), lv_after(12),
536 lv_after(13)
537 FROM (
538 SELECT current_cost,
539 salvage_value,
540 revaluation_ceiling,
541 deprn_method_code,
542 life,
543 adjusted_rate,
544 production_capacity,
545 unit_of_measure,
546 prorate_convention_code,
547 date_placed_in_service,
548 depreciate_flag,
549 bonus_rule,
550 ceiling_name
551 FROM fa_transaction_history_books_v
552 WHERE (key = v_row.transaction_header_id)
553 ORDER BY transaction_header_id_in DESC)
554 WHERE ROWNUM = 1;
555 lv_before(1) := ja_cn_utility.get_rounding_value(to_number(lv_before(1)) * ln_factor
556 ,pn_legal_entity_id);
557 lv_before(2) := ja_cn_utility.get_rounding_value(to_number(lv_before(2)) * ln_factor
558 ,pn_legal_entity_id);
559 lv_after(1) := ja_cn_utility.get_rounding_value(to_number(lv_after(1)) * ln_factor
560 ,pn_legal_entity_id);
561 lv_after(2) := ja_cn_utility.get_rounding_value(to_number(lv_after(2)) * ln_factor
562 ,pn_legal_entity_id);
563 ELSIF (v_row.transaction_type_code = 'ADJUSTMENT'
564 AND v_row.transaction_key = 'IM') --ADJUSTMENT, IMPAIRMENT
565 THEN
566 lv_change_reason := NVL(v_row.transaction_name, 'Impairment adjustment');
567 lv_change_content(1) := 'Impairment:';
568 SELECT fds1.impairment_reserve,
569 fds.impairment_reserve
570 INTO lv_before(1), lv_after(1)
571 FROM fa_deprn_summary fds,
572 fa_deprn_summary fds1,
573 fa_deprn_periods fdp
574 WHERE fds.asset_id = v_row.asset_id and fds1.asset_id = v_row.asset_id
575 AND fdp.book_type_code = v_row.book_type_code
576 AND fdp.book_type_code = fds.book_type_code
577 AND fdp.book_type_code = fds1.book_type_code
578 AND v_row.transaction_date BETWEEN fdp.calendar_period_open_date AND
579 fdp.calendar_period_close_date
580 AND fds.period_counter = fdp.period_counter
581 AND fds1.period_counter = (SELECT MAX(period_counter)
582 FROM fa_deprn_summary
583 WHERE asset_id = v_row.asset_id
584 AND book_type_code = v_row.book_type_code
585 AND period_counter < fdp.period_counter);
586
587 lv_before(1) := ja_cn_utility.get_rounding_value(to_number(lv_before(1)) * ln_factor
588 ,pn_legal_entity_id);
589 lv_after(1) := ja_cn_utility.get_rounding_value(to_number(lv_after(1)) * ln_factor
590 ,pn_legal_entity_id);
591 ELSIF (v_row.transaction_type_code = 'ADJUSTMENT' and v_row.transaction_key = 'UA') --ADJUSTMENT, Unplaned adjustment
592 THEN
593 lv_change_reason := NVL(v_row.transaction_name, 'Unplaned Depreciation');
594 lv_change_content(1) := 'current_cost:';
595 lv_before(1) := 0;
596 SELECT adjustment_amount
597 INTO lv_after(1)
598 FROM fa_financial_inquiry_upl_v
599 WHERE (transaction_header_id = v_row.transaction_header_id)
600 AND ROWNUM = 1;
601 lv_after(1) := ja_cn_utility.get_rounding_value(to_number(lv_after(1)) * ln_factor
602 ,pn_legal_entity_id);
603 END IF; -- (v_row.transaction_type_code = 'ADDITION')
604 FOR i IN 1..lv_before.count LOOP
605 IF (lv_before(i) <> lv_after(i))
606 THEN
607 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_MODIFICATION_INFO'
608 ,Ja_Cn_Utility.GV_TAG_TYPE_START
609 ,'FA');
610
611 Ja_Cn_Utility.Add_Child_Node('MODIFI_TRANSACTION_NUMBER'
612 ,v_row.transaction_header_id
613 ,'VARCHAR2'
614 ,Ja_Cn_Utility.GV_REQUIRED_YES
615 ,Ja_Cn_Utility.GV_MODULE_FA);
616 Ja_Cn_Utility.Add_Fixed_Child_Node('MODIFICATION_DATE'
617 ,to_char(v_row.transaction_date, 'YYYYMMDD')
618 ,8
619 ,Ja_Cn_Utility.GV_REQUIRED_YES
620 ,Ja_Cn_Utility.GV_MODULE_FA);
621 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD'
622 ,lv_period_name
623 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
624 ,Ja_Cn_Utility.GV_REQUIRED_YES
625 ,Ja_Cn_Utility.GV_MODULE_FA);
626 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CARD_NUMBER'
627 ,v_row.asset_number
628 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
629 ,Ja_Cn_Utility.GV_REQUIRED_YES
630 ,Ja_Cn_Utility.GV_MODULE_FA);
631 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_NUMBER'
632 ,v_row.asset_number
633 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
634 ,Ja_Cn_Utility.GV_REQUIRED_YES
635 ,Ja_Cn_Utility.GV_MODULE_FA);
636 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_NAME'
637 ,v_row.description
638 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
639 ,Ja_Cn_Utility.GV_REQUIRED_YES
640 ,Ja_Cn_Utility.GV_MODULE_FA);
641
642 Ja_Cn_Utility.Add_Child_Node('MODIFICATION_METHOD_CODE'
643 ,v_row.transaction_type_code
644 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
645 ,Ja_Cn_Utility.GV_REQUIRED_YES
646 ,Ja_Cn_Utility.GV_MODULE_FA);
647 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_TAG_NUMBER'
648 ,v_row.tag_number
649 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
650 ,Ja_Cn_Utility.GV_REQUIRED_YES
651 ,Ja_Cn_Utility.GV_MODULE_FA);
652 Ja_Cn_Utility.Add_Child_Node('PRE-MODIFI_CONTENT_AND_AMOUNT'
653 ,lv_change_content(i) || lv_before(i)
654 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
655 ,Ja_Cn_Utility.GV_REQUIRED_YES
656 ,Ja_Cn_Utility.GV_MODULE_FA);
657 Ja_Cn_Utility.Add_Child_Node('POST-MODIFI_CONTENT_AND_AMOUNT'
658 ,lv_change_content(i) || lv_after(i)
659 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
660 ,Ja_Cn_Utility.GV_REQUIRED_YES
661 ,Ja_Cn_Utility.GV_MODULE_FA);
662 Ja_Cn_Utility.Add_Child_Node('MODIFICATION_REASON'
663 ,lv_change_reason
664 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
665 ,Ja_Cn_Utility.GV_REQUIRED_YES
666 ,Ja_Cn_Utility.GV_MODULE_FA);
667 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_MODIFICATION_INFO'
668 ,Ja_Cn_Utility.GV_TAG_TYPE_END
669 ,Ja_Cn_Utility.GV_MODULE_FA);
670 END IF; --(lv_before(i) <> lv_after(i))
671 END LOOP; --FOR i IN 1..lv_before.count LOOP
672 END LOOP; --v_row IN asset_modification_cur
673
674 IF (ln_row_count = 0)
675 THEN
676 Ja_Cn_Utility.Print_No_Data_Found_For_Log('FIXED_ASSET_MODIFICATION_INFO'
677 ,Ja_Cn_Utility.GV_MODULE_FA);
678 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_MODIFICATION_INFO'
679 ,Ja_Cn_Utility.GV_TAG_TYPE_START
680 ,Ja_Cn_Utility.GV_MODULE_FA);
681
682 Ja_Cn_Utility.Add_Child_Node('MODIFI_TRANSACTION_NUMBER'
683 ,NULL
684 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
685 ,Ja_Cn_Utility.GV_REQUIRED_YES
686 ,Ja_Cn_Utility.GV_MODULE_FA);
687 Ja_Cn_Utility.Add_Fixed_Child_Node('MODIFICATION_DATE'
688 ,NULL
689 ,8
690 ,Ja_Cn_Utility.GV_REQUIRED_YES
691 ,Ja_Cn_Utility.GV_MODULE_FA);
692 Ja_Cn_Utility.Add_Child_Node('ACCOUNTING_PERIOD'
693 ,NULL
694 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
695 ,Ja_Cn_Utility.GV_REQUIRED_YES
696 ,Ja_Cn_Utility.GV_MODULE_FA);
697 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_CARD_NUMBER'
698 ,NULL
699 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
700 ,Ja_Cn_Utility.GV_REQUIRED_YES
701 ,Ja_Cn_Utility.GV_MODULE_FA);
702 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_NUMBER'
703 ,NULL
704 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
705 ,Ja_Cn_Utility.GV_REQUIRED_YES
706 ,Ja_Cn_Utility.GV_MODULE_FA);
707 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_NAME'
708 ,NULL
709 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
710 ,Ja_Cn_Utility.GV_REQUIRED_YES
711 ,Ja_Cn_Utility.GV_MODULE_FA);
712
713 Ja_Cn_Utility.Add_Child_Node('MODIFICATION_METHOD_CODE'
714 ,NULL
715 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
716 ,Ja_Cn_Utility.GV_REQUIRED_YES
717 ,Ja_Cn_Utility.GV_MODULE_FA);
718 Ja_Cn_Utility.Add_Child_Node('FIXED_ASSET_TAG_NUMBER'
719 ,NULL,Ja_Cn_Utility.GV_TYPE_VARCHAR2
720 ,Ja_Cn_Utility.GV_REQUIRED_YES
721 ,Ja_Cn_Utility.GV_MODULE_FA);
722 Ja_Cn_Utility.Add_Child_Node('PRE-MODIFI_CONTENT_AND_AMOUNT'
723 ,NULL
724 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
725 ,Ja_Cn_Utility.GV_REQUIRED_YES
726 ,Ja_Cn_Utility.GV_MODULE_FA);
727 Ja_Cn_Utility.Add_Child_Node('POST-MODIFI_CONTENT_AND_AMOUNT'
728 ,NULL
729 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
730 ,Ja_Cn_Utility.GV_REQUIRED_YES
731 ,Ja_Cn_Utility.GV_MODULE_FA);
732 Ja_Cn_Utility.Add_Child_Node('MODIFICATION_REASON'
733 ,NULL
734 ,Ja_Cn_Utility.GV_TYPE_VARCHAR2
735 ,Ja_Cn_Utility.GV_REQUIRED_YES
736 ,Ja_Cn_Utility.GV_MODULE_FA);
737 Ja_Cn_Utility.Add_Sub_Root_Node('FIXED_ASSET_MODIFICATION_INFO'
738 ,Ja_Cn_Utility.GV_TAG_TYPE_END
739 ,Ja_Cn_Utility.GV_MODULE_FA);
740 END IF; --ln_row_count = 0
741
742 --logging for debug
743 IF (ln_proc_level >= ln_dbg_level) THEN
744 FND_LOG.STRING(ln_proc_level,
745 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
746 'Exit procedure');
747 END IF; -- (ln_proc_level>=ln_dbg_level)
748 EXCEPTION
749 WHEN OTHERS THEN
750 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
751 THEN
752 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
753 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
754 '.Other_Exception ',
755 SQLCODE || SQLERRM);
756 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
757 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
758 RAISE;
759 END Add_Asset_Modification_Info;
760
761 END JA_CN_FA_MI_EXPORT_PKG;