1 PACKAGE BODY WMS_EPC_PVT AS
2 /* $Header: WMSEPCVB.pls 120.22 2006/07/06 23:27:12 satkumar noship $ */
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 procedure Insert / Upadate / Delete in WMS_EPC table with data
1328 --passed based ON action specified
1329
1330 PROCEDURE uptodate_wms_epc
1331 (p_action IN VARCHAR2,
1332 p_group_id IN NUMBER,
1333 p_cross_ref_type IN NUMBER,
1334 p_EPC_rule_TYPE_id IN NUMBER,
1335 p_lpn_id IN NUMBER,
1336 p_item_id IN NUMBER,
1337 p_serial_number IN NUMBER,
1338 p_gen_epc IN VARCHAR2,
1339 p_sscc IN NUMBER,
1340 p_gtin IN NUMBER, --calling API makes it number
1341 p_gtin_serial IN NUMBER,
1342 p_filter_VALUE IN NUMBER,
1343 x_return_status OUT nocopy VARCHAR2
1344 ) IS
1345
1346 PRAGMA AUTONOMOUS_TRANSACTION;
1347
1348 l_epc_id NUMBER;
1349 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1350 BEGIN
1351
1352 x_return_status := fnd_api.g_ret_sts_success;
1353
1354 IF l_debug = 1 THEN
1355 trace('************UPtoDATE WMS_EPC***');
1356 trace('p_action :'|| p_action );
1357 trace('p_group_id :'|| p_group_id);
1358 trace('p_cross_ref_type :'|| p_cross_ref_type);
1359 trace('p_epc_rule_type_id :'|| p_EPC_rule_TYPE_id);
1360 trace('p_lpn_id :'|| p_lpn_id);
1361 trace('p_item_id :'|| p_item_id);
1362 trace('p_serial_number :'|| p_serial_number);
1363 trace('p_gen_epc :'|| p_gen_epc);
1364 trace('p_sscc :'|| p_sscc);
1365 trace('p_gtin :'|| p_gtin);
1366 END IF;
1367
1368
1369 IF p_cross_ref_type = 1 THEN --LPN-EPC /* LPN related label*/
1370
1371 IF p_action = 'UPDATE' THEN
1372
1373 UPDATE wms_epc
1374 SET epc = p_gen_epc,
1375 cross_ref_type = p_cross_ref_type,
1376 group_id = p_group_id,
1377 last_update_date = Sysdate,
1378 last_updated_by = fnd_global.user_id,
1379 epc_rule_type_id = p_epc_rule_type_id,
1380 sscc = P_sscc,
1381 gtin = P_gtin,
1382 gtin_serial = NULL,
1383 inventory_item_id = NULL,
1384 serial_number = NULL,
1385 filter_object_type = p_filter_value,
1386 status = 'LABEL_PRINTED',
1387 status_code = 'S'
1388 WHERE lpn_id = p_lpn_id;
1389
1390 ELSIF p_action = 'INSERT' THEN
1391
1392 --INSERT NEW EPC RECORD
1393
1394 INSERT INTO wms_epc( group_id,
1395 cross_ref_type,
1396 epc_rule_type_id,
1397 lpn_id,
1398 serial_number,
1399 inventory_item_id,
1400 gtin_serial,
1401 gtin,
1402 sscc,
1403 epc,
1404 filter_object_type,
1405 status_code,
1406 status,
1407 creation_date,
1408 created_by,
1409 last_update_date,
1410 last_updated_by,
1411 last_update_login,
1412 epc_id,
1413 epc_rule_id
1414 ) VALUES (P_group_id,
1415 p_cross_ref_type,
1416 P_epc_rule_type_id,
1417 p_lpn_id,
1418 NULL,--p_serial_number,
1419 NULL,--p_ITEM_ID,
1420 NULL,--p_GTIN_SERIAL,
1421 P_gtin,
1422 P_sscc,
1423 P_gen_epc,
1424 p_filter_value,
1425 'S',
1426 'LABEL_PRINTED',
1427 Sysdate,
1428 fnd_global.user_id,
1429 Sysdate,
1430 fnd_global.user_id,
1431 fnd_global.user_id,
1432 NULL, --epc_id NOT used post R12
1433 NULL); --epc_rule_id NOT used post R12
1434
1435 ELSIF p_action = 'DELETE' THEN
1436
1437 -- Delete the existing cross -reference
1438 DELETE FROM wms_epc WHERE lpn_id = p_lpn_id;
1439
1440 END IF;
1441
1442
1443 ELSIF p_cross_ref_type = 2 THEN -- Item_Serial - EPC /* Serial Label */
1444
1445 IF p_action = 'UPDATE' THEN
1446
1447 UPDATE wms_epc
1448 SET epc = p_gen_epc,
1449 cross_ref_type = p_cross_ref_type,
1450 group_id = p_group_id,
1451 last_update_date = Sysdate,
1452 last_updated_by = fnd_global.user_id,
1453 epc_rule_type_id = p_epc_rule_type_id,
1454 sscc = NULL,-- No other value possible
1455 gtin = P_gtin,
1456 gtin_serial = NULL,
1457 lpn_id = NULL,
1458 filter_object_type = p_filter_value,
1459 status = 'LABEL_PRINTED',
1460 status_code = 'S'
1461 WHERE inventory_item_id = p_item_id
1462 AND serial_number = p_serial_number;
1463
1464 ELSIF p_action = 'INSERT' THEN
1465
1466 --INSERT NEW EPC RECORD
1467
1468 INSERT INTO wms_epc( group_id,
1469 cross_ref_type,
1470 epc_rule_type_id,
1471 lpn_id,
1472 serial_number,
1473 inventory_item_id,
1474 gtin_serial,
1475 gtin,
1476 sscc,
1477 epc,
1478 filter_object_type,
1479 status_code,
1480 status,
1481 creation_date,
1482 created_by,
1483 last_update_date,
1484 last_updated_by,
1485 last_update_login,
1486 epc_id,
1487 epc_rule_id
1488 ) VALUES (P_group_id,
1489 p_cross_ref_type,
1490 P_epc_rule_type_id,
1491 NULL,-- lpn_id
1492 p_serial_number,
1493 p_item_id,
1494 NULL,--p_gtin_serial,
1495 P_gtin,
1496 NULL,--p_sscc
1497 P_gen_epc,
1498 p_filter_value,
1499 'S',
1500 'LABEL_PRINTED',
1501 Sysdate,
1502 fnd_global.user_id,
1503 Sysdate,
1504 fnd_global.user_id,
1505 fnd_global.user_id,
1506 NULL, --epc_id NOT used post R12
1507 NULL); --epc_rule_id NOT used post R12
1508
1509 ELSIF p_action = 'DELETE' THEN
1510
1511 -- Delete the existing cross -reference
1512 DELETE FROM wms_epc
1513 WHERE inventory_item_id = p_item_id
1514 AND serial_number = p_serial_number;
1515
1516
1517 END IF;
1518
1519 ELSIF p_cross_ref_type = 3 THEN --GTIN+GTIN_Serial - EPC /* Material Label */
1520
1521 IF p_action = 'UPDATE' THEN
1522
1523 UPDATE wms_epc
1524 SET epc = p_gen_epc,
1525 cross_ref_type = p_cross_ref_type,
1526 group_id = p_group_id,
1527 last_update_date = Sysdate,
1528 last_updated_by = fnd_global.user_id,
1529 epc_rule_type_id = p_epc_rule_type_id,
1530 sscc = NULL, --NO other value possible in this case
1531 serial_number = NULL,
1532 inventory_item_id = NULL,
1533 lpn_id = NULL,
1534 filter_object_type = p_filter_value,
1535 status = 'LABEL_PRINTED',
1536 status_code = 'S'
1537 WHERE GTIN = p_gtin
1538 AND GTIN_serial = p_gtin_serial;
1539
1540 ELSIF p_action = 'INSERT' THEN
1541
1542 --INSERT NEW EPC RECORD
1543
1544 INSERT INTO wms_epc( group_id,
1545 cross_ref_type,
1546 epc_rule_type_id,
1547 lpn_id,
1548 serial_number,
1549 inventory_item_id,
1550 gtin_serial,
1551 gtin,
1552 sscc,
1553 epc,
1554 filter_object_type,
1555 status_code,
1556 status,
1557 creation_date,
1558 created_by,
1559 last_update_date,
1560 last_updated_by,
1561 last_update_login,
1562 epc_id,
1563 epc_rule_id
1564 ) VALUES (P_group_id,
1565 p_cross_ref_type,
1566 P_epc_rule_type_id,
1567 NULL ,-- p_lpn_id
1568 NULL, --p_serial_number
1569 NULL, --p_item_id,
1570 p_GTIN_serial,
1571 P_gtin,
1572 NULL, --p_sscc,
1573 P_gen_epc,
1574 p_filter_value,
1575 'S',
1576 'LABEL_PRINTED',
1577 Sysdate,
1578 fnd_global.user_id,
1579 Sysdate,
1580 fnd_global.user_id,
1581 fnd_global.user_id,
1582 NULL, --epc_id NOT used post R12
1583 NULL); --epc_rule_id NOT used post R12
1584
1585 ELSIF p_action = 'DELETE' THEN
1586
1587 -- Delete the existing cross -reference
1588 DELETE FROM wms_epc
1589 WHERE GTIN = p_gtin
1590 AND GTIN_serial = p_gtin_serial;
1591
1592 END IF;
1593
1594
1595 END IF;
1596
1597
1598 --COMMIT THE autonomous txn part of updating record in WMS_EPC
1599 COMMIT;
1600
1601 EXCEPTION
1602 WHEN OTHERS THEN
1603 x_return_status := fnd_api.g_ret_sts_error;
1604 IF l_debug = 1 THEN
1605 TRACE('UPTODATE WMS_EPC: inside exception');
1606 TRACE('ERROR CODE = ' || SQLCODE);
1607 TRACE('ERROR MESSAGE = ' || SQLERRM);
1608 END IF;
1609
1610 END uptodate_wms_epc;
1611
1612 --Given EPC_rule_type and company_prefix, calculates the pre-defined
1613 --PARTITION value (specified by EPC global standard)
1614 FUNCTION get_PARTITION_value(p_epc_rule_type IN VARCHAR2,
1615 P_company_prefix IN VARCHAR2) RETURN NUMBER
1616 IS
1617 l_partition NUMBER;
1618 l_comp_pref_len NUMBER;
1619 BEGIN
1620
1621 /*
1622 following TYPE OF epc TYPE required PARTITION length
1623 epc_sgtin_96
1624 epc_sscc_96
1625 epc_giai_96
1626 epc_grai_96
1627 epc_sgln_96
1628
1629 DoD-96/64 - does NOT need partition value
1630 */
1631
1632
1633 --GET THE decimal length of company-prefix
1634 l_comp_pref_len := Length(P_company_prefix);
1635
1636
1637 IF p_epc_rule_type = 'EPC_SGTIN_96' OR p_epc_rule_type = 'EPC_SSCC_96'
1638 OR p_epc_rule_type ='EPC_SGLN_96' OR p_epc_rule_type = 'EPC_GIAI_96'
1639 OR p_epc_rule_type = 'EPC_GRAI_96' THEN
1640
1641 IF l_comp_pref_len = 12 THEN l_partition := 0;
1642 ELSIF l_comp_pref_len =11 THEN l_partition := 1;
1643 ELSIF l_comp_pref_len =10 THEN l_partition := 2;
1644 ELSIF l_comp_pref_len =9 THEN l_partition := 3;
1645 ELSIF l_comp_pref_len =8 THEN l_partition := 4;
1646 ELSIF l_comp_pref_len =7 THEN l_partition := 5;
1647 ELSIF l_comp_pref_len =6 THEN l_partition := 6;
1648 ELSE l_partition := -1; --Error condition
1649 END IF;
1650
1651 ELSE
1652
1653 l_partition := 0; --partition is not needed for this STANDARD EPC TYPE generation
1654
1655 END IF;
1656
1657 RETURN l_partition;
1658
1659 END get_PARTITION_value;
1660
1661
1662 --Collects all informtion needed to generate EPC for given object
1663 -- For a given EPC generation type the output parameter x_components
1664 -- contains required elements that will be passed to DB- EPC generation API
1665 -- to generate EPC
1666
1667 PROCEDURE get_epc_gen_info( p_org_id IN NUMBER,
1668 p_lpn_id IN NUMBER, --FOR p_label_type_id = 3,4,5
1669 p_serial_number IN VARCHAR2, --FOR p_label_type_id = 2
1670 p_item_id IN NUMBER, --FOR p_label_type_id = 1,2
1671 p_txn_qty IN NUMBER, --FOR p_label_type_id = 1
1672 p_txn_uom IN VARCHAR2, --FOR p_label_type_id = 1
1673 p_rev IN VARCHAR2, --FOR p_label_type_id = 1,2
1674 p_company_prefix IN VARCHAR2,
1675 p_comp_prefix_index IN VARCHAR2,
1676 p_business_flow_code IN NUMBER,
1677 p_label_type_id IN NUMBER,
1678 p_epc_rule_type IN VARCHAR2,
1679 p_filter_value IN NUMBER,
1680 p_cage_code IN VARCHAR2, --FOR p_label_type_id = 2
1681 p_partition_value IN NUMBER,
1682 x_gtin OUT nocopy NUMBER,
1683 x_sscc OUT nocopy NUMBER,
1684 x_gtin_serial OUT nocopy NUMBER,
1685 x_components OUT nocopy mgd_idcomponent_varray,
1686 x_return_status OUT nocopy VARCHAR2)
1687 IS
1688
1689 l_components MGD_IDCOMPONENT_VARRAY;
1690
1691 l_sscc_len NUMBER;
1692 l_gtin_len NUMBER;
1693 l_lpn_num_format NUMBER;
1694 l_is_sscc_valid BOOLEAN := TRUE;
1695 l_is_gtin_valid BOOLEAN := TRUE;
1696 l_item_id NUMBER;
1697 l_total_qty NUMBER :=0;
1698 l_found_gtin NUMBER := 0;
1699 l_rev VARCHAR2(3);
1700 l_uom_code VARCHAR2(3);
1701 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1702 l_gtin NUMBER;
1703
1704 l_total_wlc_qty NUMBER;
1705 l_total_mmtt_qty NUMBER;
1706 l_total_mmtt_qty1 NUMBER;
1707 l_total_mmtt_qty2 NUMBER;
1708 l_comp_prefix_dig_len NUMBER;
1709 l_item_reference NUMBER;
1710 l_serial_reference NUMBER;
1711 l_gtin_serial NUMBER;
1712 l_sscc NUMBER;
1713
1714 l_return_status VARCHAR2(1);
1715 --l_primary_uom_code VARCHAR2(3);
1716
1717 ----------------------------------------
1718 /*
1719 Following table shows Valid set up in Label Format Form for EPC
1720 generation using diferent standard (X means acceptable)
1721
1722 Standard\LabelType LPN/LPNContent/LPNSummary Material Serial
1723 SGTIN_96/64 X X X
1724 SSCC_96/64 X - -
1725 DoD_96/64 - - X
1726 */
1727 -----------------------------------------
1728
1729
1730 BEGIN
1731
1732 x_return_status := fnd_api.g_ret_sts_success;
1733
1734 IF p_label_type_id IN (3,4,5) THEN --LPN, LPN Content, LPN Summary
1735
1736 l_comp_prefix_dig_len:= Length(p_company_prefix);
1737
1738 IF p_epc_rule_type IN ('EPC_SGTIN_96','EPC_SGTIN_64') THEN
1739
1740 -- {{get GTIN and gtin-Serial for the LPN }}
1741 get_lpn_gtin_serial(p_lpn_id => p_lpn_id,
1742 p_org_id => p_org_id,
1743 p_filter_value => p_filter_value,
1744 p_business_flow_code => p_business_flow_code,
1745 x_gtin => l_gtin,
1746 x_gtin_serial => l_gtin_serial,
1747 x_return_status => l_return_status);
1748
1749 --{{ get the item reference from GTIN for LPN now }}
1750 --{{ l_gtin obtained IS NOT NULL }}
1751
1752 IF l_return_status = fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
1753
1754 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));
1755
1756 IF l_debug = 1 THEN
1757 trace('l_gtin , l_gtin_serial :' || l_gtin||','||l_gtin_serial);
1758 trace('l_item_reference :'|| l_item_reference);
1759 END IF;
1760
1761 x_gtin := l_gtin;
1762 x_sscc := NULL;
1763 x_gtin_serial := l_gtin_serial;
1764
1765 --{{ get all expected components FOR EPC_SGTIN_96}}
1766 IF p_epc_rule_type = 'EPC_SGTIN_96' THEN
1767
1768 --use company-prefix
1769
1770 IF l_debug = 1 THEN
1771 trace('p_filter_value,p_partition_value, p_company_prefix,l_item_reference,l_gtin_serial');
1772 trace(p_filter_value||','||p_partition_value||','||To_number(p_company_prefix)||','||l_item_reference||','||l_gtin_serial);
1773 END IF;
1774
1775 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',48,NULL), --for 00110000
1776 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
1777 MGD_IDCOMPONENT('PARTITION',p_partition_value,NULL),
1778 MGD_IDCOMPONENT('COMPANYPREFIX',To_number(p_company_prefix),NULL),
1779 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
1780 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
1781
1782
1783 ELSIF p_epc_rule_type = 'EPC_SGTIN_64' THEN
1784
1785 --{{ get all expected components FOR EPC_SGTIN_64}}
1786 --use company-prefix -INDEX
1787 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',2,NULL),--10
1788 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
1789 MGD_IDCOMPONENT('COMPANYPREFIXINDEX',To_number(p_comp_prefix_index),NULL),
1790 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
1791 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
1792
1793 END IF;
1794 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF get_lpn_gtin_serial()
1795 END IF;
1796
1797
1798 ELSIF p_epc_rule_type IN ('EPC_SSCC_96','EPC_SSCC_64') THEN
1799
1800 --{{ get SSCC for LPN }}
1801
1802 l_sscc := get_sscc(p_lpn_id,p_org_id);
1803
1804 IF l_debug = 1 THEN
1805 trace('SSCC for the LPN :'||l_sscc);
1806 trace('l_comp_prefix_dig_len :'||l_comp_prefix_dig_len);
1807 trace('p_filter_value :'||p_filter_value);
1808 trace('To_number(p_company_prefix) :'|| To_number(p_company_prefix));
1809 END IF;
1810
1811
1812 IF l_sscc IS NOT NULL THEN
1813
1814 x_gtin := NULL;
1815 x_sscc := l_sscc;
1816 x_gtin_serial := NULL;
1817
1818 --{{ get serial reference from SSCC }}
1819 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)));
1820
1821 IF l_debug = 1 THEN
1822
1823 trace('l_serial_reference :'|| l_serial_reference);
1824 END IF;
1825 --{{ get all expected components FOR EPC_SSCC_96 for containers}}
1826 IF p_epc_rule_type = 'EPC_SSCC_96' THEN
1827 --use company-prefix
1828 l_components := mgd_idcomponent_varray(MGD_IDCOMPONENT('HEADER',49,NULL), --for 00110001
1829 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
1830 MGD_IDCOMPONENT('PARTITION',p_partition_value,NULL),
1831 MGD_IDCOMPONENT('COMPANYPREFIX',To_number(p_company_prefix),NULL),
1832 MGD_IDCOMPONENT('SERIALREFERENCE',l_serial_reference,NULL),
1833 MGD_IDCOMPONENT('UNALLOCATED',0,NULL));
1834 ELSIF p_epc_rule_type = 'EPC_SSCC_64' THEN
1835
1836
1837 --{{ get all expected components FOR EPC_SSCC_64 for containers}}
1838 l_components :=
1839 mgd_idcomponent_varray(MGD_IDCOMPONENT('HEADER',8,NULL), --00001000
1840 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
1841 MGD_IDCOMPONENT('COMPANYPREFIXINDEX',To_number(p_comp_prefix_index),NULL),
1842 MGD_IDCOMPONENT('SERIALREFERENCE',l_serial_reference,NULL));
1843
1844
1845 END IF;
1846
1847 ELSE --means l_sscc is NULL
1848 IF l_debug = 1 THEN
1849 trace('Error : Incorrect SSCC value set up for the LPN');
1850 END IF;
1851
1852 RAISE fnd_api.g_exc_error;
1853 END IF;
1854
1855
1856 ELSIF p_epc_rule_type IN ('EPC_DOD_96','EPC_DOD_64') THEN
1857
1858
1859 --This is INVALID option for EPC generation FOR LPN label
1860 IF l_debug = 1 THEN
1861 trace('Error:For LPN label, No EPC can be generated using EPC_DOD_96/64, incorrect SET up');
1862 END IF;
1863 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
1864 FND_MSG_PUB.ADD;
1865 RAISE fnd_api.g_exc_error;
1866 END IF;
1867
1868 ELSIF p_label_type_id =1 THEN /*Material Label*/
1869
1870 l_comp_prefix_dig_len:= Length(p_company_prefix);
1871
1872 IF l_debug = 1 THEN
1873 trace('l_comp_prefix_dig_len :'||l_comp_prefix_dig_len);
1874 END IF;
1875
1876 IF p_epc_rule_type IN ('EPC_SGTIN_96','EPC_SGTIN_64') THEN
1877 -- for item_id and qty + UOM, find any set up in GTIN C/R
1878 -- Generate EPC for that GTIN.
1879
1880 get_item_gtin_serial(p_item_id => p_item_id,
1881 p_org_id => p_org_id,
1882 p_qty => p_txn_qty,
1883 p_uom_code => p_txn_uom,
1884 p_rev => l_rev,
1885 x_gtin => l_gtin,
1886 x_gtin_serial => l_gtin_serial,
1887 x_return_status => l_return_status);
1888
1889
1890
1891 IF l_return_status = fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
1892
1893
1894 --{{ get the item reference from GTIN for the Item now }}
1895
1896 l_item_reference :=
1897 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));
1898
1899 x_gtin := l_gtin;
1900 x_sscc := NULL;
1901 x_gtin_serial := l_gtin_serial;
1902
1903 IF p_epc_rule_type = 'EPC_SGTIN_96' THEN
1904
1905 --{{ get all expected components FOR EPC_SGTIN_96 for Item}}
1906 --use company-prefix
1907 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',48,NULL), --for 00110000
1908 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
1909 MGD_IDCOMPONENT('PARTITION',p_partition_value,NULL),
1910 MGD_IDCOMPONENT('COMPANYPREFIX',To_number(p_company_prefix),NULL),
1911 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
1912 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
1913
1914 ELSIF p_epc_rule_type = 'EPC_SGTIN_64' THEN
1915
1916 IF l_debug = 1 THEN
1917 trace('p_comp_prefix_index :'||To_number(p_comp_prefix_index));
1918 trace('p_filter_value :'||p_filter_value);
1919 trace('l_item_reference :'||l_item_reference);
1920 trace('l_gtin_serial :'||l_gtin_serial);
1921 END IF;
1922
1923 --{{ get all expected components FOR EPC_SGTIN_64 for Item}}
1924 --use company-prefix -INDEX
1925 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',2,NULL),--10
1926 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
1927 MGD_IDCOMPONENT('COMPANYPREFIXINDEX',To_number(p_comp_prefix_index),NULL),
1928 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
1929 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
1930
1931
1932 END IF;
1933 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF get_item_gtin_serial()
1934 END IF;
1935
1936 ELSIF p_epc_rule_type IN ('EPC_SSCC_96','EPC_SSCC_64') THEN
1937 --This is INVALID option for EPC generation of Material
1938 IF l_debug = 1 THEN
1939 trace('Error:For Material, No EPC can be generated using SSCC, incorrect SET up');
1940 END IF;
1941
1942 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
1943 FND_MSG_PUB.ADD;
1944 RAISE fnd_api.g_exc_error;
1945
1946
1947 ELSIF p_epc_rule_type IN ('EPC_DOD_96','EPC_DOD_64') THEN
1948
1949 IF l_debug = 1 THEN
1950 trace('Error:For Material, No EPC can be generated using EPC_DOD, incorrect SET up');
1951 END IF;
1952 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
1953 FND_MSG_PUB.ADD;
1954 RAISE fnd_api.g_exc_error;
1955
1956
1957 END IF;
1958
1959 ELSIF p_label_type_id = 2 THEN /*Serial Label*/
1960 --Components: FILTER,CAGE, SERIAL_NUMBER
1961
1962 IF p_epc_rule_type IN ('EPC_SGTIN_96','EPC_SGTIN_64') THEN
1963
1964 --Generate EPC for that item for GTIN corresponding to Primary UOM.
1965 --we have p_serial_number + p_item_id + p_cage_code+ p_filter_value
1966
1967 --P_serial_number will be totally ignored while generating EPC. GTIN_Serial
1968 --will be used but it will be stored in wms_EPC table against the
1969 --Item+p_serial_number
1970
1971 -- for item_id and qty + UOM, find any set up in GTIN C/R
1972 -- Generate EPC for that GTIN.
1973
1974 IF l_debug = 1 THEN
1975 trace('going to call get_serialnum_gtin_serial');
1976 END IF;
1977
1978 get_serialnum_gtin_serial(p_item_id => p_item_id,
1979 p_org_id => p_org_id,
1980 p_rev => l_rev,
1981 x_gtin => l_gtin,
1982 x_gtin_serial => l_gtin_serial,
1983 x_return_status => l_return_status);
1984
1985
1986 --{{ get the item reference from GTIN for the Item now }}
1987
1988
1989 IF l_debug = 1 THEN
1990 trace('After call get_serialnum_gtin_serial');
1991 trace('l_return_status :'||l_return_status);
1992 trace('l_gtin :'||l_gtin);
1993 trace('l_gtin_serial :'||l_gtin_serial);
1994 END IF;
1995
1996
1997 IF l_return_status= fnd_api.g_ret_sts_success AND l_gtin IS NOT NULL THEN
1998
1999 l_item_reference :=
2000 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));
2001
2002 x_gtin := l_gtin;
2003 x_sscc := NULL;
2004 x_gtin_serial := l_gtin_serial;
2005
2006
2007 IF p_epc_rule_type = 'EPC_SGTIN_96' THEN
2008
2009 --{{ get all expected components FOR EPC_SGTIN_96 for Item}}
2010 --use company-prefix
2011 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',48,NULL), --for 00110000
2012 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
2013 MGD_IDCOMPONENT('PARTITION',p_partition_value,NULL),
2014 MGD_IDCOMPONENT('COMPANYPREFIX',To_number(p_company_prefix),NULL),
2015 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
2016 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
2017 ELSIF p_epc_rule_type = 'EPC_SGTIN_64' THEN
2018
2019 --{{ get all expected components FOR EPC_SGTIN_64 for Item}}
2020 --use company-prefix -INDEX
2021 l_components := mgd_idcomponent_varray( MGD_IDCOMPONENT('HEADER',2,NULL),--10
2022 MGD_IDCOMPONENT('FILTERVALUE',p_filter_value,NULL),
2023 MGD_IDCOMPONENT('COMPANYPREFIXINDEX',To_number(p_comp_prefix_index),NULL),
2024 MGD_IDCOMPONENT('ITEMREFERENCE',l_item_reference,NULL),
2025 MGD_IDCOMPONENT('SERIALNUMBER',l_gtin_serial,NULL));
2026
2027 END IF;
2028 --WHEN GTIN = NULL IS HANDLED IN THE EXCEPTION OF
2029 --get_serialnum_gtin_serial() -> get_gtin_and_gserial()
2030
2031 END IF;
2032
2033 ELSIF p_epc_rule_type IN ('EPC_SSCC_96','EPC_SSCC_64') THEN
2034 --This is INVALID option for EPC generation of Serial NUMBER
2035 IF l_debug = 1 THEN
2036 trace('Error:For Serial, No EPC can be generated using SSCC, incorrect SET up');
2037 END IF;
2038 FND_MESSAGE.SET_NAME('WMS', 'WMS_INVALID_SETUP');
2039 FND_MSG_PUB.ADD;
2040 RAISE fnd_api.g_exc_error;
2041
2042 ELSIF p_epc_rule_type IN ('EPC_DOD_96','EPC_DOD_64') THEN
2043 --{{Note: We MUST have Serial uniqueness across items for EPC generation EPC_DOD_96/EPC_DOD_64}}
2044
2045 x_gtin := NULL;
2046 x_sscc := NULL;
2047 x_gtin_serial := NULL;
2048
2049 IF p_epc_rule_type = 'EPC_DOD_96' THEN
2050 --{{ get all expected components FOR EPC_DOD_96 for container}}
2051
2052 l_components := mgd_idcomponent_varray(MGD_IDCOMPONENT('HEADER',207,NULL),--11001111
2053 MGD_IDCOMPONENT('FILTER',p_filter_value,NULL),
2054 MGD_IDCOMPONENT('GOVERNMENTMANAGEDIDENTIFIER',NULL,p_cage_code),
2055 MGD_IDCOMPONENT('SERIALNUMBER',p_serial_number,NULL));
2056
2057
2058 ELSIF p_epc_rule_type = 'EPC_DOD_64' THEN
2059 --{{ get all expected components FOR EPC_DOD_96 for container }}
2060
2061 IF l_debug = 1 THEN
2062 trace('Inside EPC_DOD_64 to collect components');
2063 END IF;
2064
2065 l_components := mgd_idcomponent_varray(MGD_IDCOMPONENT('HEADER',206,NULL),--11001110
2066 MGD_IDCOMPONENT('FILTER',p_filter_value,NULL),
2067 MGD_IDCOMPONENT('GOVERNMENTMANAGEDIDENTIFIER',NULL,p_cage_code),
2068 MGD_IDCOMPONENT('SERIALNUMBER',p_serial_number,NULL));
2069
2070 END IF;
2071
2072
2073 END IF;
2074
2075 END IF;
2076
2077 x_components := l_components;
2078
2079
2080 EXCEPTION
2081 WHEN fnd_api.g_exc_error THEN
2082 x_return_status := fnd_api.g_ret_sts_error;
2083 x_gtin_serial := NULL;
2084 x_gtin :=NULL;
2085 x_sscc := NULL;
2086 x_components := NULL;
2087 --RAISE; -- Do not raise here
2088
2089 WHEN fnd_api.g_exc_unexpected_error THEN
2090 x_return_status := fnd_api.g_ret_sts_unexp_error;
2091 x_gtin_serial := NULL;
2092 x_gtin :=NULL;
2093 x_sscc := NULL;
2094 x_components := NULL;
2095 --RAISE; -- Do not raise here
2096
2097
2098 WHEN OTHERS THEN
2099 IF l_debug = 1 THEN
2100 trace('Unexpected error inside get_epc_gen_info()');
2101 trace('ERROR CODE = ' || SQLCODE);
2102 trace('ERROR MESSAGE = ' || SQLERRM);
2103 END IF;
2104 x_return_status := fnd_api.g_ret_sts_unexp_error;
2105 x_gtin_serial := NULL;
2106 x_gtin := NULL;
2107 x_sscc := NULL;
2108 x_components := NULL;
2109 --RAISE; -- Do not raise here
2110 END get_epc_gen_info;
2111
2112
2113
2114 ---This API caches all EPC generation Rule type from the core DB EPC
2115 --TABLES AND keeps it in memory for future calls
2116
2117 FUNCTION Cache_and_get_rule(p_partition_val IN NUMBER,
2118 p_type_name IN VARCHAR2,
2119 p_category_id IN NUMBER) RETURN NUMBER AS
2120
2121 CURSOR c_epc_gen_rule_types IS
2122 select type_id, type_name, nvl(partition_value,0) partition_value
2123 ,category_id
2124 from mgd_idencoding_type ;
2125
2126 l_index NUMBER;
2127 l_epc_type_id NUMBER := NULL;
2128 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2129
2130 BEGIN
2131 IF l_debug = 1 THEN
2132 trace('Inside Cache_and_get_rule');
2133 trace('p_partition_val:'||p_partition_val);
2134 trace('p_type_name :'||p_type_name );
2135 trace(' p_category_id :'|| p_category_id);
2136
2137 END IF;
2138
2139 IF g_cached_rule_pkg.COUNT() = 0 THEN --Not cached Yet
2140
2141 IF l_debug = 1 THEN
2142 trace('Caching the rule first time ###########');
2143 END IF;
2144
2145 --Cache the rule
2146 l_index := 1;
2147
2148 FOR l_epc_gen_rule_types IN c_epc_gen_rule_types LOOP
2149
2150 g_cached_rule_pkg(l_index).type_id :=l_epc_gen_rule_types.TYPE_id;
2151 g_cached_rule_pkg(l_index).type_name :=l_epc_gen_rule_types.type_name;
2152 g_cached_rule_pkg(l_index).partition_value :=l_epc_gen_rule_types.partition_value;
2153 g_cached_rule_pkg(l_index).category_id :=l_epc_gen_rule_types.category_id;
2154
2155 IF l_epc_gen_rule_types.type_name = p_type_name
2156 AND l_epc_gen_rule_types.partition_value = p_partition_val
2157 AND l_epc_gen_rule_types.category_id = p_category_id THEN
2158
2159 l_epc_type_id := l_epc_gen_rule_types.type_id;
2160
2161 END IF;
2162
2163 l_index := l_index + 1;
2164 END LOOP;
2165
2166 ELSE --from the cached rule return proper type_id
2167
2168 IF l_debug = 1 THEN
2169 trace('Retrieving EPC rule from the cache ###########');
2170 END IF;
2171
2172 FOR i IN g_cached_rule_pkg.FIRST..g_cached_rule_pkg.LAST
2173 LOOP
2174
2175 IF l_debug = 1 THEN
2176 trace('g_cached_rule_pkg(i).type_name:'||g_cached_rule_pkg(i).type_name);
2177 trace('g_cached_rule_pkg(i).partition_value :'||g_cached_rule_pkg(i).partition_value);
2178 trace('g_cached_rule_pkg(i).category_id :'||g_cached_rule_pkg(i).category_id );
2179 END IF;
2180
2181
2182
2183 IF g_cached_rule_pkg(i).type_name = p_type_name
2184 AND g_cached_rule_pkg(i).partition_value = p_partition_val
2185
2186 /* OR
2187 (p_partition_val IS NULL AND
2188 g_cached_rule_pkg(i).partition_value IS NULL) )*/
2189
2190 AND g_cached_rule_pkg(i).category_id = p_category_id THEN
2191
2192 l_epc_type_id := g_cached_rule_pkg(i).type_id;
2193
2194 EXIT; -- got matching rule, Exit the loop
2195 END IF;
2196 END LOOP;
2197
2198 IF l_debug = 1 THEN
2199 trace('Returned EPC rule type id :'||l_epc_type_id);
2200 END IF;
2201
2202
2203 END IF;
2204
2205
2206 RETURN l_epc_type_id;
2207
2208 EXCEPTION
2209 WHEN OTHERS THEN
2210
2211 IF l_debug = 1 THEN
2212 trace('Exception in Cache_and_get_rule');
2213 trace('ERROR CODE = ' || SQLCODE);
2214 trace('ERROR MESSAGE = ' || SQLERRM);
2215 END IF;
2216
2217
2218 END Cache_and_get_rule;
2219
2220
2221
2222 --*********************************************
2223 /*
2224 Procedure generate_epc
2225
2226 FUNCTIONALITY:-
2227 This is the main API in this package that is responsible for generating
2228 EPC for current transaction
2229
2230 PARAMETER:-
2231 p_org_id : Organization id
2232 p_label_type_id : Supported LPN/Serial/Material labels : VALID VALUES 1,2,3,4,5
2233 p_group_id : groun_id per invocation
2234 p_label_format_id : label_format_id for this transaction that should have has epc field
2235 p_item_id : need TO pass fpr Material Label: 1 only
2236 p_txn_qty : need TO pass fpr Material Label: 1 only
2237 p_txn_uom : need TO pass fpr Material Label: 1 only (since uom is not in wms_label_requests table)
2238 p_label_request_id: the id from wms_label_requests foe which label will be generated
2239 p_business_flow_code : business flow code value
2240 x_epc : returns generated EPC
2241 x_return_status :-
2242 S : success : EPC generated E : error : EPC could not be generated for valid reason
2243 U : Warning : EPC could not be generated for unexpected reason
2244
2245 x_return_mesg : Appropriate error message
2246
2247 */
2248
2249 --*********************************************
2250
2251 Procedure generate_epc
2252 (p_org_id IN NUMBER,
2253 p_label_type_id IN NUMBER, /* VALID VALUES 1,2,3,4,5*/
2254 p_group_id IN NUMBER,
2255 p_label_format_id IN NUMBER,
2256 p_item_id IN NUMBER DEFAULT NULL, --For Material Label: 1
2257 p_txn_qty IN NUMBER DEFAULT null, --For Material Label: 1
2258 p_txn_uom IN VARCHAR2 DEFAULT NULL, --For Material Label: 1
2259 p_label_request_id IN NUMBER,
2260 p_business_flow_code IN NUMBER DEFAULT NULL,
2261 x_epc OUT nocopy VARCHAR2,
2262 x_return_status OUT nocopy VARCHAR2,
2263 x_return_mesg OUT nocopy VARCHAR2
2264 ) IS
2265
2266 end_processing EXCEPTION;
2267 l_gen_epc VARCHAR2(260);
2268 l_sscc NUMBER;
2269 l_gtin NUMBER;
2270 l_gtin_serial NUMBER;
2271 l_epc VARCHAR2(260);
2272 l_msg_count NUMBER;
2273
2274 l_return_status VARCHAR2(1);
2275 l_regenerate_flag VARCHAR2(1) := 'N';
2276 l_filter_value NUMBER;
2277 l_outermost_lpn_id NUMBER;
2278 l_PARENT_lpn_id NUMBER;
2279 l_return_mesg VARCHAR2(2000);
2280 --l_existing_epc_rule_id NUMBER;
2281 l_epc_output_rep NUMBER;
2282
2283 --New parameters
2284 is_epc_enabled VARCHAR2(1);
2285 l_company_prefix VARCHAR2(30);
2286 l_company_prefix_index VARCHAR2(30);
2287 l_cage_code VARCHAR2(30);
2288 l_custom_company_prefix VARCHAR2(30);
2289 l_epc_rule_type VARCHAR2(100);
2290 l_epc_rule_type_id NUMBER;
2291 l_partition_value NUMBER;
2292 l_cust_comp_prefix_index NUMBER;
2293 l_comp_prefix_len NUMBER;
2294 l_cross_ref_type NUMBER;
2295 l_epc_category_id NUMBER;
2296 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2297 l_lpn_id NUMBER;
2298 l_serial_number VARCHAR2(30);
2299 l_item_id NUMBER;
2300 l_rev VARCHAR2(3);
2301 l_bit_length NUMBER;
2302 l_components mgd_idcomponent_varray := mgd_idcomponent_varray(mgd_idcomponent('HEADER',NULL,null));
2303 pcode mgd_idcode;
2304
2305 BEGIN
2306
2307 G_PROFILE_GTIN := FND_PROFILE.value('INV:GTIN_CROSS_REFERENCE_TYPE');
2308 x_return_status := fnd_api.g_ret_sts_success;
2309
2310 IF l_debug = 1 THEN
2311 trace('************ inside generate_epc ***********');
2312 trace('p_org_id :'||p_org_id);
2313 trace(' p_label_request_id :'||p_label_request_id);
2314 trace('p_business_flow_code :'||p_business_flow_code);
2315 trace('p_group_id :'||p_group_id);
2316 trace('p_label_type_id :'||p_label_type_id);
2317 trace('p_label_format_id :'||p_label_format_id);
2318 trace('p_item_id :'||p_item_id);
2319 trace('p_txn_qty :'||p_txn_qty);
2320 trace('p_txn_uom :'||p_txn_uom);
2321
2322 END IF;
2323
2324
2325 --{{get needed information from mtl_parameters setup for EPC generation }}
2326 IF ( NOT Inv_Cache.set_org_rec( p_organization_id => p_org_id ))THEN
2327 IF (l_debug = 1) THEN
2328 trace(p_org_id || 'is an invalid organization id');
2329 END IF;
2330 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
2331 fnd_msg_pub.ADD;
2332 RAISE fnd_api.g_exc_error;
2333 END IF;
2334
2335 l_company_prefix := inv_cache.org_rec.company_prefix;
2336 l_company_prefix_index := inv_cache.org_rec.company_prefix_index;
2337
2338
2339 IF l_debug = 1 THEN
2340 trace('epc_enabled :'||Nvl(inv_cache.org_rec.epc_generation_enabled_flag,'N'));
2341 trace('l_company_prefix :'||l_company_prefix);
2342 trace('l_company_prefix_index :'||l_company_prefix_index);
2343 trace('cage_code :'||inv_cache.org_rec.commercial_govt_entity_number);
2344 END IF;
2345
2346 --{{get needed information from label formats setup for the format_id}}
2347 SELECT epc_rule_type,filter_value,generate_epc,epc_category_id
2348 INTO l_epc_rule_type,l_filter_value,l_regenerate_flag, l_epc_category_id
2349 FROM wms_label_formats
2350 WHERE label_format_id = p_label_format_id
2351 AND Nvl(label_ENTITY_type,0) =0; --label format and NOT label-set
2352
2353 IF l_debug = 1 THEN
2354 trace('l_epc_rule_type :'||l_epc_rule_type);
2355 trace('l_filter_value :'||l_filter_value);
2356 trace('l_regenerate_flag :'||l_regenerate_flag);
2357 trace('l_epc_category_id :'||l_epc_category_id);
2358 END IF;
2359
2360 --Check if EPC is enabled
2361 IF Nvl(inv_cache.org_rec.epc_generation_enabled_flag,'N') = 'Y' THEN
2362
2363 --Get required information about the current transaction
2364 SELECT lpn_id,serial_number,inventory_item_id,revision
2365 INTO l_lpn_id,l_serial_number,l_item_id, l_rev
2366 FROM wms_label_requests
2367 WHERE label_request_id = p_label_request_id;
2368
2369
2370 IF l_debug = 1 THEN
2371 trace('l_lpn_id,l_serial_number,l_item_id, l_rev :'||l_lpn_id||','||l_serial_number||','||l_item_id||','||l_rev);
2372 END IF;
2373
2374 --Find if the EPC cross-ref already exist or it needs to be re-created
2375 --For LPN
2376
2377 IF l_lpn_id IS NOT NULL AND p_label_type_id IN (3,4,5) THEN /* LPN / LPN-Content / LPN Summary*/
2378
2379 l_cross_ref_type := 1;--/*LPN-EPC Cross ref*/
2380
2381 BEGIN
2382 SELECT wlpn.parent_lpn_id, wlpn.outermost_lpn_id, we.epc
2383 INTO l_parent_lpn_id, l_outermost_lpn_id, l_epc
2384 FROM wms_license_plate_numbers wlpn, wms_epc we
2385 WHERE wlpn.lpn_id = l_lpn_id
2386 AND wlpn.lpn_id = we.lpn_id(+)
2387 AND ((we.epc is NOT NULL and Nvl(we.cross_ref_type,1) = 1)
2388 or (we.epc is NULL )) ;
2389 --Nvl(we.cross_ref_type,1) to support EPC generated
2390 --using 11.5.10.2CU code
2391
2392 -- starting R12 this must be
2393 -- populated AND old data needs to be updated
2394
2395 EXCEPTION
2396 WHEN no_data_found THEN
2397
2398 IF l_debug = 1 THEN
2399 trace('NO DATA found for the LPN');
2400 END IF;
2401
2402 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
2403 fnd_msg_pub.ADD;
2404 RAISE fnd_api.g_exc_unexpected_error;
2405 END;
2406
2407 --Neither pallet or case, then NO support FOR EPC of further
2408 -- nested lpn, error out
2409 IF NOT ((l_outermost_lpn_id = l_lpn_id AND l_parent_lpn_id IS null )
2410 OR (l_parent_lpn_id = l_outermost_lpn_id AND l_parent_lpn_id IS NOT null)) THEN
2411
2412 fnd_message.set_name('WMS', 'WMS_NO_MULTI_NESTING_SUPPORT');
2413 fnd_msg_pub.ADD;
2414 RAISE fnd_api.g_exc_error;
2415
2416 END IF;
2417
2418 --For Serial
2419 ELSIF l_serial_number IS NOT NULL AND p_label_type_id = 2 THEN --/* Serial Label*/
2420
2421 l_cross_ref_type := 2;--/*Serial-EPC Cross ref*/
2422
2423 --Item_id + Serial_number will uniquely mapped to an EPC
2424 BEGIN
2425 SELECT we.epc INTO l_epc
2426 FROM wms_epc we
2427 WHERE INVENTORY_item_id = l_item_id
2428 AND serial_number = l_serial_number
2429 AND we.cross_ref_type = 2;
2430 EXCEPTION
2431 WHEN no_data_found THEN
2432 NULL;
2433 WHEN OTHERS THEN
2434 RAISE fnd_api.g_exc_unexpected_error;
2435 END;
2436
2437 --For item_id/GTIN
2438 ELSIF l_item_id IS NOT NULL AND p_label_type_id = 1 THEN --/*Material Label*/
2439
2440 l_cross_ref_type := 3; --/*GTIN+GTIN_SERIAL-EPC Cross ref*/
2441
2442 --No need to check whether EPC exists or NOT for the GTIN
2443 --just regenerate EPC, EPC will be unique to GTIN+GTIN-SERIAL combination
2444 --and it needs to be inserted
2445
2446 l_regenerate_flag := 'Y'; -- override always
2447 END IF;
2448
2449
2450 --{{call to see if the custom company_prefix is implemented}}
2451 wms_epc_pub.GET_CUSTOM_COMPANY_PREFIX(
2452 p_org_id => p_org_id,
2453 p_label_request_id => p_label_request_id,
2454 X_company_prefix => l_custom_company_prefix,
2455 X_RETURN_STATUS => l_return_status);
2456
2457 --{{call to see if the custom company_prefix_INDEX is implemented}}
2458 wms_epc_pub.GET_CUSTOM_COMP_PREFIX_INDEX(p_org_id => p_org_id,
2459 p_label_request_id => p_label_request_id,
2460 X_comp_prefix_INDEX => l_CUST_comp_prefix_INDEX,
2461 X_RETURN_STATUS => l_return_status);
2462
2463 IF l_debug = 1 THEN
2464 trace('CUSTOM_COMPANY_PREFIX value :'||l_custom_company_prefix);
2465 trace('CUSTOM_COMPANY_PREFIX_INDEX value :'||l_cust_comp_prefix_index);
2466 END IF;
2467
2468 IF l_custom_company_prefix IS NOT NULL THEN
2469 l_company_prefix := l_custom_company_prefix;
2470 END IF;
2471
2472 IF l_cust_comp_prefix_index IS NOT NULL THEN
2473 l_company_prefix_index := l_cust_comp_prefix_index;
2474 END IF;
2475
2476 IF l_debug = 1 THEN
2477 trace('Final COMPANY_PREFIX value :'||l_company_prefix);
2478 trace('Final COMPANY_PREFIX_INDEX value :'||l_company_prefix_index );
2479 END IF;
2480
2481
2482 l_partition_value :=get_PARTITION_value(l_epc_rule_type, l_company_prefix);
2483
2484 IF l_debug = 1 THEN
2485 trace('l_partition_value :'||l_partition_value);
2486 END IF;
2487
2488
2489
2490 --{{ see if partition value returned is correct and get the l_epc_rule_type_id now}}
2491
2492 IF l_partition_value = -1 THEN --prefix length is INCORRECT
2493 fnd_message.set_name('WMS','WMS_INCORRECT_PREFIX_LEN');
2494 fnd_msg_pub.ADD;
2495 RAISE fnd_api.g_exc_error;
2496
2497 ELSE
2498 --To avoid DB table call for each read:
2499 --Cache Entire rule in the memory if first time call
2500 --otherwise get epc generation rule type for current txn
2501
2502 IF l_debug = 1 THEN
2503 trace('GOING TO CALL Cache_and_get_rule');
2504 END IF;
2505
2506
2507 l_epc_rule_type_id := Cache_and_get_rule(l_partition_value, l_epc_rule_type,l_epc_category_id);
2508
2509 --NULL value OF PARTITION will be treated AS 0
2510
2511 END IF;
2512
2513 IF l_epc_rule_type_id IS NULL THEN
2514 x_epc := NULL;
2515 fnd_message.set_name('WMS','WMS_NO_EPC_RULE_FOUND');--No rule found
2516 fnd_msg_pub.ADD;
2517 RAISE fnd_api.g_exc_error;
2518 END IF;
2519
2520
2521 IF l_debug = 1 THEN
2522 trace('Generating EPC now.............');
2523 END IF;
2524
2525 --{{ See if the rule is custom or Un-Implemented Standard Rule }}
2526 IF l_epc_category_id <> mgd_idcode_utl.epc_encoding_category_id
2527 OR ( l_epc_category_id = mgd_idcode_utl.epc_encoding_category_id AND
2528 l_epc_rule_type NOT IN ('EPC_SGTIN_96','EPC_SSCC_96','EPC_SGTIN_64','EPC_SSCC_64','EPC_DOD_96','EPC_DOD_64') )
2529 --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')
2530 THEN
2531
2532 -- {{ get custom EPC generated }}
2533 wms_epc_pub.get_custom_epc
2534 (p_org_id => p_org_id,
2535 p_category_id => l_epc_category_id,-- In mgd_idencoding_type table
2536 p_epc_rule_type_id => l_epc_rule_type_id, --Rule_type_id IN mgd_idencoding_type table
2537 p_filter_value => l_filter_value,
2538 p_label_request_id => p_label_request_id, --to get all data FROM wms_device_requests label
2539 x_return_status => l_return_status,
2540 x_return_mesg => l_return_mesg,
2541 x_epc => l_epc);
2542
2543 ELSIF l_epc_rule_type IN
2544 ('EPC_SGTIN_96','EPC_SSCC_96','EPC_SGTIN_64','EPC_SSCC_64','EPC_DOD_96','EPC_DOD_64') THEN
2545 --{{ get standard EPC generated for standard rule}}
2546
2547 --{{ Check to see if regenerate flag is ON..only then generate epc }}
2548 IF l_regenerate_flag = 'Y' OR (l_epc is NULL AND
2549 Nvl(l_regenerate_flag,'N') = 'N' ) THEN
2550
2551 --{{ get EPC Encoding Rule components }}
2552 IF l_debug = 1 THEN
2553 trace('Collecting information to generate EPC ....');
2554 END IF;
2555
2556 get_epc_gen_info( p_org_id => p_org_id,
2557 p_lpn_id => l_lpn_id, --FOR p_label_type_id = 3,4,5
2558 p_serial_number => l_serial_number, --FOR p_label_type_id = 2
2559 p_item_id => Nvl(l_item_id,p_item_id), --FOR p_label_type_id = 1
2560 p_txn_qty => p_txn_qty, --FOR p_label_type_id = 1
2561 p_txn_uom => p_txn_uom, --FOR p_label_type_id = 1
2562 p_rev => l_rev, --FOR p_label_type_id = 1,2
2563 p_company_prefix => l_company_prefix,
2564 p_comp_prefix_index => l_company_prefix_index,
2565 p_business_flow_code => p_business_flow_code,
2566 p_label_type_id => P_label_type_id,
2567 p_epc_rule_type => l_epc_rule_type,
2568 p_filter_value => l_filter_value,
2569 p_cage_code => inv_cache.org_rec.commercial_govt_entity_number, --FOR p_label_type_id = 2
2570 p_partition_value => l_partition_value,
2571 x_gtin => l_gtin,
2572 x_sscc => l_sscc,
2573 x_gtin_serial => l_gtin_seriaL,
2574 x_components => l_components,
2575 x_return_status => l_return_status);
2576
2577 IF l_debug = 1 THEN
2578 trace('after calling get_epc_gen_info... ');
2579 trace('l_gtin :'||l_gtin );
2580 trace('l_sscc :'||l_sscc);
2581 trace('l_gtin_serial :'||l_gtin_serial);
2582 trace('l_return_status :'||l_return_status);
2583 END IF;
2584
2585
2586 --{{ genereate EPC using the components }}
2587
2588 IF l_return_status = fnd_api.g_ret_sts_success AND l_components IS NOT NULL THEN
2589
2590
2591 -- This is needed to pass correct value (NULL Vs 0)in the DB API
2592 --In some case NULL and anohter 0 is needed
2593 IF (l_epc_rule_type IN
2594 ('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
2595 l_partition_value := NULL;
2596
2597 END IF;
2598
2599
2600 IF l_debug = 1 THEN
2601 trace('Before calling DB EPC category_name :'||mgd_idcode_utl.EPC_ENCODING_CATEGORY_NAME);
2602 trace('partition_val :'||l_partition_value);
2603 END IF;
2604
2605 BEGIN
2606 pcode := mgd_idcode( category_name => mgd_idcode_utl.EPC_ENCODING_CATEGORY_NAME,
2607 category_agency => NULL,
2608 category_version => NULL,
2609 category_uri => NULL,
2610 encoding_type_name => l_epc_rule_type, --mgd_idcode_utl.EPC_SGTIN_96,
2611 partition_val => l_partition_value,
2612 components => l_components);
2613
2614 l_gen_epc := pcode.bit_encoding; --EPC in HEXA system
2615
2616 IF l_debug = 1 THEN
2617 trace('EPC generated by DB Feature :'||l_gen_epc);
2618 END IF;
2619
2620 EXCEPTION
2621 WHEN OTHERS THEN
2622
2623 IF l_debug = 1 THEN
2624 TRACE('After calling mgd_idcode: Inside exception');
2625 TRACE('ERROR CODE = ' || SQLCODE);
2626 TRACE('ERROR MESSAGE = ' || SQLERRM);
2627 END IF;
2628
2629 --EPC generation failed at DB feature level
2630 fnd_message.set_name('WMS', 'WMS_DB_EPC_GEN_FAIL');
2631 fnd_msg_pub.ADD;
2632
2633 --Do not raise exception here as we want to delete
2634 --old cross-reference RECORD FROM wms_epc for some
2635 --CASES BELOW
2636 END;
2637
2638 ELSE
2639 -- {{Error out l_components are null, EPC could not be generated }}
2640
2641 IF l_debug = 1 THEN
2642 trace('get_epc_gen_info() returned error');
2643 trace('Error: Components could not be obtained for EPC generation ');
2644 END IF;
2645
2646
2647 fnd_message.set_name('WMS', 'WMS_EPC_GEN_FAIL');
2648 fnd_msg_pub.ADD;
2649
2650 --Do not raise exception here as we want to delete
2651 --old cross-reference RECORD FROM wms_epc for some
2652 --CASES BELOW
2653
2654 END IF; --l_return_status = 'S for get_epc_gen_info()
2655
2656
2657 END IF; --l_regenerate_flag = 'Y'
2658
2659 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'
2660
2661 ELSE
2662 IF l_debug = 1 THEN
2663 trace('EPC generation is NOT enabled at Orgnization level');
2664 END IF;
2665
2666 fnd_message.set_name('WMS', 'WMS_EPC_DISABLED');
2667 fnd_msg_pub.ADD;
2668 RAISE fnd_api.g_exc_error;
2669
2670 END IF;
2671
2672
2673 --{{ By NOW EPC should be generate for valid cases: Insert EPC or update EPC or delete EPC }}
2674
2675 IF l_debug = 1 THEN
2676 trace('Old EPC,if any :'||l_epc);
2677 trace('New generated EPC:'||l_gen_epc);
2678 END IF;
2679
2680
2681
2682 --{{ Get the EPC ENCODING defined with the profile 'WMS_EPC_ENCODING'}}
2683 l_epc_output_rep := NVL(fnd_profile.value('WMS_EPC_ENCODING'), 2);
2684 -- 1 : Binary
2685 -- 2 : Hex
2686 -- 3 : Decimal
2687
2688 IF l_debug = 1 THEN
2689 trace('1-Binary,2-Hex,3-Decimal l_epc_output_rep :'||l_epc_output_rep);
2690 END IF;
2691
2692 --l_epc_rule_type_id is already identified above
2693 IF l_regenerate_flag = 'Y' OR (l_epc is NULL AND
2694 Nvl(l_regenerate_flag,'N') = 'N' ) THEN
2695
2696
2697 IF l_epc IS NOT NULL AND l_gen_epc IS NOT NULL THEN
2698 --{{ EPC c/r already there: UPDATE LAST EPC with new EPC value }}
2699 uptodate_wms_epc ( p_action => 'UPDATE',
2700 p_group_id => p_group_id,
2701 p_cross_ref_type => l_cross_ref_type,
2702 p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
2703 p_lpn_id => l_lpn_id,
2704 p_item_id => l_item_id,
2705 p_serial_number => l_serial_number,
2706 p_gen_epc => l_gen_epc,
2707 p_sscc => l_sscc,
2708 p_gtin => l_gtin,
2709 p_gtin_serial => l_gtin_serial,
2710 p_filter_VALUE => l_filter_value,
2711 x_return_status => L_RETURN_STATUS);
2712
2713
2714 IF l_debug =1 then
2715 trace(' uptodate_wms_epc UPDATE: L_RETURN_STATUS:'||l_return_status);
2716 END IF;
2717
2718
2719 --return new EPC IN THE FORMAT SPECIFIED
2720 IF L_epc_output_rep = 1 THEN --Binary
2721 l_bit_length := To_number(Substr(l_epc_rule_type,(Length(l_epc_rule_type)-1),Length(l_epc_rule_type)));
2722 x_epc := Lpad(dec2bin(hex2dec(l_gen_epc)),l_bit_length,'0');
2723
2724 ELSIF l_epc_output_rep = 3 THEN --Decimal
2725 x_epc := hex2dec(l_gen_epc);
2726 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
2727 x_epc := l_gen_epc;
2728 END IF;
2729
2730
2731 ELSIF l_epc IS NOT NULL AND l_gen_epc IS NULL THEN
2732 -- Delete the existing cross -reference
2733
2734 uptodate_wms_epc ( p_action => 'DELETE',
2735 p_group_id => p_group_id,
2736 p_cross_ref_type => l_cross_ref_type,
2737 p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
2738 p_lpn_id => l_lpn_id,
2739 p_item_id => l_item_id,
2740 p_serial_number => l_serial_number,
2741 p_gen_epc => l_gen_epc,
2742 p_sscc => l_sscc,
2743 p_gtin => l_gtin,
2744 p_gtin_serial => l_gtin_serial,
2745 p_filter_VALUE => l_filter_value,
2746 x_return_status => L_RETURN_STATUS);
2747
2748 IF l_debug =1 then
2749 trace(' uptodate_wms_epc DELETE: L_RETURN_STATUS:'||l_return_status);
2750 END IF;
2751
2752 RAISE fnd_api.g_exc_error;--COULD NOT OVERIDE THE EPC
2753
2754
2755 ELSIF l_epc IS NULL AND l_gen_epc IS NOT NULL THEN
2756
2757 uptodate_wms_epc( p_action => 'INSERT',
2758 p_group_id => p_group_id,
2759 p_cross_ref_type => l_cross_ref_type,
2760 p_EPC_rule_TYPE_id => l_EPC_rule_TYPE_id,
2761 p_lpn_id => l_lpn_id,
2762 p_item_id => l_item_id,
2763 p_serial_number => l_serial_number,
2764 p_gen_epc => l_gen_epc,
2765 p_sscc => l_sscc,
2766 p_gtin => l_gtin,
2767 p_gtin_serial => l_gtin_serial,
2768 p_filter_VALUE => l_filter_value,
2769 x_return_status => L_RETURN_STATUS);
2770
2771 IF l_debug =1 then
2772 trace(' uptodate_wms_epc INSERT: L_RETURN_STATUS:'||l_return_status);
2773 END IF;
2774
2775 --return new EPC IN THE FORMAT SPECIFIED
2776 IF l_epc_output_rep = 1 THEN
2777 l_bit_length := To_number(Substr(l_epc_rule_type,(Length(l_epc_rule_type)-1),Length(l_epc_rule_type)));
2778 x_epc := Lpad(dec2bin(hex2dec(l_gen_epc)),l_bit_length,'0');
2779 ELSIF l_epc_output_rep = 3 THEN
2780 x_epc := hex2dec(l_gen_epc);
2781 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
2782 x_epc := l_gen_epc;
2783 END IF;
2784
2785
2786 ELSIF l_epc IS NULL AND l_gen_epc IS NULL THEN
2787
2788 RAISE fnd_api.g_exc_error;
2789
2790 END IF;
2791
2792 ELSIF Nvl(l_regenerate_flag,'N') = 'N' THEN
2793
2794 IF l_epc IS NOT NULL THEN
2795
2796 --Return Old EPC,Already it was stored in Hex
2797 --return new EPC IN THE FORMAT SPECIFIED
2798 IF l_epc_output_rep = 1 THEN
2799 l_bit_length := To_number(Substr(l_epc_rule_type,(Length(l_epc_rule_type)-1),Length(l_epc_rule_type)));
2800 x_epc := Lpad(dec2bin(hex2dec(l_gen_epc)),l_bit_length,'0');
2801 ELSIF l_epc_output_rep = 3 THEN
2802 x_epc := hex2dec(l_epc);
2803 ELSIF l_epc_output_rep = 2 OR l_epc_output_rep IS NULL THEN --Hex
2804 x_epc := l_epc;
2805 END IF;
2806
2807
2808 END IF; -- L_EPC IS NOT NULL
2809
2810 END IF; -- For p_regenerate_flag = 'N'
2811
2812
2813
2814 EXCEPTION
2815
2816 WHEN fnd_api.g_exc_error THEN
2817 fnd_message.set_name('WMS','WMS_EPC_GEN_FAIL');
2818 fnd_msg_pub.ADD;
2819 x_return_status := fnd_api.g_ret_sts_error;
2820 x_epc := NULL;
2821 fnd_msg_pub.count_and_get(p_encoded => 'F',p_count => l_msg_count, p_data => x_return_mesg);
2822
2823 FOR i IN 1..l_msg_count LOOP
2824 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
2825 END LOOP;
2826
2827 IF l_debug = 1 THEN
2828 TRACE('Inside g_exc_error l_msg_count :'||l_msg_count);
2829 TRACE('x_return_mesg :'||x_return_mesg);
2830 END IF;
2831
2832 -- FND_MSG_PUB.initialize; --bug 5178424
2833
2834
2835 WHEN fnd_api.g_exc_unexpected_error THEN
2836 fnd_message.set_name('WMS', 'WMS_EPC_GEN_FAIL');
2837 fnd_msg_pub.ADD;
2838 x_return_status := fnd_api.g_ret_sts_unexp_error;
2839 x_epc := NULL;
2840 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => x_return_mesg);
2841
2842 FOR i IN 1..l_msg_count LOOP
2843 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
2844 END LOOP;
2845
2846 IF l_debug = 1 THEN
2847 TRACE('Inside g_exc_unexpected_error l_msg_count :'||l_msg_count);
2848 TRACE('x_return_mesg :'||x_return_mesg);
2849 END IF;
2850
2851 -- FND_MSG_PUB.initialize; --bug 5178424
2852
2853 WHEN OTHERS THEN
2854 --ROLLBACK; blocked in R12
2855 x_return_status := fnd_api.g_ret_sts_unexp_error;
2856 x_epc := NULL;
2857 fnd_msg_pub.count_and_get(p_count => l_msg_count, p_data => x_return_mesg);
2858
2859 FOR i IN 1..l_msg_count LOOP
2860 x_return_mesg := x_return_mesg || fnd_msg_pub.get(I,'F');
2861 END LOOP;
2862
2863 -- FND_MSG_PUB.initialize; --bug 5178424
2864
2865 IF l_debug = 1 THEN
2866 TRACE('generate EPC: Inside exception');
2867 TRACE('ERROR CODE = ' || SQLCODE);
2868 TRACE('ERROR MESSAGE = ' || SQLERRM);
2869 END IF;
2870
2871 END generate_epc;
2872
2873
2874 --This API will be called while importing ASNs
2875 --Purpose
2876 -- Create Cross-reference in WMS_EPC TABLE
2877 --between EPC and objects from interface tables
2878
2879 PROCEDURE populate_outside_epc
2880 (p_group_id IN NUMBER , --obtained from WMS_EPC_S2.nextval by calling API
2881 p_cross_ref_type IN NUMBER, --1: LPN-EPC , 2: ITEM_SERIAL-EPC , 3: GTIN-EPC
2882 p_Lpn_id IN NUMBER DEFAULT NULL, --for p_cross_ref_type =1 only
2883 p_ITEM_ID IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 2 only
2884 p_SERIAL_NUMBER VARCHAR2 DEFAULT NULL, --for p_cross_ref_type = 2 only
2885 p_GTIN IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 3 , for future
2886 p_GTIN_SERIAL IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 3 , for future
2887 p_EPC IN VARCHAR2,
2888 x_return_status OUT nocopy VARCHAR2,
2889 x_return_mesg OUT nocopy VARCHAR2
2890 ) IS
2891
2892 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2893
2894 BEGIN
2895 x_return_status := fnd_api.g_ret_sts_success;
2896
2897 IF l_debug = 1 THEN
2898 trace('p_group_id :'||p_group_id ||' ,'||'p_cross_ref_type :'||p_cross_ref_type);
2899 trace('p_Lpn_id :'||p_lpn_id);
2900 trace('p_ITEM_ID :'||p_item_id||' ,'||'p_SERIAL_NUMBER :'||p_SERIAL_NUMBER);
2901 trace('p_EPC :'||p_epc);
2902 END IF;
2903
2904 IF p_group_id IS NULL OR p_cross_ref_type IS NULL OR p_epc IS NULL THEN
2905 x_return_status := fnd_api.g_ret_sts_error;
2906 x_return_mesg := fnd_message.get_string('WMS','WMS_EPC_MISSING_VALUES');
2907 RETURN;
2908
2909 ELSIF p_lpn_id IS NULL AND
2910 (p_item_id IS NULL OR p_serial_number IS NULL ) AND
2911 (p_gtin IS NULL OR p_gtin_serial IS NULL) THEN
2912
2913 x_return_status := fnd_api.g_ret_sts_error;
2914 x_return_mesg := fnd_message.get_string('WMS','WMS_EPC_MISSING_VALUES');
2915 RETURN;
2916 END IF;
2917
2918 INSERT INTO wms_epc( group_id,
2919 cross_ref_type,
2920 epc_rule_type_id,
2921 lpn_id,
2922 serial_number,
2923 inventory_item_id,
2924 gtin_serial,
2925 gtin,
2926 sscc,
2927 epc,
2928 filter_object_type,
2929 status_code,
2930 status,
2931 creation_date,
2932 created_by,
2933 last_update_date,
2934 last_updated_by,
2935 last_update_login,
2936 epc_id,
2937 epc_rule_id
2938 ) VALUES (P_group_id,
2939 p_cross_ref_type,
2940 -1, -- epc_rule_type_id:populated -1 FOR outside party
2941 p_lpn_id,
2942 p_serial_number,
2943 p_ITEM_ID,
2944 p_GTIN_SERIAL,
2945 P_gtin,
2946 NULL,
2947 P_epc,
2948 null,--filter_object_type
2949 'S',
2950 'IMPORTED',
2951 Sysdate,
2952 fnd_global.user_id,
2953 Sysdate,
2954 fnd_global.user_id,
2955 fnd_global.user_id,
2956 NULL, --epc_id NOT used post R12
2957 NULL); --epc_rule_id NOT used post R12
2958 --DO NOT COMMIT
2959
2960 EXCEPTION
2961 WHEN OTHERS THEN
2962 x_return_status := fnd_api.g_ret_sts_error;
2963 x_return_mesg := Sqlerrm;
2964
2965 IF l_debug = 1 THEN
2966 trace('Exception in populate_outside_epc');
2967 trace('ERROR CODE = ' || SQLCODE);
2968 trace('ERROR MESSAGE = ' || SQLERRM);
2969 END IF;
2970
2971 END populate_outside_epc;
2972
2973
2974 /* NOT USED. Coded in R12 but not used
2975 FUNCTION is_epc_enabled(p_org_id IN NUMBER) RETURN VARCHAR2 IS
2976
2977 l_is_epc_enabled NUMBER :=0;
2978
2979 BEGIN
2980
2981 IF g_epc_org_id.count > 0 then
2982
2983 FOR i IN g_epc_org_id.FIRST..g_epc_org_id.last LOOP
2984 IF g_epc_org_id(i) = p_org_id then
2985 RETURN 'Y';
2986 END IF;
2987 END LOOP;
2988 END IF;
2989
2990 BEGIN
2991 SELECT 1
2992 INTO l_is_epc_enabled
2993 FROM mtl_parameters
2994 WHERE organization_id = p_org_id
2995 AND Nvl(epc_generation_enabled_flag, 'N') = 'Y';
2996 EXCEPTION
2997 WHEN no_data_found then
2998 l_is_epc_enabled :=0;
2999 WHEN others THEN
3000 l_is_epc_enabled :=0;
3001 END;
3002
3003 IF l_is_epc_enabled = 1 THEN
3004 g_epc_org_id(nvl(g_epc_org_id.count,0) + 1 ) := p_org_id;
3005 RETURN 'Y';
3006 ELSE
3007 RETURN 'N';
3008 END IF;
3009
3010
3011 END is_epc_enabled;
3012 */
3013
3014
3015 END wms_epc_pvt;
[Home] [Help]