[Home] [Help]
PACKAGE BODY: APPS.JA_CN_PS_FA_ACUI_EXP_PKG
Source
1 PACKAGE BODY JA_CN_PS_FA_ACUI_EXP_PKG AS
2 --$Header: JACNFUIB.pls 120.1 2010/09/13 04:17:14 wuliu noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNFUIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export fixed asset card usage information. |
13 --| for public sector |
14 --| |
15 --| PROCEDURE LIST |
16 --| Add_FA_Usage_Info Export fixed asset card usage |
17 --| information |
18 --| Add_FA_Usage_Info_Montly Export fixed asset card usage |
19 --| information monthly |
20 --| |
21 --| |
22 --| HISTORY |
23 --| 12-AUG-2010 Wuhua Liu Added |
24 --+======================================================================*/
25
26 --==========================================================================
27 -- PROCEDURE NAME:
28 --
29 -- Add_FA_Usage_Info Public
30 --
31 -- DESCRIPTION:
32 --
33 -- This procedure is used to export asset card usage information in
34 -- Fixed Asset.
35 --
36 -- PARAMETERS:
37 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
38 -- pn_ledger_id Ledger ID
39 -- pv_accounting_year Accounting Year
40 -- pv_period_from Period From
41 -- pv_period_to Period To
42 -- DESIGN REFERENCES:
43 -- TDD_1213_FIN_GL_P_CNAOV2_FA.doc
44 --
45 -- CHANGE HISTORY:
46 --| 12-AUG-2010 Wuhua Liu created
47 --==========================================================================
48 PROCEDURE Add_FA_Usage_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 IS
56 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
57 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
58 lv_procedure_name VARCHAR2(40) := 'Add_FA_Usage_Info';
59 ld_start_date DATE;
60 ld_end_date DATE;
61 ld_start_date_monthly DATE;
62 ld_end_date_monthly DATE;
63 lv_period_name VARCHAR2(15);
64
65 CURSOR fa_export_period_cur
66 IS
67 SELECT
68 DISTINCT fps.period_name
69 , fps.start_date
70 , fps.end_date
71 FROM
72 gl_period_statuses fps
73 WHERE ledger_id = pn_ledger_id
74 AND application_id = 101
75 AND fps.start_date >= ld_start_date
76 AND fps.end_date <= ld_end_date
77 AND fps.adjustment_period_flag = 'N';
78
79 BEGIN
80 --logging for debug
81 IF (ln_proc_level>=ln_dbg_level)
82 THEN
83 FND_LOG.STRING( ln_proc_level
84 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
85 || '.begin'
86 , 'Enter procedure'
87 );
88
89 -- logging the parameters
90 FND_LOG.STRING( ln_proc_level
91 , lv_procedure_name || '.parameters'
92 , 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
93 || 'pn_ledger_id=' || pn_ledger_id || ','
94 || 'pv_accounting_year=' || pv_accounting_year || ','
95 || 'pv_period_from=' || pv_period_from || ','
96 || 'pv_period_to=' || pv_period_to
97 );
98 END IF; --(l_proc_level>=l_dbg_level)
99 FND_FILE.put_line( FND_FILE.log
100 , lv_procedure_name || '.parameters:'
101 || 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
102 || 'pn_ledger_id=' || pn_ledger_id || ','
103 || 'pv_accounting_year=' || pv_accounting_year || ','
104 || 'pv_period_from=' || pv_period_from || ','
105 || 'pv_period_to=' || pv_period_to
106 );
107
108 --Fetch start data and end date
109 IF (pv_period_from IS NOT NULL)
110 THEN
111 SELECT start_date
112 INTO ld_start_date
113 FROM gl_period_statuses
114 WHERE ledger_id = pn_ledger_id
115 AND application_id = 101
116 AND period_name = pv_period_from
117 AND to_char(period_year) = pv_accounting_year;
118 ELSE
119 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
120 END IF; -- pv_period_from IS NOT NULL
121
122 IF (pv_period_to IS NOT NULL)
123 THEN
124 SELECT end_date
125 INTO ld_end_date
126 FROM gl_period_statuses
127 WHERE ledger_id = pn_ledger_id
128 AND application_id = 101
129 AND period_name = pv_period_to
130 AND to_char(period_year) = pv_accounting_year;
131 ELSE
132 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
133 END IF; -- (pv_period_to IS NOT NULL)
134
135 OPEN fa_export_period_cur;
136 LOOP
137 FETCH fa_export_period_cur
138 INTO lv_period_name
139 , ld_start_date_monthly
140 , ld_end_date_monthly;
141 EXIT WHEN fa_export_period_cur%NOTFOUND;
142 Add_FA_Usage_Info_Monthly( pn_legal_entity_id => pn_legal_entity_id
143 , pn_ledger_id => pn_ledger_id
144 , pv_accounting_year => pv_accounting_year
145 , pv_period_from => lv_period_name
146 , pv_period_to => lv_period_name);
147 END LOOP; -- OPEN fa_export_period_cur
148 CLOSE fa_export_period_cur;
149
150 --logging for debug
151 IF (ln_proc_level>=ln_dbg_level)
152 THEN
153 FND_LOG.STRING( ln_proc_level
154 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.end'
155 , 'Exit procedure');
156 END IF; -- (ln_proc_level>=ln_dbg_level)
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
161 THEN
162 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
163 , GV_MODULE_PREFIX || '.' ||lv_procedure_name
164 || '.Other_Exception '
165 , SQLCODE || SQLERRM);
166 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
167 FND_FILE.put_line( FND_FILE.log
168 , lv_procedure_name || SQLCODE || SQLERRM);
169 END Add_FA_Usage_Info;
170
171 --==========================================================================
172 -- PROCEDURE NAME:
173 --
174 -- Add_FA_Usage_Info_Monthly Public
175 --
176 -- DESCRIPTION:
177 --
178 -- This procedure is used to export asset card usage information in
179 -- Fixed Asset monthly.
180 --
181 -- PARAMETERS:
182 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
183 -- pn_ledger_id Ledger ID
184 -- pv_accounting_year Accounting Year
185 -- pv_period_from Period From
186 -- pv_period_to Period To
187 -- DESIGN REFERENCES:
188 -- TDD_1213_FIN_GL_P_CNAOV2_FA.doc
189 --
190 -- CHANGE HISTORY:
191 --| 12-AUG-2010 Wuhua Liu created
192 --==========================================================================
193 PROCEDURE Add_FA_Usage_Info_Monthly
194 ( pn_legal_entity_id IN NUMBER
195 , pn_ledger_id IN NUMBER
196 , pv_accounting_year IN VARCHAR2
197 , pv_period_from IN VARCHAR2
198 , pv_period_to IN VARCHAR2
199 )
200 IS
201 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
202 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
203 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
204 ln_full_retirement_count NUMBER := 0;
205 ln_transaction_header_id NUMBER := 0;
206 ln_reinstate_count NUMBER := 0;
207 ln_row_count NUMBER := 0;
208 ln_assignment_count NUMBER := 0;
209 ln_asset_unit NUMBER(20,2) := 0;
210 ln_assignment_unit NUMBER(20,2) := 0;
211 ln_assignment_unit_sum NUMBER(20,2) := 0;
212 ln_assigned_to NUMBER := 0;
213 ln_dep_prorate NUMBER(4,2) := 0;
214 ln_acc_dep_prorate NUMBER(4,2) := 0;
215 lv_period_name VARCHAR2(30);
216 lv_asset_id VARCHAR2(15);
217 lv_asset_number VARCHAR2(15);
218 lb_retirement_flag BOOLEAN := false;
219 lv_procedure_name VARCHAR2(40) := 'Add_FA_Usage_Info_Monthly';
220 lv_tag_number VARCHAR2(15);
221 lv_corp_book VARCHAR2(15);
222 lv_organization VARCHAR2(240);
223 ld_start_date DATE;
224 ld_end_date DATE;
225 ld_retirement_date DATE;
226 ld_reinstated_date DATE;
227
228 CURSOR fa_usage_info_cur
229 IS
230 SELECT
231 DISTINCT faav.asset_id
232 , faav.asset_number
233 , faav.tag_number
234 , faav.current_units
235 , fb.book_type_code
236 FROM
237 fa_additions_v faav
238 , fa_books fb
239 , fa_book_controls_sec fbc
240 , fa_distribution_history fdh
241 , fa_transaction_history_trx_v fthv
242 WHERE fb.date_ineffective IS NULL
243 AND fb.transaction_header_id_out IS NULL
244 AND faav.asset_id = fb.asset_id
245 AND fb.book_type_code = fbc.book_type_code
246 AND fbc.book_class IN ('CORPORATE', 'TAX')
247 AND fbc.gl_posting_allowed_flag = 'YES'
248 -- Check ledger
249 AND fbc.set_of_books_id = pn_ledger_id
250 AND fdh.asset_id = faav.asset_id
251 -- Check legal entity
252 AND EXISTS(
253 SELECT fdp.period_close_date
254 FROM fa_deprn_periods fdp
255 WHERE fdp.book_type_code = fb.book_type_code
256 AND fdp.calendar_period_close_date
257 BETWEEN ld_start_date
258 AND ld_end_date
259 AND fdp.period_close_date IS NOT NULL
260 )
261 AND fdh.date_effective < nvl(
262 (SELECT MAX(fdp.period_close_date)
263 FROM fa_deprn_periods fdp
264 WHERE fdp.book_type_code = fb.book_type_code
265 AND fdp.calendar_period_close_date <= ld_end_date
266 AND fdp.period_close_date IS NOT NULL
267 ), SYSDATE)
268 AND (fdh.date_ineffective > nvl(
269 (SELECT MAX(fdp.period_close_date)
270 FROM fa_deprn_periods fdp
271 WHERE fdp.book_type_code = fb.book_type_code
272 AND fdp.calendar_period_close_date <= ld_end_date
273 AND fdp.period_close_date IS NOT NULL
274 ), SYSDATE)
275 OR fdh.date_ineffective IS NULL)
276 AND EXISTS
277 (SELECT jclllbg.bal_seg_value
278 FROM ja_cn_ledger_le_bsv_gt jclllbg
279 WHERE JA_CN_UTILITY.get_balancing_segment
280 (fdh.code_combination_id) = jclllbg.bal_seg_value
281 AND jclllbg.Ledger_Id = pn_ledger_id
282 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
283 -- Check the asset type
284 AND faav.asset_type = 'CAPITALIZED'
285 -- Check the asset added date
286 AND fthv.asset_id = faav.asset_id
287 AND fthv.transaction_type_code = 'ADDITION'
288 AND fthv.book_type_code = fb.book_type_code
289 AND
290 ((SELECT greatest(greatest(dp.calendar_period_open_date,
291 least(SYSDATE, dp.calendar_period_close_date)),
292 dp.calendar_period_open_date)
293 FROM fa_deprn_periods dp
294 WHERE fthv.book_type_code = dp.book_type_code
295 AND fthv.date_effective BETWEEN dp.period_open_date AND
296 nvl(dp.period_close_date, SYSDATE)) <= ld_end_date)
297 ORDER BY faav.asset_number;
298
299 --the assignment of the asset
300 CURSOR fa_assignment_cur
301 ( pv_book_type_code VARCHAR2
302 , pv_asset_id VARCHAR2
303 )
304 IS
305 SELECT
306 SUM(fdh.units_assigned)
307 , paf.organization_id
308 FROM
309 fa_distribution_history fdh
310 , per_people_f ppf
311 , per_assignments_f paf
312 , per_periods_of_service pos
313 , fa_book_controls_sec fbc
314 WHERE fbc.book_type_code = pv_book_type_code
315 AND fdh.book_type_code = fbc.distribution_source_book
316 AND fdh.asset_id = pv_asset_id
317 AND (ld_end_date BETWEEN paf.effective_start_date
318 AND paf.effective_end_date
319 OR
320 (pos.final_process_date BETWEEN ld_start_date
321 AND ld_end_date
322 AND pos.final_process_date BETWEEN paf.effective_start_date
323 AND paf.effective_end_date
324 )
325 )
326 AND ((nvl( pos.final_process_date, ld_end_date) >= ld_end_date
327 AND ld_end_date BETWEEN ppf.effective_start_date
328 AND ppf.effective_end_date
329 )
330 OR (pos.final_process_date BETWEEN ld_start_date
331 AND ld_end_date
332 AND pos.final_process_date BETWEEN ppf.effective_start_date
333 AND ppf.effective_end_date)
334 )
335 -- Check legal entity
336 AND fdh.date_effective < nvl((
337 SELECT MAX(fdp.period_close_date)
338 FROM fa_deprn_periods fdp
339 WHERE fdp.book_type_code = pv_book_type_code
340 AND fdp.calendar_period_close_date <= ld_end_date
341 AND fdp.period_close_date IS NOT NULL), SYSDATE
342 )
343 AND (
344 fdh.date_ineffective > nvl((
345 SELECT MAX(fdp.period_close_date)
346 FROM fa_deprn_periods fdp
347 WHERE fdp.book_type_code = pv_book_type_code
348 AND fdp.calendar_period_close_date <= ld_end_date
349 AND fdp.period_close_date IS NOT NULL), SYSDATE
350 )
351 OR
352 fdh.date_ineffective IS NULL
353 )
354 AND fdh.assigned_to IS NOT NULL
355 AND EXISTS
356 (SELECT jclllbg.bal_seg_value
357 FROM ja_cn_ledger_le_bsv_gt jclllbg
358 WHERE JA_CN_UTILITY.get_balancing_segment(fdh.code_combination_id) =
359 jclllbg.bal_seg_value
360 AND jclllbg.Ledger_Id = pn_ledger_id
361 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
362 AND ppf.business_group_id = paf.business_group_id
363 AND pos.period_of_service_id = paf.period_of_service_id
364 AND paf.person_id = ppf.person_id
365 AND paf.person_id = fdh.assigned_to
366 AND ppf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
367 AND paf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
368 AND paf.primary_flag = 'Y'
369 GROUP BY paf.organization_id
370
371 UNION
372
373 SELECT
374 SUM(fdh.units_assigned)
375 , fdh.assigned_to
376 FROM
377 fa_distribution_history fdh
378 , fa_book_controls_sec fbc
379 WHERE fbc.book_type_code = pv_book_type_code
380 AND fdh.book_type_code = fbc.distribution_source_book
381 AND fdh.asset_id = pv_asset_id
382 -- Check legal entity
383 AND fdh.date_effective < nvl((
384 SELECT MAX(fdp.period_close_date)
385 FROM fa_deprn_periods fdp
386 WHERE fdp.book_type_code = pv_book_type_code
387 AND fdp.calendar_period_close_date <= ld_end_date
388 AND fdp.period_close_date IS NOT NULL), SYSDATE
389 )
390 AND (fdh.date_ineffective > nvl((
391 SELECT MAX(fdp.period_close_date)
392 FROM fa_deprn_periods fdp
393 WHERE fdp.book_type_code = pv_book_type_code
394 AND fdp.calendar_period_close_date <= ld_end_date
395 AND fdp.period_close_date IS NOT NULL), SYSDATE
396 )
397 OR
398 fdh.date_ineffective IS NULL)
399 AND fdh.assigned_to IS NULL
400 AND EXISTS
401 (SELECT jclllbg.bal_seg_value
402 FROM ja_cn_ledger_le_bsv_gt jclllbg
403 WHERE JA_CN_UTILITY.get_balancing_segment(fdh.code_combination_id) =
404 jclllbg.bal_seg_value
405 AND jclllbg.Ledger_Id = pn_ledger_id
406 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
407 GROUP BY assigned_to;
408
409 BEGIN
410 --logging for debug
411 IF (ln_proc_level>=ln_dbg_level)
412 THEN
413 FND_LOG.STRING( ln_proc_level
414 , GV_MODULE_PREFIX ||'.' || lv_procedure_name
415 || '.begin'
416 , 'Enter procedure');
417 -- logging the parameters
418 FND_LOG.STRING( ln_proc_level
419 , lv_procedure_name || '.parameters'
420 , 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
421 || 'pn_ledger_id=' || pn_ledger_id || ','
422 || 'pv_accounting_year=' || pv_accounting_year || ','
423 || 'pv_period_from=' || pv_period_from || ','
424 || 'pv_period_to=' || pv_period_to);
425 END IF; --l_proc_level>=l_dbg_level
426 FND_FILE.put_line( FND_FILE.log
427 , lv_procedure_name || '.parameters:'
428 || 'pn_legal_entity_id=' || pn_legal_entity_id|| ','
429 || 'pn_ledger_id=' || pn_ledger_id || ','
430 || 'pv_accounting_year=' || pv_accounting_year || ','
431 || 'pv_period_from=' || pv_period_from || ','
432 || 'pv_period_to=' || pv_period_to);
433 --Fetch start data and end date
434 IF (pv_period_from IS NOT NULL)
435 THEN
436 SELECT
437 start_date
438 , period_num
439 INTO
440 ld_start_date
441 , lv_period_name
442 FROM
443 gl_period_statuses
444 WHERE ledger_id = pn_ledger_id
445 AND application_id = 101
446 AND period_name = pv_period_from
447 AND to_char(period_year) = pv_accounting_year;
448 ELSE
449 ld_start_date := to_date(pv_accounting_year||'0101','YYYYMMDD');
450 END IF; --(pv_period_from IS NOT NULL)
451
452 IF (pv_period_to IS NOT NULL)
453 THEN
454 SELECT
455 end_date
456 INTO
457 ld_end_date
458 FROM
459 gl_period_statuses
460 WHERE ledger_id = pn_ledger_id
461 AND application_id = 101
462 AND period_name = pv_period_to
463 AND to_char(period_year) = pv_accounting_year;
464 ELSE
465 ld_end_date := to_date(pv_accounting_year||'1231','YYYYMMDD');
466 END IF; -- pv_period_to IS NOT NULL
467
468 IF (ln_statement_level >= ln_dbg_level)
469 THEN
470 FND_LOG.STRING( ln_statement_level
471 , lv_procedure_name
472 , 'Fetched: start date='
473 || nvl(to_char(ld_start_date), 'null')
474 || ' end date=' || nvl(to_char(ld_end_date), 'null'));
475 END IF; --(ln_statement_level >= ln_dbg_level)
476 FND_FILE.put_line( FND_FILE.log
477 , lv_procedure_name || '.variable:'
478 || 'start date='
479 || nvl(to_char(ld_start_date), 'null')
480 || ' end date='
481 || nvl(to_char(ld_end_date), 'null'));
482 --logging for debug
483 IF (ln_proc_level>=ln_dbg_level)
484 THEN
485 FND_LOG.STRING( ln_proc_level
486 , GV_MODULE_PREFIX ||'.' || lv_procedure_name || '.start'
487 , 'Start procedure');
488 END IF; -- (ln_proc_level>=ln_dbg_level)
489
490 --get the export element
491 ln_row_count := 0;
492 OPEN fa_usage_info_cur;
493 LOOP
494 FETCH
495 fa_usage_info_cur
496 INTO
497 lv_asset_id
498 , lv_asset_number
499 , lv_tag_number
500 , ln_asset_unit
501 , lv_corp_book;
502 EXIT WHEN fa_usage_info_cur%NOTFOUND;
503 --- check if the asset is retired.
504 lb_retirement_flag := FALSE;--reset the retirement flag.
505 ln_assignment_count := 0; --reset the counter
506 ln_assignment_unit_sum := 0;
507 ln_acc_dep_prorate := 0;
508 ln_dep_prorate := 0;
509 --- Check the retirement of the asset
510 SELECT
511 COUNT(*)
512 INTO
513 ln_full_retirement_count
514 FROM
515 fa_transaction_history_trx_v fthv
516 WHERE fthv.asset_id = lv_asset_id
517 AND fthv.book_type_code = lv_corp_book
518 AND fthv.transaction_type_code = 'FULL RETIREMENT'
519 AND ((SELECT greatest(greatest(dp.calendar_period_open_date
520 , least(SYSDATE
521 , dp.calendar_period_close_date)),
522 dp.calendar_period_open_date)
523 FROM fa_deprn_periods dp
524 WHERE fthv.book_type_code = dp.book_type_code
525 AND fthv.date_effective
526 BETWEEN dp.period_open_date
527 AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
528 IF(ln_full_retirement_count <> 0)
529 THEN
530 --Check the reinstatement of the asset.
531 SELECT COUNT(*) INTO ln_reinstate_count
532 FROM fa_transaction_history_trx_v fthv
533 WHERE fthv.asset_id = lv_asset_id
534 AND fthv.book_type_code = lv_corp_book
535 AND fthv.transaction_type_code = 'REINSTATEMENT'
536 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
537 least(SYSDATE, dp.calendar_period_close_date)),
538 dp.calendar_period_open_date)
539 FROM fa_deprn_periods dp
540 WHERE fthv.book_type_code = dp.book_type_code
541 AND fthv.date_effective
542 BETWEEN dp.period_open_date
543 AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
544 IF(ln_reinstate_count = 0)
545 THEN
546 lb_retirement_flag := TRUE;
547 ELSE
548 --Retireve the greatest effective date of retirement. .
549 SELECT greatest(date_effective) INTO ld_retirement_date
550 FROM fa_transaction_history_trx_v fthv
551 WHERE fthv.asset_id = lv_asset_id
552 AND fthv.book_type_code = lv_corp_book
553 AND fthv.transaction_type_code = 'FULL RETIREMENT'
554 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
555 least(SYSDATE, dp.calendar_period_close_date)),
556 dp.calendar_period_open_date)
557 FROM fa_deprn_periods dp
558 WHERE fthv.book_type_code = dp.book_type_code
559 AND fthv.date_effective
560 BETWEEN dp.period_open_date
561 AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
562 --Retireve the greatest effective date of reinstatement.
563 SELECT greatest(date_effective) INTO ld_reinstated_date
564 FROM fa_transaction_history_trx_v fthv
565 WHERE fthv.asset_id = lv_asset_id
566 AND fthv.book_type_code = lv_corp_book
567 AND fthv.transaction_type_code = 'REINSTATEMENT'
568 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
569 least(SYSDATE, dp.calendar_period_close_date)),
570 dp.calendar_period_open_date)
571 FROM fa_deprn_periods dp
572 WHERE fthv.book_type_code = dp.book_type_code
573 AND fthv.date_effective
574 BETWEEN dp.period_open_date
575 AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
576
577 IF(ld_retirement_date > ld_reinstated_date)
578 THEN
579 lb_retirement_flag := TRUE;
580 END IF;-- (ld_retirement_date > ld_reinstated_date)
581 END IF; -- (ln_reinstate_count = 0)
582 END IF;-- (ln_full_retirement_count <> 0)
583
584 --get the segment value and description
585 IF(lb_retirement_flag = FALSE)
586 THEN
587
588
589 --step 1: get the total number of the assignment
590 OPEN fa_assignment_cur(lv_corp_book ,lv_asset_id);
591 LOOP
592 FETCH fa_assignment_cur INTO ln_assignment_unit
593 , ln_assigned_to;
594 EXIT WHEN fa_assignment_cur%NOTFOUND;
595 ln_assignment_count := ln_assignment_count + 1;
596 ln_assignment_unit_sum := ln_assignment_unit +ln_assignment_unit_sum;
597 END LOOP;
598 CLOSE fa_assignment_cur;
599
600 --step2: recovery the total unit
601 --check if the asset is unit adjusted after the to_date.
602 BEGIN
603 SELECT
604 MIN(thv.transaction_header_id)
605 INTO
606 ln_transaction_header_id
607 FROM
608 fa_deprn_periods dp
609 , fa_transaction_history_trx_v thv
610 WHERE asset_id = lv_asset_id
611 AND transaction_type_code = 'UNIT ADJUSTMENT'
612 AND thv.date_effective BETWEEN dp.period_open_date
613 AND nvl(dp.period_close_date, sysdate)
614 AND greatest(greatest(dp.calendar_period_open_date,
615 least(sysdate, dp.calendar_period_close_date)
616 ),
617 dp.calendar_period_open_date) > ld_end_date;
618
619 IF(ln_transaction_header_id <> -1)
620 THEN
621 SELECT fhv.units INTO ln_asset_unit
622 FROM fa_asset_history_v fhv
623 WHERE fhv.key = ln_transaction_header_id
624 AND fhv.transaction_header_id_out = ln_transaction_header_id ;
625 END IF; --(ln_transaction_header_id)
626 EXCEPTION
627 WHEN No_Data_Found THEN
628 ln_transaction_header_id := -1;
629 END;
630 --step3: get the detail of each assignment
631 OPEN fa_assignment_cur( lv_corp_book
632 , lv_asset_id);
633 LOOP
634 FETCH
635 fa_assignment_cur
636 INTO
637 ln_assignment_unit
638 , lv_organization;
639 EXIT WHEN fa_assignment_cur%NOTFOUND;
640
641 --calculate the depreciation prorate
642 /**
643 IF(fa_assignment_cur%ROWCOUNT = ln_assignment_count)
644 THEN
645 ln_dep_prorate := ln_assignment_unit_sum/ln_asset_unit
646 - ln_acc_dep_prorate;
647 IF(ln_dep_prorate < 0)
648 THEN
649 ln_dep_prorate := ln_assignment_unit/ln_asset_unit;
650 END IF;--(ln_dep_prorate < 0)
651 ELSE
652 ln_dep_prorate := ln_assignment_unit/ln_asset_unit;
653 ln_acc_dep_prorate := ln_acc_dep_prorate + ln_dep_prorate;
654 END IF;
655 **/
656 ln_row_count := ln_row_count + 1;
657
658 Ja_Cn_Utility.Add_Sub_Root_Node
659 ( pv_sub_root_tag_name => 'FIXED_ASSET_CARD_USAGE_INFO'
660 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
661 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
662 );
663 Ja_Cn_Utility.Add_Child_Node
664 ( pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER'
665 , pv_text_node_value => lv_asset_number
666 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
667 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
668 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
669 );
670 Ja_Cn_Utility.Add_Child_Node
671 ( pv_child_tag_name => 'FIXED_ASSET_TAG_NUMBER'
672 , pv_text_node_value => lv_tag_number
673 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
674 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
675 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
676 );
677 Ja_Cn_Utility.Add_Child_Node
678 ( pv_child_tag_name => 'ACCOUNTING_PERIOD'
679 , pv_text_node_value => lv_period_name
680 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
681 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
682 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
683 );
684 Ja_Cn_Utility.Add_Child_Node
685 ( pv_child_tag_name => 'INNER_ORGANIZATION_ID'
686 , pv_text_node_value => lv_organization
687 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
688 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
689 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
690 );
691 Ja_Cn_Utility.Add_Sub_Root_Node
692 ( pv_sub_root_tag_name => 'FIXED_ASSET_CARD_USAGE_INFO'
693 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
694 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
695 );
696 END LOOP;
697 CLOSE fa_assignment_cur;
698 END IF;-- (lb_retirement_flag = FALSE)
699 END LOOP; -- LOOP over fa_usage_info_cur
700 CLOSE fa_usage_info_cur;
701
702 IF (ln_row_count = 0)
703 THEN
704 Ja_Cn_Utility.Print_No_Data_Found_For_Log( 'FIXED_ASSET_CARD_USAGE_INFO'
705 , Ja_Cn_Utility.GV_MODULE_FA);
706
707 Ja_Cn_Utility.Add_Sub_Root_Node
708 ( pv_sub_root_tag_name => 'FIXED_ASSET_CARD_USAGE_INFO'
709 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
710 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
711 );
712 Ja_Cn_Utility.Add_Child_Node
713 ( pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER'
714 , pv_text_node_value => NULL
715 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
716 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
717 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
718 );
719 Ja_Cn_Utility.Add_Child_Node
720 ( pv_child_tag_name => 'FIXED_ASSET_TAG_NUMBER'
721 , pv_text_node_value => NULL
722 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
723 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
724 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
725 );
726 Ja_Cn_Utility.Add_Child_Node
727 ( pv_child_tag_name => 'ACCOUNTING_PERIOD'
728 , pv_text_node_value => NULL
729 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
730 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
731 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
732 );
733 Ja_Cn_Utility.Add_Child_Node
734 ( pv_child_tag_name => 'INNER_ORGANIZATION_ID'
735 , pv_text_node_value => NULL
736 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
737 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
738 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
739 );
740 Ja_Cn_Utility.Add_Sub_Root_Node
741 ( pv_sub_root_tag_name => 'FIXED_ASSET_CARD_USAGE_INFO'
742 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
743 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
744 );
745 END IF; --(ln_row_count = 0)
746 EXCEPTION
747 WHEN OTHERS THEN
748 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
749 THEN
750 FND_LOG.STRING( FND_LOG.LEVEL_UNEXPECTED
751 , GV_MODULE_PREFIX || '.' || lv_procedure_name
752 || '.Other_Exception '
753 , SQLCODE || SQLERRM);
754 END IF; --(FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
755 FND_FILE.put_line( FND_FILE.log
756 , lv_procedure_name || SQLCODE || SQLERRM);
757 END Add_FA_Usage_Info_Monthly;
758 END JA_CN_PS_FA_ACUI_EXP_PKG;
759
760
761