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