1 PACKAGE BODY WMS_EPC_PVT AS
2 /* $Header: WMSEPCVB.pls 120.22.12020000.4 2012/08/30 18:53:03 sahmahes ship $ */
3
4 --Global variable
5 g_cached_rule_pkg epc_rule_types_tbl;
6
7 -----------------------------------------------------
8 -- trace : TO log all message
9 -----------------------------------------------------
10 PROCEDURE trace(p_msg IN VARCHAR2) 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_EPC_PVT', 9);
16 END IF;
17 END trace;
18
19
20 FUNCTION bin2dec (binval in char) RETURN number IS
21 i number;
22 digits number;
23 result number := 0;
24 current_digit char(1);
25 current_digit_dec number;
26 BEGIN
27 digits := length(binval);
28
29 IF binval IS NOT NULL THEN
30 for i in 1..digits loop
31 current_digit := SUBSTR(binval, i, 1);
32 current_digit_dec := to_number(current_digit);
33 result := (result * 2) + current_digit_dec;
34 end loop;
35 END IF;
36 return result;
37
38 END bin2dec;
39
40 FUNCTION dec2bin (N in number) RETURN varchar2 IS
41 binval varchar2(260);
42 N2 number := N;
43 BEGIN
44 IF N IS NULL OR N = 0 THEN
45 binval := '0';
46 ELSE
47 while ( N2 > 0 ) loop
48 binval := mod(N2, 2) || binval;
49 N2 := trunc( N2 / 2 );
50 end loop;
51 END IF;
52
53 return binval;
54
55 END dec2bin;
56
57 FUNCTION oct2dec (octval in char) RETURN number IS
58 i number;
59 digits number;
60 result number := 0;
61 current_digit char(1);
62 current_digit_dec number;
63 BEGIN
64 digits := length(octval);
65 for i in 1..digits loop
66 current_digit := SUBSTR(octval, i, 1);
67 current_digit_dec := to_number(current_digit);
68 result := (result * 8) + current_digit_dec;
69 end loop;
70 return result;
71 END oct2dec;
72
73 FUNCTION dec2oct (N in number) RETURN varchar2 IS
74 octval varchar2(260);
75 N2 number := N;
76 BEGIN
77 while ( N2 > 0 ) loop
78 octval := mod(N2, 8) || octval;
79 N2 := trunc( N2 / 8 );
80 end loop;
81 return octval;
82 END dec2oct;
83
84 FUNCTION hex2dec (hexval in char) RETURN number IS
85 i number;
86 digits number;
87 result number := 0;
88 current_digit char(1);
89 current_digit_dec number;
90 BEGIN
91 IF hexval IS NULL THEN
92 RETURN 0;
93 ELSE
94
95 digits := length(hexval);
96 for i in 1..digits loop
97 current_digit := SUBSTR(hexval, i, 1);
98 if current_digit in ('A','B','C','D','E','F') then
99 current_digit_dec := ascii(current_digit) - ascii('A') + 10;
100 else
101 current_digit_dec := to_number(current_digit);
102 end if;
103 result := (result * 16) + current_digit_dec;
104 end loop;
105 return result;
106 END IF;
107
108 END hex2dec;
109
110
111 -- bug fix 4364965: appending extra 0 in the begining of HEX result
112 FUNCTION dec2hex (N in number) RETURN varchar2 IS
113 hexval varchar2(260);
114 N2 number := N;
115 digit number;
116 hexdigit char;
117 BEGIN
118 IF N > 0 THEN
119 while ( N2 > 0 ) LOOP
120 hexdigit := SUBSTR('0123456789ABCDEF',MOD(N2,16)+1,1);
121 hexval := hexdigit || hexval;
122 N2 := trunc( N2 / 16 );
123 end loop;
124
125 ELSIF N = 0 THEN
126 hexval := '0';
127 END IF;
128
129 return hexval;
130 END dec2hex;
131
132
133 --This API needs to move in the rules Engine code
134 --Purpose
135 -- Evaluate whether the LPN is standard
136 ---Standard : Contains single Item
137 ---Non-Standard : Contains multiple Items
138 FUNCTION is_lpn_standard(p_lpn_id NUMBER) RETURN NUMBER IS
139
140 l_is_standard NUMBER;
141 l_item_cnt NUMBER;
142 l_parent_lpn_id NUMBER;
143 l_outermost_lpn_id NUMBER;
144 l_lpn_item_id NUMBER;
145 l_uom_code VARCHAR2(3);
146 l_rev VARCHAR2(3);
147 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
148 BEGIN
149 --to be called from the Rules Engine
150 --Rules engine will look in to WMS_label_requestes table to get thte
151 --lpn_id AND pass it TO this api.
152
153 BEGIN
154 SELECT wlpn.parent_lpn_id, wlpn.outermost_lpn_id
155 INTO l_parent_lpn_id, l_outermost_lpn_id
156 FROM wms_license_plate_numbers wlpn
157 WHERE wlpn.lpn_id = p_lpn_id;
158
159 EXCEPTION
160 WHEN no_data_found THEN
161
162 IF l_debug = 1 THEN
163 trace('NO DATA found for the LPN');
164 END IF;
165
166 RETURN -1; --error CONDITION
167
168 END;
169
170 --{{ is the LPN pallet OR Case for Rule Engine call }}
171 IF (l_outermost_lpn_id = p_lpn_id AND l_parent_lpn_id IS null ) THEN--Pallet
172
173 BEGIN
174
175 SELECT DISTINCT wlc.inventory_item_id,wlc.uom_code,wlc.revision,1
176 INTO l_lpn_item_id,l_uom_code,l_rev,l_is_standard
177 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
178 WHERE wlpn.outermost_lpn_id = p_lpn_id
179 AND wlpn.lpn_id = wlc.parent_lpn_id
180 AND wlc.organization_id = wlpn.organization_id;
181
182 -- it must be Non-Standard here if there are multiple records for item,
183 --uom_code,revision combination
184
185
186 EXCEPTION
187 WHEN too_many_rows THEN
188 --MUITPLE ITEMS OR IN DIFFERETN UOMS OR Different Revision
189 --NON-STANDARD
190 l_is_standard := 0;
191
192 WHEN no_data_found THEN
193 --Error condition
194 l_is_standard := -1;
195
196 WHEN OTHERS THEN
197 l_is_standard := -1;
198 IF l_debug = 1 then
199 trace('ERROR CODE = ' || SQLCODE);
200 trace('ERROR MESSAGE = ' || SQLERRM);
201 END IF;
202
203 END;
204
205 ELSIF (l_parent_lpn_id = l_outermost_lpn_id AND l_parent_lpn_id IS NOT null) THEN --CASE
206 BEGIN
207
208 --FROM RECEIVING SIDE THERE might be multiple records FOR same LPN FOR SAME ITEM
209
210 --Cases might have further NESTED LPNs inside it. all
211 --quantities must be accounted
212
213 /*
214 Tested with following data in WMSDV11I and it is working fine
215 4470-LPN2256A
216
217 --4429 -LPN2248A -wm661
218 -----4509 -LPN2260A - WM661
219
220 --4449 -LPN2254A -wm661
221
222 --4471 -LPN2257A -JALI100
223 -----4490 -LPN2259A - WM661
224 ---------4769 --LPN2290A - PUN100
225
226 LPN_ID : 4470 (4449 , 4429, 4471 )
227 4429( LPN2260A --4509) --same item
228 4471( LPN2259A --4490 (LPN2290A -4769 )) --different item
229
230
231 4584 --WM661
232 28816 --JALI100
233 25816 -- PUN100
234
235 */
236
237 --there might be more than one level of nesting in LPN but
238 --epc generation AND rfid tagging will be done only AT pallet and
239 --CASE label. so CASE might have nested lpns within it.
240
241 select wlc.inventory_item_id,wlc.uom_code,wlc.revision,1
242 INTO l_lpn_item_id,l_uom_code,l_rev,l_is_standard
243 from WMS_LPN_CONTENTS WLC
244 where WLC.parent_lpn_id in (
245 select wlpn.lpn_id
246 from wms_license_plate_numbers wlpn
247 WHERE WLPN.PARENT_LPN_ID is NOT NULL
248 START WITH LPN_ID = p_lpn_id
249 CONNECT BY WLPN.PARENT_LPN_ID = PRIOR LPN_ID)
250 GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
251
252
253
254
255
256 EXCEPTION
257 WHEN too_many_rows THEN
258 --MUITPLE ITEMS
259 --NON-STANDARD
260 l_is_standard := 0;
261
262 WHEN no_data_found THEN
263 --Error condition
264 l_is_standard := -1;
265
266 WHEN OTHERS THEN
267 l_is_standard := -1;
268 IF l_debug = 1 then
269 trace('ERROR CODE = ' || SQLCODE);
270 trace('ERROR MESSAGE = ' || SQLERRM);
271 END IF;
272
273 END;
274
275 ELSE --MORE THAN 1 LEVEL OF NESTING
276 RETURN -1;
277
278 END IF;
279
280 RETURN l_is_standard;
281
282
283 END is_lpn_standard;
284
285
286 -- p_org_id, p_item_id ,p_uom , p_rev_id
287 -- are needed in this function because
288 -- duplicate GTIN can be setup for different item
289 -- in form and it is valid setup for business also
290
291 FUNCTION get_serial_for_gtin(P_gtin NUMBER,
292 p_org_id NUMBER,
293 p_item_id NUMBER,
294 p_uom_code VARCHAR2,
295 p_rev_id NUMBER) RETURN NUMBER IS
296 PRAGMA AUTONOMOUS_TRANSACTION;
297
298 l_cur_serial_num NUMBER;
299 l_new_serial NUMBER := 0; --Just some random start value for a NEW GTIN
300 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
301 BEGIN
302
303 -- See if there are records in the table for this GTIN
304 -- trace('P_gtin :'||p_gtin);
305
306
307 --user can change the Serial in the Cross-ref form manually, but he will
308 --be given warning..At the manual setup time he can assign any value or
309 --leave it null
310
311 BEGIN
312 SELECT Nvl(epc_gtin_serial,0) INTO l_cur_serial_num
313 FROM mtl_cross_references_b
314 WHERE CROSS_REFERENCE = To_char(p_gtin)
315 AND cross_reference_type = G_PROFILE_GTIN
316 AND inventory_item_id = p_item_id
317 AND uom_code = p_uom_code
318 AND nvl(revision_id, -99) = nvl(p_rev_id,-99);
319 EXCEPTION
320 WHEN no_data_found THEN
321 IF l_debug = 1 then
322 trace('no record for GTIN found');
323 END IF;
324 l_new_serial := NULL;
325 RETURN l_new_serial;
326 WHEN OTHERS THEN
327 l_new_serial := NULL;
328 IF l_debug = 1 then
329 trace('ERROR CODE = ' || SQLCODE);
330 trace('ERROR MESSAGE = ' || SQLERRM);
331 END IF;
332 RETURN l_new_serial;
333 END;
334
335 --reset the serial number to next higer value based on the existing value
336 l_new_serial := l_cur_serial_num+1;
337
338 --update the table
339 BEGIN
340
341 UPDATE mtl_cross_references_b
342 SET epc_gtin_serial = l_new_serial
343 WHERE CROSS_REFERENCE = To_char(p_gtin)
344 AND cross_reference_type = G_PROFILE_GTIN
345 AND inventory_item_id = p_item_id
346 AND uom_code = p_uom_code
347 AND nvl(revision_id, -99) = nvl(p_rev_id,-99);
348
349 EXCEPTION
350 WHEN OTHERS THEN
351 l_new_serial := NULL;
352 IF l_debug = 1 then
353 trace('ERROR CODE = ' || SQLCODE);
354 trace('ERROR MESSAGE = ' || SQLERRM);
355 END IF;
356 RETURN l_new_serial;
357 END;
358
359 COMMIT;
360
361 IF l_debug = 1 then
362 trace('Returning new GTIN serial_num :'||l_new_serial);
363 END IF;
364
365 RETURN l_new_serial;
366
367 EXCEPTION
368
369 WHEN OTHERS THEN
370
371 IF l_debug = 1 then
372 trace('get_serial_for_gtin ERROR CODE = ' || SQLCODE);
373 trace('get_serial_for_gtin ERROR MESSAGE = ' || SQLERRM);
374 END IF;
375 END get_serial_for_gtin;
376
377
378
379 --Get GTIN and GTIN-Serial
380 --given item + org + total_qty + Revision
381
382 PROCEDURE get_gtin_and_gserial(p_org_id IN NUMBER,
383 p_item_id IN NUMBER,
384 p_total_qty IN NUMBER,
385 p_rev IN VARCHAR2 ,
386 p_primary_uom IN VARCHAR2,
387 x_gtin OUT nocopy NUMBER,
388 x_gtin_serial OUT nocopy NUMBER,
389 x_return_status OUT nocopy VARCHAR2)
390 IS
391
392 CURSOR c_mtl_uom IS
393 SELECT uom_code FROM mtl_uom_conversions_view mucv
394 WHERE mucv.inventory_item_id = p_item_id
395 AND mucv.organization_id = p_org_id
396 AND mucv.conversion_rate = p_total_qty
397 AND Nvl(mucv.uom_code,'@@@') = Nvl(p_primary_uom,Nvl(mucv.uom_code,'@@@'));
398
399 l_uom_code VARCHAR2(3);
400 l_found_gtin NUMBER;
401 l_gtin NUMBER;
402 l_rev_id NUMBER;
403 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
404 BEGIN
405
406 x_return_status := fnd_api.g_ret_sts_success;
407
408 IF l_debug = 1 THEN
409 trace(' Inside get_gtin_and_gserial');
410 trace('p_org_id :'||p_org_id ||','||'p_item_id :'||p_item_id);
411 trace('p_total_qty :'||p_total_qty||','||'p_rev :'||p_rev);
412 trace('p_primary_uom :' || p_primary_uom);
413 trace('G_PROFILE_GTIN :'|| g_profile_gtin );
414 END IF;
415
416
417 IF p_rev IS NOT NULL then
418 -- open c_gtin_crossref_rev cursor
419 --l_lpn_item_id,L_REV,p_org_id,l_total_qty,l_gtin_crossref_rev.UOM_CODE
420
421
422 FOR l_mtl_uom IN c_mtl_uom LOOP
423 IF l_debug = 1 THEN
424 trace(' REV NOT NULL- UOM Code :'|| l_mtl_uom.UOM_CODE);
425 END IF;
426
427 BEGIN
428 SELECT 1, To_number(mcr.cross_reference),mirb.revision_id INTO
429 l_found_gtin,l_gtin,l_rev_id
430 FROM mtl_cross_references MCR, mtl_item_revisions_b mirb --USING base TABLE FOR PERFORMANCE
431 WHERE mcr.cross_reference_type = G_PROFILE_GTIN
432 AND mcr.inventory_item_id = p_item_id
433 AND mcr.uom_code = l_mtl_uom.UOM_CODE
434 AND mcr.inventory_item_id = mirb.inventory_item_id
435 AND MIRB.revision = p_rev
436 AND mirb.revision_id = mcr.REVISION_ID
437 AND (( mcr.org_independent_flag = 'Y' AND
438 mcr.organization_id IS NULL AND
439 MIRB.organization_id = p_org_id) OR
440 (mcr.org_independent_flag = 'N' AND
441 mcr.organization_id = p_org_id AND
442 mcr.organization_id = mirb.organization_id))
443 AND ROWNUM < 2;
444
445 EXCEPTION
446 WHEN no_data_found THEN
447 l_found_gtin := 0;
448 --Do not raise exception here
449
450 WHEN OTHERS THEN
451 l_found_gtin := 0;
452 RAISE fnd_api.g_exc_unexpected_error;
453 END;
454
455 IF l_found_gtin = 1 THEN --FOUND GTIN
456 --overwrite the value l_uom_code for GTIN setup
457 l_uom_code := l_mtl_uom.uom_code;
458 EXIT; --EXIT THE LOOP
459 END IF;
460
461 END LOOP;
462
463
464 ELSE --means p_rev IS NULL
465
466 -- open c_mtl_uom cursor
467
468 FOR l_mtl_uom IN c_mtl_uom LOOP
469
470 IF l_debug = 1 THEN
471 trace(' REV NULL- UOM Code :'|| l_mtl_uom.UOM_CODE);
472 END IF;
473
474 BEGIN
475 SELECT 1, To_number(mcr.cross_reference) INTO
476 l_found_gtin,l_gtin
477 FROM mtl_cross_references MCR
478 WHERE mcr.cross_reference_type = G_PROFILE_GTIN
479 AND mcr.inventory_item_id = p_item_id
480 AND MCR.revision_id is NULL
481 AND mcr.uom_code = l_mtl_uom.UOM_CODE
482 AND (( mcr.org_independent_flag = 'Y' AND mcr.organization_id IS NULL)
483 OR (mcr.org_independent_flag = 'N' AND mcr.organization_id = p_org_id))
484 AND ROWNUM<2;
485
486 l_rev_id := NULL ;
487
488 EXCEPTION
489 WHEN no_data_found THEN
490 l_found_gtin := 0;
491 --Do not raise exception here
492
493 WHEN OTHERS THEN
494 l_found_gtin := 0;
495 RAISE fnd_api.g_exc_unexpected_error;
496
497 END;
498
499 IF l_debug = 1 then
500 trace('l_found_gtin :' ||l_found_gtin);
501 END IF ;
502
503 IF l_found_gtin = 1 THEN --FOUND GTIN
504 --overwrite the value l_uom_code for GTIN setup
505 l_uom_code := l_mtl_uom.uom_code;
506 EXIT; --EXIT THE LOOP
507 END IF;
508
509 END LOOP;
510
511 END IF;-- p_rev IS NULL
512
513
514 IF l_debug = 1 then
515 trace('l_found_gtin :'||l_found_gtin);
516 END IF ;
517
518
519
520 IF l_found_gtin = 1 THEN
521
522 --PUT VERIFICATION FOR 14 Digit GTIN
523
524 IF Length(To_char(l_gtin)) <> 14 THEN
525
526 IF l_debug = 1 then
527 trace('Error gtin not 14 digit ');
528 END IF ;
529
530 fnd_message.set_name('WMS', 'WMS_INVALID_GTIN');
531 fnd_msg_pub.ADD;
532 RAISE fnd_api.g_exc_error;
533 END IF;
534
535 ELSIF l_found_gtin = 0 THEN
536
537 fnd_message.set_name('WMS', 'WMS_INVALID_GTIN');
538 fnd_msg_pub.ADD;
539 RAISE fnd_api.g_exc_error;
540
541 END IF;
542
543
544 IF l_debug = 1 then
545 trace('Final l_gtin :'||l_gtin );
546 END IF ;
547
548
549 --now get the GTIN-serial
550 --we could not do it as a part of above query along with GTIN
551 --becasue the call to FUNCTION will unnecessary update the
552 --serial number for GTIN
553
554 IF l_gtin IS NOT NULL THEN
555 --Finally generated GTIN SUCCESS
556 x_gtin := l_gtin;
557
558 --Get GTIN-serial for the GTIN now
559 x_gtin_serial := get_serial_for_gtin(l_gtin, p_org_id, p_item_id, l_uom_code,l_rev_id);
560
561
562 IF x_gtin_serial IS NULL THEN
563 fnd_message.set_name('WMS', 'WMS_INVALID_GTIN_GSERIAL');
564 fnd_msg_pub.ADD;
565 RAISE fnd_api.g_exc_error;
566
567 END IF;
568
569 ELSE
570 fnd_message.set_name('WMS', 'WMS_NO_GTIN_FOUND');
571 fnd_msg_pub.ADD;
572 RAISE fnd_api.g_exc_error;
573 END IF;
574
575
576 EXCEPTION
577
578 WHEN fnd_api.g_exc_error THEN
579 x_return_status := fnd_api.g_ret_sts_error;
580 x_gtin := NULL;
581 x_gtin_serial := NULL;
582 RAISE; -- to raised to the outer call
583
584 WHEN fnd_api.g_exc_unexpected_error THEN
585 x_return_status := fnd_api.g_ret_sts_unexp_error;
586 x_gtin := NULL;
587 x_gtin_serial := NULL;
588 RAISE; -- to raised to the outer call
589
590 WHEN OTHERS THEN
591 IF l_debug = 1 THEN
592 trace('Unexpected error inside get_gtin_and_gserial()');
593 trace('ERROR CODE = ' || SQLCODE);
594 trace('ERROR MESSAGE = ' || SQLERRM);
595 END IF;
596 x_return_status := fnd_api.g_ret_sts_unexp_error;
597 x_gtin := NULL;
598 x_gtin_serial := NULL;
599 RAISE;
600
601 END get_gtin_and_gserial;
602
603
604 --Get GTIN and GTIN-Serial for LPN
605 procedure get_lpn_gtin_serial(p_lpn_id IN NUMBER,
606 p_org_id IN NUMBER,
607 p_filter_value IN NUMBER,
608 p_business_flow_code IN NUMBER,
609 x_gtin OUT nocopy NUMBER,
610 x_gtin_serial OUT nocopy VARCHAR2,
611 x_return_status OUT nocopy VARCHAR2)
612 IS
613
614
615 l_lpn_item_id NUMBER;
616 l_total_qty NUMBER :=0;
617 l_found_gtin NUMBER := 0;
618 l_rev VARCHAR2(3);
619 l_uom_code VARCHAR2(3);
620 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
621 l_gtin NUMBER;
622 l_is_gtin_valid BOOLEAN := TRUE;
623
624 l_total_wlc_qty NUMBER;
625 l_total_mmtt_qty NUMBER;
626 l_total_mmtt_qty1 NUMBER;
627 l_total_mmtt_qty2 NUMBER;
628 l_rev_id NUMBER;
629 BEGIN
630 x_return_status := fnd_api.g_ret_sts_success;
631
632 IF l_debug = 1 then
633 trace('Inside get_lpn_gtin_serial p_business_flow_code :'|| p_business_flow_code);
634 END IF;
635
636
637 --1. See if has single item or multiple items
638 --2. See how much quantity of the item is there in pallet/case
639
640
641 /* p_business_flow_code = inv_label.wms_bf_wip_pick_load --VALUE 28 is not
642 needed for components only WIP completion is needed
643 p_business_flow_code = inv_label.wms_bf_replenishment_load)--VALUE 34 --Not Needed */
644
645
646 IF (p_business_flow_code = inv_label.wms_bf_pick_load) THEN --VALUE 18
647
648 --GET FOLLOWING VALUES
649 --TOTAL quantity IN LPN,inventory_item_id,TXN.uom_code,TXN.revision
650 -- INTO l_total_qty, l_lpn_item_id,L_rev
651
652 --Get values from mmtt pending records, which is either loaded
653 -- previously OR it iS curently bening loaded
654 -- While loading process, MMTT record is the source of truth for
655 -- NVL(transfer_LPN_id,content_lpn_id)
656 --Event if te entire LPN is picked, MMTT.qty will have the total qty of
657 --picking LPN. Since going against MMTT qty, I do not have to worry
658 --about the level of nesting to-be-picked LPN
659
660 BEGIN
661 SELECT DISTINCT mmtt.inventory_item_id, SUM(mmtt.primary_quantity), mmtt.revision
662 INTO l_lpn_item_id,l_total_qty,l_rev
663 FROM mtl_material_transactions_temp mmtt,
664 wms_license_plate_numbers wlpn
665 WHERE Nvl(mmtt.transfer_lpn_id,mmtt.content_lpn_id) = p_lpn_id
666 AND mmtt.organization_id = p_org_id
667 AND wlpn.lpn_id = Nvl(mmtt.transfer_lpn_id,mmtt.content_lpn_id)
668 AND wlpn.lpn_context = wms_container_pub.LPN_CONTEXT_PACKING
669 GROUP BY mmtt.inventory_item_id,mmtt.transaction_uom, mmtt.revision;
670 --All GTINS (transaction_uom) have to different in an LPN for SGTIN EPC generation rule
671
672 EXCEPTION
673 WHEN too_many_rows THEN
674 l_is_gtin_valid := FALSE;
675 IF l_debug = 1 THEN
676 trace('too_many_rows ERROR for wms_bf_pick_load');
677 END IF;
678
679
680 WHEN no_data_found THEN
681 l_is_gtin_valid := FALSE;
682 IF l_debug = 1 THEN
683 trace('no_data_found ERROR for wms_bf_pick_load');
684 END IF;
685
686 WHEN OTHERS THEN
687 IF l_debug = 1 THEN
688 trace('unexpected error for wms_bf_pick_load');
689 END IF;
690 RAISE fnd_api.g_exc_unexpected_error;
691
692 END;
693
694 IF l_debug = 1 then
695 trace('Bus PKLD l_total_qty,l_REV,l_lpn_item_id::'||l_total_qty||':'||l_rev||':'||l_lpn_item_id);
696 END IF;
697
698
699
700 ELSIF p_business_flow_code = inv_label.WMS_BF_CARTONIZATION THEN --VALUE 22
701
702 IF l_debug = 1 then
703 trace('Inside cartonization');
704 END IF;
705 --FIRST TEST PICK RELEASE CARTONIZATION AND THEN LOOK FOR BULK
706 --pack CARTONIZATION. Pick Release Performance is important
707 --PR cartonization and Bulk Pack - both are mutually exclusive cases.
708 -- Both have same business flow -22
709
710
711 --Pick Release cartonization
712 BEGIN
713 SELECT mmtt.inventory_item_id, SUM(mmtt.primary_quantity), mmtt.revision
714 INTO l_lpn_item_id,l_total_qty,l_rev
715 FROM mtl_material_transactions_temp mmtt
716 WHERE mmtt.cartonization_id = p_lpn_id
717 AND mmtt.cartonization_id IS NOT NULL
718 AND mmtt.organization_id = p_org_id
719 GROUP BY mmtt.inventory_item_id,mmtt.transaction_uom, mmtt.revision;
720 --All GTINS (transaction_uom) have to different in an LPN for SGTIN EPC generation rule
721
722 EXCEPTION
723 WHEN too_many_rows THEN
724 l_is_gtin_valid := FALSE;
725 IF l_debug = 1 THEN
726 trace('too_many_rows ERROR for Pick Release cartonization');
727 END IF;
728
729 WHEN no_data_found THEN
730 l_total_qty := 0;
731 --Do not put l_is_gtin_valid := FALSE HERE
732 IF l_debug = 1 THEN
733 trace('no_data_found ERROR for Pick Release cartonization');
734 END IF;
735
736 WHEN OTHERS THEN
737 IF l_debug = 1 THEN
738 trace('unexpected error for Pick Release cartonization');
739 END IF;
740 RAISE fnd_api.g_exc_unexpected_error;
741
742 END;
743
744 IF l_debug = 1 then
745 trace('After PR cartonization l_total_qty,l_REV,l_lpn_item_id::'||l_total_qty||':'||l_rev||':'||l_lpn_item_id);
746 END IF;
747
748 --Bulk Pack Cartonization
749
750 IF l_is_gtin_valid = TRUE AND l_total_qty = 0 THEN
751 --means this is FOR bulk pack cartonization ..no mmtt record
752
753 IF p_filter_value = wms_epc_pallet_obj_type THEN --pallet
754
755 BEGIN
756
757 SELECT SUM(wlc.primary_quantity),wlc.inventory_item_id,wlc.uom_code,wlc.revision --UOM CODE TO AVOID FUTURE
758 --support OF HAVING multiple lines FOR same lpn based ON ui UOM
759 --IT SHOULD FAIL TO USE GTIN FOR EPC GENERATION FOR MULTIPLE GTIN IN LPN
760 INTO l_total_qty, l_lpn_item_id, l_uom_code,L_rev
761 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
762 WHERE wlpn.outermost_lpn_id = p_lpn_id
763 AND wlpn.lpn_id = wlc.parent_lpn_id
764 AND wlc.organization_id = p_org_id
765 AND wlc.organization_id = wlpn.organization_id
766 GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
767 -- it must fail here if there are multiple records for item,
768 --uom_code,revision combination
769
770 EXCEPTION
771 WHEN too_many_rows THEN
772 l_is_gtin_valid := FALSE;
773 IF l_debug = 1 THEN
774 trace('Pallet too_many_rows ERROR for Bulk Pack cartonization');
775 END IF;
776 WHEN no_data_found THEN
777 l_is_gtin_valid := FALSE;
778 IF l_debug = 1 THEN
779 trace('Pallet No_data_found ERROR for Bulk Pack cartonization');
780 END IF;
781
782 WHEN OTHERS THEN
783 IF l_debug = 1 THEN
784 trace('Pallet unexpected error for Pick Release cartonization');
785 END IF;
786 RAISE fnd_api.g_exc_unexpected_error;
787
788 END;
789
790
791 ELSIF p_filter_value = WMS_EPC_CASE_OBJ_TYPE THEN --case
792
793 BEGIN
794 --1. UOM CODE TO AVOID failure due to FUTURE
795 --support OF HAVING multiple lines FOR same lpn based ON ui UOM
796 --IT SHOULD FAIL TO USE GTIN FOR EPC GENERATION FOR MULTIPLE
797 --GTIN IN LPN
798 --2. FROM RECEIVING SIDE THERE
799 --might be multiple records FOR same LPN FOR SAME ITEM
800
801 --Cases might have further NESTED LPNs inside it. all
802 --quantities must be accounted
803
804
805 SELECT SUM(wlc.primary_quantity),wlc.inventory_item_id,wlc.uom_code,wlc.revision
806 INTO l_total_qty,l_lpn_item_id,l_uom_code,l_rev
807 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn1, wms_license_plate_numbers wlpn2
808 WHERE wlpn1.lpn_id = p_lpn_id
809 and wlpn1.parent_lpn_id = wlpn2.outermost_lpn_id
810 AND wlpn2.lpn_id = wlc.parent_lpn_id
811 AND wlpn2.lpn_id <> wlpn1.parent_lpn_id --to avoid content of Pallet
812 AND wlc.organization_id = p_org_id
813 aND wlc.organization_id = wlpn1.organization_id
814 and wlc.organization_id = wlpn2.organization_id
815 GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
816
817 EXCEPTION
818 WHEN too_many_rows THEN
819 l_is_gtin_valid := FALSE;
820 IF l_debug = 1 THEN
821 trace('Case too_many_rows ERROR for Bulk Pack cartonization');
822 END IF;
823 WHEN no_data_found THEN
824 l_is_gtin_valid := FALSE;
825 IF l_debug = 1 THEN
826 trace('Case No_data_found ERROR for Bulk Pack cartonization');
827 END IF;
828
829 WHEN OTHERS THEN
830 IF l_debug = 1 THEN
831 trace('Case unexpected error for Pick Release cartonization');
832 END IF;
833 RAISE fnd_api.g_exc_unexpected_error;
834
835 END;
836
837 END IF;
838
839
840 END IF; --for bulk pack cartonization
841
842
843 IF l_debug = 1 then
844 trace('After BULK PACK cartonization l_total_qty,l_REV,l_lpn_item_id::'||l_total_qty||':'||l_rev||':'||l_lpn_item_id);
845 END IF;
846
847 ELSIF (p_business_flow_code = inv_label.WMS_BF_WIP_COMPLETION OR --VALUE 26
848 p_business_flow_code = inv_label.WMS_BF_FLOW_WORK_ASSEMBLY )
849 THEN --VALUE 33
850
851 -- While WIP completion, Nesting of LPN with context of "Reside in
852 -- WIP" is not possible. So there can not be assembly in the
853 --nested lpn . PACKUNPACK IS CALLED DIRECTLY BEFORE CALLING LABEL
854 --printing . so All assembly quantity will be available in WLC
855 --for the same LPN for CURRENT MMTT and previous WIP
856 --completions IN this LPN
857
858 --Get current MMTT qty for the assembly
859
860 -- MMTT RECORD IS processed each time (NOT through inv tm rather it IS
861 -- used manually IN wip code, mmtt acts AS a placeholder)
862
863 --Get current WLC qty for the assembly
864
865 BEGIN
866 SELECT DISTINCT wlc.inventory_item_id, SUM(wlc.primary_quantity),wlc.uom_code,wlc.revision
867 INTO l_lpn_item_id,l_total_qty,l_uom_code,l_rev
868 FROM wms_license_plate_numbers wlpn,
869 wms_lpn_contents wlc
870 WHERE wlc.parent_lpn_id = p_lpn_id
871 AND wlc.organization_id = p_org_id
872 AND wlpn.lpn_id = wlc.parent_lpn_id
873 AND wlpn.LPN_CONTEXT = wms_container_pub.LPN_CONTEXT_WIP
874 AND wlc.organization_id = wlpn.organization_id
875 GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
876 --this code will be invoked for Picking business flow only and a
877 --physical lpn can have only one status at a time..that will be
878 --"Picking here" because query is based on p_lpn_id
879
880
881 EXCEPTION
882 WHEN too_many_rows THEN
883 l_is_gtin_valid := FALSE;
884 IF l_debug = 1 THEN
885 trace('WIP too_many_rows ERROR for Bulk Pack cartonization');
886 END IF;
887 WHEN no_data_found THEN
888 l_is_gtin_valid := FALSE;
889 IF l_debug = 1 THEN
890 trace('WIP No_data_found ERROR for Bulk Pack cartonization');
891 END IF;
892
893 WHEN OTHERS THEN
894 IF l_debug = 1 THEN
895 trace('WIP unexpected error for Pick Release cartonization');
896 END IF;
897 RAISE fnd_api.g_exc_unexpected_error;
898
899 END;
900
901 IF l_debug = 1 then
902 trace('WIP l_total_qty,l_REV,l_lpn_item_id::'||l_total_qty||':'||l_rev||':'||l_lpn_item_id);
903 END IF;
904
905
906 ELSE --FOR ALL OTHER BUSINESS FLOW
907
908 IF l_debug = 1 THEN
909 trace('for all other buiness flow');
910 END IF;
911
912 IF p_filter_value = wms_epc_pallet_obj_type THEN --pallet
913
914 BEGIN
915
916 IF l_debug = 1 THEN
917 trace('for wms_epc_pallet_obj_type');
918 END IF;
919
920 SELECT SUM(wlc.primary_quantity),wlc.inventory_item_id,wlc.uom_code,wlc.revision --UOM CODE TO AVOID FUTURE
921 --support OF HAVING multiple lines FOR same lpn based ON ui UOM
922 --IT SHOULD FAIL TO USE GTIN FOR EPC GENERATION FOR MULTIPLE GTIN IN LPN
923 INTO l_total_qty, l_lpn_item_id, l_uom_code,L_rev
924 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
925 WHERE wlpn.outermost_lpn_id = p_lpn_id
926 AND wlpn.lpn_id = wlc.parent_lpn_id
927 AND wlc.organization_id = p_org_id
928 AND wlc.organization_id = wlpn.organization_id
929 GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
930 -- it must fail here if there are multiple records for item,
931 --uom_code,revision combination
932
933 EXCEPTION
934 WHEN too_many_rows THEN
935 l_is_gtin_valid := FALSE;
936
937 WHEN no_data_found THEN
938 l_is_gtin_valid := FALSE;
939
940 WHEN OTHERS THEN
941 RAISE fnd_api.g_exc_unexpected_error;
942 END;
943
944
945 ELSIF p_filter_value = WMS_EPC_CASE_OBJ_TYPE THEN --case
946
947 IF l_debug = 1 THEN
948 trace('for WMS_EPC_CASE_OBJ_TYPE');
949 END IF;
950
951 BEGIN
952 --1. UOM CODE TO AVOID failure due to FUTURE
953 --support OF HAVING multiple lines FOR same lpn based ON ui UOM
954 --IT SHOULD FAIL TO USE GTIN FOR EPC GENERATION FOR MULTIPLE
955 --GTIN IN LPN
956 --2. FROM RECEIVING SIDE THERE
957 --might be multiple records FOR same LPN FOR SAME ITEM
958
959 --Cases might have further NESTED LPNs inside it. all
960 --quantities must be accounted
961
962 --tested in logutr12 with case lpn_id=25341 (LPN2148A) --SOLUTION OF BUG 4355961
963
964 select sum(wlc.primary_quantity), wlc.inventory_item_id,wlc.uom_code, WLC.revision
965 INTO l_total_qty, l_lpn_item_id, l_uom_code,l_rev
966 from WMS_LPN_CONTENTS WLC
967 where WLC.parent_lpn_id in (
968 select wlpn.lpn_id
969 from wms_license_plate_numbers wlpn
970 where WLPN.PARENT_LPN_ID is NOT NULL
971 START WITH LPN_ID = p_lpn_id
972 CONNECT BY WLPN.PARENT_LPN_ID = PRIOR LPN_ID)
973 GROUP BY WLC.inventory_item_id,wlc.uom_code,wlc.revision;
974
975 EXCEPTION
976 WHEN too_many_rows THEN
977 l_is_gtin_valid := FALSE;
978
979 WHEN no_data_found THEN
980 l_is_gtin_valid := FALSE;
981
982 WHEN OTHERS THEN
983 RAISE fnd_api.g_exc_unexpected_error;
984
985 END;
986
987 END IF;
988
989 END IF; --For business Flows
990
991
992
993 --3. look for gtin cross refernece set up for that item
994
995 --4. Find if any gtin UOM has corresponding UOM conversion defined for
996 -- quantity, pick that GTIN
997
998 IF l_debug = 1 then
999 trace('l_total_qty,l_REV,l_lpn_item_id::'||l_total_qty||':'||l_rev||':'||l_lpn_item_id);
1000 IF l_is_gtin_valid = TRUE then
1001 trace(' l_is_gtin_valid IS TRUE');
1002 ELSIF l_is_gtin_valid = FALSE THEN
1003 trace(' l_is_gtin_valid IS FALSE');
1004 ELSE
1005 trace(' l_is_gtin_valid IS NULL');
1006 END IF;
1007 END IF;
1008
1009
1010 IF l_is_gtin_valid THEN
1011
1012 --get GTIN and GTIN-Serial now
1013 get_gtin_and_gserial(p_org_id => p_org_id,
1014 p_item_id => l_lpn_item_id,
1015 p_total_qty => l_total_qty,
1016 p_rev => l_rev,
1017 p_primary_uom => NULL,
1018 x_gtin => x_gtin,
1019 x_gtin_serial => x_gtin_serial,
1020 x_return_status => x_return_status);
1021
1022 ELSE
1023
1024 fnd_message.set_name('WMS', 'WMS_NO_GTIN_FOUND');
1025 fnd_msg_pub.ADD;
1026 RAISE fnd_api.g_exc_error;
1027
1028 END IF;--if l_is_gtin_valid
1029
1030
1031
1032 EXCEPTION
1033 WHEN fnd_api.g_exc_error THEN
1034 x_return_status := fnd_api.g_ret_sts_error;
1035 x_gtin_serial := NULL;
1036 x_gtin := NULL;
1037 RAISE;
1038 WHEN fnd_api.g_exc_unexpected_error THEN
1039 x_return_status := fnd_api.g_ret_sts_unexp_error;
1040 x_gtin := NULL;
1041 x_gtin_serial := NULL;
1042 RAISE;
1043 WHEN OTHERS THEN
1044 IF l_debug = 1 THEN
1045 trace('Unexpected error inside get_LPN_gtin_serial()');
1046 trace('ERROR CODE = ' || SQLCODE);
1047 trace('ERROR MESSAGE = ' || SQLERRM);
1048 END IF;
1049 x_return_status := fnd_api.g_ret_sts_unexp_error;
1050 x_gtin := NULL;
1051 x_gtin_serial := NULL;
1052 RAISE;
1053
1054 END get_lpn_gtin_serial;
1055
1056
1057 procedure get_item_gtin_serial(p_item_id IN NUMBER,
1058 p_org_id IN NUMBER,
1059 p_qty IN NUMBER,
1060 p_uom_code IN VARCHAR2,
1061 p_rev IN VARCHAR2,
1062 x_gtin OUT nocopy NUMBER,
1063 x_gtin_serial OUT nocopy VARCHAR2,
1064 x_return_status OUT nocopy VARCHAR2)
1065 IS
1066
1067 l_gtin NUMBER;
1068 l_rev_id NUMBER;
1069 l_pri_qty NUMBER;
1070 l_found_gtin NUMBER;
1071 l_uom_code VARCHAR2(3);
1072 l_primary_uom_code VARCHAR2(3);
1073 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1074 BEGIN
1075 x_return_status := fnd_api.g_ret_sts_success;
1076 -- if p_uom_code is NOT primary UOM, get primary qty in primary UOM
1077 -- For the primary qty, get the corresponding UOM conversion
1078
1079 IF l_debug = 1 then
1080 trace(' Inside get_ITEM_gtin_SERIAL');
1081 END IF;
1082
1083 /*
1084 May 2005 :- Question: if I get request for 5 DZ.
1085 1- Does it mean that the Dz uom is good and I find the GTIN in the cross-ref for the Dz uom.
1086 OR
1087 2- Get primary qty=60 (12x5) and get UOM_code for conversion factor of 60 (may be DZ5) and then find GTIN fir this new UOM.
1088
1089 Answer: Use Number 2 approach above.
1090
1091 August 2005 :- Decision changed: We need to print EPC for GTIN
1092 corresponding to DZ
1093
1094 */
1095
1096
1097 --If p_uom is not primary UOM code then convert it to the primary UOM
1098 --AND primary_qty and
1099
1100 IF ( inv_cache.set_item_rec(
1101 p_organization_id => p_org_id
1102 , p_item_id => p_item_id ) )
1103 THEN
1104 IF (l_debug = 1) THEN
1105 trace('Got Item info puom='||inv_cache.item_rec.primary_uom_code);
1106
1107 END IF;
1108 ELSE
1109 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ITEM');
1110 fnd_msg_pub.ADD;
1111 RAISE fnd_api.g_exc_error;
1112 END IF;
1113
1114
1115 IF (p_uom_code <> inv_cache.item_rec.primary_uom_code) THEN
1116 l_pri_qty :=
1117 inv_convert.inv_um_convert(item_id => p_item_id
1118 ,precision => 5
1119 ,from_quantity => 1 --p_qty, get the prim qty FOR UOM
1120 ,from_unit => p_uom_code
1121 ,to_unit => inv_cache.item_rec.primary_uom_code
1122 ,from_name => NULL
1123 ,to_name => NULL);
1124 ELSE
1125
1126 l_pri_qty := p_qty;
1127
1128 END IF;
1129
1130 IF l_debug = 1 then
1131 trace('for each GTIN l_pri_qty :'||l_pri_qty);
1132 trace('l_primary_uom_code :'||inv_cache.item_rec.primary_uom_code);
1133 trace('p_rev :'||p_rev);
1134 END IF;
1135
1136
1137 -- find the "conversion_uom" for the total primary qty, done with cursor c_mtl_uom
1138
1139 -- See if the conversion UOM obtained above is defined in the
1140 -- cross-reference table
1141
1142
1143 --get GTIN and GTIN-Serial now for the item
1144 get_gtin_and_gserial(p_org_id => p_org_id,
1145 p_item_id => p_item_id,
1146 p_total_qty => l_pri_qty,
1147 p_rev => p_rev,
1148 p_primary_uom => p_uom_code,
1149 x_gtin => x_gtin,
1150 x_gtin_serial => x_gtin_serial,
1151 x_return_status => x_return_status);
1152
1153
1154 IF l_debug = 1 then
1155 trace('x_gtin :'||x_gtin );
1156 trace('x_gtin_serial :'|| x_gtin_serial);
1157 END IF;
1158
1159 EXCEPTION
1160 WHEN fnd_api.g_exc_error THEN
1161 x_return_status := fnd_api.g_ret_sts_error;
1162 x_gtin_serial := NULL;
1163 x_gtin := NULL;
1164 RAISE;
1165 WHEN fnd_api.g_exc_unexpected_error THEN
1166 x_return_status := fnd_api.g_ret_sts_unexp_error;
1167 x_gtin := NULL;
1168 x_gtin_serial := NULL;
1169 RAISE;
1170 WHEN OTHERS THEN
1171 IF l_debug = 1 THEN
1172 trace('Unexpected error inside get_item_gtin_serial()');
1173 trace('ERROR CODE = ' || SQLCODE);
1174 trace('ERROR MESSAGE = ' || SQLERRM);
1175 END IF;
1176 x_return_status := fnd_api.g_ret_sts_unexp_error;
1177 x_gtin := NULL;
1178 x_gtin_serial := NULL;
1179 RAISE;
1180
1181 END get_item_gtin_serial ;
1182
1183
1184
1185 procedure get_serialnum_gtin_serial(p_item_id IN NUMBER,
1186 p_org_id IN NUMBER,
1187 p_rev IN VARCHAR2,
1188 x_gtin OUT nocopy NUMBER,
1189 x_gtin_serial OUT nocopy VARCHAR2,
1190 x_return_status OUT nocopy VARCHAR2)
1191 IS
1192
1193
1194 l_gtin NUMBER;
1195 l_rev_id NUMBER;
1196 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1197 BEGIN
1198
1199 x_return_status := fnd_api.g_ret_sts_success;
1200
1201
1202 --GET THE PRIMARY UOM code FOR SERIAL ITEM
1203 IF ( inv_cache.set_item_rec(
1204 p_organization_id => p_org_id
1205 , p_item_id => p_item_id ) )
1206 THEN
1207 IF (l_debug = 1) THEN
1208 trace('Got Item info puom='||inv_cache.item_rec.primary_uom_code);
1209 END IF;
1210 ELSE
1211 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ITEM');
1212 fnd_msg_pub.ADD;
1213 RAISE fnd_api.g_exc_error;
1214 END IF;
1215
1216
1217 -- See if the PRIMARY UOM obtained above is defined in the cross-reference table
1218 --GET THE CORRESPONDING GTIN
1219
1220
1221 --get GTIN and GTIN-Serial now
1222 get_gtin_and_gserial(p_org_id => p_org_id,
1223 p_item_id => p_item_id,
1224 p_total_qty => 1, --Always = 1 since primary UOM
1225 p_rev => p_rev,
1226 p_primary_uom => inv_cache.item_rec.primary_uom_code,
1227 x_gtin => x_gtin,
1228 x_gtin_serial => x_gtin_serial,
1229 x_return_status => x_return_status);
1230
1231
1232 IF l_debug = 1 then
1233 trace('x_gtin :'||x_gtin );
1234 trace('x_gtin_serial :'|| x_gtin_serial);
1235 END IF;
1236
1237
1238
1239 EXCEPTION
1240
1241 WHEN fnd_api.g_exc_error THEN
1242 x_return_status := fnd_api.g_ret_sts_error;
1243 x_gtin_serial := NULL;
1244 x_gtin := NULL;
1245 RAISE;
1246 WHEN fnd_api.g_exc_unexpected_error THEN
1247 x_return_status := fnd_api.g_ret_sts_unexp_error;
1248 x_gtin := NULL;
1249 x_gtin_serial := NULL;
1250 RAISE;
1251 WHEN OTHERS THEN
1252 IF l_debug = 1 THEN
1253 trace('Unexpected error inside get_serialnum_gtin_seria()');
1254 trace('ERROR CODE = ' || SQLCODE);
1255 trace('ERROR MESSAGE = ' || SQLERRM);
1256 END IF;
1257 x_return_status := fnd_api.g_ret_sts_unexp_error;
1258 x_gtin := NULL;
1259 x_gtin_serial := NULL;
1260 RAISE;
1261
1262 END get_serialnum_gtin_serial ;
1263
1264
1265 --get SSCC for give LPN_id
1266 FUNCTION get_sscc(p_lpn_id NUMBER,
1267 p_org_id NUMBER) RETURN NUMBER
1268 IS
1269
1270 l_is_sscc_valid BOOLEAN;
1271 l_sscc VARCHAR2(30);
1272 l_sscc_len NUMBER;
1273 l_lpn_num_format NUMBER;
1274 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1275
1276 BEGIN
1277 IF l_debug = 1 then
1278 trace('get_ssc() p_lpn_id,p_org_id :'||p_lpn_id ||','||p_org_id);
1279 END IF;
1280
1281 --See if SSCC can be used
1282 --make sure numeric only
1283 SELECT license_plate_number INTO l_sscc FROM wms_license_plate_numbers
1284 WHERE lpn_id = p_lpn_id
1285 AND organization_id = p_org_id;
1286
1287 BEGIN
1288 l_sscc_len := Length(l_sscc);
1289
1290 IF l_sscc_len = 18 THEN
1291
1292 l_lpn_num_format := To_number(l_sscc);--ensure Numeric Only
1293
1294 ELSE
1295 l_lpn_num_format := NULL;
1296 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SSCC');
1297 FND_MSG_PUB.ADD;
1298 RAISE fnd_api.g_exc_error;
1299 END IF;
1300
1301 EXCEPTION
1302 WHEN OTHERS THEN --catch the exception for alphanumeric case
1303 l_lpn_num_format := NULL;
1304 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SSCC');
1305 FND_MSG_PUB.ADD;
1306 trace('Other Exception in get_sscc()');
1307 RAISE fnd_api.g_exc_error;
1308 END;
1309
1310 RETURN l_lpn_num_format;
1311
1312 EXCEPTION
1313 WHEN fnd_api.g_exc_error THEN
1314 RAISE;
1315
1316 WHEN OTHERS THEN
1317 IF l_debug = 1 THEN
1318 trace('Unexpected error inside get_sscc()');
1319 trace('ERROR CODE = ' || SQLCODE);
1320 trace('ERROR MESSAGE = ' || SQLERRM);
1321 END IF;
1322 RAISE;
1323
1324 END get_sscc;
1325
1326
1327 --This API will be called while importing ASNs
1328 --Purpose
1329 -- Create Cross-reference in WMS_EPC TABLE
1330 --between EPC and objects from interface tables
1331
1332 PROCEDURE populate_outside_epc
1333 (p_group_id IN NUMBER , --obtained from WMS_EPC_S2.nextval by calling API
1334 p_cross_ref_type IN NUMBER, --1: LPN-EPC , 2: ITEM_SERIAL-EPC , 3: GTIN-EPC
1335 p_Lpn_id IN NUMBER DEFAULT NULL, --for p_cross_ref_type =1 only
1336 p_ITEM_ID IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 2 only
1337 p_SERIAL_NUMBER VARCHAR2 DEFAULT NULL, --for p_cross_ref_type = 2 only
1338 p_GTIN IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 3 , for future
1339 p_GTIN_SERIAL IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 3 , for future
1340 p_EPC IN VARCHAR2,
1341 x_return_status OUT nocopy VARCHAR2,
1342 x_return_mesg OUT nocopy VARCHAR2
1343 ) IS
1344
1345 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1346
1347 BEGIN
1348 x_return_status := fnd_api.g_ret_sts_success;
1349
1350 IF l_debug = 1 THEN
1351 trace('p_group_id :'||p_group_id ||' ,'||'p_cross_ref_type :'||p_cross_ref_type);
1352 trace('p_Lpn_id :'||p_lpn_id);
1353 trace('p_ITEM_ID :'||p_item_id||' ,'||'p_SERIAL_NUMBER :'||p_SERIAL_NUMBER);
1354 trace('p_EPC :'||p_epc);
1355 END IF;
1356
1357 IF p_group_id IS NULL OR p_cross_ref_type IS NULL OR p_epc IS NULL THEN
1358 x_return_status := fnd_api.g_ret_sts_error;
1359 x_return_mesg := fnd_message.get_string('WMS','WMS_EPC_MISSING_VALUES');
1360 RETURN;
1361
1362 ELSIF p_lpn_id IS NULL AND
1363 (p_item_id IS NULL OR p_serial_number IS NULL ) AND
1364 (p_gtin IS NULL OR p_gtin_serial IS NULL) THEN
1365
1366 x_return_status := fnd_api.g_ret_sts_error;
1367 x_return_mesg := fnd_message.get_string('WMS','WMS_EPC_MISSING_VALUES');
1368 RETURN;
1369 END IF;
1370
1371 INSERT INTO wms_epc( group_id,
1372 cross_ref_type,
1373 epc_rule_type_id,
1374 lpn_id,
1375 serial_number,
1376 inventory_item_id,
1377 gtin_serial,
1378 gtin,
1379 sscc,
1380 epc,
1381 filter_object_type,
1382 status_code,
1383 status,
1384 creation_date,
1385 created_by,
1386 last_update_date,
1387 last_updated_by,
1388 last_update_login,
1389 epc_id,
1390 epc_rule_id
1391 ) VALUES (P_group_id,
1392 p_cross_ref_type,
1393 -1, -- epc_rule_type_id:populated -1 FOR outside party
1394 p_lpn_id,
1395 p_serial_number,
1396 p_ITEM_ID,
1397 p_GTIN_SERIAL,
1398 P_gtin,
1399 NULL,
1400 P_epc,
1401 null,--filter_object_type
1402 'S',
1403 'IMPORTED',
1404 Sysdate,
1405 fnd_global.user_id,
1406 Sysdate,
1407 fnd_global.user_id,
1408 fnd_global.user_id,
1409 NULL, --epc_id NOT used post R12
1410 NULL); --epc_rule_id NOT used post R12
1411 --DO NOT COMMIT
1412
1413 EXCEPTION
1414 WHEN OTHERS THEN
1415 x_return_status := fnd_api.g_ret_sts_error;
1416 x_return_mesg := Sqlerrm;
1417
1418 IF l_debug = 1 THEN
1419 trace('Exception in populate_outside_epc');
1420 trace('ERROR CODE = ' || SQLCODE);
1421 trace('ERROR MESSAGE = ' || SQLERRM);
1422 END IF;
1423
1424 END populate_outside_epc;
1425
1426
1427 $IF DBMS_DB_VERSION.VERSION > 11 $THEN
1428 --Bug 8796558 New model chages Begin
1429 --This procedure Insert / Upadate / Delete in WMS_EPC table with data
1430 --passed based ON action specified
1431 --PROCEDURE definition applicable to RDBMS12c or higher
1432 PROCEDURE uptodate_wms_epc
1433 (p_action IN VARCHAR2,
1434 p_group_id IN NUMBER,
1435 p_cross_ref_type IN NUMBER,
1436 p_epc_rule_type_name IN VARCHAR2,
1437 p_lpn_id IN NUMBER,
1438 p_item_id IN NUMBER,
1439 p_serial_number IN NUMBER,
1440 p_gen_epc IN VARCHAR2,
1441 p_sscc IN NUMBER,
1442 p_gtin IN NUMBER, --calling API makes it number
1443 p_gtin_serial IN NUMBER,
1444 p_filter_VALUE IN NUMBER,
1445 x_return_status OUT nocopy VARCHAR2
1446 ) IS
1447
1448 PRAGMA AUTONOMOUS_TRANSACTION;
1449
1450 l_epc_id NUMBER;
1451 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1452 BEGIN
1453
1454 x_return_status := fnd_api.g_ret_sts_success;
1455
1456 IF l_debug = 1 THEN
1457 trace('************UPtoDATE WMS_EPC with new RFID model***');
1458 trace('p_action :'|| p_action );
1459 trace('p_group_id :'|| p_group_id);
1460 trace('p_cross_ref_type :'|| p_cross_ref_type);
1461 trace('p_epc_rule_type_name :'|| p_epc_rule_type_name);
1462 trace('p_lpn_id :'|| p_lpn_id);
1463 trace('p_item_id :'|| p_item_id);
1464 trace('p_serial_number :'|| p_serial_number);
1465 trace('p_gen_epc :'|| p_gen_epc);
1466 trace('p_sscc :'|| p_sscc);
1467 trace('p_gtin :'|| p_gtin);
1468 END IF;
1469
1470
1471 IF p_cross_ref_type = 1 THEN --LPN-EPC /* LPN related label*/
1472
1473 IF p_action = 'UPDATE' THEN
1474
1475 UPDATE wms_epc
1476 SET epc = p_gen_epc,
1477 cross_ref_type = p_cross_ref_type,
1478 group_id = p_group_id,
1479 last_update_date = Sysdate,
1480 last_updated_by = fnd_global.user_id,
1481 epc_rule_type_id = p_epc_rule_type_name,
1482 sscc = P_sscc,
1483 gtin = P_gtin,
1484 gtin_serial = NULL,
1485 inventory_item_id = NULL,
1486 serial_number = NULL,
1487 filter_object_type = p_filter_value,
1488 status = 'LABEL_PRINTED',
1489 status_code = 'S'
1490 WHERE lpn_id = p_lpn_id;
1491
1492 ELSIF p_action = 'INSERT' THEN
1493
1494 --INSERT NEW EPC RECORD
1495
1496 INSERT INTO wms_epc( group_id,
1497 cross_ref_type,
1498 epc_rule_type_id,
1499 lpn_id,
1500 serial_number,
1501 inventory_item_id,
1502 gtin_serial,
1503 gtin,
1504 sscc,
1505 epc,
1506 filter_object_type,
1507 status_code,
1508 status,
1509 creation_date,
1510 created_by,
1511 last_update_date,
1512 last_updated_by,
1513 last_update_login,
1514 epc_id,
1515 epc_rule_id
1516 ) VALUES (P_group_id,
1517 p_cross_ref_type,
1518 p_epc_rule_type_name,
1519 p_lpn_id,
1520 NULL,--p_serial_number,
1521 NULL,--p_ITEM_ID,
1522 NULL,--p_GTIN_SERIAL,
1523 P_gtin,
1524 P_sscc,
1525 P_gen_epc,
1526 p_filter_value,
1527 'S',
1528 'LABEL_PRINTED',
1529 Sysdate,
1530 fnd_global.user_id,
1531 Sysdate,
1532 fnd_global.user_id,
1533 fnd_global.user_id,
1534 NULL, --epc_id NOT used post R12
1535 NULL); --epc_rule_id NOT used post R12
1536
1537 ELSIF p_action = 'DELETE' THEN
1538
1539 -- Delete the existing cross -reference
1540 DELETE FROM wms_epc WHERE lpn_id = p_lpn_id;
1541
1542 END IF;
1543
1544
1545 ELSIF p_cross_ref_type = 2 THEN -- Item_Serial - EPC /* Serial Label */
1546
1547 IF p_action = 'UPDATE' THEN
1548
1549 UPDATE wms_epc
1550 SET epc = p_gen_epc,
1551 cross_ref_type = p_cross_ref_type,
1552 group_id = p_group_id,
1553 last_update_date = Sysdate,
1554 last_updated_by = fnd_global.user_id,
1555 epc_rule_type_id = p_epc_rule_type_name,
1556 sscc = NULL,-- No other value possible
1557 gtin = P_gtin,
1558 gtin_serial = NULL,
1559 lpn_id = NULL,
1560 filter_object_type = p_filter_value,
1561 status = 'LABEL_PRINTED',
1562 status_code = 'S'
1563 WHERE inventory_item_id = p_item_id
1564 AND serial_number = p_serial_number;
1565
1566 ELSIF p_action = 'INSERT' THEN
1567
1568 --INSERT NEW EPC RECORD
1569
1570 INSERT INTO wms_epc( group_id,
1571 cross_ref_type,
1572 epc_rule_type_id,
1573 lpn_id,
1574 serial_number,
1575 inventory_item_id,
1576 gtin_serial,
1577 gtin,
1578 sscc,
1579 epc,
1580 filter_object_type,
1581 status_code,
1582 status,
1583 creation_date,
1584 created_by,
1585 last_update_date,
1586 last_updated_by,
1587 last_update_login,
1588 epc_id,
1589 epc_rule_id
1590 ) VALUES (P_group_id,
1591 p_cross_ref_type,
1592 p_epc_rule_type_name,
1593 NULL,-- lpn_id
1594 p_serial_number,
1595 p_item_id,
1596 NULL,--p_gtin_serial,
1597 P_gtin,
1598 NULL,--p_sscc
1599 P_gen_epc,
1600 p_filter_value,
1601 'S',
1602 'LABEL_PRINTED',
1603 Sysdate,
1604 fnd_global.user_id,
1605 Sysdate,
1606 fnd_global.user_id,
1607 fnd_global.user_id,
1608 NULL, --epc_id NOT used post R12
1609 NULL); --epc_rule_id NOT used post R12
1610
1611 ELSIF p_action = 'DELETE' THEN
1612
1613 -- Delete the existing cross -reference
1614 DELETE FROM wms_epc
1615 WHERE inventory_item_id = p_item_id
1616 AND serial_number = p_serial_number;
1617
1618
1619 END IF;
1620
1621 ELSIF p_cross_ref_type = 3 THEN --GTIN+GTIN_Serial - EPC /* Material Label */
1622
1623 IF p_action = 'UPDATE' THEN
1624
1625 UPDATE wms_epc
1626 SET epc = p_gen_epc,
1627 cross_ref_type = p_cross_ref_type,
1628 group_id = p_group_id,
1629 last_update_date = Sysdate,
1630 last_updated_by = fnd_global.user_id,
1631 epc_rule_type_id = p_epc_rule_type_name,
1632 sscc = NULL, --NO other value possible in this case
1633 serial_number = NULL,
1634 inventory_item_id = NULL,
1635 lpn_id = NULL,
1636 filter_object_type = p_filter_value,
1637 status = 'LABEL_PRINTED',
1638 status_code = 'S'
1639 WHERE GTIN = p_gtin
1640 AND GTIN_serial = p_gtin_serial;
1641
1642 ELSIF p_action = 'INSERT' THEN
1643
1644 --INSERT NEW EPC RECORD
1645
1646 INSERT INTO wms_epc( group_id,
1647 cross_ref_type,
1648 epc_rule_type_id,
1649 lpn_id,
1650 serial_number,
1651 inventory_item_id,
1652 gtin_serial,
1653 gtin,
1654 sscc,
1655 epc,
1656 filter_object_type,
1657 status_code,
1658 status,
1659 creation_date,
1660 created_by,
1661 last_update_date,
1662 last_updated_by,
1663 last_update_login,
1664 epc_id,
1665 epc_rule_id
1666 ) VALUES (P_group_id,
1667 p_cross_ref_type,
1668 p_epc_rule_type_name,
1669 NULL ,-- p_lpn_id
1670 NULL, --p_serial_number
1671 NULL, --p_item_id,
1672 p_GTIN_serial,
1673 P_gtin,
1674 NULL, --p_sscc,
1675 P_gen_epc,
1676 p_filter_value,
1677 'S',
1678 'LABEL_PRINTED',
1679 Sysdate,
1680 fnd_global.user_id,
1681 Sysdate,
1682 fnd_global.user_id,
1683 fnd_global.user_id,
1684 NULL, --epc_id NOT used post R12
1685 NULL); --epc_rule_id NOT used post R12
1686
1687 ELSIF p_action = 'DELETE' THEN
1688
1689 -- Delete the existing cross -reference
1690 DELETE FROM wms_epc
1691 WHERE GTIN = p_gtin
1692 AND GTIN_serial = p_gtin_serial;
1693
1694 END IF;
1695
1696
1697 END IF;
1698
1699
1700 --COMMIT THE autonomous txn part of updating record in WMS_EPC
1701 COMMIT;
1702
1703 EXCEPTION
1704 WHEN OTHERS THEN
1705 x_return_status := fnd_api.g_ret_sts_error;
1706 IF l_debug = 1 THEN
1707 TRACE('UPTODATE WMS_EPC: inside exception');
1708 TRACE('ERROR CODE = ' || SQLCODE);
1709 TRACE('ERROR MESSAGE = ' || SQLERRM);
1710 END IF;
1711
1712 END uptodate_wms_epc;
1713
1714
1715 --Collects all informtion needed to generate EPC for given object
1716 -- For a given EPC generation type the output parameter x_components
1717 -- contains required elements that will be passed to DB- EPC generation API
1718 -- to generate EPC
1719 --PROCEDURE definition applicable to RDBMS12c or higher
1720 PROCEDURE get_epc_gen_info( p_org_id IN NUMBER,
1721 p_lpn_id IN NUMBER, --FOR p_label_type_id = 3,4,5
1722 p_serial_number IN VARCHAR2, --FOR p_label_type_id = 2
1723 p_item_id IN NUMBER, --FOR p_label_type_id = 1,2
1724 p_txn_qty IN NUMBER, --FOR p_label_type_id = 1
1725 p_txn_uom IN VARCHAR2, --FOR p_label_type_id = 1
1726 p_rev IN VARCHAR2, --FOR p_label_type_id = 1,2
1727 p_company_prefix IN VARCHAR2,
1728 p_comp_prefix_index IN VARCHAR2,
1729 p_business_flow_code IN NUMBER,
1730 p_label_type_id IN NUMBER,
1731 p_epc_rule_type IN VARCHAR2,
1732 p_filter_value IN NUMBER,
1733 p_cage_code IN VARCHAR2, --FOR p_label_type_id = 2
1734 x_gtin OUT nocopy NUMBER,
1735 x_sscc OUT nocopy NUMBER,
1736 x_gtin_serial OUT nocopy NUMBER,
1737 x_components OUT nocopy mgd_id_component_varray,
1738 x_return_status OUT nocopy VARCHAR2)
1739 IS
1740
1741 l_components MGD_ID_COMPONENT_VARRAY;
1742
1743 l_sscc_len NUMBER;
1744 l_gtin_len NUMBER;
1745 l_lpn_num_format NUMBER;
1746 l_is_sscc_valid BOOLEAN := TRUE;
1747 l_is_gtin_valid BOOLEAN := TRUE;
1748 l_item_id NUMBER;
1749 l_total_qty NUMBER :=0;
1750 l_found_gtin NUMBER := 0;
1751 l_rev VARCHAR2(3);
1752 l_uom_code VARCHAR2(3);
1753 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1754 l_gtin NUMBER;
1755
1756 l_total_wlc_qty NUMBER;
1757 l_total_mmtt_qty NUMBER;
1758 l_total_mmtt_qty1 NUMBER;
1759 l_total_mmtt_qty2 NUMBER;
1760 l_comp_prefix_dig_len NUMBER;
1761 l_item_reference NUMBER;
1762 l_serial_reference NUMBER;
1763 l_gtin_serial NUMBER;
1764 l_sscc NUMBER;
1765
1766 l_return_status VARCHAR2(1);
1767 --l_primary_uom_code VARCHAR2(3);
1768
1769 ----------------------------------------
1770 /*
1771 Following table shows Valid set up in Label Format Form for EPC
1772 generation using diferent standard (X means acceptable)
1773
1774 Standard\LabelType LPN/LPNContent/LPNSummary Material Serial
1775 SGTIN_96/64 X X X
1776 SSCC_96/64 X - -
1777 DoD_96/64 - - X
1778 */
1779 -----------------------------------------
1780
1781
1782 BEGIN
1783
1784 x_return_status := fnd_api.g_ret_sts_success;
1785
1786 IF p_label_type_id IN (3,4,5) THEN --LPN, LPN Content, LPN Summary
1787
1788 l_comp_prefix_dig_len:= Length(p_company_prefix);
1789
1790 IF p_epc_rule_type IN ('SGTIN-96','SGTIN-64') THEN
1791
1792 -- {{get GTIN and gtin-Serial for the LPN }}
1793 get_lpn_gtin_serial(p_lpn_id => p_lpn_id,
1794 p_org_id => p_org_id,
1795 p_filter_value => p_filter_value,
1796 p_business_flow_code => p_business_flow_code,
1797 x_gtin => l_gtin,
1798 x_gtin_serial => l_gtin_serial,
1799 x_return_status => l_return_status);
1800
1801 --{{ get the item reference from GTIN for LPN now }}
1802 --{{ l_gtin obtained IS NOT NULL }}
1803
1804 IF l_return_status = fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
1805
1806 l_item_reference := To_number(Substr(To_char(l_gtin),1,1)||Substr(To_char(l_gtin),l_comp_prefix_dig_len+2,12-l_comp_prefix_dig_len));
1807
1808 IF l_debug = 1 THEN
1809 trace('l_gtin , l_gtin_serial :' || l_gtin||','||l_gtin_serial);
1810 trace('l_item_reference :'|| l_item_reference);
1811 END IF;
1812
1813 x_gtin := l_gtin;
1814 x_sscc := NULL;
1815 x_gtin_serial := l_gtin_serial;
1816
1817 --{{ get all expected components FOR EPC_SGTIN_96}}
1818 IF p_epc_rule_type = 'SGTIN-96' THEN
1819
1820 --use company-prefix
1821
1822 IF l_debug = 1 THEN
1823 trace('p_filter_value, p_company_prefix,l_item_reference,l_gtin_serial');
1824 trace(p_filter_value||','||p_company_prefix||','||l_item_reference||','||l_gtin_serial);
1825 END IF;
1826
1827 l_components := MGD_ID_COMPONENT_VARRAY(
1828 MGD_ID_COMPONENT('filter',p_filter_value),
1829 MGD_ID_COMPONENT('companyprefix',p_company_prefix),
1830 MGD_ID_COMPONENT('companyprefixlength',l_comp_prefix_dig_len),
1831 MGD_ID_COMPONENT('itemref',l_item_reference),
1832 MGD_ID_COMPONENT('serial',l_gtin_serial),
1833 MGD_ID_COMPONENT('schemes','SGTIN-96'));
1834
1835 ELSIF p_epc_rule_type = 'SGTIN-64' THEN
1836
1837 --{{ get all expected components FOR EPC_SGTIN_64}}
1838
1839 l_components := MGD_ID_COMPONENT_VARRAY(
1840 MGD_ID_COMPONENT('filter',p_filter_value),
1841 MGD_ID_COMPONENT('companyprefix',p_company_prefix),
1842 MGD_ID_COMPONENT('companyprefixlength',l_comp_prefix_dig_len),
1843 MGD_ID_COMPONENT('itemref',l_item_reference),
1844 MGD_ID_COMPONENT('serial',l_gtin_serial),
1845 MGD_ID_COMPONENT('schemes','SGTIN-64'));
1846
1847
1848 END IF;
1849 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF get_lpn_gtin_serial()
1850 END IF;
1851
1852
1853 ELSIF p_epc_rule_type IN ('SSCC-96','SSCC-64') THEN
1854
1855 --{{ get SSCC for LPN }}
1856
1857 l_sscc := get_sscc(p_lpn_id,p_org_id);
1858
1859 IF l_debug = 1 THEN
1860 trace('SSCC for the LPN :'||l_sscc);
1861 trace('l_comp_prefix_dig_len :'||l_comp_prefix_dig_len);
1862 trace('p_filter_value :'||p_filter_value);
1863 trace('p_company_prefix :'|| p_company_prefix);
1864 END IF;
1865
1866
1867 IF l_sscc IS NOT NULL THEN
1868
1869 x_gtin := NULL;
1870 x_sscc := l_sscc;
1871 x_gtin_serial := NULL;
1872
1873 --{{ get serial reference from SSCC }}
1874 l_serial_reference := To_number(Substr(To_char(L_sscc),1,1)||Substr(To_char(l_sscc),l_comp_prefix_dig_len+2,(16-l_comp_prefix_dig_len)));
1875
1876 IF l_debug = 1 THEN
1877
1878 trace('l_serial_reference :'|| l_serial_reference);
1879 END IF;
1880 --{{ get all expected components FOR EPC_SSCC_96 for containers}}
1881 IF p_epc_rule_type = 'SSCC-96' THEN
1882 --use company-prefix
1883 IF l_debug = 1 THEN
1884 trace('Coming to calculating SSCC-96 p_filter_value :'||p_filter_value);
1885 trace('Coming to calculating SSCC-96 p_company_prefix :'||p_company_prefix);
1886 trace('Coming to calculating SSCC-96 l_comp_prefix_dig_len :'||l_comp_prefix_dig_len);
1887 trace('Coming to calculating SSCC-96 l_serial_reference :'||l_serial_reference);
1888 END IF;
1889 l_components := MGD_ID_COMPONENT_VARRAY(
1890 MGD_ID_COMPONENT('filter',p_filter_value),
1891 MGD_ID_COMPONENT('companyprefix',p_company_prefix),
1892 MGD_ID_COMPONENT('companyprefixlength',l_comp_prefix_dig_len),
1893 MGD_ID_COMPONENT('serialref',l_serial_reference),
1894 MGD_ID_COMPONENT('schemes','SSCC-96'));
1895
1896 ELSIF p_epc_rule_type = 'SSCC-64' THEN
1897
1898
1899 --{{ get all expected components FOR EPC_SSCC_64 for containers}}
1900
1901 l_components :=MGD_ID_COMPONENT_VARRAY(
1902 MGD_ID_COMPONENT('filter',p_filter_value),
1903 MGD_ID_COMPONENT('companyprefix',p_company_prefix),
1904 MGD_ID_COMPONENT('companyprefixlength',l_comp_prefix_dig_len),
1905 MGD_ID_COMPONENT('serialref',l_serial_reference),
1906 MGD_ID_COMPONENT('schemes','SSCC-64'));
1907
1908 END IF;
1909
1910 ELSE --means l_sscc is NULL
1911 IF l_debug = 1 THEN
1912 trace('Error : Incorrect SSCC value set up for the LPN');
1913 END IF;
1914
1915 RAISE fnd_api.g_exc_error;
1916 END IF;
1917
1918
1919 ELSIF p_epc_rule_type IN ('USDOD-96','USDOD-64') THEN
1920
1921
1922 --This is INVALID option for EPC generation FOR LPN label
1923 IF l_debug = 1 THEN
1924 trace('Error:For LPN label, No EPC can be generated using EPC_DOD_96/64, incorrect SET up');
1925 END IF;
1926 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
1927 FND_MSG_PUB.ADD;
1928 RAISE fnd_api.g_exc_error;
1929 END IF;
1930
1931 ELSIF p_label_type_id =1 THEN /*Material Label*/
1932
1933 l_comp_prefix_dig_len:= Length(p_company_prefix);
1934
1935 IF l_debug = 1 THEN
1936 trace('l_comp_prefix_dig_len :'||l_comp_prefix_dig_len);
1937 END IF;
1938
1939 IF p_epc_rule_type IN ('SGTIN-96','SGTIN-64') THEN
1940 -- for item_id and qty + UOM, find any set up in GTIN C/R
1941 -- Generate EPC for that GTIN.
1942
1943 get_item_gtin_serial(p_item_id => p_item_id,
1944 p_org_id => p_org_id,
1945 p_qty => p_txn_qty,
1946 p_uom_code => p_txn_uom,
1947 p_rev => l_rev,
1948 x_gtin => l_gtin,
1949 x_gtin_serial => l_gtin_serial,
1950 x_return_status => l_return_status);
1951
1952
1953
1954 IF l_return_status = fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
1955
1956
1957
1958 --{{ get the item reference from GTIN for the Item now }}
1959
1960 l_item_reference :=
1961 To_number(Substr(To_char(l_gtin),1,1)||Substr(To_char(l_gtin),l_comp_prefix_dig_len+2,12-l_comp_prefix_dig_len));
1962
1963 x_gtin := l_gtin;
1964 x_sscc := NULL;
1965 x_gtin_serial := l_gtin_serial;
1966
1967 IF p_epc_rule_type = 'SGTIN-96' THEN
1968
1969 --{{ get all expected components FOR EPC_SGTIN_96 for Item}}
1970 --use company-prefix
1971 l_components := MGD_ID_COMPONENT_VARRAY(
1972 MGD_ID_COMPONENT('filter',p_filter_value),
1973 MGD_ID_COMPONENT('companyprefix',p_company_prefix),
1974 MGD_ID_COMPONENT('companyprefixlength',l_comp_prefix_dig_len),
1975 MGD_ID_COMPONENT('itemref',l_item_reference),
1976 MGD_ID_COMPONENT('serial',l_gtin_serial),
1977 MGD_ID_COMPONENT('schemes','SGTIN-96'));
1978
1979 ELSIF p_epc_rule_type = 'SGTIN-64' THEN
1980
1981 IF l_debug = 1 THEN
1982 trace('p_comp_prefix_index :'||To_number(p_comp_prefix_index));
1983 trace('p_filter_value :'||p_filter_value);
1984 trace('l_item_reference :'||l_item_reference);
1985 trace('l_gtin_serial :'||l_gtin_serial);
1986 END IF;
1987
1988 --{{ get all expected components FOR EPC_SGTIN_64 for Item}}
1989 --use company-prefix -INDEX
1990
1991 l_components := MGD_ID_COMPONENT_VARRAY(
1992 MGD_ID_COMPONENT('filter',p_filter_value),
1993 MGD_ID_COMPONENT('companyprefix',p_company_prefix),
1994 MGD_ID_COMPONENT('companyprefixlength',l_comp_prefix_dig_len),
1995 MGD_ID_COMPONENT('itemref',l_item_reference),
1996 MGD_ID_COMPONENT('serial',l_gtin_serial),
1997 MGD_ID_COMPONENT('schemes','SGTIN-64'));
1998
1999 END IF;
2000 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF get_item_gtin_serial()
2001 END IF;
2002
2003 ELSIF p_epc_rule_type IN ('SSCC-96','SSCC-64') THEN
2004 --This is INVALID option for EPC generation of Material
2005 IF l_debug = 1 THEN
2006 trace('Error:For Material, No EPC can be generated using SSCC, incorrect SET up');
2007 END IF;
2008
2009 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
2010 FND_MSG_PUB.ADD;
2011 RAISE fnd_api.g_exc_error;
2012
2013
2014 ELSIF p_epc_rule_type IN ('USDOD-96','USDOD-64') THEN
2015
2016 IF l_debug = 1 THEN
2017 trace('Error:For Material, No EPC can be generated using EPC_DOD, incorrect SET up');
2018 END IF;
2019 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
2020 FND_MSG_PUB.ADD;
2021 RAISE fnd_api.g_exc_error;
2022
2023
2024 END IF;
2025
2026 ELSIF p_label_type_id = 2 THEN /*Serial Label*/
2027 --Components: FILTER,CAGE, SERIAL_NUMBER
2028
2029 IF p_epc_rule_type IN ('SGTIN-96','SGTIN-64') THEN
2030
2031 --Generate EPC for that item for GTIN corresponding to Primary UOM.
2032 --we have p_serial_number + p_item_id + p_cage_code+ p_filter_value
2033
2034 --P_serial_number will be totally ignored while generating EPC. GTIN_Serial
2035 --will be used but it will be stored in wms_EPC table against the
2036 --Item+p_serial_number
2037
2038 -- for item_id and qty + UOM, find any set up in GTIN C/R
2039 -- Generate EPC for that GTIN.
2040
2041 IF l_debug = 1 THEN
2042 trace('going to call get_serialnum_gtin_serial');
2043 END IF;
2044
2045 get_serialnum_gtin_serial(p_item_id => p_item_id,
2046 p_org_id => p_org_id,
2047 p_rev => l_rev,
2048 x_gtin => l_gtin,
2049 x_gtin_serial => l_gtin_serial,
2050 x_return_status => l_return_status);
2051
2052
2053 --{{ get the item reference from GTIN for the Item now }}
2054
2055
2056 IF l_debug = 1 THEN
2057 trace('After call get_serialnum_gtin_serial');
2058 trace('l_return_status :'||l_return_status);
2059 trace('l_gtin :'||l_gtin);
2060 trace('l_gtin_serial :'||l_gtin_serial);
2061 END IF;
2062
2063
2064 IF l_return_status= fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
2065
2066 l_item_reference :=
2067 To_number(Substr(To_char(l_gtin),1,1)||Substr(To_char(l_gtin),l_comp_prefix_dig_len+2,12-l_comp_prefix_dig_len));
2068
2069 x_gtin := l_gtin;
2070 x_sscc := NULL;
2071 x_gtin_serial := l_gtin_serial;
2072
2073
2074 IF p_epc_rule_type = 'SGTIN-96' THEN
2075 --{{ get all expected components FOR EPC_SGTIN_96 for Item}}
2076 --use company-prefix
2077 l_components := MGD_ID_COMPONENT_VARRAY(
2078 MGD_ID_COMPONENT('filter',p_filter_value),
2079 MGD_ID_COMPONENT('companyprefix',p_company_prefix),
2080 MGD_ID_COMPONENT('companyprefixlength',l_comp_prefix_dig_len),
2081 MGD_ID_COMPONENT('itemref',l_item_reference),
2082 MGD_ID_COMPONENT('serial',l_gtin_serial),
2083 MGD_ID_COMPONENT('schemes','SGTIN-96'));
2084 ELSIF p_epc_rule_type = 'EPC-SGTIN-64' THEN
2085
2086 --{{ get all expected components FOR EPC_SGTIN_64 for Item}}
2087 --use company-prefix -INDEX
2088
2089 l_components := MGD_ID_COMPONENT_VARRAY(
2090 MGD_ID_COMPONENT('filter',p_filter_value),
2091 MGD_ID_COMPONENT('companyprefix',p_company_prefix),
2092 MGD_ID_COMPONENT('companyprefixlength',l_comp_prefix_dig_len),
2093 MGD_ID_COMPONENT('itemref',l_item_reference),
2094 MGD_ID_COMPONENT('serial',l_gtin_serial),
2095 MGD_ID_COMPONENT('schemes','SGTIN-64'));
2096 END IF;
2097 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF
2098 --get_serialnum_gtin_serial() -> get_gtin_and_gserial()
2099
2100 END IF;
2101
2102 ELSIF p_epc_rule_type IN ('SSCC-96','SSCC-64') THEN
2103 --This is INVALID option for EPC generation of Serial NUMBER
2104 IF l_debug = 1 THEN
2105 trace('Error:For Serial, No EPC can be generated using SSCC, incorrect SET up');
2106 END IF;
2107 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
2108 FND_MSG_PUB.ADD;
2109 RAISE fnd_api.g_exc_error;
2110
2111 ELSIF p_epc_rule_type IN ('USDOD-96','USDOD-64') THEN
2112 --{{Note: We MUST have Serial uniqueness across items for EPC generation EPC_DOD_96/EPC_DOD_64}}
2113
2114 x_gtin := NULL;
2115 x_sscc := NULL;
2116 x_gtin_serial := NULL;
2117
2118 IF p_epc_rule_type = 'USDOD-96' THEN
2119 --{{ get all expected components FOR EPC_DOD_96 for container}}
2120
2121 l_components := MGD_ID_COMPONENT_VARRAY(
2122 MGD_ID_COMPONENT('filter',p_filter_value),
2123 MGD_ID_COMPONENT('cageordodaac',p_cage_code),
2124 MGD_ID_COMPONENT('serial',p_serial_number),
2125 MGD_ID_COMPONENT('schemes','USDOD-96'));
2126
2127 ELSIF p_epc_rule_type = 'USDOD-64' THEN
2128 --{{ get all expected components FOR EPC_DOD_96 for container }}
2129
2130 IF l_debug = 1 THEN
2131 trace('Inside EPC_DOD_64 to collect components');
2132 END IF;
2133
2134 l_components := MGD_ID_COMPONENT_VARRAY(
2135 MGD_ID_COMPONENT('filter',p_filter_value),
2136 MGD_ID_COMPONENT('cageordodaac',p_cage_code),
2137 MGD_ID_COMPONENT('serial',p_serial_number),
2138 MGD_ID_COMPONENT('schemes','USDOD-64'));
2139 END IF;
2140 END IF;
2141 END IF;
2142
2143 x_components := l_components;
2144
2145
2146 EXCEPTION
2147 WHEN fnd_api.g_exc_error THEN
2148 x_return_status := fnd_api.g_ret_sts_error;
2149 x_gtin_serial := NULL;
2150 x_gtin :=NULL;
2151 x_sscc := NULL;
2152 x_components := NULL;
2153 --RAISE; -- Do not raise here
2154
2155 WHEN fnd_api.g_exc_unexpected_error THEN
2156 x_return_status := fnd_api.g_ret_sts_unexp_error;
2157 x_gtin_serial := NULL;
2158 x_gtin :=NULL;
2159 x_sscc := NULL;
2160 x_components := NULL;
2161 --RAISE; -- Do not raise here
2162
2163
2164 WHEN OTHERS THEN
2165 IF l_debug = 1 THEN
2166 trace('Unexpected error inside get_epc_gen_info()');
2167 trace('ERROR CODE = ' || SQLCODE);
2168 trace('ERROR MESSAGE = ' || SQLERRM);
2169 END IF;
2170 x_return_status := fnd_api.g_ret_sts_unexp_error;
2171 x_gtin_serial := NULL;
2172 x_gtin := NULL;
2173 x_sscc := NULL;
2174 x_components := NULL;
2175 --RAISE; -- Do not raise here
2176 END get_epc_gen_info;
2177
2178
2179
2180 --*********************************************
2181 /*
2182 Procedure generate_epc
2183
2184 FUNCTIONALITY:-
2185 This is the main API in this package that is responsible for generating
2186 EPC for current transaction
2187
2188 PARAMETER:-
2189 p_org_id : Organization id
2190 p_label_type_id : Supported LPN/Serial/Material labels : VALID VALUES 1,2,3,4,5
2191 p_group_id : groun_id per invocation
2192 p_label_format_id : label_format_id for this transaction that should have has epc field
2193 p_item_id : need TO pass fpr Material Label: 1 only
2194 p_txn_qty : need TO pass fpr Material Label: 1 only
2195 p_txn_uom : need TO pass fpr Material Label: 1 only (since uom is not in wms_label_requests table)
2196 p_label_request_id: the id from wms_label_requests foe which label will be generated
2197 p_business_flow_code : business flow code value
2198 x_epc : returns generated EPC
2199 x_return_status :-
2200 S : success : EPC generated E : error : EPC could not be generated for valid reason
2201 U : Warning : EPC could not be generated for unexpected reason
2202
2203 x_return_mesg : Appropriate error message
2204
2205 */
2206
2207 --*********************************************
2208 --PROCEDURE definition applicable to RDBMS12c or higher
2209 Procedure generate_epc
2210 (p_org_id IN NUMBER,
2211 p_label_type_id IN NUMBER, /* VALID VALUES 1,2,3,4,5*/
2212 p_group_id IN NUMBER,
2213 p_label_format_id IN NUMBER,
2214 p_item_id IN NUMBER DEFAULT NULL, --For Material Label: 1
2215 p_txn_qty IN NUMBER DEFAULT null, --For Material Label: 1
2216 p_txn_uom IN VARCHAR2 DEFAULT NULL, --For Material Label: 1
2217 p_label_request_id IN NUMBER,
2218 p_business_flow_code IN NUMBER DEFAULT NULL,
2219 x_epc OUT nocopy VARCHAR2,
2220 x_return_status OUT nocopy VARCHAR2,
2221 x_return_mesg OUT nocopy VARCHAR2
2222 ) IS
2223
2224 end_processing EXCEPTION;
2225 l_gen_epc VARCHAR2(260);
2226 l_gen_binary_epc VARCHAR2(260);
2227 l_sscc NUMBER;
2228 l_gtin NUMBER;
2229 l_gtin_serial NUMBER;
2230 l_epc VARCHAR2(260);
2231 l_msg_count NUMBER;
2232
2233 l_return_status VARCHAR2(1);
2234 l_regenerate_flag VARCHAR2(1) := 'N';
2235 l_filter_value NUMBER;
2236 l_outermost_lpn_id NUMBER;
2237 l_PARENT_lpn_id NUMBER;
2238 l_return_mesg VARCHAR2(2000);
2239 --l_existing_epc_rule_id NUMBER;
2240 l_epc_output_rep NUMBER;
2241
2242 --New parameters
2243 is_epc_enabled VARCHAR2(1);
2244 l_company_prefix VARCHAR2(30);
2245 l_company_prefix_index VARCHAR2(30);
2246 l_cage_code VARCHAR2(30);
2247 l_custom_company_prefix VARCHAR2(30);
2248 l_epc_rule_type VARCHAR2(100);
2249 l_cust_comp_prefix_index NUMBER;
2250 l_comp_prefix_len NUMBER;
2251 l_cross_ref_type NUMBER;
2252 l_epc_category_id NUMBER;
2253 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2254 l_lpn_id NUMBER;
2255 l_serial_number VARCHAR2(30);
2256 l_item_id NUMBER;
2257 l_rev VARCHAR2(3);
2258 l_bit_length NUMBER;
2259 l_components mgd_id_component_varray;
2260 pcode mgd_id;
2261
2262 l_internet_proxy_hostname VARCHAR2(100) := FND_PROFILE.VALUE('WMS_INTERNET_PROXY_HOSTNAME');
2263 l_internet_proxy_port_number NUMBER := FND_PROFILE.VALUE('WMS_INTERNET_PROXY_PORT');
2264
2265 BEGIN
2266
2267 G_PROFILE_GTIN := FND_PROFILE.value('INV:GTIN_CROSS_REFERENCE_TYPE');
2268 x_return_status := fnd_api.g_ret_sts_success;
2269
2270
2271 IF l_debug = 1 THEN
2272 trace('************ inside generate_epc ***********');
2273 trace('p_org_id :'||p_org_id);
2274 trace(' p_label_request_id :'||p_label_request_id);
2275 trace('p_business_flow_code :'||p_business_flow_code);
2276 trace('p_group_id :'||p_group_id);
2277 trace('p_label_type_id :'||p_label_type_id);
2278 trace('p_label_format_id :'||p_label_format_id);
2279 trace('p_item_id :'||p_item_id);
2280 trace('p_txn_qty :'||p_txn_qty);
2281 trace('l_internet_proxy_hostname :'||l_internet_proxy_hostname);
2282 trace('l_internet_proxy_port_number :'||l_internet_proxy_port_number);
2283 END IF;
2284
2285 IF(l_internet_proxy_hostname IS NOT NULL AND l_internet_proxy_port_number IS NOT NULL) THEN
2286 DBMS_MGD_ID_UTL.set_proxy(l_internet_proxy_hostname, l_internet_proxy_port_number); --SETTING PROXY
2287 ELSE
2288 IF l_debug = 1 THEN
2289 trace('The WMS Internet proxy host name and/or number proxy profile Values are null so 64 bit models can fail');
2290 END IF;
2291 END IF;
2292
2293 DBMS_MGD_ID_UTL.refresh_category(DBMS_MGD_ID_UTL.get_category_id('EPC', NULL));
2294
2295 --{{get needed information from mtl_parameters setup for EPC generation }}
2296 IF ( NOT Inv_Cache.set_org_rec( p_organization_id => p_org_id ))THEN
2297 IF (l_debug = 1) THEN
2298 trace(p_org_id || 'is an invalid organization id');
2299 END IF;
2300 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
2301 fnd_msg_pub.ADD;
2302 RAISE fnd_api.g_exc_error;
2303 END IF;
2304
2305 l_company_prefix := inv_cache.org_rec.company_prefix;
2306 l_company_prefix_index := inv_cache.org_rec.company_prefix_index;
2307
2308
2309 IF l_debug = 1 THEN
2310 trace('epc_enabled :'||Nvl(inv_cache.org_rec.epc_generation_enabled_flag,'N'));
2311 trace('l_company_prefix :'||l_company_prefix);
2312 trace('l_company_prefix_index :'||l_company_prefix_index);
2313 trace('cage_code :'||inv_cache.org_rec.commercial_govt_entity_number);
2314 END IF;
2315
2316 --{{get needed information from label formats setup for the format_id}}
2317 SELECT epc_rule_type,filter_value,generate_epc,epc_category_id
2318 INTO l_epc_rule_type,l_filter_value,l_regenerate_flag, l_epc_category_id
2319 FROM wms_label_formats
2320 WHERE label_format_id = p_label_format_id
2321 AND Nvl(label_ENTITY_type,0) =0; --label format and NOT label-set
2322
2323 IF l_debug = 1 THEN
2324 trace('l_epc_rule_type :'||l_epc_rule_type);
2325 trace('l_filter_value :'||l_filter_value);
2326 trace('l_regenerate_flag :'||l_regenerate_flag);
2327 trace('l_epc_category_id :'||l_epc_category_id);
2328 END IF;
2329
2330 --Check if EPC is enabled
2331 IF Nvl(inv_cache.org_rec.epc_generation_enabled_flag,'N') = 'Y' THEN
2332
2333 --Get required information about the current transaction
2334 SELECT lpn_id,serial_number,inventory_item_id,revision
2335 INTO l_lpn_id,l_serial_number,l_item_id, l_rev
2336 FROM wms_label_requests
2337 WHERE label_request_id = p_label_request_id;
2338
2339
2340 IF l_debug = 1 THEN
2341 trace('l_lpn_id,l_serial_number,l_item_id, l_rev :'||l_lpn_id||','||l_serial_number||','||l_item_id||','||l_rev);
2342 END IF;
2343
2344 --Find if the EPC cross-ref already exist or it needs to be re-created
2345 --For LPN
2346
2347 IF l_lpn_id IS NOT NULL AND p_label_type_id IN (3,4,5) THEN /* LPN / LPN-Content / LPN Summary*/
2348
2349 l_cross_ref_type := 1;--/*LPN-EPC Cross ref*/
2350
2351 BEGIN
2352 SELECT wlpn.parent_lpn_id, wlpn.outermost_lpn_id, we.epc
2353 INTO l_parent_lpn_id, l_outermost_lpn_id, l_epc
2354 FROM wms_license_plate_numbers wlpn, wms_epc we
2355 WHERE wlpn.lpn_id = l_lpn_id
2356 AND wlpn.lpn_id = we.lpn_id(+)
2357 AND ((we.epc is NOT NULL and Nvl(we.cross_ref_type,1) = 1)
2358 or (we.epc is NULL )) ;
2359 --Nvl(we.cross_ref_type,1) to support EPC generated
2360 --using 11.5.10.2CU code
2361
2362 -- starting R12 this must be
2363 -- populated AND old data needs to be updated
2364
2365 EXCEPTION
2366 WHEN no_data_found THEN
2367
2368 IF l_debug = 1 THEN
2369 trace('NO DATA found for the LPN');
2370 END IF;
2371
2372 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
2373 fnd_msg_pub.ADD;
2374 RAISE fnd_api.g_exc_unexpected_error;
2375 END;
2376
2377 --Neither pallet or case, then NO support FOR EPC of further
2378 -- nested lpn, error out
2379 IF NOT ((l_outermost_lpn_id = l_lpn_id AND l_parent_lpn_id IS null )
2380 OR (l_parent_lpn_id = l_outermost_lpn_id AND l_parent_lpn_id IS NOT null)) THEN
2381
2382 fnd_message.set_name('WMS', 'WMS_NO_MULTI_NESTING_SUPPORT');
2383 fnd_msg_pub.ADD;
2384 RAISE fnd_api.g_exc_error;
2385
2386 END IF;
2387
2388 --For Serial
2389 ELSIF l_serial_number IS NOT NULL AND p_label_type_id = 2 THEN --/* Serial Label*/
2390
2391 l_cross_ref_type := 2;--/*Serial-EPC Cross ref*/
2392
2393 --Item_id + Serial_number will uniquely mapped to an EPC
2394 BEGIN
2395 SELECT we.epc INTO l_epc
2396 FROM wms_epc we
2397 WHERE INVENTORY_item_id = l_item_id
2398 AND serial_number = l_serial_number
2399 AND we.cross_ref_type = 2;
2400 EXCEPTION
2401 WHEN no_data_found THEN
2402 NULL;
2403 WHEN OTHERS THEN
2404 RAISE fnd_api.g_exc_unexpected_error;
2405 END;
2406
2407 --For item_id/GTIN
2408 ELSIF l_item_id IS NOT NULL AND p_label_type_id = 1 THEN --/*Material Label*/
2409
2410 l_cross_ref_type := 3; --/*GTIN+GTIN_SERIAL-EPC Cross ref*/
2411
2412 --No need to check whether EPC exists or NOT for the GTIN
2413 --just regenerate EPC, EPC will be unique to GTIN+GTIN-SERIAL combination
2414 --and it needs to be inserted
2415
2416 l_regenerate_flag := 'Y'; -- override always
2417 END IF;
2418
2419
2420 --{{call to see if the custom company_prefix is implemented}}
2421 wms_epc_pub.GET_CUSTOM_COMPANY_PREFIX(
2422 p_org_id => p_org_id,
2423 p_label_request_id => p_label_request_id,
2424 X_company_prefix => l_custom_company_prefix,
2425 X_RETURN_STATUS => l_return_status);
2426
2427 --{{call to see if the custom company_prefix_INDEX is implemented}}
2428 wms_epc_pub.GET_CUSTOM_COMP_PREFIX_INDEX(p_org_id => p_org_id,
2429 p_label_request_id => p_label_request_id,
2430 X_comp_prefix_INDEX => l_CUST_comp_prefix_INDEX,
2431 X_RETURN_STATUS => l_return_status);
2432
2433 IF l_debug = 1 THEN
2434 trace('CUSTOM_COMPANY_PREFIX value :'||l_custom_company_prefix);
2435 trace('CUSTOM_COMPANY_PREFIX_INDEX value :'||l_cust_comp_prefix_index);
2436 END IF;
2437
2438 IF l_custom_company_prefix IS NOT NULL THEN
2439 l_company_prefix := l_custom_company_prefix;
2440 END IF;
2441
2442 IF l_cust_comp_prefix_index IS NOT NULL THEN
2443 l_company_prefix_index := l_cust_comp_prefix_index;
2444 END IF;
2445
2446 IF l_debug = 1 THEN
2447 trace('Final COMPANY_PREFIX value :'||l_company_prefix);
2448 trace('Final COMPANY_PREFIX_INDEX value :'||l_company_prefix_index );
2449 END IF;
2450
2451 IF l_debug = 1 THEN
2452 trace('Generating EPC now.............');
2453 END IF;
2454
2455 --{{ See if the rule is custom or Un-Implemented Standard Rule }}
2456 IF l_epc_category_id <> DBMS_MGD_ID_UTL.GET_CATEGORY_ID('EPC',NULL)
2457 OR ( l_epc_category_id = DBMS_MGD_ID_UTL.GET_CATEGORY_ID('EPC',NULL) AND
2458 l_epc_rule_type NOT IN ('SGTIN-96','SSCC-96','SGTIN-64','SSCC-64','USDOD-96','USDOD-64') )
2459 --means in ('GIAI-96','GRAI-96','SGLN-96','GID-96','GIAI-64','GRAI-64','SGLN-64','GID-64')
2460 THEN
2461
2462 -- {{ get custom EPC generated }}
2463 wms_epc_pub.get_custom_epc
2464 (p_org_id => p_org_id,
2465 p_category_id => l_epc_category_id,-- In mgd_idencoding_type table
2466 p_epc_rule_type_id => l_epc_rule_type, --Rule_type_id IN mgd_idencoding_type table
2467 p_filter_value => l_filter_value,
2468 p_label_request_id => p_label_request_id, --to get all data FROM wms_device_requests label
2469 x_return_status => l_return_status,
2470 x_return_mesg => l_return_mesg,
2471 x_epc => l_epc);
2472
2473 ELSIF l_epc_rule_type IN
2474 ('SGTIN-96','SSCC-96','SGTIN-64','SSCC-64','USDOD-96','USDOD-64') THEN
2475 --{{ get standard EPC generated for standard rule}}
2476
2477 --{{ Check to see if regenerate flag is ON..only then generate epc }}
2478 IF l_regenerate_flag = 'Y' OR (l_epc is NULL AND
2479 Nvl(l_regenerate_flag,'N') = 'N' ) THEN
2480
2481 --{{ get EPC Encoding Rule components }}
2482 IF l_debug = 1 THEN
2483 trace('Collecting information to generate EPC ....');
2484 END IF;
2485
2486 get_epc_gen_info( p_org_id => p_org_id,
2487 p_lpn_id => l_lpn_id, --FOR p_label_type_id = 3,4,5
2488 p_serial_number => l_serial_number, --FOR p_label_type_id = 2
2489 p_item_id => Nvl(l_item_id,p_item_id), --FOR p_label_type_id = 1
2490 p_txn_qty => p_txn_qty, --FOR p_label_type_id = 1
2491 p_txn_uom => p_txn_uom, --FOR p_label_type_id = 1
2492 p_rev => l_rev, --FOR p_label_type_id = 1,2
2493 p_company_prefix => l_company_prefix,
2494 p_comp_prefix_index => l_company_prefix_index,
2495 p_business_flow_code => p_business_flow_code,
2496 p_label_type_id => P_label_type_id,
2497 p_epc_rule_type => l_epc_rule_type,
2498 p_filter_value => l_filter_value,
2499 p_cage_code => inv_cache.org_rec.commercial_govt_entity_number, --FOR p_label_type_id = 2
2500 x_gtin => l_gtin,
2501 x_sscc => l_sscc,
2502 x_gtin_serial => l_gtin_seriaL,
2503 x_components => l_components,
2504 x_return_status => l_return_status);
2505
2506 IF l_debug = 1 THEN
2507 trace('after calling get_epc_gen_info... ');
2508 trace('l_gtin :'||l_gtin );
2509 trace('l_sscc :'||l_sscc);
2510 trace('l_gtin_serial :'||l_gtin_serial);
2511 trace('l_return_status :'||l_return_status);
2512 END IF;
2513
2514
2515 --{{ genereate EPC using the components }}
2516
2517 IF l_return_status = fnd_api.g_ret_sts_success AND l_components IS NOT NULL THEN
2518 IF l_debug = 1 THEN
2519 trace('Before calling DB EPC category_name :'||DBMS_MGD_ID_UTL.get_category_id('EPC',NULL));
2520 END IF;
2521
2522 BEGIN
2523 pcode := MGD_ID (DBMS_MGD_ID_UTL.get_category_id('EPC',NULL),l_components);
2524 l_gen_binary_epc := pcode.format(NULL,'BINARY');
2525
2526 IF l_debug = 1 THEN
2527 trace('EPC generated by DB Feature in binary via new model :'||l_gen_binary_epc);
2528 END IF;
2529
2530 IF(l_gen_binary_epc IS NOT NULL) THEN
2531 l_gen_epc := dec2hex(bin2dec(l_gen_binary_epc));
2532 END IF;
2533
2534 IF l_debug = 1 THEN
2535 trace('EPC generated by DB Feature in HEX converted from binary :'||l_gen_epc);
2536 END IF;
2537
2538 EXCEPTION
2539 WHEN OTHERS THEN
2540
2541 IF l_debug = 1 THEN
2542 TRACE('After calling mgd_idcode: Inside exception');
2543 TRACE('ERROR CODE = ' || SQLCODE);
2544 TRACE('ERROR MESSAGE = ' || SQLERRM);
2545 END IF;
2546
2547 --EPC generation failed at DB feature level
2548 fnd_message.set_name('WMS', 'WMS_DB_EPC_GEN_FAIL');
2549 fnd_msg_pub.ADD;
2550
2551 --Do not raise exception here as we want to delete
2552 --old cross-reference RECORD FROM wms_epc for some
2553 --CASES BELOW
2554 END;
2555
2556 ELSE
2557 -- {{Error out l_components are null, EPC could not be generated }}
2558
2559 IF l_debug = 1 THEN
2560 trace('get_epc_gen_info() returned error');
2561 trace('Error: Components could not be obtained for EPC generation ');
2562 END IF;
2563
2564
2565 fnd_message.set_name('WMS', 'WMS_EPC_GEN_FAIL');
2566 fnd_msg_pub.ADD;
2567
2568 --Do not raise exception here as we want to delete
2569 --old cross-reference RECORD FROM wms_epc for some
2570 --CASES BELOW
2571
2572 END IF; --l_return_status = 'S for get_epc_gen_info()
2573
2574
2575 END IF; --l_regenerate_flag = 'Y'
2576
2577 END IF; --means 'SGTIN-96','SSCC-96','SGTIN-64','SSCC-64',EPC_DOD_96,EPC_DOD_64''SGTIN-96','SSCC-96'
2578
2579 ELSE
2580 IF l_debug = 1 THEN
2581 trace('EPC generation is NOT enabled at Orgnization level');
2582 END IF;
2583
2584 fnd_message.set_name('WMS', 'WMS_EPC_DISABLED');
2585 fnd_msg_pub.ADD;
2586 RAISE fnd_api.g_exc_error;
2587
2588 END IF;
2589
2590
2591 --{{ By NOW EPC should be generate for valid cases: Insert EPC or update EPC or delete EPC }}
2592
2593 IF l_debug = 1 THEN
2594 trace('Old EPC,if any :'||l_epc);
2595 trace('New generated EPC:'||l_gen_epc);
2596 END IF;
2597
2598
2599
2600 --{{ Get the EPC ENCODING defined with the profile 'WMS_EPC_ENCODING'}}
2601 l_epc_output_rep := NVL(fnd_profile.value('WMS_EPC_ENCODING'), 2);
2602 -- 1 : Binary
2603 -- 2 : Hex
2604 -- 3 : Decimal
2605
2606 IF l_debug = 1 THEN
2607 trace('1-Binary,2-Hex,3-Decimal l_epc_output_rep :'||l_epc_output_rep);
2608 END IF;
2609
2610
2611 --l_epc_rule_type_id is already identified above
2612 IF l_regenerate_flag = 'Y' OR (l_epc is NULL AND
2613 Nvl(l_regenerate_flag,'N') = 'N' ) THEN
2614
2615
2616 IF l_epc IS NOT NULL AND l_gen_epc IS NOT NULL THEN
2617 --{{ EPC c/r already there: UPDATE LAST EPC with new EPC value }}
2618 uptodate_wms_epc ( p_action => 'UPDATE',
2619 p_group_id => p_group_id,
2620 p_cross_ref_type => l_cross_ref_type,
2621 p_epc_rule_type_name => l_epc_rule_type,
2622 p_lpn_id => l_lpn_id,
2623 p_item_id => l_item_id,
2624 p_serial_number => l_serial_number,
2625 p_gen_epc => l_gen_epc,
2626 p_sscc => l_sscc,
2627 p_gtin => l_gtin,
2628 p_gtin_serial => l_gtin_serial,
2629 p_filter_VALUE => l_filter_value,
2630 x_return_status => L_RETURN_STATUS);
2631
2632
2633 IF l_debug =1 then
2634 trace(' uptodate_wms_epc UPDATE: L_RETURN_STATUS:'||l_return_status);
2635 END IF;
2636
2637 --return new EPC IN THE FORMAT SPECIFIED
2638 IF L_epc_output_rep = 1 THEN --Binary
2639 x_epc := l_gen_binary_epc;
2640
2641 ELSIF l_epc_output_rep = 3 THEN --Decimal
2642 x_epc := hex2dec(l_gen_epc);
2643 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
2644 x_epc := l_gen_epc;
2645 END IF;
2646
2647 IF l_debug =1 then
2648 trace('Final format EPC :'||x_epc);
2649 END IF;
2650
2651 ELSIF l_epc IS NOT NULL AND l_gen_epc IS NULL THEN
2652 -- Delete the existing cross -reference
2653
2654 uptodate_wms_epc ( p_action => 'DELETE',
2655 p_group_id => p_group_id,
2656 p_cross_ref_type => l_cross_ref_type,
2657 p_epc_rule_type_name => l_epc_rule_type,
2658 p_lpn_id => l_lpn_id,
2659 p_item_id => l_item_id,
2660 p_serial_number => l_serial_number,
2661 p_gen_epc => l_gen_epc,
2662 p_sscc => l_sscc,
2663 p_gtin => l_gtin,
2664 p_gtin_serial => l_gtin_serial,
2665 p_filter_VALUE => l_filter_value,
2666 x_return_status => L_RETURN_STATUS);
2667
2668 IF l_debug =1 then
2669 trace(' uptodate_wms_epc DELETE: L_RETURN_STATUS:'||l_return_status);
2670 END IF;
2671
2672 RAISE fnd_api.g_exc_error;--COULD NOT OVERIDE THE EPC
2673
2674
2675 ELSIF l_epc IS NULL AND l_gen_epc IS NOT NULL THEN
2676
2677 uptodate_wms_epc( p_action => 'INSERT',
2678 p_group_id => p_group_id,
2679 p_cross_ref_type => l_cross_ref_type,
2680 p_epc_rule_type_name => l_epc_rule_type,
2681 p_lpn_id => l_lpn_id,
2682 p_item_id => l_item_id,
2683 p_serial_number => l_serial_number,
2684 p_gen_epc => l_gen_epc,
2685 p_sscc => l_sscc,
2686 p_gtin => l_gtin,
2687 p_gtin_serial => l_gtin_serial,
2688 p_filter_VALUE => l_filter_value,
2689 x_return_status => L_RETURN_STATUS);
2690
2691 IF l_debug =1 then
2692 trace(' uptodate_wms_epc INSERT: L_RETURN_STATUS:'||l_return_status);
2693 END IF;
2694
2695 --return new EPC IN THE FORMAT SPECIFIED
2696 IF L_epc_output_rep = 1 THEN --Binary
2697 l_bit_length := To_number(Substr(l_epc_rule_type,(Length(l_epc_rule_type)-1),Length(l_epc_rule_type)));
2698 x_epc := Lpad(dec2bin(hex2dec(l_gen_epc)),l_bit_length,'0');
2699 ELSIF l_epc_output_rep = 3 THEN --Decimal
2700 x_epc := hex2dec(l_gen_epc);
2701 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
2702 x_epc := l_gen_epc;
2703 END IF;
2704
2705
2706 ELSIF l_epc IS NULL AND l_gen_epc IS NULL THEN
2707
2708 RAISE fnd_api.g_exc_error;
2709
2710 END IF;
2711
2712 ELSIF Nvl(l_regenerate_flag,'N') = 'N' THEN
2713
2714 IF l_epc IS NOT NULL THEN
2715
2716 --Return Old EPC,Already it was stored in Hex
2717 --return new EPC IN THE FORMAT SPECIFIED
2718 IF L_epc_output_rep = 1 THEN --Binary
2719 l_bit_length := To_number(Substr(l_epc_rule_type,(Length(l_epc_rule_type)-1),Length(l_epc_rule_type)));
2720 x_epc := Lpad(dec2bin(hex2dec(l_gen_epc)),l_bit_length,'0');
2721
2722 ELSIF l_epc_output_rep = 3 THEN --Decimal
2723 x_epc := hex2dec(l_gen_epc);
2724 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
2725 x_epc := l_gen_epc;
2726 END IF;
2727
2728 END IF; -- L_EPC IS NOT NULL
2729
2730 END IF; -- For p_regenerate_flag = 'N'
2731
2732 DBMS_MGD_ID_UTL.REMOVE_PROXY;
2733
2734 EXCEPTION
2735
2736 WHEN fnd_api.g_exc_error THEN
2737 fnd_message.set_name('WMS','WMS_EPC_GEN_FAIL');
2738 fnd_msg_pub.ADD;
2739 x_return_status := fnd_api.g_ret_sts_error;
2740 x_epc := NULL;
2741 fnd_msg_pub.count_and_get(p_encoded => 'F',p_count => l_msg_count, p_data => x_return_mesg);
2742
2743 FOR i IN 1..l_msg_count LOOP
2744 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
2745 END LOOP;
2746
2747 IF l_debug = 1 THEN
2748 TRACE('Inside g_exc_error l_msg_count :'||l_msg_count);
2749 TRACE('x_return_mesg :'||x_return_mesg);
2750 END IF;
2751
2752 -- FND_MSG_PUB.initialize; --bug 5178424
2753
2754
2755 WHEN fnd_api.g_exc_unexpected_error THEN
2756 fnd_message.set_name('WMS', 'WMS_EPC_GEN_FAIL');
2757 fnd_msg_pub.ADD;
2758 x_return_status := fnd_api.g_ret_sts_unexp_error;
2759 x_epc := NULL;
2760 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => x_return_mesg);
2761
2762 FOR i IN 1..l_msg_count LOOP
2763 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
2764 END LOOP;
2765
2766 IF l_debug = 1 THEN
2767 TRACE('Inside g_exc_unexpected_error l_msg_count :'||l_msg_count);
2768 TRACE('x_return_mesg :'||x_return_mesg);
2769 END IF;
2770
2771 -- FND_MSG_PUB.initialize; --bug 5178424
2772
2773 WHEN OTHERS THEN
2774 --ROLLBACK; blocked in R12
2775 x_return_status := fnd_api.g_ret_sts_unexp_error;
2776 x_epc := NULL;
2777 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => x_return_mesg);
2778
2779 FOR i IN 1..l_msg_count LOOP
2780 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
2781 END LOOP;
2782
2783 -- FND_MSG_PUB.initialize; --bug 5178424
2784
2785 IF l_debug = 1 THEN
2786 TRACE('generate EPC: Inside exception');
2787 TRACE('ERROR CODE = ' || SQLCODE);
2788 TRACE('ERROR MESSAGE = ' || SQLERRM);
2789 END IF;
2790
2791 END generate_epc;
2792 --Function applicable to RDBMS12c or higher
2793 FUNCTION db_version RETURN NUMBER IS
2794 BEGIN
2795 trace('Call from forms for DB version, returning 12 as higher DB version is 12c or higher so use new RFID model');
2796 RETURN 12;
2797 END db_version;
2798 --Bug 8796558 new model changes end
2799
2800 $ELSE
2801 --This procedure Insert / Upadate / Delete in WMS_EPC table with data
2802 --passed based ON action specified
2803 --PROCEDURE applicable to DB versions below RDBMS12c
2804 PROCEDURE uptodate_wms_epc
2805 (p_action IN VARCHAR2,
2806 p_group_id IN NUMBER,
2807 p_cross_ref_type IN NUMBER,
2808 p_EPC_rule_TYPE_id IN NUMBER,
2809 p_lpn_id IN NUMBER,
2810 p_item_id IN NUMBER,
2811 p_serial_number IN NUMBER,
2812 p_gen_epc IN VARCHAR2,
2813 p_sscc IN NUMBER,
2814 p_gtin IN NUMBER, --calling API makes it number
2815 p_gtin_serial IN NUMBER,
2816 p_filter_VALUE IN NUMBER,
2817 x_return_status OUT nocopy VARCHAR2
2818 ) IS
2819
2820 PRAGMA AUTONOMOUS_TRANSACTION;
2821
2822 l_epc_id NUMBER;
2823 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2824 BEGIN
2825
2826 x_return_status := fnd_api.g_ret_sts_success;
2827
2828 IF l_debug = 1 THEN
2829 trace('************UPtoDATE WMS_EPC with old RFID model***');
2830 trace('p_action :'|| p_action );
2831 trace('p_group_id :'|| p_group_id);
2832 trace('p_cross_ref_type :'|| p_cross_ref_type);
2833 trace('p_epc_rule_type_id :'|| p_EPC_rule_TYPE_id);
2834 trace('p_lpn_id :'|| p_lpn_id);
2835 trace('p_item_id :'|| p_item_id);
2836 trace('p_serial_number :'|| p_serial_number);
2837 trace('p_gen_epc :'|| p_gen_epc);
2838 trace('p_sscc :'|| p_sscc);
2839 trace('p_gtin :'|| p_gtin);
2840 END IF;
2841
2842
2843 IF p_cross_ref_type = 1 THEN --LPN-EPC /* LPN related label*/
2844
2845 IF p_action = 'UPDATE' THEN
2846
2847 UPDATE wms_epc
2848 SET epc = p_gen_epc,
2849 cross_ref_type = p_cross_ref_type,
2850 group_id = p_group_id,
2851 last_update_date = Sysdate,
2852 last_updated_by = fnd_global.user_id,
2853 epc_rule_type_id = p_epc_rule_type_id,
2854 sscc = P_sscc,
2855 gtin = P_gtin,
2856 gtin_serial = NULL,
2857 inventory_item_id = NULL,
2858 serial_number = NULL,
2859 filter_object_type = p_filter_value,
2860 status = 'LABEL_PRINTED',
2861 status_code = 'S'
2862 WHERE lpn_id = p_lpn_id;
2863
2864 ELSIF p_action = 'INSERT' THEN
2865
2866 --INSERT NEW EPC RECORD
2867
2868 INSERT INTO wms_epc( group_id,
2869 cross_ref_type,
2870 epc_rule_type_id,
2871 lpn_id,
2872 serial_number,
2873 inventory_item_id,
2874 gtin_serial,
2875 gtin,
2876 sscc,
2877 epc,
2878 filter_object_type,
2879 status_code,
2880 status,
2881 creation_date,
2882 created_by,
2883 last_update_date,
2884 last_updated_by,
2885 last_update_login,
2886 epc_id,
2887 epc_rule_id
2888 ) VALUES (P_group_id,
2889 p_cross_ref_type,
2890 P_epc_rule_type_id,
2891 p_lpn_id,
2892 NULL,--p_serial_number,
2893 NULL,--p_ITEM_ID,
2894 NULL,--p_GTIN_SERIAL,
2895 P_gtin,
2896 P_sscc,
2897 P_gen_epc,
2898 p_filter_value,
2899 'S',
2900 'LABEL_PRINTED',
2901 Sysdate,
2902 fnd_global.user_id,
2903 Sysdate,
2904 fnd_global.user_id,
2905 fnd_global.user_id,
2906 NULL, --epc_id NOT used post R12
2907 NULL); --epc_rule_id NOT used post R12
2908
2909 ELSIF p_action = 'DELETE' THEN
2910
2911 -- Delete the existing cross -reference
2912 DELETE FROM wms_epc WHERE lpn_id = p_lpn_id;
2913
2914 END IF;
2915
2916
2917 ELSIF p_cross_ref_type = 2 THEN -- Item_Serial - EPC /* Serial Label */
2918
2919 IF p_action = 'UPDATE' THEN
2920
2921 UPDATE wms_epc
2922 SET epc = p_gen_epc,
2923 cross_ref_type = p_cross_ref_type,
2924 group_id = p_group_id,
2925 last_update_date = Sysdate,
2926 last_updated_by = fnd_global.user_id,
2927 epc_rule_type_id = p_epc_rule_type_id,
2928 sscc = NULL,-- No other value possible
2929 gtin = P_gtin,
2930 gtin_serial = NULL,
2931 lpn_id = NULL,
2932 filter_object_type = p_filter_value,
2933 status = 'LABEL_PRINTED',
2934 status_code = 'S'
2935 WHERE inventory_item_id = p_item_id
2936 AND serial_number = p_serial_number;
2937
2938 ELSIF p_action = 'INSERT' THEN
2939
2940 --INSERT NEW EPC RECORD
2941
2942 INSERT INTO wms_epc( group_id,
2943 cross_ref_type,
2944 epc_rule_type_id,
2945 lpn_id,
2946 serial_number,
2947 inventory_item_id,
2948 gtin_serial,
2949 gtin,
2950 sscc,
2951 epc,
2952 filter_object_type,
2953 status_code,
2954 status,
2955 creation_date,
2956 created_by,
2957 last_update_date,
2958 last_updated_by,
2959 last_update_login,
2960 epc_id,
2961 epc_rule_id
2962 ) VALUES (P_group_id,
2963 p_cross_ref_type,
2964 P_epc_rule_type_id,
2965 NULL,-- lpn_id
2966 p_serial_number,
2967 p_item_id,
2968 NULL,--p_gtin_serial,
2969 P_gtin,
2970 NULL,--p_sscc
2971 P_gen_epc,
2972 p_filter_value,
2973 'S',
2974 'LABEL_PRINTED',
2975 Sysdate,
2976 fnd_global.user_id,
2977 Sysdate,
2978 fnd_global.user_id,
2979 fnd_global.user_id,
2980 NULL, --epc_id NOT used post R12
2981 NULL); --epc_rule_id NOT used post R12
2982
2983 ELSIF p_action = 'DELETE' THEN
2984
2985 -- Delete the existing cross -reference
2986 DELETE FROM wms_epc
2987 WHERE inventory_item_id = p_item_id
2988 AND serial_number = p_serial_number;
2989
2990
2991 END IF;
2992
2993 ELSIF p_cross_ref_type = 3 THEN --GTIN+GTIN_Serial - EPC /* Material Label */
2994
2995 IF p_action = 'UPDATE' THEN
2996
2997 UPDATE wms_epc
2998 SET epc = p_gen_epc,
2999 cross_ref_type = p_cross_ref_type,
3000 group_id = p_group_id,
3001 last_update_date = Sysdate,
3002 last_updated_by = fnd_global.user_id,
3003 epc_rule_type_id = p_epc_rule_type_id,
3004 sscc = NULL, --NO other value possible in this case
3005 serial_number = NULL,
3006 inventory_item_id = NULL,
3007 lpn_id = NULL,
3008 filter_object_type = p_filter_value,
3009 status = 'LABEL_PRINTED',
3010 status_code = 'S'
3011 WHERE GTIN = p_gtin
3012 AND GTIN_serial = p_gtin_serial;
3013
3014 ELSIF p_action = 'INSERT' THEN
3015
3016 --INSERT NEW EPC RECORD
3017
3018 INSERT INTO wms_epc( group_id,
3019 cross_ref_type,
3020 epc_rule_type_id,
3021 lpn_id,
3022 serial_number,
3023 inventory_item_id,
3024 gtin_serial,
3025 gtin,
3026 sscc,
3027 epc,
3028 filter_object_type,
3029 status_code,
3030 status,
3031 creation_date,
3032 created_by,
3033 last_update_date,
3034 last_updated_by,
3035 last_update_login,
3036 epc_id,
3037 epc_rule_id
3038 ) VALUES (P_group_id,
3039 p_cross_ref_type,
3040 P_epc_rule_type_id,
3041 NULL ,-- p_lpn_id
3042 NULL, --p_serial_number
3043 NULL, --p_item_id,
3044 p_GTIN_serial,
3045 P_gtin,
3046 NULL, --p_sscc,
3047 P_gen_epc,
3048 p_filter_value,
3049 'S',
3050 'LABEL_PRINTED',
3051 Sysdate,
3052 fnd_global.user_id,
3053 Sysdate,
3054 fnd_global.user_id,
3055 fnd_global.user_id,
3056 NULL, --epc_id NOT used post R12
3057 NULL); --epc_rule_id NOT used post R12
3058
3059 ELSIF p_action = 'DELETE' THEN
3060
3061 -- Delete the existing cross -reference
3062 DELETE FROM wms_epc
3063 WHERE GTIN = p_gtin
3064 AND GTIN_serial = p_gtin_serial;
3065
3066 END IF;
3067
3068
3069 END IF;
3070
3071
3072 --COMMIT THE autonomous txn part of updating record in WMS_EPC
3073 COMMIT;
3074
3075 EXCEPTION
3076 WHEN OTHERS THEN
3077 x_return_status := fnd_api.g_ret_sts_error;
3078 IF l_debug = 1 THEN
3079 TRACE('UPTODATE WMS_EPC: inside exception');
3080 TRACE('ERROR CODE = ' || SQLCODE);
3081 TRACE('ERROR MESSAGE = ' || SQLERRM);
3082 END IF;
3083
3084 END uptodate_wms_epc;
3085
3086 --Given EPC_rule_type and company_prefix, calculates the pre-defined
3087 --PARTITION value (specified by EPC global standard)
3088 --FUNCTION applicable to DB versions below RDBMS12c
3089 FUNCTION get_PARTITION_value(p_epc_rule_type IN VARCHAR2,
3090 P_company_prefix IN VARCHAR2) RETURN NUMBER
3091 IS
3092 l_partition NUMBER;
3093 l_comp_pref_len NUMBER;
3094 BEGIN
3095
3096 /*
3097 following TYPE OF epc TYPE required PARTITION length
3098 epc_sgtin_96
3099 epc_sscc_96
3100 epc_giai_96
3101 epc_grai_96
3102 epc_sgln_96
3103
3104 DoD-96/64 - does NOT need partition value
3105 */
3106
3107
3108 --GET THE decimal length of company-prefix
3109 l_comp_pref_len := Length(P_company_prefix);
3110
3111
3112 IF p_epc_rule_type = 'EPC_SGTIN_96' OR p_epc_rule_type = 'EPC_SSCC_96'
3113 OR p_epc_rule_type ='EPC_SGLN_96' OR p_epc_rule_type = 'EPC_GIAI_96'
3114 OR p_epc_rule_type = 'EPC_GRAI_96' THEN
3115
3116 IF l_comp_pref_len = 12 THEN l_partition := 0;
3117 ELSIF l_comp_pref_len =11 THEN l_partition := 1;
3118 ELSIF l_comp_pref_len =10 THEN l_partition := 2;
3119 ELSIF l_comp_pref_len =9 THEN l_partition := 3;
3120 ELSIF l_comp_pref_len =8 THEN l_partition := 4;
3121 ELSIF l_comp_pref_len =7 THEN l_partition := 5;
3122 ELSIF l_comp_pref_len =6 THEN l_partition := 6;
3123 ELSE l_partition := -1; --Error condition
3124 END IF;
3125
3126 ELSE
3127
3128 l_partition := 0; --partition is not needed for this STANDARD EPC TYPE generation
3129
3130 END IF;
3131
3132 RETURN l_partition;
3133
3134 END get_PARTITION_value;
3135
3136
3137 --Collects all informtion needed to generate EPC for given object
3138 -- For a given EPC generation type the output parameter x_components
3139 -- contains required elements that will be passed to DB- EPC generation API
3140 -- to generate EPC
3141 --PROCEDURE applicable to DB versions below RDBMS12c
3142 PROCEDURE get_epc_gen_info( p_org_id IN NUMBER,
3143 p_lpn_id IN NUMBER, --FOR p_label_type_id = 3,4,5
3144 p_serial_number IN VARCHAR2, --FOR p_label_type_id = 2
3145 p_item_id IN NUMBER, --FOR p_label_type_id = 1,2
3146 p_txn_qty IN NUMBER, --FOR p_label_type_id = 1
3147 p_txn_uom IN VARCHAR2, --FOR p_label_type_id = 1
3148 p_rev IN VARCHAR2, --FOR p_label_type_id = 1,2
3149 p_company_prefix IN VARCHAR2,
3150 p_comp_prefix_index IN VARCHAR2,
3151 p_business_flow_code IN NUMBER,
3152 p_label_type_id IN NUMBER,
3153 p_epc_rule_type IN VARCHAR2,
3154 p_filter_value IN NUMBER,
3155 p_cage_code IN VARCHAR2, --FOR p_label_type_id = 2
3156 p_partition_value IN NUMBER,
3157 x_gtin OUT nocopy NUMBER,
3158 x_sscc OUT nocopy NUMBER,
3159 x_gtin_serial OUT nocopy NUMBER,
3160 x_components OUT nocopy mgd_idcomponent_varray,
3161 x_return_status OUT nocopy VARCHAR2)
3162 IS
3163
3164 l_components MGD_IDCOMPONENT_VARRAY;
3165
3166 l_sscc_len NUMBER;
3167 l_gtin_len NUMBER;
3168 l_lpn_num_format NUMBER;
3169 l_is_sscc_valid BOOLEAN := TRUE;
3170 l_is_gtin_valid BOOLEAN := TRUE;
3171 l_item_id NUMBER;
3172 l_total_qty NUMBER :=0;
3173 l_found_gtin NUMBER := 0;
3174 l_rev VARCHAR2(3);
3175 l_uom_code VARCHAR2(3);
3176 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3177 l_gtin NUMBER;
3178
3179 l_total_wlc_qty NUMBER;
3180 l_total_mmtt_qty NUMBER;
3181 l_total_mmtt_qty1 NUMBER;
3182 l_total_mmtt_qty2 NUMBER;
3183 l_comp_prefix_dig_len NUMBER;
3184 l_item_reference NUMBER;
3185 l_serial_reference NUMBER;
3186 l_gtin_serial NUMBER;
3187 l_sscc NUMBER;
3188
3189 l_return_status VARCHAR2(1);
3190 --l_primary_uom_code VARCHAR2(3);
3191
3192 ----------------------------------------
3193 /*
3194 Following table shows Valid set up in Label Format Form for EPC
3195 generation using diferent standard (X means acceptable)
3196
3197 Standard\LabelType LPN/LPNContent/LPNSummary Material Serial
3198 SGTIN_96/64 X X X
3199 SSCC_96/64 X - -
3200 DoD_96/64 - - X
3201 */
3202 -----------------------------------------
3203
3204
3205 BEGIN
3206
3207 x_return_status := fnd_api.g_ret_sts_success;
3208
3209 IF p_label_type_id IN (3,4,5) THEN --LPN, LPN Content, LPN Summary
3210
3211 l_comp_prefix_dig_len:= Length(p_company_prefix);
3212
3213 IF p_epc_rule_type IN ('EPC_SGTIN_96','EPC_SGTIN_64') THEN
3214
3215 -- {{get GTIN and gtin-Serial for the LPN }}
3216 get_lpn_gtin_serial(p_lpn_id => p_lpn_id,
3217 p_org_id => p_org_id,
3218 p_filter_value => p_filter_value,
3219 p_business_flow_code => p_business_flow_code,
3220 x_gtin => l_gtin,
3221 x_gtin_serial => l_gtin_serial,
3222 x_return_status => l_return_status);
3223
3224 --{{ get the item reference from GTIN for LPN now }}
3225 --{{ l_gtin obtained IS NOT NULL }}
3226
3227 IF l_return_status = fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
3228
3229 l_item_reference := To_number(Substr(To_char(l_gtin),1,1)||Substr(To_char(l_gtin),l_comp_prefix_dig_len+2,12-l_comp_prefix_dig_len));
3230
3231 IF l_debug = 1 THEN
3232 trace('l_gtin , l_gtin_serial :' || l_gtin||','||l_gtin_serial);
3233 trace('l_item_reference :'|| l_item_reference);
3234 END IF;
3235
3236 x_gtin := l_gtin;
3237 x_sscc := NULL;
3238 x_gtin_serial := l_gtin_serial;
3239
3240 --{{ get all expected components FOR EPC_SGTIN_96}}
3241 IF p_epc_rule_type = 'EPC_SGTIN_96' THEN
3242
3243 --use company-prefix
3244
3245 IF l_debug = 1 THEN
3246 trace('p_filter_value,p_partition_value, p_company_prefix,l_item_reference,l_gtin_serial');
3247 trace(p_filter_value||','||p_partition_value||','||To_number(p_company_prefix)||','||l_item_reference||','||l_gtin_serial);
3248 END IF;
3249
3250 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',48,NULL), --for 00110000
3251 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
3252 MGD_IDCOMPONENT('PARTITION',p_partition_value,NULL),
3253 MGD_IDCOMPONENT('COMPANYPREFIX',To_number(p_company_prefix),NULL),
3254 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
3255 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
3256
3257
3258 ELSIF p_epc_rule_type = 'EPC_SGTIN_64' THEN
3259
3260 --{{ get all expected components FOR EPC_SGTIN_64}}
3261 --use company-prefix -INDEX
3262 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',2,NULL),--10
3263 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
3264 MGD_IDCOMPONENT('COMPANYPREFIXINDEX',To_number(p_comp_prefix_index),NULL),
3265 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
3266 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
3267
3268 END IF;
3269 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF get_lpn_gtin_serial()
3270 END IF;
3271
3272
3273 ELSIF p_epc_rule_type IN ('EPC_SSCC_96','EPC_SSCC_64') THEN
3274
3275 --{{ get SSCC for LPN }}
3276
3277 l_sscc := get_sscc(p_lpn_id,p_org_id);
3278
3279 IF l_debug = 1 THEN
3280 trace('SSCC for the LPN :'||l_sscc);
3281 trace('l_comp_prefix_dig_len :'||l_comp_prefix_dig_len);
3282 trace('p_filter_value :'||p_filter_value);
3283 trace('To_number(p_company_prefix) :'|| To_number(p_company_prefix));
3284 END IF;
3285
3286
3287 IF l_sscc IS NOT NULL THEN
3288
3289 x_gtin := NULL;
3290 x_sscc := l_sscc;
3291 x_gtin_serial := NULL;
3292
3293 --{{ get serial reference from SSCC }}
3294 l_serial_reference := To_number(Substr(To_char(L_sscc),1,1)||Substr(To_char(l_sscc),l_comp_prefix_dig_len+2,(16-l_comp_prefix_dig_len)));
3295
3296 IF l_debug = 1 THEN
3297
3298 trace('l_serial_reference :'|| l_serial_reference);
3299 END IF;
3300 --{{ get all expected components FOR EPC_SSCC_96 for containers}}
3301 IF p_epc_rule_type = 'EPC_SSCC_96' THEN
3302 --use company-prefix
3303 l_components := mgd_idcomponent_varray(MGD_IDCOMPONENT('HEADER',49,NULL), --for 00110001
3304 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
3305 MGD_IDCOMPONENT('PARTITION',p_partition_value,NULL),
3306 MGD_IDCOMPONENT('COMPANYPREFIX',To_number(p_company_prefix),NULL),
3307 MGD_IDCOMPONENT('SERIALREFERENCE',l_serial_reference,NULL),
3308 MGD_IDCOMPONENT('UNALLOCATED',0,NULL));
3309 ELSIF p_epc_rule_type = 'EPC_SSCC_64' THEN
3310
3311
3312 --{{ get all expected components FOR EPC_SSCC_64 for containers}}
3313 l_components :=
3314 mgd_idcomponent_varray(MGD_IDCOMPONENT('HEADER',8,NULL), --00001000
3315 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
3316 MGD_IDCOMPONENT('COMPANYPREFIXINDEX',To_number(p_comp_prefix_index),NULL),
3317 MGD_IDCOMPONENT('SERIALREFERENCE',l_serial_reference,NULL));
3318
3319
3320 END IF;
3321
3322 ELSE --means l_sscc is NULL
3323 IF l_debug = 1 THEN
3324 trace('Error : Incorrect SSCC value set up for the LPN');
3325 END IF;
3326
3327 RAISE fnd_api.g_exc_error;
3328 END IF;
3329
3330
3331 ELSIF p_epc_rule_type IN ('EPC_DOD_96','EPC_DOD_64') THEN
3332
3333
3334 --This is INVALID option for EPC generation FOR LPN label
3335 IF l_debug = 1 THEN
3336 trace('Error:For LPN label, No EPC can be generated using EPC_DOD_96/64, incorrect SET up');
3337 END IF;
3338 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
3339 FND_MSG_PUB.ADD;
3340 RAISE fnd_api.g_exc_error;
3341 END IF;
3342
3343 ELSIF p_label_type_id =1 THEN /*Material Label*/
3344
3345 l_comp_prefix_dig_len:= Length(p_company_prefix);
3346
3347 IF l_debug = 1 THEN
3348 trace('l_comp_prefix_dig_len :'||l_comp_prefix_dig_len);
3349 END IF;
3350
3351 IF p_epc_rule_type IN ('EPC_SGTIN_96','EPC_SGTIN_64') THEN
3352 -- for item_id and qty + UOM, find any set up in GTIN C/R
3353 -- Generate EPC for that GTIN.
3354
3355 get_item_gtin_serial(p_item_id => p_item_id,
3356 p_org_id => p_org_id,
3357 p_qty => p_txn_qty,
3358 p_uom_code => p_txn_uom,
3359 p_rev => l_rev,
3360 x_gtin => l_gtin,
3361 x_gtin_serial => l_gtin_serial,
3362 x_return_status => l_return_status);
3363
3364
3365
3366 IF l_return_status = fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
3367
3368
3369 --{{ get the item reference from GTIN for the Item now }}
3370
3371 l_item_reference :=
3372 To_number(Substr(To_char(l_gtin),1,1)||Substr(To_char(l_gtin),l_comp_prefix_dig_len+2,12-l_comp_prefix_dig_len));
3373
3374 x_gtin := l_gtin;
3375 x_sscc := NULL;
3376 x_gtin_serial := l_gtin_serial;
3377
3378 IF p_epc_rule_type = 'EPC_SGTIN_96' THEN
3379
3380 --{{ get all expected components FOR EPC_SGTIN_96 for Item}}
3381 --use company-prefix
3382 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',48,NULL), --for 00110000
3383 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
3384 MGD_IDCOMPONENT('PARTITION',p_partition_value,NULL),
3385 MGD_IDCOMPONENT('COMPANYPREFIX',To_number(p_company_prefix),NULL),
3386 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
3387 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
3388
3389 ELSIF p_epc_rule_type = 'EPC_SGTIN_64' THEN
3390
3391 IF l_debug = 1 THEN
3392 trace('p_comp_prefix_index :'||To_number(p_comp_prefix_index));
3393 trace('p_filter_value :'||p_filter_value);
3394 trace('l_item_reference :'||l_item_reference);
3395 trace('l_gtin_serial :'||l_gtin_serial);
3396 END IF;
3397
3398 --{{ get all expected components FOR EPC_SGTIN_64 for Item}}
3399 --use company-prefix -INDEX
3400 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',2,NULL),--10
3401 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
3402 MGD_IDCOMPONENT('COMPANYPREFIXINDEX',To_number(p_comp_prefix_index),NULL),
3403 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
3404 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
3405
3406
3407 END IF;
3408 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF get_item_gtin_serial()
3409 END IF;
3410
3411 ELSIF p_epc_rule_type IN ('EPC_SSCC_96','EPC_SSCC_64') THEN
3412 --This is INVALID option for EPC generation of Material
3413 IF l_debug = 1 THEN
3414 trace('Error:For Material, No EPC can be generated using SSCC, incorrect SET up');
3415 END IF;
3416
3417 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
3418 FND_MSG_PUB.ADD;
3419 RAISE fnd_api.g_exc_error;
3420
3421
3422 ELSIF p_epc_rule_type IN ('EPC_DOD_96','EPC_DOD_64') THEN
3423
3424 IF l_debug = 1 THEN
3425 trace('Error:For Material, No EPC can be generated using EPC_DOD, incorrect SET up');
3426 END IF;
3427 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
3428 FND_MSG_PUB.ADD;
3429 RAISE fnd_api.g_exc_error;
3430
3431
3432 END IF;
3433
3434 ELSIF p_label_type_id = 2 THEN /*Serial Label*/
3435 --Components: FILTER,CAGE, SERIAL_NUMBER
3436
3437 IF p_epc_rule_type IN ('EPC_SGTIN_96','EPC_SGTIN_64') THEN
3438
3439 --Generate EPC for that item for GTIN corresponding to Primary UOM.
3440 --we have p_serial_number + p_item_id + p_cage_code+ p_filter_value
3441
3442 --P_serial_number will be totally ignored while generating EPC. GTIN_Serial
3443 --will be used but it will be stored in wms_EPC table against the
3444 --Item+p_serial_number
3445
3446 -- for item_id and qty + UOM, find any set up in GTIN C/R
3447 -- Generate EPC for that GTIN.
3448
3449 IF l_debug = 1 THEN
3450 trace('going to call get_serialnum_gtin_serial');
3451 END IF;
3452
3453 get_serialnum_gtin_serial(p_item_id => p_item_id,
3454 p_org_id => p_org_id,
3455 p_rev => l_rev,
3456 x_gtin => l_gtin,
3457 x_gtin_serial => l_gtin_serial,
3458 x_return_status => l_return_status);
3459
3460
3461 --{{ get the item reference from GTIN for the Item now }}
3462
3463
3464 IF l_debug = 1 THEN
3465 trace('After call get_serialnum_gtin_serial');
3466 trace('l_return_status :'||l_return_status);
3467 trace('l_gtin :'||l_gtin);
3468 trace('l_gtin_serial :'||l_gtin_serial);
3469 END IF;
3470
3471
3472 IF l_return_status= fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
3473
3474 l_item_reference :=
3475 To_number(Substr(To_char(l_gtin),1,1)||Substr(To_char(l_gtin),l_comp_prefix_dig_len+2,12-l_comp_prefix_dig_len));
3476
3477 x_gtin := l_gtin;
3478 x_sscc := NULL;
3479 x_gtin_serial := l_gtin_serial;
3480
3481
3482 IF p_epc_rule_type = 'EPC_SGTIN_96' THEN
3483
3484 --{{ get all expected components FOR EPC_SGTIN_96 for Item}}
3485 --use company-prefix
3486 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',48,NULL), --for 00110000
3487 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
3488 MGD_IDCOMPONENT('PARTITION',p_partition_value,NULL),
3489 MGD_IDCOMPONENT('COMPANYPREFIX',To_number(p_company_prefix),NULL),
3490 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
3491 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
3492 ELSIF p_epc_rule_type = 'EPC_SGTIN_64' THEN
3493
3494 --{{ get all expected components FOR EPC_SGTIN_64 for Item}}
3495 --use company-prefix -INDEX
3496 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',2,NULL),--10
3497 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
3498 MGD_IDCOMPONENT('COMPANYPREFIXINDEX',To_number(p_comp_prefix_index),NULL),
3499 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
3500 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
3501
3502 END IF;
3503 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF
3504 --get_serialnum_gtin_serial() -> get_gtin_and_gserial()
3505
3506 END IF;
3507
3508 ELSIF p_epc_rule_type IN ('EPC_SSCC_96','EPC_SSCC_64') THEN
3509 --This is INVALID option for EPC generation of Serial NUMBER
3510 IF l_debug = 1 THEN
3511 trace('Error:For Serial, No EPC can be generated using SSCC, incorrect SET up');
3512 END IF;
3513 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
3514 FND_MSG_PUB.ADD;
3515 RAISE fnd_api.g_exc_error;
3516
3517 ELSIF p_epc_rule_type IN ('EPC_DOD_96','EPC_DOD_64') THEN
3518 --{{Note: We MUST have Serial uniqueness across items for EPC generation EPC_DOD_96/EPC_DOD_64}}
3519
3520 x_gtin := NULL;
3521 x_sscc := NULL;
3522 x_gtin_serial := NULL;
3523
3524 IF p_epc_rule_type = 'EPC_DOD_96' THEN
3525 --{{ get all expected components FOR EPC_DOD_96 for container}}
3526
3527 l_components := mgd_idcomponent_varray(MGD_IDCOMPONENT('HEADER',207,NULL),--11001111
3528 MGD_IDCOMPONENT('FILTER',p_filter_value,NULL),
3529 MGD_IDCOMPONENT('GOVERNMENTMANAGEDIDENTIFIER',NULL,p_cage_code),
3530 MGD_IDCOMPONENT('SERIALNUMBER',p_serial_number,NULL));
3531
3532
3533 ELSIF p_epc_rule_type = 'EPC_DOD_64' THEN
3534 --{{ get all expected components FOR EPC_DOD_96 for container }}
3535
3536 IF l_debug = 1 THEN
3537 trace('Inside EPC_DOD_64 to collect components');
3538 END IF;
3539
3540 l_components := mgd_idcomponent_varray(MGD_IDCOMPONENT('HEADER',206,NULL),--11001110
3541 MGD_IDCOMPONENT('FILTER',p_filter_value,NULL),
3542 MGD_IDCOMPONENT('GOVERNMENTMANAGEDIDENTIFIER',NULL,p_cage_code),
3543 MGD_IDCOMPONENT('SERIALNUMBER',p_serial_number,NULL));
3544
3545 END IF;
3546
3547
3548 END IF;
3549
3550 END IF;
3551
3552 x_components := l_components;
3553
3554
3555 EXCEPTION
3556 WHEN fnd_api.g_exc_error THEN
3557 x_return_status := fnd_api.g_ret_sts_error;
3558 x_gtin_serial := NULL;
3559 x_gtin :=NULL;
3560 x_sscc := NULL;
3561 x_components := NULL;
3562 --RAISE; -- Do not raise here
3563
3564 WHEN fnd_api.g_exc_unexpected_error THEN
3565 x_return_status := fnd_api.g_ret_sts_unexp_error;
3566 x_gtin_serial := NULL;
3567 x_gtin :=NULL;
3568 x_sscc := NULL;
3569 x_components := NULL;
3570 --RAISE; -- Do not raise here
3571
3572
3573 WHEN OTHERS THEN
3574 IF l_debug = 1 THEN
3575 trace('Unexpected error inside get_epc_gen_info()');
3576 trace('ERROR CODE = ' || SQLCODE);
3577 trace('ERROR MESSAGE = ' || SQLERRM);
3578 END IF;
3579 x_return_status := fnd_api.g_ret_sts_unexp_error;
3580 x_gtin_serial := NULL;
3581 x_gtin := NULL;
3582 x_sscc := NULL;
3583 x_components := NULL;
3584 --RAISE; -- Do not raise here
3585 END get_epc_gen_info;
3586
3587
3588
3589 ---This API caches all EPC generation Rule type from the core DB EPC
3590 --TABLES AND keeps it in memory for future calls
3591 --FUNCTION applicable to DB versions below RDBMS12c
3592 FUNCTION Cache_and_get_rule(p_partition_val IN NUMBER,
3593 p_type_name IN VARCHAR2,
3594 p_category_id IN NUMBER) RETURN NUMBER AS
3595
3596 CURSOR c_epc_gen_rule_types IS
3597 select type_id, type_name, nvl(partition_value,0) partition_value
3598 ,category_id
3599 from mgd_idencoding_type ;
3600
3601 l_index NUMBER;
3602 l_epc_type_id NUMBER := NULL;
3603 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3604
3605 BEGIN
3606 IF l_debug = 1 THEN
3607 trace('Inside Cache_and_get_rule');
3608 trace('p_partition_val:'||p_partition_val);
3609 trace('p_type_name :'||p_type_name );
3610 trace(' p_category_id :'|| p_category_id);
3611
3612 END IF;
3613
3614 IF g_cached_rule_pkg.COUNT() = 0 THEN --Not cached Yet
3615
3616 IF l_debug = 1 THEN
3617 trace('Caching the rule first time ###########');
3618 END IF;
3619
3620 --Cache the rule
3621 l_index := 1;
3622
3623 FOR l_epc_gen_rule_types IN c_epc_gen_rule_types LOOP
3624
3625 g_cached_rule_pkg(l_index).type_id :=l_epc_gen_rule_types.TYPE_id;
3626 g_cached_rule_pkg(l_index).type_name :=l_epc_gen_rule_types.type_name;
3627 g_cached_rule_pkg(l_index).partition_value :=l_epc_gen_rule_types.partition_value;
3628 g_cached_rule_pkg(l_index).category_id :=l_epc_gen_rule_types.category_id;
3629
3630 IF l_epc_gen_rule_types.type_name = p_type_name
3631 AND l_epc_gen_rule_types.partition_value = p_partition_val
3632 AND l_epc_gen_rule_types.category_id = p_category_id THEN
3633
3634 l_epc_type_id := l_epc_gen_rule_types.type_id;
3635
3636 END IF;
3637
3638 l_index := l_index + 1;
3639 END LOOP;
3640
3641 ELSE --from the cached rule return proper type_id
3642
3643 IF l_debug = 1 THEN
3644 trace('Retrieving EPC rule from the cache ###########');
3645 END IF;
3646
3647 FOR i IN g_cached_rule_pkg.FIRST..g_cached_rule_pkg.LAST
3648 LOOP
3649
3650 IF l_debug = 1 THEN
3651 trace('g_cached_rule_pkg(i).type_name:'||g_cached_rule_pkg(i).type_name);
3652 trace('g_cached_rule_pkg(i).partition_value :'||g_cached_rule_pkg(i).partition_value);
3653 trace('g_cached_rule_pkg(i).category_id :'||g_cached_rule_pkg(i).category_id );
3654 END IF;
3655
3656
3657
3658 IF g_cached_rule_pkg(i).type_name = p_type_name
3659 AND g_cached_rule_pkg(i).partition_value = p_partition_val
3660
3661 /* OR
3662 (p_partition_val IS NULL AND
3663 g_cached_rule_pkg(i).partition_value IS NULL) )*/
3664
3665 AND g_cached_rule_pkg(i).category_id = p_category_id THEN
3666
3667 l_epc_type_id := g_cached_rule_pkg(i).type_id;
3668
3669 EXIT; -- got matching rule, Exit the loop
3670 END IF;
3671 END LOOP;
3672
3673 IF l_debug = 1 THEN
3674 trace('Returned EPC rule type id :'||l_epc_type_id);
3675 END IF;
3676
3677
3678 END IF;
3679
3680
3681 RETURN l_epc_type_id;
3682
3683 EXCEPTION
3684 WHEN OTHERS THEN
3685
3686 IF l_debug = 1 THEN
3687 trace('Exception in Cache_and_get_rule');
3688 trace('ERROR CODE = ' || SQLCODE);
3689 trace('ERROR MESSAGE = ' || SQLERRM);
3690 END IF;
3691
3692
3693 END Cache_and_get_rule;
3694
3695
3696
3697 --*********************************************
3698 /*
3699 Procedure generate_epc
3700
3701 FUNCTIONALITY:-
3702 This is the main API in this package that is responsible for generating
3703 EPC for current transaction
3704
3705 PARAMETER:-
3706 p_org_id : Organization id
3707 p_label_type_id : Supported LPN/Serial/Material labels : VALID VALUES 1,2,3,4,5
3708 p_group_id : groun_id per invocation
3709 p_label_format_id : label_format_id for this transaction that should have has epc field
3710 p_item_id : need TO pass fpr Material Label: 1 only
3711 p_txn_qty : need TO pass fpr Material Label: 1 only
3712 p_txn_uom : need TO pass fpr Material Label: 1 only (since uom is not in wms_label_requests table)
3713 p_label_request_id: the id from wms_label_requests foe which label will be generated
3714 p_business_flow_code : business flow code value
3715 x_epc : returns generated EPC
3716 x_return_status :-
3717 S : success : EPC generated E : error : EPC could not be generated for valid reason
3718 U : Warning : EPC could not be generated for unexpected reason
3719
3720 x_return_mesg : Appropriate error message
3721
3722 */
3723
3724 --*********************************************
3725 --PROCEDURE applicable to DB versions below RDBMS12c
3726 Procedure generate_epc
3727 (p_org_id IN NUMBER,
3728 p_label_type_id IN NUMBER, /* VALID VALUES 1,2,3,4,5*/
3729 p_group_id IN NUMBER,
3730 p_label_format_id IN NUMBER,
3731 p_item_id IN NUMBER DEFAULT NULL, --For Material Label: 1
3732 p_txn_qty IN NUMBER DEFAULT null, --For Material Label: 1
3733 p_txn_uom IN VARCHAR2 DEFAULT NULL, --For Material Label: 1
3734 p_label_request_id IN NUMBER,
3735 p_business_flow_code IN NUMBER DEFAULT NULL,
3736 x_epc OUT nocopy VARCHAR2,
3737 x_return_status OUT nocopy VARCHAR2,
3738 x_return_mesg OUT nocopy VARCHAR2
3739 ) IS
3740
3741 end_processing EXCEPTION;
3742 l_gen_epc VARCHAR2(260);
3743 l_sscc NUMBER;
3744 l_gtin NUMBER;
3745 l_gtin_serial NUMBER;
3746 l_epc VARCHAR2(260);
3747 l_msg_count NUMBER;
3748
3749 l_return_status VARCHAR2(1);
3750 l_regenerate_flag VARCHAR2(1) := 'N';
3751 l_filter_value NUMBER;
3752 l_outermost_lpn_id NUMBER;
3753 l_PARENT_lpn_id NUMBER;
3754 l_return_mesg VARCHAR2(2000);
3755 --l_existing_epc_rule_id NUMBER;
3756 l_epc_output_rep NUMBER;
3757
3758 --New parameters
3759 is_epc_enabled VARCHAR2(1);
3760 l_company_prefix VARCHAR2(30);
3761 l_company_prefix_index VARCHAR2(30);
3762 l_cage_code VARCHAR2(30);
3763 l_custom_company_prefix VARCHAR2(30);
3764 l_epc_rule_type VARCHAR2(100);
3765 l_epc_rule_type_id NUMBER;
3766 l_partition_value NUMBER;
3767 l_cust_comp_prefix_index NUMBER;
3768 l_comp_prefix_len NUMBER;
3769 l_cross_ref_type NUMBER;
3770 l_epc_category_id NUMBER;
3771 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3772 l_lpn_id NUMBER;
3773 l_serial_number VARCHAR2(30);
3774 l_item_id NUMBER;
3775 l_rev VARCHAR2(3);
3776 l_bit_length NUMBER;
3777 l_components mgd_idcomponent_varray := mgd_idcomponent_varray(mgd_idcomponent('HEADER',NULL,null));
3778 pcode mgd_idcode;
3779
3780 BEGIN
3781
3782 G_PROFILE_GTIN := FND_PROFILE.value('INV:GTIN_CROSS_REFERENCE_TYPE');
3783 x_return_status := fnd_api.g_ret_sts_success;
3784
3785 IF l_debug = 1 THEN
3786 trace('************ inside generate_epc ***********');
3787 trace('p_org_id :'||p_org_id);
3788 trace(' p_label_request_id :'||p_label_request_id);
3789 trace('p_business_flow_code :'||p_business_flow_code);
3790 trace('p_group_id :'||p_group_id);
3791 trace('p_label_type_id :'||p_label_type_id);
3792 trace('p_label_format_id :'||p_label_format_id);
3793 trace('p_item_id :'||p_item_id);
3794 trace('p_txn_qty :'||p_txn_qty);
3795 trace('p_txn_uom :'||p_txn_uom);
3796
3797 END IF;
3798
3799
3800 --{{get needed information from mtl_parameters setup for EPC generation }}
3801 IF ( NOT Inv_Cache.set_org_rec( p_organization_id => p_org_id ))THEN
3802 IF (l_debug = 1) THEN
3803 trace(p_org_id || 'is an invalid organization id');
3804 END IF;
3805 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
3806 fnd_msg_pub.ADD;
3807 RAISE fnd_api.g_exc_error;
3808 END IF;
3809
3810 l_company_prefix := inv_cache.org_rec.company_prefix;
3811 l_company_prefix_index := inv_cache.org_rec.company_prefix_index;
3812
3813
3814 IF l_debug = 1 THEN
3815 trace('epc_enabled :'||Nvl(inv_cache.org_rec.epc_generation_enabled_flag,'N'));
3816 trace('l_company_prefix :'||l_company_prefix);
3817 trace('l_company_prefix_index :'||l_company_prefix_index);
3818 trace('cage_code :'||inv_cache.org_rec.commercial_govt_entity_number);
3819 END IF;
3820
3821 --{{get needed information from label formats setup for the format_id}}
3822 SELECT epc_rule_type,filter_value,generate_epc,epc_category_id
3823 INTO l_epc_rule_type,l_filter_value,l_regenerate_flag, l_epc_category_id
3824 FROM wms_label_formats
3825 WHERE label_format_id = p_label_format_id
3826 AND Nvl(label_ENTITY_type,0) =0; --label format and NOT label-set
3827
3828 IF l_debug = 1 THEN
3829 trace('l_epc_rule_type :'||l_epc_rule_type);
3830 trace('l_filter_value :'||l_filter_value);
3831 trace('l_regenerate_flag :'||l_regenerate_flag);
3832 trace('l_epc_category_id :'||l_epc_category_id);
3833 END IF;
3834
3835 --Check if EPC is enabled
3836 IF Nvl(inv_cache.org_rec.epc_generation_enabled_flag,'N') = 'Y' THEN
3837
3838 --Get required information about the current transaction
3839 SELECT lpn_id,serial_number,inventory_item_id,revision
3840 INTO l_lpn_id,l_serial_number,l_item_id, l_rev
3841 FROM wms_label_requests
3842 WHERE label_request_id = p_label_request_id;
3843
3844
3845 IF l_debug = 1 THEN
3846 trace('l_lpn_id,l_serial_number,l_item_id, l_rev :'||l_lpn_id||','||l_serial_number||','||l_item_id||','||l_rev);
3847 END IF;
3848
3849 --Find if the EPC cross-ref already exist or it needs to be re-created
3850 --For LPN
3851
3852 IF l_lpn_id IS NOT NULL AND p_label_type_id IN (3,4,5) THEN /* LPN / LPN-Content / LPN Summary*/
3853
3854 l_cross_ref_type := 1;--/*LPN-EPC Cross ref*/
3855
3856 BEGIN
3857 SELECT wlpn.parent_lpn_id, wlpn.outermost_lpn_id, we.epc
3858 INTO l_parent_lpn_id, l_outermost_lpn_id, l_epc
3859 FROM wms_license_plate_numbers wlpn, wms_epc we
3860 WHERE wlpn.lpn_id = l_lpn_id
3861 AND wlpn.lpn_id = we.lpn_id(+)
3862 AND ((we.epc is NOT NULL and Nvl(we.cross_ref_type,1) = 1)
3863 or (we.epc is NULL )) ;
3864 --Nvl(we.cross_ref_type,1) to support EPC generated
3865 --using 11.5.10.2CU code
3866
3867 -- starting R12 this must be
3868 -- populated AND old data needs to be updated
3869
3870 EXCEPTION
3871 WHEN no_data_found THEN
3872
3873 IF l_debug = 1 THEN
3874 trace('NO DATA found for the LPN');
3875 END IF;
3876
3877 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
3878 fnd_msg_pub.ADD;
3879 RAISE fnd_api.g_exc_unexpected_error;
3880 END;
3881
3882 --Neither pallet or case, then NO support FOR EPC of further
3883 -- nested lpn, error out
3884 IF NOT ((l_outermost_lpn_id = l_lpn_id AND l_parent_lpn_id IS null )
3885 OR (l_parent_lpn_id = l_outermost_lpn_id AND l_parent_lpn_id IS NOT null)) THEN
3886
3887 fnd_message.set_name('WMS', 'WMS_NO_MULTI_NESTING_SUPPORT');
3888 fnd_msg_pub.ADD;
3889 RAISE fnd_api.g_exc_error;
3890
3891 END IF;
3892
3893 --For Serial
3894 ELSIF l_serial_number IS NOT NULL AND p_label_type_id = 2 THEN --/* Serial Label*/
3895
3896 l_cross_ref_type := 2;--/*Serial-EPC Cross ref*/
3897
3898 --Item_id + Serial_number will uniquely mapped to an EPC
3899 BEGIN
3900 SELECT we.epc INTO l_epc
3901 FROM wms_epc we
3902 WHERE INVENTORY_item_id = l_item_id
3903 AND serial_number = l_serial_number
3904 AND we.cross_ref_type = 2;
3905 EXCEPTION
3906 WHEN no_data_found THEN
3907 NULL;
3908 WHEN OTHERS THEN
3909 RAISE fnd_api.g_exc_unexpected_error;
3910 END;
3911
3912 --For item_id/GTIN
3913 ELSIF l_item_id IS NOT NULL AND p_label_type_id = 1 THEN --/*Material Label*/
3914
3915 l_cross_ref_type := 3; --/*GTIN+GTIN_SERIAL-EPC Cross ref*/
3916
3917 --No need to check whether EPC exists or NOT for the GTIN
3918 --just regenerate EPC, EPC will be unique to GTIN+GTIN-SERIAL combination
3919 --and it needs to be inserted
3920
3921 l_regenerate_flag := 'Y'; -- override always
3922 END IF;
3923
3924
3925 --{{call to see if the custom company_prefix is implemented}}
3926 wms_epc_pub.GET_CUSTOM_COMPANY_PREFIX(
3927 p_org_id => p_org_id,
3928 p_label_request_id => p_label_request_id,
3929 X_company_prefix => l_custom_company_prefix,
3930 X_RETURN_STATUS => l_return_status);
3931
3932 --{{call to see if the custom company_prefix_INDEX is implemented}}
3933 wms_epc_pub.GET_CUSTOM_COMP_PREFIX_INDEX(p_org_id => p_org_id,
3934 p_label_request_id => p_label_request_id,
3935 X_comp_prefix_INDEX => l_CUST_comp_prefix_INDEX,
3936 X_RETURN_STATUS => l_return_status);
3937
3938 IF l_debug = 1 THEN
3939 trace('CUSTOM_COMPANY_PREFIX value :'||l_custom_company_prefix);
3940 trace('CUSTOM_COMPANY_PREFIX_INDEX value :'||l_cust_comp_prefix_index);
3941 END IF;
3942
3943 IF l_custom_company_prefix IS NOT NULL THEN
3944 l_company_prefix := l_custom_company_prefix;
3945 END IF;
3946
3947 IF l_cust_comp_prefix_index IS NOT NULL THEN
3948 l_company_prefix_index := l_cust_comp_prefix_index;
3949 END IF;
3950
3951 IF l_debug = 1 THEN
3952 trace('Final COMPANY_PREFIX value :'||l_company_prefix);
3953 trace('Final COMPANY_PREFIX_INDEX value :'||l_company_prefix_index );
3954 END IF;
3955
3956
3957 l_partition_value :=get_PARTITION_value(l_epc_rule_type, l_company_prefix);
3958
3959 IF l_debug = 1 THEN
3960 trace('l_partition_value :'||l_partition_value);
3961 END IF;
3962
3963
3964
3965 --{{ see if partition value returned is correct and get the l_epc_rule_type_id now}}
3966
3967 IF l_partition_value = -1 THEN --prefix length is INCORRECT
3968 fnd_message.set_name('WMS','WMS_INCORRECT_PREFIX_LEN');
3969 fnd_msg_pub.ADD;
3970 RAISE fnd_api.g_exc_error;
3971
3972 ELSE
3973 --To avoid DB table call for each read:
3974 --Cache Entire rule in the memory if first time call
3975 --otherwise get epc generation rule type for current txn
3976
3977 IF l_debug = 1 THEN
3978 trace('GOING TO CALL Cache_and_get_rule');
3979 END IF;
3980
3981
3982 l_epc_rule_type_id := Cache_and_get_rule(l_partition_value, l_epc_rule_type,l_epc_category_id);
3983
3984 --NULL value OF PARTITION will be treated AS 0
3985
3986 END IF;
3987
3988 IF l_epc_rule_type_id IS NULL THEN
3989 x_epc := NULL;
3990 fnd_message.set_name('WMS','WMS_NO_EPC_RULE_FOUND');--No rule found
3991 fnd_msg_pub.ADD;
3992 RAISE fnd_api.g_exc_error;
3993 END IF;
3994
3995
3996 IF l_debug = 1 THEN
3997 trace('Generating EPC now.............');
3998 END IF;
3999
4000 --{{ See if the rule is custom or Un-Implemented Standard Rule }}
4001 IF l_epc_category_id <> mgd_idcode_utl.epc_encoding_category_id
4002 OR ( l_epc_category_id = mgd_idcode_utl.epc_encoding_category_id AND
4003 l_epc_rule_type NOT IN ('EPC_SGTIN_96','EPC_SSCC_96','EPC_SGTIN_64','EPC_SSCC_64','EPC_DOD_96','EPC_DOD_64') )
4004 --means in ('EPC_GIAI_96','EPC_GRAI_96','EPC_SGLN_96','EPC_GID_96','EPC_GIAI_64','EPC_GRAI_64','EPC_SGLN_64','EPC_GID_64')
4005 THEN
4006
4007 -- {{ get custom EPC generated }}
4008 wms_epc_pub.get_custom_epc
4009 (p_org_id => p_org_id,
4010 p_category_id => l_epc_category_id,-- In mgd_idencoding_type table
4011 p_epc_rule_type_id => l_epc_rule_type_id, --Rule_type_id IN mgd_idencoding_type table
4012 p_filter_value => l_filter_value,
4013 p_label_request_id => p_label_request_id, --to get all data FROM wms_device_requests label
4014 x_return_status => l_return_status,
4015 x_return_mesg => l_return_mesg,
4016 x_epc => l_epc);
4017
4018 ELSIF l_epc_rule_type IN
4019 ('EPC_SGTIN_96','EPC_SSCC_96','EPC_SGTIN_64','EPC_SSCC_64','EPC_DOD_96','EPC_DOD_64') THEN
4020 --{{ get standard EPC generated for standard rule}}
4021
4022 --{{ Check to see if regenerate flag is ON..only then generate epc }}
4023 IF l_regenerate_flag = 'Y' OR (l_epc is NULL AND
4024 Nvl(l_regenerate_flag,'N') = 'N' ) THEN
4025
4026 --{{ get EPC Encoding Rule components }}
4027 IF l_debug = 1 THEN
4028 trace('Collecting information to generate EPC ....');
4029 END IF;
4030
4031 get_epc_gen_info( p_org_id => p_org_id,
4032 p_lpn_id => l_lpn_id, --FOR p_label_type_id = 3,4,5
4033 p_serial_number => l_serial_number, --FOR p_label_type_id = 2
4034 p_item_id => Nvl(l_item_id,p_item_id), --FOR p_label_type_id = 1
4035 p_txn_qty => p_txn_qty, --FOR p_label_type_id = 1
4036 p_txn_uom => p_txn_uom, --FOR p_label_type_id = 1
4037 p_rev => l_rev, --FOR p_label_type_id = 1,2
4038 p_company_prefix => l_company_prefix,
4039 p_comp_prefix_index => l_company_prefix_index,
4040 p_business_flow_code => p_business_flow_code,
4041 p_label_type_id => P_label_type_id,
4042 p_epc_rule_type => l_epc_rule_type,
4043 p_filter_value => l_filter_value,
4044 p_cage_code => inv_cache.org_rec.commercial_govt_entity_number, --FOR p_label_type_id = 2
4045 p_partition_value => l_partition_value,
4046 x_gtin => l_gtin,
4047 x_sscc => l_sscc,
4048 x_gtin_serial => l_gtin_seriaL,
4049 x_components => l_components,
4050 x_return_status => l_return_status);
4051
4052 IF l_debug = 1 THEN
4053 trace('after calling get_epc_gen_info... ');
4054 trace('l_gtin :'||l_gtin );
4055 trace('l_sscc :'||l_sscc);
4056 trace('l_gtin_serial :'||l_gtin_serial);
4057 trace('l_return_status :'||l_return_status);
4058 END IF;
4059
4060
4061 --{{ genereate EPC using the components }}
4062
4063 IF l_return_status = fnd_api.g_ret_sts_success AND l_components IS NOT NULL THEN
4064
4065
4066 -- This is needed to pass correct value (NULL Vs 0)in the DB API
4067 --In some case NULL and anohter 0 is needed
4068 IF (l_epc_rule_type IN
4069 ('EPC_DOD_96','EPC_DOD_64','EPC_SGTIN_64','EPC_SSCC_64','EPC_SGLN_64','EPC_GRAI_64','EPC_GIAI_64','EPC_GID_96')) AND (l_partition_value = 0) THEN
4070 l_partition_value := NULL;
4071
4072 END IF;
4073
4074
4075 IF l_debug = 1 THEN
4076 trace('Before calling DB EPC category_name :'||mgd_idcode_utl.EPC_ENCODING_CATEGORY_NAME);
4077 trace('partition_val :'||l_partition_value);
4078 END IF;
4079
4080 BEGIN
4081 pcode := mgd_idcode( category_name => mgd_idcode_utl.EPC_ENCODING_CATEGORY_NAME,
4082 category_agency => NULL,
4083 category_version => NULL,
4084 category_uri => NULL,
4085 encoding_type_name => l_epc_rule_type, --mgd_idcode_utl.EPC_SGTIN_96,
4086 partition_val => l_partition_value,
4087 components => l_components);
4088
4089 l_gen_epc := pcode.bit_encoding; --EPC in HEXA system
4090
4091 IF l_debug = 1 THEN
4092 trace('EPC generated by DB Feature :'||l_gen_epc);
4093 END IF;
4094
4095 EXCEPTION
4096 WHEN OTHERS THEN
4097
4098 IF l_debug = 1 THEN
4099 TRACE('After calling mgd_idcode: Inside exception');
4100 TRACE('ERROR CODE = ' || SQLCODE);
4101 TRACE('ERROR MESSAGE = ' || SQLERRM);
4102 END IF;
4103
4104 --EPC generation failed at DB feature level
4105 fnd_message.set_name('WMS', 'WMS_DB_EPC_GEN_FAIL');
4106 fnd_msg_pub.ADD;
4107
4108 --Do not raise exception here as we want to delete
4109 --old cross-reference RECORD FROM wms_epc for some
4110 --CASES BELOW
4111 END;
4112
4113 ELSE
4114 -- {{Error out l_components are null, EPC could not be generated }}
4115
4116 IF l_debug = 1 THEN
4117 trace('get_epc_gen_info() returned error');
4118 trace('Error: Components could not be obtained for EPC generation ');
4119 END IF;
4120
4121
4122 fnd_message.set_name('WMS', 'WMS_EPC_GEN_FAIL');
4123 fnd_msg_pub.ADD;
4124
4125 --Do not raise exception here as we want to delete
4126 --old cross-reference RECORD FROM wms_epc for some
4127 --CASES BELOW
4128
4129 END IF; --l_return_status = 'S for get_epc_gen_info()
4130
4131
4132 END IF; --l_regenerate_flag = 'Y'
4133
4134 END IF; --means 'EPC_SGTIN_96','EPC_SSCC_96','EPC_SGTIN_64','EPC_SSCC_64',EPC_DOD_96,EPC_DOD_64''EPC_SGTIN_96','EPC_SSCC_96'
4135
4136 ELSE
4137 IF l_debug = 1 THEN
4138 trace('EPC generation is NOT enabled at Orgnization level');
4139 END IF;
4140
4141 fnd_message.set_name('WMS', 'WMS_EPC_DISABLED');
4142 fnd_msg_pub.ADD;
4143 RAISE fnd_api.g_exc_error;
4144
4145 END IF;
4146
4147
4148 --{{ By NOW EPC should be generate for valid cases: Insert EPC or update EPC or delete EPC }}
4149
4150 IF l_debug = 1 THEN
4151 trace('Old EPC,if any :'||l_epc);
4152 trace('New generated EPC:'||l_gen_epc);
4153 END IF;
4154
4155
4156
4157 --{{ Get the EPC ENCODING defined with the profile 'WMS_EPC_ENCODING'}}
4158 l_epc_output_rep := NVL(fnd_profile.value('WMS_EPC_ENCODING'), 2);
4159 -- 1 : Binary
4160 -- 2 : Hex
4161 -- 3 : Decimal
4162
4163 IF l_debug = 1 THEN
4164 trace('1-Binary,2-Hex,3-Decimal l_epc_output_rep :'||l_epc_output_rep);
4165 END IF;
4166
4167 --l_epc_rule_type_id is already identified above
4168 IF l_regenerate_flag = 'Y' OR (l_epc is NULL AND
4169 Nvl(l_regenerate_flag,'N') = 'N' ) THEN
4170
4171
4172 IF l_epc IS NOT NULL AND l_gen_epc IS NOT NULL THEN
4173 --{{ EPC c/r already there: UPDATE LAST EPC with new EPC value }}
4174 uptodate_wms_epc ( p_action => 'UPDATE',
4175 p_group_id => p_group_id,
4176 p_cross_ref_type => l_cross_ref_type,
4177 p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
4178 p_lpn_id => l_lpn_id,
4179 p_item_id => l_item_id,
4180 p_serial_number => l_serial_number,
4181 p_gen_epc => l_gen_epc,
4182 p_sscc => l_sscc,
4183 p_gtin => l_gtin,
4184 p_gtin_serial => l_gtin_serial,
4185 p_filter_VALUE => l_filter_value,
4186 x_return_status => L_RETURN_STATUS);
4187
4188
4189 IF l_debug =1 then
4190 trace(' uptodate_wms_epc UPDATE: L_RETURN_STATUS:'||l_return_status);
4191 END IF;
4192
4193
4194 --return new EPC IN THE FORMAT SPECIFIED
4195 IF L_epc_output_rep = 1 THEN --Binary
4196 l_bit_length := To_number(Substr(l_epc_rule_type,(Length(l_epc_rule_type)-1),Length(l_epc_rule_type)));
4197 x_epc := Lpad(dec2bin(hex2dec(l_gen_epc)),l_bit_length,'0');
4198
4199 ELSIF l_epc_output_rep = 3 THEN --Decimal
4200 x_epc := hex2dec(l_gen_epc);
4201 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
4202 x_epc := l_gen_epc;
4203 END IF;
4204
4205
4206 ELSIF l_epc IS NOT NULL AND l_gen_epc IS NULL THEN
4207 -- Delete the existing cross -reference
4208
4209 uptodate_wms_epc ( p_action => 'DELETE',
4210 p_group_id => p_group_id,
4211 p_cross_ref_type => l_cross_ref_type,
4212 p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
4213 p_lpn_id => l_lpn_id,
4214 p_item_id => l_item_id,
4215 p_serial_number => l_serial_number,
4216 p_gen_epc => l_gen_epc,
4217 p_sscc => l_sscc,
4218 p_gtin => l_gtin,
4219 p_gtin_serial => l_gtin_serial,
4220 p_filter_VALUE => l_filter_value,
4221 x_return_status => L_RETURN_STATUS);
4222
4223 IF l_debug =1 then
4224 trace(' uptodate_wms_epc DELETE: L_RETURN_STATUS:'||l_return_status);
4225 END IF;
4226
4227 RAISE fnd_api.g_exc_error;--COULD NOT OVERIDE THE EPC
4228
4229
4230 ELSIF l_epc IS NULL AND l_gen_epc IS NOT NULL THEN
4231
4232 uptodate_wms_epc( p_action => 'INSERT',
4233 p_group_id => p_group_id,
4234 p_cross_ref_type => l_cross_ref_type,
4235 p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
4236 p_lpn_id => l_lpn_id,
4237 p_item_id => l_item_id,
4238 p_serial_number => l_serial_number,
4239 p_gen_epc => l_gen_epc,
4240 p_sscc => l_sscc,
4241 p_gtin => l_gtin,
4242 p_gtin_serial => l_gtin_serial,
4243 p_filter_VALUE => l_filter_value,
4244 x_return_status => L_RETURN_STATUS);
4245
4246 IF l_debug =1 then
4247 trace(' uptodate_wms_epc INSERT: L_RETURN_STATUS:'||l_return_status);
4248 END IF;
4249
4250 --return new EPC IN THE FORMAT SPECIFIED
4251 IF l_epc_output_rep = 1 THEN
4252 l_bit_length := To_number(Substr(l_epc_rule_type,(Length(l_epc_rule_type)-1),Length(l_epc_rule_type)));
4253 x_epc := Lpad(dec2bin(hex2dec(l_gen_epc)),l_bit_length,'0');
4254 ELSIF l_epc_output_rep = 3 THEN
4255 x_epc := hex2dec(l_gen_epc);
4256 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
4257 x_epc := l_gen_epc;
4258 END IF;
4259
4260
4261 ELSIF l_epc IS NULL AND l_gen_epc IS NULL THEN
4262
4263 RAISE fnd_api.g_exc_error;
4264
4265 END IF;
4266
4267 ELSIF Nvl(l_regenerate_flag,'N') = 'N' THEN
4268
4269 IF l_epc IS NOT NULL THEN
4270
4271 --Return Old EPC,Already it was stored in Hex
4272 --return new EPC IN THE FORMAT SPECIFIED
4273 IF l_epc_output_rep = 1 THEN
4274 l_bit_length := To_number(Substr(l_epc_rule_type,(Length(l_epc_rule_type)-1),Length(l_epc_rule_type)));
4275 x_epc := Lpad(dec2bin(hex2dec(l_gen_epc)),l_bit_length,'0');
4276 ELSIF l_epc_output_rep = 3 THEN
4277 x_epc := hex2dec(l_epc);
4278 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
4279 x_epc := l_epc;
4280 END IF;
4281
4282
4283 END IF; -- L_EPC IS NOT NULL
4284
4285 END IF; -- For p_regenerate_flag = 'N'
4286
4287
4288
4289 EXCEPTION
4290
4291 WHEN fnd_api.g_exc_error THEN
4292 fnd_message.set_name('WMS','WMS_EPC_GEN_FAIL');
4293 fnd_msg_pub.ADD;
4294 x_return_status := fnd_api.g_ret_sts_error;
4295 x_epc := NULL;
4296 fnd_msg_pub.count_and_get(p_encoded => 'F',p_count => l_msg_count, p_data => x_return_mesg);
4297
4298 FOR i IN 1..l_msg_count LOOP
4299 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
4300 END LOOP;
4301
4302 IF l_debug = 1 THEN
4303 TRACE('Inside g_exc_error l_msg_count :'||l_msg_count);
4304 TRACE('x_return_mesg :'||x_return_mesg);
4305 END IF;
4306
4307 -- FND_MSG_PUB.initialize; --bug 5178424
4308
4309
4310 WHEN fnd_api.g_exc_unexpected_error THEN
4311 fnd_message.set_name('WMS', 'WMS_EPC_GEN_FAIL');
4312 fnd_msg_pub.ADD;
4313 x_return_status := fnd_api.g_ret_sts_unexp_error;
4314 x_epc := NULL;
4315 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => x_return_mesg);
4316
4317 FOR i IN 1..l_msg_count LOOP
4318 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
4319 END LOOP;
4320
4321 IF l_debug = 1 THEN
4322 TRACE('Inside g_exc_unexpected_error l_msg_count :'||l_msg_count);
4323 TRACE('x_return_mesg :'||x_return_mesg);
4324 END IF;
4325
4326 -- FND_MSG_PUB.initialize; --bug 5178424
4327
4328 WHEN OTHERS THEN
4329 --ROLLBACK; blocked in R12
4330 x_return_status := fnd_api.g_ret_sts_unexp_error;
4331 x_epc := NULL;
4332 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => x_return_mesg);
4333
4334 FOR i IN 1..l_msg_count LOOP
4335 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
4336 END LOOP;
4337
4338 -- FND_MSG_PUB.initialize; --bug 5178424
4339
4340 IF l_debug = 1 THEN
4341 TRACE('generate EPC: Inside exception');
4342 TRACE('ERROR CODE = ' || SQLCODE);
4343 TRACE('ERROR MESSAGE = ' || SQLERRM);
4344 END IF;
4345
4346 END generate_epc;
4347
4348 --FUNCTION applicable to DB versions below RDBMS12c
4349 FUNCTION db_version RETURN NUMBER IS
4350 BEGIN
4351 trace('Call from forms for DB version, returning 10 as DB is 11gR2 or lower so that old model is used');
4352 RETURN 10;
4353 END db_version;
4354
4355 $END
4356
4357 /* NOT USED. Coded in R12 but not used
4358 FUNCTION is_epc_enabled(p_org_id IN NUMBER) RETURN VARCHAR2 IS
4359
4360 l_is_epc_enabled NUMBER :=0;
4361
4362 BEGIN
4363
4364 IF g_epc_org_id.count > 0 then
4365
4366 FOR i IN g_epc_org_id.FIRST..g_epc_org_id.last LOOP
4367 IF g_epc_org_id(i) = p_org_id then
4368 RETURN 'Y';
4369 END IF;
4370 END LOOP;
4371 END IF;
4372
4373 BEGIN
4374 SELECT 1
4375 INTO l_is_epc_enabled
4376 FROM mtl_parameters
4377 WHERE organization_id = p_org_id
4378 AND Nvl(epc_generation_enabled_flag, 'N') = 'Y';
4379 EXCEPTION
4380 WHEN no_data_found then
4381 l_is_epc_enabled :=0;
4382 WHEN others THEN
4383 l_is_epc_enabled :=0;
4384 END;
4385
4386 IF l_is_epc_enabled = 1 THEN
4387 g_epc_org_id(nvl(g_epc_org_id.count,0) + 1 ) := p_org_id;
4388 RETURN 'Y';
4389 ELSE
4390 RETURN 'N';
4391 END IF;
4392
4393
4394 END is_epc_enabled;
4395 */
4396
4397
4398 END wms_epc_pvt;
[Home] [Help]