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