[Home] [Help]
PACKAGE BODY: APPS.GMDRTVAL_PUB
Source
1 PACKAGE BODY GMDRTVAL_PUB AS
2 /* $Header: GMDPRTVB.pls 120.9 2011/07/25 09:36:33 rborpatl ship $ */
3
4 --Bug 3222090, NSRIVAST 20-FEB-2004, BEGIN
5 --Forward declaration.
6 FUNCTION set_debug_flag RETURN VARCHAR2;
7 l_debug VARCHAR2(1) := set_debug_flag;
8
9 FUNCTION set_debug_flag RETURN VARCHAR2 IS
10 l_debug VARCHAR2(1):= 'N';
11 BEGIN
12 IF( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) THEN
13 l_debug := 'Y';
14 END IF;
15 RETURN l_debug;
16 END set_debug_flag;
17 --Bug 3222090, NSRIVAST 20-FEB-2004, END
18
19 /* ==================================================================== */
20 /* FUNCTION: */
21 /* get_theoretical_process_loss */
22 /* */
23 /* DESCRIPTION: */
24 /* This PL/SQL function is responsible for */
25 /* retrieving the process loss for the routing class. */
26 /* */
27 /* REQUIREMENTS */
28 /* routing_class should be a non null value. */
29 /* */
30 /* SYNOPSIS: */
31 /* l_ret := gmdrtval_pub.get_theoretical_process_loss(prouting_class, pquantity); */
32 /* */
33 /* RETURNS: */
34 /* Theoretical Process loss value. */
35 /* ==================================================================== */
36 FUNCTION get_theoretical_process_loss(prouting_class IN VARCHAR2, pquantity IN NUMBER) RETURN NUMBER IS
37 /* Cursor Definitions. */
38 /* =================== */
39 CURSOR Cur_get_process_loss IS
40 SELECT process_loss
41 FROM gmd_process_loss
42 WHERE routing_class = prouting_class
43 AND NVL(max_quantity, 2147484647) >= NVL(pquantity,2147484647)
44 ORDER BY max_quantity;
45
46 l_process_loss NUMBER;
47 BEGIN
48 IF (prouting_class IS NULL) THEN
49 l_process_loss := 0;
50 ELSE
51 OPEN Cur_get_process_loss;
52 FETCH Cur_get_process_loss INTO l_process_loss;
53 IF (Cur_get_process_loss%NOTFOUND) THEN
54 l_process_loss := NULL;
55 END IF;
56 CLOSE Cur_get_process_loss;
57 END IF;
58 RETURN l_process_loss;
59 END get_theoretical_process_loss;
60
61 /* ==================================================================== */
62 /* FUNCTION: */
63 /* check_duplicate_routing */
64 /* */
65 /* DESCRIPTION: */
66 /* This PL/SQL function is responsible for */
67 /* checking the duplication of routings. */
68 /* */
69 /* REQUIREMENTS */
70 /* routing_no and routing_vers should be non null values. */
71 /* */
72 /* SYNOPSIS: */
73 /* l_ret := gmdrtval_pub.check_duplicate_routing(prouting_no, prouting_vers, */
74 /* pcalledby_form); */
75 /* */
76 /* RETURNS: */
77 /* 0 Success */
78 /* -1 Some required fields for the procedure are missing. */
79 /* -50 GMD_ROUTING_EXISTS Duplicate Routing. */
80 /* ==================================================================== */
81 FUNCTION check_duplicate_routing(prouting_no IN VARCHAR2,
82 prouting_vers IN NUMBER,
83 pcalledby_form IN VARCHAR2) RETURN NUMBER IS
84 /* Cursor Definitions. */
85 /* =================== */
86 CURSOR Cur_check_dup IS
87 SELECT 1
88 FROM SYS.DUAL
89 WHERE EXISTS (SELECT 1
90 FROM fm_rout_hdr
91 WHERE routing_no = prouting_no
92 AND routing_vers = prouting_vers);
93 /* Local variables. */
94 /* ================ */
95 l_ret NUMBER;
96 duplicate_routing EXCEPTION;
97
98 /* ================================================ */
99 BEGIN
100 OPEN Cur_check_dup;
101 FETCH Cur_check_dup INTO l_ret;
102 IF (Cur_check_dup%FOUND) THEN
103 CLOSE Cur_check_dup;
104 RAISE duplicate_routing;
105 END IF;
106 CLOSE Cur_check_dup;
107 RETURN 0;
108 EXCEPTION
109 WHEN duplicate_routing THEN
110 IF (pcalledby_form = 'T') THEN
111 FND_MESSAGE.SET_NAME('GMD', 'GMD_DUP_ROUTING');
112 APP_EXCEPTION.RAISE_EXCEPTION;
113 ELSE
114 RETURN GMD_ROUTING_EXISTS;
115 END IF;
116 END check_duplicate_routing;
117
118 /* ==================================================================== */
119 /* FUNCTION: */
120 /* check_routing_class */
121 /* */
122 /* DESCRIPTION: */
123 /* This PL/SQL function is responsible for */
124 /* checking the validity of the routing class. */
125 /* */
126 /* REQUIREMENTS */
127 /* routing_class should be non null value. */
128 /* */
129 /* SYNOPSIS: */
130 /* l_ret := gmdrtval_pub.check_routing_class(prouting_class, pcalledby_form); */
131 /* */
132 /* RETURNS: */
133 /* 0 Success */
134 /* -1 Some required fields for the procedure are missing. */
135 /* -51 GMD_INV_ROUTING_CLASS Routing class is not valid. */
136 /* ==================================================================== */
137 FUNCTION check_routing_class(prouting_class IN VARCHAR2,
138 pcalledby_form IN VARCHAR2) RETURN NUMBER IS
139 /* Cursor Definitions. */
140 /* =================== */
141 CURSOR Cur_routing_class IS
142 SELECT 1
143 FROM SYS.DUAL
144 WHERE EXISTS (SELECT 1
145 FROM fm_rout_cls
146 WHERE routing_class = prouting_class
147 AND delete_mark = 0);
148 /* Local variables. */
149 /* ================ */
150 l_ret NUMBER;
151 inv_routing_class EXCEPTION;
152 BEGIN
153 OPEN Cur_routing_class;
154 FETCH Cur_routing_class INTO l_ret;
155 IF (Cur_routing_class%NOTFOUND) THEN
156 CLOSE Cur_routing_class;
157 RAISE inv_routing_class;
158 END IF;
159 CLOSE Cur_routing_class;
160 RETURN 0;
161 EXCEPTION
162 WHEN inv_routing_class THEN
163 IF (pcalledby_form = 'T') THEN
164 FND_MESSAGE.SET_NAME('GMD', 'FM_INVROUTCLASS');
165 APP_EXCEPTION.RAISE_EXCEPTION;
166 ELSE
167 RETURN GMD_INV_ROUTING_CLASS;
168 END IF;
169 END check_routing_class;
170
171 /* ==================================================================== */
172 /* FUNCTION: */
173 /* check_routingstep_no */
174 /* */
175 /* DESCRIPTION: */
176 /* This PL/SQL function is responsible for */
177 /* checking the duplication of routing step numbers. */
178 /* */
179 /* REQUIREMENTS */
180 /* routing_id and routingstep_no should be non null values. */
181 /* */
182 /* SYNOPSIS: */
183 /* l_ret := gmdrtval_pub.check_routingstep_no(proutingstep_no, */
184 /* prouting_id, */
185 /* pcalledby_form); */
186 /* */
187 /* RETURNS: */
188 /* 0 Success */
189 /* -1 Some required fields for the procedure are missing. */
190 /* -52 GMD_DUP_ROUTINGSTEP_NO Duplicate routing step number. */
191 /* ==================================================================== */
192 FUNCTION check_routingstep_no(proutingstep_no IN NUMBER, prouting_id IN NUMBER,
193 pcalledby_form IN VARCHAR2) RETURN NUMBER IS
194 /* Cursor Definitions. */
195 /* =================== */
196 CURSOR Cur_dup_routingstep IS
197 SELECT 1
198 FROM SYS.DUAL
199 WHERE EXISTS (SELECT 1
200 FROM fm_rout_dtl
201 WHERE routing_id = prouting_id
202 AND routingstep_no = proutingstep_no);
203 /* Local variables. */
204 /* ================ */
205 l_ret NUMBER;
206 dup_routingstep_no EXCEPTION;
207 BEGIN
208 OPEN Cur_dup_routingstep;
209 FETCH Cur_dup_routingstep INTO l_ret;
210 IF (Cur_dup_routingstep%FOUND) THEN
211 CLOSE Cur_dup_routingstep;
212 RAISE dup_routingstep_no;
213 END IF;
214 CLOSE Cur_dup_routingstep;
215 RETURN 0;
216 EXCEPTION
217 WHEN dup_routingstep_no THEN
218 IF (pcalledby_form = 'T') THEN
219 FND_MESSAGE.SET_NAME('GMD', 'FM_RTSTEPERR');
220 APP_EXCEPTION.RAISE_EXCEPTION;
221 ELSE
222 RETURN GMD_DUP_ROUTINGSTEP_NO;
223 END IF;
224 END check_routingstep_no;
225
226
227 /*- =========================================================== */
228 /* Procedure Check_Routing in GMDRTVAL_PUB package */
229 /* */
230 /* Decription */
231 /* Check if the routing header exists in the database. */
232 /* If all 3 parameters are sent in, validates no/vers rather than id */
233 /* HISTORY */
234 /* L.R.Jackson 19Apr2001 Fixed references to routing_id_cur */
235 /* Bug 1745549 (There were 2 typo's). Added exception section */
236 /* Removed x_return_status variable (unnecessary). */
237 /* ========================================================== */
238 PROCEDURE check_routing(pRouting_no IN VARCHAR2,
239 pRouting_vers IN NUMBER,
240 xRouting_id IN OUT NOCOPY NUMBER,
241 xReturn_status OUT NOCOPY VARCHAR2) IS
242 CURSOR Routing_No_Cur IS
243 select routing_id
244 from fm_rout_hdr
245 where routing_no = pRouting_no AND
246 routing_vers = pRouting_vers;
247
248 Cursor Routing_id_cur IS
249 select routing_id
250 from fm_rout_hdr
251 where routing_id = xRouting_id;
252
253 l_return_val NUMBER;
254
255 BEGIN
256 xReturn_status := 'S';
257 IF (xRouting_id IS NULL) THEN
258 OPEN Routing_No_Cur;
259 Fetch Routing_No_Cur into l_return_val;
260 IF Routing_No_Cur%NOTFOUND then
261 xReturn_status := 'E';
262 END IF;
263 CLOSE Routing_No_Cur;
264 ELSE
265 OPEN Routing_Id_Cur;
266 Fetch Routing_Id_Cur into l_return_val;
267 IF Routing_ID_Cur%NOTFOUND then
268 xReturn_status := 'E';
269 END IF;
270 CLOSE Routing_Id_Cur;
271
272 END IF;
273 xrouting_id := l_return_val;
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
278 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
279 FND_MSG_PUB.ADD;
280 xReturn_status := FND_API.G_RET_STS_UNEXP_ERROR;
281 END check_routing;
282
283
284
285 /* ==================================================================== */
286 /* FUNCTION: */
287 /* check_oprn */
288 /* */
289 /* DESCRIPTION: */
290 /* This PL/SQL function is responsible for */
291 /* checking the validity of the operation. */
292 /* */
293 /* REQUIREMENTS */
294 /* oprn_no and oprn_vers should be non null values. */
295 /* */
296 /* SYNOPSIS: */
297 /* l_ret := gmdrtval_pub.check_oprn(poprn_no, poprn_vers, */
298 /* pcalledby_form); */
299 /* */
300 /* RETURNS: */
301 /* 0 Success */
302 /* -1 Some required fields for the procedure are missing. */
303 /* -53 GMD_INV_OPRN Invalid operation. */
304 /* ==================================================================== */
305 FUNCTION check_oprn(poprn_no IN VARCHAR2, poprn_vers IN NUMBER,
306 prouting_start_date IN DATE,
307 pcalledby_form IN VARCHAR2,
308 poprn_id IN NUMBER,
309 prouting_end_date IN DATE) RETURN NUMBER IS
310 /* Cursor Definitions. */
311 /* =================== */
312 CURSOR Cur_get_oprn IS
313 SELECT oprn_no, oprn_vers, effective_start_date, effective_end_date
314 FROM gmd_operations_b
315 WHERE ((poprn_id IS NOT NULL AND oprn_id = poprn_id)
316 OR (poprn_id IS NULL AND (oprn_no = poprn_no
317 AND oprn_vers = poprn_vers)
318 )
319 )
320 AND delete_mark = 0;
321
322 /* Local variables. */
323 /* ================ */
324 l_rec Cur_get_oprn%ROWTYPE;
325 l_ret NUMBER;
326 inv_oprn EXCEPTION;
327 BEGIN
328 OPEN Cur_get_oprn;
329 FETCH Cur_get_oprn INTO l_rec;
330 IF (Cur_get_oprn%NOTFOUND) THEN
331 CLOSE Cur_get_oprn;
332 RAISE inv_oprn;
333 END IF;
334 CLOSE Cur_get_oprn;
335
336 IF (l_rec.effective_start_date > prouting_start_date) THEN
337 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_FROM_DATE');
338 FND_MESSAGE.SET_TOKEN('OPRN_NO', l_rec.oprn_no);
339 FND_MESSAGE.SET_TOKEN('VERSION_NO', l_rec.oprn_vers);
340 FND_MESSAGE.SET_TOKEN('OPRN_DATE', l_rec.effective_start_date);
341 IF pcalledby_form = 'T' THEN
342 APP_EXCEPTION.RAISE_EXCEPTION;
343 ELSE
344 FND_MSG_PUB.ADD;
345 RETURN GMD_INV_OPRN;
346 END IF;
347 END IF;
348
349 IF (l_rec.effective_end_date IS NOT NULL) AND
350 (l_rec.effective_end_date < NVL(prouting_end_date, l_rec.effective_end_date + 1)) THEN
351 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUTING_TO_DATE');
352 FND_MESSAGE.SET_TOKEN('OPRN_NO', l_rec.oprn_no);
353 FND_MESSAGE.SET_TOKEN('VERSION_NO', l_rec.oprn_vers);
354 FND_MESSAGE.SET_TOKEN('OPRN_DATE', l_rec.effective_end_date);
355 IF pcalledby_form = 'T' THEN
356 APP_EXCEPTION.RAISE_EXCEPTION;
357 ELSE
358 FND_MSG_PUB.ADD;
359 RETURN GMD_INV_OPRN;
360 END IF;
361 END IF;
362
363 RETURN 0;
364 EXCEPTION
365 WHEN inv_oprn THEN
366 FND_MESSAGE.SET_NAME('GMD', 'QC_INVOPRN');
367 IF (pcalledby_form = 'T') THEN
368 APP_EXCEPTION.RAISE_EXCEPTION;
369 ELSE
370 FND_MSG_PUB.ADD;
371 RETURN GMD_INV_OPRN;
372 END IF;
373 END check_oprn;
374
375 /*====================================================================== */
376 /* FUNCTION: */
377 /* circular_dependencies_exist */
378 /* */
379 /* DESCRIPTION: */
380 /* This PL/SQL function is responsible for */
381 /* checking the circular references in the routing step dependencies */
382 /* */
383 /* REQUIREMENTS */
384 /* parent_key routing_id or batch_id should be non null values. */
385 /* called_from_batch could be null if checking at the routing level. */
386 /* SYNOPSIS: */
387 /* l_ret := gmdrtval_pub.circular_dependencies_exist(prouting_id, */
388 /* 0); */
389 /* */
390 /* RETURNS: */
391 /* TRUE Dependencies Exist */
392 /* FALSE No Circular dependencies exist */
393 /*===================================================================== */
394
395 FUNCTION circular_dependencies_exist (pparent_key IN NUMBER,
396 pcalled_from_batch IN NUMBER)
397 RETURN BOOLEAN IS
398
399 /* Cursor Definitions. */
400 /* =================== */
401 CURSOR Cur_get_routing_steps IS
402 SELECT distinct routingstep_no
403 FROM fm_rout_dep
404 WHERE routing_id = pparent_key;
405 X_rout_step_rec Cur_get_routing_steps%ROWTYPE;
406
407 CURSOR Cur_get_batch_steps IS
408 SELECT distinct batchstep_id
409 FROM gme_batch_step_dependencies
410 WHERE batch_id = pparent_key;
411 X_batch_step_rec Cur_get_batch_steps%ROWTYPE;
412
413 CURSOR Cur_check_rout_step_depen (V_routingstep_no NUMBER) IS
414 SELECT max(routingstep_no)
415 FROM (SELECT * FROM FM_ROUT_DEP WHERE routing_id = pparent_key)
416 START WITH routingstep_no = V_routingstep_no
417 CONNECT BY (PRIOR dep_routingstep_no = routingstep_no)
418 AND (PRIOR routing_id = routing_id);
419
420 CURSOR Cur_check_batch_step_depen (V_batchstep_id NUMBER) IS
421 SELECT max(batchstep_id)
422 FROM gme_batch_step_dependencies
423 START WITH batch_id = pparent_key AND
424 batchstep_id = V_batchstep_id
425 CONNECT BY (PRIOR dep_step_id = batchstep_id)
426 AND (PRIOR batch_id = batch_id);
427
428 /* Local variables. */
429 /* ================ */
430 l_step NUMBER;
431
432 circular_reference EXCEPTION;
433 PRAGMA EXCEPTION_INIT(circular_reference, -01436);
434 BEGIN
435 IF pcalled_from_batch = 0 THEN
436 FOR X_rout_step_rec IN Cur_get_routing_steps
437 LOOP
438 OPEN Cur_check_rout_step_depen (X_rout_step_rec.routingstep_no);
439 FETCH Cur_check_rout_step_depen INTO l_step;
440 CLOSE Cur_check_rout_step_depen;
441 END LOOP;
442 ELSE
443 FOR X_batch_step_rec IN Cur_get_batch_steps
444 LOOP
445 OPEN Cur_check_batch_step_depen (X_batch_step_rec.batchstep_id);
446 FETCH Cur_check_batch_step_depen INTO l_step;
447 CLOSE Cur_check_batch_step_depen;
448 END LOOP;
449 END IF;
450 RETURN FALSE;
451 EXCEPTION
452 WHEN circular_reference THEN
453 RETURN TRUE;
454 END circular_dependencies_exist;
455
456 /*====================================================================== */
457 /* PROCEDURE : */
458 /* generate_step_dependencies */
459 /* */
460 /* DESCRIPTION: */
461 /* This PL/SQL procedure is responsible for generating step */
462 /* dependencies in sequential manner. */
463 /* */
464 /* REQUIREMENTS */
465 /* prouting_id non null value. */
466 /* SYNOPSIS: */
467 /* gmdrtval_pub.generate_step_dependencies(prouting_id); */
468 /* */
469 /* */
470 /*===================================================================== */
471
472 PROCEDURE generate_step_dependencies
473 ( prouting_id IN NUMBER,
474 x_return_status OUT NOCOPY VARCHAR2) IS
475
476 /* Cursor Definitions. */
477 /* =================== */
478 CURSOR Cur_get_routing_steps IS
479 SELECT routingstep_no
480 FROM fm_rout_dtl
481 WHERE routing_id = prouting_id
482 ORDER BY routingstep_no desc;
483
484 X_rout_step_rec Cur_get_routing_steps%ROWTYPE;
485
486 CURSOR Cur_get_oprn_uom (V_step_no NUMBER) IS
487 SELECT process_qty_uom
488 FROM gmd_operations o, fm_rout_dtl d
489 WHERE d.routing_id = prouting_id
490 AND d.routingstep_no = V_step_no
491 AND d.oprn_id = o.oprn_id;
492
493 X_oprn_uom_rec Cur_get_oprn_uom%ROWTYPE;
494
495 /* Local variables. */
496 /* ================ */
497 l_rout_step_no NUMBER;
498 l_user_id NUMBER;
499
500 /* Record Buffers. */
501 /*=================*/
502 l_rout_dep FM_ROUT_DEP%ROWTYPE;
503 /* Exceptions. */
504 /* ================ */
505 missing_details EXCEPTION;
506 insert_failure EXCEPTION;
507 BEGIN
508 l_user_id := FND_PROFILE.VALUE('USER_ID');
509 DELETE FROM fm_rout_dep
510 WHERE routing_id = prouting_id;
511 OPEN Cur_get_routing_steps;
512 FETCH Cur_get_routing_steps INTO X_rout_step_rec;
513 IF Cur_get_routing_steps%FOUND THEN
514 WHILE Cur_get_routing_steps%FOUND
515 LOOP
516 l_rout_step_no := X_rout_step_rec.routingstep_no;
517 FETCH Cur_get_routing_steps INTO X_rout_step_rec;
518 IF Cur_get_routing_steps%FOUND THEN
519
520 /* Get the transfer uom from the operation associated with
521 the dependent step */
522 OPEN Cur_get_oprn_uom (X_rout_step_rec.routingstep_no);
523 FETCH Cur_get_oprn_uom INTO X_oprn_uom_rec;
524 CLOSE Cur_get_oprn_uom;
525
526 l_rout_dep.routingstep_no := l_rout_step_no;
527 l_rout_dep.dep_routingstep_no := X_rout_step_rec.routingstep_no;
528 l_rout_dep.routing_id := prouting_id;
529 l_rout_dep.dep_type := 0;
530 l_rout_dep.standard_delay := 0;
531 l_rout_dep.minimum_delay := 0;
532 l_rout_dep.max_delay := 0;
533 l_rout_dep.transfer_qty := 0;
534 l_rout_dep.routingstep_no_uom := X_oprn_uom_rec.process_qty_uom;
535 l_rout_dep.last_updated_by := l_user_id;
536 l_rout_dep.created_by := l_user_id;
537 l_rout_dep.last_update_date := SYSDATE;
538 l_rout_dep.creation_date := SYSDATE;
539 l_rout_dep.transfer_pct := 100;
540
541 IF NOT FM_ROUT_DEP_DBL.insert_row (l_rout_dep) THEN
542 CLOSE Cur_get_routing_steps;
543 RAISE INSERT_FAILURE;
544 END IF;
545 END IF;
546 END LOOP;
547 ELSE
548 CLOSE Cur_get_routing_steps;
549 RAISE missing_details;
550 END IF;
551 /* Bug 2454861 - Thomas Daniel */
552 /* Added the closing of the open cursor */
553 CLOSE Cur_get_routing_steps;
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555 EXCEPTION
556 WHEN missing_details THEN
557 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_DETAILS_MISSING');
558 FND_MSG_PUB.ADD;
559 x_return_status := FND_API.G_RET_STS_ERROR;
560 WHEN INSERT_FAILURE THEN
561 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
562 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
563 FND_MSG_PUB.ADD;
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 WHEN OTHERS THEN
566 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
567 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
568 FND_MSG_PUB.ADD;
569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
570 END generate_step_dependencies;
571
572
573 /*====================================================================== */
574 /* PROCEDURE : */
575 /* generate_step_dependencies */
576 /* */
577 /* DESCRIPTION: */
578 /* This is a Overloaded PL/SQL procedure is responsible for generating step */
579 /* dependencies in sequential manner. */
580 /* */
581 /* REQUIREMENTS */
582 /* prouting_id non null value. */
583 /* pDep_type non null value */
584 /* SYNOPSIS: */
585 /* gmdrtval_pub.generate_step_dependencies(prouting_id, pDep_type, x_return_status); */
586 /* */
587 /* */
588 /*===================================================================== */
589
590 PROCEDURE generate_step_dependencies
591 ( prouting_id IN NUMBER,
592 pDep_Type IN NUMBER,
593 x_return_status OUT NOCOPY VARCHAR2) IS
594
595 /* Cursor Definitions. */
596 /* =================== */
597 CURSOR Cur_get_routing_steps IS
598 SELECT routingstep_no
599 FROM fm_rout_dtl
600 WHERE routing_id = prouting_id
601 ORDER BY routingstep_no desc;
602
603 X_rout_step_rec Cur_get_routing_steps%ROWTYPE;
604
605 CURSOR Cur_get_oprn_uom (V_step_no NUMBER) IS
606 SELECT process_qty_uom
607 FROM gmd_operations o, fm_rout_dtl d
608 WHERE d.routing_id = prouting_id
609 AND d.routingstep_no = V_step_no
610 AND d.oprn_id = o.oprn_id;
611
612 X_oprn_uom_rec Cur_get_oprn_uom%ROWTYPE;
613
614 /* Local variables. */
615 /* ================ */
616 l_rout_step_no NUMBER;
617 l_user_id NUMBER;
618
619 /* Record Buffers. */
620 /*=================*/
621 l_rout_dep FM_ROUT_DEP%ROWTYPE;
622 /* Exceptions. */
623 /* ================ */
624 missing_details EXCEPTION;
625 insert_failure EXCEPTION;
626 BEGIN
627 l_user_id := FND_PROFILE.VALUE('USER_ID');
628 DELETE FROM fm_rout_dep
629 WHERE routing_id = prouting_id;
630 OPEN Cur_get_routing_steps;
631 FETCH Cur_get_routing_steps INTO X_rout_step_rec;
632 IF Cur_get_routing_steps%FOUND THEN
633 WHILE Cur_get_routing_steps%FOUND
634 LOOP
635 l_rout_step_no := X_rout_step_rec.routingstep_no;
636 FETCH Cur_get_routing_steps INTO X_rout_step_rec;
637 IF Cur_get_routing_steps%FOUND THEN
638
639 /* Get the transfer uom from the operation associated with
640 the dependent step */
641 OPEN Cur_get_oprn_uom (X_rout_step_rec.routingstep_no);
642 FETCH Cur_get_oprn_uom INTO X_oprn_uom_rec;
643 CLOSE Cur_get_oprn_uom;
644
645 l_rout_dep.routingstep_no := l_rout_step_no;
646 l_rout_dep.dep_routingstep_no := X_rout_step_rec.routingstep_no;
647 l_rout_dep.routing_id := prouting_id;
648 l_rout_dep.dep_type := pDep_Type;
649 l_rout_dep.standard_delay := 0;
650 l_rout_dep.minimum_delay := 0;
651 l_rout_dep.max_delay := 0;
652 l_rout_dep.transfer_qty := 0;
653 l_rout_dep.routingstep_no_uom := X_oprn_uom_rec.process_qty_uom;
654 l_rout_dep.last_updated_by := l_user_id;
655 l_rout_dep.created_by := l_user_id;
656 l_rout_dep.last_update_date := SYSDATE;
657 l_rout_dep.creation_date := SYSDATE;
658 l_rout_dep.transfer_pct := 100;
659
660 IF NOT FM_ROUT_DEP_DBL.insert_row (l_rout_dep) THEN
661 CLOSE Cur_get_routing_steps;
662 RAISE INSERT_FAILURE;
663 END IF;
664 END IF;
665 END LOOP;
666 ELSE
667 CLOSE Cur_get_routing_steps;
668 RAISE missing_details;
669 END IF;
670 /* Bug 2454861 - Thomas Daniel */
671 /* Added the closing of the open cursor */
672 CLOSE Cur_get_routing_steps;
673 x_return_status := FND_API.G_RET_STS_SUCCESS;
674 EXCEPTION
675 WHEN missing_details THEN
676 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_DETAILS_MISSING');
677 FND_MSG_PUB.ADD;
678 x_return_status := FND_API.G_RET_STS_ERROR;
679 WHEN INSERT_FAILURE THEN
680 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
681 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
682 FND_MSG_PUB.ADD;
683 x_return_status := FND_API.G_RET_STS_ERROR;
684 WHEN OTHERS THEN
685 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
686 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
687 FND_MSG_PUB.ADD;
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 END generate_step_dependencies;
690
691
692 /* ================================================= */
693 /* Chceks for overall in max qty's before */
694 /* updating the max qty values */
695 /* ================================================= */
696 PROCEDURE Get_process_loss_max_qtys( pRouting_class IN VARCHAR2,
697 pFromMaxQty IN NUMBER,
698 pToMaxQty IN NUMBER,
699 max_quantity OUT NOCOPY max_qty_tbl ,
700 x_return_status OUT NOCOPY VARCHAR2) IS
701
702 l_rows NUMBER := 1;
703
704 Cursor max_qty_cur(pRouting_class VARCHAR2, pFromMaxQty NUMBER, pToMaxQty NUMBER) IS
705 SELECT max_quantity
706 FROM gmd_process_loss
707 WHERE routing_class = pRouting_class AND
708 max_quantity > pFromMaxQty AND
709 max_quantity <= pToMaxQty;
710
711 BEGIN
712
713 /* Initialize the return status */
714 x_return_status := 'S';
715
716 /* Open the cursor and start fetching each row into the table */
717 FOR max_qty_rec IN max_qty_cur(pRouting_class, pFromMaxQty, pToMaxQty) LOOP
718 EXIT WHEN max_qty_cur%NOTFOUND;
719 max_quantity(l_rows) := max_qty_rec.max_quantity;
720 l_rows := l_rows + 1;
721 END LOOP;
722
723 EXCEPTION
724 WHEN OTHERS THEN
725 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
726 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
727 FND_MSG_PUB.ADD;
728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
729
730 END Get_process_loss_max_qtys;
731
732 /* =================================================== */
733 /* Checks if there is atleast one operation associated */
734 /* for this routing */
735 /* =================================================== */
736 PROCEDURE Validate_Routing_Details( pRouting_id IN NUMBER,
737 x_msg_count OUT NOCOPY NUMBER,
738 x_msg_stack OUT NOCOPY VARCHAR2,
739 x_return_status OUT NOCOPY VARCHAR2) IS
740
741 l_oprn_count NUMBER := 0;
742
743 CURSOR check_oprn_count IS
744 SELECT count(*) FROM gmd_operations_b
745 WHERE oprn_id IN (select oprn_id from fm_rout_dtl where routing_id = pRouting_id);
746
747 BEGIN
748 x_return_status := 'S';
749
750 OPEN check_oprn_count;
751 FETCH check_oprn_count INTO l_oprn_count;
752 IF (check_oprn_count%NOTFOUND) THEN
753 x_return_status := 'E';
754 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_DTL_REQD');
755 FND_MSG_PUB.ADD;
756 END IF;
757 CLOSE check_oprn_count;
758
759 IF (l_oprn_count = 0) THEN
760 x_return_status := 'E';
761 FND_MESSAGE.SET_NAME('GMD', 'GMD_ROUT_DTL_REQD');
762 FND_MSG_PUB.ADD;
763 END IF;
764
765 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
766 P_data => x_msg_stack);
767 EXCEPTION
768 WHEN OTHERS THEN
769 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
770 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
771 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
772 FND_MSG_PUB.ADD;
773 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
774 P_data => x_msg_stack);
775
776 END Validate_Routing_Details;
777
778 /* ==================================================================== */
779 /* This procedure verifies that the routing effective dates falls */
780 /* within all recipe validity rules that are using the routing. */
781 /* Basically, the routing must be valid during the entire life */
782 /* of the recipes using this routing. */
783 /* ==================================================================== */
784 PROCEDURE Validate_Routing_VR_Dates( pRouting_id IN NUMBER,
785 x_msg_count OUT NOCOPY NUMBER,
786 x_msg_stack OUT NOCOPY VARCHAR2,
787 x_return_status OUT NOCOPY VARCHAR2) IS
788
789 l_vr_count NUMBER := 0;
790
791 CURSOR check_VR_Rout_dates IS
792 select count(*)
793 from gmd_recipe_validity_rules v, gmd_recipes_b r, gmd_routings_b rt
794 where v.recipe_id = r.recipe_id
795 and r.routing_id = rt.routing_id
796 and rt.routing_id = pRouting_id
797 and rt.effective_start_date < v.start_date
798 and (v.end_date IS NULL OR rt.effective_end_date > v.end_date);
799
800 BEGIN
801 x_return_status := 'S';
802
803 OPEN check_VR_Rout_dates;
804 FETCH check_VR_Rout_dates INTO l_vr_count;
805 IF (check_VR_Rout_dates%FOUND) THEN
806 x_return_status := 'E';
807 FND_MESSAGE.SET_NAME('GMD', 'GMD_UPD_RECP_VR');
808 FND_MSG_PUB.ADD;
809 END IF;
810 CLOSE check_VR_Rout_dates;
811
812 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
813 P_data => x_msg_stack);
814 EXCEPTION
815 WHEN OTHERS THEN
816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
817 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
818 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
819 FND_MSG_PUB.ADD;
820 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
821 P_data => x_msg_stack);
822
823 END Validate_Routing_VR_Dates;
824
825 /* =================================================== */
826 /* This procedure updates the start and end dates of
827 the validity rules with the given start and end dates.
828 This procedure is used when the routing effective
829 dates falls outside the validity rules date range. */
830 /* =================================================== */
831 PROCEDURE Update_VR_with_Rt_Dates( pRouting_id IN NUMBER,
832 x_msg_count OUT NOCOPY NUMBER,
833 x_msg_stack OUT NOCOPY VARCHAR2,
834 x_return_status OUT NOCOPY VARCHAR2) IS
835
836 l_vr_id NUMBER := 0;
837
838 CURSOR check_VR_Rout_dates IS
839 select v.recipe_validity_rule_id vr_id , rt.effective_start_date start_date, rt.effective_end_date end_date
840 from gmd_recipe_validity_rules v, gmd_recipes_b r, gmd_routings_b rt
841 where v.recipe_id = r.recipe_id
842 and r.routing_id = rt.routing_id
843 and rt.routing_id = pRouting_id
844 and rt.effective_start_date < v.start_date
845 and (v.end_date IS NULL OR rt.effective_end_date > v.end_date);
846
847 BEGIN
848 x_return_status := 'S';
849
850 FOR update_vr_rec IN check_VR_Rout_dates LOOP
851 UPDATE gmd_recipe_validity_rules
852 SET start_date = update_vr_rec.start_date ,
853 end_date = update_vr_rec.end_date
854 WHERE recipe_validity_rule_id = update_vr_rec.vr_id;
855 END LOOP;
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
860 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
861 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
862 FND_MSG_PUB.ADD;
863 FND_MSG_PUB.COUNT_AND_GET (P_count => x_msg_count,
864 P_data => x_msg_stack);
865
866 END Update_VR_with_Rt_Dates;
867
868 /* =================================================== */
869 /* This procedure get the routingStep_id when the */
870 /* routingStep_no and routing_id is passed */
871 /* =================================================== */
872 PROCEDURE get_routingstep_info(pRouting_id IN gmd_routings.routing_id%TYPE := NULL
873 ,pxRoutingStep_no IN OUT NOCOPY fm_rout_dtl.routingStep_no%TYPE
874 ,pxRoutingStep_id IN OUT NOCOPY fm_rout_dtl.routingStep_id%TYPE
875 ,x_return_status OUT NOCOPY VARCHAR2 ) IS
876
877 CURSOR get_routingStep_id(vRouting_id gmd_routings.routing_id%TYPE
878 ,vRoutingStep_no fm_rout_dtl.routingStep_no%TYPE) IS
879 Select routingStep_id
880 From fm_rout_dtl
881 Where routingStep_no = vRoutingStep_no AND
882 routing_id = vRouting_id;
883
884 CURSOR get_routingStep_no(vRoutingStep_id fm_rout_dtl.routingStep_id%TYPE) IS
885 Select routingStep_no
886 From fm_rout_dtl
887 Where routingStep_id = vRoutingStep_id;
888
889 BEGIN
890
891 x_return_status := 'S';
892 IF pxRoutingStep_id IS NULL THEN
893 IF (l_debug = 'Y') THEN
894 gmd_debug.put_line(' Rt step id is null and Rt step no = '||pxRoutingStep_no);
895 END IF;
896 /* User needs to pass the routing_id and step no */
897 OPEN get_routingStep_id(pRouting_id,pxRoutingStep_no);
898 FETCH get_routingStep_id INTO pxRoutingStep_id;
899 IF get_routingStep_id%NOTFOUND THEN
900 x_return_status := 'E';
901 END IF;
902 CLOSE get_routingStep_id;
903 ELSE
904 IF (l_debug = 'Y') THEN
905 gmd_debug.put_line(' RT step id is not null = '||pxRoutingStep_id);
906 END IF;
907
908 OPEN get_routingStep_no(pxRoutingStep_id);
909 FETCH get_routingStep_no INTO pxRoutingStep_no;
910 IF get_routingStep_no%NOTFOUND THEN
911 x_return_status := 'E';
912 END IF;
913 CLOSE get_routingStep_no;
914 END IF;
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
919
920 END get_routingstep_info;
921
922
923 /* ****************************************************************
924 * Function Check_routing_overide_exists
925 *
926 * Check if this routing step is overriden at Recipe level.
927 * Called by Routing detail API.
928 *
929 * Returns - True - if step exists at recipe level.
930 * False - if step does not exists at recipe level
931 *
932 *
933 * *************************************************************** */
934 FUNCTION Check_routing_override_exists(p_routingstep_id NUMBER)
935 RETURN BOOLEAN IS
936
937 X_temp NUMBER;
938 X_return BOOLEAN := FALSE;
939 /* Define cursor */
940 CURSOR Cur_check_step(v_routingstep_id NUMBER) IS
941 SELECT 1
942 FROM SYS.DUAL
943 WHERE EXISTS (SELECT 1
944 FROM gmd_recipe_routing_steps
945 WHERE routingstep_id = v_routingstep_id);
946
947 CURSOR Cur_check_step2(v_routingstep_id NUMBER) IS
948 SELECT 1
949 FROM gmd_recipe_step_materials
950 WHERE routingstep_id = v_routingstep_id;
951
952 BEGIN
953 IF (p_routingstep_id IS NOT NULL) THEN
954 IF (l_debug = 'Y') THEN
955 gmd_debug.put_line(' In Check_routing_override_exists() with Rt Step id = '||p_RoutingStep_id);
956 END IF;
957
958 OPEN Cur_check_step(p_routingstep_id);
959 FETCH Cur_check_step INTO X_temp;
960 IF (Cur_check_step%FOUND) THEN
961 CLOSE Cur_check_step;
962 FND_MESSAGE.SET_NAME('GMD', 'GMD_STEP_USED_IN_RECIPE');
963 RETURN TRUE;
964 END IF;
965 CLOSE Cur_check_step;
966
967 IF (l_debug = 'Y') THEN
968 gmd_debug.put_line(' In Check_routing_override_exists() '
969 ||' after check in Recipe Step level ');
970 END IF;
971
972 -- bug 1856832. Check step/mat in addition to above check of rtg step
973 OPEN Cur_check_step2(p_routingstep_id);
974 FETCH Cur_check_step2 INTO X_temp;
975 IF (Cur_check_step2%FOUND) THEN
976 CLOSE Cur_check_step2;
977 FND_MESSAGE.SET_NAME('GMD', 'GMD_STEP_USED_IN_RECIPE');
978 RETURN TRUE;
979 END IF;
983 gmd_debug.put_line(' In Check_routing_override_exists() '
980 CLOSE Cur_check_step2;
981
982 IF (l_debug = 'Y') THEN
984 ||' after check in Recipe Mat level ');
985 END IF;
986
987 END IF;
988
989 RETURN X_return;
990 END Check_routing_override_exists;
991
992 /* ==================================================================== */
993 /* PROCEDURE: */
994 /* check_delete_mark */
995 /* */
996 /* DESCRIPTION: */
997 /* This PL/SQL procedure is responsible for */
998 /* checking the validity of the delete_mark. */
999 /* */
1000 /* REQUIREMENTS */
1001 /* delete_mark should be non null value. */
1002 /* */
1003 /* SYNOPSIS: */
1004 /* l_ret := gmdrtval_pub.check_delete_mark(pdelete_mark, pcalledby_form); */
1005 /* ==================================================================== */
1006
1007 PROCEDURE check_delete_mark(pdelete_mark IN NUMBER,
1008 x_return_status OUT NOCOPY VARCHAR2) IS
1009 inv_delete_mark EXCEPTION;
1010 BEGIN
1011 x_return_status := FND_API.G_RET_STS_SUCCESS;
1012 IF (pdelete_mark NOT IN (0,1)) THEN
1013 RAISE inv_delete_mark;
1014 END IF;
1015 EXCEPTION
1016 WHEN inv_delete_mark THEN
1017 x_return_status := 'E';
1018 FND_MESSAGE.SET_NAME('GMA', 'SY_BADDELETEMARK');
1019 FND_MSG_PUB.ADD;
1020 END check_delete_mark;
1021
1022 /* ==================================================================== */
1023 /* PROCEDURE: */
1024 /* check_ownerorgn_code */
1025 /* */
1026 /* DESCRIPTION: */
1027 /* This PL/SQL procedure is responsible for */
1028 /* checking the validity of the ownerorgn_code. */
1029 /* */
1030 /* REQUIREMENTS */
1031 /* validate ownerorgn_code. */
1032 /* */
1033 /* SYNOPSIS: */
1034 /* l_ret := gmdrtval_pub.check_ownerorgn_code(pownerorgn_code, pcalledby_form); */
1035 /* ==================================================================== */
1036
1037 PROCEDURE check_ownerorgn_code(powner_id IN NUMBER,powner_orgn IN VARCHAR2,
1038 x_return_status OUT NOCOPY VARCHAR2) IS
1039
1040 /* Cursor Definitions. */
1041 /* =================== */
1042 CURSOR Cur_ownerorgn_code IS
1043 SELECT 1
1044 FROM SYS.DUAL
1045 WHERE EXISTS (SELECT 1
1046 FROM sy_orgn_usr
1047 WHERE user_id = powner_id
1048 AND orgn_code = powner_orgn);
1049
1050 /* Local variables. */
1051 /* ================ */
1052 l_ret NUMBER;
1053 inv_owner_orgn_code EXCEPTION;
1054 BEGIN
1055 x_return_status := FND_API.G_RET_STS_SUCCESS;
1056 OPEN Cur_ownerorgn_code;
1057 FETCH Cur_ownerorgn_code INTO l_ret;
1058 IF (Cur_ownerorgn_code%NOTFOUND) THEN
1059 CLOSE Cur_ownerorgn_code;
1060 RAISE inv_owner_orgn_code;
1061 END IF;
1062 CLOSE Cur_ownerorgn_code;
1063 EXCEPTION
1064 WHEN inv_owner_orgn_code THEN
1065 x_return_status := 'E';
1066 FND_MESSAGE.SET_NAME('GMD', 'GMD_INV_USER_ORGANIZATION');
1067 FND_MSG_PUB.ADD;
1068 END check_ownerorgn_code;
1069
1070 /* ==================================================================== */
1071 /* PROCEDURE: */
1072 /* check_deprouting */
1073 /* */
1074 /* DESCRIPTION: */
1075 /* This PL/SQL procedure is responsible for */
1076 /* checking the validity of the dep_routingsteps. */
1077 /* */
1078 /* REQUIREMENTS */
1079 /* validate dep_routingsteps. */
1080 /* */
1081 /* SYNOPSIS: */
1082 /* l_ret := gmdrtval_pub.check_deprouting(pownerorgn_code,proutingStep_no,pdeproutingStep_no); */
1083 /* ==================================================================== */
1084
1085 PROCEDURE check_deprouting (prouting_id IN gmd_routings.routing_id%TYPE
1086 ,proutingStep_no IN fm_rout_dtl.routingStep_no%TYPE
1087 ,pdeproutingStep_no IN fm_rout_dep.dep_routingStep_no%TYPE
1088 ,x_return_status OUT NOCOPY VARCHAR2) IS
1089
1090 /* Cursor Definitions. */
1091 /* =================== */
1092 CURSOR Cur_step_no IS
1093 SELECT 1
1094 FROM SYS.DUAL
1095 WHERE EXISTS (SELECT 1
1096 FROM fm_rout_dep
1097 WHERE routing_id = prouting_id
1098 AND routingstep_no = proutingStep_no
1099 AND dep_routingstep_no = pdeproutingStep_no);
1100
1101 /* Local variables. */
1102 /* ================ */
1103 l_ret NUMBER;
1104 inv_dep_step EXCEPTION;
1105 BEGIN
1106 x_return_status := FND_API.G_RET_STS_SUCCESS;
1107 OPEN Cur_step_no;
1108 FETCH Cur_step_no INTO l_ret;
1109 IF (Cur_step_no%FOUND) THEN
1110 CLOSE Cur_step_no;
1111 RAISE inv_dep_step;
1112 END IF;
1113 CLOSE Cur_step_no;
1114 EXCEPTION
1115 WHEN inv_dep_step THEN
1116 x_return_status := 'E';
1117 FND_MESSAGE.SET_NAME('GME', 'PC_RECORD_EXISTS');
1118 FND_MSG_PUB.ADD;
1119 END check_deprouting;
1120
1121 /* ==================================================================== */
1122 /* FUNCTION: */
1123 /* get_fixed_process_loss */
1124 /* */
1125 /* DESCRIPTION: */
1126 /* This PL/SQL function is responsible for */
1127 /* retrieving the FIXED process loss for the routing class. */
1128 /* */
1129 /* REQUIREMENTS */
1130 /* routing_class should be a non null value. */
1131 /* */
1132 /* SYNOPSIS: */
1133 /* l_ret := gmdrtval_pub.get_theoretical_process_loss(prouting_class ); */
1134 /* */
1135 /* RETURNS: */
1136 /* Fixed Process loss value. */
1137 /* ==================================================================== */
1138 FUNCTION get_fixed_process_loss(prouting_class IN VARCHAR2) RETURN NUMBER IS
1139 /* Cursor Definitions. */
1140 /* =================== */
1141 CURSOR Cur_get_fixed_process_loss IS
1142 SELECT fixed_process_loss
1143 FROM fm_rout_cls
1144 WHERE routing_class = prouting_class ;
1145
1146 l_process_loss NUMBER;
1147 BEGIN
1148 IF (prouting_class IS NULL) THEN
1149 l_process_loss := 0;
1150 ELSE
1151 OPEN Cur_get_fixed_process_loss;
1152 FETCH Cur_get_fixed_process_loss INTO l_process_loss;
1153 IF (Cur_get_fixed_process_loss%NOTFOUND) THEN
1154 l_process_loss := NULL;
1155 END IF;
1156 CLOSE Cur_get_fixed_process_loss;
1157 END IF;
1158 RETURN l_process_loss;
1159 END get_fixed_process_loss;
1160
1161
1162 END GMDRTVAL_PUB;