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