[Home] [Help]
PACKAGE BODY: APPS.GMD_RECIPE_DESIGNER_PKG
Source
1 PACKAGE BODY GMD_RECIPE_DESIGNER_PKG AS
2 /* $Header: GMDRDMDB.pls 120.17 2009/03/23 17:22:05 rnalla ship $ */
3 /*============================================================================
4 | Copyright (c) 2001 Oracle Corporation
5 | Redwood Shores, California, USA
6 | All rights reserved
7 =============================================================================
8 | FILENAME
9 | GMDRDMDB.pls
10 |
11 | DESCRIPTION
12 | Package body containing the procedures used by the Recipe Designer
13 |
14 |
15 | NOTES
16 |
17 | HISTORY
18 | 03-JUL-2001 Eddie Oumerretane Created.
19 | 26-APR-2002 Eddie Oumerretane BUG #2342591, added ROLLBACK in
20 | Calculate_Step_Quantities after calculating charges.
21 | 30-MAY-2002 Eddie Oumerretane BUG #2396112, removed ROLLBACK in
22 | Calculate_Step_Quantities after calculating charges.
23 | 19-SEP-2002 Eddie Oumerretane. Enhancements related to implementation
24 | of Rapid Recipe features.
25 | 31-OCT-2003 Rajender Nalla. Bug 3157487 Added the gmd_formula_security_pkg.
26 | insert_row, delete_row are added in create_recipe_header procedure
27 | to add and delete the row with formula_id = -1.
28 | 27-APR-2004 S.Sriram Bug# 3408799
29 | Added SET_DEFAULT_STATUS procedure for Default Status Build
30 | 13-OCT-2004 Sriram.S Recipe Security Bug# 3948203
31 | Added a proc. to which checks if user has recipe orgn. access.
32 | 15-OCT-2004 Thomas Daniel Bug# 3953359
33 | Added code to set the default status appropriately in the copy
34 | recipe procedure.
35 | 22-Apr-2008 RLNAGARA Modified the proc Create_Recipe_Header for Fixed Process Loss ME
36 =============================================================================
37 */
38
39
40 /* Api start of comments
41 +============================================================================
42 | PROCEDURE NAME
43 | Create_Text_Row
44 |
45 | DESCRIPTION
46 | Create a row in FM_TEXT_TBL
47 |
48 | INPUT PARAMETERS
49 | p_text_code NUMBER
50 | p_lang_code VARCHAR2
51 | p_text VARCHAR2
52 | p_line_no NUMBER
53 | p_paragraph_code VARCHAR2
54 | p_sub_paracode NUMBER
55 | p_table_lnk VARCHAR2
56 | p_user_id NUMBER
57 |
58 | OUTPUT PARAMETERS
59 | x_return_code VARCHAR2(1)
60 | x_error_msg VARCHAR2(100)
61 |
62 | HISTORY
63 | 03-JUL-2001 Eddie Oumerretane Created.
64 |
65 +=============================================================================
66 Api end of comments
67 */
68
69 PROCEDURE Create_Text_Row ( p_text_code IN NUMBER,
70 p_lang_code IN VARCHAR2,
71 p_text IN VARCHAR2,
72 p_line_no IN NUMBER,
73 p_paragraph_code IN VARCHAR2,
74 p_sub_paracode IN NUMBER,
75 p_table_lnk IN VARCHAR2,
76 p_user_id IN NUMBER,
77 x_row_id OUT NOCOPY VARCHAR2,
78 x_return_code OUT NOCOPY VARCHAR2,
79 x_error_msg OUT NOCOPY VARCHAR2) IS
80 l_dummy NUMBER := 0;
81
82 BEGIN
83
84 x_return_code := 'S';
85 x_error_msg := '';
86
87
88 IF (p_line_no = 1) THEN
89
90 SELECT COUNT(*) INTO l_dummy
91 FROM fm_text_hdr WHERE text_code = p_text_code;
92
93 IF SQL%NOTFOUND OR l_dummy = 0 THEN
94
95 INSERT INTO fm_text_hdr ( text_code
96 ,last_update_date
97 ,last_updated_by
98 ,last_update_login
99 ,created_by
100 ,creation_date)
101 VALUES (p_text_code,
102 sysdate,
103 p_user_id,
104 p_user_id,
105 p_user_id,
106 sysdate);
107 END IF;
108
109 INSERT INTO fm_text_tbl_vl ( text_code
110 ,lang_code
111 ,paragraph_code
112 ,sub_paracode
113 ,line_no
114 ,text
115 ,last_update_date
116 ,last_updated_by
117 ,last_update_login
118 ,created_by
119 ,creation_date)
120 VALUES (p_text_code,
121 p_lang_code,
122 p_paragraph_code,
123 p_sub_paracode,
124 -1,
125 p_table_lnk,
126 sysdate,
127 p_user_id,
128 p_user_id,
129 p_user_id,
130 sysdate);
131
132 END IF;
133
134 GMA_FM_TEXT_TBL_PKG.INSERT_ROW(
135 X_ROWID => x_row_id,
136 X_TEXT_CODE => p_text_code,
137 X_PARAGRAPH_CODE => p_paragraph_code,
138 X_SUB_PARACODE => p_sub_paracode,
139 X_LINE_NO => p_line_no,
140 X_LANG_CODE => p_lang_code,
141 X_TEXT => p_text,
142 X_CREATION_DATE => sysdate,
143 X_CREATED_BY => p_user_id,
144 X_LAST_UPDATE_DATE => sysdate,
145 X_LAST_UPDATED_BY => p_user_id,
146 X_LAST_UPDATE_LOGIN => p_user_id);
147
148 EXCEPTION
149 WHEN OTHERS THEN
150 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
151 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
152 x_return_code := 'F';
153 x_error_msg := FND_MESSAGE.GET;
154
155 END Create_Text_Row;
156
157
158 /* Api start of comments
159 +============================================================================
160 | PROCEDURE NAME
161 | Update_Text_Row
162 |
163 | DESCRIPTION
164 | Update a row in FM_TEXT_TBL
165 |
166 | INPUT PARAMETERS
167 | p_text_code NUMBER
168 | p_lang_code VARCHAR2
169 | p_text VARCHAR2
170 | p_line_no NUMBER
171 | p_paragraph_code VARCHAR2
172 | p_sub_paracode NUMBER
173 | p_table_lnk VARCHAR2
174 | p_user_id NUMBER
175 |
176 | OUTPUT PARAMETERS
177 | x_return_code VARCHAR2(1)
178 | x_error_msg VARCHAR2(100)
179 |
180 | HISTORY
181 | 13-JUL-2001 Eddie Oumerretane Created.
182 |
183 +=============================================================================
184 Api end of comments
185 */
186
187 PROCEDURE Update_Text_Row ( p_text_code IN NUMBER,
188 p_lang_code IN VARCHAR2,
189 p_text IN VARCHAR2,
190 p_line_no IN NUMBER,
191 p_paragraph_code IN VARCHAR2,
192 p_sub_paracode IN NUMBER,
193 p_user_id IN NUMBER,
194 p_row_id IN VARCHAR2,
195 x_return_code OUT NOCOPY VARCHAR2,
196 x_error_msg OUT NOCOPY VARCHAR2) IS
197
198 BEGIN
199
200 x_return_code := 'S';
201 x_error_msg := '';
202
203 GMA_FM_TEXT_TBL_PKG.UPDATE_ROW(
204 X_ROW_ID => p_row_id,
205 X_TEXT_CODE => p_text_code,
206 X_LANG_CODE => p_lang_code,
207 X_PARAGRAPH_CODE => p_paragraph_code,
208 X_SUB_PARACODE => p_sub_paracode,
209 X_LINE_NO => p_line_no,
210 X_TEXT => p_text,
211 X_LAST_UPDATE_DATE => sysdate,
212 X_LAST_UPDATED_BY => p_user_id,
213 X_LAST_UPDATE_LOGIN => p_user_id);
214
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
219 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
220 x_return_code := 'F';
221 x_error_msg := FND_MESSAGE.GET;
222
223 END Update_Text_Row;
224
225 /* Api start of comments
226 +============================================================================
227 | PROCEDURE NAME
228 | Delete_Text_Row
229 |
230 | DESCRIPTION
231 | Delete a row in FM_TEXT_TBL
232 |
233 | INPUT PARAMETERS
234 | p_text_code NUMBER
235 | p_lang_code VARCHAR2
236 | p_paragraph_code VARCHAR2
237 | p_sub_paracode NUMBER
238 | p_line_no NUMBER
239 | p_row_id VARCHAR2
240 |
241 | OUTPUT PARAMETERS
242 | x_return_code VARCHAR2(1)
243 | x_error_msg VARCHAR2(100)
244 |
245 | HISTORY
246 | 13-JUL-2001 Eddie Oumerretane Created.
247 |
248 +=============================================================================
249 Api end of comments
250 */
251
252 PROCEDURE Delete_Text_Row ( p_text_code IN NUMBER,
253 p_lang_code IN VARCHAR2,
254 p_paragraph_code IN VARCHAR2,
255 p_sub_paracode IN NUMBER,
256 p_line_no IN NUMBER,
257 p_row_id IN VARCHAR2,
258 x_return_code OUT NOCOPY VARCHAR2,
259 x_error_msg OUT NOCOPY VARCHAR2) IS
260
261 BEGIN
262
263 x_return_code := 'S';
264 x_error_msg := '';
265
266 GMA_FM_TEXT_TBL_PKG.DELETE_ROW(
267 X_TEXT_CODE => p_text_code,
268 X_LANG_CODE => p_lang_code,
269 X_PARAGRAPH_CODE => p_paragraph_code,
270 X_SUB_PARACODE => p_sub_paracode,
271 X_LINE_NO => p_line_no,
272 X_ROW_ID => p_row_id);
273
274
275 EXCEPTION
276 WHEN OTHERS THEN
277 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
278 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
279 x_return_code := 'F';
280 x_error_msg := FND_MESSAGE.GET;
281
282 END Delete_Text_Row;
283
284 /* Api start of comments
285 +============================================================================
286 | PROCEDURE NAME
287 | Update_Recipe_Routing_step_Row
288 |
289 | DESCRIPTION
290 | Update a row in GMD_RECIPE_ROUTING_STEPS
291 |
292 | INPUT PARAMETERS
293 | p_recipe_id NUMBER
294 | p_routingstep_id NUMBER
295 | p_text_code NUMBER
296 | p_last_update_date DATE
297 | p_last_update_date_origin DATE
298 | p_user_id NUMBER
299 | p_step_qty NUMBER
300 | p_mass_qty NUMBER
301 | p_vol_qty NUMBER
302 | p_mass_uom VARCHAR2
303 | p_vol_uom VARCHAR2
304 |
305 | OUTPUT PARAMETERS
306 | x_return_code VARCHAR2(1)
307 | x_error_msg VARCHAR2(100)
308 |
309 | HISTORY
310 | 03-JUL-2001 Eddie Oumerretane Created.
311 |
312 +=============================================================================
313 Api end of comments
314 */
315
316 PROCEDURE Update_Recipe_Routing_Step_Row ( p_recipe_id IN NUMBER,
317 p_routingstep_id IN NUMBER,
318 p_text_code IN NUMBER,
319 p_last_update_date IN DATE,
320 p_last_update_date_origin IN DATE,
321 p_user_id IN NUMBER,
322 p_step_qty IN NUMBER,
323 p_mass_qty IN NUMBER,
324 p_vol_qty IN NUMBER,
325 p_mass_uom IN VARCHAR2,
326 p_vol_uom IN VARCHAR2,
327 x_return_code OUT NOCOPY VARCHAR2,
328 x_error_msg OUT NOCOPY VARCHAR2) IS
329
330 l_text_code NUMBER;
331
332 BEGIN
333
334 x_return_code := 'S';
335 x_error_msg := '';
336
337 IF p_text_code <= 0 THEN
338 l_text_code := NULL;
339 ELSE
340 l_text_code := p_text_code;
341 END IF;
342
343 UPDATE
344 GMD_RECIPE_ROUTING_STEPS
345 SET
346 STEP_QTY = p_step_qty,
347 TEXT_CODE = l_text_code,
348 LAST_UPDATED_BY = p_user_id,
349 LAST_UPDATE_DATE = p_last_update_date,
350 LAST_UPDATE_LOGIN = p_user_id,
351 MASS_QTY = p_mass_qty,
352 MASS_REF_UOM = p_mass_uom,
353 VOLUME_QTY = p_vol_qty,
354 VOLUME_REF_UOM = p_vol_uom
355 WHERE
356 RECIPE_ID = p_recipe_id AND
357 ROUTINGSTEP_ID = p_routingstep_id AND
358 LAST_UPDATE_DATE = p_last_update_date_origin;
359
360
361 EXCEPTION
362 WHEN NO_DATA_FOUND THEN
363 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
364 x_return_code := 'F';
365 x_error_msg := FND_MESSAGE.GET;
366
367 WHEN OTHERS THEN
368 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
369 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
370 x_return_code := 'F';
371 x_error_msg := FND_MESSAGE.GET;
372
373 END Update_Recipe_Routing_Step_Row;
374
375 /* Api start of comments
376 +============================================================================
377 | PROCEDURE NAME
378 | Create_Recipe_Routing_step_Row
379 |
380 | DESCRIPTION
381 | Create a row in GMD_RECIPE_ROUTING_STEPS
382 |
383 | INPUT PARAMETERS
384 | p_recipe_id NUMBER
385 | p_routingstep_id NUMBER
386 | p_text_code NUMBER
387 | p_last_update_date DATE
388 | p_user_id NUMBER
389 | p_step_qty NUMBER
390 | p_mass_qty NUMBER
391 | p_vol_qty NUMBER
392 | p_mass_uom VARCHAR2
393 | p_vol_uom VARCHAR2
394 |
395 | OUTPUT PARAMETERS
396 | x_return_code VARCHAR2(1)
397 | x_error_msg VARCHAR2(100)
398 |
399 | HISTORY
400 | 10-JUL-2001 Eddie Oumerretane Created.
401 |
402 +=============================================================================
403 Api end of comments
404 */
405
406 PROCEDURE Create_Recipe_Routing_Step_Row ( p_recipe_id IN NUMBER,
407 p_routingstep_id IN NUMBER,
408 p_text_code IN NUMBER,
409 p_last_update_date IN DATE,
410 p_user_id IN NUMBER,
411 p_step_qty IN NUMBER,
412 p_mass_qty IN NUMBER,
413 p_vol_qty IN NUMBER,
414 p_mass_uom IN VARCHAR2,
415 p_vol_uom IN VARCHAR2,
416 x_return_code OUT NOCOPY VARCHAR2,
417 x_error_msg OUT NOCOPY VARCHAR2) IS
418
419 BEGIN
420
421 x_return_code := 'S';
422 x_error_msg := '';
423
424 INSERT INTO gmd_recipe_routing_steps
425 (RECIPE_ID
426 ,ROUTINGSTEP_ID
427 ,STEP_QTY
428 ,CREATED_BY
429 ,CREATION_DATE
430 ,LAST_UPDATED_BY
431 ,LAST_UPDATE_DATE
432 ,LAST_UPDATE_LOGIN
433 ,TEXT_CODE
434 ,MASS_QTY
435 ,MASS_REF_UOM
436 ,VOLUME_QTY
437 ,VOLUME_REF_UOM)
438 VALUES
439 (p_recipe_id,
440 p_routingstep_id,
441 p_step_qty,
442 p_user_id,
443 p_last_update_date,
444 p_user_id,
445 p_last_update_date,
446 p_user_id,
447 p_text_code,
448 p_mass_qty,
449 p_mass_uom,
450 p_vol_qty,
451 p_vol_uom);
452
453 EXCEPTION
454 WHEN DUP_VAL_ON_INDEX THEN
455 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
456 x_return_code := 'F';
457 x_error_msg := FND_MESSAGE.GET;
458
459 WHEN OTHERS THEN
460 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
461 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
462 x_return_code := 'F';
463 x_error_msg := FND_MESSAGE.GET;
464
465 END Create_Recipe_Routing_Step_Row;
466
467 /* Api start of comments
468 +============================================================================
469 | PROCEDURE NAME
470 | Create_Step_Material_Link
471 |
472 | DESCRIPTION
473 | Create a row in GMD_RECIPE_STEP_MATERIALS
474 |
475 | INPUT PARAMETERS
476 | p_recipe_id NUMBER
477 | p_formulaline_id NUMBER
478 | p_routingstep_id NUMBER
479 | p_text_code NUMBER
480 | p_user_id NUMBER
481 | p_last_update_date DATE
482 |
483 | OUTPUT PARAMETERS
484 | x_return_code VARCHAR2(1)
485 | x_error_msg VARCHAR2(100)
486 |
487 | HISTORY
488 | 04-JUL-2001 Eddie Oumerretane Created.
489 |
490 +=============================================================================
491 Api end of comments
492 */
493
494 PROCEDURE Create_Step_Material_Link ( p_recipe_id IN NUMBER,
495 p_formulaline_id IN NUMBER,
496 p_routingstep_id IN NUMBER,
497 p_text_code IN NUMBER,
498 p_user_id IN NUMBER,
499 p_last_update_date IN DATE,
500 x_return_code OUT NOCOPY VARCHAR2,
501 x_error_msg OUT NOCOPY VARCHAR2) IS
502
503
504 BEGIN
505
506 x_return_code := 'S';
507 x_error_msg := '';
508
509
510 INSERT INTO gmd_recipe_step_materials (
511 recipe_id
512 ,formulaline_id
513 ,routingstep_id
514 ,text_code
515 ,creation_date
516 ,created_by
517 ,last_update_date
518 ,last_updated_by
519 ,last_update_login)
520 VALUES ( p_recipe_id
521 ,p_formulaline_id
522 ,p_routingstep_id
523 ,p_text_code
524 ,p_last_update_date
525 ,p_user_id
526 ,p_last_update_date
527 ,p_user_id
528 ,p_user_id);
529
530
531 EXCEPTION
532 WHEN DUP_VAL_ON_INDEX THEN
533 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
534 x_return_code := 'F';
535 x_error_msg := FND_MESSAGE.GET;
536
537 WHEN OTHERS THEN
538 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
539 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
540 x_return_code := 'F';
541 x_error_msg := FND_MESSAGE.GET;
542
543 END Create_Step_Material_Link;
544
545
546 /* Api start of comments
547 +============================================================================
548 | PROCEDURE NAME
549 | Delete_Step_Material_Link
550 |
551 | DESCRIPTION
552 | Delete a row in GMD_RECIPE_STEP_MATERIALS
553 |
554 | INPUT PARAMETERS
555 | p_recipe_id NUMBER
556 | p_formulaline_id NUMBER
557 | p_routingstep_id NUMBER
558 | p_last_update_date_origin DATE
559 | p_user_id NUMBER
560 |
561 | OUTPUT PARAMETERS
562 | x_return_code VARCHAR2(1)
563 | x_error_msg VARCHAR2(100)
564 |
565 | HISTORY
566 | 04-JUL-2001 Eddie Oumerretane Created.
567 |
568 +=============================================================================
569 Api end of comments
570 */
571
572 PROCEDURE Delete_Step_Material_Link ( p_recipe_id IN NUMBER,
573 p_formulaline_id IN NUMBER,
574 p_routingstep_id IN NUMBER,
575 p_last_update_date_origin IN DATE,
576 p_user_id IN NUMBER,
577 x_return_code OUT NOCOPY VARCHAR2,
578 x_error_msg OUT NOCOPY VARCHAR2) IS
579
580 /*
581 CURSOR Get_Step (c_recipe_id NUMBER, c_formulaline_id NUMBER, routingstep_id NUMBER) IS
582 SELECT
583 last_update_date
584 FROM
585 gmd_recipe_step_materials
586 WHERE
587 recipe_id = c_recipe_id AND
588 formulaline_id = c_formulaline_id AND
589 routingstep_id = routingstep_id;
590 */
591 ---l_last_update_date DATE;
592
593 BEGIN
594
595 x_return_code := 'S';
596 x_error_msg := '';
597
598
599 DELETE
600 gmd_recipe_step_materials
601 WHERE
602 recipe_id = p_recipe_id AND
603 formulaline_id = p_formulaline_id AND
604 routingstep_id = p_routingstep_id AND
605 last_update_date = p_last_update_date_origin;
606
607 --- OPEN Get_Step(p_recipe_id, p_formulaline_id, p_routingstep_id);
608 --- FETCH Get_Step INTO l_last_update_date;
609
610 --- IF Get_Step%FOUND THEN
611 --- CLOSE Get_Step;
612 --- END IF;
613
614 --- CLOSE Get_Step;
615
616
617 IF SQL%NOTFOUND THEN
618 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
619 x_return_code := 'F';
620 x_error_msg := FND_MESSAGE.GET;
621 END IF;
622
623 EXCEPTION
624 WHEN OTHERS THEN
625 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
626 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
627 x_return_code := 'F';
628 x_error_msg := FND_MESSAGE.GET;
629
630 END Delete_Step_Material_Link;
631
632
633 /* Api start of comments
634 +============================================================================
635 | PROCEDURE NAME
636 | Update_Step_Material_Link
637 |
638 | DESCRIPTION
639 | Update a row in GMD_RECIPE_STEP_MATERIALS
640 |
641 | INPUT PARAMETERS
642 | p_recipe_id NUMBER
643 | p_formulaline_id NUMBER
644 | p_routingstep_id NUMBER
645 | p_text_code NUMBER
646 | p_last_update_date DATE
647 | p_last_update_date_origin DATE
648 | p_user_id NUMBER
649 |
650 | OUTPUT PARAMETERS
651 | x_return_code VARCHAR2(1)
652 | x_error_msg VARCHAR2(100)
653 |
654 | HISTORY
655 | 04-JUL-2001 Eddie Oumerretane Created.
656 |
657 +=============================================================================
658 Api end of comments
659 */
660
661 PROCEDURE Update_Step_Material_Link ( p_recipe_id IN NUMBER,
662 p_formulaline_id IN NUMBER,
663 p_routingstep_id IN NUMBER,
664 p_text_code IN NUMBER,
665 p_last_update_date IN DATE,
666 p_last_update_date_origin IN DATE,
667 p_user_id IN NUMBER,
668 x_return_code OUT NOCOPY VARCHAR2,
669 x_error_msg OUT NOCOPY VARCHAR2) IS
670
671 BEGIN
672
673 x_return_code := 'S';
674 x_error_msg := '';
675
676
677 UPDATE
678 gmd_recipe_step_materials
679 SET
680 text_code = p_text_code,
681 last_update_date = p_last_update_date,
682 last_updated_by = p_user_id,
683 last_update_login = p_user_id
684 WHERE
685 recipe_id = p_recipe_id AND
686 formulaline_id = p_formulaline_id AND
687 routingstep_id = p_routingstep_id AND
688 last_update_date = p_last_update_date_origin;
689
690 EXCEPTION
691 WHEN NO_DATA_FOUND THEN
692 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
693 x_return_code := 'F';
694 x_error_msg := FND_MESSAGE.GET;
695
696 WHEN OTHERS THEN
697 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
698 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
699 x_return_code := 'F';
700 x_error_msg := FND_MESSAGE.GET;
701
702 END Update_Step_Material_Link;
703
704
705 /* Api start of comments
706 +============================================================================
707 | PROCEDURE NAME
708 | Calculate_Step_Quantities
709 |
710 | DESCRIPTION
711 | Calculate step quantities
712 |
713 | INPUT PARAMETERS
714 | p_recipe_id NUMBER
715 |
716 | OUTPUT PARAMETERS
717 | x_quantities VARCHAR2
718 | x_return_code VARCHAR2
719 | x_error_msg VARCHAR2
720 |
721 | HISTORY
722 | 09-JUL-2001 Eddie Oumerretane Created.
723 |
724 +=============================================================================
725 Api end of comments
726 */
727
728 PROCEDURE Calculate_Step_Quantities ( p_recipe_id IN NUMBER,
729 p_user_id IN NUMBER,
730 x_quantities OUT NOCOPY VARCHAR2,
731 x_return_code OUT NOCOPY VARCHAR2,
732 x_error_msg OUT NOCOPY VARCHAR2) IS
733
734 CURSOR Get_RoutingId IS
735 SELECT routing_id, planned_process_loss, owner_organization_id
736 FROM gmd_recipes_b
737 WHERE recipe_id = p_recipe_id;
738
739 l_routing_id NUMBER(10) := 0;
740 l_charge_tbl GMD_COMMON_VAL.charge_tbl;
741
742 l_step_tbl GMD_AUTO_STEP_CALC.step_rec_tbl;
743 l_msg_count NUMBER(10);
744 l_return_status VARCHAR2(10);
745 l_msg_stack VARCHAR2(1000);
746 l_message VARCHAR2(2000);
747 l_temp NUMBER;
748
749 l_routingstep_id NUMBER := -1;
750 l_found_charge BOOLEAN;
751 l_planned_process_loss NUMBER;
752 l_organization_id NUMBER;
753
754 STEP_QTY_ERROR EXCEPTION;
755
756
757 FUNCTION Get_Routing_StepId (p_step_no NUMBER,
758 p_routing_id NUMBER) RETURN NUMBER IS
759
760 CURSOR Get_RoutingStepId IS
761 SELECT
762 ROUTINGSTEP_ID,
763 ROUTINGSTEP_NO
764 FROM
765 FM_ROUT_DTL
766 WHERE
767 routing_id = p_routing_id;
768
769 BEGIN
770
771 IF NOT G_ROUTINGSTEP_ID.EXISTS(1) THEN
772
773 OPEN Get_RoutingStepId;
774
775 FETCH
776 Get_RoutingStepId
777 BULK COLLECT INTO
778 G_ROUTINGSTEP_ID,
779 G_ROUTINGSTEP_NO;
780
781 CLOSE Get_RoutingStepId;
782
783 END IF;
784
785 FOR i IN 1.. G_ROUTINGSTEP_ID.COUNT LOOP
786
787 IF G_ROUTINGSTEP_NO(i) = p_step_no THEN
788 l_routingstep_id := G_ROUTINGSTEP_ID(i);
789 EXIT;
790 END IF;
791
792 END LOOP;
793
794 RETURN l_routingstep_id;
795
796 END Get_Routing_StepId;
797
798
799 PROCEDURE Update_Step_Qty (p_routingstep_id NUMBER,
800 p_ind NUMBER) IS
801
802 BEGIN
803
804 UPDATE
805 GMD_RECIPE_ROUTING_STEPS
806 SET
807 STEP_QTY = l_step_tbl(p_ind).step_qty,
808 MASS_QTY = l_step_tbl(p_ind).step_mass_qty,
809 VOLUME_QTY = l_step_tbl(p_ind).step_vol_qty
810 WHERE
811 RECIPE_ID = p_recipe_id AND
812 ROUTINGSTEP_ID = p_routingstep_id;
813
814
815 IF SQL%NOTFOUND THEN
816
817 INSERT INTO gmd_recipe_routing_steps
818 (RECIPE_ID
819 ,ROUTINGSTEP_ID
820 ,STEP_QTY
821 ,CREATED_BY
822 ,CREATION_DATE
823 ,LAST_UPDATED_BY
824 ,LAST_UPDATE_DATE
825 ,LAST_UPDATE_LOGIN
826 ,TEXT_CODE
827 ,MASS_QTY
828 ,MASS_REF_UOM
829 ,VOLUME_QTY
830 ,VOLUME_REF_UOM)
831 VALUES
832 (p_recipe_id,
833 l_routingstep_id,
834 l_step_tbl(p_ind).step_qty,
835 p_user_id,
836 SYSDATE,
837 p_user_id,
838 SYSDATE,
839 p_user_id,
840 0,
841 l_step_tbl(p_ind).step_mass_qty,
842 l_step_tbl(p_ind).step_mass_uom,
843 l_step_tbl(p_ind).step_vol_qty,
844 l_step_tbl(p_ind).step_vol_uom);
845
846 END IF;
847
848 END Update_Step_Qty;
849
850 BEGIN
851
852 x_return_code := 'S';
853 x_error_msg := '';
854
855 /* Bug 3609776 - Thomas Daniel */
856 /* Added process loss to the cursor */
857 OPEN Get_RoutingId;
858 FETCH Get_RoutingId INTO l_routing_id, l_planned_process_loss, l_organization_id;
859 CLOSE Get_RoutingId;
860
861 /* Bug 3609776 - Thomas Daniel */
862 /* Added parameters ignore mass volume conv and process loss to the auto step calc call*/
863 GMD_AUTO_STEP_CALC.calc_step_qty (
864 P_parent_id => p_recipe_id,
865 P_step_tbl => l_step_tbl,
866 P_msg_count => l_msg_count,
867 P_msg_stack => l_msg_stack,
868 P_return_status => l_return_status,
869 p_ignore_mass_conv => TRUE,
870 p_ignore_vol_conv => TRUE,
871 p_process_loss => l_planned_process_loss,
872 p_organization_id => l_organization_id);
873
874 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
875 RAISE STEP_QTY_ERROR;
876 END IF;
877
878
879 --- Update Step Quantities. This is necessary because the
880 --- charge calculation engine, gets the step quantities from the database
881
882 FOR i IN 1.. l_step_tbl.COUNT LOOP
883
884 l_routingstep_id := Get_Routing_StepId(l_step_tbl(i).step_no, l_routing_id);
885 Update_Step_Qty (l_routingstep_id, i);
886
887 END LOOP;
888
889 GMD_COMMON_VAL.Calculate_Charges(batch_id => 0,
890 recipe_id => p_recipe_id,
891 routing_id => l_routing_id,
892 VR_qty => 0,
893 Tolerance => 0,
894 orgn_id => NULL,
895 x_charge_tbl => l_charge_tbl,
896 x_return_status => l_return_status);
897
898 IF l_step_tbl.COUNT > 0 THEN
899
900 l_step_tbl(1).step_vol_qty := NVL(l_step_tbl(1).step_vol_qty, -1);
901 l_step_tbl(1).step_vol_uom := NVL(l_step_tbl(1).step_vol_uom, ' ');
902 l_step_tbl(1).step_mass_qty := NVL(l_step_tbl(1).step_mass_qty, -1);
903 l_step_tbl(1).step_mass_uom := NVL(l_step_tbl(1).step_mass_uom, ' ');
904
905 x_quantities := l_step_tbl.COUNT || '//' ||
906 l_step_tbl(1).step_no || '//' ||
907 l_step_tbl(1).step_qty || '//' ||
908 l_step_tbl(1).step_mass_qty || '//' ||
909 l_step_tbl(1).step_mass_uom || '//' ||
910 l_step_tbl(1).step_vol_qty || '//' ||
911 l_step_tbl(1).step_vol_uom;
912
913 l_found_charge := FALSE;
914
915 FOR i IN 1..l_charge_tbl.COUNT LOOP
916
917 IF Get_Routing_StepId(l_step_tbl(1).step_no, l_routing_id) = l_charge_tbl(i).routingstep_id THEN
918
919 IF l_charge_tbl(i).charge IS NULL THEN
920 l_charge_tbl(i).charge := 1;
921 END IF;
922
923 IF l_charge_tbl(i).max_capacity IS NULL THEN
924 l_charge_tbl(i).max_capacity := -1;
925 END IF;
926
927 IF l_charge_tbl(i).capacity_uom IS NULL THEN
928 l_charge_tbl(i).capacity_uom := ' ';
929 END IF;
930
931 x_quantities := x_quantities || '//' ||
932 l_charge_tbl(i).charge || '//' ||
933 l_charge_tbl(i).max_capacity || '//' ||
934 l_charge_tbl(i).capacity_uom;
935
936 l_found_charge := TRUE;
937
938 EXIT;
939
940 END IF;
941
942 END LOOP;
943
944 -- Should never occur .... but just in case !
945 IF NOT l_found_charge THEN
946
947 x_quantities := x_quantities || '//' ||
948 1 || '//' ||
949 -1 || '//' ||
950 ' ';
951
952 END IF;
953
954 END IF;
955
956 FOR i IN 2.. l_step_tbl.COUNT LOOP
957
958 l_step_tbl(i).step_vol_qty := NVL(l_step_tbl(i).step_vol_qty, -1);
959 l_step_tbl(i).step_vol_uom := NVL(l_step_tbl(i).step_vol_uom, ' ');
960 l_step_tbl(i).step_mass_qty := NVL(l_step_tbl(i).step_mass_qty, -1);
961 l_step_tbl(i).step_mass_uom := NVL(l_step_tbl(i).step_mass_uom, ' ');
962
963 x_quantities := x_quantities || '//' ||
964 l_step_tbl(i).step_no || '//' ||
965 l_step_tbl(i).step_qty || '//' ||
966 l_step_tbl(i).step_mass_qty || '//' ||
967 l_step_tbl(i).step_mass_uom || '//' ||
968 l_step_tbl(i).step_vol_qty || '//' ||
969 l_step_tbl(i).step_vol_uom;
970
971 l_found_charge := FALSE;
972
973 FOR j IN 1..l_charge_tbl.COUNT LOOP
974
975 IF Get_Routing_StepId(l_step_tbl(i).step_no, l_routing_id) = l_charge_tbl(j).routingstep_id THEN
976
977 IF l_charge_tbl(j).charge IS NULL THEN
978 l_charge_tbl(j).charge := 1;
979 END IF;
980
981 IF l_charge_tbl(j).max_capacity IS NULL THEN
982 l_charge_tbl(j).max_capacity := -1;
983 END IF;
984
985 IF l_charge_tbl(j).capacity_uom IS NULL THEN
986 l_charge_tbl(j).capacity_uom := ' ';
987 END IF;
988
989 x_quantities := x_quantities || '//' ||
990 l_charge_tbl(j).charge || '//' ||
991 l_charge_tbl(j).max_capacity || '//' ||
992 l_charge_tbl(j).capacity_uom;
993
994 l_found_charge := TRUE;
995
996 EXIT;
997
998 END IF;
999
1000 END LOOP;
1001
1002 -- Should never occur .... but just in case !
1003 IF NOT l_found_charge THEN
1004
1005 x_quantities := x_quantities || '//' ||
1006 1 || '//' ||
1007 -1 || '//' ||
1008 ' ';
1009
1010 END IF;
1011
1012 END LOOP;
1013
1014 l_charge_tbl.DELETE;
1015
1016
1017 EXCEPTION
1018 WHEN STEP_QTY_ERROR THEN
1019 x_return_code := 'F';
1020 FND_MSG_PUB.GET(p_msg_index => 1,
1021 p_data => l_message,
1022 p_encoded => 'F',
1023 p_msg_index_out => l_temp);
1024 x_error_msg := l_message;
1025
1026 WHEN OTHERS THEN
1027 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1028 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1029 x_return_code := 'F';
1030 x_error_msg := FND_MESSAGE.GET;
1031
1032 END Calculate_Step_Quantities;
1033
1034 /* Api start of comments
1035 +============================================================================
1036 | PROCEDURE NAME
1037 | Calculate_Step_Charges
1038 |
1039 | DESCRIPTION
1040 | Calculate Charges for the given operation step
1041 |
1042 | INPUT PARAMETERS
1043 | p_recipe_id NUMBER
1044 | p_routiingstep_id NUMBER
1045 | p_step_qty NUMBER
1046 | p_step_um VARCHAR2
1047 |
1048 | OUTPUT PARAMETERS
1049 | x_charges_info VARCHAR2
1050 | x_return_code VARCHAR2
1051 | x_error_msg VARCHAR2
1052 |
1053 | HISTORY
1054 | 29-AUG-2001 Eddie Oumerretane Created.
1055 |
1056 +=============================================================================
1057 Api end of comments
1058 */
1059 PROCEDURE Calculate_Step_Charges ( p_recipe_id IN NUMBER,
1060 p_routingstep_id IN NUMBER,
1061 p_step_qty IN NUMBER,
1062 p_step_um IN VARCHAR2,
1063 x_charges_info OUT NOCOPY VARCHAR2,
1064 x_return_code OUT NOCOPY VARCHAR2,
1065 x_error_msg OUT NOCOPY VARCHAR2) IS
1066
1067 l_charge_tbl GMD_COMMON_VAL.charge_tbl;
1068 l_step_tbl GMD_AUTO_STEP_CALC.step_rec_tbl;
1069 l_return_code VARCHAR2(1);
1070 l_message VARCHAR2(2000);
1071 l_temp NUMBER;
1072
1073 BEGIN
1074
1075 x_return_code := 'S';
1076 x_error_msg := '';
1077
1078 l_step_tbl(1).step_id := p_routingstep_id;
1079 l_step_tbl(1).step_qty := p_step_qty;
1080 l_step_tbl(1).step_qty_uom := p_step_um;
1081
1082 GMD_COMMON_VAL.Calculate_Step_Charges (p_recipe_id => p_recipe_id
1083 ,p_tolerance => 0
1084 ,p_orgn_id => NULL
1085 ,p_step_tbl => l_step_tbl
1086 ,x_charge_tbl => l_charge_tbl
1087 ,x_return_status => l_return_code);
1088
1089 IF l_charge_tbl.EXISTS(1) THEN
1090
1091 IF l_charge_tbl(1).charge IS NULL THEN
1092 l_charge_tbl(1).charge := 1;
1093 END IF;
1094
1095 IF l_charge_tbl(1).max_capacity IS NULL THEN
1096 l_charge_tbl(1).max_capacity := -1;
1097 END IF;
1098
1099 IF l_charge_tbl(1).capacity_uom IS NULL THEN
1100 l_charge_tbl(1).capacity_uom := ' ';
1101 END IF;
1102
1103 x_charges_info := l_charge_tbl(1).charge || '//' ||
1104 l_charge_tbl(1).max_capacity || '//' ||
1105 l_charge_tbl(1).capacity_uom;
1106 ELSE
1107 x_charges_info := '1' || '//' || '-1' || '//' || ' ';
1108 END IF;
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1113 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1114 x_return_code := 'F';
1115 x_error_msg := FND_MESSAGE.GET;
1116
1117 END Calculate_Step_Charges;
1118
1119
1120 /* Api start of comments
1121 +============================================================================
1122 | PROCEDURE NAME
1123 | Get_Recipe_Mode
1124 |
1125 | DESCRIPTION
1126 | Determine whether this recipe is in update or query mode
1127 |
1128 | INPUT PARAMETERS
1129 | p_recipe_id NUMBER
1130 |
1131 | OUTPUT PARAMETERS
1132 | x_recipe_mode VARCHAR2
1133 | x_return_code VARCHAR2
1134 | x_error_msg VARCHAR2
1135 |
1136 | HISTORY
1137 | 15-OCT-2001 Eddie Oumerretane Created.
1138 |
1139 +=============================================================================
1140 Api end of comments
1141 */
1142
1143 PROCEDURE Get_Recipe_Mode ( p_recipe_id IN NUMBER,
1144 x_recipe_mode OUT NOCOPY VARCHAR2,
1145 x_return_code OUT NOCOPY VARCHAR2,
1146 x_error_msg OUT NOCOPY VARCHAR2) IS
1147
1148 l_return_code VARCHAR2(1);
1149 l_status VARCHAR2(30);
1150
1151 BEGIN
1152
1153 x_return_code := 'S';
1154 x_error_msg := '';
1155 x_recipe_mode := 'Q';
1156
1157 IF GMD_COMMON_VAL.Update_Allowed(entity => 'RECIPE',
1158 entity_id => p_recipe_id) AND
1159 GMD_API_GRP.check_Orgn_Access(entity => 'RECIPE',
1160 entity_id => p_recipe_id) THEN
1161 x_recipe_mode := 'U';
1162
1163 END IF;
1164
1165 END Get_Recipe_Mode;
1166
1167 /* Api start of comments
1168 +============================================================================
1169 | PROCEDURE NAME
1170 | Calculate_Charges
1171 |
1172 | DESCRIPTION
1173 | Calculate Charges
1174 |
1175 | INPUT PARAMETERS
1176 | p_recipe_id NUMBER
1177 |
1178 | OUTPUT PARAMETERS
1179 | x_charges_info VARCHAR2
1180 | x_return_code VARCHAR2
1181 | x_error_msg VARCHAR2
1182 |
1183 | HISTORY
1184 | 28-AUG-2001 Eddie Oumerretane Created.
1185 |
1186 +=============================================================================
1187 Api end of comments
1188 */
1189 PROCEDURE Calculate_Charges ( p_recipe_id IN NUMBER,
1190 x_charges_info OUT NOCOPY VARCHAR2,
1191 x_return_code OUT NOCOPY VARCHAR2,
1192 x_error_msg OUT NOCOPY VARCHAR2) IS
1193
1194 CURSOR Get_RoutingId IS
1195 SELECT
1196 routing_id
1197 FROM
1198 gmd_recipes
1199 WHERE
1200 recipe_id = p_recipe_id;
1201
1202 l_charge_tbl GMD_COMMON_VAL.charge_tbl;
1203 l_return_code VARCHAR2(1);
1204 l_routing_id NUMBER(10) := 0;
1205
1206 BEGIN
1207
1208 x_return_code := 'S';
1209
1210 x_error_msg := '';
1211
1212 OPEN Get_RoutingId;
1213 FETCH Get_RoutingId INTO l_routing_id;
1214 CLOSE Get_RoutingId;
1215
1216 GMD_COMMON_VAL.Calculate_Charges(batch_id => 0,
1217 recipe_id => p_recipe_id,
1218 routing_id => l_routing_id,
1219 VR_qty => 0,
1220 Tolerance => 0,
1221 orgn_id => NULL,
1222 x_charge_tbl => l_charge_tbl,
1223 x_return_status => x_return_code);
1224
1225 IF l_charge_tbl.EXISTS(1) THEN
1226
1227 IF l_charge_tbl.COUNT > 0 THEN
1228
1229 IF l_charge_tbl(1).charge IS NULL THEN
1230 l_charge_tbl(1).charge := 1;
1231 END IF;
1232
1233 IF l_charge_tbl(1).max_capacity IS NULL THEN
1234 l_charge_tbl(1).max_capacity := -1;
1235 END IF;
1236
1237 IF l_charge_tbl(1).capacity_uom IS NULL THEN
1238 l_charge_tbl(1).capacity_uom := ' ';
1239 END IF;
1240
1241 x_charges_info := l_charge_tbl(1).routingstep_id || '//' ||
1242 l_charge_tbl(1).charge || '//' ||
1243 l_charge_tbl(1).max_capacity || '//' ||
1244 l_charge_tbl(1).capacity_uom;
1245
1246
1247 FOR i IN 2.. l_charge_tbl.COUNT LOOP
1248
1249 IF l_charge_tbl(i).charge IS NULL THEN
1250 l_charge_tbl(i).charge := 1;
1251 END IF;
1252
1253 IF l_charge_tbl(i).max_capacity IS NULL THEN
1254 l_charge_tbl(i).max_capacity := -1;
1255 END IF;
1256
1257 IF l_charge_tbl(i).capacity_uom IS NULL THEN
1258 l_charge_tbl(i).capacity_uom := ' ';
1259 END IF;
1260
1261 x_charges_info := x_charges_info || '//' ||
1262 l_charge_tbl(i).routingstep_id || '//' ||
1263 l_charge_tbl(i).charge || '//' ||
1264 l_charge_tbl(i).max_capacity || '//' ||
1265 l_charge_tbl(i).capacity_uom;
1266 END LOOP;
1267
1268 END IF;
1269
1270 END IF;
1271
1272 END Calculate_Charges;
1273
1274 /* Api start of comments
1275 +============================================================================
1276 | PROCEDURE NAME
1277 | Update_Step_Quantities
1278 |
1279 | DESCRIPTION
1280 | Update step quantities table
1281 |
1282 | INPUT PARAMETERS
1283 | p_recipe_id NUMBER
1284 | p_ruting_id NUMBER
1285 | p_user_id NUMBER
1286 | p_text_code NUMBER
1287 | p_last_update_date DATE
1288 |
1289 | OUTPUT PARAMETERS
1290 | x_return_code VARCHAR2
1291 | x_error_msg VARCHAR2
1292 |
1293 | HISTORY
1294 | 10-JUL-2001 Eddie Oumerretane Created.
1295 |
1296 +=============================================================================
1297 Api end of comments
1298 */
1299 /*
1300 PROCEDURE Update_Step_Quantities ( p_recipe_id IN NUMBER,
1301 p_routing_id IN NUMBER,
1302 p_user_id IN NUMBER,
1303 p_last_update_date IN DATE,
1304 x_return_code OUT NOCOPY VARCHAR2,
1305 x_error_msg OUT NOCOPY VARCHAR2) IS
1306
1307 CURSOR Get_Step_Id (c_routing_id NUMBER, c_routingstep_no NUMBER) IS
1308 SELECT
1309 routingstep_id
1310 FROM
1311 fm_rout_dtl
1312 WHERE
1313 routing_id = c_routing_id AND
1314 routingstep_no = c_routingstep_no;
1315
1316 l_step_tbl GMD_AUTO_STEP_CALC.step_rec_tbl;
1317 l_routingstep_id NUMBER(10);
1318 l_msg_count NUMBER(10);
1319 l_return_status VARCHAR2(10);
1320 l_msg_stack VARCHAR2(1000);
1321 l_message VARCHAR2(100);
1322 l_temp NUMBER;
1323 l_step_no NUMBER(10);
1324 l_step_qty NUMBER;
1325 l_mass_qty NUMBER;
1326 l_vol_qty NUMBER;
1327 l_mass_uom VARCHAR2(4);
1328 l_vol_uom VARCHAR2(4);
1329
1330 STEP_QTY_ERROR EXCEPTION;
1331 ROUTING_STEP_ID_NOT_FOUND EXCEPTION;
1332
1333 BEGIN
1334
1335 x_return_code := 'S';
1336 x_error_msg := '';
1337
1338 GMD_AUTO_STEP_CALC.Check_Step_Qty_Calculatable (
1339 P_parent_id => p_recipe_id,
1340 P_msg_count => l_msg_count,
1341 P_msg_stack => l_msg_stack,
1342 P_return_status => l_return_status);
1343
1344 IF (l_return_status <> 'S') THEN
1345 RAISE STEP_QTY_ERROR;
1346 END IF;
1347
1348 GMD_AUTO_STEP_CALC.calc_step_qty (
1349 P_parent_id => p_recipe_id,
1350 P_step_tbl => l_step_tbl,
1351 P_msg_count => l_msg_count,
1352 P_msg_stack => l_msg_stack,
1353 P_return_status => l_return_status);
1354
1355 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1356 RAISE STEP_QTY_ERROR;
1357 END IF;
1358
1359 FOR i IN 1.. l_step_tbl.COUNT LOOP
1360
1361 l_step_no := l_step_tbl(i).step_no;
1362 l_step_qty := l_step_tbl(i).step_qty;
1363 l_mass_qty := l_step_tbl(i).step_mass_qty;
1364 l_mass_uom := l_step_tbl(i).step_mass_uom;
1365 l_vol_qty := l_step_tbl(i).step_vol_qty;
1366 l_vol_uom := l_step_tbl(i).step_vol_uom;
1367
1368 OPEN Get_Step_Id(p_routing_id, l_step_no);
1369 FETCH Get_Step_Id INTO l_routingstep_id;
1370
1371 IF Get_Step_Id%NOTFOUND THEN
1372 CLOSE Get_Step_Id;
1373 RAISE ROUTING_STEP_ID_NOT_FOUND;
1374 END IF;
1375
1376 CLOSE Get_Step_Id;
1377
1378 UPDATE
1379 gmd_recipe_routing_steps
1380 SET
1381 STEP_QTY = l_step_qty,
1382 LAST_UPDATED_BY = p_user_id,
1383 LAST_UPDATE_DATE = p_last_update_date,
1384 LAST_UPDATE_LOGIN = p_user_id,
1385 MASS_QTY = l_mass_qty,
1386 MASS_REF_UOM = l_mass_uom,
1387 VOLUME_QTY = l_vol_qty,
1388 VOLUME_REF_UOM = l_vol_uom
1389 WHERE
1390 RECIPE_ID = p_recipe_id AND
1391 ROUTINGSTEP_ID = l_routingstep_id AND
1392 LAST_UPDATE_DATE = p_last_update_date;
1393
1394 END LOOP;
1395
1396
1397 EXCEPTION
1398
1399 WHEN NO_DATA_FOUND THEN
1400
1401 INSERT INTO gmd_recipe_routing_steps
1402 (RECIPE_ID
1403 ,ROUTINGSTEP_ID
1404 ,STEP_QTY
1405 ,CREATED_BY
1406 ,CREATION_DATE
1407 ,LAST_UPDATED_BY
1408 ,LAST_UPDATE_DATE
1409 ,LAST_UPDATE_LOGIN
1410 ,TEXT_CODE
1411 ,MASS_QTY
1412 ,MASS_REF_UOM
1413 ,VOLUME_QTY
1414 ,VOLUME_REF_UOM)
1415 VALUES
1416 (p_recipe_id,
1417 l_routingstep_id,
1418 l_step_qty,
1419 p_user_id,
1420 p_last_update_date,
1421 p_user_id,
1422 p_last_update_date,
1423 p_user_id,
1424 0,
1425 l_mass_qty,
1426 l_mass_uom,
1427 l_vol_qty,
1428 l_vol_uom);
1429
1430
1431 WHEN STEP_QTY_ERROR THEN
1432 x_return_code := 'F';
1433 FND_MSG_PUB.GET(p_msg_index => 1,
1434 p_data => l_message,
1435 p_encoded => 'F',
1436 p_msg_index_out => l_temp);
1437 FND_MESSAGE.SET_NAME('GMD', l_message);
1438 x_error_msg := FND_MESSAGE.GET;
1439
1440 WHEN ROUTING_STEP_ID_NOT_FOUND THEN
1441 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1442 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1443 x_return_code := 'F';
1444 x_error_msg := FND_MESSAGE.GET;
1445
1446 WHEN DUP_VAL_ON_INDEX THEN
1447 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
1448 x_return_code := 'F';
1449 x_error_msg := FND_MESSAGE.GET;
1450
1451 WHEN OTHERS THEN
1452 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1453 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1454 x_return_code := 'F';
1455 x_error_msg := FND_MESSAGE.GET;
1456
1457 END Update_Step_Quantities;
1458 */
1459
1460 /* Api start of comments
1461 +============================================================================
1462 | PROCEDURE NAME
1463 | Delete_Recipe_Step_Quantities
1464 |
1465 | DESCRIPTION
1466 | Delete all rows in GMD_RECIPE_ROUTING_STEPS
1467 |
1468 | INPUT PARAMETERS
1469 | p_recipe_id NUMBER
1470 |
1471 | OUTPUT PARAMETERS
1472 | x_return_code VARCHAR2(1)
1473 | x_error_msg VARCHAR2(100)
1474 |
1475 | HISTORY
1476 | 31-OCT-2001 Eddie Oumerretane Created.
1477 |
1478 +=============================================================================
1479 Api end of comments
1480 */
1481
1482 PROCEDURE Delete_Recipe_Step_Quantities ( p_recipe_id IN NUMBER,
1483 x_return_code OUT NOCOPY VARCHAR2,
1484 x_error_msg OUT NOCOPY VARCHAR2) IS
1485
1486 BEGIN
1487
1488 x_return_code := 'S';
1489 x_error_msg := '';
1490
1491
1492 DELETE FROM
1493 GMD_RECIPE_ROUTING_STEPS
1494 WHERE
1495 recipe_id = p_recipe_id;
1496
1497
1498 END Delete_Recipe_Step_Quantities;
1499
1500 /* Api start of comments
1501 +============================================================================
1502 | PROCEDURE NAME
1503 | Get_Routing_Step_Quantities
1504 |
1505 | DESCRIPTION
1506 | Get step quantities from the routing of the given recipe
1507 |
1508 | INPUT PARAMETERS
1509 | p_recipe_id NUMBER
1510 |
1511 | OUTPUT PARAMETERS
1512 | x_quantities VARCHAR2
1513 | x_return_code VARCHAR2
1514 | x_error_msg VARCHAR2
1515 |
1516 | HISTORY
1517 | 30-OCT-2001 Eddie Oumerretane Created.
1518 |
1519 +=============================================================================
1520 Api end of comments
1521 */
1522 PROCEDURE Get_Routing_Step_Quantities ( p_recipe_id IN NUMBER,
1523 x_quantities OUT NOCOPY VARCHAR2,
1524 x_return_code OUT NOCOPY VARCHAR2,
1525 x_error_msg OUT NOCOPY VARCHAR2) IS
1526
1527 CURSOR Get_RoutingId IS
1528 SELECT
1529 routing_id
1530 FROM
1531 gmd_recipes
1532 WHERE
1533 recipe_id = p_recipe_id;
1534
1535 l_routing_id NUMBER(10) := 0;
1536 l_charge_tbl GMD_COMMON_VAL.charge_tbl;
1537
1538 l_msg_count NUMBER(10);
1539 l_return_status VARCHAR2(10);
1540 l_msg_stack VARCHAR2(1000);
1541 l_message VARCHAR2(2000);
1542 l_temp NUMBER;
1543 l_table gmd_recipe_fetch_pub.recipe_step_tbl;
1544 l_status VARCHAR2(30);
1545 l_msg_cnt NUMBER;
1546 l_msg_dat VARCHAR2(100);
1547 l_ret_code NUMBER;
1548
1549 l_found_charge BOOLEAN;
1550
1551 BEGIN
1552
1553 x_return_code := 'S';
1554 x_error_msg := '';
1555
1556 OPEN Get_RoutingId;
1557 FETCH Get_RoutingId INTO l_routing_id;
1558 CLOSE Get_RoutingId;
1559
1560
1561 GMD_RECIPE_FETCH_PUB.Get_Recipe_Step_Details
1562 (p_api_version => 1.0,
1563 p_init_msg_list => 'F',
1564 p_recipe_id => p_recipe_id,
1565 x_return_status => l_status,
1566 x_msg_count => l_msg_cnt,
1567 x_msg_data => l_msg_dat,
1568 x_return_code => l_ret_code,
1569 x_recipe_step_out => l_table);
1570
1571
1572 GMD_COMMON_VAL.Calculate_Charges(batch_id => 0,
1573 recipe_id => p_recipe_id,
1574 routing_id => l_routing_id,
1575 VR_qty => 0,
1576 Tolerance => 0,
1577 orgn_id => NULL,
1578 x_charge_tbl => l_charge_tbl,
1579 x_return_status => l_return_status);
1580
1581 IF l_table.COUNT > 0 THEN
1582
1583 x_quantities := l_table.COUNT || '//' ||
1584 l_table(1).routingstep_no || '//' ||
1585 l_table(1).step_qty || '//' ||
1586 l_table(1).step_qty || '//' ||
1587 l_table(1).process_qty_uom || '//' ||
1588 l_table(1).step_qty || '//' ||
1589 l_table(1).process_qty_uom;
1590
1591 l_found_charge := FALSE;
1592
1593 FOR i IN 1..l_charge_tbl.COUNT LOOP
1594
1595 IF l_table(1).routingstep_id = l_charge_tbl(i).routingstep_id THEN
1596
1597 l_charge_tbl(i).charge := NVL(l_charge_tbl(i).charge, 1);
1598 l_charge_tbl(i).max_capacity := NVL( l_charge_tbl(i).max_capacity, -1);
1599 l_charge_tbl(i).capacity_uom := NVL( l_charge_tbl(i).capacity_uom, ' ');
1600
1601 x_quantities := x_quantities || '//' ||
1602 l_charge_tbl(i).charge || '//' ||
1603 l_charge_tbl(i).max_capacity || '//' ||
1604 l_charge_tbl(i).capacity_uom;
1605
1606 l_found_charge := TRUE;
1607
1608 EXIT;
1609
1610 END IF;
1611
1612 END LOOP;
1613
1614 -- Should never occur .... but just in case !
1615 IF NOT l_found_charge THEN
1616
1617 x_quantities := x_quantities || '//' ||
1618 1 || '//' ||
1619 -1 || '//' ||
1620 ' ';
1621
1622 END IF;
1623
1624 END IF;
1625
1626 FOR i IN 2.. l_table.COUNT LOOP
1627
1628 x_quantities := x_quantities || '//' ||
1629 l_table(i).routingstep_no || '//' ||
1630 l_table(i).step_qty || '//' ||
1631 l_table(i).step_qty || '//' ||
1632 l_table(i).process_qty_uom || '//' ||
1633 l_table(i).step_qty || '//' ||
1634 l_table(i).process_qty_uom;
1635
1636 l_found_charge := FALSE;
1637
1638 FOR j IN 1..l_charge_tbl.COUNT LOOP
1639
1640 IF l_table(i).routingstep_id = l_charge_tbl(j).routingstep_id THEN
1641
1642 l_charge_tbl(j).charge := NVL(l_charge_tbl(j).charge, 1);
1643 l_charge_tbl(j).max_capacity := NVL( l_charge_tbl(j).max_capacity, -1);
1644 l_charge_tbl(j).capacity_uom := NVL( l_charge_tbl(j).capacity_uom, ' ');
1645
1646 x_quantities := x_quantities || '//' ||
1647 l_charge_tbl(j).charge || '//' ||
1648 l_charge_tbl(j).max_capacity || '//' ||
1649 l_charge_tbl(j).capacity_uom;
1650
1651 l_found_charge := TRUE;
1652
1653 EXIT;
1654
1655 END IF;
1656
1657 END LOOP;
1658
1659 -- Should never occur .... but just in case !
1660 IF NOT l_found_charge THEN
1661
1662 x_quantities := x_quantities || '//' ||
1663 1 || '//' ||
1664 -1 || '//' ||
1665 ' ';
1666
1667 END IF;
1668
1669 END LOOP;
1670
1671
1672 EXCEPTION
1673 WHEN OTHERS THEN
1674 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1675 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1676 x_return_code := 'F';
1677 x_error_msg := FND_MESSAGE.GET;
1678
1679
1680 END Get_Routing_Step_Quantities;
1681
1682 /* Api start of comments
1683 +============================================================================
1684 | PROCEDURE NAME
1685 | Is_Recipe_Used_In_Batches
1686 |
1687 | DESCRIPTION
1688 | Determine whether the recipe is used in open batches.
1689 |
1690 | INPUT PARAMETERS
1691 | p_recipe_id NUMBER
1692 |
1693 | OUTPUT PARAMETERS
1694 | x_used_in_batches VARCHAR2(1)
1695 | x_return_code VARCHAR2(1)
1696 | x_error_msg VARCHAR2(100)
1697 |
1698 | HISTORY
1699 | 05-NOV-2001 Eddie Oumerretane Created.
1700 |
1701 +=============================================================================
1702 Api end of comments
1703 */
1704
1705 PROCEDURE Is_Recipe_Used_In_Batches ( p_recipe_id IN NUMBER,
1706 x_used_in_batches OUT NOCOPY VARCHAR2,
1707 x_return_code OUT NOCOPY VARCHAR2,
1708 x_error_msg OUT NOCOPY VARCHAR2) IS
1709
1710 BEGIN
1711
1712 x_return_code := 'S';
1713 x_error_msg := '';
1714
1715 x_used_in_batches := 'N';
1716
1717 --- Returns TRUE if this recipe is used in batches
1718 IF NOT GMD_STATUS_CODE.Check_Parent_Status('RECIPE', p_recipe_id) THEN
1719 x_used_in_batches := 'Y';
1720 END IF;
1721
1722 END Is_Recipe_Used_In_Batches;
1723
1724 /* Api start of comments
1725 +============================================================================
1726 | PROCEDURE NAME
1727 | Update_Recipe_Header
1728 |
1729 | DESCRIPTION
1730 | Update a row in GMD_RECIPES
1731 |
1732 | INPUT PARAMETERS
1733 | p_recipe_id IN NUMBER
1734 | p_recipe_description IN VARCHAR2
1735 | p_recipe_no IN VARCHAR2
1736 | p_recipe_version IN NUMBER
1737 | p_recipe_status IN VARCHAR2
1738 | p_delete_mark IN NUMBER
1739 | p_formula_id IN NUMBER
1740 | p_routing_id IN NUMBER
1741 | p_planned_process_loss IN NUMBER
1742 | p_text_code IN NUMBER
1743 | p_owner_id IN NUMBER
1744 | p_calculate_step_qty IN NUMBER
1745 | p_user_id IN NUMBER
1746 | p_last_update_date IN DATE
1747 | p_last_update_date_origin IN DATE
1748 | p_update_number_version IN VARCHAR2
1749 | p_enhanced_pi_ind IN VARCHAR2
1750 |
1751 | OUTPUT PARAMETERS
1752 | x_return_code VARCHAR2(1)
1753 | x_error_msg VARCHAR2(100)
1754 |
1755 | HISTORY
1756 | 05-MAR-2002 Eddie Oumerretane Created.
1757 | 19-SEP-2002 Eddie Oumerretane Modified interface and implemented call
1758 | to the Update_Recipe_Header API.
1759 +=============================================================================
1760 Api end of comments
1761 */
1762 PROCEDURE Update_Recipe_Header ( p_recipe_id IN NUMBER,
1763 p_recipe_description IN VARCHAR2,
1764 p_recipe_no IN VARCHAR2,
1765 p_recipe_version IN NUMBER,
1766 p_owner_organization_id IN NUMBER,
1767 p_creation_organization_id IN NUMBER,
1768 p_recipe_status IN VARCHAR2,
1769 p_delete_mark IN NUMBER,
1770 p_formula_id IN NUMBER,
1771 p_routing_id IN NUMBER,
1772 p_planned_process_loss IN NUMBER,
1773 p_text_code IN NUMBER,
1774 p_owner_id IN NUMBER,
1775 p_calculate_step_qty IN NUMBER,
1776 p_user_id IN NUMBER,
1777 p_last_update_date IN DATE,
1778 p_last_update_date_origin IN DATE,
1779 p_update_number_version IN VARCHAR2,
1780 x_return_code OUT NOCOPY VARCHAR2,
1781 x_error_msg OUT NOCOPY VARCHAR2,
1782 p_enhanced_pi_ind IN VARCHAR2,
1783 p_contiguous_ind IN NUMBER,
1784 p_recipe_type IN NUMBER) IS
1785
1786 CURSOR Get_Recipe IS
1787 SELECT *
1788 FROM GMD_RECIPES
1789 WHERE
1790 recipe_id = p_recipe_id AND
1791 last_update_date = p_last_update_date_origin;
1792
1793 l_recipe_rec GMD_RECIPES%ROWTYPE;
1794 l_recipe_tbl GMD_RECIPE_HEADER.recipe_hdr;
1795 l_recipe_update_flex GMD_RECIPE_HEADER.update_flex;
1796
1797 l_return_status VARCHAR2(2);
1798 l_msg_count NUMBER(10);
1799 l_message_count NUMBER;
1800 l_msg_data VARCHAR2(2000);
1801 l_message VARCHAR2(1000);
1802 l_dummy NUMBER;
1803
1804 CURSOR Get_Old_Stp_Mat IS
1805 SELECT a.routingstep_id
1806 FROM gmd_recipe_step_materials a, fm_rout_dtl b
1807 WHERE a.recipe_id = p_recipe_id
1808 AND a.routingstep_id = b.routingstep_id
1809 AND b.routing_id = l_recipe_rec.routing_id;
1810
1811 CURSOR Get_Old_Stp IS
1812 SELECT a.routingstep_id
1813 FROM gmd_recipe_routing_steps a, fm_rout_dtl b
1814 WHERE a.recipe_id = p_recipe_id
1815 AND a.routingstep_id = b.routingstep_id
1816 AND b.routing_id = l_recipe_rec.routing_id;
1817
1818 RECORD_CHANGED_EXCEPTION EXCEPTION;
1819 UPDATE_RECIPE_EXCEPTION EXCEPTION;
1820
1821 BEGIN
1822
1823 x_return_code := 'S';
1824 x_error_msg := '';
1825
1826 OPEN Get_Recipe;
1827 FETCH Get_Recipe INTO l_recipe_rec;
1828
1829 IF Get_Recipe%NOTFOUND THEN
1830 CLOSE Get_Recipe;
1831 RAISE RECORD_CHANGED_EXCEPTION;
1832 END IF;
1833
1834 CLOSE Get_Recipe;
1835
1836 IF p_routing_id > 0 THEN
1837 l_recipe_tbl.ROUTING_ID := p_routing_id;
1838 ELSE
1839 l_recipe_tbl.ROUTING_ID := NULL;
1840 null;
1841 END IF;
1842
1843 IF p_planned_process_loss >= 0 THEN
1844 l_recipe_tbl.PLANNED_PROCESS_LOSS := p_planned_process_loss;
1845 ELSE
1846 l_recipe_tbl.PLANNED_PROCESS_LOSS := NULL;
1847 END IF;
1848
1849 IF p_text_code > 0 THEN
1850 l_recipe_tbl.TEXT_CODE := p_text_code;
1851 ELSE
1852 l_recipe_tbl.TEXT_CODE := NULL;
1853 END IF;
1854
1855 l_recipe_tbl.PROJECT_ID := l_recipe_rec.project_id;
1856 l_recipe_tbl.OWNER_LAB_TYPE := l_recipe_rec.owner_lab_type;
1857
1858 l_recipe_tbl.RECIPE_NO := l_recipe_rec.recipe_no;
1859 l_recipe_tbl.RECIPE_VERSION := l_recipe_rec.recipe_version;
1860 l_recipe_tbl.RECIPE_ID := p_recipe_id;
1861 l_recipe_tbl.RECIPE_DESCRIPTION := p_recipe_description;
1862 l_recipe_tbl.USER_ID := p_user_id;
1863 l_recipe_tbl.OWNER_ORGANIZATION_ID := p_owner_organization_id;
1864 l_recipe_tbl.CREATION_ORGANIZATION_ID := p_creation_organization_id;
1865 l_recipe_tbl.FORMULA_ID := p_formula_id;
1866 l_recipe_tbl.RECIPE_STATUS := p_recipe_status;
1867 l_recipe_tbl.DELETE_MARK := p_delete_mark;
1868 l_recipe_tbl.LAST_UPDATED_BY := p_user_id;
1869 l_recipe_tbl.LAST_UPDATE_DATE := p_last_update_date;
1870 l_recipe_tbl.LAST_UPDATE_LOGIN := p_user_id;
1871 l_recipe_tbl.OWNER_ID := p_owner_id;
1872 l_recipe_tbl.CALCULATE_STEP_QUANTITY := p_calculate_step_qty;
1873
1874 -- Added by Shyam for GMD-GMO integration
1875 l_recipe_tbl.enhanced_pi_ind := p_enhanced_pi_ind;
1876
1877 -- Include contiguous ind
1878 l_recipe_tbl.contiguous_ind := p_contiguous_ind;
1879
1880 l_recipe_tbl.recipe_type := p_recipe_type;
1881
1882 l_recipe_update_flex.ATTRIBUTE_CATEGORY := l_recipe_rec.attribute_category;
1883 l_recipe_update_flex.ATTRIBUTE1 := l_recipe_rec.attribute1;
1884 l_recipe_update_flex.ATTRIBUTE2 := l_recipe_rec.attribute2;
1885 l_recipe_update_flex.ATTRIBUTE3 := l_recipe_rec.attribute3;
1886 l_recipe_update_flex.ATTRIBUTE4 := l_recipe_rec.attribute4;
1887 l_recipe_update_flex.ATTRIBUTE5 := l_recipe_rec.attribute5;
1888 l_recipe_update_flex.ATTRIBUTE6 := l_recipe_rec.attribute6;
1889 l_recipe_update_flex.ATTRIBUTE7 := l_recipe_rec.attribute7;
1890 l_recipe_update_flex.ATTRIBUTE8 := l_recipe_rec.attribute8;
1891 l_recipe_update_flex.ATTRIBUTE9 := l_recipe_rec.attribute9;
1892 l_recipe_update_flex.ATTRIBUTE10 := l_recipe_rec.attribute10;
1893 l_recipe_update_flex.ATTRIBUTE11 := l_recipe_rec.attribute11;
1894 l_recipe_update_flex.ATTRIBUTE12 := l_recipe_rec.attribute12;
1895 l_recipe_update_flex.ATTRIBUTE13 := l_recipe_rec.attribute13;
1896 l_recipe_update_flex.ATTRIBUTE14 := l_recipe_rec.attribute14;
1897 l_recipe_update_flex.ATTRIBUTE15 := l_recipe_rec.attribute15;
1898 l_recipe_update_flex.ATTRIBUTE16 := l_recipe_rec.attribute16;
1899 l_recipe_update_flex.ATTRIBUTE17 := l_recipe_rec.attribute17;
1900 l_recipe_update_flex.ATTRIBUTE18 := l_recipe_rec.attribute18;
1901 l_recipe_update_flex.ATTRIBUTE19 := l_recipe_rec.attribute19;
1902 l_recipe_update_flex.ATTRIBUTE20 := l_recipe_rec.attribute20;
1903 l_recipe_update_flex.ATTRIBUTE21 := l_recipe_rec.attribute21;
1904 l_recipe_update_flex.ATTRIBUTE22 := l_recipe_rec.attribute22;
1905 l_recipe_update_flex.ATTRIBUTE23 := l_recipe_rec.attribute23;
1906 l_recipe_update_flex.ATTRIBUTE24 := l_recipe_rec.attribute24;
1907 l_recipe_update_flex.ATTRIBUTE25 := l_recipe_rec.attribute25;
1908 l_recipe_update_flex.ATTRIBUTE26 := l_recipe_rec.attribute26;
1909 l_recipe_update_flex.ATTRIBUTE27 := l_recipe_rec.attribute27;
1910 l_recipe_update_flex.ATTRIBUTE28 := l_recipe_rec.attribute28;
1911 l_recipe_update_flex.ATTRIBUTE29 := l_recipe_rec.attribute29;
1912 l_recipe_update_flex.ATTRIBUTE30 := l_recipe_rec.attribute30;
1913
1914 GMD_RECIPE_HEADER_PVT.Update_Recipe_Header
1915 ( p_recipe_header_rec => l_recipe_tbl
1916 ,p_flex_header_rec => l_recipe_update_flex
1917 ,x_return_status => l_return_status);
1918
1919
1920 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1921 RAISE UPDATE_RECIPE_EXCEPTION;
1922 END IF;
1923
1924 --- If recipe number and/or version have changed, we need to update them. This
1925 --- happens when creating a new recipe, because a dummy recipe header is created in
1926 --- the database. User is then prompted to enter a valid recipe number/version prior
1927 --- to modifying the recipe header.
1928
1929 IF p_update_number_version = 'Y' THEN
1930
1931 IF l_recipe_rec.recipe_no <> p_recipe_no OR
1932 l_recipe_rec.recipe_version <> p_recipe_version THEN
1933
1934 UPDATE
1935 GMD_RECIPES_B
1936 SET
1937 recipe_no = p_recipe_no,
1938 recipe_version = p_recipe_version
1939 WHERE
1940 recipe_id = p_recipe_id;
1941 --4504794, this will defaults the PI instructions.
1942 GMD_PROCESS_INSTR_UTILS.COPY_PROCESS_INSTR(
1943 p_entity_name => 'RECIPE',
1944 p_entity_id => p_recipe_id,
1945 x_return_status => l_return_status,
1946 x_msg_count => l_msg_count,
1947 x_msg_data => l_msg_data);
1948 END IF;
1949 END IF;
1950
1951 --- The routing has changed, so we need to delete all records related to the
1952 --- old routing.
1953 IF (l_recipe_rec.routing_id <> p_routing_id) THEN
1954
1955 --- Delete old rows in step material table
1956 FOR old_step_mat IN Get_Old_Stp_Mat LOOP
1957
1958 DELETE gmd_recipe_step_materials
1959 WHERE recipe_id = p_recipe_id AND
1960 routingstep_id = old_step_mat.routingstep_id;
1961
1962 END LOOP;
1963
1964 --- Delete old rows in recipe step table
1965 FOR old_step IN Get_Old_Stp LOOP
1966
1967 DELETE gmd_recipe_routing_steps
1968 WHERE recipe_id = p_recipe_id AND
1969 routingstep_id = old_step.routingstep_id;
1970
1971 END LOOP;
1972
1973 END IF;
1974
1975 EXCEPTION
1976 WHEN UPDATE_RECIPE_EXCEPTION THEN
1977 FND_MSG_PUB.GET( p_msg_index => 1,
1978 p_data => l_message,
1979 p_encoded => 'F',
1980 p_msg_index_out => l_dummy);
1981
1982 x_return_code := 'F';
1983 x_error_msg := l_message;
1984
1985 WHEN RECORD_CHANGED_EXCEPTION THEN
1986 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
1987 x_return_code := 'F';
1988 x_error_msg := FND_MESSAGE.GET;
1989
1990 WHEN OTHERS THEN
1991 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
1992 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
1993 x_return_code := 'F';
1994 x_error_msg := FND_MESSAGE.GET;
1995
1996 END Update_Recipe_Header;
1997
1998
1999 /* Api start of comments
2000 +============================================================================
2001 | PROCEDURE NAME
2002 | Create_Recipe_Header
2003 |
2004 | DESCRIPTION
2005 | Create recipe header
2006 |
2007 | INPUT PARAMETERS
2008 |
2009 | OUTPUT PARAMETERS
2010 | x_recipe_id NUMBER
2011 | x_return_code VARCHAR2(1)
2012 | x_error_msg VARCHAR2(100)
2013 |
2014 | HISTORY
2015 | 08-OCT-2002 Eddie Oumerretane Created.
2016 | 22-Apr-2008 RLNAGARA Fixed Process Loss ME
2017 +=============================================================================
2018 Api end of comments
2019 */
2020 PROCEDURE Create_Recipe_Header ( p_orgn_id IN NUMBER,
2021 x_recipe_id OUT NOCOPY NUMBER,
2022 x_return_code OUT NOCOPY VARCHAR2,
2023 x_error_msg OUT NOCOPY VARCHAR2) IS
2024
2025 l_user_id NUMBER := fnd_global.user_id;
2026 -- FND_PROFILE.VALUE('USER_ID');
2027 l_login_id NUMBER := fnd_global.login_id;
2028 --FND_PROFILE.VALUE('LOGIN_ID');
2029
2030 CURSOR Cur_recipe_id IS
2031 SELECT gmd_recipe_id_s.NEXTVAL
2032 FROM FND_DUAL;
2033
2034
2035 l_return_status VARCHAR2(5);
2036 l_owner_orgn_code VARCHAR2(4);
2037 l_timestamp DATE;
2038 l_recipe_no VARCHAR2(32);
2039 l_recipe_rec GMD_RECIPES%ROWTYPE;
2040 INSERT_RECIPE_EXCEPTION EXCEPTION;
2041
2042 l_message_count NUMBER;
2043 l_message_list VARCHAR2(2000);
2044 l_message VARCHAR2(1000);
2045 l_dummy NUMBER;
2046 l_rowid VARCHAR2(32);
2047 l_formula_security_id NUMBER;
2048
2049 BEGIN
2050
2051 x_return_code := FND_API.G_RET_STS_SUCCESS;
2052 x_error_msg := FND_MESSAGE.GET;
2053
2054 OPEN Cur_recipe_id;
2055 FETCH Cur_recipe_id INTO x_recipe_id;
2056 CLOSE Cur_recipe_id;
2057
2058
2059 l_timestamp := SYSDATE;
2060 --- Create a unique recipe number. This is used to initialize the recipe
2061 --- in the database. User will be prompted to enter a valid recipe number
2062 --- prior to saving.
2063
2064 l_recipe_no := x_recipe_id || '#' ||
2065 TO_CHAR(l_timestamp, 'YYYYMMDDHH24MISS');
2066 --Call to delete the row with formula_id -1 to fix the issue reported in 3157487
2067 -- Commented out temp
2068 /*
2069 DELETE FROM gmd_formula_security
2070 WHERE orgn_code = l_owner_orgn_code
2071 AND formula_id = -1;
2072 --Call to insert the row with formula_id -1 to fix the issue reported in 3157487
2073
2074
2075 gmd_formula_security_pkg.insert_row (
2076 X_FORMULA_SECURITY_ID => l_formula_security_id,
2077 X_FORMULA_ID => -1,
2078 X_ACCESS_TYPE_IND => 'U',
2079 X_ORGN_CODE => l_owner_orgn_code,
2080 X_USER_ID => l_user_id,
2081 X_RESPONSIBILITY_ID => NULL,
2082 X_OTHER_ORGN => NULL,
2083 X_CREATION_DATE => l_timestamp,
2084 X_CREATED_BY => l_user_id,
2085 X_LAST_UPDATE_DATE => l_timestamp,
2086 X_LAST_UPDATED_BY => l_user_id,
2087 X_LAST_UPDATE_LOGIN => l_login_id);
2088 */
2089 -- KSHUKLA added the following
2090 -- As per as bug 3843246
2091 delete from FM_FORM_MST_B where FORMULA_ID = -1;
2092
2093 insert into
2094 FM_FORM_MST_B(FORMULA_ID,
2095 OWNER_ORGANIZATION_ID,
2096 DELETE_MARK,
2097 FORMULA_STATUS,
2098 OWNER_ID,
2099 FORMULA_NO,
2100 FORMULA_VERS,
2101 FORMULA_TYPE,
2102 INACTIVE_IND,
2103 SCALE_TYPE,
2104 FORMULA_DESC1,
2105 CREATION_DATE,
2106 CREATED_BY,
2107 LAST_UPDATE_DATE,
2108 LAST_UPDATED_BY,
2109 LAST_UPDATE_LOGIN)
2110 values (-1,
2111 p_orgn_id ,
2112 1,
2113 100,
2114 l_user_id,
2115 -1,
2116 -1,
2117 0,
2118 1,
2119 1,
2120 -1,
2121 l_timestamp,
2122 l_user_id,
2123 l_timestamp,
2124 l_user_id,
2125 l_login_id);
2126 -- End BUg 3843246
2127
2128 GMD_RECIPES_MLS.Insert_Row(
2129 X_ROWID => l_rowid,
2130 X_RECIPE_ID => x_recipe_id,
2131 X_RECIPE_NO => l_recipe_no,
2132 X_RECIPE_VERSION => 1,
2133 X_OWNER_ORGANIZATION_ID => p_orgn_id,
2134 X_CREATION_ORGANIZATION_ID => p_orgn_id,
2135 X_FORMULA_ID => -1,
2136 X_ROUTING_ID => NULL,
2137 X_PROJECT_ID => NULL,
2138 X_RECIPE_STATUS => '100',
2139 X_CALCULATE_STEP_QUANTITY => 0,
2140 X_PLANNED_PROCESS_LOSS => NULL,
2141 X_RECIPE_DESCRIPTION => 'New',
2142 X_OWNER_ID => l_user_id,
2143 X_OWNER_LAB_TYPE => NULL,
2144 --Additional 3 column added - Begin
2145 X_CONTIGUOUS_IND => 0,
2146 -- By default this value is 'N'
2147 -- for gmd-gmo convergence
2148 X_ENHANCED_PI_IND => 'N',
2149 X_RECIPE_TYPE => 0,
2150 --Additional 3 column added- End
2151 X_ATTRIBUTE_CATEGORY => NULL,
2152 X_ATTRIBUTE1 => NULL,
2153 X_ATTRIBUTE2 => NULL,
2154 X_ATTRIBUTE3 => NULL,
2155 X_ATTRIBUTE4 => NULL,
2156 X_ATTRIBUTE5 => NULL,
2157 X_ATTRIBUTE6 => NULL,
2158 X_ATTRIBUTE7 => NULL,
2159 X_ATTRIBUTE8 => NULL,
2160 X_ATTRIBUTE9 => NULL,
2161 X_ATTRIBUTE10 => NULL,
2162 X_ATTRIBUTE11 => NULL,
2163 X_ATTRIBUTE12 => NULL,
2164 X_ATTRIBUTE13 => NULL,
2165 X_ATTRIBUTE14 => NULL,
2166 X_ATTRIBUTE15 => NULL,
2167 X_ATTRIBUTE16 => NULL,
2168 X_ATTRIBUTE17 => NULL,
2169 X_ATTRIBUTE18 => NULL,
2170 X_ATTRIBUTE19 => NULL,
2171 X_ATTRIBUTE20 => NULL,
2172 X_ATTRIBUTE21 => NULL,
2173 X_ATTRIBUTE22 => NULL,
2174 X_ATTRIBUTE23 => NULL,
2175 X_ATTRIBUTE24 => NULL,
2176 X_ATTRIBUTE25 => NULL,
2177 X_ATTRIBUTE26 => NULL,
2178 X_ATTRIBUTE27 => NULL,
2179 X_ATTRIBUTE28 => NULL,
2180 X_ATTRIBUTE29 => NULL,
2181 X_ATTRIBUTE30 => NULL,
2182 X_DELETE_MARK => 0,
2183 X_TEXT_CODE => NULL,
2184 X_CREATION_DATE => l_timestamp,
2185 X_CREATED_BY => l_user_id,
2186 X_LAST_UPDATE_DATE => l_timestamp,
2187 X_LAST_UPDATED_BY => l_user_id,
2188 X_LAST_UPDATE_LOGIN => l_login_id,
2189 X_FIXED_PROCESS_LOSS => NULL, /*RLNAGARA 6811759*/
2190 X_FIXED_PROCESS_LOSS_UOM => NULL);
2191
2192 EXCEPTION
2193 WHEN INSERT_RECIPE_EXCEPTION THEN
2194 FND_MSG_PUB.GET( p_msg_index => 1,
2195 p_data => l_message,
2196 p_encoded => 'F',
2197 p_msg_index_out => l_dummy);
2198
2199 x_return_code := 'F';
2200 x_error_msg := l_message;
2201
2202 WHEN OTHERS THEN
2203 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2204 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2205 x_return_code := 'F';
2206 x_error_msg := FND_MESSAGE.GET;
2207
2208 END Create_Recipe_Header;
2209
2210
2211 /* Api start of comments
2212 +============================================================================
2213 | PROCEDURE NAME
2214 | Add_Recipe_Customer
2215 |
2216 | DESCRIPTION
2217 | Add a new customer to the recipe
2218 |
2219 | INPUT PARAMETERS
2220 | p_recipe_id NUMBER
2221 | p_customer_id NUMBER
2222 | p_text_code NUMBER
2223 | p_last_update_date DATE
2224 |
2225 | OUTPUT PARAMETERS
2226 | x_return_code VARCHAR2(1)
2227 | x_error_msg VARCHAR2(100)
2228 |
2229 | HISTORY
2230 | 15-OCT-2002 Eddie Oumerretane Created.
2231 |
2232 +=============================================================================
2233 Api end of comments
2234 */
2235 PROCEDURE Add_Recipe_Customer (p_recipe_id IN NUMBER,
2236 p_customer_id IN NUMBER,
2237 p_text_code IN NUMBER,
2238 p_org_id IN NUMBER, --new addition
2239 p_site_use_id IN NUMBER, --new addition
2240 p_last_update_date IN DATE,
2241 x_return_code OUT NOCOPY VARCHAR2,
2242 x_error_msg OUT NOCOPY VARCHAR2) IS
2243
2244
2245 l_cust_tbl GMD_RECIPE_DETAIL.recipe_detail_tbl;
2246 l_status VARCHAR2(30);
2247 l_msg_cnt NUMBER;
2248 l_msg_dat VARCHAR2(30);
2249 l_message VARCHAR2(1000);
2250 l_dummy NUMBER;
2251 l_login_id NUMBER := FND_PROFILE.VALUE('LOGIN_ID');
2252 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
2253 CREATE_CUST_EXCEPTION EXCEPTION;
2254
2255 BEGIN
2256
2257 x_return_code := 'S';
2258 x_error_msg := '';
2259
2260 IF (p_text_code <= 0) THEN
2261 l_cust_tbl(1).text_code := NULL;
2262 ELSE
2263 l_cust_tbl(1).text_code := p_text_code;
2264 END IF;
2265
2266 l_cust_tbl(1).user_id := l_user_id;
2267 l_cust_tbl(1).recipe_id := p_recipe_id;
2268 l_cust_tbl(1).customer_id := p_customer_id;
2269 l_cust_tbl(1).org_id := p_org_id; --new addition
2270 l_cust_tbl(1).site_id := p_site_use_id; --new addition
2271 l_cust_tbl(1).creation_date := p_last_update_date;
2272 l_cust_tbl(1).created_by := l_user_id;
2273 l_cust_tbl(1).last_updated_by := l_user_id;
2274 l_cust_tbl(1).last_update_login := l_login_id;
2275 l_cust_tbl(1).last_update_date := p_last_update_date;
2276
2277 GMD_RECIPE_DETAIL.Create_Recipe_Customers(p_api_version => 1.0,
2278 p_init_msg_list => 'T',
2279 p_commit => 'F',
2280 p_called_from_forms => 'NO',
2281 x_return_status => l_status,
2282 x_msg_count => l_msg_cnt,
2283 x_msg_data => l_msg_dat,
2284 p_recipe_detail_tbl => l_cust_tbl);
2285
2286
2287 IF (l_status <> 'S') THEN
2288 RAISE CREATE_CUST_EXCEPTION;
2289 END IF;
2290
2291 EXCEPTION
2292 WHEN CREATE_CUST_EXCEPTION THEN
2293 FND_MSG_PUB.GET( p_msg_index => 1,
2294 p_data => l_message,
2295 p_encoded => 'F',
2296 p_msg_index_out => l_dummy);
2297 x_return_code := 'F';
2298 x_error_msg := l_message;
2299
2300 WHEN NO_DATA_FOUND THEN
2301 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
2302 x_return_code := 'F';
2303 x_error_msg := FND_MESSAGE.GET;
2304
2305 WHEN OTHERS THEN
2306 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2307 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2308 x_return_code := 'F';
2309 x_error_msg := FND_MESSAGE.GET;
2310
2311 END Add_Recipe_Customer;
2312
2313 /* Api start of comments
2314 +============================================================================
2315 | PROCEDURE NAME
2316 | Delete_Recipe_Customer
2317 |
2318 | DESCRIPTION
2319 | Delete customer from the recipe
2320 |
2321 | INPUT PARAMETERS
2322 | p_recipe_id NUMBER
2323 | p_customer_id NUMBER
2324 | p_last_update_date DATE
2325 |
2326 | OUTPUT PARAMETERS
2327 | x_return_code VARCHAR2(1)
2328 | x_error_msg VARCHAR2(100)
2329 |
2330 | HISTORY
2331 | 15-OCT-2002 Eddie Oumerretane Created.
2332 |
2333 +=============================================================================
2334 Api end of comments
2335 */
2336 PROCEDURE Delete_Recipe_Customer (p_recipe_id IN NUMBER,
2337 p_customer_id IN NUMBER,
2338 p_last_update_date IN DATE,
2339 x_return_code OUT NOCOPY VARCHAR2,
2340 x_error_msg OUT NOCOPY VARCHAR2) IS
2341
2342 l_status VARCHAR2(30);
2343
2344 BEGIN
2345
2346 x_return_code := 'S';
2347 x_error_msg := '';
2348
2349 --- This statement should be replaced by an API when it is available ...
2350
2351 DELETE
2352 gmd_recipe_customers
2353 WHERE
2354 recipe_id = p_recipe_id AND
2355 customer_id = p_customer_id AND
2356 last_update_date = p_last_update_date;
2357
2358 IF SQL%NOTFOUND THEN
2359 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
2360 x_return_code := 'F';
2361 x_error_msg := FND_MESSAGE.GET;
2362 END IF;
2363
2364 EXCEPTION
2365 WHEN OTHERS THEN
2366 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2367 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2368 x_return_code := 'F';
2369 x_error_msg := FND_MESSAGE.GET;
2370
2371 End Delete_Recipe_Customer;
2372
2373
2374 /* Api start of comments
2375 +============================================================================
2376 | PROCEDURE NAME
2377 | Create_Process_Loss
2378 |
2379 | DESCRIPTION
2380 | Add a new organization specific process loss to the recipe
2381 |
2382 | INPUT PARAMETERS
2383 | p_recipe_id NUMBER
2384 | p_orgn_code VARCHAR2
2385 | p_process_loss NUMBER
2386 | p_text_code NUMBER
2387 | p_last_update_date DATE
2388 | p_loss_id NUMBER
2389 |
2390 | OUTPUT PARAMETERS
2391 | x_loss_id NUMBER
2392 | x_return_code VARCHAR2(1)
2393 | x_error_msg VARCHAR2(100)
2394 |
2395 | HISTORY
2396 | 30-OCT-2002 Eddie Oumerretane Created.
2397 |
2398 +=============================================================================
2399 Api end of comments
2400 */
2401 PROCEDURE Create_Process_Loss (p_recipe_id IN NUMBER,
2402 p_orgn_id IN NUMBER,
2403 p_process_loss IN NUMBER,
2404 p_text_code IN NUMBER,
2405 p_contiguous_ind IN NUMBER,
2406 p_last_update_date IN DATE,
2407 p_loss_id IN NUMBER,
2408 x_loss_id OUT NOCOPY NUMBER,
2409 x_return_code OUT NOCOPY VARCHAR2,
2410 x_error_msg OUT NOCOPY VARCHAR2) IS
2411 CURSOR Cur_loss_id IS
2412 SELECT gmd_recipe_process_loss_id_s.NEXTVAL
2413 FROM SYS.DUAL;
2414
2415 l_loss_tbl GMD_RECIPE_DETAIL.recipe_dtl;
2416 l_status VARCHAR2(30);
2417 l_msg_cnt NUMBER;
2418 l_msg_dat VARCHAR2(30);
2419 l_message VARCHAR2(1000);
2420 l_dummy NUMBER;
2421 l_login_id NUMBER := FND_PROFILE.VALUE('LOGIN_ID');
2422 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
2423 CREATE_LOSS_EXCEPTION EXCEPTION;
2424
2425 BEGIN
2426
2427 x_return_code := 'S';
2428 x_error_msg := '';
2429
2430 IF p_loss_id IS NULL THEN
2431 OPEN Cur_loss_id;
2432 FETCH Cur_loss_id INTO x_loss_id;
2433 CLOSE Cur_loss_id;
2434 ELSE
2435 x_loss_id := p_loss_id;
2436 END IF;
2437
2438 IF (p_text_code <= 0) THEN
2439 l_loss_tbl.text_code := NULL;
2440 ELSE
2441 l_loss_tbl.text_code := p_text_code;
2442 END IF;
2443
2444 l_loss_tbl.user_id := l_user_id;
2445 l_loss_tbl.recipe_id := p_recipe_id;
2446 l_loss_tbl.recipe_process_loss_id := x_loss_id;
2447 l_loss_tbl.organization_id := p_orgn_id;
2448 l_loss_tbl.process_loss := p_process_loss;
2449 l_loss_tbl.CONTIGUOUS_IND :=p_contiguous_ind; --adding
2450 l_loss_tbl.creation_date := p_last_update_date;
2451 l_loss_tbl.created_by := l_user_id;
2452 l_loss_tbl.last_updated_by := l_user_id;
2453 l_loss_tbl.last_update_login := l_login_id;
2454 l_loss_tbl.last_update_date := p_last_update_date;
2455
2456 GMD_RECIPE_DETAIL_PVT.Create_Recipe_Process_Loss(
2457 p_recipe_detail_rec => l_loss_tbl,
2458 x_return_status => l_status);
2459
2460
2461 IF (l_status <> 'S') THEN
2462 RAISE CREATE_LOSS_EXCEPTION;
2463 END IF;
2464
2465 EXCEPTION
2466 WHEN CREATE_LOSS_EXCEPTION THEN
2467 FND_MSG_PUB.GET( p_msg_index => 1,
2468 p_data => l_message,
2469 p_encoded => 'F',
2470 p_msg_index_out => l_dummy);
2471 x_return_code := 'F';
2472 x_error_msg := l_message;
2473
2474 WHEN NO_DATA_FOUND THEN
2475 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
2476 x_return_code := 'F';
2477 x_error_msg := FND_MESSAGE.GET;
2478
2479 WHEN OTHERS THEN
2480 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2481 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2482 x_return_code := 'F';
2483 x_error_msg := FND_MESSAGE.GET;
2484
2485 END Create_Process_Loss;
2486
2487 /* Api start of comments
2488 +============================================================================
2489 | PROCEDURE NAME
2490 | Add_Org_Process_Loss
2491 |
2492 | DESCRIPTION
2493 | Add a new organization specific process loss to the recipe
2494 |
2495 | INPUT PARAMETERS
2496 | p_recipe_id NUMBER
2497 | p_orgn_code VARCHAR2
2498 | p_process_loss NUMBER
2499 | p_text_code NUMBER
2500 | p_last_update_date DATE
2501 | p_loss_id NUMBER
2502 |
2503 | OUTPUT PARAMETERS
2504 | x_loss_id NUMBER
2505 | x_return_code VARCHAR2(1)
2506 | x_error_msg VARCHAR2(100)
2507 |
2508 | HISTORY
2509 | 10-DEC-2002 Eddie Oumerretane Created.
2510 |
2511 +=============================================================================
2512 Api end of comments
2513 */
2514 PROCEDURE Add_Org_Process_Loss (p_recipe_id IN NUMBER,
2515 p_orgn_id IN NUMBER,
2516 p_process_loss IN NUMBER,
2517 p_text_code IN NUMBER,
2518 p_contiguous_ind IN NUMBER,
2519 p_last_update_date IN DATE,
2520 x_loss_id OUT NOCOPY NUMBER,
2521 x_return_code OUT NOCOPY VARCHAR2,
2522 x_error_msg OUT NOCOPY VARCHAR2) IS
2523 BEGIN
2524
2525 Create_Process_Loss (p_recipe_id => p_recipe_id,
2526 p_orgn_id => p_orgn_id,
2527 p_process_loss => p_process_loss,
2528 p_text_code => p_text_code,
2529 p_contiguous_ind => p_contiguous_ind,
2530 p_last_update_date => p_last_update_date,
2531 p_loss_id => NULL,
2532 x_loss_id => x_loss_id,
2533 x_return_code => x_return_code,
2534 x_error_msg => x_error_msg);
2535
2536 END Add_Org_Process_Loss;
2537
2538 /* Api start of comments
2539 +============================================================================
2540 | PROCEDURE NAME
2541 | Delete_Org_Process_Loss
2542 |
2543 | DESCRIPTION
2544 | Delete organization specific process loss from the recipe
2545 |
2546 | INPUT PARAMETERS
2547 | p_loss_id NUMBER
2548 | p_last_update_date DATE
2549 |
2550 | OUTPUT PARAMETERS
2551 | x_return_code VARCHAR2(1)
2552 | x_error_msg VARCHAR2(100)
2553 |
2554 | HISTORY
2555 | 30-OCT-2002 Eddie Oumerretane Created.
2556 |
2557 +=============================================================================
2558 Api end of comments
2559 */
2560 PROCEDURE Delete_Org_Process_Loss (p_loss_id IN NUMBER,
2561 p_last_update_date IN DATE,
2562 x_return_code OUT NOCOPY VARCHAR2,
2563 x_error_msg OUT NOCOPY VARCHAR2) IS
2564
2565 l_status VARCHAR2(30);
2566
2567 BEGIN
2568
2569 x_return_code := 'S';
2570 x_error_msg := '';
2571
2572 --- This statement should be replaced by an API when it is available ...
2573
2574 DELETE
2575 gmd_recipe_process_loss
2576 WHERE
2577 recipe_process_loss_id = p_loss_id AND
2578 last_update_date = p_last_update_date;
2579
2580 IF SQL%NOTFOUND THEN
2581 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
2582 x_return_code := 'F';
2583 x_error_msg := FND_MESSAGE.GET;
2584 END IF;
2585
2586 EXCEPTION
2587 WHEN OTHERS THEN
2588 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2589 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2590 x_return_code := 'F';
2591 x_error_msg := FND_MESSAGE.GET;
2592
2593 END Delete_Org_Process_Loss;
2594
2595 /* Api start of comments
2596 +============================================================================
2597 | PROCEDURE NAME
2598 | Update_Org_Process_Loss
2599 |
2600 | DESCRIPTION
2601 | Update an organization specific process loss
2602 |
2603 | INPUT PARAMETERS
2604 | p_recipe_id NUMBER
2605 | p_recipe_loss_id NUMBER
2606 | p_orgn_id VARCHAR2
2607 | p_process_loss NUMBER
2608 | p_text_code NUMBER
2609 | p_last_update_date DATE
2610 | p_last_update_date_orig DATE
2611 |
2612 | OUTPUT PARAMETERS
2613 | x_return_code VARCHAR2(1)
2614 | x_error_msg VARCHAR2(100)
2615 |
2616 | HISTORY
2617 | 09-OCT-2002 Eddie Oumerretane Created.
2618 |
2619 +=============================================================================
2620 Api end of comments
2621 */
2622 PROCEDURE Update_Org_Process_Loss (p_recipe_id IN NUMBER,
2623 p_recipe_loss_id IN NUMBER,
2624 p_orgn_id IN NUMBER,
2625 p_process_loss IN NUMBER,
2626 p_text_code IN NUMBER,
2627 p_contiguous_ind IN NUMBER,
2628 p_last_update_date IN DATE,
2629 p_last_update_date_orig IN DATE,
2630 x_return_code OUT NOCOPY VARCHAR2,
2631 x_error_msg OUT NOCOPY VARCHAR2) IS
2632
2633 CURSOR Get_Loss IS
2634 SELECT 1
2635 FROM
2636 gmd_recipe_process_loss
2637 WHERE
2638 recipe_process_loss_id = p_recipe_loss_id AND
2639 last_update_date = p_last_update_date_orig;
2640
2641 l_loss_tbl GMD_RECIPE_DETAIL.recipe_dtl;
2642 l_status VARCHAR2(30);
2643 l_msg_cnt NUMBER;
2644 l_msg_dat VARCHAR2(30);
2645 l_message VARCHAR2(1000);
2646 l_dummy NUMBER;
2647 l_login_id NUMBER := FND_PROFILE.VALUE('LOGIN_ID');
2648 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
2649 UPDATE_LOSS_EXCEPTION EXCEPTION;
2650
2651 BEGIN
2652
2653 x_return_code := 'S';
2654 x_error_msg := '';
2655
2656 OPEN Get_Loss;
2657 FETCH Get_Loss INTO l_dummy;
2658
2659 IF Get_Loss%NOTFOUND THEN
2660 CLOSE Get_Loss;
2661 RAISE NO_DATA_FOUND;
2662 END IF;
2663
2664 CLOSE Get_Loss;
2665
2666 IF (p_text_code <= 0) THEN
2667 l_loss_tbl.text_code := NULL;
2668 ELSE
2669 l_loss_tbl.text_code := p_text_code;
2670 END IF;
2671
2672 l_loss_tbl.user_id := l_user_id;
2673 l_loss_tbl.recipe_id := p_recipe_id;
2674 l_loss_tbl.recipe_process_loss_id := p_recipe_loss_id;
2675 l_loss_tbl.organization_id := p_orgn_id;
2676 l_loss_tbl.process_loss := p_process_loss;
2677 l_loss_tbl.CONTIGUOUS_IND := p_contiguous_ind; --adding
2678
2679 l_loss_tbl.last_updated_by := l_user_id;
2680 l_loss_tbl.last_update_login := l_login_id;
2681 l_loss_tbl.last_update_date := p_last_update_date;
2682
2683 GMD_RECIPE_DETAIL_PVT.Update_Recipe_Process_Loss(
2684 p_recipe_detail_rec => l_loss_tbl,
2685 x_return_status => l_status);
2686
2687
2688 IF (l_status <> 'S') THEN
2689 RAISE UPDATE_LOSS_EXCEPTION;
2690 END IF;
2691
2692 EXCEPTION
2693 WHEN UPDATE_LOSS_EXCEPTION THEN
2694 FND_MSG_PUB.GET( p_msg_index => 1,
2695 p_data => l_message,
2696 p_encoded => 'F',
2697 p_msg_index_out => l_dummy);
2698 x_return_code := 'F';
2699 x_error_msg := l_message;
2700
2701 WHEN NO_DATA_FOUND THEN
2702 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
2703 x_return_code := 'F';
2704 x_error_msg := FND_MESSAGE.GET;
2705
2706 WHEN OTHERS THEN
2707 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2708 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2709 x_return_code := 'F';
2710 x_error_msg := FND_MESSAGE.GET;
2711
2712 END Update_Org_Process_Loss;
2713
2714 /* Api start of comments
2715 +============================================================================
2716 | PROCEDURE NAME
2717 | Delete_Recipe
2718 |
2719 | DESCRIPTION
2720 | Mark for purge the given recipe
2721 |
2722 | INPUT PARAMETERS
2723 | p_recipe_id NUMBER
2724 | p_last_update_date_orig DATE
2725 |
2726 | OUTPUT PARAMETERS
2727 | x_return_code VARCHAR2(1)
2728 | x_error_msg VARCHAR2(100)
2729 |
2730 | HISTORY
2731 | 13-NOV-2002 Eddie Oumerretane Created.
2732 |
2733 +=============================================================================
2734 Api end of comments
2735 */
2736 PROCEDURE Delete_Recipe (p_recipe_id IN NUMBER,
2737 p_last_update_date_orig IN DATE,
2738 x_return_code OUT NOCOPY VARCHAR2,
2739 x_error_msg OUT NOCOPY VARCHAR2) IS
2740
2741 CURSOR Get_Recipe IS
2742 SELECT *
2743 FROM
2744 gmd_recipes
2745 WHERE
2746 recipe_id = p_recipe_id AND
2747 last_update_date = p_last_update_date_orig;
2748
2749 l_recipe_hdr GMD_RECIPE_HEADER.recipe_hdr;
2750 l_recipe_flex GMD_RECIPE_HEADER.update_flex;
2751 l_recipe_rec GMD_RECIPES%ROWTYPE;
2752
2753 l_status VARCHAR2(30);
2754 l_msg_cnt NUMBER;
2755 l_msg_dat VARCHAR2(30);
2756 l_message VARCHAR2(1000);
2757 l_dummy NUMBER;
2758 l_login_id NUMBER := FND_PROFILE.VALUE('LOGIN_ID');
2759 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
2760 DELETE_RECIPE_EXCEPTION EXCEPTION;
2761
2762 BEGIN
2763
2764 x_return_code := 'S';
2765 x_error_msg := '';
2766
2767 OPEN Get_Recipe;
2768 FETCH Get_Recipe INTO l_recipe_rec;
2769
2770 IF Get_Recipe%NOTFOUND THEN
2771 CLOSE Get_Recipe;
2772 RAISE NO_DATA_FOUND;
2773 END IF;
2774
2775 CLOSE Get_Recipe;
2776
2777
2778 l_recipe_hdr.ROUTING_ID := l_recipe_rec.routing_id;
2779 l_recipe_hdr.PLANNED_PROCESS_LOSS := l_recipe_rec.planned_process_loss;
2780 l_recipe_hdr.TEXT_CODE := l_recipe_rec.text_code;
2781 l_recipe_hdr.PROJECT_ID := l_recipe_rec.project_id;
2782 l_recipe_hdr.OWNER_LAB_TYPE := l_recipe_rec.owner_lab_type;
2783 l_recipe_hdr.RECIPE_NO := l_recipe_rec.recipe_no;
2784 l_recipe_hdr.RECIPE_VERSION := l_recipe_rec.recipe_version;
2785 l_recipe_hdr.RECIPE_ID := p_recipe_id;
2786 l_recipe_hdr.RECIPE_DESCRIPTION := l_recipe_rec.recipe_description;
2787 l_recipe_hdr.USER_ID := l_user_id;
2788 l_recipe_hdr.OWNER_ORGANIZATION_ID := l_recipe_rec.owner_organization_id;
2789 l_recipe_hdr.CREATION_ORGANIZATION_ID := l_recipe_rec.creation_organization_id;
2790 l_recipe_hdr.FORMULA_ID := l_recipe_rec.formula_id;
2791 l_recipe_hdr.RECIPE_STATUS := l_recipe_rec.recipe_status;
2792 l_recipe_hdr.DELETE_MARK := 1;
2793 l_recipe_hdr.LAST_UPDATED_BY := l_user_id;
2794 l_recipe_hdr.LAST_UPDATE_DATE := SYSDATE;
2795 l_recipe_hdr.LAST_UPDATE_LOGIN := l_login_id;
2796 l_recipe_hdr.OWNER_ID := l_recipe_rec.owner_id;
2797 l_recipe_hdr.CALCULATE_STEP_QUANTITY := l_recipe_rec.calculate_step_quantity;
2798
2799 l_recipe_flex.ATTRIBUTE_CATEGORY := l_recipe_rec.attribute_category;
2800 l_recipe_flex.ATTRIBUTE1 := l_recipe_rec.attribute1;
2801 l_recipe_flex.ATTRIBUTE2 := l_recipe_rec.attribute2;
2802 l_recipe_flex.ATTRIBUTE3 := l_recipe_rec.attribute3;
2803 l_recipe_flex.ATTRIBUTE4 := l_recipe_rec.attribute4;
2804 l_recipe_flex.ATTRIBUTE5 := l_recipe_rec.attribute5;
2805 l_recipe_flex.ATTRIBUTE6 := l_recipe_rec.attribute6;
2806 l_recipe_flex.ATTRIBUTE7 := l_recipe_rec.attribute7;
2807 l_recipe_flex.ATTRIBUTE8 := l_recipe_rec.attribute8;
2808 l_recipe_flex.ATTRIBUTE9 := l_recipe_rec.attribute9;
2809 l_recipe_flex.ATTRIBUTE10 := l_recipe_rec.attribute10;
2810 l_recipe_flex.ATTRIBUTE11 := l_recipe_rec.attribute11;
2811 l_recipe_flex.ATTRIBUTE12 := l_recipe_rec.attribute12;
2812 l_recipe_flex.ATTRIBUTE13 := l_recipe_rec.attribute13;
2813 l_recipe_flex.ATTRIBUTE14 := l_recipe_rec.attribute14;
2814 l_recipe_flex.ATTRIBUTE15 := l_recipe_rec.attribute15;
2815 l_recipe_flex.ATTRIBUTE16 := l_recipe_rec.attribute16;
2816 l_recipe_flex.ATTRIBUTE17 := l_recipe_rec.attribute17;
2817 l_recipe_flex.ATTRIBUTE18 := l_recipe_rec.attribute18;
2818 l_recipe_flex.ATTRIBUTE19 := l_recipe_rec.attribute19;
2819 l_recipe_flex.ATTRIBUTE20 := l_recipe_rec.attribute20;
2820 l_recipe_flex.ATTRIBUTE21 := l_recipe_rec.attribute21;
2821 l_recipe_flex.ATTRIBUTE22 := l_recipe_rec.attribute22;
2822 l_recipe_flex.ATTRIBUTE23 := l_recipe_rec.attribute23;
2823 l_recipe_flex.ATTRIBUTE24 := l_recipe_rec.attribute24;
2824 l_recipe_flex.ATTRIBUTE25 := l_recipe_rec.attribute25;
2825 l_recipe_flex.ATTRIBUTE26 := l_recipe_rec.attribute26;
2826 l_recipe_flex.ATTRIBUTE27 := l_recipe_rec.attribute27;
2827 l_recipe_flex.ATTRIBUTE28 := l_recipe_rec.attribute28;
2828 l_recipe_flex.ATTRIBUTE29 := l_recipe_rec.attribute29;
2829
2830 GMD_RECIPE_HEADER_PVT.Delete_Recipe_Header
2831 ( p_recipe_header_rec => l_recipe_hdr
2832 ,p_flex_header_rec => l_recipe_flex
2833 ,x_return_status => l_status);
2834
2835 IF (l_status <> 'S') THEN
2836 RAISE DELETE_RECIPE_EXCEPTION;
2837 END IF;
2838
2839 COMMIT;
2840
2841 EXCEPTION
2842 WHEN DELETE_RECIPE_EXCEPTION THEN
2843 FND_MSG_PUB.GET( p_msg_index => 1,
2844 p_data => l_message,
2845 p_encoded => 'F',
2846 p_msg_index_out => l_dummy);
2847 x_return_code := 'F';
2848 x_error_msg := l_message;
2849
2850 WHEN NO_DATA_FOUND THEN
2851 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
2852 x_return_code := 'F';
2853 x_error_msg := FND_MESSAGE.GET;
2854
2855 WHEN OTHERS THEN
2856 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
2857 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
2858 x_return_code := 'F';
2859 x_error_msg := FND_MESSAGE.GET;
2860
2861 END Delete_Recipe;
2862
2863 /* Api start of comments
2864 +============================================================================
2865 | PROCEDURE NAME
2866 | Undele_Recipe
2867 |
2868 | DESCRIPTION
2869 | Undelete the the given recipe
2870 |
2871 | INPUT PARAMETERS
2872 | p_recipe_id NUMBER
2873 | p_last_update_date_orig DATE
2874 |
2875 | OUTPUT PARAMETERS
2876 | x_return_code VARCHAR2(1)
2877 | x_error_msg VARCHAR2(100)
2878 |
2879 | HISTORY
2880 | 13-NOV-2002 Eddie Oumerretane Created.
2881 |
2882 +=============================================================================
2883 Api end of comments
2884 */
2885 PROCEDURE Undelete_Recipe (p_recipe_id IN NUMBER,
2886 p_last_update_date_orig IN DATE,
2887 x_return_code OUT NOCOPY VARCHAR2,
2888 x_error_msg OUT NOCOPY VARCHAR2) IS
2889
2890 CURSOR Get_Recipe IS
2891 SELECT *
2892 FROM
2893 gmd_recipes
2894 WHERE
2895 recipe_id = p_recipe_id AND
2896 last_update_date = p_last_update_date_orig;
2897
2898 l_recipe_hdr GMD_RECIPE_HEADER.recipe_hdr;
2899 l_recipe_flex GMD_RECIPE_HEADER.update_flex;
2900 l_recipe_rec GMD_RECIPES%ROWTYPE;
2901 l_status VARCHAR2(30);
2902 l_msg_cnt NUMBER;
2903 l_msg_dat VARCHAR2(30);
2904 l_message VARCHAR2(1000);
2905 l_dummy NUMBER;
2906 l_login_id NUMBER := FND_PROFILE.VALUE('LOGIN_ID');
2907 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
2908 DELETE_RECIPE_EXCEPTION EXCEPTION;
2909
2910 BEGIN
2911
2912 x_return_code := 'S';
2913 x_error_msg := '';
2914
2915 OPEN Get_Recipe;
2916 FETCH Get_Recipe INTO l_recipe_rec;
2917
2918 IF Get_Recipe%NOTFOUND THEN
2919 CLOSE Get_Recipe;
2920 RAISE NO_DATA_FOUND;
2921 END IF;
2922
2923 CLOSE Get_Recipe;
2924
2925
2926 l_recipe_hdr.ROUTING_ID := l_recipe_rec.routing_id;
2927 l_recipe_hdr.PLANNED_PROCESS_LOSS := l_recipe_rec.planned_process_loss;
2928 l_recipe_hdr.TEXT_CODE := l_recipe_rec.text_code;
2929 l_recipe_hdr.PROJECT_ID := l_recipe_rec.project_id;
2930 l_recipe_hdr.OWNER_LAB_TYPE := l_recipe_rec.owner_lab_type;
2931 l_recipe_hdr.RECIPE_NO := l_recipe_rec.recipe_no;
2932 l_recipe_hdr.RECIPE_VERSION := l_recipe_rec.recipe_version;
2933 l_recipe_hdr.RECIPE_ID := p_recipe_id;
2934 l_recipe_hdr.RECIPE_DESCRIPTION := l_recipe_rec.recipe_description;
2935 l_recipe_hdr.USER_ID := l_user_id;
2936 l_recipe_hdr.OWNER_ORGANIZATION_ID := l_recipe_rec.owner_organization_id;
2937 l_recipe_hdr.CREATION_ORGANIZATION_ID := l_recipe_rec.creation_organization_id;
2938 l_recipe_hdr.FORMULA_ID := l_recipe_rec.formula_id;
2939 l_recipe_hdr.RECIPE_STATUS := l_recipe_rec.recipe_status;
2940 l_recipe_hdr.DELETE_MARK := 0;
2941 l_recipe_hdr.LAST_UPDATED_BY := l_user_id;
2942 l_recipe_hdr.LAST_UPDATE_DATE := SYSDATE;
2943 l_recipe_hdr.LAST_UPDATE_LOGIN := l_login_id;
2944 l_recipe_hdr.OWNER_ID := l_recipe_rec.owner_id;
2945 l_recipe_hdr.CALCULATE_STEP_QUANTITY := l_recipe_rec.calculate_step_quantity;
2946
2947 l_recipe_flex.ATTRIBUTE_CATEGORY := l_recipe_rec.attribute_category;
2948 l_recipe_flex.ATTRIBUTE1 := l_recipe_rec.attribute1;
2949 l_recipe_flex.ATTRIBUTE2 := l_recipe_rec.attribute2;
2950 l_recipe_flex.ATTRIBUTE3 := l_recipe_rec.attribute3;
2951 l_recipe_flex.ATTRIBUTE4 := l_recipe_rec.attribute4;
2952 l_recipe_flex.ATTRIBUTE5 := l_recipe_rec.attribute5;
2953 l_recipe_flex.ATTRIBUTE6 := l_recipe_rec.attribute6;
2954 l_recipe_flex.ATTRIBUTE7 := l_recipe_rec.attribute7;
2955 l_recipe_flex.ATTRIBUTE8 := l_recipe_rec.attribute8;
2956 l_recipe_flex.ATTRIBUTE9 := l_recipe_rec.attribute9;
2957 l_recipe_flex.ATTRIBUTE10 := l_recipe_rec.attribute10;
2958 l_recipe_flex.ATTRIBUTE11 := l_recipe_rec.attribute11;
2959 l_recipe_flex.ATTRIBUTE12 := l_recipe_rec.attribute12;
2960 l_recipe_flex.ATTRIBUTE13 := l_recipe_rec.attribute13;
2961 l_recipe_flex.ATTRIBUTE14 := l_recipe_rec.attribute14;
2962 l_recipe_flex.ATTRIBUTE15 := l_recipe_rec.attribute15;
2963 l_recipe_flex.ATTRIBUTE16 := l_recipe_rec.attribute16;
2964 l_recipe_flex.ATTRIBUTE17 := l_recipe_rec.attribute17;
2965 l_recipe_flex.ATTRIBUTE18 := l_recipe_rec.attribute18;
2966 l_recipe_flex.ATTRIBUTE19 := l_recipe_rec.attribute19;
2967 l_recipe_flex.ATTRIBUTE20 := l_recipe_rec.attribute20;
2968 l_recipe_flex.ATTRIBUTE21 := l_recipe_rec.attribute21;
2969 l_recipe_flex.ATTRIBUTE22 := l_recipe_rec.attribute22;
2970 l_recipe_flex.ATTRIBUTE23 := l_recipe_rec.attribute23;
2971 l_recipe_flex.ATTRIBUTE24 := l_recipe_rec.attribute24;
2972 l_recipe_flex.ATTRIBUTE25 := l_recipe_rec.attribute25;
2973 l_recipe_flex.ATTRIBUTE26 := l_recipe_rec.attribute26;
2974 l_recipe_flex.ATTRIBUTE27 := l_recipe_rec.attribute27;
2975 l_recipe_flex.ATTRIBUTE28 := l_recipe_rec.attribute28;
2976 l_recipe_flex.ATTRIBUTE29 := l_recipe_rec.attribute29;
2977
2978 GMD_RECIPE_HEADER_PVT.Delete_Recipe_Header
2979 ( p_recipe_header_rec => l_recipe_hdr
2980 ,p_flex_header_rec => l_recipe_flex
2981 ,x_return_status => l_status);
2982
2983 IF (l_status <> 'S') THEN
2984 RAISE DELETE_RECIPE_EXCEPTION;
2985 END IF;
2986
2987 COMMIT;
2988
2989 EXCEPTION
2990 WHEN DELETE_RECIPE_EXCEPTION THEN
2991 FND_MSG_PUB.GET( p_msg_index => 1,
2992 p_data => l_message,
2993 p_encoded => 'F',
2994 p_msg_index_out => l_dummy);
2995 x_return_code := 'F';
2996 x_error_msg := l_message;
2997
2998 WHEN NO_DATA_FOUND THEN
2999 FND_MESSAGE.SET_NAME('FND', 'FND_RECORD_CHANGED_ERROR');
3000 x_return_code := 'F';
3001 x_error_msg := FND_MESSAGE.GET;
3002
3003 WHEN OTHERS THEN
3004 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
3005 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
3006 x_return_code := 'F';
3007 x_error_msg := FND_MESSAGE.GET;
3008
3009 END Undelete_Recipe;
3010
3011
3012 /* Api start of comments
3013 +============================================================================
3014 | PROCEDURE NAME
3015 | Get_Theoretical_Process_Loss
3016 |
3017 | DESCRIPTION
3018 | Retrieve theoretical process loss
3019 |
3020 | INPUT PARAMETERS
3021 | p_routing_id NUMBER
3022 | p_formula_id NUMBER
3023 |
3024 | OUTPUT PARAMETERS
3025 | x_theoretical_loss VARCHAR2(1)
3026 | x_return_code VARCHAR2(1)
3027 | x_error_msg VARCHAR2(100)
3028 |
3029 | HISTORY
3030 | 21-NOV-2002 Eddie Oumerretane Created.
3031 |
3032 +=============================================================================
3033 Api end of comments
3034 */
3035 PROCEDURE Get_Theoretical_Process_Loss (p_routing_id IN NUMBER,
3036 p_formula_id IN NUMBER,
3037 x_theoretical_loss OUT NOCOPY NUMBER,
3038 x_return_code OUT NOCOPY VARCHAR2,
3039 x_error_msg OUT NOCOPY VARCHAR2) IS
3040
3041 l_return_status VARCHAR2(2);
3042 l_msg_cnt NUMBER;
3043 l_msg_dat VARCHAR2(30);
3044 l_message VARCHAR2(1000);
3045 l_dummy NUMBER;
3046 l_process_loss_rec GMD_COMMON_VAL.process_loss_rec;
3047 l_recipe_theo_loss GMD_PROCESS_LOSS.process_loss%TYPE;
3048 PR_LOSS_EXCEPTION EXCEPTION;
3049
3050 BEGIN
3051
3052 x_error_msg := '';
3053 x_return_code := 'S';
3054 x_theoretical_loss := -1;
3055
3056 l_process_loss_rec.formula_id := p_formula_id;
3057 l_process_loss_rec.routing_id := p_routing_Id;
3058
3059 GMD_COMMON_VAL.Calculate_Process_Loss(process_loss => l_process_loss_rec,
3060 Entity_type => 'RECIPE',
3061 x_recipe_theo_loss => l_recipe_theo_loss,
3062 x_process_loss => x_theoretical_loss,
3063 x_return_status => l_return_status,
3064 x_msg_count => l_msg_cnt,
3065 x_msg_data => l_msg_dat);
3066
3067 IF l_return_status <> 'S' THEN
3068 RAISE PR_LOSS_EXCEPTION;
3069 END IF;
3070
3071 EXCEPTION
3072 WHEN PR_LOSS_EXCEPTION THEN
3073 FND_MSG_PUB.GET( p_msg_index => 1,
3074 p_data => l_message,
3075 p_encoded => 'F',
3076 p_msg_index_out => l_dummy);
3077 x_return_code := 'F';
3078 x_error_msg := l_message;
3079
3080 WHEN OTHERS THEN
3081 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
3082 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
3083 x_return_code := 'U';
3084 x_error_msg := FND_MESSAGE.GET;
3085
3086 END Get_Theoretical_Process_Loss;
3087
3088 /* Api start of comments
3089 +============================================================================
3090 | PROCEDURE NAME
3091 | Check_Step_Quantity_Calculatable
3092 |
3093 | DESCRIPTION
3094 | Check whether step quantities can be calculated.
3095 |
3096 | INPUT PARAMETERS
3097 | p_recipe_id NUMBER
3098 |
3099 | OUTPUT PARAMETERS
3100 | x_return_code VARCHAR2(1)
3101 | x_error_msg VARCHAR2(100)
3102 |
3103 | HISTORY
3104 | 03-DEC-2002 Eddie Oumerretane Created.
3105 |
3106 +=============================================================================
3107 Api end of comments
3108 */
3109 PROCEDURE Check_Step_Qty_Calculatable (p_recipe_id IN NUMBER,
3110 x_return_code OUT NOCOPY VARCHAR2,
3111 x_error_msg OUT NOCOPY VARCHAR2) IS
3112
3113 l_return_status VARCHAR2(2);
3114 l_msg_cnt NUMBER;
3115 l_msg_dat VARCHAR2(2000);
3116 l_dummy NUMBER;
3117 l_check GMD_AUTO_STEP_CALC.calculatable_rec_type;
3118 l_ignore_mass_cv BOOLEAN;
3119 l_ignore_vol_cv BOOLEAN;
3120 CAL_QTY_EXCEPTION EXCEPTION;
3121
3122 BEGIN
3123
3124 x_error_msg := '';
3125 x_return_code := 'S';
3126
3127 l_check.parent_id := p_recipe_id;
3128
3129 GMD_AUTO_STEP_CALC.check_step_qty_calculatable (
3130 p_check => l_check,
3131 p_msg_count => l_msg_cnt,
3132 p_msg_stack => l_msg_dat,
3133 p_return_status => l_return_status,
3134 p_ignore_mass_conv => l_ignore_mass_cv,
3135 p_ignore_vol_conv => l_ignore_vol_cv,
3136 P_organization_id => Null);
3137
3138
3139 IF l_return_status <> 'S' THEN
3140 RAISE CAL_QTY_EXCEPTION;
3141 END IF;
3142
3143 EXCEPTION
3144 WHEN CAL_QTY_EXCEPTION THEN
3145 FND_MSG_PUB.GET( p_msg_index => 1,
3146 p_data => x_error_msg,
3147 p_encoded => 'F',
3148 p_msg_index_out => l_dummy);
3149 x_return_code := 'F';
3150
3151 WHEN OTHERS THEN
3152 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
3153 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
3154 x_return_code := 'U';
3155 x_error_msg := FND_MESSAGE.GET;
3156
3157 END Check_Step_Qty_Calculatable;
3158
3159
3160 /* Api start of comments
3161 +============================================================================
3162 | PROCEDURE NAME
3163 | Copy_Recipe
3164 |
3165 | DESCRIPTION
3166 | Copy the given recipe, formula and routing
3167 |
3168 | INPUT PARAMETERS
3169 | p_copy_from_recipe_id NUMBER
3170 | p_recipe_no VARCHAR2
3171 | p_recipe_vers NUMBER
3172 | p_recipe_desc VARCHAR2
3173 | p_copy_from_formula_id NUMBER
3174 | p_formula_no VARCHAR2
3175 | p_formula_vers NUMBER
3176 | p_formula_desc VARCHAR2
3177 | p_copy_from_routing_id NUMBER
3178 | p_routing_no VARCHAR2
3179 | p_routing_vers NUMBER
3180 | p_routing_desc VARCHAR2
3181 | p_commit VARCHAR2
3182 |
3183 | OUTPUT PARAMETERS
3184 | x_recipe_id NUMBER
3185 | x_formula_id NUMBER
3186 | x_routing_id NUMBER
3187 | x_return_code VARCHAR2(1)
3188 | x_error_msg VARCHAR2(100)
3189 |
3190 | HISTORY
3191 | 10-DEC-2002 Eddie Oumerretane Created.
3192 |
3193 +=============================================================================
3194 Api end of comments
3195 */
3196 PROCEDURE Copy_Recipe ( p_copy_from_recipe_id IN NUMBER,
3197 p_recipe_no IN VARCHAR2,
3198 p_recipe_vers IN NUMBER,
3199 p_recipe_desc IN VARCHAR2,
3200 p_copy_from_formula_id IN NUMBER,
3201 p_formula_no IN VARCHAR2,
3202 p_formula_vers IN NUMBER,
3203 p_formula_desc IN VARCHAR2,
3204 p_copy_from_routing_id IN NUMBER,
3205 p_routing_no IN VARCHAR2,
3206 p_routing_vers IN NUMBER,
3207 p_routing_desc IN VARCHAR2,
3208 p_commit IN VARCHAR2,
3209 x_recipe_id OUT NOCOPY NUMBER,
3210 x_formula_id OUT NOCOPY NUMBER,
3211 x_routing_id OUT NOCOPY NUMBER,
3212 x_return_code OUT NOCOPY VARCHAR2,
3213 x_error_msg OUT NOCOPY VARCHAR2) IS
3214
3215 l_user_id NUMBER := FND_PROFILE.VALUE('USER_ID');
3216 l_login_id NUMBER := FND_PROFILE.VALUE('LOGIN_ID');
3217
3218 CURSOR Cur_get_rcp_hdr IS
3219 SELECT *
3220 FROM gmd_recipes
3221 WHERE recipe_id = p_copy_from_recipe_id;
3222
3223 CURSOR Cur_get_pp IS
3224 SELECT *
3225 FROM gmd_recipe_process_parameters
3226 WHERE recipe_id = p_copy_from_recipe_id;
3227
3228 CURSOR Cur_get_vr IS
3229 SELECT *
3230 FROM gmd_recipe_validity_rules
3231 WHERE recipe_id = p_copy_from_recipe_id;
3232
3233 CURSOR Cur_get_actv IS
3234 SELECT *
3235 FROM gmd_recipe_orgn_activities
3236 WHERE recipe_id = p_copy_from_recipe_id;
3237
3238 CURSOR Cur_get_rsrc IS
3239 SELECT *
3240 FROM gmd_recipe_orgn_resources
3241 WHERE recipe_id = p_copy_from_recipe_id;
3242
3243 CURSOR Cur_get_rcp_stp IS
3244 SELECT *
3245 FROM gmd_recipe_routing_steps
3246 WHERE recipe_id = p_copy_from_recipe_id;
3247
3248 CURSOR Cur_get_rcp_cust IS
3249 SELECT *
3250 FROM gmd_recipe_customers
3251 WHERE recipe_id = p_copy_from_recipe_id;
3252
3253 CURSOR Cur_get_rcp_loss IS
3254 SELECT *
3255 FROM gmd_recipe_process_loss
3256 WHERE recipe_id = p_copy_from_recipe_id;
3257
3258 CURSOR Cur_get_stp_mtl IS
3259 SELECT *
3260 FROM gmd_recipe_step_materials
3261 WHERE recipe_id = p_copy_from_recipe_id;
3262
3263 CURSOR Cur_get_frm_hdr IS
3264 SELECT *
3265 FROM fm_form_mst
3266 WHERE formula_id = p_copy_from_formula_id;
3267
3268 CURSOR Cur_get_frm_dtl IS
3269 SELECT *
3270 FROM fm_matl_dtl
3271 WHERE formula_id = p_copy_from_formula_id;
3272
3273 CURSOR Cur_get_rtg_hdr IS
3274 SELECT *
3275 FROM gmd_routings
3276 WHERE routing_id = p_copy_from_routing_id;
3277
3278 CURSOR Cur_get_rtg_dtl IS
3279 SELECT *
3280 FROM fm_rout_dtl
3281 WHERE routing_id = p_copy_from_routing_id;
3282
3283 CURSOR Cur_get_step_dep IS
3284 SELECT *
3285 FROM fm_rout_dep
3286 WHERE routing_id = p_copy_from_routing_id;
3287
3288 CURSOR Get_Text (p_text_code NUMBER) IS
3289 SELECT *
3290 FROM fm_text_tbl
3291 WHERE text_code = p_text_code AND
3292 line_no <> -1;
3293
3294 CURSOR Get_text_code IS
3295 SELECT gem5_text_code_s.NEXTVAL
3296 FROM sys.dual;
3297
3298 CURSOR Cur_recipe_id IS
3299 SELECT gmd_recipe_id_s.NEXTVAL
3300 FROM FND_DUAL;
3301
3302 CURSOR Cur_loss_id IS
3303 SELECT gmd_recipe_process_loss_id_s.NEXTVAL
3304 FROM FND_DUAL;
3305
3306 CURSOR Cur_vr_id IS
3307 SELECT gmd_recipe_validity_id_s.NEXTVAL
3308 FROM FND_DUAL;
3309
3310 TYPE rcp_pp IS TABLE OF Cur_get_pp%ROWTYPE INDEX BY BINARY_INTEGER;
3311 TYPE rcp_vr IS TABLE OF Cur_get_vr%ROWTYPE INDEX BY BINARY_INTEGER;
3312 TYPE rcp_rsrc IS TABLE OF Cur_get_rsrc%ROWTYPE INDEX BY BINARY_INTEGER;
3313 TYPE rcp_actv IS TABLE OF Cur_get_actv%ROWTYPE INDEX BY BINARY_INTEGER;
3314 TYPE stp_mtl_tab IS TABLE OF Cur_get_stp_mtl%ROWTYPE INDEX BY BINARY_INTEGER;
3315 TYPE rcp_loss_tab IS TABLE OF Cur_get_rcp_loss%ROWTYPE INDEX BY BINARY_INTEGER;
3316 TYPE rcp_cust_tab IS TABLE OF Cur_get_rcp_cust%ROWTYPE INDEX BY BINARY_INTEGER;
3317 TYPE rcp_stp_tab IS TABLE OF Cur_get_rcp_stp%ROWTYPE INDEX BY BINARY_INTEGER;
3318 TYPE frm_dtl_tab IS TABLE OF Cur_get_frm_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
3319 TYPE rtg_dtl_tab IS TABLE OF Cur_get_rtg_dtl%ROWTYPE INDEX BY BINARY_INTEGER;
3320 TYPE step_dep_tab IS TABLE OF Cur_get_step_dep%ROWTYPE INDEX BY BINARY_INTEGER;
3321 TYPE text_tab IS TABLE OF Get_Text%ROWTYPE INDEX BY BINARY_INTEGER;
3322
3323 l_rcp_hdr_rec Cur_get_rcp_hdr%ROWTYPE;
3324 l_rcp_pp_tbl rcp_pp;
3325 l_rcp_actv_tbl rcp_actv;
3326 l_rcp_vr_tbl rcp_vr;
3327 l_rcp_rsrc_tbl rcp_rsrc;
3328 l_stp_mtl_tbl stp_mtl_tab;
3329 l_rcp_stp_tbl rcp_stp_tab;
3330 l_rcp_loss_tbl rcp_loss_tab;
3331 l_rcp_cust_tbl rcp_cust_tab;
3332 l_frm_hdr_rec Cur_get_frm_hdr%ROWTYPE;
3333 l_frm_hdr_rec2 Cur_get_frm_hdr%ROWTYPE;
3334 l_frm_dtl_tbl frm_dtl_tab;
3335 l_rtg_hdr_rec Cur_get_rtg_hdr%ROWTYPE;
3336 l_rtg_hdr_rec2 Cur_get_rtg_hdr%ROWTYPE;
3337 l_rtg_dtl_tbl rtg_dtl_tab;
3338 l_step_dep_tbl step_dep_tab;
3339 l_rcp_actv_text_tbl text_tab;
3340 l_rcp_vr_text_tbl text_tab;
3341 l_rcp_rsrc_text_tbl text_tab;
3342 l_rcp_stp_text_tbl text_tab;
3343 l_stp_mtl_text_tbl text_tab;
3344 l_rcp_loss_text_tbl text_tab;
3345 l_rcp_cust_text_tbl text_tab;
3346 l_rcp_hdr_text_tbl text_tab;
3347 l_frm_dtl_text_tbl text_tab;
3348 l_frm_hdr_text_tbl text_tab;
3349 l_rtg_dtl_text_tbl text_tab;
3350 l_rtg_hdr_text_tbl text_tab;
3351 l_row NUMBER := 0;
3352 l_txt_ind NUMBER;
3353 l_rowid VARCHAR2(32);
3354 l_text_code NUMBER(10);
3355 l_user_org VARCHAR2(4);
3356 l_error_msg VARCHAR2(2000);
3357 l_return_code VARCHAR2(2);
3358 l_message_count NUMBER;
3359 l_message_list VARCHAR2(2000);
3360 l_table_lnk VARCHAR2(80);
3361 l_routingstep_id NUMBER(15);
3362 l_formulaline_id NUMBER(15);
3363 l_dummy NUMBER;
3364 l_formula_id NUMBER;
3365 l_routing_id NUMBER;
3366 l_loss_id NUMBER;
3367 l_vr_id NUMBER;
3368 l_copy_recipe BOOLEAN;
3369 l_copy_formula BOOLEAN;
3370 l_copy_routing BOOLEAN;
3371 l_routing_update_allowed BOOLEAN;
3372 l_new_routing BOOLEAN;
3373 l_rtg_upd_tbl GMD_ROUTINGS_PUB.update_tbl_type;
3374 l_recipe_tbl GMD_RECIPE_HEADER.recipe_hdr;
3375 l_recipe_update_flex GMD_RECIPE_HEADER.flex;
3376 COPY_RECIPE_EXCEPTION EXCEPTION;
3377 COPY_ROUTING_EXCEPTION EXCEPTION;
3378 COPY_FORMULA_EXCEPTION EXCEPTION;
3379 COPY_FORM_DTL_EXCEPTION EXCEPTION;
3380 GET_SURROGATE_EXCEPTION EXCEPTION;
3381 COPY_HEADER_TEXT_EXCEPTION EXCEPTION;
3382 RECIPE_NOT_FOUND EXCEPTION;
3383 FORMULA_NOT_FOUND EXCEPTION;
3384 ROUTING_NOT_FOUND EXCEPTION;
3385 PROCEDURE_EXCEPTION EXCEPTION;
3386
3387 BEGIN
3388
3389 x_error_msg := '';
3390 x_return_code := FND_API.G_RET_STS_SUCCESS;
3391
3392 x_recipe_id := -1;
3393 x_routing_id := -1;
3394 x_formula_id := -1;
3395
3396 ---
3397 --- Load recipe header
3398 ---
3399 OPEN Cur_get_rcp_hdr;
3400 FETCH Cur_get_rcp_hdr INTO l_rcp_hdr_rec;
3401 CLOSE Cur_get_rcp_hdr;
3402
3403 IF l_rcp_hdr_rec.recipe_id IS NULL THEN
3404 RAISE RECIPE_NOT_FOUND;
3405 ELSE
3406 IF l_rcp_hdr_rec.recipe_no <> p_recipe_no OR
3407 l_rcp_hdr_rec.recipe_version <> p_recipe_vers THEN
3408 l_copy_recipe := TRUE;
3409 ELSE
3410 l_copy_recipe := FALSE;
3411 END IF;
3412 END IF;
3413
3414 ---
3415 --- Load recipe header text
3416 ---
3417 IF (l_rcp_hdr_rec.text_code IS NOT NULL) THEN
3418 l_txt_ind := 0;
3419
3420 FOR get_txt_rec IN Get_Text (l_rcp_hdr_rec.text_code) LOOP
3421 l_txt_ind := l_txt_ind + 1;
3422 l_rcp_hdr_text_tbl(l_txt_ind) := get_txt_rec;
3423 END LOOP;
3424 END IF;
3425
3426 l_txt_ind := 0;
3427 l_row := 0;
3428
3429 ---
3430 --- Load recipe steps
3431 ---
3432 FOR get_rec IN Cur_get_rcp_stp LOOP
3433
3434 l_row := l_row + 1;
3435 l_rcp_stp_tbl(l_row) := get_rec;
3436
3437 IF (get_rec.text_code IS NOT NULL) THEN
3438
3439 --- Load text for this step line
3440 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3441 l_txt_ind := l_txt_ind + 1;
3442 l_rcp_stp_text_tbl(l_txt_ind) := get_txt_rec;
3443 END LOOP;
3444
3445 END IF;
3446
3447 END LOOP;
3448
3449 l_txt_ind := 0;
3450 l_row := 0;
3451
3452 ---
3453 --- Load recipe customers
3454 ---
3455 FOR get_rec IN Cur_get_rcp_cust LOOP
3456
3457 l_row := l_row + 1;
3458 l_rcp_cust_tbl(l_row) := get_rec;
3459
3460 IF (get_rec.text_code IS NOT NULL) THEN
3461
3462 --- Load text for this customer line
3463 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3464 l_txt_ind := l_txt_ind + 1;
3465 l_rcp_cust_text_tbl(l_txt_ind) := get_txt_rec;
3466 END LOOP;
3467
3468 END IF;
3469
3470 END LOOP;
3471
3472 l_txt_ind := 0;
3473 l_row := 0;
3474
3475 ---
3476 --- Load recipe process losses
3477 ---
3478 FOR get_rec IN Cur_get_rcp_loss LOOP
3479
3480 l_row := l_row + 1;
3481 l_rcp_loss_tbl(l_row) := get_rec;
3482
3483 IF (get_rec.text_code IS NOT NULL) THEN
3484
3485 --- Load text for this process loss line
3486 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3487 l_txt_ind := l_txt_ind + 1;
3488 l_rcp_loss_text_tbl(l_txt_ind) := get_txt_rec;
3489 END LOOP;
3490
3491 END IF;
3492
3493 END LOOP;
3494
3495 l_txt_ind := 0;
3496 l_row := 0;
3497
3498 ---
3499 --- Load step/material associations
3500 ---
3501 FOR get_rec IN Cur_get_stp_mtl LOOP
3502
3503 l_row := l_row + 1;
3504 l_stp_mtl_tbl(l_row) := get_rec;
3505
3506 IF (get_rec.text_code IS NOT NULL) THEN
3507
3508 --- Load text for this step/item line
3509 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3510 l_txt_ind := l_txt_ind + 1;
3511 l_stp_mtl_text_tbl(l_txt_ind) := get_txt_rec;
3512 END LOOP;
3513
3514 END IF;
3515
3516 END LOOP;
3517
3518 l_txt_ind := 0;
3519 l_row := 0;
3520
3521 ---
3522 --- Load validity rules
3523 ---
3524 FOR get_rec IN Cur_get_vr LOOP
3525
3526 l_row := l_row + 1;
3527 l_rcp_vr_tbl(l_row) := get_rec;
3528
3529 IF (get_rec.text_code IS NOT NULL) THEN
3530
3531 --- Load text for this validity rule
3532 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3533 l_txt_ind := l_txt_ind + 1;
3534 l_rcp_vr_text_tbl(l_txt_ind) := get_txt_rec;
3535 END LOOP;
3536
3537 END IF;
3538
3539 END LOOP;
3540
3541 l_txt_ind := 0;
3542 l_row := 0;
3543
3544 ---
3545 --- Load organization specific resource information
3546 ---
3547 FOR get_rec IN Cur_get_rsrc LOOP
3548
3549 l_row := l_row + 1;
3550 l_rcp_rsrc_tbl(l_row) := get_rec;
3551
3552 IF (get_rec.text_code IS NOT NULL) THEN
3553
3554 --- Load text for this resource
3555 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3556 l_txt_ind := l_txt_ind + 1;
3557 l_rcp_rsrc_text_tbl(l_txt_ind) := get_txt_rec;
3558 END LOOP;
3559
3560 END IF;
3561
3562 END LOOP;
3563
3564 l_txt_ind := 0;
3565 l_row := 0;
3566
3567 ---
3568 --- Load organization specific activity information
3569 ---
3570 FOR get_rec IN Cur_get_actv LOOP
3571
3572 l_row := l_row + 1;
3573 l_rcp_actv_tbl(l_row) := get_rec;
3574
3575 IF (get_rec.text_code IS NOT NULL) THEN
3576
3577 --- Load text for this activity
3578 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3579 l_txt_ind := l_txt_ind + 1;
3580 l_rcp_actv_text_tbl(l_txt_ind) := get_txt_rec;
3581 END LOOP;
3582
3583 END IF;
3584
3585 END LOOP;
3586
3587 ---
3588 --- Load process parameters
3589 ---
3590 FOR get_rec IN Cur_get_pp LOOP
3591
3592 l_row := l_row + 1;
3593 l_rcp_pp_tbl(l_row) := get_rec;
3594
3595 END LOOP;
3596
3597 ---
3598 --- Load formula header
3599 ---
3600 OPEN Cur_get_frm_hdr;
3601 FETCH Cur_get_frm_hdr INTO l_frm_hdr_rec;
3602 CLOSE Cur_get_frm_hdr;
3603
3604 IF l_frm_hdr_rec.formula_id IS NULL THEN
3605 RAISE FORMULA_NOT_FOUND;
3606 ELSE
3607 IF l_frm_hdr_rec.formula_no <> p_formula_no OR
3608 l_frm_hdr_rec.formula_vers <> p_formula_vers THEN
3609 l_copy_formula := TRUE;
3610 ELSE
3611 l_copy_formula := FALSE;
3612 END IF;
3613 END IF;
3614
3615 ---
3616 --- Load formula header text
3617 ---
3618 IF (l_frm_hdr_rec.text_code IS NOT NULL) THEN
3619 l_txt_ind := 0;
3620
3621 FOR get_txt_rec IN Get_Text (l_frm_hdr_rec.text_code) LOOP
3622 l_txt_ind := l_txt_ind + 1;
3623 l_frm_hdr_text_tbl(l_txt_ind) := get_txt_rec;
3624 END LOOP;
3625 END IF;
3626
3627 l_txt_ind := 0;
3628 l_row := 0;
3629
3630 ---
3631 --- Load fromula details
3632 ---
3633 FOR get_rec IN Cur_get_frm_dtl LOOP
3634
3635 l_row := l_row + 1;
3636 l_frm_dtl_tbl(l_row) := get_rec;
3637
3638 IF (get_rec.text_code IS NOT NULL) THEN
3639
3640 --- Load text for this item line
3641 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3642 l_txt_ind := l_txt_ind + 1;
3643 l_frm_dtl_text_tbl(l_txt_ind) := get_txt_rec;
3644 END LOOP;
3645
3646 END IF;
3647
3648 END LOOP;
3649
3650 --- Routing is not a mandatory component. So we need to make sure the
3651 --- current recipe is using a routing.
3652 IF l_rcp_hdr_rec.routing_id IS NOT NULL THEN
3653 ---
3654 --- Load routing header
3655 ---
3656 OPEN Cur_get_rtg_hdr;
3657 FETCH Cur_get_rtg_hdr INTO l_rtg_hdr_rec;
3658 CLOSE Cur_get_rtg_hdr;
3659
3660 IF l_rtg_hdr_rec.routing_id IS NULL THEN
3661 RAISE ROUTING_NOT_FOUND;
3662 ELSE
3663 IF l_rtg_hdr_rec.routing_no <> p_routing_no OR
3664 l_rtg_hdr_rec.routing_vers <> p_routing_vers THEN
3665 l_copy_routing := TRUE;
3666 ELSE
3667 l_copy_routing := FALSE;
3668 END IF;
3669 END IF;
3670
3671 ---
3672 --- Load routing header text
3673 ---
3674 IF (l_rtg_hdr_rec.text_code IS NOT NULL) THEN
3675 l_txt_ind := 0;
3676
3677 FOR get_txt_rec IN Get_Text (l_rtg_hdr_rec.text_code) LOOP
3678 l_txt_ind := l_txt_ind + 1;
3679 l_rtg_hdr_text_tbl(l_txt_ind) := get_txt_rec;
3680 END LOOP;
3681 END IF;
3682
3683 l_txt_ind := 0;
3684 l_row := 0;
3685
3686 ---
3687 --- Load routing details
3688 ---
3689 FOR get_rec IN Cur_get_rtg_dtl LOOP
3690
3691 l_row := l_row + 1;
3692 l_rtg_dtl_tbl(l_row) := get_rec;
3693
3694 IF (get_rec.text_code IS NOT NULL) THEN
3695
3696 --- Load text for this step
3697 FOR get_txt_rec IN Get_Text (get_rec.text_code) LOOP
3698 l_txt_ind := l_txt_ind + 1;
3699 l_rtg_dtl_text_tbl(l_txt_ind) := get_txt_rec;
3700 END LOOP;
3701
3702 END IF;
3703
3704 END LOOP;
3705 l_row := 0;
3706
3707 ---
3708 --- Load routing step dependencies
3709 ---
3710 FOR get_dep IN Cur_get_step_dep LOOP
3711 l_row := l_row + 1;
3712 l_step_dep_tbl(l_row) := get_dep;
3713 END LOOP;
3714
3715 END IF;
3716
3717 ---
3718 --- Do not commit pending changes to the original recipe,formula
3719 --- and routing
3720 ---
3721 ROLLBACK;
3722
3723
3724 SAVEPOINT Copy_Recipe;
3725
3726 ---
3727 --- Process formula
3728 ---
3729 IF l_rcp_hdr_rec.formula_id IS NOT NULL THEN
3730
3731 IF l_copy_formula THEN
3732 x_formula_id := GMDSURG.get_surrogate('formula_id');
3733 IF (x_formula_id < 1) THEN
3734 RAISE GET_SURROGATE_EXCEPTION;
3735 END IF;
3736 ELSE
3737 x_formula_id := l_rcp_hdr_rec.formula_id;
3738 END IF;
3739
3740 IF (l_frm_hdr_text_tbl.COUNT > 0) THEN
3741
3742 IF l_copy_formula THEN
3743 OPEN Get_Text_Code;
3744 FETCH Get_Text_Code INTO l_text_code;
3745 CLOSE Get_Text_Code;
3746 ELSE
3747 l_text_code := l_frm_hdr_rec.text_code;
3748 DELETE fm_text_tbl WHERE text_code = l_text_code;
3749 END IF;
3750
3751 l_txt_ind := 0;
3752
3753 FOR i IN 1..l_frm_hdr_text_tbl.COUNT LOOP
3754
3755 l_txt_ind := l_txt_ind + 1;
3756 l_table_lnk := 'fm_form_mst' || '|' || x_formula_id;
3757
3758 ---
3759 --- Create formula header text
3760 ---
3761 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
3762 (p_text_code => l_text_code,
3763 p_lang_code => l_frm_hdr_text_tbl(l_txt_ind).lang_code,
3764 p_text => l_frm_hdr_text_tbl(l_txt_ind).text,
3765 p_line_no => l_frm_hdr_text_tbl(l_txt_ind).line_no,
3766 p_paragraph_code => l_frm_hdr_text_tbl(l_txt_ind).paragraph_code,
3767 p_sub_paracode => l_frm_hdr_text_tbl(l_txt_ind).sub_paracode,
3768 p_table_lnk => l_table_lnk,
3769 p_user_id => l_user_id,
3770 x_row_id => l_rowid,
3771 x_return_code => l_return_code,
3772 x_error_msg => l_error_msg);
3773
3774 IF (l_return_code <> 'S') THEN
3775 RAISE COPY_HEADER_TEXT_EXCEPTION;
3776 END IF;
3777
3778 END LOOP;
3779
3780 END IF;
3781
3782 ---
3783 --- Create/update formula header record
3784 ---
3785 l_frm_hdr_rec.formula_id := x_formula_id;
3786 l_frm_hdr_rec.formula_no := p_formula_no;
3787 l_frm_hdr_rec.formula_vers := p_formula_vers;
3788 l_frm_hdr_rec.formula_desc1 := p_formula_desc;
3789 l_frm_hdr_rec.text_code := l_text_code;
3790 l_frm_hdr_rec.owner_id := l_user_id;
3791 l_frm_hdr_rec.last_update_login := l_login_id;
3792
3793 OPEN Cur_get_frm_hdr;
3794 FETCH Cur_get_frm_hdr INTO l_frm_hdr_rec2;
3795
3796 IF l_copy_formula OR Cur_get_frm_hdr%NOTFOUND THEN
3797
3798 l_frm_hdr_rec.delete_mark := 0;
3799
3800 /*Bug 3953359 - Thomas Daniel */
3801 /*Added initializing of the formula status as New */
3802 l_frm_hdr_rec.formula_status := 100;
3803
3804 GMD_FORMULA_HEADER_PVT.Insert_FormulaHeader
3805 ( p_api_version => 1.0
3806 ,p_init_msg_list => FND_API.G_TRUE
3807 ,p_commit => FND_API.G_FALSE
3808 ,x_return_status => l_return_code
3809 ,x_msg_count => l_message_count
3810 ,x_msg_data => l_message_list
3811 ,p_formula_header_rec => l_frm_hdr_rec
3812 );
3813
3814 ELSE
3815
3816 GMD_FORMULA_HEADER_PVT.Update_FormulaHeader
3817 ( p_api_version => 1.0
3818 ,p_init_msg_list => FND_API.G_TRUE
3819 ,p_commit => FND_API.G_FALSE
3820 ,x_return_status => l_return_code
3821 ,x_msg_count => l_message_count
3822 ,x_msg_data => l_message_list
3823 ,p_formula_header_rec => l_frm_hdr_rec
3824 );
3825
3826 END IF;
3827
3828 CLOSE Cur_get_frm_hdr;
3829
3830 IF l_return_code <> 'S' THEN
3831 RAISE COPY_FORMULA_EXCEPTION;
3832 END IF;
3833
3834 ---
3835 --- Insert formula detail records
3836 ---
3837 l_txt_ind := 1;
3838
3839 IF NOT l_copy_formula THEN
3840 DELETE fm_matl_dtl WHERE formula_id = x_formula_id;
3841 END IF;
3842
3843 FOR i IN 1..l_frm_dtl_tbl.count LOOP
3844 l_text_code := NULL;
3845 IF l_copy_formula THEN
3846 l_formulaline_id := GMDSURG.get_surrogate('formulaline_id');
3847 ---
3848 --- Update the step/item asociation table with the new line id
3849 ---
3850 FOR j IN 1..l_stp_mtl_tbl.COUNT LOOP
3851 IF l_stp_mtl_tbl(j).formulaline_id =
3852 l_frm_dtl_tbl(i).formulaline_id THEN
3853 --- l_frm_dtl_tbl(j).formulaline_id := l_formulaline_id;
3854 l_stp_mtl_tbl(j).formulaline_id := l_formulaline_id;
3855 EXIT;
3856 END IF;
3857 END LOOP;
3858 ELSE
3859 l_formulaline_id := l_frm_dtl_tbl(i).formulaline_id;
3860 END IF;
3861
3862 IF (l_formulaline_id < 1) THEN
3863 RAISE GET_SURROGATE_EXCEPTION;
3864 END IF;
3865
3866 IF (l_frm_dtl_tbl(i).text_code > 0) THEN
3867
3868 IF l_copy_formula THEN
3869 OPEN Get_Text_Code;
3870 FETCH Get_Text_Code INTO l_text_code;
3871 CLOSE Get_Text_Code;
3872 ELSE
3873 l_text_code := l_frm_dtl_tbl(i).text_code;
3874 DELETE fm_text_tbl WHERE text_code = l_text_code;
3875 END IF;
3876
3877 l_table_lnk := 'fm_matl_dtl' || '|' || x_formula_id || '|' ||
3878 l_formulaline_id;
3879
3880 WHILE (l_txt_ind <= l_frm_dtl_text_tbl.COUNT AND
3881 l_frm_dtl_text_tbl(l_txt_ind).text_code =
3882 l_frm_dtl_tbl(i).text_code) LOOP
3883
3884 ---
3885 --- Create formula item text
3886 ---
3887 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
3888 (p_text_code => l_text_code,
3889 p_lang_code => l_frm_dtl_text_tbl(l_txt_ind).lang_code,
3890 p_text => l_frm_dtl_text_tbl(l_txt_ind).text,
3891 p_line_no => l_frm_dtl_text_tbl(l_txt_ind).line_no,
3892 p_paragraph_code => l_frm_dtl_text_tbl(l_txt_ind).paragraph_code,
3893 p_sub_paracode => l_frm_dtl_text_tbl(l_txt_ind).sub_paracode,
3894 p_table_lnk => l_table_lnk,
3895 p_user_id => l_user_id,
3896 x_row_id => l_rowid,
3897 x_return_code => l_return_code,
3898 x_error_msg => l_error_msg);
3899
3900 IF (l_return_code <> 'S') THEN
3901 RAISE COPY_HEADER_TEXT_EXCEPTION;
3902 END IF;
3903
3904 l_txt_ind := l_txt_ind + 1;
3905
3906 END LOOP;
3907
3908 END IF;
3909
3910 ---
3911 --- Create formula item lines
3912 ---
3913 l_frm_dtl_tbl(i).formula_id := x_formula_id;
3914 l_frm_dtl_tbl(i).formulaline_id := l_formulaline_id;
3915 l_frm_dtl_tbl(i).text_code := l_text_code;
3916 GMD_FORMULA_DETAIL_PVT.Insert_FormulaDetail
3917 ( p_api_version => 1.0
3918 ,p_init_msg_list => FND_API.G_TRUE
3919 ,p_commit => FND_API.G_FALSE
3920 ,x_return_status => l_return_code
3921 ,x_msg_count => l_message_count
3922 ,x_msg_data => l_message_list
3923 ,p_formula_detail_rec => l_frm_dtl_tbl(i)
3924 );
3925
3926 IF l_return_code <> 'S' THEN
3927 RAISE COPY_FORM_DTL_EXCEPTION;
3928 END IF;
3929
3930 END LOOP;
3931 END IF;
3932
3933
3934 ---
3935 --- Process routing
3936 ---
3937 IF l_rcp_hdr_rec.routing_id IS NOT NULL THEN
3938
3939 OPEN Cur_get_rtg_hdr;
3940 FETCH Cur_get_rtg_hdr INTO l_rtg_hdr_rec2;
3941
3942 IF Cur_get_rtg_hdr%NOTFOUND THEN
3943 l_new_routing := TRUE;
3944 l_routing_update_allowed := TRUE;
3945 ELSE
3946 l_new_routing := FALSE;
3947 l_routing_update_allowed := GMD_COMMON_VAL.UPDATE_ALLOWED(
3948 Entity => 'ROUTING',
3949 Entity_id => l_rcp_hdr_rec.routing_id);
3950 END IF;
3951
3952 CLOSE Cur_get_rtg_hdr;
3953
3954 x_routing_id := l_rcp_hdr_rec.routing_id;
3955
3956 IF l_copy_routing OR l_routing_update_allowed THEN
3957
3958 IF l_copy_routing THEN
3959 x_routing_id := GMDSURG.get_surrogate('routing_id');
3960 IF (x_routing_id < 1) THEN
3961 RAISE GET_SURROGATE_EXCEPTION;
3962 END IF;
3963 END IF;
3964
3965 IF (l_rtg_hdr_text_tbl.COUNT > 0) THEN
3966
3967 IF l_copy_routing THEN
3968 OPEN Get_Text_Code;
3969 FETCH Get_Text_Code INTO l_text_code;
3970 CLOSE Get_Text_Code;
3971 ELSE
3972 l_text_code := l_rtg_hdr_rec.text_code;
3973 DELETE fm_text_tbl WHERE text_code = l_text_code;
3974 END IF;
3975
3976 l_txt_ind := 0;
3977
3978 FOR i IN 1..l_rtg_hdr_text_tbl.COUNT LOOP
3979
3980 l_txt_ind := l_txt_ind + 1;
3981 l_table_lnk := 'gmd_routings' || '|' || x_routing_id;
3982
3983 ---
3984 --- Create routing header text
3985 ---
3986 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
3987 (p_text_code => l_text_code,
3988 p_lang_code => l_rtg_hdr_text_tbl(l_txt_ind).lang_code,
3989 p_text => l_rtg_hdr_text_tbl(l_txt_ind).text,
3990 p_line_no => l_rtg_hdr_text_tbl(l_txt_ind).line_no,
3991 p_paragraph_code => l_rtg_hdr_text_tbl(l_txt_ind).paragraph_code,
3992 p_sub_paracode => l_rtg_hdr_text_tbl(l_txt_ind).sub_paracode,
3993 p_table_lnk => l_table_lnk,
3994 p_user_id => l_user_id,
3995 x_row_id => l_rowid,
3996 x_return_code => l_return_code,
3997 x_error_msg => l_error_msg);
3998
3999 IF (l_return_code <> 'S') THEN
4000 RAISE COPY_HEADER_TEXT_EXCEPTION;
4001 END IF;
4002
4003 END LOOP;
4004
4005 END IF;
4006
4007 ---
4008 --- Create routing header record
4009 ---
4010 l_rtg_hdr_rec.routing_id := x_routing_id;
4011 l_rtg_hdr_rec.routing_no := p_routing_no;
4012 l_rtg_hdr_rec.routing_vers := p_routing_vers;
4013 l_rtg_hdr_rec.routing_desc := p_routing_desc;
4014 -- l_rtg_hdr_rec.owner_organization_id := p_orgn_id;
4015 l_rtg_hdr_rec.text_code := l_text_code;
4016 l_rtg_hdr_rec.owner_id := l_user_id;
4017 l_rtg_hdr_rec.last_update_login := l_login_id;
4018
4019
4020 IF l_copy_routing OR l_new_routing THEN
4021
4022 /*Bug 3953359 - Thomas Daniel */
4023 /*Added initializing of the routing status as New */
4024 l_rtg_hdr_rec.routing_status := 100;
4025
4026 GMD_ROUTINGS_PVT.Insert_Routing ( p_routings => l_rtg_hdr_rec
4027 ,x_message_count => l_message_count
4028 ,x_message_list => l_message_list
4029 ,x_return_status => l_return_code);
4030 IF l_return_code <> 'S' THEN
4031 RAISE COPY_ROUTING_EXCEPTION;
4032 END IF;
4033
4034 ELSE
4035
4036 GMD_ROUTING_DESIGNER_PKG.Update_Routing_Header
4037 ( p_routing_id => x_routing_id,
4038 p_routing_no => p_routing_no,
4039 p_routing_vers => p_routing_vers,
4040 p_routing_desc => p_routing_desc,
4041 p_routing_class => l_rtg_hdr_rec.routing_class,
4042 p_effective_start_date => l_rtg_hdr_rec.effective_start_date,
4043 p_effective_end_date => l_rtg_hdr_rec.effective_end_date,
4044 p_routing_qty => l_rtg_hdr_rec.routing_qty,
4045 p_routing_uom => l_rtg_hdr_rec.routing_uom,
4046 p_process_loss => l_rtg_hdr_rec.process_loss,
4047 p_owner_id => l_rtg_hdr_rec.owner_id,
4048 p_owner_orgn_id => l_rtg_hdr_rec.owner_organization_id,
4049 p_enforce_step_dep => l_rtg_hdr_rec.enforce_step_dependency,
4050 p_last_update_date => l_rtg_hdr_rec.last_update_date,
4051 p_user_id => l_user_id,
4052 p_last_update_date_orig => l_rtg_hdr_rec.last_update_date,
4053 p_update_release_type => 0,
4054 p_contiguous_ind => l_rtg_hdr_rec.contiguous_ind,
4055 x_return_code => l_return_code,
4056 x_error_msg => x_error_msg);
4057
4058 IF l_return_code <> 'S' THEN
4059 RAISE PROCEDURE_EXCEPTION;
4060 END IF;
4061
4062 END IF;
4063
4064 ---
4065 --- Insert routing detail records
4066 ---
4067 l_txt_ind := 1;
4068
4069 IF NOT l_copy_routing THEN
4070 DELETE fm_rout_dep WHERE routing_id = x_routing_id;
4071 DELETE fm_rout_dtl WHERE routing_id = x_routing_id;
4072 END IF;
4073
4074 FOR i IN 1..l_rtg_dtl_tbl.COUNT LOOP
4075 l_text_code := NULL;
4076
4077 IF l_copy_routing THEN
4078 l_routingstep_id := GMDSURG.get_surrogate('routingstep_id');
4079 IF (l_routingstep_id < 1) THEN
4080 RAISE GET_SURROGATE_EXCEPTION;
4081 END IF;
4082 ---
4083 --- Update the step/item asociation table with the new step id
4084 ---
4085 FOR j IN 1..l_stp_mtl_tbl.COUNT LOOP
4086 IF l_stp_mtl_tbl(j).routingstep_id =
4087 l_rtg_dtl_tbl(i).routingstep_id THEN
4088 l_stp_mtl_tbl(j).routingstep_id := l_routingstep_id;
4089 END IF;
4090 END LOOP;
4091
4092 ---
4093 --- Update the recipe step table with the new step id
4094 ---
4095 FOR j IN 1..l_rcp_stp_tbl.COUNT LOOP
4096 IF l_rcp_stp_tbl(j).routingstep_id =
4097 l_rtg_dtl_tbl(i).routingstep_id THEN
4098 l_rcp_stp_tbl(j).routingstep_id := l_routingstep_id;
4099 EXIT;
4100 END IF;
4101 END LOOP;
4102
4103 ---
4104 --- Update the organization resource table with the new step id
4105 ---
4106 FOR j IN 1..l_rcp_rsrc_tbl.COUNT LOOP
4107 IF l_rcp_rsrc_tbl(j).routingstep_id =
4108 l_rtg_dtl_tbl(i).routingstep_id THEN
4109 l_rcp_rsrc_tbl(j).routingstep_id := l_routingstep_id;
4110 END IF;
4111 END LOOP;
4112
4113 ---
4114 --- Update the organization activity table with the new step id
4115 ---
4116 FOR j IN 1..l_rcp_actv_tbl.COUNT LOOP
4117 IF l_rcp_actv_tbl(j).routingstep_id =
4118 l_rtg_dtl_tbl(i).routingstep_id THEN
4119 l_rcp_actv_tbl(j).routingstep_id := l_routingstep_id;
4120 END IF;
4121 END LOOP;
4122
4123 ---
4124 --- Update the process parameter table with the new step id
4125 ---
4126 FOR j IN 1..l_rcp_pp_tbl.COUNT LOOP
4127 IF l_rcp_pp_tbl(j).routingstep_id =
4128 l_rtg_dtl_tbl(i).routingstep_id THEN
4129 l_rcp_pp_tbl(j).routingstep_id := l_routingstep_id;
4130 EXIT;
4131 END IF;
4132 END LOOP;
4133 ELSE
4134 l_routingstep_id := l_rtg_dtl_tbl(i).routingstep_id;
4135 END IF;
4136
4137 IF (l_rtg_dtl_tbl(i).text_code > 0) THEN
4138
4139 IF l_copy_routing THEN
4140 OPEN Get_Text_Code;
4141 FETCH Get_Text_Code INTO l_text_code;
4142 CLOSE Get_Text_Code;
4143 ELSE
4144 l_text_code := l_rtg_dtl_tbl(i).text_code;
4145 DELETE fm_text_tbl WHERE text_code = l_text_code;
4146 END IF;
4147
4148 l_table_lnk := 'fm_rout_dtl' || '|' || x_routing_id || '|' ||
4149 l_routingstep_id;
4150
4151 WHILE (l_txt_ind <= l_rtg_dtl_text_tbl.COUNT AND
4152 l_rtg_dtl_text_tbl(l_txt_ind).text_code =
4153 l_rtg_dtl_tbl(i).text_code) LOOP
4154
4155 ---
4156 --- Create routing step text
4157 ---
4158 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
4159 (p_text_code => l_text_code,
4160 p_lang_code => l_rtg_dtl_text_tbl(l_txt_ind).lang_code,
4161 p_text => l_rtg_dtl_text_tbl(l_txt_ind).text,
4162 p_line_no => l_rtg_dtl_text_tbl(l_txt_ind).line_no,
4163 p_paragraph_code => l_rtg_dtl_text_tbl(l_txt_ind).paragraph_code,
4164 p_sub_paracode => l_rtg_dtl_text_tbl(l_txt_ind).sub_paracode,
4165 p_table_lnk => l_table_lnk,
4166 p_user_id => l_user_id,
4167 x_row_id => l_rowid,
4168 x_return_code => l_return_code,
4169 x_error_msg => l_error_msg);
4170
4171
4172 IF (l_return_code <> 'S') THEN
4173 RAISE COPY_HEADER_TEXT_EXCEPTION;
4174 END IF;
4175
4176 l_txt_ind := l_txt_ind + 1;
4177
4178 END LOOP;
4179
4180 END IF;
4181
4182 ---
4183 --- Create routing step
4184 ---
4185
4186 l_rtg_dtl_tbl(i).routingstep_id := l_routingstep_id;
4187 l_rtg_dtl_tbl(i).text_code := l_text_code;
4188
4189 GMD_ROUTING_STEPS_PVT.Insert_Routing_Steps
4190 ( p_routing_id => x_routing_id
4191 ,p_routing_step_rec => l_rtg_dtl_tbl(i)
4192 ,x_return_status => l_return_code);
4193
4194 END LOOP;
4195 ---
4196 --- Insert routing step dependencies records
4197 ---
4198 FOR i IN 1..l_step_dep_tbl.COUNT LOOP
4199
4200 INSERT INTO fm_rout_dep
4201 (ROUTINGSTEP_NO,
4202 DEP_ROUTINGSTEP_NO,
4203 ROUTING_ID,
4204 DEP_TYPE,
4205 REWORK_CODE,
4206 STANDARD_DELAY,
4207 MINIMUM_DELAY,
4208 MAX_DELAY,
4209 TRANSFER_QTY,
4210 ROUTINGSTEP_NO_UOM,
4211 TEXT_CODE,
4212 LAST_UPDATED_BY,
4213 CREATED_BY,
4214 LAST_UPDATE_DATE,
4215 CREATION_DATE,
4216 LAST_UPDATE_LOGIN,
4217 TRANSFER_PCT)
4218 VALUES (l_step_dep_tbl(i).ROUTINGSTEP_NO,
4219 l_step_dep_tbl(i).DEP_ROUTINGSTEP_NO,
4220 x_routing_id,
4221 l_step_dep_tbl(i).DEP_TYPE,
4222 l_step_dep_tbl(i).REWORK_CODE,
4223 l_step_dep_tbl(i).STANDARD_DELAY,
4224 l_step_dep_tbl(i).MINIMUM_DELAY,
4225 l_step_dep_tbl(i).MAX_DELAY,
4226 l_step_dep_tbl(i).TRANSFER_QTY,
4227 l_step_dep_tbl(i).ROUTINGSTEP_NO_UOM,
4228 l_step_dep_tbl(i).TEXT_CODE,
4229 l_user_id,
4230 l_user_id,
4231 SYSDATE,
4232 SYSDATE,
4233 l_login_id,
4234 l_step_dep_tbl(i).TRANSFER_PCT);
4235 END LOOP;
4236
4237 END IF;
4238
4239 END IF;
4240
4241 ---
4242 --- Process recipe
4243 ---
4244
4245 IF l_rcp_hdr_rec.recipe_id IS NOT NULL THEN
4246
4247 IF l_copy_recipe THEN
4248 OPEN Cur_recipe_id;
4249 FETCH Cur_recipe_id INTO x_recipe_id;
4250 CLOSE Cur_recipe_id;
4251 IF (x_recipe_id < 1) THEN
4252 RAISE GET_SURROGATE_EXCEPTION;
4253 END IF;
4254 ELSE
4255 x_recipe_id := l_rcp_hdr_rec.recipe_id;
4256 END IF;
4257
4258 IF (l_rcp_hdr_text_tbl.COUNT > 0) THEN
4259
4260 IF l_copy_recipe THEN
4261 OPEN Get_Text_Code;
4262 FETCH Get_Text_Code INTO l_text_code;
4263 CLOSE Get_Text_Code;
4264 ELSE
4265 l_text_code := l_rcp_hdr_rec.text_code;
4266 DELETE fm_text_tbl WHERE text_code = l_text_code;
4267 END IF;
4268
4269 l_txt_ind := 0;
4270
4271 FOR i IN 1..l_rcp_hdr_text_tbl.COUNT LOOP
4272
4273 l_txt_ind := l_txt_ind + 1;
4274 l_table_lnk := 'gmd_recipes' || '|' || x_recipe_id;
4275
4276 ---
4277 --- Create recipe header text
4278 ---
4279 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
4280 (p_text_code => l_text_code,
4281 p_lang_code => l_rcp_hdr_text_tbl(l_txt_ind).lang_code,
4282 p_text => l_rcp_hdr_text_tbl(l_txt_ind).text,
4283 p_line_no => l_rcp_hdr_text_tbl(l_txt_ind).line_no,
4284 p_paragraph_code => l_rcp_hdr_text_tbl(l_txt_ind).paragraph_code,
4285 p_sub_paracode => l_rcp_hdr_text_tbl(l_txt_ind).sub_paracode,
4286 p_table_lnk => l_table_lnk,
4287 p_user_id => l_user_id,
4288 x_row_id => l_rowid,
4289 x_return_code => l_return_code,
4290 x_error_msg => l_error_msg);
4291
4292 IF (l_return_code <> 'S') THEN
4293 RAISE COPY_HEADER_TEXT_EXCEPTION;
4294 END IF;
4295
4296 END LOOP;
4297
4298 END IF;
4299
4300 ---
4301 --- Create recipe header record
4302 ---
4303
4304 IF NOT l_copy_recipe THEN
4305 DELETE gmd_recipes_b WHERE recipe_id = p_copy_from_recipe_id;
4306 END IF;
4307
4308 IF x_routing_id <= 0 THEN
4309 l_recipe_tbl.ROUTING_ID := NULL;
4310 ELSE
4311 l_recipe_tbl.ROUTING_ID := x_routing_id;
4312 END IF;
4313
4314 l_recipe_tbl.TEXT_CODE := l_text_code;
4315 l_recipe_tbl.PROJECT_ID := l_rcp_hdr_rec.project_id;
4316 l_recipe_tbl.OWNER_LAB_TYPE := l_rcp_hdr_rec.owner_lab_type;
4317 l_recipe_tbl.RECIPE_NO := p_recipe_no;
4318 l_recipe_tbl.RECIPE_VERSION := p_recipe_vers;
4319 l_recipe_tbl.RECIPE_ID := x_recipe_id;
4320 l_recipe_tbl.RECIPE_DESCRIPTION := p_recipe_desc;
4321 l_recipe_tbl.USER_ID := l_user_id;
4322 l_recipe_tbl.OWNER_ORGANIZATION_ID := l_rcp_hdr_rec.owner_organization_id;
4323 l_recipe_tbl.CREATION_ORGANIZATION_ID := l_rcp_hdr_rec.creation_organization_id;
4324 l_recipe_tbl.FORMULA_ID := x_formula_id;
4325
4326 /*Bug 3953359 - Thomas Daniel */
4327 /*Added initializing of the routing status as New */
4328 l_recipe_tbl.RECIPE_STATUS := 100;
4329
4330 l_recipe_tbl.DELETE_MARK := 0;
4331 l_recipe_tbl.CREATED_BY := l_user_id;
4332 l_recipe_tbl.CREATION_DATE := SYSDATE;
4333 l_recipe_tbl.LAST_UPDATED_BY := l_user_id;
4334 l_recipe_tbl.LAST_UPDATE_DATE := SYSDATE;
4335 l_recipe_tbl.LAST_UPDATE_LOGIN := l_login_id;
4336 l_recipe_tbl.OWNER_ID := l_user_id;
4337 l_recipe_tbl.CALCULATE_STEP_QUANTITY := l_rcp_hdr_rec.calculate_step_quantity;
4338
4339 -- GMD-GMO changes
4340 l_recipe_tbl.ENHANCED_PI_IND := l_rcp_hdr_rec.enhanced_pi_ind;
4341
4342 -- Include contitguous ind value
4343 l_recipe_tbl.CONTIGUOUS_IND := l_rcp_hdr_rec.contiguous_ind;
4344
4345 l_recipe_tbl.recipe_type := l_rcp_hdr_rec.recipe_type;
4346
4347 l_recipe_update_flex.ATTRIBUTE_CATEGORY := l_rcp_hdr_rec.attribute_category;
4348 l_recipe_update_flex.ATTRIBUTE1 := l_rcp_hdr_rec.attribute1;
4349 l_recipe_update_flex.ATTRIBUTE2 := l_rcp_hdr_rec.attribute2;
4350 l_recipe_update_flex.ATTRIBUTE3 := l_rcp_hdr_rec.attribute3;
4351 l_recipe_update_flex.ATTRIBUTE4 := l_rcp_hdr_rec.attribute4;
4352 l_recipe_update_flex.ATTRIBUTE5 := l_rcp_hdr_rec.attribute5;
4353 l_recipe_update_flex.ATTRIBUTE6 := l_rcp_hdr_rec.attribute6;
4354 l_recipe_update_flex.ATTRIBUTE7 := l_rcp_hdr_rec.attribute7;
4355 l_recipe_update_flex.ATTRIBUTE8 := l_rcp_hdr_rec.attribute8;
4356 l_recipe_update_flex.ATTRIBUTE9 := l_rcp_hdr_rec.attribute9;
4357 l_recipe_update_flex.ATTRIBUTE10 := l_rcp_hdr_rec.attribute10;
4358 l_recipe_update_flex.ATTRIBUTE11 := l_rcp_hdr_rec.attribute11;
4359 l_recipe_update_flex.ATTRIBUTE12 := l_rcp_hdr_rec.attribute12;
4360 l_recipe_update_flex.ATTRIBUTE13 := l_rcp_hdr_rec.attribute13;
4361 l_recipe_update_flex.ATTRIBUTE14 := l_rcp_hdr_rec.attribute14;
4362 l_recipe_update_flex.ATTRIBUTE15 := l_rcp_hdr_rec.attribute15;
4363 l_recipe_update_flex.ATTRIBUTE16 := l_rcp_hdr_rec.attribute16;
4364 l_recipe_update_flex.ATTRIBUTE17 := l_rcp_hdr_rec.attribute17;
4365 l_recipe_update_flex.ATTRIBUTE18 := l_rcp_hdr_rec.attribute18;
4366 l_recipe_update_flex.ATTRIBUTE19 := l_rcp_hdr_rec.attribute19;
4367 l_recipe_update_flex.ATTRIBUTE20 := l_rcp_hdr_rec.attribute20;
4368 l_recipe_update_flex.ATTRIBUTE21 := l_rcp_hdr_rec.attribute21;
4369 l_recipe_update_flex.ATTRIBUTE22 := l_rcp_hdr_rec.attribute22;
4370 l_recipe_update_flex.ATTRIBUTE23 := l_rcp_hdr_rec.attribute23;
4371 l_recipe_update_flex.ATTRIBUTE24 := l_rcp_hdr_rec.attribute24;
4372 l_recipe_update_flex.ATTRIBUTE25 := l_rcp_hdr_rec.attribute25;
4373 l_recipe_update_flex.ATTRIBUTE26 := l_rcp_hdr_rec.attribute26;
4374 l_recipe_update_flex.ATTRIBUTE27 := l_rcp_hdr_rec.attribute27;
4375 l_recipe_update_flex.ATTRIBUTE28 := l_rcp_hdr_rec.attribute28;
4376 l_recipe_update_flex.ATTRIBUTE29 := l_rcp_hdr_rec.attribute29;
4377 l_recipe_update_flex.ATTRIBUTE30 := l_rcp_hdr_rec.attribute30;
4378
4379 GMD_RECIPE_HEADER_PVT.Create_Recipe_Header
4380 ( p_recipe_header_rec => l_recipe_tbl
4381 ,p_recipe_hdr_flex_rec => l_recipe_update_flex
4382 ,x_return_status => l_return_code);
4383
4384 IF l_return_code <> 'S' THEN
4385 RAISE COPY_RECIPE_EXCEPTION;
4386 END IF;
4387
4388 ---
4389 --- Insert recipe step records
4390 ---
4391 l_txt_ind := 1;
4392
4393 IF NOT l_copy_recipe THEN
4394 DELETE gmd_recipe_routing_steps WHERE recipe_id = p_copy_from_recipe_id;
4395 END IF;
4396
4397 FOR i IN 1..l_rcp_stp_tbl.count LOOP
4398
4399 IF (l_rcp_stp_tbl(i).text_code > 0) THEN
4400
4401 IF l_copy_recipe THEN
4402 OPEN Get_Text_Code;
4403 FETCH Get_Text_Code INTO l_text_code;
4404 CLOSE Get_Text_Code;
4405 ELSE
4406 l_text_code := l_rcp_stp_tbl(i).text_code;
4407 DELETE fm_text_tbl WHERE text_code = l_text_code;
4408 END IF;
4409
4410 l_table_lnk := 'gmd_recipe_routing_steps' || '|' || x_recipe_id ||
4411 '|' || l_rcp_stp_tbl(i).routingstep_id;
4412
4413 WHILE (l_txt_ind <= l_rcp_stp_text_tbl.COUNT AND
4414 l_rcp_stp_text_tbl(l_txt_ind).text_code =
4415 l_rcp_stp_tbl(i).text_code) LOOP
4416
4417 ---
4418 --- Create recipe step text
4419 ---
4420 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
4421 (p_text_code => l_text_code,
4422 p_lang_code => l_rcp_stp_text_tbl(l_txt_ind).lang_code,
4423 p_text => l_rcp_stp_text_tbl(l_txt_ind).text,
4424 p_line_no => l_rcp_stp_text_tbl(l_txt_ind).line_no,
4425 p_paragraph_code => l_rcp_stp_text_tbl(l_txt_ind).paragraph_code,
4426 p_sub_paracode => l_rcp_stp_text_tbl(l_txt_ind).sub_paracode,
4427 p_table_lnk => l_table_lnk,
4428 p_user_id => l_user_id,
4429 x_row_id => l_rowid,
4430 x_return_code => l_return_code,
4431 x_error_msg => l_error_msg);
4432
4433 IF (l_return_code <> 'S') THEN
4434 RAISE COPY_HEADER_TEXT_EXCEPTION;
4435 END IF;
4436
4437 l_txt_ind := l_txt_ind + 1;
4438
4439 END LOOP;
4440
4441 END IF;
4442
4443 ---
4444 --- Create recipe step lines
4445 ---
4446 l_rcp_stp_tbl(i).recipe_id := x_recipe_id;
4447 l_rcp_stp_tbl(i).text_code := l_text_code;
4448
4449 INSERT INTO gmd_recipe_routing_steps
4450 (recipe_id,
4451 routingstep_id,
4452 step_qty,
4453 created_by,
4454 creation_date,
4455 last_update_date,
4456 last_update_login,
4457 text_code,
4458 last_updated_by,
4459 attribute1,
4460 attribute2,
4461 attribute3,
4462 attribute4,
4463 attribute5,
4464 attribute6,
4465 attribute7,
4466 attribute8,
4467 attribute9,
4468 attribute10,
4469 attribute11,
4470 attribute12,
4471 attribute13,
4472 attribute14,
4473 attribute15,
4474 attribute16,
4475 attribute17,
4476 attribute18,
4477 attribute19,
4478 attribute20,
4479 attribute21,
4480 attribute22,
4481 attribute23,
4482 attribute24,
4483 attribute25,
4484 attribute26,
4485 attribute27,
4486 attribute28,
4487 attribute29,
4488 attribute30,
4489 attribute_category,
4490 mass_std_uom,
4491 volume_std_uom,
4492 volume_qty, mass_qty)
4493 VALUES
4494 (x_recipe_id,
4495 l_rcp_stp_tbl(i).routingstep_id,
4496 l_rcp_stp_tbl(i).step_qty,
4497 l_user_id,
4498 SYSDATE,
4499 SYSDATE,
4500 l_login_id,
4501 l_rcp_stp_tbl(i).text_code,
4502 l_user_id,
4503 l_rcp_stp_tbl(i).attribute1,
4504 l_rcp_stp_tbl(i).attribute2,
4505 l_rcp_stp_tbl(i).attribute3,
4506 l_rcp_stp_tbl(i).attribute4,
4507 l_rcp_stp_tbl(i).attribute5,
4508 l_rcp_stp_tbl(i).attribute6,
4509 l_rcp_stp_tbl(i).attribute7,
4510 l_rcp_stp_tbl(i).attribute8,
4511 l_rcp_stp_tbl(i).attribute9,
4512 l_rcp_stp_tbl(i).attribute10,
4513 l_rcp_stp_tbl(i).attribute11,
4514 l_rcp_stp_tbl(i).attribute12,
4515 l_rcp_stp_tbl(i).attribute13,
4516 l_rcp_stp_tbl(i).attribute14,
4517 l_rcp_stp_tbl(i).attribute15,
4518 l_rcp_stp_tbl(i).attribute16,
4519 l_rcp_stp_tbl(i).attribute17,
4520 l_rcp_stp_tbl(i).attribute18,
4521 l_rcp_stp_tbl(i).attribute19,
4522 l_rcp_stp_tbl(i).attribute20,
4523 l_rcp_stp_tbl(i).attribute21,
4524 l_rcp_stp_tbl(i).attribute22,
4525 l_rcp_stp_tbl(i).attribute23,
4526 l_rcp_stp_tbl(i).attribute24,
4527 l_rcp_stp_tbl(i).attribute25,
4528 l_rcp_stp_tbl(i).attribute26,
4529 l_rcp_stp_tbl(i).attribute27,
4530 l_rcp_stp_tbl(i).attribute28,
4531 l_rcp_stp_tbl(i).attribute29,
4532 l_rcp_stp_tbl(i).attribute30,
4533 l_rcp_stp_tbl(i).attribute_category,
4534 l_rcp_stp_tbl(i).mass_std_uom,
4535 l_rcp_stp_tbl(i).volume_std_uom,
4536 l_rcp_stp_tbl(i).volume_qty,
4537 l_rcp_stp_tbl(i).mass_qty);
4538
4539 END LOOP;
4540
4541 ---
4542 --- Insert step/material associations
4543 ---
4544 l_txt_ind := 1;
4545
4546 IF NOT l_copy_recipe THEN
4547 DELETE gmd_recipe_step_materials WHERE recipe_id = p_copy_from_recipe_id;
4548 END IF;
4549
4550 FOR i IN 1..l_stp_mtl_tbl.count LOOP
4551
4552 IF (l_stp_mtl_tbl(i).text_code > 0) THEN
4553
4554 IF l_copy_recipe THEN
4555 OPEN Get_Text_Code;
4556 FETCH Get_Text_Code INTO l_text_code;
4557 CLOSE Get_Text_Code;
4558 ELSE
4559 l_text_code := l_stp_mtl_tbl(i).text_code;
4560 DELETE fm_text_tbl WHERE text_code = l_text_code;
4561 END IF;
4562
4563 l_table_lnk := 'gmd_recipe_step_materials' || '|' ||
4564 l_stp_mtl_tbl(i).formulaline_id ||
4565 '|' || l_stp_mtl_tbl(i).routingstep_id;
4566
4567 WHILE (l_txt_ind <= l_stp_mtl_text_tbl.COUNT AND
4568 l_stp_mtl_text_tbl(l_txt_ind).text_code =
4569 l_stp_mtl_tbl(i).text_code) LOOP
4570
4571 ---
4572 --- Create step/item association text
4573 ---
4574 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
4575 (p_text_code => l_text_code,
4576 p_lang_code => l_stp_mtl_text_tbl(l_txt_ind).lang_code,
4577 p_text => l_stp_mtl_text_tbl(l_txt_ind).text,
4578 p_line_no => l_stp_mtl_text_tbl(l_txt_ind).line_no,
4579 p_paragraph_code => l_stp_mtl_text_tbl(l_txt_ind).paragraph_code,
4580 p_sub_paracode => l_stp_mtl_text_tbl(l_txt_ind).sub_paracode,
4581 p_table_lnk => l_table_lnk,
4582 p_user_id => l_user_id,
4583 x_row_id => l_rowid,
4584 x_return_code => l_return_code,
4585 x_error_msg => l_error_msg);
4586
4587 IF (l_return_code <> 'S') THEN
4588 RAISE COPY_HEADER_TEXT_EXCEPTION;
4589 END IF;
4590
4591 l_txt_ind := l_txt_ind + 1;
4592
4593 END LOOP;
4594
4595 END IF;
4596
4597 ---
4598 --- Create step/item association lines
4599 ---
4600 l_rcp_stp_tbl(i).recipe_id := x_recipe_id;
4601 l_rcp_stp_tbl(i).text_code := l_text_code;
4602
4603 Create_Step_Material_Link (
4604 p_recipe_id => x_recipe_id,
4605 p_formulaline_id => l_stp_mtl_tbl(i).formulaline_id,
4606 p_routingstep_id => l_stp_mtl_tbl(i).routingstep_id,
4607 p_text_code => l_text_code,
4608 p_user_id => l_user_id,
4609 p_last_update_date => SYSDATE,
4610 x_return_code => x_return_code,
4611 x_error_msg => x_error_msg);
4612
4613 IF x_return_code <> 'S' THEN
4614 RAISE PROCEDURE_EXCEPTION;
4615 END IF;
4616
4617 END LOOP;
4618
4619 ---
4620 --- Insert customers
4621 ---
4622 l_txt_ind := 1;
4623
4624 IF NOT l_copy_recipe THEN
4625 DELETE gmd_recipe_customers WHERE recipe_id = p_copy_from_recipe_id;
4626 END IF;
4627
4628 FOR i IN 1..l_rcp_cust_tbl.count LOOP
4629
4630 IF (l_rcp_cust_tbl(i).text_code > 0) THEN
4631
4632 IF l_copy_recipe THEN
4633 OPEN Get_Text_Code;
4634 FETCH Get_Text_Code INTO l_text_code;
4635 CLOSE Get_Text_Code;
4636 ELSE
4637 l_text_code := l_rcp_cust_tbl(i).text_code;
4638 DELETE fm_text_tbl WHERE text_code = l_text_code;
4639 END IF;
4640
4641 l_table_lnk := 'gmd_recipe_customers' || '|' ||
4642 x_recipe_id ||
4643 '|' || l_rcp_cust_tbl(i).customer_id;
4644
4645 WHILE (l_txt_ind <= l_rcp_cust_text_tbl.COUNT AND
4646 l_rcp_cust_text_tbl(l_txt_ind).text_code =
4647 l_rcp_cust_tbl(i).text_code) LOOP
4648
4649 ---
4650 --- Create recipe customer text
4651 ---
4652 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
4653 (p_text_code => l_text_code,
4654 p_lang_code => l_rcp_cust_text_tbl(l_txt_ind).lang_code,
4655 p_text => l_rcp_cust_text_tbl(l_txt_ind).text,
4656 p_line_no => l_rcp_cust_text_tbl(l_txt_ind).line_no,
4657 p_paragraph_code => l_rcp_cust_text_tbl(l_txt_ind).paragraph_code,
4658 p_sub_paracode => l_rcp_cust_text_tbl(l_txt_ind).sub_paracode,
4659 p_table_lnk => l_table_lnk,
4660 p_user_id => l_user_id,
4661 x_row_id => l_rowid,
4662 x_return_code => l_return_code,
4663 x_error_msg => l_error_msg);
4664
4665 IF (l_return_code <> 'S') THEN
4666 RAISE COPY_HEADER_TEXT_EXCEPTION;
4667 END IF;
4668
4669 l_txt_ind := l_txt_ind + 1;
4670
4671 END LOOP;
4672
4673 END IF;
4674
4675 ---
4676 --- Create customer line
4677 ---
4678 l_rcp_stp_tbl(i).recipe_id := x_recipe_id;
4679 l_rcp_stp_tbl(i).text_code := l_text_code;
4680
4681 Add_Recipe_Customer (
4682 p_recipe_id => x_recipe_id,
4683 p_customer_id => l_rcp_cust_tbl(i).customer_id,
4684 p_text_code => l_text_code,
4685 p_org_id => l_rcp_cust_tbl(i).org_id, --new addition
4686 p_site_use_id => l_rcp_cust_tbl(i).site_id, --new addition
4687 p_last_update_date => SYSDATE,
4688 x_return_code => x_return_code,
4689 x_error_msg => x_error_msg);
4690
4691 IF x_return_code <> 'S' THEN
4692 RAISE PROCEDURE_EXCEPTION;
4693 END IF;
4694
4695 END LOOP;
4696
4697 ---
4698 --- Insert process losses
4699 ---
4700 l_txt_ind := 1;
4701
4702 IF NOT l_copy_recipe THEN
4703 DELETE gmd_recipe_process_loss WHERE recipe_id = p_copy_from_recipe_id;
4704 END IF;
4705
4706 FOR i IN 1..l_rcp_loss_tbl.count LOOP
4707
4708 IF l_copy_recipe THEN
4709 OPEN Cur_loss_id;
4710 FETCH Cur_loss_id INTO l_loss_id;
4711 CLOSE Cur_loss_id;
4712 ELSE
4713 l_loss_id := l_rcp_loss_tbl(i).recipe_process_loss_id;
4714 END IF;
4715
4716 IF (l_loss_id < 1) THEN
4717 RAISE GET_SURROGATE_EXCEPTION;
4718 END IF;
4719
4720 IF (l_rcp_loss_tbl(i).text_code > 0) THEN
4721
4722 IF l_copy_recipe THEN
4723 OPEN Get_Text_Code;
4724 FETCH Get_Text_Code INTO l_text_code;
4725 CLOSE Get_Text_Code;
4726 ELSE
4727 l_text_code := l_rcp_loss_tbl(i).text_code;
4728 DELETE fm_text_tbl WHERE text_code = l_text_code;
4729 END IF;
4730
4731 l_table_lnk := 'gmd_recipe_process_loss' || '|' ||
4732 x_recipe_id ||
4733 '|' || l_loss_id;
4734
4735 WHILE (l_txt_ind <= l_rcp_loss_text_tbl.COUNT AND
4736 l_rcp_loss_text_tbl(l_txt_ind).text_code =
4737 l_rcp_loss_tbl(i).text_code) LOOP
4738
4739 ---
4740 --- Create recipe loss text
4741 ---
4742 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
4743 (p_text_code => l_text_code,
4744 p_lang_code => l_rcp_loss_text_tbl(l_txt_ind).lang_code,
4745 p_text => l_rcp_loss_text_tbl(l_txt_ind).text,
4746 p_line_no => l_rcp_loss_text_tbl(l_txt_ind).line_no,
4747 p_paragraph_code => l_rcp_loss_text_tbl(l_txt_ind).paragraph_code,
4748 p_sub_paracode => l_rcp_loss_text_tbl(l_txt_ind).sub_paracode,
4749 p_table_lnk => l_table_lnk,
4750 p_user_id => l_user_id,
4751 x_row_id => l_rowid,
4752 x_return_code => l_return_code,
4753 x_error_msg => l_error_msg);
4754
4755 IF (l_return_code <> 'S') THEN
4756 RAISE COPY_HEADER_TEXT_EXCEPTION;
4757 END IF;
4758
4759 l_txt_ind := l_txt_ind + 1;
4760
4761 END LOOP;
4762
4763 END IF;
4764
4765 ---
4766 --- Create process loss line
4767 ---
4768 Create_Process_Loss (p_recipe_id => x_recipe_id,
4769 p_orgn_id => l_rcp_loss_tbl(i).organization_id,
4770 p_process_loss => l_rcp_loss_tbl(i).process_loss,
4771 p_text_code => l_text_code,
4772 p_contiguous_ind => l_rcp_loss_tbl(i).contiguous_ind , -- need checking
4773 p_last_update_date => SYSDATE,
4774 p_loss_id => l_loss_id,
4775 x_loss_id => l_dummy,
4776 x_return_code => x_return_code,
4777 x_error_msg => x_error_msg);
4778
4779 IF x_return_code <> 'S' THEN
4780 RAISE PROCEDURE_EXCEPTION;
4781 END IF;
4782
4783 END LOOP;
4784
4785 l_txt_ind := 1;
4786
4787 IF l_copy_recipe THEN
4788
4789 ---
4790 --- Insert validity rules
4791 ---
4792
4793 FOR i IN 1..l_rcp_vr_tbl.COUNT LOOP
4794
4795 OPEN Cur_vr_id;
4796 FETCH Cur_vr_id INTO l_vr_id;
4797 CLOSE Cur_vr_id;
4798
4799 IF (l_vr_id < 1) THEN
4800 RAISE GET_SURROGATE_EXCEPTION;
4801 END IF;
4802
4803 IF (l_rcp_vr_tbl(i).text_code > 0) THEN
4804
4805 OPEN Get_Text_Code;
4806 FETCH Get_Text_Code INTO l_text_code;
4807 CLOSE Get_Text_Code;
4808
4809 l_table_lnk := 'gmd_recipe_validity_rules' || '|' ||
4810 x_recipe_id ||
4811 '|' || l_vr_id;
4812
4813 WHILE (l_txt_ind <= l_rcp_vr_text_tbl.COUNT AND
4814 l_rcp_vr_text_tbl(l_txt_ind).text_code =
4815 l_rcp_vr_tbl(i).text_code) LOOP
4816
4817 ---
4818 --- Create validity rules text
4819 ---
4820 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
4821 (p_text_code => l_text_code,
4822 p_lang_code => l_rcp_vr_text_tbl(l_txt_ind).lang_code,
4823 p_text => l_rcp_vr_text_tbl(l_txt_ind).text,
4824 p_line_no => l_rcp_vr_text_tbl(l_txt_ind).line_no,
4825 p_paragraph_code => l_rcp_vr_text_tbl(l_txt_ind).paragraph_code,
4826 p_sub_paracode => l_rcp_vr_text_tbl(l_txt_ind).sub_paracode,
4827 p_table_lnk => l_table_lnk,
4828 p_user_id => l_user_id,
4829 x_row_id => l_rowid,
4830 x_return_code => l_return_code,
4831 x_error_msg => l_error_msg);
4832
4833 IF (l_return_code <> 'S') THEN
4834 RAISE COPY_HEADER_TEXT_EXCEPTION;
4835 END IF;
4836
4837 l_txt_ind := l_txt_ind + 1;
4838
4839 END LOOP;
4840
4841 END IF;
4842
4843 ---
4844 --- Create validity rules
4845 ---
4846
4847 INSERT INTO gmd_recipe_validity_rules
4848 ( recipe_validity_rule_id,
4849 recipe_id,
4850 organization_id,
4851 inventory_item_id,
4852 recipe_use,
4853 preference,
4854 start_date,
4855 end_date,
4856 min_qty,
4857 max_qty,
4858 std_qty,
4859 detail_uom,
4860 inv_min_qty,
4861 inv_max_qty,
4862 text_code,
4863 attribute_category,
4864 attribute1,
4865 attribute2,
4866 attribute3,
4867 attribute4,
4868 attribute5,
4869 attribute6,
4870 attribute7,
4871 attribute8,
4872 attribute9,
4873 attribute10,
4874 attribute11,
4875 attribute12,
4876 attribute13,
4877 attribute14,
4878 attribute15,
4879 attribute16,
4880 attribute17,
4881 attribute18,
4882 attribute19,
4883 attribute20,
4884 attribute21,
4885 attribute22,
4886 attribute23,
4887 attribute24,
4888 attribute25,
4889 attribute26,
4890 attribute27,
4891 attribute28,
4892 attribute29,
4893 attribute30,
4894 created_by,
4895 creation_date,
4896 last_updated_by,
4897 last_update_date,
4898 last_update_login,
4899 delete_mark,
4900 lab_type,
4901 validity_rule_status)
4902 VALUES
4903 ( l_vr_id,
4904 x_recipe_id,
4905 l_rcp_vr_tbl(i).organization_id,
4906 l_rcp_vr_tbl(i).inventory_item_id,
4907 l_rcp_vr_tbl(i).recipe_use,
4908 l_rcp_vr_tbl(i).preference,
4909 l_rcp_vr_tbl(i).start_date,
4910 l_rcp_vr_tbl(i).end_date,
4911 l_rcp_vr_tbl(i).min_qty,
4912 l_rcp_vr_tbl(i).max_qty,
4913 l_rcp_vr_tbl(i).std_qty,
4914 l_rcp_vr_tbl(i).detail_uom,
4915 l_rcp_vr_tbl(i).inv_min_qty,
4916 l_rcp_vr_tbl(i).inv_max_qty,
4917 l_text_code,
4918 l_rcp_vr_tbl(i).attribute_category,
4919 l_rcp_vr_tbl(i).attribute1,
4920 l_rcp_vr_tbl(i).attribute2,
4921 l_rcp_vr_tbl(i).attribute3,
4922 l_rcp_vr_tbl(i).attribute4,
4923 l_rcp_vr_tbl(i).attribute5,
4924 l_rcp_vr_tbl(i).attribute6,
4925 l_rcp_vr_tbl(i).attribute7,
4926 l_rcp_vr_tbl(i).attribute8,
4927 l_rcp_vr_tbl(i).attribute9,
4928 l_rcp_vr_tbl(i).attribute10,
4929 l_rcp_vr_tbl(i).attribute11,
4930 l_rcp_vr_tbl(i).attribute12,
4931 l_rcp_vr_tbl(i).attribute13,
4932 l_rcp_vr_tbl(i).attribute14,
4933 l_rcp_vr_tbl(i).attribute15,
4934 l_rcp_vr_tbl(i).attribute16,
4935 l_rcp_vr_tbl(i).attribute17,
4936 l_rcp_vr_tbl(i).attribute18,
4937 l_rcp_vr_tbl(i).attribute19,
4938 l_rcp_vr_tbl(i).attribute20,
4939 l_rcp_vr_tbl(i).attribute21,
4940 l_rcp_vr_tbl(i).attribute22,
4941 l_rcp_vr_tbl(i).attribute23,
4942 l_rcp_vr_tbl(i).attribute24,
4943 l_rcp_vr_tbl(i).attribute25,
4944 l_rcp_vr_tbl(i).attribute26,
4945 l_rcp_vr_tbl(i).attribute27,
4946 l_rcp_vr_tbl(i).attribute28,
4947 l_rcp_vr_tbl(i).attribute29,
4948 l_rcp_vr_tbl(i).attribute30,
4949 l_user_id,
4950 SYSDATE,
4951 l_user_id,
4952 SYSDATE,
4953 l_login_id,
4954 l_rcp_vr_tbl(i).delete_mark,
4955 l_rcp_vr_tbl(i).lab_type,
4956 100);
4957 END LOOP;
4958
4959 END IF;
4960
4961 IF l_rcp_hdr_rec.routing_id IS NOT NULL THEN
4962
4963
4964 ---
4965 --- Insert organization specific resource information
4966 ---
4967
4968 DELETE gmd_recipe_orgn_resources WHERE recipe_id = p_copy_from_recipe_id;
4969
4970 FOR i IN 1..l_rcp_rsrc_tbl.COUNT LOOP
4971
4972 IF (l_rcp_rsrc_tbl(i).text_code > 0) THEN
4973
4974 OPEN Get_Text_Code;
4975 FETCH Get_Text_Code INTO l_text_code;
4976 CLOSE Get_Text_Code;
4977
4978 l_table_lnk := 'gmd_recipe_orgn_resources' || '|' ||
4979 x_recipe_id ||
4980 '|' || l_rcp_rsrc_tbl(i).routingstep_id;
4981
4982 WHILE (l_txt_ind <= l_rcp_rsrc_text_tbl.COUNT AND
4983 l_rcp_rsrc_text_tbl(l_txt_ind).text_code =
4984 l_rcp_rsrc_tbl(i).text_code) LOOP
4985
4986 ---
4987 --- Create resource text
4988 ---
4989 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
4990 (p_text_code => l_text_code,
4991 p_lang_code => l_rcp_rsrc_text_tbl(l_txt_ind).lang_code,
4992 p_text => l_rcp_rsrc_text_tbl(l_txt_ind).text,
4993 p_line_no => l_rcp_rsrc_text_tbl(l_txt_ind).line_no,
4994 p_paragraph_code => l_rcp_rsrc_text_tbl(l_txt_ind).paragraph_code,
4995 p_sub_paracode => l_rcp_rsrc_text_tbl(l_txt_ind).sub_paracode,
4996 p_table_lnk => l_table_lnk,
4997 p_user_id => l_user_id,
4998 x_row_id => l_rowid,
4999 x_return_code => l_return_code,
5000 x_error_msg => l_error_msg);
5001
5002 IF (l_return_code <> 'S') THEN
5003 RAISE COPY_HEADER_TEXT_EXCEPTION;
5004 END IF;
5005
5006 l_txt_ind := l_txt_ind + 1;
5007
5008 END LOOP;
5009
5010 END IF;
5011
5012 ---
5013 --- Create resource
5014 ---
5015
5016 INSERT INTO gmd_recipe_orgn_resources
5017 ( recipe_id,
5018 organization_id,
5019 routingstep_id,
5020 oprn_line_id,
5021 resources,
5022 creation_date,
5023 created_by,
5024 last_updated_by,
5025 last_update_date,
5026 min_capacity,
5027 max_capacity,
5028 last_update_login,
5029 text_code,
5030 attribute1,
5031 attribute2,
5032 attribute3,
5033 attribute4,
5034 attribute5,
5035 attribute6,
5036 attribute7,
5037 attribute8,
5038 attribute9,
5039 attribute10,
5040 attribute11,
5041 attribute12,
5042 attribute13,
5043 attribute14,
5044 attribute15,
5045 attribute16,
5046 attribute17,
5047 attribute18,
5048 attribute19,
5049 attribute20,
5050 attribute21,
5051 attribute22,
5052 attribute23,
5053 attribute24,
5054 attribute25,
5055 attribute26,
5056 attribute27,
5057 attribute28,
5058 attribute29,
5059 attribute30,
5060 attribute_category,
5061 process_parameter_5,
5062 process_parameter_4,
5063 process_parameter_3,
5064 process_parameter_2,
5065 process_parameter_1,
5066 process_uom,
5067 usage_um,
5068 resource_usage,
5069 process_qty)
5070 VALUES
5071 ( x_recipe_id,
5072 l_rcp_rsrc_tbl(i).organization_id,
5073 l_rcp_rsrc_tbl(i).routingstep_id,
5074 l_rcp_rsrc_tbl(i).oprn_line_id,
5075 l_rcp_rsrc_tbl(i).resources,
5076 SYSDATE,
5077 l_user_id,
5078 l_user_id,
5079 SYSDATE,
5080 l_rcp_rsrc_tbl(i).min_capacity,
5081 l_rcp_rsrc_tbl(i).max_capacity,
5082 l_login_id,
5083 l_text_code,
5084 l_rcp_rsrc_tbl(i).attribute1,
5085 l_rcp_rsrc_tbl(i).attribute2,
5086 l_rcp_rsrc_tbl(i).attribute3,
5087 l_rcp_rsrc_tbl(i).attribute4,
5088 l_rcp_rsrc_tbl(i).attribute5,
5089 l_rcp_rsrc_tbl(i).attribute6,
5090 l_rcp_rsrc_tbl(i).attribute7,
5091 l_rcp_rsrc_tbl(i).attribute8,
5092 l_rcp_rsrc_tbl(i).attribute9,
5093 l_rcp_rsrc_tbl(i).attribute10,
5094 l_rcp_rsrc_tbl(i).attribute11,
5095 l_rcp_rsrc_tbl(i).attribute12,
5096 l_rcp_rsrc_tbl(i).attribute13,
5097 l_rcp_rsrc_tbl(i).attribute14,
5098 l_rcp_rsrc_tbl(i).attribute15,
5099 l_rcp_rsrc_tbl(i).attribute16,
5100 l_rcp_rsrc_tbl(i).attribute17,
5101 l_rcp_rsrc_tbl(i).attribute18,
5102 l_rcp_rsrc_tbl(i).attribute19,
5103 l_rcp_rsrc_tbl(i).attribute20,
5104 l_rcp_rsrc_tbl(i).attribute21,
5105 l_rcp_rsrc_tbl(i).attribute22,
5106 l_rcp_rsrc_tbl(i).attribute23,
5107 l_rcp_rsrc_tbl(i).attribute24,
5108 l_rcp_rsrc_tbl(i).attribute25,
5109 l_rcp_rsrc_tbl(i).attribute26,
5110 l_rcp_rsrc_tbl(i).attribute27,
5111 l_rcp_rsrc_tbl(i).attribute28,
5112 l_rcp_rsrc_tbl(i).attribute29,
5113 l_rcp_rsrc_tbl(i).attribute30,
5114 l_rcp_rsrc_tbl(i).attribute_category,
5115 l_rcp_rsrc_tbl(i).process_parameter_5,
5116 l_rcp_rsrc_tbl(i).process_parameter_4,
5117 l_rcp_rsrc_tbl(i).process_parameter_3,
5118 l_rcp_rsrc_tbl(i).process_parameter_2,
5119 l_rcp_rsrc_tbl(i).process_parameter_1,
5120 l_rcp_rsrc_tbl(i).process_uom,
5121 l_rcp_rsrc_tbl(i).usage_um,
5122 l_rcp_rsrc_tbl(i).resource_usage,
5123 l_rcp_rsrc_tbl(i).process_qty);
5124
5125 END LOOP;
5126
5127 ---
5128 --- Insert organization specific activity information
5129 ---
5130
5131 DELETE gmd_recipe_orgn_activities
5132 WHERE recipe_id = p_copy_from_recipe_id;
5133
5134 FOR i IN 1..l_rcp_actv_tbl.COUNT LOOP
5135
5136 IF (l_rcp_actv_tbl(i).text_code > 0) THEN
5137
5138 OPEN Get_Text_Code;
5139 FETCH Get_Text_Code INTO l_text_code;
5140 CLOSE Get_Text_Code;
5141
5142 l_table_lnk := 'gmd_recipe_orgn_activities' || '|' ||
5143 x_recipe_id ||
5144 '|' || l_rcp_actv_tbl(i).oprn_line_id;
5145
5146 WHILE (l_txt_ind <= l_rcp_actv_text_tbl.COUNT AND
5147 l_rcp_actv_text_tbl(l_txt_ind).text_code =
5148 l_rcp_actv_tbl(i).text_code) LOOP
5149
5150 ---
5151 --- Create resource text
5152 ---
5153 GMD_RECIPE_DESIGNER_PKG.Create_Text_Row
5154 (p_text_code => l_text_code,
5155 p_lang_code => l_rcp_actv_text_tbl(l_txt_ind).lang_code,
5156 p_text => l_rcp_actv_text_tbl(l_txt_ind).text,
5157 p_line_no => l_rcp_actv_text_tbl(l_txt_ind).line_no,
5158 p_paragraph_code => l_rcp_actv_text_tbl(l_txt_ind).paragraph_code,
5159 p_sub_paracode => l_rcp_actv_text_tbl(l_txt_ind).sub_paracode,
5160 p_table_lnk => l_table_lnk,
5161 p_user_id => l_user_id,
5162 x_row_id => l_rowid,
5163 x_return_code => l_return_code,
5164 x_error_msg => l_error_msg);
5165
5166 IF (l_return_code <> 'S') THEN
5167 RAISE COPY_HEADER_TEXT_EXCEPTION;
5168 END IF;
5169
5170 l_txt_ind := l_txt_ind + 1;
5171
5172 END LOOP;
5173
5174 END IF;
5175
5176 ---
5177 --- Create activity
5178 ---
5179
5180 INSERT INTO gmd_recipe_orgn_activities
5181 ( recipe_id,
5182 routingstep_id,
5183 oprn_line_id,
5184 activity_factor,
5185 orgn_code,
5186 organization_id,
5187 last_update_login,
5188 text_code,
5189 created_by,
5190 creation_date,
5191 last_updated_by,
5192 last_update_date,
5193 attribute1,
5194 attribute2,
5195 attribute3,
5196 attribute4,
5197 attribute5,
5198 attribute6,
5199 attribute7,
5200 attribute8,
5201 attribute9,
5202 attribute10,
5203 attribute11,
5204 attribute12,
5205 attribute13,
5206 attribute14,
5207 attribute15,
5208 attribute16,
5209 attribute17,
5210 attribute18,
5211 attribute19,
5212 attribute20,
5213 attribute21,
5214 attribute22,
5215 attribute23,
5216 attribute24,
5217 attribute25,
5218 attribute26,
5219 attribute27,
5220 attribute28,
5221 attribute29,
5222 attribute30,
5223 attribute_category)
5224 VALUES
5225 ( x_recipe_id,
5226 l_rcp_actv_tbl(i).routingstep_id,
5227 l_rcp_actv_tbl(i).oprn_line_id,
5228 l_rcp_actv_tbl(i).activity_factor,
5229 l_rcp_rsrc_tbl(i).orgn_code,
5230 l_rcp_rsrc_tbl(i).organization_id,
5231 l_login_id,
5232 l_text_code,
5233 l_user_id,
5234 SYSDATE,
5235 l_user_id,
5236 SYSDATE,
5237 l_rcp_actv_tbl(i).attribute1,
5238 l_rcp_actv_tbl(i).attribute2,
5239 l_rcp_actv_tbl(i).attribute3,
5240 l_rcp_actv_tbl(i).attribute4,
5241 l_rcp_actv_tbl(i).attribute5,
5242 l_rcp_actv_tbl(i).attribute6,
5243 l_rcp_actv_tbl(i).attribute7,
5244 l_rcp_actv_tbl(i).attribute8,
5245 l_rcp_actv_tbl(i).attribute9,
5246 l_rcp_actv_tbl(i).attribute10,
5247 l_rcp_actv_tbl(i).attribute11,
5248 l_rcp_actv_tbl(i).attribute12,
5249 l_rcp_actv_tbl(i).attribute13,
5250 l_rcp_actv_tbl(i).attribute14,
5251 l_rcp_actv_tbl(i).attribute15,
5252 l_rcp_actv_tbl(i).attribute16,
5253 l_rcp_actv_tbl(i).attribute17,
5254 l_rcp_actv_tbl(i).attribute18,
5255 l_rcp_actv_tbl(i).attribute19,
5256 l_rcp_actv_tbl(i).attribute20,
5257 l_rcp_actv_tbl(i).attribute21,
5258 l_rcp_actv_tbl(i).attribute22,
5259 l_rcp_actv_tbl(i).attribute23,
5260 l_rcp_actv_tbl(i).attribute24,
5261 l_rcp_actv_tbl(i).attribute25,
5262 l_rcp_actv_tbl(i).attribute26,
5263 l_rcp_actv_tbl(i).attribute27,
5264 l_rcp_actv_tbl(i).attribute28,
5265 l_rcp_actv_tbl(i).attribute29,
5266 l_rcp_actv_tbl(i).attribute30,
5267 l_rcp_actv_tbl(i).attribute_category);
5268 END LOOP;
5269
5270 ---
5271 --- Insert process parameter information
5272 ---
5273
5274 DELETE gmd_recipe_process_parameters
5275 WHERE recipe_id = p_copy_from_recipe_id;
5276
5277 FOR i IN 1..l_rcp_pp_tbl.COUNT LOOP
5278
5279 ---
5280 --- Create process parameter
5281 ---
5282
5283 INSERT INTO gmd_recipe_process_parameters
5284 ( recipe_id,
5285 organization_id,
5286 routingstep_id,
5287 oprn_line_id,
5288 resources,
5289 parameter_id,
5290 target_value,
5291 minimum_value,
5292 maximum_value,
5293 last_update_login,
5294 created_by,
5295 creation_date,
5296 last_updated_by,
5297 last_update_date)
5298 VALUES
5299 ( x_recipe_id,
5300 l_rcp_pp_tbl(i).organization_id,
5301 l_rcp_pp_tbl(i).routingstep_id,
5302 l_rcp_pp_tbl(i).oprn_line_id,
5303 l_rcp_pp_tbl(i).resources,
5304 l_rcp_pp_tbl(i).parameter_id,
5305 l_rcp_pp_tbl(i).target_value,
5306 l_rcp_pp_tbl(i).minimum_value,
5307 l_rcp_pp_tbl(i).maximum_value,
5308 l_login_id,
5309 l_user_id,
5310 SYSDATE,
5311 l_user_id,
5312 SYSDATE);
5313 END LOOP;
5314
5315 END IF;
5316
5317 END IF;
5318
5319 IF p_commit = 'Y' THEN
5320 COMMIT;
5321
5322 /*Bug 3953359 - Thomas Daniel */
5323 /*Added code to set the default status after copying the formula */
5324 IF l_copy_formula THEN
5325 GMD_RECIPE_DESIGNER_PKG.set_default_status (pEntity_name => 'FORMULA'
5326 ,pEntity_id => X_formula_id
5327 ,x_return_status => l_return_code
5328 ,x_msg_count => l_message_count
5329 ,x_msg_data => l_message_list);
5330 END IF;
5331 IF l_copy_routing THEN
5332 GMD_RECIPE_DESIGNER_PKG.set_default_status (pEntity_name => 'ROUTING'
5333 ,pEntity_id => X_routing_id
5334 ,x_return_status => l_return_code
5335 ,x_msg_count => l_message_count
5336 ,x_msg_data => l_message_list);
5337 END IF;
5338 IF l_copy_recipe THEN
5339 GMD_RECIPE_DESIGNER_PKG.set_default_status (pEntity_name => 'RECIPE'
5340 ,pEntity_id => X_recipe_id
5341 ,x_return_status => l_return_code
5342 ,x_msg_count => l_message_count
5343 ,x_msg_data => l_message_list);
5344 END IF;
5345 END IF;
5346
5347 EXCEPTION
5348 WHEN COPY_HEADER_TEXT_EXCEPTION THEN
5349 ROLLBACK TO Copy_Recipe;
5350 x_return_code := 'F';
5351 x_error_msg := l_error_msg;
5352
5353 WHEN PROCEDURE_EXCEPTION THEN
5354 ROLLBACK TO Copy_Recipe;
5355 x_return_code := 'F';
5356
5357 WHEN COPY_RECIPE_EXCEPTION THEN
5358 ROLLBACK TO Copy_Recipe;
5359 FND_MSG_PUB.GET( p_msg_index => 1,
5360 p_data => x_error_msg,
5361 p_encoded => 'F',
5362 p_msg_index_out => l_dummy);
5363 x_return_code := 'F';
5364
5365 WHEN COPY_ROUTING_EXCEPTION THEN
5366 ROLLBACK TO Copy_Recipe;
5367 FND_MSG_PUB.GET( p_msg_index => 1,
5368 p_data => x_error_msg,
5369 p_encoded => 'F',
5370 p_msg_index_out => l_dummy);
5371 x_return_code := 'F';
5372
5373 WHEN COPY_FORMULA_EXCEPTION THEN
5374 ROLLBACK TO Copy_Recipe;
5375 FND_MSG_PUB.GET( p_msg_index => 1,
5376 p_data => x_error_msg,
5377 p_encoded => 'F',
5378 p_msg_index_out => l_dummy);
5379 x_return_code := 'F';
5380
5381 WHEN COPY_FORM_DTL_EXCEPTION THEN
5382 ROLLBACK TO Copy_Recipe;
5383 FND_MSG_PUB.GET( p_msg_index => 1,
5384 p_data => x_error_msg,
5385 p_encoded => 'F',
5386 p_msg_index_out => l_dummy);
5387 x_return_code := 'F';
5388
5389 WHEN RECIPE_NOT_FOUND THEN
5390 ROLLBACK TO Copy_Recipe;
5391 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_NOT_FOUND');
5392 x_return_code := 'F';
5393 x_error_msg := FND_MESSAGE.GET;
5394
5395 WHEN ROUTING_NOT_FOUND THEN
5396 ROLLBACK TO Copy_Recipe;
5397 FND_MESSAGE.SET_NAME('GMD', 'GMD_RECIPE_ROUTING_INVALID');
5398 x_return_code := 'F';
5399 x_error_msg := FND_MESSAGE.GET;
5400
5401 WHEN FORMULA_NOT_FOUND THEN
5402 ROLLBACK TO Copy_Recipe;
5403 FND_MESSAGE.SET_NAME('GMD', 'GMD_FORMULA_NOT_FOUND');
5404 x_return_code := 'F';
5405 x_error_msg := FND_MESSAGE.GET;
5406
5407 WHEN GET_SURROGATE_EXCEPTION THEN
5408 ROLLBACK TO Copy_Recipe;
5409 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
5410 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
5411 x_return_code := 'F';
5412 x_error_msg := FND_MESSAGE.GET;
5413
5414 WHEN OTHERS THEN
5415 ROLLBACK TO Copy_Recipe;
5416 FND_MESSAGE.SET_NAME('GMD', 'GMD_UNEXPECTED_ERROR');
5417 FND_MESSAGE.SET_TOKEN('ERROR', sqlerrm);
5418 x_return_code := 'U';
5419 x_error_msg := FND_MESSAGE.GET;
5420
5421 END Copy_Recipe;
5422
5423 /* Api start of comments
5424 +============================================================================
5425 | PROCEDURE NAME
5426 | CHECK_RECP_ORGN_ACCESS
5427 |
5428 | DESCRIPTION
5429 | Procedure to chk if user has accesss to the Recp Orgn.
5430 |
5431 | INPUT PARAMETERS
5432 | p_recipe_id NUMBER
5433 | p_user_id NUMBER
5434 |
5435 | OUTPUT PARAMETERS
5436 | x_return_code VARCHAR2
5437 |
5438 | HISTORY
5439 | 13-OCT-2004 S.Sriram Created for Recipe Security (Bug# 3948203)
5440 |
5441 +=============================================================================
5442 Api end of comments
5443 */
5444 PROCEDURE CHECK_RECP_ORGN_ACCESS(p_recipe_id IN NUMBER,
5445 p_user_id IN NUMBER,
5446 x_return_code OUT NOCOPY VARCHAR2) IS
5447
5448 CURSOR Cur_get_recp_orgn IS
5449 SELECT owner_organization_id
5450 FROM gmd_recipes_b
5451 WHERE recipe_id = p_recipe_id;
5452
5453 l_orgn_id NUMBER;
5454 l_return_status VARCHAR2(10);
5455
5456 BEGIN
5457 OPEN Cur_get_recp_orgn;
5458 FETCH Cur_get_recp_orgn INTO l_orgn_id;
5459 CLOSE Cur_get_recp_orgn;
5460
5461 IF (l_orgn_id IS NOT NULL) THEN
5462 IF (GMD_API_GRP.setup AND GMD_API_GRP.OrgnAccessible(l_orgn_id)) THEN
5463 x_return_code := 'S';
5464 ELSE
5465 x_return_code := 'F';
5466 END IF;
5467 ELSE
5468 x_return_code := 'S';
5469 END IF;
5470 EXCEPTION
5471 WHEN OTHERS THEN
5472 x_return_code := FND_API.G_RET_STS_UNEXP_ERROR;
5473
5474 END CHECK_RECP_ORGN_ACCESS;
5475
5476
5477 PROCEDURE Check_Recipe_Formula( p_recipe_id IN NUMBER,
5478 p_organization_id IN NUMBER,
5479 x_return_code OUT NOCOPY VARCHAR2) IS
5480
5481 CURSOR get_recipe_details (l_recipe_id NUMBER) IS
5482 SELECT formula_id, owner_organization_id
5483 FROM gmd_recipes_b
5484 WHERE recipe_id = l_recipe_id;
5485
5486 l_orgn_id NUMBER;
5487 l_formula_id NUMBER;
5488 BEGIN
5489 OPEN get_recipe_details(p_recipe_id);
5490 FETCH get_recipe_details INTO l_formula_id, l_orgn_id;
5491 IF (get_recipe_details%FOUND) THEN
5492 IF (p_organization_id IS NOT NULL) THEN
5493 l_orgn_id := p_organization_id;
5494 END IF;
5495
5496 GMD_API_GRP.check_item_exists (p_formula_id => l_formula_id,
5497 x_return_status => x_return_code,
5498 p_organization_id => l_orgn_id);
5499 ELSE
5500 x_return_code := 'S';
5501 END IF;
5502 CLOSE get_recipe_details;
5503
5504 END Check_Recipe_Formula;
5505
5506 /* Api start of comments
5507 +============================================================================
5508 | PROCEDURE NAME
5509 | set_default_status
5510 |
5511 | DESCRIPTION
5512 | Procedure to set the Default Status for a new Formula, Recipe and Routing
5513 |
5514 |
5515 |
5516 | OUTPUT PARAMETERS
5517 | x_return_code VARCHAR2
5518 | x_msg_count NUMBER
5519 | x_msg_data VARCHAR2
5520 |
5521 | HISTORY
5522 | 27-APR-2004 S.Sriram Created for Default Status Build (Bug# 3408799)
5523 |
5524 +=============================================================================
5525 Api end of comments
5526 */
5527 PROCEDURE set_default_status (pEntity_name IN VARCHAR2
5528 ,pEntity_id IN NUMBER
5529 ,x_return_status OUT NOCOPY VARCHAR2
5530 ,x_msg_count OUT NOCOPY NUMBER
5531 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
5532
5533 /* Local variable section */
5534 l_entity_status gmd_api_grp.status_rec_type;
5535 l_owner_organization_id fm_form_mst_b.owner_organization_id%TYPE;
5536
5537 default_status_err EXCEPTION;
5538
5539 CURSOR get_formula_details(vFormula_id NUMBER) IS
5540 SELECT owner_organization_id
5541 FROM fm_form_mst_b
5542 WHERE formula_id = vFormula_id;
5543
5544 CURSOR get_recipe_details(vRecipe_id NUMBER) IS
5545 SELECT owner_organization_id
5546 FROM gmd_recipes_b
5547 WHERE recipe_id = vRecipe_id;
5548
5549 CURSOR get_routing_details(vRouting_id NUMBER) IS
5550 SELECT owner_organization_id
5551 FROM gmd_routings_b
5552 WHERE routing_id = vRouting_id;
5553
5554 BEGIN
5555 SAVEPOINT default_status_sp;
5556
5557 x_return_status := FND_API.g_ret_sts_success;
5558
5559 IF (pEntity_name = 'FORMULA') THEN
5560 OPEN get_formula_details(pentity_id);
5561 FETCH get_formula_details INTO l_owner_organization_id;
5562 CLOSE get_formula_details;
5563 ELSIF (pEntity_name = 'RECIPE') THEN
5564 OPEN get_recipe_details(pentity_id);
5565 FETCH get_recipe_details INTO l_owner_organization_id;
5566 CLOSE get_recipe_details;
5567 ELSIF (pEntity_name = 'ROUTING') THEN
5568 OPEN get_routing_details(pentity_id);
5569 FETCH get_routing_details INTO l_owner_organization_id;
5570 CLOSE get_routing_details;
5571 END IF;
5572
5573 -- Getting the default status for the owner orgn code
5574 -- or null orgn of recipe from parameters table
5575 gmd_api_grp.get_status_details (V_entity_type => pEntity_name,
5576 V_orgn_id => l_owner_organization_id,
5577 X_entity_status => l_entity_status);
5578
5579 -- Check for any experimental items when formula status is apfgu.
5580 IF (pEntity_name = 'FORMULA') THEN
5581 IF (l_entity_status.status_type = 700) THEN
5582 IF (gmdfmval_pub.check_expr_items(pEntity_id)) THEN
5583 FND_MESSAGE.SET_NAME('GMD','GMD_EXPR_ITEMS_FOUND');
5584 FND_MSG_PUB.ADD;
5585 RAISE FND_API.G_EXC_ERROR;
5586 END IF;
5587 END IF; -- IF (X_status_type = 700) THEN
5588
5589 --Check any inactive items in formula before changing the status
5590 IF (l_entity_status.status_type IN (400,700)) THEN
5591 IF (gmdfmval_pub.inactive_items(pEntity_id)) THEN
5592 FND_MESSAGE.SET_NAME('GMI','IC_ITEM_INACTIVE');
5593 FND_MSG_PUB.ADD;
5594 RAISE FND_API.G_EXC_ERROR;
5595 END IF;
5596 END IF; --l_entity_status.status_type IN (400,700)
5597 END IF;
5598
5599 IF (l_entity_status.entity_status <> 100) THEN
5600 gmd_status_pub.modify_status ( p_api_version => 1
5601 , p_init_msg_list => TRUE
5602 , p_entity_name => pEntity_name
5603 , p_entity_id => pEntity_id
5604 , p_entity_no => NULL
5605 , p_entity_version => NULL
5606 , p_to_status => l_entity_status.entity_status
5607 , p_ignore_flag => FALSE
5608 , x_message_count => x_msg_count
5609 , x_message_list => x_msg_data
5610 , x_return_status => x_return_status);
5611
5612 IF x_return_status NOT IN (FND_API.g_ret_sts_success,'P') THEN
5613 RAISE default_status_err;
5614 END IF; --x_return_status
5615 END IF; --l_entity_status.entity_status <> 100
5616
5617 EXCEPTION
5618 WHEN default_status_err THEN
5619 ROLLBACK TO default_status_sp;
5620 FND_MSG_PUB.Count_And_Get (p_encoded => 'F',
5621 p_count => x_msg_count,
5622 p_data => x_msg_data );
5623 WHEN OTHERS THEN
5624 ROLLBACK TO default_status_sp;
5625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5626 FND_MSG_PUB.Count_And_Get(p_encoded => 'F',
5627 p_count => x_msg_count,
5628 p_data => x_msg_data );
5629 END set_default_status;
5630
5631 END GMD_RECIPE_DESIGNER_PKG;