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