[Home] [Help]
PACKAGE BODY: APPS.WMS_CONTAINER_PUB
Source
1 PACKAGE BODY wms_container_pub AS
2 /* $Header: WMSCONTB.pls 120.5.12010000.5 2010/01/28 11:33:50 schiluve ship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_CONTAINER_PUB';
6 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: WMSCONTB.pls 120.5.12010000.5 2010/01/28 11:33:50 schiluve ship $';
7
8 -- Global value storing the transaction histories for pack/unpack operations
9 g_history_table transaction_history;
10
11 -- Various debug levels
12 G_ERROR CONSTANT NUMBER := 1;
13 G_INFO CONSTANT NUMBER := 5;
14 G_MESSAGE CONSTANT NUMBER := 9;
15
16 PROCEDURE mdebug(msg IN VARCHAR2, LEVEL NUMBER := G_MESSAGE) IS
17 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
18 BEGIN
19 --DBMS_OUTPUT.put_line(msg);
20 IF (l_debug = 1) THEN
21 inv_trx_util_pub.TRACE(msg, 'WMS_CONTAINER_PUB', LEVEL);
22 END IF;
23 NULL;
24 END;
25
26 /*Bug#2200989. Added local procedure to update the wt and volume
27 of the LPNs in shipping when the corrosponding LPN is modified
28 in WMS_LICENSE_PLATE_NUMBERS. */
29
30 PROCEDURE update_shipping_details(p_lpn_id IN NUMBER, p_gross_weight IN NUMBER, p_net_weight IN NUMBER, p_weight_uom IN VARCHAR2, p_volume IN NUMBER, p_volume_uom IN VARCHAR2) IS
31 CURSOR wsh_lpn_id IS
32 SELECT 1
33 FROM wsh_delivery_details
34 WHERE lpn_id = p_lpn_id;
35
36 --Bug 5190145
37 --Added following cursor to get inventory_item_id from WLPN
38 CURSOR lpn_item_id IS
39 SELECT nvl(inventory_item_id, -99999)
40 FROM wms_license_plate_numbers
41 WHERE lpn_id = p_lpn_id;
42
43 x_return_status VARCHAR2(10);
44 x_msg_count NUMBER;
45 x_msg_data VARCHAR2(6000);
46 l_container_name VARCHAR2(30);
47 l_dummy NUMBER;
48 l_delivery_detail_id NUMBER;
49 l_api_version NUMBER := 1.0;
50
51 --Begin bug 5190145
52 --changed to call WSH_container_grp api
53 --l_changed_attributes wsh_delivery_details_pub.changedattributerectype;
54 l_changed_attributes WSH_CONTAINER_GRP.CHANGEDATTRIBUTETABTYPE;
55 l_lpn_item_id NUMBER := -99999;
56 --End Bug 5190145
57
58 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
59 BEGIN
60 OPEN wsh_lpn_id;
61 FETCH wsh_lpn_id INTO l_dummy;
62
63 IF wsh_lpn_id%FOUND THEN
64 SELECT delivery_detail_id,
65 container_name
66 INTO l_delivery_detail_id,
67 l_container_name
68 FROM wsh_delivery_details
69 WHERE lpn_id = p_lpn_id;
70
71
72 --Begin bug 5190145
73 OPEN lpn_item_id;
74 FETCH lpn_item_id INTO l_lpn_item_id;
75 CLOSE lpn_item_id;
76
77 l_changed_attributes(1).delivery_detail_id := l_delivery_detail_id;
78 l_changed_attributes(1).container_name := l_container_name;
79 l_changed_attributes(1).net_weight := p_net_weight;
80 l_changed_attributes(1).gross_weight := p_gross_weight;
81 l_changed_attributes(1).weight_uom_code := p_weight_uom;
82 l_changed_attributes(1).volume := p_volume;
83 l_changed_attributes(1).volume_uom_code := p_volume_uom;
84 IF (l_lpn_item_id <> -99999) THEN
85 l_changed_attributes(1).inventory_item_id := l_lpn_item_id;
86 END IF;
87
88 IF (l_debug = 1) THEN
89 mdebug('***in Update Shipping Details proc***');
90 mdebug('***delivery_detail_id='|| l_delivery_detail_id);
91 mdebug('***container_name='|| l_container_name);
92 mdebug('***net weight='|| p_net_weight);
93 mdebug('***gross_weight='|| p_gross_weight);
94 mdebug('***weight_uom_code='|| p_weight_uom);
95 mdebug('***volume='|| p_volume);
96 mdebug('***volume_uom_code='|| p_volume_uom);
97 mdebug('***l_lpn_item_id='|| l_lpn_item_id);
98 END IF;
99 --Call the Shipping API to update Container details.
100 wsh_container_grp.update_container(l_api_version, fnd_api.g_false,fnd_api.g_false,fnd_api.g_valid_level_full, x_return_status, x_msg_count, x_msg_data, l_changed_attributes);
101 -- End bug 5190145
102 END IF;
103
104
105 CLOSE wsh_lpn_id;
106 END update_shipping_details;
107
108
109 PROCEDURE Generate_LPN (
110 p_api_version IN NUMBER
111 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
112 , p_commit IN VARCHAR2 := fnd_api.g_false
113 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
114 , x_return_status OUT NOCOPY VARCHAR2
115 , x_msg_count OUT NOCOPY NUMBER
116 , x_msg_data OUT NOCOPY VARCHAR2
117 , p_organization_id IN NUMBER
118 , p_container_item_id IN NUMBER := NULL
119 , p_revision IN VARCHAR2 := NULL
120 , p_lot_number IN VARCHAR2 := NULL
121 , p_from_serial_number IN VARCHAR2 := NULL
122 , p_to_serial_number IN VARCHAR2 := NULL
123 , p_subinventory IN VARCHAR2 := NULL
124 , p_locator_id IN NUMBER := NULL
125 , p_lpn_prefix IN VARCHAR2 := NULL
126 , p_lpn_suffix IN VARCHAR2 := NULL
127 , p_starting_num IN NUMBER := NULL
128 , p_quantity IN NUMBER := 1
129 , p_source IN NUMBER := LPN_CONTEXT_PREGENERATED
130 , p_cost_group_id IN NUMBER := NULL
131 , p_source_type_id IN NUMBER := NULL
132 , p_source_header_id IN NUMBER := NULL
133 , p_source_name IN VARCHAR2 := NULL
134 , p_source_line_id IN NUMBER := NULL
135 , p_source_line_detail_id IN NUMBER := NULL
136 , p_total_length IN NUMBER := NULL
137 , p_ucc_128_suffix_flag IN VARCHAR2 := NULL
138 , p_lpn_id_out OUT NOCOPY NUMBER
139 , p_lpn_out OUT NOCOPY VARCHAR2
140 , p_process_id OUT NOCOPY NUMBER
141 ) IS
142 BEGIN
143 Generate_LPN (
144 p_api_version => p_api_version
145 , p_init_msg_list => p_init_msg_list
146 , p_commit => p_commit
147 , p_validation_level => p_validation_level
148 , x_return_status => x_return_status
149 , x_msg_count => x_msg_count
150 , x_msg_data => x_msg_data
151 , p_organization_id => p_organization_id
152 , p_container_item_id => p_container_item_id
153 , p_revision => p_revision
154 , p_lot_number => p_lot_number
155 , p_from_serial_number => p_from_serial_number
156 , p_to_serial_number => p_to_serial_number
157 , p_subinventory => p_subinventory
158 , p_locator_id => p_locator_id
159 , p_lpn_prefix => p_lpn_prefix
160 , p_lpn_suffix => p_lpn_suffix
161 , p_starting_num => p_starting_num
162 , p_quantity => p_quantity
163 , p_source => p_source
164 , p_cost_group_id => p_cost_group_id
165 , p_source_type_id => p_source_type_id
166 , p_source_header_id => p_source_header_id
167 , p_source_name => p_source_name
168 , p_source_line_id => p_source_line_id
169 , p_source_line_detail_id => p_source_line_detail_id
170 , p_lpn_id_out => p_lpn_id_out
171 , p_lpn_out => p_lpn_out
172 , p_process_id => p_process_id
173 , p_total_length => p_total_length
174 , p_ucc_128_suffix_flag => p_ucc_128_suffix_flag
175 , p_client_code => NULL
176 );
177
178 END Generate_LPN;
179
180 -- Overloaded for LSP, bug 9087971
181
182 PROCEDURE Generate_LPN (
183 p_api_version IN NUMBER
184 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
185 , p_commit IN VARCHAR2 := fnd_api.g_false
186 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
187 , x_return_status OUT NOCOPY VARCHAR2
188 , x_msg_count OUT NOCOPY NUMBER
189 , x_msg_data OUT NOCOPY VARCHAR2
190 , p_organization_id IN NUMBER
191 , p_container_item_id IN NUMBER := NULL
192 , p_revision IN VARCHAR2 := NULL
193 , p_lot_number IN VARCHAR2 := NULL
194 , p_from_serial_number IN VARCHAR2 := NULL
195 , p_to_serial_number IN VARCHAR2 := NULL
196 , p_subinventory IN VARCHAR2 := NULL
197 , p_locator_id IN NUMBER := NULL
198 , p_lpn_prefix IN VARCHAR2 := NULL
199 , p_lpn_suffix IN VARCHAR2 := NULL
200 , p_starting_num IN NUMBER := NULL
201 , p_quantity IN NUMBER := 1
202 , p_source IN NUMBER := LPN_CONTEXT_PREGENERATED
203 , p_cost_group_id IN NUMBER := NULL
204 , p_source_type_id IN NUMBER := NULL
205 , p_source_header_id IN NUMBER := NULL
206 , p_source_name IN VARCHAR2 := NULL
207 , p_source_line_id IN NUMBER := NULL
208 , p_source_line_detail_id IN NUMBER := NULL
209 , p_total_length IN NUMBER := NULL
210 , p_ucc_128_suffix_flag IN VARCHAR2 := NULL
211 , p_lpn_id_out OUT NOCOPY NUMBER
212 , p_lpn_out OUT NOCOPY VARCHAR2
213 , p_process_id OUT NOCOPY NUMBER
214 , p_client_code IN VARCHAR2 -- Adding for LSP , bug 9087971
215 ) IS
216 l_api_name CONSTANT VARCHAR2(30) := 'Generate_LPN';
217 l_api_version CONSTANT NUMBER := 1.0;
218 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
219 l_progress VARCHAR2(500) := 'Entered API';
220 l_msgdata VARCHAR2(1000);
221
222 -- Variables use by Auto_Create_LPNs
223 l_lpn_att_rec WMS_Data_Type_Definitions_PUB.LPNRecordType;
224 l_serial_tbl WMS_Data_Type_Definitions_PUB.SerialRangeTableType;
225 l_gen_lpn_tbl WMS_Data_Type_Definitions_PUB.LPNTableType;
226 l_lpn_bulk_rec WMS_CONTAINER_PVT.LPNBulkRecType;
227
228 -- Validation Parameters
229 l_quantity NUMBER;
230 l_org inv_validate.org;
231 l_container_item inv_validate.item;
232 l_lpn lpn;
233 l_sub inv_validate.sub;
234 l_locator inv_validate.LOCATOR;
235 l_lot inv_validate.lot;
236 l_serial inv_validate.serial;
237 l_current_serial VARCHAR2(30) := p_from_serial_number;
238 l_prefix VARCHAR2(30);
239 l_quantity_serial NUMBER;
240 l_from_number NUMBER;
241 l_to_number NUMBER;
242 l_errorcode NUMBER;
243 l_length NUMBER;
244 l_padded_length NUMBER;
245 l_current_number NUMBER;
246 l_result NUMBER;
247
248 /* FP-J Lot/Serial Support Enhancements
249 * Add current status of resides in receiving
250 */
251 CURSOR serial_validation_cursor IS
252 SELECT 'Validate-Serial'
253 FROM DUAL
254 WHERE EXISTS( SELECT 'Subinventory-not-given'
255 FROM mtl_serial_numbers
256 WHERE inventory_item_id = p_container_item_id
257 AND current_organization_id = p_organization_id
258 AND serial_number = l_current_serial
259 AND current_status IN (1, 5, 6, 7));
260
261 l_serial_validate VARCHAR2(15);
262
263 BEGIN
264 -- Standard Start of API savepoint
265 SAVEPOINT GENERATE_LPN_PUB;
266
267 -- Standard call to check for call compatibility.
268 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
269 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
270 fnd_msg_pub.ADD;
271 RAISE fnd_api.g_exc_unexpected_error;
272 END IF;
273
274 -- Initialize message list if p_init_msg_list is set to TRUE.
275 IF fnd_api.to_boolean(p_init_msg_list) THEN
276 fnd_msg_pub.initialize;
277 END IF;
278
279 -- Initialize API return status to success
280 x_return_status := fnd_api.g_ret_sts_success;
281 -- API body
282 IF (l_debug = 1) THEN
283 mdebug('Call to Generate_LPN orgid='||p_organization_id||' sub='||p_subinventory||' loc='||p_locator_id||' src='||p_source||' vlev='||p_validation_level, G_INFO);
284 mdebug('cntitemid='||p_container_item_id||' rev='||p_revision||' lot='||p_lot_number||' fmsn='||p_from_serial_number||' tosn='||p_to_serial_number||' cstgrp='||p_cost_group_id, G_INFO);
285 mdebug('prefix='||p_lpn_prefix||' suffix='||p_lpn_suffix||' strtnum='||p_starting_num ||' qty=' ||p_quantity);
286 mdebug('scrtype='||p_source_type_id||' srchdr='||p_source_header_id||' srcname=' ||p_source_name||' srcln='||p_source_line_id||' srclndet='||p_source_line_detail_id, G_INFO);
287 END IF;
288
289 l_progress := 'Validate all inputs if validation level is set to full';
290
291 IF (p_validation_level = fnd_api.g_valid_level_full) THEN
292 l_progress := 'Validate Organization ID';
293 l_org.organization_id := p_organization_id;
294 l_result := inv_validate.ORGANIZATION(l_org);
295
296 IF (l_result = inv_validate.f) THEN
297 IF (l_debug = 1) THEN
298 mdebug(p_organization_id || ' is not a valid org id', G_ERROR);
299 END IF;
300 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
301 fnd_msg_pub.ADD;
302 RAISE fnd_api.g_exc_error;
303 END IF;
304
305 l_progress := 'Validate Container Item';
306 IF (p_container_item_id IS NOT NULL) THEN
307 l_container_item.inventory_item_id := p_container_item_id;
308 l_result := inv_validate.inventory_item(l_container_item, l_org);
309
310 IF (l_result = inv_validate.f) THEN
311 IF (l_debug = 1) THEN
312 mdebug(p_container_item_id || ' is not a valid container item id', 1);
313 END IF;
314 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ITEM');
315 fnd_msg_pub.ADD;
316 RAISE fnd_api.g_exc_error;
317 END IF;
318
319 IF (l_container_item.container_item_flag = 'N') THEN
320 IF (l_debug = 1) THEN
321 mdebug(p_container_item_id || ' is not a container', 1);
322 END IF;
323 fnd_message.set_name('WMS', 'WMS_CONT_ITEM_NOT_A_CONT');
324 fnd_msg_pub.ADD;
325 RAISE fnd_api.g_exc_error;
326 END IF;
327 END IF;
328
329 l_progress := 'Validate Subinventory';
330 IF (p_subinventory IS NOT NULL) THEN
331 l_sub.secondary_inventory_name := p_subinventory;
332 l_result := inv_validate.subinventory(l_sub, l_org);
333
334 IF (l_result = inv_validate.f) THEN
335 IF (l_debug = 1) THEN
336 mdebug(p_subinventory || ' is not a valid sub', 1);
337 END IF;
338 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SUB');
339 fnd_msg_pub.ADD;
340 RAISE fnd_api.g_exc_error;
341 END IF;
342 END IF;
343
344 l_progress := 'Validate Locator';
345 IF (p_subinventory IS NOT NULL) THEN
346 IF (l_sub.locator_type IN (2, 3)) THEN
347 IF (p_locator_id IS NULL) THEN
348 IF (l_debug = 1) THEN
349 mdebug('Generate_LPN is missing required loc', 1);
350 END IF;
351 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOC');
352 fnd_msg_pub.ADD;
353 RAISE fnd_api.g_exc_error;
354 END IF;
355
356 l_locator.inventory_location_id := p_locator_id;
357 l_result := inv_validate.validatelocator(l_locator, l_org, l_sub);
358
359 IF (l_result = inv_validate.f) THEN
360 IF (l_debug = 1) THEN
361 mdebug(p_locator_id || ' is not a valid loc id', 1);
362 END IF;
363 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOC');
364 fnd_msg_pub.ADD;
365 RAISE fnd_api.g_exc_error;
366 END IF;
367 END IF;
368 END IF;
369
370 l_progress := 'Validate Revision';
371 IF (p_container_item_id IS NOT NULL) THEN
372 IF (l_container_item.revision_qty_control_code = 2) THEN
373 IF (p_revision IS NOT NULL) THEN
374 l_result := inv_validate.revision(p_revision, l_org, l_container_item);
375
376 IF (l_result = inv_validate.f) THEN
377 IF (l_debug = 1) THEN
378 mdebug(p_revision || ' is not a valid rev', 1);
379 END IF;
380 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_REV');
381 fnd_msg_pub.ADD;
382 RAISE fnd_api.g_exc_error;
383 END IF;
384 ELSE
385 --Rev not supported for container items currently. Allow to use rev controlled items
386 IF (l_debug = 1) THEN
387 mdebug('Generate_LPN is missing the rev for rev container item..ok', 1);
388 END IF;
389 --fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_REV');
390 --fnd_msg_pub.ADD;
391 --RAISE fnd_api.g_exc_error;
392 END IF;
393 END IF;
394 END IF;
395
396 l_progress := 'Validate Lot';
397 IF (p_container_item_id IS NOT NULL) THEN
398 IF (l_container_item.lot_control_code = 2) THEN
399 IF (p_lot_number IS NOT NULL) THEN
400 l_lot.lot_number := p_lot_number;
401 l_result := inv_validate.lot_number(l_lot, l_org, l_container_item, l_sub, l_locator, p_revision);
402
403 IF (l_result = inv_validate.f) THEN
404 IF (l_debug = 1) THEN
405 mdebug(p_lot_number || ' is not a valid lot', 1);
406 END IF;
407 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOT');
408 fnd_msg_pub.ADD;
409 RAISE fnd_api.g_exc_error;
410 END IF;
411 ELSE
412 --Lots not supported for container items currently. Allow to use lot controlled items
413 IF (l_debug = 1) THEN
414 mdebug('Generate_LPN is missing lot for lot container item..ok', 1);
415 END IF;
416 --fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOT');
417 --fnd_msg_pub.ADD;
418 --RAISE fnd_api.g_exc_error;
419 END IF;
420 END IF;
421 END IF;
422
423 l_progress := 'Validate Serial';
424 IF (p_container_item_id IS NOT NULL) THEN
425 IF (l_container_item.serial_number_control_code <> 1) THEN
426 IF ((p_from_serial_number IS NOT NULL) AND (p_to_serial_number IS NOT NULL)) THEN
427 /* Call this API to parse the serial numbers into prefixes and numbers */
428 IF (NOT mtl_serial_check.inv_serial_info(p_from_serial_number, p_to_serial_number, l_prefix, l_quantity_serial, l_from_number, l_to_number, l_errorcode)) THEN
429 IF (l_debug = 1) THEN
430 mdebug(p_to_serial_number || ' failed MTL_Serial_Check', 1);
431 END IF;
432 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
433 fnd_msg_pub.ADD;
434 RAISE fnd_api.g_exc_error;
435 END IF;
436
437 -- Check that in the case of a range of serial numbers, that the
438 -- inputted p_quantity equals the amount of items in the serial range.
439 IF (p_quantity IS NOT NULL) THEN
440 IF (p_quantity <> l_quantity_serial) THEN
441 IF (l_debug = 1) THEN
442 mdebug(p_quantity || ' does not match sn range qty of ' || l_quantity_serial, 1);
443 END IF;
444 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_X_QTY');
445 fnd_msg_pub.ADD;
446 RAISE fnd_api.g_exc_error;
447 END IF;
448 END IF;
449
450 -- Get the serial number length.
451 -- Note that the from and to serial numbers must be of the same length.
452 l_length := LENGTH(p_from_serial_number);
453 -- Initialize the current pointer variables
454 l_current_serial := p_from_serial_number;
455 l_current_number := l_from_number;
456
457 LOOP
458 IF (p_subinventory IS NOT NULL) THEN
459 l_serial.serial_number := l_current_serial;
460 l_result := inv_validate.validate_serial(l_serial, l_org, l_container_item, l_sub, l_lot, l_locator, p_revision);
461
462 IF (l_result = inv_validate.f) THEN
463 IF (l_debug = 1) THEN
464 mdebug(l_current_serial || 'failed validate_serial', 1);
465 END IF;
466 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
467 fnd_msg_pub.ADD;
468 RAISE fnd_api.g_exc_error;
469 END IF;
470 ELSE
471 -- Subinventory was not given so will need to do
472 -- alternative non-standard serial number validation.
473 OPEN serial_validation_cursor;
474 FETCH serial_validation_cursor INTO l_serial_validate;
475
476 IF serial_validation_cursor%NOTFOUND THEN
477 IF (l_debug = 1) THEN
478 mdebug(l_current_serial || ' could not be found in MTL_SERIAL_NUMBERS', 1);
479 END IF;
480 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
481 fnd_msg_pub.ADD;
482 RAISE fnd_api.g_exc_error;
483 END IF;
484
485 CLOSE serial_validation_cursor;
486 END IF;
487
488 EXIT WHEN l_current_serial = p_to_serial_number;
489 /* Increment the current serial number */
490 l_current_number := l_current_number + 1;
491 l_padded_length := l_length - LENGTH(l_current_number);
492 IF l_prefix IS NOT NULL THEN
493 l_current_serial := RPAD(l_prefix, l_padded_length, '0') ||
494 l_current_number;
495 ELSE
496 l_current_serial := Rpad('@',l_padded_length+1,'0')
497 || l_current_number;
498 l_current_serial := Substr(l_current_serial,2);
499 END IF;
500 -- Bug 2375043
501 --l_current_serial := RPAD(l_prefix, l_padded_length, '0') || l_current_number;
502 END LOOP;
503 ELSE
504 --SN not supported for container items currently. Allow to use serial controlled items
505 IF (l_debug = 1) THEN
506 mdebug('Generate_LPN is missing sn for serial container item..ok', 1);
507 END IF;
508 --fnd_message.set_name('WMS', 'WMS_CONT_MISS_SER_NUM');
509 --fnd_msg_pub.ADD;
510 --RAISE fnd_api.g_exc_error;
511 END IF;
512 END IF;
513 END IF;
514
515 l_progress := 'Validate quantity';
516 IF (p_quantity IS NOT NULL) THEN
517 IF (p_quantity <= 0) THEN
518 IF (l_debug = 1) THEN
519 mdebug(p_quantity || ' is a negative qty', 1);
520 END IF;
521 fnd_message.set_name('WMS', 'WMS_CONT_NEG_QTY');
522 fnd_msg_pub.ADD;
523 RAISE fnd_api.g_exc_error;
524 END IF;
525
526 l_quantity := p_quantity;
527 ELSE
528 l_quantity := 1;
529 END IF;
530
531 l_progress := 'Validate the source, i.e. LPN Context';
532 IF (p_source IS NOT NULL) THEN
533 IF (p_source NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)) THEN
534 IF (l_debug = 1) THEN
535 mdebug(p_source || ' is an invalid source', 1);
536 END IF;
537 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN_CONTEXT');
538 fnd_msg_pub.ADD;
539 RAISE fnd_api.g_exc_error;
540 END IF;
541 END IF;
542
543 l_progress := 'Validate Cost Group';
544 IF (p_cost_group_id IS NOT NULL) THEN
545 l_result := inv_validate.cost_group(p_cost_group_id, p_organization_id);
546
547 IF (l_result = inv_validate.f) THEN
548 IF (l_debug = 1) THEN
549 mdebug(p_cost_group_id || ' is an invalid cost group id', 1);
550 END IF;
551 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_CST_GRP');
552 fnd_msg_pub.ADD;
553 RAISE fnd_api.g_exc_error;
554 END IF;
555 END IF;
556 END IF;
557
558 l_progress := 'End of Input Validation';
559
560 l_lpn_att_rec.lpn_context := p_source;
561 l_lpn_att_rec.organization_id := p_organization_id;
562 l_lpn_att_rec.subinventory_code := p_subinventory;
563 l_lpn_att_rec.locator_id := p_locator_id;
564 l_lpn_att_rec.inventory_item_id := p_container_item_id;
565 l_lpn_att_rec.revision := p_revision;
566 l_lpn_att_rec.lot_number := p_lot_number;
567 l_lpn_att_rec.cost_group_id := p_cost_group_id;
568 l_lpn_att_rec.source_type_id := p_source_type_id;
569 l_lpn_att_rec.source_header_id := p_source_header_id;
570 l_lpn_att_rec.source_name := p_source_name;
571 l_lpn_att_rec.source_line_id := p_source_line_id;
572 l_lpn_att_rec.source_line_detail_id := p_source_line_detail_id;
573
574 l_serial_tbl(1).fm_serial_number := p_from_serial_number;
575 l_serial_tbl(1).to_serial_number := p_to_serial_number;
576
577 WMS_Container_PVT.Auto_Create_LPNs (
578 p_api_version => p_api_version
579 , p_init_msg_list => fnd_api.g_false
580 , p_commit => fnd_api.g_false
581 , x_return_status => x_return_status
582 , x_msg_count => x_msg_count
583 , x_msg_data => x_msg_data
584 , p_caller => 'WMS_Generate_LPN'
585 , p_quantity => p_quantity
586 , p_lpn_prefix => p_lpn_prefix
587 , p_lpn_suffix => p_lpn_suffix
588 , p_starting_number => p_starting_num
589 , p_total_lpn_length => p_total_length
590 , p_ucc_128_suffix_flag => p_ucc_128_suffix_flag
591 , p_lpn_attributes => l_lpn_att_rec
592 , p_serial_ranges => l_serial_tbl
593 , x_created_lpns => l_gen_lpn_tbl
594 , p_client_code => p_client_code -- Added for LSP, bug 9087971
595 );
596
597 IF ( x_return_status = fnd_api.g_ret_sts_success ) THEN
598 p_lpn_id_out := l_gen_lpn_tbl(1).lpn_id;
599 p_lpn_out := l_gen_lpn_tbl(1).license_plate_number;
600 ELSE
601 IF ( l_debug = 1 ) THEN
602 mdebug('Call to WMS_Container_PVT.Auto_Create_LPNs Failed', G_ERROR);
603 END IF;
604 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
605 END IF;
606
607 l_progress := 'End of API body';
608
609 -- Standard check of p_commit.
610 IF fnd_api.to_boolean(p_commit) THEN
611 COMMIT WORK;
612 END IF;
613
614 -- Standard call to get message count and if count is 1,
615 -- get message info.
616 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
617 EXCEPTION
618 WHEN FND_API.G_EXC_ERROR THEN
619 x_return_status := fnd_api.g_ret_sts_error;
620 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
621 IF (l_debug = 1) THEN
622 FOR i in 1..x_msg_count LOOP
623 l_msgdata := substr(l_msgdata||' | '||substr(fnd_msg_pub.get(x_msg_count-i+1, 'F'), 0, 200),1,2000);
624 END LOOP;
625 mdebug(l_api_name ||' EXC_ERROR progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), G_ERROR);
626 mdebug('msg: '||l_msgdata, G_ERROR);
627 END IF;
628 ROLLBACK TO GENERATE_LPN_PUB;
629 WHEN OTHERS THEN
630 x_return_status := fnd_api.g_ret_sts_unexp_error;
631 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
632 IF (l_debug = 1) THEN
633 mdebug(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), G_ERROR);
634 END IF;
635 ROLLBACK TO GENERATE_LPN_PUB;
636 END generate_lpn;
637
638 -- ----------------------------------------------------------------------------------
639 -- ----------------------------------------------------------------------------------
640 PROCEDURE associate_lpn(
641 p_api_version IN NUMBER,
642 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
643 p_commit IN VARCHAR2 := fnd_api.g_false,
644 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
645 x_return_status OUT NOCOPY VARCHAR2,
646 x_msg_count OUT NOCOPY NUMBER,
647 x_msg_data OUT NOCOPY VARCHAR2,
648 p_lpn_id IN NUMBER,
649 p_container_item_id IN NUMBER,
650 p_lot_number IN VARCHAR2 := NULL,
651 p_revision IN VARCHAR2 := NULL,
652 p_serial_number IN VARCHAR2 := NULL,
653 p_organization_id IN NUMBER,
654 p_subinventory IN VARCHAR2 := NULL,
655 p_locator_id IN NUMBER := NULL,
656 p_cost_group_id IN NUMBER := NULL,
657 p_source_type_id IN NUMBER := NULL,
658 p_source_header_id IN NUMBER := NULL,
659 p_source_name IN VARCHAR2 := NULL,
660 p_source_line_id IN NUMBER := NULL,
661 p_source_line_detail_id IN NUMBER := NULL
662 ) IS
663 l_api_name CONSTANT VARCHAR2(30) := 'Associate_LPN';
664 l_api_version CONSTANT NUMBER := 1.0;
665 l_lpn lpn;
666 l_container_item inv_validate.item;
667 l_org inv_validate.org;
668 l_sub inv_validate.sub;
669 l_locator inv_validate.LOCATOR;
670 l_lot inv_validate.lot;
671 l_serial inv_validate.serial;
672 l_result NUMBER;
673 l_new_lpn_id NUMBER;
674 l_new_lpn VARCHAR2(30);
675 l_insert_update_flag VARCHAR2(1); -- flag to signal existing lpn or new one
676 l_curr_seq NUMBER;
677 l_new_weight NUMBER;
678 l_new_weight_uom VARCHAR2(3);
679 l_net_weight NUMBER;
680 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
681 BEGIN
682 -- Standard Start of API savepoint
683 SAVEPOINT associate_lpn_pub;
684
685 -- Standard call to check for call compatibility.
686 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
687 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
688 fnd_msg_pub.ADD;
689 RAISE fnd_api.g_exc_unexpected_error;
690 END IF;
691
692 -- Initialize message list if p_init_msg_list is set to TRUE.
693 IF fnd_api.to_boolean(p_init_msg_list) THEN
694 fnd_msg_pub.initialize;
695 END IF;
696
697 -- Initialize API return status to success
698 x_return_status := fnd_api.g_ret_sts_success;
699
700 -- API body
701 IF (l_debug = 1) THEN
702 mdebug('Call to Associate_LPN API', G_MESSAGE);
703 mdebug('orgid=' ||p_organization_id|| ' sub=' ||p_subinventory|| ' loc=' ||p_locator_id|| ' lpnid=' ||p_lpn_id, G_INFO);
704 mdebug('itemid=' ||p_container_item_id|| ' rev=' ||p_revision|| ' lot=' ||p_lot_number|| ' sn=' ||p_serial_number, G_INFO);
705 mdebug('cg=' ||p_cost_group_id|| ' srctype=' ||p_source_type_id||' srchdr='||p_source_header_id||' srcln='||p_source_line_id, G_INFO);
706 END IF;
707
708 /* Validate all inputs if validation level is set to full */
709 IF (p_validation_level = fnd_api.g_valid_level_full) THEN
710 /* Check that lpn id is given */
711 IF (p_lpn_id IS NULL) THEN
712 fnd_message.set_name('WMS', 'WMS_CONT_LPN_NOT_GIVEN');
713 fnd_msg_pub.ADD;
714 RAISE fnd_api.g_exc_error;
715 END IF;
716
717 /* Validate the LPN */
718 l_lpn.lpn_id := p_lpn_id;
719 l_lpn.license_plate_number := NULL;
720 l_result := validate_lpn(l_lpn);
721
722 IF (l_result = inv_validate.f) THEN
723 l_new_lpn_id := p_lpn_id;
724 l_insert_update_flag := 'i';
725 ELSE
726 l_insert_update_flag := 'u';
727 END IF;
728
729 /* Validate Organization ID */
730 l_org.organization_id := p_organization_id;
731 l_result := inv_validate.ORGANIZATION(l_org);
732
733 IF (l_result = inv_validate.f) THEN
734 IF (l_debug = 1) THEN
735 mdebug(p_organization_id || ' is an invalid org id', 1);
736 END IF;
737 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
738 fnd_msg_pub.ADD;
739 RAISE fnd_api.g_exc_error;
740 END IF;
741
742 /* Validate Subinventory */
743 IF (p_subinventory IS NOT NULL) THEN
744 l_sub.secondary_inventory_name := p_subinventory;
745 l_result := inv_validate.subinventory(l_sub, l_org);
746
747 IF (l_result = inv_validate.f) THEN
748 IF (l_debug = 1) THEN
749 mdebug(p_subinventory || ' is an invalid sub', 1);
750 END IF;
751 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SUB');
752 fnd_msg_pub.ADD;
753 RAISE fnd_api.g_exc_error;
754 END IF;
755 END IF;
756
757 /* Validate Locator */
758 IF (p_subinventory IS NOT NULL) THEN
759 IF (l_sub.locator_type IN (2, 3)) THEN
760 IF (p_locator_id IS NULL) THEN
761 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOC');
762 fnd_msg_pub.ADD;
763 RAISE fnd_api.g_exc_error;
764 END IF;
765
766 l_locator.inventory_location_id := p_locator_id;
767 l_result := inv_validate.validatelocator(l_locator, l_org, l_sub);
768
769 IF (l_result = inv_validate.f) THEN
770 IF (l_debug = 1) THEN
771 mdebug(p_locator_id || ' is an invalid loc id', 1);
772 END IF;
773 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOC');
774 fnd_msg_pub.ADD;
775 RAISE fnd_api.g_exc_error;
776 END IF;
777 END IF;
778 END IF;
779
780 /* Validate Container Item */
781 /* Validate Container Item */
782 IF (p_container_item_id IS NOT NULL) THEN
783 l_container_item.inventory_item_id := p_container_item_id;
784 l_result := inv_validate.inventory_item(l_container_item, l_org);
785
786 IF (l_result = inv_validate.f) THEN
787 IF (l_debug = 1) THEN
788 mdebug(p_container_item_id || ' is an invalid container item id', 1);
789 END IF;
790 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ITEM');
791 fnd_msg_pub.ADD;
792 RAISE fnd_api.g_exc_error;
793 END IF;
794
795 IF (l_container_item.container_item_flag = 'N') THEN
796 IF (l_debug = 1) THEN
797 mdebug(p_container_item_id || ' is not a container item', 1);
798 END IF;
799 fnd_message.set_name('WMS', 'WMS_CONT_ITEM_NOT_A_CONT');
800 fnd_msg_pub.ADD;
801 RAISE fnd_api.g_exc_error;
802 END IF;
803
804 IF (l_container_item.unit_weight IS NOT NULL) THEN
805 IF (l_lpn.gross_weight IS NOT NULL) THEN
806 -- convert container item weight to lpn gross weight uom
807 l_new_weight :=
808 inv_convert.inv_um_convert(l_container_item.inventory_item_id, 5, l_container_item.unit_weight, l_container_item.weight_uom_code, l_lpn.gross_weight_uom_code, NULL, NULL);
809 -- add lpn gross weight into new gross weight.
810 l_new_weight := l_new_weight + l_lpn.gross_weight;
811 l_new_weight_uom := l_lpn.gross_weight_uom_code;
812 ELSE
813 --lpn has no weight, use container item weights
814 l_new_weight := l_container_item.unit_weight;
815 l_new_weight_uom := l_container_item.weight_uom_code;
816 END IF;
817 ELSE
818 --weight not specified for container item, use default lpn weights
819 l_new_weight := l_lpn.gross_weight;
820 l_new_weight_uom := l_lpn.gross_weight_uom_code;
821 END IF;
822 ELSE
823 fnd_message.set_name('WMS', 'WMS_CONT_CONTAINER_NOT_GIVEN');
824 fnd_msg_pub.ADD;
825 RAISE fnd_api.g_exc_error;
826 END IF;
827
828 /* Validate Revision */
829 IF (p_container_item_id IS NOT NULL) THEN
830 IF (l_container_item.revision_qty_control_code = 2) THEN
831 IF (p_revision IS NOT NULL) THEN
832 l_result := inv_validate.revision(p_revision, l_org, l_container_item);
833
834 IF (l_result = inv_validate.f) THEN
835 IF (l_debug = 1) THEN
836 mdebug(p_revision || ' is an invalid rev', 1);
837 END IF;
838 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_REV');
839 fnd_msg_pub.ADD;
840 RAISE fnd_api.g_exc_error;
841 END IF;
842 ELSE
843 --Rev not supported for container items currently. Allow to use rev controlled items
844 IF (l_debug = 1) THEN
845 mdebug('Associate_LPN is missing the rev for rev container item..ok', 1);
846 END IF;
847 --fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_REV');
848 --fnd_msg_pub.ADD;
849 --RAISE fnd_api.g_exc_error;
850 END IF;
851 END IF;
852 END IF;
853
854 /* Validate Lot */
855 IF (p_container_item_id IS NOT NULL) THEN
856 IF (l_container_item.lot_control_code = 2) THEN
857 IF (p_lot_number IS NOT NULL) THEN
858 l_lot.lot_number := p_lot_number;
859 l_result := inv_validate.lot_number(l_lot, l_org, l_container_item, l_sub, l_locator, p_revision);
860
861 IF (l_result = inv_validate.f) THEN
862 IF (l_debug = 1) THEN
863 mdebug(p_lot_number || ' is not a valid lot', 1);
864 END IF;
865 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOT');
866 fnd_msg_pub.ADD;
867 RAISE fnd_api.g_exc_error;
868 END IF;
869 ELSE
870 --Lots not supported for container items currently. Allow to use lot controlled items
871 IF (l_debug = 1) THEN
872 mdebug('Associate_LPN is missing lot for lot container item..ok', 1);
873 END IF;
874 --fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOT');
875 --fnd_msg_pub.ADD;
876 --RAISE fnd_api.g_exc_error;
877 END IF;
878 END IF;
879 END IF;
880
881 /* Validate Serial */
882 IF (p_container_item_id IS NOT NULL) THEN
883 IF (l_container_item.serial_number_control_code <> 1) THEN
884 IF (p_serial_number IS NOT NULL) THEN
885 l_serial.serial_number := p_serial_number;
886 l_result := inv_validate.validate_serial(l_serial, l_org, l_container_item, l_sub, l_lot, l_locator, p_revision);
887
888 IF (l_result = inv_validate.f) THEN
889 IF (l_debug = 1) THEN
890 mdebug(p_serial_number || ' is an invalid sn', 1);
891 END IF;
892 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
893 fnd_msg_pub.ADD;
894 RAISE fnd_api.g_exc_error;
895 END IF;
896 ELSE
897 --SN not supported for container items currently. Allow to use serial controlled items
898 IF (l_debug = 1) THEN
899 mdebug('Associate_LPN is missing sn for serial container item..ok', 1);
900 END IF;
901 --fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_SER');
902 --fnd_msg_pub.ADD;
903 --RAISE fnd_api.g_exc_error;
904 END IF;
905 END IF;
906 END IF;
907
908 /* Validate Cost Group */
909 IF (p_cost_group_id IS NOT NULL) THEN
910 l_result := inv_validate.cost_group(p_cost_group_id, p_organization_id);
911
912 IF (l_result = inv_validate.f) THEN
913 IF (l_debug = 1) THEN
914 mdebug(p_cost_group_id || ' is an invalid cg', 1);
915 END IF;
916 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_CST_GRP');
917 fnd_msg_pub.ADD;
918 RAISE fnd_api.g_exc_error;
919 END IF;
920 END IF;
921 END IF;
922
923 /* End of input validation */
924
925 -- Necessary validation to get local values
926 -- if full validation was not performed
927 IF (p_validation_level <> fnd_api.g_valid_level_full) THEN
928 /* Validate the LPN */
929 l_lpn.lpn_id := p_lpn_id;
930 l_lpn.license_plate_number := NULL;
931 l_result := validate_lpn(l_lpn);
932
933 IF (l_result = inv_validate.f) THEN
934 l_new_lpn_id := p_lpn_id;
935 l_insert_update_flag := 'i';
936 ELSE
937 l_insert_update_flag := 'u';
938 END IF;
939
940 /* Validate Organization ID */
941 l_org.organization_id := p_organization_id;
942 l_result := inv_validate.ORGANIZATION(l_org);
943
944 IF (l_result = inv_validate.f) THEN
945 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
946 fnd_msg_pub.ADD;
947 RAISE fnd_api.g_exc_error;
948 END IF;
949
950 /* Validate Container Item */
951 IF (p_container_item_id IS NOT NULL) THEN
952 l_container_item.inventory_item_id := p_container_item_id;
953 l_result := inv_validate.inventory_item(l_container_item, l_org);
954
955 IF (l_result = inv_validate.f) THEN
956 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ITEM');
957 fnd_msg_pub.ADD;
958 RAISE fnd_api.g_exc_error;
959 END IF;
960
961 IF (l_container_item.container_item_flag = 'N') THEN
962 fnd_message.set_name('WMS', 'WMS_CONT_ITEM_NOT_A_CONT');
963 fnd_msg_pub.ADD;
964 RAISE fnd_api.g_exc_error;
965 END IF;
966
967 IF (l_container_item.unit_weight IS NOT NULL) THEN
968 IF (l_lpn.gross_weight IS NOT NULL) THEN
969 -- convert container item weight to lpn gross weight uom
970 l_new_weight :=
971 inv_convert.inv_um_convert(l_container_item.inventory_item_id, 5, l_container_item.unit_weight, l_container_item.weight_uom_code, l_lpn.gross_weight_uom_code, NULL, NULL);
972 -- add lpn gross weight into new gross weight.
973 l_new_weight := l_new_weight + l_lpn.gross_weight;
974 l_new_weight_uom := l_lpn.gross_weight_uom_code;
975 ELSE
976 --lpn has no weight, use container item weights
977 l_new_weight := l_container_item.unit_weight;
978 l_new_weight_uom := l_container_item.weight_uom_code;
979 END IF;
980 ELSE
981 --weight not specified for container item, use default lpn weights
982 l_new_weight := l_lpn.gross_weight;
983 l_new_weight_uom := l_lpn.gross_weight_uom_code;
984 END IF;
985 ELSE
986 fnd_message.set_name('WMS', 'WMS_CONT_CONTAINER_NOT_GIVEN');
987 fnd_msg_pub.ADD;
988 RAISE fnd_api.g_exc_error;
989 END IF;
990 END IF;
991
992 IF (l_insert_update_flag = 'u') THEN
993 UPDATE wms_license_plate_numbers
994 SET inventory_item_id = p_container_item_id,
995 last_update_date = SYSDATE,
996 last_updated_by = fnd_global.user_id,
997 revision = p_revision,
998 lot_number = p_lot_number,
999 serial_number = p_serial_number,
1000 organization_id = p_organization_id,
1001 subinventory_code = p_subinventory,
1002 /* Bug 3936269 Modifying the locator_id field as null if p_locator_id =0
1003 locator_id = p_locator_id, */
1004 locator_id = decode (p_locator_id,0,null,p_locator_id),
1005 --End of fix for Bug 3936269
1006 gross_weight_uom_code = l_new_weight_uom,
1007 gross_weight = l_new_weight,
1008 tare_weight_uom_code = l_container_item.weight_uom_code,
1009 tare_weight = l_container_item.unit_weight,
1010 sealed_status = 2,
1011 cost_group_id = p_cost_group_id,
1012 source_type_id = p_source_type_id,
1013 source_header_id = p_source_header_id,
1014 source_line_id = p_source_line_id,
1015 source_line_detail_id = p_source_line_detail_id,
1016 source_name = p_source_name
1017 WHERE lpn_id = p_lpn_id;
1018
1019 /* Added code to check if the LPN being updated is in Shipping
1020 if so, then the updated Wt ,container item are passed on to the
1021 WSH_DELIVERY_DETAILS table. Bug#2200989*/
1022
1023 l_net_weight := l_lpn.gross_weight;
1024 IF (l_debug = 1) THEN
1025 mdebug('Associate LPN***before update of shipping details***');
1026 mdebug('Associate LPN***old gross weight='|| l_lpn.gross_weight);
1027 mdebug('Associate LPN***new gross weight='|| l_new_weight);
1028 mdebug('Associate LPN***net weight='|| l_net_weight);
1029 END IF;
1030
1031 --Bug #3370346 (Passing the correct values for Gross Wt and Net Wt Parameters)
1032 update_shipping_details(
1033 p_lpn_id => p_lpn_id
1034 , p_gross_weight => l_new_weight
1035 , p_net_weight => l_net_weight
1036 , p_weight_uom => l_new_weight_uom
1037 , p_volume => l_lpn.content_volume
1038 , p_volume_uom => l_lpn.content_volume_uom_code
1039 );
1040 /* End bug#2200989 */
1041
1042
1043 ELSE /* l_insert_update_flag = 'i' */
1044 /* Need to generate a license plate number to go along with the given lpn id */
1045 LOOP
1046 SELECT wms_license_plate_numbers_s2.NEXTVAL
1047 INTO l_curr_seq
1048 FROM DUAL;
1049
1050 l_new_lpn := l_org.lpn_prefix || TO_CHAR(l_curr_seq) || l_org.lpn_suffix;
1051 l_lpn.lpn_id := l_new_lpn_id;
1052 l_lpn.license_plate_number := l_new_lpn;
1053 l_result := validate_lpn(l_lpn);
1054
1055 IF (l_result = inv_validate.f) THEN
1056 EXIT;
1057 END IF;
1058 END LOOP;
1059
1060 INSERT INTO wms_license_plate_numbers
1061 (
1062 lpn_id,
1063 license_plate_number,
1064 inventory_item_id,
1065 last_update_date,
1066 last_updated_by,
1067 creation_date,
1068 created_by,
1069 revision,
1070 lot_number,
1071 serial_number,
1072 organization_id,
1073 subinventory_code,
1074 locator_id,
1075 parent_lpn_id,
1076 gross_weight_uom_code,
1077 gross_weight,
1078 content_volume_uom_code,
1079 content_volume,
1080 tare_weight_uom_code,
1081 tare_weight,
1082 status_id,
1083 lpn_context,
1084 sealed_status,
1085 cost_group_id,
1086 source_type_id,
1087 source_header_id,
1088 source_line_id,
1089 source_line_detail_id,
1090 source_name
1091 )
1092 VALUES (
1093 l_new_lpn_id,
1094 l_new_lpn,
1095 p_container_item_id,
1096 SYSDATE,
1097 fnd_global.user_id,
1098 SYSDATE,
1099 fnd_global.user_id,
1100 p_revision,
1101 p_lot_number,
1102 p_serial_number,
1103 p_organization_id,
1104 p_subinventory,
1105 /* Bug 3936269 Inserting null for the locator_id field if p_locator_id is 0
1106 p_locator_id,*/
1107 decode(p_locator_id,0,null,p_locator_id),
1108 --End of fix for Bug 3936269
1109 NULL,
1110 l_container_item.weight_uom_code,
1111 l_container_item.unit_weight,
1112 l_container_item.volume_uom_code,
1113 0,
1114 l_container_item.weight_uom_code,
1115 l_container_item.unit_weight,
1116 NULL,
1117 1,
1118 2,
1119 p_cost_group_id,
1120 p_source_type_id,
1121 p_source_header_id,
1122 p_source_line_id,
1123 p_source_line_detail_id,
1124 p_source_name
1125 );
1126 END IF;
1127
1128 -- End of API body
1129
1130 -- Standard check of p_commit.
1131 IF fnd_api.to_boolean(p_commit) THEN
1132 COMMIT WORK;
1133 END IF;
1134
1135 -- Standard call to get message count and if count is 1,
1136 -- get message info.
1137 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1138 EXCEPTION
1139 WHEN fnd_api.g_exc_error THEN
1140 ROLLBACK TO associate_lpn_pub;
1141 x_return_status := fnd_api.g_ret_sts_error;
1142 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1143 WHEN fnd_api.g_exc_unexpected_error THEN
1144 ROLLBACK TO associate_lpn_pub;
1145 x_return_status := fnd_api.g_ret_sts_unexp_error;
1146 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1147 WHEN OTHERS THEN
1148 ROLLBACK TO associate_lpn_pub;
1149 x_return_status := fnd_api.g_ret_sts_unexp_error;
1150
1151 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1152 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1153 END IF;
1154
1155 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1156 END associate_lpn;
1157
1158 -- ----------------------------------------------------------------------------------
1159 -- ----------------------------------------------------------------------------------
1160
1161 PROCEDURE Create_LPN (
1162 p_api_version IN NUMBER
1163 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1164 , p_commit IN VARCHAR2 := fnd_api.g_false
1165 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1166 , x_return_status OUT NOCOPY VARCHAR2
1167 , x_msg_count OUT NOCOPY NUMBER
1168 , x_msg_data OUT NOCOPY VARCHAR2
1169 , p_lpn IN VARCHAR2
1170 , p_organization_id IN NUMBER
1171 , p_container_item_id IN NUMBER := NULL
1172 , p_lot_number IN VARCHAR2 := NULL
1173 , p_revision IN VARCHAR2 := NULL
1174 , p_serial_number IN VARCHAR2 := NULL
1175 , p_subinventory IN VARCHAR2 := NULL
1176 , p_locator_id IN NUMBER := NULL
1177 , p_source IN NUMBER := LPN_CONTEXT_PREGENERATED
1178 , p_cost_group_id IN NUMBER := NULL
1179 , p_parent_lpn_id IN NUMBER := NULL
1180 , p_source_type_id IN NUMBER := NULL
1181 , p_source_header_id IN NUMBER := NULL
1182 , p_source_name IN VARCHAR2 := NULL
1183 , p_source_line_id IN NUMBER := NULL
1184 , p_source_line_detail_id IN NUMBER := NULL
1185 , x_lpn_id OUT NOCOPY NUMBER
1186 ) IS
1187 l_api_name CONSTANT VARCHAR2(30) := 'Create_LPN';
1188 l_api_version CONSTANT NUMBER := 1.0;
1189 l_lpn lpn;
1190 l_parent_lpn lpn;
1191 l_container_item inv_validate.item;
1192 l_org inv_validate.org;
1193 l_sub inv_validate.sub;
1194 l_locator inv_validate.LOCATOR;
1195 l_lot inv_validate.lot;
1196 l_serial inv_validate.serial;
1197 l_result NUMBER;
1198 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1199 BEGIN
1200 -- Standard Start of API savepoint
1201 SAVEPOINT create_lpn_pub;
1202
1203 -- Standard call to check for call compatibility.
1204 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1205 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1206 fnd_msg_pub.ADD;
1207 RAISE fnd_api.g_exc_unexpected_error;
1208 END IF;
1209
1210 -- Initialize message list if p_init_msg_list is set to TRUE.
1211 IF fnd_api.to_boolean(p_init_msg_list) THEN
1212 fnd_msg_pub.initialize;
1213 END IF;
1214
1215 -- Initialize API return status to success
1216 x_return_status := fnd_api.g_ret_sts_success;
1217 -- API body
1218 IF (l_debug = 1) THEN
1219 mdebug('Call to Create_LPN orgid=' ||p_organization_id|| ' sub=' ||p_subinventory|| ' loc=' ||p_locator_id|| ' lpn=' ||p_lpn|| ' src=' ||p_source, G_INFO);
1220 mdebug('cntitemid=' ||p_container_item_id|| ' rev=' ||p_revision|| ' lot=' ||p_lot_number|| ' sn=' ||p_serial_number|| ' cstgrp=' ||p_cost_group_id, G_INFO);
1221 mdebug('prntlpnid=' ||p_parent_lpn_id|| ' scrtype=' ||p_source_type_id|| ' srchdr=' ||p_source_header_id|| ' srcname=' ||p_source_name|| ' srcln=' ||p_source_line_id||' srclndet='||p_source_line_detail_id, G_INFO);
1222 END IF;
1223
1224 /* Validate all inputs if validation level is set to full */
1225 IF (p_validation_level = fnd_api.g_valid_level_full) THEN
1226 /* Validate LPN */
1227 l_lpn.license_plate_number := p_lpn;
1228 l_lpn.lpn_id := NULL;
1229 l_result := validate_lpn(l_lpn);
1230
1231 IF (l_result = inv_validate.t) THEN
1232 IF (l_debug = 1) THEN
1233 mdebug(p_lpn || ' failed LPN validation', 1);
1234 END IF;
1235 fnd_message.set_name('WMS', 'WMS_CONT_DUPLICATE_LPN');
1236 fnd_msg_pub.ADD;
1237 RAISE fnd_api.g_exc_error;
1238 END IF;
1239
1240 /* Validate Parent LPN */
1241 IF (p_parent_lpn_id IS NOT NULL) THEN
1242 l_parent_lpn.lpn_id := p_parent_lpn_id;
1243 l_parent_lpn.license_plate_number := NULL;
1244 l_result := validate_lpn(l_parent_lpn);
1245
1246 IF (l_result = inv_validate.t) THEN
1247 IF (l_debug = 1) THEN
1248 mdebug(p_parent_lpn_id || ' parent LPN failed validation', 1);
1249 END IF;
1250 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
1251 fnd_msg_pub.ADD;
1252 RAISE fnd_api.g_exc_error;
1253 END IF;
1254 END IF;
1255
1256 /* Validate Organization ID */
1257 l_org.organization_id := p_organization_id;
1258 l_result := inv_validate.ORGANIZATION(l_org);
1259
1260 IF (l_result = inv_validate.f) THEN
1261 IF (l_debug = 1) THEN
1262 mdebug(p_organization_id || ' is an invalid Org', 1);
1263 END IF;
1264 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
1265 fnd_msg_pub.ADD;
1266 RAISE fnd_api.g_exc_error;
1267 END IF;
1268
1269 /* Validate Subinventory */
1270 IF (p_subinventory IS NOT NULL) THEN
1271 l_sub.secondary_inventory_name := p_subinventory;
1272 l_result := inv_validate.subinventory(l_sub, l_org);
1273
1274 IF (l_result = inv_validate.f) THEN
1275 IF (l_debug = 1) THEN
1276 mdebug(p_subinventory || ' Invalid Subinventory', 1);
1277 END IF;
1278 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SUB');
1279 fnd_msg_pub.ADD;
1280 RAISE fnd_api.g_exc_error;
1281 END IF;
1282 END IF;
1283
1284 /* Validate Locator */
1285 IF (p_subinventory IS NOT NULL) THEN
1286 IF (l_sub.locator_type IN (2, 3)) THEN
1287 IF (p_locator_id IS NULL) THEN
1288 IF (l_debug = 1) THEN
1289 mdebug('Missing required locator', 1);
1290 END IF;
1291 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOC');
1292 fnd_msg_pub.ADD;
1293 RAISE fnd_api.g_exc_error;
1294 END IF;
1295
1296 l_locator.inventory_location_id := p_locator_id;
1297 l_result := inv_validate.validatelocator(l_locator, l_org, l_sub);
1298
1299 IF (l_result = inv_validate.f) THEN
1300 IF (l_debug = 1) THEN
1301 mdebug(p_locator_id || ' is an invalid locator', 1);
1302 END IF;
1303 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOC');
1304 fnd_msg_pub.ADD;
1305 RAISE fnd_api.g_exc_error;
1306 END IF;
1307 END IF;
1308 END IF;
1309
1310 /* Validate Container Item */
1311 IF (p_container_item_id IS NOT NULL) THEN
1312 l_container_item.inventory_item_id := p_container_item_id;
1313 l_result := inv_validate.inventory_item(l_container_item, l_org);
1314
1315 IF (l_result = inv_validate.f) THEN
1316 IF (l_debug = 1) THEN
1317 mdebug(p_container_item_id || ' is an invalid container item', 1);
1318 END IF;
1319 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ITEM');
1320 fnd_msg_pub.ADD;
1321 RAISE fnd_api.g_exc_error;
1322 END IF;
1323
1324 IF (l_container_item.container_item_flag = 'N') THEN
1325 IF (l_debug = 1) THEN
1326 mdebug(p_container_item_id || ' is not a container', 1);
1327 END IF;
1328 fnd_message.set_name('WMS', 'WMS_CONT_ITEM_NOT_A_CONTAINER');
1329 fnd_msg_pub.ADD;
1330 RAISE fnd_api.g_exc_error;
1331 END IF;
1332 END IF;
1333
1334 /* Validate Revision */
1335 IF (p_container_item_id IS NOT NULL) THEN
1336 IF (l_container_item.revision_qty_control_code = 2) THEN
1337 IF (p_revision IS NOT NULL) THEN
1338 l_result := inv_validate.revision(p_revision, l_org, l_container_item);
1339
1340 IF (l_result = inv_validate.f) THEN
1341 IF (l_debug = 1) THEN
1342 mdebug(p_revision || ' is an invalid Revision', 1);
1343 END IF;
1344 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_REV');
1345 fnd_msg_pub.ADD;
1346 RAISE fnd_api.g_exc_error;
1347 END IF;
1348 ELSE
1349 --Revision not supported for container items currently. Allow to use rev controlled items
1350 IF (l_debug = 1) THEN
1351 mdebug('Generate_LPN is missing rev for lot container item..ok', 1);
1352 END IF;
1353 --fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_REV');
1354 --fnd_msg_pub.ADD;
1355 --RAISE fnd_api.g_exc_error;
1356 END IF;
1357 END IF;
1358 END IF;
1359
1360 /* Validate Lot */
1361 IF (p_container_item_id IS NOT NULL) THEN
1362 IF (l_container_item.lot_control_code = 2) THEN
1363 IF (p_lot_number IS NOT NULL) THEN
1364 l_lot.lot_number := p_lot_number;
1365 l_result := inv_validate.lot_number(l_lot, l_org, l_container_item, l_sub, l_locator, p_revision);
1366
1367 IF (l_result = inv_validate.f) THEN
1368 IF (l_debug = 1) THEN
1369 mdebug(p_lot_number || ' is an invalid lot', 1);
1370 END IF;
1371 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOT');
1372 fnd_msg_pub.ADD;
1373 RAISE fnd_api.g_exc_error;
1374 END IF;
1375 ELSE
1376 --Lots not supported for container items currently. Allow to use lot controlled items
1377 IF (l_debug = 1) THEN
1378 mdebug('Generate_LPN is missing lot for lot container item..ok', 1);
1379 END IF;
1380 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOT');
1381 fnd_msg_pub.ADD;
1382 RAISE fnd_api.g_exc_error;
1383 END IF;
1384 END IF;
1385 END IF;
1386
1387 /* Validate Serial */
1388 IF (p_container_item_id IS NOT NULL) THEN
1389 IF (l_container_item.serial_number_control_code <> 1) THEN
1390 IF (p_serial_number IS NOT NULL) THEN
1391 l_serial.serial_number := p_serial_number;
1392 l_result := inv_validate.validate_serial(l_serial, l_org, l_container_item, l_sub, l_lot, l_locator, p_revision);
1393
1394 IF (l_result = inv_validate.f) THEN
1395 IF (l_debug = 1) THEN
1396 mdebug(l_serial.serial_number || ' is an invalid Serial Number', 1);
1397 END IF;
1398 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
1399 fnd_msg_pub.ADD;
1400 RAISE fnd_api.g_exc_error;
1401 END IF;
1402 ELSE
1403 --SN not supported for container items currently. Allow to use serial controlled items
1404 IF (l_debug = 1) THEN
1405 mdebug('Create_LPN is missing sn for serial container item..ok', 1);
1406 END IF;
1407 --fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_SER');
1408 --fnd_msg_pub.ADD;
1409 --RAISE fnd_api.g_exc_error;
1410 END IF;
1411 END IF;
1412 END IF;
1413
1414 /* Validate Source, i.e. LPN Context */
1415 IF (p_source IS NOT NULL) THEN
1416 IF (p_source NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)) THEN
1417 IF (l_debug = 1) THEN
1418 mdebug(p_source || 'is an invalid source type', 1);
1419 END IF;
1420 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN_CONTEXT');
1421 fnd_msg_pub.ADD;
1422 RAISE fnd_api.g_exc_error;
1423 END IF;
1424 END IF;
1425
1426 /* Validate Cost Group */
1427 IF (p_cost_group_id IS NOT NULL) THEN
1428 l_result := inv_validate.cost_group(p_cost_group_id, p_organization_id);
1429
1430 IF (l_result = inv_validate.f) THEN
1431 IF (l_debug = 1) THEN
1432 mdebug(p_cost_group_id || 'is an invalid cost group', 1);
1433 END IF;
1434 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_CST_GRP');
1435 fnd_msg_pub.ADD;
1436 RAISE fnd_api.g_exc_error;
1437 END IF;
1438 END IF;
1439 END IF;
1440
1441 /* End of input validation */
1442
1443 WMS_CONTAINER_PVT.Create_LPN (
1444 p_api_version => p_api_version
1445 , p_init_msg_list => p_init_msg_list
1446 , p_commit => p_commit
1447 , p_validation_level => p_validation_level
1448 , x_return_status => x_return_status
1449 , x_msg_count => x_msg_count
1450 , x_msg_data => x_msg_data
1451 , p_lpn => p_lpn
1452 , p_organization_id => p_organization_id
1453 , p_container_item_id => p_container_item_id
1454 , p_lot_number => p_lot_number
1455 , p_revision => p_revision
1456 , p_serial_number => p_serial_number
1457 , p_subinventory => p_subinventory
1458 , p_locator_id => p_locator_id
1459 , p_source => p_source
1460 , p_cost_group_id => p_cost_group_id
1461 , p_parent_lpn_id => p_parent_lpn_id
1462 , p_source_type_id => p_source_type_id
1463 , p_source_header_id => p_source_header_id
1464 , p_source_name => p_source_name
1465 , p_source_line_id => p_source_line_id
1466 , p_source_line_detail_id => p_source_line_detail_id
1467 , x_lpn_id => x_lpn_id );
1468
1469 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
1470 IF ( l_debug = 1 ) THEN
1471 mdebug('Call to WMS_CONTAINER_PVT.Create_LPN Failed', G_ERROR);
1472 END IF;
1473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1474 END IF;
1475
1476 -- Standard check of p_commit.
1477 IF fnd_api.to_boolean(p_commit) THEN
1478 COMMIT WORK;
1479 END IF;
1480
1481 -- Standard call to get message count and if count is 1,
1482 -- get message info.
1483 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1484 EXCEPTION
1485 WHEN fnd_api.g_exc_error THEN
1486 ROLLBACK TO create_lpn_pub;
1487 x_return_status := fnd_api.g_ret_sts_error;
1488 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1489 WHEN fnd_api.g_exc_unexpected_error THEN
1490 ROLLBACK TO create_lpn_pub;
1491 x_return_status := fnd_api.g_ret_sts_unexp_error;
1492 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1493 WHEN OTHERS THEN
1494 ROLLBACK TO create_lpn_pub;
1495 x_return_status := fnd_api.g_ret_sts_unexp_error;
1496
1497 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1498 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1499 END IF;
1500
1501 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1502 END create_lpn;
1503
1504 -- ----------------------------------------------------------------------------------
1505 -- ----------------------------------------------------------------------------------
1506 PROCEDURE modify_lpn(
1507 p_api_version IN NUMBER,
1508 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1509 p_commit IN VARCHAR2 := fnd_api.g_false,
1510 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1511 x_return_status OUT NOCOPY VARCHAR2,
1512 x_msg_count OUT NOCOPY NUMBER,
1513 x_msg_data OUT NOCOPY VARCHAR2,
1514 p_lpn IN lpn,
1515 p_source_type_id IN NUMBER := NULL,
1516 p_source_header_id IN NUMBER := NULL,
1517 p_source_name IN VARCHAR2 := NULL,
1518 p_source_line_id IN NUMBER := NULL,
1519 p_source_line_detail_id IN NUMBER := NULL
1520 ) IS
1521 l_api_name CONSTANT VARCHAR2(30) := 'Modify_LPN';
1522 l_api_version CONSTANT NUMBER := 1.0;
1523 l_lpn lpn;
1524 l_container_item inv_validate.item;
1525 l_parent_item inv_validate.item;
1526 l_org inv_validate.org;
1527 l_sub inv_validate.sub;
1528 l_locator inv_validate.LOCATOR;
1529 l_result NUMBER;
1530 l_change_in_weight NUMBER := 0;
1531 l_change_in_weight_uom VARCHAR2(3);
1532 l_location_changed NUMBER := 1;
1533 l_container_sealed NUMBER := 1;
1534 l_context_changed NUMBER := 1;
1535 l_temp_conversion_num NUMBER;
1536 l_temp_conversion_num2 NUMBER;
1537 l_current_lpn NUMBER;
1538 l_dummy NUMBER;
1539 l_old_subinventory_code VARCHAR2(30);
1540 l_old_locator_id NUMBER;
1541 l_is_sub_lpn_controlled BOOLEAN; -- Bug 2308339
1542
1543 CURSOR nested_children_lpn_cursor IS
1544 -- Bug# 1546081
1545 -- SELECT *
1546 SELECT lpn_id,
1547 organization_id,
1548 subinventory_code,
1549 locator_id
1550 FROM wms_license_plate_numbers
1551 START WITH lpn_id = p_lpn.lpn_id
1552 CONNECT BY parent_lpn_id = PRIOR lpn_id;
1553
1554 CURSOR nested_parent_lpn_cursor IS
1555 SELECT *
1556 FROM wms_license_plate_numbers
1557 START WITH lpn_id = p_lpn.lpn_id
1558 CONNECT BY lpn_id = PRIOR parent_lpn_id;
1559
1560 CURSOR lpn_contents_cursor IS
1561 -- Bug# 1546081
1562 -- SELECT *
1563 SELECT organization_id,
1564 lpn_content_id,
1565 parent_lpn_id,
1566 inventory_item_id
1567 FROM wms_lpn_contents
1568 WHERE parent_lpn_id = l_current_lpn;
1569 -- AND NVL(serial_summary_entry, 2) = 2;
1570
1571 CURSOR lpn_serial_contents_cursor IS
1572 -- Bug# 1546081
1573 -- SELECT *
1574 SELECT 1
1575 FROM mtl_serial_numbers
1576 WHERE lpn_id = l_current_lpn;
1577
1578 CURSOR lpn_cursor IS
1579 -- Bug# 1546081
1580 -- SELECT *
1581 SELECT 1
1582 FROM wms_license_plate_numbers
1583 WHERE parent_lpn_id = l_current_lpn;
1584
1585 l_lpn_rec wms_license_plate_numbers%ROWTYPE;
1586 --l_lpn_contents_rec WMS_LPN_CONTENTS%ROWTYPE;
1587 l_lpn_contents_rec lpn_contents_cursor%ROWTYPE;
1588 l_lpn_serial_rec mtl_serial_numbers%ROWTYPE;
1589 l_net_weight NUMBER;
1590 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1591 BEGIN
1592 -- Standard Start of API savepoint
1593 SAVEPOINT modify_lpn_pub;
1594
1595 -- Standard call to check for call compatibility.
1596 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1597 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1598 fnd_msg_pub.ADD;
1599 RAISE fnd_api.g_exc_unexpected_error;
1600 END IF;
1601
1602 -- Initialize message list if p_init_msg_list is set to TRUE.
1603 IF fnd_api.to_boolean(p_init_msg_list) THEN
1604 fnd_msg_pub.initialize;
1605 END IF;
1606
1607 -- Initialize API return status to success
1608 x_return_status := fnd_api.g_ret_sts_success;
1609
1610 -- API body
1611 /* Validate all inputs if validation level is set to full */
1612 IF (p_validation_level = fnd_api.g_valid_level_full) THEN
1613 /* Validate LPN */
1614 l_lpn.lpn_id := p_lpn.lpn_id;
1615 l_lpn.license_plate_number := NULL;
1616 l_result := validate_lpn(l_lpn);
1617
1618 IF (l_result = inv_validate.f) THEN
1619 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
1620 fnd_msg_pub.ADD;
1621 RAISE fnd_api.g_exc_error;
1622 END IF;
1623
1624 /* Validate Organization */
1625 IF (p_lpn.organization_id IS NOT NULL) THEN
1626 l_org.organization_id := p_lpn.organization_id;
1627 l_result := inv_validate.ORGANIZATION(l_org);
1628
1629 IF (l_result = inv_validate.f) THEN
1630 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
1631 fnd_msg_pub.ADD;
1632 RAISE fnd_api.g_exc_error;
1633 END IF;
1634 END IF;
1635
1636 /* Validate Subinventory */
1637 IF (p_lpn.subinventory_code IS NOT NULL) THEN
1638 l_sub.secondary_inventory_name := p_lpn.subinventory_code;
1639 l_result := inv_validate.subinventory(l_sub, l_org);
1640
1641 IF (l_result = inv_validate.f) THEN
1642 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SUB');
1643 fnd_msg_pub.ADD;
1644 RAISE fnd_api.g_exc_error;
1645 END IF;
1646 END IF;
1647
1648 /* Validate Locator */
1649 IF (p_lpn.subinventory_code IS NOT NULL) THEN
1650 IF (l_sub.locator_type IN (2, 3)) THEN
1651 IF (p_lpn.locator_id IS NULL) THEN
1652 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOC');
1653 fnd_msg_pub.ADD;
1654 RAISE fnd_api.g_exc_error;
1655 END IF;
1656
1657 l_locator.inventory_location_id := p_lpn.locator_id;
1658 l_result := inv_validate.validatelocator(l_locator, l_org, l_sub);
1659
1660 IF (l_result = inv_validate.f) THEN
1661 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOC');
1662 fnd_msg_pub.ADD;
1663 RAISE fnd_api.g_exc_error;
1664 END IF;
1665 END IF;
1666 END IF;
1667
1668 /* Validate Container Item */
1669 IF (p_lpn.inventory_item_id IS NOT NULL) THEN
1670 l_container_item.inventory_item_id := p_lpn.inventory_item_id;
1671 l_result := inv_validate.inventory_item(l_container_item, l_org);
1672
1673 IF (l_result = inv_validate.f) THEN
1674 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ITEM');
1675 fnd_msg_pub.ADD;
1676 RAISE fnd_api.g_exc_error;
1677 END IF;
1678
1679 IF (l_container_item.container_item_flag = 'N') THEN
1680 fnd_message.set_name('WMS', 'WMS_CONT_ITEM_NOT_A_CONTAINER');
1681 fnd_msg_pub.ADD;
1682 RAISE fnd_api.g_exc_error;
1683 END IF;
1684 END IF;
1685
1686 /* Validate Gross Weight */
1687 IF (p_lpn.gross_weight IS NOT NULL) THEN
1688 IF (p_lpn.gross_weight < 0) THEN
1689 IF (l_debug = 1) THEN
1690 mdebug('gross weight= '|| p_lpn.gross_weight, 9);
1691 END IF;
1692 fnd_message.set_name('WMS', 'WMS_CONT_NEG_WEIGHT');
1693 fnd_msg_pub.ADD;
1694 RAISE fnd_api.g_exc_error;
1695 END IF;
1696 END IF;
1697
1698 /* Validate Content Volume */
1699 IF (p_lpn.content_volume IS NOT NULL) THEN
1700 IF (p_lpn.content_volume < 0) THEN
1701 fnd_message.set_name('WMS', 'WMS_CONT_NEG_VOLUME');
1702 fnd_msg_pub.ADD;
1703 RAISE fnd_api.g_exc_error;
1704 END IF;
1705 END IF;
1706
1707 /* Validate LPN Status ID */
1708 IF (p_lpn.status_id IS NOT NULL) THEN
1709 IF (p_lpn.status_id NOT IN (1, 2, 3, 4, 5, 6)) THEN
1710 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_STATUS_ID');
1711 fnd_msg_pub.ADD;
1712 RAISE fnd_api.g_exc_error;
1713 END IF;
1714 END IF;
1715
1716 /* Validate LPN Context */
1717 IF (p_lpn.lpn_context IS NOT NULL) THEN
1718 IF (p_lpn.lpn_context NOT IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11)) THEN
1719 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN_CONTEXT');
1720 fnd_msg_pub.ADD;
1721 RAISE fnd_api.g_exc_error;
1722 END IF;
1723 END IF;
1724
1725 /* Validate Sealed Status */
1726 IF (p_lpn.sealed_status IS NOT NULL) THEN
1727 IF (p_lpn.sealed_status NOT IN (1, 2)) THEN
1728 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SEALED_STAT');
1729 fnd_msg_pub.ADD;
1730 RAISE fnd_api.g_exc_error;
1731 END IF;
1732 END IF;
1733 END IF;
1734 /* End of input validation */
1735 l_lpn := p_lpn;
1736
1737 IF ( p_source_type_id IS NOT NULL ) THEN
1738 l_lpn.source_type_id := p_source_type_id;
1739 ELSIF ( p_lpn.source_type_id IS NULL ) THEN
1740 l_lpn.source_type_id := fnd_api.g_miss_num;
1741 END IF;
1742
1743 IF ( p_source_header_id IS NOT NULL ) THEN
1744 l_lpn.source_header_id := p_source_header_id;
1745 ELSIF ( p_lpn.source_header_id IS NULL ) THEN
1746 l_lpn.source_header_id := fnd_api.g_miss_num;
1747 END IF;
1748
1749 IF ( p_source_line_id IS NOT NULL ) THEN
1750 l_lpn.source_line_id := p_source_line_id;
1751 ELSIF ( p_lpn.source_line_id IS NULL ) THEN
1752 l_lpn.source_line_id := fnd_api.g_miss_num;
1753 END IF;
1754
1755 IF ( p_source_line_detail_id IS NOT NULL ) THEN
1756 l_lpn.source_line_detail_id := p_source_line_detail_id;
1757 ELSIF ( p_lpn.source_line_detail_id IS NULL ) THEN
1758 l_lpn.source_line_detail_id := fnd_api.g_miss_num;
1759 END IF;
1760
1761 IF ( p_source_name IS NOT NULL ) THEN
1762 l_lpn.source_name := p_source_name;
1763 ELSIF ( p_lpn.source_name IS NULL ) THEN
1764 l_lpn.source_name := fnd_api.g_miss_char;
1765 END IF;
1766
1767 WMS_CONTAINER_PVT.Modify_LPN (
1768 p_api_version => p_api_version
1769 , p_init_msg_list => p_init_msg_list
1770 , p_commit => p_commit
1771 , p_validation_level => p_validation_level
1772 , x_return_status => x_return_status
1773 , x_msg_count => x_msg_count
1774 , x_msg_data => x_msg_data
1775 , p_lpn => l_lpn );
1776
1777 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
1778 IF ( l_debug = 1 ) THEN
1779 mdebug('Call to WMS_CONTAINER_PVT.Modify_LPN Failed', G_ERROR);
1780 END IF;
1781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1782 END IF;
1783
1784 -- Standard check of p_commit.
1785 IF fnd_api.to_boolean(p_commit) THEN
1786 COMMIT WORK;
1787 END IF;
1788
1789 -- Standard call to get message count and if count is 1,
1790 -- get message info.
1791 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1792 EXCEPTION
1793 WHEN fnd_api.g_exc_error THEN
1794 ROLLBACK TO modify_lpn_pub;
1795 x_return_status := fnd_api.g_ret_sts_error;
1796 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1797 WHEN fnd_api.g_exc_unexpected_error THEN
1798 ROLLBACK TO modify_lpn_pub;
1799 x_return_status := fnd_api.g_ret_sts_unexp_error;
1800 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1801 WHEN OTHERS THEN
1802 ROLLBACK TO modify_lpn_pub;
1803 x_return_status := fnd_api.g_ret_sts_unexp_error;
1804
1805 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1806 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1807 END IF;
1808
1809 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1810 END modify_lpn;
1811
1812 PROCEDURE modify_lpn_wrapper(
1813 p_api_version IN NUMBER,
1814 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
1815 p_commit IN VARCHAR2 := fnd_api.g_false,
1816 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
1817 x_return_status OUT NOCOPY VARCHAR2,
1818 x_msg_count OUT NOCOPY NUMBER,
1819 x_msg_data OUT NOCOPY VARCHAR2,
1820 p_lpn_id IN NUMBER,
1821 p_license_plate_number IN VARCHAR2 := NULL,
1822 p_inventory_item_id IN NUMBER := NULL,
1823 p_weight_uom_code IN VARCHAR2 := NULL,
1824 p_gross_weight IN NUMBER := NULL,
1825 p_volume_uom_code IN VARCHAR2 := NULL,
1826 p_content_volume IN NUMBER := NULL,
1827 p_status_id IN NUMBER := NULL,
1828 p_lpn_context IN NUMBER := NULL,
1829 p_sealed_status IN NUMBER := NULL,
1830 p_organization_id IN NUMBER := NULL,
1831 p_subinventory IN VARCHAR := NULL,
1832 p_locator_id IN NUMBER := NULL,
1833 p_source_type_id IN NUMBER := NULL,
1834 p_source_header_id IN NUMBER := NULL,
1835 p_source_name IN VARCHAR2 := NULL,
1836 p_source_line_id IN NUMBER := NULL,
1837 p_source_line_detail_id IN NUMBER := NULL
1838 ) IS
1839 l_api_name CONSTANT VARCHAR2(30) := 'Modify_LPN_Wrapper';
1840 l_api_version CONSTANT NUMBER := 1.0;
1841 l_lpn lpn;
1842 l_result NUMBER;
1843 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1844 BEGIN
1845 -- Standard Start of API savepoint
1846 SAVEPOINT modify_lpn_wrapper_pub;
1847
1848 -- Standard call to check for call compatibility.
1849 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1850 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1851 fnd_msg_pub.ADD;
1852 RAISE fnd_api.g_exc_unexpected_error;
1853 END IF;
1854
1855 -- Initialize message list if p_init_msg_list is set to TRUE.
1856 IF fnd_api.to_boolean(p_init_msg_list) THEN
1857 fnd_msg_pub.initialize;
1858 END IF;
1859
1860 -- Initialize API return status to success
1861 x_return_status := fnd_api.g_ret_sts_success;
1862 -- API body
1863
1864 /* Validate LPN */
1865 l_lpn.lpn_id := p_lpn_id;
1866 l_lpn.license_plate_number := NULL;
1867 l_result := validate_lpn(l_lpn);
1868
1869 IF (l_result = inv_validate.f) THEN
1870 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
1871 fnd_msg_pub.ADD;
1872 RAISE fnd_api.g_exc_error;
1873 END IF;
1874
1875 WMS_CONTAINER_PVT.Modify_LPN_Wrapper(
1876 p_api_version => p_api_version
1877 , p_init_msg_list => p_init_msg_list
1878 , p_commit => p_commit
1879 , p_validation_level => p_validation_level
1880 , x_return_status => x_return_status
1881 , x_msg_count => x_msg_count
1882 , x_msg_data => x_msg_data
1883 , p_lpn_id => p_lpn_id
1884 , p_license_plate_number => p_license_plate_number
1885 , p_inventory_item_id => p_inventory_item_id
1886 , p_weight_uom_code => p_weight_uom_code
1887 , p_gross_weight => p_gross_weight
1888 , p_volume_uom_code => p_volume_uom_code
1889 , p_content_volume => p_content_volume
1890 , p_status_id => p_status_id
1891 , p_lpn_context => p_lpn_context
1892 , p_sealed_status => p_sealed_status
1893 , p_organization_id => p_organization_id
1894 , p_subinventory => p_subinventory
1895 , p_locator_id => p_locator_id
1896 , p_source_type_id => p_source_type_id
1897 , p_source_header_id => p_source_header_id
1898 , p_source_name => p_source_name
1899 , p_source_line_id => p_source_line_id
1900 , p_source_line_detail_id => p_source_line_detail_id );
1901
1902 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
1903 -- Modify LPN should put the appropriate error message in the stack
1904 RAISE fnd_api.g_exc_error;
1905 END IF;
1906
1907 -- End of API body
1908
1909 -- Standard check of p_commit.
1910 IF fnd_api.to_boolean(p_commit) THEN
1911 COMMIT WORK;
1912 END IF;
1913
1914 -- Standard call to get message count and if count is 1,
1915 -- get message info.
1916 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1917 EXCEPTION
1918 WHEN fnd_api.g_exc_error THEN
1919 ROLLBACK TO modify_lpn_wrapper_pub;
1920 x_return_status := fnd_api.g_ret_sts_error;
1921 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1922 WHEN fnd_api.g_exc_unexpected_error THEN
1923 ROLLBACK TO modify_lpn_wrapper_pub;
1924 x_return_status := fnd_api.g_ret_sts_unexp_error;
1925 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1926 WHEN OTHERS THEN
1927 ROLLBACK TO modify_lpn_wrapper_pub;
1928 x_return_status := fnd_api.g_ret_sts_unexp_error;
1929
1930 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1931 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1932 END IF;
1933
1934 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1935 END modify_lpn_wrapper;
1936
1937 -- ----------------------------------------------------------------------------------
1938 -- ----------------------------------------------------------------------------------
1939 PROCEDURE PackUnpack_Container (
1940 p_api_version IN NUMBER
1941 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1942 , p_commit IN VARCHAR2 := fnd_api.g_false
1943 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
1944 , x_return_status OUT NOCOPY VARCHAR2
1945 , x_msg_count OUT NOCOPY NUMBER
1946 , x_msg_data OUT NOCOPY VARCHAR2
1947 , p_lpn_id IN NUMBER
1948 , p_content_lpn_id IN NUMBER := NULL
1949 , p_content_item_id IN NUMBER := NULL
1950 , p_content_item_desc IN VARCHAR2 := NULL
1951 , p_revision IN VARCHAR2 := NULL
1952 , p_lot_number IN VARCHAR2 := NULL
1953 , p_from_serial_number IN VARCHAR2 := NULL
1954 , p_to_serial_number IN VARCHAR2 := NULL
1955 , p_quantity IN NUMBER := 1
1956 , p_uom IN VARCHAR2 := NULL
1957 , p_sec_quantity IN NUMBER := NULL --INVCONV kkillams
1958 , p_sec_uom IN VARCHAR2 := NULL --INVCONV kkillams
1959 , p_organization_id IN NUMBER
1960 , p_subinventory IN VARCHAR2 := NULL
1961 , p_locator_id IN NUMBER := NULL
1962 , p_enforce_wv_constraints IN NUMBER := 2
1963 , p_operation IN NUMBER
1964 , p_cost_group_id IN NUMBER := NULL
1965 , p_source_type_id IN NUMBER := NULL
1966 , p_source_header_id IN NUMBER := NULL
1967 , p_source_name IN VARCHAR2 := NULL
1968 , p_source_line_id IN NUMBER := NULL
1969 , p_source_line_detail_id IN NUMBER := NULL
1970 , p_homogeneous_container IN NUMBER := 2
1971 , p_match_locations IN NUMBER := 2
1972 , p_match_lpn_context IN NUMBER := 2
1973 , p_match_lot IN NUMBER := 2
1974 , p_match_cost_groups IN NUMBER := 2
1975 , p_match_mtl_status IN NUMBER := 2
1976 , p_unpack_all IN NUMBER := 2
1977 , p_trx_action_id IN NUMBER := NULL
1978 , p_concurrent_pack IN NUMBER := 0
1979 , p_ignore_item_controls IN NUMBER := 2
1980 ) IS
1981 l_api_name CONSTANT VARCHAR2(30) := 'PackUnpack_Container';
1982 l_api_version CONSTANT NUMBER := 1.0;
1983 l_lpn lpn;
1984 l_content_lpn lpn;
1985 l_content_item inv_validate.item;
1986 l_org inv_validate.org;
1987 l_sub inv_validate.sub;
1988 l_locator inv_validate.LOCATOR;
1989 l_lot inv_validate.lot;
1990 l_serial inv_validate.serial;
1991 l_current_serial VARCHAR2(30) := p_from_serial_number;
1992 l_result NUMBER;
1993 l_serial_summary_entry NUMBER := 2;
1994 l_unit_weight NUMBER;
1995 l_weight_uom_code VARCHAR2(3);
1996 l_volume_uom_code VARCHAR2(3);
1997 l_unit_volume NUMBER;
1998 l_is_sub_lpn_controlled BOOLEAN; -- Bug 2308339
1999 l_row_id ROWID;
2000 l_operation NUMBER := p_operation;
2001 l_ignore_item_controls NUMBER := p_ignore_item_controls;
2002
2003 CURSOR nested_children_cursor IS
2004 -- Bug# 1546081
2005 -- SELECT *
2006 SELECT lpn_id
2007 FROM wms_license_plate_numbers
2008 START WITH lpn_id = p_content_lpn_id
2009 CONNECT BY parent_lpn_id = PRIOR lpn_id;
2010
2011 l_current_lpn NUMBER;
2012
2013 CURSOR lpn_contents_cursor IS
2014 -- Bug# 1546081
2015 -- SELECT *
2016 SELECT organization_id,
2017 lpn_content_id,
2018 parent_lpn_id,
2019 inventory_item_id
2020 FROM wms_lpn_contents
2021 WHERE parent_lpn_id = l_current_lpn
2022 AND NVL(serial_summary_entry, 2) = 2;
2023
2024 CURSOR lpn_serial_contents_cursor IS
2025 -- Bug# 1546081
2026 -- SELECT *
2027 SELECT current_organization_id,
2028 current_subinventory_code,
2029 current_locator_id,
2030 inventory_item_id,
2031 serial_number
2032 FROM mtl_serial_numbers
2033 WHERE lpn_id = l_current_lpn;
2034
2035 -- Bug# 1546081
2036 -- l_child_lpn nested_children_cursor%ROWTYPE;
2037 l_item_quantity NUMBER;
2038 l_null_cost_group_val NUMBER := -1 * fnd_api.g_miss_num;
2039
2040 CURSOR existing_record_cursor IS
2041 -- Bug# 1546081
2042 -- SELECT wlc.*
2043 SELECT wlc.quantity,
2044 wlc.uom_code
2045 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
2046 WHERE wlc.parent_lpn_id = p_lpn_id
2047 AND wlc.organization_id = p_organization_id
2048 AND wlc.inventory_item_id = p_content_item_id
2049 AND NVL(wlc.revision, '###') = NVL(p_revision, '###')
2050 AND NVL(wlc.lot_number, '###') = NVL(p_lot_number, '###')
2051 AND NVL(wlc.serial_number, '###') = NVL(l_current_serial, '###')
2052 AND NVL(wlc.cost_group_id, l_null_cost_group_val) = NVL(DECODE(wlpn.lpn_context, 3, wlc.cost_group_id, p_cost_group_id), l_null_cost_group_val)
2053 AND NVL(wlc.source_type_id, -9999) = NVL(p_source_type_id, -9999)
2054 AND NVL(wlc.source_header_id, -9999) = NVL(p_source_header_id, -9999)
2055 AND NVL(wlc.source_line_id, -9999) = NVL(p_source_line_id, -9999)
2056 AND NVL(wlc.source_line_detail_id, -9999) = NVL(p_source_line_detail_id, -9999)
2057 AND NVL(wlc.source_name, '###') = NVL(p_source_name, '###')
2058 AND wlc.parent_lpn_id = wlpn.lpn_id
2059 AND NVL(wlc.serial_summary_entry, 2) = 2;
2060
2061 -- Bug# 1546081
2062 l_existing_record_cursor existing_record_cursor%ROWTYPE;
2063
2064 CURSOR existing_unpack_record_cursor IS
2065 -- Bug# 1546081
2066 -- SELECT wlc.*
2067 SELECT wlc.quantity,
2068 wlc.uom_code,
2069 wlc.source_type_id,
2070 wlc.source_header_id,
2071 wlc.source_line_id,
2072 wlc.source_line_detail_id,
2073 wlc.source_name,
2074 wlc.cost_group_id
2075 FROM wms_lpn_contents wlc, wms_license_plate_numbers wlpn
2076 WHERE wlc.parent_lpn_id = p_lpn_id
2077 AND wlc.organization_id = p_organization_id
2078 AND wlc.inventory_item_id = p_content_item_id
2079 AND NVL(wlc.revision, '###') = NVL(p_revision, '###')
2080 AND NVL(wlc.lot_number, '###') = NVL(p_lot_number, '###')
2081 AND NVL(wlc.serial_number, '###') = NVL(l_current_serial, '###')
2082 --AND NVL(wlc.cost_group_id, l_null_cost_group_val) = NVL(DECODE(wlpn.lpn_context, 3, wlc.cost_group_id, NVL(p_cost_group_id, wlc.cost_group_id)), l_null_cost_group_val)
2083 AND NVL(wlc.source_type_id, -9999) = NVL(p_source_type_id, NVL(wlc.source_type_id, -9999))
2084 AND NVL(wlc.source_header_id, -9999) = NVL(p_source_header_id, NVL(wlc.source_header_id, -9999))
2085 AND NVL(wlc.source_line_id, -9999) = NVL(p_source_line_id, NVL(wlc.source_line_id, -9999))
2086 AND NVL(wlc.source_line_detail_id, -9999) = NVL(p_source_line_detail_id, NVL(wlc.source_line_detail_id, -9999))
2087 AND NVL(wlc.source_name, '###') = NVL(p_source_name, NVL(wlc.source_name, '###'))
2088 AND wlc.parent_lpn_id = wlpn.lpn_id
2089 AND NVL(wlc.serial_summary_entry, 2) = 2
2090 AND (NVL(wlc.source_name, '###') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2091 OR NVL(p_source_name, '###') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2092 )
2093 ORDER BY wlc.source_type_id DESC, wlc.source_header_id DESC, wlc.source_line_id DESC, wlc.source_line_detail_id DESC, wlc.source_name DESC;
2094
2095 /* FP-J Lot/Serial Support Enhancements
2096 * Add current status of resides in receiving
2097 */
2098 CURSOR serial_validation_cursor IS
2099 SELECT 'Validate Serial'
2100 FROM DUAL
2101 WHERE EXISTS( SELECT 'X'
2102 FROM mtl_serial_numbers
2103 WHERE inventory_item_id = p_content_item_id
2104 AND current_organization_id = p_organization_id
2105 AND serial_number = l_current_serial
2106 AND current_status IN (1, 4, 5, 6, 7));
2107
2108 CURSOR lot_validation_cursor IS
2109 SELECT 'Validate Lot'
2110 FROM DUAL
2111 WHERE EXISTS( SELECT 'X'
2112 FROM mtl_lot_numbers
2113 WHERE inventory_item_id = p_content_item_id
2114 AND organization_id = p_organization_id
2115 AND lot_number = p_lot_number);
2116
2117 CURSOR one_time_item_cursor IS
2118 SELECT quantity
2119 FROM wms_lpn_contents
2120 WHERE parent_lpn_id = p_lpn_id
2121 AND organization_id = p_organization_id
2122 AND item_description = p_content_item_desc
2123 AND NVL(cost_group_id, l_null_cost_group_val) = NVL(p_cost_group_id, l_null_cost_group_val)
2124 AND NVL(serial_summary_entry, 2) = l_serial_summary_entry;
2125
2126 l_temp_record existing_unpack_record_cursor%ROWTYPE;
2127 l_temp_lot_exist VARCHAR2(20);
2128 l_temp_serial_exist VARCHAR2(20);
2129 l_prefix VARCHAR2(30);
2130 l_quantity NUMBER;
2131 l_from_number NUMBER;
2132 l_to_number NUMBER;
2133 l_errorcode NUMBER;
2134 l_length NUMBER;
2135 l_padded_length NUMBER;
2136 l_current_number NUMBER;
2137 l_valid_operation NUMBER;
2138 l_converted_quantity NUMBER;
2139 l_exploded_table wms_container_tbl_type;
2140 l_table_index BINARY_INTEGER;
2141 l_temp_outermost_lpn VARCHAR2(30);
2142 l_lpn_history_id NUMBER;
2143 l_lpn_controlled_flag NUMBER;
2144 l_lpn_is_empty NUMBER;
2145 l_lpn_context NUMBER;
2146
2147 CURSOR nested_container_cursor IS
2148 -- Bug# 1546081
2149 -- SELECT *
2150 SELECT lpn_id,
2151 inventory_item_id
2152 FROM wms_license_plate_numbers
2153 START WITH lpn_id = p_lpn_id
2154 CONNECT BY parent_lpn_id = PRIOR lpn_id;
2155
2156 l_dynamic_status NUMBER;
2157 l_exist_variable NUMBER;
2158 l_temp_quantity NUMBER;
2159 l_temp_uom_code VARCHAR2(3);
2160 l_temp_count NUMBER;
2161 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2162 BEGIN
2163 -- Standard Start of API savepoint
2164 SAVEPOINT packunpack_container_pub;
2165
2166 -- Standard call to check for call compatibility.
2167 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2168 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
2169 fnd_msg_pub.ADD;
2170 RAISE fnd_api.g_exc_unexpected_error;
2171 END IF;
2172
2173 -- Initialize message list if p_init_msg_list is set to TRUE.
2174 IF fnd_api.to_boolean(p_init_msg_list) THEN
2175 fnd_msg_pub.initialize;
2176 END IF;
2177
2178 -- Initialize API return status to success
2179 x_return_status := fnd_api.g_ret_sts_success;
2180
2181 -- API body
2182 IF (l_debug = 1) THEN
2183 mdebug('Call to Packunpack API', G_MESSAGE);
2184 mdebug('orgid=' ||p_organization_id|| ' sub=' ||p_subinventory|| ' loc=' ||p_locator_id|| ' lpnid=' ||p_lpn_id|| ' cntlpn=' ||p_content_lpn_id, G_INFO);
2185 mdebug('itemid=' ||p_content_item_id|| ' rev=' ||p_revision|| ' lot=' ||p_lot_number|| ' fmsn=' ||p_from_serial_number|| ' tosn=' ||p_to_serial_number, G_INFO);
2186 mdebug('qty=' ||p_quantity|| ' uom=' ||p_uom|| ' cg=' ||p_cost_group_id|| ' oper=' ||p_operation|| ' srctype=' ||p_source_type_id||' trxact='||p_trx_action_id, G_INFO);
2187 END IF;
2188
2189 /* Validate LPN */
2190 l_lpn.lpn_id := p_lpn_id;
2191 l_lpn.license_plate_number := NULL;
2192 l_result := validate_lpn(l_lpn, 1);
2193
2194 IF (l_result = inv_validate.f) THEN
2195 IF (l_debug = 1) THEN
2196 mdebug(p_lpn_id || 'is an invalid lpn_id', G_ERROR);
2197 END IF;
2198 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
2199 fnd_msg_pub.ADD;
2200 RAISE fnd_api.g_exc_error;
2201 END IF;
2202
2203 /* Validate all inputs if validation level is set to full */
2204 IF (p_validation_level = fnd_api.g_valid_level_full) THEN
2205 /* Validate Content LPN */
2206 IF (p_content_lpn_id IS NOT NULL) THEN
2207 l_content_lpn.lpn_id := p_content_lpn_id;
2208 l_result := validate_lpn(l_content_lpn);
2209
2210 IF (l_result = inv_validate.f) THEN
2211 IF (l_debug = 1) THEN
2212 mdebug(p_content_lpn_id || 'is and invalid content lpn id', G_ERROR);
2213 END IF;
2214 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_CONTENT_LPN');
2215 fnd_msg_pub.ADD;
2216 RAISE fnd_api.g_exc_error;
2217 END IF;
2218
2219 -- Check that the content lpn is in fact stored within the given parent lpn
2220 -- Do this check only for the unpack operation
2221 IF (p_operation in (2, 5)) THEN
2222 IF (l_content_lpn.parent_lpn_id <> l_lpn.lpn_id) THEN
2223 IF (l_debug = 1) THEN
2224 mdebug('child lpn is not in lpn parent lpn', G_ERROR);
2225 END IF;
2226 fnd_message.set_name('WMS', 'WMS_CONT_LPN_NOT_IN_LPN');
2227 fnd_msg_pub.ADD;
2228 RAISE fnd_api.g_exc_error;
2229 END IF;
2230 END IF;
2231 END IF;
2232
2233 /* Validate Organization ID */
2234 l_org.organization_id := p_organization_id;
2235 l_result := inv_validate.ORGANIZATION(l_org);
2236
2237 IF (l_result = inv_validate.f) THEN
2238 IF (l_debug = 1) THEN
2239 mdebug(p_organization_id || 'is not a valid org_id', G_ERROR);
2240 END IF;
2241 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
2242 fnd_msg_pub.ADD;
2243 RAISE fnd_api.g_exc_error;
2244 END IF;
2245
2246 /* Validate Content Item */
2247 IF (p_content_item_id IS NOT NULL) THEN
2248 l_content_item.inventory_item_id := p_content_item_id;
2249 l_result := inv_validate.inventory_item(l_content_item, l_org);
2250
2251 IF (l_result = inv_validate.f) THEN
2252 IF (l_debug = 1) THEN
2253 mdebug(p_content_item_id || 'is not a valid content item id', G_ERROR);
2254 END IF;
2255 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_CONTENT_ITEM');
2256 fnd_msg_pub.ADD;
2257 RAISE fnd_api.g_exc_error;
2258 END IF;
2259 END IF;
2260
2261 /* Check that a content is given to pack/unpack */
2262 IF ( p_content_lpn_id IS NULL AND
2263 p_content_item_id IS NULL AND
2264 p_content_item_desc IS NULL ) THEN
2265 -- Note that if the content item description is the only content
2266 -- value passed in, then we are assuming that it is a one time item
2267 /* If unpacking everything, then a content is not required */
2268 IF ( NOT (p_operation = 4 OR (p_unpack_all = 1 AND p_operation = 2)) ) THEN
2269 IF (l_debug = 1) THEN
2270 mdebug('no item description for unpack all', G_ERROR);
2271 END IF;
2272 fnd_message.set_name('WMS', 'WMS_CONT_NO_ITEM_DESC');
2273 fnd_msg_pub.ADD;
2274 RAISE fnd_api.g_exc_error;
2275 END IF;
2276 END IF;
2277
2278 /* Validate that only a content item OR LPN, not both, is given */
2279 IF ((p_content_lpn_id IS NOT NULL)
2280 AND (p_content_item_id IS NOT NULL)
2281 ) THEN
2282 IF (l_debug = 1) THEN
2283 mdebug('Can not specify both content item and container item at same time', G_ERROR);
2284 END IF;
2285 fnd_message.set_name('WMS', 'WMS_CONT_LPN_AND_ITEM');
2286 fnd_msg_pub.ADD;
2287 RAISE fnd_api.g_exc_error;
2288 END IF;
2289
2290 /* Validate that Subinventory must be given if pack operation and in INV*/
2291 IF (p_content_item_id IS NOT NULL) THEN
2292 IF (p_operation = 1) THEN
2293 IF (l_lpn.lpn_context IN (1, 11)) THEN
2294 IF (p_subinventory IS NULL) THEN
2295 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SUB');
2296 fnd_msg_pub.ADD;
2297 RAISE fnd_api.g_exc_error;
2298 END IF;
2299 END IF;
2300 END IF;
2301 END IF;
2302
2303 /* Validate Subinventory */
2304 IF (p_subinventory IS NOT NULL) THEN
2305 l_sub.secondary_inventory_name := p_subinventory;
2306 l_result := inv_validate.subinventory(l_sub, l_org);
2307
2308 IF (l_result = inv_validate.f) THEN
2309 IF (l_debug = 1) THEN
2310 mdebug(p_subinventory || 'is an invalid sub', G_ERROR);
2311 END IF;
2312 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SUB');
2313 fnd_msg_pub.ADD;
2314 RAISE fnd_api.g_exc_error;
2315 END IF;
2316 END IF;
2317
2318 --Validate Locator
2319 IF (l_org.stock_locator_control_code <> 1) THEN
2320 IF (p_subinventory IS NOT NULL) THEN
2321 IF (l_org.stock_locator_control_code IN (2, 3)
2322 OR (l_org.stock_locator_control_code = 4
2323 AND l_sub.locator_type IN (2, 3)
2324 )
2325 OR (l_org.stock_locator_control_code = 5
2326 AND l_content_item.location_control_code IN (2, 3)
2327 )
2328 OR (l_org.stock_locator_control_code = 4
2329 AND l_sub.locator_type = 5
2330 AND l_content_item.location_control_code IN (2, 3)
2331 )
2332 ) THEN
2333 --IF (l_org.stock_locator_control_code = 4 AND
2334 --(l_sub.locator_type <> 1 OR l_sub.locator_type = 5
2335 --AND l_content_item.location_control_code <> 1)) THEN
2336 --IF (l_org.stock_locator_control_code = 5 AND
2337 --l_content_item.location_control_code <> 1) THEN
2338 IF (p_locator_id IS NULL) THEN
2339 IF (l_debug = 1) THEN
2340 mdebug('Missing required locator', G_ERROR);
2341 END IF;
2342 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOC');
2343 fnd_msg_pub.ADD;
2344 RAISE fnd_api.g_exc_error;
2345 END IF;
2346
2347 l_locator.inventory_location_id := p_locator_id;
2348 l_result := inv_validate.validatelocator(l_locator, l_org, l_sub);
2349
2350 IF (l_result = inv_validate.f) THEN
2351 IF (l_debug = 1) THEN
2352 mdebug(p_locator_id || ' is an invalid locator_id', G_ERROR);
2353 END IF;
2354 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOC');
2355 fnd_msg_pub.ADD;
2356 RAISE fnd_api.g_exc_error;
2357 END IF;
2358 --END IF;
2359 END IF;
2360 END IF;
2361 END IF;
2362
2363 /* Validate Revision */
2364 IF (p_content_item_id IS NOT NULL) THEN
2365 IF (l_content_item.revision_qty_control_code = 2) THEN
2366 IF (p_revision IS NOT NULL) THEN
2367 l_result := inv_validate.revision(p_revision, l_org, l_content_item);
2368
2369 IF (l_result = inv_validate.f) THEN
2370 IF (l_debug = 1) THEN
2371 mdebug(p_revision || ' is an invalid revision', G_ERROR);
2372 END IF;
2373 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_REV');
2374 fnd_msg_pub.ADD;
2375 RAISE fnd_api.g_exc_error;
2376 END IF;
2377 ELSE
2378 IF (l_debug = 1) THEN
2379 mdebug('Mission required revision', G_ERROR);
2380 END IF;
2381 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_REV');
2382 fnd_msg_pub.ADD;
2383 RAISE fnd_api.g_exc_error;
2384 END IF;
2385 END IF;
2386 END IF;
2387
2388 /* Validate Lot */
2389 /* Sub and locator might not be given in the case of pre-packing */
2390 IF (p_content_item_id IS NOT NULL) THEN
2391 IF (l_content_item.lot_control_code = 2
2392 AND NOT (NVL(p_trx_action_id, -9999) = inv_globals.g_action_inv_lot_split)
2393 AND NOT (NVL(p_trx_action_id, -9999) = inv_globals.g_action_inv_lot_merge)
2394 ) THEN
2395 IF (p_lot_number IS NOT NULL) THEN
2396 -- Do lot validation only if the container/item is in INV,
2397 -- not WIP or REC since dynamic lots are possible in WIP and REC.
2398 IF (l_lpn.lpn_context IN (1, 11)) THEN
2399 IF (p_subinventory IS NOT NULL) THEN
2400 l_lot.lot_number := p_lot_number;
2401
2402 SELECT COUNT(*)
2403 INTO l_temp_count
2404 FROM mtl_lot_numbers
2405 WHERE organization_id = p_organization_id
2406 AND lot_number = p_lot_number
2407 AND inventory_item_id = p_content_item_id;
2408
2409 IF l_temp_count > 0 THEN
2410 l_result := inv_validate.lot_number(l_lot, l_org, l_content_item, l_sub, l_locator, p_revision);
2411
2412 IF (l_result = inv_validate.f) THEN
2413 IF (l_debug = 1) THEN
2414 mdebug(p_lot_number || ' is an invalid lot number', G_ERROR);
2415 END IF;
2416 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOT');
2417 fnd_msg_pub.ADD;
2418 RAISE fnd_api.g_exc_error;
2419 END IF;
2420 END IF;
2421 ELSE
2422 -- Subinventory was not given so will need to do
2423 -- alternative non-standard lot number validation.
2424 OPEN lot_validation_cursor;
2425 FETCH lot_validation_cursor INTO l_temp_lot_exist;
2426
2427 IF lot_validation_cursor%NOTFOUND THEN
2428 IF (l_debug = 1) THEN
2429 mdebug(p_lot_number || ' is an invalid lot number', G_ERROR);
2430 END IF;
2431 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LOT');
2432 fnd_msg_pub.ADD;
2433 RAISE fnd_api.g_exc_error;
2434 END IF;
2435
2436 CLOSE lot_validation_cursor;
2437 END IF;
2438 ELSE
2439 IF (l_debug = 1) THEN
2440 mdebug('Container not in INV', G_MESSAGE);
2441 END IF;
2442 END IF;
2443 ELSE
2444 IF (l_debug = 1) THEN
2445 mdebug('Missing required lot', G_ERROR);
2446 END IF;
2447 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOT');
2448 fnd_msg_pub.ADD;
2449 RAISE fnd_api.g_exc_error;
2450 END IF;
2451 END IF;
2452 END IF;
2453
2454 /* Validate Quantity if item is not serial controlled */
2455 IF (p_content_item_id IS NOT NULL) THEN
2456 IF (l_content_item.serial_number_control_code IN (1, 6)
2457 OR l_lpn.lpn_context = WMS_CONTAINER_PVT.LPN_PREPACK_FOR_WIP
2458 ) THEN
2459 IF (p_quantity <= 0) THEN
2460 IF (l_debug = 1) THEN
2461 mdebug('Requested a negative item qty', G_ERROR);
2462 END IF;
2463 fnd_message.set_name('WMS', 'WMS_CONT_NEG_ITEM_QTY');
2464 fnd_msg_pub.ADD;
2465 RAISE fnd_api.g_exc_error;
2466 ELSE
2467 l_quantity := p_quantity;
2468 END IF;
2469 END IF;
2470 END IF;
2471
2472 /* Check that if a content LPN is given, then quantity, */
2473 /* if given, must be equal to 1 */
2474 IF ((p_content_item_id IS NULL)
2475 AND (p_content_lpn_id IS NOT NULL)
2476 ) THEN
2477 IF (p_quantity IS NOT NULL) THEN
2478 IF (p_quantity <> 1) THEN
2479 IF (l_debug = 1) THEN
2480 mdebug('For container item unpack quantitiy must be 1', G_ERROR);
2481 END IF;
2482 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_QTY');
2483 fnd_msg_pub.ADD;
2484 RAISE fnd_api.g_exc_error;
2485 END IF;
2486 ELSE
2487 l_quantity := 1;
2488 END IF;
2489 END IF;
2490
2491 -- Validate Serial
2492 -- Sub and locator might not be given in the case of pre-packing
2493 IF (p_content_item_id IS NOT NULL) THEN
2494 IF (l_content_item.serial_number_control_code NOT IN (1, 6)) THEN
2495 IF ((p_from_serial_number IS NOT NULL) AND (p_to_serial_number IS NOT NULL)) THEN
2496 IF (l_debug = 1) THEN
2497 mdebug('Call this API to parse sn ' || p_from_serial_number||'-'||p_to_serial_number, G_MESSAGE);
2498 END IF;
2499 IF (NOT mtl_serial_check.inv_serial_info(p_from_serial_number, p_to_serial_number, l_prefix, l_quantity, l_from_number, l_to_number, l_errorcode)) THEN
2500 IF (l_debug = 1) THEN
2501 mdebug('Invalid serial number given in range', G_ERROR);
2502 END IF;
2503 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
2504 fnd_msg_pub.ADD;
2505 RAISE fnd_api.g_exc_error;
2506 END IF;
2507
2508 -- Check that in the case of a range of serial numbers, that the
2509 -- inputted p_quantity equals the amount of items in the serial range.
2510 IF (p_quantity IS NOT NULL) THEN
2511 IF (p_quantity <> l_quantity) THEN
2512 IF (l_debug = 1) THEN
2513 mdebug('Serial range quantity '||l_quantity||' not the same as given qty '||p_quantity, G_ERROR);
2514 END IF;
2515 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_X_QTY');
2516 fnd_msg_pub.ADD;
2517 RAISE fnd_api.g_exc_error;
2518 END IF;
2519 END IF;
2520
2521 -- Get the serial number length.
2522 -- Note that the from and to serial numbers must be of the same length.
2523 l_length := LENGTH(p_from_serial_number);
2524
2525 -- If lpn context is not inventory, bypass serial number validation
2526 IF (NOT l_lpn.lpn_context IN (1, 11)) THEN
2527 -- Initialize the current pointer variables
2528 l_current_serial := p_from_serial_number;
2529 l_current_number := l_from_number;
2530
2531 LOOP
2532 -- Get the serial number current status for the current
2533 -- serial number to check if it was dynamically generated
2534 SELECT COUNT(*)
2535 INTO l_dynamic_status
2536 FROM mtl_serial_numbers
2537 WHERE inventory_item_id = p_content_item_id
2538 AND serial_number = l_current_serial
2539 AND current_organization_id = p_organization_id
2540 AND current_status = 6;
2541
2542 IF ((p_subinventory IS NOT NULL) AND (l_dynamic_status = 0)) THEN
2543 l_serial.serial_number := l_current_serial;
2544 l_lot.lot_number := p_lot_number; -- Added for bug 8775286
2545 l_result := inv_validate.validate_serial(l_serial, l_org, l_content_item, l_sub, l_lot, l_locator, p_revision);
2546
2547 IF (l_result = inv_validate.f) THEN
2548 IF (l_debug = 1) THEN
2549 mdebug(l_current_serial || ' is not a valid serial number', G_ERROR);
2550 END IF;
2551 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
2552 fnd_msg_pub.ADD;
2553 RAISE fnd_api.g_exc_error;
2554 END IF;
2555 ELSE
2556 -- Either the subinventory was not given or
2557 -- the serial number was dynamically generated. We will
2558 -- need to do alternative non-standard serial number validation.
2559 OPEN serial_validation_cursor;
2560 FETCH serial_validation_cursor INTO l_temp_serial_exist;
2561
2562 IF serial_validation_cursor%NOTFOUND THEN
2563 IF (l_debug = 1) THEN
2564 mdebug(l_current_serial || ' is not a valid serial number', G_ERROR);
2565 END IF;
2566 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
2567 fnd_msg_pub.ADD;
2568 RAISE fnd_api.g_exc_error;
2569 END IF;
2570
2571 CLOSE serial_validation_cursor;
2572 END IF;
2573
2574 EXIT WHEN l_current_serial = p_to_serial_number;
2575 /* Increment the current serial number */
2576 l_current_number := l_current_number + 1;
2577 l_padded_length := l_length - LENGTH(l_current_number);
2578
2579 IF l_prefix IS NOT NULL THEN
2580 l_current_serial := RPAD(l_prefix, l_padded_length, '0') || l_current_number;
2581 ELSE
2582 l_current_serial := Rpad('@',l_padded_length+1,'0') || l_current_number;
2583 l_current_serial := Substr(l_current_serial,2);
2584 END IF;
2585
2586 -- Bug 2375043
2587 --l_current_serial := RPAD(l_prefix, l_padded_length, '0') ||
2588 --l_current_number;
2589 END LOOP;
2590 END IF;
2591 ELSIF (l_lpn.lpn_context = WMS_CONTAINER_PVT.LPN_PREPACK_FOR_WIP) THEN
2592 -- If lpn context is prepacked for WIP, user does not need to specify sn
2593 -- Needs to be treated like a non serial item, no mtl_serial_number entry
2594 -- except serial_summary_entry flag should be set.
2595 l_serial_summary_entry := 1;
2596 ELSE
2597 IF (l_debug = 1) THEN
2598 mdebug('Missing require serial number', G_ERROR);
2599 END IF;
2600 fnd_message.set_name('WMS', 'WMS_CONT_MISS_SER_NUM');
2601 fnd_msg_pub.ADD;
2602 RAISE fnd_api.g_exc_error;
2603 END IF;
2604 END IF;
2605 END IF;
2606
2607 /* Validate content item UOM */
2608 IF (p_content_item_id IS NOT NULL) THEN
2609 l_result := inv_validate.uom(p_uom, l_org, l_content_item);
2610
2611 IF (l_result = inv_validate.f) THEN
2612 IF (l_debug = 1) THEN
2613 mdebug(p_uom || ' is an invalid UOM', G_ERROR);
2614 END IF;
2615 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_UOM');
2616 fnd_msg_pub.ADD;
2617 RAISE fnd_api.g_exc_error;
2618 END IF;
2619 END IF;
2620
2621 /* Validate the operation */
2622 IF ( p_operation < 1 OR p_operation > 5 ) THEN
2623 IF (l_debug = 1) THEN
2624 mdebug(p_operation || ' is an invalid operation type', G_ERROR);
2625 END IF;
2626 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_OPERATION');
2627 fnd_msg_pub.ADD;
2628 RAISE fnd_api.g_exc_error;
2629 END IF;
2630
2631 /* Validate the enforce weight and volume constraint flag */
2632 IF (p_enforce_wv_constraints IS NOT NULL) THEN
2633 IF ((p_enforce_wv_constraints <> 1) AND (p_enforce_wv_constraints <> 2)) THEN
2634 IF (l_debug = 1) THEN
2635 mdebug(p_enforce_wv_constraints || ' is an invalid constraint type', G_MESSAGE);
2636 END IF;
2637 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_CONSTRAINT');
2638 fnd_msg_pub.ADD;
2639 RAISE fnd_api.g_exc_error;
2640 END IF;
2641 END IF;
2642
2643 /* Validate Cost Group */
2644 IF (p_cost_group_id IS NOT NULL) THEN
2645 l_result := inv_validate.cost_group(p_cost_group_id, p_organization_id);
2646
2647 IF (l_result = inv_validate.f) THEN
2648 IF (l_debug = 1) THEN
2649 mdebug(p_cost_group_id || ' is an invalid cost group is', G_ERROR);
2650 END IF;
2651 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_CST_GRP');
2652 fnd_msg_pub.ADD;
2653 RAISE fnd_api.g_exc_error;
2654 END IF;
2655 END IF;
2656 END IF;
2657 /* End of Input Validation */
2658
2659 IF ( p_operation = 5 ) THEN
2660 -- Set operation to unpack but ignore the lots/and serials
2661 l_operation := 2;
2662 l_ignore_item_controls := 1;
2663 ELSIF ( l_lpn.lpn_context = 4 AND p_trx_action_id = 8 ) THEN
2664 -- Change operation to new Adjust type
2665 l_operation := 3;
2666 ELSIF ( p_unpack_all = 1 ) THEN
2667 -- Change operation to new Unpack All type
2668 l_operation := 4;
2669 END IF;
2670
2671 WMS_CONTAINER_PVT.PackUnpack_Container (
2672 p_api_version => p_api_version
2673 , p_init_msg_list => p_init_msg_list
2674 , p_commit => p_commit
2675 , p_validation_level => p_validation_level
2676 , x_return_status => x_return_status
2677 , x_msg_count => x_msg_count
2678 , x_msg_data => x_msg_data
2679 , p_lpn_id => p_lpn_id
2680 , p_content_lpn_id => p_content_lpn_id
2681 , p_content_item_id => p_content_item_id
2682 , p_content_item_desc => p_content_item_desc
2683 , p_revision => p_revision
2684 , p_lot_number => p_lot_number
2685 , p_from_serial_number => p_from_serial_number
2686 , p_to_serial_number => p_to_serial_number
2687 , p_quantity => p_quantity
2688 , p_uom => p_uom
2689 , p_sec_quantity => p_sec_quantity --INCONV kkillams
2690 , p_sec_uom => p_sec_uom --INCONV kkillams
2691 , p_organization_id => p_organization_id
2692 , p_subinventory => p_subinventory
2693 , p_locator_id => p_locator_id
2694 , p_enforce_wv_constraints => p_enforce_wv_constraints
2695 , p_operation => l_operation
2696 , p_cost_group_id => p_cost_group_id
2697 , p_source_type_id => p_source_type_id
2698 , p_source_header_id => p_source_header_id
2699 , p_source_name => p_source_name
2700 , p_source_line_id => p_source_line_id
2701 , p_source_line_detail_id => p_source_line_detail_id
2702 , p_unpack_all => p_unpack_all
2703 , p_ignore_item_controls => l_ignore_item_controls );
2704
2705 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
2706 RAISE fnd_api.g_exc_error;
2707 END IF;
2708
2709 -- End of API body
2710
2711 -- Standard check of p_commit.
2712 IF fnd_api.to_boolean(p_commit) THEN
2713 COMMIT WORK;
2714 END IF;
2715
2716 -- Standard call to get message count and if count is 1,
2717 -- get message info.
2718 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2719 EXCEPTION
2720 WHEN fnd_api.g_exc_error THEN
2721 ROLLBACK TO packunpack_container_pub;
2722 x_return_status := fnd_api.g_ret_sts_error;
2723 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2724 WHEN fnd_api.g_exc_unexpected_error THEN
2725 ROLLBACK TO packunpack_container_pub;
2726 x_return_status := fnd_api.g_ret_sts_unexp_error;
2727 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2728 WHEN OTHERS THEN
2729 ROLLBACK TO packunpack_container_pub;
2730 x_return_status := fnd_api.g_ret_sts_unexp_error;
2731
2732 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2733 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
2734 END IF;
2735
2736 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
2737 END packunpack_container;
2738
2739 PROCEDURE pack_prepack_container(
2740 p_api_version IN NUMBER,
2741 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2742 p_commit IN VARCHAR2 := fnd_api.g_false,
2743 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
2744 x_return_status OUT NOCOPY VARCHAR2,
2745 x_msg_count OUT NOCOPY NUMBER,
2746 x_msg_data OUT NOCOPY VARCHAR2,
2747 p_lpn_id IN NUMBER,
2748 p_content_item_id IN NUMBER := NULL,
2749 p_revision IN VARCHAR2 := NULL,
2750 p_lot_number IN VARCHAR2 := NULL,
2751 p_from_serial_number IN VARCHAR2 := NULL,
2752 p_to_serial_number IN VARCHAR2 := NULL,
2753 p_quantity IN NUMBER := 1,
2754 p_uom IN VARCHAR2 := NULL,
2755 p_organization_id IN NUMBER,
2756 p_operation IN NUMBER,
2757 p_source_type_id IN NUMBER := NULL
2758 ) IS
2759 l_api_name CONSTANT VARCHAR2(30) := 'pack_prepack_container';
2760 l_api_version CONSTANT NUMBER := 1.0;
2761 l_lpn lpn;
2762 l_content_lpn lpn;
2763 l_content_item inv_validate.item;
2764 l_org inv_validate.org;
2765 l_lot inv_validate.lot;
2766 l_serial inv_validate.serial;
2767 l_current_serial VARCHAR2(30) := p_from_serial_number;
2768 l_result NUMBER;
2769 l_serial_summary_entry NUMBER := 2;
2770
2771 /* FP-J Lot/Serial Support Enhancements
2772 * Add current status of resides in receiving
2773 */
2774 CURSOR serial_validation_cursor IS
2775 SELECT 'Validate Serial'
2776 FROM DUAL
2777 WHERE EXISTS( SELECT 'X'
2778 FROM mtl_serial_numbers
2779 WHERE inventory_item_id = p_content_item_id
2780 AND current_organization_id = p_organization_id
2781 AND serial_number = l_current_serial
2782 AND current_status IN (1, 5, 6, 7));
2783
2784 l_temp_serial_exist VARCHAR2(20);
2785 l_prefix VARCHAR2(30);
2786 l_quantity NUMBER;
2787 l_from_number NUMBER;
2788 l_to_number NUMBER;
2789 l_errorcode NUMBER;
2790 l_length NUMBER;
2791 l_padded_length NUMBER;
2792 l_current_number NUMBER;
2793 l_table_index BINARY_INTEGER;
2794 l_dynamic_status NUMBER;
2795 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2796 BEGIN
2797 -- Standard Start of API savepoint
2798 SAVEPOINT pack_prepack_container_pub;
2799
2800 -- Standard call to check for call compatibility.
2801 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2802 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
2803 fnd_msg_pub.ADD;
2804 RAISE fnd_api.g_exc_unexpected_error;
2805 END IF;
2806
2807 -- Initialize message list if p_init_msg_list is set to TRUE.
2808 IF fnd_api.to_boolean(p_init_msg_list) THEN
2809 fnd_msg_pub.initialize;
2810 END IF;
2811
2812 -- Initialize API return status to success
2813 x_return_status := fnd_api.g_ret_sts_success;
2814
2815 -- API body
2816 IF (l_debug = 1) THEN
2817 mdebug('Call to Pack_Prepack_Container API', G_MESSAGE);
2818 mdebug('lpnid=' ||p_lpn_id|| ' orgid=' ||p_organization_id||' itemid=' ||p_content_item_id, G_INFO);
2819 mdebug('rev=' ||p_revision|| ' lot=' ||p_lot_number|| ' fmsn=' ||p_from_serial_number|| ' tosn=' ||p_to_serial_number, G_INFO);
2820 mdebug('qty=' ||p_quantity|| ' uom=' ||p_uom|| ' oper=' ||p_operation|| ' srctype=' ||p_source_type_id, G_INFO);
2821 END IF;
2822
2823 /* Validate all inputs if validation level is set to full */
2824 IF (p_validation_level = fnd_api.g_valid_level_full) THEN
2825 /* Validate LPN */
2826 l_lpn.lpn_id := p_lpn_id;
2827 l_lpn.license_plate_number := NULL;
2828 l_result := validate_lpn(l_lpn);
2829
2830 IF (l_result = inv_validate.f) THEN
2831 IF (l_debug = 1) THEN
2832 mdebug(p_lpn_id || 'is an invalid lpn_id', 1);
2833 END IF;
2834 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_LPN');
2835 fnd_msg_pub.ADD;
2836 RAISE fnd_api.g_exc_error;
2837 END IF;
2838
2839 /* Validate Organization ID */
2840 l_org.organization_id := p_organization_id;
2841 l_result := inv_validate.ORGANIZATION(l_org);
2842
2843 IF (l_result = inv_validate.f) THEN
2844 IF (l_debug = 1) THEN
2845 mdebug(p_organization_id || 'is not a valid org_id', 1);
2846 END IF;
2847 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
2848 fnd_msg_pub.ADD;
2849 RAISE fnd_api.g_exc_error;
2850 END IF;
2851
2852 /* Validate Content Item */
2853 IF (p_content_item_id IS NOT NULL) THEN
2854 l_content_item.inventory_item_id := p_content_item_id;
2855 l_result := inv_validate.inventory_item(l_content_item, l_org);
2856
2857 IF (l_result = inv_validate.f) THEN
2858 IF (l_debug = 1) THEN
2859 mdebug(p_content_item_id || 'is not a valid content item id', 1);
2860 END IF;
2861 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_CONTENT_ITEM');
2862 fnd_msg_pub.ADD;
2863 RAISE fnd_api.g_exc_error;
2864 END IF;
2865 END IF;
2866
2867 /* Validate Revision */
2868 IF (p_content_item_id IS NOT NULL) THEN
2869 IF (l_content_item.revision_qty_control_code = 2) THEN
2870 IF (p_revision IS NOT NULL) THEN
2871 l_result := inv_validate.revision(p_revision, l_org, l_content_item);
2872
2873 IF (l_result = inv_validate.f) THEN
2874 IF (l_debug = 1) THEN
2875 mdebug(p_revision || ' is an invalid revision', 1);
2876 END IF;
2877 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_REV');
2878 fnd_msg_pub.ADD;
2879 RAISE fnd_api.g_exc_error;
2880 END IF;
2881 ELSE
2882 IF (l_debug = 1) THEN
2883 mdebug('Mission required revision', 1);
2884 END IF;
2885 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_REV');
2886 fnd_msg_pub.ADD;
2887 RAISE fnd_api.g_exc_error;
2888 END IF;
2889 END IF;
2890 END IF;
2891
2892 /* Validate Lot */
2893 /* Sub and locator might not be given in the case of pre-packing */
2894 IF (p_content_item_id IS NOT NULL) THEN
2895 IF (l_content_item.lot_control_code = 2) THEN
2896 IF (p_lot_number IS NULL) THEN
2897 IF (l_debug = 1) THEN
2898 mdebug('Missing required lot', 1);
2899 END IF;
2900 fnd_message.set_name('WMS', 'WMS_CONT_MISS_REQ_LOT');
2901 fnd_msg_pub.ADD;
2902 RAISE fnd_api.g_exc_error;
2903 END IF;
2904 END IF;
2905 END IF;
2906
2907 /* Validate Quantity if item is not serial controlled */
2908 IF (p_content_item_id IS NOT NULL) THEN
2909 IF (l_content_item.serial_number_control_code IN (1, 6)
2910 OR l_lpn.lpn_context = WMS_CONTAINER_PVT.LPN_PREPACK_FOR_WIP
2911 ) THEN
2912 IF (p_quantity <= 0) THEN
2913 IF (l_debug = 1) THEN
2914 mdebug('Requested a negative item qty', 1);
2915 END IF;
2916 fnd_message.set_name('WMS', 'WMS_CONT_NEG_ITEM_QTY');
2917 fnd_msg_pub.ADD;
2918 RAISE fnd_api.g_exc_error;
2919 ELSE
2920 l_quantity := p_quantity;
2921 END IF;
2922 END IF;
2923 END IF;
2924
2925 /* Validate Serial */
2926 /* Sub and locator might not be given in the case of pre-packing */
2927 IF (p_content_item_id IS NOT NULL) THEN
2928 IF (l_content_item.serial_number_control_code NOT IN (1, 6)) THEN
2929 IF ((p_from_serial_number IS NOT NULL)
2930 AND (p_to_serial_number IS NOT NULL)
2931 ) THEN
2932 /* Call this API to parse the serial numbers into prefixes and numbers */
2933 IF (NOT mtl_serial_check.inv_serial_info(p_from_serial_number, p_to_serial_number, l_prefix, l_quantity, l_from_number, l_to_number, l_errorcode)) THEN
2934 IF (l_debug = 1) THEN
2935 mdebug('Invalid serial number given in range', 1);
2936 END IF;
2937 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
2938 fnd_msg_pub.ADD;
2939 RAISE fnd_api.g_exc_error;
2940 END IF;
2941
2942 -- Check that in the case of a range of serial numbers, that the
2943 -- inputted p_quantity equals the amount of items in the serial range.
2944 IF (p_quantity IS NOT NULL) THEN
2945 IF (p_quantity <> l_quantity) THEN
2946 IF (l_debug = 1) THEN
2947 mdebug('Serial range quantity '||l_quantity||' not the same as given qty '||p_quantity, G_ERROR);
2948 END IF;
2949 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_X_QTY');
2950 fnd_msg_pub.ADD;
2951 RAISE fnd_api.g_exc_error;
2952 END IF;
2953 END IF;
2954
2955 -- Get the serial number length.
2956 -- Note that the from and to serial numbers must be of the same length.
2957 l_length := LENGTH(p_from_serial_number);
2958
2959 -- If lpn context is not inventory, bypass serial number validation
2960 IF (NOT l_lpn.lpn_context IN (1, 11)) THEN
2961 -- Initialize the current pointer variables
2962 l_current_serial := p_from_serial_number;
2963 l_current_number := l_from_number;
2964
2965 LOOP
2966 -- Get the serial number current status for the current
2967 -- serial number to check if it was dynamically generated
2968 SELECT COUNT(*)
2969 INTO l_dynamic_status
2970 FROM mtl_serial_numbers
2971 WHERE inventory_item_id = p_content_item_id
2972 AND serial_number = l_current_serial
2973 AND current_organization_id = p_organization_id
2974 AND current_status = 6;
2975
2976 IF ((l_dynamic_status = 0)) THEN
2977 NULL;
2978 ELSE
2979 -- Either the subinventory was not given or
2980 -- the serial number was dynamically generated. We will
2981 -- need to do alternative non-standard serial number validation.
2982 OPEN serial_validation_cursor;
2983 FETCH serial_validation_cursor INTO l_temp_serial_exist;
2984
2985 IF serial_validation_cursor%NOTFOUND THEN
2986 IF (l_debug = 1) THEN
2987 mdebug(l_current_serial || ' is not a valid serial number', 1);
2988 END IF;
2989 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_SER');
2990 fnd_msg_pub.ADD;
2991 RAISE fnd_api.g_exc_error;
2992 END IF;
2993
2994 CLOSE serial_validation_cursor;
2995 END IF;
2996
2997 EXIT WHEN l_current_serial = p_to_serial_number;
2998 /* Increment the current serial number */
2999 l_current_number := l_current_number + 1;
3000 l_padded_length := l_length - LENGTH(l_current_number);
3001 IF l_prefix IS NOT NULL THEN
3002 l_current_serial := RPAD(l_prefix, l_padded_length, '0') ||
3003 l_current_number;
3004 ELSE
3005 l_current_serial := Rpad('@',l_padded_length+1,'0')
3006 || l_current_number;
3007 l_current_serial := Substr(l_current_serial,2);
3008 END IF;
3009 -- Bug 2375043
3010 --l_current_serial := RPAD(l_prefix, l_padded_length, '0') || l_current_number;
3011 END LOOP;
3012 END IF;
3013 ELSIF (l_lpn.lpn_context = WMS_CONTAINER_PVT.LPN_PREPACK_FOR_WIP) THEN
3014 -- If lpn context is prepacked for WIP, user does not need to specify sn
3015 -- Needs to be treated like a non serial item, no mtl_serial_number entry
3016 -- except serial_summary_entry flag should be set.
3017 l_serial_summary_entry := 1;
3018 ELSE
3019 IF (l_debug = 1) THEN
3020 mdebug('Missing require serial number', 1);
3021 END IF;
3022 fnd_message.set_name('WMS', 'WMS_CONT_MISS_SER_NUM');
3023 fnd_msg_pub.ADD;
3024 RAISE fnd_api.g_exc_error;
3025 END IF;
3026 END IF;
3027 END IF;
3028
3029 /* Validate content item UOM */
3030 IF (p_content_item_id IS NOT NULL) THEN
3031 l_result := inv_validate.uom(p_uom, l_org, l_content_item);
3032
3033 IF (l_result = inv_validate.f) THEN
3034 IF (l_debug = 1) THEN
3035 mdebug(p_uom || ' is an invalid UOM', 1);
3036 END IF;
3037 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_UOM');
3038 fnd_msg_pub.ADD;
3039 RAISE fnd_api.g_exc_error;
3040 END IF;
3041 END IF;
3042
3043 /* Validate the operation */
3044 IF ((p_operation <> 1)) THEN
3045 IF (l_debug = 1) THEN
3046 mdebug(p_operation || ' is an invalid operation type', 1);
3047 END IF;
3048 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_OPERATION');
3049 fnd_msg_pub.ADD;
3050 RAISE fnd_api.g_exc_error;
3051 END IF;
3052 END IF;
3053 /* End of Input Validation */
3054
3055 WMS_CONTAINER_PVT.Pack_Prepack_Container (
3056 p_api_version => p_api_version
3057 , p_init_msg_list => p_init_msg_list
3058 , p_commit => p_commit
3059 , p_validation_level => p_validation_level
3060 , x_return_status => x_return_status
3061 , x_msg_count => x_msg_count
3062 , x_msg_data => x_msg_data
3063 , p_lpn_id => p_lpn_id
3064 , p_content_item_id => p_content_item_id
3065 , p_revision => p_revision
3066 , p_lot_number => p_lot_number
3067 , p_from_serial_number => p_from_serial_number
3068 , p_to_serial_number => p_to_serial_number
3069 , p_quantity => p_quantity
3070 , p_uom => p_uom
3071 , p_organization_id => p_organization_id
3072 , p_operation => p_operation
3073 , p_source_type_id => p_source_type_id );
3074
3075 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
3076 RAISE fnd_api.g_exc_error;
3077 END IF;
3078
3079 -- End of API body
3080
3081 -- Standard check of p_commit.
3082 IF fnd_api.to_boolean(p_commit) THEN
3083 COMMIT WORK;
3084 END IF;
3085
3086 -- Standard call to get message count and if count is 1,
3087 -- get message info.
3088 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3089 EXCEPTION
3090 WHEN fnd_api.g_exc_error THEN
3091 ROLLBACK TO pack_prepack_container_pub;
3092 x_return_status := fnd_api.g_ret_sts_error;
3093 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3094 WHEN fnd_api.g_exc_unexpected_error THEN
3095 ROLLBACK TO pack_prepack_container_pub;
3096 x_return_status := fnd_api.g_ret_sts_unexp_error;
3097 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3098 WHEN OTHERS THEN
3099 ROLLBACK TO pack_prepack_container_pub;
3100 x_return_status := fnd_api.g_ret_sts_unexp_error;
3101
3102 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3103 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3104 END IF;
3105
3106 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3107 END pack_prepack_container;
3108
3109 -- ----------------------------------------------------------------------------------
3110 -- ----------------------------------------------------------------------------------
3111 PROCEDURE explode_lpn(
3112 p_api_version IN NUMBER,
3113 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3114 p_commit IN VARCHAR2 := fnd_api.g_false,
3115 x_return_status OUT NOCOPY VARCHAR2,
3116 x_msg_count OUT NOCOPY NUMBER,
3117 x_msg_data OUT NOCOPY VARCHAR2,
3118 p_lpn_id IN NUMBER,
3119 p_explosion_level IN NUMBER := 0,
3120 x_content_tbl OUT NOCOPY wms_container_tbl_type
3121 ) IS
3122 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3123 BEGIN
3124 WMS_CONTAINER_PVT.explode_lpn(
3125 p_api_version=> p_api_version,
3126 p_init_msg_list=> p_init_msg_list,
3127 p_commit=> p_commit,
3128 x_return_status=> x_return_status,
3129 x_msg_count=> x_msg_count,
3130 x_msg_data=> x_msg_data,
3131 p_lpn_id=> p_lpn_id,
3132 p_explosion_level=> p_explosion_level,
3133 x_content_tbl=> x_content_tbl
3134 );
3135 END explode_lpn;
3136
3137 -- ----------------------------------------------------------------------------------
3138 -- ----------------------------------------------------------------------------------
3139 PROCEDURE container_required_qty(
3140 p_api_version IN NUMBER,
3141 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3142 p_commit IN VARCHAR2 := fnd_api.g_false,
3143 x_return_status OUT NOCOPY VARCHAR2,
3144 x_msg_count OUT NOCOPY NUMBER,
3145 x_msg_data OUT NOCOPY VARCHAR2,
3146 p_source_item_id IN NUMBER,
3147 p_source_qty IN NUMBER,
3148 p_source_qty_uom IN VARCHAR2,
3149 p_qty_per_cont IN NUMBER := NULL,
3150 p_qty_per_cont_uom IN VARCHAR2 := NULL,
3151 p_organization_id IN NUMBER,
3152 p_dest_cont_item_id IN OUT NOCOPY NUMBER,
3153 p_qty_required OUT NOCOPY NUMBER
3154 ) IS
3155 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3156 BEGIN
3157 WMS_CONTAINER_PVT.container_required_qty(
3158 p_api_version=> p_api_version,
3159 p_init_msg_list=> p_init_msg_list,
3160 p_commit=> p_commit,
3161 x_return_status=> x_return_status,
3162 x_msg_count=> x_msg_count,
3163 x_msg_data=> x_msg_data,
3164 p_source_item_id=> p_source_item_id,
3165 p_source_qty=> p_source_qty,
3166 p_source_qty_uom=> p_source_qty_uom,
3167 p_qty_per_cont=> p_qty_per_cont,
3168 p_qty_per_cont_uom=> p_qty_per_cont_uom,
3169 p_organization_id=> p_organization_id,
3170 p_dest_cont_item_id=> p_dest_cont_item_id,
3171 p_qty_required=> p_qty_required
3172 );
3173 END container_required_qty;
3174
3175 -- ----------------------------------------------------------------------------------
3176 -- ----------------------------------------------------------------------------------
3177
3178 PROCEDURE prepack_lpn_cp(
3179 errbuf OUT NOCOPY VARCHAR2,
3180 retcode OUT NOCOPY NUMBER,
3181 p_api_version IN NUMBER,
3182 p_organization_id IN NUMBER,
3183 p_subinventory IN VARCHAR2 := NULL,
3184 p_locator_id IN NUMBER := NULL,
3185 p_inventory_item_id IN NUMBER,
3186 p_revision IN VARCHAR2 := NULL,
3187 p_lot_number IN VARCHAR2 := NULL,
3188 p_quantity IN NUMBER,
3189 p_uom IN VARCHAR2,
3190 p_source IN NUMBER,
3191 p_serial_number_from IN VARCHAR2 := NULL,
3192 p_serial_number_to IN VARCHAR2 := NULL,
3193 p_container_item_id IN NUMBER := NULL,
3194 p_cont_revision IN VARCHAR2 := NULL,
3195 p_cont_lot_number IN VARCHAR2 := NULL,
3196 p_cont_serial_number_from IN VARCHAR2 := NULL,
3197 p_cont_serial_number_to IN VARCHAR2 := NULL,
3198 p_lpn_sealed_flag IN NUMBER,
3199 p_print_label IN NUMBER,
3200 p_print_content_report IN NUMBER
3201 ) IS
3202 l_api_name CONSTANT VARCHAR2(30) := 'Prepack_LPN_CP';
3203 l_api_version CONSTANT NUMBER := 1.0;
3204 l_lpn lpn;
3205 l_result NUMBER;
3206 p_init_msg_list VARCHAR2(10) := fnd_api.g_false;
3207 p_commit VARCHAR2(10) := fnd_api.g_false;
3208 x_return_status VARCHAR2(4);
3209 x_msg_count NUMBER;
3210 x_msg_data VARCHAR2(300);
3211 ret BOOLEAN;
3212 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3213 BEGIN
3214 -- Standard Start of API savepoint
3215 SAVEPOINT prepack_lpn_cp_pub;
3216
3217 -- Standard call to check for call compatibility.
3218 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3219 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
3220 fnd_msg_pub.ADD;
3221 RAISE fnd_api.g_exc_unexpected_error;
3222 END IF;
3223
3224 -- Initialize message list if p_init_msg_list is set to TRUE.
3225 IF fnd_api.to_boolean(p_init_msg_list) THEN
3226 fnd_msg_pub.initialize;
3227 END IF;
3228
3229 -- Initialize API return status to success
3230 x_return_status := fnd_api.g_ret_sts_success;
3231 -- Start API body
3232
3233 -- Call Prepack LPN
3234 WMS_CONTAINER_PVT.prepack_lpn(
3235 p_api_version=> p_api_version,
3236 p_init_msg_list=> p_init_msg_list,
3237 p_commit=> p_commit,
3238 x_return_status=> x_return_status,
3239 x_msg_count=> x_msg_count,
3240 x_msg_data=> x_msg_data,
3241 p_organization_id=> p_organization_id,
3242 p_subinventory=> p_subinventory,
3243 p_locator_id=> p_locator_id,
3244 p_inventory_item_id=> p_inventory_item_id,
3245 p_revision=> p_revision,
3246 p_lot_number=> p_lot_number,
3247 p_quantity=> p_quantity,
3248 p_uom=> p_uom,
3249 p_source=> p_source,
3250 p_serial_number_from=> p_serial_number_from,
3251 p_serial_number_to=> p_serial_number_to,
3252 p_container_item_id=> p_container_item_id,
3253 p_cont_revision=> p_cont_revision,
3254 p_cont_lot_number=> p_cont_lot_number,
3255 p_cont_serial_number_from=> p_cont_serial_number_from,
3256 p_cont_serial_number_to=> p_cont_serial_number_to,
3257 p_lpn_sealed_flag=> p_lpn_sealed_flag,
3258 p_print_label=> p_print_label,
3259 p_print_content_report=> p_print_content_report
3260 );
3261
3262 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
3263 ret := fnd_concurrent.set_completion_status('NORMAL', x_msg_data);
3264 retcode := 0;
3265 ELSE
3266 ret := fnd_concurrent.set_completion_status('ERROR', x_msg_data);
3267 retcode := 2;
3268 errbuf := x_msg_data;
3269 END IF;
3270
3271 -- End of API body
3272
3273 -- Standard check of p_commit.
3274 IF fnd_api.to_boolean(p_commit) THEN
3275 COMMIT WORK;
3276 END IF;
3277
3278 -- Standard call to get message count and if count is 1,
3279 -- get message info.
3280 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3281 EXCEPTION
3282 WHEN fnd_api.g_exc_error THEN
3283 ROLLBACK TO prepack_lpn_cp_pub;
3284 x_return_status := fnd_api.g_ret_sts_error;
3285 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3286 WHEN fnd_api.g_exc_unexpected_error THEN
3287 ROLLBACK TO prepack_lpn_cp_pub;
3288 x_return_status := fnd_api.g_ret_sts_unexp_error;
3289 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3290 WHEN OTHERS THEN
3291 ROLLBACK TO prepack_lpn_cp_pub;
3292 x_return_status := fnd_api.g_ret_sts_unexp_error;
3293
3294 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3295 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
3296 END IF;
3297
3298 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
3299 END prepack_lpn_cp;
3300
3301 -- ----------------------------------------------------------------------------------
3302 -- ----------------------------------------------------------------------------------
3303 FUNCTION Validate_LPN( p_lpn IN OUT nocopy LPN, p_lock IN NUMBER := 2) RETURN NUMBER
3304 IS
3305
3306 BEGIN
3307 RETURN WMS_CONTAINER_PVT.Validate_LPN (
3308 p_lpn => p_lpn
3309 , p_lock => p_lock );
3310 EXCEPTION
3311 WHEN OTHERS THEN
3312 RETURN F;
3313 END Validate_LPN;
3314
3315 -- ----------------------------------------------------------------------------------
3316 -- ----------------------------------------------------------------------------------
3317
3318 FUNCTION lpn_pack_complete(p_revert NUMBER := 0)
3319 RETURN BOOLEAN IS
3320 lpn_weight NUMBER;
3321 lpn_volume NUMBER;
3322 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3323 BEGIN
3324 IF (p_revert = 1) THEN
3325 -- Remove all rows from the global wt/vol changes table
3326 FOR i IN 1 .. g_lpn_wt_vol_changes.COUNT LOOP
3327 SELECT gross_weight,
3328 content_volume
3329 INTO lpn_weight,
3330 lpn_volume
3331 FROM wms_license_plate_numbers
3332 WHERE lpn_id = g_lpn_wt_vol_changes(i).lpn_id;
3333
3334 -- Bug5659809: update last_update_date and last_update_by as well
3335 UPDATE wms_license_plate_numbers
3336 SET gross_weight = lpn_weight - g_lpn_wt_vol_changes(i).gross_weight_change
3337 , content_volume = lpn_volume - g_lpn_wt_vol_changes(i).content_volume_change
3338 , last_update_date = SYSDATE
3339 , last_updated_by = fnd_global.user_id
3340 WHERE lpn_id = g_lpn_wt_vol_changes(i).lpn_id;
3341 END LOOP;
3342 END IF;
3343
3344 g_lpn_wt_vol_changes.DELETE;
3345 RETURN TRUE;
3346 EXCEPTION
3347 WHEN OTHERS THEN
3348 RETURN FALSE;
3349 END lpn_pack_complete;
3350
3351 -- ----------------------------------------------------------------------------------
3352 -- ----------------------------------------------------------------------------------
3353
3354 PROCEDURE Merge_Up_LPN (
3355 p_api_version IN NUMBER
3356 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3357 , p_commit IN VARCHAR2 := fnd_api.g_false
3358 , x_return_status OUT NOCOPY VARCHAR2
3359 , x_msg_count OUT NOCOPY NUMBER
3360 , x_msg_data OUT NOCOPY VARCHAR2
3361 , p_organization_id IN NUMBER
3362 , p_outermost_lpn_id IN NUMBER
3363 ) IS
3364 l_api_name CONSTANT VARCHAR2(30) := 'Get_Default_Secondary_Quantity';
3365 l_api_version CONSTANT NUMBER := 1.0;
3366 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3367 l_progress VARCHAR2(10) := '0';
3368 l_msgdata VARCHAR2(1000);
3369
3370 -- Variables for validation
3371 l_result NUMBER;
3372 l_org inv_validate.org;
3373 l_lpn LPN;
3374
3375 BEGIN
3376 -- Standard call to check for call compatibility.
3377 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3378 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
3379 fnd_msg_pub.ADD;
3380 RAISE fnd_api.g_exc_error;
3381 END IF;
3382
3383 -- Initialize message list if p_init_msg_list is set to TRUE.
3384 IF fnd_api.to_boolean(p_init_msg_list) THEN
3385 fnd_msg_pub.initialize;
3386 END IF;
3387
3388 -- Initialize API return status to success
3389 x_return_status := fnd_api.g_ret_sts_success;
3390
3391 IF ( l_debug = 1 ) THEN
3392 mdebug(l_api_name || ' Entered ' || g_pkg_version, G_ERROR);
3393 END IF;
3394
3395 l_progress := '100';
3396 -- Validate Organization ID
3397 l_org.organization_id := p_organization_id;
3398 l_result := inv_validate.ORGANIZATION(l_org);
3399
3400 IF ( l_result = INV_Validate.F ) THEN
3401 IF ( l_debug = 1 ) THEN
3402 mdebug(p_organization_id || ' is not a valid org id', G_ERROR);
3403 END IF;
3404 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
3405 fnd_msg_pub.ADD;
3406 RAISE fnd_api.g_exc_error;
3407 END IF;
3408
3409 l_progress := '200';
3410 -- Validate LPN
3411 l_lpn.lpn_id := p_outermost_lpn_id;
3412 l_result := Validate_LPN(l_lpn);
3413
3414 IF ( l_result = INV_Validate.F ) THEN
3415 IF ( l_debug = 1 ) THEN
3416 mdebug(p_outermost_lpn_id || ' is an invalid lpn id', G_ERROR);
3417 END IF;
3418 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
3419 FND_MSG_PUB.ADD;
3420 RAISE FND_API.G_EXC_ERROR;
3421 END IF;
3422
3423 l_progress := '300';
3424 -- Validate if LPN is valid for this transaction
3425 l_result := WMS_CONTAINER_PVT.Validate_LPN (
3426 p_organization_id => p_organization_id
3427 , p_lpn_id => p_outermost_lpn_id
3428 , p_validation_type => WMS_CONTAINER_PVT.G_RECONFIGURE_LPN );
3429
3430 IF ( l_result = WMS_CONTAINER_PVT.F ) THEN
3431 IF ( l_debug = 1 ) THEN
3432 mdebug(p_outermost_lpn_id || ' cannot be used for merge up', G_ERROR);
3433 END IF;
3434 RAISE FND_API.G_EXC_ERROR;
3435 END IF;
3436
3437 l_progress := '400';
3438 WMS_CONTAINER_PVT.Merge_Up_LPN (
3439 p_api_version => p_api_version
3440 , p_init_msg_list => p_init_msg_list
3441 , p_commit => p_commit
3442 , x_return_status => x_return_status
3443 , x_msg_count => x_msg_count
3444 , x_msg_data => x_msg_data
3445 , p_organization_id => p_organization_id
3446 , p_outermost_lpn_id => p_outermost_lpn_id );
3447
3448 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
3449 IF ( x_return_status = fnd_api.g_ret_sts_error ) THEN
3450 RAISE FND_API.G_EXC_ERROR;
3451 END IF;
3452 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3453 END IF;
3454
3455 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3456 EXCEPTION
3457 WHEN FND_API.G_EXC_ERROR THEN
3458 x_return_status := fnd_api.g_ret_sts_error;
3459 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3460 IF (l_debug = 1) THEN
3461 FOR i in 1..x_msg_count LOOP
3462 l_msgdata := substr(l_msgdata||' | '||substr(fnd_msg_pub.get(x_msg_count-i+1, 'F'), 0, 200),1,2000);
3463 END LOOP;
3464 mdebug(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), 1);
3465 mdebug('msg: '||l_msgdata, 1);
3466 END IF;
3467 WHEN OTHERS THEN
3468 x_return_status := fnd_api.g_ret_sts_unexp_error;
3469 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3470 IF (l_debug = 1) THEN
3471 mdebug(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), 1);
3472 END IF;
3473 END Merge_Up_LPN;
3474
3475 -- ----------------------------------------------------------------------------------
3476 -- ----------------------------------------------------------------------------------
3477
3478 PROCEDURE Break_Down_LPN (
3479 p_api_version IN NUMBER
3480 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3481 , p_commit IN VARCHAR2 := fnd_api.g_false
3482 , x_return_status OUT NOCOPY VARCHAR2
3483 , x_msg_count OUT NOCOPY NUMBER
3484 , x_msg_data OUT NOCOPY VARCHAR2
3485 , p_organization_id IN NUMBER
3486 , p_outermost_lpn_id IN NUMBER
3487 ) IS
3488 l_api_name CONSTANT VARCHAR2(30) := 'Break_Down_LPN';
3489 l_api_version CONSTANT NUMBER := 1.0;
3490 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3491 l_progress VARCHAR2(10) := '0';
3492 l_msgdata VARCHAR2(1000);
3493
3494 -- Variables for validation
3495 l_result NUMBER;
3496 l_org inv_validate.org;
3497 l_lpn LPN;
3498
3499 BEGIN
3500 -- Standard call to check for call compatibility.
3501 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3502 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
3503 fnd_msg_pub.ADD;
3504 RAISE fnd_api.g_exc_error;
3505 END IF;
3506
3507 -- Initialize message list if p_init_msg_list is set to TRUE.
3508 IF fnd_api.to_boolean(p_init_msg_list) THEN
3509 fnd_msg_pub.initialize;
3510 END IF;
3511
3512 -- Initialize API return status to success
3513 x_return_status := fnd_api.g_ret_sts_success;
3514
3515 IF ( l_debug = 1 ) THEN
3516 mdebug(l_api_name || ' Entered ' || g_pkg_version, G_ERROR);
3517 END IF;
3518
3519 l_progress := '100';
3520 -- Validate Organization ID
3521 l_org.organization_id := p_organization_id;
3522 l_result := inv_validate.ORGANIZATION(l_org);
3523
3524 IF ( l_result = INV_Validate.F ) THEN
3525 IF ( l_debug = 1 ) THEN
3526 mdebug(p_organization_id || ' is not a valid org id', G_ERROR);
3527 END IF;
3528 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
3529 fnd_msg_pub.ADD;
3530 RAISE fnd_api.g_exc_error;
3531 END IF;
3532
3533 l_progress := '200';
3534 -- Validate LPN
3535 l_lpn.lpn_id := p_outermost_lpn_id;
3536 l_result := Validate_LPN(l_lpn);
3537
3538 IF ( l_result = INV_Validate.F ) THEN
3539 IF ( l_debug = 1 ) THEN
3540 mdebug(p_outermost_lpn_id || ' is an invalid lpn id', G_ERROR);
3541 END IF;
3542 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
3543 FND_MSG_PUB.ADD;
3544 RAISE FND_API.G_EXC_ERROR;
3545 END IF;
3546
3547 l_progress := '300';
3548 -- Validate if LPN is valid for this transaction
3549 l_result := WMS_CONTAINER_PVT.Validate_LPN (
3550 p_organization_id => p_organization_id
3551 , p_lpn_id => p_outermost_lpn_id
3552 , p_validation_type => WMS_CONTAINER_PVT.G_RECONFIGURE_LPN );
3553
3554 IF ( l_result = WMS_CONTAINER_PVT.F ) THEN
3555 IF ( l_debug = 1 ) THEN
3556 mdebug(p_outermost_lpn_id || ' cannot be used for merge up', G_ERROR);
3557 END IF;
3558 RAISE FND_API.G_EXC_ERROR;
3559 END IF;
3560
3561 l_progress := '400';
3562 WMS_CONTAINER_PVT.Break_Down_LPN (
3563 p_api_version => p_api_version
3564 , p_init_msg_list => p_init_msg_list
3565 , p_commit => p_commit
3566 , x_return_status => x_return_status
3567 , x_msg_count => x_msg_count
3568 , x_msg_data => x_msg_data
3569 , p_organization_id => p_organization_id
3570 , p_outermost_lpn_id => p_outermost_lpn_id );
3571
3572 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
3573 IF ( x_return_status = fnd_api.g_ret_sts_error ) THEN
3574 RAISE FND_API.G_EXC_ERROR;
3575 END IF;
3576 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3577 END IF;
3578
3579 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3580 EXCEPTION
3581 WHEN FND_API.G_EXC_ERROR THEN
3582 x_return_status := fnd_api.g_ret_sts_error;
3583 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3584 IF (l_debug = 1) THEN
3585 FOR i in 1..x_msg_count LOOP
3586 l_msgdata := substr(l_msgdata||' | '||substr(fnd_msg_pub.get(x_msg_count-i+1, 'F'), 0, 200),1,2000);
3587 END LOOP;
3588 mdebug(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), 1);
3589 mdebug('msg: '||l_msgdata, 1);
3590 END IF;
3591 WHEN OTHERS THEN
3592 x_return_status := fnd_api.g_ret_sts_unexp_error;
3593 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3594 IF (l_debug = 1) THEN
3595 mdebug(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), 1);
3596 END IF;
3597 END Break_Down_LPN;
3598
3599 -- ----------------------------------------------------------------------------------
3600 -- ----------------------------------------------------------------------------------
3601
3602 PROCEDURE Initialize_LPN (
3603 p_api_version IN NUMBER
3604 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3605 , p_commit IN VARCHAR2 := fnd_api.g_false
3606 , x_return_status OUT NOCOPY VARCHAR2
3607 , x_msg_count OUT NOCOPY NUMBER
3608 , x_msg_data OUT NOCOPY VARCHAR2
3609 , p_organization_id IN NUMBER
3610 , p_outermost_lpn_id IN NUMBER
3611 ) IS
3612 l_api_name CONSTANT VARCHAR2(30) := 'Initialize_LPN';
3613 l_api_version CONSTANT NUMBER := 1.0;
3614 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3615 l_progress VARCHAR2(10) := '0';
3616 l_msgdata VARCHAR2(1000);
3617
3618 -- Variables for validation
3619 l_result NUMBER;
3620 l_org inv_validate.org;
3621 l_lpn LPN;
3622
3623 BEGIN
3624 -- Standard call to check for call compatibility.
3625 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3626 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
3627 fnd_msg_pub.ADD;
3628 RAISE fnd_api.g_exc_error;
3629 END IF;
3630
3631 -- Initialize message list if p_init_msg_list is set to TRUE.
3632 IF fnd_api.to_boolean(p_init_msg_list) THEN
3633 fnd_msg_pub.initialize;
3634 END IF;
3635
3636 -- Initialize API return status to success
3637 x_return_status := fnd_api.g_ret_sts_success;
3638
3639 IF ( l_debug = 1 ) THEN
3640 mdebug(l_api_name || ' Entered ' || g_pkg_version, G_ERROR);
3641 END IF;
3642
3643 l_progress := '100';
3644 -- Validate Organization ID
3645 l_org.organization_id := p_organization_id;
3646 l_result := inv_validate.ORGANIZATION(l_org);
3647
3648 IF ( l_result = INV_Validate.F ) THEN
3649 IF ( l_debug = 1 ) THEN
3650 mdebug(p_organization_id || ' is not a valid org id', G_ERROR);
3651 END IF;
3652 fnd_message.set_name('WMS', 'WMS_CONT_INVALID_ORG');
3653 fnd_msg_pub.ADD;
3654 RAISE fnd_api.g_exc_error;
3655 END IF;
3656
3657 l_progress := '200';
3658 -- Validate LPN
3659 l_lpn.lpn_id := p_outermost_lpn_id;
3660 l_result := Validate_LPN(l_lpn);
3661
3662 IF ( l_result = INV_Validate.F ) THEN
3663 IF ( l_debug = 1 ) THEN
3664 mdebug(p_outermost_lpn_id || ' is an invalid lpn id', G_ERROR);
3665 END IF;
3666 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_LPN');
3667 FND_MSG_PUB.ADD;
3668 RAISE FND_API.G_EXC_ERROR;
3669 END IF;
3670
3671 l_progress := '300';
3672 -- Validate if LPN is valid for this transaction
3673 l_result := WMS_CONTAINER_PVT.Validate_LPN (
3674 p_organization_id => p_organization_id
3675 , p_lpn_id => p_outermost_lpn_id
3676 , p_validation_type => WMS_CONTAINER_PVT.G_NO_ONHAND_EXISTS );
3677
3678 IF ( l_result = WMS_CONTAINER_PVT.F ) THEN
3679 IF ( l_debug = 1 ) THEN
3680 mdebug(p_outermost_lpn_id || ' cannot initialize LPN', G_ERROR);
3681 END IF;
3682 RAISE FND_API.G_EXC_ERROR;
3683 END IF;
3684
3685 l_progress := '400';
3686 WMS_CONTAINER_PVT.Initialize_LPN (
3687 p_api_version => p_api_version
3688 , p_init_msg_list => p_init_msg_list
3689 , p_commit => p_commit
3690 , x_return_status => x_return_status
3691 , x_msg_count => x_msg_count
3692 , x_msg_data => x_msg_data
3693 , p_organization_id => p_organization_id
3694 , p_outermost_lpn_id => p_outermost_lpn_id );
3695
3696 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
3697 IF ( x_return_status = fnd_api.g_ret_sts_error ) THEN
3698 RAISE FND_API.G_EXC_ERROR;
3699 END IF;
3700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3701 END IF;
3702
3703 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3704 EXCEPTION
3705 WHEN FND_API.G_EXC_ERROR THEN
3706 x_return_status := fnd_api.g_ret_sts_error;
3707 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3708 IF (l_debug = 1) THEN
3709 FOR i in 1..x_msg_count LOOP
3710 l_msgdata := substr(l_msgdata||' | '||substr(fnd_msg_pub.get(x_msg_count-i+1, 'F'), 0, 200),1,2000);
3711 END LOOP;
3712 mdebug(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), 1);
3713 mdebug('msg: '||l_msgdata, 1);
3714 END IF;
3715 WHEN OTHERS THEN
3716 x_return_status := fnd_api.g_ret_sts_unexp_error;
3717 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3718 IF (l_debug = 1) THEN
3719 mdebug(l_api_name ||' Error progress='||l_progress||' SQL error: '|| SQLERRM(SQLCODE), 1);
3720 END IF;
3721 END Initialize_LPN;
3722
3723 PROCEDURE validate_lpn ( p_lpn_id IN NUMBER
3724 , p_unpack_inner_lpns IN VARCHAR2
3725 , x_msg_count OUT NOCOPY NUMBER
3726 , x_msg_data OUT NOCOPY VARCHAR2
3727 , x_return_status OUT NOCOPY VARCHAR2
3728 )
3729 IS
3730 l_valid NUMBER;
3731 l_lpn_context NUMBER;
3732 l_organization_id NUMBER;
3733 l_invalid_lpn_context EXCEPTION;
3734 l_lpn_not_found EXCEPTION;
3735 l_transactions_pending EXCEPTION;
3736 l_table_name VARCHAR2(100);
3737 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3738
3739 CURSOR all_child_wlpns
3740 IS SELECT lpn_id
3741 FROM wms_license_plate_numbers
3742 START WITH lpn_id = p_lpn_id
3743 CONNECT BY parent_lpn_id = PRIOR lpn_id;
3744
3745 BEGIN
3746
3747 IF l_debug = 1 THEN
3748 mdebug('Inside Validate LPN');
3749 END IF;
3750
3751 l_valid := 0;
3752
3753 FOR all_child_wlpn_rec IN all_child_wlpns LOOP
3754
3755 BEGIN
3756
3757 SELECT lpn_context
3758 , organization_id
3759 INTO l_lpn_context
3760 , l_organization_id
3761 FROM wms_license_plate_numbers
3762 WHERE lpn_id = p_lpn_id;
3763
3764 IF l_lpn_context <> 4 THEN
3765 IF l_debug = 1 THEN
3766 mdebug('LPN : '|| p_lpn_id ||' has context : '|| l_lpn_context ||' only LPNs with context 4 can be reused');
3767 END IF;
3768 RAISE l_invalid_lpn_context;
3769 END IF;
3770
3771 EXCEPTION
3772 WHEN NO_DATA_FOUND THEN
3773 IF l_debug = 1 THEN
3774 mdebug('LPN : '|| p_lpn_id ||' does not exist');
3775 END IF;
3776 RAISE l_lpn_not_found;
3777 END;
3778
3779 SELECT COUNT(1)
3780 INTO l_valid
3781 FROM mtl_material_transactions_temp
3782 WHERE organization_id = l_organization_id
3783 AND ( lpn_id = p_lpn_id
3784 OR transfer_lpn_id = p_lpn_id
3785 OR content_lpn_id = p_lpn_id
3786 OR allocated_lpn_id = p_lpn_id
3787 OR cartonization_id = p_lpn_id
3788 );
3789
3790 IF l_valid > 0 THEN
3791 IF l_debug = 1 THEN
3792 mdebug('Pending MMTT transactions exists against this LPN : '||p_lpn_id||', cannot reuse');
3793 END IF;
3794 l_table_name := 'MTL_MATERIAL_TRANSACTIONS_TEMP';
3795 RAISE l_transactions_pending;
3796 END IF;
3797
3798 SELECT COUNT(1)
3799 INTO l_valid
3800 FROM mtl_transactions_interface
3801 WHERE organization_id = l_organization_id
3802 AND ( lpn_id = p_lpn_id
3803 OR transfer_lpn_id = p_lpn_id
3804 OR content_lpn_id = p_lpn_id
3805 );
3806
3807 IF l_valid > 0 THEN
3808 IF l_debug = 1 THEN
3809 mdebug('Pending MTI transactions exists against this LPN : '||p_lpn_id||', cannot reuse');
3810 END IF;
3811 l_table_name := 'MTL_TRANSACTIONS_INTERFACE';
3812 RAISE l_transactions_pending;
3813 END IF;
3814
3815 SELECT COUNT(1)
3816 INTO l_valid
3817 FROM mtl_onhand_quantities_detail
3818 WHERE organization_id = l_organization_id
3819 AND lpn_id = p_lpn_id;
3820
3821 IF l_valid > 0 THEN
3822 IF l_debug = 1 THEN
3823 mdebug('LPN : '||p_lpn_id||' is currently present in MOQD, cannot reuse');
3824 END IF;
3825 l_table_name := 'MTL_ONHAND_QUANTITIES_DETAIL';
3826 RAISE l_transactions_pending;
3827 END IF;
3828
3829 SELECT COUNT(1)
3830 INTO l_valid
3831 FROM mtl_txn_request_lines
3832 WHERE organization_id = l_organization_id
3833 AND lpn_id = p_lpn_id
3834 AND line_status = 7;
3835
3836 IF l_valid > 0 THEN
3837 IF l_debug = 1 THEN
3838 mdebug('LPN : '||p_lpn_id||' has an open move order line against it, cannot reuse');
3839 END IF;
3840 l_table_name := 'MTL_TXN_REQUEST_LINES';
3841 RAISE l_transactions_pending;
3842 END IF;
3843
3844 SELECT COUNT(1)
3845 INTO l_valid
3846 FROM mtl_reservations
3847 WHERE organization_id = l_organization_id
3848 AND lpn_id = p_lpn_id;
3849
3850 IF l_valid > 0 THEN
3851 IF l_debug = 1 THEN
3852 mdebug('LPN : '||p_lpn_id||' has existing reservations against it, cannot reuse');
3853 END IF;
3854 l_table_name := 'MTL_RESERVATIONS';
3855 RAISE l_transactions_pending;
3856 END IF;
3857
3858 SELECT COUNT(1)
3859 INTO l_valid
3860 FROM mtl_serial_numbers
3861 WHERE lpn_id = p_lpn_id
3862 AND current_organization_id = l_organization_id
3863 AND current_status <> 4;
3864
3865 IF l_valid > 0 THEN
3866 IF l_debug = 1 THEN
3867 mdebug('LPN : '||p_lpn_id||' has serials which are not shipped out, cannot reuse');
3868 END IF;
3869 l_table_name := 'MTL_SERIAL_NUMBERS';
3870 RAISE l_transactions_pending;
3871 END IF;
3872
3873 SELECT COUNT(1)
3874 INTO l_valid
3875 FROM rcv_transactions_interface
3876 WHERE lpn_id = p_lpn_id
3877 OR transfer_lpn_id = p_lpn_id;
3878
3879 IF l_valid > 0 THEN
3880 IF l_debug = 1 THEN
3881 mdebug('LPN : '||p_lpn_id||' has unprocessed records in rcv_transactions_interface table, cannot reuse');
3882 END IF;
3883 l_table_name := 'RCV_TRANSACTIONS_INTERFACE';
3884 RAISE l_transactions_pending;
3885 END IF;
3886
3887 SELECT COUNT(1)
3888 INTO l_valid
3889 FROM wsh_delivery_details
3890 WHERE organization_id = l_organization_id
3891 AND lpn_id = p_lpn_id
3892 AND released_status = 'X';
3893
3894 IF l_valid > 0 THEN
3895 IF l_debug = 1 THEN
3896 mdebug('LPN : '||p_lpn_id||' has unprocessed records in wsh_delivery_details table, cannot reuse');
3897 END IF;
3898 l_table_name := 'WSH_DELIVERY_DETAILS';
3899 RAISE l_transactions_pending;
3900 END IF;
3901
3902 IF p_unpack_inner_lpns = 'N' THEN
3903 IF l_debug = 1 THEN
3904 mdebug('p_unpack_inner_lpns = N therefore only validating lpn : '||p_lpn_id);
3905 END IF;
3906 EXIT;
3907 END IF;
3908
3909 END LOOP;
3910
3911 x_return_status := 'S';
3912
3913 EXCEPTION
3914 WHEN l_transactions_pending THEN
3915 FND_MESSAGE.SET_NAME('WMS', 'WMS_PENDING_TRX_RECORDS');
3916 FND_MSG_PUB.ADD;
3917 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3918 x_return_status := 'E';
3919 WHEN l_invalid_lpn_context THEN
3920 FND_MESSAGE.SET_NAME('WMS', 'WMS_WRONG_TO_LPN_CONTEXT');
3921 FND_MSG_PUB.ADD;
3922 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3923 x_return_status := 'E';
3924 WHEN l_lpn_not_found THEN
3925 FND_MESSAGE.SET_NAME('WMS', 'WMS_LPN_NOTFOUND');
3926 FND_MSG_PUB.ADD;
3927 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3928 x_return_status := 'E';
3929 WHEN OTHERS THEN
3930 FND_MESSAGE.SET_NAME('WMS', 'WMS_UNEXPECTED_ERROR');
3931 FND_MSG_PUB.ADD;
3932 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3933 x_return_status := 'E';
3934 END validate_lpn;
3935
3936
3937
3938 PROCEDURE REUSE_LPNS (
3939 p_api_version IN NUMBER
3940 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3941 , p_commit IN VARCHAR2 := fnd_api.g_false
3942 , p_validation_level IN NUMBER := fnd_api.g_valid_level_full
3943 , x_return_status OUT NOCOPY VARCHAR2
3944 , x_msg_count OUT NOCOPY NUMBER
3945 , x_msg_data OUT NOCOPY VARCHAR2
3946 , p_lpn_id IN NUMBER
3947 , p_clear_attributes IN VARCHAR2
3948 , p_new_org_id IN NUMBER
3949 , p_unpack_inner_lpns IN VARCHAR2
3950 , p_clear_containter_item_id IN VARCHAR2
3951 )
3952 IS
3953
3954 l_invalid_org EXCEPTION;
3955 l_invalid_lpn EXCEPTION;
3956 l_wlpn_row_id ROWID;
3957 l_valid NUMBER;
3958 l_organization_id NUMBER;
3959 l_container_item_id NUMBER;
3960 l_parent_lpn_id NUMBER;
3961 l_lpn_context NUMBER;
3962 l_api_name VARCHAR2(100) := 'REUSE_LPNS';
3963 l_outermost_lpn_id NUMBER;
3964 l_outermost_lpn_name WMS_LICENSE_PLATE_NUMBERS.LICENSE_PLATE_NUMBER%TYPE;
3965 l_lpn_name WMS_LICENSE_PLATE_NUMBERS.LICENSE_PLATE_NUMBER%TYPE;
3966 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3967 l_api_version CONSTANT NUMBER := 1.0;
3968
3969 CURSOR immediate_child_wlpns
3970 IS SELECT lpn_id
3971 FROM wms_license_plate_numbers
3972 WHERE parent_lpn_id = p_lpn_id;
3973
3974 CURSOR all_child_wlpns (p_lpn_id IN NUMBER)
3975 IS SELECT lpn_id
3976 FROM wms_license_plate_numbers
3977 WHERE lpn_id <> p_lpn_id
3978 START WITH lpn_id = p_lpn_id
3979 CONNECT BY parent_lpn_id = PRIOR lpn_id;
3980
3981 CURSOR all_child_wlpns1
3982 IS SELECT lpn_id
3983 FROM wms_license_plate_numbers
3984 START WITH lpn_id = p_lpn_id
3985 CONNECT BY parent_lpn_id = PRIOR lpn_id;
3986
3987 BEGIN
3988
3989
3990 IF l_debug = 1 THEN
3991 mdebug('Inside REUSE_LPNS procedure will following parameters :');
3992 mdebug('p_api_version : '|| p_api_version);
3993 mdebug('p_init_msg_list :'|| p_init_msg_list);
3994 mdebug('p_commit :'|| p_commit);
3995 mdebug('p_validation_level :'|| p_validation_level);
3996 mdebug('p_lpn_id : '||p_lpn_id);
3997 mdebug('p_clear_attributes : '||p_clear_attributes);
3998 mdebug('p_new_org_id : '|| p_new_org_id);
3999 mdebug('p_unpack_inner_lpns : '||p_unpack_inner_lpns);
4000 mdebug('p_clear_containter_item_id : '||p_clear_containter_item_id);
4001 END IF;
4002
4003 IF p_init_msg_list ='Y' THEN
4004 fnd_msg_pub.initialize;
4005 END IF;
4006
4007 IF NOT FND_API.Compatible_API_Call( l_api_version
4008 , p_api_version
4009 , l_api_name
4010 , G_PKG_NAME) THEN
4011
4012 IF l_debug = 1 THEN
4013 mdebug('API Version not compatible');
4014 END IF;
4015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4016 END IF;
4017
4018 IF p_new_org_id IS NOT NULL THEN
4019 SELECT count(1)
4020 INTO l_valid
4021 FROM mtl_parameters
4022 WHERE organization_id = p_new_org_id
4023 AND wms_enabled_flag = 'Y';
4024 END IF;
4025
4026 IF l_valid = 0 THEN
4027 IF l_debug = 1 THEN
4028 mdebug('Organization : '|| p_new_org_id ||' does not exist or its not warehouse enabled');
4029 END IF;
4030 RAISE l_invalid_org;
4031 END IF;
4032
4033 validate_lpn (
4034 p_lpn_id => p_lpn_id
4035 , p_unpack_inner_lpns => p_unpack_inner_lpns
4036 , x_return_status => x_return_status
4037 , x_msg_count => x_msg_count
4038 , x_msg_data => x_msg_data
4039 );
4040
4041 IF NVL(x_return_status,'E') = 'E' THEN
4042 IF l_debug = 1 THEN
4043 mdebug('Validate LPN returned error');
4044 END IF;
4045 RAISE l_invalid_lpn;
4046 END IF;
4047
4048 SELECT organization_id
4049 , lpn_context
4050 , ROWID
4051 , license_plate_number
4052 , parent_lpn_id
4053 , outermost_lpn_id
4054 , inventory_item_id
4055 INTO l_organization_id
4056 , l_lpn_context
4057 , l_wlpn_row_id
4058 , l_lpn_name
4059 , l_parent_lpn_id
4060 , l_outermost_lpn_id
4061 , l_container_item_id
4062 FROM wms_license_plate_numbers
4063 WHERE lpn_id = p_lpn_id;
4064
4065 SAVEPOINT REUSE_LPN_SP;
4066
4067 -- To see if it is the outermost lpn id
4068
4069 IF l_outermost_lpn_id = p_lpn_id THEN
4070 l_outermost_lpn_name := l_lpn_name;
4071 ELSE
4072 SELECT license_plate_number
4073 INTO l_outermost_lpn_name
4074 FROM wms_license_plate_numbers
4075 WHERE lpn_id = l_outermost_lpn_id;
4076 END IF;
4077
4078 IF l_debug = 1 THEN
4079 mdebug('Outer most lpn name : '|| l_outermost_lpn_name);
4080 END IF;
4081
4082 IF p_unpack_inner_lpns = 'N' THEN
4083
4084 INSERT INTO wms_lpn_histories (
4085 LPN_HISTORY_ID -- Sequence
4086 , SECONDARY_QUANTITY -- wlc.parent_lpn_id
4087 , SECONDARY_UOM_CODE --wlc.secondary_uom_code
4088 , CALLER
4089 , SOURCE_TRANSACTION_ID
4090 , TO_SERIAL_NUMBER
4091 , SOURCE_TYPE_ID -- wlpn.source_type_id
4092 , SOURCE_HEADER_ID -- wlpn.source_header_id
4093 , SOURCE_LINE_ID -- wlpn.source_line_id
4094 , SOURCE_LINE_DETAIL_ID --wlpn.source_line_detail_id
4095 , SOURCE_NAME --wlpn.source_name
4096 , PARENT_LPN_ID -- wlc.parent_lpn_id
4097 , PARENT_LICENSE_PLATE_NUMBER --wlpn.license_plate_number
4098 , LPN_ID
4099 , LICENSE_PLATE_NUMBER
4100 , INVENTORY_ITEM_ID -- wlc.inventory_item_id
4101 , ITEM_DESCRIPTION -- wlc.item_description
4102 , REVISION -- wlc.revision
4103 , LOT_NUMBER -- wlc.lot_number
4104 , SERIAL_NUMBER -- msn.serial_number
4105 , QUANTITY -- Need to derive
4106 , UOM_CODE -- wlc.uom_code
4107 , ORGANIZATION_ID -- wlpn.organization_id
4108 , SUBINVENTORY_CODE -- wlpn.subinventory_code
4109 , LOCATOR_ID -- wlpn.locator_id
4110 , STATUS_ID -- wlpn.status_id
4111 --, LPN_STATE -- wlpn.lpn_state --Commented for Bug#7828840
4112 , SEALED_STATUS -- wlpn.sealed_status
4113 , OPERATION_MODE -- Need to derive PACK or UNPACK
4114 , LAST_UPDATE_DATE -- SYSDATE
4115 , LAST_UPDATED_BY -- FND_GLOBAL.USER_ID
4116 , CREATION_DATE -- SYSDATE
4117 , CREATED_BY
4118 , LAST_UPDATE_LOGIN
4119 , REQUEST_ID
4120 , PROGRAM_APPLICATION_ID
4121 , PROGRAM_ID
4122 , PROGRAM_UPDATE_DATE -- SYSDATE
4123 , ATTRIBUTE_CATEGORY
4124 , ATTRIBUTE1
4125 , ATTRIBUTE2
4126 , ATTRIBUTE3
4127 , ATTRIBUTE4
4128 , ATTRIBUTE5
4129 , ATTRIBUTE6
4130 , ATTRIBUTE7
4131 , ATTRIBUTE8
4132 , ATTRIBUTE9
4133 , ATTRIBUTE10
4134 , ATTRIBUTE11
4135 , ATTRIBUTE12
4136 , ATTRIBUTE13
4137 , ATTRIBUTE14
4138 , ATTRIBUTE15
4139 , COST_GROUP_ID --wlc.cost_group_id
4140 , LPN_CONTEXT --wlpn.lpn_context
4141 , LPN_REUSABILITY --wlpn.lpn_reusability
4142 , OUTERMOST_LPN_ID --wlpn.outermost_lpn_id
4143 , OUTERMOST_LICENSE_PLATE_NUMBER -- Need to derive
4144 , HOMOGENEOUS_CONTAINER --wlpn.homogeneous_container
4145 ) SELECT
4146 wms_lpn_histories_s.NEXTVAL -- LPN_HISTORY_ID
4147 , wlc.quantity -- SECONDARY_QUANTITY
4148 , wlc.secondary_uom_code -- SECONDARY_UOM_CODE
4149 , NULL -- CALLER
4150 , NULL -- SOURCE_TRANSACTION_ID
4151 , msn.serial_number -- TO_SERIAL_NUMBER
4152 , wlpn1.source_type_id -- SOURCE_TYPE_ID
4153 , wlpn1.source_header_id -- SOURCE_HEADER_ID
4154 , wlpn1.source_line_id -- SOURCE_LINE_ID
4155 , wlpn1.source_line_detail_id -- SOURCE_LINE_DETAIL_ID
4156 , wlpn1.source_name -- SOURCE_NAME
4157 , wlpn1.lpn_id -- PARENT_LPN_ID
4158 , wlpn1.license_plate_number -- PARENT_LICENSE_PLATE_NUMBER
4159 , wlpn2.lpn_id -- LPN_ID
4160 , wlpn2.license_plate_number -- LICENSE_PLATE_NUMBER
4161 , wlc.inventory_item_id -- INVENTORY_ITEM_ID
4162 , wlc.item_description -- ITEM_DESCRIPTION
4163 , wlc.revision -- REVISION
4164 , wlc.lot_number -- LOT_NUMBER
4165 , msn.serial_number -- SERIAL_NUMBER
4166 , NVL2(msn.serial_number, 1,wlc.quantity) --QUANTITY
4167 , wlc.uom_code -- UOM_CODE
4168 , wlpn1.organization_id -- ORGANIZATION_ID
4169 , wlpn1.subinventory_code -- SUBINVENTORY_CODE
4170 , wlpn1.locator_id -- LOCATOR_ID
4171 , wlpn1.status_id -- STATUS_ID
4172 --, wlpn1.lpn_state -- LPN_STATE --Commented for Bug#7828840
4173 , wlpn1.sealed_status -- SEALED_STATUS
4174 , 2 -- OPERATION_MODE
4175 , SYSDATE -- LAST_UPDATE_DATE
4176 , FND_GLOBAL.USER_ID -- LAST_UPDATED_BY
4177 , SYSDATE -- CREATION_DATE
4178 , FND_GLOBAL.USER_ID -- CREATED_BY
4179 , FND_GLOBAL.USER_ID -- LAST_UPDATE_LOGIN
4180 , NULL -- REQUEST_ID
4181 , NULL -- PROGRAM_APPLICATION_ID
4182 , NULL -- PROGRAM_ID
4183 , NULL -- PROGRAM_UPDATE_DATE
4184 , wlpn1.attribute_category
4185 , wlpn1.ATTRIBUTE1
4186 , wlpn1.ATTRIBUTE2
4187 , wlpn1.ATTRIBUTE3
4188 , wlpn1.ATTRIBUTE4
4189 , wlpn1.ATTRIBUTE5
4190 , wlpn1.ATTRIBUTE6
4191 , wlpn1.ATTRIBUTE7
4192 , wlpn1.ATTRIBUTE8
4193 , wlpn1.ATTRIBUTE9
4194 , wlpn1.ATTRIBUTE10
4195 , wlpn1.ATTRIBUTE11
4196 , wlpn1.ATTRIBUTE12
4197 , wlpn1.ATTRIBUTE13
4198 , wlpn1.ATTRIBUTE14
4199 , wlpn1.ATTRIBUTE15
4200 , wlc.cost_group_id -- COST_GROUP_ID
4201 , wlpn1.lpn_context -- LPN_CONTEXT
4202 , wlpn1.lpn_reusability -- LPN_REUSABILITY
4203 , wlpn1.lpn_id -- OUTERMOST_LPN_ID
4204 , l_outermost_lpn_name -- OUTERMOST_LICENSE_PLATE_NUMBER
4205 , wlpn1.homogeneous_container -- HOMOGENEOUS_CONTAINER
4206 FROM wms_license_plate_numbers wlpn1
4207 , wms_license_plate_numbers wlpn2
4208 , wms_lpn_contents wlc
4209 , mtl_serial_numbers msn
4210 WHERE wlpn1.lpn_id = wlc.parent_lpn_id(+)
4211 AND wlpn1.lpn_id = msn.lpn_id(+)
4212 AND wlpn1.lpn_id = wlpn2.parent_lpn_id(+)
4213 AND wlpn1.lpn_id = p_lpn_id;
4214
4215 IF l_debug = 1 THEN
4216 mdebug('Inserted wms_lpn_histories rows for lpn : '|| p_lpn_id);
4217 END IF;
4218
4219 DELETE FROM wms_lpn_contents
4220 WHERE parent_lpn_id = p_lpn_id;
4221
4222 IF p_clear_attributes = 'Y' THEN
4223 UPDATE wms_license_plate_numbers
4224 SET ATTRIBUTE1 = NULL
4225 , ATTRIBUTE2 = NULL
4226 , ATTRIBUTE3 = NULL
4227 , ATTRIBUTE4 = NULL
4228 , ATTRIBUTE5 = NULL
4229 , ATTRIBUTE6 = NULL
4230 , ATTRIBUTE7 = NULL
4231 , ATTRIBUTE8 = NULL
4232 , ATTRIBUTE9 = NULL
4233 , ATTRIBUTE10 = NULL
4234 , ATTRIBUTE11 = NULL
4235 , ATTRIBUTE12 = NULL
4236 , ATTRIBUTE13 = NULL
4237 , ATTRIBUTE14 = NULL
4238 , ATTRIBUTE15 = NULL
4239 , ATTRIBUTE_CATEGORY = NULL
4240 WHERE ROWID = l_wlpn_row_id;
4241 END IF;
4242
4243 IF l_parent_lpn_id IS NULL THEN -- This parameter LPN is the outermost_lpn
4244
4245 IF l_debug = 1 THEN
4246 mdebug('LPN : '|| p_lpn_id ||' is the outermost LPN');
4247 END IF;
4248
4249 FOR immediate_child_wlpn_rec IN immediate_child_wlpns LOOP
4250
4251 IF l_debug = 1 THEN
4252 mdebug('Updating its immediate chile LPNs : '|| immediate_child_wlpn_rec.lpn_id);
4253 END IF;
4254
4255 UPDATE wms_license_plate_numbers
4256 SET parent_lpn_id = NULL
4257 , outermost_lpn_id = immediate_child_wlpn_rec.lpn_id
4258 WHERE lpn_id = immediate_child_wlpn_rec.lpn_id;
4259
4260 FOR all_child_wlpn_rec IN all_child_wlpns (immediate_child_wlpn_rec.lpn_id) LOOP
4261
4262 IF l_debug = 1 THEN
4263 mdebug('Updating all inner lpns of LPN : '|| immediate_child_wlpn_rec.lpn_id);
4264 END IF;
4265
4266 UPDATE wms_license_plate_numbers
4267 SET outermost_lpn_id = immediate_child_wlpn_rec.lpn_id
4268 WHERE lpn_id = all_child_wlpn_rec.lpn_id;
4269
4270 END LOOP;
4271 END LOOP;
4272 ELSE
4273 FOR immediate_child_wlpn_rec IN immediate_child_wlpns LOOP
4274 UPDATE wms_license_plate_numbers
4275 SET parent_lpn_id = l_parent_lpn_id
4276 WHERE lpn_id = immediate_child_wlpn_rec.lpn_id;
4277 END LOOP;
4278 END IF;
4279
4280 IF p_new_org_id IS NOT NULL THEN
4281 UPDATE wms_license_plate_numbers
4282 SET lpn_context = 5
4283 , subinventory_code = NULL
4284 , locator_id = NULL
4285 , parent_lpn_id = NULL
4286 , outermost_lpn_id = p_lpn_id
4287 , organization_id = p_new_org_id
4288 WHERE ROWID = l_wlpn_row_id;
4289 ELSE
4290 UPDATE wms_license_plate_numbers
4291 SET lpn_context = 5
4292 , subinventory_code = NULL
4293 , locator_id = NULL
4294 , parent_lpn_id = NULL
4295 , outermost_lpn_id = p_lpn_id
4296 WHERE ROWID = l_wlpn_row_id;
4297 END IF;
4298
4299 UPDATE mtl_serial_numbers
4300 SET lpn_id = NULL
4301 WHERE lpn_id = p_lpn_id
4302 AND current_organization_id = l_organization_id;
4303
4304 UPDATE wms_license_plate_numbers
4305 SET content_volume = NULL
4306 , content_volume_uom_code = NULL
4307 WHERE ROWID = l_wlpn_row_id;
4308
4309 IF l_container_item_id IS NULL
4310 OR p_clear_containter_item_id = 'Y' THEN
4311 UPDATE wms_license_plate_numbers
4312 SET inventory_item_id = NULL
4313 , gross_weight = NULL
4314 , gross_weight_uom_code = NULL
4315 , tare_weight = NULL
4316 , tare_weight_uom_code = NULL
4317 , container_volume = NULL
4318 , container_volume_uom = NULL
4319 WHERE ROWID = l_wlpn_row_id;
4320
4321 ELSIF p_clear_containter_item_id = 'N'
4322 AND l_container_item_id IS NOT NULL THEN
4323 IF ( NOT inv_cache.set_item_rec( p_organization_id => p_new_org_id
4324 , p_item_id => l_container_item_id ) ) THEN
4325 IF l_debug = 1 THEN
4326 mdebug('Error calling inv_cache.set_item_rec');
4327 END IF;
4328 RAISE fnd_api.g_exc_unexpected_error;
4329 END IF;
4330
4331 UPDATE wms_license_plate_numbers
4332 SET gross_weight = inv_cache.item_rec.unit_weight
4333 , gross_weight_uom_code = inv_cache.item_rec.weight_uom_code
4334 , tare_weight = inv_cache.item_rec.unit_weight
4335 , tare_weight_uom_code = inv_cache.item_rec.weight_uom_code
4336 , container_volume = inv_cache.item_rec.unit_volume
4337 , container_volume_uom = inv_cache.item_rec.volume_uom_code
4338 WHERE ROWID = l_wlpn_row_id;
4339 END IF;
4340
4341 ELSIF p_unpack_inner_lpns = 'Y' THEN
4342
4343 FOR all_child_wlpn_rec IN all_child_wlpns1 LOOP
4344
4345 SELECT license_plate_number
4346 INTO l_outermost_lpn_name
4347 FROM wms_license_plate_numbers
4348 WHERE lpn_id = (SELECT outermost_lpn_id
4349 FROM wms_license_plate_numbers
4350 WHERE lpn_id = all_child_wlpn_rec.lpn_id);
4351
4352
4353 INSERT INTO wms_lpn_histories (
4354 LPN_HISTORY_ID -- Sequence
4355 , SECONDARY_QUANTITY -- wlc.parent_lpn_id
4356 , SECONDARY_UOM_CODE --wlc.secondary_uom_code
4357 , CALLER
4358 , SOURCE_TRANSACTION_ID
4359 , TO_SERIAL_NUMBER
4360 , SOURCE_TYPE_ID -- wlpn.source_type_id
4361 , SOURCE_HEADER_ID -- wlpn.source_header_id
4362 , SOURCE_LINE_ID -- wlpn.source_line_id
4363 , SOURCE_LINE_DETAIL_ID --wlpn.source_line_detail_id
4364 , SOURCE_NAME --wlpn.source_name
4365 , PARENT_LPN_ID -- wlc.parent_lpn_id
4366 , PARENT_LICENSE_PLATE_NUMBER --wlpn.license_plate_number
4367 , LPN_ID
4368 , LICENSE_PLATE_NUMBER
4369 , INVENTORY_ITEM_ID -- wlc.inventory_item_id
4370 , ITEM_DESCRIPTION -- wlc.item_description
4371 , REVISION -- wlc.revision
4372 , LOT_NUMBER -- wlc.lot_number
4373 , SERIAL_NUMBER -- msn.serial_number
4374 , QUANTITY -- Need to derive
4375 , UOM_CODE -- wlc.uom_code
4376 , ORGANIZATION_ID -- wlpn.organization_id
4377 , SUBINVENTORY_CODE -- wlpn.subinventory_code
4378 , LOCATOR_ID -- wlpn.locator_id
4379 , STATUS_ID -- wlpn.status_id
4380 --, LPN_STATE -- wlpn.lpn_state --Commented for Bug#7828840
4381 , SEALED_STATUS -- wlpn.sealed_status
4382 , OPERATION_MODE -- Need to derive PACK or UNPACK
4383 , LAST_UPDATE_DATE -- SYSDATE
4384 , LAST_UPDATED_BY -- FND_GLOBAL.USER_ID
4385 , CREATION_DATE -- SYSDATE
4386 , CREATED_BY
4387 , LAST_UPDATE_LOGIN
4388 , REQUEST_ID
4389 , PROGRAM_APPLICATION_ID
4390 , PROGRAM_ID
4391 , PROGRAM_UPDATE_DATE -- SYSDATE
4392 , ATTRIBUTE_CATEGORY
4393 , ATTRIBUTE1
4394 , ATTRIBUTE2
4395 , ATTRIBUTE3
4396 , ATTRIBUTE4
4397 , ATTRIBUTE5
4398 , ATTRIBUTE6
4399 , ATTRIBUTE7
4400 , ATTRIBUTE8
4401 , ATTRIBUTE9
4402 , ATTRIBUTE10
4403 , ATTRIBUTE11
4404 , ATTRIBUTE12
4405 , ATTRIBUTE13
4406 , ATTRIBUTE14
4407 , ATTRIBUTE15
4408 , COST_GROUP_ID --wlc.cost_group_id
4409 , LPN_CONTEXT --wlpn.lpn_context
4410 , LPN_REUSABILITY --wlpn.lpn_reusability
4411 , OUTERMOST_LPN_ID --wlpn.outermost_lpn_id
4412 , OUTERMOST_LICENSE_PLATE_NUMBER -- Need to derive
4413 , HOMOGENEOUS_CONTAINER --wlpn.homogeneous_container
4414 ) SELECT
4415 wms_lpn_histories_s.NEXTVAL -- LPN_HISTORY_ID
4416 , wlc.quantity -- SECONDARY_QUANTITY
4417 , wlc.secondary_uom_code -- SECONDARY_UOM_CODE
4418 , NULL -- CALLER
4419 , NULL -- SOURCE_TRANSACTION_ID
4420 , msn.serial_number -- TO_SERIAL_NUMBER
4421 , wlpn1.source_type_id -- SOURCE_TYPE_ID
4422 , wlpn1.source_header_id -- SOURCE_HEADER_ID
4423 , wlpn1.source_line_id -- SOURCE_LINE_ID
4424 , wlpn1.source_line_detail_id -- SOURCE_LINE_DETAIL_ID
4425 , wlpn1.source_name -- SOURCE_NAME
4426 , wlpn1.lpn_id -- PARENT_LPN_ID
4427 , wlpn1.license_plate_number -- PARENT_LICENSE_PLATE_NUMBER
4428 , wlpn2.lpn_id -- LPN_ID
4429 , wlpn2.license_plate_number -- LICENSE_PLATE_NUMBER
4430 , wlc.inventory_item_id -- INVENTORY_ITEM_ID
4431 , wlc.item_description -- ITEM_DESCRIPTION
4432 , wlc.revision -- REVISION
4433 , wlc.lot_number -- LOT_NUMBER
4434 , msn.serial_number -- SERIAL_NUMBER
4435 , NVL2(msn.serial_number, 1,wlc.quantity) --QUANTITY
4436 , wlc.uom_code -- UOM_CODE
4437 , wlpn1.organization_id -- ORGANIZATION_ID
4438 , wlpn1.subinventory_code -- SUBINVENTORY_CODE
4439 , wlpn1.locator_id -- LOCATOR_ID
4440 , wlpn1.status_id -- STATUS_ID
4441 --, wlpn1.lpn_state -- LPN_STATE --Commented for Bug#7828840
4442 , wlpn1.sealed_status -- SEALED_STATUS
4443 , 2 -- OPERATION_MODE
4444 , SYSDATE -- LAST_UPDATE_DATE
4445 , FND_GLOBAL.USER_ID -- LAST_UPDATED_BY
4446 , SYSDATE -- CREATION_DATE
4447 , FND_GLOBAL.USER_ID -- CREATED_BY
4448 , FND_GLOBAL.USER_ID -- LAST_UPDATE_LOGIN
4449 , NULL -- REQUEST_ID
4450 , NULL -- PROGRAM_APPLICATION_ID
4451 , NULL -- PROGRAM_ID
4452 , NULL -- PROGRAM_UPDATE_DATE
4453 , wlpn1.attribute_category
4454 , wlpn1.ATTRIBUTE1
4455 , wlpn1.ATTRIBUTE2
4456 , wlpn1.ATTRIBUTE3
4457 , wlpn1.ATTRIBUTE4
4458 , wlpn1.ATTRIBUTE5
4459 , wlpn1.ATTRIBUTE6
4460 , wlpn1.ATTRIBUTE7
4461 , wlpn1.ATTRIBUTE8
4462 , wlpn1.ATTRIBUTE9
4463 , wlpn1.ATTRIBUTE10
4464 , wlpn1.ATTRIBUTE11
4465 , wlpn1.ATTRIBUTE12
4466 , wlpn1.ATTRIBUTE13
4467 , wlpn1.ATTRIBUTE14
4468 , wlpn1.ATTRIBUTE15
4469 , wlc.cost_group_id -- COST_GROUP_ID
4470 , wlpn1.lpn_context -- LPN_CONTEXT
4471 , wlpn1.lpn_reusability -- LPN_REUSABILITY
4472 , wlpn1.lpn_id -- OUTERMOST_LPN_ID
4473 , l_outermost_lpn_name -- OUTERMOST_LICENSE_PLATE_NUMBER
4474 , wlpn1.homogeneous_container -- HOMOGENEOUS_CONTAINER
4475 FROM wms_license_plate_numbers wlpn1
4476 , wms_license_plate_numbers wlpn2
4477 , wms_lpn_contents wlc
4478 , mtl_serial_numbers msn
4479 WHERE wlpn1.lpn_id = wlc.parent_lpn_id(+)
4480 AND wlpn1.lpn_id = msn.lpn_id(+)
4481 AND wlpn1.lpn_id = wlpn2.parent_lpn_id(+)
4482 AND wlpn1.lpn_id = all_child_wlpn_rec.lpn_id;
4483
4484 DELETE FROM wms_lpn_contents
4485 WHERE parent_lpn_id = all_child_wlpn_rec.lpn_id;
4486
4487
4488 IF p_clear_attributes = 'Y' THEN
4489 UPDATE wms_license_plate_numbers
4490 SET ATTRIBUTE1 = NULL
4491 , ATTRIBUTE2 = NULL
4492 , ATTRIBUTE3 = NULL
4493 , ATTRIBUTE4 = NULL
4494 , ATTRIBUTE5 = NULL
4495 , ATTRIBUTE6 = NULL
4496 , ATTRIBUTE7 = NULL
4497 , ATTRIBUTE8 = NULL
4498 , ATTRIBUTE9 = NULL
4499 , ATTRIBUTE10 = NULL
4500 , ATTRIBUTE11 = NULL
4501 , ATTRIBUTE12 = NULL
4502 , ATTRIBUTE13 = NULL
4503 , ATTRIBUTE14 = NULL
4504 , ATTRIBUTE15 = NULL
4505 , ATTRIBUTE_CATEGORY = NULL
4506 WHERE lpn_id = all_child_wlpn_rec.lpn_id;
4507 END IF;
4508
4509 IF p_new_org_id IS NOT NULL THEN
4510 UPDATE wms_license_plate_numbers
4511 SET lpn_context = 5
4512 , subinventory_code = NULL
4513 , locator_id = NULL
4514 , parent_lpn_id = NULL
4515 , outermost_lpn_id = all_child_wlpn_rec.lpn_id
4516 , organization_id = p_new_org_id
4517 WHERE lpn_id = all_child_wlpn_rec.lpn_id;
4518 ELSE
4519 UPDATE wms_license_plate_numbers
4520 SET lpn_context = 5
4521 , subinventory_code = NULL
4522 , locator_id = NULL
4523 , parent_lpn_id = NULL
4524 , outermost_lpn_id = all_child_wlpn_rec.lpn_id
4525 WHERE lpn_id = all_child_wlpn_rec.lpn_id;
4526 END IF;
4527
4528 UPDATE mtl_serial_numbers
4529 SET lpn_id = NULL
4530 WHERE lpn_id = all_child_wlpn_rec.lpn_id
4531 AND current_organization_id = l_organization_id;
4532
4533 UPDATE wms_license_plate_numbers
4534 SET content_volume = NULL
4535 , content_volume_uom_code = NULL
4536 WHERE lpn_id = all_child_wlpn_rec.lpn_id;
4537
4538 IF l_container_item_id IS NULL
4539 OR p_clear_containter_item_id = 'Y' THEN
4540 UPDATE wms_license_plate_numbers
4541 SET inventory_item_id = NULL
4542 , gross_weight = NULL
4543 , gross_weight_uom_code = NULL
4544 , tare_weight = NULL
4545 , tare_weight_uom_code = NULL
4546 , container_volume = NULL
4547 , container_volume_uom = NULL
4548 WHERE lpn_id = all_child_wlpn_rec.lpn_id;
4549
4550 ELSIF p_clear_containter_item_id = 'N'
4551 AND l_container_item_id IS NOT NULL THEN
4552 IF ( NOT inv_cache.set_item_rec( p_organization_id => p_new_org_id
4553 , p_item_id => l_container_item_id ) ) THEN
4554 IF l_debug = 1 THEN
4555 mdebug('Error calling inv_cache.set_item_rec');
4556 END IF;
4557 RAISE fnd_api.g_exc_unexpected_error;
4558 END IF;
4559
4560 UPDATE wms_license_plate_numbers
4561 SET gross_weight = inv_cache.item_rec.unit_weight
4562 , gross_weight_uom_code = inv_cache.item_rec.weight_uom_code
4563 , tare_weight = inv_cache.item_rec.unit_weight
4564 , tare_weight_uom_code = inv_cache.item_rec.weight_uom_code
4565 , container_volume = inv_cache.item_rec.unit_volume
4566 , container_volume_uom = inv_cache.item_rec.volume_uom_code
4567 WHERE lpn_id = all_child_wlpn_rec.lpn_id;
4568 END IF;
4569
4570 END LOOP;
4571
4572 END IF;
4573
4574 IF p_commit = fnd_api.g_true THEN
4575 IF l_debug = 1 THEN
4576 mdebug('p_commit is true, committing the transaction');
4577 END IF;
4578 COMMIT;
4579 END IF;
4580
4581 x_return_status := 'S';
4582
4583 IF l_debug = 1 THEN
4584 mdebug('Returning normally from REUSE_LPNS');
4585 END IF;
4586
4587 EXCEPTION
4588
4589 WHEN l_invalid_org THEN
4590 FND_MESSAGE.SET_NAME('WMS', 'WMS_CONT_INVALID_ORG');
4591 FND_MSG_PUB.ADD;
4592 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4593 x_return_status := 'E';
4594 WHEN l_invalid_lpn THEN
4595 x_return_status := 'E';
4596 WHEN OTHERS THEN
4597 FND_MESSAGE.SET_NAME('WMS', 'WMS_UNEXPECTED_ERROR');
4598 FND_MSG_PUB.ADD;
4599 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
4600 x_return_status := 'E';
4601 ROLLBACK TO REUSE_LPN_SP;
4602 END REUSE_LPNS;
4603 -- End of package
4604 END WMS_CONTAINER_PUB;