1 PACKAGE WMS_Task_Dispatch_put_away AS
2 /* $Header: WMSTKPTS.pls 120.2.12010000.1 2008/07/28 18:37:22 appldev ship $ */
3
4
5 TYPE CRDK_WIP_REC IS RECORD
6 (
7 move_order_line_id NUMBER,
8 wip_entity_id NUMBER,
9 operation_seq_num NUMBER := NULL,
10 repetitive_schedule_id NUMBER :=NULL,
11 wip_issue_flag VARCHAR2(1):= NULL
12 );
13
14 TYPE CRDK_WIP_TB IS TABLE OF crdk_wip_rec INDEX BY BINARY_INTEGER;
15
16 crdk_wip_info_table crdk_wip_tb;
17 crdk_wip_table_index NUMBER := 0;
18
19
20 /* Used to create move order line
21 * p_wms_process_flag - Flag to indicate processing status for putaways.
22 * 1 means Ok to process,2 means Do not Allocate, 3 means Allocate but
23 * do not process. To be used by Receiving and WIP
24 */
25
26 PROCEDURE Create_MO_Line
27 (p_org_id IN NUMBER,
28 p_inventory_item_id IN NUMBER,
29 p_qty IN NUMBER,
30 p_uom IN VARCHAR2,
31 p_lpn IN NUMBER,
32 p_project_id IN NUMBER,
33 p_task_id IN NUMBER,
34 p_reference IN VARCHAR2,
35 p_reference_type_code IN NUMBER,
36 p_reference_id IN NUMBER,
37 p_header_id IN NUMBER,
38 p_lot_number IN VARCHAR2,
39 p_revision IN VARCHAR2,
40 p_inspection_status IN NUMBER:=NULL,
41 p_txn_source_id IN NUMBER:= FND_API.G_MISS_NUM,
42 p_transaction_type_id IN NUMBER:= FND_API.G_MISS_NUM,
43 p_transaction_source_type_id IN NUMBER:= FND_API.g_miss_num,
44 p_wms_process_flag IN NUMBER:=NULL,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2,
48 p_from_cost_group_id IN NUMBER := NULL,
49 p_sec_qty IN NUMBER := NULL, -- Added for OPM convergance
50 p_sec_uom IN VARCHAR2 := NULL, -- Added for OPM convergance
51 x_line_id OUT nocopy NUMBER -- Added for R12 MOL Consolidation
52 );
53
54 PROCEDURE create_mo
55 (p_org_id IN NUMBER,
56 p_inventory_item_id IN NUMBER,
57 p_qty IN NUMBER,
58 p_uom IN VARCHAR2,
59 p_lpn IN NUMBER,
60 p_project_id IN NUMBER:=NULL,
61 p_task_id IN NUMBER:=NULL,
62 p_reference IN VARCHAR2:=NULL,
63 p_reference_type_code IN NUMBER:=NULL,
64 p_reference_id IN NUMBER:=NULL,
65 p_lot_number IN VARCHAR2,
66 p_revision IN VARCHAR2,
67 p_header_id IN OUT NOCOPY NUMBER,
68 p_sub IN VARCHAR:=NULL,
69 p_loc IN NUMBER:=NULL,
70 x_line_id OUT NOCOPY NUMBER,
71 p_inspection_status IN NUMBER:=NULL,
72 p_txn_source_id IN NUMBER:= FND_API.G_MISS_NUM,
73 p_transaction_type_id IN NUMBER:= FND_API.G_MISS_NUM,
74 p_transaction_source_type_id IN NUMBER:= FND_API.g_miss_num,
75 p_wms_process_flag IN NUMBER:=NULL,
76 x_return_status OUT NOCOPY VARCHAR2,
77 x_msg_count OUT NOCOPY NUMBER,
78 x_msg_data OUT NOCOPY VARCHAR2,
79 p_from_cost_group_id IN NUMBER:=NULL,
80 p_transfer_org_id IN NUMBER := NULL,
81 p_sec_qty IN NUMBER := NULL, -- Added for OPM convergance
82 p_sec_uom IN VARCHAR2 := NULL -- Added for OPM convergance
83 );
84
85
86 -- Bug# 2752119
87 -- Added an extra input parameter called p_check_for_crossdock
88 -- which will default to 'Y' = Yes.
89 -- This is needed when we are performing an Express Drop and need
90 -- to validate against the rules. In that case, it is possible that
91 -- a crossdocking opportunity exists but the user chose to ignore it
92 -- and proceed with the express drop. We should not call the
93 -- crossdocking API's at all in that case since it might split the
94 -- move order lines.
95
96 -- ATF_J:
97 -- Added new parameter p_move_order_Line_ID
98 -- support item putaway load.
99 -- Detailing should happen for LPN/item combination, not
100 -- entire LPN, therefore suggestions_PUB needs to be called
101 -- for move order line ID.
102
103 -- Nested LPN support
104 -- Added new parameter p_commit since this procedure would be called from a
105 -- wrapper and we may not commit always
106
107 PROCEDURE Suggestions_PUB
108 ( p_lpn_id IN NUMBER ,
109 p_org_id IN NUMBER ,
110 p_user_id IN NUMBER ,
111 p_eqp_ins IN VARCHAR2 ,
112 x_number_of_rows OUT NOCOPY NUMBER ,
113 x_return_status OUT NOCOPY VARCHAR2 ,
114 x_msg_count OUT NOCOPY NUMBER ,
115 x_msg_data OUT NOCOPY VARCHAR2 ,
116 x_crossdock OUT NOCOPY VARCHAR2 ,
117 p_status IN NUMBER := 3 ,
118 p_check_for_crossdock IN VARCHAR2 := 'Y' ,
119 p_move_order_line_id IN NUMBER DEFAULT NULL ,
120 p_commit IN VARCHAR2 DEFAULT 'Y' ,
121 p_drop_type IN VARCHAR2 DEFAULT NULL , -- Added for Nested LPN changes
122 p_subinventory IN VARCHAR2 DEFAULT NULL , -- Added for Nested LPN changes
123 p_locator_id IN NUMBER DEFAULT NULL ); -- Added for Nested LPN changes
124
125
126 -- Bug# 2795096
127 -- Added an extra input parameter called p_commit
128 -- which will default to 'Y' = Yes.
129 -- This is needed when we are performing a consolidated drop
130 -- where complete_putaway is called for each and every MMTT line
131 -- within the same commit cycle. Previously it would perform a
132 -- commit at the end of the call to complete_putaway. This doesn't
133 -- work for consolidated drops since if one of the MMTT lines fails
134 -- in the call to complete_putaway, we'd like to rollback all of the
135 -- changes done. Thus we should not call a commit until complete_putaway
136 -- has been successfully called for every MMTT line.
137
138 -- FP-J Lot/Serial Support Enhancement
139 -- Added a new parameter p_product_transaction_id which stores
140 -- the product_transaction_id column value in MTLI/MSNI for lots and serials
141 -- that were created from the putaway drop UI. This value would be populated
142 -- only if there were a quantity discrepancy in the UI
143 PROCEDURE Complete_Putaway
144 ( p_lpn_id IN NUMBER ,
145 p_org_id IN NUMBER ,
146 p_temp_id IN NUMBER ,
147 p_item_id IN NUMBER ,
148 p_rev IN VARCHAR2 ,
149 p_lot IN VARCHAR2 ,
150 p_loc IN NUMBER ,
151 p_sub IN VARCHAR2 ,
152 p_qty IN NUMBER ,
153 p_uom IN VARCHAR2 ,
154 p_user_id IN NUMBER ,
155 p_disc IN VARCHAR2 ,
156 x_return_status OUT NOCOPY VARCHAR2 ,
157 x_msg_count OUT NOCOPY NUMBER ,
158 x_msg_data OUT NOCOPY VARCHAR2 ,
159 p_entire_lpn IN VARCHAR2 := 'N' ,
160 p_to_lpn IN VARCHAR2 := FND_API.g_miss_char ,
161 p_qty_reason_id IN NUMBER ,
162 p_loc_reason_id IN NUMBER ,
163 p_process_serial_flag IN VARCHAR2 ,
164 p_commit IN VARCHAR2 := 'Y' ,
165 p_product_transaction_id IN NUMBER DEFAULT NULL ,
166 p_lpn_mode IN NUMBER DEFAULT NULL ,
167 p_new_txn_header_id IN NUMBER DEFAULT NULL ,
168 p_secondary_quantity IN NUMBER DEFAULT NULL , --OPM Convergence
169 p_secondary_uom IN VARCHAR2 DEFAULT NULL , --OPM Convergence
170 p_primary_uom IN VARCHAR2
171 );
172
173 -- No OPM changes needed here since this is not used Post J
174 PROCEDURE Discrepancy
175 (p_lpn_id IN NUMBER
176 , p_org_id IN NUMBER
177 , p_temp_id IN NUMBER
178 , p_qty IN NUMBER
179 , p_uom IN VARCHAR2
180 , p_user_id IN NUMBER
181 , x_return_status OUT NOCOPY VARCHAR2
182 );
183
184 /* Will Check to see if lpn is eligible for putaway
185 x_ret =0 is success,
186 x_ret=1 indicates lpn needs inspection,
187 x_ret=2 means that it is incomplete
188 x_ret=3 means that no mols exist and it is not a inventory lpn
189 */
190 PROCEDURE check_lpn_validity
191 ( p_org_id IN NUMBER
192 , p_lpn_id IN NUMBER
193 , x_ret OUT NOCOPY NUMBER
194 , x_return_status OUT NOCOPY VARCHAR2
195 , x_msg_count OUT NOCOPY NUMBER
196 , x_msg_data OUT NOCOPY VARCHAR2
197 , x_context OUT NOCOPY NUMBER
198 , p_user_id IN NUMBER
199 );
200
201
202 PROCEDURE archive_task
203 ( p_temp_id IN NUMBER
204 , p_org_id IN NUMBER
205 , x_return_status OUT NOCOPY VARCHAR2
206 , x_msg_count OUT NOCOPY NUMBER
207 , x_msg_data OUT NOCOPY VARCHAR2
208 );
209
210
211 PROCEDURE archive_task
212 ( p_temp_id IN NUMBER
213 , p_org_id IN NUMBER
214 , x_return_status OUT NOCOPY VARCHAR2
215 , x_msg_count OUT NOCOPY NUMBER
216 , x_msg_data OUT NOCOPY VARCHAR2
217 , p_delete_mmtt_flag IN VARCHAR2
218 , p_txn_header_id IN NUMBER
219 , p_transfer_lpn_id IN NUMBER DEFAULT NULL
220 );
221
222
223 PROCEDURE putaway_cleanup
224 ( p_temp_id IN NUMBER
225 , p_org_id IN NUMBER
226 , x_return_status OUT NOCOPY VARCHAR2
227 , x_msg_count OUT NOCOPY NUMBER
228 , x_msg_data OUT NOCOPY VARCHAR2
229 );
230
231
232 PROCEDURE validate_putaway_to_lpn
233 (p_org_id IN NUMBER,
234 p_to_lpn IN VARCHAR2,
235 p_from_lpn IN VARCHAR2,
236 p_sub IN VARCHAR2,
237 p_loc_id IN NUMBER,
238 x_return_status OUT NOCOPY VARCHAR2,
239 x_msg_count OUT NOCOPY NUMBER,
240 x_msg_data OUT NOCOPY VARCHAR2,
241 x_return OUT NOCOPY NUMBER,
242 p_crossdock IN VARCHAR2 default NULL );
243
244 /* This API will check the status of the mmtt lines
245 This will be called from the suggestions_api, as part of the putaway
246 process We need to do this at the mmtt line level rather than just
247 checking the lpn contents because the transaction type id might differ
248 in each MOL. Returns x_mtl_status 0 if everything is fine, 1 otherwise*/
249
250 PROCEDURE check_mmtt_mtl_status
251 ( p_temp_id IN VARCHAR2
252 , p_org_id IN NUMBER
253 , x_mtl_status OUT NOCOPY NUMBER
254 , x_return_status OUT NOCOPY VARCHAR2
255 , x_msg_count OUT NOCOPY NUMBER
256 , x_msg_data OUT NOCOPY VARCHAR2
257 );
258
259
260 PROCEDURE cleanup_partial_putaway_LPN
261 (x_return_status OUT NOCOPY VARCHAR2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2,
264 p_lpn_id IN NUMBER);
265
266
267 -- Name: validate_against_rules
268 --
269 -- Input parameters:
270 -- p_organization_id Organization ID
271 -- p_lpn_id LPN ID
272 -- p_subinventory User suggeested sub
273 -- p_locator_id User suggested loc
274 -- p_user_id User ID
275 -- p_eqp_ins Equipment Instance
276 -- p_project_id Project ID
277 -- p_task_id Task ID
278 --
279 -- Output parameters:
280 -- x_return_status
281 -- if the validate_against_rules API succeeds, the value is
282 -- fnd_api.g_ret_sts_success;
283 -- if there is an expected error, the value is
284 -- fnd_api.g_ret_sts_error;
285 -- if there is an unexpected error, the value is
286 -- fnd_api.g_ret_sts_unexp_error;
287 -- x_msg_count
288 -- if there are one or more errors, the number of error messages
289 -- in the buffer
290 -- x_msg_data
291 -- if there is one and only one error, the error message
292 -- (See fnd_api package for more details about the above output parameters)
293 -- x_validation_passed
294 -- if rules validation passed, the value is 'Y' else 'N'
295 --
296 -- Functions: This API validates the user suggested sub/loc during
297 -- a user directed putaway process. It will return the
298 -- status of this validation whether it passed or not.
299 PROCEDURE validate_against_rules
300 (p_organization_id IN NUMBER ,
301 p_lpn_id IN NUMBER ,
302 p_subinventory IN VARCHAR2 ,
303 p_locator_id IN NUMBER ,
304 p_user_id IN NUMBER ,
305 p_eqp_ins IN VARCHAR2 ,
306 p_project_id IN NUMBER ,
310 x_msg_data OUT NOCOPY VARCHAR2 ,
307 p_task_id IN NUMBER ,
308 x_return_status OUT NOCOPY VARCHAR2 ,
309 x_msg_count OUT NOCOPY NUMBER ,
311 x_validation_passed OUT NOCOPY VARCHAR2);
312
313
314 -- Name: create_user_suggestions
315 --
316 -- Input parameters:
317 -- p_organization_id Organization ID
318 -- p_lpn_id LPN ID
319 -- p_subinventory User suggeested sub
320 -- p_locator_id User suggested loc
321 -- p_user_id User ID
322 -- p_eqp_ins Equipment Instance
323 --
324 -- Output parameters:
325 -- x_return_status
326 -- if the validate_against_rules API succeeds, the value is
327 -- fnd_api.g_ret_sts_success;
328 -- if there is an expected error, the value is
329 -- fnd_api.g_ret_sts_error;
330 -- if there is an unexpected error, the value is
331 -- fnd_api.g_ret_sts_unexp_error;
332 -- x_msg_count
333 -- if there are one or more errors, the number of error messages
334 -- in the buffer
335 -- x_msg_data
336 -- if there is one and only one error, the error message
337 -- (See fnd_api package for more details about the above output parameters)
338 -- x_number_of_rows
339 -- This returned the number of user suggestions (MMTT records) created
340 --
341 -- Functions: This API will create manual user suggestions. This is
342 -- called during a user directed putaway process when the
343 -- rules engine isn't called for a suggestion. Therefore,
344 -- we need to manually create MMTT, MTLT, and WDT records
345 -- since the procedure Suggestions_PUB in this package is
346 -- not called in that flow.
347 PROCEDURE create_user_suggestions
348 (p_organization_id IN NUMBER ,
349 p_lpn_id IN NUMBER ,
350 p_subinventory IN VARCHAR2 ,
351 p_locator_id IN NUMBER ,
352 p_user_id IN NUMBER ,
353 p_eqp_ins IN VARCHAR2 ,
354 x_return_status OUT NOCOPY VARCHAR2 ,
355 x_msg_count OUT NOCOPY NUMBER ,
356 x_msg_data OUT NOCOPY VARCHAR2 ,
357 x_number_of_rows OUT NOCOPY NUMBER);
358
359 -- Name: validate_lot_serial_status
360 --
361 -- Input parameters:
362 -- p_organization_id Organization ID
363 -- p_lpn_id LPN ID
364 --
365 -- Output parameters:
366 -- x_return_status
367 -- if the validate_lot_serial_status API succeeds, the value is
368 -- fnd_api.g_ret_sts_success;
369 -- if there is an expected error, the value is
370 -- fnd_api.g_ret_sts_error;
371 -- if there is an unexpected error, the value is
372 -- fnd_api.g_ret_sts_unexp_error;
373 -- x_msg_count
374 -- if there are one or more errors, the number of error messages
375 -- in the buffer
376 -- x_msg_data
377 -- if there is one and only one error, the error message
378 -- (See fnd_api package for more details about the above output parameters)
379 -- x_validation_passed
380 -- if lot serial status validation passed, the value is 'Y' else 'N'
381 -- x_invalid_value
382 -- if validation fails, which lot or serial number has an invalid
383 -- material status
384 --
385 -- Functions: This API validates the lot and serial statuses of the
386 -- items packed within an LPN during a user directed
387 -- putaway process. It will make sure that the lots and
388 -- serials have material statuses which allow the given
389 -- putaway transaction. It will return the status of this
390 -- validation whether it passed or not. If validation
391 -- fails, this will also output the lot or serial number
392 -- that has an invalid lot/serial material status for the
393 -- given transaction.
394 PROCEDURE validate_lot_serial_status
395 (p_organization_id IN NUMBER ,
396 p_lpn_id IN NUMBER ,
397 x_return_status OUT NOCOPY VARCHAR2 ,
398 x_msg_count OUT NOCOPY NUMBER ,
399 x_msg_data OUT NOCOPY VARCHAR2 ,
400 x_validation_passed OUT NOCOPY VARCHAR2 ,
401 x_invalid_value OUT NOCOPY VARCHAR2);
402
403 -- Name: revert_loc_suggested_capacity
404 --
405 -- Input parameters:
406 -- p_organization_id Organization ID
407 -- p_lpn_id LPN ID
408 --
409 -- Output parameters:
410 -- x_return_status
411 -- if the validate_lot_serial_status API succeeds, the value is
412 -- fnd_api.g_ret_sts_success;
413 -- if there is an expected error, the value is
414 -- fnd_api.g_ret_sts_error;
415 -- if there is an unexpected error, the value is
416 -- fnd_api.g_ret_sts_unexp_error;
417 -- x_msg_count
418 -- if there are one or more errors, the number of error messages
419 -- in the buffer
420 -- x_msg_data
421 -- if there is one and only one error, the error message
425 -- weight and units capacity of all locators when a user
422 -- (See fnd_api package for more details about the above output parameters)
423 --
424 -- Functions: -- This API reverts the updates of the suggested volume,
426 -- directed putaway occurs. If the rules engine is
427 -- called, and the user chooses a locator different
428 -- from the one suggested, the suggested locator
429 -- capacities must be reverted back to their prior values.
430 -- This will basically call the procedure,
431 -- INV_LOC_WMS_UTILS.revert_loc_suggested_capacity
432 -- for each MMTT suggestion created for the given LPN
433 PROCEDURE revert_loc_suggested_capacity
434 (x_return_status OUT NOCOPY VARCHAR2 ,
435 x_msg_count OUT NOCOPY NUMBER ,
436 x_msg_data OUT NOCOPY VARCHAR2 ,
437 p_organization_id IN NUMBER ,
438 p_lpn_id IN NUMBER);
439
440 -- Name: check_for_crossdock
441 --
442 -- Input parameters:
443 -- p_organization_id Organization ID
444 -- p_lpn_id LPN ID
445 --
446 -- Output parameters:
447 -- x_return_status
448 -- if the validate_lot_serial_status API succeeds, the value is
449 -- fnd_api.g_ret_sts_success;
450 -- if there is an expected error, the value is
451 -- fnd_api.g_ret_sts_error;
452 -- if there is an unexpected error, the value is
453 -- fnd_api.g_ret_sts_unexp_error;
454 -- x_msg_count
455 -- if there are one or more errors, the number of error messages
456 -- in the buffer
457 -- x_msg_data
458 -- if there is one and only one error, the error message
459 -- (See fnd_api package for more details about the above output parameters)
460 -- x_crossdock
461 -- Returns 'Y' if there is a crossdock opportunity,
462 -- 'N' if no crossdocking or procedure call errored out
463 --
464 -- Functions: -- This API will check for crossdocking
465 -- opportunities in the Express Putaway Page. When
466 -- performing an express user directed drop, we want to
467 -- make sure that if there is a crossdocking
468 -- opportunity, we should let the user be aware of that.
469 -- The user can then decide if they still want to
470 -- putaway the LPN or let the rules direct them to a
471 -- putaway location.
472 --
473 PROCEDURE check_for_crossdock
474 (p_organization_id IN NUMBER ,
475 p_lpn_id IN NUMBER ,
476 x_return_status OUT NOCOPY VARCHAR2 ,
477 x_msg_count OUT NOCOPY NUMBER ,
478 x_msg_data OUT NOCOPY VARCHAR2 ,
479 x_crossdock OUT NOCOPY VARCHAR2
480 );
481
482 FUNCTION insert_msni_helper
483 (p_txn_if_id IN OUT NOCOPY NUMBER
484 , p_serial_number IN VARCHAR2
485 , p_item_id IN NUMBER
486 , p_org_id IN NUMBER
487 , p_product_txn_id IN OUT NOCOPY NUMBER
488 ) RETURN BOOLEAN;
489
490 END WMS_Task_Dispatch_put_away;
[Home] [Help]