1 PACKAGE WMS_EPC_PVT AS
2 /* $Header: WMSEPCVS.pls 120.6 2005/07/08 19:15:07 satkumar noship $ */
3
4 -- R12
5 G_PROFILE_GTIN VARCHAR2(100) := NULL;
6
7 /* NOT USED. Coded in R12 but not used
8 type l_num_tbl is table of number index by binary_integer;
9 g_EPC_ORG_id l_num_tbl;
10 */
11
12 TYPE epc_rule_types IS RECORD
13 (
14 type_id NUMBER,
15 type_name VARCHAR2(100),
16 partition_value NUMBER,
17 category_id NUMBER
18 );
19
20
21 TYPE epc_rule_types_tbl IS TABLE OF epc_rule_types INDEX BY BINARY_INTEGER;
22
23
24
25 --constants TO IDENTIFY OBJECT TYPE
26
27 WMS_EPC_OTHER_OBJ_TYPE CONSTANT NUMBER := 0;
28 WMS_EPC_ITEM_OBJ_TYPE CONSTANT NUMBER := 1;
29 WMS_EPC_INNER_PACK_OBJ_TYPE CONSTANT NUMBER := 2;
30 WMS_EPC_CASE_OBJ_TYPE CONSTANT NUMBER := 3;
31 WMS_EPC_PALLET_OBJ_TYPE CONSTANT NUMBER := 4;
32 WMS_EPC_OTH5_OBJ_TYPE CONSTANT NUMBER := 5;
33 WMS_EPC_OTH6_OBJ_TYPE CONSTANT NUMBER := 6;
34 WMS_EPC_OTH7_OBJ_TYPE CONSTANT NUMBER := 7;
35
36
37 PROCEDURE generate_epc
38 (p_org_id IN NUMBER,
39 p_label_type_id IN NUMBER, /* VALID VALUES 1,2,3,4,5*/
40 p_group_id IN NUMBER,
41 p_label_format_id IN NUMBER,
42 p_item_id IN NUMBER DEFAULT NULL, --For Material Label: 1
43 p_txn_qty IN NUMBER DEFAULT null, --For Material Label: 1
44 p_txn_uom IN VARCHAR2 DEFAULT NULL, --For Material Label: 1
45 p_label_request_id IN NUMBER,
46 p_business_flow_code IN NUMBER DEFAULT NULL,
47 x_epc OUT nocopy VARCHAR2,
48 x_return_status OUT nocopy VARCHAR2,
49 x_return_mesg OUT nocopy VARCHAR2
50 );
51
52
53 --To be called from rcv_transaction_processor/3rd party to populate from inerface tables
54 PROCEDURE populate_outside_epc
55 (p_group_id IN NUMBER , --obtained from WMS_EPC_S2.nextval by calling API
56 p_cross_ref_type IN NUMBER, --1: LPN-EPC , 2: ITEM_SERIAL-EPC , 3: GTIN-EPC
57 p_Lpn_id IN NUMBER DEFAULT NULL, --for p_cross_ref_type =1 only
58 p_ITEM_ID IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 2 only
59 p_SERIAL_NUMBER VARCHAR2 DEFAULT NULL, --for p_cross_ref_type = 2 only
60 p_GTIN IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 3 , for future
61 p_GTIN_SERIAL IN NUMBER DEFAULT NULL, --for p_cross_ref_type = 3 , for future
62 p_EPC IN VARCHAR2,
63 x_return_status OUT nocopy VARCHAR2,
64 x_return_mesg OUT nocopy VARCHAR2
65 );
66
67 FUNCTION is_lpn_standard(p_lpn_id NUMBER) RETURN NUMBER;
68
69
70 FUNCTION bin2dec (binval in char) RETURN NUMBER;
71 FUNCTION dec2bin (N in number) RETURN VARCHAR2;
72 FUNCTION oct2dec (octval in char) RETURN NUMBER;
73 FUNCTION dec2oct (N in number) RETURN VARCHAR2;
74 FUNCTION hex2dec (hexval in char) RETURN NUMBER;
75 FUNCTION dec2hex (N in number) RETURN VARCHAR2;
76
77
78 END wms_epc_pvt;