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