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