1 PACKAGE BODY EAM_AutomaticEst AS
2 /* $Header: EAMPARCB.pls 115.4 2004/06/23 12:51:32 cboppana ship $ */
3 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_AutomaticEst';
4
5 /*---------------------------------------------------------------------------*
6 PROCEDURE Call_Validate_for_Reestimation
7 DESCRIPTION
8 This procedure expects wip_entity_id as input parameter and finds out NOCOPY
9 whether that job qualifies for reestimation or not by calling procedure
10 CST_eamCost_PUB.validate_for_reestimation. If it does it update
11 the estimation_status to reestimate in wip_discrete_jobs.
12 *----------------------------------------------------------------------------*/
13
14
15 PROCEDURE Call_Validate_for_Reestimation(
16 p_wip_entity_id IN NUMBER ,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_data OUT NOCOPY VARCHAR2
19 ) IS
20
21 l_est_status NUMBER := 0;
22 l_job_status NUMBER := 0;
23 l_msg_count NUMBER := 0;
24 l_validate NUMBER := 0;
25 l_stmt_num NUMBER := 0;
26 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
27 l_msg_data VARCHAR2(8000) := '';
28
29 BEGIN
30
31 l_stmt_num := 10;
32
33 SELECT estimation_status , status_type
34 INTO l_est_status , l_job_status
35 FROM WIP_DISCRETE_JOBS
36 WHERE wip_entity_id = p_wip_entity_id;
37
38 l_stmt_num := 20;
39
40 CST_eamCost_PUB.validate_for_reestimation(
41 p_api_version => 1.0,
42 p_init_msg_list => NULL,
43 p_commit => NULL,
44 p_validation_level => NULL,
45 p_wip_entity_id =>p_wip_entity_id,
46 p_job_status => l_job_status,
47 p_curr_est_status => l_est_status,
48 x_validate_flag => l_validate,
49 x_return_status => l_return_status,
50 x_msg_count => l_msg_count,
51 x_msg_data => l_msg_data
52 );
53 x_return_status := l_return_status;
54 x_msg_data := l_msg_data;
55
56 l_stmt_num := 30;
57
58 IF (l_return_status = fnd_api.g_ret_sts_success) THEN
59
60 l_stmt_num := 40;
61
62 -- Work order qualifies for reestimation
63 IF (l_validate = 1) THEN
64
65 l_stmt_num := 50;
66
67 IF (l_est_status = EAM_CONSTANTS.COMPLETE) THEN
68
69 l_stmt_num := 60;
70
71 UPDATE WIP_DISCRETE_JOBS
72 SET estimation_status = EAM_CONSTANTS.REESTIMATE
73 WHERE wip_entity_id = p_wip_entity_id;
74
75 ELSIF (l_est_status = EAM_CONSTANTS.RUNNING) THEN
76
77 l_stmt_num := 70;
78
79 UPDATE WIP_DISCRETE_JOBS
80 SET estimation_status = EAM_CONSTANTS.RUNREEST
81 WHERE wip_entity_id = p_wip_entity_id;
82
83 END IF; /* ENDIF for l_est_status IF */
84
85 END IF; /* ENDIF for l_validate IF */
86
87 END IF; /* ENDIF for l_return_status IF */
88
89 EXCEPTION
90
91 WHEN NO_DATA_FOUND THEN
92 x_return_status := fnd_api.g_ret_sts_error;
93 x_msg_data := ' Call_Validate_for_Reestimation : Statement - '||l_stmt_num||' Error Message - '||SQLERRM ;
94
95 END Call_Validate_for_Reestimation;
96
97 /*---------------------------------------------------------------------------*
98 PROCEDURE CST_Item_Cost_Change
99 DESCRIPTION
100 This procedure expects Inventory_item_id and organization_id as input
101 parameter. This is called by costing if item cost gets changed. This API
102 finds out NOCOPY all the jobs that are using this inventory item and then check
103 for each job for reestimation by calling Call_Validate_for_Reestimation.
104 *----------------------------------------------------------------------------*/
105
106 PROCEDURE CST_Item_Cost_Change(
107 p_inv_item_id IN NUMBER ,
108 p_org_id IN NUMBER ,
109 x_return_status OUT NOCOPY VARCHAR2 ,
110 x_msg_data OUT NOCOPY VARCHAR2
111 ) IS
112
113 l_wip_entity_tbl wip_entity_tbl_type;
114 l_stmt_num NUMBER := 0;
115 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
116 l_msg_data VARCHAR2(8000) := '';
117
118 BEGIN
119
120 l_stmt_num := 10;
121
122 SELECT wro.wip_entity_id BULK COLLECT
123 INTO l_wip_entity_tbl
124 FROM WIP_REQUIREMENT_OPERATIONS wro , WIP_DISCRETE_JOBS wdj
125 WHERE wdj.wip_entity_id = wro.wip_entity_id
126 AND wdj.status_type
127 IN ( WIP_CONSTANTS.UNRELEASED , WIP_CONSTANTS.RELEASED ,
128 WIP_CONSTANTS.COMP_CHRG , WIP_CONSTANTS.HOLD ,
129 WIP_CONSTANTS.DRAFT )
130 AND wro.inventory_item_id = p_inv_item_id
131 AND wro.organization_id = p_org_id;
132
133 l_stmt_num := 20;
134
135 /* if no data is selected */
136 IF ( l_wip_entity_tbl.count = 0 ) THEN
137
138 l_msg_data := ' OR no data found ' ;
139
140 END IF; /* ENDIF for l_wip_entity_tbl.count */
141
142 FOR i IN l_wip_entity_tbl.FIRST..l_wip_entity_tbl.LAST LOOP
143
144 l_stmt_num := 30;
145
146 Call_Validate_for_Reestimation (
147 p_wip_entity_id => l_wip_entity_tbl(i),
148 x_return_status => l_return_status,
149 x_msg_data => l_msg_data
150 );
151 x_return_status := l_return_status;
152 x_msg_data := l_msg_data;
153
154 END LOOP;
155
156 EXCEPTION
157
158 WHEN OTHERS THEN
159 x_return_status := fnd_api.g_ret_sts_unexp_error;
160 x_msg_data := ' CST_Item_Cost_Change : Statement - '||l_stmt_num||' Error Message - '||SQLERRM ||l_msg_data ;
161
162 END CST_Item_Cost_Change;
163
164 /*---------------------------------------------------------------------------*
165 PROCEDURE CST_Usage_Rate_Change
166 DESCRIPTION
167 This procedure expects resource_id and organization_id as input
168 parameter. This is called by costing if resource rate gets changed. This API
169 finds out NOCOPY all the jobs that are using this resource and then check
170 for each job for reestimation by calling Call_Validate_for_Reestimation.
171 *----------------------------------------------------------------------------*/
172
173 PROCEDURE CST_Usage_Rate_Change(
174 p_resource_id IN NUMBER,
175 p_org_id IN NUMBER,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_data OUT NOCOPY VARCHAR2
178 ) IS
179
180 l_wip_entity_tbl wip_entity_tbl_type;
181 l_stmt_num NUMBER := 0;
182 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
183 l_msg_data VARCHAR2(8000) := '';
184
185 BEGIN
186
187 l_stmt_num := 10;
188
189 SELECT wor.wip_entity_id BULK COLLECT
190 INTO l_wip_entity_tbl
191 FROM WIP_OPERATION_RESOURCES wor , WIP_DISCRETE_JOBS wdj
192 WHERE wdj.wip_entity_id = wor.wip_entity_id
193 AND wdj.status_type
194 IN ( WIP_CONSTANTS.UNRELEASED , WIP_CONSTANTS.RELEASED ,
195 WIP_CONSTANTS.COMP_CHRG , WIP_CONSTANTS.HOLD ,
196 WIP_CONSTANTS.DRAFT )
197 AND wor.resource_id = p_resource_id
198 AND wor.organization_id = p_org_id;
199
200 l_stmt_num := 20;
201
202 /* if no data is selected */
203 IF ( l_wip_entity_tbl.count = 0 ) THEN
204
205 l_msg_data := ' OR no data found ' ;
206
207 END IF; /* ENDIF for l_wip_entity_tbl.count */
208
209 FOR i IN l_wip_entity_tbl.FIRST..l_wip_entity_tbl.LAST LOOP
210
211 l_stmt_num := 30;
212
213 Call_Validate_for_Reestimation (
214 p_wip_entity_id => l_wip_entity_tbl(i),
215 x_return_status => l_return_status ,
216 x_msg_data => l_msg_data
217 );
218
219 x_return_status := l_return_status;
220 x_msg_data := l_msg_data;
221
222 END LOOP;
223
224 EXCEPTION
225
226 WHEN OTHERS THEN
227 x_return_status := fnd_api.g_ret_sts_error;
228 x_msg_data := ' CST_Usage_Rate_Change : Statement - '||l_stmt_num||' Error Message - '||SQLERRM ||l_msg_data ;
229
230 END CST_Usage_Rate_Change;
231
232 /*---------------------------------------------------------------------------*
233 PROCEDURE PO_Req_Logic
234 DESCRIPTION
235 This procedure expects requisition_line_id as input parameter.
236 This is called by 'PO' when a requisition is saved. They need to call this API for
237 each requisition line.
238 This API first checks for Authorization_Status , it shoud not be in ('CANCELLED',
239 'REJECTED'). Then it checks for Destination_Type , it should be 'SHOP FLOOR'.
240 Then it checks for Cancel_Flag , it should not be 'Yes'. Then it checks for
241 osp flag, if this is 'NO' then it checks whether
242 that requisition line exists in cst_eam_wo_estimate_details table. If it
243 exists then this compares for required quantity, rate and unit price in PO
244 table and EAM table. If there is any change or requisition does not exist
245 in EAM table then this calls 'Call_Validate_for_Reestimation' procedure
246 to set the estimation status to reestimate.
247 *----------------------------------------------------------------------------*/
248
249 PROCEDURE PO_Req_Logic(
250 p_req_line_id IN NUMBER,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_msg_data OUT NOCOPY VARCHAR2
253 ) IS
254
255 l_count NUMBER := 0;
256 l_req_qty NUMBER := 0;
257 l_rate NUMBER := 0;
258 l_unit_price NUMBER := 0;
259 l_req_qty_p NUMBER := 0;
260 l_rate_p NUMBER := 0;
261 l_unit_price_p NUMBER := 0;
262 l_stmt_num NUMBER := 0;
263 l_wip_entity_id NUMBER := 0;
264 l_osp VARCHAR2(1) := 'N';
265 l_cancel_flag VARCHAR2(1) := 'N';
266 l_auth_status VARCHAR2(25) := '';
267 l_dest_type VARCHAR2(25) := '';
268 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
269 l_msg_data VARCHAR2(8000) := '';
270
271 BEGIN
272
273 l_stmt_num := 10;
274
275 IF ( p_req_line_id IS NOT NULL ) THEN
276
277 l_stmt_num := 20;
278
279 SELECT nvl( prha.authorization_status , 'INCOMPLETE') , prla.destination_type_code,
280 nvl( prla.cancel_flag , 'N') , nvl( prla.wip_entity_id , 0) ,
281 nvl( plt.outside_operation_flag, 'N' )
282 INTO l_auth_status , l_dest_type ,
283 l_cancel_flag , l_wip_entity_id ,
284 l_osp
285 FROM PO_REQUISITION_LINES_ALL prla , PO_REQUISITION_HEADERS_ALL prha ,
286 PO_LINE_TYPES plt
287 WHERE prla.requisition_line_id = p_req_line_id
288 AND prla.requisition_header_id = prha.requisition_header_id
289 AND prla.line_type_id = plt.line_type_id;
290
291 l_stmt_num := 30;
292
293 --Line type for requisition is not outside processing
294 IF ( l_auth_status NOT IN ( 'CANCELLED' , 'REJECTED') AND
295 l_dest_type = 'SHOP FLOOR' AND
296 l_cancel_flag <> 'Y' AND
297 l_osp = 'N' AND
298 l_wip_entity_id <> 0 ) THEN
299
300 l_stmt_num := 40;
301
302 BEGIN
303
304 SELECT requisition_line_id
305 INTO l_count
306 FROM CST_EAM_WO_ESTIMATE_DETAILS
307 WHERE requisition_line_id = p_req_line_id
308 AND wip_entity_id = l_wip_entity_id;
309
310 EXCEPTION
311
312 WHEN NO_DATA_FOUND THEN
313
314 l_count := 0;
315 END;
316
317 l_stmt_num := 50;
318
319 -- Requisition line id exists in cst_eam_wo_estimate_details
320 IF ( l_count <> 0 ) THEN
321
322 l_stmt_num := 60;
323
324 SELECT nvl( required_quantity, 0) , nvl( rate, 0) ,
325 nvl( item_cost, 0)
326 INTO l_req_qty , l_rate , l_unit_price
327 FROM CST_EAM_WO_ESTIMATE_DETAILS
328 WHERE requisition_line_id = p_req_line_id
329 AND wip_entity_id = l_wip_entity_id;
330
331 l_stmt_num := 70;
332
333 SELECT quantity , nvl( rate, 0 ) , unit_price
334 INTO l_req_qty_p , l_rate_p , l_unit_price_p
335 FROM PO_REQUISITION_LINES_ALL
336 WHERE requisition_line_id = p_req_line_id
337 AND wip_entity_id = l_wip_entity_id;
338
339 l_stmt_num := 80;
340
341 -- IF required quantity/rate/cost has changed
342 IF ( l_req_qty <> l_req_qty_p OR
343 l_rate <> l_rate_p OR
344 l_unit_price <> l_unit_price_p ) THEN
345
346 l_stmt_num := 90;
347
348 Call_Validate_for_Reestimation (
349 p_wip_entity_id => l_wip_entity_id,
350 x_return_status => l_return_status,
351 x_msg_data => l_msg_data
352 );
353
354 x_return_status := l_return_status;
355 x_msg_data := l_msg_data;
356
357 END IF; /* ENDIF for comparing values IF */
358
359 -- Requisition line id does not exist in cst_eam_wo_estimate_details
360 ELSIF ( l_count = 0 ) THEN
361
362 l_stmt_num := 100;
363
364 Call_Validate_for_Reestimation (
365 p_wip_entity_id => l_wip_entity_id,
366 x_return_status => l_return_status,
367 x_msg_data => l_msg_data
368 );
369
370 x_return_status := l_return_status;
371 x_msg_data := l_msg_data;
372
373 END IF; /* ENDIF for l_count IF */
374
375 END IF; /* ENDIF for l_osp IF */
376
377 END IF; /* ENDIF of main IF */
378
379 EXCEPTION
380
381 WHEN NO_DATA_FOUND THEN
382
383 x_return_status := fnd_api.g_ret_sts_error;
384 x_msg_data := ' PO_Req_Logic : Statement - '||l_stmt_num||' Error Message - '||SQLERRM ;
385
386
387 END PO_Req_Logic;
388
389 /*---------------------------------------------------------------------------*
390 PROCEDURE PO_Po_Logic
391 DESCRIPTION
392 This procedure expects po_distribution_id as input parameter.
393 This is called by 'PO' when a purchase order is saved. They need to call this API
394 for each distribution line. This API first checks for Authorization_Status ,
395 it shoud not be in ('CANCELLED','REJECTED'). Then it checks for Destination_Type ,
396 it should be 'SHOP FLOOR'.Then it checks for Cancel_Flag , it should not be 'Yes'.
397 Then it checks forF osp flag, if this is 'NO' then it checks whether
398 that distribution line exists in cst_eam_wo_estimate_details table. If it
399 exists then this compares for required quantity, rate and unit price in PO
400 table and EAM table. If there is any change in the values then call the procedure
401 Call_Validate_for_Reestimation'. If distribution line does not exist in EAM
402 table then it checks whether requisition has been converted into 'PO' or not
403 and calls'Call_Validate_for_Reestimation' procedure to set the estimation
404 status to reestimate.
405 *----------------------------------------------------------------------------*/
406
407 PROCEDURE PO_Po_Logic(
408 p_po_dist_id IN NUMBER,
409 x_return_status OUT NOCOPY VARCHAR2,
413 l_count NUMBER := 0;
410 x_msg_data OUT NOCOPY VARCHAR2
411 ) IS
412
414 l_req_qty NUMBER := 0;
415 l_rate NUMBER := 0;
416 l_unit_price NUMBER := 0;
417 l_req_qty_p NUMBER := 0;
418 l_rate_p NUMBER := 0;
419 l_unit_price_p NUMBER := 0;
420 l_line_location_id NUMBER := 0;
421 l_estimate_flag NUMBER := 0;
422 l_stmt_num NUMBER := 0;
423 l_wip_entity_id NUMBER := 0;
424 l_req_line_id NUMBER := 0;
425 l_osp VARCHAR2(1) := 'N';
426 l_cancel_flag VARCHAR2(1) := 'N';
427 l_auth_status VARCHAR2(25) := '';
428 l_dest_type VARCHAR2(25) := '';
429 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
430 l_msg_data VARCHAR2(8000) := '';
431
432
433 BEGIN
434
435 l_stmt_num := 10;
436
437 IF ( p_po_dist_id IS NOT NULL ) THEN
438
439 l_stmt_num := 20;
440
441 SELECT nvl( pha.authorization_status , 'INCOMPLETE') , pda.destination_type_code,
442 nvl( pla.cancel_flag , 'N') , nvl( pda.wip_entity_id , 0) ,
443 nvl( plt.outside_operation_flag, 'N' )
444 INTO l_auth_status , l_dest_type ,
445 l_cancel_flag , l_wip_entity_id ,
446 l_osp
447 FROM PO_DISTRIBUTIONS_ALL pda , PO_LINE_TYPES plt ,
448 PO_LINES_ALL pla , PO_HEADERS_ALL pha
449 WHERE pda.po_line_id = pla.po_line_id
450 AND pla.line_type_id = plt.line_type_id
451 AND pda.po_distribution_id = p_po_dist_id
452 AND pda.po_header_id = pha.po_header_id ;
453
454 l_stmt_num := 30;
455
456 --Line type for requisition is not outside processing
457 IF ( l_auth_status NOT IN ( 'CANCELLED' , 'REJECTED') AND
458 l_dest_type = 'SHOP FLOOR' AND
459 l_cancel_flag <> 'Y' AND
460 l_osp = 'N' AND
461 l_wip_entity_id <> 0 ) THEN
462
463 l_stmt_num := 40;
464
465 BEGIN
466
467 SELECT po_distribution_id , nvl( required_quantity, 0 ) , nvl( rate, 0 ) ,
468 nvl( item_cost , 0 )
469 INTO l_count, l_req_qty , l_rate , l_unit_price
470 FROM CST_EAM_WO_ESTIMATE_DETAILS
471 WHERE po_distribution_id = p_po_dist_id
472 AND wip_entity_id = l_wip_entity_id;
473
474 EXCEPTION
475
476 WHEN NO_DATA_FOUND THEN
477
478 l_count := 0;
479 END;
480
481 l_stmt_num := 50;
482
483 --po_distribution_id exists in cst_eam_wo_estimate_details
484 IF( l_count <> 0) then
485
486 l_stmt_num := 60;
487
488 /* SELECT nvl( required_quantity, 0 ) , nvl( rate, 0 ) ,
489 nvl( item_cost , 0 )
490 INTO l_req_qty , l_rate ,
491 l_unit_price
492 FROM CST_EAM_WO_ESTIMATE_DETAILS
493 WHERE po_distribution_id = p_po_dist_id
494 AND wip_entity_id = l_wip_entity_id ; */
495
496 l_estimate_flag := 1;
497
498 --Po distribution id does not exist in cst_eam_wo_estimate_details
499 ELSIF ( l_count = 0 ) THEN
500
501 BEGIN
502
503 l_stmt_num := 70;
504
505 SELECT prla.line_location_id
506 INTO l_line_location_id
507 FROM PO_DISTRIBUTIONS_ALL pda , PO_REQUISITION_LINES_ALL prla
508 WHERE pda.line_location_id = nvl( prla.line_location_id , -999 )
509 AND pda.po_distribution_id = p_po_dist_id ;
510
511 EXCEPTION
512
513 WHEN NO_DATA_FOUND THEN
514
515 l_stmt_num := 80;
516 l_line_location_id := 0;
517 END;
518
519 --requisition has been converted INTO PO
520 IF ( l_line_location_id <> 0) THEN
521
522 l_stmt_num := 90;
523
524 SELECT requisition_line_id
525 INTO l_req_line_id
526 FROM CST_EAM_WO_ESTIMATE_DETAILS
527 WHERE line_location_id = l_line_location_id
528 AND wip_entity_id = l_wip_entity_id;
529
530 l_stmt_num := 100;
531
532 SELECT nvl( required_quantity , 0 ) , nvl( rate , 0 ) ,
533 nvl( item_cost , 0 )
534 INTO l_req_qty , l_rate ,
535 l_unit_price
536 FROM CST_EAM_WO_ESTIMATE_DETAILS
537 WHERE requisition_line_id = l_req_line_id
538 AND wip_entity_id = l_wip_entity_id;
539
540 l_estimate_flag := 1;
541
542 --PO is created independent of requisition
543 ELSIF ( l_line_location_id = 0 ) THEN
544
545 l_stmt_num := 110;
546
547 Call_Validate_for_Reestimation (
548 p_wip_entity_id => l_wip_entity_id,
549 x_return_status => l_return_status,
550 x_msg_data => l_msg_data
551 );
552
553 x_return_status := l_return_status;
557
554 x_msg_data := l_msg_data;
555
556 END IF ; /* end if for l_line_location_id if */
558 END IF; /* end if for l_count if */
559
560 IF ( l_estimate_flag = 1 ) THEN
561
562 l_stmt_num := 120;
563
564 SELECT pda.quantity_ordered , nvl( pda.rate , 0 ) ,
565 nvl( pla.unit_price,0)
566 INTO l_req_qty_p , l_rate_p ,
567 l_unit_price_p
568 FROM PO_DISTRIBUTIONS_ALL pda , PO_LINES_ALL pla
569 WHERE pda.wip_entity_id = l_wip_entity_id
570 AND pda.po_distribution_id = p_po_dist_id
571 AND pda.po_line_id = pla.po_line_id;
572
573 -- required quanity/cost/rate has changed
574 IF ( l_req_qty <> l_req_qty_p OR
575 l_rate <> l_rate_p OR
576 l_unit_price <> l_unit_price_p) THEN
577
578 l_stmt_num := 130;
579
580 Call_Validate_for_Reestimation (
581 p_wip_entity_id => l_wip_entity_id,
582 x_return_status => l_return_status,
583 x_msg_data => l_msg_data
584 );
585
586 x_return_status := l_return_status;
587 x_msg_data := l_msg_data;
588
589 END IF; /* end if for comparing if */
590
591 END IF; /* end if for l_estimate_flag if */
592
593 END IF; /* end if for l_osp if */
594
595 END IF; /* end if for main if */
596
597 EXCEPTION
598
599 WHEN NO_DATA_FOUND THEN
600
601 x_return_status := fnd_api.g_ret_sts_unexp_error;
602 x_msg_data := ' PO_Po_Logic : Statement - '||l_stmt_num||' Error Message - '||SQLERRM ;
603
604 END PO_Po_Logic;
605
606 /*---------------------------------------------------------------------------*
607 PROCEDURE PO_Line_Logic
608 DESCRIPTION
609 This procedure expects po_line_id as input parameter.
610 This is called by 'PO' when a purchase order is saved. They need to call this API
611 for each PO line. This API finds out NOCOPY all the distribution lines for each PO line.
612 Then it loops through all the distribution lines and calls PO_Po_logic.
613 *----------------------------------------------------------------------------*/
614
615 PROCEDURE PO_Line_Logic(
616 p_po_line_id IN NUMBER,
617 x_return_status OUT NOCOPY VARCHAR2,
618 x_msg_data OUT NOCOPY VARCHAR2
619 ) IS
620
621 l_po_dist_tbl po_dist_tbl_type;
622 l_stmt_num NUMBER := 0;
623 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
624 l_msg_data VARCHAR2(8000) := '';
625
626 BEGIN
627
628 l_stmt_num := 10;
629
630 IF ( p_po_line_id IS NOT NULL ) THEN
631
632 SELECT pda.po_distribution_id BULK COLLECT
633 INTO l_po_dist_tbl
634 FROM PO_LINES_ALL pla , PO_DISTRIBUTIONS_ALL pda
635 WHERE pla.po_line_id = p_po_line_id
636 AND pla.po_line_id = pda.po_line_id ;
637
638 l_stmt_num := 20;
639
640 /* if no data is selected */
641 IF ( l_po_dist_tbl.count = 0 ) THEN
642
643 l_msg_data := ' OR no data found ' ;
644
645 END IF; /* ENDIF for l_po_dist_tbl.count */
646
647 FOR i IN l_po_dist_tbl.FIRST..l_po_dist_tbl.LAST LOOP
648
649 l_stmt_num := 30;
650
651 PO_Po_Logic(
652 p_po_dist_id => l_po_dist_tbl(i),
653 x_return_status => l_return_status,
654 x_msg_data => l_msg_data
655 );
656
657 x_return_status := l_return_status;
658 x_msg_data := l_msg_data;
659
660 END LOOP;
661
662 END IF ; /* ENDIF for p_po_line_id IF */
663
664 EXCEPTION
665
666 WHEN OTHERS THEN
667
668 x_return_status := fnd_api.g_ret_sts_error;
669 x_msg_data := ' PO_Line_Logic : Statement - '||l_stmt_num||' Error Message - '||SQLERRM ||l_msg_data ;
670
671 END PO_Line_Logic;
672
673 /*---------------------------------------------------------------------------*
674 PUBLIC PROCEDURE Auto_Reest_of_Cost
675 DESCRIPTION
676 This API will be called by different products. 'EAM' will call this API
677 if the material quantity or usage rate gets changed. 'WIP' will call this API
678 whenever new job is created. 'CST' will call this API if item cost or
679 resource rate gets changed. 'PO' will call this API while saving a requisition
680 or purchase order.
681 *----------------------------------------------------------------------------*/
682
683 PROCEDURE Auto_Reest_of_Cost(
684 p_wip_entity_id IN NUMBER,
685 p_api_name IN VARCHAR2,
686 p_req_line_id IN NUMBER,
687 p_po_dist_id IN NUMBER,
688 p_po_line_id IN NUMBER,
689 p_inv_item_id IN NUMBER,
690 p_org_id IN NUMBER,
691 p_resource_id IN NUMBER,
692 x_return_status OUT NOCOPY VARCHAR2,
693 x_msg_count OUT NOCOPY NUMBER,
694 x_msg_data OUT NOCOPY VARCHAR2
695 ) IS
696
697 l_api_version CONSTANT NUMBER := 1.0;
698 l_api_name CONSTANT VARCHAR2(30) := 'Auto_Reest_of_Cost';
699 l_stmt_num NUMBER := 0;
700 l_msg_data VARCHAR2(8000) := '';
701 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
702
703 BEGIN
704 -- Standard Start of API savepoint
705 SAVEPOINT Auto_Reest_of_Cost_PUB;
706
707 /* Called by 'EAM' and wip_entity_id is required parameter */
708 IF ( p_api_name = 'EAM' AND p_wip_entity_id IS NOT NULL ) THEN
709
710 l_stmt_num := 10;
711
712 Call_Validate_for_Reestimation (
713 p_wip_entity_id => p_wip_entity_id,
714 x_return_status => l_return_status,
715 x_msg_data => l_msg_data
716 );
717
718 x_msg_data := l_msg_data;
719 x_return_status := l_return_status;
720
721 END IF; /* END IF for 'EAM' IF */
722
723 /* Called by 'WIP' and wip_entity_id is required parameter */
724 IF ( p_api_name = 'WIP' AND p_wip_entity_id IS NOT NULL ) THEN
725
726 l_stmt_num := 20;
727
728 Call_Validate_for_Reestimation (
729 p_wip_entity_id => p_wip_entity_id,
730 x_return_status => l_return_status,
731 x_msg_data => l_msg_data
732 );
733
734 x_msg_data := l_msg_data;
735 x_return_status := l_return_status;
736
737 END IF; /* END IF for 'WIP' IF */
738
739 /* Called by 'CST' and inventory_item_id and organization_id are required parameters */
740 IF ( p_api_name = 'CST' AND
741 p_inv_item_id IS NOT NULL AND
742 p_org_id IS NOT NULL ) THEN
743
744 l_stmt_num := 30;
745
746 CST_Item_Cost_Change(
747 p_inv_item_id => p_inv_item_id,
748 p_org_id => p_org_id,
749 x_return_status => l_return_status,
750 x_msg_data => l_msg_data
751 );
752
753 x_msg_data := l_msg_data;
754 x_return_status := l_return_status;
755
756 END IF; /* END IF for 'CST_Item' IF */
757
758 /* Called by 'CST' and resource_id and organization_id are required parameters */
759 IF ( p_api_name = 'CST' AND
760 p_resource_id IS NOT NULL AND
761 p_org_id IS NOT NULL ) THEN
762
763 l_stmt_num := 40;
764
765 CST_Usage_Rate_Change(
766 p_resource_id => p_resource_id,
767 p_org_id => p_org_id,
768 x_return_status => l_return_status,
769 x_msg_data => l_msg_data
770 );
771
772 x_msg_data := l_msg_data;
773 x_return_status := l_return_status;
774
775 END IF; /* END IF for 'CST_Usage' IF */
776
777 /* Called by 'PO' and requisition_line_id are required paramaters */
778 IF ( p_api_name = 'PO' AND
779 p_req_line_id IS NOT NULL ) THEN
780
781 l_stmt_num := 50;
782
783 PO_Req_Logic(
784 p_req_line_id => p_req_line_id,
785 x_return_status => l_return_status,
786 x_msg_data => l_msg_data
787 );
788
789 x_msg_data := l_msg_data;
790 x_return_status := l_return_status;
791
792 END IF; /* END IF for 'Req' IF */
793
794 /* Called by 'PO' and po_distribution_id are required paramaters */
795 IF ( p_api_name = 'PO' AND
796 p_po_dist_id IS NOT NULL ) THEN
797
798 l_stmt_num := 60;
799
800 PO_Po_Logic(
801 p_po_dist_id => p_po_dist_id,
802 x_return_status => l_return_status,
803 x_msg_data => l_msg_data
804 );
805
806 x_msg_data := l_msg_data;
807 x_return_status := l_return_status;
808
809 END IF; /* END IF for 'PO' IF */
810
811 /* Called by 'PO' and po_line_id are required paramaters */
812 IF ( p_api_name = 'PO' AND
813 p_po_line_id IS NOT NULL ) THEN
814
815 l_stmt_num := 60;
816
817 PO_Line_Logic(
818 p_po_line_id => p_po_line_id,
819 x_return_status => l_return_status,
820 x_msg_data => l_msg_data
821 );
822
823 x_msg_data := l_msg_data;
824 x_return_status := l_return_status;
825
826 END IF; /* END IF for 'PO_Line' IF */
827
828 --fix for 3550864
829 if(x_msg_data is not null) then
830 x_msg_data := substr(x_msg_data,1,2000);
831 end if;
832
833 EXCEPTION
834
835 WHEN OTHERS THEN
836 ROLLBACK TO Auto_Reest_of_Cost_PUB;
837 x_return_status := fnd_api.g_ret_sts_unexp_error ;
838 x_msg_data := ' Auto_Reest_of_Cost : Statement - '||l_stmt_num||' Error Message - '||SQLERRM ||l_msg_data ;
839
840 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
841 THEN
842 FND_MSG_PUB.add_exc_msg
843 ( 'EAM_AutomaticEst'
844 , '.Auto_Reest_of_Cost : Statement -'||to_char(l_stmt_num)||'Error Message -'||SQLERRM
845 );
846 END IF;
847
848 -- Get message count and data
849 FND_MSG_PUB.count_and_get
850 ( p_count => x_msg_count
851 , p_data => x_msg_data
852 );
853 if(x_msg_data is not null) then
854 x_msg_data := substr(x_msg_data,1,2000);
855 end if;
856
857 END Auto_Reest_of_Cost;
858
859 END EAM_AutomaticEst;
[Home] [Help]