[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_FA_AC_EXP_PKG
Source
1 PACKAGE BODY JA_CN_PS_FA_AC_EXP_PKG AS
2 --$Header: JACNFACB.pls 120.2 2010/09/16 01:23:19 wuliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNFACB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export fixed asset card |
13 --| for Public Sector . |
14 --| |
15 --| PROCEDURE LIST |
16 --| Add_FA_Asset_Card Export asset Card Information |
17 --| Add_FA_Asset_Card_Monthly Export asset Card Information monthly |
18 --| |
19 --| |
20 --| HISTORY |
21 --| 12-AUG-2010 Wuhua Liu created |
22 --+======================================================================*/
23 -- PROCEDURE NAME:
24 --
25 -- Add_FA_Asset_Card Public
26 --
27 -- DESCRIPTION:
28 --
29 -- This procedure is used to export asset card of Fixed Asset
30 -- for Public Sector
31 --
32 -- PARAMETERS:
33 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
34 -- pn_ledger_id Ledger ID
35 -- pv_accounting_year Accounting Year
36 -- pv_period_from Period From
37 -- pv_period_to Period To
38 -- pn_coa_id Identifier of chart of account
39 -- DESIGN REFERENCES:
40 -- TDD_1213_FIN_GL_P_CNAOV2_FA.doc
41 --
42 -- CHANGE HISTORY:
43 --| 12-AUG-2010 Wuhua Liu created
44 --==========================================================================
45 PROCEDURE Add_FA_Asset_Card
46 ( pn_legal_entity_id IN NUMBER
47 , pn_ledger_id IN NUMBER
48 , pv_accounting_year IN VARCHAR2
49 , pv_period_from IN VARCHAR2
50 , pv_period_to IN VARCHAR2
51 , pn_coa_id IN NUMBER
52 )
53 IS
54 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
55 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
56 lv_procedure_name VARCHAR2(40) := 'Add_FA_Asset_Card';
57 ld_start_date DATE;
58 ld_end_date DATE;
59 ld_start_date_monthly DATE;
60 ld_end_date_monthly DATE;
61 lv_period_name VARCHAR2(15);
62
63 CURSOR fa_export_period_cur
64 IS
65 SELECT
66 DISTINCT fps.period_name
67 , fps.start_date
68 , fps.end_date
69 FROM
70 gl_period_statuses fps
71 WHERE ledger_id = pn_ledger_id
72 AND application_id = 101
73 AND fps.start_date >= ld_start_date
74 AND fps.end_date <= ld_end_date
75 AND fps.adjustment_period_flag = 'N';
76
77 BEGIN
78 --logging for debug
79 IF (ln_proc_level>=ln_dbg_level)
80 THEN
81 FND_LOG.STRING( ln_proc_level
82 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
83 || '.begin'
84 , 'Enter procedure'
85 );
86 -- logging the parameters
87 FND_LOG.STRING( ln_proc_level
88 , lv_procedure_name || '.parameters'
89 , 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
90 || 'pn_ledger_id=' || pn_ledger_id || ','
91 || 'pv_accounting_year=' || pv_accounting_year || ','
92 || 'pv_period_from=' || pv_period_from || ','
93 || 'pv_period_to=' || pv_period_to || ','
94 || 'pn_coa_id =' || pn_coa_id);
95 END IF; --(l_proc_level>=l_dbg_level)
96 FND_FILE.put_line( FND_FILE.log
97 , lv_procedure_name || '.parameters:'
98 || 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
99 || 'pn_ledger_id=' || pn_ledger_id || ','
100 || 'pv_accounting_year=' || pv_accounting_year || ','
101 || 'pv_period_from=' || pv_period_from || ','
102 || 'pv_period_to=' || pv_period_to || ','
103 || 'pn_coa_id =' || pn_coa_id);
104 --Fetch start data and end date
105 IF (pv_period_from IS NOT NULL)
106 THEN
107 SELECT
108 start_date
109 INTO
110 ld_start_date
111 FROM
112 gl_period_statuses
113 WHERE ledger_id = pn_ledger_id
114 AND application_id = 101
115 AND period_name = pv_period_from
116 AND TO_CHAR(period_year) = pv_accounting_year;
117 ELSE
118 ld_start_date := TO_DATE( pv_accounting_year||'0101'
119 , 'YYYYMMDD');
120 END IF; --(pv_period_from IS NOT NULL)
121
122 IF (pv_period_to IS NOT NULL)
123 THEN
124 SELECT
125 end_date
126 INTO
127 ld_end_date
128 FROM
129 gl_period_statuses
130 WHERE ledger_id = pn_ledger_id
131 AND application_id = 101
132 AND period_name = pv_period_to
133 AND TO_CHAR(period_year) = pv_accounting_year;
134 ELSE
135 ld_end_date := TO_DATE( pv_accounting_year||'1231'
136 , 'YYYYMMDD');
137 END IF; -- pv_period_to IS NOT NULL
138
139 OPEN fa_export_period_cur;
140 LOOP
141 FETCH
142 fa_export_period_cur
143 INTO
144 lv_period_name
145 , ld_start_date_monthly
146 , ld_end_date_monthly;
147 EXIT WHEN fa_export_period_cur%NOTFOUND;
148 Add_FA_Asset_Card_Monthly( pn_legal_entity_id => pn_legal_entity_id
149 , pn_ledger_id => pn_ledger_id
150 , pv_accounting_year => pv_accounting_year
151 , pv_period_from => lv_period_name
152 , pv_period_to => lv_period_name
153 , pn_coa_id => pn_coa_id
154 );
155 END LOOP; -- fa_export_period_cur
156 CLOSE fa_export_period_cur;
157
158 --logging for debug
159 IF (ln_proc_level>=ln_dbg_level)
160 THEN
161 FND_LOG.STRING( ln_proc_level
162 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
163 , 'Exit procedure');
164 END IF; -- (ln_proc_level>=ln_dbg_level)
165
166 EXCEPTION
167 WHEN OTHERS THEN
168 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
169 THEN
170 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
171 , GV_MODULE_PREFIX || '.' || lv_procedure_name
172 || '.Other_Exception '
173 , SQLCODE || SQLERRM);
174 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
175 FND_FILE.put_line( FND_FILE.log
176 , lv_procedure_name || SQLCODE || SQLERRM);
177 RAISE;
178 END Add_FA_Asset_Card;
179 --==========================================================================
180 -- PROCEDURE NAME:
181 --
182 -- Add_FA_Asset_Card_Monthly Public
183 --
184 -- DESCRIPTION:
185 --
186 -- This procedure is used to export asset card in Fixed Asset monthly.
187 --
188 -- PARAMETERS:
189 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
190 -- pn_ledger_id Ledger ID
191 -- pv_accounting_year Accounting Year
192 -- pv_period_from Period From
193 -- pv_period_to Period To
194 -- pn_coa_id Identifier of chart of account
195 -- DESIGN REFERENCES:
196 -- TDD_1213_FIN_GL_P_CNAOV2_FA.doc
197 --
198 -- CHANGE HISTORY:
199 -- 12-AUG-2010 Wuhua Liu created
200 -- 15-SEP-2010 Wuhua Liu bug#10115127 wrongly compute the orginal
201 -- value and net book value
202 --==========================================================================
203 PROCEDURE Add_FA_Asset_Card_Monthly
204 ( pn_legal_entity_id IN NUMBER
205 , pn_ledger_id IN NUMBER
206 , pv_accounting_year IN VARCHAR2
207 , pv_period_from IN VARCHAR2
208 , pv_period_to IN VARCHAR2
209 , pn_coa_id IN NUMBER
210 )
211 IS
212 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
213 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
214 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
215 ln_full_retirement_count NUMBER := 0;
216 ln_reinstate_count NUMBER := 0;
217 ln_depreciation_counter NUMBER := 0;
218 ln_real_deprn_counter NUMBER := 0;
219 ln_deprn_reserve NUMBER(20,2) := 0;
220 ln_impairment_rsv NUMBER(20,2) := 0;
221 ln_deprn_rsv_last NUMBER(20,2) := 0; --last month depreciate reserve
222 ln_impt_rsv_last NUMBER(20,2) := 0; --last month impairment reserve
223 ln_transaction_header_id NUMBER := 0;
224 ln_retire_tran_header_id NUMBER := 0;
225 ln_asset_unit NUMBER(20,2) := 0;
226 ln_original_value NUMBER(20,2) := 0;
227 ln_salvage_value NUMBER(20,2) := 0;
228 ln_salvage_percent NUMBER(20,2) := 0;
229 ln_production_capacity NUMBER(20,2) := 0;
230 ln_recoverable_cost NUMBER(20,2) := 0;
231 ln_life_production NUMBER(20,2) := 0;
232 ln_life_of_month NUMBER := 0;
233 ln_dep_month NUMBER := 0;
234 ln_legacy_dep_month NUMBER := 0; --existing depreciate when adding asset.
235 ln_net_book_value NUMBER(20,2) := 0;
236 ln_assigned_unit NUMBER(20,2) := 0;
237 ln_asset_key_ccid NUMBER := 0;
238 ln_monthly_deprn_amount NUMBER(20,2) := 0;
239 ln_mth_impairment_amount NUMBER(20,2) := 0;
240 ln_monthly_deprn_rate NUMBER(20,6) := 0;
241 --bug#10115127 change the precision of this type
242 ln_assigned_rate NUMBER := 0;
243 lb_retirement_flag BOOLEAN := false;
244 lv_procedure_name VARCHAR2(40) := 'Add_FA_Asset_Card_Monthly';
245 lv_asset_id VARCHAR2(15);
246 lv_asset_number VARCHAR2(15);
247 lv_currency VARCHAR2(15);
248 lv_category_code VARCHAR2(210);
249 lv_asset_name VARCHAR2(80);
250 lv_usage_segment VARCHAR2(30);
251 lv_measure_segment VARCHAR2(30);
252 lv_asset_measure VARCHAR2(30);
253 lv_asset_usage VARCHAR2(30);
254 lv_unit_of_measure VARCHAR2(25); --UOM
255 lv_accounting_period VARCHAR2(30);
256 lv_nature_segment VARCHAR2(30);
257 lv_book_class VARCHAR2(15);
258 lv_delimiter VARCHAR2(1);
259 lv_asset_cost_acct VARCHAR2(25);
260 lv_impair_reserve_acct VARCHAR2(25);
261 lv_deprn_reserve_acct VARCHAR2(25);
262 lv_depn_meth_code VARCHAR2(12);
263 lv_book_type_code VARCHAR2(15);
264 ld_start_date DATE;
265 ld_end_date DATE;
266 ld_reinstated_date DATE;
267 ld_retirement_date DATE;
268 ld_in_service_date DATE;
269 ld_addition_date DATE;
270 ln_row_count NUMBER; -- row count for asset card
271
272 CURSOR asset_card_cur
273 IS
274 SELECT
275 DISTINCT faav.asset_id
276 , faav.asset_number
277 , faav.attribute_category_code
278 , faav.description
279 , bk.date_placed_in_service
280 , bk.book_type_code
281 , faav.current_units
282 , bk.deprn_method_code
283 , bk.life_in_months
284 , fncv.name
285 , bk.cost
286 , bk.salvage_value
287 , bk.unit_of_measure --UOM
288 , bk.production_capacity
289 , fcb.asset_cost_acct
290 , fcb.impair_reserve_acct
291 , fcb.deprn_reserve_acct
292 , fbc.book_class
293 , faav.asset_key_ccid
294 FROM
295 fa_additions_v faav
296 , fa_books bk
297 , fa_book_controls_sec fbc
298 , fa_category_books fcb
299 , fa_methods mth
300 , fa_distribution_history fdh
301 , gl_ledgers gl
302 , fa_transaction_history_trx_v fthv
303 , fnd_currencies_vl fncv
304 -- Check ledger
305 WHERE bk.date_ineffective IS NULL
306 AND bk.transaction_header_id_out IS NULL
307 AND faav.asset_id = bk.asset_id
308 AND bk.book_type_code = fbc.book_type_code
309 AND fbc.book_class IN ('CORPORATE', 'TAX')
310 AND fbc.gl_posting_allowed_flag = 'YES'
311 AND fbc.set_of_books_id = pn_ledger_id
312 AND fdh.asset_id = faav.asset_id
313 -- Check legal entity
314 AND EXISTS
315 (
316 SELECT fdp.period_close_date
317 FROM fa_deprn_periods fdp
318 WHERE fdp.book_type_code = bk.book_type_code
319 AND fdp.calendar_period_close_date
320 BETWEEN ld_start_date AND ld_end_date
321 AND fdp.period_close_date IS NOT NULL
322 )
323 AND fdh.date_effective < nvl(
324 (SELECT MAX(fdp.period_close_date)
325 FROM fa_deprn_periods fdp
326 WHERE fdp.book_type_code = bk.book_type_code
327 AND fdp.calendar_period_close_date <= ld_end_date
328 AND fdp.period_close_date IS NOT NULL), SYSDATE)
329 AND (fdh.date_ineffective > nvl(
330 (SELECT MAX(fdp.period_close_date)
331 FROM fa_deprn_periods fdp
332 WHERE fdp.book_type_code = bk.book_type_code
333 AND fdp.calendar_period_close_date <= ld_end_date
334 AND fdp.period_close_date IS NOT NULL), SYSDATE)
335 OR fdh.date_ineffective IS NULL)
336 AND EXISTS
337 (
338 SELECT jclllbg.bal_seg_value
339 FROM ja_cn_ledger_le_bsv_gt jclllbg
340 WHERE JA_CN_CFS_DATA_CLT_PKG.
341 get_balancing_segment(fdh.code_combination_id) =
342 jclllbg.bal_seg_value
343 AND jclllbg.Ledger_Id = pn_ledger_id
344 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id
345 )
346 -- Check the asset type
347 AND faav.asset_type = 'CAPITALIZED'
348 --get category accounts
349 AND fcb.category_id = faav.asset_category_id
350 AND fcb.book_type_code = bk.book_type_code
351 -- get the book window elements.
352 AND bk.asset_id = faav.asset_id
353 AND bk.date_ineffective IS NULL
354 AND bk.book_type_code = fbc.book_type_code
355 AND bk.deprn_method_code = mth.method_code
356 AND (
357 bk.life_in_months = mth.life_in_months
358 OR
359 (bk.life_in_months IS NULL AND mth.life_in_months IS NULL)
360 )
361 --get the currency
362 AND gl.ledger_id = pn_ledger_id
363 AND gl.currency_code = fncv.currency_code
364 -- Check the asset added date
365 AND fthv.asset_id = faav.asset_id
366 AND fthv.transaction_type_code = 'ADDITION'
367 AND fthv.book_type_code = bk.book_type_code
368 AND
369 (
370 (SELECT greatest(greatest( dp.calendar_period_open_date
371 , least( SYSDATE
372 , dp.calendar_period_close_date)
373 )
374 , dp.calendar_period_open_date)
375 FROM fa_deprn_periods dp
376 WHERE fthv.book_type_code = dp.book_type_code
377 AND fthv.date_effective BETWEEN dp.period_open_date AND
378 nvl(dp.period_close_date, SYSDATE)
379 ) <= ld_end_date
380 )
381 ORDER BY faav.asset_number;
382 -- to get the delimiter
383 CURSOR delimiter_cur
384 IS
385 SELECT
386 concatenated_segment_delimiter
387 FROM
388 fnd_id_flex_structures
389 WHERE id_flex_code = 'CAT#';
390
391 BEGIN
392 --logging for debug
393 IF (ln_proc_level>=ln_dbg_level)
394 THEN
395 FND_LOG.STRING( ln_proc_level
396 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
397 || '.begin'
398 , 'Enter procedure'
399 );
400 -- logging the parameters
401 FND_LOG.STRING( ln_proc_level
402 , lv_procedure_name || '.parameters'
403 , 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
404 || 'pn_ledger_id=' || pn_ledger_id || ','
405 || 'pv_accounting_year=' || pv_accounting_year || ','
406 || 'pv_period_from=' || pv_period_from || ','
407 || 'pv_period_to=' || pv_period_to || ','
408 || 'pn_coa_id =' || pn_coa_id);
409 END IF; --(l_proc_level>=l_dbg_level)
410 FND_FILE.put_line( FND_FILE.log
411 , lv_procedure_name || '.parameters:'
412 || 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
413 || 'pn_ledger_id=' || pn_ledger_id || ','
414 || 'pv_accounting_year=' || pv_accounting_year || ','
415 || 'pv_period_from=' || pv_period_from || ','
416 || 'pv_period_to=' || pv_period_to || ','
417 || 'pn_coa_id =' || pn_coa_id);
418
419 --Fetch start data and end date
420 IF (pv_period_from IS NOT NULL)
421 THEN
422 SELECT
423 start_date
424 , period_num
425 INTO
426 ld_start_date
427 , lv_accounting_period
428 FROM
429 gl_period_statuses
430 WHERE ledger_id = pn_ledger_id
431 AND application_id = 101
432 AND period_name = pv_period_from
433 AND TO_CHAR(period_year) = pv_accounting_year;
434 ELSE
435 ld_start_date := TO_CHAR( pv_accounting_year||'0101'
436 , 'YYYYMMDD');
437 END IF; --(pv_period_from IS NOT NULL)
438
439 IF (pv_period_to IS NOT NULL)
440 THEN
441 SELECT
442 end_date
443 INTO
444 ld_end_date
445 FROM
446 gl_period_statuses
447 WHERE ledger_id = pn_ledger_id
448 AND application_id = 101
449 AND period_name = pv_period_to
450 AND TO_CHAR(period_year) = pv_accounting_year;
451 ELSE
452 ld_end_date := TO_CHAR( pv_accounting_year||'1231'
453 , 'YYYYMMDD');
454 END IF; --(pv_period_to IS NOT NULL)
455
456 --log start data and end date
457 IF (ln_statement_level >= ln_dbg_level)
458 THEN
459 FND_LOG.STRING( ln_statement_level
460 , lv_procedure_name
461 , 'Fetched:'
462 || ' start date=' || nvl(TO_CHAR(ld_start_date), 'null')
463 || ' end date=' || nvl(to_char(ld_end_date), 'null'));
464 END IF; --(ln_statement_level >= ln_dbg_level)
465 FND_FILE.put_line( FND_FILE.log
466 , lv_procedure_name || '.variable:'
467 || 'start date=' || nvl(to_char(ld_start_date), 'null')
468 || ' end date=' || nvl(to_char(ld_end_date), 'null'));
469 --get the nature accouting segment name
470 SELECT
471 application_column_name
472 INTO
473 lv_nature_segment
474 FROM
475 fnd_segment_attribute_values
476 WHERE application_id = 101
477 AND id_flex_num = pn_coa_id
478 AND id_flex_code = 'GL#'
479 AND attribute_value = 'Y'
480 AND segment_attribute_type = 'GL_ACCOUNT';
481
482 --get the usage segment.
483 SELECT
484 dffa.attribute_column
485 INTO
486 lv_usage_segment
487 FROM
488 ja_cn_dff_assignments_v dffa
489 WHERE dffa.lookup_code = 'FAAU'
490 AND dffa.chart_of_accounts_id = pn_coa_id;
491
492 --get the measure segment.
493 SELECT
494 dffa.attribute_column
495 INTO
496 lv_measure_segment
497 FROM
498 ja_cn_dff_assignments_v dffa
499 WHERE dffa.lookup_code = 'FAUM'
500 AND dffa.chart_of_accounts_id = pn_coa_id;
501
502 --get the system delimiter
503 OPEN delimiter_cur;
504 FETCH
505 delimiter_cur
506 INTO
507 lv_delimiter;
508 CLOSE delimiter_cur;
509
510 --get the export element
511 ln_row_count := 0;
512
513 OPEN asset_card_cur;
514 LOOP
515 FETCH
516 asset_card_cur
517 INTO
518 lv_asset_id
519 , lv_asset_number
520 , lv_category_code
521 , lv_asset_name
522 , ld_in_service_date
523 , lv_book_type_code
524 , ln_asset_unit
525 , lv_depn_meth_code
526 , ln_life_of_month
527 , lv_currency
528 , ln_original_value
529 , ln_salvage_value
530 , lv_unit_of_measure
531 , ln_production_capacity
532 , lv_asset_cost_acct
533 , lv_impair_reserve_acct
534 , lv_deprn_reserve_acct
535 , lv_book_class
536 , ln_asset_key_ccid;
537 EXIT WHEN asset_card_cur%NOTFOUND;
538 BEGIN
539 --- check if the asset is retired.
540 --- Check the retirement of the asset
541 lb_retirement_flag := FALSE;--reset the retirement flag.
542 ln_assigned_unit := 0; --reset the assigned unit
543 ln_depreciation_counter := 0;
544 ln_real_deprn_counter := 0;
545 lv_asset_measure := NULL;
546 lv_asset_usage := NULL;
547 ln_assigned_rate := 0;
548 ln_monthly_deprn_rate := 0;
549 ln_salvage_percent := 0;
550 ln_dep_month := 0;
551 ln_legacy_dep_month := 0;
552
553 SELECT
554 COUNT(*)
555 INTO
556 ln_full_retirement_count
557 FROM
558 fa_transaction_history_trx_v fthv
559 WHERE fthv.asset_id = lv_asset_id
560 AND fthv.book_type_code = lv_book_type_code
561 AND fthv.transaction_type_code = 'FULL RETIREMENT'
562 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
563 least(SYSDATE, dp.calendar_period_close_date)),
564 dp.calendar_period_open_date)
565 FROM fa_deprn_periods dp
566 WHERE fthv.book_type_code = dp.book_type_code
567 AND fthv.date_effective
568 BETWEEN dp.period_open_date
569 AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
570 IF(ln_full_retirement_count <> 0)
571 THEN
572 --Check the reinstatement of the asset.
573 SELECT COUNT(*) INTO ln_reinstate_count
574 FROM fa_transaction_history_trx_v fthv
575 WHERE fthv.asset_id = lv_asset_id
576 AND fthv.book_type_code = lv_book_type_code
577 AND fthv.transaction_type_code = 'REINSTATEMENT'
578 AND
579 (
580 (
581 SELECT greatest(greatest( dp.calendar_period_open_date,
582 least(SYSDATE, dp.calendar_period_close_date)),
583 dp.calendar_period_open_date)
584 FROM fa_deprn_periods dp
585 WHERE fthv.book_type_code = dp.book_type_code
586 AND fthv.date_effective BETWEEN dp.period_open_date
587 AND nvl(dp.period_close_date, SYSDATE)
588 ) <= ld_end_date
589 );
590 IF(ln_reinstate_count = 0)
591 THEN
592 lb_retirement_flag := TRUE;
593 ELSE
594 --Retireve the greatest effective date of retirement.
595 ---TODO get the real accouting retirement date.
596 SELECT
597 greatest(date_effective)
598 INTO
599 ld_retirement_date
600 FROM
601 fa_transaction_history_trx_v fthv
602 WHERE fthv.asset_id = lv_asset_id
603 AND fthv.book_type_code = lv_book_type_code
604 AND fthv.transaction_type_code = 'FULL RETIREMENT'
605 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
606 least(SYSDATE, dp.calendar_period_close_date)),
607 dp.calendar_period_open_date)
608 FROM fa_deprn_periods dp
609 WHERE fthv.book_type_code = dp.book_type_code
610 AND fthv.date_effective BETWEEN dp.period_open_date
611 AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
612 --Retireve the greatest effective date of reinstatement.
613 SELECT
614 greatest(date_effective)
615 INTO
616 ld_reinstated_date
617 FROM
618 fa_transaction_history_trx_v fthv
619 WHERE fthv.asset_id = lv_asset_id
620 AND fthv.book_type_code = lv_book_type_code
621 AND fthv.transaction_type_code = 'REINSTATEMENT'
622 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
623 least(SYSDATE, dp.calendar_period_close_date)),
624 dp.calendar_period_open_date)
625 FROM fa_deprn_periods dp
626 WHERE fthv.book_type_code = dp.book_type_code
627 AND fthv.date_effective BETWEEN dp.period_open_date
628 AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
629 IF(ld_retirement_date > ld_reinstated_date)
630 THEN
631 lb_retirement_flag := TRUE;
632 END IF;-- (ld_retirement_date > ld_reinstated_date)
633 END IF; -- (ln_reinstate_count = 0)
634 END IF;-- (ln_full_retirement_count <> 0)
635
636 --get the segment value and description
637 IF(lb_retirement_flag = FALSE)
638 THEN
639
640 --get the period counter from depreciation period.
641 SELECT
642 MAX(dp.period_counter)
643 INTO
644 ln_depreciation_counter
645 FROM
646 fa_deprn_periods dp
647 WHERE dp.book_type_code = lv_book_type_code
648 AND dp.calendar_period_open_date <= ld_end_date
649 AND dp.period_close_date IS NOT NULL;
650 --get the period counter from depreciation summary.
651 SELECT
652 nvl(MAX(period_counter), 0)
653 INTO
654 ln_real_deprn_counter
655 FROM
656 fa_deprn_summary
657 WHERE asset_id = lv_asset_id
658 AND book_type_code = lv_book_type_code
659 AND deprn_source_code = 'DEPRN';
660
661 IF(ln_real_deprn_counter < ln_depreciation_counter)
662 THEN
663 ln_depreciation_counter := ln_real_deprn_counter;
664 END IF; --(IF(ln_real_deprn_counter < ln_depreciation_counter))
665
666 --check if the asset is adjusted after the to_date.
667 BEGIN
668 SELECT
669 MIN(thv.transaction_header_id)
670 INTO
671 ln_transaction_header_id
672 FROM fa_deprn_periods dp
673 , fa_transaction_history_trx_v thv
674 , fa_transaction_headers fthr
675 WHERE thv.book_type_code = dp.book_type_code
676 AND thv.asset_id = lv_asset_id
677 AND dp.book_type_code = lv_book_type_code
678 AND thv.transaction_type_code
679 IN
680 ( 'ADJUSTMENT' , 'FULL RETIREMENT'
681 , 'PARTIAL RETIREMENT'
682 , 'REINSTATEMENT', 'REVALUATION'
683 )
684 AND thv.transaction_header_id = fthr.transaction_header_id
685 AND (fthr.transaction_key <> 'UA'
686 OR fthr.transaction_key IS NULL)
687 AND thv.date_effective BETWEEN dp.period_open_date
688 AND nvl(dp.period_close_date, SYSDATE)
689 AND greatest(greatest(dp.calendar_period_open_date,
690 least(SYSDATE, dp.calendar_period_close_date)),
691 dp.calendar_period_open_date) > ld_end_date;
692
693 IF(ln_transaction_header_id IS NOT NULL
694 AND
695 ln_transaction_header_id <> -1
696 )
697 THEN
698 --get the old element before adjustment.
699 SELECT
700 cost
701 , salvage_value
702 , adjusted_recoverable_cost
703 , deprn_method_code
704 , production_capacity
705 , date_placed_in_service
706 INTO
707 ln_original_value
708 , ln_salvage_value
709 , ln_recoverable_cost
710 , lv_depn_meth_code
711 , ln_production_capacity
712 , ld_in_service_date
713 FROM
714 fa_books
715 WHERE transaction_header_id_out = ln_transaction_header_id;
716 END IF; --(ln_transaction_header_id IS NOT NULL)
717 EXCEPTION
718 WHEN OTHERS THEN
719 ln_transaction_header_id := -1;
720 END;
721 --check if the asset is unit adjusted or reclassed after the to_date.
722 --tax and coporate books share the same infomration,
723 --so we don't need identify the book type.
724 BEGIN
725 SELECT
726 MIN(thv.transaction_header_id)
727 INTO
728 ln_transaction_header_id
729 FROM
730 fa_deprn_periods dp
731 , fa_transaction_history_trx_v thv
732 WHERE asset_id = lv_asset_id
733 AND dp.book_type_code = lv_book_type_code
734 AND transaction_type_code IN('UNIT ADJUSTMENT', 'RECLASS')
735 AND thv.date_effective BETWEEN dp.period_open_date
736 AND nvl(dp.period_close_date, SYSDATE)
737 AND greatest(greatest(dp.calendar_period_open_date,
738 least(SYSDATE, dp.calendar_period_close_date)),
739 dp.calendar_period_open_date) > ld_end_date;
740
741 IF(ln_transaction_header_id IS NOT NULL
742 AND
743 ln_transaction_header_id <> -1
744 )
745 THEN
746 SELECT
747 fhv.units
748 , ckfv.concatenated_segments
749 INTO
750 ln_asset_unit
751 , lv_category_code
752 FROM
753 fa_asset_history_v fhv
754 , fa_categories_b_kfv ckfv
755 WHERE fhv.key = ln_transaction_header_id
756 AND fhv.transaction_header_id_out = ln_transaction_header_id
757 AND fhv.category_id = ckfv.category_id;
758 END IF; --(ln_transaction_header_id)
759 EXCEPTION
760 WHEN OTHERS THEN
761 ln_transaction_header_id := -1;
762 END;
763
764 --- monthly depreciation Rate
765 SELECT deprn_reserve
766 , impairment_reserve
767 , ltd_production
768 , deprn_amount
769 , nvl(impairment_amount, 0)
770 INTO ln_deprn_reserve
771 , ln_impairment_rsv
772 , ln_life_production
773 , ln_monthly_deprn_amount
774 , ln_mth_impairment_amount
775 FROM fa_deprn_summary
776 WHERE asset_id = lv_asset_id
777 AND period_counter = ln_depreciation_counter
778 AND book_type_code = lv_book_type_code;
779
780
781 --get the assigned unit
782 SELECT SUM(fdh.units_assigned)
783 INTO ln_assigned_unit
784 FROM fa_distribution_history fdh
785 WHERE fdh.asset_id = lv_asset_id
786 AND fdh.date_effective < nvl((SELECT MAX(fdp.period_close_date)
787 FROM fa_deprn_periods fdp
788 WHERE fdp.book_type_code = lv_book_type_code
789 AND fdp.calendar_period_close_date <= ld_end_date
790 AND fdp.period_close_date IS NOT NULL), SYSDATE)
791 AND (fdh.date_ineffective > nvl((SELECT MAX(fdp.period_close_date)
792 FROM fa_deprn_periods fdp
793 WHERE fdp.book_type_code = lv_book_type_code
794 AND fdp.calendar_period_close_date <= ld_end_date
795 AND fdp.period_close_date IS NOT NULL), SYSDATE)
796 OR fdh.date_ineffective IS NULL)
797 AND EXISTS
798 (SELECT jclllbg.bal_seg_value
799 FROM ja_cn_ledger_le_bsv_gt jclllbg
800 WHERE JA_CN_UTILITY.get_balancing_segment(fdh.code_combination_id)
801 = jclllbg.bal_seg_value
802 AND jclllbg.Ledger_Id = pn_ledger_id
803 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
804
805 GROUP BY fdh.asset_id;
806
807 --verify if the asset was retired during the period.
808 SELECT MIN(fthv.transaction_header_id)
809 INTO ln_retire_tran_header_id
810 FROM fa_transaction_history_trx_v fthv
811 , fa_retirements fr
812 WHERE fthv.book_type_code = lv_book_type_code
813 AND fthv.asset_id = lv_asset_id
814 AND fthv.transaction_type_code IN
815 ( 'FULL RETIREMENT', 'PARTIAL RETIREMENT')
816 AND
817 ((SELECT greatest(greatest(dp.calendar_period_open_date,
818 least(SYSDATE, dp.calendar_period_close_date)),
819 dp.calendar_period_open_date)
820 FROM fa_deprn_periods dp
821 WHERE fthv.book_type_code = dp.book_type_code
822 AND fthv.date_effective BETWEEN dp.period_open_date AND
823 nvl(dp.period_close_date, SYSDATE))
824 BETWEEN ld_start_date --from date
825 AND ld_end_date --to date)
826 )
827 AND fr.transaction_header_id_in = fthv.transaction_header_id
828 AND fr.status <> 'PENDING'
829 AND fr.asset_id = fthv.asset_id
830 AND (
831 TRANSACTION_HEADER_ID_OUT IS NULL
832 OR NOT EXISTS(
833 SELECT thv.transaction_header_id
834 FROM fa_transaction_history_trx_v thv
835 WHERE thv.transaction_header_id = fr.transaction_header_id_out
836 AND thv.book_type_code = lv_book_type_code
837 AND thv.asset_id = lv_asset_id
838 AND thv.transaction_type_code = 'REINSTATEMENT'
839 AND(
840 (SELECT greatest(greatest(dp.calendar_period_open_date,
841 least(SYSDATE, dp.calendar_period_close_date)),
842 dp.calendar_period_open_date)
843 FROM fa_deprn_periods dp
844 WHERE thv.book_type_code = dp.book_type_code
845 AND thv.date_effective BETWEEN dp.period_open_date AND
846 nvl(dp.period_close_date, SYSDATE))
847 BETWEEN ld_start_date --from date
848 AND ld_end_date --to date)
849 )
850 )
851 );
852 --get the cost, salvage value and unit before the retirement.
853 IF(ln_retire_tran_header_id IS NOT NULL)
854 THEN
855 SELECT fb.cost
856 , fb.salvage_value
857 INTO ln_original_value
858 , ln_salvage_value
859 FROM fa_books fb
860 WHERE fb.transaction_header_id_out = ln_retire_tran_header_id;
861
862 SELECT nvl(fds.deprn_reserve, 0)
863 , nvl(fds.impairment_reserve, 0)
864 INTO ln_deprn_rsv_last
865 , ln_impt_rsv_last
866 FROM fa_deprn_summary fds
867 WHERE fds.asset_id = lv_asset_id
868 AND fds.book_type_code = lv_book_type_code
869 AND fds.period_counter =
870 (SELECT MAX(period_counter)
871 FROM fa_deprn_summary fds2
872 WHERE fds2.asset_id = lv_asset_id
873 AND fds2.book_type_code = lv_book_type_code
874 AND fds2.period_counter < ln_depreciation_counter);
875 ln_deprn_reserve := ln_deprn_rsv_last + ln_monthly_deprn_amount;
876 ln_impairment_rsv := ln_impt_rsv_last + ln_mth_impairment_amount;
877 --get the total unit
878 SELECT SUM(fdh.units_assigned)
879 INTO ln_asset_unit
880 FROM fa_distribution_history fdh
881 WHERE fdh.asset_id = lv_asset_id
882 AND fdh.date_effective < nvl(
883 (SELECT MAX(fdp.period_close_date)
884 FROM fa_deprn_periods fdp
885 WHERE fdp.book_type_code = lv_book_type_code
886 AND fdp.calendar_period_close_date <= ld_start_date
887 AND fdp.period_close_date IS NOT NULL), SYSDATE)
888 AND (fdh.date_ineffective > nvl(
889 (SELECT MAX(fdp.period_close_date)
890 FROM fa_deprn_periods fdp
891 WHERE fdp.book_type_code = lv_book_type_code
892 AND fdp.calendar_period_close_date <= ld_start_date
893 AND fdp.period_close_date IS NOT NULL), SYSDATE)
894 OR fdh.date_ineffective IS NULL);
895 --get the assigned unit
896 SELECT SUM(fdh.units_assigned)
897 INTO ln_assigned_unit
898 FROM fa_distribution_history fdh
899 WHERE fdh.asset_id = lv_asset_id
900 AND fdh.date_effective < nvl((SELECT MAX(fdp.period_close_date)
901 FROM fa_deprn_periods fdp
902 WHERE fdp.book_type_code = lv_book_type_code
903 AND fdp.calendar_period_close_date <= ld_start_date
904 AND fdp.period_close_date IS NOT NULL), SYSDATE)
905 AND (fdh.date_ineffective > nvl((SELECT MAX(fdp.period_close_date)
906 FROM fa_deprn_periods fdp
907 WHERE fdp.book_type_code = lv_book_type_code
908 AND fdp.calendar_period_close_date <= ld_start_date
909 AND fdp.period_close_date IS NOT NULL), SYSDATE)
910 OR fdh.date_ineffective IS NULL)
911 AND EXISTS
912 (SELECT jclllbg.bal_seg_value
913 FROM ja_cn_ledger_le_bsv_gt jclllbg
914 WHERE JA_CN_UTILITY
915 .get_balancing_segment(fdh.code_combination_id) =
916 jclllbg.bal_seg_value
917 AND jclllbg.Ledger_Id = pn_ledger_id
918 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
919 GROUP BY fdh.asset_id;
920 END IF; --(ln_retire_tran_header_id IS NOT NULL)
921
922 -- calculate the assignment rate
923 IF(ln_asset_unit IS NOT NULL AND ln_asset_unit <> 0)
924 THEN
925 ln_assigned_rate := ln_assigned_unit/ln_asset_unit;
926 END IF; --(IF(ln_asset_unit IS NOT NULL AND ln_asset_unit <> 0))
927
928 --calcluate the recoverable value
929 ln_recoverable_cost := ln_original_value - ln_salvage_value;
930
931 --calculate the salvage percent.
932 IF(ln_original_value IS NOT NULL AND ln_original_value <> 0)
933 THEN
934 ln_salvage_percent := ln_salvage_value / ln_original_value;
935 END IF; --(IF(ln_original_value IS NOT NULL || ln_original_value <> 0))
936
937 --calculate the dpreciated months after addition.
938 SELECT COUNT(period_counter)
939 INTO ln_dep_month
940 FROM fa_deprn_summary
941 WHERE asset_id = lv_asset_id
942 AND period_counter <= ln_depreciation_counter
943 AND book_type_code = lv_book_type_code
944 AND deprn_source_code = 'DEPRN'
945 AND deprn_amount <> 0;
946 --calculate the depreicated months before addition.
947 SELECT nvl(fdp.calendar_period_close_date, ld_in_service_date)
948 INTO ld_addition_date
949 FROM fa_deprn_periods fdp,
950 fa_transaction_history_trx_v thv
951 WHERE thv.asset_id = lv_asset_id
952 AND fdp.book_type_code = lv_book_type_code
953 AND thv.book_type_code = fdp.book_type_code
954 AND thv.transaction_type_code = 'ADDITION'
955 AND thv.period_entered = fdp.period_name;
956
957 ln_legacy_dep_month := TRUNC(MONTHS_BETWEEN( ld_addition_date
958 , ld_in_service_date), 0
959 ) - 1;
960
961 IF(ln_legacy_dep_month > 0)
962 THEN
963 ln_dep_month := ln_dep_month + ln_legacy_dep_month;
964 END IF; --(IF(ln_legacy_dep_month > 0))
965
966 --calculate monthly depreciation rate
967 IF(ln_original_value IS NOT NULL AND ln_original_value <> 0)
968 THEN
969 ln_monthly_deprn_rate := ln_monthly_deprn_amount / ln_original_value;
970 END IF; --(ln_original_value IS NOT NULL AND ln_original_value <> 0)
971
972 --calculate the net book value
973 ln_net_book_value := ln_original_value - ln_deprn_reserve
974 - ln_impairment_rsv;
975
976
977
978 --remove the delimiter in asset category.
979 IF(lv_category_code IS NOT NULL AND lv_delimiter IS NOT NULL)
980 THEN
981 lv_category_code := REPLACE(lv_category_code, lv_delimiter, '');
982 END IF;--(lv_category_code IS NOT NULL)
983
984 --get the unit of measure and asset usage code
985 IF(ln_asset_key_ccid IS NOT NULL)
986 THEN
987 SELECT decode( lv_measure_segment
988 , 'SEGMENT1',faak.segment1, 'SEGMENT2',faak.segment2
989 , 'SEGMENT3',faak.segment3, 'SEGMENT4',faak.segment4
990 , 'SEGMENT5',faak.segment5, 'SEGMENT6',faak.segment6
991 , 'SEGMENT7',faak.segment7, 'SEGMENT8',faak.segment8
992 , 'SEGMENT9',faak.segment9, 'SEGMENT10',faak.segment10
993 ),
994 decode( lv_usage_segment
995 , 'SEGMENT1',faak.segment1, 'SEGMENT2',faak.segment2
996 , 'SEGMENT3',faak.segment3, 'SEGMENT4',faak.segment4
997 , 'SEGMENT5',faak.segment5, 'SEGMENT6',faak.segment6
998 , 'SEGMENT7',faak.segment7, 'SEGMENT8',faak.segment8
999 , 'SEGMENT9',faak.segment9, 'SEGMENT10',faak.segment10
1000 )
1001 INTO lv_asset_measure
1002 , lv_asset_usage
1003 FROM fa_asset_keywords faak
1004 WHERE faak.code_combination_id = ln_asset_key_ccid;
1005 END IF; --( IF(ln_asset_key_ccid IS NOT NULL))
1006 ln_row_count := ln_row_count + 1;
1007 Ja_Cn_Utility.Add_Sub_Root_Node
1008 ( pv_sub_root_tag_name => 'FIXED_ASSET_CARD'
1009 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
1010 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1011 );
1012 Ja_Cn_Utility.Add_Child_Node
1013 ( pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER'
1014 , pv_text_node_value => lv_asset_number
1015 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1016 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1017 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1018 );
1019 Ja_Cn_Utility.Add_Child_Node
1020 ( pv_child_tag_name => 'FIXED_ASSET_CATEGORY_CODE'
1021 , pv_text_node_value => lv_category_code
1022 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1023 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1024 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1025 );
1026 Ja_Cn_Utility.Add_Child_Node
1027 ( pv_child_tag_name => 'FIXED_ASSET_NUMBER'
1028 , pv_text_node_value => lv_asset_number
1029 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1030 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1031 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1032 );
1033 Ja_Cn_Utility.Add_Child_Node
1034 ( pv_child_tag_name => 'FIXED_ASSET_NAME'
1035 , pv_text_node_value => lv_asset_name
1036 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1037 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1038 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1039 );
1040 Ja_Cn_Utility.Add_Fixed_Child_Node
1041 ( pv_child_tag_name => 'DATE_IN_SERVICE'
1042 , pv_text_node_value => TO_CHAR(ld_in_service_date, 'YYYYMMDD')
1043 , pn_fixed_length => 8
1044 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1045 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1046 );
1047 Ja_Cn_Utility.Add_Child_Node
1048 ( pv_child_tag_name => 'ACCOUNTING_PERIOD'
1049 , pv_text_node_value => lv_accounting_period
1050 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1051 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1052 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1053 );
1054 Ja_Cn_Utility.Add_Child_Node
1055 ( pv_child_tag_name => 'FIXED_ASSET_UNIT_OF_MEASURE'
1056 , pv_text_node_value => lv_asset_measure
1057 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1058 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1059 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1060 );
1061 Ja_Cn_Utility.Add_Child_Node
1062 ( pv_child_tag_name => 'UNIT'
1063 , pv_text_node_value => ln_assigned_unit
1064 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1065 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1066 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1067 );
1068 Ja_Cn_Utility.Add_Child_Node
1069 ( pv_child_tag_name => 'MODIFICATION_METHOD_CODE'
1070 , pv_text_node_value => 'Addition'
1071 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1072 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1073 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1074 );
1075 Ja_Cn_Utility.Add_Child_Node
1076 ( pv_child_tag_name => 'USAGE_STATUS_CODE'
1077 , pv_text_node_value => lv_asset_usage
1078 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1079 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1080 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1081 );
1082 Ja_Cn_Utility.Add_Child_Node
1083 ( pv_child_tag_name => 'FUNCTIONAL_CURRENCY'
1084 , pv_text_node_value => lv_currency
1085 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1086 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1087 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1088 );
1089 Ja_Cn_Utility.Add_Child_Node
1090 ( pv_child_tag_name => 'ORIGINAL_VALUE'
1091 , pv_text_node_value => Ja_Cn_Utility.get_rounding_value
1092 ( pn_original_value =>
1093 ln_original_value * ln_assigned_rate
1094 , pn_legal_entity_id => pn_legal_entity_id
1095 )
1096 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1097 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1098 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1099 );
1100 Ja_Cn_Utility.Add_Child_Node
1101 ( pv_child_tag_name => 'NET_BOOK_VALUE'
1102 , pv_text_node_value => Ja_Cn_Utility.get_rounding_value
1103 ( pn_original_value =>
1104 ln_net_book_value * ln_assigned_rate
1105 , pn_legal_entity_id => pn_legal_entity_id
1106 )
1107 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1108 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1109 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1110 );
1111 JA_CN_UTILITY.Add_Child_Node
1112 ( pv_child_tag_name => 'FIXED_ASSET_VOUCHER_NUMBER'
1113 , pv_text_node_value => NULL
1114 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1115 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1116 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1117 );
1118 Ja_Cn_Utility.Add_Sub_Root_Node
1119 ( pv_sub_root_tag_name => 'FIXED_ASSET_CARD'
1120 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
1121 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1122 );
1123 END IF;-- (lb_retirement_flag = FALSE)
1124 EXCEPTION
1125 WHEN OTHERS THEN
1126 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1127 THEN
1128 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1129 , GV_MODULE_PREFIX || '.' || lv_procedure_name
1130 ||'.Other_Exception '
1131 , SQLCODE || SQLERRM);
1132 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1133 FND_FILE.put_line( FND_FILE.log
1134 , lv_procedure_name || SQLCODE || SQLERRM);
1135 END;
1136 END LOOP;
1137 CLOSE asset_card_cur;
1138 IF (ln_row_count = 0)
1139 THEN
1140 Ja_Cn_Utility.Print_No_Data_Found_For_Log( 'FIXED_ASSET_CARD'
1141 , Ja_Cn_Utility.GV_MODULE_FA);
1142 Ja_Cn_Utility.Add_Sub_Root_Node
1143 ( pv_sub_root_tag_name => 'FIXED_ASSET_CARD'
1144 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
1145 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1146 );
1147 Ja_Cn_Utility.Add_Child_Node
1148 ( pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER'
1149 , pv_text_node_value => NULL
1150 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1151 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1152 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1153 );
1154 Ja_Cn_Utility.Add_Child_Node
1155 ( pv_child_tag_name => 'FIXED_ASSET_CATEGORY_CODE'
1156 , pv_text_node_value => NULL
1157 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1158 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1159 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1160 );
1161 Ja_Cn_Utility.Add_Child_Node
1162 ( pv_child_tag_name => 'FIXED_ASSET_NUMBER'
1163 , pv_text_node_value => NULL
1164 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1165 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1166 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1167 );
1168 Ja_Cn_Utility.Add_Child_Node
1169 ( pv_child_tag_name => 'FIXED_ASSET_NAME'
1170 , pv_text_node_value => NULL
1171 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1172 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1173 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1174 );
1175 Ja_Cn_Utility.Add_Fixed_Child_Node
1176 ( pv_child_tag_name => 'DATE_IN_SERVICE'
1177 , pv_text_node_value => NULL
1178 , pn_fixed_length => 8
1179 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1180 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1181 );
1182 Ja_Cn_Utility.Add_Child_Node
1183 ( pv_child_tag_name => 'ACCOUNTING_PERIOD'
1184 , pv_text_node_value => NULL
1185 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1186 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1187 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1188 );
1189 Ja_Cn_Utility.Add_Child_Node
1190 ( pv_child_tag_name => 'FIXED_ASSET_UNIT_OF_MEASURE'
1191 , pv_text_node_value => NULL
1192 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1193 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1194 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1195 );
1196 Ja_Cn_Utility.Add_Child_Node
1197 ( pv_child_tag_name => 'UNIT'
1198 , pv_text_node_value => NULL
1199 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1200 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1201 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1202 );
1203 Ja_Cn_Utility.Add_Child_Node
1204 ( pv_child_tag_name => 'MODIFICATION_METHOD_CODE'
1205 , pv_text_node_value => NULL
1206 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1207 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1208 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1209 );
1210 Ja_Cn_Utility.Add_Child_Node
1211 ( pv_child_tag_name => 'USAGE_STATUS_CODE'
1212 , pv_text_node_value => NULL
1213 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1214 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1215 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1216 );
1217 Ja_Cn_Utility.Add_Child_Node
1218 ( pv_child_tag_name => 'FUNCTIONAL_CURRENCY'
1219 , pv_text_node_value => NULL
1220 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1221 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1222 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1223 );
1224 Ja_Cn_Utility.Add_Child_Node
1225 ( pv_child_tag_name => 'ORIGINAL_VALUE'
1226 , pv_text_node_value => NULL
1227 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1228 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1229 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1230 );
1231 Ja_Cn_Utility.Add_Child_Node
1232 ( pv_child_tag_name => 'NET_BOOK_VALUE'
1233 , pv_text_node_value => NULL
1234 , pv_data_type => Ja_Cn_Utility.GV_TYPE_NUMBER
1235 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1236 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1237 );
1238 JA_CN_UTILITY.Add_Child_Node
1239 ( pv_child_tag_name => 'FIXED_ASSET_VOUCHER_NUMBER'
1240 , pv_text_node_value => NULL
1241 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
1242 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
1243 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1244 );
1245 Ja_Cn_Utility.Add_Sub_Root_Node
1246 ( pv_sub_root_tag_name => 'FIXED_ASSET_CARD'
1247 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
1248 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
1249 );
1250 END IF; --(ln_row_count = 0)
1251 --logging for debug
1252 IF (ln_proc_level>=ln_dbg_level)
1253 THEN
1254 FND_LOG.STRING( ln_proc_level
1255 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
1256 , 'Exit procedure'
1257 );
1258 END IF; -- (ln_proc_level>=ln_dbg_level)
1259 EXCEPTION
1260 WHEN OTHERS THEN
1261 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1262 THEN
1263 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
1264 , GV_MODULE_PREFIX || '.' || lv_procedure_name
1265 || '.Other_Exception '
1266 , SQLCODE || SQLERRM);
1267 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1268 FND_FILE.put_line(FND_FILE.log, lv_procedure_name || SQLCODE || SQLERRM);
1269 RAISE;
1270 END Add_FA_Asset_Card_Monthly;
1271 END JA_CN_PS_FA_AC_EXP_PKG;
1272