[Home] [Help]
PACKAGE BODY: APPS.CSTPECEP
Source
1 PACKAGE BODY CSTPECEP AS
2 /* $Header: CSTECEPB.pls 120.5.12020000.2 2012/07/11 12:52:43 vkatakam ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CSTPECEP';
5
6 /*---------------------------------------------------------------------------*
7 | PUBLIC PROCEDURE |
8 | estimate_wip_jobs |
9 | |
10 | p_job_otion : |
11 | 1: All Jobs |
12 | 2: Specific job |
13 | 3: All Jobs for an asset |
14 | 4: All Jobs for an department |
15 | |
16 | Estimation Status : |
17 | NULL,1: Pending |
18 | -ve : Running |
19 | 3: Error |
20 | 7: Complete |
21 | |
22 | PARAMETERS |
23 | p_organization_id |
24 | p_entity_type |
25 | p_job_option |
26 | p_wip_entity_id |
27 | p_inventory_item_id |
28 | p_asset_number |
29 | p_owning_department_id |
30 | |
31 *----------------------------------------------------------------------------*/
32
33 PROCEDURE estimate_wip_jobs(
34 errbuf OUT NOCOPY VARCHAR2,
35 retcode OUT NOCOPY NUMBER,
36 p_organization_id IN NUMBER,
37 p_entity_type IN NUMBER DEFAULT 6,
38 p_job_option IN NUMBER DEFAULT 1,
39 p_item_dummy IN NUMBER DEFAULT NULL,
40 p_job_dummy IN NUMBER DEFAULT NULL,
41 p_owning_department_dummy IN NUMBER DEFAULT NULL,
42 p_wip_entity_id IN NUMBER DEFAULT NULL,
43 p_inventory_item_id IN NUMBER DEFAULT NULL,
44 p_asset_number IN VARCHAR2 DEFAULT NULL,
45 p_owning_department_id IN NUMBER DEFAULT NULL
46 )
47 IS
48
49 l_dummy NUMBER;
50 l_debug VARCHAR2(80);
51 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
52 l_msg_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
53 l_msg_count NUMBER := 0;
54 l_msg_data VARCHAR2(8000) := '';
55
56 l_err_num NUMBER := 0;
57 l_err_code VARCHAR2(240) := '';
58 l_err_msg VARCHAR2(240) := '';
59
60 l_stmt_num NUMBER := 0;
61 l_request_id NUMBER := 0;
62 l_user_id NUMBER := 0;
63 l_prog_id NUMBER := 0;
64 l_prog_app_id NUMBER := 0;
65 l_login_id NUMBER := 0;
66 l_conc_program_id NUMBER := 0;
67
68 l_estimation_group_id NUMBER := 0;
69 l_current_wip_id NUMBER := 0;
70 /*l_update_wip_job_flag NUMBER := 1;*/
71
72 conc_status BOOLEAN;
73 /*cst_process_error EXCEPTION;*/
74 process_error EXCEPTION;
75
76 l_entity_id_tab CSTPECEP.wip_entity_id_type;
77 l_maint_organization_id NUMBER;
78
79 BEGIN
80
81 ---------------------------------------------------------------------
82 -- Initializing Variables
83 ---------------------------------------------------------------------
84 l_err_num := 0;
85 l_err_code := '';
86 l_err_msg := '';
87
88 l_request_id := 0;
89 l_user_id := 0;
90 l_prog_id := 0;
91 l_prog_app_id := 0;
92 l_login_id := 0;
93
94 ----------------------------------------------------------------------
95 -- retrieving concurrent program information
96 ----------------------------------------------------------------------
97 l_stmt_num := 5;
98
99 l_request_id := FND_GLOBAL.conc_request_id;
100 l_user_id := FND_GLOBAL.user_id;
101 l_prog_id := FND_GLOBAL.conc_program_id;
102 l_prog_app_id := FND_GLOBAL.prog_appl_id;
103 l_login_id := FND_GLOBAL.conc_login_id;
104 l_conc_program_id := FND_GLOBAL.conc_program_id;
105
106 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
107
108 l_stmt_num := 10;
109
110 IF l_debug = 'Y' THEN
111 FND_FILE.PUT_LINE(FND_FILE.LOG, 'request_id: '
112 ||to_char(l_request_id));
113 FND_FILE.PUT_LINE(FND_FILE.LOG, 'prog_appl_id: '
114 ||to_char(l_prog_app_id));
115 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_user_id: '
116 ||to_char(l_user_id));
117 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_program_id: '
118 ||to_char(l_prog_id));
119 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_login_id: '
120 ||to_char(l_login_id));
121
122 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_conc_program_id: '
123 ||to_char(l_conc_program_id));
124
125 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Debug: '
126 ||l_debug);
127
128
129 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
130
131 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Organization: '
132 ||TO_CHAR(p_organization_id));
133
134 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Job Option: '
135 ||TO_CHAR(p_job_option));
136
137 FND_FILE.PUT_LINE(FND_FILE.LOG, 'WIP Entity Id: '
138 ||TO_CHAR(p_wip_entity_id));
139
140 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inventory Item Id: '
141 ||TO_CHAR(p_inventory_item_id));
142
143 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Asset Number: '
144 ||p_asset_number);
145
146 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Owning Dept Id: '
147 ||TO_CHAR(p_owning_department_id));
148
149 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
150
151 END IF;
152
153 l_stmt_num := 15;
154
155 IF ((p_job_option = 2 AND p_wip_entity_id IS NULL) OR
156 (p_job_option = 3 AND p_inventory_item_id IS NULL) OR
157 (p_job_option = 4 AND p_owning_department_id IS NULL) OR
158 (p_entity_type NOT IN (1,6)))
159 THEN
160
161 l_err_code := 'Invalid Program Argument Combination';
162
163 l_err_num := 2002;
164 l_err_msg := 'CSTPECEP.estimate_wip_jobs('
165 || to_char(l_stmt_num)
166 || '): '
167 ||l_err_code;
168 IF l_debug = 'Y' THEN
169 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
170 END IF;
171 CONC_STATUS := FND_CONCURRENT.
172 SET_COMPLETION_STATUS('ERROR',l_err_msg);
173 ELSE
174
175 l_stmt_num := 17;
176
177 SELECT -1 * cst_wip_cost_estimates_s.nextval
178 INTO l_estimation_group_id
179 FROM DUAL;
180
181 /* Select Maintenance organization id for this org. The work order will be created
182 in maintenance org only. That would mean that all wip table will store the
183 WO details against the maintenance organization id */
184 select maint_organization_id
185 into l_maint_organization_id
186 from mtl_parameters where organization_id = p_organization_id; /* Bug 5203079*/
187
188 l_stmt_num := 20;
189
190 IF p_job_option = 1 THEN
191
192 UPDATE wip_discrete_jobs wdj -- job_option 1
193 SET wdj.estimation_status = l_estimation_group_id,
194 wdj.last_update_date = SYSDATE,
195 wdj.last_updated_by = l_user_id,
196 wdj.request_id = l_request_id
197 WHERE WDJ.organization_id = l_maint_organization_id
198 AND NVL(WDJ.estimation_status,1) <> 7
199 AND NVL(WDJ.estimation_status,1) > 0
200 AND WDJ.status_type IN (1,3,4,6,17)
201 AND p_job_option = 1
202 AND p_entity_type IN (1,6)
203 AND EXISTS ( SELECT 'X'
204 FROM wip_entities we
205 WHERE we.wip_entity_id = wdj.wip_entity_id
206 AND we.entity_type = p_entity_type
207 )RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
208
209 ELSIF p_job_option = 2 THEN
210
211 UPDATE wip_discrete_jobs wdj -- job_option 2
212 SET wdj.estimation_status = l_estimation_group_id,
213 wdj.last_update_date = SYSDATE,
214 wdj.last_updated_by = l_user_id,
215 wdj.request_id = l_request_id
216 WHERE WDJ.organization_id = l_maint_organization_id
217 AND NVL(WDJ.estimation_status,1) <> 7
218 AND NVL(WDJ.estimation_status,1) > 0
219 AND WDJ.status_type IN (1,3,4,6,17)
220 AND p_job_option = 2
221 AND WDJ.wip_entity_id = p_wip_entity_id
222 RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
223
224 ELSIF p_job_option = 3 AND p_entity_type=1 THEN
225
226 UPDATE wip_discrete_jobs wdj -- option 3 entity_type 1, primary_item_id
227 SET wdj.estimation_status = l_estimation_group_id,
228 wdj.last_update_date = SYSDATE,
229 wdj.last_updated_by = l_user_id,
230 wdj.request_id = l_request_id
231 WHERE WDJ.organization_id = l_maint_organization_id
232 AND NVL(wdj.estimation_status,1) <> 7
233 AND NVL(wdj.estimation_status,1) > 0
234 AND WDJ.status_type IN (1,3,4,6,17)
235 AND p_job_option = 3
236 AND WDJ.primary_item_id = p_inventory_item_id
237 AND p_entity_type = 1
238 AND EXISTS ( SELECT 'X'
239 FROM wip_entities WE
240 WHERE WE.wip_entity_id = WDJ.wip_entity_id
241 AND WE.entity_type = p_entity_type
242 )
243 RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
244
245 ELSIF p_job_option = 3 AND p_entity_type=6 THEN
246
247 UPDATE wip_discrete_jobs wdj -- job_option 3 entity_type 6
248 SET wdj.estimation_status = l_estimation_group_id,
249 wdj.last_update_date = SYSDATE,
250 wdj.last_updated_by = l_user_id,
251 wdj.request_id = l_request_id
252 WHERE wdj.organization_id = l_maint_organization_id
253 AND NVL(wdj.estimation_status,1) <> 7
254 AND NVL(wdj.estimation_status,1) > 0
255 AND wdj.status_type IN (1,3,4,6,17)
256 AND p_job_option = 3
257 AND wdj.maintenance_object_id in
258 (select cii.instance_id
259 from csi_item_instances cii
260 where cii.instance_number = p_asset_number
261 AND cii.inventory_item_id = p_inventory_item_id
262 )
263 AND wdj.maintenance_object_type = 3
264 AND p_entity_type = 6
265 RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
266
267 ELSIF p_job_option = 4 THEN
268
269 UPDATE wip_discrete_jobs wdj -- option 4
270 SET wdj.estimation_status = l_estimation_group_id,
271 wdj.last_update_date = SYSDATE,
272 wdj.last_updated_by = l_user_id,
273 wdj.request_id = l_request_id
274 WHERE wdj.organization_id = l_maint_organization_id
275 AND NVL(wdj.estimation_status,1) <> 7
276 AND NVL(wdj.estimation_status,1) > 0
277 AND wdj.status_type IN (1,3,4,6,17)
278 AND p_job_option = 4
279 AND wdj.owning_department = p_owning_department_id
280 AND p_entity_type = 6
281 RETURNING wdj.wip_entity_id BULK COLLECT INTO l_entity_id_tab;
282
283 END IF;
284
285 IF l_debug = 'Y' THEN
286 FND_FILE.PUT_LINE(FND_FILE.LOG, TO_CHAR(SQL%ROWCOUNT)
287 ||' Job Record(s) Updated with Group Id: '
288 ||TO_CHAR(l_estimation_group_id));
289
290 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
291 END IF;
292
293 COMMIT;
294
295 l_stmt_num := 22;
296 /* Delete from the global temp table just to make sure it is empty */
297 DELETE FROM cst_eam_direct_items_temp;
298
299 l_stmt_num := 24;
300
301 /* Populate the Global Temp Table that replaces WEDIV */
302 CST_eamCost_PUB.Insert_tempEstimateDetails (
303 p_api_version => 1.0,
304 x_return_status => l_return_status,
305 x_msg_count => l_msg_count,
306 x_msg_data => l_msg_data,
307 p_entity_id_tab => l_entity_id_tab
308 );
309
310
314 -- Have a savepoint before starting the job. This is main savepoint
311 --------------------------------------------------------------------
312 -- Processs WIP Jobs
313 --------------------------------------------------------------------
315 SAVEPOINT CSTPECEP_MAIN_PUB;
316
317 l_stmt_num := 30;
318
319 IF l_entity_id_tab.COUNT > 0 THEN
320
321 -- Delete existing estimates
322 -- The estimate may have been rolled up to asset
323
324 CST_EAMCOST_PUB.delete_eamperbal(
325 p_api_version => 1.0,
326 p_init_msg_list => FND_API.g_false,
327 p_entity_id_tab => l_entity_id_tab,
328 p_org_id => l_maint_organization_id,
329 p_type => 1,
330 x_return_status => l_return_status,
331 x_msg_count => l_msg_count,
332 x_msg_data => l_msg_data);
333
334 IF l_return_status <> FND_API.g_ret_sts_success THEN
335
336
337 CST_UTILITY_PUB.writelogmessages
338 ( p_api_version => 1.0,
339 p_msg_count => l_msg_count,
340 p_msg_data => l_msg_data,
341 x_return_status => l_msg_return_status);
342
343 l_err_code := 'Error: CSTEAM_COST_PUB.delete_eamperbal()';
344
345 RAISE process_error;
346
347 END IF;
348
349 /* the following lines delete the rows for this wip entity ID from the table
350 CST_EAM_WO_ESTIMATE_DETAILS */
351
352 l_stmt_num := 32;
353
354 FORALL l_index IN l_entity_id_tab.FIRST..l_entity_id_tab.LAST
355 Delete from CST_EAM_WO_ESTIMATE_DETAILS
356 where wip_entity_id = l_entity_id_tab(l_index);
357
358 /* Added the call to Delete_eamBalAcct as part of eAM
359 Requirements Project - R12. The procedure deletes the
360 rows for this wip entity ID from the table
361 WIP_EAM_BALANCE_BY_ACCOUNTS */
362
363 l_stmt_num := 35;
364 CST_eamCost_PUB.Delete_eamBalAcct(
365 p_api_version => 1.0,
366 p_init_msg_list => FND_API.G_FALSE,
367 p_commit => FND_API.G_FALSE,
368 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
369 x_return_status => l_return_status,
370 x_msg_count => l_msg_count,
371 x_msg_data => l_msg_data,
372 p_entity_id_tab => l_entity_id_tab,
373 p_org_id => l_maint_organization_id
374 ) ;
375
376 IF l_return_status <> FND_API.g_ret_sts_success THEN
377
378 CST_UTILITY_PUB.writelogmessages
379 ( p_api_version => 1.0,
380 p_msg_count => l_msg_count,
381 p_msg_data => l_msg_data,
382 x_return_status => l_msg_return_status);
383
384 l_err_code := 'Error: CST_EAMCOST_PUB.Delete_eamBalAcct()';
385
386 RAISE process_error;
387
388 END IF;
389
390 IF l_return_status <> FND_API.g_ret_sts_success THEN
391
392 CST_UTILITY_PUB.writelogmessages
393 ( p_api_version => 1.0,
394 p_msg_count => l_msg_count,
395 p_msg_data => l_msg_data,
396 x_return_status => l_msg_return_status);
397
398 l_err_code := 'Error: CST_eamCost_PUB.Insert_tempEstimateDetails()';
399
400 RAISE process_error;
401
402 END IF;
403
404 l_stmt_num := 38;
405
406 FOR l_index IN l_entity_id_tab.FIRST..l_entity_id_tab.LAST LOOP
407 BEGIN
408
409 -- Have an intermediate savepoint. Its position is updated as and when
410 -- we have a successful completion. We would rollback only errored out estimation
411 SAVEPOINT CSTPECEP_INT_PUB;
412
413 l_stmt_num := 40;
414
415 CST_EAMCOST_PUB.compute_job_estimate
416 ( p_api_version => 1.0,
417 p_init_msg_list => FND_API.g_true,
418 p_debug => l_debug,
419 p_wip_entity_id => l_entity_id_tab(l_index),
420 p_user_id => l_user_id,
421 p_request_id => l_request_id,
422 p_prog_id => l_prog_id,
423 p_prog_app_id => l_prog_app_id,
424 p_login_id => l_login_id,
425 x_return_status => l_return_status,
426 x_msg_count => l_msg_count,
427 x_msg_data => l_msg_data);
428
429 IF l_return_status <> FND_API.g_ret_sts_success THEN
430
431 CST_UTILITY_PUB.writelogmessages
435 x_return_status => l_msg_return_status);
432 ( p_api_version => 1.0,
433 p_msg_count => l_msg_count,
434 p_msg_data => l_msg_data,
436
437 l_err_code := 'Error: CSTEAM_COST_PUB.compute_job_estimate()';
438
439 RAISE process_error;
440
441 END IF;
442
443 l_stmt_num := 45;
444
445 CST_UTILITY_PUB.writelogmessages
446 ( p_api_version => 1.0,
447 p_msg_count => l_msg_count,
448 p_msg_data => l_msg_data,
449 x_return_status => l_msg_return_status);
450
451 /* the following statement sets the status to re estimate if the status of the
452 job is 9(re estimate and runnin, otherwise the status is set to 7(complete) */
453
454
455 UPDATE wip_discrete_jobs wdj
456 SET estimation_status = decode(estimation_status,9,8,7),
457 last_estimation_date = SYSDATE,
458 last_estimation_req_id = l_request_id,
459 last_update_date = SYSDATE
460 WHERE wdj.wip_entity_id = l_entity_id_tab(l_index);
461
462 EXCEPTION
463 WHEN PROCESS_ERROR THEN
464
465 ROLLBACK TO CSTPECEP_INT_PUB;
466
467 UPDATE wip_discrete_jobs
468 SET estimation_status = 3,
469 last_update_date = SYSDATE,
470 last_estimation_date = SYSDATE,
471 last_estimation_req_id = l_request_id
472 WHERE wip_entity_id = l_entity_id_tab(l_index);
473
474
475 l_err_num := 2002;
476 l_err_msg := 'CSTPECEP.estimate_wip_jobs('
477 || to_char(l_stmt_num)
478 || '): '
479 ||l_err_code;
480 IF l_debug = 'Y' THEN
481 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
482 END IF;
483 END;
484
485 END LOOP; -- End l_entity_id_tab loop
486 END IF; -- checking for count
487
488 END IF; -- Main If
489
490 COMMIT; -- All Done. Commit
491
492 EXCEPTION
493
494 WHEN OTHERS THEN
495
496 -- Rollback all. Even last estimation data is restored. That is primary reason
497 -- of having this main Savepoint. This is done only in some unexpected exception
498 ROLLBACK TO CSTPECEP_MAIN_PUB;
499
500 FORALL l_index IN l_entity_id_tab.FIRST..l_entity_id_tab.LAST
501 UPDATE wip_discrete_jobs
502 SET estimation_status = 1,
503 last_update_date = SYSDATE,
504 last_estimation_date = SYSDATE,
505 last_estimation_req_id = l_request_id
506 WHERE estimation_status = l_estimation_group_id
507 AND wip_entity_id = l_entity_id_tab(l_index);
508
509 l_err_num := SQLCODE;
510 l_err_code := NULL;
511 l_err_msg := SUBSTR('CSTPECEP.estimate_wip_jobs('
512 || to_char(l_stmt_num)
513 || '): '
514 ||SQLERRM,1,240);
515 IF l_debug = 'Y' THEN
516 FND_FILE.PUT_LINE(fnd_file.log,l_err_msg);
517 END IF;
518 CONC_STATUS := FND_CONCURRENT.
519 SET_COMPLETION_STATUS('ERROR',l_err_msg);
520 COMMIT;
521
522
523 END estimate_wip_jobs;
524
525 /*---------------------------------------------------------------------------*
526 | PUBLIC PROCEDURE |
527 | Estimate_WorkOrder_GRP |
528 | |
529 | API provided for online estimation of workorder. |
530 | WDJ.estimation_status should be set to Running and Committed |
531 | before calling this API. This is to prevent concurrency issues |
532 | if there is a Cost Estimation Concurrent request currently |
533 | running. |
534 | |
535 | This API has been added as part of estimation enhancements for |
536 | Patchset I. |
537 | |
538 | PARAMETERS |
539 | p_organization_id |
540 | p_wip_entity_id |
541 | |
542 *----------------------------------------------------------------------------*/
543
544 PROCEDURE Estimate_WorkOrder_GRP(
545 p_api_version IN NUMBER,
546 p_init_msg_list IN VARCHAR2,
547 p_commit IN VARCHAR2,
548 p_validation_level IN NUMBER,
549 x_return_status OUT NOCOPY VARCHAR2,
550 x_msg_count OUT NOCOPY NUMBER,
551 x_msg_data OUT NOCOPY VARCHAR2,
552 p_organization_id IN NUMBER,
556 IS
553 p_wip_entity_id IN NUMBER,
554 p_delete_only IN VARCHAR2 := 'N'
555 )
557
558 l_api_name CONSTANT VARCHAR2(30) := 'Estimate_WorkOrder';
559 l_api_version CONSTANT NUMBER := 1.0;
560
561 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
562 l_msg_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
563 l_msg_count NUMBER := 0;
564 l_msg_data VARCHAR2(8000) := '';
565 l_api_message VARCHAR2(1000) := '';
566
567 l_stmt_num NUMBER := 0;
568 l_request_id NUMBER := 0;
569 l_user_id NUMBER := 0;
570 l_prog_id NUMBER := 0;
571 l_prog_app_id NUMBER := 0;
572 l_login_id NUMBER := 0;
573 l_conc_program_id NUMBER := 0;
574
575 l_entity_id_tab CSTPECEP.wip_entity_id_type;
576
577
578 BEGIN
579
580 -- Standard Start of API savepoint
581 SAVEPOINT Estimate_WorkOrder_GRP;
582
583 l_stmt_num := 5;
584
585 -- Standard call to check for call compatibility
586 IF NOT FND_API.Compatible_API_Call( l_api_version,
587 p_api_version,
588 l_api_name,
589 G_PKG_NAME) THEN
590 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
591 END IF;
592
593 -- Initialize message list if p_init_msg_list is set to TRUE
594 IF FND_API.to_Boolean(p_init_msg_list) THEN
595 FND_MSG_PUB.initialize;
596 END IF;
597
598 -- Initialize API return status to success
599 x_return_status := FND_API.G_RET_STS_SUCCESS;
600
601 l_entity_id_tab(1) := p_wip_entity_id; -- Bug#4239253 PL/SQL table to be used instead of p_wip_entity_id
602
603
604 l_stmt_num := 10;
605
606 l_request_id := FND_GLOBAL.conc_request_id;
607 l_user_id := FND_GLOBAL.user_id;
608 l_prog_id := FND_GLOBAL.conc_program_id;
609 l_prog_app_id := FND_GLOBAL.prog_appl_id;
610 l_login_id := FND_GLOBAL.conc_login_id;
611 l_conc_program_id := FND_GLOBAL.conc_program_id;
612
613 --------------------------------------------------------------------
614 -- Processs WorkOrder
615 --------------------------------------------------------------------
616
617 l_stmt_num := 20;
618 -- Delete existing estimates
619 -- The estimate may have been rolled up to asset
620
621 CST_EAMCOST_PUB.delete_eamperbal(
622 p_api_version => 1.0,
623 p_init_msg_list => FND_API.g_false,
624 p_entity_id_tab => l_entity_id_tab,
625 p_org_id => p_organization_id,
626 p_type => 1,
627 x_return_status => l_return_status,
628 x_msg_count => l_msg_count,
629 x_msg_data => l_msg_data);
630
631 IF l_return_status <> FND_API.g_ret_sts_success THEN
632
633 l_api_message := 'Error: CST_EAMCOST_PUB.delete_eamperbal()';
634
635 FND_MSG_PUB.ADD_EXC_MSG('CSTPECEP', 'ESTIMATE_WORKORDER('
636 ||TO_CHAR(l_stmt_num)
637 ||'): ', l_api_message);
638 RAISE FND_API.g_exc_error;
639
640 END IF;
641
642 /* the following lines delete the rows for this wip entity ID from the table
643 CST_EAM_WO_ESTIMATE_DETAILS */
644
645 l_stmt_num := 30;
646
647 Delete from CST_EAM_WO_ESTIMATE_DETAILS
648 where wip_entity_id = l_entity_id_tab(1);
649
650 /* Added the call to Delete_eamBalAcct as part of eAM
651 Requirements Project - R12. The procedure deletes the
652 rows for this wip entity ID from the table
653 WIP_EAM_BALANCE_BY_ACCOUNTS */
654
655 l_stmt_num := 35;
656 CST_eamCost_PUB.Delete_eamBalAcct(
657 p_api_version => 1.0,
658 p_init_msg_list => FND_API.G_FALSE,
659 p_commit => FND_API.G_FALSE,
660 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
661 x_return_status => l_return_status,
662 x_msg_count => l_msg_count,
663 x_msg_data => l_msg_data,
664 p_entity_id_tab => l_entity_id_tab,
665 p_org_id => p_organization_id
666 ) ;
667
668 IF l_return_status <> FND_API.g_ret_sts_success THEN
669
670 l_api_message := 'Error: CST_EAMCOST_PUB.Delete_eamBalAcct()';
671
672 FND_MSG_PUB.ADD_EXC_MSG('CSTPECEP', 'ESTIMATE_WORKORDER('
673 ||TO_CHAR(l_stmt_num)
674 ||'): ', l_api_message);
675 RAISE FND_API.g_exc_error;
676
677 END IF;
678
679
680 DELETE FROM cst_eam_direct_items_temp;
681
682 l_stmt_num := 36;
683
684 IF (NVL(p_delete_only, 'N') = 'N') THEN
685
686 l_stmt_num := 37;
687
691 x_return_status => l_return_status,
688 /* Populate the Global Temp Table that replaces WEDIV */
689 CST_eamCost_PUB.Insert_tempEstimateDetails (
690 p_api_version => 1.0,
692 x_msg_count => l_msg_count,
693 x_msg_data => l_msg_data,
694 p_entity_id_tab => l_entity_id_tab
695 );
696
697 IF l_return_status <> FND_API.g_ret_sts_success THEN
698
699 l_api_message := 'Error: CST_eamCost_PUB.Insert_tempEstimateDetails()';
700
701 FND_MSG_PUB.ADD_EXC_MSG('CSTPECEP', 'ESTIMATE_WORKORDER('
702 ||TO_CHAR(l_stmt_num)
703 ||'): ', l_api_message);
704 RAISE FND_API.g_exc_error;
705
706 END IF;
707
708 l_stmt_num := 38;
709
710
711 l_stmt_num := 40;
712
713 CST_EAMCOST_PUB.compute_job_estimate
714 (p_api_version => 1.0,
715 p_init_msg_list => FND_API.g_false,
716 p_debug => 'N',
717 p_wip_entity_id => l_entity_id_tab(1),
718 p_user_id => l_user_id,
719 p_request_id => l_request_id,
720 p_prog_id => l_prog_id,
721 p_prog_app_id => l_prog_app_id,
722 p_login_id => l_login_id,
723 x_return_status => l_return_status,
724 x_msg_count => l_msg_count,
725 x_msg_data => l_msg_data);
726
727 IF l_return_status <> FND_API.g_ret_sts_success THEN
728
729 l_api_message := 'Error: CST_EAMCOST_PUB.compute_job_estimate()';
730
731 FND_MSG_PUB.ADD_EXC_MSG('CSTPECEP', 'ESTIMATE_WORKORDER('
732 ||TO_CHAR(l_stmt_num)
733 ||'): ', l_api_message);
734 RAISE FND_API.g_exc_error;
735
736 END IF;
737
738 l_stmt_num := 50;
739
740 /* the following statement sets the status to re estimate if the status of the
741 job is 9(re estimate and runnin, otherwise the status is set to 7(complete) */
742
743
744 UPDATE wip_discrete_jobs wdj
745 SET estimation_status = decode(estimation_status,9,8,7),
746 last_estimation_date = SYSDATE,
747 last_estimation_req_id = l_request_id,
748 last_update_date = SYSDATE
749 WHERE wdj.wip_entity_id = l_entity_id_tab(1);
750
751 END IF; -- p_delete_only check
752
753 --- Standard check of p_commit
754 IF FND_API.to_Boolean(p_commit) THEN
755 COMMIT WORK;
756 END IF;
757
758 -- Standard Call to get message count and if count = 1, get message info
759 FND_MSG_PUB.Count_And_Get (
760 p_count => x_msg_count,
761 p_data => x_msg_data );
762
763 EXCEPTION
764 WHEN fnd_api.g_exc_error THEN
765 ROLLBACK TO Estimate_WorkOrder_GRP;
766
767 UPDATE wip_discrete_jobs
768 SET estimation_status = 3,
769 last_update_date = SYSDATE,
770 last_estimation_date = SYSDATE,
771 last_estimation_req_id = l_request_id
772 WHERE wip_entity_id = l_entity_id_tab(1);
773
774 --- Standard check of p_commit
775 IF FND_API.to_Boolean(p_commit) THEN
776 COMMIT WORK;
777 END IF;
778
779 x_return_status := fnd_api.g_ret_sts_error;
780
781 -- Get message count and data
782 fnd_msg_pub.count_and_get
783 ( p_count => x_msg_count
784 , p_data => x_msg_data
785 );
786 --
787 WHEN fnd_api.g_exc_unexpected_error THEN
788 ROLLBACK TO Estimate_WorkOrder_GRP;
789
790 UPDATE wip_discrete_jobs
791 SET estimation_status = 3,
792 last_update_date = SYSDATE,
793 last_estimation_date = SYSDATE,
794 last_estimation_req_id = l_request_id
795 WHERE wip_entity_id = l_entity_id_tab(1);
796
797 --- Standard check of p_commit
798 IF FND_API.to_Boolean(p_commit) THEN
799 COMMIT WORK;
800 END IF;
801
802 x_return_status := fnd_api.g_ret_sts_unexp_error ;
803
804 -- Get message count and data
805 fnd_msg_pub.count_and_get
806 ( p_count => x_msg_count
807 , p_data => x_msg_data
808 );
809 --
810
811 WHEN OTHERS THEN
812 ROLLBACK TO Estimate_WorkOrder_GRP;
813
814
815 UPDATE wip_discrete_jobs
816 SET estimation_status = 3,
817 last_update_date = SYSDATE,
818 last_estimation_date = SYSDATE,
819 last_estimation_req_id = l_request_id
820 WHERE wip_entity_id = l_entity_id_tab(1);
821
822 --- Standard check of p_commit
823 IF FND_API.to_Boolean(p_commit) THEN
824 COMMIT WORK;
825 END IF;
826
827 x_return_status := fnd_api.g_ret_sts_unexp_error ;
828 --
829 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
830 THEN
831 fnd_msg_pub.add_exc_msg
832 ( 'CSTPECEP'
836 END IF;
833 , 'Estimate_WorkOrder : l_stmt_num - '||to_char(l_stmt_num)
834 );
835
837 -- Get message count and data
838 fnd_msg_pub.count_and_get
839 ( p_count => x_msg_count
840 , p_data => x_msg_data
841 );
842
843 END Estimate_WorkOrder_GRP;
844
845
846
847 END CSTPECEP;