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