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