[Home] [Help]
PACKAGE BODY: APPS.CST_MISCACCRUALREPORT_PVT
Source
1 PACKAGE BODY CST_MiscAccrualReport_PVT AS
2 /* $Header: CSTVAMRB.pls 120.23.12020000.2 2012/09/11 11:37:31 vnagasow ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(2000) := 'CST_MiscAccrualReport_PVT';
5 G_LOG_HEADER CONSTANT VARCHAR2(100) := 'cst.plsql.CST_ACCRUAL_MISC_REPORT';
6 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7
8 PROCEDURE Generate_MiscReportXml (
9 errcode OUT NOCOPY VARCHAR2,
10 errno OUT NOCOPY NUMBER,
11
12 p_Chart_of_accounts_id IN NUMBER,
13 p_bal_seg_val IN NUMBER,
14 p_title IN VARCHAR2,
15 p_bal_segment_from IN VARCHAR2,
16 p_bal_segment_to IN VARCHAR2,
17 p_from_date IN VARCHAR2,
18 p_to_date IN VARCHAR2,
19 p_from_amount IN NUMBER,
20 p_to_amount IN NUMBER,
21 p_from_item IN VARCHAR2,
22 p_to_item IN VARCHAR2,
23 p_sort_by IN VARCHAR2 )
24 IS
25
26 l_api_name CONSTANT VARCHAR2(2000) := 'Generate_MiscAccrualReportXml';
27 l_api_version CONSTANT NUMBER := 1.0;
28
29 l_xml_doc CLOB;
30 l_qryCtx NUMBER;
31 l_from_date DATE;
32 l_to_date DATE;
33 l_current_org_id NUMBER;
34
35 l_amount NUMBER ;
36 l_offset NUMBER ;
37 l_length NUMBER;
38 l_buffer VARCHAR2(32767);
39 l_stmt_num NUMBER;
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(2000);
42 l_return_status VARCHAR2(1);
43 l_success BOOLEAN;
44 l_error_message VARCHAR2(300);
45
46 l_full_name CONSTANT VARCHAR2(4000) := G_PKG_NAME || '.' || l_api_name;
47 l_module CONSTANT VARCHAR2(4000) := 'cst.plsql.' || l_full_name;
48
49 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED,
50 l_module);
51 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
52 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
53 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
54 l_sLog CONSTANT BOOLEAN := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
55
56 l_conc_request BOOLEAN;
57 /*Bug 7305146*/
58 l_encoding VARCHAR2(20);
59 l_xml_header VARCHAR2(100);
60 BEGIN
61
62 EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS=''.,''';
63
64 -- Initialze variables
65 l_amount := 16383; --Changed for bug 7013852
66 l_offset := 1;
67 l_return_status := fnd_api.g_ret_sts_success;
68 l_msg_count := 0;
69
70
71
72 -- select the operating unit for which the program is launched.
73
74 l_stmt_num := 5;
75
76 l_current_org_id := MO_GLOBAL.get_current_org_id;
77
78 -- Initialze variables for storing XML Data
79
80 DBMS_LOB.createtemporary(l_xml_doc, TRUE);
81 /*Bug 7305146*/
82 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
83 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
84 DBMS_LOB.writeappend (l_xml_doc, length(l_xml_header), l_xml_header);
85
86 DBMS_LOB.writeappend (l_xml_doc, 8, '<REPORT>');
87
88 -- convert from date parameter to date type variable
89
90 l_stmt_num := 10;
91
92 IF (p_from_date IS NOT NULL) THEN
93 l_from_date := FND_DATE.canonical_to_date(p_from_date);
94 ELSE
95 l_from_date := NULL;
96 END IF;
97
98 -- convert to date parameter to date type variable
99
100 l_stmt_num := 20;
101
102 IF (p_to_date IS NOT NULL) THEN
103 l_to_date := FND_DATE.canonical_to_date(p_to_date );
104 ELSE
105 l_to_date := NULL;
106 END IF;
107
108 -- Write the module name and user parameters to fnd log file
109
110 IF (l_pLog) THEN
111 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
112 l_module || '.begin',
113 '>>> ' || l_api_name || ':Parameters:
114 Org id:'|| l_current_org_id
115 || 'Title: ' || p_title
116 || 'Sort Option: ' || p_sort_by
117 || ' From Date: ' || l_from_date
118 || ' To Date: ' || l_to_date
119 || ' From Item: ' || p_from_item
120 || ' To Item: ' || p_to_item
121 || ' From Amount: ' || p_from_amount
122 || ' To Amount: ' || p_to_amount
126 END IF;
123 || ' Balancing Segment From: ' || p_bal_segment_from
124 || ' Balancing Segment To: ' || p_bal_segment_to );
125
127
128 l_stmt_num := 30;
129
130 /* check if to_date is greater than or equal to to_date */
131
132 If (p_from_date is not null and p_to_date < p_from_date ) then
133
134 l_error_message := 'CST_INVALID_TO_DATE';
135 fnd_message.set_name('BOM','CST_INVALID_TO_DATE');
136 RAISE fnd_api.g_exc_error;
137 End If;
138
139 /* check if to_amount is greater than or equal to from_amount */
140
141 If (p_from_amount is not null and p_to_amount < p_from_amount ) then
142
143 l_error_message := 'CST_INVALID_TO_AMOUNT';
144 fnd_message.set_name('BOM','CST_INVALID_TO_AMOUNT');
145 RAISE fnd_api.g_exc_error;
146 End If;
147
148
149 -- Standard call to get message count and if count is 1, get message info.
150
151 FND_MSG_PUB.Count_And_Get
152 ( p_count => l_msg_count,
153 p_data => l_msg_data
154 );
155
156
157
158 /*========================================================================*/
159 -- Call to Procedure Add Parameters. To Add user entered Parameters to
160 -- XML data
161 /*========================================================================*/
162
163 l_stmt_num := 40;
164
165 Add_Parameters (p_api_version => l_api_version,
166 p_init_msg_list => FND_API.G_FALSE,
167 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
168 x_return_status => l_return_status,
169 x_msg_count => l_msg_count,
170 x_msg_data => l_msg_data,
171 i_title => p_title,
172 i_sort_by => p_sort_by,
173 i_from_date => l_from_date,
174 i_to_date => l_to_date,
175 i_from_item => p_from_item,
176 i_to_item => p_to_item,
177 i_from_amount => p_from_amount,
178 i_to_amount => p_to_amount,
179 i_bal_segment_from => p_bal_segment_from,
180 i_bal_segment_to => p_bal_segment_to,
181 x_xml_doc => l_xml_doc);
182
183 -- Standard call to check the return status from API called
184
185 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END IF;
188
189
190 /*========================================================================*/
191 -- Call to Procedure Add Parameters. To add misc data to XML data
192 /*========================================================================*/
193
194 l_stmt_num := 50;
195
196 Add_MiscData (p_api_version => l_api_version,
197 p_init_msg_list => FND_API.G_FALSE,
198 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
199 x_return_status => l_return_status,
200 x_msg_count => l_msg_count,
201 x_msg_data => l_msg_data,
202 i_title => p_title,
203 i_sort_by => p_sort_by,
204 i_from_date => l_from_date ,
205 i_to_date => l_to_date,
206 i_from_item => p_from_item,
207 i_to_item => p_to_item,
208 i_from_amount => p_from_amount,
209 i_to_amount => p_to_amount,
210 i_bal_segment_from => p_bal_segment_from,
211 i_bal_segment_to => p_bal_segment_to,
212 x_xml_doc => l_xml_doc);
213
214 -- Standard call to check the return status from API called
215
216 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 END IF;
219
220 -- write the closing tag to the XML data
221
222 DBMS_LOB.writeappend (l_xml_doc, 9, '</REPORT>');
223
224 -- write xml data to the output file
225
226 l_length := nvl(dbms_lob.getlength(l_xml_doc),0);
227 LOOP
228 EXIT WHEN l_length <= 0;
229 dbms_lob.read (l_xml_doc, l_amount, l_offset, l_buffer);
230 FND_FILE.PUT (FND_FILE.OUTPUT, l_buffer);
231 l_length := l_length - l_amount;
232 l_offset := l_offset + l_amount;
233 END LOOP;
234
235 DBMS_XMLGEN.closeContext(l_qryCtx);
236
237
238 -- Write the event log to fnd log file
239
240 IF (l_eventLog) THEN
241 FND_LOG.STRING (FND_LOG.LEVEL_EVENT,
242 l_module || '.' || l_stmt_num,
243 'Completed writing to output file');
244 END IF;
245
246 -- free temporary memory and close the context
247 DBMS_LOB.FREETEMPORARY (l_xml_doc);
248
252
249 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'Request Completed Successfully');
250
251 -- Write the module name to fnd log file
253 IF (l_pLog) THEN
254 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
255 l_module || '.end',
256 '<<< ' || l_api_name);
257 END IF;
258
259 EXCEPTION
260
261 WHEN FND_API.G_EXC_ERROR THEN
262 ROLLBACK;
263 l_return_status := FND_API.g_ret_sts_error;
264 If l_errorLog then
265 fnd_log.message(FND_LOG.LEVEL_ERROR,
266 G_LOG_HEADER || '.' || l_api_name || '(' ||to_char(l_stmt_num)||')',
267 FALSE
268 );
269 end If;
270
271 fnd_msg_pub.add;
272
273 If l_slog then
274 fnd_log.string(FND_LOG.LEVEL_STATEMENT,
275 G_LOG_HEADER || '.'||l_api_name||'('||to_char(l_stmt_num)||')',
276 l_error_message
277 );
278 End If;
279
280 FND_MSG_PUB.count_and_get
281 ( p_count => l_msg_count
282 , p_data => l_msg_data
283 );
284
285
286 CST_UTILITY_PUB.writelogmessages
287 ( p_api_version => l_api_version,
288 p_msg_count => l_msg_count,
289 p_msg_data => l_msg_data,
290 x_return_status => l_return_status);
291
292 l_msg_data := SUBSTRB (SQLERRM,1,240);
293 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
294
295 l_conc_request := fnd_concurrent.set_completion_status('ERROR',substr(fnd_message.get_string('BOM',l_error_message),1,240));
296
297
298 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
299 FND_MSG_PUB.Count_And_Get
300 ( p_count => l_msg_count,
301 p_data => l_msg_data
302 );
303
304 CST_UTILITY_PUB.writelogmessages
305 ( p_api_version => l_api_version,
306 p_msg_count => l_msg_count,
307 p_msg_data => l_msg_data,
308 x_return_status => l_return_status);
309
310 l_msg_data := SUBSTRB (SQLERRM,1,240);
311 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
312
313 WHEN OTHERS THEN
314 IF (l_uLog) THEN
315 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
316 l_module || '.' || l_stmt_num,
317 SUBSTRB (SQLERRM , 1 , 240));
318 END IF;
319
320 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
321 THEN
322 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
323 END IF;
324
325 FND_MSG_PUB.Count_And_Get
326 ( p_count => l_msg_count,
327 p_data => l_msg_data
328 );
329
330 CST_UTILITY_PUB.writelogmessages
331 ( p_api_version => l_api_version,
332 p_msg_count => l_msg_count,
333 p_msg_data => l_msg_data,
334 x_return_status => l_return_status);
335
336 l_msg_data := SUBSTRB (SQLERRM,1,240);
337 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
338
339 END Generate_MiscReportXml;
340
341 PROCEDURE Add_Parameters
342 (p_api_version IN NUMBER,
343 p_init_msg_list IN VARCHAR2,
344 p_validation_level IN NUMBER,
345
346 x_return_status OUT NOCOPY VARCHAR2,
347 x_msg_count OUT NOCOPY NUMBER,
348 x_msg_data OUT NOCOPY VARCHAR2,
349
350 i_title IN VARCHAR2,
351 i_sort_by IN VARCHAR2,
352 i_from_date IN DATE,
353 i_to_date IN DATE,
354 i_from_item IN VARCHAR2,
355 i_to_item IN VARCHAR2,
356 i_from_amount IN NUMBER,
357 i_to_amount IN NUMBER,
358 i_bal_segment_from IN VARCHAR2,
359 i_bal_segment_to IN VARCHAR2,
360
361 x_xml_doc IN OUT NOCOPY CLOB)
362 IS
363
364 l_api_name CONSTANT VARCHAR2(3000) := 'ADD_PARAMETERS';
365 l_api_version CONSTANT NUMBER := 1.0;
366 l_ref_cur SYS_REFCURSOR;
367 l_qryCtx NUMBER;
368 l_xml_temp CLOB;
369 l_offset PLS_INTEGER;
370 l_stmt_num NUMBER;
371 l_current_org_id NUMBER;
372 l_age_option NUMBER;
373 l_org_code VARCHAR2(3000);
374 l_org_name VARCHAR2(3000);
375
376 l_full_name CONSTANT VARCHAR2(3000) := G_PKG_NAME || '.' || l_api_name;
377 l_module CONSTANT VARCHAR2(3000) := 'cst.plsql.' || l_full_name;
378
382 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
379 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED,
380 l_module);
381 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
383 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
384
385 BEGIN
386
387 -- Write the module name to fnd log file
388
389 IF (l_pLog) THEN
390 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
391 l_module || '.begin',
392 '>>> ' || l_api_name);
393 END IF;
394
395 -- Standard call to check for call compatibility.
396
397 IF NOT FND_API.Compatible_API_Call ( l_api_version,
398 p_api_version,
399 l_api_name,
400 G_PKG_NAME )
401 THEN
402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
403 END IF;
404
405 -- Initialize message list if p_init_msg_list is set to TRUE.
406
407 IF FND_API.to_Boolean( p_init_msg_list ) THEN
408 FND_MSG_PUB.initialize;
409 END IF;
410
411 -- Initialize API return status to success
412
413 x_return_status := FND_API.G_RET_STS_SUCCESS;
414
415 -- Initialize temporary variable to hold xml data
416
417 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
418 l_offset := 21;
419
420 l_stmt_num := 10;
421
422 -- Get the proile value to determine the aging basis
423
424 fnd_profile.get('CST_ACCRUAL_AGE_IN_DAYS', l_age_option);
425
426
427 -- select the operating unit for which the program is launched.
428
429 l_stmt_num := 20;
430
431 l_current_org_id := MO_GLOBAL.get_current_org_id;
432
433 -- select the operating unit code for which the program is launched.
434
435 l_stmt_num := 30;
436
437 begin
438 select mp.organization_code
439 into l_org_code
440 from mtl_parameters mp
441 where mp.organization_id = l_current_org_id;
442
443 exception
444 when no_data_found then
445 l_org_code := NULL;
446
447 end;
448
449
450 -- select the operating unit name for which the program is launched.
451
452 l_stmt_num := 40;
453
454 select hr.NAME
455 into l_org_name
456 from HR_ALL_ORGANIZATION_UNITS hr
457 where hr.ORGANIZATION_ID = l_current_org_id;
458
459 -- Open Ref Cursor to collect the report parameters
460
461 l_stmt_num := 50;
462
463 OPEN l_ref_cur FOR 'SELECT :l_org_code org_code,
464 :l_org_name org_name,
465 xla.NAME ledger_name,
466 xla.currency_code CUR_CODE,
467 :i_title TITLE_NAME,
468 crs.displayed_field sort_option,
469 :i_from_date from_date,
470 :i_to_date to_date,
471 :i_from_item from_item,
472 :i_to_item to_item,
473 :i_from_amount from_amount,
474 :i_to_amount to_amount,
475 :i_bal_segment_from bal_seg_from,
476 :i_bal_segment_to bal_seg_to,
477 decode(:l_age_option,
478 1,
479 ''Last Receipt Date'',
480 ''Last Activity Date'') age_option
481 FROM cst_reconciliation_codes crs,
482 XLA_GL_LEDGERS_V xla,
483 HR_ORGANIZATION_INFORMATION hoi
484 WHERE hoi.ORGANIZATION_ID = :l_current_org_id
485 and hoi.ORG_INFORMATION_CONTEXT = ''Operating Unit Information''
486 and xla.LEDGER_ID = hoi.ORG_INFORMATION3
487 AND crs.lookup_type = ''SRS ACCRUAL ORDER BY''
488 AND crs.LOOKUP_CODE = :i_sort_by'
489 USING l_org_code,
490 l_org_name,
491 i_title,
492 i_from_date ,
493 i_to_date ,
494 i_from_item ,
495 i_to_item ,
496 i_from_amount,
497 i_to_amount,
498 i_bal_segment_from,
499 i_bal_segment_to,
500 l_age_option,
501 l_current_org_id,
505
502 i_sort_by;
503
504 -- create new context
506 l_stmt_num := 60;
507
508 l_qryCtx := DBMS_XMLGEN.newContext (l_ref_cur);
509 DBMS_XMLGEN.setRowSetTag (l_qryCtx,'PARAMETERS');
510 DBMS_XMLGEN.setRowTag (l_qryCtx,NULL);
511
512 -- get XML into the temporary clob variable
513
514 l_stmt_num := 70;
515
516 DBMS_XMLGEN.getXML (l_qryCtx, l_xml_temp, DBMS_XMLGEN.none);
517
518 -- remove the header (21 characters) and append the rest to xml output
519
520 IF (DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx) > 0) THEN
521 DBMS_LOB.erase (l_xml_temp, l_offset,1);
522 DBMS_LOB.append (x_xml_doc, l_xml_temp);
523 END IF;
524
525 -- close context and free memory
526
527 DBMS_XMLGEN.closeContext(l_qryCtx);
528 CLOSE l_ref_cur;
529 DBMS_LOB.FREETEMPORARY (l_xml_temp);
530
531 -- Standard call to get message count and if count is 1, get message info.
532
533 FND_MSG_PUB.Count_And_Get
534 ( p_count => x_msg_count,
535 p_data => x_msg_data
536 );
537
538 -- Write the module name to fnd log file
539
540 IF (l_pLog) THEN
541 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
542 l_module || '.end',
543 '<<< ' || l_api_name);
544 END IF;
545
546 EXCEPTION
547 WHEN FND_API.G_EXC_ERROR THEN
548 x_return_status := FND_API.G_RET_STS_ERROR ;
549 FND_MSG_PUB.Count_And_Get
550 ( p_count => x_msg_count,
551 p_data => x_msg_data
552 );
553
554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
556 FND_MSG_PUB.Count_And_Get
557 ( p_count => x_msg_count,
558 p_data => x_msg_data
559 );
560
561 WHEN OTHERS THEN
562 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
563 IF (l_uLog) THEN
564 FND_LOG.STRING(FND_LOG.LEVEL_UNEXPECTED,
565 l_module || '.' || l_stmt_num,
566 SUBSTRB (SQLERRM , 1 , 240));
567 END IF;
568
569 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
570 THEN
571 FND_MSG_PUB.Add_Exc_Msg
572 ( G_PKG_NAME,
573 l_api_name
574 );
575 END IF;
576
577 FND_MSG_PUB.Count_And_Get
578 ( p_count => x_msg_count,
579 p_data => x_msg_data
580 );
581
582 END Add_Parameters;
583
584
585 PROCEDURE Add_MiscData
586 (p_api_version IN NUMBER,
587 p_init_msg_list IN VARCHAR2,
588 p_validation_level IN NUMBER,
589
590 x_return_status OUT NOCOPY VARCHAR2,
591 x_msg_count OUT NOCOPY NUMBER,
592 x_msg_data OUT NOCOPY VARCHAR2,
593
594 i_title IN VARCHAR2,
595 i_sort_by IN VARCHAR2,
596 i_from_date IN DATE,
597 i_to_date IN DATE,
598 i_from_item IN VARCHAR2,
599 i_to_item IN VARCHAR2,
600 i_from_amount IN NUMBER,
601 i_to_amount IN NUMBER,
602 i_bal_segment_from IN VARCHAR2,
603 i_bal_segment_to IN VARCHAR2,
604
605 x_xml_doc IN OUT NOCOPY CLOB)
606 IS
607
608 l_api_name CONSTANT VARCHAR2(3000) := 'MISC_REPORT_DATA';
609 l_api_version CONSTANT NUMBER := 1.0;
610 l_ref_cur SYS_REFCURSOR;
611 l_qryCtx NUMBER;
612 l_xml_temp CLOB;
613 l_offset PLS_INTEGER;
614 l_bal_segment VARCHAR2(50);
615 l_items_null VARCHAR2(1);
616 l_count NUMBER;
617 l_stmt_num NUMBER;
618 l_current_org_id NUMBER;
619 l_account_range NUMBER;
620 l_age_option NUMBER;
621
622 l_full_name CONSTANT VARCHAR2(3000) := G_PKG_NAME || '.' || l_api_name;
623 l_module CONSTANT VARCHAR2(3000) := 'cst.plsql.' || l_full_name;
624
625 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED,
626 l_module);
627 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
628 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
632
629 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
630
631 BEGIN
633 -- Write the module name to fnd log file
634
635 IF (l_pLog) THEN
636 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
637 l_module || '.begin',
638 '>>> ' || l_api_name);
639 END IF;
640
641 -- Standard call to check for call compatibility.
642
643 IF NOT FND_API.Compatible_API_Call ( l_api_version,
644 p_api_version,
645 l_api_name,
646 G_PKG_NAME )
647 THEN
648 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
649 END IF;
650
651 -- Initialize message list if p_init_msg_list is set to TRUE.
652
653 IF FND_API.to_Boolean( p_init_msg_list ) THEN
654 FND_MSG_PUB.initialize;
655 END IF;
656
657 -- Initialize API return status to success
658
659 x_return_status := FND_API.G_RET_STS_SUCCESS;
660
661 -- Initialize temporary variable to hold xml data
662
663 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
664 l_offset := 21;
665
666 l_stmt_num := 5;
667
668 -- Get the proile value to determine the aging basis
669
670 fnd_profile.get('CST_ACCRUAL_AGE_IN_DAYS', l_age_option);
671
672 -- select the operating unit for which the program is launched.
673
674 l_stmt_num := 10;
675
676 l_current_org_id := MO_GLOBAL.get_current_org_id;
677
678
679 -- Check if item range is given
680
681 l_stmt_num := 20;
682
683 IF ( (i_from_item IS NULL) AND (i_to_item IS NULL) ) THEN
684 l_items_null := 'Y';
685
686 ELSE
687
688 l_items_null := 'N';
689
690 END IF;
691
692 -- select the balancing segment value
693
694 l_stmt_num := 30;
695
696 SELECT fav.application_column_name
697 INTO l_bal_segment
698 FROM gl_sets_of_books gl,
699 fnd_segment_attribute_values fav,
700 hr_organization_information hr
701 WHERE hr.org_information_context = 'Operating Unit Information'
702 AND hr.organization_id = l_current_org_id
703 AND to_number(hr.org_information3) = gl.set_of_books_id
704 AND fav.segment_attribute_type = 'GL_BALANCING'
705 AND fav.attribute_value = 'Y'
706 AND fav.application_id = 101
707 AND fav.id_flex_code = 'GL#'
708 AND id_flex_num = gl.chart_of_accounts_id;
709
710
711 -- find if balancing segment range is given
712
713 IF ( (i_bal_segment_from IS NULL) AND (i_bal_segment_to IS NULL) ) THEN
714
715 l_account_range := 0;
716
717 ELSIF ( (i_bal_segment_from IS NOT NULL) AND (i_bal_segment_to IS NULL) ) THEN
718
719 l_account_range := 1;
720
721 ELSIF ( (i_bal_segment_from IS NULL) AND (i_bal_segment_to IS NOT NULL) ) THEN
722
723 l_account_range := 2;
724 ELSE
725
726 l_account_range := 3;
727 END IF;
728
729 -- open ref cur to fetch misc data
730
731 l_stmt_num := 40;
732
733 OPEN l_ref_cur FOR 'SELECT gcc.concatenated_segments account,
734 decode(cmr.invoice_distribution_id,
735 NULL,
736 decode ( cmr.transaction_type_code,
737 ''CONSIGNMENT'',
738 (SELECT crc.displayed_field
739 FROM cst_reconciliation_codes crc
740 WHERE crc.lookup_code =
741 cmr.transaction_type_code
742 AND crc.lookup_type IN
743 ( ''ACCRUAL WRITE-OFF ACTION'',''ACCRUAL TYPE'')),
744 (SELECT mtt.transaction_type_name
745 FROM mtl_transaction_types mtt
746 WHERE cmr.transaction_type_code =
747 to_char(mtt.transaction_type_id) )),
748 (SELECT crc.displayed_field
749 FROM cst_reconciliation_codes crc
750 WHERE crc.lookup_code =
751 cmr.transaction_type_code
752 AND crc.lookup_type IN
753 ( ''ACCRUAL WRITE-OFF ACTION'',''ACCRUAL TYPE''))) transaction_type,
754 decode(cmr.invoice_distribution_id,
755 NULL,
756 ''INV'',
757 ''AP'') transaction_source,
758 cmr.transaction_date transaction_date,
759 cmr.quantity quantity,
763 apia.invoice_num invoice_number,
760 cmr.amount amount,
761 cmr.entered_amount entered_amount,
762 cmr.currency_code currency_code,
764 aida.invoice_line_number invoice_line,
765 NVL(poh.CLM_DOCUMENT_NUMBER,poh.SEGMENT1) po_number,--Changed as a part of CLM
766 por.release_num po_release,
767 nvl(POL.LINE_NUM_DISPLAY, to_char(POL.LINE_NUM)) po_line,--Changed as a part of CLM
768 poll.shipment_num po_shipment,
769 pod.distribution_num po_distribution,
770 cmr.po_distribution_id po_distribution_id,
771 cmr.inventory_transaction_id inventory_transaction_id,
772 decode(cmr.inventory_item_id, null, null,
773 (select msi.concatenated_segments from
774 mtl_system_items_vl msi
775 where inventory_item_id = cmr.inventory_item_id
776 and rownum <2)
777 ) item,
778 decode(cmr.invoice_distribution_id,
779 NULL,
780 mmt.TRANSACTION_UOM,
781 pol.UNIT_MEAS_LOOKUP_CODE) uom,
782 trunc (decode (cmr.transaction_type_code,
783 ''CONSIGNMENT'', decode(cmr.po_distribution_id,
784 null, null,
785 decode ( :l_age_option, 1,
786 (sysdate - nvl( (select max(cmr2.transaction_date)
787 from cst_misc_reconciliation cmr2
788 where cmr2.po_distribution_id= cmr.po_distribution_id
789 and cmr2.inventory_transaction_id is not null
790 and cmr2.transaction_type_code = ''CONSIGNMENT''
791 ),
792 (select max(cmr2.transaction_date)
793 from cst_misc_reconciliation cmr2
794 where cmr2.po_distribution_id = cmr.po_distribution_id
795 and cmr2.inventory_transaction_id is null
796 and cmr2.transaction_type_code = ''CONSIGNMENT''
797 and cmr2.invoice_distribution_id is not null)
798 )),
799 (sysdate - greatest( nvl( (select max(cmr2.transaction_date)
800 from cst_misc_reconciliation cmr2
801 where cmr2.po_distribution_id= cmr.po_distribution_id
802 and cmr2.inventory_transaction_id is not null
803 and cmr2.transaction_type_code = ''CONSIGNMENT''),
804 (select max(cmr2.transaction_date)
805 from cst_misc_reconciliation cmr2
806 where cmr2.po_distribution_id = cmr.po_distribution_id
807 and cmr2.inventory_transaction_id is null
808 and cmr2.transaction_type_code = ''CONSIGNMENT''
809 and cmr2.invoice_distribution_id is not null)
810 ),
811 NVL((select max(cmr2.transaction_date)
812 from cst_misc_reconciliation cmr2
813 where cmr2.po_distribution_id = cmr.po_distribution_id
814 and cmr2.inventory_transaction_id is null
815 and cmr2.transaction_type_code = ''CONSIGNMENT''
816 and cmr2.invoice_distribution_id is not null),
817 (select max(cmr2.transaction_date)
818 from cst_misc_reconciliation cmr2
819 where cmr2.po_distribution_id= cmr.po_distribution_id
820 and cmr2.inventory_transaction_id is not null
821 and cmr2.transaction_type_code = ''CONSIGNMENT'')
822 )
823 )) -- age option 2
824 ) --po dist id not null, age option 1
825 ), --po dist_id null
826 null) --txn_type_code not consignment
827 ) age_in_days,
828 pov.vendor_name vendor,
829 mp.organization_code org
830 FROM cst_misc_reconciliation cmr,
831 ap_invoices_all apia,
832 ap_invoice_distributions_all aida,
833 po_vendors pov,
834 mtl_parameters mp,
835 gl_code_combinations_kfv gcc,
836 po_distributions_all pod,
837 po_line_locations_all poll,
841 mtl_material_transactions mmt
838 po_releases_all por,
839 po_lines_all pol,
840 po_headers_all poh,
842 WHERE cmr.invoice_distribution_id = aida.invoice_distribution_id(+)
843 AND aida.invoice_id = apia.invoice_id(+)
844 AND cmr.vendor_id = pov.vendor_id(+)
845 AND cmr.inventory_organization_id = mp.organization_id(+)
846 AND cmr.accrual_account_id = gcc.code_combination_id
847 AND pod.po_distribution_id(+) = cmr.po_distribution_id
848 and cmr.inventory_transaction_id = mmt.transaction_id (+)
849 AND poll.line_location_id(+) = pod.line_location_id
850 AND pod.po_release_id = por.po_release_id(+)
851 AND pol.po_line_id(+) = pod.po_line_id
852 AND poh.po_header_id(+) = pod.po_header_id
853 AND cmr.operating_unit_id = :l_current_org_id
854 AND cmr.transaction_date BETWEEN
855 nvl( :i_from_date ,cmr.transaction_date )
856 AND nvl(:i_to_date ,cmr.transaction_date)
857 AND cmr.amount BETWEEN nvl(:i_from_amount,cmr.amount)
858 AND nvl(:i_to_amount,cmr.amount)
859 AND (:l_items_null = ''Y''
860 OR (:l_items_null = ''N''
861 AND decode(cmr.inventory_item_id, null, null,
862 (select msi.concatenated_segments
863 from mtl_system_items_vl msi
864 where inventory_item_id = cmr.inventory_item_id
865 and rownum <2))
866 between nvl(:i_from_item, decode(cmr.inventory_item_id, null,
867 null,
868 (select msi.concatenated_segments
869 from mtl_system_items_vl msi
870 where inventory_item_id = cmr.inventory_item_id
871 and rownum <2)))
872 and nvl(:i_to_item ,decode(cmr.inventory_item_id, null, null,
873 (select msi.concatenated_segments
874 from mtl_system_items_vl msi
875 where inventory_item_id = cmr.inventory_item_id
876 and rownum <2)))
877 ))
878 AND (( :l_account_range = 0 )
879 OR ( :l_account_range = 1 AND
880 gcc.' || l_bal_segment || ' >= :i_bal_segment_from)
881 OR ( :l_account_range = 2 AND
882 gcc.' || l_bal_segment || ' <= :i_bal_segment_to)
883 OR ( :l_account_range = 3 AND
884 gcc.' || l_bal_segment || ' BETWEEN :i_bal_segment_from
885 AND :i_bal_segment_to ) )
886 ORDER BY decode( :i_sort_by ,
887 ''ITEM'', item,
888 ''AMOUNT'', decode(sign(amount),-1,
889 chr(0) || translate( to_char(abs(amount), ''000000000999.999''),
890 ''0123456789'', ''9876543210''), to_char(amount, ''000000000999.999'' ) ),
891 ''DATE'', to_char(transaction_date, ''yyyymmddhh24miss'')) '
892 USING l_age_option,
893 l_current_org_id,
894 i_from_date,
895 i_to_date,
896 i_from_amount,
897 i_to_amount,
898 l_items_null,
899 l_items_null,
900 i_from_item,
901 i_to_item,
902 l_account_range,
903 l_account_range,
904 i_bal_segment_from,
905 l_account_range,
906 i_bal_segment_to,
907 l_account_range,
908 i_bal_segment_from,
909 i_bal_segment_to,
910 i_sort_by;
911
912 -- create new context
913
914 l_stmt_num := 50;
915
916 l_qryCtx := DBMS_XMLGEN.newContext (l_ref_cur);
917 DBMS_XMLGEN.setRowSetTag (l_qryCtx,'MISC_DATA');
918 DBMS_XMLGEN.setRowTag (l_qryCtx,'MISC');
919
920 -- get XML into the temporary clob variable
921
922 l_stmt_num := 60;
923
924 DBMS_XMLGEN.getXML (l_qryCtx, l_xml_temp, DBMS_XMLGEN.none);
925
926 -- remove the header (21 characters) and append the rest to xml output
927
928 l_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
929
930 IF (DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx) > 0) THEN
931 DBMS_LOB.erase (l_xml_temp, l_offset,1);
932 DBMS_LOB.append (x_xml_doc, l_xml_temp);
933 END IF;
934
935
936 -- close context and free memory
937
938 DBMS_XMLGEN.closeContext(l_qryCtx);
939 CLOSE l_ref_cur;
940 DBMS_LOB.FREETEMPORARY (l_xml_temp);
941
942 -- to add number of rows processed
943
944 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
945
946 -- open ref cursor to get the number of rows processed
947
948 l_stmt_num := 70;
949
950 OPEN l_ref_cur FOR SELECT l_count l_count FROM dual ;
951
952 -- create new context
953
954 l_stmt_num := 80;
955
956 l_qryCtx := DBMS_XMLGEN.newContext (l_ref_cur);
957 DBMS_XMLGEN.setRowSetTag (l_qryCtx,'record_num');
958 DBMS_XMLGEN.setRowTag (l_qryCtx,NULL);
959
960 -- get XML to add the number of rows processed
961
962 l_stmt_num := 90;
963
964 DBMS_XMLGEN.getXML (l_qryCtx, l_xml_temp, DBMS_XMLGEN.none);
965
966 -- remove the header (21 characters) and append the rest to xml output
967
968 IF ( DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx) > 0 ) THEN
969 DBMS_LOB.erase (l_xml_temp, l_offset,1);
970 DBMS_LOB.append (x_xml_doc, l_xml_temp);
971 END IF;
972
973 -- close context and free memory
974
975 DBMS_XMLGEN.closeContext(l_qryCtx);
976 CLOSE l_ref_cur;
977 DBMS_LOB.FREETEMPORARY (l_xml_temp);
978
979
980 -- Standard call to get message count and if count is 1, get message info.
981
982 FND_MSG_PUB.Count_And_Get
983 ( p_count => x_msg_count,
984 p_data => x_msg_data
985 );
986
987 -- Write the module name to fnd log file
988
989 IF (l_pLog) THEN
990 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
991 l_module || '.end',
992 '<<< ' || l_api_name);
993 END IF;
994
995 EXCEPTION
996 WHEN FND_API.G_EXC_ERROR THEN
997 x_return_status := FND_API.G_RET_STS_ERROR ;
998 FND_MSG_PUB.Count_And_Get
999 ( p_count => x_msg_count,
1000 p_data => x_msg_data
1001 );
1002
1003 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1005 FND_MSG_PUB.Count_And_Get
1006 ( p_count => x_msg_count,
1007 p_data => x_msg_data);
1008
1009 WHEN OTHERS THEN
1010 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1011 IF (l_uLog) THEN
1012 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
1013 l_module || '.' || l_stmt_num,
1014 SUBSTRB (SQLERRM , 1 , 240));
1015 END IF;
1016
1017 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1018 THEN
1019 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
1020 END IF;
1021
1022 FND_MSG_PUB.Count_And_Get
1023 ( p_count => x_msg_count,
1024 p_data => x_msg_data
1025 );
1026
1027 END Add_MiscData;
1028
1029 END CST_MiscAccrualReport_PVT ;