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