1 PACKAGE wms_task_load AUTHID CURRENT_USER AS
2 /* $Header: WMSLOADS.pls 120.8.12020000.5 2013/04/13 08:08:36 raminoch ship $ */
3
4 TYPE lpn_lot_qty_rec IS RECORD(
5 lpn_id NUMBER
6 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
7 , lot_number VARCHAR2(80)
8 , pri_qty NUMBER
9 , trx_qty NUMBER
10 , sec_qty NUMBER -- Bug #4141928
11 , non_alloc_qty NUMBER --jxlu 10/22/04 check serial ATT for allocated serial
12 );
13
14
15 TYPE lpn_lot_qty_tb IS TABLE OF lpn_lot_qty_rec
16 INDEX BY BINARY_INTEGER;
17
18 -- PL/SQL TABLE used to store lot_number and qty for passed in lpn_id
19
20 --/* Bug 9448490 Lot Substitution Project */ start
21 PROCEDURE populate_lot_lov(
22 p_fromlpn_id IN NUMBER
23 , p_org_id IN NUMBER
24 , p_item_id IN NUMBER
25 , p_rev IN VARCHAR2
26 , p_lot IN VARCHAR2
27 , p_trx_qty IN NUMBER
28 , p_trx_uom IN VARCHAR2
29 , x_match OUT NOCOPY NUMBER
30 , x_return_status OUT NOCOPY VARCHAR2
31 , p_temp_id IN NUMBER
32 , p_transaction_type_id IN NUMBER
33 , p_cost_group_id IN NUMBER
34 , p_is_sn_alloc IN VARCHAR2
35 , p_user_id IN NUMBER
36 , x_lpn_lot_vector OUT NOCOPY VARCHAR2
37 , x_lpn_lot_vector2 OUT NOCOPY VARCHAR2 --BUG14003388
38 , x_lpn_lot_vector3 OUT NOCOPY VARCHAR2 --BUG14003388
39 , x_lpn_lot_vector4 OUT NOCOPY VARCHAR2 --BUG14003388
40 , p_transaction_action_id IN NUMBER --BV TODO Add project and task inputs and put in SKU?
41 , p_confirmed_sub IN VARCHAR2
42 , p_confirmed_loc_id IN NUMBER
43 , p_from_lpn_id IN NUMBER
44 );
45
46 --/* Bug 9448490 Lot Substitution Project */ end
47
48 t_lpn_lot_qty_table lpn_lot_qty_tb;
49
50
51 PROCEDURE task_load(
52 p_action IN VARCHAR2
53 -- LOAD_SINGLE/LOAD_MULTIPLE/SPLIT/UPDATE_LOADED
54 , p_organization_id IN NUMBER
55 , p_user_id IN NUMBER
56 , p_person_id IN NUMBER
57 , p_transaction_header_id IN NUMBER
58 , p_temp_id IN NUMBER
59 , p_parent_line_id IN NUMBER -- For bulk parent
60 , p_lpn_id IN NUMBER
61 , p_content_lpn_id IN NUMBER
62 , p_transfer_lpn_id IN NUMBER
63 , p_confirmed_sub IN VARCHAR2
64 , p_confirmed_loc_id IN NUMBER
65 , p_confirmed_uom IN VARCHAR2
66 , p_suggested_uom IN VARCHAR2
67 , p_primary_uom IN VARCHAR2 -- 03/02/04
68 , p_item_id IN NUMBER
69 , p_revision IN VARCHAR2
70 , p_confirmed_qty IN NUMBER
71 , p_confirmed_lots IN VARCHAR2
72 , p_confirmed_lot_trx_qty IN VARCHAR2
73 , p_confirmed_sec_uom IN VARCHAR2
74 , p_confirmed_sec_qty IN VARCHAR2
75 , p_confirmed_serials IN VARCHAR2
76 , p_container_item_id IN NUMBER
77 , p_transaction_type_id IN NUMBER
78 , p_transaction_source_type_id IN NUMBER
79 , p_lpn_match IN NUMBER
80 , p_lpn_match_lpn_id IN NUMBER
81 , p_serial_allocated_flag IN VARCHAR2 -- Y/V or NULL
82 , p_lot_controlled IN VARCHAR2 -- Y/N
83 , p_serial_controlled IN VARCHAR2 -- Y/N
84 , p_effective_start_date IN DATE
85 , p_effective_end_date IN DATE
86 , p_exception IN VARCHAR2 -- SHORT, OVER
87 , p_discrepancies IN VARCHAR2
88 , p_qty_rsn_id IN NUMBER
89 , p_parent_lpn_id IN NUMBER
90 , p_lpnpickedasis IN VARCHAR2 -- Y/N
91 , x_new_transaction_temp_id OUT NOCOPY NUMBER
92 , x_cms_check OUT NOCOPY VARCHAR2
93 , x_return_status OUT NOCOPY VARCHAR2
94 , x_msg_count OUT NOCOPY NUMBER
95 , x_msg_data OUT NOCOPY VARCHAR2
96 , p_substitute_lots IN VARCHAR2--/* Bug 9448490 Lot Substitution Project */
97 , p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y' -- Added for 14699845 (Flexible Lot Allocation)
98 , p_fulfillment_base IN VARCHAR2 DEFAULT 'P');-- 16070349 raminoch
99
100 --BUG12622871LSC Procedure to convert qty to suggested UoM
101 PROCEDURE proc_convert_qty_to_sugg_uom
102 (p_inventory_item_id IN NUMBER
103 ,p_organization_id IN NUMBER
104 ,p_lpn_match_lpn_id IN NUMBER
105 ,p_confirmed_trx_qty IN NUMBER
106 ,p_lot_controlled IN VARCHAR2
107 ,p_confirmed_lots IN VARCHAR2
108 ,p_confirmed_lot_trx_qty IN VARCHAR2
109 ,p_suggested_uom IN VARCHAR2
110 ,p_confirmed_uom IN VARCHAR2
111 ,p_primary_uom IN VARCHAR2
112 ,p_confirmed_sec_uom IN VARCHAR2
113 ,p_confirmed_sec_qty IN VARCHAR2
114 ,p_fulfillment_base IN VARCHAR2 DEFAULT 'P' --muom:sk
115 ,p_confirmed_sub IN VARCHAR2
116 ,p_confirmed_locator_id IN NUMBER
117 ,p_revision IN VARCHAR2
118 ,x_confirmed_prim_qty OUT NOCOPY NUMBER
119 ,x_confirmed_sec_qty OUT NOCOPY NUMBER --muom:sk
120 ,x_confirmed_sugg_qty OUT NOCOPY NUMBER
121 ,x_confirmed_pri_qty_string OUT NOCOPY VARCHAR2
122 ,x_qoh OUT NOCOPY NUMBER
123 ,x_sqoh OUT NOCOPY NUMBER
124 ,x_att OUT NOCOPY NUMBER
125 ,x_satt OUT NOCOPY NUMBER
126 ,x_return_status OUT NOCOPY VARCHAR2
127 ,x_msg_count OUT NOCOPY NUMBER
128 ,x_msg_data OUT NOCOPY VARCHAR2
129 );
130
131 PROCEDURE task_merge_split(
132 p_action IN VARCHAR2 -- LOAD_MULTIPLE/LOAD_SINGLE/SPLIT
133 ,p_exception IN VARCHAR2 -- SHORT/OVER
134 ,p_organization_id IN NUMBER
135 ,p_user_id IN NUMBER
136 ,p_transaction_header_id IN NUMBER
137 ,p_transaction_temp_id IN NUMBER
138 ,p_parent_line_id IN NUMBER
139 ,p_remaining_temp_id IN NUMBER
140 ,p_lpn_id IN NUMBER
141 ,p_content_lpn_id IN NUMBER
142 ,p_transfer_lpn_id IN NUMBER
143 ,p_confirmed_sub IN VARCHAR2
144 ,p_confirmed_locator_id IN NUMBER
145 ,p_confirmed_uom IN VARCHAR2
146 ,p_suggested_uom IN VARCHAR2
147 ,p_primary_uom IN VARCHAR2 -- 03/02/04
148 ,p_inventory_item_id IN NUMBER
149 ,p_revision IN VARCHAR2
150 ,p_confirmed_trx_qty IN NUMBER
151 ,p_confirmed_lots IN VARCHAR2
152 ,p_confirmed_lot_trx_qty IN VARCHAR2
153 ,p_confirmed_sec_uom IN VARCHAR2
154 ,p_confirmed_sec_qty IN VARCHAR2
155 ,p_confirmed_serials IN VARCHAR2
156 ,p_container_item_id IN NUMBER
157 ,p_lpn_match IN NUMBER
158 ,p_lpn_match_lpn_id IN NUMBER
159 ,p_serial_allocated_flag IN VARCHAR2
160 ,p_lot_controlled IN VARCHAR2 -- Y/N
161 ,p_serial_controlled IN VARCHAR2 -- Y/N
162 ,p_parent_lpn_id IN NUMBER
163 ,x_new_transaction_temp_id OUT NOCOPY NUMBER
164 ,x_cms_check OUT NOCOPY VARCHAR2
165 ,x_return_status OUT NOCOPY VARCHAR2
166 ,x_msg_count OUT NOCOPY NUMBER
167 ,x_msg_data OUT NOCOPY VARCHAR2
168 ,p_substitute_lots IN VARCHAR2--/* Bug 9448490 Lot Substitution Project */
169 ,p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y' -- Added for 14699845 (Flexible Lot Allocation)
170 ,p_fulfillment_base IN VARCHAR2 DEFAULT 'P'); -- 16070349 raminoch
171
172 PROCEDURE process_F2(
173 p_action IN VARCHAR2 -- NULL/CMS
174 ,p_organization_id IN NUMBER
175 ,p_user_id IN NUMBER
176 ,p_employee_id IN NUMBER
177 ,p_transaction_header_id IN NUMBER
178 ,p_transaction_temp_id IN NUMBER
179 ,p_original_sub IN VARCHAR2
180 ,p_original_locator_id IN NUMBER
181 ,p_lot_controlled IN VARCHAR2 -- Y/N
182 ,p_serial_controlled IN VARCHAR2 -- Y/N
183 ,p_serial_allocated_flag IN VARCHAR2 -- Y/N
184 ,p_suggested_uom IN VARCHAR2 -- original allocation UOM -- 03/02/04
185 ,p_start_over IN VARCHAR2 -- Y/Nstart_over button
186 ,p_retain_task IN VARCHAR2 -- Y/N for bug 4310093
187 ,x_start_over_taskno OUT NOCOPY NUMBER -- start_over
188 ,x_return_status OUT NOCOPY VARCHAR2
189 ,x_msg_count OUT NOCOPY NUMBER
190 ,x_msg_data OUT NOCOPY VARCHAR2) ;
191
192 -- This lpn will be used during the picking process. If the user specifies
193 -- a from lpn, this procedure will figure out if the lpn in question will
194 -- satisfy the pick in question
195 -- It will return 1 if this is the case, 0 if not and 2 if the item does
196 -- not exist in the lpn, 3 if the qty is not adequate and 4 if it already
197 -- has been loaded
198
199 PROCEDURE lpn_match(
200 p_fromlpn_id IN NUMBER
201 , p_org_id IN NUMBER
202 , p_item_id IN NUMBER
203 , p_rev IN VARCHAR2
204 , p_lot IN VARCHAR2
205 , p_trx_qty IN NUMBER
206 , p_trx_uom IN VARCHAR2
207 , p_sec_qty IN NUMBER -- Bug #4141928
208 , p_sec_uom IN VARCHAR2 -- Bug #4141928
209 , x_match OUT NOCOPY NUMBER
210 , x_sub OUT NOCOPY VARCHAR2
211 , x_loc OUT NOCOPY VARCHAR2
212 , x_trx_qty OUT NOCOPY NUMBER
213 , x_trx_sec_qty OUT NOCOPY NUMBER -- Bug #4141928
214 , x_return_status OUT NOCOPY VARCHAR2
215 , x_msg_count OUT NOCOPY NUMBER
216 , x_msg_data OUT NOCOPY VARCHAR2
217 , p_temp_id IN NUMBER
218 , p_parent_line_id IN NUMBER
219 , p_wms_installed IN VARCHAR2
220 , p_transaction_type_id IN NUMBER
221 , p_cost_group_id IN NUMBER
222 , p_is_sn_alloc IN VARCHAR2
223 , p_action IN NUMBER
224 , p_split IN VARCHAR2
225 , p_user_id IN NUMBER
226 , x_temp_id OUT NOCOPY NUMBER
227 , x_loc_id OUT NOCOPY NUMBER
228 , x_lpn_lot_vector OUT NOCOPY VARCHAR2
229 , x_lpn_lot_vector2 OUT NOCOPY VARCHAR2-- bug 14003388
230 , x_lpn_lot_vector3 OUT NOCOPY VARCHAR2-- bug 14003388
231 , x_lpn_lot_vector4 OUT NOCOPY VARCHAR2-- bug 14003388
232 , x_cms_check OUT NOCOPY VARCHAR2
233 , x_parent_lpn_id OUT NOCOPY VARCHAR2
234 , x_trx_qty_alloc OUT NOCOPY NUMBER --jxlu 10/12/04
235 , p_transaction_action_id IN NUMBER --jxlu
236 , p_pickOverNoException IN VARCHAR2
237 , p_toLPN_Default IN VARCHAR2 -- Bug 3855835
238 , p_project_id IN NUMBER
239 , p_task_id IN NUMBER
240 , p_confirmed_sub IN VARCHAR2
241 , p_confirmed_loc_id IN NUMBER
242 , p_from_lpn_id IN NUMBER
243 , x_toLPN_status OUT NOCOPY VARCHAR2 --Bug 3855835
244 , x_lpnpickedasis OUT NOCOPY VARCHAR2
245 , x_lpn_qoh OUT NOCOPY NUMBER
246 , p_changelotNoException IN VARCHAR2 --/* Bug 9448490 Lot Substitution Project */
247 , p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y' -- Added for 14699845 (Flexible Lot Allocation)
248 , p_fulfillment_base IN VARCHAR2 DEFAULT 'P');-- 16070349 raminoch
249
250 -- during the picking process. If the user does not specifies
251 -- a from lpn, this procedure will figure out if the loose quantity will
252 -- satisfy the pick in question, the temp table mtl_allocations_gtmp
253 -- will store the available lot and serial numbers for this pick
254
255 PROCEDURE loose_match(
256 p_org_id IN NUMBER
257 , p_item_id IN NUMBER
258 , p_rev IN VARCHAR2
259 , p_trx_qty IN NUMBER
260 , p_trx_uom IN VARCHAR2
261 , p_pri_uom IN VARCHAR2
262 , p_sec_uom IN VARCHAR2 -- Bug #4141928
263 , p_sec_qty IN NUMBER -- Bug #4141928
264 , p_temp_id IN NUMBER
265 , p_suggested_locator IN NUMBER
266 , p_confirmed_locator IN NUMBER
267 , p_confirmed_sub IN VARCHAR2
268 , p_is_sn_alloc IN VARCHAR2
269 , p_is_revision_control IN VARCHAR2
270 , p_is_lot_control IN VARCHAR2
271 , p_is_serial_control IN VARCHAR2
275 , p_task_id IN NUMBER
272 , p_is_negbal_allowed IN VARCHAR2 --vikas 09/07/04 v1
273 , p_toLPN_Default IN VARCHAR2 -- Bug 3855835
274 , p_project_id IN NUMBER
276 , x_trx_qty OUT NOCOPY NUMBER
277 , x_trx_sec_qty OUT NOCOPY NUMBER -- Bug #4141928
278 , x_return_status OUT NOCOPY VARCHAR2
279 , x_msg_count OUT NOCOPY NUMBER
280 , x_msg_data OUT NOCOPY VARCHAR2
281 , x_toLPN_status OUT NOCOPY VARCHAR2 --Bug 3855835
282 , x_lot_att_vector OUT NOCOPY VARCHAR2
283 , x_lot_att_vector2 OUT NOCOPY VARCHAR2-- bug 14003388
284 , x_lot_att_vector3 OUT NOCOPY VARCHAR2-- bug 14003388
285 , x_lot_att_vector4 OUT NOCOPY VARCHAR2-- bug 14003388
286 , x_trx_qty_alloc OUT NOCOPY NUMBER -- jxlu 10/6/04
287 , p_transaction_type_id IN NUMBER -- Bug 4632519
288 , p_transaction_action_id IN NUMBER -- Bug 4632519
289 , p_changelotNoException IN VARCHAR2 --/* Bug 9448490 Lot Substitution Project */
290 , p_full_lot_allocation IN VARCHAR2 DEFAULT 'Y' -- Added for 14699845 (Flexible Lot Allocation)
291 , p_fulfillment_base IN VARCHAR2 DEFAULT 'P');-- 16070349 raminoch
292
293
294 FUNCTION can_pickdrop(p_transaction_temp_id IN NUMBER)
295 RETURN VARCHAR2;
296
297
298 /* This API will return the number of tasks that still need to be performed
299 for a given carton. If it returns more than 1, the user should not be
300 allowed to drop off the carton*/
301 PROCEDURE check_pack_lpn(
302 p_lpn IN VARCHAR2
303 , p_org_id IN NUMBER
304 , p_container_item_id IN NUMBER
305 , p_temp_id IN NUMBER --Bug7120019
306 , x_lpn_id OUT NOCOPY NUMBER
307 , x_lpn_context OUT NOCOPY NUMBER
308 , x_outermost_lpn_id OUT NOCOPY NUMBER
309 , x_pick_to_lpn_exists OUT NOCOPY BOOLEAN
310 , x_return_status OUT NOCOPY VARCHAR2
311 , x_msg_count OUT NOCOPY NUMBER
312 , x_msg_data OUT NOCOPY VARCHAR2
313 );
314
315 PROCEDURE validate_pick_to_lpn(
316 p_api_version_number IN NUMBER
317 , p_init_msg_lst IN VARCHAR2 := fnd_api.g_false
318 , x_return_status OUT NOCOPY VARCHAR2
319 , x_msg_count OUT NOCOPY NUMBER
320 , x_msg_data OUT NOCOPY VARCHAR2
321 , p_organization_id IN NUMBER
322 , p_pick_to_lpn IN VARCHAR2
323 , p_temp_id IN NUMBER
324 , p_project_id IN NUMBER := NULL
325 , p_task_id IN NUMBER := NULL
326 , p_container_item IN VARCHAR2
327 , p_container_item_id IN NUMBER
328 , p_suggested_container_item IN VARCHAR2
329 , p_suggested_container_item_id IN NUMBER
330 , p_suggested_carton_name IN VARCHAR2
331 , p_suggested_tolpn_id IN NUMBER
332 , x_pick_to_lpn_id OUT NOCOPY NUMBER
333 , p_inventory_item_id IN NUMBER
334 , p_confirmed_sub IN VARCHAR2
335 , p_confirmed_loc_id IN NUMBER
336 , p_revision IN VARCHAR2
337 , p_confirmed_lots IN VARCHAR2
338 , p_from_lpn_id IN NUMBER
339 , p_lot_control IN VARCHAR2
340 , p_revision_control IN VARCHAR2
341 , p_serial_control IN VARCHAR2
342 , p_trx_type_id IN VARCHAR2 -- Bug 4632519
343 , p_trx_action_id IN VARCHAR2 -- Bug 4632519
344 );
345
346 PROCEDURE validate_sub_loc_status(
347 p_wms_installed IN VARCHAR2
348 , p_temp_id IN NUMBER
349 , p_confirmed_sub IN VARCHAR2
350 , p_confirmed_loc_id IN NUMBER
351 , x_return_status OUT NOCOPY VARCHAR2
352 , x_msg_count OUT NOCOPY NUMBER
353 , x_msg_data OUT NOCOPY VARCHAR2
354 , x_result OUT NOCOPY NUMBER
355 );
356
357
358 PROCEDURE insert_serial(
359 p_serial_transaction_temp_id IN OUT NOCOPY NUMBER,
360 p_organization_id IN NUMBER,
361 p_item_id IN NUMBER,
362 p_revision IN VARCHAR2,
363 p_lot IN VARCHAR2,
364 p_transaction_temp_id IN NUMBER,
365 p_created_by IN NUMBER,
366 p_from_serial IN VARCHAR2,
367 p_to_serial IN VARCHAR2,
368 p_status_id IN NUMBER := NULL,
369 x_return_status OUT NOCOPY VARCHAR2,
370 x_msg_data OUT NOCOPY VARCHAR2
371 ) ;
372
373 FUNCTION check_if_lot_is_substituted ( p_substitute_lots VARCHAR2
374 , p_current_lot VARCHAR2
375 , p_full_lot_allocation VARCHAR2)-- Added for 14699845 (Flexible Lot Allocation)
376 RETURN BOOLEAN; --BUG12670785
377
378 PROCEDURE item_lot_divisible_flag (
379 p_org_id IN NUMBER
380 ,p_inventory_item_id IN NUMBER
381 ,x_lot_divisible_flag OUT NOCOPY VARCHAR2);
382
383 --16267113
384 PROCEDURE Update_Tree_Flex_Lot (p_fromlpn_id IN NUMBER,
385 p_org_id IN NUMBER,
386 p_item_id IN NUMBER,
387 p_rev IN VARCHAR2,
388 p_trx_qty IN NUMBER,
389 p_trx_uom IN VARCHAR2,
390 p_sec_qty IN NUMBER,
391 p_sec_uom IN VARCHAR2,
392 p_lot IN VARCHAR2,
393 p_temp_id IN NUMBER,
394 p_transaction_type_id IN NUMBER,
395 p_user_id IN NUMBER,
396 p_transaction_action_id IN NUMBER ,
397 p_confirmed_sub IN VARCHAR2,
398 p_confirmed_loc_id IN NUMBER,
399 p_full_lot_allocation IN VARCHAR2,
400 p_fulfillment_base IN VARCHAR2,
401 x_lpn_lot_vector OUT NOCOPY VARCHAR2,
402 x_lpn_lot_vector2 OUT NOCOPY VARCHAR2,
403 x_lpn_lot_vector3 OUT NOCOPY VARCHAR2,
404 x_lpn_lot_vector4 OUT NOCOPY VARCHAR2,
405 x_return_status OUT NOCOPY VARCHAR2);
406
407 END wms_task_load;
[Home] [Help]