1 PACKAGE BODY WMS_Device_Integration_PVT AS
2 /* $Header: WMSDEVPB.pls 120.13 2012/02/10 11:56:21 rdudani ship $ */
3
4
5 -----------------------------------------------------
6 -- Global declarations
7 -----------------------------------------------------
8 SUBTYPE WDR_ROW IS WMS_DEVICE_REQUESTS%ROWTYPE;
9 SUBTYPE WDRH_ROW IS WMS_DEVICE_REQUESTS_HIST%ROWTYPE;
10
11
12 -----------------------------------------------------
13 -- trace
14 -----------------------------------------------------
15 PROCEDURE trace(p_msg IN VARCHAR2, p_level IN NUMBER) IS
16 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
17 BEGIN
18 IF (l_debug = 1) THEN
19 inv_trx_util_pub.trace(p_msg, 'WMS_DEVICE_INTEGRATION_PVT', p_level);
20 END IF;
21 --dbms_output.put_line(p_msg);
22 END trace;
23
24 -----------------------------------------------------
25 -- retrieve_Ship_Confirm_Details
26 --
27 ------------------------------------------------------
28 PROCEDURE retrieve_ship_confirm_Details ( p_task_trx_id IN NUMBER,
29 p_bus_event IN NUMBER,
30 x_request_id OUT NOCOPY NUMBER,
31 x_return_status OUT NOCOPY VARCHAR2) is
32
33 l_request_id NUMBER;
34 l_org_id NUMBER;
35 l_item_id NUMBER;
36 l_subinv VARCHAR2(30);
37 l_locator_id NUMBER;
38 l_lpn_id NUMBER;
39 l_qty NUMBER;
40 l_uom VARCHAR2(3);
41 l_rev VARCHAR2(10);
42
43 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
44 BEGIN
45
46 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
47 x_request_id := l_request_id;
48
49 SELECT wdd1.organization_id,
50 wdd1.subinventory,
51 wdd1.locator_id,
52 wdd1.inventory_item_id,
53 wdd1.revision,
54 wdd1.requested_quantity_uom,
55 wdd2.lpn_id,
56 wdd1.shipped_quantity
57 INTO
58 l_org_id,
59 l_subinv,
60 l_locator_id,
61 l_item_id,
62 l_rev,
63 l_uom,
64 l_lpn_id,
65 l_qty
66 FROM wsh_delivery_details wdd1, wsh_delivery_assignments_v wda,
67 wsh_delivery_details wdd2
68 WHERE wdd1.DELIVERY_DETAIL_ID = p_task_trx_id
69 AND wdd1.delivery_detail_id = wda.parent_delivery_detail_id
70 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id;
71
72 insert INTO wms_device_requests (request_id,
73 task_id,
74 task_summary,
75 business_event_id,
76 organization_id,
77 subinventory_code,
78 locator_id,
79 inventory_item_id,
80 revision,
81 uom,
82 lpn_id,
83 transaction_quantity,
84 last_update_date,
85 last_updated_by) VALUES
86 (l_request_id,
87 p_task_trx_id,
88 'Y',
89 p_bus_event,
90 l_org_id,
91 l_subinv,
92 l_locator_id,
93 l_item_id,
94 l_rev,
95 l_uom,
96 l_lpn_id,
97 l_qty,
98 Sysdate,
99 FND_GLOBAL.USER_ID);
100
101 x_return_status := 'S';
102
103 EXCEPTION
104 WHEN no_data_found THEN
105 x_return_status := 'E';
106 IF (l_debug = 1) THEN
107 trace('Error retrieve in ship confirm details, no data found');
108 END IF;
109 RAISE fnd_api.g_exc_unexpected_error;
110 WHEN others THEN
111 x_return_status := 'E';
112 IF (l_debug = 1) THEN
113 trace('Other error in retrieve ship confirm details');
114 trace('SQL error :'||substr(sqlerrm, 1, 240));
115 END IF;
116
117
118 END;
119
120
121
122
123 -----------------------------------------------------
124 -- retrieve_Bus_Event_Details
125 -- create device request record with MMTT record
126 ------------------------------------------------------
127 PROCEDURE retrieve_Bus_Event_Details ( p_task_trx_id IN NUMBER,
128 p_bus_event IN NUMBER,
129 x_request_id OUT NOCOPY NUMBER,
130 x_return_status OUT NOCOPY VARCHAR2) is
131
132 l_request_id NUMBER;
133 l_org_id NUMBER;
134 l_item_id NUMBER;
135 l_subinv VARCHAR2(30);
136 l_locator_id NUMBER;
137 l_lpn_id NUMBER;
138 l_xfr_org_id NUMBER;
139 l_xfr_subinv VARCHAR2(30);
140 l_xfr_locator_id NUMBER;
141 l_qty NUMBER;
142 l_uom VARCHAR2(3);
143 l_rev VARCHAR2(10);
144
145 l_temp_sub VARCHAR2(30);
146 l_temp_loc NUMBER;
147 l_xfr_lpn_id NUMBER; --Added for Bug#8512121
148
149 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
150 BEGIN
151
152 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
153 x_request_id := l_request_id;
154
155 SELECT organization_id,
156 subinventory_code,
157 locator_id,
158 transfer_organization,
159 transfer_subinventory,
160 transfer_to_location,
161 inventory_item_id,
162 revision,
163 transaction_uom,
164 Nvl(lpn_id,allocated_lpn_id) lpn_id,
165 transaction_quantity,
166 TRANSFER_LPN_ID --Added for Bug#8512121
167 INTO
168 l_org_id,
169 l_subinv,
170 l_locator_id,
171 l_xfr_org_id,
172 l_xfr_subinv,
173 l_xfr_locator_id,
174 l_item_id,
175 l_rev,
176 l_uom,
177 l_lpn_id,
178 l_qty,
179 l_xfr_lpn_id --Added for Bug#8512121
180 FROM mtl_material_transactions_temp
181 WHERE transaction_temp_id = p_task_trx_id;
182
183 IF p_bus_event in (WMS_BE_PUTAWAY_DROP, WMS_BE_PICK_DROP) THEN
184 IF (l_debug = 1) THEN
185 trace(' for putaway drop or pick drop, swap the sub/loc and transfer sub/loc');
186 END IF;
187 l_subinv := l_xfr_subinv;
188 l_xfr_subinv := null;
189
190 l_locator_id := l_xfr_locator_id;
191 l_xfr_locator_id := null;
192
193 --made all these details null for multiple lines in LPN for putaway
194 --or drop
195 l_item_id :=NULL;
196 l_rev := NULL;
197 l_uom := NULL;
198 l_qty := NULL;
199
200 END IF;
201
202 IF (l_debug = 1) THEN
203 trace(' sub,loc,xfr_sub,xfr_loc:'||l_subinv||','||l_locator_id||','||l_xfr_subinv||','||l_xfr_locator_id);
204 trace('l_xfr_lpn_id:'||l_xfr_lpn_id);
205 trace('l_lpn_id:'||l_lpn_id);
206 END IF;
207
208 insert INTO wms_device_requests (request_id,
209 task_id,
210 task_summary,
211 business_event_id,
212 organization_id,
213 subinventory_code,
214 locator_id,
215 transfer_org_id,
216 transfer_sub_code,
217 transfer_loc_id,
218 inventory_item_id,
219 revision,
220 uom,
221 lpn_id,
222 xfer_lpn_id, --Added for Bug#8512121
223 transaction_quantity,
224 last_update_date,
225 last_updated_by) VALUES
226 (l_request_id,
227 p_task_trx_id,
228 'Y',
229 p_bus_event,
230 l_org_id,
231 l_subinv,
232 l_locator_id,
233 l_xfr_org_id,
234 l_xfr_subinv,
235 l_xfr_locator_id,
236 l_item_id,
237 l_rev,
238 l_uom,
239 l_lpn_id,
240 l_xfr_lpn_id, --Added for Bug#8512121
241 l_qty,
242 Sysdate,
243 FND_GLOBAL.USER_ID);
244
245
246 x_return_status := 'S';
247
248 EXCEPTION
249 WHEN no_data_found THEN
250 x_return_status := 'E';
251 IF (l_debug = 1) THEN
252 trace('Error retrieve in business event details, no data found');
253 END IF;
254 RAISE fnd_api.g_exc_unexpected_error;
255 WHEN others THEN
256 x_return_status := 'E';
257 IF (l_debug = 1) THEN
258 trace('Other error in retrieve business event details');
259 trace('SQL error :'||substr(sqlerrm, 1, 240));
260 END IF;
261
262
263 END;
264
265 -----------------------------------------------------
266 -- retrieve_Bus_Event_Details
267 -- create device request record with the input parameters
268 ------------------------------------------------------
269 PROCEDURE retrieve_Bus_Event_Details(
270 p_bus_event IN NUMBER,
271 p_task_trx_id IN NUMBER,
272 p_org_id IN NUMBER,
273 p_item_id IN NUMBER := NULL,
274 p_subinv IN VARCHAR2 := NULL,
275 p_locator_id IN NUMBER := NULL,
276 p_lpn_id IN NUMBER := NULL,
277 p_xfer_lpn_id IN NUMBER := NULL, --Added for Bug#8778050
278 p_xfr_org_id IN NUMBER := NULL,
279 p_xfr_subinv IN VARCHAR2 := NULL,
280 p_xfr_locator_id IN NUMBER := NULL,
281 p_qty IN NUMBER :=NULL ,
282 p_uom IN VARCHAR2 := NULL,
283 p_rev IN VARCHAR2 := NULL,
284 x_request_id OUT NOCOPY NUMBER ,
285 x_return_status OUT NOCOPY VARCHAR2) IS
286
287 l_request_id NUMBER;
288 l_org_id NUMBER;
289 l_item_id NUMBER;
290 l_subinv VARCHAR2(30);
291 l_locator_id NUMBER;
292 l_lpn_id NUMBER;
293 l_xfr_org_id NUMBER;
294 l_xfr_subinv VARCHAR2(30);
295 l_xfr_locator_id NUMBER;
296 l_qty NUMBER;
297 l_uom VARCHAR2(3);
298 l_rev VARCHAR2(10);
299
300 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
301 BEGIN
302
303 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
304 x_request_id := l_request_id;
305
306 --Added for Bug#8778050 start
307
308 IF (l_debug = 1) THEN
309 trace(' p_xfer_lpn_id'|| p_xfer_lpn_id);
310 trace(' p_lpn_id'|| p_lpn_id);
311 trace(' p_bus_event'|| p_bus_event);
312 END IF;
313
314 --Added for Bug#8778050 end
315
316 IF (p_bus_event=WMS_BE_PICK_DROP) OR
317 ((p_bus_event=WMS_BE_PUTAWAY_DROP) AND (p_xfr_subinv IS NOT NULL)) THEN
318
319 IF (l_debug = 1) THEN
320 trace(' for putaway drop or pick drop, swap the sub/loc and transfer sub/loc');
321 END IF;
322 l_subinv := p_xfr_subinv;
323 l_locator_id := p_xfr_locator_id;
324 l_xfr_subinv := p_subinv;
325 l_xfr_locator_id := p_locator_id;
326 ELSE
327 l_subinv := p_subinv;
328 l_locator_id := p_locator_id;
329 l_xfr_subinv := p_xfr_subinv;
330 l_xfr_locator_id := p_xfr_locator_id;
331
332 END IF;
333 IF (l_debug = 1) THEN
334 trace(' sub,loc,xfr_sub,xfr_loc:'||l_subinv||','||l_locator_id||','||l_xfr_subinv||','||l_xfr_locator_id);
335 END IF;
336
337 insert INTO wms_device_requests (request_id,
338 task_id,
339 task_summary,
340 business_event_id,
341 organization_id,
342 subinventory_code,
343 locator_id,
344 transfer_org_id,
345 transfer_sub_code,
346 transfer_loc_id,
347 inventory_item_id,
348 revision,
349 uom,
350 lpn_id,
351 xfer_lpn_id, --Added for Bug#8778050
352 transaction_quantity,
353 last_update_date,
354 last_updated_by) VALUES
355 (l_request_id,
356 Nvl(p_task_trx_id,-9999),
357 'Y',
358 p_bus_event,
359 p_org_id,
360 l_subinv,
361 l_locator_id,
362 p_xfr_org_id,
363 l_xfr_subinv,
364 l_xfr_locator_id,
365 p_item_id,
366 p_rev,
367 p_uom,
368 p_lpn_id,
369 p_xfer_lpn_id, --Added for Bug#8778050
370 p_qty,
371 Sysdate,
372 FND_GLOBAL.USER_ID);
373
374 x_return_status := 'S';
375
376 EXCEPTION
377 WHEN no_data_found THEN
378 x_return_status := 'E';
379 IF (l_debug = 1) THEN
380 trace('Error in retrieve business event details, no data found');
381 END IF;
382 RAISE fnd_api.g_exc_unexpected_error;
383 WHEN others THEN
384 x_return_status := 'E';
385 IF (l_debug = 1) THEN
386 trace('Other error in retrieve business event details');
387 trace('SQL error :'||substr(sqlerrm, 1, 240));
388 END IF;
389
390 END;
391
392 -----------------------------------------------------
393 -- Overloaded for WMS-OPM
394 --
395 ------------------------------------------------------
396 PROCEDURE retrieve_Bus_Event_Details(
397 p_bus_event IN NUMBER,
398 p_task_trx_id IN NUMBER,
399 p_org_id IN NUMBER,
400 p_item_id IN NUMBER := NULL,
401 p_subinv IN VARCHAR2 := NULL,
402 p_locator_id IN NUMBER := NULL,
403 p_lpn_id IN NUMBER := NULL,
404 p_xfr_org_id IN NUMBER := NULL,
405 p_xfr_subinv IN VARCHAR2 := NULL,
406 p_xfr_locator_id IN NUMBER := NULL,
407 p_qty IN NUMBER :=NULL ,
408 p_uom IN VARCHAR2 := NULL,
409 p_rev IN VARCHAR2 := NULL,
410 p_device_id IN NUMBER,
411 x_request_id OUT NOCOPY NUMBER ,
412 x_return_status OUT NOCOPY VARCHAR2) IS
413
414 l_request_id NUMBER;
415 l_org_id NUMBER;
416 l_item_id NUMBER;
417 l_subinv VARCHAR2(30);
418 l_locator_id NUMBER;
419 l_lpn_id NUMBER;
420 l_xfr_org_id NUMBER;
421 l_xfr_subinv VARCHAR2(30);
422 l_xfr_locator_id NUMBER;
423 l_qty NUMBER;
424 l_uom VARCHAR2(3);
425 l_rev VARCHAR2(10);
426
427 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
428 BEGIN
429
430 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
431 x_request_id := l_request_id;
432
433 IF (p_bus_event=WMS_BE_PICK_DROP) OR
434 ((p_bus_event=WMS_BE_PUTAWAY_DROP) AND (p_xfr_subinv IS NOT NULL)) THEN
435
436 IF (l_debug = 1) THEN
437 trace(' for putaway drop or pick drop, swap the sub/loc and transfer sub/loc');
438 END IF;
439 l_subinv := p_xfr_subinv;
440 l_locator_id := p_xfr_locator_id;
441 l_xfr_subinv := p_subinv;
442 l_xfr_locator_id := p_locator_id;
443 ELSE
444 l_subinv := p_subinv;
445 l_locator_id := p_locator_id;
446 l_xfr_subinv := p_xfr_subinv;
447 l_xfr_locator_id := p_xfr_locator_id;
448
449 END IF;
450 IF (l_debug = 1) THEN
451 trace('Overloaded retrieve_Bus_Event_Details: sub,loc,xfr_sub,xfr_loc,dev_id:'||l_subinv||','||l_locator_id||','||l_xfr_subinv||','||l_xfr_locator_id||','||p_device_id);
452 END IF;
453
454 insert INTO wms_device_requests (request_id,
455 task_id,
456 task_summary,
457 business_event_id,
458 organization_id,
459 subinventory_code,
460 locator_id,
461 transfer_org_id,
462 transfer_sub_code,
463 transfer_loc_id,
464 inventory_item_id,
465 revision,
466 uom,
467 lpn_id,
468 device_id,
469 transaction_quantity,
470 last_update_date,
471 last_updated_by) VALUES
472 (l_request_id,
473 Nvl(p_task_trx_id,-9999),
474 'Y',
475 p_bus_event,
476 p_org_id,
477 l_subinv,
478 l_locator_id,
479 p_xfr_org_id,
480 l_xfr_subinv,
481 l_xfr_locator_id,
482 p_item_id,
483 p_rev,
484 p_uom,
485 p_lpn_id,
486 p_device_id,
487 p_qty,
488 Sysdate,
489 FND_GLOBAL.USER_ID);
490
491 x_return_status := 'S';
492
493 EXCEPTION
494 WHEN no_data_found THEN
495 x_return_status := 'E';
496 IF (l_debug = 1) THEN
497 trace('Error in retrieve business event details, no data found');
498 END IF;
499 RAISE fnd_api.g_exc_unexpected_error;
500 WHEN others THEN
501 x_return_status := 'E';
502 IF (l_debug = 1) THEN
503 trace('Other error in retrieve business event details');
504 trace('SQL error :'||substr(sqlerrm, 1, 240));
505 END IF;
506
507 END;
508
509 -------------------------------------------------------
510 -- retrieve_Lot_Serial_Details
511 --
512 -------------------------------------------------------
513 PROCEDURE retrieve_Lot_Serial_Details(wdrrec wdr_row,
514 x_return_status OUT NOCOPY VARCHAR2 ) IS
515
516 CURSOR lot_ser_cursor IS
517 SELECT
518 mtlt.lot_number lot_num,
519 mtlt.transaction_quantity lot_qty,
520 msnt.fm_serial_number ser_num
521 FROM mtl_material_transactions_temp mmtt,
522 mtl_transaction_lots_temp mtlt,
523 mtl_serial_numbers_temp msnt
524 WHERE
525 mmtt.transaction_temp_id = wdrrec.task_id
526 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
527 AND mmtt.transaction_temp_id = msnt.transaction_temp_id(+)
528 AND ((mmtt.transaction_temp_id=msnt.transaction_temp_id
529 AND mtlt.serial_transaction_temp_id=msnt.transaction_temp_id)
530 OR 1=1);
531
532 l_qty NUMBER;
533 l_count NUMBER :=0 ;
534
535 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
536 BEGIN
537
538 FOR l_rec IN lot_ser_cursor LOOP
539 IF (l_rec.lot_num IS NOT NULL OR l_rec.ser_num IS NOT NULL) THEN
540 IF (l_rec.ser_num IS NOT NULL) THEN
541 l_qty := 1;
542 ELSE
543 l_qty := wdrrec.transaction_quantity;
544 END IF;
545 l_count := l_count + 1;
546 INSERT INTO wms_device_requests (request_id,
547 task_id,
548 relation_id,
549 sequence_id,
550 task_summary,
551 task_type_id,
552 business_event_id,
553 organization_id,
554 subinventory_code,
555 locator_id,
556 transfer_org_id,
557 transfer_sub_code,
558 transfer_loc_id,
559 inventory_item_id,
560 revision,
561 uom,
562 lot_number,
563 lot_qty,
564 serial_number,
565 lpn_id,
566 transaction_quantity,
567 device_id,
568 status_code,
569 last_update_date,
570 last_updated_by,
571 last_update_login) VALUES
572 (wdrrec.request_id,
573 wdrrec.task_id,
574 wdrrec.relation_id,
575 wdrrec.sequence_id,
576 'N',
577 wdrrec.task_type_id,
578 wdrrec.business_event_id,
579 wdrrec.organization_id,
580 wdrrec.subinventory_code,
581 wdrrec.locator_id,
582 wdrrec.transfer_org_id,
583 wdrrec.transfer_sub_code,
584 wdrrec.transfer_loc_id,
585 wdrrec.inventory_item_id,
586 wdrrec.revision,
587 wdrrec.uom,
588 l_rec.lot_num,
589 l_rec.lot_qty,
590 l_rec.ser_num,
591 wdrrec.lpn_id,
592 l_qty,
593 wdrrec.device_id,
594 wdrrec.status_code,
595 wdrrec.last_update_date,
596 wdrrec.last_updated_by,
597 wdrrec.last_update_login);
598 END IF;
599 END LOOP;
600
601 IF(l_count = 0) THEN
602 IF (l_debug = 1) THEN
603 trace('Error in retrieve lot serial details, no data found');
604 END IF;
605 END IF;
606
607 x_return_status := 'S';
608
609 EXCEPTION
610
611 WHEN others THEN
612 x_return_status := 'E';
613 IF (l_debug = 1) THEN
614 trace('Other error in retrieve lot serial details');
615 trace('SQL error :'||substr(sqlerrm, 1, 240));
616 END IF;
617
618
619
620 END;
621
622 ---------------------------------------------------------
623 -- select_Device
624 --
625 ---------------------------------------------------------
626 FUNCTION select_Device(wdrrec WMS_DEVICE_REQUESTS%ROWTYPE,
627 p_autoenable VARCHAR2,
628 p_parent_request_id NUMBER
629 ) return NUMBER is
630
631 dev_id number := 0;
632 par_task_id number := null;
633 l_lot_ser_ok VARCHAR2(1);
634 l_notification_flag VARCHAR2(1);
635 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
636 l_force_sign_on_flag Varchar2(1) :='N';
637 l_dev_id NUMBER :=0;
638 l_current_release_level NUMBER := WMS_CONTROL.g_current_release_level;
639 l_j_release_level NUMBER := INV_RELEASE.g_j_release_level;
640
641 BEGIN
642 IF (l_debug = 1) THEN
643 trace('in select device, org, sub, user, autoenable, bus, parent req:');
644 trace(wdrrec.organization_id ||','||wdrrec.subinventory_code||','||FND_GLOBAL.USER_ID||',' || p_autoenable || ',' || wdrrec.business_event_id || ',' || p_parent_request_id);
645 END IF;
646 -- Try to get the device id from history if the
647 -- event id is task complete and the parent request id is not null
648 IF wdrrec.business_event_id in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel) AND
649 p_parent_request_id IS NOT NULL THEN
650 IF (l_debug = 1) THEN
651 trace('Finding device id from history for task completion');
652 END IF;
653 dev_id := 0;
654 BEGIN
655 SELECT device_id, task_id
656 INTO dev_id, par_task_id
657 FROM wms_device_requests_hist
658 WHERE request_id = p_parent_request_id
659 AND ROWNUM < 2;
660 EXCEPTION
661 WHEN OTHERS THEN
662 dev_id := NULL;
663 IF (l_debug = 1) THEN
664 trace('SQL error :'||substr(sqlerrm, 1, 240));
665 END IF;
666 END;
667 IF (l_debug = 1) THEN
668 trace('Found device id:'||dev_id);
669 END IF;
670
671 ELSIF ( wdrrec.business_event_id = wms_be_mo_task_alloc
672 AND wdrrec.business_event_id = wms_be_putaway_drop) THEN
673
674 --look AT the destination sub for device for replenishment task
675 IF (l_debug = 1) THEN
676 trace('SelectDev:find device at destination for putaway OR repl-allocation');
677 END IF;
678
679 begin
680 seLECT DEVICE_ID INTO dev_ID FROM
681 ( SELECT wbed.DEVICE_ID FROM
682 wms_bus_event_devices wbed,
683 wms_devices_b wd
684 WHERE
685 wd.device_id = wbed.device_id
686 AND WBED.organization_id = WD.organization_id
687 and wd.ENABLED_FLAG = 'Y'
688 and wbed.ENABLED_FLAG = 'Y'
689 AND decode(level_type,DEVICE_LEVEL_SUB,wbed.subinventory_code,level_value) =
690 decode(level_type,DEVICE_LEVEL_SUB,wdrrec.transfer_sub_code,DEVICE_LEVEL_ORG,
691 wdrrec.organization_id,DEVICE_LEVEL_LOCATOR,wdrrec.transfer_loc_id,
692 DEVICE_LEVEL_USER,FND_GLOBAL.USER_ID,level_value)
693 AND Nvl(wbed.organization_id,-1) = Nvl(wdrrec.organization_id,Nvl(wbed.organization_id,-1))
694 AND wbed.AUTO_ENABLED_FLAG = decode(p_autoenable,'Y','Y',wbed.AUTO_ENABLED_FLAG)
695 AND wbed.business_event_id = wdrrec.business_event_id
696 ORDER BY level_type desc)
697 where ROWNUM<2;
698
699 -- J Development
700 IF l_current_release_level >= l_j_release_level THEN
701 IF (dev_id <> 0 ) THEN
702 BEGIN
703 SELECT force_sign_on_flag
704 INTO l_force_sign_on_flag
705 FROM wms_devices_b
706 WHERE device_id = dev_id;
707 IF(l_force_sign_on_flag='Y') THEN
708 BEGIN
709 SELECT device_id
710 INTO l_dev_id
711 FROM wms_device_assignment_temp
712 WHERE device_id = dev_id
713 AND CREATED_BY = FND_GLOBAL.USER_ID;
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 dev_id :=0;
717 END;
718
719 END IF;
720
721 EXCEPTION
722 WHEN OTHERS THEN
723 dev_id :=0;
724 END;
725 END IF;
726 END IF;
727
728 IF (l_debug = 1) THEN
729 trace('Found device at destination device id:'||dev_id);
730 END IF;
731
732 exception
733 when NO_DATA_FOUND THEN
734 IF (l_debug = 1) THEN
735 trace('SelectDev:No device found at destination FOR putaway OR repl-allocation');
736 END IF;
737
738 END;
739
740
741 ELSE --Other business events
742
743 BEGIN
744 SELECT DEVICE_ID INTO dev_ID FROM
745 ( SELECT wbed.DEVICE_ID FROM
746 wms_bus_event_devices wbed,
747 wms_devices_b wd
748 WHERE
749 wd.device_id = wbed.device_id
750 AND WBED.organization_id = WD.organization_id
751 and wd.ENABLED_FLAG = 'Y'
752 and wbed.ENABLED_FLAG = 'Y'
753 AND decode(level_type,DEVICE_LEVEL_SUB,wbed.subinventory_code,level_value) =
754 decode(level_type,DEVICE_LEVEL_SUB,wdrrec.subinventory_code,DEVICE_LEVEL_ORG,
755 wdrrec.organization_id,DEVICE_LEVEL_LOCATOR,wdrrec.locator_id,
756 DEVICE_LEVEL_USER,FND_GLOBAL.USER_ID,level_value)
757 AND Nvl(wbed.organization_id,-1) = Nvl(wdrrec.organization_id,Nvl(wbed.organization_id,-1))
758 AND wbed.AUTO_ENABLED_FLAG = decode(p_autoenable,'Y','Y',wbed.AUTO_ENABLED_FLAG)
759 AND wbed.business_event_id = wdrrec.business_event_id
760 ORDER BY level_type desc)
761 where ROWNUM<2;
762
763 -- J Development
764 IF l_current_release_level >= l_j_release_level THEN
765 IF (dev_id <> 0 ) THEN
766 BEGIN
767 SELECT force_sign_on_flag
768 INTO l_force_sign_on_flag
769 FROM wms_devices_b
770 WHERE device_id = dev_id;
771 IF(l_force_sign_on_flag='Y') THEN
772 BEGIN
773 SELECT device_id
774 INTO l_dev_id
775 FROM wms_device_assignment_temp
776 WHERE device_id = dev_id
777 AND CREATED_BY = FND_GLOBAL.USER_ID;
778 EXCEPTION
779 WHEN NO_DATA_FOUND THEN
780 dev_id :=0;
781 END;
782
783 END IF;
784
785 EXCEPTION
786 WHEN OTHERS THEN
787 dev_id :=0;
788 END;
789 END IF;
790 END IF;
791
792 IF (l_debug = 1) THEN
793 trace('Found device id:'||dev_id);
794 END IF;
795
796 EXCEPTION
797 when NO_DATA_FOUND THEN
798 IF (l_debug = 1) THEN
799 trace('SelectDev:No device found at any level for source');
800
801 END IF;
802 END;
803
804 END IF;
805
806 IF (dev_id <> 0)THEN
807 -- For business event Task Complete, check whether the device is enabled
808 -- for task complete notification.
809
810 /*Bug#6344286. Removed events wms_be_task_skip and wms_be_task_cancel from below IF
811 because notification_flag is relevant only for the task_complete even */
812 IF wdrrec.business_event_id in (wms_be_task_complete) THEN
813 BEGIN
814 select nvl(notification_flag, 'N')
815 into l_notification_flag
816 from wms_devices_b
817 where device_id = dev_id;
818 EXCEPTION
819 WHEN no_data_found THEN
820 IF (l_debug = 1) THEN
821 trace('No device found for ID:'||dev_id);
822 END IF;
823 l_notification_flag := 'N';
824 END;
825 ELSE
826 --SET the notification flag to Y for all other bus event for processing
827 l_notification_flag := 'Y';
828 END IF;
829
830 IF l_notification_flag = 'Y' THEN
831 UPDATE wms_device_requests
832 SET device_id = dev_id,
833 task_id = nvl(par_task_id, task_id)
834 WHERE request_id = wdrrec.request_id
835 AND Nvl(task_type_id,0) = Nvl(wdrrec.task_type_id,Nvl(task_type_id,0))
836 AND organization_id = wdrrec.organization_id
837 AND business_event_id = wdrrec.business_event_id
838 AND Nvl(task_id,0) = Nvl(wdrrec.task_id,Nvl(task_id,0)); -- BUG4616997
839
840 --set the global var if device is lot/ser capable. to be called
841 --FROM cartonization FOR pick release AND REPLENISHMENT TASK
842 --allocation bus event
843 IF wdrrec.business_event_id IN (wms_be_pick_release, wms_be_mo_task_alloc,WMS_BE_WIP_PICK_RELEASE) then BEGIN --Added WMS_BE_WIP_PICK_RELEASE for Bug 13702075
844 select Nvl(lot_serial_capable,'N')
845 into l_lot_ser_ok
846 from WMS_DEVICES_B
847 where device_id = dev_id;
848 EXCEPTION
849 WHEN no_data_found THEN
850 l_lot_ser_ok := 'N';
851 END;
852
853 -- If Details enabled for device, retrieve the Lot/Serialdetails
854 IF (l_lot_ser_ok = 'Y') THEN
855 --set the global variable to be used in cartonization
856 --code to insert lot/ser records into wdr table
857 wms_insert_lotSer_rec_WDR := 1;
858 END IF;
859
860 END IF;
861 END IF;
862 ELSIF wdrrec.business_event_id NOT IN (wms_be_task_complete,
863 wms_be_task_skip,
864 wms_be_task_cancel) then
865 IF (l_debug = 1) THEN
866 trace('No device found: Updating WDT for Task_id::'||wdrrec.task_id);
867 END IF;
868 UPDATE wms_dispatched_tasks
869 SET DEVICE_REQUEST_ID = NULL
870 WHERE TRANSACTION_TEMP_ID = wdrrec.task_id;
871 END IF;
872
873 IF (l_debug = 1) THEN
874 trace('SelectDev: Deviceid='||dev_id||',parent_task='||par_task_id);
875 END IF;
876
877 return dev_id;
878 END;
879
880
881 ---------------------------------------------------------
882 -- Write XML/CSV to File
883 ---------------------------------------------------------
884 PROCEDURE WriteToFile(p_xml IN CLOB, p_file_dir IN VARCHAR2, p_file_name IN VARCHAR2) IS
885 l_xmlstr varchar2(32767);
886 l_line varchar2(2000);
887 l_file UTL_FILE.FILE_TYPE;
888 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
889 BEGIN
890 l_file := utl_file.fopen(rtrim(p_file_dir,'/'), p_file_name, 'w');
891 l_xmlstr := dbms_lob.substr(p_xml, 32767);
892 loop
893 exit when l_xmlstr is null;
894 l_line := substr(l_xmlstr, 1, instr(l_xmlstr, fnd_global.local_chr(10))-1);
895 utl_file.put_line(l_file, l_line);
896 l_xmlstr := substr(l_xmlstr, instr(l_xmlstr, fnd_global.local_chr(10))+1);
897 end loop;
898 utl_file.fclose(l_file);
899 EXCEPTION
900 WHEN utl_file.invalid_path THEN
901 IF (l_debug = 1) THEN
902 trace('Invalid path in WriteToFile: '||p_file_dir);
903 END IF;
904 WHEN utl_file.invalid_mode THEN
905 IF (l_debug = 1) THEN
906 trace('Invalid mode in WriteToFile: w');
907 END IF;
908 WHEN fnd_api.g_exc_error THEN
909 IF (l_debug = 1) THEN
910 trace(' Expected Error in WriteXmlFile');
911 END IF;
912 WHEN fnd_api.g_exc_unexpected_error THEN
913 IF (l_debug = 1) THEN
914 trace(' Unexpected Error in WriteXmlFile');
915 END IF;
916 WHEN OTHERS THEN
917 IF (l_debug = 1) THEN
918 trace('Error in WriteXmlFile');
919 trace('SQL error :'||substr(sqlerrm, 1, 240));
920 END IF;
921 END WriteToFile;
922
923
924 ---------------------------------------------------------
925 -- Generate XML/CSV
926 --
927 ---------------------------------------------------------
928 FUNCTION generate_xml_csv(p_device_id NUMBER, p_iotype NUMBER) return NUMBER is
929 retval number := 0;
930
931 CURSOR xml_cur(p_dev_id NUMBER, p_task_sum VARCHAR2) IS
932 SELECT wd.name DEVICE, wdr.request_id REQUESTID, ml1.meaning TASKTYPE,
933 ml2.meaning BUSINESSEVENT,
934 wdr.task_id TASKID, wdr.sequence_id SEQUENCEID,
935 wdr.relation_id RELATIONID,
936 mp1.organization_code ORG, wdr.subinventory_code SUB,
937 milk1.concatenated_segments LOC, mp2.organization_code TRANSFERORG,
938 wdr.transfer_sub_code TRANSFERSUB, milk2.concatenated_segments TRANSFERLOC,
939 wlpn.license_plate_number LPN, wdr.xfer_lpn_id XFERLPNID, msik.concatenated_segments ITEM, --Added for Bug#8512121
940 wdr.revision REVISION, wdr.transaction_quantity QUANTITY,
941 wdr.uom UOM, wdr.lot_number LOT, wdr.lot_qty LOTQTY,
942 wdr.serial_number serial,
943 wdr.status_msg STATUSMSG, wdr.last_update_date timestamp,
944 wdr.business_event_id bus_event_id
945 FROM wms_device_requests wdr, mfg_lookups ml1, mfg_lookups ml2,
946 wms_devices_vl wd, mtl_parameters mp1, mtl_item_locations_kfv milk1,
947 mtl_parameters mp2, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn,
948 mtl_system_items_kfv msik
949 WHERE ml1.lookup_type(+)= 'WMS_TASK_TYPES' AND ml1.lookup_code(+) = wdr.task_type_id
950 AND ml2.lookup_type(+)='WMS_BUS_EVENT_TYPES'
951 AND ml2.lookup_code(+) = wdr.business_event_id
952 AND wd.device_id = wdr.device_id AND mp1.organization_id = wdr.organization_id
953 AND milk1.organization_id(+) = wdr.organization_id
954 AND milk1.subinventory_code(+) = wdr.subinventory_code
955 AND milk1.inventory_location_id(+) = wdr.locator_id
956 AND mp2.organization_id (+) = wdr.transfer_org_id
957 AND milk2.organization_id(+) = wdr.transfer_org_id
958 AND milk2.subinventory_code(+) = wdr.transfer_sub_code
959 AND milk2.inventory_location_id (+) = wdr.transfer_loc_id
960 AND wlpn.lpn_id(+) = wdr.lpn_id
961 AND msik.organization_id(+)= wdr.organization_id
962 AND msik.inventory_item_id(+) = wdr.inventory_item_id
963 AND wdr.device_id = p_dev_id
964 AND nvl(wdr.task_summary,'Y') = p_task_sum
965 ORDER BY wdr.task_id asc, wdr.sequence_id asc,wdr.task_type_id asc;
966
967 l_seperator VARCHAR2(1) := ',';
968
969 CURSOR csv_cur(p_dev_id NUMBER, p_task_sum VARCHAR2) IS
970 SELECT wd.name ||l_seperator|| wdr.request_id ||l_seperator|| ml1.meaning ||l_seperator||
971 ml2.meaning ||l_seperator|| wdr.task_id ||l_seperator|| wdr.sequence_id ||l_seperator
972 ||wdr.relation_id||l_seperator ||
973 mp1.organization_code ||l_seperator|| wdr.subinventory_code ||l_seperator||
974 milk1.concatenated_segments ||l_seperator|| mp2.organization_code ||l_seperator||
975 wdr.transfer_sub_code ||l_seperator|| milk2.concatenated_segments ||l_seperator||
976 wlpn.license_plate_number ||l_seperator|| wlpn1.license_plate_number ||l_seperator|| msik.concatenated_segments ||l_seperator|| --Added for Bug#8512121
977 wdr.revision ||l_seperator|| wdr.transaction_quantity ||l_seperator||
978 wdr.uom ||l_seperator|| wdr.lot_number ||l_seperator||
979 wdr.lot_qty||l_seperator||
980 wdr.serial_number||l_seperator||wdr.status_msg||l_seperator||wdr.last_update_date
981 CSV_LINE
982 FROM wms_device_requests wdr, mfg_lookups ml1, mfg_lookups ml2,
983 wms_devices_vl wd, mtl_parameters mp1, mtl_item_locations_kfv milk1,
984 mtl_parameters mp2, mtl_item_locations_kfv milk2, wms_license_plate_numbers wlpn,wms_license_plate_numbers wlpn1, --Added for Bug#8512121
985 mtl_system_items_kfv msik
986 WHERE ml1.lookup_type(+) = 'WMS_TASK_TYPES' AND ml1.lookup_code(+) = wdr.task_type_id
987 AND ml2.lookup_type(+)='WMS_BUS_EVENT_TYPES'
988 AND ml2.lookup_code(+) = wdr.business_event_id
989 AND wd.device_id = wdr.device_id AND mp1.organization_id = wdr.organization_id
990 AND milk1.organization_id(+) = wdr.organization_id
991 AND milk1.subinventory_code(+) = wdr.subinventory_code
992 AND milk1.inventory_location_id(+) = wdr.locator_id
993 AND mp2.organization_id (+) = wdr.transfer_org_id
994 AND milk2.organization_id(+) = wdr.transfer_org_id
995 AND milk2.subinventory_code(+) = wdr.transfer_sub_code
996 AND milk2.inventory_location_id (+) = wdr.transfer_loc_id
997 AND wlpn.lpn_id(+) = wdr.lpn_id
998 AND wlpn1.lpn_id(+)= wdr.xfer_lpn_id --Added for Bug#8512121
999 AND msik.organization_id(+) = wdr.organization_id
1000 AND msik.inventory_item_id(+) = wdr.inventory_item_id
1001 AND wdr.device_id = p_dev_id
1002 AND nvl(wdr.task_summary,'Y') = p_task_sum
1003 ORDER BY wdr.task_id asc, wdr.sequence_id asc, wdr.task_type_id asc;
1004
1005 l_lot_serial_enabled VARCHAR2(1);
1006 l_detail_available NUMBER;
1007 l_task_sum VARCHAR2(1);
1008 l_dev_name VARCHAR2(30);
1009 l_file_dir VARCHAR2(50);
1010 l_file_prefix VARCHAR2(50);
1011 l_seq_id NUMBER;
1012 l_file_name VARCHAR2(50);
1013
1014 l_file UTL_FILE.FILE_TYPE;
1015
1016 l_csv_column_list VARCHAR2(200) :=
1017 'DEVICE,REQUESTID,TASKTYPE,BUSINESSEVENT,TASKID,SEQUENCEID,RELATIONID,ORG,SUB,'||'LOC,TRANSFERORG,TRANSFERSUB,TRANSFERLOC,LPN,XFERLPN,ITEM,REVISION,QUANTITY,UOM,LOT,LOTQTY,SERIAL,ERRORCODE,TIMESTAMP'; --Added for Bug#8512121
1018
1019 l_order_num NUMBER;
1020 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1021 BEGIN
1022 IF (l_debug = 1) THEN
1023 trace('In generate xml/csv, device_id = ' || p_device_id);
1024 trace('Getting output dir, file name, lot/serial enabled, device name');
1025 END IF;
1026 BEGIN
1027 SELECT rtrim(out_directory,'/'), out_file_prefix, nvl(LOT_SERIAL_CAPABLE, 'N'), name
1028 INTO l_file_dir, l_file_prefix, l_lot_serial_enabled, l_dev_name
1029 FROM wms_devices
1030 WHERE device_id = p_device_id;
1031
1032 SELECT request_id INTO l_seq_id
1033 FROM wms_device_requests
1034 WHERE device_id = p_device_id
1035 AND ROWNUM<2;
1036
1037 EXCEPTION
1038 WHEN no_data_found THEN
1039 IF (l_debug = 1) THEN
1040 trace('Error in getting device property, no data found');
1041 END IF;
1042 RAISE fnd_api.g_exc_unexpected_error;
1043 WHEN others THEN
1044 IF (l_debug = 1) THEN
1045 trace('Other error in getting device property');
1046 trace('SQL error :'||substr(sqlerrm, 1, 240));
1047 END IF;
1048 END;
1049 l_file_name := l_file_prefix || l_seq_id||'_'||p_device_id;
1050
1051 IF (l_debug = 1) THEN
1052 trace('Device supports lot/serial ? ' || l_lot_serial_enabled);
1053 END IF;
1054 IF l_lot_serial_enabled = 'Y' THEN
1055 -- When the device supports lot/serial,
1056 -- lot/serial information still may not be available
1057 -- check if detail information is not avaible, query summary record.
1058 BEGIN
1059 -- Bug 2762697: Lot information is not populated for device request
1060 -- The problem is that if there is more than one detail record
1061 -- The select into will fail
1062 -- Change to just check existence
1063 SELECT 1 INTO l_detail_available FROM dual
1064 WHERE exists(
1065 SELECT 1
1066 FROM wms_device_requests
1067 WHERE device_id = p_device_id
1068 AND nvl(task_summary,'Y') = 'N');
1069
1070 IF (l_debug = 1) THEN
1071 trace('Lot/Serial detail info available');
1072 END IF;
1073 l_task_sum := 'N';
1074 EXCEPTION
1075 WHEN no_data_found THEN
1076 IF (l_debug = 1) THEN
1077 trace('Lot/Serial detail info not available, use summary info');
1078 END IF;
1079 l_task_sum := 'Y';
1080 WHEN others THEN
1081 IF (l_debug = 1) THEN
1082 trace('Error in checking detail info, use summary info');
1083 trace('SQL error :'||substr(sqlerrm, 1, 240));
1084 END IF;
1085 l_task_sum := 'Y';
1086 END;
1087 ELSE
1088 l_task_sum := 'Y';
1089 END IF;
1090
1091 IF (l_debug = 1) THEN
1092 trace('After checking device and detail info, l_task_sum=' || l_task_sum);
1093 END IF;
1094
1095 IF p_iotype = WMS_DEV_IO_XML THEN
1096
1097 -- XML
1098 l_file_name := l_file_name || '.xml';
1099 IF (l_debug = 1) THEN
1100 trace('Result will be saved in '||l_file_dir||'/'||l_file_name);
1101 END IF;
1102
1103 --OPEN FILE
1104 l_file := UTL_FILE.FOPEN(l_file_dir, l_file_name, 'w');
1105 UTL_FILE.PUT_LINE(l_file, XML_HEADER);
1106 UTL_FILE.PUT_LINE(l_file, DEVICEH_TB || ' name="' || l_dev_name ||'"'
1107 || ' request_id="' || l_seq_id || '"'|| TAG_E);
1108 IF (l_debug = 1) THEN
1109 trace('Opening xml cursor with dev_id='||p_device_id||',task_sum='||l_task_sum);
1110 END IF;
1111
1112 FOR v_xml IN xml_cur(p_device_id, l_task_sum) LOOP
1113
1114 IF v_xml.bus_event_id = wms_be_pick_release THEN
1115 begin
1116 select To_number(wdd.source_header_number) into l_order_num from
1117 wsh_delivery_details wdd,
1118 mtl_material_transactions_temp mmtt
1119 where mmtt.transaction_temp_id = v_xml.taskid
1120 and mmtt.trx_source_line_id = wdd.source_line_id;
1121 EXCEPTION
1122 WHEN others THEN
1123 IF (l_debug = 1) THEN
1124 trace('Could not retrieve the SO Number');
1125 END IF;
1126 END;
1127
1128 ELSIF v_xml.bus_event_id = wms_be_mo_task_alloc THEN
1129 BEGIN
1130 select mtrl.header_id into l_order_num
1131 from mtl_txn_request_lines mtrl,
1132 mtl_material_transactions_temp mmtt
1133 where mmtt.move_order_line_id = mtrl.line_id
1134 and transaction_temp_id = v_xml.taskid;
1135 EXCEPTION
1136 WHEN others THEN
1137 IF (l_debug = 1) THEN
1138 trace('Could not retrieve the Move Order Header');
1139 END IF;
1140 END;
1141 END IF;
1142
1143 IF (l_debug = 1) THEN
1144 trace('Done setting header information');
1145 END IF;
1146
1147
1148
1149 UTL_FILE.PUT_LINE(l_file, TASK_TB);
1150 UTL_FILE.PUT_LINE(l_file, DEVICE_TB|| v_xml.DEVICE ||DEVICE_TE);
1151 UTL_FILE.PUT_LINE(l_file, REQUESTID_TB|| v_xml.REQUESTID||REQUESTID_TE);
1152 UTL_FILE.PUT_LINE(l_file, TASKTYPE_TB|| v_xml.TASKTYPE||TASKTYPE_TE);
1153 UTL_FILE.PUT_LINE(l_file, BUSINESSEVENT_TB||v_xml.BUSINESSEVENT ||BUSINESSEVENT_TE);
1154 UTL_FILE.PUT_LINE(l_file, TASKID_TB|| v_xml.TASKID||TASKID_TE);
1155 UTL_FILE.PUT_LINE(l_file, SEQUENCEID_TB|| v_xml.SEQUENCEID||SEQUENCEID_TE);
1156 UTL_FILE.PUT_LINE(l_file, RELATIONID_TB|| v_xml.RELATIONID||RELATIONID_TE);
1157 UTL_FILE.PUT_LINE(l_file, ORG_TB|| v_xml.ORG||ORG_TE);
1158 UTL_FILE.PUT_LINE(l_file, SUB_TB|| v_xml.SUB||SUB_TE);
1159 UTL_FILE.PUT_LINE(l_file, LOC_TB||v_xml.LOC ||LOC_TE);
1160 UTL_FILE.PUT_LINE(l_file, TRANSFERORG_TB|| v_xml.TRANSFERORG||TRANSFERORG_TE);
1161 UTL_FILE.PUT_LINE(l_file, TRANSFERSUB_TB|| v_xml.TRANSFERSUB||TRANSFERSUB_TE);
1162 UTL_FILE.PUT_LINE(l_file, TRANSFERLOC_TB|| v_xml.TRANSFERLOC||TRANSFERLOC_TE);
1163 UTL_FILE.PUT_LINE(l_file, LPN_TB||v_xml.LPN ||LPN_TE);
1164 UTL_FILE.PUT_LINE(l_file, XFERLPN_TB||v_xml.XFERLPNID ||XFERLPN_TE); --Added for Bug#8512121
1165 UTL_FILE.PUT_LINE(l_file, ITEM_TB||v_xml.ITEM ||ITEM_TE);
1166 UTL_FILE.PUT_LINE(l_file, REVISION_TB|| v_xml.REVISION||REVISION_TE);
1167 UTL_FILE.PUT_LINE(l_file, QUANTITY_TB|| v_xml.QUANTITY||QUANTITY_TE);
1168 UTL_FILE.PUT_LINE(l_file, UOM_TB|| v_xml.UOM||UOM_TE);
1169 UTL_FILE.PUT_LINE(l_file, LOT_TB|| v_xml.LOT||LOT_TE);
1170 UTL_FILE.PUT_LINE(l_file, LOTQTY_TB|| v_xml.LOTQTY||LOTQTY_TE);
1171 UTL_FILE.PUT_LINE(l_file, SERIAL_TB|| v_xml.SERIAL||SERIAL_TE);
1172 UTL_FILE.PUT_LINE(l_file, SO_TB|| l_order_num||SO_TE);
1173 UTL_FILE.PUT_LINE(l_file, ERRORCODE_TB|| v_xml.STATUSMSG||ERRORCODE_TE);
1174 UTL_FILE.PUT_LINE(l_file, TIMESTAMP_TB|| v_xml.TIMESTAMP||TIMESTAMP_TE);
1175 UTL_FILE.PUT_LINE(l_file, TASK_TE);
1176 END LOOP;
1177 UTL_FILE.PUT_LINE(l_file, DEVICE_TE);
1178 UTL_FILE.fclose(l_file);
1179 IF (l_debug = 1) THEN
1180 trace(' File created ');
1181 END IF;
1182
1183 ELSIF p_iotype = WMS_DEV_IO_CSV THEN
1184 -- CSV
1185 l_file_name := l_file_name || '.csv';
1186 IF (l_debug = 1) THEN
1187 trace('Result will be saved in '||l_file_dir||' '||l_file_name);
1188 END IF;
1189 l_seperator := ',';
1190
1191 -- OPEN FILE
1192 l_file := UTL_FILE.FOPEN(l_file_dir, l_file_name, 'w');
1193 UTL_FILE.PUT_LINE(l_file, l_csv_column_list);
1194 IF (l_debug = 1) THEN
1195 trace('Opening csv cursor with dev_id='||p_device_id||',task_sum='||l_task_sum);
1196 END IF;
1197 FOR v_csv IN csv_cur(p_device_id, l_task_sum) LOOP
1198 UTL_FILE.PUT_LINE(l_file, v_csv.csv_line);
1199 END LOOP;
1200
1201 UTL_FILE.fclose(l_file);
1202 IF (l_debug = 1) THEN
1203 trace(' File created ');
1204 END IF;
1205 ELSE
1206 IF (l_debug = 1) THEN
1207 trace('Invalid iotype value passed to generate_xml_csv:'||p_iotype);
1208 END IF;
1209 RAISE fnd_api.g_exc_unexpected_error;
1210 END IF;
1211
1212 -- update outfile_name
1213 IF (l_debug = 1) THEN
1214 trace('update outfile_name ' || l_file_name || p_device_id || l_task_sum);
1215 END IF;
1216 UPDATE wms_device_requests
1217 SET outfile_name = l_file_name
1218 WHERE device_id = p_device_id
1219 AND nvl(task_summary, 'Y') = decode(l_lot_serial_enabled,'N','Y','Y',nvl(task_summary,'Y'),'Y');
1220
1221 return retval;
1222 EXCEPTION
1223 WHEN utl_file.invalid_path THEN
1224 IF (l_debug = 1) THEN
1225 trace('Invalid Path error in generate_xml_csv');
1226 END IF;
1227 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_SETUP_ERR');
1228 FND_MESSAGE.SET_TOKEN('ERROR_REASON', 'Invalid Path');
1229 FND_MSG_PUB.ADD;
1230 retval := -1;
1231 return retval;
1232 WHEN utl_file.invalid_mode THEN
1233 IF (l_debug = 1) THEN
1234 trace('Invalid mode in generate_xml_csv: w');
1235 END IF;
1236 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_SETUP_ERR');
1237 FND_MESSAGE.SET_TOKEN('ERROR_REASON', 'Invalid Mode');
1238 FND_MSG_PUB.ADD;
1239 retval := -1;
1240 return retval;
1241 WHEN FND_API.G_EXC_ERROR THEN
1242 IF (l_debug = 1) THEN
1243 trace(' Expected error in generate XML ');
1244 END IF;
1245 RAISE FND_API.G_EXC_ERROR;
1246 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1247 IF (l_debug = 1) THEN
1248 trace(' UnExpected error in generate XML ');
1249 END IF;
1250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1251 WHEN OTHERS THEN
1252 IF (l_debug = 1) THEN
1253 trace(' Other error in generate XML ');
1254 trace('SQL error :'||substr(sqlerrm, 1, 240));
1255 END IF;
1256 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1257 END generate_xml_csv;
1258
1259
1260
1261 ---------------------------------------------------------
1262 -- Populate History
1263 --
1264 ---------------------------------------------------------
1265 PROCEDURE populate_History (p_call_ctx IN VARCHAR2 ,
1266 p_bus_event IN NUMBER,
1267 x_device_records_exist OUT NOCOPY VARCHAR2) IS -- Modified for bug#8778050
1268
1269 l_counter NUMBER := 0;
1270 CURSOR cur_dev IS SELECT * FROM wms_device_requests where device_id is
1271 not null;
1272
1273 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1274 BEGIN
1275 x_device_records_exist := 'Y';
1276
1277 FOR l_rec IN cur_dev LOOP
1278 /* IF (l_debug = 1) THEN
1279 trace('outfile_name='||l_rec.outfile_name||'request_id:'||l_rec.request_id||'task_id:'||l_rec.task_id);
1280 END IF;
1281 */
1282 l_counter := l_counter +1;
1283 INSERT INTO wms_device_requests_hist (request_id,
1284 task_id,
1285 relation_id,
1286 sequence_id,
1287 task_summary,
1288 task_type_id,
1289 business_event_id,
1290 organization_id,
1291 subinventory_code,
1292 locator_id,
1293 transfer_org_id,
1294 transfer_sub_code,
1295 transfer_loc_id,
1296 inventory_item_id,
1297 revision,
1298 uom,
1299 lot_number,
1300 lot_qty,
1301 serial_number,
1302 lpn_id,
1303 xfer_lpn_id,
1304 transaction_quantity,
1305 device_id,
1306 status_code,
1307 status_msg,
1308 outfile_name,
1309 request_date,
1310 resubmit_date,
1311 requested_by,
1312 responsibility_application_id,
1313 responsibility_id,
1314 concurrent_request_id,
1315 program_application_id,
1316 program_id,
1317 program_update_date,
1318 creation_date,
1319 created_by,
1320 last_update_date,
1321 last_updated_by,
1322 last_update_login) VALUES
1323 (l_rec.request_id,
1324 l_rec.task_id,
1325 l_rec.relation_id,
1326 l_rec.sequence_id,
1327 l_rec.task_summary,
1328 l_rec.task_type_id,
1329 l_rec.business_event_id,
1330 l_rec.organization_id,
1331 l_rec.subinventory_code,
1332 l_rec.locator_id,
1333 l_rec.transfer_org_id,
1334 l_rec.transfer_sub_code,
1335 l_rec.transfer_loc_id,
1336 l_rec.inventory_item_id,
1337 l_rec.revision,
1338 l_rec.uom,
1339 l_rec.lot_number,
1340 l_rec.lot_qty,
1341 l_rec.serial_number,
1342 l_rec.lpn_id,
1343 l_rec.xfer_lpn_id,
1344 l_rec.transaction_quantity,
1345 l_rec.device_id,
1346 Nvl(l_rec.status_code,'S'),
1347 l_rec.status_msg,
1348 l_rec.outfile_name,
1349 l_rec.last_update_date,
1350 NULL,
1351 l_rec.last_updated_by,
1352 FND_GLOBAL.RESP_APPL_ID,
1353 FND_GLOBAL.RESP_ID,
1354 null,
1355 null,
1356 null,
1357 null,
1358 l_rec.last_update_date,
1359 l_rec.last_updated_by,
1360 l_rec.last_update_date,
1361 l_rec.last_updated_by,
1362 l_rec.last_update_login);
1363
1364
1365 END LOOP;
1366
1367 -- Added for bug#8778050 start
1368
1369 IF ( p_call_ctx = DEV_REQ_USER and p_bus_event=WMS_BE_TASK_COMPLETE) then
1370 COMMIT;
1371 END IF;
1372
1373 -- Added for bug#8778050 end
1374
1375 IF (l_debug = 1) THEN
1376 trace('Inside the populate_history:l_counter'||l_counter);
1377 END IF;
1378 IF l_counter = 0 THEN
1379 x_device_records_exist := 'N';
1380 END IF;
1381
1382 END;
1383
1384 ----------------------------------------------------------
1385 ---- Move rows back to the wms_device_requests temp table
1386 ----------------------------------------------------------
1387
1388 PROCEDURE move_resubmit_rows(p_request_id number, p_bus_event_id number)IS
1389
1390
1391 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1392 BEGIN
1393
1394 IF (l_debug = 1) THEN
1395 trace('Move from Hist table to temp table');
1396 END IF;
1397
1398 insert into wms_device_requests (
1399 BUSINESS_EVENT_ID
1400 ,DEVICE_ID
1401 ,DEVICE_STATUS
1402 ,INVENTORY_ITEM_ID
1403 ,LAST_UPDATED_BY
1404 ,LAST_UPDATE_DATE
1405 ,LAST_UPDATE_LOGIN
1406 ,LOCATOR_ID
1407 ,LOT_NUMBER
1408 ,LOT_QTY
1409 ,LPN_ID
1410 ,ORGANIZATION_ID
1411 ,OUTFILE_NAME
1412 ,REASON_ID
1413 ,RELATION_ID
1414 ,REQUEST_ID
1415 ,REVISION
1416 ,SEQUENCE_ID
1417 ,SERIAL_NUMBER
1418 ,STATUS_CODE
1419 ,STATUS_MSG
1420 ,SUBINVENTORY_CODE
1421 ,TASK_ID
1422 ,TASK_SUMMARY
1423 ,TASK_TYPE_ID
1424 ,TRANSACTION_QUANTITY
1425 ,TRANSFER_LOC_ID
1426 ,TRANSFER_ORG_ID
1427 ,TRANSFER_SUB_CODE
1428 ,UOM
1429 ,XFER_LPN_ID)
1430 select
1431 BUSINESS_EVENT_ID
1432 ,DEVICE_ID
1433 ,DEVICE_STATUS
1434 ,INVENTORY_ITEM_ID
1435 ,LAST_UPDATED_BY
1436 ,LAST_UPDATE_DATE
1437 ,LAST_UPDATE_LOGIN
1438 ,LOCATOR_ID
1439 ,LOT_NUMBER
1440 ,LOT_QTY
1441 ,LPN_ID
1442 ,ORGANIZATION_ID
1443 ,OUTFILE_NAME
1444 ,REASON_ID
1445 ,RELATION_ID
1446 ,REQUEST_ID
1447 ,REVISION
1448 ,SEQUENCE_ID
1449 ,SERIAL_NUMBER
1450 ,STATUS_CODE
1451 ,STATUS_MSG
1452 ,SUBINVENTORY_CODE
1453 ,TASK_ID
1454 ,TASK_SUMMARY
1455 ,TASK_TYPE_ID
1456 ,TRANSACTION_QUANTITY
1457 ,TRANSFER_LOC_ID
1458 ,TRANSFER_ORG_ID
1459 ,TRANSFER_SUB_CODE
1460 ,UOM
1461 ,XFER_LPN_ID
1462 from wms_device_requests_hist
1463 WHERE request_id = p_request_id
1464 AND status_code ='P'
1465 AND Nvl(business_event_id,-1) = Nvl(p_bus_event_id,-1);
1466
1467
1468 END;
1469
1470
1471 ---------------------------------------------------------
1472 -- Retrieve err message from the message stack
1473 ---------------------------------------------------------
1474 FUNCTION GET_MSG_STACK RETURN VARCHAR2 IS
1475 l_msg_count number;
1476 l_msg_data varchar2(240);
1477 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1478 BEGIN
1479 fnd_msg_pub.count_And_get(
1480 p_count => l_msg_count,
1481 p_data => l_msg_data,
1482 p_encoded => 'F'
1483 );
1484 IF (l_debug = 1) THEN
1485 trace('get message stack, count='||l_msg_count);
1486 END IF;
1487 IF l_msg_count = 0 THEN
1488 l_msg_data := '';
1489 ELSIF l_msg_count =1 THEN
1490 null;
1491 ELSE
1492 l_msg_data := fnd_msg_pub.get(l_msg_count,'F');
1493
1494 /*l_msg_data := '';
1495 FOR i IN 1..l_msg_count LOOP
1496 l_msg_data := l_msg_data || fnd_msg_pub.get(I,'F');
1497 END LOOP;
1498 */
1499 END IF;
1500 FND_MSG_PUB.initialize;
1501 RETURN l_msg_data;
1502 END GET_MSG_STACK;
1503
1504 ---------------------------------------------------------
1505 -- DEVICE_REQUEST
1506 --
1507 ---------------------------------------------------------
1508 PROCEDURE DEVICE_REQUEST(
1509 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1510 p_bus_event IN NUMBER,
1511 p_call_ctx IN VARCHAR2 ,
1512 p_task_trx_id IN NUMBER := NULL,
1513 p_org_id IN NUMBER := NULL,
1514 p_item_id IN NUMBER := NULL,
1515 p_subinv IN VARCHAR2 := NULL,
1516 p_locator_id IN NUMBER := NULL,
1517 p_lpn_id IN NUMBER := NULL,
1518 p_xfer_lpn_id IN NUMBER := NULL, --Added for Bug#8778050
1519 p_xfr_org_id IN NUMBER := NULL,
1520 p_xfr_subinv IN VARCHAR2 := NULL,
1521 p_xfr_locator_id IN NUMBER := NULL,
1522 p_trx_qty IN NUMBER := NULL,
1523 p_trx_uom IN VARCHAR2 := NULL,
1524 p_rev IN VARCHAR2 := NULL,
1525 x_request_msg OUT NOCOPY VARCHAR2,
1526 x_return_status OUT NOCOPY VARCHAR2,
1527 x_msg_count OUT NOCOPY NUMBER,
1528 x_msg_data OUT NOCOPY VARCHAR2,
1529 p_request_id IN OUT NOCOPY NUMBER) IS
1530 cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
1531 cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
1532
1533 l_seldev number;
1534 l_cur_dev number;
1535 l_lot_ser_ok varchar2(1);
1536 l_deviotype number;
1537 l_req_stat varchar2(255);
1538 l_req_stat_msg varchar2(255);
1539 l_dev_stat varchar2(255);
1540 l_dev_req_type number;
1541 l_retval number;
1542 l_sort NUMBER;
1543 l_msg varchar2(30);
1544 l_autoenabled varchar2(2);
1545 l_request_id number;
1546 l_xml_stat VARCHAR2(1);
1547 l_status_msg VARCHAR2(240) := '';
1548 l_parent_request_id NUMBER;
1549 l_notification_flag VARCHAR2(1);
1550 l_device_records_exists VARCHAR2(1);
1551 l_setup_row_cnt NUMBER := 0 ;
1552 l_wcs_enabled VARCHAR2(1) := 'N'; --MHE
1553
1554 --Added for bug#9233592 start
1555 l_xfr_org_id NUMBER := NULL;
1556 l_xfr_subinv VARCHAR2(10) := '';
1557 l_xfr_locator_id NUMBER := NULL;
1558 --Added for bug#9233592 end
1559
1560 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1561 BEGIN
1562 -- FOR PICK RELEASE AND REPLENISHMENT TASK ALLOCATION PARAMETER
1563 --'wms_call_device_request' SHOULD HAVE BEEN
1564 -- SET UP THROUGH CALL FROM PICK RELEASE AND MO PICK SLIP REPORT
1565 -- allocation CODE RESPECTIVELY.
1566 -- IF IT IS NULL THEN IT MEANS NO SET UP FOR THIS BUS EVENT IS DONE
1567 IF p_bus_event IN (wms_be_pick_release,wms_be_mo_task_alloc,WMS_BE_WIP_PICK_RELEASE) AND wms_call_device_request IS NULL THEN --Added WMS_BE_WIP_PICK_RELEASE for Bug 13702075
1568 RETURN;
1569 END IF;
1570
1571
1572 --If there is no record in the setup table for the concerned business
1573 --event then, return from here itself with success;
1574
1575 BEGIN
1576 SELECT 1 INTO l_setup_row_cnt FROM DUAL WHERE exists
1577 (SELECT 1
1578 FROM wms_bus_event_devices
1579 WHERE business_event_id = p_bus_event);
1580
1581 EXCEPTION
1582 WHEN NO_DATA_FOUND THEN
1583 l_setup_row_cnt:=0;
1584 END;
1585
1586 IF (l_setup_row_cnt = 0 AND p_bus_event < 50) THEN
1587 x_return_status := FND_API.G_RET_STS_SUCCESS;
1588 RETURN;
1589 END IF;
1590
1591 SAVEPOINT WMS_DEVICE_REQUESTS;
1592 -- Initialize message list if p_init_msg_list is set to TRUE.
1593 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1594 FND_MSG_PUB.initialize;
1595 END IF;
1596 -- Initialize API return status to success
1597 x_return_status := FND_API.G_RET_STS_SUCCESS;
1598
1599 IF (l_debug = 1) THEN
1600 trace('******* Device Request *********');
1601 END IF;
1602
1603 IF (p_bus_event IS NULL)THEN
1604 x_return_status := 'E';
1605 IF (l_debug = 1) THEN
1606 trace('Invlid Business Event');
1607 END IF;
1608 FND_MESSAGE.SET_NAME('WMS', 'WMS_BUSEVENT_INVALID');
1609 FND_MSG_PUB.ADD;
1610 RAISE FND_API.G_EXC_ERROR;
1611 END IF;
1612
1613 -- Verify parameters passed . Either TaskId should be not NULL or
1614 -- Org and Sub should be Not NULL
1615 IF (l_debug = 1) THEN
1616 trace('busev='||p_bus_event||',callctx='||p_call_ctx||',task='||p_task_trx_id||',org='||p_org_id||',sub='||p_subinv||',loc='||p_locator_id||', p_request_id='||p_request_id|| ' ,p_item_id= '||p_item_id);
1617 END IF;
1618 IF (p_bus_event NOT IN (wms_be_pick_release, wms_be_mo_task_alloc,WMS_BE_WIP_PICK_RELEASE)--Added WMS_BE_WIP_PICK_RELEASE for Bug 13702075
1619 AND p_task_trx_id IS NULL AND
1620 (p_org_id IS NULL OR (p_subinv is NULL AND p_xfr_subinv IS NULL))
1621 ) THEN
1622
1623 x_return_status := 'E';
1624 IF (l_debug = 1) THEN
1625 trace('Either Task_id is null or orgid is NULL OR sub IS NULL');
1626 END IF;
1627 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
1628 FND_MSG_PUB.ADD;
1629 RAISE FND_API.G_EXC_ERROR;
1630 END IF;
1631
1632 -- Validate p_request_id when the busniess event is Task Complete
1633 IF(p_bus_event in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel)) THEN
1634 IF p_request_id IS NULL THEN
1635 IF (l_debug = 1) THEN
1636 trace('Error: p_request_id is null for Task Complete/skip/cancel bus event');
1637 END IF;
1638 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
1639 FND_MSG_PUB.ADD;
1640 RAISE FND_API.G_EXC_ERROR;
1641 ELSE
1642 BEGIN
1643 SELECT request_id INTO l_parent_request_id
1644 FROM wms_device_requests_hist
1645 WHERE request_id = p_request_id
1646 AND task_summary = 'Y';
1647 EXCEPTION
1648 WHEN no_data_found THEN
1649 IF (l_debug = 1) THEN
1650 trace('Error: p_request_id is not valid for Task Complete/skip/cancel');
1651 END IF;
1652 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
1653 FND_MSG_PUB.ADD;
1654 RAISE FND_API.G_EXC_ERROR;
1655 END;
1656 END IF;
1657 IF (l_debug = 1) THEN
1658 trace('Passed validation for Task Complete/skip/cancel, l_parent_request_id='||l_parent_request_id);
1659 END IF;
1660 END IF;
1661
1662
1663 -- Set the l_autoenabled based on the calling context
1664 if ( p_call_ctx = DEV_REQ_AUTO) then
1665 l_autoenabled := 'Y';
1666 else
1667 l_autoenabled := 'N';
1668 end if;
1669
1670 -- Retrieve Business Event details
1671 --for Pick Release and Replenish Task Allocation bus event it is done in cartonization code
1672 IF p_bus_event IN (wms_be_pick_release, wms_be_mo_task_alloc,WMS_BE_WIP_PICK_RELEASE) THEN --Added WMS_BE_WIP_PICK_RELEASE for Bug 13702075
1673 l_request_id := wms_pkRel_dev_req_id;
1674 ELSIF p_bus_event <> wms_be_ship_confirm AND p_bus_event NOT IN
1675 (wms_be_pick_release, wms_be_mo_task_alloc,WMS_BE_WIP_PICK_RELEASE) THEN --Added WMS_BE_WIP_PICK_RELEASE for Bug 13702075
1676
1677 IF (l_debug = 1) THEN
1678 trace('bus_event not ship_confirm, retrieve_Bus_Event_Details',9);
1679 END IF;
1680 -- IF ( p_org_id IS NOT NULL /* AND p_item_id IS NOT NULL */ AND --Commented against bug : 5742996
1681 -- p_subinv IS NOT NULL AND p_locator_id IS NOT NULL) THEN
1682
1683 IF (l_debug = 1) THEN
1684 trace('busev='||p_bus_event||',callctx='||p_call_ctx||',task='||p_task_trx_id||',org='||p_org_id||',sub='||p_subinv||',loc='||p_locator_id||', p_request_id='||p_request_id|| ' ,p_item_id= '||p_item_id);
1685 trace('busev='||p_bus_event||',callctx='||p_call_ctx||',task='||p_task_trx_id||',xfr_org='||p_xfr_org_id||',xfr_sub='||p_xfr_subinv||',xfr_loc='||p_xfr_locator_id||', p_request_id='||p_request_id|| ' ,p_item_id= '||p_item_id);
1686 END IF;
1687
1688
1689 -- Bug :5742996 START
1690 IF (p_org_id IS NOT NULL AND/* AND p_item_id IS NOT NULL */
1691 p_subinv IS NOT NULL AND
1692 p_locator_id IS NOT NULL)
1693 OR (p_bus_event in (WMS_BE_PUTAWAY_DROP, WMS_BE_PICK_DROP)
1694 AND p_xfr_org_id IS NOT NULL
1695 AND p_xfr_subinv IS NOT NULL
1696 AND p_xfr_locator_id IS NOT NULL)
1697 THEN
1698 -- Bug :5742996 END
1699 IF (l_debug = 1) THEN
1700 trace('CALL TO DEVICE_REQUEST BY ALL PARAMETERS WITH TEMP_ID also, retrieve_Bus_Event_Details',9);
1701 END IF;
1702 --Added for bug#9233592 start
1703 IF( p_bus_event in (wms_be_task_complete)
1704 AND p_xfr_org_id IS NULL
1705 AND p_xfr_subinv IS NULL
1706 AND p_xfr_locator_id IS NULL )
1707 THEN
1708 BEGIN
1709 SELECT TRANSFER_ORG_ID,TRANSFER_SUB_CODE,TRANSFER_LOC_ID
1710 INTO l_xfr_org_id,l_xfr_subinv,l_xfr_locator_id
1711 FROM
1712 wms_device_requests_hist
1713 WHERE REQUEST_ID=p_request_id;
1714 EXCEPTION
1715 WHEN no_data_found THEN
1716 IF (l_debug = 1) THEN
1717 trace('Could not retrieve p_xfr_org_id,p_xfr_subinv,p_xfr_locator_id for Task Complete business flow using p_request_id');
1718 END IF;
1719 END;
1720 trace('p_xfr_org_id,p_xfr_subinv,p_xfr_locator_id values for Task Complete business flow '||p_xfr_org_id||','||p_xfr_subinv||','||p_xfr_subinv||'are retrieved using p_request_id'||p_request_id);
1721
1722 retrieve_Bus_Event_Details(p_bus_event,
1723 p_task_trx_id,
1724 p_org_id,
1725 p_item_id,
1726 p_subinv,
1727 p_locator_id,
1728 p_lpn_id,
1729 p_xfer_lpn_id, --Added for Bug#8778050
1730 l_xfr_org_id,
1731 l_xfr_subinv,
1732 l_xfr_locator_id,
1733 p_trx_qty,
1734 p_trx_uom,
1735 p_rev,
1736 l_request_id,
1737 x_return_status);
1738
1739 ELSE
1740
1741 --Here Temp_id is needed just to identify the record in wdt to stamp device_id IN it
1742 --Bug #2458131
1743 retrieve_Bus_Event_Details(p_bus_event,
1744 p_task_trx_id,
1745 p_org_id,
1746 p_item_id,
1747 p_subinv,
1748 p_locator_id,
1749 p_lpn_id,
1750 p_xfer_lpn_id, --Added for Bug#8778050
1751 p_xfr_org_id,
1752 p_xfr_subinv,
1753 p_xfr_locator_id,
1754 p_trx_qty,
1755 p_trx_uom,
1756 p_rev,
1757 l_request_id,
1758 x_return_status);
1759 END IF;
1760
1761 --Added for bug#9233592 end
1762
1763 IF (x_return_status = 'S')THEN
1764
1765 -- Updating wms_dispatched_tasks with the device_id
1766 UPDATE wms_dispatched_tasks
1767 SET DEVICE_REQUEST_ID = l_request_id
1768 WHERE TRANSACTION_TEMP_ID = p_task_trx_id;
1769
1770 ELSIF (x_return_status = 'E')THEN
1771 IF (l_debug = 1) THEN
1772 trace ('Could not retrieve Event Details');
1773 END IF;
1774 FND_MESSAGE.SET_NAME('WMS', 'WMS_PICKREL_ERR');
1775 FND_MSG_PUB.ADD;
1776 RAISE FND_API.G_EXC_ERROR;
1777 END IF;
1778
1779 ELSIF (p_task_trx_id IS NOT NULL) THEN
1780 IF (l_debug = 1) THEN
1781 trace(' CALL TO DEVICE_REQUEST BY TEMP_ID ONLY, retrieve_Bus_Event_Details',9);
1782 END IF;
1783 retrieve_Bus_Event_Details(p_task_trx_id,
1784 p_bus_event,
1785 l_request_id,
1786 x_return_status);
1787
1788
1789 IF (x_return_status = 'S')THEN
1790 -- Updating wms_dispatched_tasks with the device_id
1791 UPDATE wms_dispatched_tasks
1792 SET DEVICE_REQUEST_ID = l_request_id
1793 WHERE transaction_temp_id = p_task_trx_id;
1794
1795 ELSIF (x_return_status = 'E')THEN
1796 IF (l_debug = 1) THEN
1797 trace('Could not retrieve Event Details');
1798 END IF;
1799 FND_MESSAGE.SET_NAME('WMS', 'WMS_BUSEVENT_ERR');
1800 FND_MSG_PUB.ADD;
1801 RAISE FND_API.G_EXC_ERROR;
1802 END IF;
1803 END IF;
1804 ELSIF (p_task_trx_id IS NOT NULL AND p_bus_event = wms_be_ship_confirm) THEN
1805 IF (l_debug = 1) THEN
1806 trace(' Task ID is not null and event is ship confirm, retrieve_Ship_Confirm_Details');
1807 END IF;
1808
1809 retrieve_Ship_Confirm_Details(p_task_trx_id, p_bus_event,
1810 l_request_id,x_return_status);
1811 IF (x_return_status = 'E')THEN
1812 IF (l_debug = 1) THEN
1813 trace('Could not retrieve ship confirm details');
1814 END IF;
1815 FND_MESSAGE.SET_NAME('WMS', 'WMS_SHIPCONFIRM_ERR');
1816 FND_MSG_PUB.ADD;
1817 RAISE FND_API.G_EXC_ERROR;
1818 END IF;
1819 end if;
1820
1821 -- Loop on WMS_DEVICE_REQUESTS per device
1822 IF p_bus_event NOT IN (wms_be_pick_release, wms_be_mo_task_alloc,WMS_BE_WIP_PICK_RELEASE) --Added WMS_BE_WIP_PICK_RELEASE for Bug 13702075
1823 THEN --for pick release and replenish Task Allocation this IS done IN cartonization code
1824
1825 IF (l_debug = 1) THEN
1826 trace('Select device for each request');
1827 END IF;
1828 for l_wdr in c_wdr LOOP
1829 l_seldev := select_Device(l_wdr, l_autoenabled, l_parent_request_id);
1830 l_wdr.device_id := l_seldev;
1831 -- If No device has been selected for this record, then do not
1832 -- consider this record for further processing
1833 if (l_seldev <> 0) then
1834 select Nvl(lot_serial_capable,'N')
1835 into l_lot_ser_ok
1836 from WMS_DEVICES_B
1837 where device_id = l_seldev;
1838 -- If Details enabled for device, retrieve the Lot/Serialdetails
1839 IF (l_lot_ser_ok = 'Y') THEN
1840 retrieve_Lot_Serial_Details(l_wdr, x_return_status);
1841 IF (x_return_status = 'E')THEN
1842 IF (l_debug = 1) THEN
1843 trace('Could not retrieve lot and or serial details');
1844 END IF;
1845 FND_MESSAGE.SET_NAME('WMS', 'WMS_LOT_SER_DETAIL_ERR');
1846 FND_MSG_PUB.ADD;
1847 RAISE FND_API.G_EXC_ERROR;
1848 END IF;
1849 l_lot_ser_ok:= NULL;
1850 END IF;
1851 end if;
1852
1853 end loop;
1854 END IF;
1855 -- Loop on WMS_DEVICE_REQUESTS per Device
1856 IF (l_debug = 1) THEN
1857 trace('### Submit request per device group ');
1858 END IF;
1859 for l_cur_dev in c_wdr_devgrp loop
1860
1861 -- For Task Complete, if the device is enabled to notify task complete
1862 -- update the request records' RELATION_ID with the parent_request_id
1863 IF(p_bus_event in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel)) THEN
1864 select nvl(notification_flag, 'N')
1865 into l_notification_flag
1866 from WMS_DEVICES_B
1867 where device_id = l_cur_dev.device_id;
1868
1869 IF (l_debug = 1) THEN
1870 trace('Event is task complete/skip/cancel, check whether need to update relation_id, notification_flag='||l_notification_flag);
1871 END IF;
1872 IF l_notification_flag = 'Y' THEN
1873 IF (l_debug = 1) THEN
1874 trace('update request and request_hist for device '||l_cur_dev.device_id|| ' and parent_request_id='||l_parent_request_id);
1875 END IF;
1876 BEGIN
1877 update wms_device_requests
1878 set relation_id = l_parent_request_id
1879 where device_id = l_cur_dev.device_id;
1880
1881 update wms_device_requests_hist
1882 set relation_id = l_parent_request_id
1883 where request_id = l_parent_request_id;
1884 EXCEPTION
1885 WHEN others THEN
1886 IF (l_debug = 1) THEN
1887 trace('Error in updating relation_id on the request table for task complete/skip/cancel, dev_id='||l_cur_dev.device_id);
1888 trace('SQL error :'||substr(sqlerrm, 1, 240));
1889 END IF;
1890 RAISE FND_API.G_EXC_ERROR;
1891 END;
1892 END IF;
1893 END IF;
1894
1895 select d.OUTPUT_METHOD_ID, p.WCS_ENABLED
1896 into l_deviotype, l_wcs_enabled
1897 from WMS_DEVICES_B d, mtl_parameters p
1898 where d.device_id = l_cur_dev.device_id
1899 and p.organization_id = d.organization_id;
1900
1901
1902 IF (l_debug = 1) THEN
1903 trace('!----Device_ID: '||l_cur_dev.device_id||', got iotype ' || l_deviotype);
1904 trace('MHE: p_org_id = '||p_org_id||', l_wcs_enabled = '||l_wcs_enabled);
1905 END IF;
1906 -- Generate XML,CSV if configured for it
1907 IF (( l_deviotype = WMS_DEV_IO_XML) OR (l_deviotype = WMS_DEV_IO_CSV)) then
1908 IF (l_debug = 1) THEN
1909 trace('going to call generate_xml_csv');
1910 END IF;
1911 l_retval := generate_xml_csv(l_cur_dev.device_id,l_deviotype);
1912 IF l_retval <> 0 THEN
1913 l_xml_stat := 'E';
1914 ELSE
1915 l_xml_stat := 'S';
1916 END IF;
1917 IF (l_debug = 1) THEN
1918 trace(' Done with generate xml , retval '||l_retval ||' status_code: '||l_xml_stat);
1919 END IF;
1920 l_status_msg := get_msg_stack;--only last message
1921 --IN the stack
1922
1923 IF l_xml_stat <> 'S' THEN
1924 UPDATE wms_device_requests
1925 SET status_code = l_xml_stat,
1926 status_msg = l_status_msg
1927 WHERE device_id = l_cur_dev.device_id;
1928 ELSE
1929 UPDATE wms_device_requests
1930 SET status_code = 'S'
1931 WHERE device_id = l_cur_dev.device_id;
1932 END IF;
1933
1934 ELSIF (l_deviotype = WMS_DEV_IO_API) then
1935 IF (l_debug = 1) THEN
1936 trace(' Submit sync_device_request');
1937 END IF;
1938 IF nvl(l_wcs_enabled,'N') = 'N' THEN
1939 trace('MHE: Calling WMS_DEVICE_INTEGRATION_PUB.SYNC_DEVICE_REQUEST');
1940 WMS_DEVICE_INTEGRATION_PUB.SYNC_DEVICE_REQUEST(
1941 p_request_id => l_request_id,
1942 p_device_id => l_cur_dev.device_id,
1943 p_resubmit_flag => 'N',
1944 x_status_code => l_req_stat,
1945 x_device_status => l_dev_stat,
1946 x_status_msg => l_req_stat_msg );
1947 ELSE
1948 trace('MHE: Calling WMS_DEVICE_INTEGRATION_WCS.SYNC_DEVICE_REQUEST');
1949 WMS_DEVICE_INTEGRATION_WCS.SYNC_DEVICE_REQUEST(
1950 p_request_id => l_request_id,
1951 p_device_id => l_cur_dev.device_id,
1952 p_resubmit_flag => 'N',
1953 x_status_code => l_req_stat,
1954 x_device_status => l_dev_stat,
1955 x_status_msg => l_req_stat_msg );
1956 END IF;
1957
1958
1959 IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
1960 UPDATE wms_device_requests
1961 SET status_code = l_req_stat,
1962 status_msg = l_req_stat_msg
1963 WHERE device_id = l_cur_dev.device_id;
1964 ELSE
1965 UPDATE wms_device_requests
1966 SET status_code = 'S'
1967 WHERE device_id = l_cur_dev.device_id;
1968 END IF;
1969 end if;
1970 end loop;
1971
1972 -- Populate the History table
1973 IF (l_debug = 1) THEN
1974 trace('Populate Request History');
1975 END IF;
1976 populate_History(p_call_ctx,p_bus_event,l_device_records_exists); -- Modified for bug#8778050
1977
1978 IF l_device_records_exists = 'N' THEN
1979 -- No device exists, return null request id
1980 IF (l_debug = 1) THEN
1981 trace('setting _request_id to NULL');
1982 END IF;
1983 p_request_id := NULL;
1984 ELSE
1985 p_request_id := l_request_id;
1986 END IF;
1987
1988 -- Finally delete all rows from WMS_DEV_REQUEST so that each invocation of
1989 -- this API in this session starts with an empty table.
1990
1991 IF (l_debug = 1) THEN
1992 trace(' Delete request rows');
1993 END IF;
1994 delete from wms_device_requests;
1995
1996 EXCEPTION
1997 WHEN FND_API.G_EXC_ERROR THEN
1998 IF (l_debug = 1) THEN
1999 trace('Error: G_EXC_ERR : Delete request rows');
2000 END IF;
2001 delete from wms_device_requests;
2002 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2003 FND_MSG_PUB.ADD;
2004 ROLLBACK TO WMS_DEVICE_REQUESTS;
2005 x_return_status := FND_API.G_RET_STS_ERROR;
2006 FND_MSG_PUB.Count_And_Get
2007 ( p_count => x_msg_count,
2008 p_data => x_msg_data
2009 );
2010
2011 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2012 IF (l_debug = 1) THEN
2013 trace('Error: G_EXC_UNEXP : Delete request rows');
2014 END IF;
2015 delete from wms_device_requests;
2016 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2017 FND_MSG_PUB.ADD;
2018 ROLLBACK TO WMS_DEVICE_REQUESTS;
2019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2020 FND_MSG_PUB.Count_And_Get
2021 ( p_count => x_msg_count,
2022 p_data => x_msg_data
2023 );
2024
2025 WHEN OTHERS THEN
2026 IF (l_debug = 1) THEN
2027 trace('Error: '||substr(sqlerrm, 1, 100));
2028 END IF;
2029 delete from wms_device_requests;
2030 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2031 FND_MSG_PUB.ADD;
2032 ROLLBACK TO WMS_DEVICE_REQUESTS;
2033 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2034 FND_MSG_PUB.Count_And_Get
2035 ( p_count => x_msg_count,
2036 p_data => x_msg_data
2037 );
2038
2039 END;
2040
2041 PROCEDURE DEVICE_REQUEST(
2042 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2043 p_bus_event IN NUMBER,
2044 p_call_ctx IN VARCHAR2 ,
2045 p_task_trx_id IN NUMBER := NULL,
2046 p_org_id IN NUMBER := NULL,
2047 p_item_id IN NUMBER := NULL,
2048 p_subinv IN VARCHAR2 := NULL,
2049 p_locator_id IN NUMBER := NULL,
2050 p_lpn_id IN NUMBER := NULL,
2051 p_xfr_org_id IN NUMBER := NULL,
2052 p_xfr_subinv IN VARCHAR2 := NULL,
2053 p_xfr_locator_id IN NUMBER := NULL,
2054 p_trx_qty IN NUMBER := NULL,
2055 p_trx_uom IN VARCHAR2 := NULL,
2056 p_rev IN VARCHAR2 := NULL,
2057 x_request_msg OUT NOCOPY VARCHAR2,
2058 x_return_status OUT NOCOPY VARCHAR2,
2059 x_msg_count OUT NOCOPY NUMBER,
2060 x_msg_data OUT NOCOPY VARCHAR2)
2061 IS
2062 l_request_id VARCHAR2(40);
2063 l_xfer_lpn_id NUMBER := NULL; --Added for Bug#8778050
2064 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2065 BEGIN
2066 DEVICE_REQUEST(
2067 p_init_msg_list=>p_init_msg_list,
2068 p_bus_event=>p_bus_event,
2069 p_call_ctx =>p_call_ctx,
2070 p_task_trx_id=>p_task_trx_id,
2071 p_org_id=>p_org_id,
2072 p_item_id=>p_item_id,
2073 p_subinv=>p_subinv,
2074 p_locator_id=>p_locator_id,
2075 p_lpn_id=>p_lpn_id,
2076 p_xfer_lpn_id=>l_xfer_lpn_id, --Added for Bug#8778050
2077 p_xfr_org_id=>p_xfr_org_id,
2078 p_xfr_subinv=>p_xfr_subinv,
2079 p_xfr_locator_id=>p_xfr_locator_id,
2080 p_trx_qty=>p_trx_qty,
2081 p_trx_uom=>p_trx_uom,
2082 p_rev=>p_rev,
2083 x_request_msg=>x_request_msg,
2084 x_return_status=>x_return_status,
2085 x_msg_count=>x_msg_count,
2086 x_msg_data=>x_msg_data,
2087 p_request_id=>l_request_id);
2088
2089 END device_request;
2090
2091 --WMS-OPM
2092 /*
2093 This will be the overloaded device_request API which will be called
2094 from an OPM UI. The difference in this API is that, the caller will
2095 know the device_id to which the the request must be sent and the API
2096 will not have any logic to resolve the API from the table wms_bus_event_devices
2097
2098 Inserting into WDR is kept transparent to the OPM team who will call this
2099 through a wrapper API in a group package
2100
2101 The request traffic will get logged in the wms_device_requests_hist table
2102 in addition to being captured in wms_carousel_log
2103 */
2104 PROCEDURE DEVICE_REQUEST(
2105 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2106 p_bus_event IN NUMBER,
2107 p_call_ctx IN VARCHAR2 ,
2108 p_task_trx_id IN NUMBER := NULL,
2109 p_org_id IN NUMBER := NULL,
2110 p_item_id IN NUMBER := NULL,
2111 p_subinv IN VARCHAR2 := NULL,
2112 p_locator_id IN NUMBER := NULL,
2113 p_lpn_id IN NUMBER := NULL,
2114 p_xfr_org_id IN NUMBER := NULL,
2115 p_xfr_subinv IN VARCHAR2 := NULL,
2116 p_xfr_locator_id IN NUMBER := NULL,
2117 p_trx_qty IN NUMBER := NULL,
2118 p_trx_uom IN VARCHAR2 := NULL,
2119 p_rev IN VARCHAR2 := NULL,
2120 x_request_msg OUT NOCOPY VARCHAR2,
2121 x_return_status OUT NOCOPY VARCHAR2,
2122 x_msg_count OUT NOCOPY NUMBER,
2123 x_msg_data OUT NOCOPY VARCHAR2,
2124 p_request_id IN OUT NOCOPY NUMBER,
2125 p_device_id IN NUMBER) IS
2126 cursor c_wdr is select * from WMS_DEVICE_REQUESTS where task_summary = 'Y';
2127 cursor c_wdr_devgrp is select device_id from WMS_DEVICE_REQUESTS where device_id is not null group BY device_id;
2128
2129 l_seldev number;
2130 l_cur_dev number;
2131 l_lot_ser_ok varchar2(1);
2132 l_deviotype number;
2133 l_req_stat varchar2(255);
2134 l_req_stat_msg varchar2(255);
2135 l_dev_stat varchar2(255);
2136 l_dev_req_type number;
2137 l_retval number;
2138 l_sort NUMBER;
2139 l_msg varchar2(30);
2140 l_autoenabled varchar2(2);
2141 l_request_id number;
2142 l_xml_stat VARCHAR2(1);
2143 l_status_msg VARCHAR2(240) := '';
2144 l_parent_request_id NUMBER;
2145 l_notification_flag VARCHAR2(1);
2146 l_device_records_exists VARCHAR2(1);
2147 l_setup_row_cnt NUMBER := 0 ;
2148 l_wcs_enabled VARCHAR2(1) := 'N'; --MHE
2149
2150 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2151 BEGIN
2152 -- FOR PICK RELEASE AND REPLENISHMENT TASK ALLOCATION PARAMETER
2153 --'wms_call_device_request' SHOULD HAVE BEEN
2154 -- SET UP THROUGH CALL FROM PICK RELEASE AND MO PICK SLIP REPORT
2155 -- allocation CODE RESPECTIVELY.
2156 -- IF IT IS NULL THEN IT MEANS NO SET UP FOR THIS BUS EVENT IS DONE
2157 IF p_bus_event IN (wms_be_pick_release,wms_be_mo_task_alloc) AND wms_call_device_request IS NULL THEN
2158 RETURN;
2159 END IF;
2160
2161
2162 --If there is no record in the setup table for the concerned business
2163 --event then, return from here itself with success;
2164
2165 BEGIN
2166 SELECT 1 INTO l_setup_row_cnt FROM DUAL WHERE exists
2167 (SELECT 1
2168 FROM wms_bus_event_devices
2169 WHERE business_event_id = p_bus_event);
2170
2171 EXCEPTION
2172 WHEN NO_DATA_FOUND THEN
2173 l_setup_row_cnt:=0;
2174 END;
2175
2176 IF (l_setup_row_cnt = 0 AND p_bus_event < 50) THEN
2177 x_return_status := FND_API.G_RET_STS_SUCCESS;
2178 RETURN;
2179 END IF;
2180
2181 SAVEPOINT WMS_DEVICE_REQUESTS;
2182 -- Initialize message list if p_init_msg_list is set to TRUE.
2183 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2184 FND_MSG_PUB.initialize;
2185 END IF;
2186 -- Initialize API return status to success
2187 x_return_status := FND_API.G_RET_STS_SUCCESS;
2188
2189 IF (l_debug = 1) THEN
2190 trace('******* Device Request *********');
2191 END IF;
2192
2193 IF (p_bus_event IS NULL)THEN
2194 x_return_status := 'E';
2195 IF (l_debug = 1) THEN
2196 trace('Invlid Business Event');
2197 END IF;
2198 FND_MESSAGE.SET_NAME('WMS', 'WMS_BUSEVENT_INVALID');
2199 FND_MSG_PUB.ADD;
2200 RAISE FND_API.G_EXC_ERROR;
2201 END IF;
2202
2203 -- Verify parameters passed . Either TaskId should be not NULL or
2204 -- Org and Sub should be Not NULL
2205 IF (l_debug = 1) THEN
2206 trace('busev='||p_bus_event||',callctx='||p_call_ctx||',task='||p_task_trx_id||',org='||p_org_id||',sub='||p_subinv||',loc='||p_locator_id||', p_request_id='||p_request_id|| ' ,p_item_id= '||p_item_id);
2207 END IF;
2208 IF (p_bus_event NOT IN (wms_be_pick_release, wms_be_mo_task_alloc)
2209 AND p_task_trx_id IS NULL AND
2210 (p_org_id IS NULL OR (p_subinv is NULL AND p_xfr_subinv IS NULL))
2211 ) THEN
2212
2213 x_return_status := 'E';
2214 IF (l_debug = 1) THEN
2215 trace('Either Task_id is null or orgid is NULL OR sub IS NULL');
2216 END IF;
2217 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
2218 FND_MSG_PUB.ADD;
2219 RAISE FND_API.G_EXC_ERROR;
2220 END IF;
2221
2222 -- Validate p_request_id when the busniess event is Task Complete
2223 IF(p_bus_event in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel)) THEN
2224 IF p_request_id IS NULL THEN
2225 IF (l_debug = 1) THEN
2226 trace('Error: p_request_id is null for Task Complete/skip/cancel bus event');
2227 END IF;
2228 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
2229 FND_MSG_PUB.ADD;
2230 RAISE FND_API.G_EXC_ERROR;
2231 ELSE
2232 BEGIN
2233 SELECT request_id INTO l_parent_request_id
2234 FROM wms_device_requests_hist
2235 WHERE request_id = p_request_id
2236 AND task_summary = 'Y';
2237 EXCEPTION
2238 WHEN no_data_found THEN
2239 IF (l_debug = 1) THEN
2240 trace('Error: p_request_id is not valid for Task Complete/skip/cancel');
2241 END IF;
2242 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVOKE_ERR');
2243 FND_MSG_PUB.ADD;
2244 RAISE FND_API.G_EXC_ERROR;
2245 END;
2246 END IF;
2247 IF (l_debug = 1) THEN
2248 trace('Passed validation for Task Complete/skip/cancel, l_parent_request_id='||l_parent_request_id);
2249 END IF;
2250 END IF;
2251
2252
2253 -- Set the l_autoenabled based on the calling context
2254 if ( p_call_ctx = DEV_REQ_AUTO) then
2255 l_autoenabled := 'Y';
2256 else
2257 l_autoenabled := 'N';
2258 end if;
2259
2260 -- Retrieve Business Event details
2261 --for Pick Release and Replenish Task Allocation bus event it is done in cartonization code
2262 IF p_bus_event IN (wms_be_pick_release, wms_be_mo_task_alloc) THEN
2263 l_request_id := wms_pkRel_dev_req_id;
2264 ELSIF p_bus_event <> wms_be_ship_confirm AND p_bus_event NOT IN
2265 (wms_be_pick_release, wms_be_mo_task_alloc) THEN
2266
2267 IF (l_debug = 1) THEN
2268 trace('bus_event not ship_confirm, retrieve_Bus_Event_Details',9);
2269 END IF;
2270 IF ( p_org_id IS NOT NULL /* AND p_item_id IS NOT NULL */ AND
2271 p_subinv IS NOT NULL AND p_locator_id IS NOT NULL) THEN
2272 IF (l_debug = 1) THEN
2273 trace('CALL TO DEVICE_REQUEST BY ALL PARAMETERS WITH TEMP_ID also, retrieve_Bus_Event_Details',9);
2274 END IF;
2275 --Here Temp_id is needed just to identify the record in wdt to stamp device_id IN it
2276 --Bug #2458131
2277 retrieve_Bus_Event_Details(p_bus_event,
2278 p_task_trx_id,
2279 p_org_id,
2280 p_item_id,
2281 p_subinv,
2282 p_locator_id,
2283 p_lpn_id,
2284 p_xfr_org_id,
2285 p_xfr_subinv,
2286 p_xfr_locator_id,
2287 p_trx_qty,
2288 p_trx_uom,
2289 p_rev,
2290 p_device_id,
2291 l_request_id,
2292 x_return_status);
2293
2294 IF (x_return_status = 'S')THEN
2295
2296 -- Updating wms_dispatched_tasks with the device_id
2297 UPDATE wms_dispatched_tasks
2298 SET DEVICE_REQUEST_ID = l_request_id
2299 WHERE TRANSACTION_TEMP_ID = p_task_trx_id;
2300
2301 ELSIF (x_return_status = 'E')THEN
2302 IF (l_debug = 1) THEN
2303 trace ('Could not retrieve Event Details');
2304 END IF;
2305 FND_MESSAGE.SET_NAME('WMS', 'WMS_PICKREL_ERR');
2306 FND_MSG_PUB.ADD;
2307 RAISE FND_API.G_EXC_ERROR;
2308 END IF;
2309
2310 ELSIF (p_task_trx_id IS NOT NULL) THEN
2311 IF (l_debug = 1) THEN
2312 trace(' CALL TO DEVICE_REQUEST BY TEMP_ID ONLY, retrieve_Bus_Event_Details',9);
2313 END IF;
2314 retrieve_Bus_Event_Details(p_task_trx_id,
2315 p_bus_event,
2316 l_request_id,
2317 x_return_status);
2318
2319
2320 IF (x_return_status = 'S')THEN
2321 -- Updating wms_dispatched_tasks with the device_id
2322 UPDATE wms_dispatched_tasks
2323 SET DEVICE_REQUEST_ID = l_request_id
2324 WHERE transaction_temp_id = p_task_trx_id;
2325
2326 ELSIF (x_return_status = 'E')THEN
2327 IF (l_debug = 1) THEN
2328 trace('Could not retrieve Event Details');
2329 END IF;
2330 FND_MESSAGE.SET_NAME('WMS', 'WMS_BUSEVENT_ERR');
2331 FND_MSG_PUB.ADD;
2332 RAISE FND_API.G_EXC_ERROR;
2333 END IF;
2334 END IF;
2335 ELSIF (p_task_trx_id IS NOT NULL AND p_bus_event = wms_be_ship_confirm) THEN
2336 IF (l_debug = 1) THEN
2337 trace(' Task ID is not null and event is ship confirm, retrieve_Ship_Confirm_Details');
2338 END IF;
2339
2340 retrieve_Ship_Confirm_Details(p_task_trx_id, p_bus_event,
2341 l_request_id,x_return_status);
2342 IF (x_return_status = 'E')THEN
2343 IF (l_debug = 1) THEN
2344 trace('Could not retrieve ship confirm details');
2345 END IF;
2346 FND_MESSAGE.SET_NAME('WMS', 'WMS_SHIPCONFIRM_ERR');
2347 FND_MSG_PUB.ADD;
2348 RAISE FND_API.G_EXC_ERROR;
2349 END IF;
2350 end if;
2351
2352 -- Loop on WMS_DEVICE_REQUESTS per device
2353 IF p_bus_event NOT IN (wms_be_pick_release, wms_be_mo_task_alloc)
2354 THEN --for pick release and replenish Task Allocation this IS done IN cartonization code
2355
2356 IF (l_debug = 1) THEN
2357 trace('Select device for each request');
2358 END IF;
2359 for l_wdr in c_wdr LOOP
2360 --WMS-OPM
2361 --l_seldev := select_Device(l_wdr, l_autoenabled, l_parent_request_id);
2362 IF (l_debug = 1) THEN
2363 trace('Not calling select_Device. Directly using the passed Device Id:'||p_device_id);
2364 END IF;
2365 l_seldev := p_device_id;
2366
2367 l_wdr.device_id := l_seldev;
2368 -- If No device has been selected for this record, then do not
2369 -- consider this record for further processing
2370 if (l_seldev <> 0) then
2371 select Nvl(lot_serial_capable,'N')
2372 into l_lot_ser_ok
2373 from WMS_DEVICES_B
2374 where device_id = l_seldev;
2375 -- If Details enabled for device, retrieve the Lot/Serialdetails
2376 IF (l_lot_ser_ok = 'Y') THEN
2377 retrieve_Lot_Serial_Details(l_wdr, x_return_status);
2378 IF (x_return_status = 'E')THEN
2379 IF (l_debug = 1) THEN
2380 trace('Could not retrieve lot and or serial details');
2381 END IF;
2382 FND_MESSAGE.SET_NAME('WMS', 'WMS_LOT_SER_DETAIL_ERR');
2383 FND_MSG_PUB.ADD;
2384 RAISE FND_API.G_EXC_ERROR;
2385 END IF;
2386 l_lot_ser_ok:= NULL;
2387 END IF;
2388 end if;
2389
2390 end loop;
2391 END IF;
2392 -- Loop on WMS_DEVICE_REQUESTS per Device
2393 IF (l_debug = 1) THEN
2394 trace('### Submit request per device group ');
2395 END IF;
2396 for l_cur_dev in c_wdr_devgrp loop
2397
2398 -- For Task Complete, if the device is enabled to notify task complete
2399 -- update the request records' RELATION_ID with the parent_request_id
2400 IF(p_bus_event in (wms_be_task_complete, wms_be_task_skip, wms_be_task_cancel)) THEN
2401 select nvl(notification_flag, 'N')
2402 into l_notification_flag
2403 from WMS_DEVICES_B
2404 where device_id = l_cur_dev.device_id;
2405
2406 IF (l_debug = 1) THEN
2407 trace('Event is task complete/skip/cancel, check whether need to update relation_id, notification_flag='||l_notification_flag);
2408 END IF;
2409 IF l_notification_flag = 'Y' THEN
2410 IF (l_debug = 1) THEN
2411 trace('update request and request_hist for device '||l_cur_dev.device_id|| ' and parent_request_id='||l_parent_request_id);
2412 END IF;
2413 BEGIN
2414 update wms_device_requests
2415 set relation_id = l_parent_request_id
2416 where device_id = l_cur_dev.device_id;
2417
2418 update wms_device_requests_hist
2419 set relation_id = l_parent_request_id
2420 where request_id = l_parent_request_id;
2421 EXCEPTION
2422 WHEN others THEN
2423 IF (l_debug = 1) THEN
2424 trace('Error in updating relation_id on the request table for task complete/skip/cancel, dev_id='||l_cur_dev.device_id);
2425 trace('SQL error :'||substr(sqlerrm, 1, 240));
2426 END IF;
2427 RAISE FND_API.G_EXC_ERROR;
2428 END;
2429 END IF;
2430 END IF;
2431
2432 select d.OUTPUT_METHOD_ID, p.WCS_ENABLED
2433 into l_deviotype, l_wcs_enabled
2434 from WMS_DEVICES_B d, mtl_parameters p
2435 where d.device_id = l_cur_dev.device_id
2436 and p.organization_id = d.organization_id;
2437
2438
2439 IF (l_debug = 1) THEN
2440 trace('!----Device_ID: '||l_cur_dev.device_id||', got iotype ' || l_deviotype);
2441 trace('MHE: p_org_id = '||p_org_id||', l_wcs_enabled = '||l_wcs_enabled);
2442 END IF;
2443 -- Generate XML,CSV if configured for it
2444 IF (( l_deviotype = WMS_DEV_IO_XML) OR (l_deviotype = WMS_DEV_IO_CSV)) then
2445 IF (l_debug = 1) THEN
2446 trace('going to call generate_xml_csv');
2447 END IF;
2448 l_retval := generate_xml_csv(l_cur_dev.device_id,l_deviotype);
2449 IF l_retval <> 0 THEN
2450 l_xml_stat := 'E';
2451 ELSE
2452 l_xml_stat := 'S';
2453 END IF;
2454 IF (l_debug = 1) THEN
2455 trace(' Done with generate xml , retval '||l_retval ||' status_code: '||l_xml_stat);
2456 END IF;
2457 l_status_msg := get_msg_stack;--only last message
2458 --IN the stack
2459
2460 IF l_xml_stat <> 'S' THEN
2461 UPDATE wms_device_requests
2462 SET status_code = l_xml_stat,
2463 status_msg = l_status_msg
2464 WHERE device_id = l_cur_dev.device_id;
2465 ELSE
2466 UPDATE wms_device_requests
2467 SET status_code = 'S'
2468 WHERE device_id = l_cur_dev.device_id;
2469 END IF;
2470
2471 ELSIF (l_deviotype = WMS_DEV_IO_API) then
2472 IF (l_debug = 1) THEN
2473 trace(' Submit sync_device_request');
2474 END IF;
2475 IF nvl(l_wcs_enabled,'N') = 'N' THEN
2476 trace('MHE: Calling WMS_DEVICE_INTEGRATION_PUB.SYNC_DEVICE_REQUEST');
2477 WMS_DEVICE_INTEGRATION_PUB.SYNC_DEVICE_REQUEST(
2478 p_request_id => l_request_id,
2479 p_device_id => l_cur_dev.device_id,
2480 p_resubmit_flag => 'N',
2481 x_status_code => l_req_stat,
2482 x_device_status => l_dev_stat,
2483 x_status_msg => l_req_stat_msg );
2484 ELSE
2485 trace('MHE: Calling WMS_DEVICE_INTEGRATION_WCS.SYNC_DEVICE_REQUEST');
2486 WMS_DEVICE_INTEGRATION_WCS.SYNC_DEVICE_REQUEST(
2487 p_request_id => l_request_id,
2488 p_device_id => l_cur_dev.device_id,
2489 p_resubmit_flag => 'N',
2490 x_status_code => l_req_stat,
2491 x_device_status => l_dev_stat,
2492 x_status_msg => l_req_stat_msg );
2493 END IF;
2494
2495
2496 IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2497 UPDATE wms_device_requests
2498 SET status_code = l_req_stat,
2499 status_msg = l_req_stat_msg
2500 WHERE device_id = l_cur_dev.device_id;
2501 ELSE
2502 UPDATE wms_device_requests
2503 SET status_code = 'S'
2504 WHERE device_id = l_cur_dev.device_id;
2505 END IF;
2506 end if;
2507 end loop;
2508
2509 -- Populate the History table
2510 IF (l_debug = 1) THEN
2511 trace('Populate Request History');
2512 END IF;
2513 populate_History(p_call_ctx,p_bus_event,l_device_records_exists); -- Modified for bug#8778050
2514
2515 --WMS-OPM
2516 p_request_id := l_request_id;
2517
2518 -- Finally delete all rows from WMS_DEV_REQUEST so that each invocation of
2519 -- this API in this session starts with an empty table.
2520
2521 IF (l_debug = 1) THEN
2522 trace(' Delete request rows');
2523 END IF;
2524 delete from wms_device_requests;
2525
2526 EXCEPTION
2527 WHEN FND_API.G_EXC_ERROR THEN
2528 IF (l_debug = 1) THEN
2529 trace('Error: G_EXC_ERR : Delete request rows');
2530 END IF;
2531 delete from wms_device_requests;
2532 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2533 FND_MSG_PUB.ADD;
2534 ROLLBACK TO WMS_DEVICE_REQUESTS;
2535 x_return_status := FND_API.G_RET_STS_ERROR;
2536 FND_MSG_PUB.Count_And_Get
2537 ( p_count => x_msg_count,
2538 p_data => x_msg_data
2539 );
2540
2541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2542 IF (l_debug = 1) THEN
2543 trace('Error: G_EXC_UNEXP : Delete request rows');
2544 END IF;
2545 delete from wms_device_requests;
2546 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2547 FND_MSG_PUB.ADD;
2548 ROLLBACK TO WMS_DEVICE_REQUESTS;
2549 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2550 FND_MSG_PUB.Count_And_Get
2551 ( p_count => x_msg_count,
2552 p_data => x_msg_data
2553 );
2554
2555 WHEN OTHERS THEN
2556 IF (l_debug = 1) THEN
2557 trace('Error: '||substr(sqlerrm, 1, 100));
2558 END IF;
2559 delete from wms_device_requests;
2560 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_REQ_FAIL');
2561 FND_MSG_PUB.ADD;
2562 ROLLBACK TO WMS_DEVICE_REQUESTS;
2563 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2564 FND_MSG_PUB.Count_And_Get
2565 ( p_count => x_msg_count,
2566 p_data => x_msg_data
2567 );
2568
2569 END;
2570
2571 ---------------------------------------------------------
2572 -- RESUBMIT_REQUEST
2573 --
2574 ---------------------------------------------------------
2575
2576 PROCEDURE resubmit_request
2577 (
2578 x_retcode OUT NOCOPY VARCHAR2,
2579 x_errbuf OUT NOCOPY VARCHAR2,
2580 p_request_id IN NUMBER,
2581 p_device_id IN NUMBER := null,
2582 p_task_trx_id IN NUMBER := null,
2583 p_sequence_id IN NUMBER := NULL,
2584 P_business_event_id IN NUMBER
2585 )IS
2586
2587 CURSOR x_cur IS SELECT distinct device_id dev_id
2588 FROM wms_device_requests_hist
2589 WHERE Nvl(device_id, -1) = Nvl( p_device_id, -1)
2590 AND request_id = p_request_id
2591 AND status_code ='P'
2592 AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1))
2593 AND Nvl(task_id,-1) = Nvl(p_task_trx_id,nvl(task_id, -1))
2594 GROUP BY device_id;
2595
2596 l_req_stat varchar2(255);
2597 l_stat_msg varchar2(255);
2598 l_dev_stat varchar2(255);
2599 l_dev_req_type number;
2600 l_msg VARCHAR2(240);
2601 l_ret boolean;
2602 l_msg_count NUMBER;
2603 l_msg_data VARCHAR2(240);
2604 l_wcs_enabled VARCHAR2(1);
2605 l_successful_row_cnt NUMBER;
2606 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2607 BEGIN
2608 IF (l_debug = 1) THEN
2609 trace('RESUBMIT REQUEST::req ID :='||p_business_event_id||':p_request_id:='||p_request_id);
2610 END IF;
2611
2612 move_resubmit_rows(p_request_id,p_business_event_id);
2613
2614 IF (l_debug = 1) THEN
2615 trace('After Calling move_resubmit_rows');
2616 END IF;
2617
2618 IF p_business_event_id = WMS_BE_TASK_CONFIRM THEN--DEVICE CONFIRMATION
2619
2620 IF (l_debug = 1) THEN
2621 trace('deleting all current error records from WDRH');
2622 END IF;
2623 --delete all current error records from WDRH
2624 DELETE FROM wms_device_requests_hist
2625 WHERE request_id = p_request_id
2626 AND status_code ='P'
2627 AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1))
2628 AND Nvl(task_id,-1) = Nvl(p_task_trx_id,nvl(task_id, -1))
2629 AND business_event_id = p_business_event_id ;
2630
2631
2632 IF (l_debug = 1) THEN
2633 trace('calling Resubmission for device Confirmation');
2634 END IF;
2635 wms_device_confirmation_pub.device_confirmation(
2636 l_req_stat
2637 ,l_msg_count
2638 ,l_msg_data
2639 ,p_request_id
2640 ,l_successful_row_cnt
2641 );
2642
2643 IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2644 IF (l_debug = 1) THEN
2645 trace('device Confirmation returned:Unexpected error:l_msg_count'||l_msg_count||'::l_msg_data:'||l_msg_data);
2646 END IF;
2647 RAISE FND_API.g_exc_unexpected_error;
2648 ELSE
2649 IF (l_debug = 1) THEN
2650 trace('device Confirmation returned:Success:Number of successful rows ::'||l_successful_row_cnt);
2651 END IF;
2652 --do not need to commit the txn, since concurrent request does it
2653 END IF;
2654
2655 ELSE--other business event
2656 BEGIN
2657 SELECT nvl(WCS_ENABLED,'N') into l_wcs_enabled FROM MTL_PARAMETERS
2658 WHERE ORGANIZATION_ID = (SELECT ORGANIZATION_ID
2659 FROM wms_device_requests_hist
2660 WHERE request_id = p_request_id
2661 AND status_code ='P'
2662 AND Nvl(business_event_id,-1) = Nvl(p_business_event_id,-1)
2663 AND ROWNUM < 2);
2664 EXCEPTION
2665 WHEN OTHERS THEN
2666 l_wcs_enabled := 'N';
2667 END;
2668
2669
2670 FOR l_rec IN x_cur LOOP
2671 IF (l_debug = 1) THEN
2672 trace('Resubmitting request '||p_request_id ||', device '||l_rec.dev_id);
2673 END IF;
2674 IF (l_wcs_enabled = 'Y') THEN
2675 WMS_DEVICE_INTEGRATION_WCS.SYNC_DEVICE_REQUEST(
2676 p_request_id => p_request_id,
2677 p_device_id => l_rec.dev_id,
2678 p_resubmit_flag => 'Y',
2679 x_status_code => l_req_stat,
2680 x_device_status => l_dev_stat,
2681 x_status_msg => l_stat_msg );
2682 ELSE
2683 WMS_DEVICE_INTEGRATION_PUB.SYNC_DEVICE_REQUEST(
2684 p_request_id => p_request_id,
2685 p_device_id => l_rec.dev_id,
2686 p_resubmit_flag => 'Y',
2687 x_status_code => l_req_stat,
2688 x_device_status => l_dev_stat,
2689 x_status_msg => l_stat_msg );
2690 END IF;
2691
2692 IF ( l_req_stat <> FND_API.g_ret_sts_success) THEN
2693 UPDATE wms_device_requests_hist
2694 SET status_code = l_req_stat,
2695 status_msg = l_stat_msg
2696 WHERE device_id = l_rec.dev_id
2697 AND request_id = p_request_id;
2698 ELSE
2699 UPDATE wms_device_requests_hist
2700 SET status_code = 'S'
2701 WHERE device_id = l_rec.dev_id
2702 AND request_id = p_request_id;
2703 END IF;
2704
2705 END LOOP;
2706
2707 END IF;
2708
2709 --finally remove the rows from the request table.
2710
2711 DELETE FROM wms_device_requests
2712 WHERE request_id = p_request_id
2713 AND Nvl(business_event_id,-1) = Nvl(p_business_event_id,-1)
2714 AND Nvl(sequence_id,-1) = Nvl(p_sequence_id,nvl(sequence_id, -1));
2715
2716 EXCEPTION
2717 WHEN FND_API.G_EXC_ERROR THEN
2718 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2719 FND_MSG_PUB.ADD;
2720 ROLLBACK TO WMS_DEVICE_REQUESTS;
2721 FND_MSG_PUB.Count_And_Get
2722 ( p_count => l_dev_req_type,
2723 p_data => l_req_stat
2724 );
2725 --this is set to P in the form while making call to concurrent req
2726 UPDATE wms_device_requests_hist
2727 SET status_code = 'E',resubmit_date = null
2728 WHERE request_id = p_request_id
2729 AND BUSINESS_EVENT_ID =p_business_event_id
2730 AND status_code = 'P';
2731
2732 COMMIT;
2733
2734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2735 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2736 FND_MSG_PUB.ADD;
2737 ROLLBACK TO WMS_DEVICE_REQUESTS;
2738 FND_MSG_PUB.Count_And_Get
2739 ( p_count => l_dev_req_type,
2740 p_data => l_req_stat
2741 );
2742
2743 --this is set to P in the form while making call to concurrent req
2744 UPDATE wms_device_requests_hist
2745 SET status_code = 'E',resubmit_date = null
2746 WHERE request_id = p_request_id
2747 AND BUSINESS_EVENT_ID =p_business_event_id
2748 AND status_code = 'P';
2749
2750 COMMIT;
2751
2752 WHEN OTHERS THEN
2753 trace('Resubmit_req SQL error :'||substr(sqlerrm, 1, 240));
2754 FND_MESSAGE.SET_NAME('WMS', 'WMS_DEV_RESUBMIT_FAIL');
2755 FND_MSG_PUB.ADD;
2756 ROLLBACK TO WMS_DEVICE_REQUESTS;
2757 FND_MSG_PUB.Count_And_Get
2758 ( p_count => l_dev_req_type,
2759 p_data => l_req_stat
2760 );
2761 --this is set to P in the form while making call to concurrent req
2762 UPDATE wms_device_requests_hist
2763 SET status_code = 'E',resubmit_date = null
2764 WHERE request_id = p_request_id
2765 AND BUSINESS_EVENT_ID =p_business_event_id
2766 AND status_code = 'P';
2767
2768 COMMIT;
2769
2770
2771 END RESUBMIT_REQUEST;
2772
2773
2774 PROCEDURE is_device_set_up(p_org_id NUMBER,
2775 p_bus_event_id NUMBER DEFAULT NULL,
2776 x_return_status OUT NOCOPY VARCHAR2 )
2777 --to set global vaiable WMS_CALLD_EVICE_REQUEST and wms_pick_release_device_request_id
2778 IS
2779 l_setup_row_cnt NUMBER:=0;
2780 l_device_cnt NUMBER:=0;
2781 l_request_id NUMBER :=0;
2782 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2783 BEGIN
2784
2785 IF p_org_id IS NOT NULL THEN
2786 BEGIN
2787 --Check whether any device exist
2788 SELECT 1 INTO l_device_cnt FROM DUAL WHERE exists
2789 (SELECT 1
2790 FROM wms_devices_b
2791 WHERE ORGANIZATION_ID= p_org_id);
2792 EXCEPTION
2793 WHEN NO_DATA_FOUND THEN
2794 l_device_cnt:=0;
2795 END;
2796
2797 IF l_device_cnt = 0 THEN
2798 IF (l_debug = 1) THEN
2799 trace(' No device exist in this Org'||p_org_id);
2800 END IF;
2801 x_return_status := FND_API.G_RET_STS_SUCCESS;
2802 RETURN;
2803 ELSIF p_bus_event_id IS NOT NULL THEN --check in the association table between device and bus_event
2804
2805 BEGIN
2806 SELECT 1 INTO l_setup_row_cnt FROM DUAL WHERE exists
2807 (SELECT 1
2808 FROM wms_bus_event_devices
2809 WHERE business_event_id = p_bus_event_id
2810 AND organization_id = p_org_id
2811 and business_event_id < 50);
2812
2813
2814 EXCEPTION
2815 WHEN NO_DATA_FOUND THEN
2816 l_setup_row_cnt:=0;
2817 END;
2818
2819 IF (l_setup_row_cnt = 0 AND (p_bus_event_id < 50 )) THEN
2820 IF (l_debug = 1) THEN
2821 trace(' Device Request is not being used for business event='||p_bus_event_id);
2822 END IF;
2823 x_return_status := FND_API.G_RET_STS_SUCCESS;
2824 RETURN;
2825 END IF;
2826
2827 END IF ;
2828
2829 END IF ;
2830 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
2831 wms_pkRel_dev_req_id := l_request_id;
2832
2833 --global parameter wms_call_device_request is used in WMSCRTNB.pls
2834 IF p_bus_event_id = wms_be_pick_release then
2835 wms_call_device_request := 1;
2836 ELSIF p_bus_event_id = wms_be_mo_task_alloc then
2837 wms_call_device_request := 2;
2838 --Adding below elseif for Bug 13702075
2839 ELSIF p_bus_event_id= WMS_BE_WIP_PICK_RELEASE THEN
2840 wms_call_device_request := 3;
2841 END IF;
2842
2843 IF (l_debug = 1) THEN
2844 trace('wms_call_device_request:::'||wms_call_device_request);
2845 END IF;
2846 x_return_status := FND_API.G_RET_STS_SUCCESS;
2847
2848 END is_device_set_up;
2849
2850
2851
2852 /* OBSOLETED :this procedure has been moved to WMSPURGS.pls/WMSPURGB.pls */
2853 --call in hte concurrrent program has been changed to use new package
2854 ----------------------------------------------------------------------
2855 -- failure x_retcode = 2 x_errbuf = 'ERROR'
2856 -- success x_retcode = 0 x_errbuf = 'NORMAL'
2857 ----------------------------------------------------------------------
2858 /*
2859 PROCEDURE purge_wms( x_errbuf OUT NOCOPY VARCHAR2,
2860 x_retcode OUT NOCOPY NUMBER,
2861 p_purge_date IN DATE,
2862 p_orgid IN NUMBER,
2863 p_purge_name IN VARCHAR2 )
2864 IS
2865 l_ret boolean;
2866
2867 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2868 BEGIN
2869
2870 IF (p_purge_date IS NULL OR p_orgid IS NULL)THEN
2871 l_ret := fnd_concurrent.set_completion_status('ERROR', 'WMS_MISS_REQ_PARAMETER');
2872 x_retcode := 2;
2873 x_errbuf := 'ERROR';
2874 ELSE
2875 delete from wms_device_requests_hist
2876 where creation_date < p_purge_date and organization_id = p_orgid ;
2877 delete from wms_lpn_histories
2878 where creation_date < p_purge_date and organization_id = p_orgid ;
2879 delete from wms_dispatched_tasks_history
2880 where creation_date < p_purge_date and organization_id = p_orgid ;
2881 delete from wms_exceptions
2882 where creation_date < p_purge_date and organization_id = p_orgid ;
2883 delete from wms_lpn_process_temp ;
2884
2885 INSERT INTO mtl_purge_header (
2886 purge_id,
2887 last_update_date,
2888 last_updated_by,
2889 last_update_login,
2890 creation_date,
2891 created_by,
2892 purge_date,
2893 archive_flag,
2894 purge_name,
2895 organization_id)
2896 VALUES (
2897 mtl_material_transactions_s.NEXTVAL,
2898 Sysdate,
2899 FND_GLOBAL.user_id,
2900 fnd_global.user_id,
2901 Sysdate,
2902 FND_GLOBAL.user_id,
2903 p_purge_date,
2904 NULL,
2905 p_purge_name,
2906 p_orgid );
2907
2908 l_ret := fnd_concurrent.set_completion_status('NORMAL', 'WMS_PURGE_SUCCESS');
2909 x_retcode := 0;
2910 END IF;
2911 END purge_wms;
2912 */
2913
2914 END WMS_DEVICE_INTEGRATION_PVT;
[Home] [Help]