[Home] [Help]
PACKAGE BODY: APPS.CST_ACCRUALSUMMARYREPORT_PVT
Source
1 PACKAGE BODY CST_AccrualSummaryReport_PVT AS
2 /* $Header: CSTVASRB.pls 120.14.12010000.2 2008/10/30 13:55:39 svelumur ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_AccrualSummaryReport_PVT';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 PROCEDURE Generate_SummaryReportXml (
8 errcode OUT NOCOPY VARCHAR2,
9 errno OUT NOCOPY NUMBER,
10
11 p_Chart_of_accounts_id IN NUMBER,
12 p_bal_seg_val IN NUMBER,
13 p_title IN VARCHAR2,
14 p_bal_segment_from IN VARCHAR2,
15 p_bal_segment_to IN VARCHAR2 )
16 IS
17
18 l_api_name CONSTANT VARCHAR2(100) := 'Generate_SummaryReportXml';
19 l_api_version CONSTANT NUMBER := 1.0;
20
21 l_xml_doc CLOB;
22 l_qryCtx NUMBER;
23 l_amount NUMBER ;
24 l_offset NUMBER ;
25 l_length NUMBER;
26 l_offset_val PLS_INTEGER;
27 l_buffer VARCHAR2(32767);
28 l_msg_count NUMBER;
29 l_stmt_num NUMBER;
30 l_success BOOLEAN;
31 l_return_status VARCHAR2(1);
32 l_msg_data VARCHAR2(2000);
33 l_current_org_id NUMBER;
34
35 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
36 l_module CONSTANT VARCHAR2(2000) := 'cst.plsql.' || l_full_name;
37
38 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED,
39 l_module);
40 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
41 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
42 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
43 /*Bug 7305146*/
44 l_encoding VARCHAR2(20);
45 l_xml_header VARCHAR2(100);
46
47 BEGIN
48
49 -- Initialze variables
50 l_amount := 16383;
51 l_offset := 1;
52 l_msg_count := 0;
53 l_offset_val := 21;
54
55 -- select the operating unit for which the program is launched.
56
57 l_stmt_num := 10;
58
59 l_current_org_id := MO_GLOBAL.get_current_org_id;
60
61 -- Write the module name and user parameters to fnd log file
62
63 IF (l_pLog) THEN
64 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
65 l_module || '.begin',
66 '>>> ' || l_api_name || ':Parameters:
67 Org id:'|| l_current_org_id
68 || 'Title: ' || p_title
69 || ' Balancing Segment From: ' || p_bal_segment_from
70 || ' Balancing Segment To: ' || p_bal_segment_to );
71
72 END IF;
73
74 -- Initialze variables for storing XML Data
75
76 DBMS_LOB.createtemporary(l_xml_doc, TRUE);
77
78 /*Bug 7305146*/
79 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
80 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
81 DBMS_LOB.writeappend (l_xml_doc, length(l_xml_header), l_xml_header);
82
83 DBMS_LOB.writeappend (l_xml_doc, 8, '<REPORT>');
84
85 -- Initialize message stack
86 FND_MSG_PUB.initialize;
87
88 -- Standard call to get message count and if count is 1, get message info.
89
90 FND_MSG_PUB.Count_And_Get
91 ( p_count => l_msg_count,
92 p_data => l_msg_data
93 );
94
95 /*========================================================================*/
96 -- Call to Procedure Add Parameters. To Add user entered Parameters to
97 -- XML data
98 /*========================================================================*/
99
100 l_stmt_num := 20;
101
102 Add_Parameters (p_api_version => l_api_version,
103 x_return_status => l_return_status,
104 p_init_msg_list => FND_API.G_FALSE,
105 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
106 x_msg_count => l_msg_count,
107 x_msg_data => l_msg_data,
108 i_title => p_title,
109 i_bal_segment_from => p_bal_segment_from,
110 i_bal_segment_to => p_bal_segment_to,
111 x_xml_doc => l_xml_doc);
112
113 -- Standard call to check the return status from API called
114
115 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
117 END IF;
118
119 /*========================================================================*/
120 -- Call to Procedure Add Parameters. To add summary data to XML data
121 /*========================================================================*/
122
123 l_stmt_num := 30;
124
125 Add_SummaryData (p_api_version => l_api_version,
126 p_init_msg_list => FND_API.G_FALSE,
127 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
128 x_return_status => l_return_status,
129 x_msg_count => l_msg_count,
130 x_msg_data => l_msg_data,
131 i_title => p_title,
132 i_bal_segment_from => p_bal_segment_from,
133 i_bal_segment_to => p_bal_segment_to,
134 x_xml_doc => l_xml_doc);
135
136 -- Standard call to check the return status from API called
137
138 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140 END IF;
141
142 -- write the closing tag to the XML data
143
144 DBMS_LOB.writeappend (l_xml_doc, 9, '</REPORT>');
145
146 -- write xml data to the output file
147
148 l_length := nvl(dbms_lob.getlength(l_xml_doc),0);
149 LOOP
150 EXIT WHEN l_length <= 0;
151 dbms_lob.read (l_xml_doc, l_amount, l_offset, l_buffer);
152 FND_FILE.PUT (FND_FILE.OUTPUT, l_buffer);
153 l_length := l_length - l_amount;
154 l_offset := l_offset + l_amount;
155 END LOOP;
156
157 -- Write the event log to fnd log file
158
159 IF (l_eventLog) THEN
160 FND_LOG.STRING (FND_LOG.LEVEL_EVENT,
161 l_module || '.' || l_stmt_num,
162 'Completed writing to output file');
163 END IF;
164
165 -- free temporary memory
166
167 DBMS_XMLGEN.closeContext(l_qryCtx);
168 DBMS_LOB.FREETEMPORARY (l_xml_doc);
169
170 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'Request Completed Successfully');
171
172 -- Write the module name to fnd log file
173
174 IF (l_pLog) THEN
175 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
176 l_module || '.end',
177 '<<< ' || l_api_name);
178 END IF;
179
180 EXCEPTION
181 WHEN FND_API.G_EXC_ERROR THEN
182 l_return_status := FND_API.G_RET_STS_ERROR ;
183 FND_MSG_PUB.Count_And_Get
184 ( p_count => l_msg_count,
185 p_data => l_msg_data
186 );
187
188 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
189 FND_MSG_PUB.Count_And_Get
190 ( p_count => l_msg_count,
191 p_data => l_msg_data
192 );
193
194 CST_UTILITY_PUB.writelogmessages
195 ( p_api_version => 1.0,
196 p_msg_count => l_msg_count,
197 p_msg_data => l_msg_data,
198 x_return_status => l_return_status);
199
200 l_msg_data := SUBSTRB (SQLERRM,1,240);
201 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
202
203 WHEN OTHERS THEN
204 IF (l_uLog) THEN
205 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
206 l_module || '.' || l_stmt_num,
207 SUBSTRB (SQLERRM , 1 , 240));
208 END IF;
209
210 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
211 THEN
212 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
213 END IF;
214
215 FND_MSG_PUB.Count_And_Get
216 ( p_count => l_msg_count,
217 p_data => l_msg_data
218 );
219
220 CST_UTILITY_PUB.writelogmessages
221 ( p_api_version => l_api_version,
222 p_msg_count => l_msg_count,
223 p_msg_data => l_msg_data,
224 x_return_status => l_return_status);
225
226 l_msg_data := SUBSTRB (SQLERRM,1,240);
227 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
228
229 END Generate_SummaryReportXml;
230
231 PROCEDURE Add_Parameters
232 (p_api_version IN NUMBER,
233 p_init_msg_list IN VARCHAR2 ,
234 p_validation_level IN NUMBER,
235
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239
240 i_title IN VARCHAR2,
241 i_bal_segment_from IN VARCHAR2,
242 i_bal_segment_to IN VARCHAR2,
243
244 x_xml_doc IN OUT NOCOPY CLOB)
245 IS
246
247 l_api_name CONSTANT VARCHAR2(30) := 'add_parameters';
248 l_api_version CONSTANT NUMBER := 1.0;
249
250 l_ref_cur SYS_REFCURSOR;
251 l_qryCtx NUMBER;
252 l_xml_temp CLOB;
253 l_offset PLS_INTEGER;
254 l_org_code VARCHAR2(300);
255 l_stmt_num NUMBER;
256 l_current_org_id NUMBER;
257 l_org_name VARCHAR2(300);
258
259 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
260 l_module CONSTANT VARCHAR2(2000) := 'cst.plsql.' || l_full_name;
261
262 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED,
263 l_module);
264 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
265 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
266 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
267
268 BEGIN
269
270 -- Write the module name to fnd log file
271
272 IF (l_pLog) THEN
273 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
274 l_module || '.begin',
275 '>>> ' || l_api_name);
276 END IF;
277
278 -- Standard call to check for call compatibility.
279
280 IF NOT FND_API.Compatible_API_Call ( l_api_version,
281 p_api_version,
282 l_api_name,
283 G_PKG_NAME )
284 THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF;
287
288 -- Initialize message list if p_init_msg_list is set to TRUE.
289
290 IF FND_API.to_Boolean( p_init_msg_list ) THEN
291 FND_MSG_PUB.initialize;
292 END IF;
293
294 -- Initialize API return status to success
295
296 x_return_status := FND_API.G_RET_STS_SUCCESS;
297
298 -- Initialize temporary variable to hold xml data
299
300 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
301 l_offset := 21;
302
303 -- select the operating unit for which the program is launched.
304
305 l_stmt_num := 10;
306
307 l_current_org_id := MO_GLOBAL.get_current_org_id;
308
309 -- select the operating unit code for which the program is launched.
310
311 l_stmt_num := 20;
312
313 begin
314 select mp.organization_code
315 into l_org_code
316 from mtl_parameters mp
317 where mp.organization_id = l_current_org_id;
318
319 exception
320 when no_data_found then
321 l_org_code := NULL;
322
323 end;
324
325 -- select the operating unit name for which the program is launched.
326
327 l_stmt_num := 30;
328
329 select hr.NAME
330 into l_org_name
331 from HR_ALL_ORGANIZATION_UNITS hr
332 where hr.ORGANIZATION_ID = l_current_org_id;
333
334 -- Open Ref Cursor to collect the report parameters
335
336 l_stmt_num := 40;
337
338 OPEN l_ref_cur FOR 'SELECT :l_org_code org_code,
339 :l_org_name org_name,
340 xla.NAME ledger_name,
341 xla.currency_code CUR_CODE,
342 :i_title TITLE_NAME,
343 :i_bal_segment_from from_seg,
344 :i_bal_segment_to to_seg
345 FROM XLA_GL_LEDGERS_V xla,
346 HR_ORGANIZATION_INFORMATION hoi
347 WHERE hoi.ORGANIZATION_ID = :l_current_org_id
348 and hoi.ORG_INFORMATION_CONTEXT = ''Operating Unit Information''
349 and xla.LEDGER_ID = hoi.ORG_INFORMATION3 '
350 USING l_org_code,
351 l_org_name,
352 i_title,
353 i_bal_segment_from,
354 i_bal_segment_to,
355 l_current_org_id;
356
357
358 -- create new context
359
360 l_stmt_num := 50;
361
362 l_qryCtx := DBMS_XMLGEN.newContext (l_ref_cur);
363 DBMS_XMLGEN.setRowSetTag (l_qryCtx,'PARAMETERS');
364 DBMS_XMLGEN.setRowTag (l_qryCtx,NULL);
365
366 -- get XML into the temporary clob variable
367
368 l_stmt_num := 60;
369
370 DBMS_XMLGEN.getXML (l_qryCtx, l_xml_temp, DBMS_XMLGEN.none);
371
372 -- remove the header (21 characters) and append the rest to xml output
373
374 IF (DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx) > 0) THEN
378
375 DBMS_LOB.erase (l_xml_temp, l_offset,1);
376 DBMS_LOB.append (x_xml_doc, l_xml_temp);
377 END IF;
379 -- close context and free memory
380
381 DBMS_XMLGEN.closeContext(l_qryCtx);
382 CLOSE l_ref_cur;
383 DBMS_LOB.FREETEMPORARY (l_xml_temp);
384
385 -- Standard call to get message count and if count is 1, get message info.
386
387 FND_MSG_PUB.Count_And_Get
388 ( p_count => x_msg_count,
389 p_data => x_msg_data);
390
391 -- Write the module name to fnd log file
392
393 IF (l_pLog) THEN
394 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
395 l_module || '.end',
396 '<<< ' || l_api_name);
397 END IF;
398
399 EXCEPTION
400 WHEN FND_API.G_EXC_ERROR THEN
401 x_return_status := FND_API.G_RET_STS_ERROR ;
402 FND_MSG_PUB.Count_And_Get
403 ( p_count => x_msg_count,
404 p_data => x_msg_data
405 );
406
407 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
408 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
409 FND_MSG_PUB.Count_And_Get
410 ( p_count => x_msg_count,
411 p_data => x_msg_data
412 );
413
414 WHEN OTHERS THEN
415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
416 IF (l_uLog) THEN
417 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
418 l_module || '.' || l_stmt_num,
419 SUBSTRB (SQLERRM , 1 , 240));
420 END IF;
421
422 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
423 THEN
424 FND_MSG_PUB.Add_Exc_Msg
425 ( G_PKG_NAME,
426 l_api_name
427 );
428 END IF;
429
430 FND_MSG_PUB.Count_And_Get
431 ( p_count => x_msg_count,
432 p_data => x_msg_data
433 );
434
435 END Add_Parameters;
436
437 PROCEDURE Add_SummaryData
438 (p_api_version IN NUMBER,
439 p_init_msg_list IN VARCHAR2 ,
440 p_validation_level IN NUMBER,
441
442 x_return_status OUT NOCOPY VARCHAR2,
443 x_msg_count OUT NOCOPY NUMBER,
444 x_msg_data OUT NOCOPY VARCHAR2,
445
446 i_title IN VARCHAR2,
447 i_bal_segment_from IN VARCHAR2,
448 i_bal_segment_to IN VARCHAR2,
449
450 x_xml_doc IN OUT NOCOPY CLOB)
451 IS
452
453 l_api_name CONSTANT VARCHAR2(30) := 'SUMMARY_DATA';
454 l_api_version CONSTANT NUMBER := 1.0;
455 l_ref_cur SYS_REFCURSOR;
456 l_qryCtx NUMBER;
457 l_xml_temp CLOB;
458 l_offset PLS_INTEGER;
459 l_bal_segment VARCHAR2(50);
460 l_stmt_num NUMBER;
461 l_count NUMBER;
462 l_current_org_id NUMBER;
463 l_account_range NUMBER;
464
465 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
466 l_module CONSTANT VARCHAR2(2000) := 'cst.plsql.' || l_full_name;
467
468 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED,
469 l_module);
470 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
471 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
472 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
473
474 BEGIN
475
476 -- Write the module name to fnd log file
477
478 IF (l_pLog) THEN
479 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
480 l_module || '.begin',
481 '>>> ' || l_api_name);
482 END IF;
483
484 -- Standard call to check for call compatibility.
485
486 IF NOT FND_API.Compatible_API_Call ( l_api_version,
487 p_api_version,
488 l_api_name,
489 G_PKG_NAME )
490 THEN
491 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
492 END IF;
493
494 -- Initialize message list if p_init_msg_list is set to TRUE.
495
496 IF FND_API.to_Boolean( p_init_msg_list ) THEN
497 FND_MSG_PUB.initialize;
501
498 END IF;
499
500 -- Initialize API return status to success
502 x_return_status := FND_API.G_RET_STS_SUCCESS;
503
504 -- Initialize temporary variable to hold xml data
505
506 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
507 l_offset := 21;
508
509 -- select the operating unit for which the program is launched.
510
511 l_stmt_num := 10;
512
513 l_current_org_id := MO_GLOBAL.get_current_org_id;
514
515
516 -- select the balancing segment value
517
518 l_stmt_num := 20;
519
520 SELECT fav.application_column_name
521 INTO l_bal_segment
522 FROM gl_sets_of_books gl,
523 fnd_segment_attribute_values fav,
524 hr_organization_information hr
525 WHERE hr.org_information_context = 'Operating Unit Information'
526 AND hr.organization_id = l_current_org_id
527 AND to_number(hr.org_information3) = gl.set_of_books_id
528 AND fav.segment_attribute_type = 'GL_BALANCING'
529 AND fav.attribute_value = 'Y'
530 AND fav.application_id = 101
531 AND fav.id_flex_code = 'GL#'
532 AND id_flex_num = gl.chart_of_accounts_id;
533
534
535 -- find if balancing segment range is given
536
537 IF ( (i_bal_segment_from IS NULL) AND (i_bal_segment_to IS NULL) ) THEN
538
539 l_account_range := 0;
540
541 ELSIF ( (i_bal_segment_from IS NOT NULL) AND (i_bal_segment_to IS NULL) ) THEN
542
543 l_account_range := 1;
544
545 ELSIF ( (i_bal_segment_from IS NULL) AND (i_bal_segment_to IS NOT NULL) ) THEN
546
547 l_account_range := 2;
548 ELSE
549
550 l_account_range := 3;
551 END IF;
552
553
554
555 -- open ref cur to fetch summary data
556
557 l_stmt_num := 30;
558
559 OPEN l_ref_cur FOR ' SELECT account,
560 transaction_type,
561 amount_written_off,
562 outstanding_balance
563 FROM
564 (SELECT gcc.concatenated_segments account,
565 ''AP-PO'' transaction_type,
566 SUM(crs.write_off_balance) amount_written_off,
567 SUM(crs.ap_balance +
568 crs.po_balance +
569 crs.write_off_balance) outstanding_balance
570 FROM cst_reconciliation_summary crs,
571 gl_code_combinations_kfv gcc
572 WHERE crs.accrual_account_id = gcc.code_combination_id
573 AND crs.operating_unit_id = :l_current_org_id
574 AND (( :l_account_range = 0 )
575 OR ( :l_account_range = 1 AND
576 gcc.' || l_bal_segment || ' >= :i_bal_segment_from)
577 OR ( :l_account_range = 2 AND
578 gcc.' || l_bal_segment || ' <= :i_bal_segment_to)
579 OR ( :l_account_range = 3 AND
580 gcc.' || l_bal_segment || ' BETWEEN :i_bal_segment_from
581 AND :i_bal_segment_to ) )
582 GROUP BY crs.accrual_account_id,
583 gcc.concatenated_segments
584 UNION
585 SELECT gcc.concatenated_segments account,
586 decode( cmr.transaction_type_code, ''CONSIGNMENT'',
587 (SELECT crc.displayed_field
588 FROM cst_reconciliation_codes crc
589 WHERE crc.lookup_code =
590 cmr.transaction_type_code
591 AND crc.lookup_type IN
592 ( ''ACCRUAL WRITE-OFF ACTION'',''ACCRUAL TYPE'' ) ) ,
593 decode (min(nvl(INVENTORY_TRANSACTION_ID,-1)), -1,
594 (SELECT crc.displayed_field
595 FROM cst_reconciliation_codes crc
596 WHERE crc.lookup_code = cmr.transaction_type_code
597 AND crc.lookup_type IN
598 ( ''ACCRUAL WRITE-OFF ACTION'',''ACCRUAL TYPE'' ) ),
599 (SELECT mtt.transaction_type_name
600 FROM mtl_transaction_types mtt
601 WHERE cmr.transaction_type_code =
602 to_char(mtt.transaction_type_id) ))) transaction_type,
603 (select nvl(sum (cwo.write_off_amount) ,0)
604 from cst_write_offs cwo ,
605 cst_write_off_details cwod
606 where cwo.accrual_account_id =
607 cmr.accrual_account_id
608 and cmr.TRANSACTION_TYPE_CODE =
609 cwod.TRANSACTION_TYPE_CODE
610 and cwod.write_off_id =
611 cwo.write_off_id) amount_written_off,
612 SUM(cmr.amount) outstanding_balance
613 FROM gl_code_combinations_kfv gcc,
614 cst_misc_reconciliation cmr
615 WHERE cmr.accrual_account_id = gcc.code_combination_id
616 AND cmr.operating_unit_id = :l_current_org_id
617 AND (( :l_account_range = 0 )
618 OR ( :l_account_range = 1 AND
619 gcc.' || l_bal_segment || ' >= :i_bal_segment_from)
620 OR ( :l_account_range = 2 AND
621 gcc.' || l_bal_segment || ' <= :i_bal_segment_to)
622 OR ( :l_account_range = 3 AND
623 gcc.' || l_bal_segment || ' BETWEEN :i_bal_segment_from
624 AND :i_bal_segment_to ) )
625 GROUP BY cmr.accrual_account_id,
626 gcc.concatenated_segments,
627 cmr.transaction_type_code )'
628 USING l_current_org_id,
629 l_account_range,
630 l_account_range,
631 i_bal_segment_from,
632 l_account_range,
633 i_bal_segment_to,
634 l_account_range,
635 i_bal_segment_from,
636 i_bal_segment_to,
637 l_current_org_id,
638 l_account_range,
639 l_account_range,
640 i_bal_segment_from,
641 l_account_range,
642 i_bal_segment_to,
643 l_account_range,
644 i_bal_segment_from,
645 i_bal_segment_to ;
646
647 -- create new context
648
649 l_stmt_num := 40;
650
651 l_qryCtx := DBMS_XMLGEN.newContext (l_ref_cur);
652 DBMS_XMLGEN.setRowSetTag (l_qryCtx,'SUMMARY_DATA');
653 DBMS_XMLGEN.setRowTag (l_qryCtx,'SUMMARY');
654
655
656
657 -- get XML into the temporary clob variable
658
659 l_stmt_num := 50;
660
661 DBMS_XMLGEN.getXML (l_qryCtx, l_xml_temp, DBMS_XMLGEN.none);
662
663 -- remove the header (21 characters) and append the rest to xml output
664
665 l_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
666
667 IF (DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx) > 0) THEN
668 DBMS_LOB.erase (l_xml_temp, l_offset,1);
669 DBMS_LOB.append (x_xml_doc, l_xml_temp);
670 END IF;
671
672 -- close context and free memory
673
674 DBMS_XMLGEN.closeContext(l_qryCtx);
675 CLOSE l_ref_cur;
676 DBMS_LOB.FREETEMPORARY (l_xml_temp);
677
678 -- to add number of rows processed
679
680 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
681
682 -- open ref cursor to add number of rows processed
683
684 l_stmt_num := 60;
685
686 OPEN l_ref_cur FOR SELECT l_count l_count FROM dual ;
687
688 -- create new context
689
690 l_stmt_num := 70;
691
692 l_qryCtx := DBMS_XMLGEN.newContext (l_ref_cur);
693 DBMS_XMLGEN.setRowSetTag (l_qryCtx,'record_num');
694 DBMS_XMLGEN.setRowTag (l_qryCtx,NULL);
695
696
697 -- get XML to add the number of rows processed
698
699 l_stmt_num := 80;
700
701 DBMS_XMLGEN.getXML (l_qryCtx, l_xml_temp, DBMS_XMLGEN.none);
702
703 -- remove the header (21 characters) and append the rest to xml output
704
705 IF ( DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx) > 0 ) THEN
706 DBMS_LOB.erase (l_xml_temp, l_offset,1);
707 DBMS_LOB.append (x_xml_doc, l_xml_temp);
708 END IF;
709
710 -- close context and free memory
711
712 DBMS_XMLGEN.closeContext(l_qryCtx);
713 CLOSE l_ref_cur;
714 DBMS_LOB.FREETEMPORARY (l_xml_temp);
715
716 -- Standard call to get message count and if count is 1, get message info.
717
718 FND_MSG_PUB.Count_And_Get
719 ( p_count => x_msg_count,
720 p_data => x_msg_data
721 );
722
723 -- Write the module name to fnd log file
724
725 IF (l_pLog) THEN
726 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
727 l_module || '.end',
728 '<<< ' || l_api_name);
729 END IF;
730
731 EXCEPTION
732 WHEN FND_API.G_EXC_ERROR THEN
733 x_return_status := FND_API.G_RET_STS_ERROR ;
734 FND_MSG_PUB.Count_And_Get
735 ( p_count => x_msg_count,
736 p_data => x_msg_data
737 );
738
739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
741 FND_MSG_PUB.Count_And_Get
742 ( p_count => x_msg_count,
743 p_data => x_msg_data);
744
745 WHEN OTHERS THEN
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
747 IF (l_uLog) THEN
748 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
749 l_module || '.' || l_stmt_num,
750 SUBSTRB (SQLERRM , 1 , 240));
751 END IF;
752
753 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
754 THEN
755 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
756 END IF;
757
758 FND_MSG_PUB.Count_And_Get
759 ( p_count => x_msg_count,
760 p_data => x_msg_data
761 );
762
763 END Add_SummaryData;
764
765
766 END CST_AccrualSummaryReport_PVT;