[Home] [Help]
PACKAGE BODY: APPS.CST_PAC_WIP_VALUE_REPORT_PVT
Source
1 PACKAGE BODY CST_PAC_WIP_VALUE_REPORT_PVT AS
2 /* $Header: CSTPWVRB.pls 120.11 2010/08/31 20:50:25 fayang ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_PAC_WIP_VALUE_REPORT_PVT';
5
6 /*-------------------------------------------------------------------------------
7 -- PROCEDURE : Generate_XML
8 -- DESCRIPTION : Procedure to call all the procedures which generates XML data
9 --------------------------------------------------------------------------------*/
10
11 PROCEDURE Generate_XMLData
12 (errbuf OUT NOCOPY VARCHAR2,
13 retcode OUT NOCOPY NUMBER,
14 p_report_type IN VARCHAR2,
15 p_legal_entity_id IN VARCHAR2,
16 p_cost_type_id IN VARCHAR2,
17 p_pac_period_id IN NUMBER,
18 p_cost_group_id IN VARCHAR2,
19 p_set_of_books IN VARCHAR2,
20 p_class_type IN VARCHAR2,
21 p_from_job IN VARCHAR2,
22 p_to_job IN VARCHAR2,
23 p_from_assembly IN VARCHAR2,
24 p_to_assembly IN VARCHAR2,
25 p_currency_code IN VARCHAR2,
26 p_disp_inv_rate IN VARCHAR2,
27 p_exchange_rate_type IN NUMBER,
28 p_exchange_rate_char IN VARCHAR2,
29 p_stuct_number IN NUMBER
30 )
31 IS
32 l_api_name CONSTANT VARCHAR2(30) := 'Generate_XMLData';
33 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_PAC_WIP_Value_Report_PVT.Generate_XMLData';
34 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
35 l_uLog CONSTANT BOOLEAN := fnd_log.TEST(fnd_log.level_unexpected, l_module) AND fnd_log.level_unexpected >= l_log_level;
36 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
37 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
38 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
39 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
40 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
41
42 l_xml_doc CLOB;
43 l_amount NUMBER;
44 l_offset NUMBER;
45 l_length NUMBER;
46 l_buffer VARCHAR2(32767);
47 l_stmt_num NUMBER;
48
49 l_return_status VARCHAR2(1);
50 l_msg_count NUMBER;
51 l_msg_data VARCHAR2(2000);
52 l_success BOOLEAN;
53
54 l_report_type NUMBER;
55 l_legal_entity_id NUMBER;
56 l_cost_type_id NUMBER;
57 l_cost_group_id NUMBER;
58
59 BEGIN
60 l_stmt_num := 0;
61 IF l_plog THEN
62 fnd_log.string(
63 fnd_log.level_procedure,
64 l_module||'.'||l_stmt_num,
65 'Entering CST_PAC_WIP_Value_Report_PVT.Generate_XMLData with << '||
66 'p_report_type = '||p_report_type||','||
67 'p_legal_entity_id = '||p_legal_entity_id||','||
68 'p_cost_group_id = '||p_cost_group_id||','||
69 'p_cost_type_id = '||p_cost_type_id||','||
70 'p_pac_period_id = '||p_pac_period_id||','||
71 'p_class_type = '||p_class_type||','||
72 'p_from_job = '||p_from_job||','||
73 'p_to_job = '||p_to_job||','||
74 'p_from_assembly = '||p_from_assembly||','||
75 'p_to_assembly = '||p_to_assembly||','||
76 'p_exchange_rate_char = '||p_exchange_rate_char||','||
77 'p_currency_code = '||p_currency_code
78 );
79 END IF;
80
81 -- Convert to NUMBER data type
82 l_report_type := TO_NUMBER(p_report_type);
83 l_legal_entity_id := TO_NUMBER(p_legal_entity_id);
84 l_cost_type_id := TO_NUMBER(p_cost_type_id);
85 l_cost_group_id := TO_NUMBER(p_cost_group_id);
86
87 l_stmt_num := 20;
88 -- Create CLOB object to store AML data
89 DBMS_LOB.createtemporary(l_xml_doc, TRUE);
90
91 -- Initialize message stack
92 FND_MSG_PUB.initialize;
93
94 ------------------------------------------------------------------
95 -- Populate temporary table for given PAC CostType and CostGroup
96 ------------------------------------------------------------------
97 l_stmt_num := 30;
98 Periodic_WIP_value_rpt_details(p_api_version => 1.0,
99 p_init_msg_list => FND_API.G_FALSE,
100 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
101 x_return_status => l_return_status,
102 x_msg_count => l_msg_count,
103 x_msg_data => l_msg_data,
104 p_report_type => p_report_type,
105 p_pac_period_id => p_pac_period_id,
106 p_cost_group_id => p_cost_group_id,
107 p_cost_type_id => p_cost_type_id,
108 p_legal_entity_id => p_legal_entity_id);
109
110
111
112 -- If return status is not success, add message to the log
113 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
114 l_msg_data := 'Failed generating Periodic WIP value details information';
115 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
116 END IF;
117
118 -----------------------------------------------------------------
119 -- Generate XML data for displaying the report parameters values
120 -----------------------------------------------------------------
121 l_stmt_num := 40;
122 Display_Parameters (p_api_version => 1.0,
123 p_init_msg_list => FND_API.G_FALSE,
124 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
125 x_return_status => l_return_status,
126 x_msg_count => l_msg_count,
127 x_msg_data => l_msg_data,
128 p_report_type => p_report_type,
129 p_legal_entity_id => p_legal_entity_id,
130 p_cost_group_id => p_cost_group_id,
131 p_cost_type_id => p_cost_type_id,
132 p_pac_period_id => p_pac_period_id,
133 p_class_type => p_class_type,
134 p_from_job => p_from_job,
135 p_to_job => p_to_job,
136 p_from_assembly => p_from_assembly,
137 p_to_assembly => p_to_assembly,
138 p_exchange_rate_char => p_exchange_rate_char,
139 p_currency_code => p_currency_code,
140 x_xml_doc => l_xml_doc);
141
142 -- If return status is not success, add message to the log
143 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
144 l_msg_data := 'Failed generating XML data for report parameter information';
145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
146 END IF;
147
148 -----------------------------------------------------------------
149 -- Generate XML data for displaying the report parameters values
150 -----------------------------------------------------------------
151 l_stmt_num := 50;
152 Get_XMLData(p_api_version => 1.0,
153 p_init_msg_list => FND_API.G_FALSE,
154 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
155 x_return_status => l_return_status,
156 x_msg_count => l_msg_count,
157 x_msg_data => l_msg_data,
158 p_legal_entity_id => p_legal_entity_id,
159 p_cost_group_id => p_cost_group_id,
160 p_cost_type_id => p_cost_type_id,
161 p_pac_period_id => p_pac_period_id ,
162 p_class_type => p_class_type,
163 p_from_job => p_from_job,
164 p_to_job => p_to_job,
165 p_from_assembly => p_from_assembly,
166 p_to_assembly => p_to_assembly,
167 p_exchange_rate_char => p_exchange_rate_char,
168 p_currency_code => p_currency_code,
169 x_xml_doc => l_xml_doc);
170
171 -- If return status is not success, add message to the log
172 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
173 l_msg_data := 'Failed generating XML data of the report output';
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 END IF;
176
177 DBMS_LOB.writeappend (l_xml_doc, 9, '</REPORT>');
178
179 -- Get length of the CLOB l_xml_doc
180 l_stmt_num := 60;
181 l_length := nvl(DBMS_LOB.getlength(l_xml_doc), 0);
182 l_offset := 1;
183 l_amount := 16383;
184
185 -- Loop until the length of CLOB data is zero
186 l_stmt_num := 70;
187 LOOP
188 EXIT WHEN l_length <= 0;
189 -- Read 32 KB of data and print it to the report output
190 DBMS_LOB.read (l_xml_doc, l_amount, l_offset, l_buffer);
191 FND_FILE.PUT (FND_FILE.OUTPUT, l_buffer);
192 l_length := l_length - l_amount;
193 l_offset := l_offset + l_amount;
194 END LOOP;
195
196 DBMS_LOB.FREETEMPORARY (l_xml_doc);
197
198 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'Request Completed Successfully');
199
200 IF l_plog THEN
201 fnd_log.string(
202 fnd_log.level_procedure,
203 l_module||'.'||l_stmt_num,
204 'Exiting CST_PAC_WIP_Value_Report_PVT.Generate_XMLData >> ');
205 END IF;
206
207 EXCEPTION
208 WHEN fnd_api.g_exc_unexpected_error THEN
209
210 IF l_exceptionlog THEN
211 fnd_msg_pub.add_exc_msg(
212 p_pkg_name => 'CST_PAC_WIP_Value_Report_PVT',
213 p_procedure_name => 'Generate_XMLData',
214 p_error_text => 'An exception has occurred.'
215 );
216 fnd_log.string(
217 fnd_log.level_exception,
218 l_module||'.'||l_stmt_num,
219 'An exception has occurred.'
220 );
221 END IF;
222 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'Exception Occured');
223
224 WHEN OTHERS THEN
225 IF l_uLog THEN
226 fnd_message.set_name('BOM','CST_UNEXPECTED');
227 fnd_message.set_token('SQLERRM',SQLERRM);
228 fnd_msg_pub.add;
229 fnd_log.message(
230 fnd_log.level_unexpected,
231 l_module||'.'||l_stmt_num,
232 FALSE
233 );
234 END IF;
235 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', 'An unexpected error has occurred');
236 END Generate_XMLData;
237
238 -----------------------------------------------------------------------------
239 -- PROCEDURE : Display_Parameters
240 -- DESCRIPTION : Procedure for to display all the concurrent request
241 -- parameters in XML
242 -----------------------------------------------------------------------------
243
244 PROCEDURE Display_Parameters (p_api_version IN NUMBER,
245 p_init_msg_list IN VARCHAR2,
246 p_validation_level IN NUMBER,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 p_report_type IN NUMBER,
251 p_legal_entity_id IN NUMBER,
252 p_cost_group_id IN NUMBER,
253 p_cost_type_id IN NUMBER,
254 p_pac_period_id IN NUMBER,
255 p_class_type IN VARCHAR2,
256 p_from_job IN VARCHAR2,
257 p_to_job IN VARCHAR2,
258 p_from_assembly IN VARCHAR2,
259 p_to_assembly IN VARCHAR2,
260 p_exchange_rate_char IN VARCHAR2,
261 p_currency_code IN VARCHAR2,
262 x_xml_doc IN OUT NOCOPY CLOB)
263 IS
264 l_api_name CONSTANT VARCHAR2(30) := 'Display_Parameters';
265 l_api_version CONSTANT NUMBER := 1.0;
266
267 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_PAC_WIP_Value_Report_PVT.Display_Parameters';
268 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
269 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
270 fnd_log.TEST(fnd_log.level_unexpected, l_module);
271 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
272 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
273 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
274 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
275 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
276
277 l_ref_cur SYS_REFCURSOR;
278 l_ctx NUMBER;
279 l_xml_temp CLOB;
280 l_offset PLS_INTEGER;
281 l_wip_entity_name VARCHAR2(240);
282 l_stmt_num NUMBER;
283 /*Bug 7305146*/
284 l_encoding VARCHAR2(20);
285 l_xml_header VARCHAR2(100);
286
287 BEGIN
288 l_stmt_num := 0;
289 IF l_plog THEN
290 fnd_log.string(
291 fnd_log.level_procedure,
292 l_module||'.'||l_stmt_num,
293 'Entering CST_PAC_WIP_Value_Report_PVT.Display_Parameters with << '||
294 'p_init_msg_list = '||p_init_msg_list||','||
295 'p_validation_level = '||p_validation_level||','||
296 'p_report_type = '||p_report_type||','||
297 'p_legal_entity_id = '||p_legal_entity_id||','||
298 'p_cost_group_id = '||p_cost_group_id||','||
299 'p_cost_type_id = '||p_cost_type_id||','||
300 'p_pac_period_id = '||p_pac_period_id||','||
301 'p_class_type = '||p_class_type||','||
302 'p_from_job = '||p_from_job||','||
303 'p_to_job = '||p_to_job||','||
304 'p_from_assembly = '||p_from_assembly||','||
305 'p_to_assembly = '||p_to_assembly||','||
306 'p_exchange_rate_char = '||p_exchange_rate_char||','||
307 'p_currency_code = '||p_currency_code
308 );
309 END IF;
310
311 l_stmt_num := 10;
312 IF NOT FND_API.Compatible_API_Call (l_api_version,
313 p_api_version,
314 l_api_name,
315 G_PKG_NAME )
316 THEN
317 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
318 END IF;
319
320 -- Initialize API return status to success
321 l_stmt_num := 20;
322 x_return_status := FND_API.G_RET_STS_SUCCESS;
323
324 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
325
326 -------------------------------------------------------------------------------
327 -- Open reference cursor for fetching data related to report parameter values
328 -------------------------------------------------------------------------------
329 l_stmt_num := 30;
330 OPEN l_ref_cur FOR SELECT xle.name,
331 cct.cost_type,
332 cpp.PERIOD_NAME,
333 ccg.cost_group,
334 p_class_type className,
335 p_from_job from_job,
336 p_to_job to_job,
337 p_from_assembly from_aasembly,
338 p_to_assembly to_aasembly,
339 p_exchange_rate_char exchange_rate_char,
340 p_currency_code currency_code,
341 m1.meaning rep_type
342 FROM xle_entity_profiles xle,
343 cst_pac_periods cpp,
344 cst_cost_types cct,
345 cst_le_cost_types clct,
346 cst_cost_groups ccg,
347 mfg_lookups m1
348 WHERE xle.legal_entity_id = p_legal_entity_id
349 AND clct.legal_entity = xle.legal_entity_id
350 AND clct.cost_type_id = cct.cost_type_id
351 AND cct.cost_type_id = p_cost_type_id
352 AND cpp.legal_entity = clct.legal_entity
353 AND cpp.cost_type_id = cct.cost_type_id
354 AND cpp.pac_period_id = p_pac_period_id
355 AND ccg.cost_group_id = p_cost_group_id
356 AND ccg.legal_entity = clct.legal_entity
357 AND m1.lookup_type = 'WIP_REP_VAL_TYPE'
358 AND m1.lookup_code = p_report_type;
359
360 -- create a new context with the SQL query
361 l_stmt_num := 40;
362 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
363
364 -- Set the row tag and rowset tag
365 l_stmt_num := 50;
366 DBMS_XMLGEN.setRowSetTag(l_ctx,'REPORTPARAMETERS');
367 DBMS_XMLGEN.setRowTag(l_ctx,NULL);
368
369 -- generate XML data
370 l_stmt_num := 60;
371 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
372
373 l_stmt_num := 70;
374 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
375 pattern => '>',
376 offset => 1,
377 nth => 1);
378
379 /*Bug 7305146
380 -- Copy XML header part to the destination XML doc
381 l_stmt_num := 80;
382 DBMS_LOB.copy (x_xml_doc, l_xml_temp, l_offset + 1);*/
383
384 -- Remove the header
385 l_stmt_num := 80;
386 DBMS_LOB.erase (l_xml_temp, l_offset,1);
387
388 l_stmt_num := 90;
389 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
390 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
391 DBMS_LOB.writeappend (x_xml_doc, length(l_xml_header), l_xml_header);
392
393 -- append the REPORT tag to XML
394 l_stmt_num := 100;
395 DBMS_LOB.writeappend (X_xml_doc, 8, '<REPORT>');
396
397 l_stmt_num := 110;
398 -- Append the rest to xml output
399 DBMS_LOB.append (x_xml_doc, l_xml_temp);
400
401 -- close context and free memory
402 l_stmt_num := 120;
403 DBMS_XMLGEN.closeContext(l_ctx);
404 CLOSE l_ref_cur;
405 DBMS_LOB.FREETEMPORARY (l_xml_temp);
406
407 IF l_plog THEN
408 fnd_log.string(
409 fnd_log.level_procedure,
410 l_module||'.'||l_stmt_num,
411 'Exiting CST_PAC_WIP_Value_Report_PVT.Display_Parameters >> ');
412 END IF;
413
414 EXCEPTION
415 WHEN fnd_api.g_exc_unexpected_error THEN
416 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
417
418 IF l_exceptionlog THEN
419 fnd_msg_pub.add_exc_msg(
420 p_pkg_name => 'CST_PAC_WIP_Value_Report_PVT',
421 p_procedure_name => 'Display_Parameters',
422 p_error_text => 'An exception has occurred.'
423 );
424 IF l_uLog THEN
425 fnd_log.string(
426 fnd_log.level_exception,
427 l_module||'.'||l_stmt_num,
428 'An exception has occurred.'
429 );
430 END IF;
431 END IF;
432 WHEN OTHERS THEN
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434
435 IF l_uLog THEN
436 fnd_log.string(
437 FND_LOG.LEVEL_UNEXPECTED,
438 l_module || '.' || l_stmt_num,
439 SUBSTRB (SQLERRM , 1 , 230)
440 );
441 END IF;
442 END Display_Parameters;
443
444 -----------------------------------------------------------------------------
445 -- PROCEDURE : Periodic_WIP_Value_Rpt_Details
446 -- DESCRIPTION : Procedure for to populate the table cst_wip_pac_period_bal_tmp
447 -- For a given pac period, pac cost type and pac cost group
448 -----------------------------------------------------------------------------
449
450 PROCEDURE Periodic_WIP_Value_Rpt_Details( p_api_version IN NUMBER,
451 p_init_msg_list IN VARCHAR2,
452 p_validation_level IN NUMBER,
453 x_return_status OUT NOCOPY VARCHAR2,
454 x_msg_count OUT NOCOPY NUMBER,
455 x_msg_data OUT NOCOPY VARCHAR2,
456 p_report_type IN NUMBER,
457 p_pac_period_id IN NUMBER,
458 p_cost_group_id IN NUMBER,
459 p_cost_type_id IN NUMBER,
460 p_legal_entity_id IN NUMBER)
461 IS
462
463 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_PAC_WIP_Value_Report_PVT.Periodic_WIP_Value_Rpt_Details';
464 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
465 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
466 fnd_log.TEST(fnd_log.level_unexpected, l_module);
467 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
468 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
469 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
470 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
471 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
472
473
474 CURSOR wip_pac_bal is
475 SELECT wip_entity_id,
476 organization_id,
477 line_id,
478 /* All In */
479 sum( nvl(pl_material_in, 0)) material_in,
480 sum( nvl(pl_material_overhead_in, 0)) material_ovhd_in ,
481 sum( nvl(tl_resource_in ,0) + nvl( pl_resource_in,0)) Resource_in,
482 sum( nvl(tl_overhead_in ,0) + nvl( pl_overhead_in, 0)) overhead_in,
483 sum( nvl(tl_outside_processing_in ,0) + nvl( pl_outside_processing_in,0)) osp_in,
484 /* All out */
485 sum( nvl(pl_material_out, 0) ) material_out,
486 sum( nvl(pl_material_overhead_out , 0)) material_ovhd_out,
487 sum( nvl(tl_resource_out ,0) + nvl( pl_resource_out, 0)) resource_out,
488 sum( nvl(tl_outside_processing_out ,0) + nvl( pl_outside_processing_out,0)) osp_out,
489 sum( nvl(tl_overhead_out ,0) + nvl( pl_overhead_out, 0)) overhead_out,
490 /* All var */
491 sum( nvl(pl_material_var, 0) ) material_var,
492 sum( nvl(pl_material_overhead_var , 0)) material_ovhd_var,
493 sum( nvl(tl_resource_var ,0) + nvl(pl_resource_var, 0)) resource_var,
494 sum( nvl(tl_outside_processing_var ,0) + nvl(pl_outside_processing_var,0)) osp_var,
495 sum( nvl(tl_overhead_var,0) + nvl(pl_overhead_var, 0)) overhead_var
496
497 FROM wip_pac_period_balances wppb
498 WHERE wppb.pac_period_id = p_pac_period_id
499 AND wppb.cost_type_id = p_cost_type_id
500 AND wppb.cost_group_id = p_cost_group_id
501 GROUP BY wppb.wip_entity_id,
502 wppb.organization_id,
503 wppb.line_id;
504
505 l_begining_material NUMBER;
506 l_begining_material_ovhd NUMBER;
507 l_begining_resource NUMBER;
508 l_begining_overhead NUMBER;
509 l_begining_osp NUMBER;
510
511 l_prev_material_in NUMBER;
512 l_prev_material_ovhd_in NUMBER;
513 l_prev_resource_in NUMBER;
514 l_prev_overhead_in NUMBER;
515 l_prev_osp_in NUMBER;
516
517 l_prev_material_out NUMBER;
518 l_prev_material_ovhd_out NUMBER;
519 l_prev_resource_out NUMBER;
520 l_prev_overhead_out NUMBER;
521 l_prev_osp_out NUMBER;
522
523 l_prev_material_var NUMBER;
524 l_prev_material_ovhd_var NUMBER;
525 l_prev_resource_var NUMBER;
526 l_prev_overhead_var NUMBER;
527 l_prev_osp_var NUMBER;
528
529 l_prev_period_id NUMBER;
530 l_stmt_num NUMBER;
531 l_ctr NUMBER;
532
533 BEGIN
534
535 l_stmt_num := 0;
536 x_return_status := FND_API.G_RET_STS_SUCCESS;
537
538 IF l_plog THEN
539 fnd_log.string(
540 fnd_log.level_procedure,
541 l_module||'.'||l_stmt_num,
542 'Entering CST_PAC_WIP_Value_Report_PVT.Periodic_WIP_Value_Rpt_Details with '||
543 'p_init_msg_list = '||p_init_msg_list||','||
544 'p_validation_level = '||p_validation_level||','||
545 'p_report_type = '||p_report_type||','||
546 'p_pac_period_id = '||p_pac_period_id||','||
547 'p_cost_group_id = '||p_cost_group_id||','||
548 'p_legal_entity_id = '||p_legal_entity_id||','||
549 'p_cost_type_id = '||p_cost_type_id
550 );
551 END IF;
552
553 -------------------------------------------------
554 -- Get the prior PAC period Id
555 -------------------------------------------------
556 l_stmt_num := 10;
557
558 SELECT nvl(max(cpp.pac_period_id), -1)
559 INTO l_prev_period_id
560 FROM cst_pac_process_phases cppp,
561 cst_pac_periods cpp
562 WHERE cppp.pac_period_id = cpp.pac_period_id
563 AND cppp.cost_group_id = p_cost_group_id
564 AND cpp.cost_type_id = p_cost_type_id
565 AND cpp.legal_entity = p_legal_entity_id
566 AND cpp.pac_period_id < p_pac_period_id;
567
568 l_stmt_num := 20;
569 l_ctr := 0;
570
571 FOR temp_rec IN wip_pac_bal LOOP
572 ---------------------------------------------------------------------------
573 -- If prior period exist the get the prior period details from WPPB table
574 ---------------------------------------------------------------------------
575 l_begining_material := 0;
576 l_begining_material_ovhd := 0;
577 l_begining_Resource := 0;
578 l_begining_overhead := 0;
579 l_begining_osp := 0;
580
581 l_prev_material_in := 0;
582 l_prev_material_ovhd_in := 0;
583 l_prev_resource_in := 0;
584 l_prev_overhead_in := 0;
585 l_prev_osp_in := 0;
586
587 l_prev_material_out := 0;
588 l_prev_material_ovhd_out := 0;
589 l_prev_resource_out := 0;
590 l_prev_overhead_out := 0;
591 l_prev_osp_out := 0;
592
593 l_prev_material_var := 0;
594 l_prev_material_ovhd_var := 0;
595 l_prev_resource_var := 0;
596 l_prev_overhead_var := 0;
597 l_prev_osp_var := 0;
598
599 l_ctr := l_ctr + 1;
600 ---------------------------------------------------------------------------
601 -- If prior period exist the get the prior period details from WPPB table
602 ---------------------------------------------------------------------------
603 IF (l_prev_period_id <> -1 ) THEN
604
605 -------------------------------------------------------------------------------------------------------
606 -- Get the ending balance of prior period, Total incurred value and relieved value till prior period
607 -- Begining Balance in current period = Ending Balance of prior period
608 -------------------------------------------------------------------------------------------------------
609 l_stmt_num := 30;
610 SELECT
611 SUM( nvl(pl_material_in, 0) - nvl(pl_material_out, 0) - nvl(pl_material_var, 0) ),
612 SUM( nvl(pl_material_overhead_in, 0) - nvl(pl_material_overhead_out,0) - nvl(pl_material_overhead_var,0)),
613 SUM( nvl(tl_resource_in ,0) + nvl( pl_resource_in,0)
614 - nvl(tl_resource_out ,0) - nvl( pl_resource_out, 0)
615 - nvl(tl_resource_var ,0) - nvl( pl_resource_var, 0) ),
616 SUM( nvl(tl_overhead_in ,0) + nvl( pl_overhead_in, 0)
617 - nvl(tl_overhead_out ,0) - nvl( pl_overhead_out, 0)
618 - nvl(tl_overhead_var,0) - nvl( pl_overhead_var, 0) ) ,
619 SUM( nvl(tl_outside_processing_in ,0) + nvl( pl_outside_processing_in,0)
620 - nvl(tl_outside_processing_out ,0) - nvl( pl_outside_processing_out,0)
621 - nvl(tl_outside_processing_var ,0) - nvl( pl_outside_processing_var,0) ),
622 /* Total incurred value till previous period */
623 SUM ( nvl(pl_material_in, 0) ) ,
624 SUM( nvl(pl_material_overhead_in, 0)) ,
625 SUM( nvl(tl_resource_in ,0) + nvl( pl_resource_in,0) ) ,
626 SUM( nvl(tl_overhead_in ,0) + nvl( pl_overhead_in, 0) ) ,
627 SUM( nvl(tl_outside_processing_in ,0) + nvl(pl_outside_processing_in,0)),
628 /* Total relieved value till prior period */
629 SUM( nvl(pl_material_out, 0) ) ,
630 SUM( nvl(pl_material_overhead_out, 0)) ,
631 SUM( nvl(tl_resource_out ,0) + nvl( pl_resource_out,0) ) ,
632 SUM( nvl(tl_overhead_out ,0) + nvl( pl_overhead_out, 0) ) ,
633 SUM( nvl(tl_outside_processing_out ,0) + nvl(pl_outside_processing_out,0) ),
634 /* Total variance till prior period */
635 sum( nvl(pl_material_var, 0) ) material_var,
636 sum( nvl(pl_material_overhead_var , 0)) material_ovhd_var,
637 sum( nvl(tl_resource_var ,0) + nvl(pl_resource_var, 0)) resource_var,
638 sum( nvl(tl_outside_processing_var ,0) + nvl(pl_outside_processing_var,0)) osp_var,
639 sum( nvl(tl_overhead_var,0) + nvl(pl_overhead_var, 0)) overhead_var
640
641 INTO l_begining_material,
642 l_begining_material_ovhd,
643 l_begining_resource,
644 l_begining_overhead,
645 l_begining_osp,
646 l_prev_material_in,
647 l_prev_material_ovhd_in,
648 l_prev_resource_in,
649 l_prev_overhead_in,
650 l_prev_osp_in,
651 l_prev_material_out,
652 l_prev_material_ovhd_out,
653 l_prev_resource_out,
654 l_prev_overhead_out,
655 l_prev_osp_out,
656 l_prev_material_var,
657 l_prev_material_ovhd_var,
658 l_prev_resource_var,
659 l_prev_osp_var,
660 l_prev_overhead_var
661 FROM wip_pac_period_balances wppb
662 WHERE wppb.pac_period_id = l_prev_period_id
663 AND wppb.wip_entity_id = temp_rec.wip_entity_id
664 AND nvl(wppb.line_id,-99) = nvl(temp_rec.line_id,-99)
665 AND wppb.cost_type_id = p_cost_type_id;
666
667 END IF;
668
669 FOR i IN 1..5 LOOP
670
671 --------------------------------------------------------------------
672 -- Insert the values into Temporary table cst_wip_pac_period_bal_tmp
673 --------------------------------------------------------------------
674 l_stmt_num := 40;
675 INSERT INTO cst_wip_pac_period_bal_tmp (cost_group_id,
676 pac_period_id,
677 cost_type_id,
678 wip_entity_id,
679 organization_id,
680 line_id,
681 cost_element_id,
682 begining_balance,
683 costs_incurred,
684 costs_relieved,
685 ending_balance,
686 variance_amount)
687 VALUES (p_cost_group_id,
688 p_pac_period_id,
689 p_cost_type_id,
690 temp_rec.wip_entity_id,
691 temp_rec.organization_id,
692 temp_rec.line_id,
693 i, -- Cost Element
694 DECODE(i,1,DECODE(p_report_type,1,nvl(l_begining_material,0),0),
695 2,DECODE(p_report_type,1,nvl(l_begining_material_ovhd,0),0),
696 3,DECODE(p_report_type,1,nvl(l_begining_Resource,0),0),
697 4,DECODE(p_report_type,1,nvl(l_begining_osp,0),0),
698 5,DECODE(p_report_type,1,nvl(l_begining_overhead,0),0)
699 ),
700 DECODE(i,1,temp_rec.material_in - DECODE(p_report_type,1,nvl(l_prev_material_in,0),0),
701 2,temp_rec.material_ovhd_in - DECODE(p_report_type,1,nvl(l_prev_material_ovhd_in,0),0),
702 3,temp_rec.Resource_in - DECODE(p_report_type,1,nvl(l_prev_Resource_in,0),0),
703 4,temp_rec.osp_in - DECODE(p_report_type,1,nvl(l_prev_osp_in,0),0),
704 5,temp_rec.overhead_in - DECODE(p_report_type,1,nvl(l_prev_overhead_in,0),0)
705 ),
706 DECODE(i,1,temp_rec.material_out - DECODE(p_report_type,1,nvl(l_prev_material_out,0),0),
707 2,temp_rec.material_ovhd_out - DECODE(p_report_type,1,nvl(l_prev_material_ovhd_out,0),0),
708 3,temp_rec.Resource_out - DECODE(p_report_type,1,nvl(l_prev_Resource_out,0),0) ,
709 4,temp_rec.osp_out - DECODE(p_report_type,1,nvl(l_prev_osp_out,0),0),
710 5,temp_rec.overhead_out - DECODE(p_report_type,1,nvl(l_prev_overhead_out,0),0)
711 ),
712 DECODE(i,1,(temp_rec.material_in - temp_rec.material_out - temp_rec.material_var ),
713 2,(temp_rec.material_ovhd_in - temp_rec.material_ovhd_out - temp_rec.material_ovhd_var),
714 3,(temp_rec.Resource_in - temp_rec.Resource_out - temp_rec.Resource_var),
715 4,(temp_rec.osp_in - temp_rec.osp_out - temp_rec.osp_var),
716 5,(temp_rec.overhead_in - temp_rec.overhead_out - temp_rec.overhead_var)
717 ),
718 DECODE(i,1,temp_rec.material_var - DECODE(p_report_type,1,nvl(l_prev_material_var,0),0),
719 2,temp_rec.material_ovhd_var - DECODE(p_report_type,1,nvl(l_prev_material_ovhd_var,0),0),
720 3,temp_rec.Resource_var - DECODE(p_report_type,1,nvl(l_prev_Resource_var,0),0) ,
721 4,temp_rec.osp_var - DECODE(p_report_type,1,nvl(l_prev_osp_var,0),0) ,
722 5,temp_rec.overhead_var - DECODE(p_report_type,1,nvl(l_prev_overhead_var,0),0)
723 )
724 );
725 END LOOP;
726 END LOOP;
727
728 COMMIT;
729 IF l_plog THEN
730 fnd_log.string(
731 fnd_log.level_procedure,
732 l_module||'.'||l_stmt_num,
733 'Inserted '||l_ctr|| ' rows into temp table.' ||
734 'Exiting CST_PAC_WIP_Value_Report_PVT.Periodic_WIP_Value_Rpt_Details >> ');
735 END IF;
736
737 EXCEPTION
738 WHEN fnd_api.g_exc_unexpected_error THEN
739 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
740
741 IF l_exceptionlog THEN
742 fnd_msg_pub.add_exc_msg(
743 p_pkg_name => 'CST_PAC_WIP_Value_Report_PVT',
744 p_procedure_name => 'Periodic_WIP_Value_Rpt_Details',
745 p_error_text => 'An exception has occurred.'
746 );
747 IF l_uLog THEN
748 fnd_log.string(
749 fnd_log.level_exception,
750 l_module||'.'||l_stmt_num,
751 'An exception has occurred.'
752 );
753 END IF;
754 END IF;
755
756 WHEN OTHERS THEN
757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
758
759 IF l_uLog THEN
760 FND_LOG.STRING(
761 FND_LOG.LEVEL_UNEXPECTED,
762 l_module || '.' || l_stmt_num,
763 SUBSTRB (SQLERRM , 1 , 230)
764 );
765 END IF;
766
767 End Periodic_WIP_Value_Rpt_Details;
768
769 -----------------------------------------------------------------------------
770 -- PROCEDURE : Get_XMLData
771 -- DESCRIPTION : Procedure for to generare XML data for given parameter values
772 --
773 -----------------------------------------------------------------------------
774
775 PROCEDURE Get_XMLData(p_api_version IN NUMBER,
776 p_init_msg_list IN VARCHAR2,
777 p_validation_level IN NUMBER,
778 x_return_status OUT NOCOPY VARCHAR2,
779 x_msg_count OUT NOCOPY NUMBER,
780 x_msg_data OUT NOCOPY VARCHAR2,
781 p_legal_entity_id IN NUMBER,
782 p_cost_group_id IN NUMBER,
783 p_cost_type_id IN NUMBER,
784 p_pac_period_id IN NUMBER,
785 p_class_type IN VARCHAR2,
786 p_from_job IN VARCHAR2,
787 p_to_job IN VARCHAR2,
788 p_from_assembly IN VARCHAR2,
789 p_to_assembly IN VARCHAR2,
790 p_exchange_rate_char IN VARCHAR2,
791 p_currency_code IN VARCHAR2,
792 x_xml_doc IN OUT NOCOPY CLOB)
793 IS
794 l_api_name CONSTANT VARCHAR2(30) := 'Get_XMLData';
795 l_api_version CONSTANT NUMBER := 1.0;
796 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_PAC_WIP_Value_Report_PVT.Get_XMLData';
797 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
798 l_uLog CONSTANT BOOLEAN := fnd_log.TEST(fnd_log.level_unexpected, l_module) AND fnd_log.level_unexpected >= l_log_level;
799 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
800 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
801 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
802 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
803 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
804
805 l_ref_cur SYS_REFCURSOR;
806 l_ctx NUMBER;
807 l_xml_temp CLOB;
808 l_offset PLS_INTEGER;
809 l_total_rows_processed NUMBER;
810
811 l_sql_stsmt VARCHAR2(4000);
812 l_where_clause VARCHAR2(2000);
813 l_group_by_clause VARCHAR2(2000);
814
815 l_sql_stsmt1 VARCHAR2(4000);
816 l_where_clause1 VARCHAR2(2000);
817 l_group_by_clause1 VARCHAR2(2000);
818
819 l_exchange_rate NUMBER;
820 l_precision NUMBER;
821 l_period_end_date DATE;
822 l_stmt_num NUMBER;
823
824 BEGIN
825
826 l_stmt_num := 0;
827 x_return_status := FND_API.G_RET_STS_SUCCESS;
828
829 IF l_plog THEN
830 fnd_log.string(
831 fnd_log.level_procedure,
832 l_module||'.'||l_stmt_num,
833 'Entering CST_PAC_WIP_Value_Report_PVT.Get_XMLData with '||
834 'p_init_msg_list = '||p_init_msg_list||','||
835 'p_validation_level = '||p_validation_level||','||
836 'p_legal_entity_id = '||p_legal_entity_id||','||
837 'p_cost_group_id = '||p_cost_group_id||','||
838 'p_cost_type_id = '||p_cost_type_id||','||
839 'p_pac_period_id = '||p_pac_period_id||','||
840 'p_class_type = '||p_class_type||','||
841 'p_from_job = '||p_from_job||','||
842 'p_to_job = '||p_to_job||','||
843 'p_from_assembly = '||p_from_assembly||','||
844 'p_to_assembly = '||p_to_assembly||','||
845 'p_exchange_rate_char = '||p_exchange_rate_char||','||
846 'p_currency_code = '||p_currency_code
847 );
848 END IF;
849
850 l_exchange_rate := fnd_number.canonical_to_number(P_EXCHANGE_RATE_CHAR);
851
852 l_stmt_num := 5;
853 IF (p_currency_code is NOT NULL) THEN
854 SELECT fc.precision
855 INTO l_precision
856 FROM fnd_currencies fc
857 WHERE fc.currency_code = p_currency_code;
858 END IF;
859 l_stmt_num := 10;
860
861 SELECT cpp.period_end_date
862 INTO l_period_end_date
863 FROM cst_pac_periods cpp
864 WHERE cpp.pac_period_id = P_PAC_PERIOD_ID;
865
866 l_stmt_num := 20;
867
868 l_offset := 21;
869 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
870
871 -- Standard call to check for call compatibility.
872 IF NOT FND_API.Compatible_API_Call ( l_api_version,
873 p_api_version,
874 l_api_name,
875 G_PKG_NAME )
876 THEN
877 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
878 END IF;
879
880
881 -- Execute the Query
882 l_stmt_num := 140;
883 OPEN l_ref_cur FOR
884 select
885 min(ml.meaning) Class_type,
886 we.wip_entity_name,
887 mp.organization_code,
888 msik.concatenated_segments Assembly,
889 min(ml2.meaning) job_status,
890 wdj.scheduled_start_date scheduled_start_date,
891 cce.cost_element,
892 sum(round( wppb.BEGINING_BALANCE * l_exchange_rate, l_precision )) BeginingBalance,
893 sum(round( wppb.COSTS_INCURRED * l_exchange_rate, l_precision )) CostsIncurred,
894 sum(round( wppb.costs_relieved * l_exchange_rate, l_precision )) CostsRelieved,
895 sum(round( wppb.ENDING_BALANCE * l_exchange_rate, l_precision )) EndingBalance,
896 sum(round( wppb.VARIANCE_AMOUNT * l_exchange_rate, l_precision )) VarianceAmount
897 from
898 wip_entities we,
899 mfg_lookups ml,
900 cst_wip_pac_period_bal_tmp wppb,
901 wip_discrete_jobs wdj,
902 wip_accounting_classes wac,
903 mfg_lookups ml2,
904 mtl_parameters mp,
905 cst_cost_elements cce,
906 mtl_system_items_kfv msik
907 where
908 wppb.cost_group_id = P_COST_GROUP_ID
909 and wppb.pac_period_id = P_PAC_PERIOD_ID
910 and wdj.wip_entity_id = wppb.wip_entity_id
911 and wdj.organization_id = wppb.organization_id
912 and we.wip_entity_id = wdj.wip_entity_id
913 and wac.class_code = wdj.class_code
914 and wac.organization_id = wdj.organization_id
915 and ml.lookup_type = 'WIP_CLASS_TYPE_CAP'
916 and ml.lookup_code = wac.class_type
917 and msik.organization_id = wdj.organization_id
918 and msik.inventory_item_id = wdj.primary_item_id
919 and ml2.lookup_type = 'WIP_JOB_STATUS'
920 and ml2.lookup_code = wdj.status_type
921 and mp.organization_id = wppb.organization_id
922 and cce.cost_element_id = WPPB.cost_element_id
923 and ( p_class_type is null or wac.class_type = p_class_type )
924 and ( p_from_job is null or WE.WIP_ENTITY_NAME >= P_FROM_JOB )
925 and ( p_to_job is null or WE.WIP_ENTITY_NAME <= P_TO_JOB )
926 and ( p_from_assembly is null or msik.concatenated_segments >= p_from_assembly )
927 and ( p_to_assembly is null or msik.concatenated_segments <= p_to_assembly )
928 GROUP BY
929 wac.class_type,
930 wppb.wip_entity_id,
931 we.wip_entity_name,
932 mp.organization_code,
933 wdj.scheduled_start_date,
934 wdj.primary_item_id,
935 msik.concatenated_segments,
936 wdj.status_type,
937 wppb.pac_period_id,
938 wppb.cost_element_id,
939 cce.cost_element
940 UNION ALL
941 select
942 min(ml.meaning) Class_type,
943 wl.line_code wip_entity_name,
944 mp.organization_code,
945 msik.concatenated_segments Assembly,
946 decode( sign( l_period_end_date - NVL(min(NVL(wl.disable_date,l_period_end_date + 1)), l_period_end_date + 1 )),
947 1, 'Line Disabled', 'Line Open') job_status ,
948 to_date(NULL) scheduled_start_date,
949 cce.cost_element ,
950 sum(round( wppb.BEGINING_BALANCE * l_exchange_rate, l_precision )) BeginingBalance,
951 sum(round( wppb.COSTS_INCURRED * l_exchange_rate, l_precision )) CostsIncurred,
952 sum(round( wppb.costs_relieved * l_exchange_rate, l_precision )) CostsRelieved,
953 sum(round( wppb.ENDING_BALANCE * l_exchange_rate, l_precision )) EndingBalance,
954 sum(round( wppb.VARIANCE_AMOUNT * l_exchange_rate, l_precision )) VarianceAmount
955 FROM
956 wip_lines wl ,
957 mfg_lookups ml ,
958 cst_wip_pac_period_bal_tmp wppb ,
959 wip_repetitive_items wri ,
960 mtl_system_items_kfv msik ,
961 wip_accounting_classes wac ,
962 wip_entities we ,
963 mtl_parameters mp ,
964 cst_cost_elements cce
965 WHERE
966 wppb.cost_group_id = P_COST_GROUP_ID
967 and wppb.pac_period_id = P_PAC_PERIOD_ID
968 and wl.line_id = wppb.line_id
969 and wri.wip_entity_id = wppb.wip_entity_id
970 and wri.line_id = wppb.line_id
971 and we.wip_entity_id = wppb.wip_entity_id
972 and wac.class_code = wri.class_code
973 and wac.organization_id = wppb.organization_id
974 and ml.lookup_type = 'WIP_CLASS_TYPE_CAP'
975 and ml.lookup_code = wac.class_type
976 and msik.organization_id = wppb.organization_id
977 and msik.inventory_item_id = wri.primary_item_id
978 and mp.organization_id = wppb.organization_id
979 and cce.cost_element_id = WPPB.cost_element_id
980 and ( p_class_type is null or wac.class_type = p_class_type )
981 and ( p_from_job is null or WE.WIP_ENTITY_NAME >= P_FROM_JOB )
982 and ( p_to_job is null or WE.WIP_ENTITY_NAME <= P_TO_JOB )
983 and ( p_from_assembly is null or msik.concatenated_segments >= p_from_assembly )
984 and ( p_to_assembly is null or msik.concatenated_segments <= p_to_assembly )
985 GROUP BY
986 wac.class_type,
987 WPPB.wip_entity_id,
988 wl.line_code,
989 mp.organization_code,
990 wri.primary_item_id,
991 msik.concatenated_segments,
992 wppb.pac_period_id,
993 wppb.cost_element_id,
994 cce.cost_element
995 ORDER BY
996 1,2,3,4,5,6,7;
997
998
999
1000
1001 -- create new context
1002 l_stmt_num := 150;
1003 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1004
1005 DBMS_XMLGEN.setRowSetTag (l_ctx,'PACwipentites');
1006 DBMS_XMLGEN.setRowTag (l_ctx,'CostElements');
1007
1008 -- get XML
1009 l_stmt_num := 160;
1010 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
1011
1012 -- Remove the header (21 characters) and append the rest to xml output
1013 l_stmt_num := 170;
1014 l_total_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1015
1016 l_stmt_num := 180;
1017 IF ( l_total_rows_processed > 0) THEN
1018 DBMS_LOB.erase (l_xml_temp, l_offset,1);
1019 DBMS_LOB.append (x_xml_doc, l_xml_temp);
1020 END IF;
1021
1022 -- close context and free memory
1023 l_stmt_num := 190;
1024 DBMS_XMLGEN.closeContext(l_ctx);
1025 CLOSE l_ref_cur;
1026 DBMS_LOB.FREETEMPORARY (l_xml_temp);
1027
1028 l_stmt_num := 200;
1029 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
1030
1031 l_stmt_num := 210;
1032 -- create new context to get the total number rows processes
1033 open l_ref_cur FOR select l_total_rows_processed row_count from DUAL;
1034 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
1035 DBMS_XMLGEN.setRowSetTag (l_ctx,NULL);
1036 DBMS_XMLGEN.setRowTag (l_ctx,NULL);
1037
1038 l_stmt_num := 220;
1039 -- get XML
1040 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
1041
1042 -- remove the header (21 characters) and append the rest to xml output
1043 l_stmt_num := 230;
1044 l_total_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
1045
1046 -- Check the number of rows more than zero then add XML result
1047 l_stmt_num := 240;
1048 IF ( l_total_rows_processed > 0) THEN
1049 DBMS_LOB.erase (l_xml_temp, l_offset,1);
1050 DBMS_LOB.append (x_xml_doc, l_xml_temp);
1051 END IF;
1052
1053 l_stmt_num := 250;
1054 -- close context and free memory
1055 DBMS_XMLGEN.closeContext(l_ctx);
1056 CLOSE l_ref_cur;
1057 DBMS_LOB.FREETEMPORARY (l_xml_temp);
1058
1059 IF l_plog THEN
1060 fnd_log.string(
1061 fnd_log.level_procedure,
1062 l_module||'.'||l_stmt_num,
1063 'Exiting CST_PAC_WIP_Value_Report_PVT.Get_XMLData >> ');
1064 END IF;
1065
1066
1067 EXCEPTION
1068 WHEN fnd_api.g_exc_unexpected_error THEN
1069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1070
1071 IF l_exceptionlog THEN
1072 fnd_msg_pub.add_exc_msg(
1073 p_pkg_name => 'CST_PAC_WIP_Value_Report_PVT',
1074 p_procedure_name => 'Get_XMLData',
1075 p_error_text => 'An exception has occurred.'
1076 );
1077 IF l_uLog THEN
1078 fnd_log.string(
1079 fnd_log.level_exception,
1080 l_module||'.'||l_stmt_num,
1081 'An exception has occurred.'
1082 );
1083 END IF;
1084 END IF;
1085
1086 WHEN OTHERS THEN
1087 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1088
1089 IF l_uLog THEN
1090 FND_LOG.STRING(
1091 FND_LOG.LEVEL_UNEXPECTED,
1092 l_module || '.' || l_stmt_num,
1093 SUBSTRB (SQLERRM , 1 , 230)
1094 );
1095 END IF;
1096 END Get_XMLData;
1097
1098 END CST_PAC_WIP_VALUE_REPORT_PVT;