[Home] [Help]
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;
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:
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
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
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
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
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;
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
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
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
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
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,
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,
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,
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,
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);
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,
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,
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,
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,
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:
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,
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,
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;
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
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:
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
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.
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 )) ;
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
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);
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:
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,
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
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
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:
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;
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
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:
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,
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
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,
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:
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,
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
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);
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);
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,
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,
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);
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,
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,
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,
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,
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:
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,
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,
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;
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
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:
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
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.
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 )) ;
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
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);
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:
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,
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
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
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:
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;
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
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:
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,
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
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,
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:
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,
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
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);
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);
4394: END is_epc_enabled;
4395: */
4396:
4397:
4398: END wms_epc_pvt;