1 PACKAGE GMD_FORMULATION_SPECS_PKG AS
2 /* $Header: GMDFSTHS.pls 120.3 2006/02/07 02:50:19 srsriran noship $ */
3
4 /* Formulation Specification - Table Handlers */
5
6 PROCEDURE INSERT_FORMULATION_SPEC(
7 X_ROWID OUT NOCOPY VARCHAR2 ,
8 X_FORMULATION_SPEC_ID IN NUMBER ,
9 X_SPEC_VERS IN NUMBER ,
10 X_PRODUCT_ID IN NUMBER ,
11 X_OWNER_ORGANIZATION_ID IN NUMBER ,
12 X_SPEC_STATUS IN VARCHAR2 ,
13 X_STD_QTY IN NUMBER ,
14 X_STD_UOM IN VARCHAR2 ,
15 X_PROCESS_LOSS IN NUMBER ,
16 X_START_DATE IN DATE ,
17 X_END_DATE IN DATE ,
18 X_MIN_INGREDS IN NUMBER ,
19 X_MAX_INGREDS IN NUMBER ,
20 X_INGRED_PICK_BASE_IND IN VARCHAR2 ,
21 X_PICK_LOT_STRATEGY IN VARCHAR2 ,
22 X_TECH_PARM_ID IN NUMBER ,
23 X_OBJECTIVE_IND IN NUMBER ,
24 X_ROUTING_ID IN NUMBER ,
25 X_SPEC_NAME IN VARCHAR2 ,
26 X_TEXT_CODE IN VARCHAR2 ,
27 X_DELETE_MARK IN NUMBER ,
28 X_CREATION_DATE IN DATE ,
29 X_CREATED_BY IN NUMBER ,
30 X_LAST_UPDATE_DATE IN DATE ,
31 X_LAST_UPDATED_BY IN NUMBER ,
32 X_LAST_UPDATE_LOGIN IN NUMBER
33 );
34
35 PROCEDURE LOCK_FORMULATION_SPEC (
36 X_FORMULATION_SPEC_ID IN NUMBER ,
37 X_SPEC_VERS IN NUMBER ,
38 X_PRODUCT_ID IN NUMBER ,
39 X_OWNER_ORGANIZATION_ID IN NUMBER ,
40 X_SPEC_STATUS IN VARCHAR2 ,
41 X_STD_QTY IN NUMBER ,
42 X_STD_UOM IN VARCHAR2 ,
43 X_PROCESS_LOSS IN NUMBER ,
44 X_START_DATE IN DATE ,
45 X_END_DATE IN DATE ,
46 X_MIN_INGREDS IN NUMBER ,
47 X_MAX_INGREDS IN NUMBER ,
48 X_INGRED_PICK_BASE_IND IN VARCHAR2 ,
49 X_PICK_LOT_STRATEGY IN VARCHAR2 ,
50 X_TECH_PARM_ID IN NUMBER ,
51 X_OBJECTIVE_IND IN NUMBER ,
52 X_TEXT_CODE IN VARCHAR2 ,
53 X_DELETE_MARK IN NUMBER ,
54 X_SPEC_NAME IN VARCHAR2
55 );
56
57
58 PROCEDURE UPDATE_FORMULATION_SPEC (
59 X_FORMULATION_SPEC_ID IN NUMBER ,
60 X_PRODUCT_ID IN NUMBER ,
61 X_OWNER_ORGANIZATION_ID IN NUMBER ,
62 X_SPEC_STATUS IN VARCHAR2 ,
63 X_STD_QTY IN NUMBER ,
64 X_STD_UOM IN VARCHAR2 ,
65 X_PROCESS_LOSS IN NUMBER ,
66 X_START_DATE IN DATE ,
67 X_END_DATE IN DATE ,
68 X_MIN_INGREDS IN NUMBER ,
69 X_MAX_INGREDS IN NUMBER ,
70 X_INGRED_PICK_BASE_IND IN VARCHAR2 ,
71 X_PICK_LOT_STRATEGY IN VARCHAR2 ,
72 X_TECH_PARM_ID IN NUMBER ,
73 X_OBJECTIVE_IND IN NUMBER ,
74 X_TEXT_CODE IN VARCHAR2 ,
75 X_DELETE_MARK IN NUMBER ,
76 X_LAST_UPDATE_DATE IN DATE ,
77 X_LAST_UPDATED_BY IN NUMBER ,
78 X_LAST_UPDATE_LOGIN IN NUMBER
79 );
80
81 PROCEDURE DELETE_FORMULATION_SPEC (
82 X_FORMULATION_SPEC_ID IN NUMBER
83 );
84
85 /* END - Formulation Specification - Table Handlers */
86
87
88
89 /* Material Req - Table Handlers */
90
91 PROCEDURE INSERT_MATERIAL_REQ (
92 X_ROWID OUT NOCOPY VARCHAR2 ,
93 X_MATL_REQ_ID IN NUMBER ,
94 X_FORMULATION_SPEC_ID IN NUMBER ,
95 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
96 X_LINE_NO IN NUMBER ,
97 X_INVENTORY_ITEM_ID IN NUMBER ,
98 X_ITEM_UOM IN VARCHAR2 ,
99 X_MIN_QTY IN NUMBER ,
100 X_MAX_QTY IN NUMBER ,
101 X_RANGE_TYPE IN NUMBER ,
102 X_CREATION_DATE IN DATE ,
103 X_CREATED_BY IN NUMBER ,
104 X_LAST_UPDATE_DATE IN DATE ,
105 X_LAST_UPDATED_BY IN NUMBER ,
106 X_LAST_UPDATE_LOGIN IN NUMBER
107 );
108
109
110 PROCEDURE LOCK_MATERIAL_REQ (
111 X_MATL_REQ_ID IN NUMBER ,
112 X_FORMULATION_SPEC_ID IN NUMBER ,
113 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
114 X_LINE_NO IN NUMBER ,
115 X_INVENTORY_ITEM_ID IN NUMBER ,
116 X_ITEM_UOM IN VARCHAR2 ,
117 X_MIN_QTY IN NUMBER ,
118 X_MAX_QTY IN NUMBER ,
119 X_RANGE_TYPE IN NUMBER
120 );
121
122
123 PROCEDURE UPDATE_MATERIAL_REQ (
124 X_MATL_REQ_ID IN NUMBER ,
125 X_FORMULATION_SPEC_ID IN NUMBER ,
126 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
127 X_LINE_NO IN NUMBER ,
128 X_INVENTORY_ITEM_ID IN NUMBER ,
129 X_ITEM_UOM IN VARCHAR2 ,
130 X_MIN_QTY IN NUMBER ,
131 X_MAX_QTY IN NUMBER ,
132 X_RANGE_TYPE IN NUMBER ,
133 X_LAST_UPDATE_DATE IN DATE ,
134 X_LAST_UPDATED_BY IN NUMBER ,
135 X_LAST_UPDATE_LOGIN IN NUMBER
136 );
137
138
139 PROCEDURE DELETE_MATERIAL_REQ (
140 X_MATL_REQ_ID IN NUMBER
141 );
142
143 /* END - Material Req - Table Handlers */
144
145
146 /* Compositional Req - Table Handlers */
147
148 PROCEDURE INSERT_COMPOSITIONAL_REQ (
149 X_ROWID OUT NOCOPY VARCHAR2 ,
150 X_COMP_REQ_ID IN NUMBER ,
151 X_FORMULATION_SPEC_ID IN NUMBER ,
152 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
153 X_MIN_PCT IN NUMBER ,
154 X_MAX_PCT IN NUMBER ,
155 X_CATEGORY_SET_ID IN NUMBER ,
156 X_CATEGORY_ID IN NUMBER ,
157 X_PLANNED_PCT IN NUMBER ,
158 X_ORDER_NO IN NUMBER ,
159 X_CREATION_DATE IN DATE ,
160 X_CREATED_BY IN NUMBER ,
161 X_LAST_UPDATE_DATE IN DATE ,
162 X_LAST_UPDATED_BY IN NUMBER ,
163 X_LAST_UPDATE_LOGIN IN NUMBER
164 );
165
166
167 PROCEDURE LOCK_COMPOSITIONAL_REQ (
168 X_COMP_REQ_ID IN NUMBER ,
169 X_FORMULATION_SPEC_ID IN NUMBER ,
170 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
171 X_MIN_PCT IN NUMBER ,
172 X_MAX_PCT IN NUMBER ,
173 X_CATEGORY_SET_ID IN NUMBER ,
174 X_CATEGORY_ID IN NUMBER ,
175 X_PLANNED_PCT IN NUMBER ,
176 X_ORDER_NO IN NUMBER
177 );
178
179
180 PROCEDURE UPDATE_COMPOSITIONAL_REQ (
181 X_COMP_REQ_ID IN NUMBER ,
182 X_FORMULATION_SPEC_ID IN NUMBER ,
183 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
184 X_MIN_PCT IN NUMBER ,
185 X_MAX_PCT IN NUMBER ,
186 X_CATEGORY_SET_ID IN NUMBER ,
187 X_CATEGORY_ID IN NUMBER ,
188 X_PLANNED_PCT IN NUMBER ,
189 X_ORDER_NO IN NUMBER ,
190 X_LAST_UPDATE_DATE IN DATE ,
191 X_LAST_UPDATED_BY IN NUMBER ,
192 X_LAST_UPDATE_LOGIN IN NUMBER
193 );
194
195
196 PROCEDURE DELETE_COMPOSITIONAL_REQ (
197 X_COMP_REQ_ID IN NUMBER
198 );
199
200 /* END - Compositional Req - Table Handlers */
201
202
203 /* Technical Req - Table Handlers */
204
205 PROCEDURE INSERT_TECHNICAL_REQ (
206 X_ROWID OUT NOCOPY VARCHAR2 ,
207 X_TECH_REQ_ID IN NUMBER ,
208 X_FORMULATION_SPEC_ID IN NUMBER ,
209 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
210 X_TECH_PARM_ID IN NUMBER ,
211 X_MIN_VALUE IN NUMBER ,
212 X_MAX_VALUE IN NUMBER ,
213 X_CREATION_DATE IN DATE ,
214 X_CREATED_BY IN NUMBER ,
215 X_LAST_UPDATE_DATE IN DATE ,
216 X_LAST_UPDATED_BY IN NUMBER ,
217 X_LAST_UPDATE_LOGIN IN NUMBER
218 );
219
220
221 PROCEDURE LOCK_TECHNICAL_REQ (
222 X_TECH_REQ_ID IN NUMBER ,
223 X_FORMULATION_SPEC_ID IN NUMBER ,
224 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
225 X_TECH_PARM_ID IN NUMBER ,
226 X_MIN_VALUE IN NUMBER ,
227 X_MAX_VALUE IN NUMBER
228 );
229
230
231 PROCEDURE UPDATE_TECHNICAL_REQ (
232 X_TECH_REQ_ID IN NUMBER ,
233 X_FORMULATION_SPEC_ID IN NUMBER ,
234 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
235 X_TECH_PARM_ID IN NUMBER ,
236 X_MIN_VALUE IN NUMBER ,
237 X_MAX_VALUE IN NUMBER ,
238 X_LAST_UPDATE_DATE IN DATE ,
239 X_LAST_UPDATED_BY IN NUMBER ,
240 X_LAST_UPDATE_LOGIN IN NUMBER
241 );
242
243
244 PROCEDURE DELETE_TECHNICAL_REQ (
245 X_TECH_REQ_ID IN NUMBER
246 );
247
248 /* END - Technical Req - Table Handlers */
249
250
251 /* Specification Attributes - Table Handlers */
252
253 PROCEDURE INSERT_SPEC_ATTRIBUTE (
254 X_ROWID OUT NOCOPY VARCHAR2 ,
255 X_SPEC_ATTRIBUTE_ID IN NUMBER ,
256 X_FORMULATION_SPEC_ID IN NUMBER ,
257 X_LOOKUP_TYPE IN VARCHAR2 ,
258 X_LOOKUP_CODE IN VARCHAR2 ,
259 X_CREATION_DATE IN DATE ,
260 X_CREATED_BY IN NUMBER ,
261 X_LAST_UPDATE_DATE IN DATE ,
262 X_LAST_UPDATED_BY IN NUMBER ,
263 X_LAST_UPDATE_LOGIN IN NUMBER);
264
265 PROCEDURE DELETE_SPEC_ATTRIBUTE (
266 X_SPEC_ATTRIBUTE_ID IN NUMBER
267 );
268
269 /* END - Specification Attributes - Table Handlers */
270
271 l_spec_rec gmd_formulation_specs%ROWTYPE;
272 TYPE l_spec_table IS TABLE OF l_spec_rec%ROWTYPE INDEX BY BINARY_INTEGER;
273
274 /*-------------------------------------------------------------------
275 -- NAME
276 -- Get_specifications
277 --
278 -- SYNOPSIS
279 -- Procedure Get_specifications
280 --
281 -- DESCRIPTION
282 -- This procedure is called to fetch specifications based on search
283 -- condition passed to the API
284 --
285 --
286 -- HISTORY
287 -- Sriram 9/05/2005 Created for LCF Build
288 --------------------------------------------------------------------*/
289
290 PROCEDURE Get_specifications( p_spec_no IN VARCHAR2 DEFAULT NULL ,
291 p_spec_vers IN NUMBER DEFAULT NULL ,
292 p_spec_status IN NUMBER DEFAULT NULL ,
293 p_product IN VARCHAR2 DEFAULT NULL ,
294 p_product_id IN NUMBER DEFAULT NULL ,
295 p_routing IN VARCHAR2 DEFAULT NULL ,
296 p_routing_id IN NUMBER DEFAULT NULL ,
297 p_tech_parm_name IN VARCHAR2 DEFAULT NULL ,
298 p_tech_parm_id IN NUMBER DEFAULT NULL ,
299 p_spec_organization IN VARCHAR2 DEFAULT NULL ,
300 p_start_date IN VARCHAR2 DEFAULT NULL ,
301 p_end_date IN VARCHAR2 DEFAULT NULL ,
302 p_min_ingreds IN NUMBER DEFAULT NULL ,
303 p_max_ingreds IN NUMBER DEFAULT NULL ,
304 p_process_loss IN NUMBER DEFAULT NULL ,
305 p_obj_ind IN NUMBER DEFAULT NULL ,
306 p_ingr_pick_base IN VARCHAR2 DEFAULT NULL ,
307 p_lot_pick_strategy IN VARCHAR2 DEFAULT NULL ,
308 p_std_qty IN NUMBER DEFAULT NULL ,
309 p_std_uom IN VARCHAR2 DEFAULT NULL ,
310 x_search_clause IN OUT NOCOPY VARCHAR2 ,
311 x_spec_rec OUT NOCOPY GMD_FORMULATION_SPECS_PKG.l_spec_table
312 );
313
314 END GMD_FORMULATION_SPECS_PKG;
315
[Home] [Help]