[Home] [Help]
PACKAGE BODY: APPS.WMS_EXPRESS_PICK_TASK
Source
1 PACKAGE BODY WMS_EXPRESS_PICK_TASK AS
2 /* $Header: WMSEXPTB.pls 120.1 2005/10/11 11:28:40 methomas noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_EXPRESS_PICK_TASK';
5
6 PROCEDURE MYDEBUG(MSG IN VARCHAR2) IS
7
8 L_MSG VARCHAR2(5100);
9 L_TS VARCHAR2(30);
10
11 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
12 BEGIN
13
14 L_MSG := MSG;
15
16 INV_MOBILE_HELPER_FUNCTIONS.TRACELOG(
17 P_ERR_MSG => L_MSG,
18 P_MODULE => 'WMS_EXPRESS_PICK_TASK',
19 P_LEVEL => 4
20 );
21
22 -- DBMS_OUTPUT.PUT_LINE(L_MSG);
23
24 END;
25
26 /*
27 * Calls label printing API for the passed transaction temp Id
28 * The process should go through fine even if Label printing
29 * fails
30 */
31 PROCEDURE PRINT_LABEL(
32 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
33 X_MSG_COUNT OUT NOCOPY NUMBER,
34 X_MSG_DATA OUT NOCOPY VARCHAR2,
35 P_TEMP_ID IN NUMBER
36 ) IS
37 L_TRANSACTION_TYPE_ID NUMBER;
38 L_TRANSACTION_SOURCE_TYPE_ID NUMBER;
39 L_BUSINESS_FLOW_CODE NUMBER := INV_LABEL.WMS_BF_PICK_LOAD;
40 L_LABEL_STATUS VARCHAR2(100);
41
42 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
43 BEGIN
44
45 IF (l_debug = 1) THEN
46 MYDEBUG('IN PROCEDURE PRINT_LABEL');
47 END IF;
48
49 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
50
51 SELECT TRANSACTION_TYPE_ID,
52 TRANSACTION_SOURCE_TYPE_ID
53 INTO L_TRANSACTION_TYPE_ID,
54 L_TRANSACTION_SOURCE_TYPE_ID
55 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
56 WHERE TRANSACTION_TEMP_ID = P_TEMP_ID;
57
58 IF L_TRANSACTION_TYPE_ID = 52 THEN
59 -- Picking for sales order
60
61 L_BUSINESS_FLOW_CODE := INV_LABEL.WMS_BF_PICK_LOAD;
62
63 ELSIF L_TRANSACTION_TYPE_ID = 35 THEN
64 -- WIP issue
65
66 L_BUSINESS_FLOW_CODE := INV_LABEL.WMS_BF_WIP_PICK_LOAD;
67
68 ELSIF L_TRANSACTION_TYPE_ID = 51 AND L_TRANSACTION_SOURCE_TYPE_ID = 13 THEN
69 --Backflush
70
71 L_BUSINESS_FLOW_CODE := INV_LABEL.WMS_BF_WIP_PICK_LOAD;
72
73 ELSIF L_TRANSACTION_TYPE_ID = 64 AND L_TRANSACTION_SOURCE_TYPE_ID = 4 THEN
74 --Replenishment
75
76 L_BUSINESS_FLOW_CODE := INV_LABEL.WMS_BF_PICK_LOAD;
77
78 END IF;
79
80 INV_LABEL.PRINT_LABEL_WRAP(
81 X_RETURN_STATUS => X_RETURN_STATUS,
82 X_MSG_COUNT => X_MSG_COUNT,
83 X_MSG_DATA => X_MSG_DATA,
84 X_LABEL_STATUS => L_LABEL_STATUS,
85 P_BUSINESS_FLOW_CODE => L_BUSINESS_FLOW_CODE,
86 P_TRANSACTION_ID => P_TEMP_ID
87 );
88
89 IF (l_debug = 1) THEN
90 MYDEBUG('PRINT_LABEL : ' ||X_RETURN_STATUS||' LABEL:'||L_LABEL_STATUS);
91 END IF;
92
93 EXCEPTION
94 WHEN FND_API.G_EXC_ERROR THEN
95
96 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
97 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
98
99 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
100
101 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
102 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
103
104 WHEN OTHERS THEN
105
106 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
107 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
108 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'PRINT_LABEL');
109 END IF;
110 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
111
112 END PRINT_LABEL;
113
114 PROCEDURE HAS_EXPRESS_PICK_TASKS(
115 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
116 X_MSG_COUNT OUT NOCOPY NUMBER,
117 X_MSG_DATA OUT NOCOPY VARCHAR2,
118 P_USER_ID IN NUMBER,
119 P_ORG_ID IN NUMBER
120 ) IS
121
122 L_DUMMY NUMBER := 0;
123
124 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
125 BEGIN
126
127 BEGIN
128
129 SELECT 1
130 INTO L_DUMMY
131 FROM WMS_DISPATCHED_TASKS
132 WHERE PERSON_ID = P_USER_ID
133 AND ORGANIZATION_ID = P_ORG_ID
134 AND TASK_TYPE IN (1, 3, 4)
135 AND STATUS <= 3
136 AND IS_EXPRESS_PICK_TASK(TASK_ID) = 'S'
137 AND ROWNUM < 2;
138
139 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
140
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 RAISE FND_API.G_EXC_ERROR;
144 END;
145
146 EXCEPTION
147
148 WHEN FND_API.G_EXC_ERROR THEN
149
150 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
151 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
152
153 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154
155 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
156 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
157
158 WHEN OTHERS THEN
159
160 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
161 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
162 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'HAS_EXPRESS_PICK_TASKS');
163 END IF;
164 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
165
166 END HAS_EXPRESS_PICK_TASKS;
167
168 FUNCTION IS_EXPRESS_PICK_TASK(
169 P_TASK_ID IN NUMBER
170 ) RETURN VARCHAR2 IS
171
172 X_RETURN_STATUS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
173
174 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
175 BEGIN
176
177 SELECT IS_EXPRESS_PICK_TASK_ELIGIBLE(WDT.TRANSACTION_TEMP_ID)
178 INTO X_RETURN_STATUS
179 FROM WMS_DISPATCHED_TASKS WDT
180 WHERE WDT.TASK_ID = P_TASK_ID;
181
182 RETURN X_RETURN_STATUS;
183
184 EXCEPTION
185
186 WHEN FND_API.G_EXC_ERROR THEN
187
188 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
189 RETURN X_RETURN_STATUS;
190
191 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
192
193 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
194 RETURN X_RETURN_STATUS;
195
196 WHEN OTHERS THEN
197
198 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
199 RETURN X_RETURN_STATUS;
200
201 END IS_EXPRESS_PICK_TASK;
202
203 FUNCTION IS_EXPRESS_PICK_TASK_ELIGIBLE(
204 P_TRANSACTION_TEMP_ID IN NUMBER
205 ) RETURN VARCHAR2 IS
206
207 X_RETURN_STATUS VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
208 L_SERIAL_CODE NUMBER := 1;
209 L_LOT_CODE NUMBER := 1;
210 L_DUMMY NUMBER := 1;
211 L_IS_BULK_PICK NUMBER := 0;
212 l_allocated_lpn_id NUMBER;
213
214 -- Start of Bugfix 2244633
215 l_api_version_number CONSTANT NUMBER := 1.0;
216 l_init_msg_lst VARCHAR2(10) := fnd_api.g_false;
217 l_return_status VARCHAR2(10);
218 l_msg_count NUMBER;
219 l_msg_data VARCHAR2(5000);
220 l_organization_id NUMBER;
221 l_inventory_item_id NUMBER;
222 l_is_revision_control VARCHAR2(5) := 'false';
223 l_is_lot_control VARCHAR2(5) := 'false';
224 l_is_serial_control VARCHAR2(5) := 'false';
225 l_revision VARCHAR2(30);
226 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
227 l_lot_number VARCHAR2(80);
228 l_transaction_quantity NUMBER;
229 l_transaction_uom VARCHAR2(10);
230 l_subinventory_code VARCHAR2(10);
231 l_locator_id NUMBER;
232 l_revision_control_code NUMBER;
233 l_lot_control_code NUMBER;
234 s_ok_to_process VARCHAR2(10);
235 l_transfer_subinventory VARCHAR2(10);
236
237 -- end of part of bugfix 2244633
238
239 -- bug 2675498
240 l_sub_lpn_controlled_flag NUMBER;
241
242 -- bug 2675498
243
244
245 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
246 BEGIN
247
248 IF (l_debug = 1) THEN
249 MYDEBUG('IS_EXPRESS_PICK_TASK_ELIGIBLE: TRANSACTION_TEMP_ID: '||TO_CHAR(P_TRANSACTION_TEMP_ID));
250 END IF;
251
252 BEGIN
253 -- Start of bugfix 2287341
254 -- Selecting the allocated_lpn_id also
255
256 SELECT NVL(MSI.SERIAL_NUMBER_CONTROL_CODE,1),
257 NVL(MSI.LOT_CONTROL_CODE,1),
258 mmtt.allocated_lpn_id,
259 Nvl(sub.lpn_controlled_flag, 1), -- bug 2675498
260 mmtt.inventory_item_id,
261 mmtt.subinventory_code,
262 mmtt.locator_id,
263 mmtt.organization_id
264 INTO L_SERIAL_CODE,
265 L_LOT_CODE,
266 l_allocated_lpn_id,
267 l_sub_lpn_controlled_flag, -- bug 2675498
268 l_inventory_item_id,
269 l_subinventory_code,
270 l_locator_id,
271 l_organization_id
272 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT,
273 MTL_SYSTEM_ITEMS_B msi,
274 mtl_secondary_inventories sub -- bug 2675498
275 WHERE MMTT.TRANSACTION_TEMP_ID = P_TRANSACTION_TEMP_ID
276 AND MMTT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
277 AND sub.ORGANIZATION_ID = MSI.ORGANIZATION_ID -- Bug #2722444
278 AND MMTT.INVENTORY_ITEM_ID = MSI.inventory_item_id
279 AND mmtt.subinventory_code = sub.secondary_inventory_name -- bug 2675498
280 ;
281
282 -- bug 2675498
283
284 IF l_sub_lpn_controlled_flag = 1 THEN -- from sub is LPN controlled
285
286 IF (l_debug = 1) THEN
287 MYDEBUG('Pick from an LPN controlled sub. This task cannnot be express picked');
288 END IF;
289
290 RAISE fnd_api.g_exc_error;
291 END IF;
292
293 -- bug 2675498
294
295 -- Checking whether the lpn has been already allocated or not?
296
297 IF l_allocated_lpn_id IS NOT NULL THEN
298 IF (l_debug = 1) THEN
299 MYDEBUG('LPN Allocation is already done, Cannot Express Pick This Task');
300 END IF;
301 RAISE FND_API.G_EXC_ERROR;
302 END IF;
303
304 -- End of bugfix 2287341
305
306 IF L_SERIAL_CODE NOT IN ( 1, 6 ) THEN
307 IF (l_debug = 1) THEN
308 MYDEBUG('SERIAL CONTROL CODE: '||TO_CHAR(L_SERIAL_CODE));
309 END IF;
310 RAISE FND_API.G_EXC_ERROR;
311 END IF;
312
313 EXCEPTION
314 WHEN NO_DATA_FOUND THEN
315 IF (l_debug = 1) THEN
316 MYDEBUG('INVALID MMTT: ');
317 END IF;
318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
319 END;
320
321 IF L_LOT_CODE <> 1 THEN
322
323 BEGIN
324
325 SELECT 1
326 INTO L_DUMMY
327 FROM MTL_TRANSACTION_LOTS_TEMP MTLT
328 WHERE MTLT.TRANSACTION_TEMP_ID = P_TRANSACTION_TEMP_ID;
329
330 EXCEPTION
331 WHEN NO_DATA_FOUND THEN
332 IF (l_debug = 1) THEN
333 MYDEBUG('INVALID LOT MMTT: ');
334 END IF;
335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
336 WHEN TOO_MANY_ROWS THEN
337 IF (l_debug = 1) THEN
338 MYDEBUG('MULTIPLE LOTS FOR MMTT: ');
339 END IF;
340 RAISE FND_API.G_EXC_ERROR;
341 END;
342
343 END IF;
344
345 BEGIN
346
347 SELECT 1
348 INTO L_IS_BULK_PICK
349 FROM MTL_MATERIAL_TRANSACTIONS_TEMP
350 WHERE PARENT_LINE_ID = P_TRANSACTION_TEMP_ID
351 AND ROWNUM < 2;
352
353 IF (l_debug = 1) THEN
354 MYDEBUG('THIS IS A BULK PICK TASK');
355 END IF;
356 RAISE FND_API.G_EXC_ERROR;
357
358 EXCEPTION
359 WHEN NO_DATA_FOUND THEN
360 IF (l_debug = 1) THEN
361 MYDEBUG('THIS IS NOT A BULK PICK TASK');
362 END IF;
363 NULL;
364 END;
365
366
367 /* comment out for bug 2675498
368
369
370 -- Bugfix 2244633 contd..
371 BEGIN
372 IF (l_debug = 1) THEN
373 MYDEBUG('Is Express Pick Task? Checking for enough loose quantity');
374 END IF;
375 --Bug 2676657
376 SELECT
377 MMTT.ORGANIZATION_ID,
378 MMTT.INVENTORY_ITEM_ID,
379 MSI.REVISION_QTY_CONTROL_CODE,
380 MSI.LOT_CONTROL_CODE,
381 MMTT.REVISION,
382 MTLT.LOT_NUMBER,
383 MMTT.TRANSACTION_QUANTITY,
384 MMTT.TRANSACTION_UOM,
385 MMTT.SUBINVENTORY_CODE,
386 MMTT.LOCATOR_ID,
387 MMTT.TRANSFER_SUBINVENTORY
388 INTO L_ORGANIZATION_ID,
389 L_INVENTORY_ITEM_ID,
390 L_REVISION_CONTROL_CODE,
391 L_LOT_CONTROL_CODE,
392 L_REVISION,
393 L_LOT_NUMBER,
394 L_TRANSACTION_QUANTITY,
395 L_TRANSACTION_UOM,
396 L_SUBINVENTORY_CODE,
397 L_LOCATOR_ID,
398 L_TRANSFER_SUBINVENTORY
399
400
401 FROM MTL_MATERIAL_TRANSACTIONS_TEMP MMTT
402 ,MTL_SYSTEM_ITEMS_B MSI
403 ,MTL_TRANSACTION_LOTS_TEMP MTLT
404 WHERE MMTT.ORGANIZATION_ID = MSI.ORGANIZATION_ID
405 AND MMTT.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
406 AND MMTT.TRANSACTION_TEMP_ID = P_TRANSACTION_TEMP_ID
407 AND MMTT.TRANSACTION_TEMP_ID = MTLT.transaction_temp_id(+);
408
409 IF (l_revision_control_code = 2) THEN
410 IF (l_debug = 1) THEN
411 MYDEBUG('Revision Controlled item: ' || TO_CHAR(l_revision_control_code));
412 END IF;
413 l_is_revision_control := 'true';
414 END IF;
415
416 IF (l_lot_control_code = 2) THEN
417 IF (l_debug = 1) THEN
418 MYDEBUG('Lot Controlled item: ' || TO_CHAR(l_lot_control_code));
419 END IF;
420 l_is_lot_control := 'true';
421 END IF;
422
423 IF (l_debug = 1) THEN
424 MYDEBUG(' Before Calling Check_Loose_Quantity');
425 END IF;
426
427 INV_TXN_VALIDATIONS.CHECK_LOOSE_QUANTITY
428 ( p_api_version_number => l_api_version_number,
429 p_init_msg_lst => fnd_api.g_true,
430 x_return_status => l_return_status,
431 x_msg_count => l_msg_count,
432 x_msg_data => l_msg_data,
433 p_organization_id => l_organization_id,
434 p_inventory_item_id => l_inventory_item_id,
435 p_is_revision_control => l_is_revision_control,
436 p_is_lot_control => l_is_lot_control,
437 p_is_serial_control => l_is_serial_control,
438 p_revision => l_revision,
439 p_lot_number => l_lot_number,
440 p_transaction_quantity => l_transaction_quantity,
441 p_transaction_uom => l_transaction_uom,
442 p_subinventory_code => l_subinventory_code,
443 p_locator_id => l_locator_id,
444 p_transaction_temp_id => p_transaction_temp_id,
445 p_ok_to_process => s_ok_to_process,
446 p_transfer_subinventory => l_transfer_subinventory
447 );
448
449 IF (l_debug = 1) THEN
453 IF l_return_status = fnd_api.g_ret_sts_error THEN
450 MYDEBUG(' After Calling Check_Loose_Quantity');
451 END IF;
452
454 IF (l_debug = 1) THEN
455 MYDEBUG('return status = ' || l_return_status);
456 END IF;
457 RAISE fnd_api.g_exc_error;
458 END IF ;
459
460 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
461 IF (l_debug = 1) THEN
462 MYDEBUG('return status = ' || l_return_status);
463 END IF;
464 RAISE fnd_api.g_exc_unexpected_error;
465 END IF;
466
467 IF (l_debug = 1) THEN
468 MYDEBUG('s_ok_to_process = ' || s_ok_to_process);
469 END IF;
470
471 IF s_ok_to_process <> 'true' THEN
472 IF (l_debug = 1) THEN
473 MYDEBUG(' Not Enough Loose Quantity. This task cannnot be express picked');
474 END IF;
475 RAISE fnd_api.g_exc_error;
476 END IF;
477
478 X_RETURN_STATUS := l_return_status;
479
480 END;
481 -- end of bugfix 2244633
482
483 comment out for bug 2675498 */
484
485 RETURN X_RETURN_STATUS;
486
487
488 EXCEPTION
489
490 WHEN FND_API.G_EXC_ERROR THEN
491
492 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
493 RETURN X_RETURN_STATUS;
494
495 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
496
497 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
498 RETURN X_RETURN_STATUS;
499
500 WHEN OTHERS THEN
501
502 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
503 RETURN X_RETURN_STATUS;
504
505 END IS_EXPRESS_PICK_TASK_ELIGIBLE;
506
507 PROCEDURE LOAD_AND_DROP(
508 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
509 X_MSG_COUNT OUT NOCOPY NUMBER,
510 X_MSG_DATA OUT NOCOPY VARCHAR2,
511 P_ORG_ID IN NUMBER,
512 P_TEMP_ID IN NUMBER,
513 P_TO_LPN IN VARCHAR2,
514 P_TO_SUB IN VARCHAR2,
515 P_TO_LOC IN NUMBER,
516 P_ACTION IN VARCHAR2,
517 P_USER_ID IN NUMBER,
518 P_TASK_TYPE IN NUMBER
519 ) IS
520
521 L_LPN_ID NUMBER := 0;
522 L_TEMP_ID NUMBER := 0;
523 L_MMTT_TO_UPDATE VARCHAR2(100) := '';
524 L_OK_TO_PROCESS VARCHAR2(100) := 'false';
525 L_TXN_HDR_ID NUMBER := 0;
526
527 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
528 BEGIN
529
530 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
531
532 BEGIN
533
534 SELECT LPN_ID
535 INTO L_LPN_ID
536 FROM WMS_LICENSE_PLATE_NUMBERS
537 WHERE LICENSE_PLATE_NUMBER = P_TO_LPN
538 AND ORGANIZATION_ID = P_ORG_ID;
539
540 IF (l_debug = 1) THEN
541 MYDEBUG('THE LPN ID IS '||TO_CHAR(L_LPN_ID));
542 END IF;
543
544 EXCEPTION
545 WHEN NO_DATA_FOUND THEN
546 IF (l_debug = 1) THEN
547 MYDEBUG('INVALID LPN'||P_TO_LPN);
548 END IF;
549 RAISE FND_API.G_EXC_ERROR;
550 WHEN OTHERS THEN
551 IF (l_debug = 1) THEN
552 MYDEBUG('UNEXPECTED ERROR IN FETCHING LPN '||SQLERRM);
553 END IF;
554 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
555 END;
556
557 IF (l_debug = 1) THEN
558 mydebug('current action is: ' || p_action);
559 END IF;
560
561 -- Check the value of p_action. If 'LOAD' then call procedure LOAD
562 -- If 'LOAD_AND_DROP' then call procedure call 'LOAD_TASK' and 'DROP_TASK'
563 IF (p_action = 'LOAD') THEN
564
565 WMS_EXPRESS_PICK_TASK.LOAD_TASK(
566 X_RETURN_STATUS => X_RETURN_STATUS,
567 X_MSG_COUNT => X_MSG_COUNT,
568 X_MSG_DATA => X_MSG_DATA,
569 P_TEMP_ID => P_TEMP_ID,
570 P_LPN_ID => L_LPN_ID,
571 P_USER_ID => P_USER_ID
572 );
573
574 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
575 IF (l_debug = 1) THEN
576 MYDEBUG('ERROR IN LOAD_TASK ');
577 END IF;
578 RAISE FND_API.G_EXC_ERROR;
579 END IF;
580
581 IF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
582 IF (l_debug = 1) THEN
583 MYDEBUG('UNEXPECTED ERROR IN LOAD_TASK ');
584 END IF;
585 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
586 END IF;
587
588 ELSIF (p_action = 'LOAD_AND_DROP') THEN
589
590 WMS_EXPRESS_PICK_TASK.LOAD_TASK(
591 X_RETURN_STATUS => X_RETURN_STATUS,
592 X_MSG_COUNT => X_MSG_COUNT,
593 X_MSG_DATA => X_MSG_DATA,
597 );
594 P_TEMP_ID => P_TEMP_ID,
595 P_LPN_ID => L_LPN_ID,
596 P_USER_ID => P_USER_ID
598
599 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
600 IF (l_debug = 1) THEN
601 MYDEBUG('ERROR IN LOAD_TASK ');
602 END IF;
603 RAISE FND_API.G_EXC_ERROR;
604 END IF;
605
606 IF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
607 IF (l_debug = 1) THEN
608 MYDEBUG('UNEXPECTED ERROR IN LOAD_TASK ');
609 END IF;
610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
611 END IF;
612
613 WMS_EXPRESS_PICK_TASK.DROP_TASK(
614 X_RETURN_STATUS => X_RETURN_STATUS,
615 X_MSG_COUNT => X_MSG_COUNT,
616 X_MSG_DATA => X_MSG_DATA,
617 P_ORG_ID => P_ORG_ID,
618 P_TEMP_ID => P_TEMP_ID,
619 P_LPN_ID => L_LPN_ID,
620 P_TO_SUB => P_TO_SUB,
621 P_TO_LOC => P_TO_LOC,
622 P_USER_ID => P_USER_ID,
623 P_TASK_TYPE => P_TASK_TYPE
624 );
625
626 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
627 IF (l_debug = 1) THEN
628 MYDEBUG('ERROR IN DROP_TASK ');
629 END IF;
630 RAISE FND_API.G_EXC_ERROR;
631 END IF;
632
633 IF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
634 IF (l_debug = 1) THEN
635 MYDEBUG('UNEXPECTED ERROR IN DROP_TASK ');
636 END IF;
637 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
638 END IF;
639
640 END IF;
641
642 EXCEPTION
643
644 WHEN FND_API.G_EXC_ERROR THEN
645
646 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
647 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
648
649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
650
651 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
652 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
653
654 WHEN OTHERS THEN
655
656 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
657 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
658 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'LOAD_AND_DROP');
659 END IF;
660 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
661
662 END LOAD_AND_DROP;
663
664 PROCEDURE LOAD_TASK(
665 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
666 X_MSG_COUNT OUT NOCOPY NUMBER,
667 X_MSG_DATA OUT NOCOPY VARCHAR2,
668 P_TEMP_ID IN NUMBER,
669 P_LPN_ID IN NUMBER,
670 P_USER_ID IN NUMBER
671 ) IS
672 l_lpn_id NUMBER := p_lpn_id;
673 l_temp_id NUMBER := p_temp_id;
674 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
675 BEGIN
676 IF (l_debug = 1) THEN
677 mydebug('in procedure load_task');
678 END IF;
679 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
680
681 UPDATE mtl_material_transactions_temp
682 SET transfer_lpn_id = l_lpn_id,
683 last_update_date = SYSDATE,
684 last_updated_by = p_user_id
685 WHERE transaction_temp_id = l_temp_id;
686
687 IF (l_debug = 1) THEN
688 mydebug('Updated MTTT row count: ' || SQL%ROWCOUNT);
689 END IF;
690
691 --Set the task status to Loaded
692 UPDATE wms_dispatched_tasks
693 SET status = 4,
694 loaded_time = SYSDATE,
695 last_update_date = SYSDATE,
696 last_updated_by = p_user_id
697 WHERE transaction_temp_id = l_temp_id;
698
699 IF (l_debug = 1) THEN
700 mydebug('Updated WDT row count: ' || SQL%ROWCOUNT);
701 END IF;
702
703 PRINT_LABEL(
704 X_RETURN_STATUS => X_RETURN_STATUS,
705 X_MSG_COUNT => X_MSG_COUNT,
706 X_MSG_DATA => X_MSG_DATA,
707 P_TEMP_ID => P_TEMP_ID
708 );
709
710 IF (l_debug = 1) THEN
711 MYDEBUG('IGNORE RETURN STATUS FOR LABEL PRINTING');
712 END IF;
713
714 EXCEPTION
715 WHEN FND_API.G_EXC_ERROR THEN
716
717 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
718 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
719
720 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
721
722 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
723 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
724
725 WHEN OTHERS THEN
726
727 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
728 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
729 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'LOAD_TASK');
730 END IF;
734
731 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
732
733 END LOAD_TASK;
735 PROCEDURE DROP_TASK(
736 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
737 X_MSG_COUNT OUT NOCOPY NUMBER,
738 X_MSG_DATA OUT NOCOPY VARCHAR2,
739 P_ORG_ID IN NUMBER,
740 P_TEMP_ID IN NUMBER,
741 P_LPN_ID IN NUMBER,
742 P_TO_SUB IN VARCHAR2,
743 P_TO_LOC IN NUMBER,
744 P_USER_ID IN NUMBER,
745 P_TASK_TYPE IN NUMBER
746 ) IS
747 l_lpn_id NUMBER := p_lpn_id;
748 l_temp_id NUMBER := p_temp_id;
749 l_txn_hdr_id NUMBER := 0;
750 s_ok_to_process VARCHAR2(10);
751 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
752 BEGIN
753
754 IF (l_debug = 1) THEN
755 mydebug('in prod drop_task');
756 END IF;
757
758 X_RETURN_STATUS := FND_API.G_RET_STS_SUCCESS;
759
760 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
761 INTO L_TXN_HDR_ID
762 FROM DUAL;
763
764 IF (l_debug = 1) THEN
765 MYDEBUG('L_TXN_HDR_ID IS '|| L_TXN_HDR_ID);
766 END IF;
767
768 WMS_TASK_DISPATCH_GEN.COMPLETE_PICK(
769 P_LPN => NULL,
770 P_CONTAINER_ITEM_ID => NULL,
771 P_ORG_ID => P_ORG_ID,
772 P_TEMP_ID => P_TEMP_ID,
773 P_LOC => P_TO_LOC,
774 P_SUB => P_TO_SUB,
775 P_FROM_LPN_ID => L_LPN_ID ,
776 P_TXN_HDR_ID => L_TXN_HDR_ID,
777 P_USER_ID => P_USER_ID,
778 X_RETURN_STATUS => X_RETURN_STATUS,
779 X_MSG_COUNT => X_MSG_COUNT,
780 X_MSG_DATA => X_MSG_DATA,
781 P_OK_TO_PROCESS => S_OK_TO_PROCESS
782 );
783
784 IF (l_debug = 1) THEN
785 MYDEBUG('Finished Complete Pick. p_ok_to_process =' || s_ok_to_process);
786 END IF;
787
788 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
789 IF (l_debug = 1) THEN
790 MYDEBUG('ERROR IN COMPLETE PICK ');
791 END IF;
792 RAISE FND_API.G_EXC_ERROR;
793 END IF;
794
795 IF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
796 IF (l_debug = 1) THEN
797 MYDEBUG('UNEXPECTED ERROR IN COMPLETE PICK ');
798 END IF;
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800 END IF;
801
802
803 WMS_TASK_DISPATCH_GEN.PICK_DROP(
804 P_TEMP_ID => P_TEMP_ID,
805 P_TXN_HEADER_ID => L_TXN_HDR_ID,
806 P_ORG_ID => P_ORG_ID,
807 X_RETURN_STATUS => X_RETURN_STATUS,
808 X_MSG_COUNT => X_MSG_COUNT,
809 X_MSG_DATA => X_MSG_DATA,
810 P_FROM_LPN_ID => L_LPN_ID ,
811 P_DROP_LPN => NULL,
812 P_LOC_REASON_ID => 0,
813 P_SUB => P_TO_SUB,
814 P_LOC => P_TO_LOC,
815 P_ORIG_SUB => P_TO_SUB,
816 P_ORIG_LOC => P_TO_LOC,
817 P_USER_ID => P_USER_ID,
818 P_TASK_TYPE => P_TASK_TYPE
819 );
820
821 IF X_RETURN_STATUS = FND_API.G_RET_STS_ERROR THEN
822 IF (l_debug = 1) THEN
823 MYDEBUG('ERROR IN PICK DROP ');
824 END IF;
825 RAISE FND_API.G_EXC_ERROR;
826 END IF;
827
828 IF X_RETURN_STATUS = FND_API.G_RET_STS_UNEXP_ERROR THEN
829 IF (l_debug = 1) THEN
830 MYDEBUG('UNEXPECTED ERROR IN PICK DROP ');
831 END IF;
832 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
833 END IF;
834
835 EXCEPTION
836
837 WHEN FND_API.G_EXC_ERROR THEN
838
839 X_RETURN_STATUS := FND_API.G_RET_STS_ERROR;
840 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
841
842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
843
844 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
845 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
846
847 WHEN OTHERS THEN
848
849 X_RETURN_STATUS := FND_API.G_RET_STS_UNEXP_ERROR;
850 IF FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
851 FND_MSG_PUB.ADD_EXC_MSG( g_pkg_name, 'DROP_TASK');
852 END IF;
853 FND_MSG_PUB.COUNT_AND_GET( P_COUNT => X_MSG_COUNT, P_DATA => X_MSG_DATA );
854
855 END DROP_TASK;
856
857 END WMS_EXPRESS_PICK_TASK;