1 PACKAGE BODY GMDFMVAL_PUB AS
2 /* $Header: GMDPFMVB.pls 120.13.12010000.2 2008/10/14 11:56:02 kannavar ship $ */
3 /* ============================================= */
4 /* PROCEDURE: */
5 /* get_formula_id */
6 /* */
7 /* DESCRIPTION: */
8 /* This PL/SQL function is responsible for */
9 /* retrieving a formula's surrogate key unique number */
10 /* based on the passed in formula number and formula */
11 /* version and formula type. */
12 /* */
13 /* Formula Number is returned in the xvalue parameter */
14 /* and xreturn_code is 0 (zero) upon success. Failure */
15 /* returns xvalue as NULL and xreturn_code contains the */
16 /* error code. */
17 /* */
18 /* SYNOPSIS: */
19 /* iret := GMDFMVAL_PUB.get_formula_id(pformula_no, */
20 /* pversion, */
21 /* ptype, */
22 /* xvalue, */
23 /* xreturn_code); */
24 /* */
25 /* RETURNS: */
26 /* 0 Success */
27 /* -92200 Formula_id not found. */
28 /* < 0 RDBMS error */
29 /* ============================================= */
30
31 PROCEDURE get_formula_id(pformula_no IN VARCHAR2,
32 pversion IN NUMBER,
33 xvalue OUT NOCOPY NUMBER,
34 xreturn_code OUT NOCOPY NUMBER) IS
35
36 /* Local variables. */
37 /* ================ */
38 l_formula_id fm_form_mst.formula_id%TYPE := 0;
39
40 /* Cursor Definitions. */
41 /* =================== */
42 CURSOR get_id IS
43 SELECT formula_id
44 FROM fm_form_mst
45 WHERE formula_no = UPPER(pformula_no)
46 AND formula_vers = pversion;
47
48 /* ================================================ */
49 BEGIN
50
51 OPEN get_id;
52 FETCH get_id INTO
53 l_formula_id;
54
55 IF (get_id%NOTFOUND) THEN
56 xvalue := NULL;
57 xreturn_code := FMVAL_FORMID_ERR;
58 CLOSE get_id;
59 RETURN;
60 END IF;
61
62 xvalue := l_formula_id;
63 xreturn_code := 0;
64 CLOSE get_id;
65 RETURN;
66
67 EXCEPTION
68 WHEN OTHERS THEN
69
70 RETURN;
71 END get_formula_id;
72
73 /* ====================================== */
74 /* get_formulaline_id */
75 /* This function validates the existence of formulaline_id */
76 /* in the database. */
77 /* Prior to updates the formulaline_id value */
78 /* should exists in the database */
79 /* IN formulaline_id NUMBER */
80 /* OUT NOCOPY return_code NUMBER */
81 /* ==================================================== */
82 PROCEDURE get_formulaline_id(pformulaline_id IN NUMBER,
83 xreturn_code OUT NOCOPY NUMBER) IS
84
85 /* Local variables. */
86 /* ================ */
87 l_formulaline_id fm_matl_dtl.formulaline_id%TYPE := 0;
88
89 /* Cursor Definitions. */
90 /* =================== */
91 CURSOR get_formulalineid IS
92 SELECT formulaline_id
93 FROM fm_matl_dtl
94 WHERE formulaline_id = pformulaline_id;
95
96 /* ================================================ */
97 BEGIN
98 OPEN get_formulalineid;
99 FETCH get_formulalineid INTO
100 l_formulaline_id;
101
102 IF (get_formulalineid%NOTFOUND) THEN
103 xreturn_code := FMVAL_FORMLINEID_ERR;
104 CLOSE get_formulalineid;
105 RETURN;
106 END IF;
107
108 xreturn_code := 0;
109 CLOSE get_formulalineid;
110 RETURN;
111
112 EXCEPTION
113 WHEN OTHERS THEN
114
115 RETURN;
116
117 END get_formulaline_id;
118
119
120 /* ============================================= */
121 /* PROCEDURE: */
122 /* get_item_id */
123 /* */
124 /* DESCRIPTION: */
125 /* This PL/SQL function is responsible for */
126 /* retrieving an item's surrogate key unique number */
127 /* based on the passed in item number. */
128 /* */
129 /* ITEM_ID is returned in the xvalue parameter */
130 /* and xreturn_code is 0 (zero) upon success. Failure */
131 /* returns xvalue as NULL and xreturn_code contains the */
132 /* error code. */
133 /* */
134 /* SYNOPSIS: */
135 /* iret := GMDFMVAL_PUB.get_item_id(pitem_no, */
136 /* xitem_id, */
137 /* xitem_um, */
138 /* xreturn_code); */
139 /* */
140 /* RETURNS: */
141 /* 0 Success */
142 /* -92201 Item Number not found. */
143 /* -92202 Item Number is inactive. */
144 /* -92203 Item Number is Experimental. */
145 /* < 0 RDBMS error */
146 /* ============================================= */
147 PROCEDURE get_item_id(pitem_no IN VARCHAR2,
148 pinventory_item_id IN NUMBER,
149 porganization_id IN NUMBER,
150 xitem_id OUT NOCOPY NUMBER,
151 xitem_um OUT NOCOPY VARCHAR2,
152 xreturn_code OUT NOCOPY NUMBER) IS
153
154 /* Local variables. */
155 /* ================ */
156 l_item_id mtl_system_items_kfv.inventory_item_id%TYPE := 0;
157 l_item_um mtl_system_items_kfv.primary_uom_code%TYPE := NULL;
158 l_enabled_flag mtl_system_items_kfv.enabled_flag%TYPE := 0;
159
160
161 CURSOR get_id IS
162 SELECT inventory_item_id, primary_uom_code, enabled_flag
163 FROM mtl_system_items_kfv
164 WHERE concatenated_segments = pitem_no
165 AND organization_id = porganization_id;
166
167 CURSOR get_it_id IS
168 SELECT inventory_item_id, primary_uom_code, enabled_flag
169 FROM mtl_system_items
170 WHERE inventory_item_id = pinventory_item_id
171 AND organization_id = porganization_id;
172
173 BEGIN
174 IF (pitem_no IS NOT NULL) THEN
175 OPEN get_id;
176 FETCH get_id INTO l_item_id, l_item_um, l_enabled_flag;
177 IF (get_id%NOTFOUND) THEN
178 xitem_id := NULL;
179 xitem_um := NULL;
180 xreturn_code := -1;
181 CLOSE get_id;
182 RETURN;
183 END IF;
184
185 IF (l_enabled_flag = 'N') THEN
186 xitem_id := NULL;
187 xitem_um := NULL;
188 xreturn_code := -1;
189 CLOSE get_id;
190 RETURN;
191 END IF;
192
193 xitem_id := l_item_id;
194 xitem_um := l_item_um;
195 xreturn_code := 0;
196 CLOSE get_id;
197 RETURN;
198 ELSIF (pinventory_item_id IS NOT NULL) THEN
199 OPEN get_it_id;
200 FETCH get_it_id INTO l_item_id, l_item_um, l_enabled_flag;
201 IF (get_it_id%NOTFOUND) THEN
202 xitem_id := NULL;
203 xitem_um := NULL;
204 xreturn_code := -1;
205 CLOSE get_it_id;
206 RETURN;
207 END IF;
208
209 IF (l_enabled_flag = 'N') THEN
210 xitem_id := NULL;
211 xitem_um := NULL;
212 xreturn_code := -1;
213 CLOSE get_it_id;
214 RETURN;
215 END IF;
216
217 xitem_id := l_item_id;
218 xitem_um := l_item_um;
219 xreturn_code := 0;
220
221 CLOSE get_it_id;
222 RETURN;
223 END IF;
224 EXCEPTION
225 WHEN OTHERS THEN
226 RETURN;
227 END get_item_id;
228
229
230 /* ============================================= */
231 /* PROCEDURE: */
232 /* determine_product */
233 /* */
234 /* DESCRIPTION: */
235 /* This PL/SQL function is responsible for */
236 /* retrieving a formula's product item_id and the */
237 /* product's inventory (primary) unit of measure */
238 /* based on the passed in formula ID surrogate. */
239 /* */
240 /* ITEM_ID and ITEM_UM are returned in the xitem_id and */
241 /* xitem_um parameters respectively. */
242 /* and xreturn_code is 0 (zero) upon success. Failure */
243 /* returns values as NULL and xreturn_code contains the */
244 /* error code. */
245 /* */
246 /* SYNOPSIS: */
247 /* iret := GMDFMVAL_PUB.determine_product(formula_id, */
248 /* xitem_id, */
249 /* xitem_um, */
250 /* xreturn_code); */
251 /* */
252 /* RETURNS: */
253 /* 0 Success */
254 /* -92211 Cannot determine product */
255 /* -92212 Cannot determine product's primary UOM */
256 /* < 0 RDBMS error */
257 /* ============================================= */
258 PROCEDURE determine_product(pformula_id IN NUMBER,
259 xitem_id OUT NOCOPY NUMBER,
260 xitem_um OUT NOCOPY VARCHAR2,
261 xreturn_code OUT NOCOPY NUMBER) IS
262
263 /* Local variables. */
264 /* ================ */
265 l_item_id mtl_system_items.inventory_item_id%TYPE := 0;
266 l_um mtl_system_items.primary_uom_code%TYPE := NULL;
267
268 /* Cursor Definitions. */
269 /* =================== */
270 CURSOR get_product IS
271 SELECT inventory_item_id
272 FROM fm_matl_dtl
273 WHERE formula_id = pformula_id
274 AND line_type = 1;
275
276 CURSOR get_uom IS
277 SELECT primary_uom_code
278 FROM mtl_system_items_kfv
279 WHERE inventory_item_id = l_item_id;
280
281 /* ================================================ */
282 BEGIN
283
284 OPEN get_product;
285 FETCH get_product INTO l_item_id;
286 IF (get_product%NOTFOUND) THEN
287 CLOSE get_product;
288 xitem_id := 0;
289 xreturn_code := FMVAL_PRODUCT_FIND_ERR;
290 RETURN;
291 END IF;
292 CLOSE get_product;
293
294 OPEN get_uom;
295 FETCH get_uom INTO l_um;
296
297 IF (get_uom%NOTFOUND) THEN
298 CLOSE get_uom;
299 xitem_um := NULL;
300 xreturn_code := FMVAL_PRODUCT_INVUOM_ERR;
301 RETURN;
302 END IF;
303 CLOSE get_uom;
304
305 /* Assign our values and return */
306 /* ============================ */
307
308 xitem_id := l_item_id;
309 xitem_um := l_um;
310 xreturn_code := 0;
311 RETURN;
312
313 EXCEPTION
314 WHEN OTHERS THEN
315
316 RETURN;
317 END determine_product;
318
319
320 /* ============================================= */
321 /* FUNCTION */
322 /* detail_line_val */
323 /* Parameters passed */
324 /* IN formula_id IN Number */
325 /* line_no In NUMBER */
326 /* line_type IN NUmber */
327 /* */
328 /* RETURNS NUMBER */
329 /* 0 Success */
330 /* */
331 /* Description: */
332 /* Validates before inserting detail line into fm_matl_dtl */
333 /* table. */
334 /* If this record that has same formula_id, line_type , */
335 /* and line_no we cannot make an insert. */
336 /* In such cases we return a non zero value. */
337 /* If no record is found we return a zero */
338 /* =============================================== */
339 FUNCTION detail_line_val(pformula_id NUMBER,
340 pline_no NUMBER,
341 pline_type NUMBER)
342 RETURN NUMBER IS
343
344 /* Local variables. */
345 /* ================ */
346 l_line_existing NUMBER := 0;
347
348 /* Cursor Definitions. */
349 /* =================== */
350 CURSOR detail_line_cur IS
351 SELECT formula_id
352 FROM fm_matl_dtl
353 WHERE formula_id = pformula_id
354 AND line_type = pline_type
355 AND line_no = pline_no;
356
357 /* ================================================ */
358 BEGIN
359 OPEN detail_line_cur;
360 FETCH detail_line_cur INTO
361 l_line_existing;
362
363 IF (detail_line_cur%NOTFOUND) THEN
364 RETURN 0;
365 ELSE
366 RETURN FMVAL_DETAILLINE_ERR;
367 END IF;
368
369 CLOSE detail_line_cur;
370 EXCEPTION
371 WHEN OTHERS THEN
372 RETURN FMVAL_DETAILLINE_ERR;
373 END;
374
375
376 /* ============================================= */
377 /* FUNCTION: */
378 /* formula_class_val */
379 /* */
380 /* DESCRIPTION: */
381 /* This PL/SQL function is responsible for */
382 /* validating a formula class */
383 /* based on the passed in formula class */
384 /* */
385 /* SYNOPSIS: */
386 /* iret := GMDFMVAL_PUB.formula_class_val(pform_class); */
387 /* */
388 /* RETURNS: */
389 /* 0 Success */
390 /* -92206 Formula Class Not Found. */
391 /* < 0 RDBMS error */
392 /* ============================================= */
393 FUNCTION formula_class_val(pform_class VARCHAR2)
394 RETURN NUMBER IS
395
396 /* Local variables. */
397 /* ================ */
398 l_form_class_desc fm_form_cls.formula_class_desc%TYPE;
399
400 /* Cursor Definitions. */
401 /* =================== */
402 CURSOR class_val IS
403 SELECT formula_class_desc
404 FROM fm_form_cls
405 WHERE formula_class = UPPER(pform_class)
406 AND delete_mark = 0;
407
408 /* ================================================ */
409 BEGIN
410
411 OPEN class_val;
412 FETCH class_val INTO
413 l_form_class_desc;
414
415 IF (class_val%NOTFOUND) THEN
416 CLOSE class_val;
417 RETURN FMVAL_CLASS_ERR;
418 END IF;
419
420 CLOSE class_val;
421 RETURN 0;
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 RETURN SQLCODE;
426 END formula_class_val;
427
428
429 /* ============================================= */
430 /* FUNCTION: */
431 /* cost_alloc_val */
432 /* */
433 /* DESCRIPTION: */
434 /* This PL/SQL function is responsible for */
435 /* validating a cost allocation */
436 /* based on the passed in cost allocation amount */
437 /* and the line type of the item. This is available */
438 /* for products only. */
439 /* */
440 /* */
441 /* SYNOPSIS: */
442 /* iret := GMDFMVAL_PUB.cost_alloc_val(pcost_alloc, */
443 /* pline_type); */
444 /* */
445 /* RETURNS: */
446 /* 0 Success */
447 /* -92208 Line is not a product */
448 /* -92209 Cost Allocation percentage error. */
449 /* < 0 RDBMS error */
450 /* ============================================= */
451 FUNCTION cost_alloc_val(pcost_alloc NUMBER,
452 pline_type NUMBER)
453 RETURN NUMBER IS
454
455 /* ================================================ */
456 BEGIN
457
458 IF (pline_type <> 1) THEN
459 RETURN FMVAL_COSTALLOC_ERR;
460 END IF;
461
462 IF (pcost_alloc < 0 OR pcost_alloc > 100) THEN
463 RETURN FMVAL_COSTPCT_ERR;
464 END IF;
465
466 RETURN 0;
467
468 EXCEPTION
469 WHEN OTHERS THEN
470 RETURN SQLCODE;
471 END cost_alloc_val;
472
473
474 /* ============================================= */
475 /* FUNCTION: */
476 /* type_val */
477 /* */
478 /* DESCRIPTION: */
479 /* This PL/SQL function is responsible for */
480 /* validating all formula type columns */
481 /* */
482 /* Valid line type are as follows: */
483 /* -1 Ingredient */
484 /* 1 Finished good */
485 /* 2 ByProduct */
486
487 /* Valid scale type are as follows: */
488 /* 0 - No Scaling */
489 /* 1 - Yes Item should be scalable */
490
491 /* Valid phantom types are as follows: */
492 /* 0 - Not a phantom */
493 /* 1 - automatic phantom replacement */
494 /* 2 - manual phantom */
495 /* */
496 /* Valid release types are as follows: */
497 /* 0 - Automatic */
498 /* 1 - Manual */
499 /* 2 - Incremental */
500 /* */
501 /* Valid formula use types are as follows: */
502 /* 0 - Production */
503 /* 1 - MRP */
504 /* 2 - Costing */
505 /* 3 - MSDS (Material Safety Data Sheets) */
506 /* */
507 /* SYNOPSIS: */
508 /* iret := GMDFMVAL_PUB.formula_class_val(pcolumn_name, */
509 /* pvalue); */
510 /* */
511 /* RETURNS: */
512 /* 0 Success */
513 /* -92207 Formula Class not found. */
514 /* < 0 RDBMS error */
515 /* */
516 /* HISTORY: */
517 /* P.Raghu 18-AUG-2003 Bug#3090630. */
518 /* Changed datatype of pvalue parameter*/
519 /* to VARCHAR2 from NUMBER. */
520 /* ============================================= */
521 --Begin Bug#3090630 P.Raghu
522 --Changed datatype of pvalue parameter to VARCHAR2 from NUMBER.
523 FUNCTION type_val(ptype_name VARCHAR2,
524 pvalue VARCHAR2)
525 RETURN NUMBER IS
526 --End Bug#3090630
527
528 /* Local variables. */
529 /* ================ */
530 l_value fnd_lookups.lookup_code%TYPE;
531
532 /* Cursor Definitions. */
533 /* =================== */
534 CURSOR form_type_val IS
535 SELECT unique(lookup_code)
536 FROM gem_lookups
537 WHERE lookup_type = ptype_name
538 --Begin Bug#3090630 P.Raghu
539 --Removed TO_CHAR function for pvalue parameter.
540 AND lookup_code = pvalue;
541 --End Bug#3090630
542
543 /* ================================================ */
544 BEGIN
545
546 OPEN form_type_val;
547 FETCH form_type_val INTO
548 l_value;
549
550 IF (form_type_val%NOTFOUND) THEN
551 CLOSE form_type_val;
552 RETURN FMVAL_TYPE_ERR;
553 END IF;
554
555 CLOSE form_type_val;
556 RETURN 0;
557
558 EXCEPTION
559 WHEN OTHERS THEN
560 RETURN SQLCODE;
561 END type_val;
562
563 /* ============================================= */
564 /* FUNCTION: */
565 /* GMD_EFFECTIVITY_LOCKED_STATUS */
566 /* */
567 /* DESCRIPTION: */
568 /* This PL/SQL function is responsible for */
569 /* determining if a cost rollup has been done. */
570 /* If it has, returning a 900 denotes that */
571 /* effectivity is not subject to change and is locked */
572 /* down. Otherwise if the rollover indicator in the */
573 /* cm_cmpt_dtl table is zero or does not exist, */
574 /* allow modification to the effectivity. */
575
576 /* SYNOPSIS: */
577 /* iret := GMD_EFFECTIVITY_LOCKED_STATUS(pfmeff_id); */
578 /* */
579 /* RETURNS: */
580 /* 700 Success (effectivity may be changed) */
581 /* 900 Effectivity is locked (update not allowed) */
582 /* ============================================= */
583 FUNCTION GMD_EFFECTIVITY_LOCKED_STATUS(pfmeff_id NUMBER)
584 RETURN VARCHAR2 IS
585
586 l_status VARCHAR2(32) := '700';
587 iret NUMBER;
588
589 CURSOR locked_val_cur(pfmeff_id NUMBER) IS
590 SELECT 1 FROM sys.dual
591 WHERE EXISTS (SELECT 1 FROM cm_cmpt_dtl
592 WHERE fmeff_id = pfmeff_id
593 AND rollover_ind = 1) ;
594 BEGIN
595 OPEN locked_val_cur(pfmeff_id);
596 FETCH locked_val_cur INTO iret;
597 IF (locked_val_cur%NOTFOUND) THEN
598 l_status := '700';
599 ELSE
600 l_status := '900';
601 END IF;
602 CLOSE locked_val_cur;
603
604 RETURN l_status;
605
606 END GMD_EFFECTIVITY_LOCKED_STATUS;
607
608
609
610 /* ============================================= */
611 /* FUNCTION: */
612 /* locked_effectivity_val */
613 /* */
614 /* DESCRIPTION: */
615 /* This PL/SQL function is responsible for */
616 /* determining if a cost rollup has been done. */
617 /* If it has, returning a -92215 denotes that */
618 /* effectivity is not subject to change and is locked */
619 /* down. Otherwise if the rollover indicator in the */
620 /* cm_cmpt_dtl table is zero or does not exist, */
621 /* allow modification to the effectivity. */
622 /* */
623 /* This validation is for CHANGES only! */
624 /* */
625 /* SYNOPSIS: */
626 /* iret := GMDFMVAL_PUB.locked_effectivity_val(pformula_id); */
627 /* */
628 /* RETURNS: */
629 /* 0 Success (effectivity may be changed) */
630 /* -92213 Effectivity is locked (update not allowed) */
631 /* < 0 error */
632 /* ============================================= */
633 FUNCTION locked_effectivity_val(pformula_id NUMBER)
634 RETURN NUMBER IS
635
636 /* Local variables. */
637 /* ================ */
638 l_formula_id NUMBER := 0;
639
640 /* Cursor Definitions. */
641 /* =================== */
642 CURSOR locked_val IS
643 SELECT rcp.formula_id
644 FROM gmd_recipe_validity_rules vr,
645 gmd_recipes_b rcp,
646 cm_cmpt_dtl cost
647 WHERE rcp.formula_id = pformula_id
648 AND vr.recipe_validity_rule_id = cost.fmeff_id
649 AND vr.recipe_id = rcp.recipe_id
650 AND cost.rollover_ind = 1;
651
652 /* ================================================ */
653 BEGIN
654 OPEN locked_val;
655 FETCH locked_val INTO l_formula_id;
656 CLOSE locked_val;
657
658 IF (l_formula_id <> 0) THEN
659 RETURN FMVAL_LOCKED_EFF;
660 ELSE
661 RETURN 0;
662 END IF;
663
664 EXCEPTION
665 WHEN OTHERS THEN
666
667 RETURN SQLCODE;
668 END locked_effectivity_val;
669
670
671 /* ============================================= */
672 /* FUNCTION: */
673 /* convertuom_val */
674 /* */
675 /* DESCRIPTION: */
676 /* This PL/SQL function is responsible for */
677 /* validating that a unit of measure entered */
678 /* for a formula line item can be converted to the item's */
679 /* primary unit of measure (UOM) in the event the */
680 /* formualted UOM of the item is different from */
681 /* the item's primary inventored UOM. */
682 /* */
683 /* SYNOPSIS: */
684 /* iret := GMDFMVAL_PUB.convertuom_val(pitem_id, */
685 /* pfrom_uom, */
686 /* pto_uom); */
687 /* */
688 /* pitem_id the item surrogate (unique number). */
689 /* pfrom_uom the formulated uom code you are working on. */
690 /* pto_uom the items primary inventory unit of measure */
691 /* */
692 /* RETURNS: */
693 /* 0 Success */
694 /* -1 - Package problem. */
695 /* -3 - UM_TYPE and conversion factor for current UOM not found. */
696 /* -4 - UM_TYPE and conversion factor for NEW UOM not found. */
697 /* -5 - Cannot determine INVENTORY UOM for item. */
698 /* -6 - UM_TYPE and conversion factor for INV UOM not found. */
699 /* -7 - Cannot find conversion factor for CURRENT UOM. */
700 /* -10 - Cannot find conversion factor for NEW UOM. */
701 /* -11 - Item_id not passed as a parameter. */
702 /* < 0 RDBMS Oracle Error. */
703 /* ============================================= */
704 FUNCTION convertuom_val(pitem_id NUMBER,
705 pfrom_uom VARCHAR2,
706 pto_uom VARCHAR2)
707 RETURN NUMBER IS
708
709 /* Local variables. */
710 /* ================ */
711 iret NUMBER := 0;
712
713 /* ================================================ */
714 BEGIN
715 iret := INV_CONVERT.inv_um_convert(item_id => pitem_id,
716 precision => 5,
717 from_quantity => 100,
718 from_unit => pfrom_uom,
719 to_unit => pto_uom,
720 from_name => NULL,
721 to_name => NULL);
722 IF (iret < 0) THEN
723 RETURN iret;
724 END IF;
725
726 RETURN 0;
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 IF(ss_debug = 1) THEN
731 null;
732 END IF;
733
734 RETURN SQLCODE;
735 END convertuom_val;
736
737 /* ******************************************************
738 Wrapper for getting formula element specifics
739 Take the elment name that needs to be returned as in Parameter
740 e.g if the pElementName is formulaline it returns all formulaline
741 based on the formula_id
742 ****************************************************** */
743
744 PROCEDURE get_element(pElement_name IN VARCHAR2,
745 pRecord_in IN formula_info_in,
746 xTable_out OUT NOCOPY formula_table_out,
747 xReturn OUT NOCOPY VARCHAR2) IS
748
749 /* Local variables */
750 litem_id mtl_system_items.inventory_item_id%TYPE;
751 litem_um mtl_system_items.primary_uom_code%TYPE;
752 l_return_code NUMBER ;
753 X_count NUMBER := 1;
754
755 l_formula_id NUMBER;
756
757 /* Cursor that returns multiple formulas for */
758 /* an item. */
759 CURSOR get_fmitem_cur(pitem_id IN NUMBER) IS
760 select h.formula_no, h.formula_vers, h.formula_id
761 from fm_form_mst h,
762 fm_matl_dtl d
763 where d.inventory_item_id = pitem_id AND
764 h.formula_id = d.formula_id;
765
766 /* Cursor that returns formulaline_ids for a formula */
767 CURSOR get_fmline_cur(pformula_id IN NUMBER) IS
768 select formulaline_id
769 from fm_matl_dtl
770 where formula_id = pformula_id;
771
772 /* Cursor that formula_no and version for a formula_id */
773 CURSOR get_formula_no(pformula_id IN NUMBER) IS
774 select formula_no, formula_vers
775 from fm_form_mst
776 where formula_id = pformula_id;
777
778 /* Cursor that gets formula_id when formula_no and vers */
779 /* is passed. */
780 CURSOR get_formula_id IS
781 select formula_id
782 from fm_form_mst
783 where formula_no = UPPER(pRecord_in.formula_no)
784 and formula_vers = pRecord_in.formula_vers;
785
786 /* Cursor that returns user info */
787 CURSOR get_user_id IS
788 select user_id
789 from fnd_user
790 where user_name = pRecord_in.user_name;
791
792
793 BEGIN
794
795
796 IF (Upper(pElement_name) = 'FORMULA') THEN
797 /* User might have the formula_id */
798 /* and might need all other formula details */
799 /* for this formula e.g formula_desc */
800 IF (pRecord_in.formula_id IS NOT NULL) THEN
801 OPEN get_formula_no(pRecord_in.formula_id);
802 FETCH get_formula_no
803 into xTable_out(1).formula_no,
804 xTable_out(1).formula_vers;
805
806 xTable_out(1).formula_id := pRecord_in.formula_id;
807
808 If (get_formula_no%NOTFOUND) THEN
809 xReturn := 'E';
810 ELSE
811 xReturn := 'S';
812 END IF;
813 CLOSE get_formula_no;
814 /* User might provide formula_no and version */
815 /* and might need all formula info */
816 ELSIF ((pRecord_in.formula_no IS NOT NULL) AND
817 (pRecord_in.formula_vers IS NOT NULL)) THEN
818
819 OPEN get_formula_id;
820 FETCH get_formula_id INTO xTable_out(1).formula_id;
821
822 xTable_out(1).formula_no := pRecord_in.formula_no;
823 xTable_out(1).formula_vers := pRecord_in.formula_vers;
824
825 If (get_formula_id%NOTFOUND) THEN
826 xReturn := 'E';
827 ELSE
828 xReturn := 'S';
829 END IF;
830 CLOSE get_formula_id;
831 END IF;
832 END IF;
833
834 IF (pElement_name = 'USER') THEN
835
836 OPEN get_user_id;
837 FETCH get_user_id
838 INTO xTable_out(1).user_id;
839
840 IF (get_user_id%NOTFOUND) THEN
841 xReturn := 'E';
842 ELSE
843 xReturn := 'S';
844 END IF;
845 CLOSE get_user_id;
846 END IF;
847
848 IF (pElement_name = 'FORMULALINE') THEN
849
850 /* User passes formula_id info and gets */
851 /* the formulaline info */
852 IF (pRecord_in.formula_id IS NOT NULL) THEN
853
854 FOR get_fmline_rec IN get_fmline_cur(pRecord_in.formula_id) LOOP
855 xTable_out(X_count).formulaline_id := get_fmline_rec.formulaline_id;
856 X_count := X_count + 1;
857 END LOOP;
858
859 IF (get_fmline_cur%NOTFOUND) THEN
860 xReturn := 'E';
861 ELSE
862 xReturn := 'S';
863 END IF;
864
865 END IF;
866
867 END IF; /* End for formulaline condition */
868
869 IF (pElement_name = 'RECIPE') THEN
870
871 /* get the formula_id from gmd_recipes table */
872 IF (pRecord_in.Recipe_id IS NOT NULL) THEN
873 -- get the formula id
874
875 Select formula_id INTO l_formula_id
876 From gmd_recipes
877 Where recipe_id = pRecord_in.Recipe_id;
878
879 OPEN get_formula_no(l_formula_id);
880 FETCH get_formula_no
881 INTO xTable_out(1).formula_no,
882 xTable_out(1).formula_vers;
883 CLOSE get_formula_no;
884 xTable_out(1).formula_id := pRecord_in.formula_id;
885
886 -- get the formula line info
887 FOR get_fmline_rec IN get_fmline_cur(l_formula_id) LOOP
888 xTable_out(X_count).formulaline_id := get_fmline_rec.formulaline_id;
889 X_count := X_count + 1;
890 END LOOP;
891
892 ELSE
893 xReturn := 'E';
894 END IF;
895
896 END IF; /* end for recipe clause */
897
898 END get_element;
899
900
901 /* *************************************************************
902 * Overloaded function that returns the formula header and details
903 * in separate form
904 * Current accepts recipe as the pElement_name
905 ****************************************************************/
906
907 PROCEDURE get_element(pElement_name IN VARCHAR2,
908 pRecord_in IN formula_info_in,
909 pDate IN DATE Default Null, --Bug 4479101
910 xFormulaHeader_rec OUT NOCOPY fm_form_mst%ROWTYPE,
911 xFormulaDetail_tbl OUT NOCOPY formula_detail_tbl,
912 xReturn OUT NOCOPY VARCHAR2) IS
913
914 l_formula_id NUMBER;
915 X_count NUMBER := 1;
916
917 /* Bug 2307820 - Thomas Daniel */
918 /* Added order by condition and changed the cursor to return */
919 /* the entire row instead of just formulaline ids */
920 /* Cursor that returns formulaline details for a formula */
921 CURSOR get_fmline_cur(pformula_id IN NUMBER) IS
922 select *
923 from fm_matl_dtl
924 where formula_id = pformula_id
925 order by line_type, line_no;
926
927 BEGIN
928 -- Initialize the status
929 xReturn := 'S';
930
931 IF (pElement_name = 'RECIPE') THEN
932
933 /* get the formula_id from gmd_recipes table */
934 IF (pRecord_in.Recipe_id IS NOT NULL) THEN
935 -- get the formula id
936
937 SELECT formula_id INTO l_formula_id
938 FROM gmd_recipes
939 WHERE recipe_id = pRecord_in.Recipe_id;
940
941 -- Call substitute item codeset, Bug 4479101
942 get_substitute_items(pFormula_id => l_formula_id,
943 pDate => pDate,
944 xFormulaDetail_tbl => xFormulaDetail_tbl);
945 FOR formula_rec IN (select * from fm_form_mst where formula_id = l_formula_id) LOOP
946 xFormulaHeader_rec := formula_rec;
947 END LOOP;
948 ELSE
949 xReturn := 'E';
950 END IF;
951
952 END IF;
953
954 END get_element;
955
956 --Bug 4479101, Item Substitution
957 PROCEDURE get_substitute_items(pFormula_id in NUMBER,
958 pDate in DATE Default Null,
959 xFormulaDetail_tbl OUT NOCOPY formula_detail_tbl) IS
960
961 CURSOR get_fmline_cur(vformula_id IN NUMBER) IS
962 select *
963 from fm_matl_dtl
964 where formula_id = vformula_id
965 order by line_type, line_no;
966
967 X_count Number := 0;
968 xFormulaDetail_output_tbl formula_detail_tbl;
969 BEGIN
970 -- get the formula line info
971 FOR get_fmline_rec IN get_fmline_cur(pformula_id) LOOP
972 X_count := X_count + 1;
973 IF ((get_fmline_rec.line_type = -1) AND
974 ((pDate IS NOT NULL) AND (pDate >= get_fmline_rec.ingredient_end_date))) THEN
975 get_substitute_line_item( pFormulaline_id => get_fmline_rec.formulaline_id
976 ,pItem_id => get_fmline_rec.inventory_item_id
977 ,pQty => get_fmline_rec.qty
978 ,pUom => get_fmline_rec.detail_uom
979 ,pScale_multiple => get_fmline_rec.scale_multiple
980 ,pDate => pDate
981 ,xFormulaDetail_tbl => xFormulaDetail_output_tbl);
982
983 -- Assign each record of the formula line ouput table
984 -- In Sanofi's case this should return only one row
985 For j IN 1 .. xFormulaDetail_output_tbl.count Loop
986 xFormulaDetail_tbl(X_count) := xFormulaDetail_output_tbl(j);
987 End Loop;
988 ELSE
989 xFormulaDetail_tbl(X_count) := get_fmline_rec;
990 END IF;
991
992 END LOOP;
993 END get_substitute_items;
994
995 PROCEDURE get_substitute_line_item(pFormulaline_id in NUMBER,
996 pItem_id in Number Default Null,
997 pQty in Number Default Null,
998 pUom in Varchar2 Default Null,
999 pScale_multiple in Number Default Null,
1000 pDate in DATE,
1001 xFormulaDetail_tbl Out NOCOPY formula_detail_tbl) IS
1002
1003 CURSOR Cur_retrieve_fmline(vFormulaline_id Number) IS
1004 SELECT *
1005 FROM fm_matl_dtl
1006 WHERE formulaline_id = vFormulaline_id;
1007
1008 CURSOR get_item_substitution (vFormula_id Number
1009 ,vItem_id Number
1010 ,vDate Date) IS
1011 SELECT *
1012 FROM gmd_material_effectivities_vw vw
1013 WHERE vw.formula_id = vFormula_id
1014 AND vw.line_item_id = vItem_id
1015 AND vw.start_date <= vDate
1016 AND (vw.end_date IS NULL OR
1017 vw.end_date >= vDate)
1018 ORDER BY vw.preference asc, vw.start_date;
1019
1020 CURSOR get_replacement_factor (vFormula_id Number
1021 ,vline_item_id Number
1022 ,vSubstitute_item_id Number) IS
1023
1024 SELECT hdr.original_qty, dtl.unit_qty, hdr.original_uom
1025 FROM gmd_formula_substitution fmsub
1026 ,gmd_item_substitution_hdr_b hdr
1027 ,gmd_item_substitution_dtl dtl
1028 Where fmsub.formula_id = vFormula_id
1029 and fmsub.substitution_id = hdr.substitution_id
1030 and hdr.substitution_id = dtl.substitution_id
1031 and hdr.original_inventory_item_id = vLine_Item_id
1032 and hdr.substitution_status between 700 and 799
1033 and fmsub.associated_flag = 'Y'
1034 and dtl.inventory_item_id = vSubstitute_item_id
1035 Order by hdr.preference asc, hdr.start_date;
1036
1037 X_count Number := 1;
1038 X_SubstituteExists Boolean := False;
1039
1040 l_batch_scale_factor NUMBER := 1;
1041 l_old_replacement_factor NUMBER := 1;
1042 l_sub_original_qty NUMBER;
1043 l_sub_replace_qty NUMBER;
1044 l_original_item_uom VARCHAR2(4);
1045
1046 get_fmline_rec fm_matl_dtl%ROWTYPE;
1047 l_new_qty NUMBER;
1048 l_integer_scale_ratio NUMBER := 1;
1049
1050 l_subst_recs NUMBER := 0; /* Added in Bug No.7460898 */
1051
1052
1053 BEGIN
1054 -- Considering item substitution for each formula line
1055 OPEN Cur_retrieve_fmline(pFormulaline_id);
1056 FETCH Cur_retrieve_fmline INTO get_fmline_rec;
1057 CLOSE Cur_retrieve_fmline;
1058
1059 -- Initially assign formula line record output table
1060 -- Data in this table can be manipulated later
1061 xFormulaDetail_tbl(X_count) := get_fmline_rec;
1062
1063 -- We consider subtitution only formula line end date
1064 -- has a end date. If the end date is NULL it implies that
1065 -- this ingredient has no substitution list associated to it
1066 IF ((get_fmline_rec.ingredient_end_date IS NOT NULL) AND
1067 ((pDate IS NOT NULL) AND (pDate >= get_fmline_rec.ingredient_end_date))) THEN
1068 -- get the substitute item/items info for a specific date (pDate)
1069 -- since the cursor select is sorted based on preference we
1070 -- need to process only the top most row.
1071 FOR get_item_subs_rec IN get_item_substitution(get_fmline_rec.formula_id
1072 ,get_fmline_rec.inventory_item_id
1073 ,pDate) LOOP
1074
1075 X_SubstituteExists := True;
1076
1077
1078
1079 -- replace formulaline ingredient with substitute item
1080 xFormulaDetail_tbl(X_count).inventory_item_id := get_item_subs_rec.item_id;
1081 xFormulaDetail_tbl(X_count).detail_uom := get_item_subs_rec.replacement_uom;
1082
1083 -- Bug 4549316 started KSHUKLA
1084 /* IF NVL(pQty,0) > 0 THEN
1085 xFormulaDetail_tbl(X_count).qty :=
1086 get_line_qty (P_line_item_id => get_item_subs_rec.line_item_id
1087 ,P_organization_id => get_item_subs_rec.organization_id
1088 ,P_formula_qty => pQty
1089 ,P_formula_uom => get_item_subs_rec.line_item_uom
1090 ,P_replacement_Item => get_item_subs_rec.item_id
1091 ,P_original_item_qty => get_item_subs_rec.sub_original_qty
1092 ,P_original_item_uom => get_item_subs_rec.line_item_primary_uom
1093 ,P_replace_unit_qty => get_item_subs_rec.sub_replace_qty
1094 ,P_replace_unit_uom => get_item_subs_rec.substitution_item_uom
1095 ,P_replacement_uom => get_item_subs_rec.replacement_uom);
1096
1097 l_integer_scale_ratio := xFormulaDetail_tbl(X_count).qty / pQty;
1098
1099 ELSIF get_fmline_rec.qty > 0 THEN
1100 xFormulaDetail_tbl(X_count).qty :=
1101 get_line_qty (P_line_item_id => get_item_subs_rec.line_item_id
1102 ,P_organization_id => get_item_subs_rec.organization_id
1103 ,P_formula_qty => get_item_subs_rec.line_item_qty
1104 ,P_formula_uom => get_item_subs_rec.line_item_uom
1105 ,P_replacement_Item => get_item_subs_rec.item_id
1106 ,P_original_item_qty => get_item_subs_rec.sub_original_qty
1107 ,P_original_item_uom => get_item_subs_rec.line_item_primary_uom
1108 ,P_replace_unit_qty => get_item_subs_rec.sub_replace_qty
1109 ,P_replace_unit_uom => get_item_subs_rec.substitution_item_uom
1110 ,P_replacement_uom => get_item_subs_rec.replacement_uom);
1111
1112 l_integer_scale_ratio := xFormulaDetail_tbl(X_count).qty / get_item_subs_rec.line_item_qty;
1113
1114
1115 ELSE
1116 xFormulaDetail_tbl(X_count).qty := 0;
1117
1118 END IF; */
1119
1120 /* Bug No.6667241 - Start */
1121 IF get_fmline_rec.qty > 0 THEN
1122 xFormulaDetail_tbl(X_count).qty :=
1123 get_line_qty (P_line_item_id => get_item_subs_rec.line_item_id
1124 ,P_organization_id => get_item_subs_rec.organization_id
1125 ,P_formula_qty => get_item_subs_rec.line_item_qty
1126 ,P_formula_uom => get_item_subs_rec.line_item_uom
1127 ,P_replacement_Item => get_item_subs_rec.item_id
1128 ,P_original_item_qty => get_item_subs_rec.sub_original_qty
1129 ,P_original_item_uom => get_item_subs_rec.line_item_primary_uom
1130 ,P_replace_unit_qty => get_item_subs_rec.sub_replace_qty
1131 ,P_replace_unit_uom => get_item_subs_rec.substitution_item_uom
1132 ,P_replacement_uom => get_item_subs_rec.replacement_uom);
1133
1134 l_integer_scale_ratio := xFormulaDetail_tbl(X_count).qty / get_item_subs_rec.line_item_qty;
1135 ELSIF NVL(pQty,0) > 0 THEN
1136 xFormulaDetail_tbl(X_count).qty :=
1137 get_line_qty (P_line_item_id => get_item_subs_rec.line_item_id
1138 ,P_organization_id => get_item_subs_rec.organization_id
1139 ,P_formula_qty => pQty
1140 ,P_formula_uom => get_item_subs_rec.line_item_uom
1141 ,P_replacement_Item => get_item_subs_rec.item_id
1142 ,P_original_item_qty => get_item_subs_rec.sub_original_qty
1143 ,P_original_item_uom => get_item_subs_rec.line_item_primary_uom
1144 ,P_replace_unit_qty => get_item_subs_rec.sub_replace_qty
1145 ,P_replace_unit_uom => get_item_subs_rec.substitution_item_uom
1146 ,P_replacement_uom => get_item_subs_rec.replacement_uom);
1147
1148 l_integer_scale_ratio := xFormulaDetail_tbl(X_count).qty / pQty;
1149 ELSE
1150 xFormulaDetail_tbl(X_count).qty := 0;
1151
1152 END IF;
1153
1154 /* Bug No.6667241 - End */
1155
1156
1157 IF (xFormulaDetail_tbl(X_count).Scale_multiple IS NOT NULL) THEN
1158 xFormulaDetail_tbl(X_count).Scale_multiple
1159 := xFormulaDetail_tbl(X_count).Scale_multiple * l_integer_scale_ratio;
1160 END IF;
1161
1162 -- Extended support for batch call
1163 -- During batch update - batch material line items are chceked
1164 -- for any potential subtitutes.
1165 -- Again there are few ways that batch could call this procedure
1166
1167 -- Possibility 1
1168 -- Parameter pItem_id that batch passes is the original item
1169 -- and there is a substitute for this original item
1170 IF (pItem_id IS NOT NULL) THEN
1171 IF (get_fmline_rec.inventory_item_id = pItem_id) AND
1172 (get_fmline_rec.qty > 0) THEN
1173
1174 -- Get batch scale factor
1175 -- example -
1176 -- 1 lb Sugar <=> 10 Pack Neutra Sweet
1177 -- Formula ingredient => 100 Kg of Sugar
1178
1179 -- Batch scale factor = batch qty to formula ingredient uom (Kg) /formula qty
1180 -- Batch scale factor = 500 Kg / 100 kg = 5
1181 IF (pQty IS NOT NULL) THEN
1182 l_batch_scale_factor := pQty / get_fmline_rec.qty ;
1183 xFormulaDetail_tbl(X_count).qty := xFormulaDetail_tbl(X_count).qty
1184 * l_batch_scale_factor;
1185 END IF;
1186
1187 -- Possibility 2
1188 -- Batch passes pItem_id which is same as substitute item so in this
1189 -- case we pass back batch item details like qty, uom, scale_muliple etc
1190 -- without any change.
1191 -- For example -
1192 -- 1 lb Sugar <=> 10 Pack Neutra Sweet
1193 -- Batch Passes In pItem_id = Neutra Sweet
1194 -- Since the substitute item for Sugar (Neutra Sweet)
1195 -- hasnt changed we pass back details without any change
1196 ELSIF (get_item_subs_rec.item_id = pItem_id) THEN
1197 IF (pQty IS NOT NULL) THEN
1198 xFormulaDetail_tbl(X_count).qty := pQty;
1199 END IF;
1200 IF (pUom IS NOT NULL) THEN
1201 xFormulaDetail_tbl(X_count).detail_uom := pUom;
1202 END IF;
1203 IF (pScale_multiple IS NOT NULL) THEN
1204 xFormulaDetail_tbl(X_count).Scale_multiple := pScale_multiple;
1205 END IF;
1206
1207 -- Possibility 3
1208 -- Batch passes pItem_id which is different from current substitute item
1209 -- in this case we need to find the ratio of pItem_id (old substitute) qty
1210 -- to formula original ingredient qty
1211
1212 -- example -
1213 -- 1 lb Sugar <=> 10 Pack Neutra Sweet from 05/01/2005 to 05/30/2005
1214 -- 1 lb Sugar <=> 5 Each Splenda from 04/01/2005 to 04/30/2005
1215 -- Formula ingredient => 100 Kg of Sugar
1216
1217 -- Run 1
1218 -- Batch runs update batch routine on 05/15/2005 (pDate)
1219 -- It would replace Sugar with Neutra Sweet
1220 -- After scaling batch qty, the batch details are
1221 -- Batch Item = Neutra Sweet, qty 10000 (scaled it by 5), Uom = Pack
1222
1223 -- Run 2
1224 -- Now Batch runs update batch routine again on 04/15/2005 (pDate)
1225 -- The actual substitute for sugar is Splenda and not Neutra sweet
1226 -- Batch Passes In pItem_id = Neutra Sweet, Qty = 10000, Uom = Pack
1227 -- Batch scale factor = batch qty to formula ingredient uom (Kg) /formula qty
1228 -- Batch qty to formula ingr in Kg = 10000 * (original qty of Sugar/ unit qty of Neutra )
1229 -- Batch qty to formula ingr in Kg = 10000 * (1/10) = 1000 lb = 500 Kg
1230 -- Batch scale factor = 500 Kg / 100 kg = 5
1231 ELSIF (get_item_subs_rec.item_id <> pItem_id) AND
1232 (get_fmline_rec.qty > 0) THEN
1233 -- Step 1 : To find Batch qty in original ingredient uom
1234 -- if batch qty uom is expressed in terms of substitute uom
1235 -- i.e replacement_uom_type = 2
1236 -- then we need to find replacement factor between original
1237 -- ingredient and batch item (which is the substitute item)
1238 OPEN get_replacement_factor (vFormula_id => get_fmline_rec.formula_id
1239 ,vline_item_id => get_fmline_rec.inventory_item_id
1240 ,vSubstitute_item_id => pItem_id);
1241 FETCH get_replacement_factor INTO l_sub_original_qty
1242 ,l_sub_replace_qty
1243 ,l_original_item_uom;
1244 CLOSE get_replacement_factor;
1245
1246 IF ((l_sub_original_qty IS NOT NULL) AND (l_sub_original_qty <> 0)) THEN
1247 l_old_replacement_factor := l_sub_replace_qty / l_sub_original_qty;
1248 END IF;
1249
1250 -- Batch qty in terms of original ingredient qty
1251 l_batch_scale_factor :=
1252 inv_convert.inv_um_convert (item_id => pItem_id,
1253 lot_number => NULL,
1254 organization_id => get_fmline_rec.organization_id,
1255 precision => 5,
1256 from_quantity => (pQty/l_old_replacement_factor),
1257 from_unit => l_original_item_uom,
1258 to_unit => get_fmline_rec.detail_uom,
1259 from_name => NULL,
1260 to_name => NULL)
1261 / get_fmline_rec.qty;
1262
1263 --Step 2 : Find the new qty and its scale multiple
1264 IF (pQty IS NOT NULL) THEN
1265 xFormulaDetail_tbl(X_count).qty := xFormulaDetail_tbl(X_count).qty
1266 * l_batch_scale_factor;
1267 END IF;
1268 END IF; -- (get_item_subs_rec.line_item_id = pItem_id)
1269 END IF; -- pItem_id IS NOT NULL
1270 EXIT WHEN X_SubstituteExists;
1271 END LOOP; -- end loop for get_item_substitution
1272 /* Bug No.7460898 - Start (Added the following code based on 11i fixes 6774787 and 7226993 ) */
1273 ELSE -- No Substitution exists for this item
1274
1275 --Check substitution records for the formulaline_id.
1276 SELECT count(*)
1277 INTO l_subst_recs
1278 FROM gmd_material_effectivities_vw
1279 WHERE formula_id = get_fmline_rec.formula_id
1280 AND formulaline_id = get_fmline_rec.formulaline_id;
1281
1282 IF l_subst_recs <= 1 THEN
1283 xFormulaDetail_tbl(X_count).item_id := pItem_id;
1284 END IF;
1285
1286 IF (pQty IS NOT NULL) THEN
1287 xFormulaDetail_tbl(X_count).qty := pQty;
1288 END IF;
1289
1290 IF (pUom IS NOT NULL) THEN
1291 xFormulaDetail_tbl(X_count).item_um := pUom;
1292 END IF;
1293
1294 IF (pScale_multiple IS NOT NULL) THEN
1295 xFormulaDetail_tbl(X_count).Scale_multiple := pScale_multiple;
1296 END IF;
1297
1298 /* Bug No.7460898 - End */
1299 END IF; -- when end_date not null
1300
1301 END get_substitute_line_item;
1302 -- END BUG 4549316 KSHUKLA
1303
1304 PROCEDURE Copy_Formula_Substitution_list(pOldFormula_id NUMBER
1305 ,pNewFormula_id NUMBER
1306 ,xReturn_Status OUT NOCOPY VARCHAR2
1307 , p_create_new_version VARCHAR2 DEFAULT 'N') IS
1308
1309 -- Bug# 5354649 Kapil M
1310 -- Bug 5394909 select all except obsoleted substitutions
1311 CURSOR get_substitution IS
1312 SELECT fm.*
1313 FROM gmd_formula_substitution fm
1314 WHERE formula_id = pOldFormula_id
1315 AND associated_flag = 'Y'
1316 AND NOT EXISTS (SELECT 1
1317 FROM gmd_formula_substitution
1318 WHERE formula_id = pNewFormula_id)
1319 AND EXISTS (SELECT 1
1320 FROM gmd_item_substitution_hdr_b subs
1321 WHERE subs.substitution_id = fm.substitution_id
1322 AND (subs.end_date IS NULL OR subs.end_date > SYSDATE)
1323 AND ( subs.substitution_status between 700 and 799
1324 OR subs.substitution_status between 900 and 999));
1325
1326 -- Bug 5394532
1327 CURSOR get_substitution_orgn(c_substitution_id NUMBER) IS
1328 SELECT owner_organization_id
1329 FROM gmd_item_substitution_hdr_b im
1330 WHERE substitution_id = c_substitution_id;
1331
1332
1333 x_versioning NUMBER := 0;
1334 x_new_substitution_id NUMBER;
1335 X_status VARCHAR2(10) := 'N';
1336 x_newformsubs_id NUMBER;
1337 l_NewFormula_id NUMBER;
1338 -- Bug# 5354649 Kapil M
1339 x_ret_status VARCHAR2(1);
1340 -- Bug 5394532
1341 l_default_subs_status gmd_api_grp.status_rec_type;
1342 l_orgn_id NUMBER;
1343 x_msg_count NUMBER;
1344 x_msg_data VARCHAR2(2000);
1345 BEGIN
1346 SAVEPOINT Copy_Formula_Substitution;
1347 xReturn_Status := 'S';
1348
1349 -- Bug# 5354649 Kapil M
1350 -- Handle Item substitution version control
1351 FOR subs_rec IN get_substitution LOOP
1352 x_versioning := 0;
1353
1354 X_status := gmd_common_val.version_control_state('SUBSTITUTION', subs_rec.substitution_id );
1355 IF (X_status = 'Y' OR (X_status = 'O' AND p_create_new_version = 'Y' ))THEN
1356 x_versioning := 1;
1357 END IF;
1358 IF x_versioning = 1 THEN
1359 gmd_version_control.create_substitution(p_substitution_id => subs_rec.substitution_id,
1360 x_substitution_id => x_new_substitution_id);
1361 -- Bug 5394532
1362 OPEN get_substitution_orgn(x_new_substitution_id);
1363 FETCH get_substitution_orgn INTO l_orgn_id;
1364 CLOSE get_substitution_orgn;
1365
1366 GMD_API_GRP.get_status_details (V_entity_type => 'SUBSTITUTION',
1367 V_orgn_id => l_orgn_id,
1368 X_entity_status => l_default_subs_status);
1369
1370 IF (l_default_subs_status.entity_status <> 100) THEN
1371 gmd_status_pub.modify_status( p_api_version => 1
1372 , p_init_msg_list => TRUE
1373 , p_entity_name => 'SUBSTITUTION'
1374 , p_entity_id => x_new_substitution_id
1375 , p_entity_no => NULL
1376 , p_entity_version => NULL
1377 , p_to_status => l_default_subs_status.entity_status
1378 , p_ignore_flag => FALSE
1379 , x_message_count => x_msg_count
1380 , x_message_list => x_msg_data
1381 , x_return_status => x_ret_status);
1382 END IF;
1383 ELSE
1384 x_new_substitution_id := subs_rec.substitution_id;
1385 END IF;
1386 SELECT gmd_formula_substitution_s.nextval INTO x_newformsubs_id FROM DUAL;
1387
1388 -- End of bug# 5354649
1389
1390 -- Copy all substitution list specific to old formula into
1391 -- new formula
1392 INSERT INTO gmd_formula_substitution
1393 ( formula_substitution_id
1394 ,substitution_id
1395 ,formula_id
1396 ,associated_flag
1397 ,created_by
1398 ,creation_date
1399 ,last_updated_by
1400 ,last_update_login
1401 ,last_update_date )
1402 VALUES(
1403 x_newformsubs_id
1404 ,x_new_substitution_id
1405 ,pNewFormula_id
1406 ,subs_rec.associated_flag
1407 ,fnd_global.user_id
1408 ,SYSDATE
1409 ,fnd_global.user_id
1410 ,fnd_global.user_id
1411 ,SYSDATE);
1412 IF (SQL%NOTFOUND) THEN
1413 xReturn_Status := 'E';
1414 RAISE no_data_found;
1415 END IF;
1416 END LOOP;
1417
1418 EXCEPTION
1419 WHEN OTHERS THEN
1420 ROLLBACK TO Copy_Formula_Substitution;
1421 xReturn_Status := 'U';
1422
1423 END Copy_Formula_Substitution_list;
1424
1425 FUNCTION get_line_qty ( P_line_item_id in NUMBER
1426 ,P_organization_id in NUMBER
1427 ,P_formula_qty in NUMBER
1428 ,P_formula_uom in VARCHAR2
1429 ,P_replacement_Item in NUMBER
1430 ,P_original_item_qty in NUMBER
1431 ,P_original_item_uom in VARCHAR2
1432 ,P_replace_unit_qty in NUMBER
1433 ,P_replace_unit_uom in VARCHAR2
1434 ,P_replacement_uom in VARCHAR2) RETURN NUMBER IS
1435 l_subs_to_original_qty_ratio NUMBER;
1436 l_subs_qty_in_replacement_uom NUMBER;
1437 l_uconv_from_puom_to_fmuom NUMBER;
1438 BEGIN
1439 l_subs_to_original_qty_ratio :=
1440 (P_replace_unit_qty / P_original_item_qty);
1441
1442 l_uconv_from_puom_to_fmuom :=
1443 inv_convert.inv_um_convert (item_id => P_line_item_id,
1444 lot_number => NULL,
1445 organization_id => P_organization_id,
1446 precision => 5,
1447 from_quantity => P_formula_qty,
1448 from_unit => P_formula_uom,
1449 to_unit => P_formula_uom,
1450 from_name => NULL,
1451 to_name => NULL);
1452 l_subs_qty_in_replacement_uom := l_uconv_from_puom_to_fmuom
1453 * l_subs_to_original_qty_ratio;
1454
1455 IF (P_original_item_uom = P_replacement_uom) THEN
1456 RETURN inv_convert.inv_um_convert (
1457 item_id => P_replacement_Item,
1458 lot_number => NULL,
1459 organization_id => P_organization_id,
1460 precision => 5,
1461 from_quantity => l_subs_qty_in_replacement_uom,
1462 from_unit => P_replace_unit_uom,
1463 to_unit => P_formula_uom,
1464 from_name => NULL,
1465 to_name => NULL);
1466 ELSE
1467 RETURN l_subs_qty_in_replacement_uom;
1468 END IF; -- end if (l_primary_uom != get_item_subs_rec.item_uom)
1469 END get_line_qty;
1470
1471 PROCEDURE check_rework_type( pType_value IN VARCHAR2,
1472 xReturn IN OUT NOCOPY VARCHAR2) IS
1473 iret NUMBER;
1474 check_type_exception EXCEPTION;
1475 BEGIN
1476 /* Use the function type_val to validate the */
1477 /* field type. Checks if it exists in GEM_LOOKUPS */
1478 iret := GMDFMVAL_PUB.type_val( 'REWORK_TYPE',pType_value);
1479
1480 If (iret <> 0) THEN
1481 xReturn := 'E';
1482 RAISE check_type_exception;
1483 ELSE
1484 xReturn := 'S';
1485 END IF;
1486
1487 EXCEPTION
1488 when check_type_exception then
1489 If (p_called_from_forms = 'YES') then
1490 fnd_message.set_name('GMD','FM_INVALID_REWORK_TYPE');
1491 APP_EXCEPTION.RAISE_EXCEPTION;
1492 Else
1493 xReturn := 'E';
1494 END IF;
1495 END check_rework_type;
1496
1497
1498 /* *******************************************************
1499 Wrapper for all validation functionality of formula
1500 insert detail record.
1501 ****************************************************** */
1502
1503 PROCEDURE validate_insert_record(P_formula_dtl IN GMD_FORMULA_COMMON_PUB.formula_insert_rec_type,
1504 X_formula_dtl OUT NOCOPY GMD_FORMULA_COMMON_PUB.formula_insert_rec_type,
1505 xReturn OUT NOCOPY VARCHAR2) IS
1506 --Cursor Declaration
1507 CURSOR Cur_get_revision (Vitem_id NUMBER, Vorgn_id NUMBER) IS
1508 SELECT revision_qty_control_code
1509 FROM mtl_system_items
1510 WHERE inventory_item_id = Vitem_id
1511 AND organization_id = Vorgn_id;
1512 -- Bug 5350197 Added
1513 CURSOR Cur_get_serial_control (Vitem_id NUMBER, Vorgn_id NUMBER) IS
1514 SELECT serial_number_control_code
1515 FROM mtl_system_items
1516 WHERE inventory_item_id = Vitem_id
1517 AND organization_id = Vorgn_id;
1518
1519 CURSOR Cur_check_revision (Vitem_id NUMBER, Vorgn_id NUMBER, V_revision VARCHAR2) IS
1520 SELECT 1
1521 FROM mtl_item_revisions
1522 WHERE inventory_item_id = Vitem_id
1523 AND organization_id = Vorgn_id
1524 AND revision = V_revision;
1525 --Variable declaration
1526 l_ret NUMBER;
1527 lItem_id NUMBER;
1528 l_temp NUMBER;
1529 l_revision NUMBER;
1530 lItem_um mtl_system_items.primary_uom_code%type;
1531 l_serial_control NUMBER; -- Bug 5350197
1532
1533 --Exceptions
1534 check_buffer_ind_exception EXCEPTION;
1535 check_line_no_exception EXCEPTION;
1536 check_qty_exception EXCEPTION;
1537 check_cost_alloc_exception EXCEPTION;
1538 Inv_line_type EXCEPTION;
1539 Inv_Byprod_Type EXCEPTION;
1540 check_type_exception EXCEPTION;
1541 check_rel_type_exception EXCEPTION;
1542 check_line_type_exception EXCEPTION;
1543 check_scale_type_exception EXCEPTION;
1544 check_item_um_exception EXCEPTION;
1545 check_revision_exception EXCEPTION;
1546 no_org_access EXCEPTION;
1547 check_no_revision_exception EXCEPTION;
1548 check_serial_exception EXCEPTION; -- Bug 5350197
1549
1550 BEGIN
1551 X_formula_dtl := P_formula_dtl;
1552 -- Organization accessiblity check
1553 if gmd_api_grp.OrgnAccessible(X_formula_dtl.owner_organization_id)= TRUE then
1554 xReturn := 'S';
1555 ELSE
1556 xReturn := 'E';
1557 RAISE no_org_access;
1558 END IF;
1559 --Buffer_ind validation
1560 IF (X_formula_dtl.line_type = - 1) THEN
1561 IF (NVL(X_formula_dtl.buffer_ind,0) in (1,0) ) THEN
1562 xReturn := 'S';
1563 ELSE
1564 xReturn := 'E';
1565 RAISE check_buffer_ind_exception;
1566 END IF;
1567 END IF;
1568
1569 --Line no validation
1570 IF (X_formula_dtl.line_no IS NULL) THEN
1571 xReturn := 'E';
1572 RAISE check_line_no_exception;
1573 ELSIF (X_formula_dtl.line_no <= 0) THEN
1574 xReturn := 'E';
1575 RAISE check_line_no_exception;
1576 ELSE
1577 xReturn := 'S';
1578 END IF;
1579
1580 --Qty Validation
1581 IF (to_number(X_formula_dtl.qty) < 0) THEN
1582 xReturn := 'E';
1583 RAISE check_qty_exception;
1584 ELSE
1585 xReturn := 'S';
1586 END IF;
1587
1588 --Cost Alloc check
1589 IF (X_formula_dtl.line_type = 1) THEN
1590 IF (X_formula_dtl.cost_alloc < 0 OR X_formula_dtl.cost_alloc > 100) THEN
1591 xReturn := 'E';
1592 RAISE check_cost_alloc_exception;
1593 ELSE
1594 xReturn := 'S';
1595 END IF;
1596 ELSE
1597 X_formula_dtl.cost_alloc := NULL;
1598 END IF;
1599
1600 --By product type validation
1601 IF (X_formula_dtl.line_type = 2) THEN
1602 Xreturn := FND_API.g_ret_sts_success;
1603 IF X_formula_dtl.by_product_type IS NOT NULL THEN
1604 --The value for by_product_type is only applicable for byproducts
1605 IF (X_formula_dtl.line_type <> 2) THEN
1606 RAISE inv_line_type;
1607 ELSE
1608 l_ret := GMDFMVAL_PUB.type_val(ptype_name => 'GMD_BY_PRODUCT_TYPE',
1609 Pvalue => X_formula_dtl.by_product_type);
1610 IF l_ret <> 0 THEN
1611 RAISE Inv_Byprod_Type;
1612 ELSE
1613 IF (X_formula_dtl.by_product_type = 'S') THEN
1614 X_formula_dtl.release_type := 1;
1615 END IF;
1616 END IF;
1617 END IF;
1618 END IF;
1619 ELSE
1620 X_formula_dtl.by_product_type := NULL;
1621 END IF;
1622
1623 --Phantom_type validation
1624 l_ret := GMDFMVAL_PUB.type_val('PHANTOM_TYPE',X_formula_dtl.phantom_type);
1625
1626 IF (l_ret <> 0) THEN
1627 xReturn := 'E';
1628 RAISE check_type_exception;
1629 ELSE
1630 xReturn := 'S';
1631 END IF;
1632
1633 --Line Type validation
1634 IF (X_formula_dtl.line_type IS NULL) THEN
1635 xReturn := 'E';
1636 RAISE check_line_type_exception;
1637 ELSE
1638 xReturn := 'S';
1639 END IF;
1640
1641 IF (xReturn = 'S') then
1642 l_ret := GMDFMVAL_PUB.type_val('LINE_TYPE',X_formula_dtl.line_type);
1643 IF (l_ret < 0) then
1644 xReturn := 'E';
1645 RAISE check_line_type_exception;
1646 ELSE
1647 xReturn := 'S';
1648 END IF;
1649 END IF;
1650
1651 --Release Type validation
1652 l_ret := GMDFMVAL_PUB.type_val('RELEASE_TYPE',X_formula_dtl.release_type);
1653
1654 IF (l_ret <> 0) THEN
1655 xReturn := 'E';
1656 RAISE check_rel_type_exception;
1657 ELSE
1658 xReturn := 'S';
1659 END IF;
1660
1661 --Scale Type validation
1662 l_ret := GMDFMVAL_PUB.type_val('SCALE_TYPE',X_formula_dtl.scale_type_dtl);
1663
1664 IF (l_ret <> 0) THEN
1665 xReturn := 'E';
1666 RAise check_scale_type_exception;
1667 ELSE
1668 xReturn := 'S';
1669 END IF;
1670
1671 --Item Uom Validation
1672 IF (X_formula_dtl.inventory_item_id IS NOT NULL) then
1673 --Based on the item_id get its primary UOM
1674 GMDFMVAL_PUB.get_item_id(pitem_no => X_formula_dtl.Item_no,
1675 pinventory_item_id => X_formula_dtl.inventory_item_id,
1676 porganization_id => X_formula_dtl.owner_organization_id,
1677 xitem_id => lItem_id,
1678 xitem_um => lItem_um,
1679 xreturn_code => l_ret);
1680
1681 /* Inavlid item used */
1682 IF (X_formula_dtl.detail_uom IS NOT NULL) THEN
1683 IF (l_ret < 0) THEN
1684 xReturn := 'E';
1685 END IF;
1686
1687 --Based on item_id, primary UOM,formula item UOM check if it can be converted.
1688 l_ret := INV_CONVERT.inv_um_convert (item_id => litem_id
1689 ,precision => 5
1690 ,from_quantity => 100
1691 ,from_unit => X_formula_dtl.detail_uom
1692 ,to_unit => lItem_um
1693 ,from_name => NULL
1694 ,to_name => NULL);
1695 IF (l_ret < 0) THEN
1696 xReturn := 'E';
1697 Raise check_item_um_exception;
1698 ELSE
1699 xReturn := 'S';
1700 END IF;
1701 ELSE
1702 X_formula_dtl.detail_uom := litem_um;
1703 END IF;
1704 END IF;
1705
1706 --Revision Validation
1707 IF (X_formula_dtl.inventory_item_id IS NOT NULL) then
1708 -- bug 5350197 Serail control items are not allowed
1709 OPEN Cur_get_serial_control(X_formula_dtl.inventory_item_id, X_formula_dtl.owner_organization_id);
1710 FETCH Cur_get_serial_control INTO l_serial_control;
1711 CLOSE Cur_get_serial_control;
1712 IF l_serial_control <> 1 THEN
1713 RAISE check_serial_exception;
1714 END IF;
1715
1716 OPEN Cur_get_revision(X_formula_dtl.inventory_item_id, X_formula_dtl.owner_organization_id);
1717 FETCH Cur_get_revision INTO l_revision;
1718 CLOSE Cur_get_revision;
1719 IF (l_revision = 2 AND X_formula_dtl.revision IS NOT NULL) THEN
1720 OPEN Cur_check_revision(X_formula_dtl.inventory_item_id,
1721 X_formula_dtl.owner_organization_id,
1722 X_formula_dtl.revision);
1723 FETCH Cur_check_revision INTO l_temp;
1724 IF (Cur_check_revision%NOTFOUND) THEN
1725 xReturn := 'E';
1726 RAISE check_revision_exception;
1727 CLOSE Cur_check_revision;
1728 END IF;
1729 ELSIF (l_revision <> 2 AND X_formula_dtl.revision IS NOT NULL) THEN
1730 xReturn := 'E';
1731 RAISE check_no_revision_exception;
1732 CLOSE Cur_check_revision;
1733 ELSE
1734 X_formula_dtl.revision := NULL;
1735 END IF;
1736 END IF;
1737
1738
1739 EXCEPTION
1740 when check_buffer_ind_exception then
1741 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_BUFFER_IND');
1742 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
1743 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
1744 FND_MSG_PUB.ADD;
1745
1746 when check_line_no_exception then
1747 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_LINE_NO');
1748 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_formula_dtl.item_no);
1749 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
1750 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
1751 FND_MSG_PUB.ADD;
1752
1753 when check_qty_exception then
1754 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_QTY');
1755 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_formula_dtl.item_no);
1756 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
1757 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
1758 FND_MSG_PUB.ADD;
1759
1760 when check_cost_alloc_exception then
1761 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_COST_ALLOC');
1762 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
1763 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
1764 FND_MSG_PUB.ADD;
1765
1766 when inv_line_type THEN
1767 Xreturn := FND_API.g_ret_sts_error;
1768 FND_MESSAGE.SET_NAME('GMD','GMD_INV_LINE_BYPROD_TYP');
1769 FND_MSG_PUB.ADD;
1770 when inv_byprod_type THEN
1771 Xreturn := FND_API.g_ret_sts_error;
1772 FND_MESSAGE.SET_NAME('GMD','GMD_INV_BYPROD_TYPE');
1773 FND_MSG_PUB.ADD;
1774
1775 when check_type_exception then
1776 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_PHANTOM_TYPE');
1777 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
1778 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
1779 FND_MSG_PUB.ADD;
1780
1781 when check_line_type_exception then
1782 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_LINE_TYPE');
1783 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_formula_dtl.item_no);
1784 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
1785 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
1786 FND_MSG_PUB.ADD;
1787
1788 when check_rel_type_exception then
1789 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_RELEASE_TYPE');
1790 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
1791 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
1792 FND_MSG_PUB.ADD;
1793
1794 when check_scale_type_exception then
1795 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_SCALE_TYPE');
1796 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
1797 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
1798 FND_MSG_PUB.ADD;
1799
1800 when check_item_um_exception then
1801 FND_MESSAGE.SET_NAME('GMD','FM_SCALE_BAD_ITEM_UOM');
1802 FND_MESSAGE.SET_TOKEN('FROM_UOM', X_formula_dtl.detail_uom);
1803 FND_MESSAGE.SET_TOKEN('TO_UOM', litem_um);
1804 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_formula_dtl.item_no);
1805 FND_MSG_PUB.ADD;
1806
1807 when check_revision_exception then
1808 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_REVISION');
1809 FND_MSG_PUB.ADD;
1810 -- Bug 5350197
1811 when check_serial_exception then
1812 Xreturn := FND_API.g_ret_sts_error;
1813 FND_MESSAGE.SET_NAME('GMD','GMD_SERIAL_NOT_ALLOWED');
1814 FND_MSG_PUB.ADD;
1815
1816 when check_no_revision_exception then
1817 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_NOT_REVISION_CTRL');
1818 FND_MSG_PUB.ADD;
1819
1820 when no_org_access then
1821 null;
1822
1823 END validate_insert_record;
1824
1825 /* *******************************************************
1826 Wrapper for all validation functionality of formula
1827 update detail record.
1828 ****************************************************** */
1829
1830 PROCEDURE validate_update_record(P_formula_dtl IN GMD_FORMULA_COMMON_PUB.formula_update_rec_type,
1831 X_formula_dtl OUT NOCOPY GMD_FORMULA_COMMON_PUB.formula_update_rec_type,
1832 xReturn OUT NOCOPY VARCHAR2) IS
1833 --Cursor Declaration
1834 CURSOR Cur_get_revision (Vitem_id NUMBER, Vorgn_id NUMBER) IS
1835 SELECT revision_qty_control_code
1836 FROM mtl_system_items
1837 WHERE inventory_item_id = Vitem_id
1838 AND organization_id = Vorgn_id;
1839 CURSOR Cur_check_revision (Vitem_id NUMBER, Vorgn_id NUMBER, V_revision VARCHAR2) IS
1840 SELECT 1
1841 FROM mtl_item_revisions
1842 WHERE inventory_item_id = Vitem_id
1843 AND organization_id = Vorgn_id
1844 AND revision = V_revision;
1845 --Variable declaration
1846 l_ret NUMBER;
1847 lItem_id NUMBER;
1848 l_temp NUMBER;
1849 l_revision NUMBER;
1850 lItem_um mtl_system_items.primary_uom_code%type;
1851 --Exceptions
1852 check_buffer_ind_exception EXCEPTION;
1853 check_line_no_exception EXCEPTION;
1854 check_qty_exception EXCEPTION;
1855 check_cost_alloc_exception EXCEPTION;
1856 Inv_line_type EXCEPTION;
1857 Inv_Byprod_Type EXCEPTION;
1858 check_type_exception EXCEPTION;
1859 check_rel_type_exception EXCEPTION;
1860 check_line_type_exception EXCEPTION;
1861 check_scale_type_exception EXCEPTION;
1862 check_item_um_exception EXCEPTION;
1863 check_revision_exception EXCEPTION;
1864 no_org_access EXCEPTION;
1865 check_no_revision_exception EXCEPTION;
1866 -- Added the exception when item is not revision controlled
1867
1868 BEGIN
1869 X_formula_dtl := P_formula_dtl;
1870 -- Organization accessiblity check
1871 if gmd_api_grp.OrgnAccessible(X_formula_dtl.owner_organization_id)= TRUE then
1872 xReturn := 'S';
1873 ELSE
1874 xReturn := 'E';
1875 RAISE no_org_access;
1876 END IF;
1877 --Buffer_ind validation
1878 IF (X_formula_dtl.line_type = - 1) THEN
1879 IF (NVL(X_formula_dtl.buffer_ind,0) in (1,0) ) THEN
1880 xReturn := 'S';
1881 ELSE
1882 xReturn := 'E';
1883 RAISE check_buffer_ind_exception;
1884 END IF;
1885 END IF;
1886
1887 --Qty Validation
1888 IF (to_number(X_formula_dtl.qty) < 0) THEN
1889 xReturn := 'E';
1890 RAISE check_qty_exception;
1891 ELSE
1892 xReturn := 'S';
1893 END IF;
1894
1895 --Cost Alloc check
1896 IF (X_formula_dtl.line_type = 1) THEN
1897 IF (X_formula_dtl.cost_alloc < 0 OR X_formula_dtl.cost_alloc > 100) THEN
1898 xReturn := 'E';
1899 RAISE check_cost_alloc_exception;
1900 ELSE
1901 xReturn := 'S';
1902 END IF;
1903 ELSE
1904 X_formula_dtl.cost_alloc := NULL;
1905 END IF;
1906
1907 --By product type validation
1908 IF (X_formula_dtl.line_type = 2) THEN
1909 Xreturn := FND_API.g_ret_sts_success;
1910 IF X_formula_dtl.by_product_type IS NOT NULL THEN
1911 --The value for by_product_type is only applicable for byproducts
1912 IF (X_formula_dtl.line_type <> 2) THEN
1913 RAISE inv_line_type;
1914 ELSE
1915 l_ret := GMDFMVAL_PUB.type_val(ptype_name => 'GMD_BY_PRODUCT_TYPE',
1916 Pvalue => X_formula_dtl.by_product_type);
1917 IF l_ret <> 0 THEN
1918 RAISE Inv_Byprod_Type;
1919 ELSE
1920 IF (X_formula_dtl.by_product_type = 'S') THEN
1921 X_formula_dtl.release_type := 1;
1922 END IF;
1923 END IF;
1924 END IF;
1925 END IF;
1926 ELSE
1927 X_formula_dtl.by_product_type := NULL;
1928 END IF;
1929
1930 --Phantom_type validation
1931 l_ret := GMDFMVAL_PUB.type_val('PHANTOM_TYPE',X_formula_dtl.phantom_type);
1932
1933 IF (l_ret <> 0) THEN
1934 xReturn := 'E';
1935 RAISE check_type_exception;
1936 ELSE
1937 xReturn := 'S';
1938 END IF;
1939
1940 --Release Type validation
1941 l_ret := GMDFMVAL_PUB.type_val('RELEASE_TYPE',X_formula_dtl.release_type);
1942
1943 IF (l_ret <> 0) THEN
1944 xReturn := 'E';
1945 RAISE check_rel_type_exception;
1946 ELSE
1947 xReturn := 'S';
1948 END IF;
1949
1950 --Scale Type validation
1951 l_ret := GMDFMVAL_PUB.type_val('SCALE_TYPE',X_formula_dtl.scale_type_dtl);
1952
1953 IF (l_ret <> 0) THEN
1954 xReturn := 'E';
1955 RAise check_scale_type_exception;
1956 ELSE
1957 xReturn := 'S';
1958 END IF;
1959
1960 --Item Uom Validation
1961 --Based on the item_id get its primary UOM
1962 GMDFMVAL_PUB.get_item_id(pitem_no => X_formula_dtl.Item_no,
1963 pinventory_item_id => X_formula_dtl.inventory_item_id,
1964 porganization_id => X_formula_dtl.owner_organization_id,
1965 xitem_id => lItem_id,
1966 xitem_um => lItem_um,
1967 xreturn_code => l_ret);
1968
1969 /* Inavlid item used */
1970 IF (X_formula_dtl.detail_uom IS NOT NULL) THEN
1971 IF (l_ret < 0) THEN
1972 xReturn := 'E';
1973 END IF;
1974
1975 --Based on item_id, primary UOM,formula item UOM check if it can be converted.
1976 l_ret := INV_CONVERT.inv_um_convert (item_id => litem_id
1977 ,precision => 5
1978 ,from_quantity => 100
1979 ,from_unit => X_formula_dtl.detail_uom
1980 ,to_unit => lItem_um
1981 ,from_name => NULL
1982 ,to_name => NULL);
1983 IF (l_ret < 0) THEN
1984 xReturn := 'E';
1985 Raise check_item_um_exception;
1986 ELSE
1987 xReturn := 'S';
1988 END IF;
1989 ELSE
1990 X_formula_dtl.detail_uom := litem_um;
1991 END IF;
1992
1993 --Revision Validation
1994 OPEN Cur_get_revision(X_formula_dtl.inventory_item_id, X_formula_dtl.owner_organization_id);
1995 FETCH Cur_get_revision INTO l_revision;
1996 CLOSE Cur_get_revision;
1997 IF (l_revision = 2 AND X_formula_dtl.revision IS NOT NULL) THEN
1998 OPEN Cur_check_revision(X_formula_dtl.inventory_item_id,
1999 X_formula_dtl.owner_organization_id,
2000 X_formula_dtl.revision);
2001 FETCH Cur_check_revision INTO l_temp;
2002 IF (Cur_check_revision%NOTFOUND) THEN
2003 xReturn := 'E';
2004 RAISE check_revision_exception;
2005 CLOSE Cur_check_revision;
2006 END IF;
2007 -- Bug # 4603056 Kapil M
2008 -- Added the check if the Item is revision controlled and display message if not.
2009 ELSIF (l_revision <> 2 AND X_formula_dtl.revision IS NOT NULL) THEN
2010 xReturn := 'E';
2011 RAISE check_no_revision_exception;
2012 CLOSE Cur_check_revision;
2013 END IF;
2014
2015
2016 EXCEPTION
2017 when check_buffer_ind_exception then
2018 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_BUFFER_IND');
2019 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
2020 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
2021 FND_MSG_PUB.ADD;
2022
2023 when check_line_no_exception then
2024 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_LINE_NO');
2025 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_formula_dtl.item_no);
2026 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
2027 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
2028 FND_MSG_PUB.ADD;
2029
2030 when check_qty_exception then
2031 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_QTY');
2032 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_formula_dtl.item_no);
2033 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
2034 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
2035 FND_MSG_PUB.ADD;
2036
2037 when check_cost_alloc_exception then
2038 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_COST_ALLOC');
2039 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
2040 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
2041 FND_MSG_PUB.ADD;
2042
2043 when inv_line_type THEN
2044 Xreturn := FND_API.g_ret_sts_error;
2045 FND_MESSAGE.SET_NAME('GMD','GMD_INV_LINE_BYPROD_TYP');
2046 FND_MSG_PUB.ADD;
2047 when inv_byprod_type THEN
2048 Xreturn := FND_API.g_ret_sts_error;
2049 FND_MESSAGE.SET_NAME('GMD','GMD_INV_BYPROD_TYPE');
2050 FND_MSG_PUB.ADD;
2051
2052 when check_type_exception then
2053 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_PHANTOM_TYPE');
2054 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
2055 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
2056 FND_MSG_PUB.ADD;
2057
2058 when check_line_type_exception then
2059 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_LINE_TYPE');
2060 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_formula_dtl.item_no);
2061 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
2062 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
2063 FND_MSG_PUB.ADD;
2064
2065 when check_rel_type_exception then
2066 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_RELEASE_TYPE');
2067 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
2068 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
2069 FND_MSG_PUB.ADD;
2070
2071 when check_scale_type_exception then
2072 FND_MESSAGE.SET_NAME('GMD','FM_INVALID_SCALE_TYPE');
2073 FND_MESSAGE.SET_TOKEN('FORMULA_NO', X_formula_dtl.formula_no);
2074 FND_MESSAGE.SET_TOKEN('FORMULA_VERS',X_formula_dtl.formula_vers);
2075 FND_MSG_PUB.ADD;
2076
2077 when check_item_um_exception then
2078 FND_MESSAGE.SET_NAME('GMD','FM_SCALE_BAD_ITEM_UOM');
2079 FND_MESSAGE.SET_TOKEN('FROM_UOM', X_formula_dtl.detail_uom);
2080 FND_MESSAGE.SET_TOKEN('TO_UOM', litem_um);
2081 FND_MESSAGE.SET_TOKEN('ITEM_NO', X_formula_dtl.item_no);
2082 FND_MSG_PUB.ADD;
2083
2084 when check_revision_exception then
2085 FND_MESSAGE.SET_NAME('GMD','GMD_INVALID_REVISION');
2086 FND_MSG_PUB.ADD;
2087
2088 when check_no_revision_exception then
2089 FND_MESSAGE.SET_NAME('GMD','GMD_SPEC_NOT_REVISION_CTRL');
2090 FND_MSG_PUB.ADD;
2091
2092 when no_org_access then
2093 null;
2094
2095 END validate_update_record;
2096
2097
2098 FUNCTION check_expr_items (V_formula_id IN NUMBER ) RETURN BOOLEAN IS
2099 ------------------------------------------------------------------
2100 --Created by : Sriram.S
2101 --Date created: 20-JAN-2004
2102 --
2103 --Purpose: Function returns TRUE formula has experied item(s) otherwise
2104 -- returns FALSE.
2105 --Known limitations/enhancements and/or remarks:
2106 --
2107 --Change History:
2108 --Who When What
2109 --srsriran 20-FEB-2004 Created w.r.t. bug 3408799
2110 -------------------------------------------------------------------
2111 --Cursor fetches 1 if context formula has experied item(s)
2112 CURSOR cur_check_expr(p_org_id NUMBER) IS
2113 SELECT 1
2114 FROM SYS.DUAL
2115 WHERE EXISTS (SELECT 1
2116 FROM fm_matl_dtl d,
2117 mtl_system_items_b i
2118 WHERE recipe_enabled_flag ='Y'
2119 AND i.ORGANIZATION_ID = p_org_id
2120 AND d.formula_id = V_formula_id
2121 AND i.inventory_item_id = d.inventory_item_id
2122 AND i.eng_item_flag = 'Y');
2123 l_orgid NUMBER;
2124 l_temp NUMBER;
2125 BEGIN
2126 IF (V_formula_id IS NOT NULL) THEN
2127 -- KSHUKLA added as per as performance bug 4917329
2128 -- Find out the formula owner organization and narrow down the query
2129 -- perf
2130 select owner_organization_id
2131 into l_orgid
2132 from fm_form_mst
2133 where formula_id=V_formula_id;
2134 -- Cursor Call need to change inorder to take into account
2135 -- the formula owner org id
2136
2137 OPEN cur_check_expr(l_orgid);
2138 FETCH Cur_check_expr INTO l_temp;
2139 IF (cur_check_expr%FOUND) THEN
2140 CLOSE Cur_check_expr;
2141 RETURN TRUE;
2142 ELSE
2143 CLOSE cur_check_expr;
2144 END IF; -- IF (Cur_check_expr%FOUND) THEN
2145 END IF; -- IF (V_formula_id IS NOT NULL) THEN
2146 RETURN FALSE;
2147 END check_expr_items;
2148
2149 FUNCTION output_qty_zero (V_formula_id IN NUMBER ) RETURN BOOLEAN IS
2150 ------------------------------------------------------------------
2151 --Created by : Sriram.S
2152 --Date created: 20-JAN-2004
2153 --
2154 --Purpose: Function returns TRUE if sum of out quantity is zero otherwise
2155 -- returns FALSE.
2156 --Known limitations/enhancements and/or remarks:
2157 --
2158 --Change History:
2159 --Who When What
2160 --srsriran 20-FEB-2004 Bug 3408799
2161 -------------------------------------------------------------------
2162 --Cursor returns the sum of total quantity of a formaula
2163 CURSOR cur_check_total_output IS
2164 SELECT SUM(qty)
2165 FROM fm_matl_dtl
2166 WHERE formula_id = V_formula_id
2167 AND line_type > 0;
2168
2169 l_total_output NUMBER;
2170 BEGIN
2171 IF (V_formula_id IS NOT NULL) THEN
2172 OPEN cur_check_total_output;
2173 FETCH cur_check_total_output INTO l_total_output;
2174 CLOSE cur_check_total_output;
2175 IF l_total_output = 0 THEN
2176 RETURN TRUE;
2177 END IF; --IF X_total_output = 0 THEN
2178 END IF; --IF (V_formula_id IS NOT NULL) THEN
2179 RETURN FALSE;
2180 END output_qty_zero;
2181
2182 FUNCTION inactive_items (V_formula_id IN NUMBER ) RETURN BOOLEAN IS
2183 ------------------------------------------------------------------
2184 --Created by : Sriram.S
2185 --Date created: 20-JAN-2004
2186 --
2187 --Purpose: Function returns TRUE formula has inactive item(s) otherwise
2188 -- returns FALSE.
2189 --Known limitations/enhancements and/or remarks:
2190 --
2191 --Change History:
2192 --Who When What
2193 --srsriran 20-FEB-2004 Bug 3408799
2194 --Kapil M 06-MAR-2006 Bug 5040915 Changed the query by adding the condition on organization_id
2195 -------------------------------------------------------------------
2196 --Cursor fetches 1 if context formula has experied item(s)
2197 CURSOR cur_check_inactive IS
2198 SELECT 1
2199 FROM SYS.DUAL
2200 WHERE EXISTS (SELECT 1 FROM fm_matl_dtl d,
2201 mtl_system_items_b i
2202 WHERE d.formula_id = V_formula_id
2203 AND i.inventory_item_id = d.inventory_item_id
2204 AND i.recipe_enabled_flag = 'N'
2205 AND i.organization_id = d.organization_id); -- Bug # 5040915 Kapil M
2206 X_temp NUMBER;
2207 BEGIN
2208 IF (V_formula_id IS NOT NULL) THEN
2209 OPEN Cur_check_inactive;
2210 FETCH Cur_check_inactive INTO X_temp;
2211 IF (Cur_check_inactive%FOUND) THEN
2212 CLOSE Cur_check_inactive;
2213 RETURN TRUE;
2214 ELSE
2215 CLOSE Cur_check_inactive;
2216 END IF; -- IF (Cur_check_inactive%FOUND) THEN
2217 END IF; -- IF (V_formula_id IS NOT NULL) THEN
2218 RETURN FALSE;
2219 END inactive_items;
2220 END GMDFMVAL_PUB;
[Home] [Help]