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