1 PACKAGE GR_EXPLOSIONS AS
2 /*$Header: GRPXPLNS.pls 120.1 2005/07/08 12:22:45 methomas noship $*/
3
4 /*
5 ** PL/SQL table defined for global use with the package procedures
6 ** to record the lower level formulas that need to be exploded.
7 */
8
9 TYPE explosion_list IS RECORD
10 (organization_id NUMBER,
11 inventory_item_id NUMBER,
12 parent_formula NUMBER,
13 current_formula NUMBER,
14 quantity NUMBER,
15 /*
16 ** M.Thomas 05-Feb-2002 Bug 1323951 Added the following to the PL/SQL Table
17 */
18 weight_pct NUMBER);
19
20 /*
21 ** M.Thomas 05-Feb-2002 Bug 1323951 End of the changes to the PL/SQL Table
22 */
23
24 TYPE t_explosion_list IS TABLE OF explosion_list
25 INDEX BY BINARY_INTEGER;
26
27 /*
28 ** PL/SQL table defined for global use with the package procedures
29 ** to record the missing ingredients while exploding the formula.
30 */
31 TYPE t_missing_ing_list IS TABLE OF gr_item_general.item_code%TYPE
32 INDEX BY BINARY_INTEGER;
33
34 /*
35 ** Datastructures
36 */
37 L_EXPLOSION_LIST GR_EXPLOSIONS.t_explosion_list;
38 L_MISSING_ING_LIST GR_EXPLOSIONS.t_missing_ing_list;
39
40 /* M. Grosser 19-Feb-2002 BUG 1323951 - Added for ingredient list for toxicity calculation */
41 L_INGREDIENT_LIST GR_EXPLOSIONS.t_explosion_list;
42
43
44 /* Alphanumeric Global Variables */
45
46 G_PKG_NAME CONSTANT VARCHAR2(255) := 'GR_EXPLOSIONS';
47 G_CURRENT_DATE DATE := sysdate;
48
49 /* Numeric Global Variables */
50
51 /* 11 Dec 2001 Mercy Thomas Bug 2145449 -- Added value to the Global variable G_USER_ID */
52 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
53
54 /* M. Grosser 19-Feb-2002 BUG 1323951 - Added for ingredient list for toxicity calculation */
55 G_MAX_INGRED NUMBER := 0;
56
57 /* MGROSSER */
58 G_FORMULA_NO VARCHAR2(32);
59 G_FORMULA_VERS NUMBER;
60 G_RECIPE_NO VARCHAR2(32);
61 G_RECIPE_VERS NUMBER;
62
63 PROCEDURE OPM_410_MSDS_Formula
64 (p_commit IN VARCHAR2,
65 p_init_msg_list IN VARCHAR2,
66 p_validation_level IN NUMBER,
67 p_api_version IN NUMBER,
68 p_organization_id NUMBER,
69 p_inventory_item_id IN NUMBER,
70 p_session_id IN NUMBER,
71 x_return_status OUT NOCOPY VARCHAR2,
72 x_msg_count OUT NOCOPY NUMBER,
73 x_msg_data OUT NOCOPY VARCHAR2);
74 PROCEDURE OPM_410_Lab_Formula
75 (p_commit IN VARCHAR2,
76 p_init_msg_list IN VARCHAR2,
77 p_validation_level IN NUMBER,
78 p_api_version IN NUMBER,
79 p_organization_id NUMBER,
80 p_inventory_item_id IN NUMBER,
81 p_session_id IN NUMBER,
82 x_return_status OUT NOCOPY VARCHAR2,
83 x_msg_count OUT NOCOPY NUMBER,
84 x_msg_data OUT NOCOPY VARCHAR2);
85 PROCEDURE OPM_11i_MSDS_Formula
86 (p_commit IN VARCHAR2,
87 p_init_msg_list IN VARCHAR2,
88 p_validation_level IN NUMBER,
89 p_api_version IN NUMBER,
90 p_organization_id NUMBER,
91 p_inventory_item_id IN NUMBER,
92 p_session_id IN NUMBER,
93 x_return_status OUT NOCOPY VARCHAR2,
94 x_msg_count OUT NOCOPY NUMBER,
95 x_msg_data OUT NOCOPY VARCHAR2);
96 PROCEDURE OPM_11i_Lab_Formula
97 (p_commit IN VARCHAR2,
98 p_init_msg_list IN VARCHAR2,
99 p_validation_level IN NUMBER,
100 p_api_version IN NUMBER,
101 p_organization_id NUMBER,
102 p_inventory_item_id IN NUMBER,
103 p_session_id IN NUMBER,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_msg_count OUT NOCOPY NUMBER,
106 x_msg_data OUT NOCOPY VARCHAR2);
107 PROCEDURE Handle_Error_Messages
108 (p_called_by_form IN VARCHAR2,
109 p_message_code IN VARCHAR2,
110 p_token_name IN VARCHAR2,
111 p_token_value IN VARCHAR2,
112 x_msg_count IN OUT NOCOPY NUMBER,
113 x_msg_data IN OUT NOCOPY VARCHAR2,
114 x_return_status OUT NOCOPY VARCHAR2);
115
116 /*
117 ** routine for checking circular reference
118 */
119 FUNCTION check_circular_reference(p_organization_id IN NUMBER,
120 p_inventory_item_id IN NUMBER,
121 p_parent_formula NUMBER,
122 p_max_record NUMBER) RETURN BOOLEAN;
123 /*
124 ** Procedure for Inserting/Updating transactions
125 */
126 PROCEDURE process_concentrations
127 (p_organization_id IN NUMBER,
128 p_inventory_item_id IN NUMBER,
129 p_explosion_item_id IN NUMBER,
130 p_source_item_id IN NUMBER,
131 p_item_percent IN NUMBER,
132 p_current_record IN NUMBER,
133 p_item_um IN VARCHAR2,
134 x_msg_count IN OUT NOCOPY NUMBER,
135 x_msg_data IN OUT NOCOPY VARCHAR2,
136 x_return_status OUT NOCOPY VARCHAR2);
137
138 /*
139 ** M.Thomas 05-Feb-2002 Bug 1323951 Added the following Procedure to build the list of the exploded components
140 ** and return it in table form
141 */
142
143
144 PROCEDURE build_explosion_list
145 (p_commit IN VARCHAR2,
146 p_init_msg_list IN VARCHAR2,
147 p_validation_level IN NUMBER,
148 p_api_version IN NUMBER,
149 p_organization_id NUMBER,
150 p_inventory_item_id IN NUMBER,
151 p_session_id IN NUMBER,
152 x_explosion_list OUT NOCOPY GR_EXPLOSIONS.t_explosion_list,
153 x_return_status OUT NOCOPY VARCHAR2,
154 x_msg_count OUT NOCOPY NUMBER,
155 x_msg_data OUT NOCOPY VARCHAR2);
156
157 /*
158 ** M.Thomas 05-Feb-2002 Bug 1323951 End of the changes to the Procedure to build the list of the exploded components
159 */
160
161
162 /* M. Grosser 19-Feb-2002 BUG 1323951 - Added for ingredient list for toxicity calculation */
163 PROCEDURE add_to_ingredient_list (p_organization_id IN NUMBER,
164 p_inventory_item_id IN NUMBER,
165 p_conc_percent IN NUMBER,
166 p_wt_percent IN NUMBER );
167
168
169 /* Melanie Grosser 20-May-2003 BUG 2932007 - Document Management Phase I
170 Added new procedure OPM_MSDS_Formula_With_IDS
171 to return formula_no, formula_vers, recipe_no, recipe_vers
172 */
173 PROCEDURE OPM_MSDS_Formula_With_IDS
174 (p_commit IN VARCHAR2,
175 p_init_msg_list IN VARCHAR2,
176 p_validation_level IN NUMBER,
177 p_api_version IN NUMBER,
178 p_organization_id NUMBER,
179 p_inventory_item_id IN NUMBER,
180 p_session_id IN NUMBER,
181 x_formula_no OUT NOCOPY VARCHAR2,
182 x_formula_vers OUT NOCOPY NUMBER,
183 x_recipe_no OUT NOCOPY VARCHAR2,
184 x_recipe_vers OUT NOCOPY NUMBER,
185 x_return_status OUT NOCOPY VARCHAR2,
186 x_msg_count OUT NOCOPY NUMBER,
187 x_msg_data OUT NOCOPY VARCHAR2);
188 END GR_EXPLOSIONS;
[Home] [Help]