1 PACKAGE BODY WMS_RFID_DEVICE_PUB AS
2 --/* $Header: WMSRFIDB.pls 120.10 2006/11/02 10:01:14 ashchand ship $ */
3
4 l_ship_confirm_pkg_mesg VARCHAR2(2000) := null;
5 l_device_req_id_pkg NUMBER;
6
7 -----------------------------------------------------
8 -- trace
9 -----------------------------------------------------
10 PROCEDURE trace(p_msg IN VARCHAR2,p_level IN NUMBER DEFAULT 1 ) IS
11
12 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
13 BEGIN
14 IF l_debug = 1 THEN
15 inv_trx_util_pub.trace(p_msg, 'WMS_RFID_DEVICE_PUB', P_LEVEL);
16 END IF;
17 END trace;
18
19
20 ---------------------------------------------------------
21 -- Populate History
22 --This API transfer data from WDR to WDRH irrespective of whether txn
23 --succeed OR fails, It is always called after the call to autonomous
24 --PROCEDURE generate_xml_csv_api()
25 ---------------------------------------------------------
26 PROCEDURE populate_history(p_device_id IN NUMBER)
27 IS
28 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
29 --might need to change this to transfer only required columns
30 CURSOR cur_dev IS SELECT * FROM wms_device_requests where device_id = p_device_id;
31
32 l_cnt NUMBER := 0;
33 BEGIN
34
35 IF (l_debug = 1) THEN
36 trace('Inside populate_history device_id:'||p_device_id);
37 END IF;
38
39
40 FOR l_rec IN cur_dev LOOP
41
42 IF (l_debug = 1) THEN
43 trace('count record in the populate hist:'|| l_cnt);
44 END IF;
45
46 l_cnt := l_cnt +1;
47
48
49 INSERT INTO wms_device_requests_hist
50 (request_id,
51 business_event_id,
52 organization_id,
53 lpn_id,
54 device_id,
55 subinventory_code,
56 locator_id,
57 status_code,
58 status_msg,
59 task_summary,
60 requested_by,
61 responsibility_application_id,
62 responsibility_id,
63 creation_date,
64 created_by,
65 last_update_date,
66 last_updated_by,
67 REQUEST_DATE
68 )VALUES(
69 l_rec.request_id,
70 l_rec.business_event_id,
71 l_rec.organization_id,
72 l_rec.lpn_id,
73 l_rec.device_id,
74 l_rec.subinventory_code,
75 l_rec.locator_id,
76 Nvl(l_rec.status_code,'S'),
77 l_rec.status_msg,
78 'Y',
79 fnd_global.user_id,
80 FND_GLOBAL.RESP_APPL_ID,
81 FND_GLOBAL.RESP_ID,
82 l_rec.last_update_date,
83 fnd_global.user_id,
84 l_rec.last_update_date,
85 fnd_global.user_id,
86 l_rec.last_update_date);
87
88 END LOOP;
89
90 EXCEPTION
91 WHEN OTHERS THEN
92 IF (l_debug = 1) THEN
93 trace('Other errror: populate_history');
94 trace('SQL error :'||substr(sqlerrm, 1, 240));
95 END IF;
96 END;
97
98 -------------------------------------------------------
99 --generate_xml_csv_api.generates XML/CSV or calls API
100 --after inserting records into WDR
101 -------------------------------------------------------
102
103 PROCEDURE generate_xml_csv_api(p_device_id IN NUMBER,
104 p_business_event_id IN NUMBER,
105 P_organization_id IN NUMBER,
106 p_lpn_id IN NUMBER,
107 p_output_method_id IN NUMBER,
108 p_subinventory_code IN VARCHAR2,
109 p_locator_id IN NUMBER,
110 p_status_code IN VARCHAR2,
111 p_event_date IN DATE,
112 x_request_id OUT NOCOPY NUMBER,
113 x_return_status OUT NOCOPY VARCHAR2)
114
115 IS
116 PRAGMA AUTONOMOUS_TRANSACTION;
117
118 l_request_id NUMBER;
119 l_msg_data VARCHAR2(240);
120 l_xml_stat VARCHAR2(1);
121 l_return_status VARCHAR2(1);
122 l_dev_stat varchar2(255);
123 l_retval NUMBER;
124 l_msg_count NUMBER;
125 l_count NUMBER;
126 l_tmp_out NUMBER;
127 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
128 BEGIN
129
130 SAVEPOINT generate_xml_csv_sp;
131
132 x_return_status := FND_API.g_ret_sts_success;
133
134
135 IF (l_debug = 1) THEN
136 trace('generate_xml_csv_api ::DEVICE::OUT_METHOD:status_code:p_event_date');
137 trace('generate_xml_csv_api ::'||p_device_id ||'::'||p_output_method_id||'::'||p_status_code||'::'||p_event_date);
138
139 END IF;
140
141 --populate the WDR table
142 IF l_device_req_id_pkg IS NULL THEN
143 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
144 l_device_req_id_pkg := l_request_id;
145 ELSE
146 l_request_id := l_device_req_id_pkg;
147 END IF;
148
149
150 IF p_business_event_id = wms_device_integration_pvt.wms_be_truck_load_ship THEN
151 l_msg_data := l_ship_confirm_pkg_mesg;
152 else
153 l_msg_data := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
154
155 END IF;
156
157
158 IF (l_debug = 1) THEN
159 trace('generate_xml_csv_api ::l_msg_data::'||l_msg_data);
160
161 END IF;
162
163
164 INSERT INTO wms_device_requests (request_id,
165 business_event_id,
166 organization_id,
167 lpn_id,
168 device_id,
169 subinventory_code,
170 locator_id,
171 status_code,
172 status_msg,
173 last_update_date,
174 last_updated_by
175 ) VALUES
176 (l_request_id,
177 p_business_event_id,
178 p_organization_id,
179 p_lpn_id,
180 p_device_id,
181 p_subinventory_code,
182 p_locator_id,
183 p_status_code,
184 l_msg_data,
185 p_event_date,
186 fnd_global.user_id);
187
188
189 --generate XML/CSV/API
190
191 IF (l_debug = 1) THEN
192 trace('generate_xml_csv_api:Inserted record into WDR');
193 END IF;
194
195
196 -- Generate XML,CSV if configured for it
197 IF (( p_output_method_id= wms_device_integration_pvt.WMS_DEV_IO_XML) OR (p_output_method_id = wms_device_integration_pvt.WMS_DEV_IO_CSV)) then
198 IF (l_debug = 1) THEN
199 trace('going to call wms_device_integration_pvt.generate_xml_csv');
200 END IF;
201
202 BEGIN
203 l_retval := wms_device_integration_pvt.generate_xml_csv(p_device_id,p_output_method_id);
204
205 IF l_retval <> 0 THEN
206 l_xml_stat := 'E';
207 ELSE
208 l_xml_stat := 'S';
209 END IF;
210 IF (l_debug = 1) THEN
211 trace(' Done with generate xml , retval '||l_retval ||' status_code: '||l_xml_stat);
212 END IF;
213
214 EXCEPTION
215 WHEN OTHERS THEN
216 IF (l_debug = 1) THEN
217 trace(' Exception in call to wms_device_integration_pvt.generate_xml_csv');
218 END IF;
219 l_xml_stat := 'E';
220 END;
221
222
223 --Update WDR since error while generating the XML
224 IF l_xml_stat <> 'S' THEN
225
226 l_msg_data := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');--only last message
227
228 UPDATE wms_device_requests
229 SET status_code = l_xml_stat,
230 status_msg = l_msg_data
231 WHERE device_id = p_device_id;
232 END IF;
233
234
235 ELSIF (p_output_method_id = wms_device_integration_pvt.WMS_DEV_IO_API) then
236 IF (l_debug = 1) THEN
237 trace(' generate_xml_csv_api: Submit sync_device_request');
238 END IF;
239
240
241 BEGIN
242 WMS_DEVICE_INTEGRATION_PUB.SYNC_DEVICE_REQUEST(
243 p_request_id => l_request_id,
244 p_device_id => p_device_id,
245 p_resubmit_flag => 'N',
246 x_status_code => l_return_status,
247 x_device_status => l_dev_stat,
248 x_status_msg => l_msg_data );
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 IF (l_debug = 1) THEN
253 trace(' EXCEPTION from call to SYNC_DEVICE_REQUEST');
254 END IF;
255 l_return_status := 'E';
256 END;
257
258
259 IF (l_debug = 1) THEN
260 trace(' After call to SYNC_DEVICE_REQUEST l_return_status:'||l_return_status);
261 END IF;
262
263
264 IF (l_return_status<> FND_API.g_ret_sts_success) THEN
265
266 l_msg_data := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');--only last message
267
268 UPDATE wms_device_requests
269 SET status_code = l_return_status,
270 status_msg = l_msg_data
271 WHERE device_id = p_device_id;
272
273 END IF;
274
275 END IF;
276
277
278 IF p_business_event_id <> wms_device_integration_pvt.wms_be_rfid_error THEN
279 IF (l_debug = 1) THEN
280 trace(' generate_xml_csv_api: Calling populate_history');
281 END IF;
282
283 trace('before calling populate_hist');
284
285
286 populate_history(p_device_id);
287
288 END IF;
289
290 COMMIT;
291
292 IF (l_debug = 1) THEN
293 trace(' generate_xml_csv_api: Returning::'||x_return_status);
294 END IF;
295
296 EXCEPTION
297
298 WHEN others THEN
299 x_return_status := 'E';
300 ROLLBACK TO generate_xml_csv_sp;
301 IF (l_debug = 1) THEN
302 trace('Other error in generate_xml_csv_api');
303 trace('SQL error :'||substr(sqlerrm, 1, 240));
304 END IF;
305
306 END generate_xml_csv_api;
307
308
309
310
311 FUNCTION is_last_lpn_load(p_lpn_id IN NUMBER)
312 RETURN NUMBER
313
314 IS
315 --x_is_last_lpn ::
316 --0 : can't find/Error
317 --1 : NO
318 --2 : Yes, for the delivery
319 --3 : Yes, for the trip;
320 l_trip_id NUMBER;
321 l_delivery_id NUMBER;
322 x_is_last_lpn NUMBER;
323 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
324 BEGIN
325 IF (l_debug = 1) THEN
326 trace('Inside is_last_lpn_load');
327 END IF;
328
329 --defaulting it to be NOT the last one
330 x_is_last_lpn := 1;
331
332 --get the delivery_id
333 -- After staging and before Truck Load, there might not be any delivery attached to the LPN
334 begin
335 select distinct wda.delivery_id INTO l_delivery_id
336 from wsh_delivery_details wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
337 wsh_delivery_details wdd2
338 where lpn.outermost_lpn_id = p_lpn_id
339 and wdd2.lpn_id = lpn.lpn_id
340 and wdd2.lpn_id is not null
341 and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
342 and wdd.delivery_detail_id = wda.delivery_detail_id;
343 EXCEPTION
344 WHEN no_data_found THEN
345 IF (l_debug = 1) THEN
346 trace('No delivery found');
347 END IF;
348 x_is_last_lpn :=0;
349 RETURN x_is_last_lpn;
350 END;
351
352 IF (l_debug = 1) THEN
353 trace(' is_last_lpn_load: l_delivery_id::'||l_delivery_id);
354 END IF;
355
356 IF l_delivery_id IS NOT NULL THEN
357 --get the trip if any associated with the delivery
358 begin
359 select wts.trip_id
360 into l_trip_id
361 from wsh_delivery_legs wdl, wsh_trip_stops wts
362 where wdl.delivery_id = l_delivery_id
363 and wdl.pick_up_stop_id = wts.stop_id;
364 EXCEPTION
365 WHEN no_data_found THEN
366 l_trip_id := NULL;
367 END;
368
369 END IF;
370 IF (l_debug = 1) THEN
371 trace('is_last_lpn_load: l_trip_id ::'||l_trip_id);
372 END IF;
373
374 IF l_trip_id IS NULL THEN
375
376 IF (l_debug = 1) THEN
377 trace('is_last_lpn_load: Inside l_trip_id is null');
378 END IF;
379
380
381 --check whether all the lines in the delivery are loaded
382 begin
383 SELECT -1 INTO x_is_last_lpn FROM dual WHERE exists
384 ( select wlpn.license_plate_number --distinct wlpn.license_plate_number
385 from wsh_delivery_details wdd, wsh_delivery_assignments_v wda, wms_license_plate_numbers lpn,
386 wsh_delivery_details wdd2,wms_license_plate_numbers wlpn
387 where wdd2.delivery_detail_id = wda.parent_delivery_detail_id
388 and wdd.delivery_detail_id = wda.delivery_detail_id
389 and wdd2.lpn_id is not null
390 and wdd2.lpn_id = lpn.lpn_id
391 and lpn.outermost_lpn_id = wlpn.lpn_id
392 AND wlpn.lpn_id <> P_LPN_ID
393 and wlpn.lpn_context <> 9
394 and lpn.organization_id = wdd2.organization_id
395 and nvl(wdd.released_status,'N') = 'Y'
396 and (wdd.inv_interfaced_flag <> 'Y' or wdd.inv_interfaced_flag is null )
397 and wda.delivery_id = l_delivery_id
398 );
399
400
401
402 EXCEPTION
403 WHEN no_data_found THEN
404 x_is_last_lpn := 2;
405
406 END;
407
408 IF x_is_last_lpn <> -1 THEN
409 x_is_last_lpn := 2; --last LPN to be loaded for the delivery
410 END IF;
411
412 ELSE
413 IF (l_debug = 1) THEN
414 trace('is_last_lpn_load: Inside l_trip_id is NOT null');
415 END IF;
416 --get all the lines of the all deliveries in the trip are loaded
417 begin
418
419 SELECT -1 INTO x_is_last_lpn FROM dual WHERE exists
420 (
421 select wlpn.license_plate_number --distinct wlpn.license_plate_number
422 from
423 wms_license_plate_numbers lpn,
424 wms_license_plate_numbers wlpn,
425 wsh_new_deliveries wnd,
426 wsh_delivery_legs wdl,
427 wsh_delivery_details wdd,
428 wsh_delivery_assignments_v wda,
429 wsh_delivery_details wdd2,
430 wsh_trip_stops pickup_stop,
431 wsh_trip_stops dropoff_stop
432 where pickup_stop.trip_id = l_trip_id
433 and wdl.pick_up_stop_id = pickup_stop.stop_id
434 and wdl.drop_off_stop_id = dropoff_stop.stop_id
435 and pickup_stop.trip_id = dropoff_stop.trip_id
436 and wdl.delivery_id = wnd.delivery_id
437 and wnd.status_code in ('OP', 'PA')
438 and wnd.delivery_id = wda.delivery_id
439 and wdd.delivery_detail_id = wda.delivery_detail_id
440 and wdd2.delivery_detail_id = wda.parent_delivery_detail_id
441 and wdd2.lpn_id is not null
442 and wdd2.lpn_id = lpn.lpn_id
443 and lpn.outermost_lpn_id = wlpn.lpn_id
444 AND wlpn.lpn_id <> P_LPN_ID
445 and wlpn.lpn_context <> 9
446 and wdd.released_status = 'Y'
447 and (wdd.inv_interfaced_flag <> 'Y' )
448 );
449
450
451
452 EXCEPTION
453 WHEN no_data_found THEN
454 x_is_last_lpn := 3;
455 END;
456
457
458 IF x_is_last_lpn <> -1 THEN
459 x_is_last_lpn := 3; --last LPN to be loaded for the trip
460 END IF;
461
462 END IF;
463 IF (l_debug = 1) THEN
464 trace('returning: 2-last from deliv; 3-last for trip:: Value'|| x_is_last_lpn);
465 END IF;
466 RETURN x_is_last_lpn;
467
468 END;
469
470
471
472 --To find out whether the device has been set up to perfomr the valid transactions
473 --x_out_business_event_id will return whether Truck_Load ot Truck_load_ship
474 procedure is_valid_txn_device(p_device_id IN NUMBER,
475 p_lpn_context IN NUMBER,
476 p_organization_id IN NUMBER,
477 x_out_business_event_id OUT nocopy NUMBER,
478 x_valid_device_for_txn OUT nocopy NUMBER,
479 x_verif_req OUT nocopy VARCHAR2)
480 IS
481 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
482 l_count NUMBER :=0;
483
484 BEGIN
485
486 IF (l_debug = 1) THEN
487 trace('Inside is_valid_txn_device');
488 END IF;
489
490
491 IF (p_lpn_context = wms_container_pub.lpn_context_picked OR
492 p_lpn_context = wms_container_pub.lpn_context_inv) THEN
493
494 BEGIN
495 SELECT business_event_id,verification_required INTO x_out_business_event_id,x_verif_req FROM wms_bus_event_devices
496 WHERE device_id = p_device_id
497 AND ENABLED_FLAG = 'Y'
498 AND organization_id = p_organization_id
499 AND (business_event_id = wms_device_integration_pvt.wms_be_truck_load
500 OR business_event_id = wms_device_integration_pvt.wms_be_truck_load_ship);
501
502 x_valid_device_for_txn := 1;
503
504 EXCEPTION
505 WHEN no_data_found THEN
506 x_valid_device_for_txn := 0;
507 WHEN too_many_rows THEN
508 --Must be a unique record in the organization
509 --return error, can not have both bus event Truck Load as well as Truck Load and ship
510 x_valid_device_for_txn := -1;
511 END;
512
513
514 ELSIF (p_lpn_context = wms_container_pub.lpn_context_intransit OR p_lpn_context = wms_container_pub.lpn_context_vendor) THEN
515
516 BEGIN
517 SELECT business_event_id,verification_required INTO x_out_business_event_id,X_VERIF_REQ FROM wms_bus_event_devices
518 WHERE device_id = p_device_id
519 AND ENABLED_FLAG = 'Y'
520 AND organization_id = p_organization_id
521 AND business_event_id IN
522 (wms_device_integration_pvt.wms_be_std_insp_receipt,wms_device_integration_pvt.wms_be_direct_receipt);
523
524 x_valid_device_for_txn := 1;
525
526 EXCEPTION
527 WHEN no_data_found THEN
528 x_valid_device_for_txn := 0;
529 WHEN too_many_rows THEN
530 --Must be a unique record in this organization
531 --return error, can not have both bus event direct rcv as well
532 --as std/insp associated WITH the same device
533 x_valid_device_for_txn := -2;
534 END;
535
536 ELSE --Invalid LPN context
537 x_valid_device_for_txn := -3;
538 END IF;
539
540
541 EXCEPTION
542 WHEN OTHERS THEN
543 x_valid_device_for_txn := -999;
544 IF (l_debug = 1) THEN
545 trace('Other errror: is_valid_txn_device');
546 trace('SQL error :'||substr(sqlerrm, 1, 240));
547 END IF;
548
549 END;
550
551
552 -- Here if we find missing LPNs, We do not return, rather we unassign these
553 --missing lines from the delivery
554 PROCEDURE CHECK_LPN_DELIVERIES(p_trip_id IN NUMBER,
555 p_organization_id IN NUMBER,
556 p_dock_door_id IN NUMBER,
557 p_outermost_lpn_id IN NUMBER,
558 p_delivery_id IN NUMBER,
559 x_error_code OUT NOCOPY NUMBER,
560 x_missing_item OUT NOCOPY t_genref,
561 x_missing_lpns OUT NOCOPY t_genref,
562 x_ship_set OUT NOCOPY VARCHAR2,
563 x_delivery_info OUT NOCOPY t_genref,
564 x_deli_count OUT NOCOPY NUMBER)
565 IS
566
567 cursor delivery_for_trip is
568 select distinct delivery_id
569 from WMS_SHIPPING_TRANSACTION_TEMP
570 where organization_id = p_organization_id
571 and dock_door_id = p_dock_door_id
572 and trip_id = p_trip_id;
573
574 cursor delivery_for_dock is
575 select distinct delivery_id
576 from WMS_SHIPPING_TRANSACTION_TEMP
577 where organization_id = p_organization_id
578 and dock_door_id = p_dock_door_id
579 and dock_appoint_flag = 'N';
580
581 l_missing_exists NUMBER;
582 l_return_status VARCHAR2(1);
583 l_error_msg VARCHAR2(240);
584 l_delivery_id NUMBER;
585 l_dock_appoint_flag VARCHAR2(1);
586 temp_val NUMBER;
587 l_subinventory_code VARCHAR2(30);
588 l_license_plate_number VARCHAR2(30);
589 l_lpn_id NUMBER ;
590 l_delivery_detail_id NUMBER;
591 l_locator_id NUMBER;
592 l_concatenated_segments VARCHAR2(120);
593 l_del_name VARCHAR2(30);
594
595
596 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
597
598
599 BEGIN
600 IF (l_debug = 1) THEN
601 trace('Inside CHECK_LPN_DELIVERIES::'||p_trip_id ||'::'|| p_organization_id ||'::'|| p_dock_door_id||'::'||p_outermost_lpn_id||'::'||p_delivery_id);
602 END IF;
603
604 x_error_code := 0;
605
606 -- check if there is anything loaded
607 --RFID does not care about dock-appointment, if there is LPN just ship
608 --it, so passing p_dock_appoint_flag = 'N'
609 if (wms_shipping_transaction_pub.is_loaded(p_organization_id,p_dock_door_id,'N') = 'N') then
610 x_error_code := 5;
611 return;
612 end if;
613
614 -- check missing item first
615 IF (l_debug = 1) THEN
616 trace('CHECK_LPN_DELIVERIES: Checking missing items');
617 END IF;
618 wms_shipping_transaction_pub.MISSING_ITEM_CHECK( x_missing_item,
619 p_trip_id,
620 p_dock_door_id,
621 p_organization_id,
622 l_missing_exists);
623 if (l_missing_exists > 0) then
624 x_error_code := 1;
625 return;
626 end if;
627
628
629 -- check ship set
630 wms_shipping_transaction_pub.SHIP_SET_CHECK(p_trip_id,
631 p_dock_door_id,
632 p_organization_id,
633 x_ship_set,
634 l_return_status,
635 l_error_msg);
636 if l_return_status = 'E' then
637 x_error_code := 3;
638 return;
639 end if;
640
641
642 -- check missing LPNs
643 IF (l_debug = 1) THEN
644 trace('CHECK_LPN_DELIVERIES: Checking missing LPNs');
645 END IF;
646 wms_shipping_transaction_pub.MISSING_LPN_CHECK( x_missing_lpns,
647 p_trip_id,
648 p_dock_door_id,
649 p_organization_id,
650 l_missing_exists);
651 if (l_missing_exists > 0) then
652 --x_error_code := 2;
653 --return; do not return, Rather unassign these lines from the delivery
654
655 IF (l_debug = 1) THEN
656
657 trace('CHECK_LPN_DELIVERIES : Unassigning LPNs that are not loaded to the truck');
658 trace('CHECK_LPN_DELIVERIES :l_license_plate_number::l_lpn_id::l_delivery_detail_id');
659 END IF;
660
661 LOOP
662
663 FETCH x_missing_lpns INTO
664 l_license_plate_number,l_lpn_id,l_delivery_detail_id,
665 l_subinventory_code, l_locator_id, l_concatenated_segments,l_del_name;
666 EXIT WHEN x_missing_lpns%NOTFOUND;
667
668 IF (l_debug = 1) THEN
669 trace('CHECK_LPN_DELIVERIES :unassigning delivery'||l_license_plate_number||'::'||l_lpn_id||'::'||l_delivery_detail_id);
670 END IF;
671
672 WMS_SHIPPING_TRANSACTION_PUB.unassign_delivery_line
673 (p_delivery_detail_id =>l_delivery_detail_id,
674 x_return_status => l_return_status,
675 p_commit_flag => fnd_api.g_true --Committed
676 );
677
678 END LOOP;
679
680
681 END IF;
682
683 -- Locked the record first to avoid conccurent process error
684
685 BEGIN
686 if p_trip_id >0 then
687 select 1
688 into temp_val
689 from WMS_SHIPPING_TRANSACTION_TEMP
690 where organization_id = p_organization_id
691 and dock_door_id = p_dock_door_id
692 and trip_id = p_trip_id
693 and rownum = 1
694 for update NOWAIT;
695 else
696 select 1
697 into temp_val
698 from WMS_SHIPPING_TRANSACTION_TEMP
699 where organization_id = p_organization_id
700 and dock_door_id = p_dock_door_id
701 and dock_appoint_flag = 'N'
702 and rownum = 1
703 for update NOWAIT;
704 end if;
705 EXCEPTION WHEN others THEN
706 x_error_code := 6;
707 return;
708 END;
709
710 -- create delivery for LPNs without delivery
711 l_return_status := 'S';
712 if p_trip_id = 0 then
713 wms_shipping_transaction_pub.CREATE_DELIVERY(p_outermost_lpn_id,
714 p_trip_id,
715 p_organization_id,
716 p_dock_door_id,
717 l_delivery_id,
718 l_return_status,
719 l_error_msg);
720 if ( l_return_status <> 'S') then
721 x_error_code := 4;
722 return;
723 end if;
724 end if;
725
726 if p_delivery_id >0 then l_delivery_id := p_delivery_id;
727 elsif p_outermost_lpn_id = 0 then
728 if p_trip_id >0 then
729 open delivery_for_trip;
730 fetch delivery_for_trip into l_delivery_id;
731 close delivery_for_trip;
732 else
733 open delivery_for_dock;
734 fetch delivery_for_dock into l_delivery_id;
735 close delivery_for_dock;
736 end if;
737 end if;
738
739 x_deli_count := 0;
740 if p_trip_id >0 then
741 select count(distinct delivery_id)
742 into x_deli_count
743 from WMS_SHIPPING_TRANSACTION_TEMP
744 where organization_id = p_organization_id
745 and dock_door_id = p_dock_door_id
746 and trip_id = p_trip_id;
747 else
748 select count(distinct delivery_id)
749 into x_deli_count
750 from WMS_SHIPPING_TRANSACTION_TEMP
751 where organization_id = p_organization_id
752 and dock_door_id = p_dock_door_id
753 and dock_appoint_flag = 'N';
754 end if;
755
756 -- Query the delivery
757 wms_shipping_transaction_pub.get_delivery_info(x_delivery_info,
758 l_delivery_id);
759
760
761 EXCEPTION
762 WHEN OTHERS THEN
763 x_error_code := 9999;
764
765 IF x_missing_lpns%isopen then
766 CLOSE x_missing_lpns;
767 END IF;
768
769 END CHECK_LPN_DELIVERIES;
770
771
772 /* To process Normal Truck Load Txn */
773 procedure process_normal_truck_load(p_lpn_id IN NUMBER,
774 p_org_id IN NUMBER,
775 p_dock_door_id IN NUMBER,
776 x_is_last_lpn_load OUT nocopy NUMBER,
777 x_return_status OUT NOCOPY VARCHAR2)
778 IS
779 l_error_code NUMBER;
780 l_outermost_lpn_id NUMBER;
781 l_outermost_lpn varchar2(30);
782 l_parent_lpn_id NUMBER;
783 l_parent_lpn varchar2(30);
784 l_inventory_item_id NUMBER;
785 l_quantity NUMBER;
786 l_requested_quantity NUMBER;
787 l_delivery_detail_id NUMBER;
788 l_transaction_Temp_id NUMBER;
789 l_item_name varchar2(50);
790 l_subinventory_code varchar2(30);
791 l_revision varchar2(1);
792 l_locator_id NUMBER;
793 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
794 l_lot_number varchar2(80);
795 l_loaded_dock_door varchar2(50);
796 l_delivery_name varchar2(50);
797 l_trip_name varchar2(50);
798 l_delivery_detail_ids varchar2(50);
799 l_serial_at_issue NUMBER;
800 l_is_last_lpn_load NUMBER;
801 l_msg_count NUMBER;
802 l_msg_data VARCHAR2(250);
803 l_catch_wt_check NUMBER;
804 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
805 --l_return_status varchar2;
806 BEGIN
807 IF (l_debug = 1) THEN
808 trace('Inside process_normal_truck_load');
809 END IF;
810
811 X_return_status := 'S';
812
813 --Check that for all items in the LPN, either catch weights are
814 --defaulted OR defaulting is enabled so that shipping can default them
815 --if the following api returns 0 it is fine
816
817
818 l_catch_wt_check := WMS_CATCH_WEIGHT_PVT.Check_LPN_Secondary_Quantity
819 ( p_api_version => 1.0
820 , x_return_status => x_return_status
821 , x_msg_count => l_msg_COUNT
822 , x_msg_data => l_msg_DATA
823 , p_organization_id => p_org_id
824 , p_outermost_lpn_id => p_lpn_id
825 );
826
827
828 IF (l_debug = 1) THEN
829 trace('Check for catch wt: l_catch_wt_check :: '|| l_catch_wt_check||'::return_status::'||x_return_status);
830 END IF;
831
832
833 IF l_catch_wt_check <> 0 THEN
834 IF (l_debug = 1) THEN
835 trace('catch wt validation failed');
836 END IF;
837 FND_MESSAGE.SET_NAME('WMS', 'WMS_CTWT_DEFAULT_ERROR');
838 FND_MSG_PUB.ADD;
839 RAISE FND_API.G_EXC_ERROR;
840
841 END IF;
842
843 --make sure that the LPN does not have any Serial item which is defined as
844 --"Serials at SO issue
845 begin
846 SELECT 1 INTO l_serial_at_issue FROM dual WHERE exists
847 (SELECT wlpn.lpn_id
848 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn, mtl_system_items msi
849 WHERE wlpn.lpn_id = wlc.parent_lpn_id
850 AND msi.inventory_item_id = wlc.inventory_item_id
851 AND msi.organization_id = wlc.organization_id
852 and wlc.organization_id = p_org_id
853 AND MSI.SERIAL_NUMBER_CONTROL_CODE = 6
854 AND wlpn.outermost_lpn_id = p_lpn_id
855 AND wlpn.organization_id = p_org_id
856 );
857 EXCEPTION
858 WHEN no_data_found THEN
859 l_serial_at_issue := 0;
860 END;
861
862
863 --l_serial_at_issue := 0;
864 IF (l_debug = 1) THEN
865 trace('l_serial_at_issue::'||l_serial_at_issue);
866 END IF;
867
868
869 IF l_serial_at_issue = 1 THEN
870 -- it should return error not possible to process through RFID Txn
871 IF (l_debug = 1) THEN
872 trace('Error: LPN contains Serials at SO Issue');
873 END IF;
874 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_SERIAL_ISSUE');
875 FND_MSG_PUB.ADD;
876 RAISE FND_API.G_EXC_ERROR;
877
878 END IF;
879
880
881 IF (l_debug = 1) THEN
882 trace('Calling WMS_SHIPPING_TRANSACTION_PUB.LPN_SUBMIT');
883 END IF;
884 -- Load the LPN on the dock door
885 -- this call commits inside
886
887 WMS_SHIPPING_TRANSACTION_PUB.LPN_SUBMIT
888 ( p_outermost_lpn_id => p_lpn_id,--Outermost LPN only
889 p_trip_id => 0,--equivalent to LPN Ship Page
890 p_organization_id => p_org_id,
891 p_dock_door_id => p_dock_door_id,
892 x_error_code => l_error_code, --out
893 x_outermost_lpn => l_outermost_lpn,
894 x_outermost_lpn_id => l_outermost_lpn_id,
895 x_parent_lpn_id => l_parent_lpn_id,
896 x_parent_lpn => l_parent_lpn,
897 x_inventory_item_id => l_inventory_item_id,
898 x_quantity => l_quantity,
899 x_requested_quantity => l_requested_quantity ,
900 x_delivery_detail_id => l_delivery_detail_id,
901 x_transaction_Temp_id => l_transaction_Temp_id,
902 x_item_name => l_item_name,
903 x_subinventory_code => l_subinventory_code,
904 x_revision => l_revision,
905 x_locator_id => l_locator_id,
906 x_lot_number => l_lot_number,
907 x_loaded_dock_door => l_loaded_dock_door,
908 x_delivery_name => l_delivery_name,
909 x_trip_name => l_trip_name ,
910 x_delivery_detail_ids => l_delivery_detail_ids,
911 p_is_rfid_call => 'Y'
912 );
913
914
915
916 IF (l_debug = 1) THEN
917 trace('value of l_error_code :'|| l_error_code);
918 END IF;
919
920 IF l_error_code <> 0 AND l_error_code <> 6 THEN--l_error_code <> 6 because RFID does not
921 --care about dock appointment, if LPN is there process it
922
923 x_return_status := 'E';
924
925 IF l_error_code = 5 THEN
926
927 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LPN_OTH_DOCK');
928 FND_MESSAGE.SET_TOKEN('DOCK', l_loaded_dock_door);
929 FND_MSG_PUB.ADD;
930 else
931 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_TRUCK_LOAD_FAIL');
932 FND_MSG_PUB.ADD;
933 END IF;
934
935 RAISE FND_API.G_EXC_ERROR;
936
937 ELSE
938
939 IF (l_debug = 1) THEN
940 trace('call to LPN_SUBMIT success: Checking whether it is last LPN');
941 END IF;
942
943 --Load_SHIP will use the value of l_is_last_lpn_load to know
944 --whether TO call ship confirm.
945 l_is_last_lpn_load := is_last_lpn_load(p_lpn_id);
946
947
948 --Setting mesg for last lpn
949 IF l_is_last_lpn_load = 2 THEN
950 IF (l_debug = 1) THEN
951 trace('last LPN to be loaded for delivery');
952 END IF;
953 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LAST_LPN_DELIVERY');
954 FND_MSG_PUB.ADD;
955 ELSIF l_is_last_lpn_load = 3 THEN
956 IF (l_debug = 1) THEN
957 trace('last LPN to be loaded for trip');
958 END IF;
959 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LAST_LPN_TRIP');
960 FND_MSG_PUB.ADD;
961 ELSE
962 IF (l_debug = 1) THEN
963 trace('Truck Load Successful');
964 END IF;
965 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_TRUCK_LOAD_SUCCESS');
966 FND_MSG_PUB.ADD;
967
968 END IF;
969
970 END IF;
971
972 x_is_last_lpn_load := l_is_last_lpn_load;
973
974
975 EXCEPTION
976 WHEN OTHERS THEN
977 x_return_status := 'E';
978 x_is_last_lpn_load := 0;
979 IF (l_debug = 1) THEN
980 trace('Other errror: process_normal_truck_load');
981 trace('SQL error :'||substr(sqlerrm, 1, 240));
982 END IF;
983
984 END process_normal_truck_load;
985
986
987
988 /* To process Normal Truck Load and ship Txn */
989 procedure process_normal_truck_load_ship(p_lpn_id IN NUMBER,
990 p_org_id IN NUMBER,
991 p_dock_door_id IN NUMBER,
992 x_return_status OUT NOCOPY VARCHAR2)
993
994 IS
995 l_error_code NUMBER;
996 l_msg_data VARCHAR2(2000);
997 l_return_status varchar2(1);
998 l_missing_item t_genref;
999 l_missing_lpns t_genref;
1000 l_ship_set VARCHAR2(30);
1001 l_delivery_info t_genref;
1002 l_deli_count NUMBER;
1003 l_msg_count NUMBER;
1004 l_trip_id NUMBER;
1005 l_is_last_lpn_load NUMBER;
1006 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1007 BEGIN
1008 IF (l_debug = 1) THEN
1009 trace('Inside process_normal_truck_load_ship');
1010 END IF;
1011 x_return_status := FND_API.g_ret_sts_success;
1012
1013
1014 --First Load the LPN
1015 process_normal_truck_load(p_lpn_id => p_lpn_id,
1016 p_org_id => p_org_id,
1017 p_dock_door_id => p_dock_door_id,
1018 x_is_last_lpn_load => l_is_last_lpn_load,
1019 x_return_status => l_return_status);
1020
1021
1022 IF l_return_status IS NULL OR l_return_status = 'E' OR l_return_status = 'U' THEN
1023 IF (l_debug = 1) THEN
1024 trace('process_normal_truck_load_ship: Truck load failed');
1025 END IF;
1026 RAISE FND_API.G_EXC_ERROR;
1027
1028 ELSE
1029
1030 IF (l_debug = 1) THEN
1031 trace('process_normal_truck_load_ship: Truck Load Successful');
1032 END IF;
1033
1034
1035 --Checking whether the last LPN for delivery or Trip (if exists)
1036
1037 IF l_is_last_lpn_load IN (2,3) THEN
1038 IF (l_debug = 1) THEN
1039 trace('Last LPN in the trip or delivery: Calling ship confirm');
1040 END IF;
1041
1042 WMS_SHIPPING_TRANSACTION_PUB.close_truck
1043 ( P_dock_door_id => p_dock_door_id,
1044 P_organization_id => p_org_id,
1045 p_shipping_mode => 'NORMAL',
1046 p_commit_flag => fnd_api.g_false,
1047 x_return_status => l_return_status,
1048 x_return_msg => l_msg_data
1049 );
1050
1051
1052 IF (l_debug = 1) THEN
1053 trace('process_normal_truck_load_ship: l_return_status ::' ||l_return_status);
1054 END IF;
1055
1056 l_ship_confirm_pkg_mesg := substr(l_msg_data,1,240);
1057
1058 if x_return_status in ('S','W') THEN
1059
1060 COMMIT;
1061 IF (l_debug = 1) THEN
1062 trace('process_normal_truck_load_ship return Success/Warning: Commit Done');
1063 END IF;
1064 ELSE
1065 RAISE FND_API.G_EXC_ERROR;
1066 END IF;
1067
1068
1069 ELSE
1070 IF (l_debug = 1) THEN
1071 trace('process_normal_truck_load_ship: Not the last LPN in the trip or delivery:So DO not Ship Confirm');
1072
1073 l_ship_confirm_pkg_mesg :=fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
1074
1075 END IF;
1076
1077 END IF;
1078
1079 END IF;
1080
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 x_return_status := 'E';
1084 IF (l_debug = 1) THEN
1085 trace('Other errror: process_normal_truck_load_ship');
1086 trace('SQL error :'||substr(sqlerrm, 1, 240));
1087 END IF;
1088
1089 END process_normal_truck_load_ship;
1090
1091
1092 PROCEDURE process_direct_truck_load( p_lpn_id IN NUMBER,
1093 p_org_id IN NUMBER,
1094 p_dock_door_id IN NUMBER,
1095 x_is_last_lpn_load OUT nocopy NUMBER,
1096 x_return_status OUT NOCOPY VARCHAR2)
1097 IS
1098
1099 l_return_status VARCHAR2(1);
1100 l_msg_count NUMBER;
1101 l_msg_data VARCHAR2(250);
1102 l_remaining_qty NUMBER;
1103 l_is_last_lpn_load NUMBER;
1104
1105 l_num_line_processed NUMBER;
1106 l_project_id NUMBER;
1107 l_task_id NUMBER;
1108 l_cross_project_allowed VARCHAR2(1);
1109 l_cross_unit_allowed VARCHAR2(1);
1110 l_group_by_customer_flag VARCHAR2(1);
1111 l_group_by_fob_flag VARCHAR2(1);
1112 l_group_by_freight_terms_flag VARCHAR2(1);
1113 l_group_by_intmed_ship_flag VARCHAR2(1);
1114 l_group_by_ship_method_flag VARCHAR2(1);
1115 l_group_by_ship_to_loc_value VARCHAR2(100);
1116 l_group_by_ship_from_loc_value VARCHAR2(100);
1117 l_group_by_customer_value VARCHAR2(100);
1118 l_group_by_fob_value VARCHAR2(100);
1119 l_group_by_freight_terms_value VARCHAR2(100);
1120 l_group_by_intmed_value VARCHAR2(100);
1121 l_group_by_ship_method_value VARCHAR2(100);
1122 l_serial_at_issue NUMBER;
1123 l_ct_wt_enabled NUMBER;
1124 l_catch_wt_check NUMBER;
1125 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1126 BEGIN
1127
1128 IF (l_debug = 1) THEN
1129 trace('Inside PROCESS_DIRECT_TRUCK_LOAD');
1130 END IF;
1131
1132 x_return_status := FND_API.g_ret_sts_success;
1133
1134 --Jason will provide this API
1135 --Check that for all items in the LPN, either catch weights are
1136 --defaulted OR defaulting is enabled so that shipping can default them
1137 --if the following api returns 0 it is fine
1138
1139 l_catch_wt_check := WMS_CATCH_WEIGHT_PVT.Check_LPN_Secondary_Quantity
1140 ( p_api_version => 1.0
1141 , x_return_status => x_return_status
1142 , x_msg_count => l_msg_count
1143 , x_msg_data => l_msg_data
1144 , p_organization_id => p_org_id
1145 , p_outermost_lpn_id => p_lpn_id
1146 );
1147
1148 IF (l_debug = 1) THEN
1149 trace('Check for catch wt: l_catch_wt_check ::'||l_catch_wt_check||'::return_status::'||x_return_status);
1150 END IF;
1151
1152 IF l_catch_wt_check <> 0 THEN
1153 IF (l_debug = 1) THEN
1154 trace('catch wt validation failed');
1155 END IF;
1156 FND_MESSAGE.SET_NAME('WMS', 'WMS_CTWT_DEFAULT_ERROR');
1157 FND_MSG_PUB.ADD;
1158 RAISE FND_API.G_EXC_ERROR;
1159
1160 END IF;
1161
1162
1163 --make sure that the LPN does not have any Serial item which is defined as
1164 --"Serials at SO issue
1165 begin
1166 SELECT 1 INTO l_serial_at_issue FROM dual WHERE exists
1167 (SELECT wlpn.lpn_id
1168 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn, mtl_system_items msi
1169 WHERE wlpn.lpn_id = wlc.parent_lpn_id
1170 AND msi.inventory_item_id = wlc.inventory_item_id
1171 AND msi.organization_id = wlc.organization_id
1172 and wlc.organization_id = p_org_id
1173 AND MSI.SERIAL_NUMBER_CONTROL_CODE = 6 --Serials at SO issue
1174 AND wlpn.outermost_lpn_id = p_lpn_id
1175 AND wlpn.organization_id = p_org_id
1176 );
1177 EXCEPTION
1178 WHEN no_data_found THEN
1179 l_serial_at_issue := 0;
1180 END;
1181
1182 --l_serial_at_issue := 0;
1183 IF (l_debug = 1) THEN
1184 trace('l_serial_at_issue::'||l_serial_at_issue);
1185 END IF;
1186
1187
1188 IF l_serial_at_issue = 1 THEN
1189 -- it should return error not possible to process through RFID Txn
1190 IF (l_debug = 1) THEN
1191 trace('Error: LPN contains Serials at SO Issue');
1192 END IF;
1193 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_SERIAL_ISSUE');
1194 FND_MSG_PUB.ADD;
1195 RAISE FND_API.G_EXC_ERROR;
1196
1197 END IF;
1198
1199
1200 IF (l_debug = 1) THEN
1201 trace('Processing direct truck load');
1202 END IF;
1203
1204
1205 INV_PROJECT.SET_SESSION_PARAMETERS(l_return_status,l_msg_count,l_msg_data,p_org_id);
1206
1207 IF (l_debug = 1) THEN
1208 trace('after setting::'||l_return_status||':'||l_msg_data||':'||l_msg_count);
1209 trace('calling wms_direct_ship_pvt.process_lpn');
1210 END IF;
1211
1212
1213 -- Load the LPN on the dock door
1214 wms_direct_ship_pvt.process_lpn
1215 (p_lpn_id => p_lpn_id,
1216 p_org_id => p_org_id,
1217 p_dock_door_id => p_dock_door_id,
1218 x_remaining_qty => l_remaining_qty,
1219 x_num_line_processed => l_num_line_processed,
1220 x_project_id => l_project_id,
1221 x_task_id => l_task_id,
1222 x_cross_project_allowed => l_cross_project_allowed,
1223 x_cross_unit_allowed => l_cross_unit_allowed,
1224 x_group_by_customer_flag => l_group_by_customer_flag,
1225 x_group_by_fob_flag => l_group_by_fob_flag,
1226 x_group_by_freight_terms_flag => l_group_by_freight_terms_flag,
1227 x_group_by_intmed_ship_flag => l_group_by_intmed_ship_flag,
1228 x_group_by_ship_method_flag => l_group_by_ship_method_flag,
1229 x_group_by_ship_to_loc_value => l_group_by_ship_to_loc_value,
1230 x_group_by_ship_from_loc_value => l_group_by_ship_from_loc_value,
1231 x_group_by_customer_value => l_group_by_customer_value,
1232 x_group_by_fob_value => l_group_by_fob_value,
1233 x_group_by_freight_terms_value => l_group_by_freight_terms_value,
1234 x_group_by_intmed_value => l_group_by_intmed_value,
1235 x_group_by_ship_method_value => l_group_by_ship_method_value,
1236 x_ct_wt_enabled => l_ct_wt_enabled,
1237 x_return_status => l_return_status,
1238 x_msg_count => l_msg_data,
1239 x_msg_data => l_msg_count
1240 );
1241
1242 IF (l_debug = 1) THEN
1243 trace('Process_lpn returned l_return_status::l_remaining_qty::'||l_return_status||'::'||l_remaining_qty);
1244 END IF;
1245
1246 IF l_return_status IS NULL OR l_return_status = 'E' OR
1247 l_return_status = 'U' THEN
1248 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_TRUCK_LOAD_FAIL');
1249 FND_MSG_PUB.ADD;
1250 RAISE FND_API.G_EXC_ERROR;
1251
1252 ELSE
1253
1254 if l_remaining_qty <> 0 then
1255 --LPN should be totally consumed for RFID transactions,so fail the transaction
1256 IF (l_debug = 1) THEN
1257 trace('ERROR: LPN not Totally Consumed');
1258 END IF;
1259 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LPN_NOT_CONSUMED');
1260 FND_MSG_PUB.ADD;
1261 RAISE FND_API.G_EXC_ERROR;
1262
1263 else
1264 IF (l_debug = 1) THEN
1265 trace('Load the LPN');
1266 END IF;
1267
1268 wms_direct_ship_pvt.Load_LPN
1269 ( x_return_status => l_return_status,
1270 x_msg_count => l_msg_count,
1271 x_msg_data => l_msg_data,
1272 p_lpn_id => p_lpn_id,
1273 p_org_id => p_org_id,
1274 p_dock_door_id => p_dock_door_id);
1275
1276 IF (l_debug = 1) THEN
1277 trace('After Loading the LPN');
1278 trace(l_group_by_ship_method_value||':'||l_return_status||':'||l_msg_data||':'||l_msg_count);
1279 END IF;
1280
1281 IF l_return_status IS NULL OR l_return_status = 'E' OR
1282 l_return_status = 'U' THEN
1283 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_TRUCK_LOAD_FAIL');
1284 FND_MSG_PUB.ADD;
1285 RAISE FND_API.G_EXC_ERROR;
1286
1287 ELSE
1288 IF (l_debug = 1) THEN
1289 trace('Load LPN success: Checking whether the last LPN for truck Load only');
1290 END IF;
1291
1292 --check it only for Truck Load Business event and not for Load_SHIP
1293 l_is_last_lpn_load := is_last_lpn_load(p_lpn_id);
1294
1295 IF l_is_last_lpn_load = 2 THEN
1296 IF (l_debug = 1) THEN
1297 trace('last LPN to be loaded for delivery');
1298 END IF;
1299
1300 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LAST_LPN_DELIVERY');
1301 FND_MSG_PUB.ADD;
1302 ELSIF l_is_last_lpn_load = 3 THEN
1303 IF (l_debug = 1) THEN
1304 trace('last LPN to be loaded for trip');
1305 END IF;
1306 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LAST_LPN_TRIP');
1307 FND_MSG_PUB.ADD;
1308 ELSE
1309 IF (l_debug = 1) THEN
1310 trace('Truck Load Successful');
1311 END IF;
1312 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_TRUCK_LOAD_SUCCESS');
1313 FND_MSG_PUB.ADD;
1314
1315 END IF;
1316
1317 --Must Commit here
1318 Commit;
1319
1320 END IF;--success from Load_LPN
1321
1322 END IF;--l_remaining_qty <> 0
1323
1324 END IF;--for Process_lpn success
1325
1326 x_is_last_lpn_load := l_is_last_lpn_load;
1327
1328 exception
1329 when OTHERS then
1330 x_return_status := 'E';
1331 IF (l_debug = 1) THEN
1332 trace('Other errror: process_direct_truck_load');
1333 trace('SQL error :'||substr(sqlerrm, 1, 240));
1334 END IF;
1335
1336 END process_direct_truck_load;
1337
1338
1339
1340 PROCEDURE process_direct_truck_load_ship( p_lpn_id IN NUMBER,
1341 p_org_id IN NUMBER,
1342 p_dock_door_id IN NUMBER,
1343 x_return_status OUT NOCOPY VARCHAR2)
1344 IS
1345 l_return_status VARCHAR2(1);
1346 l_msg_count NUMBER;
1347 l_msg_data VARCHAR2(2000);
1348 l_delivery_id NUMBER;
1349 l_trip_id NUMBER;
1350 l_error_code NUMBER;
1351 l_vehicle_item_id NUMBER;
1352 l_vehicle_num_prefix VARCHAR2(30);
1353 l_vehicle_num VARCHAR2(30);
1354 l_seal_code VARCHAR2(30);
1355 l_document_set_id NUMBER;
1356 l_missing_item_cur t_genref;
1357
1358 l_is_last_lpn_load NUMBER;
1359
1360 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1361
1362 BEGIN
1363 IF (l_debug = 1) THEN
1364 trace('Inside process_direct_truck_load_ship');
1365 END IF;
1366
1367 x_return_status := FND_API.g_ret_sts_success;
1368
1369 process_direct_truck_load( p_lpn_id => p_lpn_id,
1370 p_org_id => p_org_id,
1371 p_dock_door_id => p_dock_door_id,
1372 x_is_last_lpn_load => l_is_last_lpn_load,
1373 x_return_status => l_return_status);
1374
1375
1376 IF (l_debug = 1) THEN
1377 trace('After calling process_direct_truck_load l_return_status::'||l_return_status);
1378 END IF;
1379
1380 IF l_return_status IS NULL OR l_return_status = 'E' OR l_return_status ='U' THEN
1381
1382 RAISE FND_API.G_EXC_ERROR;
1383 IF (l_debug = 1) THEN
1384 trace('process_direct_truck_load_ship::Truck Load failed');
1385 END IF;
1386
1387 ELSE
1388
1389 IF (l_debug = 1) THEN
1390 trace('Inside process_direct_truck_load_ship:: Truck Load Successful');
1391 END IF;
1392
1393
1394 --Checking whether the last LPN for delivery or Trip (if exists)
1395
1396 IF l_is_last_lpn_load IN (2,3) THEN
1397 IF (l_debug = 1) THEN
1398 trace('Last LPN in the trip or delivery: Calling ship confirm');
1399 END IF;
1400
1401 WMS_SHIPPING_TRANSACTION_PUB.close_truck
1402 ( P_dock_door_id => p_dock_door_id,
1403 P_organization_id => p_org_id,
1404 p_shipping_mode => 'DIRECT',
1405 p_commit_flag => fnd_api.g_false,
1406 x_return_status => l_return_status,
1407 x_return_msg => l_msg_data
1408 );
1409
1410 IF (l_debug = 1) THEN
1411 trace('process_direct_truck_load_ship l_return_status :'||l_return_status);
1412 END IF;
1413
1414 fnd_msg_pub.Count_And_Get
1415 (p_encoded => FND_API.g_false,
1416 p_count => l_msg_count,
1417 p_data => l_msg_data
1418 );
1419
1420 IF l_msg_count > 1 THEN
1421 FOR i IN 1..l_msg_count LOOP
1422 l_ship_confirm_pkg_mesg := substr((l_msg_data || '|' || FND_MSG_PUB.GET(p_msg_index => l_msg_count - i + 1, p_encoded => FND_API.g_false)),1,240);
1423 END LOOP;
1424 ELSE
1425
1426 l_ship_confirm_pkg_mesg :=substr(l_msg_data,1,240);
1427
1428 END IF;
1429
1430 fnd_msg_pub.delete_msg;
1431
1432
1433 IF (l_debug = 1) THEN
1434 trace('process_direct_truck_load_ship SHP_msg_data ::'||l_ship_confirm_pkg_mesg);
1435 END IF;
1436
1437 if x_return_status in ('S','W') THEN
1438
1439 COMMIT;
1440
1441 IF (l_debug = 1) THEN
1442 trace('process_direct_truck_load_ship return Success/Warning: Commit Done');
1443 END IF;
1444
1445 --do not need to update the msg here, it is already in the stack
1446 --l_msg_data is returned as nulll
1447
1448 END IF;
1449
1450 ELSE
1451 IF (l_debug = 1) THEN
1452 trace('process_direct_truck_load_ship: Not the last LPN in the trip or delivery: DO not Ship Confirm');
1453 END IF;
1454 l_ship_confirm_pkg_mesg :=fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
1455 END IF;
1456
1457 END IF;
1458
1459 exception
1460 when OTHERS then
1461 x_return_status := 'E';
1462 IF (l_debug = 1) THEN
1463 trace('Other errror: process_direct_truck_load_ship');
1464 trace('SQL error :'||substr(sqlerrm, 1, 240));
1465 END IF;
1466 END process_direct_truck_load_ship;
1467
1468
1469
1470
1471 PROCEDURE process_rfid_receiving_txn(p_lpn_id IN NUMBER,
1472 p_device_id IN NUMBER,
1473 p_dest_org_id IN NUMBER,
1474 p_lpn_context IN NUMBER,
1475 p_routing_id IN NUMBER,
1476 p_shipment_header_id IN NUMBER,
1477 p_direct_putaway_sub IN VARCHAR2,
1478 p_direct_putaway_loc IN NUMBER,
1479 x_return_status OUT nocopy VARCHAR2)
1480 IS
1481
1482 l_return_status VARCHAR2(1);
1483 l_msg_count NUMBER;
1484 l_msg_data VARCHAR2(250);
1485 l_move_order_header_id number;
1486 l_lot_ser_flag VARCHAR2(1) := NULL;
1487 l_inspect NUMBER;
1488 l_shipment_header_id NUMBER := p_shipment_header_id;
1489 l_org_id number;
1490 l_org_location VARCHAR2(60);
1491 l_org_locator_control NUMBER;
1492 l_manual_po_num_type VARCHAR2(25);
1493 l_wms_install_status VARCHAR2(1);
1494 l_wms_purchased VARCHAR2(1);
1495 l_receipt_num VARCHAR2(30);
1496 l_shipment_num VARCHAR2(30);
1497 l_source_type VARCHAR2(30);
1498 l_shipment_hdr_id_dummy NUMBER;
1499 l_vendor_id NUMBER;
1500
1501 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1502 BEGIN
1503
1504 IF (l_debug = 1) THEN
1505 trace('process_rfid_receiving_txn:l_shipment_header_id:p_routing_id:p_lpn_id:p_lpn_context');
1506 trace('process_rfid_receiving_txn:'||l_shipment_header_id||'::'||p_routing_id||'::'||p_lpn_id||'::'|| p_lpn_context);
1507 END IF;
1508
1509 x_return_status := FND_API.g_ret_sts_success;
1510
1511 if p_routing_id IS NOT NULL THEN
1512
1513 if p_routing_id = 2 then --inspection
1514 l_inspect := 1;
1515 end if;
1516
1517 IF (l_debug = 1) THEN
1518 trace('calling inv_rcv_common_apis.init_startup_values ');
1519 END IF;
1520
1521 inv_rcv_common_apis.init_rcv_ui_startup_values
1522 ( p_organization_id => p_dest_org_id, --destination_org
1523 x_org_id => l_org_id,
1524 x_org_location => l_org_location,
1525 x_org_locator_control => l_org_locator_control,
1526 x_manual_po_num_type => l_manual_po_num_type,
1527 x_wms_install_status => l_wms_install_status,
1528 x_wms_purchased => l_wms_purchased,
1529 x_return_status => l_return_status,
1530 x_msg_data => l_msg_data );
1531
1532 IF (l_debug = 1) THEN
1533 trace('process_rfid_receiving_txn:l_org_id,l_org_location,l_org_locator_control,l_return_status,l_msg_data');
1534 trace(l_org_id||'::'||l_org_location||'::'||l_org_locator_control||'::'||l_return_status||'::'||l_msg_data);
1535 END IF;
1536
1537
1538 --`Check for lot serial flag between both org
1539
1540 INV_RCV_COMMON_APIS.check_lot_serial_codes
1541 (
1542 p_lpn_id => p_lpn_id,
1543 p_req_header_id => null,
1544 p_shipment_header_id => l_shipment_header_id,
1545 x_lot_ser_flag => l_lot_ser_flag,
1546 x_return_status => l_return_status,
1547 x_msg_count => l_msg_count,
1548 x_msg_data => l_msg_data);
1549 IF (l_debug = 1) THEN
1550 trace('process_rfid_receiving_txn:l_lot_ser_flag,l_return_status,l_msg_data');
1551 trace(l_lot_ser_flag||'::'||l_return_status||'::'||l_msg_data);
1552 END IF;
1553
1554 IF l_lot_ser_flag = 'N' THEN
1555 IF (l_debug = 1) THEN
1556 trace('l_lot_ser_flag is N');
1557 END IF;
1558
1559 --Fail the transaction, it can not be performed by RFID
1560 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_ITEM_CTRL_ERROR');
1561 FND_MSG_PUB.ADD;
1562 RAISE FND_API.G_EXC_ERROR;
1563
1564 ELSE
1565 IF (l_debug = 1) THEN
1566 trace('process_rfid_receiving_txn:l_lot_ser_code test passed');
1567 END IF;
1568
1569 IF p_routing_id in (1,2) then
1570
1571 IF p_lpn_context = 6 THEN
1572 l_source_type := 'REQEXP';--Internal requisition
1573 ELSIF p_lpn_context = 7 THEN
1574 l_source_type := 'ASNEXP';-- ASN
1575 END IF;
1576
1577 IF (l_debug = 1) THEN
1578 trace('process_rfid_receiving_txn: Standard routing/inspection,insert into interface TABLE');
1579 trace('l_move_order_header_id :'||l_move_order_header_id);
1580 trace('p_dest_org_id :'|| p_dest_org_id);
1581 trace('l_shipment_header_id :'||l_shipment_header_id);
1582 trace('ASN/Internal Req l_source_type'||l_source_type);
1583 trace('p_lpn_id :'||p_lpn_id);
1584 trace('l_inspect :'||l_inspect);
1585 trace('p_direct_putaway_sub :'||p_direct_putaway_sub);
1586 trace('p_direct_putaway_loc :'||p_direct_putaway_loc);
1587 END IF;
1588
1589
1590 INV_RCV_STD_RCPT_APIS.create_std_rcpt_intf_rec
1591 (
1592 p_move_order_header_id => l_move_order_header_id,
1593 p_organization_id => p_dest_org_id,-- destination Org
1594 p_po_header_id => null,
1595 p_po_release_number_id => null,
1596 p_po_line_id => null,
1597 p_shipment_header_id => l_shipment_header_id,
1598 p_req_header_id => null,
1599 p_oe_order_header_id => null,
1600 p_item_id => null,
1601 p_location_id => null,--it defaults from the backend
1602 p_rcv_qty => null,
1603 p_rcv_uom => null,
1604 p_rcv_uom_code => null,
1605 p_source_type => l_source_type,
1606 p_from_lpn_id => p_lpn_id,
1607 p_lpn_id => NULL,
1608 p_lot_control_code => null,
1609 p_revision => null,
1610 p_inspect => l_inspect,
1611 p_rcv_subinventory_code => p_direct_putaway_sub,
1612 p_rcv_locator_id => p_direct_putaway_loc,
1613 x_status => l_return_status,
1614 x_message => l_msg_data
1615 );
1616
1617
1618 IF (l_debug = 1) THEN
1619 trace('process_rfid_receiving_txn: l_return_status, l_msg_data, l_move_order_header_id');
1620 trace('process_rfid_receiving_txn:'||l_return_status||'::'||l_msg_data||'::'||l_move_order_header_id);
1621 END IF;
1622
1623 elsif p_routing_id = 3 then
1624
1625 IF p_lpn_context = 6 THEN
1626 l_source_type := 'REQEXP';
1627 ELSIF p_lpn_context = 7 THEN
1628 l_source_type := 'ASNEXP';
1629 END IF;
1630
1631 IF (l_debug = 1) THEN
1632 trace('process_rfid_receiving_txn: Direct routing, insert into interface table');
1633 trace('l_move_order_header_id :'||l_move_order_header_id);
1634 trace('p_dest_org_id :'|| p_dest_org_id);
1635 trace('l_shipment_header_id :'||l_shipment_header_id);
1636 trace('ASN/Internal Req l_source_type'||l_source_type);
1637 trace('p_direct_putaway_sub :'||p_direct_putaway_sub);
1638 trace('p_direct_putaway_loc :'||p_direct_putaway_loc);
1639 trace('p_lpn_id :'||p_lpn_id);
1640 END IF;
1641
1642 inv_rcv_dir_rcpt_apis.create_direct_rti_rec
1643 (
1644 p_move_order_header_id => l_move_order_header_id,
1645 p_organization_id => p_dest_org_id ,-- destination Org
1646 p_po_header_id => NULL,
1647 p_po_release_id => NULL,
1648 p_po_line_id => NULL,
1649 p_shipment_header_id => l_shipment_header_id,
1650 p_oe_order_header_id => NULL,
1651 p_item_id => NULL,
1652 p_rcv_qty => NULL,
1653 p_rcv_uom => NULL,
1654 p_rcv_uom_code => NULL,
1655 p_source_type => l_source_type,
1656 p_subinventory => p_direct_putaway_sub,
1657 p_locator_id => p_direct_putaway_loc,
1658 p_transaction_temp_id => NULL,
1659 p_lot_control_code => NULL,
1660 p_serial_control_code => NULL,
1661 p_lpn_id => p_lpn_id,
1662 p_revision => NULL,
1663 x_status => l_return_status,
1664 x_message => l_msg_data);
1665
1666
1667 IF (l_debug = 1) THEN
1668 trace('process_rfid_receiving_txn: l_return_status, l_msg_data, l_move_order_header_id');
1669 trace('process_rfid_receiving_txn:'||l_return_status||'::'||l_msg_data||'::'||l_move_order_header_id);
1670 END IF;
1671
1672 END IF;
1673
1674
1675 IF (l_debug = 1) THEN
1676 trace('Calling rcv_gen_receipt_num');
1677 END IF;
1678
1679 inv_rcv_common_apis.rcv_gen_receipt_num(
1680 x_receipt_num => l_receipt_num,
1681 p_organization_id => p_dest_org_id,--destination Org
1682 x_return_status => l_return_status,
1683 x_msg_count => l_msg_count,
1684 x_msg_data => l_msg_data);
1685
1686 IF (l_debug = 1) THEN
1687 trace('process_rfid_receiving_txn: l_receipt_num,l_return_status, l_msg_data, l_msg_count');
1688 trace('process_rfid_receiving_txn:'||l_receipt_num||'::'||l_return_status||'::'||l_msg_data||'::'||l_msg_count);
1689 END IF;
1690
1691 IF l_return_status IS NULL OR l_return_status = 'E' THEN
1692
1693 IF (l_debug = 1) THEN
1694 trace('process_rfid_receiving_txn: Error: Rcpt generation failed');
1695 END IF;
1696 RAISE FND_API.G_EXC_ERROR;
1697
1698 ELSE
1699
1700 IF (l_debug = 1) THEN
1701 trace('process_rfid_receiving_txn: Calling rcv_insert_update_header');
1702 END IF;
1703
1704 IF p_lpn_context = 6 THEN
1705 l_source_type :='INTERNAL ORDER';--Internal requisition
1706 ELSIF p_lpn_context = 7 THEN
1707 l_source_type := 'VENDOR';--ASN
1708 END IF;
1709
1710 SELECT shipment_num,vendor_id
1711 INTO l_shipment_num,l_vendor_id
1712 FROM rcv_shipment_headers
1713 WHERE shipment_header_id = l_shipment_header_id;
1714
1715 IF (l_debug = 1) THEN
1716 trace('process_rfid_receiving_txn: ASN/Inernal req l_source_type'|| l_source_type);
1717 trace('p_dest_org_id :'||p_dest_org_id);
1718 trace('l_source_type :'||l_source_type);
1719 trace('l_receipt_num :'||l_receipt_num);
1720 trace('l_shipment_num :'||l_shipment_num);
1721 trace('l_vendor_id :'||l_vendor_id);
1722 END IF;
1723
1724 l_shipment_hdr_id_dummy := NULL;
1725
1726 INV_RCV_STD_RCPT_APIS.rcv_insert_update_header
1727 (p_organization_id => p_dest_org_id, --destination Org ,
1728 p_shipment_header_id => l_shipment_hdr_id_dummy, --IN OUT parameter
1729 p_source_type => l_source_type,
1730 p_receipt_num => l_receipt_num,
1731 p_vendor_id => l_vendor_id,
1732 p_vendor_site_id => null,
1733 p_shipment_num => l_shipment_num,
1734 p_ship_to_location_id => null,
1735 p_bill_of_lading => null,
1736 p_packing_slip => null,
1737 p_shipped_date => null,
1738 p_freight_carrier_code => null,
1739 p_expected_receipt_date => null,
1740 p_num_of_containers => null,
1741 p_waybill_airbill_num => null,
1742 p_comments => null,
1743 p_ussgl_transaction_code => null,
1744 p_government_context => null,
1745 p_request_id => null,
1746 p_program_application_id => null,
1747 p_program_id => null,
1748 p_program_update_date => null,
1749 p_customer_id => null,
1750 p_customer_site_id => null,
1751 x_return_status => l_return_status,
1752 x_msg_count => l_msg_count,
1753 x_msg_data => l_msg_data
1754 );
1755
1756
1757
1758 IF (l_debug = 1) THEN
1759 trace('process_rfid_receiving_txn: l_return_status,l_msg_data::'||l_return_status||'::'||l_msg_data);
1760 END IF;
1761
1762 if l_return_status IS NULL OR l_return_status = 'E' THEN
1763
1764 IF (l_debug = 1) THEN
1765 trace('process_rfid_receiving_txn: Error: rcv_insert_update_header failed');
1766 END IF;
1767 RAISE FND_API.G_EXC_ERROR;
1768
1769 ELSE
1770
1771 IF (l_debug = 1) THEN
1772 trace('process_rfid_receiving_txn: Calling RCV TM');
1773 END IF;
1774
1775
1776 INV_RCV_MOBILE_PROCESS_TXN.rcv_process_receive_txn
1777 ( x_return_status => l_return_status,
1778 x_msg_data => l_msg_data
1779 );
1780
1781
1782 IF (l_debug = 1) THEN
1783 trace('process_rfid_receiving_txn: l_return_status, l_msg_data::'||l_return_status||'::'||l_msg_data);
1784 END IF;
1785
1786 inv_rcv_common_apis.rcv_clear_global;
1787
1788 IF l_return_status = 'S' THEN
1789 FND_MESSAGE.SET_NAME('WMS', 'WMS_TXN_SUCCESS');
1790 FND_MSG_PUB.ADD;
1791 END IF;
1792
1793 x_return_status := l_return_status;
1794
1795 end if;--INSERT UPDATE HEADR
1796
1797 END IF; --RCPT GENERATION
1798
1799 end if; --l_lot_ser_flag
1800
1801 end if; --p_routing_id is not null
1802
1803 IF (l_debug = 1) THEN
1804 trace('RETURNING::'||x_return_status);
1805 END IF;
1806
1807 exception
1808 when OTHERS THEN
1809 IF l_lot_ser_flag = 'N' THEN --return other status to avoid overwrite
1810 --OF standard RCV failure mesg
1811 x_return_status := 'N';
1812 ELSE
1813 x_return_status := 'E';
1814 END IF;
1815
1816 IF (l_debug = 1) THEN
1817 trace('Other errror: process_rfid_receiving_txn');
1818 trace('SQL error :'||substr(sqlerrm, 1, 240));
1819 END IF;
1820
1821 END process_rfid_receiving_txn;
1822
1823
1824
1825 PROCEDURE parse_read_tags(p_tagid IN WMS_EPC_TAGID_TYPE,
1826 p_org_id IN NUMBER,
1827 x_tag_info OUT nocopy tag_info_tbl,
1828 x_pallet_lpn_id OUT nocopy NUMBER,
1829 x_pallet_lpn_context OUT nocopy NUMBER,
1830 x_tag_count OUT nocopy NUMBER,
1831 x_return_status out nocopy VARCHAR2) --S/E
1832 IS
1833
1834
1835 l_PARENT_lpn_id NUMBER;
1836 --l_tag_info tag_info_tbl;
1837 l_index NUMBER;
1838 l_parent_row_id NUMBER;
1839 L_pallet_lpn_context NUMBER;
1840 l_outermost_lpn_id NUMBER;
1841 l_is_error NUMBER := 0;
1842 l_prev_serial_pallet_id NUMBER;
1843 l_serial_pallet_id NUMBER;
1844 l_lpn_pallet_id NUMBER;
1845 l_prev_lpn_pallet_id NUMBER;
1846 l_gtin NUMBER;
1847 l_gtin_serial NUMBER;
1848
1849 l_cnt NUMBER;
1850
1851 l_cross_ref_type NUMBER;
1852 l_lpn_id NUMBER;
1853 l_item_id NUMBER;
1854 l_serial_number VARCHAR2(30);
1855
1856
1857 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1858 BEGIN
1859
1860
1861 x_return_status := 'S';
1862
1863 IF (l_debug = 1) THEN
1864 trace('Inside parse_read_tags');
1865 END IF;
1866
1867 ---get all the values from the tagid LOV and tagdata LOV
1868
1869
1870 IF p_tagid IS NOT null THEN
1871
1872 l_index := 1;
1873
1874 IF (l_debug = 1) THEN
1875 trace('Get all values of the EPC tags ######');
1876 END IF;
1877
1878 FOR i IN p_tagid.FIRST .. p_tagid.LAST
1879 LOOP
1880 IF (l_debug = 1) THEN
1881 trace('Tag values = ' || p_tagid(i));
1882 END IF;
1883
1884 x_tag_info(l_index).tag_id := to_CHAR(Ltrim(Rtrim(p_tagid(i))));
1885 l_index := l_index +1;
1886
1887 END LOOP;
1888
1889 --Return the tag count
1890 x_tag_count := x_tag_info.COUNT;
1891
1892 IF (l_debug = 1) THEN
1893 trace (' x_tag_count :'|| x_tag_count);
1894 END IF;
1895
1896
1897
1898 IF x_tag_count = 1 THEN
1899
1900 -- It can be LPN Name String OR EPC
1901
1902 BEGIN
1903 SELECT wlpn.lpn_id,wlpn.lpn_context,outermost_lpn_id INTO
1904 x_pallet_lpn_id,x_pallet_lpn_context,l_outermost_lpn_id
1905 FROM wms_license_plate_numbers wlpn,
1906 wms_epc we
1907 WHERE we.lpn_id = wlpn.lpn_id
1908 AND we.cross_ref_type =1 --LPN-EPC type
1909 AND we.epc = x_tag_info(1).tag_id
1910 AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND wlpn.parent_lpn_id IS NOT null) OR
1911 ( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
1912
1913 --process only the pallet LPN
1914 IF l_outermost_lpn_id <> x_pallet_lpn_id THEN
1915
1916 IF (l_debug = 1) THEN
1917 trace('parse_read_tags : read EPC must be outer LPN');
1918 END IF;
1919
1920 x_pallet_lpn_id := NULL;
1921 x_pallet_lpn_context := NULL;
1922 x_return_status := 'E';
1923 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_INNER_LPN_READ');--Already seeded
1924 FND_MSG_PUB.ADD;
1925
1926 END IF;
1927
1928
1929 EXCEPTION
1930 WHEN no_data_found THEN
1931
1932 --this value is not in the the cross reference table, try to see
1933 --IF the passed value is lpn name string instead
1934
1935 BEGIN
1936 SELECT wlpn.lpn_id,wlpn.lpn_context,wlpn.outermost_lpn_id INTO
1937 x_pallet_lpn_id,x_pallet_lpn_context,l_outermost_lpn_id
1938 FROM wms_license_plate_numbers wlpn
1939 WHERE wlpn.license_plate_number = x_tag_info(1).tag_id
1940 AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND wlpn.parent_lpn_id IS NOT null) OR
1941 ( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
1942
1943
1944 --process only the pallet LPN
1945 --Be it Shipping or Receiving only the outer LPN is needed
1946
1947 --Receiving can take inner LPN for receiving
1948 --partially though, so we have TO stop it here
1949
1950 --However Shipping does not support processing inner
1951 --lpn, so we have to discard those reads
1952
1953 IF l_outermost_lpn_id <> x_pallet_lpn_id THEN
1954
1955 IF (l_debug = 1) THEN
1956 trace('parse_read_tags : read LPN must be outer LPN');
1957 END IF;
1958
1959 x_pallet_lpn_id := NULL;
1960 x_pallet_lpn_context := NULL;
1961 x_return_status := 'E';
1962 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_INNER_LPN_READ');
1963 FND_MSG_PUB.ADD;
1964
1965 END IF;
1966
1967 EXCEPTION
1968 WHEN no_data_found THEN
1969 x_pallet_lpn_id := NULL;
1970 x_pallet_lpn_context := NULL;
1971 x_return_status := 'E';
1972 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
1973 FND_MSG_PUB.ADD;
1974
1975 RETURN;
1976
1977 END;
1978
1979 END;
1980
1981 ELSE --means x_tag_count > 1
1982 --Assumption: if it is coming as list of values using pallet filter from
1983 --edgeserver, it has to be list of EPCs and not list of LPN Names strings
1984
1985 l_prev_serial_pallet_id := -99;
1986 l_prev_lpn_pallet_id := -99;
1987
1988
1989 FOR i IN 1..x_tag_info.COUNT LOOP
1990
1991 --CODE LOGIC
1992 --1- get the cross_ref_type and all relevant value for
1993 --cross-reference from wms_epc table based on EPC
1994 --2-Query respective tables to ensure that cross-referenced
1995 --object IS indeed correct and get other relevant parameter values
1996
1997
1998 BEGIN
1999
2000 SELECT cross_ref_type , lpn_id , inventory_item_id, serial_number, gtin, gtin_serial
2001 INTO l_cross_ref_type , l_lpn_id , l_item_id, l_serial_number, l_gtin, l_gtin_serial
2002 FROM wms_epc we
2003 WHERE we.epc = x_tag_info(i).tag_id;
2004
2005
2006
2007 IF (l_debug = 1) THEN
2008 trace('l_cross_ref_type :'||l_cross_ref_type );
2009 trace('l_lpn_id :'||l_lpn_id);
2010 trace('l_item_id :'||l_item_id );
2011 trace('l_serial_number :'||l_serial_number);
2012 trace('l_gtin_serial :'||l_gtin_serial);
2013
2014 END IF;
2015
2016
2017 EXCEPTION
2018 WHEN no_data_found THEN
2019
2020 --Extraneous read, NOT a cross-referenced EPC read
2021
2022 IF (l_debug = 1) THEN
2023 trace('when no data found');
2024 END IF;
2025
2026
2027 x_pallet_lpn_id := NULL;
2028 x_pallet_lpn_context := NULL;
2029 x_return_status := fnd_api.g_ret_sts_error;
2030 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_READ');
2031 FND_MSG_PUB.ADD;
2032
2033 l_is_error :=1;
2034 EXIT; --exit the loop
2035
2036 WHEN too_many_rows THEN
2037 --THIS SHOULD NEVER HAPPEN AS EPC IS UNIQUE COLUMN IN THE TABLE
2038 x_pallet_lpn_id := NULL;
2039 x_pallet_lpn_context := NULL;
2040 x_return_status := fnd_api.g_ret_sts_error;
2041 FND_MESSAGE.SET_NAME('WMS', 'WMS_DUPLICATE_EPC');
2042 FND_MSG_PUB.ADD;
2043
2044 l_is_error :=1;
2045 EXIT; --exit the loop
2046 WHEN OTHERS THEN
2047 IF (l_debug = 1) THEN
2048 trace('WHEN OTHERS EXCEPTION OF QUERY ........');
2049 END IF;
2050
2051 x_pallet_lpn_id := NULL;
2052 x_pallet_lpn_context := NULL;
2053 x_return_status := fnd_api.g_ret_sts_error;
2054 FND_MESSAGE.SET_NAME('WMS', 'WMS_UNEXPECTED_ERR');
2055 FND_MSG_PUB.ADD;
2056
2057 l_is_error :=1;
2058
2059 EXIT; --exit the loop
2060
2061 END;
2062
2063
2064
2065 --2-see whether it is cross-referenced to LPN, serial_number or
2066 --to GTIN
2067
2068 IF l_cross_ref_type = 1 OR l_cross_ref_type IS NULL THEN
2069 --LPN-EPC cross reference
2070 --NULL TO SUPPORT OLD VALUES OF 11.5.10
2071 BEGIN
2072 -- This will match only for pallets and cases
2073
2074 SELECT wlpn.parent_lpn_id, wlpn.LPN_CONTEXT INTO
2075 l_parent_lpn_id,l_PALLET_LPN_CONTEXT
2076 FROM wms_license_plate_numbers wlpn
2077 WHERE wlpn.lpn_id = L_LPN_ID
2078 AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND
2079 wlpn.parent_lpn_id IS NOT null)
2080 OR
2081 ( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
2082 EXCEPTION
2083 WHEN no_data_found THEN
2084
2085 --handle it but do not do anything here. leave it.In the verification part because
2086 --lpn_id, parent_lpn_id, item_id , serial_number,
2087 --GTIN ALL WILL BE null for
2088 --that entry in the x_tag_info pl/sql table,
2089
2090 NULL;
2091 END;
2092 --find if multiple pallet. irrespective of whether user
2093 --wants the verification OR NOT there should NOT be two
2094 --pallets IN the list OF EPCs.
2095 --Also if the somehow the pallet did not get read but if
2096 --ALL cases point TO the same Pallet, it should still PASS
2097
2098
2099 IF l_parent_lpn_id IS NULL THEN --this is pallet
2100 l_lpn_pallet_id := l_lpn_id;
2101 ELSE --this IS case
2102 l_lpn_pallet_id := l_parent_lpn_id;
2103 END IF;
2104
2105 IF l_lpn_pallet_id <> l_prev_lpn_pallet_id AND l_prev_lpn_pallet_id <> -99 THEN--this is to ensure this
2106
2107 --Error;
2108
2109 --Cases were read before the pallet and the Pallet
2110 --does not match WITH parent LPN of cases
2111 -------------------OR----------------
2112 -- Case belongs to multiple Pallet or there are
2113 --multiple Pallets
2114
2115 IF (l_debug = 1) THEN
2116 trace('Error: Multiple Pallets OR Cases belongs to multiple Pallets');
2117 END IF;
2118 x_return_status := 'E';
2119 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_MULTIPLE_PALLET');
2120 FND_MSG_PUB.ADD;
2121
2122 l_is_error := 1;
2123 EXIT; --exit the loop
2124
2125 END IF;
2126
2127
2128 l_prev_lpn_pallet_id := l_lpn_pallet_id;
2129
2130 x_tag_info(i).lpn_id := l_lpn_id;
2131 x_tag_info(i).parent_lpn_id := l_parent_lpn_id;
2132
2133
2134 ELSIF l_cross_ref_type = 2 THEN
2135
2136 --Serial_Number-EPC cross reference
2137 --Serial can be inside inner-pack (modeled as LPN) also
2138 --with NO tagging of inner-pack LPN so more than 1 level of
2139 --nesting and still valid scenario
2140
2141 BEGIN
2142 select MSN.LPN_ID,WLPN.outermost_LPN_ID
2143 INTO l_lpn_id, l_serial_pallet_id
2144 from mtl_serial_numbers MSN,
2145 WMS_LICENSE_PLATE_NUMBERS wlpn
2146 WHERE MSN.inventory_item_id = l_item_id
2147 AND MSN.serial_number = l_SERIAL_NUMBER
2148 AND MSN.lpn_id = WLPN.LPN_ID;
2149 EXCEPTION
2150 WHEN no_data_found THEN
2151
2152 --handle it but do not do anything here. leave it.In the verification part because
2153 --lpn_id, parent_lpn_id, item_id , serial_number,
2154 --GTIN ALL WILL BE null for
2155 --that entry in the x_tag_info pl/sql table,
2156
2157 NULL;
2158 END;
2159
2160 --Serial-tag might get read first or LPN-EPC (pallet or CASE)
2161
2162 IF l_prev_serial_pallet_id = -99 THEN
2163 IF l_serial_pallet_id IS NULL THEN
2164 --Error;
2165 --Serial has to be inside an outer pallet at any
2166 --LEVEL OF nesting
2167 IF (l_debug = 1) THEN
2168 trace('Error: There IS NO PALLET for Serial');
2169 END IF;
2170 x_return_status := 'E';
2171 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_NO_PALLET');
2172 FND_MSG_PUB.ADD;
2173
2174 l_is_error := 1;
2175 EXIT; --exit the loop
2176
2177 END IF ;
2178
2179 ELSE --means l_prev_serial_pallet_id <> -99
2180 IF l_serial_pallet_id <> l_prev_serial_pallet_id THEN
2181
2182 --Error;
2183 --Error: Serials BELONG TO MULTIPLE PALLET
2184 IF (l_debug = 1) THEN
2185 trace('Error: Serials belong to multiple Pallet');
2186 END IF;
2187 x_return_status := 'E';
2188 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_MULTIPLE_PALLET');
2189 FND_MSG_PUB.ADD;
2190
2191 l_is_error := 1;
2192 EXIT; --exit the loop
2193
2194 END IF;
2195
2196 END IF;
2197
2198 l_prev_serial_pallet_id := l_serial_pallet_id;
2199
2200 --Assign values
2201 x_tag_info(i).item_id := l_item_id ;
2202 x_tag_info(i).serial_number := l_serial_number;
2203 x_tag_info(i).lpn_id := NULL; --since it can be CASE or inner lpn
2204 x_tag_info(i).parent_lpn_id := l_serial_pallet_id;
2205
2206
2207 ELSIF l_cross_ref_type = 3 THEN
2208 --GTIN-EPC cross reference
2209
2210 --since there is no connection between GTIN and LPN,
2211 --we can NOT validate anything here
2212
2213 x_tag_info(i).gtin := l_gtin;
2214 x_tag_info(i).gtin_serial := l_gtin_serial;
2215
2216 ELSE
2217
2218 --Error-UNIDENTIFIED CROSS-REFERENCE TYPE
2219
2220 IF (l_debug = 1) THEN
2221 trace('Error:UNIDENTIFIED CROSS-REFERENCE TYPE');
2222 END IF;
2223 x_return_status := 'E';
2224 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_INVALID_READ');
2225 FND_MSG_PUB.ADD;
2226
2227 l_is_error := 1;
2228 EXIT; --exit the loop
2229 END IF;
2230
2231
2232 END LOOP;
2233
2234 --LPNs at all level will have same LPN context, picking last one
2235 x_pallet_lpn_context := l_pallet_lpn_context;
2236
2237
2238
2239 --if the somehow the pallet did not get read but if
2240 --ALL cases point TO the same Pallet, it should still PASS
2241
2242 IF l_serial_pallet_id <> l_lpn_pallet_id AND
2243 l_serial_pallet_id <> -99 THEN
2244 --LPN-EPC PALLET
2245 --AND serial-epc pallet DO NOT MATCH and there is at least
2246 --one serial epc also (There has to be one LPN pallet, NO
2247 --consideration OF l_lpn_pallet_id)
2248
2249 x_pallet_lpn_id := NULL;
2250 x_pallet_lpn_context := NULL;
2251 x_return_status := 'E';
2252 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_MULTIPLE_PALLET');
2253 FND_MSG_PUB.ADD;
2254
2255 l_is_error :=1;
2256
2257 ELSE
2258 x_pallet_lpn_id := l_lpn_pallet_id;
2259
2260 END IF;
2261
2262
2263 END IF;--corresponding to x_tag_count > 1
2264
2265
2266 ELSE --means p_tagid IS null
2267
2268 --NO EPC/LPN VALUE PASSED, just return Error
2269 x_pallet_lpn_id := NULL;
2270 x_pallet_lpn_context := NULL;
2271 x_return_status := 'E';
2272 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_READ');
2273 FND_MSG_PUB.ADD;
2274
2275 RETURN;
2276
2277 END IF;
2278
2279
2280 -- IF there is error then update wms_epc
2281 -- No need to insert into wms_device_Requests, as transaction is going
2282 --TO fail because OF the multiple palletS
2283
2284 IF l_is_error = 1 THEN
2285
2286 FOR j IN 1..x_tag_info.COUNT LOOP
2287 UPDATE wms_epc
2288 SET status = substr(fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F'),1,240),
2289 status_code = 'E'
2290 WHERE EPC = x_tag_info(j).TAG_id;
2291
2292 END LOOP;
2293
2294 END IF;
2295
2296 --Commit is done in the calling API
2297
2298
2299 EXCEPTION
2300 WHEN OTHERS THEN
2301 x_pallet_lpn_id := NULL;
2302 x_pallet_lpn_context := NULL;
2303 x_return_status := 'E';
2304 FND_MESSAGE.SET_NAME('WMS', 'UNEXPECTED ERROR');
2305 FND_MSG_PUB.ADD;
2306 IF (l_debug = 1) THEN
2307 trace ('parse_read_tags :Inside when others exception');
2308 trace ('SQL ERROR :'||SQLCODE);
2309 trace ('SQL ERROR :'||Sqlerrm);
2310 END IF;
2311
2312 END parse_read_tags;
2313
2314
2315
2316
2317 PROCEDURE verify_load
2318 (
2319 p_org_id IN NUMBER,
2320 p_device_id IN NUMBER,
2321 p_tag_info IN tag_info_tbl,
2322 p_pallet_lpn_id IN NUMBER,
2323 p_bus_event_id IN NUMBER,
2324 p_subinventory_code IN VARCHAR2,
2325 p_locator_id IN NUMBER,
2326 p_event_date IN DATE,
2327 x_return_status out nocopy VARCHAR2 --S/E
2328 ) AS
2329
2330 l_expected_case_cnt NUMBER;
2331 l_expected_ser_cnt NUMBER;
2332
2333 l_cur_percent NUMBER;
2334 l_cur_case_cnt NUMBER;
2335
2336 l_load_verify_threshold NUMBER ; --get it from org setup
2337 l_error_code NUMBER;
2338 l_msg_data VARCHAR2(240);
2339 l_request_id NUMBER;
2340
2341 l_case_cnt NUMBER;
2342 l_serial_cnt NUMBER;
2343 l_gtin_cnt NUMBER;
2344
2345 l_lpn_item_id NUMBER;
2346 l_total_qty NUMBER :=0;
2347 l_uom_code VARCHAR2(3);
2348 l_single_item BOOLEAN := TRUE;
2349 l_total_gtin_qty NUMBER := 0;
2350 l_total_lpn_qty NUMBER := 0;
2351 l_total_temp_qty NUMBER := 0;
2352
2353 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2354 --Assumption All cases in the pallet will use same rule and
2355 --threshold_percentage including the pallet
2356 G_PROFILE_GTIN VARCHAR2(100) := FND_PROFILE.value('INV:GTIN_CROSS_REFERENCE_TYPE');
2357 BEGIN
2358
2359 x_return_status := 'S';
2360
2361 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
2362 l_device_req_id_pkg := l_request_id; --to be used for the pallet record
2363
2364
2365 --By the time this API is called
2366 --1. it has been ensured that only one
2367 --pallet IS IN the list OF epc's. There might be cases from multiple
2368 --pallets though. that IS part FOR the verifiaction TO ensure
2369
2370 --2. LPN_id and prent_lpn_id has been set for each read in the p_tag_info table
2371
2372 --3. More than one tag is present
2373 IF (l_debug = 1) THEN
2374 trace('p_tag_count :'|| p_tag_info.COUNT);
2375 END IF;
2376
2377 /****************************************/
2378 /*
2379 ERROR code for verification
2380 0- VERIFIED
2381 1- ERROR_INVALID_PALLET_CASE
2382 2- ERROR_MIXED_CASES
2383 3- ERROR_UNDER_THRESHOL
2384 4- ERROR_UNDER_THRESHOLD
2385 5- ERROR_EXTRA_CASE
2386 */
2387 /****************************************/
2388
2389
2390 --See if the PALLET lpn has single item
2391 BEGIN
2392 SELECT SUM(WLC.primary_quantity),wlc.inventory_item_id
2393 INTO l_total_lpn_qty, l_lpn_item_id
2394 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
2395 WHERE wlpn.outermost_lpn_id = p_pallet_lpn_id
2396 AND wlpn.lpn_id = wlc.parent_lpn_id
2397 --AND wlc.organization_id = p_org_id
2398 AND wlc.organization_id = wlpn.organization_id
2399 GROUP BY WLC.inventory_item_id;
2400
2401 EXCEPTION
2402 WHEN too_many_rows THEN
2403 l_SINGLE_ITEM := FALSE;
2404
2405 WHEN OTHERS THEN
2406
2407 x_return_status := 'E';
2408 FND_MESSAGE.SET_NAME('WMS', 'UNEXPECTED_ERROR');
2409 FND_MSG_PUB.ADD;
2410
2411 IF l_debug = 1 then
2412 trace('ERROR CODE = ' || SQLCODE);
2413 trace('ERROR MESSAGE = ' || SQLERRM);
2414 END IF;
2415
2416 END;
2417
2418
2419
2420 --Process them against our WMS_EPC table shipment verification
2421 l_case_cnt :=0;
2422 l_serial_cnt := 0;
2423
2424 FOR i IN 1..p_tag_info.COUNT LOOP
2425
2426
2427 --Search for any extraneous read
2428 IF p_tag_info(i).lpn_id IS NULL AND p_tag_info(i).parent_lpn_id IS NULL
2429 AND p_tag_info(i).serial_number IS NULL
2430 AND p_tag_info(i).gtin IS NULL AND p_tag_info(i).gtin_serial IS NULL THEN
2431
2432 IF (l_debug = 1) THEN
2433 trace('Extraneous read ERROR');
2434 END IF;
2435
2436 l_error_code := 1;
2437 --mark all read EPC as invalid in EPC table
2438 x_return_status := 'E';
2439 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_READ');
2440 FND_MSG_PUB.ADD;
2441
2442 EXIT;
2443
2444 END IF;
2445
2446
2447 IF p_tag_info(i).parent_lpn_id IS NOT NULL AND P_pallet_lpn_id <> p_tag_info(i).parent_lpn_id THEN
2448 --Cases from different pallet are in this set of tags
2449
2450 x_return_status := 'E';
2451 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_MIXED_CASES');
2452 FND_MSG_PUB.ADD;
2453 EXIT;
2454 END IF;
2455
2456
2457 IF p_tag_info(i).LPN_ID IS NOT NULL AND p_tag_info(i).PARENT_LPN_ID IS NOT NULL THEN --CASE
2458 l_case_cnt := l_case_cnt +1;
2459 ELSIF p_tag_info(i).serial_number IS NOT NULL AND p_tag_info(i).PARENT_LPN_ID IS NOT NULL THEN --Serial
2460 l_serial_cnt := l_serial_cnt +1;
2461 ELSIF p_tag_info(i).gtin IS NOT NULL AND p_tag_info(i).gtin_serial IS NOT NULL THEN
2462
2463
2464 --ONLY FOR SINLGE ITEM IN PALLET WE DO THIS VALIDATION
2465 IF l_single_item THEN
2466 BEGIN
2467 --Assumption is that given a GTIN + Item_id + org_id , the set
2468 --up for cross reference will return only one record
2469 select uom_code INTO l_uom_code FROM mtl_cross_references mcr
2470 WHERE mcr.inventory_item_id = L_LPN_ITEM_ID
2471 AND mcr.CROSS_REFERENCE = To_char(p_tag_info(i).gtin)
2472 AND mcr.CROSS_REFERENCE_TYPE = G_PROFILE_GTIN
2473 AND (( mcr.org_independent_flag = 'Y' AND mcr.organization_id IS NULL) OR
2474 (mcr.org_independent_flag = 'N' AND mcr.organization_id = p_org_id ));
2475
2476 EXCEPTION
2477 WHEN too_many_rows THEN
2478 x_return_status := 'E';
2479 IF (l_debug = 1) THEN
2480 trace ('Validating GTIN: Inside too_many_rows for GTIN :'||p_tag_info(i).gtin);
2481 END IF;
2482
2483 EXIT;
2484
2485 WHEN OTHERS THEN
2486 x_return_status := 'E';
2487 IF (l_debug = 1) THEN
2488 trace ('Validating GTIN: Inside when others exception');
2489 trace ('SQL ERROR :'||SQLCODE);
2490 trace ('SQL ERROR :'||Sqlerrm);
2491 END IF;
2492
2493 EXIT;
2494
2495 END;
2496
2497
2498 SELECT conversion_rate INTO l_total_temp_qty FROM mtl_uom_conversions_view mucv
2499 where organization_id = p_org_id
2500 and uom_code = l_uom_code
2501 AND INVENTORY_ITEM_ID = l_lpn_item_id;
2502
2503 l_total_gtin_qty := l_total_temp_qty + l_total_gtin_qty;
2504
2505
2506 END IF; --single item
2507
2508
2509 END IF;
2510
2511 END LOOP;
2512
2513 IF (l_debug = 1) THEN
2514 trace('CASE COUNT ::'|| l_case_cnt);
2515 trace('SERIAL COUNT ::'|| l_serial_cnt);
2516 trace ('Total_gtin_qty :'||l_total_gtin_qty);
2517 END IF;
2518
2519
2520 IF x_return_status = 'S' THEN
2521
2522 BEGIN
2523 wms_rfid_ext_pub.get_new_load_verif_threshold(p_org_id => p_org_id,
2524 p_pallet_lpn_id => p_pallet_lpn_id,
2525 x_new_load_verif_threshold => l_load_verify_threshold,
2526 x_return_status => x_return_status);
2527 EXCEPTION
2528 WHEN OTHERS THEN
2529 x_return_status := 'E' ;
2530 END;
2531
2532 IF x_return_status = 'S' THEN
2533
2534 IF l_load_verify_threshold IS NULL THEN
2535 --DO NOT OVERRIDE
2536 SELECT Nvl(rfid_verif_pcnt_threshold,0) INTO l_load_verify_threshold
2537 FROM mtl_parameters WHERE organization_id = p_org_id;
2538 END IF;
2539
2540 ELSE
2541 x_return_status := 'S';
2542
2543 SELECT Nvl(rfid_verif_pcnt_threshold,0) INTO l_load_verify_threshold
2544 FROM mtl_parameters WHERE organization_id = p_org_id;
2545
2546 END IF;
2547
2548
2549 IF (l_debug = 1) THEN
2550 trace('l_load_verify_threshold :'||l_load_verify_threshold);
2551 END IF;
2552
2553 SELECT COUNT(1) INTO l_expected_case_cnt
2554 FROM wms_license_plate_numbers wlpn
2555 WHERE parent_lpn_id = p_pallet_lpn_id
2556 AND wlpn.parent_lpn_id = wlpn.outermost_lpn_id
2557 AND wlpn.parent_lpn_id IS NOT NULL;
2558
2559
2560 SELECT COUNT(1) INTO l_expected_ser_cnt
2561 FROM mtl_serial_numbers msn, wms_license_plate_numbers wlpn
2562 WHERE msn.lpn_id = wlpn.lpn_id
2563 and wlpn.outermost_lpn_id = p_pallet_lpn_id;
2564
2565 IF (l_debug = 1) THEN
2566 trace('l_expected_case_cnt :'||l_expected_case_cnt);
2567 trace('l_expected_SER_cnt :' ||l_expected_ser_cnt);
2568
2569 END IF;
2570
2571
2572 IF l_expected_case_cnt <> 0 THEN
2573
2574 l_cur_percent := ((l_case_cnt + l_serial_cnt)/(l_expected_case_cnt+l_expected_ser_cnt))*100;
2575 ELSE
2576 l_cur_percent := 0;
2577 END IF;
2578
2579 IF (l_debug = 1) THEN
2580 trace('l_cur_percent :'|| l_cur_percent);
2581 END IF;
2582 --Bug 5636478 - Moving the condition l_load_verify_threshold=0 to the IF condition
2583 --from ELSIF as the Verification is failing if Required Percentage of Load = 0
2584 --In this case it must succeed
2585 IF l_cur_percent = 100 OR
2586 ( l_load_verify_threshold <> 0 AND l_cur_percent >=
2587 l_load_verify_threshold AND l_cur_percent < 100 ) OR
2588 l_load_verify_threshold = 0 THEN
2589
2590 --verification SUCCEEDED....update associated case records amd
2591 --CURRENT pallet with STATUS='VALID'
2592
2593 --update all the CASE + PALLET set recods as VALID, including
2594 --the unread ones
2595 IF (l_debug = 1) THEN
2596 trace(' sucessful validation');
2597 END IF;
2598
2599 x_return_status := 'S';
2600 FND_MESSAGE.SET_NAME('WMS', 'WMS_VERIFY_COMPLETE');
2601 FND_MSG_PUB.ADD;
2602
2603 ELSIF l_cur_percent < l_load_verify_threshold THEN
2604
2605 IF (l_debug = 1) THEN
2606 trace('Failed validation');
2607 END IF;
2608 x_return_status := 'E';
2609
2610 FND_MESSAGE.SET_NAME('WMS', 'WMS_VERIF_UNDER_THRESHOLD');
2611 FND_MSG_PUB.ADD;
2612
2613
2614 ELSIF l_cur_percent > 100 THEN --case when there are more cases on
2615 --the pallet the expected, amy be OF other pallet
2616
2617 x_return_status := 'E';
2618 FND_MESSAGE.SET_NAME('WMS','WMS_VERIF_EXTRA_CASE');
2619 FND_MSG_PUB.ADD;
2620 END IF;
2621
2622 END IF; -- x_return_status = 'S'
2623
2624
2625 --Now check for GTIN tag failure condition
2626 --this check condition is applied only if the LPN has single items.
2627
2628
2629 IF l_single_item AND x_return_status = 'S' THEN -- VALIDATE FOR GTIN
2630
2631 IF (l_debug = 1) THEN
2632 trace('***** Total LPN qty :'||l_total_lpn_qty);
2633 END IF;
2634
2635 IF l_total_lpn_qty < l_total_gtin_qty THEN
2636 x_return_status := 'E';
2637 FND_MESSAGE.SET_NAME('WMS','WMS_VERIF_EXTRA_GTIN_PACK');
2638 FND_MSG_PUB.ADD;
2639
2640 END IF;
2641
2642 ELSE
2643 IF (l_debug = 1) THEN
2644 trace('No GTIN level validation is needed for Multi-Item Pallet');
2645 END IF;
2646
2647 END IF;
2648
2649
2650 --Update appropriate tables with verification result
2651
2652 --get the failue/success meag
2653 l_msg_data := substr(fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F'),1,240);
2654
2655 IF x_return_status = 'E' THEN
2656
2657 --1. UPDATE WMS_EPC TABLE FOR FAILURE FOR ALL READS
2658 --2. INSERT INTO WMS_DEVICE_REQUESTs TABLE FOR READ CASE TAGS, NOT THE PALLET
2659 -- pallet will be transferred in the process_rfid_txn table and all
2660 -- above records will be moved TO the wms_device_request_hist table
2661
2662 /* we can not use the group_id to update wms_epc table in one shot
2663 here as all records of the group_id might not be read. I have to
2664 update only those records which have been read by the reader.
2665 */
2666
2667 IF l_error_code = 1 THEN --Extraneous read
2668
2669 FOR j IN 1..p_tag_info.COUNT LOOP
2670 UPDATE wms_epc
2671 SET status = l_msg_data,
2672 status_code = x_return_status
2673 WHERE EPC = p_tag_info(j).tag_id;--Since no LPN
2674 --corredponding TO the extraneous read
2675
2676
2677 IF p_tag_info(j).parent_lpn_id IS NOT NULL THEN --Pallet rec
2678 --will be handled later
2679
2680 INSERT INTO wms_device_requests (request_id,
2681 business_event_id,
2682 organization_id,
2683 lpn_id,
2684 device_id,
2685 subinventory_code,
2686 locator_id,
2687 status_code,
2688 status_msg,
2689 last_update_date,
2690 last_updated_by
2691 ) VALUES
2692 (l_request_id,
2693 p_bus_event_id ,
2694 p_org_id,
2695 p_tag_info(j).LPN_id,
2696 p_device_id,
2697 p_subinventory_code,
2698 p_locator_id,
2699 x_return_status,
2700 l_msg_data,
2701 p_event_date,
2702 fnd_global.user_id);
2703
2704 END IF;
2705
2706 END LOOP;
2707
2708 ELSE
2709
2710 FOR j IN 1..p_tag_info.COUNT LOOP
2711 UPDATE wms_epc
2712 SET status = l_msg_data,
2713 status_code = x_return_status
2714 WHERE lpn_id = p_tag_info(j).lpn_id;
2715
2716 IF p_tag_info(j).parent_lpn_id IS NOT NULL THEN --Pallet rec
2717 --will be handled later
2718
2719 INSERT INTO wms_device_requests (request_id,
2720 business_event_id,
2721 organization_id,
2722 lpn_id,
2723 device_id,
2724 subinventory_code,
2725 locator_id,
2726 status_code,
2727 status_msg,
2728 last_update_date,
2729 last_updated_by
2730 ) VALUES
2731 (l_request_id,
2732 p_bus_event_id ,
2733 p_org_id,
2734 p_tag_info(j).LPN_id,
2735 p_device_id,
2736 p_subinventory_code,
2737 p_locator_id,
2738 x_return_status,
2739 l_msg_data,
2740 p_event_date,
2741 fnd_global.user_id);
2742
2743 END IF;
2744
2745 END LOOP;
2746
2747 END IF;
2748
2749 ELSIF x_return_status= 'S' THEN
2750
2751 --1. UPDATE WMS_EPC TABLE FOR VERIFICATION SUCESSSFUL FOR ALL READS
2752 --NOTHING IN THE WMS_DEVICE_REQUEST table, no case records in the wms
2753 --device history TABLE IN successful scenario
2754
2755 FOR j IN 1..p_tag_info.COUNT LOOP
2756 UPDATE wms_epc
2757 SET status = l_msg_data,
2758 status_code = x_return_status
2759 WHERE EPC = p_tag_info(j).tag_id;
2760
2761 END LOOP;
2762
2763 END IF;
2764
2765
2766 --Commit is done in the calling API
2767
2768
2769 EXCEPTION
2770 WHEN OTHERS THEN
2771 x_return_status := 'E';
2772 IF (l_debug = 1) THEN
2773 trace ('verify_load : Inside when others exception');
2774 trace ('SQL ERROR :'||SQLCODE);
2775 trace ('SQL ERROR :'||Sqlerrm);
2776 END IF;
2777
2778 END verify_load;
2779
2780
2781
2782 procedure process_rfid_txn
2783 (
2784 p_tagid in WMS_EPC_TAGID_TYPE, -- EPC TAGS ID VALUE, IN VARRAY
2785 p_tagdata IN WMS_EPC_TAGDATA_TYPE,-- ANY ADDITIONAL DATA IN VARRAY
2786 p_portalid in VARCHAR2,
2787 p_event_date in date,
2788 p_system_id in VARCHAR2 DEFAULT null,
2789 p_statuschange in NUMBER DEFAULT null,
2790 p_datachange in NUMBER DEFAULT null,
2791 p_status in NUMBER DEFAULT null,
2792 p_x in NUMBER DEFAULT null,
2793 p_y in NUMBER DEFAULT NULL,
2794 x_return_value out nocopy VARCHAR2, --success,error,warning
2795 x_return_mesg out nocopy varchar2
2796 ) IS
2797
2798 end_processing EXCEPTION;--Exception to stop processing
2799
2800 l_tag_info tag_info_tbl;
2801 l_device_enabled VARCHAR2(1);
2802 l_lpn_context NUMBER;
2803 l_lpn_id NUMBER;
2804 l_device_id NUMBER;
2805 l_organization_id NUMBER;
2806 l_subinventory_code VARCHAR2(30);
2807 l_locator_id NUMBER;
2808 l_outermost_lpn_id NUMBER;
2809 l_output_method_id NUMBER;
2810 l_request_id NUMBER;
2811 l_return_status VARCHAR2(1);
2812 l_shipment_header_id NUMBER;
2813 l_routing_id NUMBER;
2814 l_msg_count NUMBER;
2815 l_msg_data VARCHAR2(500);
2816 l_is_expense VARCHAR2(1);
2817 l_is_valid_txn_device NUMBER;
2818 l_progress VARCHAR2(8);
2819 l_out_business_event_id NUMBER;
2820 l_is_last_lpn_load NUMBER;
2821 l_user_id NUMBER;
2822 l_resp_appl_id NUMBER;
2823 l_resp_id NUMBER;
2824
2825 l_tagid VARCHAR2(500);
2826 l_tag_len NUMBER;
2827 l_tag_count NUMBER;
2828 l_verif_req VARCHAR2(1);
2829
2830 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2831
2832 BEGIN
2833 x_return_value := 'success';
2834
2835
2836 IF (l_debug = 1) THEN
2837 trace('Entering the call process_rfid_txn ');
2838 END IF;
2839
2840 IF fnd_global.user_id = -1 OR fnd_global.user_id IS NULL THEN
2841
2842 l_user_id := fnd_profile.value('WMS_RFID_USER');
2843 select APPLICATION_ID INTO l_resp_appl_id from fnd_application where
2844 APPLICATION_SHORT_NAME = 'WMS' AND ROWNUM <2;
2845 l_resp_id := 21676; --Corresponding to "Warehouse Manager" responsibility
2846
2847 fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);
2848 IF (l_debug = 1) THEN
2849 trace('Setting the user context for RFID Txn');
2850 END IF;
2851 END IF;
2852
2853
2854 FND_MSG_PUB.initialize;
2855
2856 IF (l_debug = 1) THEN
2857 trace('Inside process_rfid_txn rfid_user_id :'||l_user_id||
2858 --', LPN/EPC name :'||l_tagid||
2859 ', device_id :'||p_portalid||
2860 ', p_event_date :'||p_event_date||
2861 ', p_system_id :'||p_system_id||
2862 ', p_statuschange :'||p_statuschange||
2863 ', p_datachange :'||p_datachange);
2864 END IF;
2865
2866
2867 l_progress := '10';
2868
2869 BEGIN
2870 SELECT
2871 device_id,enabled_flag,organization_id,subinventory_code,locator_id,output_method_id
2872 INTO l_device_id,l_device_enabled,l_organization_id,l_subinventory_code,l_locator_id,l_output_method_id
2873 FROM wms_devices_vl
2874 WHERE name = p_portalid; --Device Name
2875 EXCEPTION
2876 WHEN no_data_found THEN
2877 IF (l_debug = 1) THEN
2878 trace('process_rfid_txn :No device defined');
2879 --Can not generate xml since organization_id is required
2880 --column IN the wdr AND we have no information about it here
2881 END IF;
2882 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_DEVICE_FOUND');
2883 FND_MSG_PUB.ADD;
2884 raise end_processing;
2885
2886 WHEN too_many_rows THEN
2887 IF (l_debug = 1) THEN
2888 trace('Error :Multiple devices with same device_name');
2889 END IF;
2890 FND_MESSAGE.SET_NAME('WMS', 'DUPLICATE_DEVICE_ENTRY');
2891 FND_MSG_PUB.ADD;
2892 raise end_processing;
2893
2894 WHEN OTHERS then
2895 IF (l_debug = 1) THEN
2896 trace('Other error in finding device');
2897 trace('SQL error :'||substr(sqlerrm, 1, 240));
2898
2899 END IF;
2900 raise end_processing;
2901 END;
2902
2903 l_progress := '20';
2904
2905 IF (l_debug = 1) THEN
2906 trace('process_rfid_txn :device_id,locator_id::Org_id::'||l_device_id||'::'||l_locator_id||'::'||l_organization_id);
2907 END IF;
2908
2909 --parse tags, get the pallet info
2910 --get appropriate bus event
2911 --process verification, if needed
2912 --process transaction
2913
2914 --PARSE READ TAGS
2915 parse_read_tags(p_tagid => p_tagid,
2916 p_org_id => l_organization_id,
2917 x_tag_info => l_tag_info,
2918 x_pallet_lpn_id => l_lpn_id,
2919 x_pallet_lpn_context => l_lpn_context,
2920 x_tag_count => l_tag_count,
2921 x_return_status => l_return_status);
2922
2923
2924 --Committing the update of failue cases in parse_read_tags
2925 COMMIT;
2926
2927 IF (l_debug = 1) THEN
2928 trace('process_rfid_txn Pallet_lpn_id :'||l_lpn_id);
2929 trace('process_rfid_txn l_lpn_context :'||l_lpn_context);
2930 END IF;
2931
2932
2933 IF L_return_status <> 'S' THEN
2934
2935 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_rfid_error,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E',p_event_date,l_request_id,l_return_status);
2936 raise end_processing;
2937
2938 END IF;
2939
2940
2941 --Create a save point here
2942 SAVEPOINT wms_rfid_sp;
2943
2944
2945 l_progress := '30';
2946
2947 IF (l_debug = 1) THEN
2948 trace('process_rfid_txn:LPN is good l_lpn_context::'||l_lpn_context);
2949 END IF;
2950
2951
2952 --make sure that the the context of LPN is eligible for devices set up in
2953 --the device assignment form
2954
2955 is_valid_txn_device(p_device_id => l_device_id,
2956 p_lpn_context => l_lpn_context,
2957 p_organization_id => l_organization_id,
2958 x_out_business_event_id => l_out_business_event_id,--to distinguish truck_load
2959 --Vs truck_load_ship and Direct rcv Vs Std/Insp rcv
2960 x_valid_device_for_txn => l_is_valid_txn_device,
2961 x_verif_req => l_verif_req );
2962
2963
2964
2965 IF (l_debug = 1) THEN
2966 trace('process_rfid_txn:l_out_business_event_id::l_is_valid_txn_device ::'||l_out_business_event_id||'::'||l_is_valid_txn_device);
2967 END IF;
2968
2969
2970 IF l_is_valid_txn_device <> 1 THEN
2971 IF (l_debug = 1) THEN
2972 trace('process_rfid_txn :Error:No or Multiple valid business events in the set up');
2973 END IF;
2974
2975 IF l_is_valid_txn_device = 0 THEN
2976 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_ELIGIBLE_BUS_EVENT');
2977 FND_MSG_PUB.ADD;
2978 ELSIF l_is_valid_txn_device = -1 THEN
2979 IF (l_debug = 1) THEN
2980 trace('process_rfid_txn : Error: Both Truck_load and Truck_load_ship are associated');
2981 END IF;
2982 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_BUS_ASSOC');
2983 FND_MSG_PUB.ADD;
2984 ELSIF l_is_valid_txn_device = -2 THEN
2985 IF (l_debug = 1) THEN
2986 trace('process_rfid_txn : Error: Direct rcv and Std/Insp both with same device');
2987 END IF;
2988 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_BUS_ASSOC');
2989 FND_MSG_PUB.ADD;
2990 ELSIF l_is_valid_txn_device = -3 THEN
2991 IF (l_debug = 1) THEN
2992 trace('process_rfid_txn : Error: Invalid LPN context');
2993 END IF;
2994 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN_CONTEXT');
2995 FND_MSG_PUB.ADD;
2996
2997 END IF;
2998
2999 --Insert generate XML/CSV or Call API for wms_be_rfid_error bus
3000 --event
3001 ROLLBACK TO wms_rfid_sp;
3002 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_rfid_error,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E',p_event_date,l_request_id,l_return_status);
3003 raise end_processing;
3004
3005
3006 ELSE --is_transaction_valid
3007
3008 l_progress := '30.5';
3009 IF (l_debug = 1) THEN
3010 trace('process_rfid_txn : valid business events have been set up');
3011 trace('l_out_business_event_id :'|| l_out_business_event_id);
3012 trace('l_verif_req :'||l_verif_req );
3013 END IF;
3014
3015
3016 --PERFORM THE VERIFICATION only if tag count > 1
3017 IF l_verif_req = 'Y' THEN
3018 IF l_tag_count > 1 THEN
3019 IF (l_debug = 1) THEN
3020 trace('Starting the verification process.....');
3021 END IF;
3022
3023 verify_load
3024 (
3025 p_org_id => l_organization_id,
3026 p_device_id => l_device_id,
3027 p_tag_info => l_tag_info,
3028 p_pallet_lpn_id => l_lpn_id,
3029 p_bus_event_id => l_out_business_event_id,
3030 p_subinventory_code => l_subinventory_code,
3031 p_locator_id => l_locator_id,
3032 p_event_date => p_event_date,
3033 x_return_status => l_return_status
3034 );
3035
3036 ELSIF l_tag_count = 1 THEN
3037
3038 IF (l_debug = 1) THEN
3039 trace('Error: Verification of a Single LPN');
3040 END IF;
3041
3042 FND_MESSAGE.SET_NAME('WMS', 'WMS_VERIF_UNDER_THRESHOLD');
3043 FND_MSG_PUB.ADD;
3044 l_return_status:= 'E';
3045
3046 END IF;
3047
3048
3049 END IF;
3050
3051
3052 trace(' verify_load returned status:'||l_return_status);
3053
3054 --Whatever happens in verification later, result of verficiation needs to be commited
3055 -- commit all the changes in the WMS_EPC table
3056 COMMIT;
3057
3058
3059 IF l_return_status <> 'S' THEN
3060 IF (l_debug = 1) THEN
3061 trace(' INSIDE VERIFY FAILURE, CALLING GENERATE xml api');
3062 END IF;
3063 generate_xml_csv_api(l_device_id,l_out_business_event_id,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E',p_event_date,l_request_id,l_return_status);
3064 raise end_processing;
3065
3066 END IF;
3067
3068
3069 l_progress := '30.7';
3070
3071
3072 --define a new savepoint for further processing
3073 SAVEPOINT wms_rfid_sp1;
3074
3075 IF (l_debug = 1) THEN
3076 trace('AFTER VALIDATION l_return_status::'||l_return_status);
3077 END IF;
3078
3079 --PROCESS THE ACTUAL TRANSACTION
3080 IF l_return_status ='S' THEN --Verification successful
3081
3082 IF (l_lpn_context = wms_container_pub.lpn_context_inv) THEN
3083
3084 IF (l_debug = 1) THEN
3085 trace('process_rfid_txn :Direct Ship');
3086 END IF;
3087
3088
3089 IF l_out_business_event_id= wms_device_integration_pvt.wms_be_TRUCK_LOAD THEN
3090
3091 IF (l_debug = 1) THEN
3092 trace('process_rfid_txn :Processing Direct Truck Load');
3093 END IF;
3094
3095 l_progress := '40';
3096
3097 --process truck_load txn
3098 process_direct_truck_load(p_lpn_id => l_lpn_id,
3099 p_org_id => l_organization_id,
3100 p_dock_door_id => l_locator_id,
3101 x_is_last_lpn_load => l_is_last_lpn_load,
3102 x_return_status => l_return_status
3103 );
3104
3105 l_progress := '50';
3106
3107 IF (l_debug = 1) THEN
3108 trace('process_rfid_txn:After process_direct_truck_load:l_return_status:"'||l_return_status);
3109 END IF;
3110
3111 IF l_return_status IS NULL OR l_return_status = 'E' OR l_return_status = 'U'THEN
3112
3113 --Error mesg should have been at the point of failure
3114
3115 --Insert record into the WMS_DEVICE_REQUESTS table
3116 --Generate xml/call API for truck_load business event
3117 --populate in the history table
3118 ROLLBACK TO wms_rfid_sp1;
3119 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_TRUCK_LOAD,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3120 x_return_value := 'error';
3121
3122 ELSE
3123 --Truck Load Txn Successful
3124 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_TRUCK_LOAD,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'S', p_event_date,l_request_id,l_return_status);
3125
3126 END IF;
3127
3128
3129 elsif l_out_business_event_id = wms_device_integration_pvt.wms_be_truck_load_ship THEN
3130
3131 IF (l_debug = 1) THEN
3132 trace('process_rfid_txn :Processing Direct Truck Load and SHIP');
3133 END IF;
3134
3135 --process truck_load_ship txn and inside make sure that the
3136 --lpn is COMPLETELY reserved against a SO
3137 l_progress := '40';
3138
3139 process_direct_truck_load_SHIP(p_lpn_id => l_lpn_id,
3140 p_org_id => l_organization_id,
3141 p_dock_door_id => l_locator_id,
3142 x_return_status => l_return_status
3143 );
3144
3145 l_progress := '50';
3146
3147
3148 IF (l_debug = 1) THEN
3149 trace('process_rfid_txn:After process_direct_truck_load_SHIP:l_return_status:'||l_return_status);
3150 trace('process_rfid_txn: message stored'||l_ship_confirm_pkg_mesg);
3151 END IF;
3152
3153 IF l_return_status IS NULL OR l_return_status = 'E' OR
3154 l_return_status = 'U' THEN
3155
3156 --Error mesg should have been at the point of failure
3157
3158 --Insert record into the WMS_DEVICE_REQUESTS table
3159 --Generate xml/call API for truck_load business event
3160 --populate in the history table
3161 ROLLBACK TO wms_rfid_sp1;
3162 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_TRUCK_LOAD_SHIP,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3163 x_return_value := 'error';
3164
3165 ELSE
3166 --Truck Load Txn Successful
3167 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_TRUCK_LOAD_SHIP,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'S', p_event_date,l_request_id,l_return_status);
3168
3169 END IF;
3170
3171 END IF; --FOR l_out_business_event_id
3172
3173
3174 ELSIF (l_lpn_context = wms_container_pub.lpn_context_picked ) THEN
3175 IF (l_debug = 1) THEN
3176 trace('process_rfid_txn :Normal LPN Ship');
3177 END IF;
3178
3179 IF l_out_business_event_id= wms_device_integration_pvt.wms_be_TRUCK_LOAD THEN
3180
3181 IF (l_debug = 1) THEN
3182 trace('process_rfid_txn :Processing Normal Truck Load');
3183 END IF;
3184
3185
3186 --dock-door for the device is locator of the device
3187 --The device assignment form will make sure that only those devices
3188 --are associated with Truck_load AND Truck_Load_Ship business events
3189 --for which Sub/Loc are defined.So if it comes here l_locator_id
3190 --will have value.
3191
3192 l_progress := '40';
3193
3194 process_normal_truck_load(p_lpn_id => l_lpn_id,
3195 p_org_id => l_organization_id,
3196 p_dock_door_id => l_locator_id,
3197 x_is_last_lpn_load => l_is_last_lpn_load,
3198 x_return_status => l_return_status);
3199
3200 l_progress := '50';
3201
3202
3203 IF (l_debug = 1) THEN
3204 trace('process_rfid_txn:After process_normal_truck_load:l_return_status:"'||l_return_status);
3205 END IF;
3206
3207 IF l_return_status IS NULL OR l_return_status = 'E' THEN
3208
3209 --Error mesg should have been at the point of failure
3210
3211 --Insert record into the WMS_DEVICE_REQUESTS table
3212 --Generate xml/call API for truck_load business event
3213 --populate in the history table
3214
3215 ROLLBACK TO wms_rfid_sp1;
3216 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_TRUCK_LOAD,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3217 x_return_value := 'error';
3218 ELSE
3219 --Truck Load Txn Successful
3220 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_TRUCK_LOAD,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'S', p_event_date,l_request_id,l_return_status);
3221
3222 END IF;
3223
3224
3225 ELSIF l_out_business_event_id= wms_device_integration_pvt.wms_be_TRUCK_LOAD_ship THEN
3226
3227 IF (l_debug = 1) THEN
3228 trace('process_rfid_txn :Processing Normal Truck Load and SHIP');
3229 END IF;
3230
3231 --dock-door for the device is locator of the device
3232 --The device assignment form will make sure that only those devices
3233 --are associated with Truck_load AND Truck_Load_Ship business events
3234 --for which Sub/Loc are defined.So if it comes here l_locator_id
3235 --will have value.
3236 l_progress := '40';
3237
3238 process_normal_truck_load_ship(p_lpn_id => l_lpn_id,
3239 p_org_id => l_organization_id,
3240 p_dock_door_id => l_locator_id,
3241 x_return_status => l_return_status
3242 );
3243
3244 l_progress := '50';
3245
3246 IF (l_debug = 1) THEN
3247 trace('process_rfid_txn:After process_normal_truck_load_ship:l_return_status:'||l_return_status);
3248 trace('process_rfid_txn:Return Message::'||l_ship_confirm_pkg_mesg);
3249 END IF;
3250
3251
3252
3253 IF l_return_status IS NULL OR l_return_status = 'E' THEN
3254
3255 --Insert record into the WMS_DEVICE_REQUESTS table
3256 --Generate xml/call API for truck_load business event
3257 --populate in the history table
3258
3259 ROLLBACK TO wms_rfid_sp1;
3260 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_TRUCK_LOAD_ship,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3261 x_return_value := 'error';
3262 ELSE
3263
3264 --Truck Load ship Txn Successful
3265 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_TRUCK_LOAD_ship,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'S', p_event_date,l_request_id,l_return_status);
3266
3267 END IF;
3268
3269 END IF;
3270
3271 ELSIF (l_lpn_context = wms_container_pub.lpn_context_intransit OR
3272 l_lpn_context = wms_container_pub.lpn_context_vendor) THEN
3273
3274 IF (l_debug = 1) THEN
3275 trace('process_rfid_txn : processing receiving txn');
3276 END IF;
3277
3278 --Only possible transactions with these LPN conexts can be
3279 --receiving
3280 l_progress := '40';
3281 BEGIN
3282 select rsh.shipment_header_id into l_shipment_header_id from
3283 rcv_shipment_headers rsh ,wms_license_plate_numbers wlpn where wlpn.lpn_id = l_lpn_id
3284 and wlpn.lpn_context IN (6,7) --for ASN 7, blocked for ASN in patch set J
3285 and (rsh.shipment_num = Nvl(wlpn.source_name,'@#$@')
3286 or rsh.shipment_header_id = Nvl(wlpn.source_header_id, -1));
3287
3288 l_progress := '50';
3289
3290 exception
3291 when no_data_found then
3292 IF (l_debug = 1) THEN
3293 trace('process_rfid_txn:No record found with LPN ::'||l_lpn_id);
3294 END IF;
3295 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_RCV_LPN ');
3296 FND_MSG_PUB.ADD;
3297 ROLLBACK TO wms_rfid_sp1;
3298 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_rfid_error,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3299 raise end_processing;
3300 end;
3301
3302 IF (l_debug = 1) THEN
3303 trace('process_rfid_txn:for internal req/ASN shipment_header_id::'||l_shipment_header_id);
3304 END IF;
3305
3306 if l_shipment_header_id is not null then
3307
3308 l_progress := '60';
3309
3310 IF l_lpn_context = 7 THEN
3311
3312 inv_rcv_common_apis.get_asn_routing_id
3313 (x_asn_routing_id => l_routing_id
3314 , x_return_status => l_return_status
3315 , x_msg_count => l_msg_count
3316 , x_msg_data => l_msg_data
3317 , p_shipment_header_id => l_shipment_header_id
3318 , p_lpn_id => l_lpn_id
3319 , p_po_header_id => NULL);
3320
3321 ELSE
3322
3323 inv_rcv_common_apis.get_routing_id
3324 (
3325 x_routing_id => l_routing_id,
3326 x_return_status => l_return_status,
3327 x_msg_count => l_msg_count,
3328 x_msg_data => l_msg_data,
3329 x_is_expense => l_is_expense,
3330 p_po_header_id => null,
3331 p_po_release_id => null,
3332 p_po_line_id => null,
3333 p_shipment_header_id => l_shipment_header_id,
3334 p_oe_order_header_id => null,
3335 p_item_id => null,
3336 p_organization_id => l_organization_id,
3337 p_vendor_id => null,
3338 p_lpn_id => l_lpn_id);
3339
3340 END IF;
3341
3342 l_progress := '70';
3343
3344 IF (l_debug = 1) THEN
3345 trace('process_rfid_txn:p_routing_id,l_return_status,l_msg_count,l_msg_data');
3346
3347 trace('process_rfid_txn:'||l_routing_id||'::'||l_return_status||'::'||l_msg_count||'::'||l_msg_data);
3348 END IF;
3349
3350
3351 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3352
3353 IF (l_debug = 1) THEN
3354 trace(' Error : inv_rcv_common_apis.failed in getting _routing_id');
3355 END IF;
3356 ROLLBACK TO wms_rfid_sp1;
3357 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_rfid_error,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3358 raise end_processing;
3359
3360 ELSE -- get_routing_id succeeded
3361
3362
3363 --make sure that the returned routing_id matches with the
3364 --device setup for business event
3365 --possible case: if the transaction record for rcv gives Direct
3366 --routing whereas the device_id passed is setup for Std/Insp
3367 --business event this transaction should fail
3368
3369 IF ((l_routing_id = 3 AND l_out_business_event_id = wms_device_integration_pvt.wms_be_direct_receipt) OR
3370 (l_routing_id IN (1,2)
3371 AND l_out_business_event_id = wms_device_integration_pvt.wms_be_std_insp_receipt)) THEN
3372
3373 IF (l_debug = 1) THEN
3374 trace('process_rfid_txn: Processing receiving txn');
3375 END IF;
3376 l_progress := '80';
3377
3378 process_rfid_receiving_txn(p_lpn_id => l_lpn_id,
3379 p_device_id => l_device_id,
3380 p_dest_org_id => l_organization_id,
3381 p_lpn_context => l_lpn_context,
3382 p_routing_id => l_routing_id,
3383 p_shipment_header_id => l_shipment_header_id,
3384 p_direct_putaway_sub => l_subinventory_code,
3385 p_direct_putaway_loc => l_locator_id,
3386 x_return_status => l_return_status);
3387
3388 l_progress := '90';
3389
3390 IF l_return_status <> fnd_api.g_ret_sts_success THEN--failed transaction
3391
3392 IF (l_debug = 1) THEN
3393 trace('process_rfid_txn: Receiving txn failed');
3394 END IF;
3395
3396 IF l_routing_id = 1 OR l_routing_id = 2 THEN
3397 --Standard/inspection routing
3398 IF l_return_status <> 'N' THEN --avoid overwriting of mesg SET IN the API call
3399 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_STD_INSP_RCV_FAIL');
3400 FND_MSG_PUB.ADD;
3401 END IF;
3402
3403 ROLLBACK TO wms_rfid_sp1;
3404 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.WMS_BE_STD_INSP_RECEIPT,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3405 x_return_value := 'error';
3406
3407 ELSIF l_routing_id = 3 THEN
3408 -- direct routing
3409 IF l_return_status <> 'N' THEN--avoid overwriting of mesg set in the API call
3410 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_DIR_RCV_FAIL');
3411 FND_MSG_PUB.ADD;
3412 END IF;
3413 ROLLBACK TO wms_rfid_sp1;
3414 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.WMS_BE_DIRECT_RECEIPT,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3415 x_return_value := 'error';
3416
3417 END IF;
3418
3419 ELSE--transaction succeeded
3420
3421 IF (l_debug = 1) THEN
3422 trace('process_rfid_txn: Receiving txn succeeded');
3423 END IF;
3424
3425 IF l_routing_id = 1 OR l_routing_id = 2 THEN
3426 --Standard/inspection routing
3427 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.WMS_BE_STD_INSP_RECEIPT,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'S', p_event_date,l_request_id,l_return_status);
3428
3429
3430 ELSIF l_routing_id = 3 THEN
3431 -- direct routing
3432 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.WMS_BE_DIRECT_RECEIPT,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'S', p_event_date,l_request_id,l_return_status);
3433
3434 END IF;
3435
3436
3437 END IF;--TXN SUCCEEDED
3438
3439 ELSE --matching routing_id and l_out_business_event_id
3440
3441 IF (l_debug = 1) THEN
3442 trace('Error: Device set up and returned txn routing does not match');
3443 END IF;
3444 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_NO_ELIGIBLE_BUS_EVENT');
3445 FND_MSG_PUB.ADD;
3446 ROLLBACK TO wms_rfid_sp1;
3447 generate_xml_csv_api(l_device_id,wms_device_integration_pvt.wms_be_rfid_error,l_organization_id,l_lpn_id,l_output_method_id,l_subinventory_code,l_locator_id,'E', p_event_date,l_request_id,l_return_status);
3448 raise end_processing;
3449
3450 END IF; --matching routing_id and l_out_business_event_id
3451
3452 END IF;-- get_routing_id succeeded
3453
3454 END IF;-- L_shipmet_header_id is not null
3455
3456 END IF;--l_lpn_context
3457
3458 END IF; --Verification successful
3459
3460
3461 END IF;--is_valid_txn_device
3462
3463
3464 l_progress := '100';
3465
3466 IF (l_debug = 1) THEN
3467 trace('process_rfid_txn:Delete requested rows from WDR');
3468 END IF;
3469 delete from wms_device_requests;--since temp table is session specific
3470
3471 IF (l_debug = 1) THEN
3472 trace('process_rfid_txn :End of processing for current read');
3473 END IF;
3474
3475 --commiting the reansaction
3476 COMMIT;
3477
3478 --We do populate mesg in case of succes too.
3479 --when the delivery or trip is complete.
3480
3481 --returning message
3482 IF l_out_business_event_id = wms_device_integration_pvt.wms_be_truck_load_ship THEN
3483 x_return_mesg := l_ship_confirm_pkg_mesg;
3484 else
3485 x_return_mesg := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
3486 END IF;
3487
3488 EXCEPTION
3489 WHEN end_processing THEN
3490 x_return_value := 'error';
3491
3492 /* somehow it does not retrieve all mesg
3493 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
3494 x_return_mesg := l_msg_data;
3495 */
3496
3497 x_return_mesg := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
3498
3499 IF (l_debug = 1) THEN
3500 trace('process_rfid_txn:throwing Exception:end_processing, Delete requested rows from WDR');
3501 trace('x_return_mesg :' || x_return_mesg);
3502 END IF;
3503
3504
3505 delete from wms_device_requests;--since temp table is session specific
3506 COMMIT;
3507
3508 WHEN OTHERS THEN
3509 ROLLBACK TO wms_rfid_sp1;
3510 x_return_value := 'error';
3511 x_return_mesg := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
3512 IF (l_debug = 1) THEN
3513 trace('Other error in process_rfid_txn l_progress:;'|| l_progress);
3514 trace('SQL error :'||substr(sqlerrm, 1, 240));
3515 END IF;
3516
3517 END;
3518
3519
3520 Procedure WMS_READ_EVENT
3521 (
3522 p_tagid in WMS_EPC_TAGID_TYPE, -- EPC TAGS ID VALUE, IN VARRAY
3523 p_tagdata IN WMS_EPC_TAGDATA_TYPE, -- ANY ADDITIONAL DATA IN VARRAY
3524 p_portalid in VARCHAR2,
3525 p_event_date in date,
3526 p_system_id in VARCHAR2 DEFAULT null,
3527 p_statuschange in NUMBER DEFAULT null,
3528 p_datachange in NUMBER DEFAULT null,
3529 p_status in NUMBER DEFAULT null,
3530 p_x in NUMBER DEFAULT null,
3531 p_y in NUMBER DEFAULT null,
3532 x_return_value out nocopy VARCHAR2, --success,error,warning
3533 x_return_mesg out nocopy varchar2
3534 ) IS
3535
3536
3537 BEGIN
3538
3539 x_return_value := 'read';
3540 x_return_mesg := 'Data Read';
3541
3542 END WMS_READ_EVENT;
3543
3544
3545 procedure process_rfid_txn
3546 (
3547 p_tagid in WMS_EPC_TAGID_TYPE, -- EPC TAGS ID VALUE, IN VARRAY
3548 p_tagdata IN WMS_EPC_TAGDATA_TYPE, -- ANY ADDITIONAL DATA IN VARRAY
3549 p_portalid in VARCHAR2,--Device name as varchar2
3550 p_event_date in date,
3551 p_system_id in VARCHAR2 DEFAULT null,
3552 p_statuschange in NUMBER DEFAULT null,
3553 p_datachange in NUMBER DEFAULT null,
3554 p_status in NUMBER DEFAULT null,
3555 p_x in NUMBER DEFAULT null,
3556 p_y in NUMBER DEFAULT NULL,
3557 x_return_value out nocopy VARCHAR2, --success,error,warning
3558 x_return_mesg OUT nocopy VARCHAR2,
3559 x_request_id OUT nocopy NUMBER
3560 ) IS
3561 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3562 BEGIN
3563
3564 IF (l_debug = 1) THEN
3565 trace('process_rfid_txn :Calling parent process_rfid_txn');
3566 END IF;
3567
3568 process_rfid_txn
3569 (
3570 p_tagid => p_tagid,
3571 p_tagdata => p_tagdata,
3572 p_portalid => p_portalid,
3573 p_event_date => p_event_date,
3574 p_system_id => p_system_id,
3575 p_statuschange => p_statuschange,
3576 p_datachange => p_datachange,
3577 p_status => p_status,
3578 p_x => p_x,
3579 p_y => p_y,
3580 x_return_value => x_return_value,
3581 x_return_mesg => x_return_mesg);
3582
3583 x_request_id := l_device_req_id_pkg;
3584
3585 IF (l_debug = 1) THEN
3586 trace('process_rfid_txn :value of request_id '||l_device_req_id_pkg);
3587 END IF;
3588
3589
3590 END process_rfid_txn;
3591
3592
3593
3594
3595 --Internal Wrapper API for testing purpose only using Mobile
3596 --Not to be touched by customer
3597 --it just convertes the type of p_tagid form Clob to varray and calls the
3598 --main API
3599
3600 procedure MobTest_process_rfid_txn
3601 (
3602 p_tagid in clob, -- EPC tag ID
3603 p_tagdata IN clob, -- Any additional value with EPC tag
3604 p_portalid in varchar2,--reader name
3605 p_event_date in date,
3606 p_system_id in VARCHAR2 DEFAULT null,
3607 p_statuschange in NUMBER DEFAULT null,
3608 p_datachange in NUMBER DEFAULT null,
3609 p_status in NUMBER DEFAULT null,
3610 p_x in NUMBER DEFAULT null,
3611 p_y in NUMBER DEFAULT NULL,
3612 x_return_value out nocopy VARCHAR2, --success,error,warning
3613 x_return_mesg OUT nocopy varchar2)
3614
3615 IS
3616 l_start_pos NUMBER;
3617 l_first_pos NUMBER;
3618 l_second_pos NUMBER;
3619 l_index NUMBER;
3620
3621 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3622
3623 epc_tag_id wms_epc_tagid_type;
3624 epc_tag_data wms_epc_tagdata_type;
3625
3626
3627 BEGIN
3628
3629
3630 IF (l_debug = 1) THEN
3631 trace('Inside MobTest_process_rfid_txn');
3632 END IF;
3633
3634 --Initialize the varray
3635 epc_tag_id := wms_epc_tagid_type('S');
3636 epc_tag_data := wms_epc_tagdata_type('S');
3637
3638
3639 IF p_tagid IS NOT null then
3640 IF (l_debug = 1) THEN
3641 trace('tagid is NOT null');
3642 END IF;
3643
3644 l_start_pos := 1;
3645 l_first_pos := 1;
3646 l_second_pos := 1;
3647
3648 l_index := 1;
3649
3650
3651 WHILE l_second_pos <> 0 LOOP
3652
3653 --trace('Inside outer loop index :'||l_index);
3654
3655
3656 l_first_pos := Instr(p_tagid,',',l_start_pos,1);
3657 l_second_pos := Instr(p_tagid,',',l_start_pos,2);
3658
3659
3660 --trace('l_first_pos , l_second_pos :'||l_first_pos||','||l_second_pos );
3661
3662 l_start_pos := l_second_pos;
3663
3664 IF l_index = 1 THEN
3665
3666 IF l_first_pos = 0 THEN -- only one value is present in the list
3667 --trace('only one value case tag');
3668 epc_tag_id(l_index) := Ltrim(Rtrim(p_tagid));
3669
3670 EXIT;
3671
3672 ELSE --for first AND SECOND value in the list of values
3673
3674 --trace('first value in list of many case');
3675 epc_tag_id(l_index) := Substr(p_tagid,1,l_first_pos-1);
3676
3677 IF l_second_pos <> 0 THEN
3678
3679 l_index := l_index+1;
3680 -- trace('second value in list of many case');
3681 epc_tag_id.extend;
3682 epc_tag_id(l_index) :=Substr(p_tagid,l_first_pos+1,l_second_pos-l_first_pos-1);
3683
3684 ELSE
3685 l_index := l_index+1;
3686 epc_tag_id.extend;
3687 epc_tag_id(l_index) :=Substr(p_tagid,l_first_pos+1);
3688
3689 END IF;
3690 END IF;
3691
3692 ELSIF l_second_pos = 0 THEN --FOR last value
3693
3694 IF (l_debug = 1) THEN
3695 trace('Last value in list of many epc');
3696 END IF;
3697 epc_tag_id.extend;
3698 epc_tag_id(l_index) := Ltrim(Rtrim(Substr(p_tagid,l_first_pos+1)));
3699
3700 ELSE --for in-between values
3701 --trace('In between val in list of many epc');
3702 epc_tag_id.extend;
3703 epc_tag_id(l_index) := Ltrim(Rtrim(Substr(p_tagid,l_first_pos+1,l_second_pos-l_first_pos-1)));
3704
3705 END IF;
3706 l_index := l_index +1 ;
3707
3708 END LOOP;
3709
3710 --call the main API
3711 process_rfid_txn
3712 (
3713 p_tagid => epc_tag_id,
3714 p_tagdata => epc_tag_data,
3715 p_portalid => p_portalid,
3716 p_event_date => p_event_date,
3717 p_system_id => p_system_id,
3718 p_statuschange => p_statuschange,
3719 p_datachange => p_datachange,
3720 p_status => p_status,
3721 p_x => p_x,
3722 p_y => p_y,
3723 x_return_value => x_return_value,
3724 x_return_mesg => x_return_mesg);
3725
3726
3727 ELSE
3728 IF (l_debug = 1) THEN
3729 trace('tagid is NULL');
3730 END IF;
3731 x_return_value := 'error';
3732 x_return_mesg := 'No EPC passed';
3733
3734 END IF;
3735
3736 END MobTest_process_rfid_txn;
3737
3738
3739 END WMS_RFID_DEVICE_PUB;
[Home] [Help]