[Home] [Help]
PACKAGE BODY: APPS.FA_XML_REPORT_PKG
Source
1 PACKAGE BODY FA_XML_REPORT_PKG AS
2 /* $Header: FAXREXTB.pls 120.15.12020000.2 2012/07/19 12:40:02 mswetha ship $ */
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FA_XML_REPORT_PKG';
7 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
8 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
9 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
10 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
11 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
12 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
13 G_LINES_PER_FETCH CONSTANT NUMBER := 1000;
14
15 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
16 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
17 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
18 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
19 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
20 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
21 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
22 G_MODULE_NAME CONSTANT VARCHAR2(80) := 'FA_XML_REPORT_PKG';
23
24
25
26 PROCEDURE clob_to_file
27 (p_xml_clob IN CLOB) IS
28
29 l_clob_size NUMBER;
30 l_offset NUMBER;
31 l_chunk_size INTEGER;
32 l_chunk VARCHAR2(32767);
33 l_log_module VARCHAR2(240);
34
35 BEGIN
36
37
38 l_clob_size := dbms_lob.getlength(p_xml_clob);
39
40 IF (l_clob_size = 0) THEN
41 RETURN;
42 END IF;
43
44 l_offset := 1;
45 l_chunk_size := 3000;
46
47 WHILE (l_clob_size > 0) LOOP
48 l_chunk := dbms_lob.substr (p_xml_clob, l_chunk_size, l_offset);
49 fnd_file.put
50 (which => fnd_file.output
51 ,buff => l_chunk);
52
53 l_clob_size := l_clob_size - l_chunk_size;
54 l_offset := l_offset + l_chunk_size;
55 END LOOP;
56
57 fnd_file.new_line(fnd_file.output,1);
58
59 EXCEPTION
60 WHEN OTHERS THEN
61 APP_EXCEPTION.RAISE_EXCEPTION;
62
63 END clob_to_file;
64
65 /*Bug#12767347 -*/
66 PROCEDURE put_encoding(code IN VARCHAR2) IS
67 l_encoding varchar2(30);
68 BEGIN
69
70 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
71 fnd_file.put_line(fnd_file.output, '<?xml version = ''1.0'' encoding = '''||l_encoding||'''?>');
72 fnd_file.new_line(fnd_file.output,1);
73
74 EXCEPTION
75
76 WHEN OTHERS then
77 APP_EXCEPTION.RAISE_EXCEPTION;
78
79 END;
80
81
82 PROCEDURE put_starttag(tag_name IN VARCHAR2) IS
83 BEGIN
84
85 fnd_file.put_line(fnd_file.output, '<'||tag_name||'>');
86 fnd_file.new_line(fnd_file.output,1);
87
88 EXCEPTION
89
90 WHEN OTHERS then
91 APP_EXCEPTION.RAISE_EXCEPTION;
92
93 END;
94
95 PROCEDURE put_endtag(tag_name IN VARCHAR2) IS
96 BEGIN
97
98 fnd_file.put_line(fnd_file.output, '</'||tag_name||'>');
99 fnd_file.new_line(fnd_file.output,1);
100
101 EXCEPTION
102
103 WHEN OTHERS then
104 APP_EXCEPTION.RAISE_EXCEPTION;
105
106 END;
107
108 PROCEDURE put_element(tag_name IN VARCHAR2,
109 value IN VARCHAR2) IS
110 BEGIN
111
112 fnd_file.put(fnd_file.output, '<'||tag_name||'>');
113 fnd_file.put(fnd_file.output, '<![CDATA[');
114 fnd_file.put(fnd_file.output, value);
115 fnd_file.put(fnd_file.output, ']]>');
116 fnd_file.put_line(fnd_file.output, '</'||tag_name||'>');
117
118
119 EXCEPTION
120
121 WHEN OTHERS then
122 APP_EXCEPTION.RAISE_EXCEPTION;
123
124 END;
125
126
127 PROCEDURE asset_impairment_report(
128 errbuf OUT NOCOPY VARCHAR2,
129 retcode OUT NOCOPY NUMBER,
130 p_book_type_code IN VARCHAR2, -- req
131 p_set_of_books_id IN NUMBER, -- req
132 p_period_counter IN NUMBER, -- req
133 p_impairment_id IN NUMBER, -- opt
134 p_cash_gen_unit_id IN NUMBER, -- opt
135 p_request_id IN NUMBER, -- opt, not displayed
136 p_status IN VARCHAR2 ) IS -- opt, not displayed
137
138 l_qryCtx DBMS_XMLGEN.ctxHandle;
139 l_result_clob CLOB;
140 l_calling_fn varchar2(200);
141 l_debug_info varchar2(200);
142
143 l_report_name varchar2(80) := 'Asset Impairment Report';
144 l_currency_code varchar2(15);
145 l_sob_name varchar2(30);
146 l_period_name varchar2(30);
147 l_reporting_flag varchar2(1);
148 l_reporting_suffix varchar2(30);
149 l_orig_set_of_books_id number(15);
150 l_orig_currency_context varchar2(15);
151
152 l_impairment_count number;
153 l_asset_count number;
154 l_temp_asset_count number;
155
156
157 r_CASH_GENERATING_UNIT varchar2(30);
158 r_IMPAIRMENT_DATE date;
159 r_NET_SELLING_PRICE number;
160 r_VALUE_IN_USE number;
161 r_GOODWILL_AMOUNT number;
162 r_ASSET_NUMBER varchar2(15);
163 r_IMPAIRMENT_ID number(15);
164 r_IMPAIRMENT_ID_text varchar2(30);
165 l_imp_description varchar2(240);
166 l_imp_date date;
167 /*Bug# 9182681 */
168 r_CGU_id number;
169 r_CASH_GENERATING_UNIT_id number;
170 l_proc_flag number := 0;
171 /*Bug12803924 */
172 l_cpp_meaning varchar2(80);
173 l_ceb_meaning varchar2(80);
174 l_oth_meaning varchar2(80);
175 l_code varchar2(30);
176 l_meaning varchar2(80);
177 l_user_lang varchar2(4);
178 cursor c_get_impair_class(c_language varchar2) is
179 select lookup_code,meaning
180 from fa_lookups_tl lk
181 where lk.lookup_type = 'IMPAIRMENT_CLASSIFICATION'
182 and lk.language = c_language;
183
184 BEGIN
185
186 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS=''.,'''; /* Added for Bug 13731230 */
187
188 l_calling_fn := 'FA_XML_REPORT_PKG.asset_impairment_report';
189
190 FA_SRVR_MSG.Init_Server_Message;
191 FA_DEBUG_PKG.Initialize;
192
193 if g_print_debug then
194 fa_debug_pkg.add(l_calling_fn, 'milestone', 'START....');
195 fa_debug_pkg.add(l_calling_fn, 'substrb(userenv(''CLIENT_INFO''),45,10)', substrb(userenv('CLIENT_INFO'),45,10));
196 end if;
197
198 -- save the orignal set of books id
199 l_orig_currency_context := substrb(userenv('CLIENT_INFO'),45,10);
200
201 if g_print_debug then
202 fa_debug_pkg.add(l_calling_fn, 'milestone', '1.1');
203 end if;
204
205 fnd_profile.get('GL_SET_OF_BKS_ID',l_orig_set_of_books_id);
206
207 -- set the given set of books id for MRC_V
208 fnd_client_info.set_currency_context (to_char(p_set_of_books_id));
209
210 -- get the book type code P,R or N
211 if not fa_cache_pkg.fazcsob
212 (x_set_of_books_id => p_set_of_books_id
213 ,x_mrc_sob_type_code => l_reporting_flag)
214 then
215 raise FND_API.G_EXC_UNEXPECTED_ERROR;
216 end if;
217 /*Bug#12803924 */
218 if not FA_CACHE_PKG.fazcbc(X_book => p_book_type_code, p_log_level_rec => null) then
219 raise FND_API.G_EXC_UNEXPECTED_ERROR;
220 end if;
221 if fa_cache_pkg.fazcbc_record.sorp_enabled_flag = 'Y' then
222 l_user_lang := userenv('LANG') ;
223 open c_get_impair_class(l_user_lang);
224 loop
225 fetch c_get_impair_class into l_code,l_meaning;
226 if ( c_get_impair_class%notfound ) then
227 exit;
228 end if;
229 if l_code = 'CPP' then
230 l_cpp_meaning := l_meaning;
231 elsif l_code = 'CEB' then
232 l_ceb_meaning := l_meaning;
233 else
234 l_oth_meaning := l_meaning;
235 end if;
236 end loop;
237 close c_get_impair_class;
238 end if;
239 if l_reporting_flag = 'R' then
240 l_reporting_suffix := 'FA_MC_ITF_IMPAIRMENTS';
241 else
242 l_reporting_suffix := 'FA_ITF_IMPAIRMENTS';
243 end if;
244
245 if g_print_debug then
246 fa_debug_pkg.add(l_calling_fn, 'milestone', '1.5');
247 end if;
248
249
250 l_debug_info := 'Resolve input parameters...';
251
252 declare
253
254 cursor c_currency (c_sob_id number) is
255 select sob.currency_code
256 ,sob.name
257 from gl_sets_of_books sob
258 where sob.set_of_books_id = c_sob_id;
259
260 cursor c_period (c_book varchar2, c_period_counter number) is
261 select period_name
262 from fa_deprn_periods
263 where book_type_code = c_book
264 and period_counter = c_period_counter;
265 -- Bug#6666666 SORP Cursor c_imp and related attributes added for SORP
266 cursor c_imp(c_impairment_id number) is
267 select description,
268 impairment_date
269 from FA_IMPAIRMENTS
270 where impairment_id = c_impairment_id;
271
272 begin
273
274 open c_currency(p_set_of_books_id);
275 fetch c_currency into l_currency_code, l_sob_name;
276 close c_currency;
277
278 open c_period(p_book_type_code, p_period_counter);
279 fetch c_period into l_period_name;
280 close c_period;
281
282 open c_imp(p_impairment_id);
283 fetch c_imp into l_imp_description,l_imp_date;
284 close c_imp;
285
286 exception
287 when others then
288 raise FND_API.G_EXC_UNEXPECTED_ERROR;
289 end;
290
291
292 l_debug_info := 'Select the main sql...';
293
294 put_encoding('UTF-8');
295 put_starttag('IMPAIRMENT_REPORT');
296 put_starttag('IMPAIRMENT_SET');
297
298 l_impairment_count := 0;
299 l_asset_count := 0;
300
301 if g_print_debug then
302 fa_debug_pkg.add(l_calling_fn, 'milestone', '2');
303 end if;
304
305
306 declare
307
308 cursor c_master is
309 SELECT CGU.CASH_GENERATING_UNIT
310 ,CGU.CASH_GENERATING_UNIT_ID /*Bug# 9182681 */
311 ,IMP.IMPAIRMENT_DATE
312 ,IMP.NET_SELLING_PRICE
313 ,IMP.VALUE_IN_USE
314 ,IMP.GOODWILL_AMOUNT
315 ,AD.ASSET_NUMBER
316 ,IMP.IMPAIRMENT_ID
317 FROM FA_IMPAIRMENTS IMP
318 ,FA_CASH_GEN_UNITS CGU
319 ,FA_ADDITIONS_B AD
320 WHERE IMP.CASH_GENERATING_UNIT_ID = CGU.CASH_GENERATING_UNIT_ID(+)
321 AND IMP.GOODWILL_ASSET_ID = AD.ASSET_ID(+)
322 AND IMP.BOOK_TYPE_CODE = p_book_type_code
323 AND IMP.PERIOD_COUNTER_IMPAIRED = p_period_counter
324 AND IMP.REQUEST_ID = nvl(p_request_id, IMP.REQUEST_ID)
325 -- AND CGU.CASH_GENERATING_UNIT_ID = nvl(p_cash_gen_unit_id, CGU.CASH_GENERATING_UNIT_ID) -- bug# 5893164
326 AND IMP.STATUS = nvl(p_status, 'POSTED')
327 AND IMP.IMPAIRMENT_ID = nvl(p_impairment_id,IMP.IMPAIRMENT_ID); --Bug#8539194
328
329 cursor c_master_mrc is
330 SELECT CGU.CASH_GENERATING_UNIT
331 ,CGU.CASH_GENERATING_UNIT_ID
332 ,IMP.IMPAIRMENT_DATE
333 ,IMP.NET_SELLING_PRICE
334 ,IMP.VALUE_IN_USE
335 ,IMP.GOODWILL_AMOUNT
336 ,AD.ASSET_NUMBER
337 ,IMP.IMPAIRMENT_ID
338 FROM FA_MC_IMPAIRMENTS IMP
339 ,FA_CASH_GEN_UNITS CGU
340 ,FA_ADDITIONS_B AD
341 WHERE IMP.CASH_GENERATING_UNIT_ID = CGU.CASH_GENERATING_UNIT_ID(+)
342 AND IMP.GOODWILL_ASSET_ID = AD.ASSET_ID(+)
343 AND IMP.BOOK_TYPE_CODE = p_book_type_code
344 AND IMP.PERIOD_COUNTER_IMPAIRED = p_period_counter
345 AND IMP.REQUEST_ID = nvl(p_request_id, IMP.REQUEST_ID)
346 -- AND CGU.CASH_GENERATING_UNIT_ID = nvl(p_cash_gen_unit_id, CGU.CASH_GENERATING_UNIT_ID) -- bug# 5893164
347 AND IMP.STATUS = nvl(p_status, 'POSTED')
348 AND IMP.IMPAIRMENT_ID = nvl(p_impairment_id,IMP.IMPAIRMENT_ID);
349
350
351 begin
352
353 if l_reporting_flag = 'R' then
354 OPEN c_master_mrc;
355 else
356 OPEN c_master;
357 end if;
358
359
360 LOOP
361 <<skip_loop>> /*Bug# 9182681 */
362 if g_print_debug then
363 fa_debug_pkg.add(l_calling_fn, 'milestone', '3.1');
364 end if;
365
366 if l_reporting_flag = 'R' then
367 FETCH c_master_mrc into
368 r_CASH_GENERATING_UNIT
369 ,r_CASH_GENERATING_UNIT_id /*Bug# 9182681 */
370 ,r_IMPAIRMENT_DATE
371 ,r_NET_SELLING_PRICE
372 ,r_VALUE_IN_USE
373 ,r_GOODWILL_AMOUNT
374 ,r_ASSET_NUMBER
375 ,r_IMPAIRMENT_ID;
376
377 EXIT when c_master_mrc%NOTFOUND;
378
379 else
380
381 if g_print_debug then
382 fa_debug_pkg.add(l_calling_fn, 'milestone', '3.2');
383 end if;
384
385 FETCH c_master into
386 r_CASH_GENERATING_UNIT
387 ,r_CASH_GENERATING_UNIT_id
388 ,r_IMPAIRMENT_DATE
389 ,r_NET_SELLING_PRICE
390 ,r_VALUE_IN_USE
391 ,r_GOODWILL_AMOUNT
392 ,r_ASSET_NUMBER
393 ,r_IMPAIRMENT_ID;
394
395 EXIT when c_master%NOTFOUND;
396
397 if g_print_debug then
398 fa_debug_pkg.add(l_calling_fn, 'milestone', '3.3');
399 end if;
400
401 end if;
402 /*Bug# 9182681 */
403 if l_proc_flag = 0 and r_CASH_GENERATING_UNIT is null then
404 l_proc_flag := 1;
405 elsif r_CASH_GENERATING_UNIT is null then
406 goto skip_loop;
407 end if;
408 l_impairment_count := l_impairment_count + 1;
409
410 put_starttag('IMPAIRMENT_RECORD');
411
412 put_element('CASH_GENERATING_UNIT',r_CASH_GENERATING_UNIT);
413 put_element('IMPAIRMENT_DATE',r_IMPAIRMENT_DATE);
414 put_element('H_NET_SELLING_PRICE',r_NET_SELLING_PRICE);
415 put_element('H_VALUE_IN_USE',r_VALUE_IN_USE);
416 put_element('GOODWILL_AMOUNT',r_GOODWILL_AMOUNT);
417 put_element('ASSET_NUMBER',r_ASSET_NUMBER);
418 put_element('IMPAIRMENT_ID',r_IMPAIRMENT_ID);
419
420 if r_IMPAIRMENT_ID is null then
421 r_IMPAIRMENT_ID_text := 'ITF.IMPAIRMENT_ID';
422 else
423 r_IMPAIRMENT_ID_text := r_IMPAIRMENT_ID;
424 end if;
425 /*Bug# 9182681 */
426 if r_CASH_GENERATING_UNIT is null then
427 r_CGU_id := -99;
428 else
429 r_CGU_id := r_CASH_GENERATING_UNIT_id;
430 end if;
431 -- Bug#6666666 SORP Start
432 /*Bug# 9182681 - Modified query to group by CGU*/
433 l_qryCtx := DBMS_XMLGEN.newContext(
434 'SELECT AD.ASSET_NUMBER
435 ,ITF.COST
436 ,(ITF.NET_BOOK_VALUE - (ITF.IMPAIRMENT_AMOUNT + nvl(ITF.REVAL_RESERVE_ADJ_AMOUNT,0))) NEW_NBV
437 ,ITF.NET_BOOK_VALUE OLD_NBV
438 ,ITF.NET_BOOK_VALUE NET_BOOK_VALUE
439 ,ITF.IMPAIRMENT_AMOUNT
440 , decode(ITF.impair_class ,''CPP'','''||l_cpp_meaning||''',''CEB'','''||l_ceb_meaning||''',''OTH'','''||l_oth_meaning||''') IMPAIR_CLASS,
441 ITF.reason,
442 ITF.REVAL_RESERVE_ADJ_AMOUNT,
443 ITF.impair_loss_acct,
444 ITF.split_impair_flag,
445 decode(ITF.split1_impair_class,''CPP'','''||l_cpp_meaning||''',''CEB'','''||l_ceb_meaning||''',''OTH'','''||l_oth_meaning||''') SPLIT1_IMPAIR_CLASS,
446 ITF.split1_reason,
447 ITF.SPLIT1_REVAL_RESERVE,
448 ITF.split1_loss_acct,
449 ITF.split1_loss_amount,
450 decode(ITF.split2_impair_class,''CPP'','''||l_cpp_meaning||''',''CEB'','''||l_ceb_meaning||''',''OTH'','''||l_oth_meaning||''') SPLIT2_IMPAIR_CLASS,
451 ITF.split2_reason,
452 ITF.SPLIT2_REVAL_RESERVE,
453 ITF.split2_loss_acct,
454 ITF.split2_loss_amount,
455 decode(ITF.split3_impair_class,''CPP'','''||l_cpp_meaning||''',''CEB'','''||l_ceb_meaning||''',''OTH'','''||l_oth_meaning||''') SPLIT3_IMPAIR_CLASS,
456 ITF.split3_reason,
457 ITF.SPLIT3_REVAL_RESERVE,
458 ITF.split3_loss_acct,
459 ITF.split3_loss_amount
460 ,ITF.NET_SELLING_PRICE NET_SELLING_PRICE
461 ,ITF.VALUE_IN_USE VALUE_IN_USE
462 FROM '||l_reporting_suffix||' ITF
463 ,FA_ADDITIONS_B AD
464 WHERE ITF.ASSET_ID = AD.ASSET_ID
465 AND ITF.BOOK_TYPE_CODE = '''||p_book_type_code||'''
466 AND NVL(ITF.cash_generating_unit_id,-99) = '''||r_CGU_id||'''
467 AND ITF.REQUEST_ID = nvl( '''||p_request_id||''', ITF.REQUEST_ID)
468 AND EXISTS
469 (SELECT 1 FROM FA_IMPAIRMENTS IMP
470 WHERE IMP.IMPAIRMENT_ID = ITF.IMPAIRMENT_ID
471 AND IMP.PERIOD_COUNTER_IMPAIRED = '''||p_period_counter||'''
472 AND IMP.STATUS = nvl( '''||p_status||''', ''POSTED''))
473 ORDER BY AD.ASSET_NUMBER'
474 );
475 -- Bug#6666666 SORP END
476 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'ASSET_SET');
477 DBMS_XMLGEN.setRowTag(l_qryCtx, 'ASSET_RECORD');
478 -- setBindValue doesn't work for 11i
479 --
480 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
481 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
482 l_temp_asset_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
483 l_asset_count := l_asset_count + l_temp_asset_count;
484 DBMS_XMLGEN.closeContext(l_qryCtx);
485 clob_to_file(l_result_clob);
486
487 put_endtag('IMPAIRMENT_RECORD');
488
489 END LOOP;
490
491 if l_reporting_flag = 'R' then
492 CLOSE c_master_mrc;
493 else
494 CLOSE c_master;
495 end if;
496
497 end;
498
499 put_endtag('IMPAIRMENT_SET');
500
501 put_starttag('SETUP');
502 /*put_element('REPORT_NAME',l_report_name); Commented for Bug 13850793 */
503 put_element('BOOK_TYPE_CODE',p_book_type_code);
504 put_element('SET_OF_BOOKS_ID',p_set_of_books_id);
505 put_element('SET_OF_BOOKS_NAME',l_sob_name);
506 put_element('CURRENCY_CODE',l_currency_code);
507 put_element('PERIOD_COUNTER',p_period_counter);
508 put_element('PERIOD_NAME',l_period_name);
509 put_element('IMPAIRMENT_ID',p_impairment_id);
510 put_element('CASH_GENERATING_UNIT_ID',p_cash_gen_unit_id);
511 put_element('REQUEST_ID',p_request_id);
512 put_element('IMPAIRMENT_DESCRIPTION',l_imp_description);
513 put_element('IMPAIRMENT_DATE',r_IMPAIRMENT_DATE); /*Bug#9000114 */
514
515 put_element('IMPAIRMENT_COUNT',to_char(l_impairment_count));
516 put_element('ASSET_COUNT',to_char(l_asset_count));
517 put_endtag('SETUP');
518
519 put_endtag('IMPAIRMENT_REPORT');
520
521 -- set back to original environment when the procedure is finished
522 fnd_client_info.set_currency_context (l_orig_currency_context);
523 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
524
525 EXCEPTION
526
527 WHEN OTHERS then
528
529 if (g_print_debug) then
530 fa_debug_pkg.add(l_calling_fn,
531 'l_debug_info',
532 l_debug_info);
533 fa_debug_pkg.add(l_calling_fn,
534 'SQLERRM',
535 substr(SQLERRM, 1, 200));
536 end if;
537
538 fa_srvr_msg.add_message(calling_fn => l_calling_fn || ':' || l_debug_info);
539
540 fa_srvr_msg.add_sql_error
541 (calling_fn => l_calling_fn);
542
543 -- set back to original environment when the procedure is finished
544 fnd_client_info.set_currency_context (l_orig_currency_context);
545 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
546
547 APP_EXCEPTION.RAISE_EXCEPTION;
548
549 END asset_impairment_report;
550
551 ----
552
553 PROCEDURE list_assets_by_cash_gen(
554 errbuf OUT NOCOPY VARCHAR2,
555 retcode OUT NOCOPY NUMBER,
556 p_book_type_code IN VARCHAR2,
557 p_set_of_books_id IN NUMBER,
558 p_cash_gen_unit_id IN NUMBER,
559 p_asset_id IN NUMBER ) IS
560
561 l_qryCtx DBMS_XMLGEN.ctxHandle;
562 l_result_clob CLOB;
563 l_calling_fn varchar2(200);
564 l_debug_info varchar2(200);
565
566 l_report_name varchar2(80) := 'List Assets by Cash Generating Unit Report';
567 l_currency_code varchar2(15);
568 l_sob_name varchar2(30);
569 --l_period_counter number(15);
570 l_reporting_flag varchar2(1);
571 l_reporting_suffix varchar2(10);
572 l_fa_books_tb_name varchar2(30);
573 l_fa_dpr_sum_tb_name varchar2(30);
574 l_orig_set_of_books_id number(15);
575 l_orig_currency_context varchar2(15);
576
577 l_unit_count number;
578 l_asset_count number;
579 l_temp_asset_count number;
580
581
582 r_cash_gen_unit varchar2(30);
583 r_cash_gen_unit_id number(15);
584 r_cash_gen_unit_id_text varchar2(30);
585 r_asset_id_text varchar2(30);
586
587
588 BEGIN
589
590
591 l_calling_fn := 'FA_XML_REPORT_PKG.list_assets_by_cash_gen';
592
593 FA_SRVR_MSG.Init_Server_Message;
594 FA_DEBUG_PKG.Initialize;
595
596 -- save the orignal set of books id
597 l_orig_currency_context := substrb(userenv('CLIENT_INFO'),45,10);
598 fnd_profile.get('GL_SET_OF_BKS_ID',l_orig_set_of_books_id);
599
600 -- set the given set of books id for MRC_V
601 fnd_client_info.set_currency_context (to_char(p_set_of_books_id));
602
603 -- get the book type code P,R or N
604 if not fa_cache_pkg.fazcsob
605 (x_set_of_books_id => p_set_of_books_id
606 ,x_mrc_sob_type_code => l_reporting_flag)
607 then
608 raise FND_API.G_EXC_UNEXPECTED_ERROR;
609 end if;
610
611 if l_reporting_flag = 'R' then
612 l_fa_books_tb_name := 'fa_mc_books';
613 l_fa_dpr_sum_tb_name := 'fa_mc_deprn_summary';
614 else
615 l_fa_books_tb_name := 'fa_books';
616 l_fa_dpr_sum_tb_name := 'fa_deprn_summary';
617 end if;
618
619
620 l_debug_info := 'Resolve input parameters...';
621
622 declare
623
624 cursor c_currency (c_sob_id number) is
625 select sob.currency_code
626 ,sob.name
627 from gl_sets_of_books sob
628 where sob.set_of_books_id = c_sob_id;
629
630 begin
631
632 open c_currency(p_set_of_books_id);
633 fetch c_currency into l_currency_code, l_sob_name;
634 close c_currency;
635
636 exception
637 when others then
638 raise FND_API.G_EXC_UNEXPECTED_ERROR;
639 end;
640
641
642 l_debug_info := 'Select the main sql...';
643
644 put_encoding('UTF-8');
645 put_starttag('LIST_ASSETS_REPORT');
646 put_starttag('CASH_GEN_UNIT_SET');
647
648 l_unit_count := 0;
649 l_asset_count := 0;
650
651 declare
652
653 cursor c_master is
654 select cash_generating_unit
655 ,cgu.cash_generating_unit_id
656 from fa_cash_gen_units cgu
657 where cgu.book_type_code = p_book_type_code
658 and cgu.cash_generating_unit_id = nvl(p_cash_gen_unit_id, cgu.cash_generating_unit_id)
659 and cgu.cash_generating_unit_id
660 in (select bk.cash_generating_unit_id
661 from fa_books bk
662 where bk.book_type_code = p_book_type_code
663 and bk.transaction_header_id_out is null
664 and bk.asset_id = nvl(p_asset_id, bk.asset_id)
665 );
666
667 cursor c_master_mrc is
668 select cash_generating_unit
669 ,cgu.cash_generating_unit_id
670 from fa_cash_gen_units cgu
671 where cgu.book_type_code = p_book_type_code
672 and cgu.cash_generating_unit_id = nvl(p_cash_gen_unit_id, cgu.cash_generating_unit_id)
673 and cgu.cash_generating_unit_id
674 in (select bk.cash_generating_unit_id
675 from fa_mc_books bk
676 where bk.book_type_code = p_book_type_code
677 and bk.transaction_header_id_out is null
678 and bk.asset_id = nvl(p_asset_id, bk.asset_id)
679 );
680
681
682 begin
683
684 if l_reporting_flag = 'R' then
685 OPEN c_master_mrc;
686 else
687 OPEN c_master;
688 end if;
689
690
691 LOOP
692
693 if l_reporting_flag = 'R' then
694 FETCH c_master_mrc into
695 r_cash_gen_unit
696 ,r_cash_gen_unit_id;
697
698 EXIT when c_master_mrc%NOTFOUND;
699
700 else
701 FETCH c_master into
702 r_cash_gen_unit
703 ,r_cash_gen_unit_id;
704
705 EXIT when c_master%NOTFOUND;
706
707 end if;
708
709 l_unit_count := l_unit_count + 1;
710
711 put_starttag('CASH_GEN_UNIT_RECORD');
712
713 put_element('CASH_GENERATING_UNIT',r_cash_gen_unit);
714 put_element('CASH_GENERATING_UNIT_ID',r_cash_gen_unit_id);
715
716 if r_cash_gen_unit_id is null then
717 r_cash_gen_unit_id_text := 'bk.cash_generating_unit_id';
718 else
719 r_cash_gen_unit_id_text := to_char(r_cash_gen_unit_id);
720 end if;
721
722 if p_asset_id is null then
723 r_asset_id_text := 'bk.asset_id';
724 else
725 r_asset_id_text := to_char(p_asset_id);
726 end if;
727
728 l_qryCtx := DBMS_XMLGEN.newContext(
729 'select ad.asset_number as asset_number
730 ,bk.cost as cost
731 ,bk.cost - ds.deprn_reserve - ds.impairment_reserve as net_book_value
732 ,ds.impairment_reserve as accumulated_impairment
733 ,ds.ytd_impairment as ytd_impairment
734 from fa_additions_b ad
735 ,'||l_fa_books_tb_name||' bk
736 ,'||l_fa_dpr_sum_tb_name||' ds
737 where bk.book_type_code = '''||p_book_type_code||'''
738 and ds.book_type_code = bk.book_type_code
739 and bk.asset_id = ad.asset_id
740 and bk.asset_id = ds.asset_id
741 and bk.cash_generating_unit_id = '||r_cash_gen_unit_id_text||'
742 and bk.asset_id = '||r_asset_id_text||'
743 and bk.transaction_header_id_out is null
744 and ds.period_counter =
745 (select max(period_counter)
746 from '||l_fa_dpr_sum_tb_name||' ds2
747 where ds2.book_type_code = '''||p_book_type_code||'''
748 and ds2.asset_id = bk.asset_id
749 )
750 order by ad.asset_number'
751 );
752
753 DBMS_XMLGEN.setRowSetTag(l_qryCtx,'ASSET_SET');
754 DBMS_XMLGEN.setRowTag(l_qryCtx, 'ASSET_RECORD');
755 -- fyi: setBindValue doesn't work for 11i
756 l_result_clob :=DBMS_XMLGEN.GETXML(l_qryCtx);
757 l_result_clob := substr(l_result_clob,instr(l_result_clob,'>')+1);
758 l_temp_asset_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
759 l_asset_count := l_asset_count + l_temp_asset_count;
760 DBMS_XMLGEN.closeContext(l_qryCtx);
761 clob_to_file(l_result_clob);
762
763 put_endtag('CASH_GEN_UNIT_RECORD');
764
765 END LOOP;
766
767 if l_reporting_flag = 'R' then
768 CLOSE c_master_mrc;
769 else
770 CLOSE c_master;
771 end if;
772
773 end;
774
775 put_endtag('CASH_GEN_UNIT_SET');
776
777 put_starttag('SETUP');
778 put_element('REPORT_NAME',l_report_name);
779 put_element('BOOK_TYPE_CODE',p_book_type_code);
780 put_element('SET_OF_BOOKS_ID',p_set_of_books_id);
781 put_element('SET_OF_BOOKS_NAME',l_sob_name);
782 put_element('CURRENCY_CODE',l_currency_code);
783 put_element('CASH_GENERATING_UNIT_ID',p_cash_gen_unit_id);
784 put_element('ASSET_ID',p_asset_id);
785
786 put_element('CASH_GEN_UNIT_COUNT',to_char(l_unit_count));
787 put_element('ASSET_COUNT',to_char(l_asset_count));
788 put_endtag('SETUP');
789
790 put_endtag('LIST_ASSETS_REPORT');
791
792 -- set back to original environment when the procedure is finished
793 fnd_client_info.set_currency_context (l_orig_currency_context);
794 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
795
796 EXCEPTION
797
798 WHEN OTHERS then
799
800 if (g_print_debug) then
801 fa_debug_pkg.add(l_calling_fn,
802 'l_debug_info',
803 l_debug_info);
804 fa_debug_pkg.add(l_calling_fn,
805 'SQLERRM',
806 substr(SQLERRM, 1, 200));
807 end if;
808
809 fa_srvr_msg.add_message(calling_fn => l_calling_fn || ':' || l_debug_info);
810
811 fa_srvr_msg.add_sql_error
812 (calling_fn => l_calling_fn);
813
814 -- set back to original environment when the procedure is finished
815 fnd_client_info.set_currency_context (l_orig_currency_context);
816 fnd_profile.put('GL_SET_OF_BKS_ID', l_orig_set_of_books_id);
817
818 APP_EXCEPTION.RAISE_EXCEPTION;
819
820 END list_assets_by_cash_gen;
821
822
823 END FA_XML_REPORT_PKG;