1 PACKAGE GMD_FORMULA_DESIGNER_PKG AS
2 /* $Header: GMDFRDDS.pls 120.7.12000000.2 2007/02/09 12:50:55 kmotupal ship $ */
3 /*==========================================================================================
4 | Copyright (c) 2002 Oracle Corporation
5 | Redwood Shores, California, USA
6 | All rights reserved
7 ===========================================================================================
8 | FILENAME
9 | GMDFRDDB.pls
10 |
11 | DESCRIPTION
12 | Package body containing the procedures used by the Formula Designer
13 |
14 | NOTES
15 |
16 | HISTORY
17 | 05-SEP-2002 Eddie Oumerretane Created.
18 | 27-APR-2004 S.Sriram
19 | Added SET_DEFAULT_STATUS procedure for Default Status Build (Bug# 3408799)
20 | 23-JUN-2004 S.Sriram Bug# 3700829
21 | Added procedure CHECK_USR_HAS_FSEC_RESP to check if user has formula
22 | security responsibility (i-e) Product development security manager.
23 | 29-Jul-2005 Tdaniel Added organization_id for convergence changes.
24 =============================================================================================
25 */
26 G_CREATED_BY NUMBER := FND_PROFILE.VALUE('USER_ID');
27 G_LOGIN_ID NUMBER := FND_PROFILE.VALUE('LOGIN_ID');
28 G_USER_ORG VARCHAR2(4);
29 G_CALCULATABLE_REC GMD_AUTO_STEP_CALC.CALCULATABLE_REC_TYPE;
30 G_RECIPE_TBL GMD_AUTO_STEP_CALC.RECIPE_ID_TBL;
31 G_CHECK_STEP_MAT GMD_AUTO_STEP_CALC.CHECK_STEP_MAT_TYPE;
32 G_SCALE_REC GMD_COMMON_SCALE.scale_tab;
33
34 /* Api start of comments
35 +============================================================================
36 | PROCEDURE NAME
37 | Get_Formula_Mode
38 |
39 | DESCRIPTION
40 | Determine whether the user has access to this formula and in which
41 | mode (view or update mode).
42 |
43 | INPUT PARAMETERS
44 | p_formula_id NUMBER
45 |
46 | OUTPUT PARAMETERS
47 | x_formula_mode VARCHAR2
48 | x_create_allowed VARCHAR2
49 | x_return_code VARCHAR2
50 | x_error_msg VARCHAR2
51 |
52 | HISTORY
53 | 05-SEP-2002 Eddie Oumerretane Created.
54 |
55 +=============================================================================
56 Api end of comments
57 */
58 PROCEDURE Get_Formula_Mode (p_formula_id IN NUMBER,
59 p_organization_id IN NUMBER,
60 x_formula_mode OUT NOCOPY VARCHAR2,
61 x_create_allowed OUT NOCOPY VARCHAR2,
62 x_return_code OUT NOCOPY VARCHAR2,
63 x_error_msg OUT NOCOPY VARCHAR2);
64 /* Api start of comments
65 +============================================================================
66 | PROCEDURE NAME
67 | Is_Formula_Used_In_Recipes
68 |
69 | DESCRIPTION
70 | Determine whether the formula is used in one or more recipes.
71 |
72 | INPUT PARAMETERS
73 | p_formula_id NUMBER
74 |
75 | OUTPUT PARAMETERS
76 | x_used_in_recipes VARCHAR2(1)
77 | x_return_code VARCHAR2(1)
78 | x_error_msg VARCHAR2(100)
79 |
80 | HISTORY
81 | 05-SEP-2002 Eddie Oumerretane Created.
82 |
83 +=============================================================================
84 Api end of comments
85 */
86 PROCEDURE Is_Formula_Used_In_Recipes (p_formula_id IN NUMBER,
87 x_used_in_recipes OUT NOCOPY VARCHAR2,
88 x_return_code OUT NOCOPY VARCHAR2,
89 x_error_msg OUT NOCOPY VARCHAR2);
90
91 /* Api start of comments
92 +============================================================================
93 | PROCEDURE NAME
94 | Update_Formula_Header
95 |
96 | DESCRIPTION
97 | Update formula header
98 |
99 | INPUT PARAMETERS
100 | p_formula_id IN NUMBER,
101 | p_formula_no IN VARCHAR2
102 | p_formula_vers IN NUMBER,
103 | p_formula_desc IN VARCHAR2
104 | p_formula_class IN VARCHAR2
105 | p_last_update_date IN DATE
106 | p_user_id IN NUMBER
107 | p_last_update_date_orig IN DATE
108 | p_update_release_type IN NUMBER
109 | p_auto_product_calc VARCHAR2
110 |
111 | OUTPUT PARAMETERS
112 | x_return_code VARCHAR2(1)
113 | x_error_msg VARCHAR2(100)
114 |
115 | HISTORY
116 | 05-SEP-2002 Eddie Oumerretane Created.
117 | 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
118 | Added the new column auto_product_calc
119 |
120 +=============================================================================
121 Api end of comments
122 */
123 PROCEDURE Update_Formula_Header ( p_formula_id IN NUMBER,
124 p_formula_no IN VARCHAR2,
125 p_formula_vers IN NUMBER,
126 p_formula_desc IN VARCHAR2,
127 p_formula_desc2 IN VARCHAR2,
128 p_formula_status IN VARCHAR2,
129 p_formula_class IN VARCHAR2,
130 p_owner_organization_id IN NUMBER,
131 p_owner_id IN NUMBER,
132 p_formula_type IN NUMBER,
133 p_scale_type IN NUMBER,
134 p_text_code IN NUMBER,
135 p_last_update_date IN DATE,
136 p_user_id IN NUMBER,
137 p_last_update_date_orig IN DATE,
138 p_auto_product_calc IN VARCHAR2,
139 x_return_code OUT NOCOPY VARCHAR2,
140 x_error_msg OUT NOCOPY VARCHAR2) ;
141
142 /* Api start of comments
143 +============================================================================
144 | PROCEDURE NAME
145 | Insert_Formula_Detail
146 |
147 | DESCRIPTION
148 | Create a formula line
149 |
150 | INPUT PARAMETERS
151 | p_formula_id IN NUMBER
152 | p_formulaline_id IN NUMBER
153 | p_line_type IN NUMBER
154 | p_line_no IN NUMBER
155 | p_item_id IN NUMBER
156 | p_item_no IN VARCHAR2
157 | p_qty IN NUMBER
158 | p_item_um IN VARCHAR2
159 | p_release_type IN NUMBER
160 | p_scrap_factor IN NUMBER
161 | p_scale_type IN NUMBER
162 | p_cost_alloc IN NUMBER
163 | p_phantom_type IN NUMBER
164 | p_rework_type IN NUMBER
165 | p_text_code IN NUMBER
166 | p_tp_formula_id IN NUMBER
167 | p_iaformula_id IN NUMBER
168 | p_scale_uom IN VARCHAR2
169 | p_contribute_step_qty_ind IN VARCHAR2
170 | p_contribute_yield_ind IN VARCHAR2
171 | p_scale_multiple IN NUMBER
172 | p_scale_rounding_variance IN NUMBER
173 | p_rounding_direction IN NUMBER
174 | p_by_product_type IN VARCHAR2
175 | p_text_code IN NUMBER
176 | p_last_update_date IN DATE
177 | p_user_id IN NUMBER
178 | p_prod_percent IN NUMBER
179 |
180 | OUTPUT PARAMETERS
181 | x_return_code VARCHAR2(1)
182 | x_error_msg VARCHAR2(100)
183 |
184 | HISTORY
185 | 05-SEP-2002 Eddie Oumerretane Created.
186 | 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
187 | Added the new column prod_percent
188 |
189 +=============================================================================
190 Api end of comments
191 */
192 PROCEDURE Insert_Formula_Detail ( p_formula_id IN NUMBER
193 ,p_formulaline_id IN NUMBER
194 ,p_line_type IN NUMBER
195 ,p_line_no IN NUMBER
196 ,p_item_id IN NUMBER
197 ,p_item_no IN VARCHAR2
198 ,p_revision IN VARCHAR2
199 ,p_qty IN NUMBER
200 ,p_item_um IN VARCHAR2
201 ,p_release_type IN NUMBER
202 ,p_scrap_factor IN NUMBER
203 ,p_scale_type IN NUMBER
204 ,p_cost_alloc IN NUMBER
205 ,p_phantom_type IN NUMBER
206 ,p_rework_type IN NUMBER
207 ,p_text_code IN NUMBER
208 ,p_tp_formula_id IN NUMBER
209 ,p_iaformula_id IN NUMBER
210 ,p_scale_uom IN VARCHAR2
211 ,p_contribute_step_qty_ind IN VARCHAR2
212 ,p_contribute_yield_ind IN VARCHAR2
213 ,p_scale_multiple IN NUMBER
214 ,p_scale_rounding_variance IN NUMBER
215 ,p_rounding_direction IN NUMBER
216 ,p_by_product_type IN VARCHAR2
217 ,p_last_update_date IN DATE
218 ,p_user_id IN NUMBER
219 ,p_prod_percent IN NUMBER
220 ,x_return_code OUT NOCOPY VARCHAR2
221 ,x_error_msg OUT NOCOPY VARCHAR2);
222
223 /* Api start of comments
224 +============================================================================
225 | PROCEDURE NAME
226 | Update_Formula_Detail
227 |
228 | DESCRIPTION
229 | Update formula detail line
230 |
231 | INPUT PARAMETERS
232 | p_formula_id IN NUMBER
233 | p_formulaline_id IN NUMBER
234 | p_line_type IN NUMBER
235 | p_line_no IN NUMBER
236 | p_item_id IN NUMBER
237 | p_item_no IN VARCHAR2
238 | p_qty IN NUMBER
239 | p_item_um IN VARCHAR2
240 | p_release_type IN NUMBER
241 | p_scrap_factor IN NUMBER
242 | p_scale_type IN NUMBER
243 | p_cost_alloc IN NUMBER
244 | p_phantom_type IN NUMBER
245 | p_rework_type IN NUMBER
246 | p_text_code IN NUMBER
247 | p_tp_formula_id IN NUMBER
248 | p_iaformula_id IN NUMBER
249 | p_scale_uom IN VARCHAR2
250 | p_contribute_step_qty_ind IN VARCHAR2
251 | p_contribute_yield_ind IN VARCHAR2
252 | p_scale_multiple IN NUMBER
253 | p_scale_rounding_variance IN NUMBER
254 | p_rounding_direction IN NUMBER
255 | p_by_product_type IN VARCHAR2
256 | p_text_code IN NUMBER
257 | p_last_update_date IN DATE
258 | p_user_id IN NUMBER
259 | p_text_code IN NUMBER
260 | p_last_update_date IN DATE
261 | p_user_id IN NUMBER
262 | p_last_update_date_orig IN DATE
263 | p_prod_percent IN NUMBER
264 |
265 | OUTPUT PARAMETERS
266 | x_return_code VARCHAR2(1)
267 | x_error_msg VARCHAR2(100)
268 |
269 | HISTORY
270 | 05-SEP-2002 Eddie Oumerretane Created.
271 | 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
272 | Added the new column prod_percent
273 |
274 +=============================================================================
275 Api end of comments
276 */
277 PROCEDURE Update_Formula_Detail ( p_formula_id IN NUMBER
278 ,p_formulaline_id IN NUMBER
279 ,p_line_type IN NUMBER
280 ,p_line_no IN NUMBER
281 ,p_item_id IN NUMBER
282 ,p_item_no IN VARCHAR2
283 ,p_revision IN VARCHAR2
284 ,p_qty IN NUMBER
285 ,p_item_um IN VARCHAR2
286 ,p_release_type IN NUMBER
287 ,p_scrap_factor IN NUMBER
288 ,p_scale_type IN NUMBER
289 ,p_cost_alloc IN NUMBER
290 ,p_phantom_type IN NUMBER
291 ,p_rework_type IN NUMBER
292 ,p_text_code IN NUMBER
293 ,p_tp_formula_id IN NUMBER
294 ,p_iaformula_id IN NUMBER
295 ,p_scale_uom IN VARCHAR2
296 ,p_contribute_step_qty_ind IN VARCHAR2
297 ,p_contribute_yield_ind IN VARCHAR2
298 ,p_scale_multiple IN NUMBER
299 ,p_scale_rounding_variance IN NUMBER
300 ,p_rounding_direction IN NUMBER
301 ,p_by_product_type IN VARCHAR2
302 ,p_last_update_date IN DATE
303 ,p_user_id IN NUMBER
304 ,p_last_update_date_orig IN DATE
305 ,p_prod_percent IN NUMBER
306 ,x_return_code OUT NOCOPY VARCHAR2
307 ,x_error_msg OUT NOCOPY VARCHAR2);
308
309 /* Api start of comments
310 +============================================================================
311 | PROCEDURE NAME
312 | Create_formula_Header
313 |
314 | DESCRIPTION
315 | Create formula header
316 |
317 | INPUT PARAMETERS
318 | p_user_id IN NUMBER
319 |
320 | OUTPUT PARAMETERS
321 | x_formula_id NUMBER
322 | x_return_code VARCHAR2(1)
323 | x_error_msg VARCHAR2(100)
324 |
325 | HISTORY
326 | 05-SEP-2002 Eddie Oumerretane Created.
327 | 05-FEB-2007 Kapil M. Bug# 5716318. Auto-Product Qty ME
328 | Added the new column auto_product_calc
329 |
330 +=============================================================================
331 Api end of comments
332 */
333 PROCEDURE Create_Formula_Header ( p_formula_no IN VARCHAR2,
334 p_formula_vers IN NUMBER,
335 p_formula_desc IN VARCHAR2,
336 p_formula_desc2 IN VARCHAR2,
337 p_formula_class IN VARCHAR2,
338 p_owner_organization_id IN NUMBER,
339 p_owner_id IN NUMBER,
340 p_formula_type IN NUMBER,
341 p_scale_type IN NUMBER,
342 p_text_code IN NUMBER,
343 p_last_update_date IN DATE,
344 p_auto_product_calc IN VARCHAR2,
345 x_formula_id OUT NOCOPY NUMBER,
346 x_return_code OUT NOCOPY VARCHAR2,
347 x_error_msg OUT NOCOPY VARCHAR2);
348
349 /* Api start of comments
350 +============================================================================
351 | PROCEDURE NAME
352 | Delete_Formula_Detail
353 |
354 | DESCRIPTION
355 | Delete a formula line
356 |
357 | INPUT PARAMETERS
358 | p_formula_id NUMBER
359 | p_formulaline_id NUMBER
360 | p_line_type NUMBER
361 | p_last_update_date DATE
362 |
363 | OUTPUT PARAMETERS
364 | x_return_code VARCHAR2(1)
365 | x_error_msg VARCHAR2(100)
366 |
367 | HISTORY
368 | 05-SEP-2002 Eddie Oumerretane Created.
369 |
370 +=============================================================================
371 Api end of comments
372 */
373 PROCEDURE Delete_Formula_Detail(p_formula_id IN NUMBER,
374 p_formulaline_id IN NUMBER,
375 p_line_type IN NUMBER,
376 p_last_update_date IN DATE,
377 x_return_code OUT NOCOPY VARCHAR2,
378 x_error_msg OUT NOCOPY VARCHAR2);
379
380
381 /* Api start of comments
382 +============================================================================
383 | PROCEDURE NAME
384 | Delete_Formula_Detail_With_No_Val
385 |
386 | DESCRIPTION
387 | Delete a formula line without performing any validations
388 |
389 | INPUT PARAMETERS
390 | p_formula_id NUMBER
391 | p_formulaline_id NUMBER
392 | p_line_type NUMBER
393 | p_last_update_date DATE
394 |
395 | OUTPUT PARAMETERS
396 | x_return_code VARCHAR2(1)
397 | x_error_msg VARCHAR2(100)
398 |
399 | HISTORY
400 | 26-SEP-2002 Eddie Oumerretane Created.
401 |
402 +=============================================================================
403 Api end of comments
404 */
405 PROCEDURE Del_Formula_Detail_With_No_Val(p_formula_id IN NUMBER,
406 p_formulaline_id IN NUMBER,
407 p_line_type IN NUMBER,
408 p_last_update_date IN DATE,
409 x_return_code OUT NOCOPY VARCHAR2,
410 x_error_msg OUT NOCOPY VARCHAR2);
411
412 /* Api start of comments
413 +============================================================================
414 | PROCEDURE NAME
415 | Validate_Cost_Allocation
416 |
417 | DESCRIPTION
418 | Make sure cost allocation is <= 1
419 |
420 | INPUT PARAMETERS
421 | p_formula_id NUMBER
422 | p_formulaline_id NUMBER
423 | p_cost_alloc NUMBER
424 |
425 | OUTPUT PARAMETERS
426 | x_return_code VARCHAR2(1)
427 | x_error_msg VARCHAR2(100)
428 |
429 | HISTORY
430 | 09-SEP-2002 Eddie Oumerretane Created.
431 |
432 +=============================================================================
433 Api end of comments
434 */
435 PROCEDURE Validate_Cost_Allocation(p_formula_id IN NUMBER,
436 p_formulaline_id IN NUMBER,
437 p_cost_alloc IN NUMBER,
438 x_return_code OUT NOCOPY VARCHAR2,
439 x_error_msg OUT NOCOPY VARCHAR2);
440
441 /* Api start of comments
442 +============================================================================
443 | PROCEDURE NAME
444 | Validate_Item_Uom
445 |
446 | DESCRIPTION
447 | Make sure uom is convertible to item inventory uom
448 |
449 | INPUT PARAMETERS
450 | p_item_id NUMBER
451 | p_item_um VARCHAR
452 |
453 | OUTPUT PARAMETERS
454 | x_return_code VARCHAR2(1)
455 | x_error_msg VARCHAR2(100)
456 |
457 | HISTORY
458 | 09-SEP-2002 Eddie Oumerretane Created.
459 |
460 +=============================================================================
461 Api end of comments
462 */
463 PROCEDURE Validate_Item_Uom (p_item_id IN NUMBER,
464 p_item_uom IN VARCHAR2,
465 x_return_code OUT NOCOPY VARCHAR2,
466 x_error_msg OUT NOCOPY VARCHAR2);
467
468
469 /* Api start of comments
470 +============================================================================
471 | PROCEDURE NAME
472 | Check_Item_Used_In_Recipe
473 |
474 | DESCRIPTION
475 | Check whether the given item is used in recipes
476 |
477 | INPUT PARAMETERS
478 | p_formulaline_id NUMBER
479 |
480 | OUTPUT PARAMETERS
481 | x_return_code VARCHAR2
482 | x_warning_message VARCHAR2
483 | x_error_msg VARCHAR2
484 |
485 | HISTORY
486 | 20-SEP-2002 Eddie Oumerretane Created.
487 |
488 +=============================================================================
489 Api end of comments
490 */
491 PROCEDURE Check_Item_Used_In_Recipe( p_formulaline_id IN NUMBER,
492 x_nb_recipes OUT NOCOPY NUMBER,
493 x_warning_message OUT NOCOPY VARCHAR2,
494 x_return_code OUT NOCOPY VARCHAR2,
495 x_error_msg OUT NOCOPY VARCHAR2);
496 /* Api start of comments
497 +============================================================================
498 | PROCEDURE NAME
499 | Cascade_Update_Recipes
500 |
501 | DESCRIPTION
502 | Update all recipes impacted by the deletion of an item in the formula
503 | or a step in the routing. Check_Item_Used_In_Recipe must be called
504 | prior to invoking this procedure.
505 |
506 | INPUT PARAMETERS
507 |
508 | OUTPUT PARAMETERS
509 | x_return_code VARCHAR2
510 | x_warning_message VARCHAR2
511 | x_error_msg VARCHAR2
512 |
513 | HISTORY
514 | 20-SEP-2002 Eddie Oumerretane Created.
515 |
516 +=============================================================================
517 Api end of comments
518 */
519 PROCEDURE Cascade_Update_Recipes(x_return_code OUT NOCOPY VARCHAR2,
520 x_error_msg OUT NOCOPY VARCHAR2);
521
522 /* Api start of comments
523 +============================================================================
524 | PROCEDURE NAME
525 | Calculate_Theoretical_Yield
526 |
527 | DESCRIPTION
528 | Calculate theoretical yield.
529 |
530 | INPUT PARAMETERS
531 | p_formula_id NUMBER
532 |
533 | OUTPUT PARAMETERS
534 | x_return_code VARCHAR2
535 | x_error_msg VARCHAR2
536 |
537 | HISTORY
538 | 24-OCT-2002 Eddie Oumerretane Created.
539 |
540 +=============================================================================
541 Api end of comments
542 */
543 PROCEDURE Calculate_Theoretical_yield(p_formula_id IN NUMBER,
544 p_scale_factor IN NUMBER,
545 x_return_code OUT NOCOPY VARCHAR2,
546 x_error_msg OUT NOCOPY VARCHAR2);
547
548 /* Api start of comments
549 +============================================================================
550 | PROCEDURE NAME
551 | Scale_Formula
552 |
553 | DESCRIPTION
554 | Scale the formula.
555 |
556 | INPUT PARAMETERS
557 | p_formula_id NUMBER
558 | p_scale_factor NUMBER
559 | p_primaries VARCHAR2
560 |
561 | OUTPUT PARAMETERS
562 | x_return_code VARCHAR2
563 | x_error_msg VARCHAR2
564 |
565 | HISTORY
566 | 29-OCT-2002 Eddie Oumerretane Created.
567 |
568 +=============================================================================
569 Api end of comments
570 */
571 PROCEDURE Scale_Formula(p_formula_id IN NUMBER,
572 p_scale_factor IN NUMBER,
573 p_primaries IN VARCHAR2,
574 x_return_code OUT NOCOPY VARCHAR2,
575 x_error_msg OUT NOCOPY VARCHAR2);
576
577 /* Api start of comments
578 +============================================================================
579 | PROCEDURE NAME
580 | Validate_Formula_Details
581 |
582 | DESCRIPTION
583 | Validate formula details
584 |
585 | INPUT PARAMETERS
586 | p_formula_id NUMBER
587 |
588 | OUTPUT PARAMETERS
589 | x_return_code VARCHAR2(1)
590 | x_error_msg VARCHAR2(100)
591 |
592 | HISTORY
593 | 18-NOV-2002 Eddie Oumerretane Created.
594 |
595 +=============================================================================
596 Api end of comments
597 */
598 PROCEDURE Validate_Formula_Details ( p_formula_id IN VARCHAR2,
599 x_return_code OUT NOCOPY VARCHAR2,
600 x_error_msg OUT NOCOPY VARCHAR2);
601
602
603 /* Api start of comments
604 +============================================================================
605 | PROCEDURE NAME
606 | Set_Save_Point
607 |
608 | DESCRIPTION
609 | Establish a SAVEPOINT. This is used to provide the ability to
610 | rollback a logical transaction performed by the Designer.
611 |
612 | INPUT PARAMETERS
613 | None
614 |
615 | OUTPUT PARAMETERS
616 | x_return_code VARCHAR2
617 | x_error_msg VARCHAR2
618 |
619 | HISTORY
620 | 03-DEC-2002 Eddie Oumerretane Created.
621 |
622 +=============================================================================
623 Api end of comments
624 */
625 PROCEDURE Set_Save_Point ( x_return_code OUT NOCOPY VARCHAR2,
626 x_error_msg OUT NOCOPY VARCHAR2);
627
628 /* Api start of comments
629 +============================================================================
630 | PROCEDURE NAME
631 | Rollback_Save_Point
632 |
633 | DESCRIPTION
634 | Rollback up to the save point established after a call to
635 | Set_Save_Point.
636 |
637 | INPUT PARAMETERS
638 | None
639 |
640 | OUTPUT PARAMETERS
641 | x_return_code VARCHAR2
642 | x_error_msg VARCHAR2
643 |
644 | HISTORY
645 | 03-DEC-2002 Eddie Oumerretane Created.
646 |
647 +=============================================================================
648 Api end of comments
649 */
650 PROCEDURE Rollback_Save_Point ( x_return_code OUT NOCOPY VARCHAR2,
651 x_error_msg OUT NOCOPY VARCHAR2);
652
653
654
655
656 /* Api start of comments
657 +============================================================================
658 | PROCEDURE NAME
659 | CHECK_USR_HAS_FSEC_RESP
660 |
661 | DESCRIPTION
662 | Procedure to check if user has formula security responsibility.
663 | (i-e) Product Development Security manager.
664 |
665 | INPUT PARAMETERS
666 | None
667 |
668 | OUTPUT PARAMETERS
669 | x_return_code VARCHAR2
670 | x_error_msg VARCHAR2
671 |
672 | HISTORY
673 | 23-JUN-2004 S.Sriram Created for Bug# 3700829
674 |
675 +=============================================================================
676 Api end of comments
677 */
678 PROCEDURE CHECK_USR_HAS_FSEC_RESP (x_return_code OUT NOCOPY VARCHAR2,
679 x_error_msg OUT NOCOPY VARCHAR2);
680
681
682 /* Api start of comments
683 +============================================================================
684 | PROCEDURE NAME
685 | Check_fm_orgn_access
686 |
687 | DESCRIPTION
688 | Procedure to check if user with appropriate responsibility
689 | has accesss to the Formula based on its owning organization
690 |
691 | INPUT PARAMETERS
692 | p_formula_id NUMBER
693 |
694 | OUTPUT PARAMETERS
695 | x_return_code VARCHAR2
696 |
697 | HISTORY
698 | 23-Aug-2005 Shyam Initial implementation
699 |
700 +=============================================================================
701 Api end of comments
702 */
703 PROCEDURE Check_fm_orgn_access(p_formula_id IN NUMBER,
704 x_return_code OUT NOCOPY VARCHAR2);
705
706
707 /* Api start of comments
708 +============================================================================
709 | PROCEDURE NAME
710 | Validate_Item_Revision
711 |
712 | DESCRIPTION
713 | Make sure the item revision is valid
714 |
715 | INPUT PARAMETERS
716 | p_organization_id NUMBER
717 | p_item_id NUMBER
718 | p_item_revision VARCHAR
719 |
720 | OUTPUT PARAMETERS
721 | x_return_code VARCHAR2(1)
722 | x_error_msg VARCHAR2(100)
723 |
724 | HISTORY
725 | 01-JAN-2006 Thomas Daniel Created.
726 |
727 +=============================================================================
728 Api end of comments
729 */
730 PROCEDURE Validate_Item_Revision (p_organization_id IN NUMBER,
731 p_item_id IN NUMBER,
732 p_item_revision IN VARCHAR2,
733 x_return_code OUT NOCOPY VARCHAR2,
734 x_error_msg OUT NOCOPY VARCHAR2);
735
736 /* Api start of comments
737 +============================================================================
738 | PROCEDURE NAME
739 | check_item_exists
740 |
741 | DESCRIPTION
742 | Make sure the items in the formula exists under the organization
743 |
744 | INPUT PARAMETERS
745 | p_formula_id NUMBER
746 | p_organization_id NUMBER
747 |
748 | OUTPUT PARAMETERS
749 | x_return_status VARCHAR2(1)
750 |
751 | HISTORY
752 | 27-JAN-2006 Thomas Daniel Created.
753 |
754 +=============================================================================
755 Api end of comments
756 */
757 PROCEDURE Check_Item_Exists (p_formula_id IN NUMBER,
758 p_organization_id IN NUMBER,
759 x_return_status OUT NOCOPY VARCHAR2,
760 x_error_msg OUT NOCOPY VARCHAR2);
761
762 /* Api start of comments
763 +============================================================================
764 | PROCEDURE NAME
765 | CHECK_FORMULA_ITEM_ACCESS
766 |
767 | DESCRIPTION
768 | Checks If the Item is accessible to the formula
769 |
770 | INPUT PARAMETERS
771 | p_formula_id NUMBER
772 | p_organization_id NUMBER
773 | prevision VARCHAR2
774 |
775 | OUTPUT PARAMETERS
776 | x_return_status VARCHAR2(1)
777 | x_error_msg VARCHAR2
778 |
779 | HISTORY
780 | 04-AUG-2006 KapilM Created.
781 |
782 +=============================================================================
783 Api end of comments
784 */
785 PROCEDURE CHECK_FORMULA_ITEM_ACCESS(pFormula_id IN NUMBER,
786 pInventory_Item_ID IN NUMBER,
787 x_return_status OUT NOCOPY VARCHAR2,
788 x_error_msg OUT NOCOPY VARCHAR2 ,
789 pRevision IN VARCHAR2 DEFAULT NULL);
790
791 -- Kapil ME Auto-prod
792 -- Added the following procedure for Auto-Product Qty Calculation ME
793 /* Api start of comments
794 +============================================================================
795 | PROCEDURE NAME
796 | CHECK_AUTO_PRODUCT
797 |
798 | DESCRIPTION
799 | Checks whether Automatic Product QTy Calculation parameter is set at Organization level.
800 |
801 | INPUT PARAMETERS
802 | pOrgn_id NUMBER
803 |
804 | OUTPUT PARAMETERS
805 | pAuto_calc VARCHAR2
806 | x_return_status VARCHAR2
807 | x_error_msg VARCHAR2
808 |
809 | HISTORY
810 | 14-JUN-2006 Kapil M Bug# 5716318 Created.
811 |
812 +=============================================================================
813 Api end of comments
814 */
815
816 PROCEDURE CHECK_AUTO_PRODUCT( pOrgn_id IN NUMBER,
817 pAuto_calc OUT NOCOPY VARCHAR2,
818 x_return_status OUT NOCOPY VARCHAR2,
819 x_error_msg OUT NOCOPY VARCHAR2);
820
821 /* Api start of comments
822 +============================================================================
823 | PROCEDURE NAME
824 | CALCULATE_TOTAL_PRODUCT_QTY
825 |
826 | DESCRIPTION
827 | Procedure to calculate Product Qty autmatically.
828 |
829 | INPUT PARAMETERS
830 | pFormula_id NUMBER
831 |
832 | OUTPUT PARAMETERS
833 | x_msg_data VARCHAR2
834 | x_return_status VARCHAR2
835 | x_msg_count NUMBER
836 |
837 | HISTORY
838 | 14-JUN-2006 Kapil M Bug# 5716318 Created.
839 |
840 +=============================================================================
841 Api end of comments
842 */
843
844 PROCEDURE CALCULATE_TOTAL_PRODUCT_QTY( pFormula_id IN NUMBER,
845 x_return_status OUT NOCOPY VARCHAR2,
846 x_msg_count OUT NOCOPY NUMBER,
847 x_msg_data OUT NOCOPY VARCHAR2);
848 -- Kapil ME Auto-prod
849 END GMD_FORMULA_DESIGNER_PKG;