[Home] [Help]
PACKAGE BODY: APPS.CST_ACCRUALWRITEOFFREPORT_PVT
Source
1 PACKAGE BODY CST_AccrualWriteOffReport_PVT AS
2 /* $Header: CSTVAWOB.pls 120.21.12010000.2 2008/10/30 14:03:07 svelumur ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_AccrualWriteOffReport_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_WriteOffReportXml (
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_from_write_off_date IN VARCHAR2,
18 p_to_write_off_date IN VARCHAR2,
19 p_from_amount IN NUMBER,
20 p_to_amount IN NUMBER,
21 p_reason IN NUMBER,
22 p_comments IN VARCHAR2,
23 p_sort_by IN VARCHAR2 )
24 IS
25
26 l_api_name CONSTANT VARCHAR2(100) := 'Generate_WriteOffReportXml';
27 l_api_version CONSTANT NUMBER := 1.0;
28 l_qryCtx NUMBER;
29 l_ref_cur SYS_REFCURSOR;
30 l_xml_doc CLOB;
31 l_amount NUMBER ;
32 l_offset NUMBER ;
33 l_buffer VARCHAR2(32767);
34 l_length NUMBER;
35 l_from_write_off_date DATE;
36 l_to_write_off_date DATE;
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_current_org_id NUMBER;
44 l_error_message VARCHAR2(300);
45
46 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
47 l_module CONSTANT VARCHAR2(2000) := '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, 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 7305146*/
57 l_encoding VARCHAR2(20);
58 l_xml_header VARCHAR2(100);
59 BEGIN
60
61 -- Initialze variables
62 l_amount := 16383;
63 l_offset := 1;
64 l_return_status := fnd_api.g_ret_sts_success;
65 l_msg_count := 0;
66
67 -- select the operating unit for which the program is launched.
68
69 l_stmt_num := 5;
70
71 l_current_org_id := MO_GLOBAL.get_current_org_id;
72
73 -- Write the module name and user parameters to fnd log file
74
75 IF (l_pLog) THEN
76 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
77 l_module || '.begin',
78 '>>> ' || l_api_name || ':Parameters:
79 Org id:'|| l_current_org_id
80 || 'Title: ' || p_title
81 || 'Sort Option: ' || p_sort_by
82 || ' From Date: ' || p_from_write_off_date
83 || ' To Date: ' || p_to_write_off_date
84 || ' Reason: ' || p_reason
85 || ' Comments: ' || p_comments
86 || ' Min Amount: ' || p_from_amount
87 || ' Max Amount: ' || p_to_amount
88 || ' Balancing Segment From: ' || p_bal_segment_from
89 || ' Balancing Segment To: ' || p_bal_segment_to);
90
91 END IF;
92
93
94 l_stmt_num := 10;
95
96 /* check if to_date is greater than or equal to from_date */
97
98 If (p_from_write_off_date is not null and p_to_write_off_date < p_from_write_off_date ) then
99
100 l_error_message := 'CST_INVALID_TO_DATE';
101 fnd_message.set_name('BOM','CST_INVALID_TO_DATE');
102 RAISE fnd_api.g_exc_error;
103 End If;
104
105 l_stmt_num := 20;
106
107 /* check if to_amount is greater than or equal to from_amount */
108
109 If (p_from_amount is not null and p_to_amount < p_from_amount ) then
110
111 l_error_message := 'CST_INVALID_TO_AMOUNT';
112 fnd_message.set_name('BOM','CST_INVALID_TO_AMOUNT');
113 RAISE fnd_api.g_exc_error;
114 End If;
115
116 -- Initialze variables for storing XML Data
117
118 DBMS_LOB.createtemporary(l_xml_doc, TRUE);
119
120 /*Bug 7305146*/
121 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
122 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
123 DBMS_LOB.writeappend (l_xml_doc, length(l_xml_header), l_xml_header);
124
125 DBMS_LOB.writeappend (l_xml_doc, 8, '<REPORT>');
126
127 -- convert from date parameter to date type variable
128
129 l_stmt_num := 30;
130
131 IF (p_from_write_off_date IS NOT NULL) THEN
132 l_from_write_off_date := FND_DATE.canonical_to_date(p_from_write_off_date);
133 ELSE
134 l_from_write_off_date := NULL;
135 END IF;
136
137 -- convert to date parameter to date type variable
138
139 l_stmt_num := 40;
140
141 IF (p_to_write_off_date IS NOT NULL) THEN
142 l_to_write_off_date := FND_DATE.canonical_to_date(p_to_write_off_date );
143 ELSE
144 l_to_write_off_date := NULL;
145 END IF;
146
147
148 -- Initialize message stack
149
150 FND_MSG_PUB.initialize;
151
152 -- Standard call to get message count and if count is 1, get message info.
153
154 FND_MSG_PUB.Count_And_Get
155 ( p_count => l_msg_count,
156 p_data => l_msg_data
157 );
158
159 /*========================================================================*/
160 -- Call to Procedure Add Parameters. To Add user entered Parameters to
161 -- XML data
162 /*========================================================================*/
163
164 l_stmt_num := 50;
165
166 Add_Parameters (p_api_version => l_api_version,
167 p_init_msg_list => FND_API.G_FALSE,
168 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
169 x_return_status => l_return_status,
170 x_msg_count => l_msg_count,
171 x_msg_data => l_msg_data,
172 i_title => p_title,
173 i_from_write_off_date => l_from_write_off_date,
174 i_to_write_off_date => l_to_write_off_date,
175 i_reason => p_reason,
176 i_comments => p_comments ,
177 i_from_amount => p_from_amount,
178 i_to_amount => p_to_amount,
179 i_sort_by => p_sort_by,
180 i_bal_segment_from => p_bal_segment_from ,
181 i_bal_segment_to => p_bal_segment_to ,
182 x_xml_doc => l_xml_doc);
183
184 -- Standard call to check the return status from API called
185
186 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
188 END IF;
189
190 /*========================================================================*/
191 -- Call to Procedure Add Parameters. To add write off data to XML data
192 /*========================================================================*/
193
194 l_stmt_num := 60;
195
196 Add_WriteOffData (p_api_version => l_api_version,
197 p_init_msg_list => FND_API.G_FALSE,
198 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
199 x_return_status => l_return_status,
200 x_msg_count => l_msg_count,
201 x_msg_data => l_msg_data,
202 i_title => p_title,
203 i_from_write_off_date => l_from_write_off_date,
204 i_to_write_off_date => l_to_write_off_date,
205 i_reason => p_reason,
206 i_comments => p_comments ,
207 i_from_amount => p_from_amount,
208 i_to_amount => p_to_amount,
209 i_sort_by => p_sort_by,
210 i_bal_segment_from => p_bal_segment_from ,
211 i_bal_segment_to => p_bal_segment_to ,
212 x_xml_doc => l_xml_doc);
213
214 -- Standard call to check the return status from API called
215
216 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
217 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
218 END IF;
219
220 -- write the closing tag to the XML data
221
222 DBMS_LOB.writeappend (l_xml_doc, 9, '</REPORT>');
223
224 -- write xml data to the output file
225
226 l_length := nvl(dbms_lob.getlength(l_xml_doc),0);
227 LOOP
228 EXIT WHEN l_length <= 0;
229 dbms_lob.read (l_xml_doc, l_amount, l_offset, l_buffer);
230 FND_FILE.PUT (FND_FILE.OUTPUT, l_buffer);
231 l_length := l_length - l_amount;
232 l_offset := l_offset + l_amount;
233 END LOOP;
234
235 DBMS_XMLGEN.closeContext(l_qryCtx);
236
237 -- Write the event log to fnd log file
238
239 IF (l_eventLog) THEN
240 FND_LOG.STRING (FND_LOG.LEVEL_EVENT,
241 l_module || '.' || l_stmt_num,
242 'Completed writing to output file');
243 END IF;
244
245 -- free temporary memory
246 DBMS_LOB.FREETEMPORARY (l_xml_doc);
247
248 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'Request Completed Successfully');
249
250 -- Write the module name to fnd log file
251
252 IF (l_pLog) THEN
253 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
254 l_module || '.end',
255 '<<< ' || l_api_name);
256 END IF;
257
258 EXCEPTION
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 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
296 FND_MSG_PUB.Count_And_Get
297 ( p_count => l_msg_count,
298 p_data => l_msg_data
299 );
300
301 CST_UTILITY_PUB.writelogmessages
302 ( p_api_version => l_api_version,
303 p_msg_count => l_msg_count,
304 p_msg_data => l_msg_data,
305 x_return_status => l_return_status);
306
307 l_msg_data := SUBSTRB (SQLERRM,1,240);
308 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
309
310 WHEN OTHERS THEN
311 IF (l_uLog) THEN
312 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
313 l_module || '.' || l_stmt_num,
314 SUBSTRB (SQLERRM , 1 , 240));
315 END IF;
316
317 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
318 THEN
319 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME,l_api_name);
320 END IF;
321
322 FND_MSG_PUB.Count_And_Get
323 ( p_count => l_msg_count,
324 p_data => l_msg_data
325 );
326
327 CST_UTILITY_PUB.writelogmessages
328 ( p_api_version => 1.0,
329 p_msg_count => l_msg_count,
330 p_msg_data => l_msg_data,
331 x_return_status => l_return_status);
332
333 l_msg_data := SUBSTRB (SQLERRM,1,240);
334 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
335
336 END Generate_WriteOffReportXml;
337
338 PROCEDURE Add_Parameters
339 (p_api_version IN NUMBER,
340 p_init_msg_list IN VARCHAR2 ,
341 p_validation_level IN NUMBER,
342
343 x_return_status OUT NOCOPY VARCHAR2,
344 x_msg_count OUT NOCOPY NUMBER,
345 x_msg_data OUT NOCOPY VARCHAR2,
346
347 i_title IN VARCHAR2,
348 i_from_write_off_date IN DATE,
349 i_to_write_off_date IN DATE,
350 i_reason IN NUMBER,
351 i_comments IN VARCHAR2,
352 i_from_amount IN NUMBER,
353 i_to_amount IN NUMBER,
354 i_sort_by IN VARCHAR2,
355 i_bal_segment_from IN VARCHAR2 ,
356 i_bal_segment_to IN VARCHAR2 ,
357
358 x_xml_doc IN OUT NOCOPY CLOB)
359 IS
360
361 l_api_name CONSTANT VARCHAR2(30) := 'ADD_PARAMETERS';
362 l_api_version CONSTANT NUMBER := 1.0;
363
364 l_ref_cur SYS_REFCURSOR;
365 l_qryCtx NUMBER;
366 l_xml_temp CLOB;
367 l_offset PLS_INTEGER;
368 l_org_code VARCHAR2(300);
369 l_org_name VARCHAR2(300);
370 l_reason VARCHAR2(2000);
371 l_stmt_num NUMBER;
372 l_current_org_id NUMBER;
373
374 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
375 l_module CONSTANT VARCHAR2(2000) := '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, l_module);
378 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
379 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
380 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
381
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 -- select the operating unit for which the program is launched.
419
420 l_stmt_num := 10;
421
422 l_current_org_id := MO_GLOBAL.get_current_org_id;
423
424 -- select the reason name for the user entered user id
425
426 l_stmt_num := 20;
427
428 IF (i_reason IS NULL) THEN
429 l_reason := NULL;
430 ELSE
431 SELECT mtr.reason_name
432 INTO l_reason
433 FROM mtl_transaction_reasons mtr
434 WHERE mtr.reason_id = i_reason;
435 END IF;
436 -- select the operating unit code for which the program is launched.
437
438 l_stmt_num := 30;
439
440 begin
441 select mp.organization_code
442 into l_org_code
443 from mtl_parameters mp
444 where mp.organization_id = l_current_org_id;
445
446 exception
447 when no_data_found then
448 l_org_code := NULL;
449
450 end;
451
452 -- select the operating unit name for which the program is launched.
453
454 l_stmt_num := 40;
455
456 select hr.NAME
457 into l_org_name
458 from HR_ALL_ORGANIZATION_UNITS hr
459 where hr.ORGANIZATION_ID = l_current_org_id;
460
461 -- Open Ref Cursor to collect the report parameters
462
463 l_stmt_num := 50;
464
465 OPEN l_ref_cur FOR 'SELECT :l_org_code org_code,
466 :l_org_name org_name,
467 xla.NAME ledger_name,
468 xla.currency_code CUR_CODE,
469 :i_title TITLE_NAME,
470 :i_from_write_off_date from_write_date,
471 :i_to_write_off_date to_write_date,
472 :l_reason reason_name,
473 decode(:i_comments, ''N'', ''No'',
474 ''Yes'') comments,
475 :i_from_amount min_amount,
476 :i_to_amount max_amount,
477 crs.displayed_field sort_option,
478 :i_bal_segment_from bal_seg_from,
479 :i_bal_segment_to bal_seg_to
480 FROM cst_reconciliation_codes crs,
481 XLA_GL_LEDGERS_V xla,
482 HR_ORGANIZATION_INFORMATION hoi
483 WHERE hoi.ORGANIZATION_ID = :l_current_org_id
484 and hoi.ORG_INFORMATION_CONTEXT = ''Operating Unit Information''
485 and xla.LEDGER_ID = hoi.ORG_INFORMATION3
486 AND crs.lookup_type = ''SRS ACCRUAL ORDER BY''
487 AND crs.LOOKUP_CODE = :i_sort_by'
488 USING l_org_code,
489 l_org_name,
490 i_title,
491 i_from_write_off_date ,
492 i_to_write_off_date ,
493 l_reason,
494 i_comments ,
495 i_from_amount ,
496 i_to_amount ,
497 i_bal_segment_from,
498 i_bal_segment_to,
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 PROCEDURE Add_WriteOffData
583 (p_api_version IN NUMBER,
584 p_init_msg_list IN VARCHAR2 ,
585 p_validation_level IN NUMBER ,
586
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2,
590
591 i_title IN VARCHAR2,
592 i_from_write_off_date IN DATE,
593 i_to_write_off_date IN DATE,
594 i_reason IN NUMBER,
595 i_comments IN VARCHAR2,
596 i_from_amount IN NUMBER,
597 i_to_amount IN NUMBER,
598 i_sort_by IN VARCHAR2,
599 i_bal_segment_from IN VARCHAR2 ,
600 i_bal_segment_to IN VARCHAR2 ,
601
602 x_xml_doc IN OUT NOCOPY CLOB)
603 IS
604
605 l_api_name CONSTANT VARCHAR2(30) := 'WRITE_OFF_DATA';
606 l_api_version CONSTANT NUMBER := 1.0;
607
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_count NUMBER;
614 l_stmt_num NUMBER;
615 l_current_org_id NUMBER;
616 l_account_range NUMBER;
617 l_currency VARCHAR2(50);
618
619 l_full_name CONSTANT VARCHAR2(2000) := G_PKG_NAME || '.' || l_api_name;
620 l_module CONSTANT VARCHAR2(2000) := 'cst.plsql.' || l_full_name;
621
622 l_uLog CONSTANT BOOLEAN := FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL AND FND_LOG.TEST (FND_LOG.LEVEL_UNEXPECTED, l_module);
623 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
624 l_eventLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
625 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
626
627 BEGIN
628
629 IF (l_pLog) THEN
630 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
631 l_module || '.begin',
632 '>>> ' || l_api_name);
633 END IF;
634
635 -- Standard call to check for call compatibility.
636
637 IF NOT FND_API.Compatible_API_Call ( l_api_version,
638 p_api_version,
639 l_api_name,
640 G_PKG_NAME )
641 THEN
642 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
643 END IF;
644
645 -- Initialize message list if p_init_msg_list is set to TRUE.
646
647 IF FND_API.to_Boolean( p_init_msg_list ) THEN
648 FND_MSG_PUB.initialize;
649 END IF;
650
651 -- Initialize API return status to success
652
653 x_return_status := FND_API.G_RET_STS_SUCCESS;
654
655 -- Initialize temporary variable to hold xml data
656
657 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
658 l_offset := 21;
659
660 -- select the operating unit for which the program is launched.
661
662 l_stmt_num := 10;
663
664 l_current_org_id := MO_GLOBAL.get_current_org_id;
665
666 -- select the balancing segment value
667
668 l_stmt_num := 20;
669
670 SELECT fav.application_column_name
671 INTO l_bal_segment
672 FROM gl_sets_of_books gl,
673 fnd_segment_attribute_values fav,
674 hr_organization_information hr
675 WHERE hr.org_information_context = 'Operating Unit Information'
676 AND hr.organization_id = l_current_org_id
677 AND to_number(hr.org_information3) = gl.set_of_books_id
678 AND fav.segment_attribute_type = 'GL_BALANCING'
679 AND fav.attribute_value = 'Y'
680 AND fav.application_id = 101
681 AND fav.id_flex_code = 'GL#'
682 AND id_flex_num = gl.chart_of_accounts_id;
683
684 -- find if balancing segment range is given
685
686 IF ( (i_bal_segment_from IS NULL) AND (i_bal_segment_to IS NULL) ) THEN
687
688 l_account_range := 0;
689
690 ELSIF ( (i_bal_segment_from IS NOT NULL) AND (i_bal_segment_to IS NULL) ) THEN
691
692 l_account_range := 1;
693
694 ELSIF ( (i_bal_segment_from IS NULL) AND (i_bal_segment_to IS NOT NULL) ) THEN
695
696 l_account_range := 2;
697 ELSE
698
699 l_account_range := 3;
700 END IF;
701
702 -- select the currency code
703
704 select xla.currency_code
705 into l_currency
706 from XLA_GL_LEDGERS_V xla,
707 HR_ORGANIZATION_INFORMATION hoi
708 where hoi.ORGANIZATION_ID = l_current_org_id
709 and hoi.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
710 and xla.LEDGER_ID = hoi.ORG_INFORMATION3;
711
712 -- open ref cur to fetch write off data
713
714 l_stmt_num := 30;
715
716 OPEN l_ref_cur FOR 'SELECT gcc.concatenated_segments account,
717 cwo.write_off_id write_off_id,
718 cwo.write_off_amount write_off_amount,
719 cwo.transaction_date write_off_date,
720 (SELECT gcc2.concatenated_segments
721 FROM gl_code_combinations_kfv gcc2,
722 cst_write_offs cwo2
723 WHERE cwo2.offset_account_id =
724 gcc2.code_combination_id
725 AND cwo2.accrual_account_id =
726 cwo.accrual_account_id
727 AND cwo2.write_off_id =
728 cwo.write_off_id
729 AND cwo2.offset_account_id =
730 cwo.offset_account_id) offset_account,
731 decode ( cwo.transaction_type_code,
732 ''REVERSE WRITE OFF'',
733 cwo.reversal_id,
734 NULL) reversal_id,
735 decode(cwo.inventory_item_id, null, null,
736 (select msi.concatenated_segments from
737 mtl_system_items_vl msi
738 where inventory_item_id = cwo.inventory_item_id
739 and rownum <2)
740 ) item,
741 decode (cwod.write_off_transaction_id,
742 NULL,
743 decode(cwod.inventory_transaction_id,
744 NULL,
745 decode( cwod.invoice_distribution_id,
746 NULL,
747 pol.UNIT_MEAS_LOOKUP_CODE,
748 pol.UNIT_MEAS_LOOKUP_CODE),
749 mmt.TRANSACTION_UOM),
750 null) uom,
751 pdt.displayed_field destination,
752 pov.vendor_name vendor,
753 mtr.reason_name reason,
754 :l_currency l_currency,
755 decode( :i_comments,
756 ''Y'',
757 cwo.comments,
758 NULL) comments,
759 poh.segment1 po_number,
760 por.release_num po_release,
761 pol.line_num po_line,
762 poll.shipment_num po_shipment,
763 pod.distribution_num po_distribution,
764 cwo.po_distribution_id po_distribution_id,
765 decode (cwod.write_off_transaction_id,
766 NULL,
767 decode(cwod.inventory_transaction_id,
768 NULL,
769 decode( cwod.invoice_distribution_id,
770 NULL,
771 ''PO'',
772 ''AP''),
773 ''INV''),
774 ''WO'') transaction_source,
775 decode( cwod.inventory_transaction_id,
776 NULL,
777 (SELECT crc2.displayed_field
778 FROM cst_reconciliation_codes crc2
779 WHERE to_char(crc2.lookup_code) =
780 to_char(cwod.transaction_type_code)
781 AND crc2.lookup_type IN
782 ( ''RCV TRANSACTION TYPE'',
783 ''ACCRUAL WRITE-OFF ACTION'',
784 ''ACCRUAL TYPE'') ) ,
785 (SELECT mtt.transaction_type_name
786 FROM mtl_transaction_types mtt
787 WHERE to_char(mtt.transaction_type_id) =
788 to_char(cwod.transaction_type_code)
789 )) transaction_type,
790 cwod.transaction_date transaction_date,
791 cwod.quantity quantity,
792 decode ( cwo.transaction_type_code,
793 ''REVERSE WRITE OFF'',
794 cwod.amount,
795 (-1*cwod.amount)) abs_amount,
796 cwod.amount amount,
797 cwod.entered_amount entered_amount,
798 cwod.currency_code currency_code,
799 apia.invoice_num invoice_number,
800 aida.distribution_line_number invoice_line,
801 rsh.receipt_num receipt_number,
802 cwod.inventory_transaction_id inventory_transaction_id,
803 cwod.write_off_transaction_id write_off_trans_id,
804 mp.organization_code org
805 FROM cst_write_offs cwo,
806 po_vendors pov,
807 mtl_transaction_reasons mtr,
808 po_headers_all poh,
809 po_lines_all pol,
810 po_releases_all por,
811 po_line_locations_all poll,
812 po_distributions_all pod,
813 cst_write_off_details cwod,
814 ap_invoices_all apia,
815 ap_invoice_distributions_all aida,
816 rcv_transactions rct,
817 rcv_shipment_headers rsh,
818 mtl_parameters mp,
819 po_destination_types_all_v pdt,
820 gl_code_combinations_kfv gcc,
821 mtl_material_transactions mmt
822 WHERE cwo.write_off_id = cwod.write_off_id
823 AND pov.vendor_id(+) = cwo.vendor_id
824 AND mtr.reason_id(+) = cwo.reason_id
825 AND pod.po_distribution_id(+) = cwo.po_distribution_id
826 AND poll.line_location_id(+) = pod.line_location_id
827 AND pol.po_line_id(+) = pod.po_line_id
828 AND por.po_release_id(+) = pod.po_release_id
829 AND poh.po_header_id(+) = pod.po_header_id
830 AND cwod.invoice_distribution_id = aida.invoice_distribution_id (+)
831 AND apia.invoice_id(+) = aida.invoice_distribution_id
832 AND cwod.rcv_transaction_id = rct.transaction_id(+)
833 AND rsh.shipment_header_id(+) = rct.shipment_header_id
834 AND pdt.lookup_code(+) = cwo.destination_type_code
835 AND cwod.inventory_organization_id = mp.organization_id(+)
836 and cwod.inventory_transaction_id = mmt.transaction_id (+)
837 AND cwo.accrual_account_id = gcc.code_combination_id
838 AND cwo.operating_unit_id = :l_current_org_id
839 AND cwod.operating_unit_id = :l_current_org_id
840 AND cwo.WRITE_OFF_AMOUNT
841 BETWEEN nvl(:i_from_amount,cwo.WRITE_OFF_AMOUNT)
842 AND nvl(:i_to_amount,cwo.WRITE_OFF_AMOUNT)
843 AND cwo.transaction_date
844 BETWEEN nvl( :i_from_write_off_date,cwo.transaction_date )
845 AND nvl(:i_to_write_off_date ,cwo.transaction_date )
846 AND nvl(:i_reason ,nvl(cwo.reason_id,-1)) = nvl(cwo.reason_id,-1)
847 AND (( :l_account_range = 0 )
848 OR ( :l_account_range = 1 AND
849 gcc.' || l_bal_segment || ' >= :i_bal_segment_from)
850 OR ( :l_account_range = 2 AND
851 gcc.' || l_bal_segment || ' <= :i_bal_segment_to)
852 OR ( :l_account_range = 3 AND
853 gcc.' || l_bal_segment || ' BETWEEN :i_bal_segment_from
854 AND :i_bal_segment_to ) )
855 ORDER BY decode( :i_sort_by ,
856 ''REASON'',mtr.reason_name,
857 ''AMOUNT'', decode(sign(write_off_amount),-1,
858 chr(0) || translate( to_char(abs(write_off_amount), ''000000000999.999''),
859 ''0123456789'', ''9876543210''), to_char(write_off_amount, ''000000000999.999'' ) ),
860 ''OFFSET ACCOUNT'', cwo.offset_account_id,
861 ''DATE'', to_char(transaction_date, ''yyyymmddhh24miss'')) '
862 USING l_currency,
863 i_comments,
864 l_current_org_id,
865 l_current_org_id,
866 i_from_amount,
867 i_to_amount,
868 i_from_write_off_date,
869 i_to_write_off_date,
870 i_reason,
871 l_account_range,
872 l_account_range,
873 i_bal_segment_from,
874 l_account_range,
875 i_bal_segment_to,
876 l_account_range,
877 i_bal_segment_from,
878 i_bal_segment_to,
879 i_sort_by;
880
881 -- create new context
882
883 l_stmt_num := 40;
884
885 l_qryCtx := DBMS_XMLGEN.newContext (l_ref_cur);
886 DBMS_XMLGEN.setRowSetTag (l_qryCtx,'WRITE_OFF_DATA');
887 DBMS_XMLGEN.setRowTag (l_qryCtx,'WRITE_OFF');
888
889 -- get XML into the temporary clob variable
890
891 l_stmt_num := 50;
892
893 DBMS_XMLGEN.getXML (l_qryCtx, l_xml_temp, DBMS_XMLGEN.none);
894
895 -- remove the header (21 characters) and append the rest to xml output
896
897 l_count := DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx);
898
899 IF (DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx) > 0) THEN
900 DBMS_LOB.erase (l_xml_temp, l_offset,1);
901 DBMS_LOB.append (x_xml_doc, l_xml_temp);
902 END IF;
903
904 -- close context and free memory
905
906 DBMS_XMLGEN.closeContext(l_qryCtx);
907 CLOSE l_ref_cur;
908 DBMS_LOB.FREETEMPORARY (l_xml_temp);
909
910 -- to add number of rows processed
911
912 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
913
914 -- open ref cursor to add number of rows processed
915
916 l_stmt_num := 60;
917
918 OPEN l_ref_cur FOR SELECT l_count l_count FROM dual ;
919
920 -- create new context
921
922 l_stmt_num := 70;
923
924 l_qryCtx := DBMS_XMLGEN.newContext (l_ref_cur);
925 DBMS_XMLGEN.setRowSetTag (l_qryCtx,'RECORD_NUM');
926 DBMS_XMLGEN.setRowTag (l_qryCtx,NULL);
927
928
929 -- get XML to add the number of rows processed
930
931 l_stmt_num := 80;
932
933 DBMS_XMLGEN.getXML (l_qryCtx, l_xml_temp, DBMS_XMLGEN.none);
934
935 -- remove the header (21 characters) and append the rest to xml output
936
937 IF ( DBMS_XMLGEN.getNumRowsProcessed(l_qryCtx) > 0 ) THEN
938 DBMS_LOB.erase (l_xml_temp, l_offset,1);
939 DBMS_LOB.append (x_xml_doc, l_xml_temp);
940 END IF;
941
942 -- close context and free memory
943
944 DBMS_XMLGEN.closeContext(l_qryCtx);
945 CLOSE l_ref_cur;
946 DBMS_LOB.FREETEMPORARY (l_xml_temp);
947
948 -- Standard call to get message count and if count is 1, get message info.
949
950 FND_MSG_PUB.Count_And_Get
951 ( p_count => x_msg_count,
952 p_data => x_msg_data
953 );
954
955 -- Write the module name to fnd log file
956
957 IF (l_pLog) THEN
958 FND_LOG.STRING (FND_LOG.LEVEL_PROCEDURE,
959 l_module || '.end',
960 '<<< ' || l_api_name);
961 END IF;
962
963 EXCEPTION
964 WHEN FND_API.G_EXC_ERROR THEN
965 x_return_status := FND_API.G_RET_STS_ERROR ;
966 FND_MSG_PUB.Count_And_Get
967 ( p_count => x_msg_count,
968 p_data => x_msg_data
969 );
970
971 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
973 FND_MSG_PUB.Count_And_Get
974 ( p_count => x_msg_count,
975 p_data => x_msg_data);
976
977 WHEN OTHERS THEN
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
979 IF (l_uLog) THEN
980 FND_LOG.STRING (FND_LOG.LEVEL_UNEXPECTED,
981 l_module || '.' || l_stmt_num,
982 SUBSTRB (SQLERRM , 1 , 240));
983 END IF;
984
985 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
986 THEN
987 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
988 END IF;
989
990 FND_MSG_PUB.Count_And_Get
991 ( p_count => x_msg_count,
992 p_data => x_msg_data
993 );
994
995 END Add_WriteOffData;
996
997 END CST_AccrualWriteOffReport_PVT ;