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