[Home] [Help]
PACKAGE BODY: APPS.WMS_UCC_128_PVT
Source
1 PACKAGE BODY WMS_UCC_128_PVT AS
2 /* $Header: WMSUCCSB.pls 120.0.12010000.2 2008/08/04 19:12:16 bvanjaku ship $ */
3
4 -- Global constant holding the package name
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WMS_UCC_128_PVT';
6 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: WMSUCCSB.pls 120.0.12010000.2 2008/08/04 19:12:16 bvanjaku ship $';
7
8 g_gtin_cross_ref_type VARCHAR2(25) := fnd_profile.value('INV:GTIN_CROSS_REFERENCE_TYPE');
9 g_gtin_code_length NUMBER := 14;
10
11 PROCEDURE print_debug ( msg in varchar2, p_level NUMBER := 4 ) IS
12 BEGIN
13 INV_TRX_UTIL_PUB.TRACE(msg, 'WMS_UCC_128_PVT', p_level);
14 --dbms_output.put_line(msg);
15 END;
16
17 -- ======================================================================
18 -- PROCEDURE get_xref_values
19 -- ======================================================================
20 -- Purpose
21 -- Retrieve crossreference UOM and Revision base on cross reference type
22 -- and number .
23 -- Input Parameters
24 -- p_org_id
25 -- p_cross_reference
26 --
27 -- Output Parameters
28 -- x_uom_code return a revision if define , otherwise NULL
29 -- x_revision return a revision if define , otherwise NULL
30 -- x_item_id returns matching item-id of p_cross_reference value
31 --
32 PROCEDURE get_xref_values
33 (x_uom_code OUT NOCOPY VARCHAR2,
34 x_revision OUT NOCOPY VARCHAR2,
35 x_item_id OUT NOCOPY NUMBER,
36 p_org_id IN NUMBER,
37 p_cross_reference IN VARCHAR2)
38 IS
39 l_cross_ref varchar2(204);
40
41 BEGIN
42 print_debug('Begin get_xref_values: p_org_id:'||p_org_id||',p_xref='||p_cross_reference);
43
44 SELECT mx.inventory_item_id, mx.uom_code, mr.revision
45 INTO x_item_id, x_uom_code, x_revision
46 FROM MTL_CROSS_REFERENCES mx, MTL_ITEM_REVISIONS_B mr
47 WHERE mx.organization_id = mr.organization_id(+)
48 and mx.inventory_item_id = mr.inventory_item_id(+)
49 and mx.revision_id = mr.revision_id(+)
50 and mx.cross_reference_type = g_gtin_cross_ref_type
51 and mx.cross_reference = p_cross_reference
52 and nvl(mx.organization_id, p_org_id) = p_org_id
53 and rownum = 1;
54
55 print_debug('x_item='||x_item_id||',x_uom='||x_uom_code||',x_rev='||x_revision);
56
57 EXCEPTION
58 WHEN OTHERS THEN
59 x_uom_code := null;
60 x_revision := null;
61 x_item_id := null;
62 END get_xref_values;
63
64 -- ======================================================================
65 -- PROCEDURE get_xref_values
66 -- ======================================================================
67 -- Purpose
68 -- This procedure is another version of get_xref_values which uses concatenated_segments as
69 -- one of the input parameters instead of zero padded concatenated segments
70 -- Retrieve crossreference UOM and Revision base on cross reference type
71 -- and number .
72 -- Input Parameters
73 -- p_org_id
74 -- p_concatenated_segments -
75 --
76 -- Output Parameters
77 -- x_uom_code return a revision if define , otherwise NULL
78 -- x_revision return a revision if define , otherwise NULL
79 -- x_item_id returns matching item-id of p_cross_reference value
80
81 PROCEDURE get_xref_values
82 (x_uom_code OUT NOCOPY VARCHAR2,
83 x_revision OUT NOCOPY VARCHAR2,
84 x_concatenated_segments OUT NOCOPY VARCHAR2,
85 p_org_id IN NUMBER,
86 p_cross_reference IN VARCHAR2)
87 IS
88 l_cross_ref varchar2(204);
89
90 BEGIN
91 print_debug('Begin get_xref_values: p_org_id:'||p_org_id||',p_cross_reference='||p_cross_reference||',g_gtin_cross_ref_type='||g_gtin_cross_ref_type);
92
93 SELECT msi.concatenated_segments, mx.uom_code, mr.revision
94 INTO x_concatenated_segments, x_uom_code, x_revision
95 FROM MTL_CROSS_REFERENCES mx, MTL_ITEM_REVISIONS_B mr,
96 mtl_system_items_kfv msi
97 WHERE mx.organization_id = mr.organization_id(+)
98 and mx.inventory_item_id = mr.inventory_item_id(+)
99 and mx.revision_id = mr.revision_id(+)
100 and mx.cross_reference_type = g_gtin_cross_ref_type
101 and mx.cross_reference LIKE lpad(Rtrim(p_cross_reference,'%'),
102 g_gtin_code_length, '00000000000000')
103 and nvl(mx.organization_id, p_org_id) = p_org_id
104 and msi.organization_id = p_org_id
105 and msi.inventory_item_id = mx.inventory_item_id
106 and rownum = 1;
107
108 print_debug('x_concatenated_segments='||x_concatenated_segments||',x_uom='||x_uom_code||',x_rev='||x_revision);
109
110 EXCEPTION
111 WHEN OTHERS THEN
112 x_uom_code := null;
113 x_revision := null;
114 x_concatenated_segments := null;
115 END get_xref_values;
116
117
118 -- ======================================================================
119 -- FUNCTION GenCheckDigit
120 -- ======================================================================
121 -- Purpose
122 -- Generate the CheckDigit for LPN using Modulo 10 Check Digit Algorithm
123 -- 1. Consider the right most digit of the code to be in an 'even'
124 -- position and assign odd/even to each character moving from right to
125 -- left.
126 -- 2. Sum the digits in all odd positions
127 -- 3. Sum the digits in all even positions and multiply the result by 3 .
128 -- 4. Sum the totals calculated in steps 2 and 3.
129 -- 5. The Check digit is the number which, when added to the totals
130 -- calculated in step 4, result in a number evenly divisible by 10.
131
132 -- Input Parameters
133 -- P_lpn_str (Required)
134 --
135 -- Output value :
136 -- Valid single check digit .
137 --
138
139 FUNCTION GenCheckDigit(p_lpn_str IN VARCHAR2)
140 RETURN NUMBER
141 IS
142
143 L NUMBER;
144 I NUMBER;
145 l_evensum NUMBER := 0;
146 l_oddsum NUMBER := 0;
147 l_total NUMBER := 0;
148 l_checkdigit NUMBER := 0;
149 l_remainder NUMBER := 0;
150
151 l_length NUMBER;
152 l_lpn_str varchar2(255);
153 BEGIN
154 print_debug('Begin GenCheckDigit()');
155 print_debug('p_lpn_str : ' || p_lpn_str);
156 L := 0;
157 l_lpn_str := rtrim(p_lpn_str);
158 l_length := LENGTH(l_lpn_str);
159
160 FOR I IN REVERSE 1..l_length
161 LOOP
162 -- print_debug('l_lpn_str(' || I || ') : ' ||
163 -- to_number(substr(l_lpn_str,I,1)));
164 IF (mod(L,2) = 0) THEN
165 l_Evensum := l_Evensum + to_number(substr(l_lpn_str,I,1));
166 ELSE
167 l_Oddsum := l_Oddsum + to_number(substr(l_lpn_str,I,1));
168 END IF;
169 L := L + 1;
170 END LOOP;
171
172 l_Evensum := l_Evensum * 3;
173 l_Total := l_Evensum + l_Oddsum;
174 l_remainder := mod(l_total,10);
175 print_debug('l_total:' || l_total || ' l_remainder : ' || l_remainder);
176 IF (l_remainder > 0) THEN
177 l_checkdigit := 10 - l_remainder;
178 END IF;
179 print_debug('l_checkdigit : ' || l_checkdigit);
180 RETURN l_checkdigit;
181
182 END GenCheckDigit;
183
184
185 PROCEDURE Get_UCC_128_Attributes (
186 x_return_status OUT NOCOPY VARCHAR2
187 , x_ucc_128_attributes IN OUT NOCOPY UCC_128_Attributes
188 , p_org IN INV_VALIDATE.ORG := NULL
189 ) IS
190 l_api_name CONSTANT VARCHAR2(30) := 'Get_UCC_128_Attributes';
191 l_api_version CONSTANT NUMBER := 1.0;
192 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
193 l_progress VARCHAR2(10) := '0';
194
195 BEGIN
196 -- Initialize API return status to success
197 x_return_status := fnd_api.g_ret_sts_success;
198
199 IF ( l_debug = 1 ) THEN
200 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
201 print_debug('xorgid='||x_ucc_128_attributes.organization_id||' orgid='||p_org.organization_id||' sfxflag='||p_org.ucc_128_suffix_flag||' length='||p_org.total_lpn_length, 4);
202 END IF;
203
204 IF ( p_org.organization_id IS NOT NULL ) THEN
205 l_progress := '100';
206 x_ucc_128_attributes.organization_id := p_org.organization_id;
207 x_ucc_128_attributes.total_lpn_length := p_org.total_lpn_length;
208 x_ucc_128_attributes.ucc_128_suffix_flag := p_org.ucc_128_suffix_flag;
209 l_progress := '110';
210 ELSE
211 l_progress := '200';
212 SELECT total_lpn_length
213 , ucc_128_suffix_flag
214 INTO x_ucc_128_attributes.total_lpn_length
215 , x_ucc_128_attributes.ucc_128_suffix_flag
216 FROM mtl_parameters
217 WHERE organization_id = x_ucc_128_attributes.organization_id;
218 l_progress := '210';
219 END IF;
220
221 IF ( l_debug = 1 ) THEN
222 print_debug(l_api_name||' Exited sfxflag='||x_ucc_128_attributes.ucc_128_suffix_flag||' length='||x_ucc_128_attributes.total_lpn_length, 1);
223 END IF;
224 EXCEPTION
225 WHEN OTHERS THEN
226 IF (l_debug = 1) THEN
227 print_debug(l_api_name ||' Error l_progress=' || l_progress);
228 IF ( SQLCODE IS NOT NULL ) THEN
229 print_debug('SQL error: ' || SQLERRM(SQLCODE));
230 END IF;
231 END IF;
232 x_return_status := fnd_api.g_ret_sts_error;
233 END Get_UCC_128_Attributes;
234
235 END WMS_UCC_128_PVT;