[Home] [Help]
PACKAGE BODY: APPS.CST_EAM_WORKORDERS_PVT
Source
1 PACKAGE BODY CST_eAM_WorkOrders_PVT AS
2 /* $Header: CSTPEEAB.pls 120.3.12010000.2 2008/10/30 12:15:08 svelumur ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CST_eAM_WorkOrders_PVT';
5
6 PROCEDURE Generate_XMLData (
7 errcode OUT NOCOPY VARCHAR2,
8 errno OUT NOCOPY NUMBER,
9 p_legal_entity_id IN NUMBER,
10 p_cost_type_id IN NUMBER,
11 p_cost_group_id IN NUMBER,
12 p_range IN NUMBER,
13 p_dummy1 IN NUMBER := NULL,
14 p_dummy2 IN NUMBER := NULL,
15 p_from_workorder IN VARCHAR2 := NULL,
16 p_to_workorder IN VARCHAR2 := NULL,
17 p_specific_workorder IN NUMBER := NULL)
18 IS
19 l_api_name CONSTANT VARCHAR2(30) := 'Generate_XMLData';
20 l_xml_doc CLOB;
21 l_amount NUMBER;
22 l_offset NUMBER;
23 l_length NUMBER;
24 l_buffer VARCHAR2(32767);
25
26 l_return_status VARCHAR2(1);
27 l_msg_count NUMBER;
28 l_msg_data VARCHAR2(2000);
29 l_success BOOLEAN;
30 l_stmt_num NUMBER;
31
32 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_eAM_WorkOrders_PVT.Generate_XMLData';
33 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
34 l_uLog CONSTANT BOOLEAN := fnd_log.TEST(fnd_log.level_unexpected, l_module) AND fnd_log.level_unexpected >= l_log_level;
35 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
36 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
37 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
38 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
39 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
40
41 BEGIN
42
43 IF l_plog THEN
44 fnd_log.string(
45 fnd_log.level_procedure,
46 l_module||'.'||l_stmt_num,
47 'Entering CST_eAM_WorkOrders_PVT.Generate_XMLData with '||
48 'p_legal_entity_id = '||p_legal_entity_id||','||
49 'p_cost_group_id = '||p_cost_group_id||','||
50 'p_cost_type_id = '||p_cost_type_id||','||
51 'p_range = '||p_range||','||
52 'p_from_workorder = '||p_from_workorder||','||
53 'p_to_workorder = '||p_to_workorder||','||
54 'p_specific_workorder = '||p_specific_workorder
55 );
56 END IF;
57
58 -- Initialze variables
59 l_stmt_num := 0;
60 DBMS_LOB.createtemporary(l_xml_doc, TRUE);
61
62 -- Initialize message stack
63 l_stmt_num := 10;
64 FND_MSG_PUB.initialize;
65
66 -----------------------------------------------------------------
67 -- Generate XML data for displaying the report parameters values
68 -----------------------------------------------------------------
69 l_stmt_num := 20;
70 Display_Parameters (p_api_version => 1.0,
71 p_init_msg_list => FND_API.G_FALSE,
72 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
73 x_return_status => l_return_status,
74 x_msg_count => l_msg_count,
75 x_msg_data => l_msg_data,
76 p_legal_entity_id => p_legal_entity_id,
77 p_cost_group_id => p_cost_group_id,
78 p_cost_type_id => p_cost_type_id,
79 p_range => p_range,
80 p_from_workorder => p_from_workorder,
81 p_to_workorder => p_to_workorder,
82 p_specific_workorder => p_specific_workorder,
83 x_xml_doc => l_xml_doc);
84
85 -- If return status is not success, add message to the log
86 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
87 l_msg_data := 'Failed generating XML data for report parameter information';
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END IF;
90
91 ------------------------------------------------------------------------------------
92 -- Generate Estimation, Actuals data for workoder for given Cost Type and Cost Group
93 ------------------------------------------------------------------------------------
94 l_stmt_num := 30;
95 eAM_Est_Actual_details (p_api_version => 1.0,
96 p_init_msg_list => FND_API.G_FALSE,
97 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
98 x_return_status => l_return_status,
99 x_msg_count => l_msg_count,
100 x_msg_data => l_msg_data,
101 p_legal_entity_id => p_legal_entity_id,
102 p_cost_group_id => p_cost_group_id,
103 p_cost_type_id => p_cost_type_id,
104 p_range => p_range,
105 p_from_workorder => p_from_workorder,
106 p_to_workorder => p_to_workorder,
107 p_specific_workorder => p_specific_workorder,
108 x_xml_doc => l_xml_doc);
109
110 -- If return status is not success, add message to the log
111 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
112 l_msg_data := 'Failed generating Workorder Estimation, Actuals details';
113 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
114 END IF;
115
116 -- Append the XML end tag
117 l_stmt_num := 40;
118 DBMS_LOB.writeappend (l_xml_doc, 9, '</REPORT>');
119
120 -- Get length of the CLOB l_xml_doc
121 l_stmt_num := 50;
122 l_length := nvl(DBMS_LOB.getlength(l_xml_doc), 0);
123 l_offset := 1;
124 l_amount := 16383;
125
126 -- Loop until the length of CLOB data is zero
127 l_stmt_num := 60;
128 LOOP
129 EXIT WHEN l_length <= 0;
130 DBMS_LOB.read (l_xml_doc, l_amount, l_offset, l_buffer);
131 FND_FILE.PUT (FND_FILE.OUTPUT, l_buffer);
132 l_length := l_length - l_amount;
133 l_offset := l_offset + l_amount;
134 END LOOP;
135
136 -- free temporary memory
137 l_stmt_num := 70;
138 DBMS_LOB.FREETEMPORARY (l_xml_doc);
139
140 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL', 'Request Completed Successfully');
141
142
143 IF l_plog THEN
144 fnd_log.string(
145 fnd_log.level_procedure,
146 l_module||'.'||l_stmt_num,
147 'Exiting CST_eAM_WorkOrders_PVT.Generate_XMLData with '||
148 'errno = '||errno
149 );
150 END IF;
151
152 EXCEPTION
153 WHEN fnd_api.g_exc_unexpected_error THEN
154 -- Set return status to error
155 l_msg_data := SUBSTRB (SQLERRM,1,240);
156 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
157
158 IF l_exceptionlog THEN
159 fnd_msg_pub.add_exc_msg(
160 p_pkg_name => 'CST_eAM_WorkOrders_PVT',
161 p_procedure_name => 'Generate_XMLData',
162 p_error_text => 'An exception has occurred.'
163 );
164 fnd_log.string(
165 fnd_log.level_exception,
166 l_module||'.'||l_stmt_num,
167 'An exception has occurred.'
168 );
169 END IF;
170 WHEN OTHERS THEN
171
172 l_msg_data := SUBSTRB (SQLERRM,1,240);
173 l_success := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR', l_msg_data);
174
175 IF l_uLog THEN
176 fnd_message.set_name('BOM','CST_UNEXPECTED');
177 fnd_message.set_token('SQLERRM',SQLERRM);
178 fnd_msg_pub.add;
179 fnd_log.message(
180 fnd_log.level_unexpected,
181 l_module||'.'||l_stmt_num,
182 FALSE
183 );
184 END IF;
185 END Generate_XMLData;
186
187 PROCEDURE Display_Parameters(p_api_version IN NUMBER,
188 p_init_msg_list IN VARCHAR2,
189 p_validation_level IN NUMBER,
190 x_return_status OUT NOCOPY VARCHAR2,
191 x_msg_count OUT NOCOPY NUMBER,
192 x_msg_data OUT NOCOPY VARCHAR2,
193 p_legal_entity_id IN NUMBER,
194 p_cost_group_id IN NUMBER,
195 p_cost_type_id IN NUMBER,
196 p_range IN NUMBER,
197 p_from_workorder IN VARCHAR2,
198 p_to_workorder IN VARCHAR2,
199 p_specific_workorder IN NUMBER,
200 x_xml_doc IN OUT NOCOPY CLOB)
201 IS
202 l_api_name CONSTANT VARCHAR2(30) := 'Display_Parameters';
203 l_api_version CONSTANT NUMBER := 1.0;
204 l_ref_cur SYS_REFCURSOR;
205 l_ctx NUMBER;
206 l_xml_temp CLOB;
207 l_offset PLS_INTEGER;
208 l_wip_entity_name VARCHAR2(240);
209 l_stmt_num NUMBER;
210
211 l_legal_entity VARCHAR2(240);
212 l_cost_type VARCHAR2(10);
213 l_cost_group VARCHAR2(10);
214
215 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_eAM_WorkOrders_PVT.Display_Parameters';
216 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
217 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
218 fnd_log.TEST(fnd_log.level_unexpected, l_module);
219 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
220 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
221 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
222 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
223 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
224 /*Bug 7305146*/
225 l_encoding VARCHAR2(20);
226 l_xml_header VARCHAR2(100);
227
228 BEGIN
229 IF l_plog THEN
230 fnd_log.string(
231 fnd_log.level_procedure,
232 l_module||'.'||l_stmt_num,
233 'Entering CST_eAM_WorkOrders_PVT.Display_Parameters with '||
234 'p_init_msg_list = '||p_init_msg_list||','||
235 'p_validation_level = '||p_validation_level||','||
236 'p_legal_entity_id = '||p_legal_entity_id||','||
237 'p_cost_group_id = '||p_cost_group_id||','||
238 'p_cost_type_id = '||p_cost_type_id||','||
239 'p_range = '||p_range||','||
240 'p_from_workorder = '||p_from_workorder||','||
241 'p_to_workorder = '||p_to_workorder||','||
242 'p_specific_workorder = '||p_specific_workorder
243 );
244 END IF;
245
246 l_stmt_num := 0;
247 IF NOT FND_API.Compatible_API_Call (l_api_version,
248 p_api_version,
249 l_api_name,
250 G_PKG_NAME )
251 THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254
255 -- Initialize API return status to success
256 l_stmt_num := 10;
257 x_return_status := FND_API.G_RET_STS_SUCCESS;
258
259 -- Create CLOB object to store the XML data
260 l_stmt_num := 20;
261 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
262
263 -- If it is Specific WorkOrder then get the WP entity Name
264 IF (p_range = 2) THEN
265 l_stmt_num := 30;
266 SELECT wip_entity_name
267 INTO l_wip_entity_name
268 FROM wip_entities
269 WHERE wip_entity_id = p_specific_workorder;
270 END IF;
271
272 l_stmt_num := 30;
273 -- Get Legal Entity Name
274 SELECT xle.name
275 INTO l_legal_entity
276 FROM xle_firstparty_information_v xle
277 WHERE xle.legal_entity_id = p_legal_entity_id;
278
279 l_stmt_num := 40;
280 -- Get PAC Cost Type Name
281 SELECT cct.COST_TYPE
282 INTO l_cost_type
283 FROM cst_cost_types cct
284 WHERE cct.cost_type_id = p_cost_type_id;
285
286 l_stmt_num := 50;
287 -- Get PAC Cost group Name
288 SELECT ccg.cost_group
289 INTO l_cost_group
290 FROM cst_cost_groups ccg
291 WHERE ccg.cost_group_id = p_cost_group_id
292 AND NVL(ccg.cost_group_type,1) = 2;
293
294
295 -- Get the report parameter value for displaying in the report
296 l_stmt_num := 60;
297 OPEN l_ref_cur FOR
298 'SELECT
299 :l_legal_entity NAME,
300 :l_cost_type COST_TYPE,
301 :l_cost_group COST_GROUP,
302 LU.meaning RANGE,
303 :p_from_workorder FROM_WO,
304 :p_to_workorder TO_WO,
305 :l_wip_entity_name ENTITY
306 FROM mfg_lookups LU
307 WHERE LU.lookup_type = ''CST_PAC_EAM_JOB_OPTION''
308 AND LU.lookup_code = :p_range'
309 USING l_legal_entity, l_cost_type, l_cost_group,
310 p_from_workorder, p_to_workorder, l_wip_entity_name, p_range;
311
312 -- create a new context with the SQL query
313 l_stmt_num := 50;
314 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
315 DBMS_XMLGEN.setRowSetTag (l_ctx,'REPORTPARAMETERS');
316 DBMS_XMLGEN.setRowTag (l_ctx,NULL);
317
318 -- generate XML data
319 l_stmt_num := 70;
320 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
321
322 l_stmt_num := 80;
323 l_offset := DBMS_LOB.instr (lob_loc => l_xml_temp,
324 pattern => '>',
325 offset => 1,
326 nth => 1);
327
328 /*Bug 7305146*/
329 /*-- Copy XML header part to the destination XML doc
330 l_stmt_num := 90;
331 DBMS_LOB.copy (x_xml_doc, l_xml_temp, l_offset + 1);*/
332
333 -- Remove the header
334 l_stmt_num := 90;
335 DBMS_LOB.erase (l_xml_temp, l_offset,1);
336
337 l_stmt_num := 100;
338 /*The following 3 lines of code ensures that XML data generated here uses the right encoding*/
339 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
340 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
341 DBMS_LOB.writeappend (x_xml_doc, length(l_xml_header), l_xml_header);
342
343 -- append the REPORT tag to XML
344 l_stmt_num := 110;
345 DBMS_LOB.writeappend (X_xml_doc, 8, '<REPORT>');
346
347 -- Append the rest to xml output
348 l_stmt_num := 120;
349 DBMS_LOB.append (x_xml_doc, l_xml_temp);
350
351 /* close context and free memory */
352 l_stmt_num := 130;
353 DBMS_XMLGEN.closeContext(l_ctx);
357 IF l_plog THEN
354 CLOSE l_ref_cur;
355 DBMS_LOB.FREETEMPORARY (l_xml_temp);
356
358 fnd_log.string(
359 fnd_log.level_procedure,
360 l_module||'.'||l_stmt_num,
361 'Exiting CST_eAM_WorkOrders_PVT.Display_Parameters with '||
362 'x_return_status = '||x_return_status||','||
363 'x_msg_count = '||x_msg_count||','||
364 'x_msg_data = '||x_msg_data
365 );
366 END IF;
367 EXCEPTION
368 WHEN fnd_api.g_exc_unexpected_error THEN
369 IF l_exceptionlog THEN
370 fnd_msg_pub.add_exc_msg(
371 p_pkg_name => 'CST_eAM_WorkOrders_PVT',
372 p_procedure_name => 'Display_Parameters',
373 p_error_text => 'An exception has occurred.'
374 );
375 fnd_log.string(
376 fnd_log.level_exception,
377 l_module||'.'||l_stmt_num,
378 'An exception has occurred.'
379 );
380 END IF;
381 WHEN OTHERS THEN
382 IF l_uLog THEN
383 fnd_message.set_name('BOM','CST_UNEXPECTED');
384 fnd_message.set_token('SQLERRM',SQLERRM);
385 fnd_msg_pub.add;
386 fnd_log.message(
387 fnd_log.level_unexpected,
388 l_module||'.'||l_stmt_num,
389 FALSE
390 );
391 END IF;
392 END Display_Parameters;
393
394 PROCEDURE eAM_Est_Actual_details(p_api_version IN NUMBER,
395 p_init_msg_list IN VARCHAR2,
396 p_validation_level IN NUMBER,
397 x_return_status OUT NOCOPY VARCHAR2,
398 x_msg_count OUT NOCOPY NUMBER,
399 x_msg_data OUT NOCOPY VARCHAR2,
400 p_legal_entity_id IN NUMBER,
401 p_cost_group_id IN NUMBER,
402 p_cost_type_id IN NUMBER,
403 p_range IN NUMBER,
404 p_from_workorder IN VARCHAR2,
405 p_to_workorder IN VARCHAR2,
406 p_specific_workorder IN NUMBER,
407 x_xml_doc IN OUT NOCOPY CLOB)
408 IS
409 l_api_name CONSTANT VARCHAR2(30) := 'eAM_Est_Actual_details';
410 l_api_version CONSTANT NUMBER := 1.0;
411 l_ref_cur SYS_REFCURSOR;
412 l_ctx NUMBER;
413 l_xml_temp CLOB;
414 l_offset PLS_INTEGER;
415 l_total_rows_processed NUMBER;
416 l_stmt_num NUMBER;
417
418 l_module CONSTANT VARCHAR2(90) := 'cst.plsql.CST_eAM_WorkOrders_PVT.eAM_Est_Actual_details';
419 l_log_level CONSTANT NUMBER := fnd_log.G_CURRENT_RUNTIME_LEVEL;
420 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= l_log_level AND
421 fnd_log.TEST(fnd_log.level_unexpected, l_module);
422 l_errorLog CONSTANT BOOLEAN := l_uLog AND fnd_log.level_error >= l_log_level;
423 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND fnd_log.level_exception >= l_log_level;
424 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND fnd_log.level_event >= l_log_level;
425 l_pLog CONSTANT BOOLEAN := l_eventLog AND fnd_log.level_procedure >= l_log_level;
426 l_sLog CONSTANT BOOLEAN := l_pLog AND fnd_log.level_statement >= l_log_level;
427
428 BEGIN
429 l_stmt_num := 0;
430 IF l_plog THEN
431 fnd_log.string(
432 fnd_log.level_procedure,
433 l_module||'.'||l_stmt_num,
434 'Entering CST_eAM_WorkOrders_PVT.eAM_Est_Actual_details with '||
435 'p_init_msg_list = '||p_init_msg_list||','||
436 'p_validation_level = '||p_validation_level||','||
437 'p_legal_entity_id = '||p_legal_entity_id||','||
438 'p_cost_group_id = '||p_cost_group_id||','||
439 'p_cost_type_id = '||p_cost_type_id||','||
440 'p_range = '||p_range||','||
441 'p_from_workorder = '||p_from_workorder||','||
442 'p_to_workorder = '||p_to_workorder||','||
443 'p_specific_workorder = '||p_specific_workorder
444 );
445 END IF;
446
447 -- Initialize API return status to success
448 l_stmt_num := 10;
449 x_return_status := FND_API.G_RET_STS_SUCCESS;
450
451 --Create the CLOB object to store XML data
452 l_stmt_num := 15;
453 l_offset := 21;
454 DBMS_LOB.createtemporary(l_xml_temp, TRUE);
455
456 -- Standard call to check for call compatibility.
457 l_stmt_num := 20;
458 IF NOT FND_API.Compatible_API_Call ( l_api_version,
459 p_api_version,
460 l_api_name,
461 G_PKG_NAME )
462 THEN
463 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
464 END IF;
465
466 /* 1 - ALL , 2 - Specific 3 - Range */
467 l_stmt_num := 30;
468 IF (p_range = 1) THEN
469
470 OPEN l_ref_cur FOR '
471 select we.WIP_ENTITY_NAME ENTITY_NAME,
472 mp.organization_code ORGANIZATION,
473 lu1.meaning CATEGORY,
477 sum(nvl(wpepb.system_estimated_lab_cost,0)) EST_LABR_COST,
474 bd.department_code DEPARTMENT,
475 wpepb.operation_seq_num OPERATION_SEQ,
476 sum(nvl(wpepb.system_estimated_mat_cost,0)) EST_MATL_COST,
478 sum(nvl(wpepb.system_estimated_eqp_cost,0)) EST_EQUIP_COST,
479 sum(nvl(wpepb.actual_mat_cost,0)) ACT_MATL_COST,
480 sum(nvl(wpepb.actual_lab_cost,0)) ACT_LABR_COST,
481 sum(nvl(wpepb.actual_eqp_cost,0)) ACT_EQUIP_COST,
482 sum(nvl(wpepb.system_estimated_mat_cost,0)) - sum(nvl(wpepb.actual_mat_cost,0)) VAR_MATL_COST,
483 sum(nvl(wpepb.system_estimated_lab_cost,0)) - sum(nvl(wpepb.actual_lab_cost,0)) VAR_LABR_COST,
484 sum(nvl(wpepb.system_estimated_eqp_cost,0)) - sum(nvl(wpepb.actual_eqp_cost,0)) VAR_EQUIP_COST
485 FROM CST_PAC_EAM_PERIOD_BALANCES WPEPB,
486 MTL_PARAMETERS mp,
487 WIP_ENTITIES we,
488 BOM_DEPARTMENTS bd,
489 MFG_LOOKUPS lu1
490
491 Where wpepb.legal_entity_id = :p_legal_entity_id
492 and wpepb.cost_group_id = :p_cost_group_id
493 and wpepb.Cost_type_id = :p_cost_type_id
494 and wpepb.wip_entity_id = we.wip_entity_id
495 and wpepb.organization_id = mp.organization_id
496 and we.organization_id = mp.organization_id
497 and we.ENTITY_TYPE in (6,7)
498 and bd.department_id = wpepb.owning_dept_id
499 and bd.organization_id = wpepb.organization_id
500 and lu1.lookup_type = ''BOM_EAM_COST_CATEGORY''
501 and lu1.lookup_code = wpepb.maint_cost_category
502 group by we.WIP_ENTITY_NAME,
503 mp.organization_code,
504 lu1.meaning,
505 bd.department_code,
506 wpepb.operation_seq_num' using p_legal_entity_id, p_cost_group_id, p_cost_type_id ;
507
508 ELSIF (p_range = 2) THEN
509 l_stmt_num := 40;
510 OPEN l_ref_cur FOR '
511 select we.WIP_ENTITY_NAME ENTITY_NAME,
512 mp.organization_code ORGANIZATION,
513 lu1.meaning CATEGORY,
514 bd.department_code DEPARTMENT,
515 wpepb.operation_seq_num OPERATION_SEQ,
516 sum(nvl(wpepb.system_estimated_mat_cost,0)) EST_MATL_COST,
517 sum(nvl(wpepb.system_estimated_lab_cost,0)) EST_LABR_COST,
518 sum(nvl(wpepb.system_estimated_eqp_cost,0)) EST_EQUIP_COST,
519 sum(nvl(wpepb.actual_mat_cost,0)) ACT_MATL_COST,
520 sum(nvl(wpepb.actual_lab_cost,0)) ACT_LABR_COST,
521 sum(nvl(wpepb.actual_eqp_cost,0)) ACT_EQUIP_COST,
522 sum(nvl(wpepb.system_estimated_mat_cost,0)) - sum(nvl(wpepb.actual_mat_cost,0)) VAR_MATL_COST,
523 sum(nvl(wpepb.system_estimated_lab_cost,0)) - sum(nvl(wpepb.actual_lab_cost,0)) VAR_LABR_COST,
524 sum(nvl(wpepb.system_estimated_eqp_cost,0)) - sum(nvl(wpepb.actual_eqp_cost,0)) VAR_EQUIP_COST
525
526 FROM CST_PAC_EAM_PERIOD_BALANCES WPEPB,
527 MTL_PARAMETERS mp,
528 WIP_ENTITIES we,
529 BOM_DEPARTMENTS bd,
530 MFG_LOOKUPS lu1
531
532 Where wpepb.legal_entity_id = :p_legal_entity_id
533 and wpepb.cost_group_id = :p_cost_group_id
534 and wpepb.Cost_type_id = :p_cost_type_id
535 and wpepb.wip_entity_id = we.wip_entity_id
536 and we.wip_entity_id = :p_specific_workorder
537 /* 1 - ALL , 2 - Specific 3 - Range */
538 and wpepb.organization_id = mp.organization_id
539 and we.organization_id = mp.organization_id
540 and we.ENTITY_TYPE in (6,7)
541 and bd.department_id = wpepb.owning_dept_id
542 and bd.organization_id = wpepb.organization_id
543 and lu1.lookup_type = ''BOM_EAM_COST_CATEGORY''
544 and lu1.lookup_code = wpepb.maint_cost_category
545 group by we.WIP_ENTITY_NAME,
546 mp.organization_code,
547 lu1.meaning,
548 bd.department_code,
549 wpepb.operation_seq_num' using p_legal_entity_id, p_cost_group_id, p_cost_type_id, p_specific_workorder ;
550
551 ELSE
552 l_stmt_num := 50;
553 OPEN l_ref_cur FOR '
554 SELECT we.WIP_ENTITY_NAME ENTITY_NAME,
555 mp.organization_code ORGANIZATION,
556 lu1.meaning CATEGORY,
557 bd.department_code DEPARTMENT,
558 wpepb.operation_seq_num OPERATION_SEQ,
559 sum(nvl(wpepb.system_estimated_mat_cost,0)) EST_MATL_COST,
560 sum(nvl(wpepb.system_estimated_lab_cost,0)) EST_LABR_COST,
561 sum(nvl(wpepb.system_estimated_eqp_cost,0)) EST_EQUIP_COST,
562 sum(nvl(wpepb.actual_mat_cost,0)) ACT_MATL_COST,
563 sum(nvl(wpepb.actual_lab_cost,0)) ACT_LABR_COST,
564 sum(nvl(wpepb.actual_eqp_cost,0)) ACT_EQUIP_COST,
565 sum(nvl(wpepb.system_estimated_mat_cost,0)) - sum(nvl(wpepb.actual_mat_cost,0)) VAR_MATL_COST,
566 sum(nvl(wpepb.system_estimated_lab_cost,0)) - sum(nvl(wpepb.actual_lab_cost,0)) VAR_LABR_COST,
567 sum(nvl(wpepb.system_estimated_eqp_cost,0)) - sum(nvl(wpepb.actual_eqp_cost,0)) VAR_EQUIP_COST
568
569 FROM CST_PAC_EAM_PERIOD_BALANCES WPEPB,
570 MTL_PARAMETERS mp,
571 WIP_ENTITIES we,
572 BOM_DEPARTMENTS bd,
573 MFG_LOOKUPS lu1
574
575 Where wpepb.legal_entity_id = :p_legal_entity_id
576 and wpepb.cost_group_id = :p_cost_group_id
577 and wpepb.Cost_type_id = :p_cost_type_id
578 and wpepb.wip_entity_id = we.wip_entity_id
579 and we.wip_entity_name between :p_from_workorder
580 AND :p_to_workorder
581 and wpepb.organization_id = mp.organization_id
582 and we.organization_id = mp.organization_id
583 and we.ENTITY_TYPE in (6,7)
584 and bd.department_id = wpepb.owning_dept_id
585 and bd.organization_id = wpepb.organization_id
586 and lu1.lookup_type = ''BOM_EAM_COST_CATEGORY''
587 and lu1.lookup_code = wpepb.maint_cost_category
588 group by we.WIP_ENTITY_NAME,
589 mp.organization_code,
590 lu1.meaning,
591 bd.department_code,
592 wpepb.operation_seq_num' using p_legal_entity_id, p_cost_group_id, p_cost_type_id, p_from_workorder, p_to_workorder ;
593 END IF;
594
595 -- create a new context with the SQL query
596 l_stmt_num := 60;
597 l_ctx := DBMS_XMLGEN.newContext (l_ref_cur);
598
599 -- Set the row tag and rowset tag
600 l_stmt_num := 70;
601 DBMS_XMLGEN.setRowSetTag (l_ctx,'eAMWorkOrders');
602 DBMS_XMLGEN.setRowTag (l_ctx,'eAMWorkOrderopSeq');
603
604 -- generate XML data
605 l_stmt_num := 80;
606 DBMS_XMLGEN.getXML (l_ctx, l_xml_temp, DBMS_XMLGEN.none);
607
608 -- remove the header (21 characters) and append the rest to xml output
609 l_stmt_num := 90;
610 l_total_rows_processed := DBMS_XMLGEN.getNumRowsProcessed(l_ctx);
611 IF ( l_total_rows_processed > 0) THEN
612 DBMS_LOB.erase (l_xml_temp, l_offset,1);
613 DBMS_LOB.append (x_xml_doc, l_xml_temp);
614 END IF;
615
616 -- close context and free memory
617 l_stmt_num := 100;
618 DBMS_XMLGEN.closeContext(l_ctx);
619 CLOSE l_ref_cur;
620 DBMS_LOB.FREETEMPORARY (l_xml_temp);
621
622
623 -- Add tag ROW_COUNT and total number rows
624 l_stmt_num := 110;
625 DBMS_LOB.writeappend (X_xml_doc, 11, '<ROW_COUNT>');
626 DBMS_LOB.writeappend (X_xml_doc, 10, '<TOT_ROWS>');
627 DBMS_LOB.writeappend (X_xml_doc, length (to_char(l_total_rows_processed)), to_char(l_total_rows_processed));
628 DBMS_LOB.writeappend (X_xml_doc, 11, '</TOT_ROWS>');
629 DBMS_LOB.writeappend (X_xml_doc, 12, '</ROW_COUNT>');
630
631 l_stmt_num := 120;
632 IF l_plog THEN
633 fnd_log.string(
634 fnd_log.level_procedure,
635 l_module||'.'||l_stmt_num,
636 'Exiting CST_eAM_WorkOrders_PVT.eAM_Est_Actual_details with '||
637 'x_return_status = '||x_return_status||','||
638 'x_msg_count = '||x_msg_count||','||
639 'x_msg_data = '||x_msg_data
640 );
641 END IF;
642
643 EXCEPTION
644 WHEN fnd_api.g_exc_unexpected_error THEN
645 IF l_exceptionlog THEN
646 fnd_msg_pub.add_exc_msg(
647 p_pkg_name => 'CST_eAM_WorkOrders_PVT',
648 p_procedure_name => 'eAM_Est_Actual_details',
649 p_error_text => 'An exception has occurred.'
650 );
651 fnd_log.string(
652 fnd_log.level_exception,
653 l_module||'.'||l_stmt_num,
654 'An exception has occurred.'
655 );
656 END IF;
657 WHEN OTHERS THEN
658 IF l_uLog THEN
659 fnd_message.set_name('BOM','CST_UNEXPECTED');
660 fnd_message.set_token('SQLERRM',SQLERRM);
661 fnd_msg_pub.add;
662 fnd_log.message(
663 fnd_log.level_unexpected,
664 l_module||'.'||l_stmt_num,
665 FALSE
666 );
667 END IF;
668 END eAM_Est_Actual_details;
669
670 END CST_eAM_WorkOrders_PVT;