[Home] [Help]
PACKAGE BODY: APPS.CST_UNINVOICEDRECEIPTS_PVT
Source
1 PACKAGE BODY CST_UninvoicedReceipts_PVT AS
2 /* $Header: CSTVURRB.pls 120.7.12010000.2 2008/08/08 12:34:39 smsasidh ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CST_UninvoicedReceipts_PVT';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 G_GL_APPLICATION_ID CONSTANT NUMBER := 101;
8 G_PO_APPLICATION_ID CONSTANT NUMBER := 201;
9
10 -----------------------------------------------------------------------------
11 -- PROCEDURE : Start_Process
12 -- DESCRIPTION : Starting point for Uninvoiced Receipt Report
13 -----------------------------------------------------------------------------
14 PROCEDURE Start_Process
15 (
16 errbuf OUT NOCOPY VARCHAR2,
17 retcode OUT NOCOPY NUMBER,
18
19 p_title IN VARCHAR2,
20 p_accrued_receipts IN VARCHAR2,
21 p_inc_online_accruals IN VARCHAR2,
22 p_inc_closed_pos IN VARCHAR2,
23 p_struct_num IN NUMBER,
24 p_category_from IN VARCHAR2,
25 p_category_to IN VARCHAR2,
26 p_min_accrual_amount IN NUMBER,
27 p_period_name IN VARCHAR2,
28 p_vendor_from IN VARCHAR2,
29 p_vendor_to IN VARCHAR2,
30 p_orderby IN NUMBER,
31 p_qty_precision IN NUMBER
32 )
33
34 IS
35 l_api_name CONSTANT VARCHAR2(30) :='Start_Process';
36 l_api_version CONSTANT NUMBER := 1.0;
37 l_return_status VARCHAR2(1);
38
39 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
40 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
41
42 /* Log Severities*/
43 /* 6- UNEXPECTED */
44 /* 5- ERROR */
45 /* 4- EXCEPTION */
46 /* 3- EVENT */
47 /* 2- PROCEDURE */
48 /* 1- STATEMENT */
49
50 /* In general, we should use the following:
51 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
52 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
53 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
54 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
55 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
56 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
57 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
58 */
59
60 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
61 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
62 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
63 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
64
65 l_msg_count NUMBER;
66 l_msg_data VARCHAR2(240);
67
68 l_header_ref_cur SYS_REFCURSOR;
69 l_body_ref_cur SYS_REFCURSOR;
70 l_row_tag VARCHAR2(100);
71 l_row_set_tag VARCHAR2(100);
72 l_xml_header CLOB;
73 l_xml_body CLOB;
74 l_xml_report CLOB;
75
76 l_conc_status BOOLEAN;
77 l_return BOOLEAN;
78 l_status VARCHAR2(1);
79 l_industry VARCHAR2(1);
80 l_schema VARCHAR2(30);
81 l_application_id NUMBER;
82 l_legal_entity NUMBER;
83 l_end_date DATE;
84 l_sob_id NUMBER;
85 l_order_by VARCHAR2(15);
86 l_multi_org_flag VARCHAR2(1);
87
88 l_stmt_num NUMBER;
89 l_row_count NUMBER;
90
91 BEGIN
92
93 l_stmt_num := 0;
94 -- Procedure level log message for Entry point
95 IF (l_pLog) THEN
96 FND_LOG.STRING(
97 FND_LOG.LEVEL_PROCEDURE,
98 l_module || '.begin',
99 'Start_Process <<' ||
100 'p_title = ' || p_title ||','||
101 'p_accrued_receipts = ' || p_accrued_receipts ||','||
102 'p_inc_online_accruals = ' || p_inc_online_accruals ||','||
103 'p_inc_closed_pos = ' || p_inc_closed_pos ||','||
104 'p_struct_num = ' || p_struct_num ||','||
105 'p_category_from = ' || p_category_from ||','||
106 'p_category_to = ' || p_category_to ||','||
107 'p_min_accrual_amount = ' || p_min_accrual_amount ||','||
108 'p_period_name = ' || p_period_name ||','||
109 'p_vendor_from = ' || p_vendor_from ||','||
110 'p_vendor_to = ' || p_vendor_to ||','||
111 'p_orderby = ' || p_orderby ||','||
112 'p_qty_precision = ' || p_qty_precision
113 );
114 END IF;
115
116 -- Initialize message list if p_init_msg_list is set to TRUE.
117 FND_MSG_PUB.initialize;
118
119 -- Initialize API return status to success
120 l_return_status := FND_API.G_RET_STS_SUCCESS;
121
122 -- Check whether GL is installed
123 l_stmt_num := 10;
124 l_return := FND_INSTALLATION.GET_APP_INFO (
125 'SQLGL',
126 l_status,
127 l_industry,
128 l_schema
129 );
130
131 IF (l_status = 'I') THEN
132 l_application_id := G_GL_APPLICATION_ID;
133 ELSE
134 l_application_id := G_PO_APPLICATION_ID;
135 END IF;
136
137 -- Convert Accrual Cutoff date from Legal entity timezone to
138 -- Server timezone
139 l_stmt_num := 20;
140
141 SELECT set_of_books_id
142 INTO l_sob_id
143 FROM financials_system_parameters;
144
145 SELECT TO_NUMBER(org_information2)
146 INTO l_legal_entity
147 FROM hr_organization_information
148 WHERE organization_id = MO_GLOBAL.GET_CURRENT_ORG_ID
149 AND org_information_context = 'Operating Unit Information';
150
151 l_stmt_num := 30;
152 SELECT INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE (gps.end_date,
153 l_legal_entity)
154 INTO l_end_date
155 FROM gl_period_statuses gps
156 WHERE gps.application_id = l_application_id
157 AND gps.set_of_books_id = l_sob_id
158 AND gps.period_name = NVL(p_period_name,
159 (SELECT gp.period_name
160 FROM gl_periods gp,
161 gl_sets_of_books sob
162 WHERE sob.set_of_books_id = l_sob_id
163 AND sob.period_set_name = gp.period_set_name
164 AND sob.accounted_period_type = gp.period_type
165 AND gp.start_date <= TRUNC(SYSDATE)
166 AND gp.end_date >= TRUNC(SYSDATE))
167 );
168
169 ---------------------------------------------------------------------
170 -- Call the common API CST_PerEndAccruals_PVT.Create_PerEndAccruals
171 -- This API creates period end accrual entries in the temporary
172 -- table CST_PER_END_ACCRUALS_TEMP.
173 ---------------------------------------------------------------------
174 l_stmt_num := 60;
175 CST_PerEndAccruals_PVT.Create_PerEndAccruals (
176 p_api_version => 1.0,
177 p_init_msg_list => FND_API.G_FALSE,
178 p_commit => FND_API.G_FALSE,
179 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
180 x_return_status => l_return_status,
181 x_msg_count => l_msg_count,
182 x_msg_data => l_msg_data,
183 p_min_accrual_amount => p_min_accrual_amount,
184 p_vendor_from => p_vendor_from,
185 p_vendor_to => p_vendor_to,
186 p_category_from => p_category_from,
187 p_category_to => p_category_to,
188 p_end_date => l_end_date,
189 p_accrued_receipt => NVL(p_accrued_receipts, 'N'),
190 p_online_accruals => NVL(p_inc_online_accruals, 'N'),
191 p_closed_pos => NVL(p_inc_closed_pos, 'N'),
192 p_calling_api => CST_PerEndAccruals_PVT.G_UNINVOICED_RECEIPT_REPORT
193 );
194 -- If return status is not success, add message to the log
195 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
196 l_msg_data := 'Failed generating Period End Accrual information';
197 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
198 END IF;
199
200 l_stmt_num := 90;
201 DBMS_LOB.createtemporary(l_xml_header, TRUE);
202 DBMS_LOB.createtemporary(l_xml_body, TRUE);
203 DBMS_LOB.createtemporary(l_xml_report, TRUE);
204
205 -- Count the no. of rows in the accrual temp table
206 -- l_row_count will be part of report header information
207 l_stmt_num := 100;
208 SELECT COUNT('X')
209 INTO l_row_count
210 FROM CST_PER_END_ACCRUALS_TEMP
211 WHERE ROWNUM = 1;
212
213 IF(p_orderby = 1) THEN
214 l_order_by := 'Category';
215 ELSIF(p_orderby = 2) THEN
216 l_order_by := 'Vendor';
217 ELSE
218 l_order_by := ' ';
219 END IF;
220
221 -------------------------------------------------------------------------
222 -- Open reference cursor for fetching data related to report header
223 -------------------------------------------------------------------------
224 l_stmt_num := 110;
225 OPEN l_header_ref_cur FOR
226 'SELECT gsb.name company_name,
227 :p_title report_title,
228 SYSDATE report_date,
229 DECODE(:p_accrued_receipts,
230 ''Y'', ''Yes'',
231 ''N'', ''No'') accrued_receipt,
232 DECODE(:p_inc_online_accruals,
233 ''Y'', ''Yes'',
234 ''N'', ''No'') include_online_accruals,
235 DECODE(:p_inc_closed_pos,
236 ''Y'', ''Yes'',
237 ''N'', ''No'') include_closed_pos,
238 :p_category_from category_from,
239 :p_category_to category_to,
240 :p_min_accrual_amount minimum_accrual_amount,
241 :p_period_name period_name,
242 :p_vendor_from vendor_from,
243 :p_vendor_to vendor_to,
244 :l_order_by order_by,
245 :l_row_count row_count
246 FROM gl_sets_of_books gsb
247 WHERE gsb.set_of_books_id = :l_sob_id'
248 USING
249 p_title,
250 p_accrued_receipts,
251 p_inc_online_accruals,
252 p_inc_closed_pos,
253 p_category_from,
254 p_category_to,
255 p_min_accrual_amount,
256 p_period_name,
257 p_vendor_from,
258 p_vendor_to,
259 l_order_by,
260 l_row_count,
261 l_sob_id;
262
263 -- Set row_tag as HEADER for report header data
264 l_row_tag := 'HEADER';
265 l_row_set_tag := NULL;
266
267 -- Generate XML data for header part
268 l_stmt_num := 120;
269 Generate_XML (
270 p_api_version => 1.0,
271 p_init_msg_list => FND_API.G_FALSE,
272 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
273 x_return_status => l_return_status,
274 x_msg_count => l_msg_count,
275 x_msg_data => l_msg_data,
276 p_ref_cur => l_header_ref_cur,
277 p_row_tag => l_row_tag,
278 p_row_set_tag => l_row_set_tag,
279 x_xml_data => l_xml_header
280 );
281 -- If return status is not success, add message to the log
282 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
283 l_msg_data := 'Failed generating XML data to the report output' ;
284 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
285 END IF;
286
287 -- If row_count is 0, no need to open body_ref_cursor
288 IF (l_row_count > 0) THEN
289
290 ---------------------------------------------------------------------
291 -- Open reference cursor for fetching data related to report body
292 ---------------------------------------------------------------------
293 l_stmt_num := 140;
294 OPEN l_body_ref_cur FOR
295 'SELECT poh.segment1 po_number,
296 porl.release_num po_release_number,
297 poh.po_header_id po_header_id,
298 pol.po_line_id po_line_id,
299 cpea.shipment_id po_shipment_id,
300 cpea.distribution_id po_distribution_id,
301 plt.line_type line_type,
302 pol.line_num line_num,
303 msi.concatenated_segments item_name,
304 mca.concatenated_segments category,
305 pol.item_description item_description,
306 pov.vendor_name vendor_name,
307 fnc2.currency_code accrual_currency_code,
308 poll.shipment_num shipment_number,
309 poll.unit_meas_lookup_code uom_code,
310 pod.distribution_num distribution_num,
311 cpea.quantity_received quantity_received,
312 cpea.quantity_billed quantity_billed,
313 cpea.accrual_quantity quantity_accrued,
314 ROUND(cpea.unit_price,
315 NVL(fnc2.precision, 2)) po_unit_price,
316 cpea.currency_code po_currency_code,
317 ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
318 0, cpea.unit_price * cpea.currency_conversion_rate,
319 (cpea.unit_price / fnc1.minimum_accountable_unit)
320 * cpea.currency_conversion_rate
321 * fnc1.minimum_accountable_unit), NVL(fnc1.precision, 2))
322 func_unit_price,
323 gcc1.concatenated_segments charge_account,
324 gcc2.concatenated_segments accrual_account,
325 cpea.accrual_amount accrual_amount,
326 ROUND(DECODE(NVL(fnc1.minimum_accountable_unit, 0),
330 * fnc1.minimum_accountable_unit), NVL(fnc1.precision, 2))
327 0, cpea.accrual_amount * cpea.currency_conversion_rate,
328 (cpea.accrual_amount / fnc1.minimum_accountable_unit)
329 * cpea.currency_conversion_rate
331 func_accrual_amount
332 FROM cst_per_end_accruals_temp cpea,
333 po_headers_all poh,
334 po_lines_all pol,
335 po_line_locations_all poll,
336 po_distributions_all pod,
337 po_vendors pov,
338 po_line_types plt,
339 po_releases_all porl,
340 mtl_system_items_kfv msi,
341 fnd_currencies fnc1,
342 fnd_currencies fnc2,
343 mtl_categories_kfv mca,
344 gl_code_combinations_kfv gcc1,
345 gl_code_combinations_kfv gcc2,
346 gl_sets_of_books sob
347 WHERE pod.po_distribution_id = cpea.distribution_id
348 AND poh.po_header_id = pol.po_header_id
349 AND pol.po_line_id = poll.po_line_id
350 AND poll.line_location_id = pod.line_location_id
351 AND pol.line_type_id = plt.line_type_id
352 AND porl.po_release_id (+) = poll.po_release_id
353 AND poh.vendor_id = pov.vendor_id
354 AND msi.inventory_item_id (+) = pol.item_id
355 AND (msi.organization_id IS NULL
356 OR
357 (msi.organization_id = poll.ship_to_organization_id AND msi.organization_id IS NOT NULL))
358 AND fnc1.currency_code = cpea.currency_code
359 AND fnc2.currency_code = sob.currency_code
360 AND cpea.category_id = mca.category_id
361 AND gcc1.code_combination_id = pod.code_combination_id
362 AND gcc2.code_combination_id = pod.accrual_account_id
363 AND sob.set_of_books_id = :l_sob_id
364 ORDER BY DECODE(:l_order_by,
365 ''Category'', mca.concatenated_segments,
366 ''Vendor'', pov.vendor_name),
367 poh.segment1,
368 pol.line_num,
369 poll.shipment_num,
370 pod.distribution_num'
371 USING l_sob_id, l_order_by
372 ;
373
374 l_row_tag := 'BODY';
375 l_row_set_tag := 'ACCRUAL_INFO';
376
377 -- Generate XML data for report body
378 l_stmt_num := 150;
379 Generate_XML (
380 p_api_version => 1.0,
381 p_init_msg_list => FND_API.G_FALSE,
382 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
383 x_return_status => l_return_status,
384 x_msg_count => l_msg_count,
385 x_msg_data => l_msg_data,
386 p_ref_cur => l_body_ref_cur,
387 p_row_tag => l_row_tag,
388 p_row_set_tag => l_row_set_tag,
389 x_xml_data => l_xml_body
390 );
391 -- If return status is not success, add message to the log
392 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
393 l_msg_data := 'Failed generating XML data to the report output' ;
394 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
395 END IF;
396
397 END IF;
398
399 -- Merge the header part with the body part.
400 -- 'ACR_REPORT' will be used as root tag for resultant XML data
401 l_stmt_num := 160;
402 Merge_XML
403 (
404 p_api_version => 1.0,
405 p_init_msg_list => FND_API.G_FALSE,
406 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
407 x_return_status => l_return_status,
408 x_msg_count => l_msg_count,
409 x_msg_data => l_msg_data,
410 p_xml_src1 => l_xml_header,
411 p_xml_src2 => l_xml_body,
412 p_root_tag => 'ACR_REPORT',
413 x_xml_doc => l_xml_report
414 );
415 -- If return status is not success, add message to the log
416 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
417 l_msg_data := 'Failed generating XML data to the report output' ;
418 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
419 END IF;
420
421 -- Print the XML data to the report output
422 l_stmt_num := 170;
423 Print_ClobOutput(
424 p_api_version => 1.0,
425 p_init_msg_list => FND_API.G_FALSE,
426 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
427 x_return_status => l_return_status,
428 x_msg_count => l_msg_count,
429 x_msg_data => l_msg_data,
430 p_xml_data => l_xml_report
431 );
432 -- If return status is not success, add message to the log
433 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
437
434 l_msg_data := 'Failed writing XML data to the report output' ;
435 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
436 END IF;
438 -- Write log messages to request log
439 l_stmt_num := 180;
440 CST_UTILITY_PUB.writelogmessages (
441 p_api_version => 1.0,
442 p_msg_count => l_msg_count,
443 p_msg_data => l_msg_data,
444 x_return_status => l_return_status
445 );
446 -- If return status is not success, add message to the log
447 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
448 l_msg_data := 'Failed writing log messages' ;
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451
452 -- Procedure level log message for exit point
453 IF (l_pLog) THEN
454 FND_LOG.STRING(
455 FND_LOG.LEVEL_PROCEDURE,
456 l_module || '.end',
457 'Start_Process >>'
458 );
459 END IF;
460
461 EXCEPTION
462 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
463
464 IF (l_exceptionLog) THEN
465 FND_LOG.STRING(
466 FND_LOG.LEVEL_EXCEPTION,
467 l_module || '.' || l_stmt_num,
468 l_msg_data
469 );
470 END IF;
471
472 -- Write log messages to request log
473 CST_UTILITY_PUB.writelogmessages (
474 p_api_version => 1.0,
475 p_msg_count => l_msg_count,
476 p_msg_data => l_msg_data,
477 x_return_status => l_return_status
478 );
479
480 -- Set concurrent program status to error
481 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_msg_data);
482
483 WHEN OTHERS THEN
484
485 -- Unexpected level log message for FND log
486 IF (l_uLog) THEN
487 FND_LOG.STRING(
488 FND_LOG.LEVEL_UNEXPECTED,
489 l_module || '.' || l_stmt_num,
490 SQLERRM
491 );
492 END IF;
493
494 IF FND_MSG_PUB.Check_Msg_Level
495 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
496 THEN
497 FND_MSG_PUB.Add_Exc_Msg
498 ( G_PKG_NAME,
499 l_api_name,
500 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
501 );
502 END IF;
503
504 -- Write log messages to request log
505 CST_UTILITY_PUB.writelogmessages (
506 p_api_version => 1.0,
507 p_msg_count => l_msg_count,
508 p_msg_data => l_msg_data,
509 x_return_status => l_return_status
510 );
511
512 -- Set concurrent program status to error
513 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
514 'An unexpected error has occurred, please contact System Administrator. ');
515
516 END Start_Process;
517
518
519 -----------------------------------------------------------------------------
520 -- PROCEDURE : Generate_XML
521 -- DESCRIPTION : The procedure generates and returns the XML data for
522 -- the reference cursor passed by the calling API.
523 -----------------------------------------------------------------------------
524 PROCEDURE Generate_XML
525 (
526 p_api_version IN NUMBER,
527 p_init_msg_list IN VARCHAR2,
528 p_validation_level IN NUMBER,
529
530 x_return_status OUT NOCOPY VARCHAR2,
531 x_msg_count OUT NOCOPY NUMBER,
532 x_msg_data OUT NOCOPY VARCHAR2,
533
534 p_ref_cur IN SYS_REFCURSOR,
535 p_row_tag IN VARCHAR2,
536 p_row_set_tag IN VARCHAR2,
537
538 x_xml_data OUT NOCOPY CLOB
539 )
540 IS
541 l_api_name CONSTANT VARCHAR2(30) :='Generate_XML';
542 l_api_version CONSTANT NUMBER := 1.0;
543 l_return_status VARCHAR2(1);
544 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
545 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
546
547 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
548 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
549 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
550
551 l_stmt_num NUMBER;
552 l_ctx DBMS_XMLGEN.CTXHANDLE;
553
554 BEGIN
555
556 l_stmt_num := 0;
557 -- Procedure level log message for Entry point
558 IF (l_pLog) THEN
559 FND_LOG.STRING(
560 FND_LOG.LEVEL_PROCEDURE,
561 l_module || '.begin',
562 'Generate_XML <<');
563 END IF;
564
565 -- Standard call to check for call compatibility.
566 IF NOT FND_API.Compatible_API_Call ( l_api_version,
567 p_api_version,
568 l_api_name,
572 END IF;
569 G_PKG_NAME )
570 THEN
571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
573
574 -- Initialize message list if p_init_msg_list is set to TRUE.
575 IF FND_API.to_Boolean( p_init_msg_list ) THEN
576 FND_MSG_PUB.initialize;
577 END IF;
578
579 -- Initialize API return status to success
580 x_return_status := FND_API.G_RET_STS_SUCCESS;
581 l_return_status := FND_API.G_RET_STS_SUCCESS;
582
583 -- create a new context with the SQL query
584 l_stmt_num := 10;
585 l_ctx := DBMS_XMLGEN.newContext (p_ref_cur);
586
587 -- Add tag names for rows and row sets
588 l_stmt_num := 20;
589 DBMS_XMLGEN.setRowSetTag(l_ctx, p_row_tag);
590 DBMS_XMLGEN.setRowTag(l_ctx, p_row_set_tag);
591
592 -- generate XML data
593 l_stmt_num := 30;
594 x_xml_data := DBMS_XMLGEN.getXML (l_ctx);
595
596 -- close the context
597 l_stmt_num := 40;
598 DBMS_XMLGEN.CLOSECONTEXT(l_ctx);
599
600 -- Procedure level log message for exit point
601 IF (l_pLog) THEN
602 FND_LOG.STRING(
603 FND_LOG.LEVEL_PROCEDURE,
604 l_module || '.end',
605 'Generate_XML >>'
606 );
607 END IF;
608
609 -- Get message count and if 1, return message data.
610 FND_MSG_PUB.Count_And_Get
611 ( p_count => x_msg_count,
612 p_data => x_msg_data
613 );
614
615 EXCEPTION
616
617 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
618 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
619
620 FND_MSG_PUB.Count_And_Get
621 ( p_count => x_msg_count,
622 p_data => x_msg_data
623 );
624
625 WHEN OTHERS THEN
626 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
627
628 -- Unexpected level log message
629 IF (l_uLog) THEN
630 FND_LOG.STRING(
631 FND_LOG.LEVEL_UNEXPECTED,
632 l_module || '.' || l_stmt_num,
633 SQLERRM
634 );
635 END IF;
636
637 IF FND_MSG_PUB.Check_Msg_Level
638 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
639 THEN
640 FND_MSG_PUB.Add_Exc_Msg
641 ( G_PKG_NAME,
642 l_api_name,
643 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
644 );
645 END IF;
646
647 FND_MSG_PUB.Count_And_Get
648 ( p_count => x_msg_count,
649 p_data => x_msg_data
650 );
651
652 END Generate_XML;
653
654 -----------------------------------------------------------------------------
655 -- PROCEDURE : Merge_XML
656 -- DESCRIPTION : The procedure merges data from two XML objects into a
657 -- single XML object and adds a root tag to the resultant
658 -- XML data.
659 -----------------------------------------------------------------------------
660 PROCEDURE Merge_XML
661 (
662 p_api_version IN NUMBER,
663 p_init_msg_list IN VARCHAR2,
664 p_validation_level IN NUMBER,
665
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2,
669
670 p_xml_src1 IN CLOB,
671 p_xml_src2 IN CLOB,
672 p_root_tag IN VARCHAR2,
673
674 x_xml_doc OUT NOCOPY CLOB
675 )
676
677 IS
678 l_api_name CONSTANT VARCHAR2(30) :='Merge_XML';
679 l_api_version CONSTANT NUMBER := 1.0;
680 l_return_status VARCHAR2(1);
681 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
682 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
683
684 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
685 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
686 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
687
688 l_ctx DBMS_XMLGEN.CTXHANDLE;
689 l_offset NUMBER;
690 l_stmt_num NUMBER;
691 l_length_src1 NUMBER;
692 l_length_src2 NUMBER;
693 /*Bug 7282242*/
694 l_encoding VARCHAR2(20);
695 l_xml_header VARCHAR2(100);
696
697 BEGIN
698
699 l_stmt_num := 0;
700 -- Procedure level log message for Entry point
701 IF (l_pLog) THEN
702 FND_LOG.STRING(
703 FND_LOG.LEVEL_PROCEDURE,
704 l_module || '.begin',
705 'Merge_XML <<');
706 END IF;
707
708 -- Standard call to check for call compatibility.
712 G_PKG_NAME )
709 IF NOT FND_API.Compatible_API_Call ( l_api_version,
710 p_api_version,
711 l_api_name,
713 THEN
714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
715 END IF;
716
717 -- Initialize message list if p_init_msg_list is set to TRUE.
718 IF FND_API.to_Boolean( p_init_msg_list ) THEN
719 FND_MSG_PUB.initialize;
720 END IF;
721
722 -- Initialize API return status to success
723 x_return_status := FND_API.G_RET_STS_SUCCESS;
724 l_return_status := FND_API.G_RET_STS_SUCCESS;
725
726 l_stmt_num := 10;
727 l_length_src1 := DBMS_LOB.GETLENGTH(p_xml_src1);
728 l_length_src2 := DBMS_LOB.GETLENGTH(p_xml_src2);
729
730 l_stmt_num := 20;
731 DBMS_LOB.createtemporary(x_xml_doc, TRUE);
732
733 IF (l_length_src1 > 0) THEN
734
735 -- Get the first occurence of XML header
736 l_stmt_num := 30;
737 l_offset := DBMS_LOB.instr (lob_loc => p_xml_src1,
738 pattern => '>',
739 offset => 1,
740 nth => 1);
741
742 -- Copy XML header part to the destination XML doc
743 l_stmt_num := 40;
744
745 /*Bug 7282242*/
746 /*Remove the header (21 characters)*/
747 --DBMS_LOB.copy (x_xml_doc, p_xml_src1, l_offset + 1);
748
749 /*The following 3 lines of code ensures that XML data generated here uses the right encoding*/
750 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
751 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
752 DBMS_LOB.writeappend (x_xml_doc, length(l_xml_header), l_xml_header);
753
754 -- Append the root tag to the XML doc
755 l_stmt_num := 50;
756 DBMS_LOB.writeappend (x_xml_doc, LENGTH(p_root_tag) + 2, '<' || p_root_tag || '>');
757
758 -- Append the 1st XML doc to the destination XML doc
759 l_stmt_num := 60;
760 DBMS_LOB.copy ( x_xml_doc,
761 p_xml_src1,
762 l_length_src1 - l_offset,
763 DBMS_LOB.GETLENGTH(x_xml_doc) + 1,
764 l_offset + 1
765 );
766
767 -- Append the 2nd XML doc to the destination XML doc
768 IF (l_length_src2 > 0) THEN
769 l_stmt_num := 70;
770 DBMS_LOB.copy ( x_xml_doc,
771 p_xml_src2,
772 l_length_src2 - l_offset,
773 DBMS_LOB.GETLENGTH(x_xml_doc) + 1,
774 l_offset + 1
775 );
776 END IF;
777
778 -- Append the root tag to the end of XML doc
779 l_stmt_num := 80;
780 DBMS_LOB.writeappend (x_xml_doc, LENGTH(p_root_tag) + 3, '</' || p_root_tag || '>');
781
782 END IF;
783
784 -- Procedure level log message for exit point
785 IF (l_pLog) THEN
786 FND_LOG.STRING(
787 FND_LOG.LEVEL_PROCEDURE,
788 l_module || '.end',
789 'Merge_XML >>'
790 );
791 END IF;
792
793 -- Get message count and if 1, return message data.
794 FND_MSG_PUB.Count_And_Get
795 ( p_count => x_msg_count,
796 p_data => x_msg_data
797 );
798
799 EXCEPTION
800
801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
803
804 FND_MSG_PUB.Count_And_Get
805 ( p_count => x_msg_count,
806 p_data => x_msg_data
807 );
808
809 WHEN OTHERS THEN
810 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
811
812 -- Unexpected level log message
813 IF (l_uLog) THEN
814 FND_LOG.STRING(
815 FND_LOG.LEVEL_UNEXPECTED,
816 l_module || '.' || l_stmt_num,
817 SQLERRM
818 );
819 END IF;
820
821 IF FND_MSG_PUB.Check_Msg_Level
822 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
823 THEN
824 FND_MSG_PUB.Add_Exc_Msg
825 ( G_PKG_NAME,
826 l_api_name,
827 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
828 );
829 END IF;
830
831 FND_MSG_PUB.Count_And_Get
832 ( p_count => x_msg_count,
833 p_data => x_msg_data
834 );
835
836 END Merge_XML;
837
838 -----------------------------------------------------------------------------
839 -- PROCEDURE : Merge_XML
840 -- DESCRIPTION : The procedure writes the XML data to the report output
841 -- file. The XML publisher picks the data from this output
842 -- file to display the data in user specified format.
843 -----------------------------------------------------------------------------
844 PROCEDURE Print_ClobOutput
845 (
846 p_api_version IN NUMBER,
847 p_init_msg_list IN VARCHAR2,
848 p_validation_level IN NUMBER,
849
850 x_return_status OUT NOCOPY VARCHAR2,
851 x_msg_count OUT NOCOPY NUMBER,
852 x_msg_data OUT NOCOPY VARCHAR2,
853 p_xml_data IN CLOB
854 )
855 IS
856 l_api_name CONSTANT VARCHAR2(30) :='Print_ClobOutput';
857 l_api_version CONSTANT NUMBER := 1.0;
858 l_return_status VARCHAR2(1);
859 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
860 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
861
862 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
863 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
864 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
865
866 l_stmt_num NUMBER;
867 l_amount NUMBER;
868 l_offset NUMBER;
869 l_length NUMBER;
870 l_data VARCHAR2(32767);
871
872 BEGIN
873
874 l_stmt_num := 0;
875 -- Procedure level log message for Entry point
876 IF (l_pLog) THEN
877 FND_LOG.STRING(
878 FND_LOG.LEVEL_PROCEDURE,
879 l_module || '.begin',
880 'Print_ClobOutput <<');
881 END IF;
882
883 -- Standard call to check for call compatibility.
884 IF NOT FND_API.Compatible_API_Call ( l_api_version,
885 p_api_version,
886 l_api_name,
887 G_PKG_NAME )
888 THEN
889 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
890 END IF;
891
892 -- Initialize message list if p_init_msg_list is set to TRUE.
893 IF FND_API.to_Boolean( p_init_msg_list ) THEN
894 FND_MSG_PUB.initialize;
895 END IF;
896
897 -- Initialize API return status to success
898 x_return_status := FND_API.G_RET_STS_SUCCESS;
899 l_return_status := FND_API.G_RET_STS_SUCCESS;
900
901 -- Get length of the CLOB p_xml_data
902 l_stmt_num := 10;
903 l_length := nvl(DBMS_LOB.getlength(p_xml_data), 0);
904
905 -- Set the offset point to be the start of the CLOB data
906 l_offset := 1;
907
908 -- l_amount will be used to read 32KB of data once at a time
909 l_amount := 16383; --Changed for bug 6954937
910
911 -- Loop until the length of CLOB data is zero
912 l_stmt_num := 20;
913 LOOP
914
915 EXIT WHEN l_length <= 0;
916
917 -- Read 32 KB of data and print it to the report output
918 DBMS_LOB.read (p_xml_data, l_amount, l_offset, l_data);
919
920 FND_FILE.PUT(FND_FILE.OUTPUT, l_data);
921
922 l_length := l_length - l_amount;
923 l_offset := l_offset + l_amount;
924
925 END LOOP;
926
927 -- Procedure level log message for exit point
928 IF (l_pLog) THEN
929 FND_LOG.STRING(
930 FND_LOG.LEVEL_PROCEDURE,
931 l_module || '.end',
932 'Print_ClobOutput >>'
933 );
934 END IF;
935
936 -- Get message count and if 1, return message data.
937 FND_MSG_PUB.Count_And_Get
938 ( p_count => x_msg_count,
939 p_data => x_msg_data
940 );
941
942 EXCEPTION
943
944 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
946
947 FND_MSG_PUB.Count_And_Get
948 ( p_count => x_msg_count,
949 p_data => x_msg_data
950 );
951
952 WHEN OTHERS THEN
953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
954
955 -- Unexpected level log message
956 IF (l_uLog) THEN
957 FND_LOG.STRING(
958 FND_LOG.LEVEL_UNEXPECTED,
959 l_module || '.' || l_stmt_num,
960 SQLERRM
961 );
962 END IF;
963
964 IF FND_MSG_PUB.Check_Msg_Level
965 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
966 THEN
967 FND_MSG_PUB.Add_Exc_Msg
968 ( G_PKG_NAME,
969 l_api_name,
970 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
971 );
972 END IF;
973
974 FND_MSG_PUB.Count_And_Get
975 ( p_count => x_msg_count,
976 p_data => x_msg_data
977 );
978
979 END Print_ClobOutput;
980
981 END CST_UninvoicedReceipts_PVT;