[Home] [Help]
PACKAGE BODY: APPS.INV_LOT_SERIAL_UPGRADE
Source
4 /**************************
1 PACKAGE BODY INV_LOT_SERIAL_UPGRADE AS
2 /* $Header: INVLSUGB.pls 120.2 2005/06/11 08:44:29 appldev $ */
3
5 * Private API *
6 **************************/
7
8 /* Debug */
9 PROCEDURE trace(p_msg IN VARCHAR2, p_level IN NUMBER DEFAULT 4) IS
10 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
11 BEGIN
12 IF (l_debug = 1) THEN
13 inv_trx_util_pub.trace(p_msg, 'UPGRADE_LOT_SER', 1);
14 END IF;
15 --dbms_output.put_line(p_msg);
16 END trace;
17
18 /* Private API to upgrade the lot numbers*/
19 PROCEDURE UPGRADE_LOT(
20 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
21 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
22 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
23 , x_upgrade_count OUT NOCOPY /* file.sql.39 change */ NUMBER
24 , p_organization_id IN NUMBER DEFAULT NULL
25 ) IS
26
27 CURSOR c_lot(p_org_id NUMBER) IS
28 SELECT organization_id, inventory_item_id, lot_number
29 FROM mtl_lot_numbers
30 WHERE organization_id = nvl(p_org_id, organization_id)
31 AND lot_attribute_category IS NULL
32 ORDER BY organization_id, inventory_item_id;
33
34 l_lot_count NUMBER;
35 l_upgrade_count NUMBER;
36 l_context_code VARCHAR2(30);
37 l_attribute_default inv_lot_sel_attr.lot_sel_attributes_tbl_type;
38 l_null_attribute inv_lot_sel_attr.lot_sel_attributes_tbl_type;
39 l_attribute_default_count NUMBER := 0;
40 l_update_count NUMBER := 0;
41 l_return_status VARCHAR2(10);
42 l_msg_count NUMBER := 0;
43 l_msg_data VARCHAR2(2000);
44 l_previous_item_id NUMBER := -1;
45 l_previous_org_id NUMBER := -1;
46
47
48 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
49 BEGIN
50 IF (l_debug = 1) THEN
51 trace('Upgrading Lot numbers, org ='|| p_organization_id);
52 END IF;
53
54 l_lot_count := 0;
55 l_upgrade_count := 0;
56 x_return_status := fnd_api.g_ret_sts_success ;
57
58 l_previous_item_id := -1;
59 l_previous_org_id := -1;
60 FOR v_lot IN c_lot(p_organization_id) LOOP
61 --trace('In Lot loop, org, item, lot: '|| v_lot.organization_id
62 -- ||','||v_lot.inventory_item_id||','||v_lot.lot_number);
63
64 -- Step 1. Get the context code for the lot
65 -- we only need to run this for every org/item, not every lot
66 IF((v_lot.inventory_item_id <> l_previous_item_id) OR
67 (v_lot.organization_id <> l_previous_org_id)) THEN
68 INV_LOT_SEL_ATTR.GET_CONTEXT_CODE(
69 context_value => l_context_code
70 , org_id => v_lot.organization_id
71 , item_id => v_lot.inventory_item_id
72 , flex_name => LOT_FLEX_NAME);
73 l_previous_org_id := v_lot.organization_id;
74 l_previous_item_id := v_lot.inventory_item_id;
75 END IF;
76 --trace('Got context code:' || l_context_code);
77 IF l_context_code IS NOT NULL THEN
78 l_lot_count := l_lot_count + 1;
79
80 -- Step 2. Get the default attribute for this lot
81 INV_LOT_SEL_ATTR.GET_DEFAULT(
82 x_attributes_default => l_attribute_default
83 , x_attributes_default_count => l_attribute_default_count
84 , x_return_status => l_return_status
85 , x_msg_count => l_msg_count
86 , x_msg_data => l_msg_data
87 , p_table_name => LOT_TABLE_NAME
88 , p_attributes_name => LOT_FLEX_NAME
89 , p_inventory_item_id => v_lot.inventory_item_id
90 , p_organization_id => v_lot.organization_id
91 , p_lot_serial_number => v_lot.lot_number
92 , p_attributes => l_null_attribute);
93 --trace('Got default attributes, status, count '
94 -- || l_return_status || ',' || l_attribute_default_count);
95 IF l_return_status <> 'S' THEN
96 IF (l_debug = 1) THEN
97 trace('Error in getting default attr, can not upgrade lot '||v_lot.lot_number
98 || ',org:'||v_lot.organization_id||',item:'||v_lot.inventory_item_id);
99 END IF;
100 ELSE
101 -- Step 3. Update lot with the default attribute
102 UPDATE_LOT_SERIAL_ATTR(
103 x_return_status => l_return_status
104 , x_msg_count => l_msg_count
108 , p_organization_id => v_lot.organization_id
105 , x_msg_data => l_msg_data
106 , x_update_count => l_update_count
107 , p_lot_serial_option => OPTION_LOT
109 , p_inventory_item_id => v_lot.inventory_item_id
110 , p_lot_serial_number => v_lot.lot_number
111 , p_attribute_category => l_context_code
112 , p_attributes => l_attribute_default);
113 --trace('Updated lot wtih attributes, status, count '
114 -- || l_return_status || ',' || l_update_count);
115 IF l_return_status <> 'S' THEN
116 IF (l_debug = 1) THEN
117 trace('Error in updating lot with default attributes, can not upgrade lot '
118 ||v_lot.lot_number|| ',org:'||v_lot.organization_id||',item:'||v_lot.inventory_item_id);
119 END IF;
120 ELSE
121 IF (l_debug = 1) THEN
122 trace('Successfully updated lot:'||v_lot.lot_number
123 || ',org:'||v_lot.organization_id||',item:'||v_lot.inventory_item_id);
124 END IF;
125 l_upgrade_count := l_upgrade_count + 1;
126 END IF; -- Step 3. update lot
127 END IF; -- Step 2. get default attribute
128 END IF; -- Step 1. get context code
129 END LOOP;
130 IF (l_debug = 1) THEN
131 trace('Upgraded ' || l_upgrade_count || ' lot numbers out of '
132 || l_lot_count || ' lot numbers found.');
133 END IF;
134
135 EXCEPTION
136 WHEN others THEN
137 IF (l_debug = 1) THEN
138 trace('Error in UPGRADE_LOT ');
139 END IF;
140 x_return_status := fnd_api.g_ret_sts_error;
141 END UPGRADE_LOT;
142
143 /* Private API to upgrade the serial numbers*/
144 PROCEDURE UPGRADE_SERIAL(
145 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
146 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
147 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
148 , x_upgrade_count OUT NOCOPY /* file.sql.39 change */ NUMBER
149 , p_organization_id IN NUMBER DEFAULT NULL
150 ) IS
151 CURSOR c_serial(p_org_id NUMBER) IS
152 SELECT current_organization_id, inventory_item_id, serial_number
153 FROM mtl_serial_numbers
154 WHERE current_organization_id = nvl(p_org_id, current_organization_id)
155 AND serial_attribute_category IS NULL
156 AND current_status in (3,4,5)
157 ORDER BY current_organization_id, inventory_item_id;
158
159 l_serial_count NUMBER;
160 l_upgrade_count NUMBER;
161 l_context_code VARCHAR2(30);
162 l_attribute_default inv_lot_sel_attr.lot_sel_attributes_tbl_type;
166 l_return_status VARCHAR2(10);
163 l_null_attribute inv_lot_sel_attr.lot_sel_attributes_tbl_type;
164 l_attribute_default_count NUMBER := 0;
165 l_update_count NUMBER := 0;
167 l_msg_count NUMBER := 0;
168 l_msg_data VARCHAR2(2000);
169 l_previous_item_id NUMBER := -1;
170 l_previous_org_id NUMBER := -1;
171
172
173 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
174 BEGIN
175 --trace('Upgrading Serial numbers, org, item='|| p_organization_id
176 -- || ',' || p_inventory_item_id);
177
178 l_serial_count := 0;
179 l_upgrade_count := 0;
180 x_return_status := fnd_api.g_ret_sts_success ;
181
182 l_previous_item_id := -1;
183 l_previous_org_id := -1;
184 FOR v_serial IN c_serial(p_organization_id) LOOP
185 --trace('In Serial loop, org, item, serial: '|| v_serial.current_organization_id
186 -- ||','||v_serial.inventory_item_id||','||v_serial.serial_number);
187
188 -- Step 1. Get the context code for the serial
189 -- we only need to run this for every org/item, not every serial
190 IF((v_serial.inventory_item_id <> l_previous_item_id) OR
191 (v_serial.current_organization_id <> l_previous_org_id)) THEN
192 INV_LOT_SEL_ATTR.GET_CONTEXT_CODE(
193 context_value => l_context_code
194 , org_id => v_serial.current_organization_id
195 , item_id => v_serial.inventory_item_id
196 , flex_name => SERIAL_FLEX_NAME);
197 l_previous_item_id := v_serial.inventory_item_id;
198 l_previous_org_id := v_serial.current_organization_id;
199 END IF;
200 --trace('Got context code:' || l_context_code);
201
202 IF l_context_code IS NOT NULL THEN
203 l_serial_count := l_serial_count + 1;
204 -- Step 2. Get the default attribute for this serial
205 INV_LOT_SEL_ATTR.GET_DEFAULT(
206 x_attributes_default => l_attribute_default
207 , x_attributes_default_count => l_attribute_default_count
208 , x_return_status => l_return_status
209 , x_msg_count => l_msg_count
210 , x_msg_data => l_msg_data
211 , p_table_name => SERIAL_TABLE_NAME
212 , p_attributes_name => SERIAL_FLEX_NAME
213 , p_inventory_item_id => v_serial.inventory_item_id
214 , p_organization_id => v_serial.current_organization_id
215 , p_lot_serial_number => v_serial.serial_number
216 , p_attributes => l_null_attribute);
217 --trace('Got default attributes, status, count '
218 -- || l_return_status || ',' || l_attribute_default_count);
219 IF l_return_status <> 'S' THEN
220 IF (l_debug = 1) THEN
221 trace('Error in getting default attributes, can not upgrade serial '||v_serial.serial_number
222 || ',org:'||v_serial.current_organization_id||',item:'||v_serial.inventory_item_id);
223 END IF;
224 ELSE
225 -- Step 3. Update serial with the default attribute
229 , x_msg_data => l_msg_data
226 UPDATE_LOT_SERIAL_ATTR(
227 x_return_status => l_return_status
228 , x_msg_count => l_msg_count
230 , x_update_count => l_update_count
231 , p_lot_serial_option => OPTION_SERIAL
232 , p_organization_id => v_serial.current_organization_id
233 , p_inventory_item_id => v_serial.inventory_item_id
234 , p_lot_serial_number => v_serial.serial_number
235 , p_attribute_category => l_context_code
236 , p_attributes => l_attribute_default);
237 --trace('Updated serial wtih attributes, status, count '
238 -- || l_return_status || ',' || l_update_count);
239 IF l_return_status <> 'S' THEN
240 IF (l_debug = 1) THEN
241 trace('Error in updating serial with default attributes, can not upgrade serial '
242 ||v_serial.serial_number|| ',org:'||v_serial.current_organization_id
243 ||',item:'||v_serial.inventory_item_id);
244 END IF;
245 ELSE
246 IF (l_debug = 1) THEN
247 trace('Successfully updated serial:'||v_serial.serial_number
248 || ',org:'||v_serial.current_organization_id ||',item:'||v_serial.inventory_item_id);
249 END IF;
250 l_upgrade_count := l_upgrade_count + 1;
251 END IF; -- Step 3. update serial
252 END IF; -- Step 2. get default attribute
253 END IF; -- Step 1. get context code
254 END LOOP;
255 IF (l_debug = 1) THEN
256 trace('Upgraded ' || l_upgrade_count || ' serial numbers out of '
257 || l_serial_count || ' serial numbers found.');
258 END IF;
259
260 EXCEPTION
261 WHEN others THEN
262 IF (l_debug = 1) THEN
263 trace('Error in UPGRADE_SERIAL ');
264 END IF;
265 x_return_status := fnd_api.g_ret_sts_error;
266 END UPGRADE_SERIAL;
267
268 /**************************
269 * Public API *
270 **************************/
271
272 /* Update lot/serial number with the given attribute record
273 Input Parameter:
274 p_lot_serial_option: specify update lot or serial
275 possible value: OPTION_LOT(1), OPTION_SERIAL(2)*/
276 PROCEDURE UPDATE_LOT_SERIAL_ATTR(
277 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
278 , x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
279 , x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
280 , x_update_count OUT NOCOPY /* file.sql.39 change */ NUMBER
281 , p_lot_serial_option IN NUMBER
282 , p_organization_id IN NUMBER
283 , p_inventory_item_id IN NUMBER
284 , p_lot_serial_number IN VARCHAR2
285 , p_attribute_category IN VARCHAR2
286 , p_attributes IN inv_lot_sel_attr.lot_sel_attributes_tbl_type
287 )IS
288
289 l_update_cur INTEGER;
290 l_update_stmt VARCHAR2(1000);
291 i BINARY_INTEGER;
292 l_rowupdated NUMBER;
293 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
294 BEGIN
295 --trace('In UPDATE_LOT_SERIAL, lot/ser option, org, item, lot/ser, p_attr_count ');
296 --trace(' ' || p_lot_serial_option || ',' || p_organization_id
297 -- ||','|| p_inventory_item_id || ',' || p_lot_serial_number
298 -- ||','|| p_attributes.count);
299
300 x_return_status := fnd_api.g_ret_sts_success ;
301
302 -- Construct the update statement
303 l_update_stmt := 'UPDATE ';
304 IF p_lot_serial_option = OPTION_LOT THEN
305 l_update_stmt := l_update_stmt || LOT_TABLE_NAME ||
306 ' SET lot_attribute_category = :attr_category ';
307 ELSE
308 l_update_stmt := l_update_stmt || SERIAL_TABLE_NAME ||
309 ' SET serial_attribute_category = :attr_category ';
310 END IF;
311
312 FOR i IN 1..p_attributes.count LOOP
313 l_update_stmt := l_update_stmt || ' , ' || p_attributes(i).column_name || '=';
314
315 IF p_attributes(i).column_type = 'NUMBER' THEN
316 IF length(rtrim(p_attributes(i).column_value)) IS NULL THEN
317 l_update_stmt := l_update_stmt || 'null';
318 ELSE
319 l_update_stmt := l_update_stmt || p_attributes(i).column_value;
320 END IF;
321 ELSIF p_attributes(i).column_type in ('VARCHAR2', 'DATE') THEN
322 l_update_stmt := l_update_stmt || '''' || p_attributes(i).column_value|| '''';
323 ELSE
324 l_update_stmt := l_update_stmt || p_attributes(i).column_value;
325 END IF;
326 END LOOP;
327
328 IF p_lot_serial_option = OPTION_LOT THEN
329 l_update_stmt := l_update_stmt ||
330 ' WHERE organization_id=:org_id AND inventory_item_id=:item_id AND lot_number=:lot_serial';
331 ELSE
332 l_update_stmt := l_update_stmt ||
333 ' WHERE current_organization_id=:org_id AND inventory_item_id=:item_id AND serial_number=:lot_serial';
334 END IF;
335
336 -- Open dynamic SQL cursor
337 l_update_cur := DBMS_SQL.OPEN_CURSOR;
338 -- Parse statement
339 DBMS_SQL.PARSE(l_update_cur, l_update_stmt, DBMS_SQL.v7);
340 -- Bind variables
341 DBMS_SQL.BIND_VARIABLE(l_update_cur, ':attr_category', p_attribute_category);
342 DBMS_SQL.BIND_VARIABLE(l_update_cur, ':org_id', p_organization_id);
343 DBMS_SQL.BIND_VARIABLE(l_update_cur, ':item_id', p_inventory_item_id);
344 DBMS_SQL.BIND_VARIABLE(l_update_cur, ':lot_serial', p_lot_serial_number);
345 -- Execute statement
346 l_rowupdated := DBMS_SQL.EXECUTE(l_update_cur);
347
348 IF l_rowupdated >= 1 THEN
349 x_update_count := l_rowupdated;
350 ELSE
351 IF (l_debug = 1) THEN
352 trace(' No rows updated , error in update lot/serial ' ||p_lot_serial_number
353 || ',org:'||p_organization_id ||',item:'||p_inventory_item_id);
354 END IF;
355 RAISE fnd_api.g_exc_error;
356 END IF;
357
358 -- Close the cursor
359 DBMS_SQL.CLOSE_CURSOR(l_update_cur);
360
361 EXCEPTION
365 trace('Error in update lot/serial '|| p_lot_serial_number
362 WHEN others THEN
363 DBMS_SQL.CLOSE_CURSOR(l_update_cur);
364 IF (l_debug = 1) THEN
366 || ',org:'||p_organization_id ||',item:'||p_inventory_item_id);
367 END IF;
368 x_return_status := fnd_api.g_ret_sts_error;
369
370 END UPDATE_LOT_SERIAL_ATTR;
371
372 /* Upgrade procedure to be called by the concurrent program
373 which follows the concurrent program API standard
374 Input Parameter:
375 p_organization_id: specify an organization or all orgs (if null)*/
376 PROCEDURE UPGRADE_LOT_SERIAL(
377 x_retcode OUT NOCOPY /* file.sql.39 change */ NUMBER
378 , x_errbuf OUT NOCOPY /* file.sql.39 change */ VARCHAR2
379 , p_organization_id IN NUMBER := NULL
380 )IS
381 l_return_status VARCHAR2(100);
382 l_msg_count NUMBER;
383 l_msg_data VARCHAR2(2000);
384 l_upgrade_count NUMBER := 0;
385 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
386 BEGIN
387 IF (l_debug = 1) THEN
388 trace('**** Upgrade Lot and Serial('||
389 to_char(sysdate, 'DD-MON-YYYY HH:MI:SS')||') ****');
390 trace(' org_id='|| p_organization_id);
391 END IF;
392
393 x_retcode := 0;
394
395 IF (l_debug = 1) THEN
396 trace('Upgrading Lot');
397 END IF;
398 UPGRADE_LOT(
399 l_return_status
400 , l_msg_count
401 , l_msg_data
402 , l_upgrade_count
403 , p_organization_id);
404 IF (l_debug = 1) THEN
405 trace('Upgraded Lot, return_status: '|| l_return_status);
406 END IF;
407
408 IF (l_debug = 1) THEN
409 trace('Upgrading Serial');
410 END IF;
411 UPGRADE_SERIAL(
412 l_return_status
413 , l_msg_count
414 , l_msg_data
415 , l_upgrade_count
416 , p_organization_id);
417 IF (l_debug = 1) THEN
418 trace('Upgraded Serial, return_status: '|| l_return_status);
419 trace('End of upgrade lot/serial');
420 END IF;
421
422 EXCEPTION
423 WHEN others THEN
424 IF (l_debug = 1) THEN
425 trace('Error in upgrade_lot_serial');
426 END IF;
427 x_retcode := 2;
428
429 END UPGRADE_LOT_SERIAL;
430
431 END INV_LOT_SERIAL_UPGRADE;