[Home] [Help]
PACKAGE BODY: APPS.JA_CN_FA_RAI_EXPORT_PKG
Source
1 PACKAGE BODY JA_CN_FA_RAI_EXPORT_PKG AS
2 --$Header: JACNRAIB.pls 120.11 2011/03/09 09:28:43 jiachi noship $
3 --+=======================================================================+
4 --| Copyright (c) 1998 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| JACNRAIB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Use this package to export real fixed asset information |
13 --| |
14 --| PROCEDURE LIST |
15 --| Add_FA_Real_Asset_Info Export real fixed asset |
16 --| information |
17 --| Add_FA_Real_Asset_Info_Monthly Export real fixed asset |
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_Real_Asset_Info_Monthly|
24 --| 09-Mar-2011 Jianchao Chi Updated for bug 11847991 |
25 --+======================================================================*/
26 GV_MODULE_PREFIX VARCHAR2(100) := 'JA_CN_FA_RAI_EXPORT';
27 --==========================================================================
28 -- PROCEDURE NAME:
29 --
30 -- Add_FA_Real_Asset_Info Public
31 --
32 -- DESCRIPTION:
33 --
34 -- This procedure is used to export real fixed asset 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_Real_Asset_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_Real_Asset_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_Real_Asset_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
138 --Add the IF statement and the logic by Jiancao Chi for bug 11847991 on 2011-03-09
139 IF (ln_row_count = 0) THEN
140 Ja_Cn_Utility.Print_No_Data_Found_For_Log('FIXED_ASSET_CARD-REAL_ASE_INFO',
141 Ja_Cn_Utility.GV_MODULE_FA);
142
143 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'FIXED_ASSET_CARD-REAL_ASE_INFO',
144 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START,
145 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
146 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER',
147 pv_text_node_value => NULL,
148 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
149 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
150 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
151 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCOUNTING_PERIOD',
152 pv_text_node_value => NULL,
153 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
154 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
155 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
156 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_TAG_NUMBER',
157 pv_text_node_value => NULL,
158 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
159 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
160 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
161 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_LOCATION',
162 pv_text_node_value => NULL,
163 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
164 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
165 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
166 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_MODEL',
167 pv_text_node_value => NULL,
168 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
169 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
170 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
171 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'FIXED_ASSET_CARD-REAL_ASE_INFO',
172 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END,
173 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
174 END IF; --IF (ln_row_count = 0)
175 --logging for debug
176 IF (ln_proc_level >= ln_dbg_level) THEN
177 FND_LOG.STRING(ln_proc_level,
178 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
179 'Exit procedure');
180 END IF; -- (ln_proc_level>=ln_dbg_level)
181
182 EXCEPTION
183 WHEN OTHERS THEN
184 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
185 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
186 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
187 '.Other_Exception ',
188 SQLCODE || SQLERRM);
189 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
190 FND_FILE.put_line(FND_FILE.log,
191 lv_procedure_name || SQLCODE || SQLERRM);
192 END Add_FA_Real_Asset_Info;
193
194 --==========================================================================
195 -- PROCEDURE NAME:
196 --
197 -- Add_FA_Real_Asset_Info_Monthly Public
198 --
199 -- DESCRIPTION:
200 --
201 -- This procedure is used to export real fixed asset information in
202 -- Fixed Asset monthly.
203 --
204 -- PARAMETERS:
205 -- In: pn_legal_entity_id LEGAL_ENTITY_ID
206 -- pn_ledger_id Ledger ID
207 -- pv_accounting_year Accounting Year
208 -- pv_period_from Period From
209 -- pv_period_to Period To
210 -- DESIGN REFERENCES:
211 -- CNAO_V2_FA_TD.doc
212 --
213 -- CHANGE HISTORY:
214 --| 04/27/2010 Qingyi Wang created
215 -- 09-Mar-2011 Jianchao Chi Updated for bug 11847991
216 --==========================================================================
217 --Changed from procdure to function, and add a return value by Jianchao Chi
218 FUNCTION Add_FA_Real_Asset_Info_Monthly(pn_legal_entity_id IN NUMBER,
219 pn_ledger_id IN NUMBER,
220 pv_accounting_year IN VARCHAR2,
221 pv_period_from IN VARCHAR2,
222 pv_period_to IN VARCHAR2)
223 RETURN NUMBER IS
224 ln_dbg_level NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
225 ln_proc_level NUMBER := FND_LOG.LEVEL_PROCEDURE;
226 ln_statement_level NUMBER := FND_LOG.LEVEL_STATEMENT;
227 ln_full_retirement_count NUMBER := 0;
228 ln_reinstate_count NUMBER := 0;
229 ln_row_count NUMBER := 0;
230 lb_retirement_flag BOOLEAN := false;
231 lv_procedure_name VARCHAR2(40) := 'Add_FA_Real_Asset_Info_Monthly';
232 lv_asset_id VARCHAR2(15);
233 lv_asset_number VARCHAR2(15);
234 lv_tag_number VARCHAR2(15);
235 lv_location VARCHAR2(123);
236 lv_model_number VARCHAR2(40);
237 lv_corp_book VARCHAR2(15);
238 ln_period_num VARCHAR2(30);
239 ld_start_date DATE;
240 ld_end_date DATE;
241 ld_retirement_date DATE;
242 ld_reinstated_date DATE;
243 ld_dp_period_close_date DATE;
244
245 CURSOR fa_usage_information_cur IS
246 SELECT DISTINCT faav.asset_number,
247 faav.asset_id,
248 faav.tag_number,
249 faav.model_number,
250 flk.concatenated_segments,
251 fb.book_type_code --- corporate book code, the asset in tax book cannot be assigned.
252 FROM fa_additions_v faav,
253 fa_books fb,
254 fa_distribution_history fdh,
255 fa_locations_kfv flk,
256 fa_book_controls_sec fbc,
257 fa_transaction_history_trx_v fthv
258 WHERE fb.date_ineffective IS NULL
259 AND fb.transaction_header_id_out IS NULL
260 --AND faav.asset_id = 109098
261 AND faav.asset_id = fb.asset_id
262 AND fb.book_type_code = fbc.book_type_code
263 AND fbc.book_class IN ('CORPORATE', 'TAX')
264 AND fbc.gl_posting_allowed_flag = 'YES'
265 -- Check ledger
266 AND fbc.set_of_books_id = pn_ledger_id
267 --AND fbc.set_of_books_id = 566
268 AND flk.location_id = fdh.location_id
269 AND fdh.asset_id = faav.asset_id
270 -- Check legal entity
271 AND EXISTS
272 (SELECT fdp.period_close_date
273 FROM fa_deprn_periods fdp
274 WHERE fdp.book_type_code = fb.book_type_code
275 AND fdp.calendar_period_close_date BETWEEN ld_start_date AND
276 ld_end_date
277 AND fdp.period_close_date IS NOT NULL)
278 AND fdh.date_effective <
279 nvl((SELECT MAX(fdp.period_close_date)
280 FROM fa_deprn_periods fdp
281 WHERE fdp.book_type_code = fb.book_type_code
282 AND fdp.calendar_period_close_date <= ld_end_date
283 AND fdp.period_close_date IS NOT NULL),
284 SYSDATE)
285 AND (fdh.date_ineffective >
286 nvl((SELECT MAX(fdp.period_close_date)
287 FROM fa_deprn_periods fdp
288 WHERE fdp.book_type_code = fb.book_type_code
289 AND fdp.calendar_period_close_date <= ld_end_date
290 AND fdp.period_close_date IS NOT NULL),
291 SYSDATE) OR fdh.date_ineffective IS NULL)
292 AND EXISTS
293 (SELECT jclllbg.bal_seg_value
294 FROM ja_cn_ledger_le_bsv_gt jclllbg
295 WHERE JA_CN_CFS_DATA_CLT_PKG.get_balancing_segment(fdh.code_combination_id) =
296 jclllbg.bal_seg_value
297 AND jclllbg.Ledger_Id = pn_ledger_id
298 AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
299 -- Check the asset type
300 AND faav.asset_type = 'CAPITALIZED'
301 -- Check the asset added date
302 AND fthv.asset_id = faav.asset_id
303 AND fthv.transaction_type_code = 'ADDITION'
304 AND fthv.book_type_code = fb.book_type_code
305 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
306 least(SYSDATE,
307 dp.calendar_period_close_date)),
308 dp.calendar_period_open_date)
309 FROM fa_deprn_periods dp
310 WHERE fthv.book_type_code = dp.book_type_code
311 AND fthv.date_effective BETWEEN dp.period_open_date AND
312 nvl(dp.period_close_date, SYSDATE)) <= ld_end_date)
313 ORDER BY faav.asset_number, flk.concatenated_segments;
314
315 BEGIN
316
317 --logging for debug
318 IF (ln_proc_level >= ln_dbg_level) THEN
319 FND_LOG.STRING(ln_proc_level,
320 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
321 '.begin',
322 'Enter procedure');
323
324 -- logging the parameters
325 FND_LOG.STRING(ln_proc_level,
326 lv_procedure_name || '.parameters',
327 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
328 'pn_ledger_id=' || pn_ledger_id || ',' ||
329 'pv_accounting_year=' || pv_accounting_year || ',' ||
330 'pv_period_from=' || pv_period_from || ',' ||
331 'pv_period_to=' || pv_period_to);
332 END IF; --l_proc_level>=l_dbg_level
333
334 FND_FILE.put_line(FND_FILE.log,
335 lv_procedure_name || '.parameters:' ||
336 'pn_legal_entity_id=' || pn_legal_entity_id || ',' ||
337 'pn_ledger_id=' || pn_ledger_id || ',' ||
338 'pv_accounting_year=' || pv_accounting_year || ',' ||
339 'pv_period_from=' || pv_period_from || ',' ||
340 'pv_period_to=' || pv_period_to);
341
342 --Fetch start data and end date
343 IF pv_period_from IS NOT NULL THEN
344 SELECT start_date, period_num
345 INTO ld_start_date, ln_period_num
346 FROM gl_period_statuses
347 WHERE ledger_id = pn_ledger_id
348 AND application_id = 101
349 AND period_name = pv_period_from
350 AND to_char(period_year) = pv_accounting_year;
351 ELSE
352 ld_start_date := to_date(pv_accounting_year || '0101', 'YYYYMMDD');
353 END IF; -- pv_period_from IS NOT NULL
354
355 IF pv_period_to IS NOT NULL THEN
356 SELECT end_date
357 INTO ld_end_date
358 FROM gl_period_statuses
359 WHERE ledger_id = pn_ledger_id
360 AND application_id = 101
361 AND period_name = pv_period_to
362 AND to_char(period_year) = pv_accounting_year;
363 ELSE
364 ld_end_date := to_date(pv_accounting_year || '1231', 'YYYYMMDD');
365 END IF; -- pv_period_to IS NOT NULL
366
367 --log start data and end date
368 IF (ln_statement_level >= ln_dbg_level) THEN
369 FND_LOG.STRING(ln_statement_level,
370 lv_procedure_name,
371 'Fetched: start date=' ||
372 nvl(to_char(ld_start_date), 'null') || ' end date=' ||
373 nvl(to_char(ld_end_date), 'null'));
374 END IF; --(ln_statement_level >= ln_dbg_level)
375 FND_FILE.put_line(FND_FILE.log,
376 lv_procedure_name || '.variable:' || 'start date=' ||
377 nvl(to_char(ld_start_date), 'null') || ' end date=' ||
378 nvl(to_char(ld_end_date), 'null'));
379
380 --get the period counter
381 SELECT MAX(fdp.period_close_date)
382 INTO ld_dp_period_close_date
383 FROM fa_deprn_periods fdp
384 WHERE greatest(greatest(fdp.calendar_period_open_date,
385 least(sysdate, fdp.calendar_period_close_date)),
386 fdp.calendar_period_open_date) BETWEEN ld_start_date AND
387 ld_end_date
388 AND fdp.period_close_date IS NOT NULL;
389
390 --get the export element
391 ln_row_count := 0;
392
393 OPEN fa_usage_information_cur;
394 LOOP
395 FETCH fa_usage_information_cur
396 INTO lv_asset_number,
397 lv_asset_id,
398 lv_tag_number,
399 lv_model_number,
400 lv_location,
401 lv_corp_book;
402 EXIT WHEN fa_usage_information_cur%NOTFOUND;
403
404 --- Check the retirement of the asset
405 lb_retirement_flag := FALSE; --reset the retirement flag.
406
407 SELECT COUNT(*)
408 INTO ln_full_retirement_count
409 FROM fa_transaction_history_trx_v fthv
410 WHERE fthv.asset_id = lv_asset_id
411 AND fthv.book_type_code = lv_corp_book
412 AND fthv.transaction_type_code = 'FULL RETIREMENT'
413 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
414 least(sysdate,
415 dp.calendar_period_close_date)),
416 dp.calendar_period_open_date)
417 FROM fa_deprn_periods dp
418 WHERE fthv.book_type_code = dp.book_type_code
419 AND fthv.date_effective BETWEEN dp.period_open_date AND
420 nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
421 IF (ln_full_retirement_count <> 0) THEN
422
423 --Check the reinstatement of the asset.
424 SELECT COUNT(*)
425 INTO ln_reinstate_count
426 FROM fa_transaction_history_trx_v fthv
427 WHERE fthv.asset_id = lv_asset_id
428 AND fthv.book_type_code = lv_corp_book
429 AND fthv.transaction_type_code = 'REINSTATEMENT'
430 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
431 least(SYSDATE,
432 dp.calendar_period_close_date)),
433 dp.calendar_period_open_date)
434 FROM fa_deprn_periods dp
435 WHERE fthv.book_type_code = dp.book_type_code
436 AND fthv.date_effective BETWEEN dp.period_open_date AND
437 nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
438 IF (ln_reinstate_count = 0) THEN
439 lb_retirement_flag := TRUE;
440 ELSE
441 --Retireve the greatest effective date of retirement. ---TODO get the real accouting retirement date.
442 SELECT greatest(date_effective)
443 INTO ld_retirement_date
444 FROM fa_transaction_history_trx_v fthv
445 WHERE fthv.asset_id = lv_asset_id
446 AND fthv.book_type_code = lv_corp_book
447 AND fthv.transaction_type_code = 'FULL RETIREMENT'
448 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
449 least(sysdate,
450 dp.calendar_period_close_date)),
451 dp.calendar_period_open_date)
452 FROM fa_deprn_periods dp
453 WHERE fthv.book_type_code = dp.book_type_code
454 AND fthv.date_effective BETWEEN dp.period_open_date AND
455 nvl(dp.period_close_date, SYSDATE)) <=
456 ld_end_date);
457 --Retireve the greatest effective date of reinstatement.
458 SELECT greatest(date_effective)
459 INTO ld_reinstated_date
460 FROM fa_transaction_history_trx_v fthv
461 WHERE fthv.asset_id = lv_asset_id
462 AND fthv.book_type_code = lv_corp_book
463 AND fthv.transaction_type_code = 'REINSTATEMENT'
464 AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
465 least(SYSDATE,
466 dp.calendar_period_close_date)),
467 dp.calendar_period_open_date)
468 FROM fa_deprn_periods dp
469 WHERE fthv.book_type_code = dp.book_type_code
470 AND fthv.date_effective BETWEEN dp.period_open_date AND
471 nvl(dp.period_close_date, SYSDATE)) <=
472 ld_end_date);
473
474 IF (ld_retirement_date > ld_reinstated_date) THEN
475 lb_retirement_flag := TRUE;
476 END IF; -- (ld_retirement_date > ld_reinstated_date)
477 END IF; -- (ln_reinstate_count = 0)
478 END IF; -- (ln_full_retirement_count <> 0)
479
480 IF (lb_retirement_flag = FALSE) THEN
481 ln_row_count := ln_row_count + 1;
482
483 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'FIXED_ASSET_CARD-REAL_ASE_INFO',
484 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START,
485 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
486 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER',
487 pv_text_node_value => lv_asset_number,
488 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
489 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
490 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
491 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'ACCOUNTING_PERIOD',
492 pv_text_node_value => ln_period_num,
493 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
494 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
495 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
496 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_TAG_NUMBER',
497 pv_text_node_value => lv_tag_number,
498 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
499 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
500 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
501 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_LOCATION',
502 pv_text_node_value => lv_location,
503 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
504 pv_required => Ja_Cn_Utility.GV_REQUIRED_NO,
505 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
506 Ja_Cn_Utility.Add_Child_Node(pv_child_tag_name => 'FIXED_ASSET_MODEL',
507 pv_text_node_value => lv_model_number,
508 pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2,
509 pv_required => Ja_Cn_Utility.GV_REQUIRED_YES,
510 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
511 Ja_Cn_Utility.Add_Sub_Root_Node(pv_sub_root_tag_name => 'FIXED_ASSET_CARD-REAL_ASE_INFO',
512 pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END,
513 pv_module_name => Ja_Cn_Utility.GV_MODULE_FA);
514 END IF; -- (lb_retirement_flag = FALSE)
515 END LOOP;
516 CLOSE fa_usage_information_cur;
517
518 /*IF (ln_row_count = 0)--Commented by Jianchao Chi for bug 11847991
519 THEN
520
521 Ja_Cn_Utility.Print_No_Data_Found_For_Log('FIXED_ASSET_CARD-REAL_ASE_INFO',
522 Ja_Cn_Utility.GV_MODULE_FA);
523
524 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'FIXED_ASSET_CARD-REAL_ASE_INFO'
525 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_START
526 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
527 );
528 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_CARD_NUMBER'
529 , pv_text_node_value => NULL
530 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
531 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
532 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
533 );
534 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'ACCOUNTING_PERIOD'
535 , pv_text_node_value => NULL
536 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
537 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
538 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
539 );
540 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_TAG_NUMBER'
541 , pv_text_node_value => NULL
542 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
543 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
544 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
545 );
546 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_LOCATION'
547 , pv_text_node_value => NULL
548 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
549 , pv_required => Ja_Cn_Utility.GV_REQUIRED_NO
550 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
551 );
552 Ja_Cn_Utility.Add_Child_Node( pv_child_tag_name => 'FIXED_ASSET_MODEL'
553 , pv_text_node_value => NULL
554 , pv_data_type => Ja_Cn_Utility.GV_TYPE_VARCHAR2
555 , pv_required => Ja_Cn_Utility.GV_REQUIRED_YES
556 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
557 );
558 Ja_Cn_Utility.Add_Sub_Root_Node( pv_sub_root_tag_name => 'FIXED_ASSET_CARD-REAL_ASE_INFO'
559 , pv_tag_type => Ja_Cn_Utility.GV_TAG_TYPE_END
560 , pv_module_name => Ja_Cn_Utility.GV_MODULE_FA
561 );
562 END IF; --(ln_row_count = 0)*/
563
564 --logging for debug
565 IF (ln_proc_level >= ln_dbg_level) THEN
566 FND_LOG.STRING(ln_proc_level,
567 GV_MODULE_PREFIX || '.' || lv_procedure_name || '.end',
568 'Exit procedure');
569 END IF; -- (ln_proc_level>=ln_dbg_level)
570
571 IF (ln_row_count = 0) THEN
572 --Added by Jianchao Chi for bug 11847991
573 RETURN 0;
574 ELSE
575 RETURN 1;
576 END IF; --(ln_row_count = 0)
577
578 EXCEPTION
579 WHEN OTHERS THEN
580 IF (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
581 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
582 GV_MODULE_PREFIX || '.' || lv_procedure_name ||
583 '.Other_Exception ',
584 SQLCODE || SQLERRM);
585 END IF; -- (FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
586 FND_FILE.put_line(FND_FILE.log,
587 lv_procedure_name || SQLCODE || SQLERRM);
588
589 END Add_FA_Real_Asset_Info_Monthly;
590
591 END JA_CN_FA_RAI_EXPORT_PKG;
592