1 PACKAGE BODY WMS_RFID_DEVICE_PUB AS
2 --/* $Header: WMSRFIDB.pls 120.10.12020000.4 2013/03/13 20:07:04 sahmahes 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_sec_remaining_qty NUMBER;
1104 l_is_last_lpn_load NUMBER;
1105
1106 l_num_line_processed NUMBER;
1107 l_project_id NUMBER;
1108 l_task_id NUMBER;
1109 l_cross_project_allowed VARCHAR2(1);
1110 l_cross_unit_allowed VARCHAR2(1);
1111 l_group_by_customer_flag VARCHAR2(1);
1112 l_group_by_fob_flag VARCHAR2(1);
1113 l_group_by_freight_terms_flag VARCHAR2(1);
1114 l_group_by_intmed_ship_flag VARCHAR2(1);
1115 l_group_by_ship_method_flag VARCHAR2(1);
1116 l_group_by_ship_to_loc_value VARCHAR2(100);
1117 l_group_by_ship_from_loc_value VARCHAR2(100);
1118 l_group_by_customer_value VARCHAR2(100);
1119 l_group_by_fob_value VARCHAR2(100);
1120 l_group_by_freight_terms_value VARCHAR2(100);
1121 l_group_by_intmed_value VARCHAR2(100);
1122 l_group_by_ship_method_value VARCHAR2(100);
1123 l_serial_at_issue 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 p_fulfillment_base => 'P',
1219 x_remaining_qty => l_remaining_qty,
1220 x_sec_remaining_qty => l_sec_remaining_qty,
1221 x_num_line_processed => l_num_line_processed,
1222 x_project_id => l_project_id,
1223 x_task_id => l_task_id,
1224 x_cross_project_allowed => l_cross_project_allowed,
1225 x_cross_unit_allowed => l_cross_unit_allowed,
1226 x_group_by_customer_flag => l_group_by_customer_flag,
1227 x_group_by_fob_flag => l_group_by_fob_flag,
1228 x_group_by_freight_terms_flag => l_group_by_freight_terms_flag,
1229 x_group_by_intmed_ship_flag => l_group_by_intmed_ship_flag,
1230 x_group_by_ship_method_flag => l_group_by_ship_method_flag,
1231 x_group_by_ship_to_loc_value => l_group_by_ship_to_loc_value,
1232 x_group_by_ship_from_loc_value => l_group_by_ship_from_loc_value,
1233 x_group_by_customer_value => l_group_by_customer_value,
1234 x_group_by_fob_value => l_group_by_fob_value,
1235 x_group_by_freight_terms_value => l_group_by_freight_terms_value,
1236 x_group_by_intmed_value => l_group_by_intmed_value,
1237 x_group_by_ship_method_value => l_group_by_ship_method_value,
1238 x_return_status => l_return_status,
1239 x_msg_count => l_msg_data,
1240 x_msg_data => l_msg_count
1241 );
1242
1243 IF (l_debug = 1) THEN
1244 trace('Process_lpn returned l_return_status::l_remaining_qty::'||l_return_status||'::'||l_remaining_qty);
1245 END IF;
1246
1247 IF l_return_status IS NULL OR l_return_status = 'E' OR
1248 l_return_status = 'U' THEN
1249 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_TRUCK_LOAD_FAIL');
1250 FND_MSG_PUB.ADD;
1251 RAISE FND_API.G_EXC_ERROR;
1252
1253 ELSE
1254
1255 if l_remaining_qty <> 0 then
1256 --LPN should be totally consumed for RFID transactions,so fail the transaction
1257 IF (l_debug = 1) THEN
1258 trace('ERROR: LPN not Totally Consumed');
1259 END IF;
1260 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LPN_NOT_CONSUMED');
1261 FND_MSG_PUB.ADD;
1262 RAISE FND_API.G_EXC_ERROR;
1263
1264 else
1265 IF (l_debug = 1) THEN
1266 trace('Load the LPN');
1267 END IF;
1268
1269 wms_direct_ship_pvt.Load_LPN
1270 ( x_return_status => l_return_status,
1271 x_msg_count => l_msg_count,
1272 x_msg_data => l_msg_data,
1273 p_lpn_id => p_lpn_id,
1274 p_org_id => p_org_id,
1275 p_dock_door_id => p_dock_door_id);
1276
1277 IF (l_debug = 1) THEN
1278 trace('After Loading the LPN');
1279 trace(l_group_by_ship_method_value||':'||l_return_status||':'||l_msg_data||':'||l_msg_count);
1280 END IF;
1281
1282 IF l_return_status IS NULL OR l_return_status = 'E' OR
1283 l_return_status = 'U' THEN
1284 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_TRUCK_LOAD_FAIL');
1285 FND_MSG_PUB.ADD;
1286 RAISE FND_API.G_EXC_ERROR;
1287
1288 ELSE
1289 IF (l_debug = 1) THEN
1290 trace('Load LPN success: Checking whether the last LPN for truck Load only');
1291 END IF;
1292
1293 --check it only for Truck Load Business event and not for Load_SHIP
1294 l_is_last_lpn_load := is_last_lpn_load(p_lpn_id);
1295
1296 IF l_is_last_lpn_load = 2 THEN
1297 IF (l_debug = 1) THEN
1298 trace('last LPN to be loaded for delivery');
1299 END IF;
1300
1301 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LAST_LPN_DELIVERY');
1302 FND_MSG_PUB.ADD;
1303 ELSIF l_is_last_lpn_load = 3 THEN
1304 IF (l_debug = 1) THEN
1305 trace('last LPN to be loaded for trip');
1306 END IF;
1307 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_LAST_LPN_TRIP');
1308 FND_MSG_PUB.ADD;
1309 ELSE
1310 IF (l_debug = 1) THEN
1311 trace('Truck Load Successful');
1312 END IF;
1313 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_TRUCK_LOAD_SUCCESS');
1314 FND_MSG_PUB.ADD;
1315
1316 END IF;
1317
1318 --Must Commit here
1319 Commit;
1320
1321 END IF;--success from Load_LPN
1322
1323 END IF;--l_remaining_qty <> 0
1324
1325 END IF;--for Process_lpn success
1326
1327 x_is_last_lpn_load := l_is_last_lpn_load;
1328
1329 exception
1330 when OTHERS then
1331 x_return_status := 'E';
1332 IF (l_debug = 1) THEN
1333 trace('Other errror: process_direct_truck_load');
1334 trace('SQL error :'||substr(sqlerrm, 1, 240));
1335 END IF;
1336
1337 END process_direct_truck_load;
1338
1339
1340
1341 PROCEDURE process_direct_truck_load_ship( p_lpn_id IN NUMBER,
1342 p_org_id IN NUMBER,
1343 p_dock_door_id IN NUMBER,
1344 x_return_status OUT NOCOPY VARCHAR2)
1345 IS
1346 l_return_status VARCHAR2(1);
1347 l_msg_count NUMBER;
1348 l_msg_data VARCHAR2(2000);
1349 l_delivery_id NUMBER;
1350 l_trip_id NUMBER;
1351 l_error_code NUMBER;
1352 l_vehicle_item_id NUMBER;
1353 l_vehicle_num_prefix VARCHAR2(30);
1354 l_vehicle_num VARCHAR2(30);
1355 l_seal_code VARCHAR2(30);
1356 l_document_set_id NUMBER;
1357 l_missing_item_cur t_genref;
1358
1359 l_is_last_lpn_load NUMBER;
1360
1361 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1362
1363 BEGIN
1364 IF (l_debug = 1) THEN
1365 trace('Inside process_direct_truck_load_ship');
1366 END IF;
1367
1368 x_return_status := FND_API.g_ret_sts_success;
1369
1370 process_direct_truck_load( p_lpn_id => p_lpn_id,
1371 p_org_id => p_org_id,
1372 p_dock_door_id => p_dock_door_id,
1373 x_is_last_lpn_load => l_is_last_lpn_load,
1374 x_return_status => l_return_status);
1375
1376
1377 IF (l_debug = 1) THEN
1378 trace('After calling process_direct_truck_load l_return_status::'||l_return_status);
1379 END IF;
1380
1381 IF l_return_status IS NULL OR l_return_status = 'E' OR l_return_status ='U' THEN
1382
1383 RAISE FND_API.G_EXC_ERROR;
1384 IF (l_debug = 1) THEN
1385 trace('process_direct_truck_load_ship::Truck Load failed');
1386 END IF;
1387
1388 ELSE
1389
1390 IF (l_debug = 1) THEN
1391 trace('Inside process_direct_truck_load_ship:: Truck Load Successful');
1392 END IF;
1393
1394
1395 --Checking whether the last LPN for delivery or Trip (if exists)
1396
1397 IF l_is_last_lpn_load IN (2,3) THEN
1398 IF (l_debug = 1) THEN
1399 trace('Last LPN in the trip or delivery: Calling ship confirm');
1400 END IF;
1401
1402 WMS_SHIPPING_TRANSACTION_PUB.close_truck
1403 ( P_dock_door_id => p_dock_door_id,
1404 P_organization_id => p_org_id,
1405 p_shipping_mode => 'DIRECT',
1406 p_commit_flag => fnd_api.g_false,
1407 x_return_status => l_return_status,
1408 x_return_msg => l_msg_data
1409 );
1410
1411 IF (l_debug = 1) THEN
1412 trace('process_direct_truck_load_ship l_return_status :'||l_return_status);
1413 END IF;
1414
1415 fnd_msg_pub.Count_And_Get
1416 (p_encoded => FND_API.g_false,
1417 p_count => l_msg_count,
1418 p_data => l_msg_data
1419 );
1420
1421 IF l_msg_count > 1 THEN
1422 FOR i IN 1..l_msg_count LOOP
1423 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);
1424 END LOOP;
1425 ELSE
1426
1427 l_ship_confirm_pkg_mesg :=substr(l_msg_data,1,240);
1428
1429 END IF;
1430
1431 fnd_msg_pub.delete_msg;
1432
1433
1434 IF (l_debug = 1) THEN
1435 trace('process_direct_truck_load_ship SHP_msg_data ::'||l_ship_confirm_pkg_mesg);
1436 END IF;
1437
1438 if x_return_status in ('S','W') THEN
1439
1440 COMMIT;
1441
1442 IF (l_debug = 1) THEN
1443 trace('process_direct_truck_load_ship return Success/Warning: Commit Done');
1444 END IF;
1445
1446 --do not need to update the msg here, it is already in the stack
1447 --l_msg_data is returned as nulll
1448
1449 END IF;
1450
1451 ELSE
1452 IF (l_debug = 1) THEN
1453 trace('process_direct_truck_load_ship: Not the last LPN in the trip or delivery: DO not Ship Confirm');
1454 END IF;
1455 l_ship_confirm_pkg_mesg :=fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
1456 END IF;
1457
1458 END IF;
1459
1460 exception
1461 when OTHERS then
1462 x_return_status := 'E';
1463 IF (l_debug = 1) THEN
1464 trace('Other errror: process_direct_truck_load_ship');
1465 trace('SQL error :'||substr(sqlerrm, 1, 240));
1466 END IF;
1467 END process_direct_truck_load_ship;
1468
1469
1470
1471
1472 PROCEDURE process_rfid_receiving_txn(p_lpn_id IN NUMBER,
1473 p_device_id IN NUMBER,
1474 p_dest_org_id IN NUMBER,
1475 p_lpn_context IN NUMBER,
1476 p_routing_id IN NUMBER,
1477 p_shipment_header_id IN NUMBER,
1478 p_direct_putaway_sub IN VARCHAR2,
1479 p_direct_putaway_loc IN NUMBER,
1480 x_return_status OUT nocopy VARCHAR2)
1481 IS
1482
1483 l_return_status VARCHAR2(1);
1484 l_msg_count NUMBER;
1485 l_msg_data VARCHAR2(250);
1486 l_move_order_header_id number;
1487 l_lot_ser_flag VARCHAR2(1) := NULL;
1488 l_inspect NUMBER;
1489 l_shipment_header_id NUMBER := p_shipment_header_id;
1490 l_org_id number;
1491 l_org_location VARCHAR2(60);
1492 l_org_locator_control NUMBER;
1493 l_manual_po_num_type VARCHAR2(25);
1494 l_wms_install_status VARCHAR2(1);
1495 l_wms_purchased VARCHAR2(1);
1496 l_receipt_num VARCHAR2(30);
1497 l_shipment_num VARCHAR2(30);
1498 l_source_type VARCHAR2(30);
1499 l_shipment_hdr_id_dummy NUMBER;
1500 l_vendor_id NUMBER;
1501
1502 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1503 BEGIN
1504
1505 IF (l_debug = 1) THEN
1506 trace('process_rfid_receiving_txn:l_shipment_header_id:p_routing_id:p_lpn_id:p_lpn_context');
1507 trace('process_rfid_receiving_txn:'||l_shipment_header_id||'::'||p_routing_id||'::'||p_lpn_id||'::'|| p_lpn_context);
1508 END IF;
1509
1510 x_return_status := FND_API.g_ret_sts_success;
1511
1512 if p_routing_id IS NOT NULL THEN
1513
1514 if p_routing_id = 2 then --inspection
1515 l_inspect := 1;
1516 end if;
1517
1518 IF (l_debug = 1) THEN
1519 trace('calling inv_rcv_common_apis.init_startup_values ');
1520 END IF;
1521
1522 inv_rcv_common_apis.init_rcv_ui_startup_values
1523 ( p_organization_id => p_dest_org_id, --destination_org
1524 x_org_id => l_org_id,
1525 x_org_location => l_org_location,
1526 x_org_locator_control => l_org_locator_control,
1527 x_manual_po_num_type => l_manual_po_num_type,
1528 x_wms_install_status => l_wms_install_status,
1529 x_wms_purchased => l_wms_purchased,
1530 x_return_status => l_return_status,
1531 x_msg_data => l_msg_data );
1532
1533 IF (l_debug = 1) THEN
1534 trace('process_rfid_receiving_txn:l_org_id,l_org_location,l_org_locator_control,l_return_status,l_msg_data');
1535 trace(l_org_id||'::'||l_org_location||'::'||l_org_locator_control||'::'||l_return_status||'::'||l_msg_data);
1536 END IF;
1537
1538
1539 --`Check for lot serial flag between both org
1540
1541 INV_RCV_COMMON_APIS.check_lot_serial_codes
1542 (
1543 p_lpn_id => p_lpn_id,
1544 p_req_header_id => null,
1545 p_shipment_header_id => l_shipment_header_id,
1546 x_lot_ser_flag => l_lot_ser_flag,
1547 x_return_status => l_return_status,
1548 x_msg_count => l_msg_count,
1549 x_msg_data => l_msg_data);
1550 IF (l_debug = 1) THEN
1551 trace('process_rfid_receiving_txn:l_lot_ser_flag,l_return_status,l_msg_data');
1552 trace(l_lot_ser_flag||'::'||l_return_status||'::'||l_msg_data);
1553 END IF;
1554
1555 IF l_lot_ser_flag = 'N' THEN
1556 IF (l_debug = 1) THEN
1557 trace('l_lot_ser_flag is N');
1558 END IF;
1559
1560 --Fail the transaction, it can not be performed by RFID
1561 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_ITEM_CTRL_ERROR');
1562 FND_MSG_PUB.ADD;
1563 RAISE FND_API.G_EXC_ERROR;
1564
1565 ELSE
1566 IF (l_debug = 1) THEN
1567 trace('process_rfid_receiving_txn:l_lot_ser_code test passed');
1568 END IF;
1569
1570 IF p_routing_id in (1,2) then
1571
1572 IF p_lpn_context = 6 THEN
1573 l_source_type := 'REQEXP';--Internal requisition
1574 ELSIF p_lpn_context = 7 THEN
1575 l_source_type := 'ASNEXP';-- ASN
1576 END IF;
1577
1578 IF (l_debug = 1) THEN
1579 trace('process_rfid_receiving_txn: Standard routing/inspection,insert into interface TABLE');
1580 trace('l_move_order_header_id :'||l_move_order_header_id);
1581 trace('p_dest_org_id :'|| p_dest_org_id);
1582 trace('l_shipment_header_id :'||l_shipment_header_id);
1583 trace('ASN/Internal Req l_source_type'||l_source_type);
1584 trace('p_lpn_id :'||p_lpn_id);
1585 trace('l_inspect :'||l_inspect);
1586 trace('p_direct_putaway_sub :'||p_direct_putaway_sub);
1587 trace('p_direct_putaway_loc :'||p_direct_putaway_loc);
1588 END IF;
1589
1590
1591 INV_RCV_STD_RCPT_APIS.create_std_rcpt_intf_rec
1592 (
1593 p_move_order_header_id => l_move_order_header_id,
1594 p_organization_id => p_dest_org_id,-- destination Org
1595 p_po_header_id => null,
1596 p_po_release_number_id => null,
1597 p_po_line_id => null,
1598 p_shipment_header_id => l_shipment_header_id,
1599 p_req_header_id => null,
1600 p_oe_order_header_id => null,
1601 p_item_id => null,
1602 p_location_id => null,--it defaults from the backend
1603 p_rcv_qty => null,
1604 p_rcv_uom => null,
1605 p_rcv_uom_code => null,
1606 p_source_type => l_source_type,
1607 p_from_lpn_id => p_lpn_id,
1608 p_lpn_id => NULL,
1609 p_lot_control_code => null,
1610 p_revision => null,
1611 p_inspect => l_inspect,
1612 p_rcv_subinventory_code => p_direct_putaway_sub,
1613 p_rcv_locator_id => p_direct_putaway_loc,
1614 x_status => l_return_status,
1615 x_message => l_msg_data
1616 );
1617
1618
1619 IF (l_debug = 1) THEN
1620 trace('process_rfid_receiving_txn: l_return_status, l_msg_data, l_move_order_header_id');
1621 trace('process_rfid_receiving_txn:'||l_return_status||'::'||l_msg_data||'::'||l_move_order_header_id);
1622 END IF;
1623
1624 elsif p_routing_id = 3 then
1625
1626 IF p_lpn_context = 6 THEN
1627 l_source_type := 'REQEXP';
1628 ELSIF p_lpn_context = 7 THEN
1629 l_source_type := 'ASNEXP';
1630 END IF;
1631
1632 IF (l_debug = 1) THEN
1633 trace('process_rfid_receiving_txn: Direct routing, insert into interface table');
1634 trace('l_move_order_header_id :'||l_move_order_header_id);
1635 trace('p_dest_org_id :'|| p_dest_org_id);
1636 trace('l_shipment_header_id :'||l_shipment_header_id);
1637 trace('ASN/Internal Req l_source_type'||l_source_type);
1638 trace('p_direct_putaway_sub :'||p_direct_putaway_sub);
1639 trace('p_direct_putaway_loc :'||p_direct_putaway_loc);
1640 trace('p_lpn_id :'||p_lpn_id);
1641 END IF;
1642
1643 inv_rcv_dir_rcpt_apis.create_direct_rti_rec
1644 (
1645 p_move_order_header_id => l_move_order_header_id,
1646 p_organization_id => p_dest_org_id ,-- destination Org
1647 p_po_header_id => NULL,
1648 p_po_release_id => NULL,
1649 p_po_line_id => NULL,
1650 p_shipment_header_id => l_shipment_header_id,
1651 p_oe_order_header_id => NULL,
1652 p_item_id => NULL,
1653 p_rcv_qty => NULL,
1654 p_rcv_uom => NULL,
1655 p_rcv_uom_code => NULL,
1656 p_source_type => l_source_type,
1657 p_subinventory => p_direct_putaway_sub,
1658 p_locator_id => p_direct_putaway_loc,
1659 p_transaction_temp_id => NULL,
1660 p_lot_control_code => NULL,
1661 p_serial_control_code => NULL,
1662 p_lpn_id => p_lpn_id,
1663 p_revision => NULL,
1664 x_status => l_return_status,
1665 x_message => l_msg_data);
1666
1667
1668 IF (l_debug = 1) THEN
1669 trace('process_rfid_receiving_txn: l_return_status, l_msg_data, l_move_order_header_id');
1670 trace('process_rfid_receiving_txn:'||l_return_status||'::'||l_msg_data||'::'||l_move_order_header_id);
1671 END IF;
1672
1673 END IF;
1674
1675
1676 IF (l_debug = 1) THEN
1677 trace('Calling rcv_gen_receipt_num');
1678 END IF;
1679
1680 inv_rcv_common_apis.rcv_gen_receipt_num(
1681 x_receipt_num => l_receipt_num,
1682 p_organization_id => p_dest_org_id,--destination Org
1683 x_return_status => l_return_status,
1684 x_msg_count => l_msg_count,
1685 x_msg_data => l_msg_data);
1686
1687 IF (l_debug = 1) THEN
1688 trace('process_rfid_receiving_txn: l_receipt_num,l_return_status, l_msg_data, l_msg_count');
1689 trace('process_rfid_receiving_txn:'||l_receipt_num||'::'||l_return_status||'::'||l_msg_data||'::'||l_msg_count);
1690 END IF;
1691
1692 IF l_return_status IS NULL OR l_return_status = 'E' THEN
1693
1694 IF (l_debug = 1) THEN
1695 trace('process_rfid_receiving_txn: Error: Rcpt generation failed');
1696 END IF;
1697 RAISE FND_API.G_EXC_ERROR;
1698
1699 ELSE
1700
1701 IF (l_debug = 1) THEN
1702 trace('process_rfid_receiving_txn: Calling rcv_insert_update_header');
1703 END IF;
1704
1705 IF p_lpn_context = 6 THEN
1706 l_source_type :='INTERNAL ORDER';--Internal requisition
1707 ELSIF p_lpn_context = 7 THEN
1708 l_source_type := 'VENDOR';--ASN
1709 END IF;
1710
1711 SELECT shipment_num,vendor_id
1712 INTO l_shipment_num,l_vendor_id
1713 FROM rcv_shipment_headers
1714 WHERE shipment_header_id = l_shipment_header_id;
1715
1716 IF (l_debug = 1) THEN
1717 trace('process_rfid_receiving_txn: ASN/Inernal req l_source_type'|| l_source_type);
1718 trace('p_dest_org_id :'||p_dest_org_id);
1719 trace('l_source_type :'||l_source_type);
1720 trace('l_receipt_num :'||l_receipt_num);
1721 trace('l_shipment_num :'||l_shipment_num);
1722 trace('l_vendor_id :'||l_vendor_id);
1723 END IF;
1724
1725 l_shipment_hdr_id_dummy := NULL;
1726
1727 INV_RCV_STD_RCPT_APIS.rcv_insert_update_header
1728 (p_organization_id => p_dest_org_id, --destination Org ,
1729 p_shipment_header_id => l_shipment_hdr_id_dummy, --IN OUT parameter
1730 p_source_type => l_source_type,
1731 p_receipt_num => l_receipt_num,
1732 p_vendor_id => l_vendor_id,
1733 p_vendor_site_id => null,
1734 p_shipment_num => l_shipment_num,
1735 p_ship_to_location_id => null,
1736 p_bill_of_lading => null,
1737 p_packing_slip => null,
1738 p_shipped_date => null,
1739 p_freight_carrier_code => null,
1740 p_expected_receipt_date => null,
1741 p_num_of_containers => null,
1742 p_waybill_airbill_num => null,
1743 p_comments => null,
1744 p_ussgl_transaction_code => null,
1745 p_government_context => null,
1746 p_request_id => null,
1747 p_program_application_id => null,
1748 p_program_id => null,
1749 p_program_update_date => null,
1750 p_customer_id => null,
1751 p_customer_site_id => null,
1752 x_return_status => l_return_status,
1753 x_msg_count => l_msg_count,
1754 x_msg_data => l_msg_data
1755 );
1756
1757
1758
1759 IF (l_debug = 1) THEN
1760 trace('process_rfid_receiving_txn: l_return_status,l_msg_data::'||l_return_status||'::'||l_msg_data);
1761 END IF;
1762
1763 if l_return_status IS NULL OR l_return_status = 'E' THEN
1764
1765 IF (l_debug = 1) THEN
1766 trace('process_rfid_receiving_txn: Error: rcv_insert_update_header failed');
1767 END IF;
1768 RAISE FND_API.G_EXC_ERROR;
1769
1770 ELSE
1771
1772 IF (l_debug = 1) THEN
1773 trace('process_rfid_receiving_txn: Calling RCV TM');
1774 END IF;
1775
1776
1777 INV_RCV_MOBILE_PROCESS_TXN.rcv_process_receive_txn
1778 ( x_return_status => l_return_status,
1779 x_msg_data => l_msg_data
1780 );
1781
1782
1783 IF (l_debug = 1) THEN
1784 trace('process_rfid_receiving_txn: l_return_status, l_msg_data::'||l_return_status||'::'||l_msg_data);
1785 END IF;
1786
1787 inv_rcv_common_apis.rcv_clear_global;
1788
1789 IF l_return_status = 'S' THEN
1790 FND_MESSAGE.SET_NAME('WMS', 'WMS_TXN_SUCCESS');
1791 FND_MSG_PUB.ADD;
1792 END IF;
1793
1794 x_return_status := l_return_status;
1795
1796 end if;--INSERT UPDATE HEADR
1797
1798 END IF; --RCPT GENERATION
1799
1800 end if; --l_lot_ser_flag
1801
1802 end if; --p_routing_id is not null
1803
1804 IF (l_debug = 1) THEN
1805 trace('RETURNING::'||x_return_status);
1806 END IF;
1807
1808 exception
1809 when OTHERS THEN
1810 IF l_lot_ser_flag = 'N' THEN --return other status to avoid overwrite
1811 --OF standard RCV failure mesg
1812 x_return_status := 'N';
1813 ELSE
1814 x_return_status := 'E';
1815 END IF;
1816
1817 IF (l_debug = 1) THEN
1818 trace('Other errror: process_rfid_receiving_txn');
1819 trace('SQL error :'||substr(sqlerrm, 1, 240));
1820 END IF;
1821
1822 END process_rfid_receiving_txn;
1823
1824
1825
1826 PROCEDURE parse_read_tags(p_tagid IN WMS_EPC_TAGID_TYPE,
1827 p_org_id IN NUMBER,
1828 x_tag_info OUT nocopy tag_info_tbl,
1829 x_pallet_lpn_id OUT nocopy NUMBER,
1830 x_pallet_lpn_context OUT nocopy NUMBER,
1831 x_tag_count OUT nocopy NUMBER,
1832 x_return_status out nocopy VARCHAR2) --S/E
1833 IS
1834
1835
1836 l_PARENT_lpn_id NUMBER;
1837 --l_tag_info tag_info_tbl;
1838 l_index NUMBER;
1839 l_parent_row_id NUMBER;
1840 L_pallet_lpn_context NUMBER;
1841 l_outermost_lpn_id NUMBER;
1842 l_is_error NUMBER := 0;
1843 l_prev_serial_pallet_id NUMBER;
1844 l_serial_pallet_id NUMBER;
1845 l_lpn_pallet_id NUMBER;
1846 l_prev_lpn_pallet_id NUMBER;
1847 l_gtin NUMBER;
1848 l_gtin_serial NUMBER;
1849
1850 l_cnt NUMBER;
1851
1852 l_cross_ref_type NUMBER;
1853 l_lpn_id NUMBER;
1854 l_item_id NUMBER;
1855 l_serial_number VARCHAR2(30);
1856
1857
1858 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1859 BEGIN
1860
1861
1862 x_return_status := 'S';
1863
1864 IF (l_debug = 1) THEN
1865 trace('Inside parse_read_tags');
1866 END IF;
1867
1868 ---get all the values from the tagid LOV and tagdata LOV
1869
1870
1871 IF p_tagid IS NOT null THEN
1872
1873 l_index := 1;
1874
1875 IF (l_debug = 1) THEN
1876 trace('Get all values of the EPC tags ######');
1877 END IF;
1878
1879 FOR i IN p_tagid.FIRST .. p_tagid.LAST
1880 LOOP
1881 IF (l_debug = 1) THEN
1882 trace('Tag values = ' || p_tagid(i));
1883 END IF;
1884
1885 x_tag_info(l_index).tag_id := to_CHAR(Ltrim(Rtrim(p_tagid(i))));
1886 l_index := l_index +1;
1887
1888 END LOOP;
1889
1890 --Return the tag count
1891 x_tag_count := x_tag_info.COUNT;
1892
1893 IF (l_debug = 1) THEN
1894 trace (' x_tag_count :'|| x_tag_count);
1895 END IF;
1896
1897
1898
1899 IF x_tag_count = 1 THEN
1900
1901 -- It can be LPN Name String OR EPC
1902
1903 BEGIN
1904 SELECT wlpn.lpn_id,wlpn.lpn_context,outermost_lpn_id INTO
1905 x_pallet_lpn_id,x_pallet_lpn_context,l_outermost_lpn_id
1906 FROM wms_license_plate_numbers wlpn,
1907 wms_epc we
1908 WHERE we.lpn_id = wlpn.lpn_id
1909 AND we.cross_ref_type =1 --LPN-EPC type
1910 AND we.epc = x_tag_info(1).tag_id
1911 AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND wlpn.parent_lpn_id IS NOT null) OR
1912 ( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
1913
1914 --process only the pallet LPN
1915 IF l_outermost_lpn_id <> x_pallet_lpn_id THEN
1916
1917 IF (l_debug = 1) THEN
1918 trace('parse_read_tags : read EPC must be outer LPN');
1919 END IF;
1920
1921 x_pallet_lpn_id := NULL;
1922 x_pallet_lpn_context := NULL;
1923 x_return_status := 'E';
1924 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_INNER_LPN_READ');--Already seeded
1925 FND_MSG_PUB.ADD;
1926
1927 END IF;
1928
1929
1930 EXCEPTION
1931 WHEN no_data_found THEN
1932
1933 --this value is not in the the cross reference table, try to see
1934 --IF the passed value is lpn name string instead
1935
1936 BEGIN
1937 SELECT wlpn.lpn_id,wlpn.lpn_context,wlpn.outermost_lpn_id INTO
1938 x_pallet_lpn_id,x_pallet_lpn_context,l_outermost_lpn_id
1939 FROM wms_license_plate_numbers wlpn
1940 WHERE wlpn.license_plate_number = x_tag_info(1).tag_id
1941 AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND wlpn.parent_lpn_id IS NOT null) OR
1942 ( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
1943
1944
1945 --process only the pallet LPN
1946 --Be it Shipping or Receiving only the outer LPN is needed
1947
1948 --Receiving can take inner LPN for receiving
1949 --partially though, so we have TO stop it here
1950
1951 --However Shipping does not support processing inner
1952 --lpn, so we have to discard those reads
1953
1954 IF l_outermost_lpn_id <> x_pallet_lpn_id THEN
1955
1956 IF (l_debug = 1) THEN
1957 trace('parse_read_tags : read LPN must be outer LPN');
1958 END IF;
1959
1960 x_pallet_lpn_id := NULL;
1961 x_pallet_lpn_context := NULL;
1962 x_return_status := 'E';
1963 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_INNER_LPN_READ');
1964 FND_MSG_PUB.ADD;
1965
1966 END IF;
1967
1968 EXCEPTION
1969 WHEN no_data_found THEN
1970 x_pallet_lpn_id := NULL;
1971 x_pallet_lpn_context := NULL;
1972 x_return_status := 'E';
1973 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
1974 FND_MSG_PUB.ADD;
1975
1976 RETURN;
1977
1978 END;
1979
1980 END;
1981
1982 ELSE --means x_tag_count > 1
1983 --Assumption: if it is coming as list of values using pallet filter from
1984 --edgeserver, it has to be list of EPCs and not list of LPN Names strings
1985
1986 l_prev_serial_pallet_id := -99;
1987 l_prev_lpn_pallet_id := -99;
1988
1989
1990 FOR i IN 1..x_tag_info.COUNT LOOP
1991
1992 --CODE LOGIC
1993 --1- get the cross_ref_type and all relevant value for
1994 --cross-reference from wms_epc table based on EPC
1995 --2-Query respective tables to ensure that cross-referenced
1996 --object IS indeed correct and get other relevant parameter values
1997
1998
1999 BEGIN
2000
2001 SELECT cross_ref_type , lpn_id , inventory_item_id, serial_number, gtin, gtin_serial
2002 INTO l_cross_ref_type , l_lpn_id , l_item_id, l_serial_number, l_gtin, l_gtin_serial
2003 FROM wms_epc we
2004 WHERE we.epc = x_tag_info(i).tag_id;
2005
2006
2007
2008 IF (l_debug = 1) THEN
2009 trace('l_cross_ref_type :'||l_cross_ref_type );
2010 trace('l_lpn_id :'||l_lpn_id);
2011 trace('l_item_id :'||l_item_id );
2012 trace('l_serial_number :'||l_serial_number);
2013 trace('l_gtin_serial :'||l_gtin_serial);
2014
2015 END IF;
2016
2017
2018 EXCEPTION
2019 WHEN no_data_found THEN
2020
2021 --Extraneous read, NOT a cross-referenced EPC read
2022
2023 IF (l_debug = 1) THEN
2024 trace('when no data found');
2025 END IF;
2026
2027
2028 x_pallet_lpn_id := NULL;
2029 x_pallet_lpn_context := NULL;
2030 x_return_status := fnd_api.g_ret_sts_error;
2031 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_READ');
2032 FND_MSG_PUB.ADD;
2033
2034 l_is_error :=1;
2035 EXIT; --exit the loop
2036
2037 WHEN too_many_rows THEN
2038 --THIS SHOULD NEVER HAPPEN AS EPC IS UNIQUE COLUMN IN THE TABLE
2039 x_pallet_lpn_id := NULL;
2040 x_pallet_lpn_context := NULL;
2041 x_return_status := fnd_api.g_ret_sts_error;
2042 FND_MESSAGE.SET_NAME('WMS', 'WMS_DUPLICATE_EPC');
2043 FND_MSG_PUB.ADD;
2044
2045 l_is_error :=1;
2046 EXIT; --exit the loop
2047 WHEN OTHERS THEN
2048 IF (l_debug = 1) THEN
2049 trace('WHEN OTHERS EXCEPTION OF QUERY ........');
2050 END IF;
2051
2052 x_pallet_lpn_id := NULL;
2053 x_pallet_lpn_context := NULL;
2054 x_return_status := fnd_api.g_ret_sts_error;
2055 FND_MESSAGE.SET_NAME('WMS', 'WMS_UNEXPECTED_ERR');
2056 FND_MSG_PUB.ADD;
2057
2058 l_is_error :=1;
2059
2060 EXIT; --exit the loop
2061
2062 END;
2063
2064
2065
2066 --2-see whether it is cross-referenced to LPN, serial_number or
2067 --to GTIN
2068
2069 IF l_cross_ref_type = 1 OR l_cross_ref_type IS NULL THEN
2070 --LPN-EPC cross reference
2071 --NULL TO SUPPORT OLD VALUES OF 11.5.10
2072 BEGIN
2073 -- This will match only for pallets and cases
2074
2075 SELECT wlpn.parent_lpn_id, wlpn.LPN_CONTEXT INTO
2076 l_parent_lpn_id,l_PALLET_LPN_CONTEXT
2077 FROM wms_license_plate_numbers wlpn
2078 WHERE wlpn.lpn_id = L_LPN_ID
2079 AND ((wlpn.parent_lpn_id = wlpn.outermost_lpn_id AND
2080 wlpn.parent_lpn_id IS NOT null)
2081 OR
2082 ( wlpn.parent_lpn_id IS NULL AND wlpn.lpn_id = wlpn.outermost_lpn_id ));
2083 EXCEPTION
2084 WHEN no_data_found THEN
2085
2086 --handle it but do not do anything here. leave it.In the verification part because
2087 --lpn_id, parent_lpn_id, item_id , serial_number,
2088 --GTIN ALL WILL BE null for
2089 --that entry in the x_tag_info pl/sql table,
2090
2091 NULL;
2092 END;
2093 --find if multiple pallet. irrespective of whether user
2094 --wants the verification OR NOT there should NOT be two
2095 --pallets IN the list OF EPCs.
2096 --Also if the somehow the pallet did not get read but if
2097 --ALL cases point TO the same Pallet, it should still PASS
2098
2099
2100 IF l_parent_lpn_id IS NULL THEN --this is pallet
2101 l_lpn_pallet_id := l_lpn_id;
2102 ELSE --this IS case
2103 l_lpn_pallet_id := l_parent_lpn_id;
2104 END IF;
2105
2106 IF l_lpn_pallet_id <> l_prev_lpn_pallet_id AND l_prev_lpn_pallet_id <> -99 THEN--this is to ensure this
2107
2108 --Error;
2109
2110 --Cases were read before the pallet and the Pallet
2111 --does not match WITH parent LPN of cases
2112 -------------------OR----------------
2113 -- Case belongs to multiple Pallet or there are
2114 --multiple Pallets
2115
2116 IF (l_debug = 1) THEN
2117 trace('Error: Multiple Pallets OR Cases belongs to multiple Pallets');
2118 END IF;
2119 x_return_status := 'E';
2120 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_MULTIPLE_PALLET');
2121 FND_MSG_PUB.ADD;
2122
2123 l_is_error := 1;
2124 EXIT; --exit the loop
2125
2126 END IF;
2127
2128
2129 l_prev_lpn_pallet_id := l_lpn_pallet_id;
2130
2131 x_tag_info(i).lpn_id := l_lpn_id;
2132 x_tag_info(i).parent_lpn_id := l_parent_lpn_id;
2133
2134
2135 ELSIF l_cross_ref_type = 2 THEN
2136
2137 --Serial_Number-EPC cross reference
2138 --Serial can be inside inner-pack (modeled as LPN) also
2139 --with NO tagging of inner-pack LPN so more than 1 level of
2140 --nesting and still valid scenario
2141
2142 BEGIN
2143 select MSN.LPN_ID,WLPN.outermost_LPN_ID
2144 INTO l_lpn_id, l_serial_pallet_id
2145 from mtl_serial_numbers MSN,
2146 WMS_LICENSE_PLATE_NUMBERS wlpn
2147 WHERE MSN.inventory_item_id = l_item_id
2148 AND MSN.serial_number = l_SERIAL_NUMBER
2149 AND MSN.lpn_id = WLPN.LPN_ID;
2150 EXCEPTION
2151 WHEN no_data_found THEN
2152
2153 --handle it but do not do anything here. leave it.In the verification part because
2154 --lpn_id, parent_lpn_id, item_id , serial_number,
2155 --GTIN ALL WILL BE null for
2156 --that entry in the x_tag_info pl/sql table,
2157
2158 NULL;
2159 END;
2160
2161 --Serial-tag might get read first or LPN-EPC (pallet or CASE)
2162
2163 IF l_prev_serial_pallet_id = -99 THEN
2164 IF l_serial_pallet_id IS NULL THEN
2165 --Error;
2166 --Serial has to be inside an outer pallet at any
2167 --LEVEL OF nesting
2168 IF (l_debug = 1) THEN
2169 trace('Error: There IS NO PALLET for Serial');
2170 END IF;
2171 x_return_status := 'E';
2172 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_NO_PALLET');
2173 FND_MSG_PUB.ADD;
2174
2175 l_is_error := 1;
2176 EXIT; --exit the loop
2177
2178 END IF ;
2179
2180 ELSE --means l_prev_serial_pallet_id <> -99
2181 IF l_serial_pallet_id <> l_prev_serial_pallet_id THEN
2182
2183 --Error;
2184 --Error: Serials BELONG TO MULTIPLE PALLET
2185 IF (l_debug = 1) THEN
2186 trace('Error: Serials belong to multiple Pallet');
2187 END IF;
2188 x_return_status := 'E';
2189 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_MULTIPLE_PALLET');
2190 FND_MSG_PUB.ADD;
2191
2192 l_is_error := 1;
2193 EXIT; --exit the loop
2194
2195 END IF;
2196
2197 END IF;
2198
2199 l_prev_serial_pallet_id := l_serial_pallet_id;
2200
2201 --Assign values
2202 x_tag_info(i).item_id := l_item_id ;
2203 x_tag_info(i).serial_number := l_serial_number;
2204 x_tag_info(i).lpn_id := NULL; --since it can be CASE or inner lpn
2205 x_tag_info(i).parent_lpn_id := l_serial_pallet_id;
2206
2207
2208 ELSIF l_cross_ref_type = 3 THEN
2209 --GTIN-EPC cross reference
2210
2211 --since there is no connection between GTIN and LPN,
2212 --we can NOT validate anything here
2213
2214 x_tag_info(i).gtin := l_gtin;
2215 x_tag_info(i).gtin_serial := l_gtin_serial;
2216
2217 ELSE
2218
2219 --Error-UNIDENTIFIED CROSS-REFERENCE TYPE
2220
2221 IF (l_debug = 1) THEN
2222 trace('Error:UNIDENTIFIED CROSS-REFERENCE TYPE');
2223 END IF;
2224 x_return_status := 'E';
2225 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_INVALID_READ');
2226 FND_MSG_PUB.ADD;
2227
2228 l_is_error := 1;
2229 EXIT; --exit the loop
2230 END IF;
2231
2232
2233 END LOOP;
2234
2235 --LPNs at all level will have same LPN context, picking last one
2236 x_pallet_lpn_context := l_pallet_lpn_context;
2237
2238
2239
2240 --if the somehow the pallet did not get read but if
2241 --ALL cases point TO the same Pallet, it should still PASS
2242
2243 IF l_serial_pallet_id <> l_lpn_pallet_id AND
2244 l_serial_pallet_id <> -99 THEN
2245 --LPN-EPC PALLET
2246 --AND serial-epc pallet DO NOT MATCH and there is at least
2247 --one serial epc also (There has to be one LPN pallet, NO
2248 --consideration OF l_lpn_pallet_id)
2249
2250 x_pallet_lpn_id := NULL;
2251 x_pallet_lpn_context := NULL;
2252 x_return_status := 'E';
2253 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_MULTIPLE_PALLET');
2254 FND_MSG_PUB.ADD;
2255
2256 l_is_error :=1;
2257
2258 ELSE
2259 x_pallet_lpn_id := l_lpn_pallet_id;
2260
2261 END IF;
2262
2263
2264 END IF;--corresponding to x_tag_count > 1
2265
2266
2267 ELSE --means p_tagid IS null
2268
2269 --NO EPC/LPN VALUE PASSED, just return Error
2270 x_pallet_lpn_id := NULL;
2271 x_pallet_lpn_context := NULL;
2272 x_return_status := 'E';
2273 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_READ');
2274 FND_MSG_PUB.ADD;
2275
2276 RETURN;
2277
2278 END IF;
2279
2280
2281 -- IF there is error then update wms_epc
2282 -- No need to insert into wms_device_Requests, as transaction is going
2283 --TO fail because OF the multiple palletS
2284
2285 IF l_is_error = 1 THEN
2286
2287 FOR j IN 1..x_tag_info.COUNT LOOP
2288 UPDATE wms_epc
2289 SET status = substr(fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F'),1,240),
2290 status_code = 'E'
2291 WHERE EPC = x_tag_info(j).TAG_id;
2292
2293 END LOOP;
2294
2295 END IF;
2296
2297 --Commit is done in the calling API
2298
2299
2300 EXCEPTION
2301 WHEN OTHERS THEN
2302 x_pallet_lpn_id := NULL;
2303 x_pallet_lpn_context := NULL;
2304 x_return_status := 'E';
2305 FND_MESSAGE.SET_NAME('WMS', 'UNEXPECTED ERROR');
2306 FND_MSG_PUB.ADD;
2307 IF (l_debug = 1) THEN
2308 trace ('parse_read_tags :Inside when others exception');
2309 trace ('SQL ERROR :'||SQLCODE);
2310 trace ('SQL ERROR :'||Sqlerrm);
2311 END IF;
2312
2313 END parse_read_tags;
2314
2315
2316
2317
2318 PROCEDURE verify_load
2319 (
2320 p_org_id IN NUMBER,
2321 p_device_id IN NUMBER,
2322 p_tag_info IN tag_info_tbl,
2323 p_pallet_lpn_id IN NUMBER,
2324 p_bus_event_id IN NUMBER,
2325 p_subinventory_code IN VARCHAR2,
2326 p_locator_id IN NUMBER,
2327 p_event_date IN DATE,
2328 x_return_status out nocopy VARCHAR2 --S/E
2329 ) AS
2330
2331 l_expected_case_cnt NUMBER;
2332 l_expected_ser_cnt NUMBER;
2333
2334 l_cur_percent NUMBER;
2335 l_cur_case_cnt NUMBER;
2336
2337 l_load_verify_threshold NUMBER ; --get it from org setup
2338 l_error_code NUMBER;
2339 l_msg_data VARCHAR2(240);
2340 l_request_id NUMBER;
2341
2342 l_case_cnt NUMBER;
2343 l_serial_cnt NUMBER;
2344 l_gtin_cnt NUMBER;
2345
2346 l_lpn_item_id NUMBER;
2347 l_total_qty NUMBER :=0;
2348 l_uom_code VARCHAR2(3);
2349 l_single_item BOOLEAN := TRUE;
2350 l_total_gtin_qty NUMBER := 0;
2351 l_total_lpn_qty NUMBER := 0;
2352 l_total_temp_qty NUMBER := 0;
2353
2354 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2355 --Assumption All cases in the pallet will use same rule and
2356 --threshold_percentage including the pallet
2357 G_PROFILE_GTIN VARCHAR2(100) := FND_PROFILE.value('INV:GTIN_CROSS_REFERENCE_TYPE');
2358 BEGIN
2359
2360 x_return_status := 'S';
2361
2362 SELECT wms_device_requests_s.nextval INTO l_request_id FROM dual;
2363 l_device_req_id_pkg := l_request_id; --to be used for the pallet record
2364
2365
2366 --By the time this API is called
2367 --1. it has been ensured that only one
2368 --pallet IS IN the list OF epc's. There might be cases from multiple
2369 --pallets though. that IS part FOR the verifiaction TO ensure
2370
2371 --2. LPN_id and prent_lpn_id has been set for each read in the p_tag_info table
2372
2373 --3. More than one tag is present
2374 IF (l_debug = 1) THEN
2375 trace('p_tag_count :'|| p_tag_info.COUNT);
2376 END IF;
2377
2378 /****************************************/
2379 /*
2380 ERROR code for verification
2381 0- VERIFIED
2382 1- ERROR_INVALID_PALLET_CASE
2383 2- ERROR_MIXED_CASES
2384 3- ERROR_UNDER_THRESHOL
2385 4- ERROR_UNDER_THRESHOLD
2386 5- ERROR_EXTRA_CASE
2387 */
2388 /****************************************/
2389
2390
2391 --See if the PALLET lpn has single item
2392 BEGIN
2393 SELECT SUM(WLC.primary_quantity),wlc.inventory_item_id
2394 INTO l_total_lpn_qty, l_lpn_item_id
2395 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
2396 WHERE wlpn.outermost_lpn_id = p_pallet_lpn_id
2397 AND wlpn.lpn_id = wlc.parent_lpn_id
2398 --AND wlc.organization_id = p_org_id
2399 AND wlc.organization_id = wlpn.organization_id
2400 GROUP BY WLC.inventory_item_id;
2401
2402 EXCEPTION
2403 WHEN too_many_rows THEN
2404 l_SINGLE_ITEM := FALSE;
2405
2406 WHEN OTHERS THEN
2407
2408 x_return_status := 'E';
2409 FND_MESSAGE.SET_NAME('WMS', 'UNEXPECTED_ERROR');
2410 FND_MSG_PUB.ADD;
2411
2412 IF l_debug = 1 then
2413 trace('ERROR CODE = ' || SQLCODE);
2414 trace('ERROR MESSAGE = ' || SQLERRM);
2415 END IF;
2416
2417 END;
2418
2419
2420
2421 --Process them against our WMS_EPC table shipment verification
2422 l_case_cnt :=0;
2423 l_serial_cnt := 0;
2424
2425 FOR i IN 1..p_tag_info.COUNT LOOP
2426
2427
2428 --Search for any extraneous read
2429 IF p_tag_info(i).lpn_id IS NULL AND p_tag_info(i).parent_lpn_id IS NULL
2430 AND p_tag_info(i).serial_number IS NULL
2431 AND p_tag_info(i).gtin IS NULL AND p_tag_info(i).gtin_serial IS NULL THEN
2432
2433 IF (l_debug = 1) THEN
2434 trace('Extraneous read ERROR');
2435 END IF;
2436
2437 l_error_code := 1;
2438 --mark all read EPC as invalid in EPC table
2439 x_return_status := 'E';
2440 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_READ');
2441 FND_MSG_PUB.ADD;
2442
2443 EXIT;
2444
2445 END IF;
2446
2447
2448 IF p_tag_info(i).parent_lpn_id IS NOT NULL AND P_pallet_lpn_id <> p_tag_info(i).parent_lpn_id THEN
2449 --Cases from different pallet are in this set of tags
2450
2451 x_return_status := 'E';
2452 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_MIXED_CASES');
2453 FND_MSG_PUB.ADD;
2454 EXIT;
2455 END IF;
2456
2457
2458 IF p_tag_info(i).LPN_ID IS NOT NULL AND p_tag_info(i).PARENT_LPN_ID IS NOT NULL THEN --CASE
2459 l_case_cnt := l_case_cnt +1;
2460 ELSIF p_tag_info(i).serial_number IS NOT NULL AND p_tag_info(i).PARENT_LPN_ID IS NOT NULL THEN --Serial
2461 l_serial_cnt := l_serial_cnt +1;
2462 ELSIF p_tag_info(i).gtin IS NOT NULL AND p_tag_info(i).gtin_serial IS NOT NULL THEN
2463
2464
2465 --ONLY FOR SINLGE ITEM IN PALLET WE DO THIS VALIDATION
2466 IF l_single_item THEN
2467 BEGIN
2468 --Assumption is that given a GTIN + Item_id + org_id , the set
2469 --up for cross reference will return only one record
2470 select uom_code INTO l_uom_code FROM mtl_cross_references mcr
2471 WHERE mcr.inventory_item_id = L_LPN_ITEM_ID
2472 AND mcr.CROSS_REFERENCE = To_char(p_tag_info(i).gtin)
2473 AND mcr.CROSS_REFERENCE_TYPE = G_PROFILE_GTIN
2474 AND (( mcr.org_independent_flag = 'Y' AND mcr.organization_id IS NULL) OR
2475 (mcr.org_independent_flag = 'N' AND mcr.organization_id = p_org_id ));
2476
2477 EXCEPTION
2478 WHEN too_many_rows THEN
2479 x_return_status := 'E';
2480 IF (l_debug = 1) THEN
2481 trace ('Validating GTIN: Inside too_many_rows for GTIN :'||p_tag_info(i).gtin);
2482 END IF;
2483
2484 EXIT;
2485
2486 WHEN OTHERS THEN
2487 x_return_status := 'E';
2488 IF (l_debug = 1) THEN
2489 trace ('Validating GTIN: Inside when others exception');
2490 trace ('SQL ERROR :'||SQLCODE);
2491 trace ('SQL ERROR :'||Sqlerrm);
2492 END IF;
2493
2494 EXIT;
2495
2496 END;
2497
2498
2499 SELECT conversion_rate INTO l_total_temp_qty FROM mtl_uom_conversions_view mucv
2500 where organization_id = p_org_id
2501 and uom_code = l_uom_code
2502 AND INVENTORY_ITEM_ID = l_lpn_item_id;
2503
2504 l_total_gtin_qty := l_total_temp_qty + l_total_gtin_qty;
2505
2506
2507 END IF; --single item
2508
2509
2510 END IF;
2511
2512 END LOOP;
2513
2514 IF (l_debug = 1) THEN
2515 trace('CASE COUNT ::'|| l_case_cnt);
2516 trace('SERIAL COUNT ::'|| l_serial_cnt);
2517 trace ('Total_gtin_qty :'||l_total_gtin_qty);
2518 END IF;
2519
2520
2521 IF x_return_status = 'S' THEN
2522
2523 BEGIN
2524 wms_rfid_ext_pub.get_new_load_verif_threshold(p_org_id => p_org_id,
2525 p_pallet_lpn_id => p_pallet_lpn_id,
2526 x_new_load_verif_threshold => l_load_verify_threshold,
2527 x_return_status => x_return_status);
2528 EXCEPTION
2529 WHEN OTHERS THEN
2530 x_return_status := 'E' ;
2531 END;
2532
2533 IF x_return_status = 'S' THEN
2534
2535 IF l_load_verify_threshold IS NULL THEN
2536 --DO NOT OVERRIDE
2537 SELECT Nvl(rfid_verif_pcnt_threshold,0) INTO l_load_verify_threshold
2538 FROM mtl_parameters WHERE organization_id = p_org_id;
2539 END IF;
2540
2541 ELSE
2542 x_return_status := 'S';
2543
2544 SELECT Nvl(rfid_verif_pcnt_threshold,0) INTO l_load_verify_threshold
2545 FROM mtl_parameters WHERE organization_id = p_org_id;
2546
2547 END IF;
2548
2549
2550 IF (l_debug = 1) THEN
2551 trace('l_load_verify_threshold :'||l_load_verify_threshold);
2552 END IF;
2553
2554 SELECT COUNT(1) INTO l_expected_case_cnt
2555 FROM wms_license_plate_numbers wlpn
2556 WHERE parent_lpn_id = p_pallet_lpn_id
2557 AND wlpn.parent_lpn_id = wlpn.outermost_lpn_id
2558 AND wlpn.parent_lpn_id IS NOT NULL;
2559
2560
2561 SELECT COUNT(1) INTO l_expected_ser_cnt
2562 FROM mtl_serial_numbers msn, wms_license_plate_numbers wlpn
2563 WHERE msn.lpn_id = wlpn.lpn_id
2564 and wlpn.outermost_lpn_id = p_pallet_lpn_id;
2565
2566 IF (l_debug = 1) THEN
2567 trace('l_expected_case_cnt :'||l_expected_case_cnt);
2568 trace('l_expected_SER_cnt :' ||l_expected_ser_cnt);
2569
2570 END IF;
2571
2572
2573 IF l_expected_case_cnt <> 0 THEN
2574
2575 l_cur_percent := ((l_case_cnt + l_serial_cnt)/(l_expected_case_cnt+l_expected_ser_cnt))*100;
2576 ELSE
2577 l_cur_percent := 0;
2578 END IF;
2579
2580 IF (l_debug = 1) THEN
2581 trace('l_cur_percent :'|| l_cur_percent);
2582 END IF;
2583 --Bug 5636478 - Moving the condition l_load_verify_threshold=0 to the IF condition
2584 --from ELSIF as the Verification is failing if Required Percentage of Load = 0
2585 --In this case it must succeed
2586 IF l_cur_percent = 100 OR
2587 ( l_load_verify_threshold <> 0 AND l_cur_percent >=
2588 l_load_verify_threshold AND l_cur_percent < 100 ) OR
2589 l_load_verify_threshold = 0 THEN
2590
2591 --verification SUCCEEDED....update associated case records amd
2592 --CURRENT pallet with STATUS='VALID'
2593
2594 --update all the CASE + PALLET set recods as VALID, including
2595 --the unread ones
2596 IF (l_debug = 1) THEN
2597 trace(' sucessful validation');
2598 END IF;
2599
2600 x_return_status := 'S';
2601 FND_MESSAGE.SET_NAME('WMS', 'WMS_VERIFY_COMPLETE');
2602 FND_MSG_PUB.ADD;
2603
2604 ELSIF l_cur_percent < l_load_verify_threshold THEN
2605
2606 IF (l_debug = 1) THEN
2607 trace('Failed validation');
2608 END IF;
2609 x_return_status := 'E';
2610
2611 FND_MESSAGE.SET_NAME('WMS', 'WMS_VERIF_UNDER_THRESHOLD');
2612 FND_MSG_PUB.ADD;
2613
2614
2615 ELSIF l_cur_percent > 100 THEN --case when there are more cases on
2616 --the pallet the expected, amy be OF other pallet
2617
2618 x_return_status := 'E';
2619 FND_MESSAGE.SET_NAME('WMS','WMS_VERIF_EXTRA_CASE');
2620 FND_MSG_PUB.ADD;
2621 END IF;
2622
2623 END IF; -- x_return_status = 'S'
2624
2625
2626 --Now check for GTIN tag failure condition
2627 --this check condition is applied only if the LPN has single items.
2628
2629
2630 IF l_single_item AND x_return_status = 'S' THEN -- VALIDATE FOR GTIN
2631
2632 IF (l_debug = 1) THEN
2633 trace('***** Total LPN qty :'||l_total_lpn_qty);
2634 END IF;
2635
2636 IF l_total_lpn_qty < l_total_gtin_qty THEN
2637 x_return_status := 'E';
2638 FND_MESSAGE.SET_NAME('WMS','WMS_VERIF_EXTRA_GTIN_PACK');
2639 FND_MSG_PUB.ADD;
2640
2641 END IF;
2642
2643 ELSE
2644 IF (l_debug = 1) THEN
2645 trace('No GTIN level validation is needed for Multi-Item Pallet');
2646 END IF;
2647
2648 END IF;
2649
2650
2651 --Update appropriate tables with verification result
2652
2653 --get the failue/success meag
2654 l_msg_data := substr(fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F'),1,240);
2655
2656 IF x_return_status = 'E' THEN
2657
2658 --1. UPDATE WMS_EPC TABLE FOR FAILURE FOR ALL READS
2659 --2. INSERT INTO WMS_DEVICE_REQUESTs TABLE FOR READ CASE TAGS, NOT THE PALLET
2660 -- pallet will be transferred in the process_rfid_txn table and all
2661 -- above records will be moved TO the wms_device_request_hist table
2662
2663 /* we can not use the group_id to update wms_epc table in one shot
2664 here as all records of the group_id might not be read. I have to
2665 update only those records which have been read by the reader.
2666 */
2667
2668 IF l_error_code = 1 THEN --Extraneous read
2669
2670 FOR j IN 1..p_tag_info.COUNT LOOP
2671 UPDATE wms_epc
2672 SET status = l_msg_data,
2673 status_code = x_return_status
2674 WHERE EPC = p_tag_info(j).tag_id;--Since no LPN
2675 --corredponding TO the extraneous read
2676
2677
2678 IF p_tag_info(j).parent_lpn_id IS NOT NULL THEN --Pallet rec
2679 --will be handled later
2680
2681 INSERT INTO wms_device_requests (request_id,
2682 business_event_id,
2683 organization_id,
2684 lpn_id,
2685 device_id,
2686 subinventory_code,
2687 locator_id,
2688 status_code,
2689 status_msg,
2690 last_update_date,
2691 last_updated_by
2692 ) VALUES
2693 (l_request_id,
2694 p_bus_event_id ,
2695 p_org_id,
2696 p_tag_info(j).LPN_id,
2697 p_device_id,
2698 p_subinventory_code,
2699 p_locator_id,
2700 x_return_status,
2701 l_msg_data,
2702 p_event_date,
2703 fnd_global.user_id);
2704
2705 END IF;
2706
2707 END LOOP;
2708
2709 ELSE
2710
2711 FOR j IN 1..p_tag_info.COUNT LOOP
2712 UPDATE wms_epc
2713 SET status = l_msg_data,
2714 status_code = x_return_status
2715 WHERE lpn_id = p_tag_info(j).lpn_id;
2716
2717 IF p_tag_info(j).parent_lpn_id IS NOT NULL THEN --Pallet rec
2718 --will be handled later
2719
2720 INSERT INTO wms_device_requests (request_id,
2721 business_event_id,
2722 organization_id,
2723 lpn_id,
2724 device_id,
2725 subinventory_code,
2726 locator_id,
2727 status_code,
2728 status_msg,
2729 last_update_date,
2730 last_updated_by
2731 ) VALUES
2732 (l_request_id,
2733 p_bus_event_id ,
2734 p_org_id,
2735 p_tag_info(j).LPN_id,
2736 p_device_id,
2737 p_subinventory_code,
2738 p_locator_id,
2739 x_return_status,
2740 l_msg_data,
2741 p_event_date,
2742 fnd_global.user_id);
2743
2744 END IF;
2745
2746 END LOOP;
2747
2748 END IF;
2749
2750 ELSIF x_return_status= 'S' THEN
2751
2752 --1. UPDATE WMS_EPC TABLE FOR VERIFICATION SUCESSSFUL FOR ALL READS
2753 --NOTHING IN THE WMS_DEVICE_REQUEST table, no case records in the wms
2754 --device history TABLE IN successful scenario
2755
2756 FOR j IN 1..p_tag_info.COUNT LOOP
2757 UPDATE wms_epc
2758 SET status = l_msg_data,
2759 status_code = x_return_status
2760 WHERE EPC = p_tag_info(j).tag_id;
2761
2762 END LOOP;
2763
2764 END IF;
2765
2766
2767 --Commit is done in the calling API
2768
2769
2770 EXCEPTION
2771 WHEN OTHERS THEN
2772 x_return_status := 'E';
2773 IF (l_debug = 1) THEN
2774 trace ('verify_load : Inside when others exception');
2775 trace ('SQL ERROR :'||SQLCODE);
2776 trace ('SQL ERROR :'||Sqlerrm);
2777 END IF;
2778
2779 END verify_load;
2780
2781
2782
2783 procedure process_rfid_txn
2784 (
2785 p_tagid in WMS_EPC_TAGID_TYPE, -- EPC TAGS ID VALUE, IN VARRAY
2786 p_tagdata IN WMS_EPC_TAGDATA_TYPE,-- ANY ADDITIONAL DATA IN VARRAY
2787 p_portalid in VARCHAR2,
2788 p_event_date in date,
2789 p_system_id in VARCHAR2 DEFAULT null,
2790 p_statuschange in NUMBER DEFAULT null,
2791 p_datachange in NUMBER DEFAULT null,
2792 p_status in NUMBER DEFAULT null,
2793 p_x in NUMBER DEFAULT null,
2794 p_y in NUMBER DEFAULT NULL,
2795 x_return_value out nocopy VARCHAR2, --success,error,warning
2796 x_return_mesg out nocopy varchar2
2797 ) IS
2798
2799 end_processing EXCEPTION;--Exception to stop processing
2800
2801 l_tag_info tag_info_tbl;
2802 l_device_enabled VARCHAR2(1);
2803 l_lpn_context NUMBER;
2804 l_lpn_id NUMBER;
2805 l_device_id NUMBER;
2806 l_organization_id NUMBER;
2807 l_subinventory_code VARCHAR2(30);
2808 l_locator_id NUMBER;
2809 l_outermost_lpn_id NUMBER;
2810 l_output_method_id NUMBER;
2811 l_request_id NUMBER;
2812 l_return_status VARCHAR2(1);
2813 l_shipment_header_id NUMBER;
2814 l_routing_id NUMBER;
2815 l_msg_count NUMBER;
2816 l_msg_data VARCHAR2(500);
2817 l_is_expense VARCHAR2(1);
2818 l_is_valid_txn_device NUMBER;
2819 l_progress VARCHAR2(8);
2820 l_out_business_event_id NUMBER;
2821 l_is_last_lpn_load NUMBER;
2822 l_user_id NUMBER;
2823 l_resp_appl_id NUMBER;
2824 l_resp_id NUMBER;
2825
2826 l_tagid VARCHAR2(500);
2827 l_tag_len NUMBER;
2828 l_tag_count NUMBER;
2829 l_verif_req VARCHAR2(1);
2830
2831 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2832
2833 BEGIN
2834 x_return_value := 'success';
2835
2836
2837 IF (l_debug = 1) THEN
2838 trace('Entering the call process_rfid_txn ');
2839 END IF;
2840
2841 IF fnd_global.user_id = -1 OR fnd_global.user_id IS NULL THEN
2842
2843 l_user_id := fnd_profile.value('WMS_RFID_USER');
2844 select APPLICATION_ID INTO l_resp_appl_id from fnd_application where
2845 APPLICATION_SHORT_NAME = 'WMS' AND ROWNUM <2;
2846 l_resp_id := 21676; --Corresponding to "Warehouse Manager" responsibility
2847
2848 fnd_global.apps_initialize(l_user_id,l_resp_id,l_resp_appl_id);
2849 IF (l_debug = 1) THEN
2850 trace('Setting the user context for RFID Txn');
2851 END IF;
2852 END IF;
2853
2854
2855 FND_MSG_PUB.initialize;
2856
2857 IF (l_debug = 1) THEN
2858 trace('Inside process_rfid_txn rfid_user_id :'||l_user_id||
2859 --', LPN/EPC name :'||l_tagid||
2860 ', device_id :'||p_portalid||
2861 ', p_event_date :'||p_event_date||
2862 ', p_system_id :'||p_system_id||
2863 ', p_statuschange :'||p_statuschange||
2864 ', p_datachange :'||p_datachange);
2865 END IF;
2866
2867
2868 l_progress := '10';
2869
2870 BEGIN
2871 SELECT
2872 device_id,enabled_flag,organization_id,subinventory_code,locator_id,output_method_id
2873 INTO l_device_id,l_device_enabled,l_organization_id,l_subinventory_code,l_locator_id,l_output_method_id
2874 FROM wms_devices_vl
2875 WHERE name = p_portalid; --Device Name
2876 EXCEPTION
2877 WHEN no_data_found THEN
2878 IF (l_debug = 1) THEN
2879 trace('process_rfid_txn :No device defined');
2880 --Can not generate xml since organization_id is required
2881 --column IN the wdr AND we have no information about it here
2882 END IF;
2883 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_DEVICE_FOUND');
2884 FND_MSG_PUB.ADD;
2885 raise end_processing;
2886
2887 WHEN too_many_rows THEN
2888 IF (l_debug = 1) THEN
2889 trace('Error :Multiple devices with same device_name');
2890 END IF;
2891 FND_MESSAGE.SET_NAME('WMS', 'DUPLICATE_DEVICE_ENTRY');
2892 FND_MSG_PUB.ADD;
2893 raise end_processing;
2894
2895 WHEN OTHERS then
2896 IF (l_debug = 1) THEN
2897 trace('Other error in finding device');
2898 trace('SQL error :'||substr(sqlerrm, 1, 240));
2899
2900 END IF;
2901 raise end_processing;
2902 END;
2903
2904 l_progress := '20';
2905
2906 IF (l_debug = 1) THEN
2907 trace('process_rfid_txn :device_id,locator_id::Org_id::'||l_device_id||'::'||l_locator_id||'::'||l_organization_id);
2908 END IF;
2909
2910 --parse tags, get the pallet info
2911 --get appropriate bus event
2912 --process verification, if needed
2913 --process transaction
2914
2915 --PARSE READ TAGS
2916 parse_read_tags(p_tagid => p_tagid,
2917 p_org_id => l_organization_id,
2918 x_tag_info => l_tag_info,
2919 x_pallet_lpn_id => l_lpn_id,
2920 x_pallet_lpn_context => l_lpn_context,
2921 x_tag_count => l_tag_count,
2922 x_return_status => l_return_status);
2923
2924
2925 --Committing the update of failue cases in parse_read_tags
2926 COMMIT;
2927
2928 IF (l_debug = 1) THEN
2929 trace('process_rfid_txn Pallet_lpn_id :'||l_lpn_id);
2930 trace('process_rfid_txn l_lpn_context :'||l_lpn_context);
2931 END IF;
2932
2933
2934 IF L_return_status <> 'S' THEN
2935
2936 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);
2937 raise end_processing;
2938
2939 END IF;
2940
2941
2942 --Create a save point here
2943 SAVEPOINT wms_rfid_sp;
2944
2945
2946 l_progress := '30';
2947
2948 IF (l_debug = 1) THEN
2949 trace('process_rfid_txn:LPN is good l_lpn_context::'||l_lpn_context);
2950 END IF;
2951
2952
2953 --make sure that the the context of LPN is eligible for devices set up in
2954 --the device assignment form
2955
2956 is_valid_txn_device(p_device_id => l_device_id,
2957 p_lpn_context => l_lpn_context,
2958 p_organization_id => l_organization_id,
2959 x_out_business_event_id => l_out_business_event_id,--to distinguish truck_load
2960 --Vs truck_load_ship and Direct rcv Vs Std/Insp rcv
2961 x_valid_device_for_txn => l_is_valid_txn_device,
2962 x_verif_req => l_verif_req );
2963
2964
2965
2966 IF (l_debug = 1) THEN
2967 trace('process_rfid_txn:l_out_business_event_id::l_is_valid_txn_device ::'||l_out_business_event_id||'::'||l_is_valid_txn_device);
2968 END IF;
2969
2970
2971 IF l_is_valid_txn_device <> 1 THEN
2972 IF (l_debug = 1) THEN
2973 trace('process_rfid_txn :Error:No or Multiple valid business events in the set up');
2974 END IF;
2975
2976 IF l_is_valid_txn_device = 0 THEN
2977 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_NO_ELIGIBLE_BUS_EVENT');
2978 FND_MSG_PUB.ADD;
2979 ELSIF l_is_valid_txn_device = -1 THEN
2980 IF (l_debug = 1) THEN
2981 trace('process_rfid_txn : Error: Both Truck_load and Truck_load_ship are associated');
2982 END IF;
2983 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_BUS_ASSOC');
2984 FND_MSG_PUB.ADD;
2985 ELSIF l_is_valid_txn_device = -2 THEN
2986 IF (l_debug = 1) THEN
2987 trace('process_rfid_txn : Error: Direct rcv and Std/Insp both with same device');
2988 END IF;
2989 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_BUS_ASSOC');
2990 FND_MSG_PUB.ADD;
2991 ELSIF l_is_valid_txn_device = -3 THEN
2992 IF (l_debug = 1) THEN
2993 trace('process_rfid_txn : Error: Invalid LPN context');
2994 END IF;
2995 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN_CONTEXT');
2996 FND_MSG_PUB.ADD;
2997
2998 END IF;
2999
3000 --Insert generate XML/CSV or Call API for wms_be_rfid_error bus
3001 --event
3002 ROLLBACK TO wms_rfid_sp;
3003 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);
3004 raise end_processing;
3005
3006
3007 ELSE --is_transaction_valid
3008
3009 l_progress := '30.5';
3010 IF (l_debug = 1) THEN
3011 trace('process_rfid_txn : valid business events have been set up');
3012 trace('l_out_business_event_id :'|| l_out_business_event_id);
3013 trace('l_verif_req :'||l_verif_req );
3014 END IF;
3015
3016
3017 --PERFORM THE VERIFICATION only if tag count > 1
3018 IF l_verif_req = 'Y' THEN
3019 IF l_tag_count > 1 THEN
3020 IF (l_debug = 1) THEN
3021 trace('Starting the verification process.....');
3022 END IF;
3023
3024 verify_load
3025 (
3026 p_org_id => l_organization_id,
3027 p_device_id => l_device_id,
3028 p_tag_info => l_tag_info,
3029 p_pallet_lpn_id => l_lpn_id,
3030 p_bus_event_id => l_out_business_event_id,
3031 p_subinventory_code => l_subinventory_code,
3032 p_locator_id => l_locator_id,
3033 p_event_date => p_event_date,
3034 x_return_status => l_return_status
3035 );
3036
3037 ELSIF l_tag_count = 1 THEN
3038
3039 IF (l_debug = 1) THEN
3040 trace('Error: Verification of a Single LPN');
3041 END IF;
3042
3043 FND_MESSAGE.SET_NAME('WMS', 'WMS_VERIF_UNDER_THRESHOLD');
3044 FND_MSG_PUB.ADD;
3045 l_return_status:= 'E';
3046
3047 END IF;
3048
3049
3050 END IF;
3051
3052
3053 trace(' verify_load returned status:'||l_return_status);
3054
3055 --Whatever happens in verification later, result of verficiation needs to be commited
3056 -- commit all the changes in the WMS_EPC table
3057 COMMIT;
3058
3059
3060 IF l_return_status <> 'S' THEN
3061 IF (l_debug = 1) THEN
3062 trace(' INSIDE VERIFY FAILURE, CALLING GENERATE xml api');
3063 END IF;
3064 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);
3065 raise end_processing;
3066
3067 END IF;
3068
3069
3070 l_progress := '30.7';
3071
3072
3073 --define a new savepoint for further processing
3074 SAVEPOINT wms_rfid_sp1;
3075
3076 IF (l_debug = 1) THEN
3077 trace('AFTER VALIDATION l_return_status::'||l_return_status);
3078 END IF;
3079
3080 --PROCESS THE ACTUAL TRANSACTION
3081 IF l_return_status ='S' THEN --Verification successful
3082
3083 IF (l_lpn_context = wms_container_pub.lpn_context_inv) THEN
3084
3085 IF (l_debug = 1) THEN
3086 trace('process_rfid_txn :Direct Ship');
3087 END IF;
3088
3089
3090 IF l_out_business_event_id= wms_device_integration_pvt.wms_be_TRUCK_LOAD THEN
3091
3092 IF (l_debug = 1) THEN
3093 trace('process_rfid_txn :Processing Direct Truck Load');
3094 END IF;
3095
3096 l_progress := '40';
3097
3098 --process truck_load txn
3099 process_direct_truck_load(p_lpn_id => l_lpn_id,
3100 p_org_id => l_organization_id,
3101 p_dock_door_id => l_locator_id,
3102 x_is_last_lpn_load => l_is_last_lpn_load,
3103 x_return_status => l_return_status
3104 );
3105
3106 l_progress := '50';
3107
3108 IF (l_debug = 1) THEN
3109 trace('process_rfid_txn:After process_direct_truck_load:l_return_status:"'||l_return_status);
3110 END IF;
3111
3112 IF l_return_status IS NULL OR l_return_status = 'E' OR l_return_status = 'U'THEN
3113
3114 --Error mesg should have been at the point of failure
3115
3116 --Insert record into the WMS_DEVICE_REQUESTS table
3117 --Generate xml/call API for truck_load business event
3118 --populate in the history table
3119 ROLLBACK TO wms_rfid_sp1;
3120 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);
3121 x_return_value := 'error';
3122
3123 ELSE
3124 --Truck Load Txn Successful
3125 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);
3126
3127 END IF;
3128
3129
3130 elsif l_out_business_event_id = wms_device_integration_pvt.wms_be_truck_load_ship THEN
3131
3132 IF (l_debug = 1) THEN
3133 trace('process_rfid_txn :Processing Direct Truck Load and SHIP');
3134 END IF;
3135
3136 --process truck_load_ship txn and inside make sure that the
3137 --lpn is COMPLETELY reserved against a SO
3138 l_progress := '40';
3139
3140 process_direct_truck_load_SHIP(p_lpn_id => l_lpn_id,
3141 p_org_id => l_organization_id,
3142 p_dock_door_id => l_locator_id,
3143 x_return_status => l_return_status
3144 );
3145
3146 l_progress := '50';
3147
3148
3149 IF (l_debug = 1) THEN
3150 trace('process_rfid_txn:After process_direct_truck_load_SHIP:l_return_status:'||l_return_status);
3151 trace('process_rfid_txn: message stored'||l_ship_confirm_pkg_mesg);
3152 END IF;
3153
3154 IF l_return_status IS NULL OR l_return_status = 'E' OR
3155 l_return_status = 'U' THEN
3156
3157 --Error mesg should have been at the point of failure
3158
3159 --Insert record into the WMS_DEVICE_REQUESTS table
3160 --Generate xml/call API for truck_load business event
3161 --populate in the history table
3162 ROLLBACK TO wms_rfid_sp1;
3163 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);
3164 x_return_value := 'error';
3165
3166 ELSE
3167 --Truck Load Txn Successful
3168 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);
3169
3170 END IF;
3171
3172 END IF; --FOR l_out_business_event_id
3173
3174
3175 ELSIF (l_lpn_context = wms_container_pub.lpn_context_picked ) THEN
3176 IF (l_debug = 1) THEN
3177 trace('process_rfid_txn :Normal LPN Ship');
3178 END IF;
3179
3180 IF l_out_business_event_id= wms_device_integration_pvt.wms_be_TRUCK_LOAD THEN
3181
3182 IF (l_debug = 1) THEN
3183 trace('process_rfid_txn :Processing Normal Truck Load');
3184 END IF;
3185
3186
3187 --dock-door for the device is locator of the device
3188 --The device assignment form will make sure that only those devices
3189 --are associated with Truck_load AND Truck_Load_Ship business events
3190 --for which Sub/Loc are defined.So if it comes here l_locator_id
3191 --will have value.
3192
3193 l_progress := '40';
3194
3195 process_normal_truck_load(p_lpn_id => l_lpn_id,
3196 p_org_id => l_organization_id,
3197 p_dock_door_id => l_locator_id,
3198 x_is_last_lpn_load => l_is_last_lpn_load,
3199 x_return_status => l_return_status);
3200
3201 l_progress := '50';
3202
3203
3204 IF (l_debug = 1) THEN
3205 trace('process_rfid_txn:After process_normal_truck_load:l_return_status:"'||l_return_status);
3206 END IF;
3207
3208 IF l_return_status IS NULL OR l_return_status = 'E' THEN
3209
3210 --Error mesg should have been at the point of failure
3211
3212 --Insert record into the WMS_DEVICE_REQUESTS table
3213 --Generate xml/call API for truck_load business event
3214 --populate in the history table
3215
3216 ROLLBACK TO wms_rfid_sp1;
3217 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);
3218 x_return_value := 'error';
3219 ELSE
3220 --Truck Load Txn Successful
3221 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);
3222
3223 END IF;
3224
3225
3226 ELSIF l_out_business_event_id= wms_device_integration_pvt.wms_be_TRUCK_LOAD_ship THEN
3227
3228 IF (l_debug = 1) THEN
3229 trace('process_rfid_txn :Processing Normal Truck Load and SHIP');
3230 END IF;
3231
3232 --dock-door for the device is locator of the device
3233 --The device assignment form will make sure that only those devices
3234 --are associated with Truck_load AND Truck_Load_Ship business events
3235 --for which Sub/Loc are defined.So if it comes here l_locator_id
3236 --will have value.
3237 l_progress := '40';
3238
3239 process_normal_truck_load_ship(p_lpn_id => l_lpn_id,
3240 p_org_id => l_organization_id,
3241 p_dock_door_id => l_locator_id,
3242 x_return_status => l_return_status
3243 );
3244
3245 l_progress := '50';
3246
3247 IF (l_debug = 1) THEN
3248 trace('process_rfid_txn:After process_normal_truck_load_ship:l_return_status:'||l_return_status);
3249 trace('process_rfid_txn:Return Message::'||l_ship_confirm_pkg_mesg);
3250 END IF;
3251
3252
3253
3254 IF l_return_status IS NULL OR l_return_status = 'E' THEN
3255
3256 --Insert record into the WMS_DEVICE_REQUESTS table
3257 --Generate xml/call API for truck_load business event
3258 --populate in the history table
3259
3260 ROLLBACK TO wms_rfid_sp1;
3261 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);
3262 x_return_value := 'error';
3263 ELSE
3264
3265 --Truck Load ship Txn Successful
3266 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);
3267
3268 END IF;
3269
3270 END IF;
3271
3272 ELSIF (l_lpn_context = wms_container_pub.lpn_context_intransit OR
3273 l_lpn_context = wms_container_pub.lpn_context_vendor) THEN
3274
3275 IF (l_debug = 1) THEN
3276 trace('process_rfid_txn : processing receiving txn');
3277 END IF;
3278
3279 --Only possible transactions with these LPN conexts can be
3280 --receiving
3281 l_progress := '40';
3282 BEGIN
3283 select rsh.shipment_header_id into l_shipment_header_id from
3284 rcv_shipment_headers rsh ,wms_license_plate_numbers wlpn where wlpn.lpn_id = l_lpn_id
3285 and wlpn.lpn_context IN (6,7) --for ASN 7, blocked for ASN in patch set J
3286 and (rsh.shipment_num = Nvl(wlpn.source_name,'@#$@')
3287 or rsh.shipment_header_id = Nvl(wlpn.source_header_id, -1));
3288
3289 l_progress := '50';
3290
3291 exception
3292 when no_data_found then
3293 IF (l_debug = 1) THEN
3294 trace('process_rfid_txn:No record found with LPN ::'||l_lpn_id);
3295 END IF;
3296 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_INVALID_RCV_LPN ');
3297 FND_MSG_PUB.ADD;
3298 ROLLBACK TO wms_rfid_sp1;
3299 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);
3300 raise end_processing;
3301 end;
3302
3303 IF (l_debug = 1) THEN
3304 trace('process_rfid_txn:for internal req/ASN shipment_header_id::'||l_shipment_header_id);
3305 END IF;
3306
3307 if l_shipment_header_id is not null then
3308
3309 l_progress := '60';
3310
3311 IF l_lpn_context = 7 THEN
3312
3313 inv_rcv_common_apis.get_asn_routing_id
3314 (x_asn_routing_id => l_routing_id
3315 , x_return_status => l_return_status
3316 , x_msg_count => l_msg_count
3317 , x_msg_data => l_msg_data
3318 , p_shipment_header_id => l_shipment_header_id
3319 , p_lpn_id => l_lpn_id
3320 , p_po_header_id => NULL);
3321
3322 ELSE
3323
3324 inv_rcv_common_apis.get_routing_id
3325 (
3326 x_routing_id => l_routing_id,
3327 x_return_status => l_return_status,
3328 x_msg_count => l_msg_count,
3329 x_msg_data => l_msg_data,
3330 x_is_expense => l_is_expense,
3331 p_po_header_id => null,
3332 p_po_release_id => null,
3333 p_po_line_id => null,
3334 p_shipment_header_id => l_shipment_header_id,
3335 p_oe_order_header_id => null,
3336 p_item_id => null,
3337 p_organization_id => l_organization_id,
3338 p_vendor_id => null,
3339 p_lpn_id => l_lpn_id);
3340
3341 END IF;
3342
3343 l_progress := '70';
3344
3345 IF (l_debug = 1) THEN
3346 trace('process_rfid_txn:p_routing_id,l_return_status,l_msg_count,l_msg_data');
3347
3348 trace('process_rfid_txn:'||l_routing_id||'::'||l_return_status||'::'||l_msg_count||'::'||l_msg_data);
3349 END IF;
3350
3351
3352 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3353
3354 IF (l_debug = 1) THEN
3355 trace(' Error : inv_rcv_common_apis.failed in getting _routing_id');
3356 END IF;
3357 ROLLBACK TO wms_rfid_sp1;
3358 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);
3359 raise end_processing;
3360
3361 ELSE -- get_routing_id succeeded
3362
3363
3364 --make sure that the returned routing_id matches with the
3365 --device setup for business event
3366 --possible case: if the transaction record for rcv gives Direct
3367 --routing whereas the device_id passed is setup for Std/Insp
3368 --business event this transaction should fail
3369
3370 IF ((l_routing_id = 3 AND l_out_business_event_id = wms_device_integration_pvt.wms_be_direct_receipt) OR
3371 (l_routing_id IN (1,2)
3372 AND l_out_business_event_id = wms_device_integration_pvt.wms_be_std_insp_receipt)) THEN
3373
3374 IF (l_debug = 1) THEN
3375 trace('process_rfid_txn: Processing receiving txn');
3376 END IF;
3377 l_progress := '80';
3378
3379 process_rfid_receiving_txn(p_lpn_id => l_lpn_id,
3380 p_device_id => l_device_id,
3381 p_dest_org_id => l_organization_id,
3382 p_lpn_context => l_lpn_context,
3383 p_routing_id => l_routing_id,
3384 p_shipment_header_id => l_shipment_header_id,
3385 p_direct_putaway_sub => l_subinventory_code,
3386 p_direct_putaway_loc => l_locator_id,
3387 x_return_status => l_return_status);
3388
3389 l_progress := '90';
3390
3391 IF l_return_status <> fnd_api.g_ret_sts_success THEN--failed transaction
3392
3393 IF (l_debug = 1) THEN
3394 trace('process_rfid_txn: Receiving txn failed');
3395 END IF;
3396
3397 IF l_routing_id = 1 OR l_routing_id = 2 THEN
3398 --Standard/inspection routing
3399 IF l_return_status <> 'N' THEN --avoid overwriting of mesg SET IN the API call
3400 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_STD_INSP_RCV_FAIL');
3401 FND_MSG_PUB.ADD;
3402 END IF;
3403
3404 ROLLBACK TO wms_rfid_sp1;
3405 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);
3406 x_return_value := 'error';
3407
3408 ELSIF l_routing_id = 3 THEN
3409 -- direct routing
3410 IF l_return_status <> 'N' THEN--avoid overwriting of mesg set in the API call
3411 FND_MESSAGE.SET_NAME('WMS', 'WMS_RFID_DIR_RCV_FAIL');
3412 FND_MSG_PUB.ADD;
3413 END IF;
3414 ROLLBACK TO wms_rfid_sp1;
3415 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);
3416 x_return_value := 'error';
3417
3418 END IF;
3419
3420 ELSE--transaction succeeded
3421
3422 IF (l_debug = 1) THEN
3423 trace('process_rfid_txn: Receiving txn succeeded');
3424 END IF;
3425
3426 IF l_routing_id = 1 OR l_routing_id = 2 THEN
3427 --Standard/inspection routing
3428 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);
3429
3430
3431 ELSIF l_routing_id = 3 THEN
3432 -- direct routing
3433 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);
3434
3435 END IF;
3436
3437
3438 END IF;--TXN SUCCEEDED
3439
3440 ELSE --matching routing_id and l_out_business_event_id
3441
3442 IF (l_debug = 1) THEN
3443 trace('Error: Device set up and returned txn routing does not match');
3444 END IF;
3445 FND_MESSAGE.SET_NAME('WMS','WMS_RFID_NO_ELIGIBLE_BUS_EVENT');
3446 FND_MSG_PUB.ADD;
3447 ROLLBACK TO wms_rfid_sp1;
3448 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);
3449 raise end_processing;
3450
3451 END IF; --matching routing_id and l_out_business_event_id
3452
3453 END IF;-- get_routing_id succeeded
3454
3455 END IF;-- L_shipmet_header_id is not null
3456
3457 END IF;--l_lpn_context
3458
3459 END IF; --Verification successful
3460
3461
3462 END IF;--is_valid_txn_device
3463
3464
3465 l_progress := '100';
3466
3467 IF (l_debug = 1) THEN
3468 trace('process_rfid_txn:Delete requested rows from WDR');
3469 END IF;
3470 delete from wms_device_requests;--since temp table is session specific
3471
3472 IF (l_debug = 1) THEN
3473 trace('process_rfid_txn :End of processing for current read');
3474 END IF;
3475
3476 --commiting the reansaction
3477 COMMIT;
3478
3479 --We do populate mesg in case of succes too.
3480 --when the delivery or trip is complete.
3481
3482 --returning message
3483 IF l_out_business_event_id = wms_device_integration_pvt.wms_be_truck_load_ship THEN
3484 x_return_mesg := l_ship_confirm_pkg_mesg;
3485 else
3486 x_return_mesg := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
3487 END IF;
3488
3489 EXCEPTION
3490 WHEN end_processing THEN
3491 x_return_value := 'error';
3492
3493 /* somehow it does not retrieve all mesg
3494 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => l_msg_data);
3495 x_return_mesg := l_msg_data;
3496 */
3497
3498 x_return_mesg := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
3499
3500 IF (l_debug = 1) THEN
3501 trace('process_rfid_txn:throwing Exception:end_processing, Delete requested rows from WDR');
3502 trace('x_return_mesg :' || x_return_mesg);
3503 END IF;
3504
3505
3506 delete from wms_device_requests;--since temp table is session specific
3507 COMMIT;
3508
3509 WHEN OTHERS THEN
3510 ROLLBACK TO wms_rfid_sp1;
3511 x_return_value := 'error';
3512 x_return_mesg := fnd_msg_pub.get(fnd_msg_pub.G_LAST,'F');
3513 IF (l_debug = 1) THEN
3514 trace('Other error in process_rfid_txn l_progress:;'|| l_progress);
3515 trace('SQL error :'||substr(sqlerrm, 1, 240));
3516 END IF;
3517
3518 END;
3519
3520
3521 Procedure WMS_READ_EVENT
3522 (
3523 p_tagid in WMS_EPC_TAGID_TYPE, -- EPC TAGS ID VALUE, IN VARRAY
3524 p_tagdata IN WMS_EPC_TAGDATA_TYPE, -- ANY ADDITIONAL DATA IN VARRAY
3525 p_portalid in VARCHAR2,
3526 p_event_date in date,
3527 p_system_id in VARCHAR2 DEFAULT null,
3528 p_statuschange in NUMBER DEFAULT null,
3529 p_datachange in NUMBER DEFAULT null,
3530 p_status in NUMBER DEFAULT null,
3531 p_x in NUMBER DEFAULT null,
3532 p_y in NUMBER DEFAULT null,
3533 x_return_value out nocopy VARCHAR2, --success,error,warning
3534 x_return_mesg out nocopy varchar2
3535 ) IS
3536
3537
3538 BEGIN
3539
3540 x_return_value := 'read';
3541 x_return_mesg := 'Data Read';
3542
3543 END WMS_READ_EVENT;
3544
3545
3546 procedure process_rfid_txn
3547 (
3548 p_tagid in WMS_EPC_TAGID_TYPE, -- EPC TAGS ID VALUE, IN VARRAY
3549 p_tagdata IN WMS_EPC_TAGDATA_TYPE, -- ANY ADDITIONAL DATA IN VARRAY
3550 p_portalid in VARCHAR2,--Device name as varchar2
3551 p_event_date in date,
3552 p_system_id in VARCHAR2 DEFAULT null,
3553 p_statuschange in NUMBER DEFAULT null,
3554 p_datachange in NUMBER DEFAULT null,
3555 p_status in NUMBER DEFAULT null,
3556 p_x in NUMBER DEFAULT null,
3557 p_y in NUMBER DEFAULT NULL,
3558 x_return_value out nocopy VARCHAR2, --success,error,warning
3559 x_return_mesg OUT nocopy VARCHAR2,
3560 x_request_id OUT nocopy NUMBER
3561 ) IS
3562 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3563 BEGIN
3564
3565 IF (l_debug = 1) THEN
3566 trace('process_rfid_txn :Calling parent process_rfid_txn');
3567 END IF;
3568
3569 process_rfid_txn
3570 (
3571 p_tagid => p_tagid,
3572 p_tagdata => p_tagdata,
3573 p_portalid => p_portalid,
3574 p_event_date => p_event_date,
3575 p_system_id => p_system_id,
3576 p_statuschange => p_statuschange,
3577 p_datachange => p_datachange,
3578 p_status => p_status,
3579 p_x => p_x,
3580 p_y => p_y,
3581 x_return_value => x_return_value,
3582 x_return_mesg => x_return_mesg);
3583
3584 x_request_id := l_device_req_id_pkg;
3585
3586 IF (l_debug = 1) THEN
3587 trace('process_rfid_txn :value of request_id '||l_device_req_id_pkg);
3588 END IF;
3589
3590
3591 END process_rfid_txn;
3592
3593
3594
3595
3596 --Internal Wrapper API for testing purpose only using Mobile
3597 --Not to be touched by customer
3598 --it just convertes the type of p_tagid form Clob to varray and calls the
3599 --main API
3600
3601 procedure MobTest_process_rfid_txn
3602 (
3603 p_tagid in clob, -- EPC tag ID
3604 p_tagdata IN clob, -- Any additional value with EPC tag
3605 p_portalid in varchar2,--reader name
3606 p_event_date in date,
3607 p_system_id in VARCHAR2 DEFAULT null,
3608 p_statuschange in NUMBER DEFAULT null,
3609 p_datachange in NUMBER DEFAULT null,
3610 p_status in NUMBER DEFAULT null,
3611 p_x in NUMBER DEFAULT null,
3612 p_y in NUMBER DEFAULT NULL,
3613 x_return_value out nocopy VARCHAR2, --success,error,warning
3614 x_return_mesg OUT nocopy varchar2)
3615
3616 IS
3617 l_start_pos NUMBER;
3618 l_first_pos NUMBER;
3619 l_second_pos NUMBER;
3620 l_index NUMBER;
3621
3622 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3623
3624 epc_tag_id wms_epc_tagid_type;
3625 epc_tag_data wms_epc_tagdata_type;
3626
3627
3628 BEGIN
3629
3630
3631 IF (l_debug = 1) THEN
3632 trace('Inside MobTest_process_rfid_txn');
3633 END IF;
3634
3635 --Initialize the varray
3636 epc_tag_id := wms_epc_tagid_type('S');
3637 epc_tag_data := wms_epc_tagdata_type('S');
3638
3639
3640 IF p_tagid IS NOT null then
3641 IF (l_debug = 1) THEN
3642 trace('tagid is NOT null');
3643 END IF;
3644
3645 l_start_pos := 1;
3646 l_first_pos := 1;
3647 l_second_pos := 1;
3648
3649 l_index := 1;
3650
3651
3652 WHILE l_second_pos <> 0 LOOP
3653
3654 --trace('Inside outer loop index :'||l_index);
3655
3656
3657 l_first_pos := Instr(p_tagid,',',l_start_pos,1);
3658 l_second_pos := Instr(p_tagid,',',l_start_pos,2);
3659
3660
3661 --trace('l_first_pos , l_second_pos :'||l_first_pos||','||l_second_pos );
3662
3663 l_start_pos := l_second_pos;
3664
3665 IF l_index = 1 THEN
3666
3667 IF l_first_pos = 0 THEN -- only one value is present in the list
3668 --trace('only one value case tag');
3669 epc_tag_id(l_index) := Ltrim(Rtrim(p_tagid));
3670
3671 EXIT;
3672
3673 ELSE --for first AND SECOND value in the list of values
3674
3675 --trace('first value in list of many case');
3676 epc_tag_id(l_index) := Substr(p_tagid,1,l_first_pos-1);
3677
3678 IF l_second_pos <> 0 THEN
3679
3680 l_index := l_index+1;
3681 -- trace('second value in list of many case');
3682 epc_tag_id.extend;
3683 epc_tag_id(l_index) :=Substr(p_tagid,l_first_pos+1,l_second_pos-l_first_pos-1);
3684
3685 ELSE
3686 l_index := l_index+1;
3687 epc_tag_id.extend;
3688 epc_tag_id(l_index) :=Substr(p_tagid,l_first_pos+1);
3689
3690 END IF;
3691 END IF;
3692
3693 ELSIF l_second_pos = 0 THEN --FOR last value
3694
3695 IF (l_debug = 1) THEN
3696 trace('Last value in list of many epc');
3697 END IF;
3698 epc_tag_id.extend;
3699 epc_tag_id(l_index) := Ltrim(Rtrim(Substr(p_tagid,l_first_pos+1)));
3700
3701 ELSE --for in-between values
3702 --trace('In between val in list of many epc');
3703 epc_tag_id.extend;
3704 epc_tag_id(l_index) := Ltrim(Rtrim(Substr(p_tagid,l_first_pos+1,l_second_pos-l_first_pos-1)));
3705
3706 END IF;
3707 l_index := l_index +1 ;
3708
3709 END LOOP;
3710
3711 --call the main API
3712 process_rfid_txn
3713 (
3714 p_tagid => epc_tag_id,
3715 p_tagdata => epc_tag_data,
3716 p_portalid => p_portalid,
3717 p_event_date => p_event_date,
3718 p_system_id => p_system_id,
3719 p_statuschange => p_statuschange,
3720 p_datachange => p_datachange,
3721 p_status => p_status,
3722 p_x => p_x,
3723 p_y => p_y,
3724 x_return_value => x_return_value,
3725 x_return_mesg => x_return_mesg);
3726
3727
3728 ELSE
3729 IF (l_debug = 1) THEN
3730 trace('tagid is NULL');
3731 END IF;
3732 x_return_value := 'error';
3733 x_return_mesg := 'No EPC passed';
3734
3735 END IF;
3736
3737 END MobTest_process_rfid_txn;
3738
3739
3740 END WMS_RFID_DEVICE_PUB;
[Home] [Help]