[Home] [Help]
PACKAGE BODY: APPS.WMS_CONSOLIDATION_PUB
Source
1 PACKAGE BODY wms_consolidation_pub AS
2 /* $Header: WMSCONSB.pls 120.13.12010000.2 2008/08/25 06:50:57 anviswan ship $ */
3
4 /** Globals to hold Logging attributes **/
5 g_trace_on NUMBER := 0;
6
7 g_loc_type_dock_door CONSTANT NUMBER := inv_globals.g_loc_type_dock_door;
8 g_loc_type_staging_lane CONSTANT NUMBER := inv_globals.g_loc_type_staging_lane;
9 g_loc_type_consolidation CONSTANT NUMBER := inv_globals.g_loc_type_consolidation;
10 g_loc_type_packing_station CONSTANT NUMBER := inv_globals.g_loc_type_packing_station;
11
12
13 PROCEDURE get_values_for_loc(p_sub IN VARCHAR2,
14 p_loc_id IN NUMBER,
15 p_org_id IN NUMBER,
16 p_comp_cons_dels_inq_mode IN VARCHAR2,
17 x_total_no_of_dels OUT NOCOPY NUMBER,
18 x_total_no_of_cons_dels OUT NOCOPY NUMBER,
19 x_total_no_of_lpns OUT NOCOPY NUMBER,
20 x_return_status OUT NOCOPY VARCHAR2,
21 x_msg_count OUT NOCOPY NUMBER,
22 x_msg_data OUT NOCOPY VARCHAR2)
23
24 IS
25
26 CURSOR del_csr IS
27 SELECT DISTINCT
28 wda.delivery_id,
29 mil.inventory_location_id
30 FROM
31 wms_license_plate_numbers wlpn,
32 wsh_delivery_details wdd,
33 wsh_delivery_assignments_v wda,
34 mtl_item_locations mil
35 WHERE wlpn.organization_id = p_org_id
36 AND wlpn.locator_id = mil.inventory_location_id
37 AND Nvl(mil.physical_location_id, mil.inventory_location_id) = p_loc_id
38 AND wlpn.subinventory_code = p_sub
39 AND wlpn.lpn_context in ( 11,12)
40 AND wlpn.lpn_id = wdd.lpn_id
41 AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
42 AND wdd.organization_id = p_org_id
43 AND wda.delivery_id IS NOT NULL
44 AND wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
45
46 l_count NUMBER;
47 l_delivery_id NUMBER;
48 l_locator_id NUMBER;
49
50 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
51 BEGIN
52
53 g_trace_on := fnd_profile.value('INV_DEBUG_TRACE') ;
54 x_return_status := FND_API.G_RET_STS_SUCCESS;
55
56 x_total_no_of_dels := 0;
57 x_total_no_of_cons_dels := 0;
58
59
60 OPEN del_csr;
61 LOOP
62 FETCH del_csr INTO l_delivery_id, l_locator_id;
63 EXIT WHEN del_csr%notfound;
64
65 x_total_no_of_dels := x_total_no_of_dels + 1;
66
67 IF wms_consolidation_pub.is_delivery_consolidated
68 (p_delivery_id => l_delivery_id,
69 p_org_id => p_org_id,
70 p_sub => p_sub,
71 p_loc_id => l_locator_id) = 'Y' THEN
72
73 x_total_no_of_cons_dels := x_total_no_of_cons_dels + 1;
74
75 END IF;
76
77 END LOOP;
78 CLOSE del_csr;
79
80
81 SELECT COUNT(DISTINCT wlpn.outermost_lpn_id)
82 INTO x_total_no_of_lpns
83 FROM
84 wms_license_plate_numbers wlpn,
85 mtl_item_locations mil
86 WHERE wlpn.lpn_context in (11,12)
87 AND wlpn.organization_id = p_org_id
88 AND wlpn.subinventory_code = p_sub
89 AND wlpn.locator_id = mil.inventory_location_id
90 AND Nvl(mil.physical_location_id, mil.inventory_location_id) = p_loc_id
91 AND wlpn.lpn_id = wlpn.outermost_lpn_id;
92
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 EXCEPTION
96
97 WHEN FND_API.G_EXC_ERROR THEN
98 x_return_status:=FND_API.G_RET_STS_ERROR;
99 fnd_msg_pub.count_and_get
100 ( p_count => x_msg_count
101 , p_data => x_msg_data
102 );
103
104 IF (g_trace_on = 1) THEN mydebug('get_values_for_loc: Error in get_values_for_loc API: ' || sqlerrm);
105 END IF;
106
107 WHEN OTHERS THEN
108
109 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
110 fnd_msg_pub.count_and_get
111 ( p_count => x_msg_count
112 , p_data => x_msg_data
113 );
114
115 IF (g_trace_on = 1) THEN mydebug('get_values_for_loc: Unexpected Error in get_values_for_loc API: ' || sqlerrm);
116 END IF;
117
118 END get_values_for_loc;
119
120
121
122 PROCEDURE get_consolidation_inq_loc(x_loc IN OUT NOCOPY VARCHAR2,
123 p_sub IN VARCHAR2,
124 p_org_id IN NUMBER,
125 p_comp_cons_dels_inq_mode IN VARCHAR2,
126 x_total_no_of_dels OUT NOCOPY NUMBER,
127 x_total_no_of_cons_dels OUT NOCOPY NUMBER,
128 x_total_no_of_lpns OUT NOCOPY NUMBER,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_count OUT NOCOPY NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2,
132 x_loc_available OUT NOCOPY VARCHAR2,
133 x_loc_count OUT NOCOPY NUMBER)
134 IS
135
136 l_return_status VARCHAR2(10);
137 l_loc_id NUMBER;
138 l_loc VARCHAR2(30);
139 l_loc_id1 NUMBER;
140 l_temp_var NUMBER;
141 /*******************************
142 * Bug No : 3481421
143 * Changed the datatype to the type of the associated column.
144 * VARCHAR2(30) was not eanough to hold the 'meaning' column under
145 * translated environments
146 ********************************/
147 l_type_meaning mfg_lookups.meaning%TYPE;
148
149 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
150
151 CURSOR all_locs_csr
152 IS
153 -- need to query from both mil and milk
154 -- because we need to reference picking_order and concatenated_segments
155 SELECT DISTINCT
156 milk.inventory_location_id,
157 nvl(milk.inventory_location_type, 3),
158 mil.picking_order,
159 mil.dropping_order,
160 ml.meaning
161 FROM
162 mtl_item_locations_kfv milk,
163 mtl_item_locations mil,
164 wms_license_plate_numbers wlpn,
165 mfg_lookups ml
166 WHERE milk.organization_id = p_org_id
167 AND milk.subinventory_code = p_sub
168 AND milk.concatenated_segments LIKE l_loc
169 AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
170 AND mil.inventory_location_id = wlpn.locator_id
171 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
172 AND milk.project_id IS NULL
173 AND milk.task_id IS NULL
174 AND milk.organization_id = mil.organization_id
175 AND wlpn.lpn_context in ( 11,12)
176 AND wlpn.organization_id = mil.organization_id
177 AND wlpn.subinventory_code = mil.subinventory_code
178 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
179 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
180 ORDER BY ml.meaning, mil.picking_order, mil.dropping_order, milk.inventory_location_id;
181
182 CURSOR cons_locs_csr
183 IS
184 SELECT DISTINCT
185 milk.inventory_location_id,
186 nvl(milk.inventory_location_type, 3),
187 mil.picking_order,
188 mil.dropping_order,
189 ml.meaning
190 FROM
191 mtl_item_locations_kfv milk,
192 mtl_item_locations mil,
193 wms_license_plate_numbers wlpn,
194 wsh_delivery_details wdd2,
195 wsh_delivery_assignments_v wda,
196 mfg_lookups ml
197 WHERE milk.organization_id = p_org_id
198 AND milk.subinventory_code = p_sub
199 AND milk.concatenated_segments LIKE l_loc
200 AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
201 AND mil.inventory_location_id = wlpn.locator_id
202 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
203 AND milk.organization_id = mil.organization_id
204 AND milk.project_id IS NULL
205 AND milk.task_id IS NULL
206 AND wlpn.lpn_context in ( 11,12)
207 AND wlpn.subinventory_code = mil.subinventory_code
208 AND wlpn.organization_id = mil.organization_id
209 AND wlpn.lpn_id = wdd2.lpn_id
210 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
211 AND wda.delivery_id IS NOT NULL
212 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
213 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
214 AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
215 AND wms_consolidation_pub.is_delivery_consolidated
216 (wda.delivery_id,
217 p_org_id,
218 p_sub,
219 mil.inventory_location_id) = 'Y'
220 ORDER BY ml.meaning, mil.picking_order, mil.dropping_order, milk.inventory_location_id;
221
222 BEGIN
223
224 IF (l_debug = 1) THEN
225 mydebug('Enter get_consolidation_inq_loc');
226 mydebug('x_loc : ' ||x_loc);
227 mydebug('p_sub : ' ||p_sub);
228 mydebug('p_org_id : ' ||p_org_id);
229 mydebug('p_comp_cons_dels_inq_mode : ' ||p_comp_cons_dels_inq_mode);
230 END IF;
231
232 g_trace_on := fnd_profile.value('INV_DEBUG_TRACE') ;
233 l_return_status := FND_API.G_RET_STS_SUCCESS;
234 x_loc_available := 'Y';
235 l_loc := (x_loc || '%');
236 x_loc_count := 0;
237
238 IF p_comp_cons_dels_inq_mode = 'ALL' THEN
239
240 OPEN all_locs_csr;
241 LOOP
242 FETCH all_locs_csr INTO l_loc_id1, l_temp_var, l_temp_var, l_temp_var, l_type_meaning;
243 EXIT WHEN all_locs_csr%notfound;
244
245 x_loc_count := x_loc_count + 1;
246
247 IF x_loc_count = 1 THEN l_loc_id := l_loc_id1;
248 END IF;
249
250 END LOOP;
251 CLOSE all_locs_csr;
252
253 ELSE
254
255 OPEN cons_locs_csr;
256 LOOP
257 FETCH cons_locs_csr INTO l_loc_id1, l_temp_var, l_temp_var, l_temp_var, l_type_meaning;
258 EXIT WHEN cons_locs_csr%notfound;
259
260 x_loc_count := x_loc_count + 1;
261
262 IF x_loc_count = 1 THEN l_loc_id := l_loc_id1;
263 END IF;
264
265 END LOOP;
266 CLOSE cons_locs_csr;
267
268 END IF;
269
270
271 IF x_loc_count = 0 THEN
272
273 x_loc_available := 'N';
274 x_return_status := l_return_status;
275 RETURN;
276
277 END IF;
278
279 IF (l_debug = 1) THEN
280 mydebug('get_consolidation_inq_loc: before calling wms_consolidation_pub.get_values_for_loc');
281 mydebug('p_sub : '||p_sub);
282 mydebug('l_loc_id : '||l_loc_id);
283 mydebug('p_org_id : '||p_org_id);
284 mydebug('p_comp_cons_dels_inq_mode : '||p_comp_cons_dels_inq_mode);
285 END IF;
286
287 wms_consolidation_pub.get_values_for_loc
288 (p_sub => p_sub,
289 p_loc_id => l_loc_id,
290 p_org_id => p_org_id,
291 p_comp_cons_dels_inq_mode => p_comp_cons_dels_inq_mode,
292 x_total_no_of_dels => x_total_no_of_dels,
293 x_total_no_of_cons_dels => x_total_no_of_cons_dels,
294 x_total_no_of_lpns => x_total_no_of_lpns,
295 x_return_status => l_return_status,
296 x_msg_count => x_msg_count,
297 x_msg_data => x_msg_data);
298
299 IF (l_debug = 1) THEN
300 mydebug('x_total_no_of_dels : '||x_total_no_of_dels);
301 mydebug('x_total_no_of_cons_dels : '||x_total_no_of_cons_dels);
302 mydebug('x_total_no_of_lpns : '||x_total_no_of_lpns);
303 mydebug('l_return_status : '||l_return_status);
304 mydebug('x_msg_data : '||x_msg_data);
305 mydebug('x_msg_count : '||x_msg_count);
306 END IF;
307
308
309 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
310
311 RAISE FND_API.G_exc_unexpected_error;
312
313 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
314
315 RAISE FND_API.g_exc_error;
316
317 END IF;
318
319 x_loc := INV_PROJECT.GET_LOCSEGS(l_loc_id,p_org_id);
320 x_return_status := l_return_status;
321
322 IF (l_debug = 1) THEN
323
324 mydebug('x_total_no_of_dels : ' ||x_total_no_of_dels);
325 mydebug('x_total_no_of_cons_dels : ' ||x_total_no_of_cons_dels);
326 mydebug('x_total_no_of_lpns : ' ||x_total_no_of_lpns);
327 mydebug('x_return_status : ' ||x_return_status);
328 mydebug('x_loc_available : ' ||x_loc_available);
329 mydebug('x_loc_count : ' ||x_loc_count);
330 mydebug('Exiting get_consolidation_inq_loc');
331
332 END IF;
333
334
335 EXCEPTION
336
337 WHEN FND_API.G_EXC_ERROR THEN
338 x_return_status:=FND_API.G_RET_STS_ERROR;
339 fnd_msg_pub.count_and_get
340 ( p_count => x_msg_count
341 , p_data => x_msg_data
342 );
343
344 IF (g_trace_on = 1) THEN mydebug('get_consolidation_inq_loc: Error in get_consolidation_inq_loc API: ' || sqlerrm);
345 END IF;
346
347 WHEN OTHERS THEN
348
349 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
350 fnd_msg_pub.count_and_get
351 ( p_count => x_msg_count
352 , p_data => x_msg_data
353 );
354
355 IF (g_trace_on = 1) THEN mydebug('get_consolidation_inq_loc: Unexpected Error in get_consolidation_inq_loc API: ' || sqlerrm);
356 END IF;
357
358 END get_consolidation_inq_loc;
359
360
361
362 PROCEDURE get_consolidation_inq_lpn_lov(x_lpn_lov OUT NOCOPY t_genref,
363 p_org_id IN NUMBER,
364 p_lpn IN VARCHAR2)
365
366 IS
367
368 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
369 BEGIN
370
371 OPEN x_lpn_lov FOR
372 SELECT DISTINCT
373 wlpn.license_plate_number,
374 milk.subinventory_code,
375 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
376 wlpn.lpn_id,
377 /* Need to get LocatorId for LMS project, added by Anupam Jain*/
378 milk.inventory_location_id,
379 /* lms code end */
380 nvl(milk.inventory_location_type, 3),
381 mil.picking_order
382 FROM
383 wms_license_plate_numbers wlpn,
384 mtl_item_locations_kfv milk,
385 mtl_item_locations mil
386 WHERE wlpn.lpn_context in (11, 12)
387 AND wlpn.organization_id = p_org_id
388 AND wlpn.license_plate_number LIKE p_lpn
389 AND wlpn.outermost_lpn_id = wlpn.lpn_id
390 AND milk.organization_id = wlpn.organization_id
391 AND milk.inventory_location_id = wlpn.locator_id
392 AND milk.inventory_location_id = mil.inventory_location_id
393 AND milk.organization_id = mil.organization_id
394 AND milk.subinventory_code = wlpn.subinventory_code
395 AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
396 ORDER BY nvl(milk.inventory_location_type, 3), mil.picking_order;
397
398 END get_consolidation_inq_lpn_lov;
399
400
401
402 PROCEDURE get_consolidation_inq_del_lov(x_deliveryLOV OUT NOCOPY t_genref,
403 p_organization_id IN NUMBER,
404 p_delivery_name IN VARCHAR2,
405 p_lpn_id IN NUMBER)
406 IS
407 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
408 BEGIN
409
410 IF p_lpn_id IS NOT NULL AND p_lpn_id <> 0 THEN
411
412 OPEN x_deliveryLOV for
413
414 SELECT DISTINCT
415 wnd.name Delivery,
416 wnd.delivery_id,
417 inv_shipping_transaction_pub.get_shipmethod_meaning(wnd.ship_method_code)
418 FROM
419 wsh_new_deliveries wnd,
420 wsh_delivery_assignments_v wda,
421 wsh_delivery_details wdd,
422 wms_license_plate_numbers wlpn
423 WHERE wda.parent_delivery_detail_id = wdd.delivery_detail_id
424 AND wda.delivery_id = wnd.delivery_id
425 AND wnd.name LIKE p_delivery_name
426 AND wnd.organization_id = p_organization_id
427 AND wlpn.organization_id = p_organization_id
428 AND wlpn.outermost_lpn_id = p_lpn_id
429 AND wdd.lpn_id = wlpn.lpn_id
430 AND wlpn.lpn_context in ( 11,12)
431 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
432 ORDER BY wnd.name;
433
434 ELSE
435
436 OPEN x_deliveryLOV for
437 SELECT DISTINCT
438 wnd.name Delivery,
439 wnd.delivery_id,
440 inv_shipping_transaction_pub.get_shipmethod_meaning(wnd.ship_method_code)
441 FROM wsh_new_deliveries_ob_grp_v wnd,
442 wsh_delivery_assignments_v wda,
443 wsh_delivery_details_ob_grp_v wdd
444 WHERE wda.delivery_Detail_id = wdd.delivery_Detail_id
445 AND wda.delivery_id = wnd.delivery_id
446 AND wdd.released_status = 'Y'
447 AND wnd.organization_id = p_organization_id
448 AND wnd.name LIKE p_delivery_name
449 ORDER BY wnd.name;
450
451 END IF;
452
453 END get_consolidation_inq_del_lov;
454
455
456 PROCEDURE get_cons_inq_orders_lov(x_order_lov OUT NOCOPY t_genref,
457 p_org_id IN NUMBER,
458 p_order IN VARCHAR2,
459 p_delivery_id IN NUMBER,
460 p_lpn_id IN NUMBER)
461 IS
462
463 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
464 BEGIN
465
466 IF p_delivery_id IS NOT NULL AND p_delivery_id <> 0 THEN
467
468 IF p_lpn_id IS NOT NULL AND p_lpn_id <> 0 THEN
469
470 --Both delivery and LPN were entered
471
472 OPEN x_order_lov FOR
473
474 SELECT DISTINCT
475 wdd.source_header_number,
476 wda.delivery_id,
477 --c.customer_name -- Commented for Bug# 4579790
481 wsh_delivery_assignments_v wda,
478 party.party_name customer_name -- Added for Bug# 4579790
479 FROM
480 wsh_delivery_details wdd,
482 -- ra_customers c, -- Commented for Bug# 4579790
483 hz_parties party, -- Added for Bug# 4579790
484 hz_cust_accounts cust_acct, -- Added for Bug# 4579790
485 wms_license_plate_numbers wlpn,
486 wsh_delivery_details wdd2
487 WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
488 --wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
489 AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
490 AND wdd.organization_id = p_org_id
491 AND wdd.released_status IN ('Y')
492 AND wdd.source_header_number LIKE p_order
493 AND wda.delivery_detail_id = wdd.delivery_detail_id
494 AND wda.delivery_id = p_delivery_id
495 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
496 AND wlpn.organization_id = p_org_id
497 AND wlpn.outermost_lpn_id = p_lpn_id
498 AND wlpn.lpn_context in ( 11,12)
499 AND wlpn.lpn_id = wdd2.lpn_id
500 ORDER BY 1;
501
502
503 ELSE
504
505 -- Only delivery was entered
506
507 OPEN x_order_lov FOR
508
509 SELECT DISTINCT
510 wdd.source_header_number,
511 wda.delivery_id,
512 --c.customer_name -- Commented for Bug# 4579790
513 party.party_name customer_name -- Added for Bug# 4579790
514 FROM
515 wsh_delivery_details wdd,
516 wsh_delivery_assignments_v wda,
517 -- ra_customers c, -- Commented for Bug# 4579790
518 hz_parties party, -- Added for Bug# 4579790
519 hz_cust_accounts cust_acct -- Added for Bug# 4579790
520 WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
521 --wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
522 AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
523 AND wdd.organization_id = p_org_id
524 AND wdd.released_status IN ('Y')
525 AND wda.delivery_Detail_id = wdd.delivery_Detail_id
526 AND wdd.source_header_number LIKE p_order
527 AND wda.delivery_id = p_delivery_id
528 ORDER BY 1;
529
530 END IF;
531
532 ELSIF p_lpn_id IS NOT NULL AND p_lpn_id <> 0 THEN
533
534 --Only LPN was entered
535
536 OPEN x_order_lov FOR
537
538 SELECT DISTINCT
539 wdd.source_header_number,
540 wda.delivery_id,
541 --c.customer_name -- Commented for Bug# 4579790
542 party.party_name customer_name -- Added for Bug# 4579790
543 FROM
544 wsh_delivery_details wdd,
545 wsh_delivery_assignments_v wda,
546 -- ra_customers c, -- Commented for Bug# 4579790
547 hz_parties party, -- Added for Bug# 4579790
548 hz_cust_accounts cust_acct, -- Added for Bug# 4579790
549 wms_license_plate_numbers wlpn,
550 wsh_delivery_details wdd2
551 WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
552 --wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
553 AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
554 AND wdd.organization_id = p_org_id
555 AND wdd.released_status IN ('Y')
556 AND wdd.source_header_number LIKE p_order
557 AND wda.delivery_Detail_id = wdd.delivery_Detail_id
558 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
559 AND wlpn.organization_id = p_org_id
560 AND wlpn.outermost_lpn_id = p_lpn_id
561 AND wlpn.lpn_context in ( 11,12)
562 AND wdd2.released_status = 'X'
563 AND wlpn.lpn_id = wdd2.lpn_id
564 ORDER BY 1;
565
566
567 ELSE
568
569 --Neither LPN nor delivery was entered
570
571 OPEN x_order_lov FOR
572
573 SELECT DISTINCT
574 wdd.source_header_number,
575 wda.delivery_id,
576 --c.customer_name -- Commented for Bug# 4579790
577 party.party_name customer_name -- Added for Bug# 4579790
578 FROM
579 wsh_delivery_details_ob_grp_v wdd,
580 wsh_delivery_assignments_v wda,
581 -- ra_customers c, -- Commented for Bug# 4579790
582 hz_parties party, -- Added for Bug# 4579790
583 hz_cust_accounts cust_acct -- Added for Bug# 4579790
587 AND wdd.organization_id = p_org_id
584 WHERE wdd.customer_id = cust_acct.cust_account_id -- Added for Bug# 4579790
585 --wdd.customer_id = c.customer_id -- Commented for Bug# 4579790
586 AND cust_acct.party_id = party.party_id -- Added for Bug# 4579790
588 AND wdd.released_status IN ('Y')
589 AND wda.delivery_Detail_id = wdd.delivery_Detail_id
590 AND wdd.source_header_number LIKE p_order
591 ORDER BY 1;
592
593 END IF;
594
595 END get_cons_inq_orders_lov;
596
597
598
599 PROCEDURE get_consolidation_inq_sub_lov(x_sub_lov OUT NOCOPY t_genref,
600 p_sub IN VARCHAR2,
601 p_org_id IN NUMBER)
602
603 IS
604
605 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
606 BEGIN
607
608 OPEN x_sub_lov FOR
609
610 SELECT
611 msi.secondary_inventory_name,
612 Nvl(msi.locator_type, 1),
613 msi.description,
614 msi.asset_inventory,
615 msi.lpn_controlled_flag,
616 msi.picking_order,
617 msi.enable_locator_alias
618 FROM
619 mtl_secondary_inventories msi
620 WHERE msi.organization_id = p_org_id
621 AND msi.lpn_controlled_flag = 1
622 AND msi.secondary_inventory_name LIKE p_sub
623 ORDER BY msi.picking_order;
624
625 END get_consolidation_inq_sub_lov;
626
627
628 PROCEDURE get_consolidation_inq_loc_lov(x_loc_lov OUT NOCOPY t_genref,
629 p_sub IN VARCHAR2,
630 p_loc IN VARCHAR2,
631 p_org_id IN NUMBER,
632 p_comp_cons_dels_inq_mode IN VARCHAR2,
633 p_alias IN VARCHAR2)
634
635 IS
636
637 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
638 BEGIN
639
640 IF p_alias IS NULL THEN
641 get_consolidation_inq_loc_lov(
642 x_loc_lov => x_loc_lov
643 ,p_sub => p_sub
644 ,p_loc => p_loc
645 ,p_org_id => p_org_id
646 ,p_comp_cons_dels_inq_mode => p_comp_cons_dels_inq_mode
647 );
648 RETURN;
649 END IF;
650
651 IF p_comp_cons_dels_inq_mode = 'ALL' THEN
652
653 OPEN x_loc_lov FOR
654
655 SELECT DISTINCT
656 milk.inventory_location_id,
657 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
658 milk.description,
659 ml.meaning,
660 mil.picking_order
661 FROM
662 mtl_item_locations_kfv milk,
663 mtl_item_locations mil,
664 wms_license_plate_numbers wlpn,
665 mfg_lookups ml
666 WHERE milk.organization_id = p_org_id
667 AND milk.alias = p_alias
668 AND milk.subinventory_code = p_sub
669 AND milk.project_id IS NULL
670 AND milk.task_id IS NULL
671 AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
672 AND mil.inventory_location_id = wlpn.locator_id
673 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
674 AND milk.organization_id = mil.organization_id
675 AND wlpn.lpn_context in ( 11,12)
676 AND wlpn.organization_id = mil.organization_id
677 AND wlpn.subinventory_code = mil.subinventory_code
678 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
679 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
680 ORDER BY ml.meaning, mil.picking_order;
681
682 ELSE
683
684
685 OPEN x_loc_lov FOR
686
687 SELECT DISTINCT
688 milk.inventory_location_id,
689 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
690 milk.description,
691 ml.meaning,
692 mil.picking_order
693 FROM
694 mtl_item_locations_kfv milk,
695 mtl_item_locations mil,
696 wms_license_plate_numbers wlpn,
697 wsh_delivery_details wdd2,
698 wsh_delivery_assignments_v wda,
699 mfg_lookups ml
700 WHERE milk.organization_id = p_org_id
701 AND milk.subinventory_code = p_sub
702 AND milk.alias = p_alias
703 AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
704 AND mil.inventory_location_id = wlpn.locator_id
705 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
706 AND milk.organization_id = mil.organization_id
707 AND milk.project_id IS NULL
708 AND milk.task_id IS NULL
709 AND wlpn.lpn_context in ( 11,12)
710 AND wlpn.subinventory_code = mil.subinventory_code
711 AND wlpn.organization_id = mil.organization_id
712 AND wlpn.lpn_id = wdd2.lpn_id
713 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
714 AND wda.delivery_id IS NOT NULL
715 AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
716 AND wms_consolidation_pub.is_delivery_consolidated
717 (wda.delivery_id,
718 p_org_id,
719 p_sub,
720 mil.inventory_location_id) = 'Y'
721 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
725 END IF;
722 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
723 ORDER BY ml.meaning, mil.picking_order;
724
726
727 END get_consolidation_inq_loc_lov;
728 PROCEDURE get_consolidation_inq_loc_lov(x_loc_lov OUT NOCOPY t_genref,
729 p_sub IN VARCHAR2,
730 p_loc IN VARCHAR2,
731 p_org_id IN NUMBER,
732 p_comp_cons_dels_inq_mode IN VARCHAR2)
733
734 IS
735
736 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
737 BEGIN
738
739 IF p_comp_cons_dels_inq_mode = 'ALL' THEN
740
741 OPEN x_loc_lov FOR
742
743 SELECT DISTINCT
744 milk.inventory_location_id,
745 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
746 milk.description,
747 ml.meaning,
748 mil.picking_order
749 FROM
750 mtl_item_locations_kfv milk,
751 mtl_item_locations mil,
752 wms_license_plate_numbers wlpn,
753 mfg_lookups ml
754 WHERE milk.organization_id = p_org_id
755 AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc -- bug 2769126
756 AND milk.subinventory_code = p_sub
757 AND milk.project_id IS NULL
758 AND milk.task_id IS NULL
759 AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
760 AND mil.inventory_location_id = wlpn.locator_id
761 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
762 AND milk.organization_id = mil.organization_id
763 AND wlpn.lpn_context in ( 11,12)
764 AND wlpn.organization_id = mil.organization_id
765 AND wlpn.subinventory_code = mil.subinventory_code
766 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
767 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
768 ORDER BY ml.meaning, mil.picking_order;
769
770 ELSE
771
772
773 OPEN x_loc_lov FOR
774
775 SELECT DISTINCT
776 milk.inventory_location_id,
777 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
778 milk.description,
779 ml.meaning,
780 mil.picking_order
781 FROM
782 mtl_item_locations_kfv milk,
783 mtl_item_locations mil,
784 wms_license_plate_numbers wlpn,
785 wsh_delivery_details wdd2,
786 wsh_delivery_assignments_v wda,
787 mfg_lookups ml
788 WHERE milk.organization_id = p_org_id
789 AND milk.subinventory_code = p_sub
790 AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc -- bug 2769126
791 AND nvl(milk.inventory_location_type, 3) NOT IN (g_loc_type_dock_door)
792 AND mil.inventory_location_id = wlpn.locator_id
793 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
794 AND milk.organization_id = mil.organization_id
795 AND milk.project_id IS NULL
796 AND milk.task_id IS NULL
797 AND wlpn.lpn_context in ( 11,12)
798 AND wlpn.subinventory_code = mil.subinventory_code
799 AND wlpn.organization_id = mil.organization_id
800 AND wlpn.lpn_id = wdd2.lpn_id
801 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
802 AND wda.delivery_id IS NOT NULL
803 AND wms_consolidation_pub.is_delivery_consolidated
804 (wda.delivery_id,
805 p_org_id,
806 p_sub,
807 mil.inventory_location_id) = 'Y'
808 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
809 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
810 AND wdd2.released_status = 'X' -- For LPN reuse ER : 6845650
811 ORDER BY ml.meaning, mil.picking_order;
812
813 END IF;
814
815 END get_consolidation_inq_loc_lov;
816
817
818
819 PROCEDURE get_values_for_lpn(p_lpn_id IN NUMBER,
820 p_org_id IN NUMBER,
821 x_sub IN OUT NOCOPY VARCHAR2,
822 x_loc IN OUT NOCOPY VARCHAR2,
823 x_delivery_id IN OUT NOCOPY NUMBER,
824 x_order_number IN OUT NOCOPY VARCHAR2,
825 p_inquiry_mode IN NUMBER,
826 p_comp_cons_dels_inq_mode IN VARCHAR2,
827 x_delivery_status OUT NOCOPY VARCHAR2,
828 x_return_status OUT NOCOPY VARCHAR2,
829 x_msg_count OUT NOCOPY NUMBER,
830 x_msg_data OUT NOCOPY VARCHAR2,
831 x_lpn OUT NOCOPY VARCHAR2,
832 x_project OUT NOCOPY VARCHAR2,
833 x_task OUT NOCOPY VARCHAR2)
834
835 IS
836
837 CURSOR order_csr IS
838
839 SELECT DISTINCT
840 wdd2.source_header_number
841 FROM
842 wsh_delivery_assignments_v wda,
843 wsh_delivery_details wdd,
844 wsh_delivery_details wdd2,
845 wms_license_plate_numbers wlpn
846 WHERE wlpn.outermost_lpn_id = p_lpn_id
847 AND wlpn.organization_id = p_org_id
848 AND wlpn.lpn_context in ( 11,12)
849 AND wlpn.lpn_id = wdd.lpn_id
850 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
854 AND wdd2.released_status = 'Y';
851 AND wdd.organization_id = wlpn.organization_id
852 AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
853 AND wda.delivery_detail_id = wdd2.delivery_detail_id
855
856 CURSOR order_csr2 IS
857
858 SELECT DISTINCT
859 wdd2.source_header_number
860 FROM
861 wsh_delivery_assignments_v wda,
862 wsh_delivery_details wdd,
863 wsh_delivery_details wdd2,
864 wms_license_plate_numbers wlpn
865 WHERE wlpn.outermost_lpn_id = p_lpn_id
866 AND wlpn.organization_id = p_org_id
867 AND wlpn.lpn_context in ( 11,12)
868 AND wlpn.lpn_id = wdd.lpn_id
869 AND wdd.organization_id = wlpn.organization_id
870 AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
871 AND wda.delivery_detail_id = wdd2.delivery_detail_id
872 AND wdd2.released_status = 'Y'
873 AND wda.delivery_id = x_delivery_id;
874
875
876 l_count NUMBER;
877 l_order_num VARCHAR2(30);
878 l_loc_id NUMBER;
879
880 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
881 BEGIN
882
883 g_trace_on := fnd_profile.value('INV_DEBUG_TRACE') ;
884 x_return_status := FND_API.G_RET_STS_SUCCESS;
885 x_delivery_status := 'N';
886
887 IF p_inquiry_mode = 1 THEN
888
889 IF (g_trace_on = 1) THEN
890 mydebug('get_values_for_lpn : p_inquiry_mode = 1 ');
891 END IF;
892
893 BEGIN
894
895 SELECT DISTINCT
896 wda.delivery_id,
897 wlpno.license_plate_number,
898 wlpn.locator_id,
899 inv_projectlocator_pub.get_project_number(mil.project_id),
900 inv_projectlocator_pub.get_task_number(mil.task_id)
901 INTO
902 x_delivery_id,
903 x_lpn,
904 l_loc_id,
905 x_project,
906 x_task
907 FROM
908 wsh_delivery_assignments_v wda,
909 wsh_delivery_details wdd,
910 wms_license_plate_numbers wlpn,
911 mtl_item_locations mil,
912 wms_license_plate_numbers wlpno
913 WHERE wlpn.organization_id = p_org_id
914 AND wlpn.lpn_context in ( 11,12)
915 AND wlpn.outermost_lpn_id = p_lpn_id
916 AND wlpno.lpn_id = p_lpn_id -- bug 2764736
917 AND wlpn.lpn_id = wdd.lpn_id
918 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
919 AND wdd.organization_id = wlpn.organization_id
920 AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
921 AND wlpn.locator_id = mil.inventory_location_id
922 AND wlpn.organization_id = mil.organization_id
923 AND ROWNUM = 1;
924
925 EXCEPTION
926 WHEN no_data_found THEN
927 RAISE FND_API.g_exc_error;
928
929 END;
930
931 l_count := 0;
932 OPEN order_csr;
933 LOOP
934 FETCH order_csr INTO l_order_num;
935 EXIT WHEN order_csr%notfound;
936
937 l_count := l_count + 1;
938
939 END LOOP;
940 CLOSE order_csr;
941
942 IF l_count > 1 THEN
943 x_order_number := 'Multiple';
944 ELSE
945 x_order_number := l_order_num;
946 END IF;
947
948
949 ELSIF p_inquiry_mode = 2 THEN
950
951 IF (g_trace_on = 1) THEN
952 mydebug('get_values_for_lpn : p_inquiry_mode = 2 ');
953 END IF;
954
955 IF x_order_number IS NOT NULL THEN
956
957 IF x_delivery_id IS NULL OR x_delivery_id = 0 THEN
958
959 BEGIN
960
961 SELECT DISTINCT
962 wda.delivery_id,
963 wlpno.license_plate_number,
964 wlpn.locator_id,
965 wlpn.subinventory_code,
966 INV_PROJECT.GET_LOCSEGS(wlpn.locator_id, wlpn.organization_id) concatenated_segments,
967 inv_projectlocator_pub.get_project_number(mil.project_id),
968 inv_projectlocator_pub.get_task_number(mil.task_id)
969 INTO
970 x_delivery_id,
971 x_lpn,
972 l_loc_id,
973 x_sub,
974 x_loc,
975 x_project,
976 x_task
977 FROM
978 wms_license_plate_numbers wlpn,
979 wms_license_plate_numbers wlpno,
980 wsh_delivery_assignments_v wda,
981 wsh_delivery_details wdd,
982 wsh_delivery_details wdd2,
983 mtl_item_locations mil
984 WHERE wlpn.outermost_lpn_id = p_lpn_id
985 AND wlpno.lpn_id = p_lpn_id -- bug 2764736
986 AND wlpn.lpn_context in ( 11, 12)
987 AND wlpn.organization_id = p_org_id
988 AND wdd.source_header_number = x_order_number
989 AND wdd.released_status = 'Y'
990 AND wda.delivery_detail_id = wdd.delivery_detail_id
991 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
992 AND wlpn.lpn_id = wdd2.lpn_id
993 AND wlpn.locator_id = mil.inventory_location_id
994 AND wlpn.organization_id = mil.organization_id
995 AND ROWNUM = 1;
996
997 EXCEPTION
998 WHEN no_data_found THEN
999 RAISE FND_API.g_exc_error;
1000
1001 END;
1002
1003 ELSE
1004
1008 wlpn.license_plate_number,
1005 BEGIN
1006
1007 SELECT
1009 wlpn.locator_id,
1010 wlpn.subinventory_code,
1011 INV_PROJECT.GET_LOCSEGS(wlpn.locator_id, wlpn.organization_id) concatenated_segments,
1012 inv_projectlocator_pub.get_project_number(mil.project_id),
1013 inv_projectlocator_pub.get_task_number(mil.task_id)
1014
1015 INTO
1016 x_lpn,
1017 l_loc_id,
1018 x_sub,
1019 x_loc,
1020 x_project,
1021 x_task
1022 FROM wms_license_plate_numbers wlpn,
1023 mtl_item_locations mil
1024 WHERE wlpn.lpn_id = p_lpn_id
1025 AND wlpn.organization_id = p_org_id
1026 AND wlpn.locator_id = mil.inventory_location_id
1027 AND wlpn.organization_id = mil.organization_id
1028 AND wlpn.lpn_context in ( 11,12);
1029
1030 EXCEPTION
1031 WHEN no_data_found THEN
1032 RAISE FND_API.g_exc_error;
1033
1034 END;
1035
1036 END IF;
1037
1038 ELSIF x_delivery_id IS NOT NULL AND x_delivery_id <> 0 THEN
1039
1040 BEGIN
1041
1042 SELECT
1043 wlpn.license_plate_number,
1044 wlpn.locator_id,
1045 wlpn.subinventory_code,
1046 INV_PROJECT.GET_LOCSEGS(wlpn.locator_id, wlpn.organization_id) concatenated_segments,
1047 inv_projectlocator_pub.get_project_number(mil.project_id),
1048 inv_projectlocator_pub.get_task_number(mil.task_id)
1049
1050 INTO
1051 x_lpn,
1052 l_loc_id,
1053 x_sub,
1054 x_loc,
1055 x_project,
1056 x_task
1057 FROM wms_license_plate_numbers wlpn,
1058 mtl_item_locations mil
1059 WHERE wlpn.lpn_id = p_lpn_id
1060 AND wlpn.organization_id = p_org_id
1061 AND wlpn.locator_id = mil.inventory_location_id
1062 AND wlpn.organization_id = mil.organization_id
1063 AND wlpn.lpn_context in ( 11,12);
1064
1065 EXCEPTION
1066 WHEN no_data_found THEN
1067 RAISE FND_API.g_exc_error;
1068
1069 END;
1070
1071 l_count := 0;
1072 OPEN order_csr2;
1073 LOOP
1074 FETCH order_csr2 INTO l_order_num;
1075 EXIT WHEN order_csr2%notfound;
1076
1077 l_count := l_count + 1;
1078
1079 END LOOP;
1080 CLOSE order_csr2;
1081
1082 IF l_count > 1 THEN
1083 x_order_number := 'Multiple';
1084 ELSE
1085 x_order_number := l_order_num;
1086 END IF;
1087
1088 ELSE -- Only LPN was entered
1089
1090 BEGIN
1091
1092 SELECT DISTINCT
1093 wda.delivery_id,
1094 wlpno.license_plate_number,
1095 wlpn.locator_id,
1096 wlpn.subinventory_code,
1097 INV_PROJECT.GET_LOCSEGS(wlpn.locator_id, wlpn.organization_id) concatenated_segments,
1098 inv_projectlocator_pub.get_project_number(mil.project_id),
1099 inv_projectlocator_pub.get_task_number(mil.task_id)
1100
1101 INTO
1102 x_delivery_id,
1103 x_lpn,
1104 l_loc_id,
1105 x_sub,
1106 x_loc,
1107 x_project,
1108 x_task
1109 FROM
1110 wms_license_plate_numbers wlpn,
1111 wms_license_plate_numbers wlpno,
1112 wsh_delivery_assignments_v wda,
1113 wsh_delivery_details wdd,
1114 mtl_item_locations mil
1115 WHERE wlpn.outermost_lpn_id = p_lpn_id
1116 AND wlpno.lpn_id = p_lpn_id -- bug 2764736
1117 AND wlpn.lpn_context in ( 11,12)
1118 AND wlpn.organization_id = p_org_id
1119 AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
1120 AND wlpn.lpn_id = wdd.lpn_id
1121 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
1122 AND wlpn.locator_id = mil.inventory_location_id
1123 AND wlpn.organization_id = mil.organization_id
1124 AND ROWNUM = 1;
1125
1126 EXCEPTION
1127 WHEN no_data_found THEN
1128 RAISE FND_API.g_exc_error;
1129
1130 END;
1131
1132 l_count := 0;
1133
1134 OPEN order_csr;
1135 LOOP
1136 FETCH order_csr INTO l_order_num;
1137 EXIT WHEN order_csr%notfound;
1138
1139 l_count := l_count + 1;
1140
1141 END LOOP;
1142 CLOSE order_csr;
1143
1144 IF l_count > 1 THEN
1145 x_order_number := 'Multiple';
1146 ELSE
1147 x_order_number := l_order_num;
1148 END IF;
1149
1150 END IF;
1151
1152 END IF;
1153
1154
1155 IF wms_consolidation_pub.is_delivery_consolidated
1156 (p_delivery_id => x_delivery_id,
1157 p_org_id => p_org_id,
1158 p_sub => x_sub,
1159 p_loc_id => l_loc_id) = 'Y' THEN
1160
1161 x_delivery_status := 'Y';
1162
1163 END IF;
1164
1165 x_return_status := FND_API.G_RET_STS_SUCCESS;
1166
1167 EXCEPTION
1168
1169 WHEN FND_API.G_EXC_ERROR THEN
1170 x_return_status:=FND_API.G_RET_STS_ERROR;
1171 fnd_msg_pub.count_and_get
1172 ( p_count => x_msg_count
1173 , p_data => x_msg_data
1174 );
1175
1179 WHEN OTHERS THEN
1176 IF (g_trace_on = 1) THEN mydebug('get_values_for_lpn: Error in get_values_for_lpn API: ' || sqlerrm);
1177 END IF;
1178
1180
1181 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1182 fnd_msg_pub.count_and_get
1183 ( p_count => x_msg_count
1184 , p_data => x_msg_data
1185 );
1186
1187 IF (g_trace_on = 1) THEN mydebug('get_values_for_lpn: Unexpected Error in get_values_for_lpn API: ' || sqlerrm);
1188 END IF;
1189
1190 END get_values_for_lpn;
1191
1192
1193
1194 PROCEDURE get_consolidation_inq_lpn(x_loc IN OUT NOCOPY VARCHAR2,
1195 x_sub IN OUT NOCOPY VARCHAR2,
1196 p_org_id IN NUMBER,
1197 x_delivery_id IN OUT NOCOPY NUMBER,
1198 x_order_number IN OUT NOCOPY VARCHAR2,
1199 p_inquiry_mode IN NUMBER,
1200 p_comp_cons_dels_inq_mode IN VARCHAR2,
1201 x_lpn_vector OUT NOCOPY VARCHAR2,
1202 x_delivery_status OUT NOCOPY VARCHAR2,
1203 x_return_status OUT NOCOPY VARCHAR2,
1204 x_msg_count OUT NOCOPY NUMBER,
1205 x_msg_data OUT NOCOPY VARCHAR2,
1206 x_lpn IN OUT NOCOPY VARCHAR2,
1207 x_lpn_available OUT NOCOPY VARCHAR2,
1208 x_project OUT NOCOPY VARCHAR2,
1209 x_task OUT NOCOPY VARCHAR2)
1210 IS
1211
1212 CURSOR lpn_csr IS
1213
1214 SELECT DISTINCT
1215 wlpn.outermost_lpn_id
1216 FROM
1217 wms_license_plate_numbers wlpn,
1218 mtl_item_locations_kfv milk
1219 WHERE wlpn.lpn_context in (11,12)
1220 AND wlpn.subinventory_code = x_sub
1221 AND wlpn.organization_id = p_org_id
1222 AND milk.concatenated_segments LIKE (x_loc || '%')
1223 AND wlpn.locator_id = milk.inventory_location_id
1224 AND wlpn.subinventory_code = milk.subinventory_code
1225 AND milk.organization_id = wlpn.organization_id
1226 ORDER BY wlpn.outermost_lpn_id;
1227
1228
1229 CURSOR lpn_csr2 IS
1230
1231 SELECT DISTINCT wlpn.outermost_lpn_id
1232 FROM
1233 wsh_delivery_details wdd,
1234 wms_license_plate_numbers wlpn,
1235 wsh_delivery_assignments_v wda,
1236 wsh_delivery_details wdd2
1237 WHERE wdd.source_header_number = x_order_number
1238 AND wdd.organization_id = p_org_id
1239 AND wdd.released_status = 'Y'
1240 AND wdd.delivery_detail_id = wda.delivery_detail_id
1241 AND (Nvl(wda.delivery_id, -999) = Nvl(x_delivery_id, Nvl(wda.delivery_id, -999))
1242 OR
1243 x_delivery_id = 0)
1244 AND wdd2.delivery_detail_id = wda.parent_delivery_detail_id
1245 AND wdd2.lpn_id = wlpn.lpn_id
1246 AND wlpn.lpn_context in ( 11,12)
1247 AND wlpn.organization_id = wdd2.organization_id
1248 ORDER BY wlpn.outermost_lpn_id;
1249
1250
1251 CURSOR lpn_csr3 IS
1252
1253 SELECT DISTINCT wlpn.outermost_lpn_id
1254 FROM
1255 wsh_delivery_details wdd,
1256 wms_license_plate_numbers wlpn,
1257 wsh_delivery_assignments_v wda
1258 WHERE wda.delivery_id = x_delivery_id
1259 AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
1260 AND wdd.organization_id = p_org_id
1261 AND wdd.lpn_id = wlpn.lpn_id
1262 AND wlpn.lpn_context in (11,12)
1263 AND wlpn.organization_id = wdd.organization_id
1264 ORDER BY wlpn.outermost_lpn_id;
1265
1266
1267 l_lpn_id NUMBER;
1268 l_count NUMBER := 0;
1269 l_loc_id NUMBER;
1270 l_return_status VARCHAR2(30);
1271 l_lpn1_id NUMBER;
1272
1273 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1274 BEGIN
1275
1276 g_trace_on := fnd_profile.value('INV_DEBUG_TRACE') ;
1277 l_return_status := FND_API.G_RET_STS_SUCCESS;
1278 x_lpn_available := 'Y';
1279
1280
1281 IF p_inquiry_mode = 1 THEN l_count := 0;
1282 OPEN lpn_csr;
1283 LOOP
1284 FETCH lpn_csr INTO l_lpn_id;
1285 EXIT WHEN lpn_csr%notfound;
1286
1287 l_count := l_count + 1;
1288
1289 IF l_count = 1 THEN
1290 l_lpn1_id := l_lpn_id;
1291 END IF;
1292
1293 x_lpn_vector := x_lpn_vector || l_lpn_id || ':';
1294
1295 END LOOP;
1296 CLOSE lpn_csr;
1297
1298 ELSIF p_inquiry_mode = 2 THEN
1299
1300 IF x_lpn IS NOT NULL THEN
1301
1302 SELECT wlpn.lpn_id
1303 INTO l_lpn1_id
1304 FROM wms_license_plate_numbers wlpn
1305 WHERE wlpn.license_plate_number = x_lpn
1306 AND wlpn.lpn_context in ( 11,12)
1307 AND wlpn.organization_id = p_org_id;
1308
1309 l_count := 1;
1310 x_lpn_vector := x_lpn_vector || l_lpn1_id || ':';
1311
1312 ELSIF x_order_number IS NOT NULL THEN
1313
1314 l_count := 0;
1315 OPEN lpn_csr2;
1316 LOOP
1317 FETCH lpn_csr2 INTO l_lpn_id;
1318 EXIT WHEN lpn_csr2%notfound;
1319
1320 l_count := l_count + 1;
1321
1322 IF l_count = 1 THEN
1323 l_lpn1_id := l_lpn_id;
1324 END IF;
1325
1329 CLOSE lpn_csr2;
1326 x_lpn_vector := x_lpn_vector || l_lpn_id || ':';
1327
1328 END LOOP;
1330
1331 ELSIF x_delivery_id IS NOT NULL THEN
1332
1333 l_count := 0;
1334 OPEN lpn_csr3;
1335 LOOP
1336 FETCH lpn_csr3 INTO l_lpn_id;
1337 EXIT WHEN lpn_csr3%notfound;
1338
1339 l_count := l_count + 1;
1340
1341 IF l_count = 1 THEN
1342 l_lpn1_id := l_lpn_id;
1343 END IF;
1344
1345 x_lpn_vector := x_lpn_vector || l_lpn_id || ':';
1346
1347 END LOOP;
1348 CLOSE lpn_csr3;
1349
1350 END IF;
1351
1352 END IF;
1353
1354 IF l_count = 0 THEN
1355
1356 x_lpn_available := 'N';
1357 x_return_status := l_return_status;
1358 RETURN;
1359
1360 END IF;
1361
1362 IF (g_trace_on = 1) THEN mydebug('get_consolidation_inq_lpn : x_lpn_vector ->' || x_lpn_vector);
1363 END IF;
1364
1365 wms_consolidation_pub.get_values_for_lpn
1366 (p_lpn_id => l_lpn1_id,
1367 p_org_id => p_org_id,
1368 x_sub => x_sub,
1369 x_loc => x_loc,
1370 x_delivery_id => x_delivery_id,
1371 x_order_number => x_order_number,
1372 p_inquiry_mode => p_inquiry_mode,
1373 p_comp_cons_dels_inq_mode => p_comp_cons_dels_inq_mode,
1374 x_delivery_status => x_delivery_status,
1375 x_return_status => l_return_status,
1376 x_msg_count => x_msg_count,
1377 x_msg_data => x_msg_data,
1378 x_lpn => x_lpn,
1379 x_project => x_project,
1380 x_task => x_task);
1381
1382 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1383
1384 RAISE FND_API.G_exc_unexpected_error;
1385
1386 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1387
1388 RAISE FND_API.g_exc_error;
1389
1390 END IF;
1391
1392
1393 x_return_status := l_return_status;
1394
1395 EXCEPTION
1396
1397 WHEN FND_API.G_EXC_ERROR THEN
1398 x_return_status:=FND_API.G_RET_STS_ERROR;
1399 fnd_msg_pub.count_and_get
1400 ( p_count => x_msg_count
1401 , p_data => x_msg_data
1402 );
1403
1404 IF (g_trace_on = 1) THEN mydebug('get_consolidation_inq_lpn: Error in get_consolidation_inq_lpn API: ' || sqlerrm);
1405 END IF;
1406
1407 WHEN OTHERS THEN
1408
1409 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1410 fnd_msg_pub.count_and_get
1411 ( p_count => x_msg_count
1412 , p_data => x_msg_data
1413 );
1414
1415 IF (g_trace_on = 1) THEN mydebug('get_consolidation_inq_lpn: Unexpected Error in get_consolidation_inq_lpn API: ' || sqlerrm);
1416 END IF;
1417
1418 END get_consolidation_inq_lpn;
1419
1420
1421
1422 PROCEDURE get_query_by_del_lpn(x_delivery_id IN OUT NOCOPY NUMBER,
1423 p_org_id IN NUMBER,
1424 x_order_number OUT NOCOPY VARCHAR2,
1425 x_loc OUT NOCOPY VARCHAR2,
1426 x_sub OUT NOCOPY VARCHAR2,
1427 x_lpn_vector OUT NOCOPY VARCHAR2,
1428 x_delivery_status OUT NOCOPY VARCHAR2,
1429 x_return_status OUT NOCOPY VARCHAR2,
1430 x_msg_count OUT NOCOPY NUMBER,
1431 x_msg_data OUT NOCOPY VARCHAR2,
1432 x_lpn OUT NOCOPY VARCHAR2,
1433 x_lpn_available OUT NOCOPY VARCHAR2,
1434 x_tot_lines_for_del OUT NOCOPY NUMBER,
1435 x_tot_comp_lines_for_del OUT NOCOPY NUMBER,
1436 x_tot_locs_for_del OUT NOCOPY NUMBER,
1437 x_project OUT NOCOPY VARCHAR2,
1438 x_task OUT NOCOPY VARCHAR2)
1439
1440 IS
1441
1442 CURSOR lpn_csr IS
1443
1444 SELECT DISTINCT wlpn.outermost_lpn_id
1445 FROM
1446 wsh_delivery_details wdd,
1447 wms_license_plate_numbers wlpn,
1448 wsh_delivery_assignments_v wda
1449 WHERE wda.delivery_id = x_delivery_id
1450 AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
1451 AND wdd.organization_id = p_org_id
1452 AND wdd.lpn_id = wlpn.lpn_id
1453 AND wlpn.lpn_context in ( 11,12)
1454 AND wlpn.organization_id = wdd.organization_id
1455 ORDER BY wlpn.outermost_lpn_id;
1456
1457
1458 l_lpn_id NUMBER;
1459 l_count NUMBER;
1460 l_loc_id NUMBER;
1461 l_return_status VARCHAR2(30);
1462 l_lpn1_id NUMBER;
1463
1464 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1465 BEGIN
1466
1467 g_trace_on := fnd_profile.value('INV_DEBUG_TRACE') ;
1468 l_return_status := FND_API.G_RET_STS_SUCCESS;
1469 x_lpn_available := 'Y';
1470
1471
1472 IF (l_debug = 1) THEN
1473 mydebug('Enter get_query_by_del_lpn : x_delivery_id = ' || x_delivery_id);
1474 END IF;
1475
1476 SELECT COUNT(DISTINCT Nvl(mil.physical_location_id,
1480 wsh_delivery_details wdd,
1477 mil.inventory_location_id))
1478 INTO x_tot_locs_for_del
1479 FROM mtl_item_locations mil,
1481 wsh_delivery_assignments_v wda
1482 WHERE wda.delivery_id = x_delivery_id
1483 AND wda.delivery_detail_id = wdd.delivery_detail_id
1484 AND wdd.locator_id = mil.inventory_location_id
1485 AND wdd.organization_id = mil.organization_id
1486 AND wdd.released_status = 'Y'
1487 ;
1488
1489 IF (l_debug = 1) THEN
1490 mydebug('get_query_by_del_lpn : x_tot_locs_for_del = ' || x_tot_locs_for_del);
1491 END IF;
1492
1493
1494 l_count := 0;
1495 OPEN lpn_csr;
1496 LOOP
1497 FETCH lpn_csr INTO l_lpn_id;
1498 EXIT WHEN lpn_csr%notfound;
1499
1500 l_count := l_count + 1;
1501
1502 IF l_count = 1 THEN
1503 l_lpn1_id := l_lpn_id;
1504 END IF;
1505
1506 x_lpn_vector := x_lpn_vector || l_lpn_id || ':';
1507
1508 END LOOP;
1509 CLOSE lpn_csr;
1510
1511 IF l_count = 0 THEN
1512
1513 x_lpn_available := 'N';
1514 x_return_status := l_return_status;
1515 RETURN;
1516
1517 END IF;
1518
1519 IF (g_trace_on = 1) THEN mydebug('get_query_by_del_lpn : x_lpn_vector ->' || x_lpn_vector);
1520 END IF;
1521
1522 wms_consolidation_pub.get_values_for_lpn
1523 (p_lpn_id => l_lpn1_id,
1524 p_org_id => p_org_id,
1525 x_sub => x_sub,
1526 x_loc => x_loc,
1527 x_delivery_id => x_delivery_id,
1528 x_order_number => x_order_number,
1529 p_inquiry_mode => 2,
1530 p_comp_cons_dels_inq_mode => 'ALL',
1531 x_delivery_status => x_delivery_status,
1532 x_return_status => l_return_status,
1533 x_msg_count => x_msg_count,
1534 x_msg_data => x_msg_data,
1535 x_lpn => x_lpn,
1536 x_project => x_project,
1537 x_task => x_task);
1538
1539 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1540
1541 RAISE FND_API.G_exc_unexpected_error;
1542
1543 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1544
1545 RAISE FND_API.g_exc_error;
1546
1547 END IF;
1548
1549 SELECT COUNT(wdd.delivery_detail_id)
1550 INTO x_tot_lines_for_del
1551 FROM
1552 wsh_delivery_details wdd,
1553 wsh_delivery_assignments_v wda
1554 WHERE wdd.organization_id = p_org_id
1555 AND wdd.delivery_detail_id = wda.delivery_detail_id
1556 AND wda.delivery_id = x_delivery_id
1557 AND wdd.lpn_id IS NULL;
1558
1559 SELECT COUNT(wdd.delivery_detail_id)
1560 INTO x_tot_comp_lines_for_del
1561 FROM
1562 wsh_delivery_details wdd,
1563 wsh_delivery_assignments_v wda
1564 WHERE wdd.organization_id = p_org_id
1565 AND wdd.released_status = 'Y'
1566 AND wdd.delivery_detail_id = wda.delivery_detail_id
1567 AND wda.delivery_id = x_delivery_id
1568 AND wdd.lpn_id IS NULL;
1569
1570 x_return_status := l_return_status;
1571
1572 EXCEPTION
1573
1574 WHEN FND_API.G_EXC_ERROR THEN
1575 x_return_status:=FND_API.G_RET_STS_ERROR;
1576 fnd_msg_pub.count_and_get
1577 ( p_count => x_msg_count
1578 , p_data => x_msg_data
1579 );
1580
1581 IF (g_trace_on = 1) THEN mydebug('get_query_by_del_lpn: Error in get_query_by_del_lpn API: ' || sqlerrm);
1582 END IF;
1583
1584 WHEN OTHERS THEN
1585
1586 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1587 fnd_msg_pub.count_and_get
1588 ( p_count => x_msg_count
1589 , p_data => x_msg_data
1590 );
1591
1592 IF (g_trace_on = 1) THEN mydebug('get_query_by_del_lpn: Unexpected Error in get_query_by_del_lpn API: ' || sqlerrm);
1593 END IF;
1594
1595 END get_query_by_del_lpn;
1596
1597
1598 PROCEDURE lpn_mass_move (p_org_id IN NUMBER,
1599 p_from_sub IN VARCHAR2,
1600 p_from_loc_id IN NUMBER,
1601 p_to_sub IN VARCHAR2,
1602 p_to_loc_id IN NUMBER,
1603 p_to_loc_type IN NUMBER,
1604 p_transfer_lpn_id IN NUMBER, -- = 0 when TOLPN is not input on the page
1605 x_return_status OUT NOCOPY VARCHAR2,
1606 x_msg_count OUT NOCOPY NUMBER,
1607 x_msg_data OUT NOCOPY VARCHAR2)
1608
1609 IS
1610
1611 CURSOR lpn_csr
1612 IS
1613 SELECT wlpn.lpn_id
1614 FROM wms_license_plate_numbers wlpn
1615 WHERE wlpn.lpn_context = 11
1616 AND wlpn.subinventory_code = p_from_sub
1617 AND wlpn.locator_id = p_from_loc_id
1618 AND wlpn.organization_id = p_org_id
1619 AND wlpn.lpn_id = wlpn.outermost_lpn_id
1620 AND wlpn.parent_lpn_id IS NULL;
1621
1622 l_temp_id NUMBER;
1623 l_lpn_id NUMBER;
1624 l_return NUMBER;
1625 l_hdr_id NUMBER;
1629 p_user_id NUMBER;
1626 l_period_id NUMBER;
1627 l_open_past_period BOOLEAN;
1628 l_item_id NUMBER;
1630 l_lpns wms_mdc_pvt.number_table_type;
1631 l_deliveries wms_mdc_pvt.number_table_type;
1632 l_allow_packing VARCHAR2(1);
1633 i NUMBER := 1;
1634 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1635 l_to_loc_type NUMBER;
1636 BEGIN
1637 p_user_id := fnd_global.user_id;
1638 SAVEPOINT sp_lpn_mass_move;
1639 g_trace_on := fnd_profile.value('INV_DEBUG_TRACE') ;
1640
1641 IF (g_trace_on = 1) THEN mydebug('Entered lpn_mass_move: '); END IF;
1642
1643 invttmtx.tdatechk(org_id => p_org_id,
1644 transaction_date => sysdate,
1645 period_id => l_period_id,
1646 open_past_period => l_open_past_period);
1647
1648 IF l_period_id = -1 THEN
1649
1650 IF (g_trace_on = 1) THEN mydebug('lpn_mass_move: Period is invalid');
1651 END IF;
1652
1653 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
1654 FND_MSG_PUB.ADD;
1655 RAISE FND_API.G_exc_unexpected_error;
1656
1657 END IF;
1658
1659 FOR lpn_rec IN lpn_csr LOOP
1660 l_lpns(i) := lpn_rec.lpn_id;
1661 i := i + 1;
1662 END LOOP;
1663
1664 IF (g_trace_on = 1) THEN mydebug('l_lpns.count ' || l_lpns.count ); END IF;
1665
1666 -- MR: IF p_transfer_lpn_id IS NOT NULL AND p_transfer_lpn_id <> 0 THEN
1667 -- Commented the above since we want to validate from LPN even when TO_LPN is
1668 -- not provided, so that LPN mass move without MDC does not move CONSOL LPNs to any
1669 -- other locator
1670
1671 -- Call the validation API to check if the from lpns can be dropped into TO lpn
1672 wms_mdc_pvt.validate_to_lpn(p_from_lpn_ids => l_lpns,
1673 p_from_delivery_ids => l_deliveries,
1674 p_to_lpn_id => p_transfer_lpn_id,
1675 p_to_sub => p_to_sub,
1676 p_to_locator_id => p_to_loc_id,
1677 x_allow_packing => l_allow_packing,
1678 x_return_status => x_return_status,
1679 x_msg_count => x_msg_count,
1680 x_msg_data => x_msg_data);
1681
1682 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1683 IF g_trace_on = 1 THEN
1684 mydebug('lpn_mass_move: Error from wms_mdc_pvt.validate_to_lpn: ' || x_msg_data);
1685 END IF;
1686 RAISE fnd_api.g_exc_error;
1687 ELSE
1688 IF g_trace_on = 1 THEN
1689 mydebug('lpn_mass_move: wms_mdc_pvt.validate_to_lpn returned: ' || l_allow_packing);
1690 END IF;
1691
1692 IF l_allow_packing = 'N' THEN
1693 RAISE fnd_api.g_exc_error;
1694 END IF;
1695
1696 END IF;
1697 -- MR: END IF; -- if p_transfer_lpn_id is not null
1698
1699
1700 IF l_allow_packing = 'C' THEN -- one of the from LPNs is a consol LPN
1701 IF p_transfer_lpn_id IS NULL OR p_transfer_lpn_id = 0 THEN
1702 BEGIN
1703 SELECT mil.inventory_location_type
1704 INTO l_to_loc_type
1705 FROM mtl_item_locations mil
1706 WHERE mil.organization_id = p_org_id
1707 AND mil.subinventory_code = p_to_sub
1708 AND mil.inventory_location_id = p_to_loc_id;
1709 IF (g_trace_on = 1) THEN mydebug('l_to_loc_type' || l_to_loc_type ); END IF;
1710 EXCEPTION WHEN NO_DATA_FOUND THEN
1711 IF (g_trace_on = 1) THEN mydebug('exception selecting to_loc_type' ); END IF;
1712 RAISE FND_API.G_exc_unexpected_error;
1713 END ;
1714 IF l_to_loc_type <> g_loc_type_staging_lane THEN
1715 fnd_message.set_name('WMS', 'WMS_STAGE_FROM_CONSOL_LPN'); -- mrana :addmsg
1716 fnd_msg_pub.ADD;
1717 IF g_trace_on = 1 THEN
1718 mydebug('WMS_STAGE_FROM_CONSOL_LPN : Destination Locator must be staging locator when one of' ||
1719 ' the From LPNs is a consol LPN : ' );
1720 -- {{- Destination Locator must be staging locator when one of the From LPNs is a consol LPN }}
1721
1722 END IF;
1723 RAISE fnd_api.g_exc_error;
1724 END IF ;
1725 END IF ;
1726 END IF;
1727
1728 SELECT mtl_material_transactions_s.NEXTVAL INTO l_hdr_id FROM dual;
1729
1730 IF (g_trace_on = 1) THEN mydebug('l_hdr_id ' || l_hdr_id ); END IF;
1731
1732 OPEN lpn_csr;
1733 LOOP
1734 FETCH lpn_csr INTO l_lpn_id;
1735 EXIT WHEN lpn_csr%notfound;
1736
1737 IF (g_trace_on = 1) THEN mydebug('lpn_mass_move: l_lpn_id : ' || l_lpn_id); END IF;
1738
1739
1740 IF inv_ui_item_sub_loc_lovs.vaildate_lpn_status
1741 (p_lpn_id => l_lpn_id,
1742 p_orgid => p_org_id,
1743 p_to_org_id => p_org_id,
1744 p_wms_installed => 'TRUE',
1745 p_transaction_type_id => 2) = 'N'
1746 OR
1747 inv_txn_validations.check_lpn_allocation
1751 OR
1748 (p_lpn_id => l_lpn_id,
1749 p_org_id => p_org_id,
1750 x_return_msg => x_msg_data) = 'N'
1752 inv_txn_validations.check_lpn_serial_allocation
1753 (p_lpn_id => l_lpn_id,
1754 p_org_id => p_org_id,
1755 x_return_msg => x_msg_data) = 'N' THEN
1756
1757 FND_MESSAGE.SET_NAME('WMS', x_msg_data);
1758 FND_MSG_PUB.ADD;
1759 RAISE FND_API.G_exc_unexpected_error;
1760 END IF;
1761
1762 IF (g_trace_on = 1) THEN mydebug('after pn validations ...:: ' ); END IF;
1763 SELECT wlc.inventory_item_id
1764 INTO l_item_id
1765 FROM
1766 wms_lpn_contents wlc,
1767 wms_license_plate_numbers wlpn
1768 WHERE wlc.parent_lpn_id = wlpn.lpn_id
1769 AND wlc.organization_id = wlpn.organization_id
1770 AND wlpn.outermost_lpn_id = l_lpn_id
1771 AND wlpn.organization_id = p_org_id
1772 AND ROWNUM = 1;
1773 IF (g_trace_on = 1) THEN mydebug('l_item_id ' || l_item_id); END IF;
1774
1775
1776 INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP
1777 (TRANSACTION_HEADER_ID,
1778 TRANSACTION_TEMP_ID,
1779 PROCESS_FLAG,
1780 transaction_status,
1781 CREATION_DATE,
1782 CREATED_BY,
1783 LAST_UPDATE_DATE,
1784 LAST_UPDATED_BY,
1785 LAST_UPDATE_LOGIN,
1786 INVENTORY_ITEM_ID,
1787 ORGANIZATION_ID,
1788 SUBINVENTORY_CODE,
1789 LOCATOR_ID,
1790 TRANSFER_SUBINVENTORY ,
1791 TRANSFER_TO_LOCATION,
1792 TRANSACTION_QUANTITY,
1793 PRIMARY_QUANTITY,
1794 TRANSACTION_UOM,
1795 TRANSACTION_TYPE_ID,
1796 TRANSACTION_ACTION_ID,
1797 TRANSACTION_SOURCE_TYPE_ID,
1798 TRANSACTION_DATE,
1799 acct_period_id,
1800 CONTENT_LPN_ID,
1801 transfer_lpn_id,
1802 posting_flag,
1803 wms_task_type) -- bug 2879208
1804 VALUES
1805 (l_hdr_id,
1806 mtl_material_transactions_s.NEXTVAL,
1807 'Y',
1808 3,
1809 sysdate,
1810 p_user_id,
1811 sysdate,
1812 p_user_id,
1813 p_user_id,
1814 l_item_id,-- inventory item id
1815 p_org_id,
1816 p_from_sub,
1817 p_from_loc_id,
1818 p_to_sub,
1819 p_to_loc_id,
1820 1,--trx qty
1821 1, --prim qty
1822 'X',--uom
1823 2,-- p_trx_type_id,
1824 2,-- p_trx_action_id,
1825 13,-- p_trx_src_type_id,
1826 sysdate, --tran date
1827 l_period_id,
1828 l_lpn_id,--content lpn id
1829 p_transfer_lpn_id, -- transfer lpn id
1830 'Y',
1831 7) -- bug 2879208
1832 returning transaction_temp_id INTO l_temp_id;
1833 l_return:=0;
1834 IF (g_trace_on = 1) THEN mydebug('lpn_mass_move: transaction_temp_id just inserted: ' || l_temp_id);
1835 END IF;
1836
1837 IF l_return <> 0 THEN
1838
1839 ROLLBACK TO sp_lpn_mass_move;
1840 RAISE FND_API.G_exc_unexpected_error;
1841
1842 END IF;
1843
1844 END LOOP;
1845 CLOSE lpn_csr;
1846 IF (l_debug = 1) THEN
1847 mydebug('lpn_mass_move:before calling TM for header : ' || l_hdr_id);
1848 END IF;
1849 l_return :=inv_lpn_trx_pub.process_lpn_trx
1850 (p_trx_hdr_id => l_hdr_id,
1851 p_commit => fnd_api.g_false,
1852 x_proc_msg => x_msg_data);
1853
1854 IF (l_debug = 1) THEN mydebug('l_return: ' || l_return); END IF;
1855 IF l_return <> 0 THEN
1856 ROLLBACK TO sp_lpn_mass_move;
1857 FND_MESSAGE.SET_NAME('WMS','WMS_TD_TXNMGR_ERROR' );
1858 FND_MSG_PUB.ADD;
1859 RAISE FND_API.g_exc_unexpected_error;
1860 END IF;
1861
1862 COMMIT;
1863 x_return_status := FND_API.G_RET_STS_SUCCESS;
1864 IF (l_debug = 1) THEN mydebug('exit lpn_mass_move : ' || x_return_status); END IF;
1865
1866 EXCEPTION
1867
1868 WHEN FND_API.G_EXC_ERROR THEN
1869 ROLLBACK TO sp_lpn_mass_move;
1870 x_return_status:=FND_API.G_RET_STS_ERROR;
1871 fnd_msg_pub.count_and_get
1872 ( p_count => x_msg_count
1873 , p_data => x_msg_data
1874 );
1875
1876 IF (g_trace_on = 1) THEN mydebug('lpn_mass_move: Expected Error in lpn_mass_move API: ' || sqlerrm);
1877 END IF;
1878
1879 WHEN fnd_api.g_exc_unexpected_error THEN
1880 x_return_status := fnd_api.g_ret_sts_unexp_error;
1881 fnd_msg_pub.count_and_get(p_count => x_msg_count, p_data => x_msg_data);
1882 IF (g_trace_on = 1) THEN mydebug('Unexpected ROLLBACK ' ); END IF;
1883 ROLLBACK TO sp_lpn_mass_move;
1884
1885 WHEN OTHERS THEN
1886 ROLLBACK TO sp_lpn_mass_move;
1887 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
1888 fnd_msg_pub.count_and_get
1889 ( p_count => x_msg_count
1890 , p_data => x_msg_data
1891 );
1892
1893 IF (g_trace_on = 1) THEN mydebug('lpn_mass_move: Other Unexpected Error in lpn_mass_move API: ' || sqlerrm);
1894 END IF;
1895
1896 END lpn_mass_move;
1897
1898
1902
1899 PROCEDURE get_lpn_mass_move_sub_lov(x_sub_lov OUT NOCOPY t_genref,
1900 p_sub IN VARCHAR2,
1901 p_org_id IN NUMBER)
1903 IS
1904
1905 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1906 BEGIN
1907
1908 OPEN x_sub_lov FOR
1909
1910 SELECT DISTINCT
1911 msi.secondary_inventory_name,
1912 Nvl(msi.locator_type, 1),
1913 msi.description,
1914 msi.asset_inventory,
1915 msi.picking_order,
1916 msi.enable_locator_alias
1917 FROM
1918 mtl_secondary_inventories msi,
1919 mtl_item_locations mil
1920 WHERE msi.organization_id = p_org_id
1921 AND msi.lpn_controlled_flag = 1
1922 AND msi.secondary_inventory_name LIKE p_sub
1923 AND msi.secondary_inventory_name = mil.subinventory_code
1924 AND mil.organization_id = msi.organization_id
1925 AND Nvl(mil.inventory_location_type, 3) IN (g_loc_type_consolidation,
1926 g_loc_type_packing_station,
1927 g_loc_type_staging_lane)
1928 AND inv_material_status_grp.is_status_applicable('Y',
1929 NULL,
1930 2,
1931 NULL,
1932 NULL,
1933 p_org_id,
1934 NULL,
1935 msi.secondary_inventory_name,
1936 NULL,
1937 NULL,
1938 NULL,
1939 'Z') = 'Y'
1940 ORDER BY msi.picking_order;
1941
1942 END get_lpn_mass_move_sub_lov;
1943
1944
1945 PROCEDURE get_lpn_mass_move_locs_lov(x_loc_lov OUT NOCOPY t_genref,
1946 p_org_id IN NUMBER,
1947 p_sub IN VARCHAR2,
1948 p_loc IN VARCHAR2,
1949 p_from_sub IN VARCHAR2,
1950 p_from_loc IN VARCHAR2,
1951 p_alias IN VARCHAR2)
1952
1953 IS
1954
1955 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1956 BEGIN
1957
1958 IF p_alias IS NULL THEN
1959 get_lpn_mass_move_locs_lov(
1960 x_loc_lov => x_loc_lov
1961 ,p_org_id => p_org_id
1962 ,p_sub => p_sub
1963 ,p_loc => p_loc
1964 ,p_from_sub => p_from_sub
1965 ,p_from_loc => p_from_loc
1966 );
1967 RETURN;
1968 END IF;
1969 IF p_from_sub IS NULL THEN
1970
1971 OPEN x_loc_lov FOR
1972
1973 SELECT DISTINCT
1974 milk.inventory_location_id,
1975 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
1976 milk.description,
1977 ml.meaning,
1978 mil.picking_order
1979 FROM
1980 mtl_item_locations_kfv milk,
1981 mtl_item_locations mil,
1982 mfg_lookups ml,
1983 wms_license_plate_numbers wlpn
1984 WHERE milk.organization_id = p_org_id
1985 AND milk.alias = p_alias
1986 AND milk.subinventory_code = p_sub
1987 AND milk.project_id IS NULL
1988 AND milk.task_id IS NULL
1989 AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
1990 g_loc_type_packing_station,
1991 g_loc_type_staging_lane)
1992 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
1993 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
1994 AND wlpn.lpn_context = 11
1995 AND wlpn.organization_id = mil.organization_id
1996 AND wlpn.subinventory_code = mil.subinventory_code
1997 AND wlpn.locator_id = mil.inventory_location_id
1998 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
1999 AND milk.organization_id = mil.organization_id
2000 AND NOT exists
2001 (
2002 SELECT 1
2003 FROM
2004 wms_license_plate_numbers wlpn2
2005 WHERE wlpn2.lpn_context <> 11
2006 AND wlpn2.organization_id = mil.organization_id
2007 AND wlpn2.subinventory_code = mil.subinventory_code
2008 AND wlpn2.locator_id = mil.inventory_location_id
2009 )
2010 AND NOT exists
2011 (
2012 SELECT 1
2013 FROM mtl_onhand_quantities_detail moqd
2014 WHERE moqd.primary_transaction_quantity > 0
2015 AND moqd.locator_id = mil.inventory_location_id
2016 AND moqd.organization_id = mil.organization_id
2017 AND moqd.lpn_id IS NULL
2018 )
2019 AND inv_material_status_grp.is_status_applicable('Y',
2020 NULL,
2021 2,
2022 NULL,
2023 NULL,
2024 p_org_id,
2025 NULL,
2026 milk.subinventory_code,
2027 milk.inventory_location_id,
2028 NULL,
2029 NULL,
2030 'L') = 'Y'
2031 ORDER BY ml.meaning, mil.picking_order;
2032
2033
2034 ELSE
2035
2036 OPEN x_loc_lov FOR
2037
2038 SELECT DISTINCT
2039 milk.inventory_location_id,
2040 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2041 milk.description,
2042 ml.meaning,
2043 mil.picking_order
2044 FROM
2045 mtl_item_locations_kfv milk,
2046 mtl_item_locations mil,
2047 mfg_lookups ml
2048 WHERE milk.organization_id = p_org_id
2049 AND milk.alias = p_alias
2050 AND milk.subinventory_code = p_sub
2054 AND milk.task_id IS NULL
2051 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
2052 AND milk.organization_id = mil.organization_id
2053 AND milk.project_id IS NULL
2055 AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
2056 g_loc_type_packing_station,
2057 g_loc_type_staging_lane)
2058 AND NOT (milk.subinventory_code = p_from_sub
2059 AND
2060 milk.concatenated_segments LIKE p_from_loc ||'%')
2061 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
2062 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
2063 AND NOT exists
2064 (
2065 SELECT 1
2066 FROM
2067 wms_license_plate_numbers wlpn2
2068 WHERE wlpn2.lpn_context <> 11
2069 AND wlpn2.organization_id = mil.organization_id
2070 AND wlpn2.subinventory_code = mil.subinventory_code
2071 AND wlpn2.locator_id = mil.inventory_location_id
2072 )
2073 AND inv_material_status_grp.is_status_applicable('Y',
2074 NULL,
2075 2,
2076 NULL,
2077 NULL,
2078 p_org_id,
2079 NULL,
2080 milk.subinventory_code,
2081 milk.inventory_location_id,
2082 NULL,
2083 NULL,
2084 'L') = 'Y'
2085 ORDER BY ml.meaning, mil.picking_order;
2086
2087 END IF;
2088
2089 END get_lpn_mass_move_locs_lov;
2090
2091 PROCEDURE get_lpn_mass_move_locs_lov(x_loc_lov OUT NOCOPY t_genref,
2092 p_org_id IN NUMBER,
2093 p_sub IN VARCHAR2,
2094 p_loc IN VARCHAR2,
2095 p_from_sub IN VARCHAR2,
2096 p_from_loc IN VARCHAR2)
2097
2098 IS
2099
2100 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2101 BEGIN
2102
2103 IF p_from_sub IS NULL THEN
2104
2105 OPEN x_loc_lov FOR
2106
2107 SELECT DISTINCT
2108 milk.inventory_location_id,
2109 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2110 milk.description,
2111 ml.meaning,
2112 mil.picking_order
2113 FROM
2114 mtl_item_locations_kfv milk,
2115 mtl_item_locations mil,
2116 mfg_lookups ml,
2117 wms_license_plate_numbers wlpn
2118 WHERE milk.organization_id = p_org_id
2119 AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc
2120 AND milk.subinventory_code = p_sub
2121 AND milk.project_id IS NULL
2122 AND milk.task_id IS NULL
2123 AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
2124 g_loc_type_packing_station,
2125 g_loc_type_staging_lane)
2126 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
2127 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
2128 AND wlpn.lpn_context = 11
2129 AND wlpn.organization_id = mil.organization_id
2130 AND wlpn.subinventory_code = mil.subinventory_code
2131 AND wlpn.locator_id = mil.inventory_location_id
2132 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
2133 AND milk.organization_id = mil.organization_id
2134 AND NOT exists
2135 (
2136 SELECT 1
2137 FROM
2138 wms_license_plate_numbers wlpn2
2139 WHERE wlpn2.lpn_context <> 11
2140 AND wlpn2.organization_id = mil.organization_id
2141 AND wlpn2.subinventory_code = mil.subinventory_code
2142 AND wlpn2.locator_id = mil.inventory_location_id
2143 )
2144 AND NOT exists
2145 (
2146 SELECT 1
2147 FROM mtl_onhand_quantities_detail moqd
2148 WHERE moqd.primary_transaction_quantity > 0
2149 AND moqd.locator_id = mil.inventory_location_id
2150 AND moqd.organization_id = mil.organization_id
2151 AND moqd.lpn_id IS NULL
2152 )
2153 AND inv_material_status_grp.is_status_applicable('Y',
2154 NULL,
2155 2,
2156 NULL,
2157 NULL,
2158 p_org_id,
2159 NULL,
2160 milk.subinventory_code,
2161 milk.inventory_location_id,
2162 NULL,
2163 NULL,
2164 'L') = 'Y'
2165 ORDER BY ml.meaning, mil.picking_order;
2166
2167
2168 ELSE
2169
2170 OPEN x_loc_lov FOR
2171
2172 SELECT DISTINCT
2173 milk.inventory_location_id,
2174 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2175 milk.description,
2176 ml.meaning,
2177 mil.picking_order
2178 FROM
2179 mtl_item_locations_kfv milk,
2180 mtl_item_locations mil,
2181 mfg_lookups ml
2182 WHERE milk.organization_id = p_org_id
2183 AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc
2184 AND milk.subinventory_code = p_sub
2185 AND milk.inventory_location_id = Nvl(mil.physical_location_id, mil.inventory_location_id)
2186 AND milk.organization_id = mil.organization_id
2187 AND milk.project_id IS NULL
2188 AND milk.task_id IS NULL
2189 AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
2190 g_loc_type_packing_station,
2194 milk.concatenated_segments LIKE p_from_loc ||'%')
2191 g_loc_type_staging_lane)
2192 AND NOT (milk.subinventory_code = p_from_sub
2193 AND
2195 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
2196 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
2197 AND NOT exists
2198 (
2199 SELECT 1
2200 FROM
2201 wms_license_plate_numbers wlpn2
2202 WHERE wlpn2.lpn_context <> 11
2203 AND wlpn2.organization_id = mil.organization_id
2204 AND wlpn2.subinventory_code = mil.subinventory_code
2205 AND wlpn2.locator_id = mil.inventory_location_id
2206 )
2207 AND inv_material_status_grp.is_status_applicable('Y',
2208 NULL,
2209 2,
2210 NULL,
2211 NULL,
2212 p_org_id,
2213 NULL,
2214 milk.subinventory_code,
2215 milk.inventory_location_id,
2216 NULL,
2217 NULL,
2218 'L') = 'Y'
2219 ORDER BY ml.meaning, mil.picking_order;
2220
2221 END IF;
2222
2223 END get_lpn_mass_move_locs_lov;
2224
2225
2226 PROCEDURE get_lpn_mass_move_lpn_lov(x_lpn_lov OUT NOCOPY t_genref,
2227 p_org_id IN NUMBER,
2228 p_lpn IN VARCHAR2,
2229 p_from_loc_id IN VARCHAR2) IS
2230 BEGIN
2231 OPEN x_lpn_lov FOR
2232 SELECT lpn_id, license_plate_number, inventory_location_id,
2233 concatenated_segments, subinventory_code, lpn_context
2234 FROM (
2235 SELECT DISTINCT
2236 wlpn.lpn_id,
2237 wlpn.license_plate_number,
2238 milk.inventory_location_id,
2239 inv_project.get_locsegs(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2240 milk.subinventory_code,
2241 11 lpn_context
2242 FROM
2243 wms_license_plate_numbers wlpn,
2244 mtl_item_locations_kfv milk
2245 WHERE wlpn.license_plate_number LIKE p_lpn
2246 AND wlpn.locator_id <> p_from_loc_id -- Make sure that we don't choose the TO lpn FROM the FROM locator
2247 AND milk.organization_id = p_org_id
2248 AND milk.project_id IS NULL
2249 AND milk.task_id IS NULL
2250 AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
2251 g_loc_type_packing_station,
2252 g_loc_type_staging_lane)
2253 AND wlpn.lpn_context = 11 -- Picked
2254 AND wlpn.organization_id = milk.organization_id
2255 AND wlpn.locator_id = milk.inventory_location_id
2256 UNION ALL
2257 SELECT DISTINCT
2258 wlpn.lpn_id,
2259 wlpn.license_plate_number,
2260 NULL,
2261 NULL,
2262 NULL,
2263 5 lpn_context
2264 FROM
2265 wms_license_plate_numbers wlpn
2266 WHERE wlpn.license_plate_number LIKE p_lpn
2267 AND wlpn.lpn_context = 5 -- Defined but not used
2268 AND wlpn.organization_id = p_org_id)
2269 ORDER BY license_plate_number;
2270
2271 END get_lpn_mass_move_lpn_lov;
2272
2273
2274 PROCEDURE get_empty_cons_loc_lov(x_loc_lov OUT NOCOPY t_genref,
2275 p_sub IN VARCHAR2,
2276 p_loc IN VARCHAR2,
2277 p_org_id IN NUMBER)
2278
2279 IS
2280
2281 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2282 BEGIN
2283
2284 OPEN x_loc_lov FOR
2285
2286 SELECT DISTINCT
2287 milk.inventory_location_id,
2288 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2289 milk.description,
2290 ml.meaning,
2291 mil.dropping_order
2292 FROM
2293 mtl_item_locations_kfv milk,
2294 mtl_item_locations mil,
2295 mfg_lookups ml
2296 WHERE milk.organization_id = p_org_id
2297 AND INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) LIKE p_loc
2298 AND milk.subinventory_code = p_sub
2299 AND milk.inventory_location_id = mil.inventory_location_id
2300 AND milk.organization_id = mil.organization_id
2301 AND milk.project_id IS NULL
2302 AND milk.task_id IS NULL
2303 AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
2304 g_loc_type_packing_station,
2305 g_loc_type_staging_lane)
2306 AND nvl(mil.empty_flag, 'Y') = 'Y'
2307 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
2308 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
2309 ORDER BY ml.meaning, mil.dropping_order;
2310
2311 END get_empty_cons_loc_lov;
2312
2313 PROCEDURE get_empty_cons_loc_lov(x_loc_lov OUT NOCOPY t_genref,
2314 p_sub IN VARCHAR2,
2315 p_loc IN VARCHAR2,
2316 p_org_id IN NUMBER,
2317 p_alias IN VARCHAR2)
2318
2319 IS
2320
2321 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2322 BEGIN
2323
2324 IF p_alias IS NULL THEN
2325 get_empty_cons_loc_lov(
2326 x_loc_lov => x_loc_lov
2327 ,p_sub => p_sub
2328 ,p_loc => p_loc
2329 ,p_org_id => p_org_id
2333
2330 );
2331 RETURN;
2332 END IF;
2334 OPEN x_loc_lov FOR
2335
2336 SELECT DISTINCT
2337 milk.inventory_location_id,
2338 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2339 milk.description,
2340 ml.meaning,
2341 mil.dropping_order
2342 FROM
2343 mtl_item_locations_kfv milk,
2344 mtl_item_locations mil,
2345 mfg_lookups ml
2346 WHERE milk.organization_id = p_org_id
2347 AND milk.alias = p_alias
2348 AND milk.subinventory_code = p_sub
2349 AND milk.inventory_location_id = mil.inventory_location_id
2350 AND milk.organization_id = mil.organization_id
2351 AND milk.project_id IS NULL
2352 AND milk.task_id IS NULL
2353 AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
2354 g_loc_type_packing_station,
2355 g_loc_type_staging_lane)
2356 AND nvl(mil.empty_flag, 'Y') = 'Y'
2357 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
2358 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
2359 ORDER BY ml.meaning, mil.dropping_order;
2360
2361 END get_empty_cons_loc_lov;
2362
2363
2364 --This procedure gets the first empty consolidation loc
2365
2366 PROCEDURE get_empty_cons_loc(p_sub IN VARCHAR2,
2367 p_org_id IN NUMBER,
2368 x_loc OUT NOCOPY VARCHAR2,
2369 x_loc_count OUT NOCOPY NUMBER,
2370 x_return_status OUT NOCOPY VARCHAR2,
2371 x_msg_count OUT NOCOPY NUMBER,
2372 x_msg_data OUT NOCOPY VARCHAR2)
2373
2374 IS
2375
2376 CURSOR empty_loc_csr IS
2377
2378 SELECT DISTINCT
2379 INV_PROJECT.GET_LOCSEGS(milk.inventory_location_id, milk.organization_id) concatenated_segments,
2380 ml.meaning,
2381 mil.dropping_order
2382 FROM
2383 mtl_item_locations_kfv milk,
2384 mtl_item_locations mil,
2385 mfg_lookups ml
2386 WHERE milk.organization_id = p_org_id
2387 AND milk.subinventory_code = p_sub
2388 AND milk.inventory_location_id = mil.inventory_location_id
2389 AND milk.organization_id = mil.organization_id
2390 AND nvl(milk.inventory_location_type, 3) IN (g_loc_type_consolidation,
2391 g_loc_type_packing_station,
2392 g_loc_type_staging_lane)
2393 AND nvl(mil.empty_flag, 'Y') = 'Y'
2394 AND ml.lookup_type = 'MTL_LOCATOR_TYPES'
2395 AND ml.lookup_code = nvl(milk.inventory_location_type, 3)
2396 ORDER BY ml.meaning, mil.dropping_order;
2397
2398 l_loc_rec empty_loc_csr%ROWTYPE;
2399 l_count NUMBER;
2400
2401 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2402 BEGIN
2403
2404 x_return_status := 'S';
2405
2406 l_count := 0;
2407 OPEN empty_loc_csr;
2408 LOOP
2409 FETCH empty_loc_csr INTO l_loc_rec;
2410 EXIT WHEN empty_loc_csr%notfound;
2411
2412 l_count := l_count + 1;
2413 IF l_count = 1 THEN x_loc := l_loc_rec.concatenated_segments;
2414 END IF;
2415
2416 END LOOP;
2417 CLOSE empty_loc_csr;
2418
2419 x_loc_count := l_count;
2420
2421 EXCEPTION
2422
2423 WHEN FND_API.G_EXC_ERROR THEN
2424 x_return_status:=FND_API.G_RET_STS_ERROR;
2425 fnd_msg_pub.count_and_get
2426 ( p_count => x_msg_count
2427 , p_data => x_msg_data
2428 );
2429
2430 WHEN OTHERS THEN
2431
2432 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2433 fnd_msg_pub.count_and_get
2434 ( p_count => x_msg_count
2435 , p_data => x_msg_data
2436 );
2437
2438 END get_empty_cons_loc;
2439
2440
2441
2442 FUNCTION is_delivery_consolidated(p_delivery_id IN NUMBER,
2443 p_org_id IN NUMBER,
2444 p_sub IN VARCHAR2 DEFAULT NULL, -- added default for packing workbench query (patchset J)
2445 p_loc_id IN NUMBER DEFAULT NULL) -- added default for packing workbench query (patchset J)
2446
2447 RETURN VARCHAR2 IS
2448
2449 l_is_delivery_consolidated VARCHAR2(2) := 'N';
2450 l_count NUMBER := 0;
2451
2452 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2453 BEGIN
2454
2455 BEGIN
2456
2457 SELECT 1
2458 INTO l_count
2459 FROM
2460 wms_license_plate_numbers wlpn,
2461 -- wms_dispatched_tasks_history wdth, Commented by Bug#4337112.
2462 wsh_delivery_details wdd2,
2463 wsh_delivery_assignments_v wda
2464 WHERE wda.delivery_id = p_delivery_id
2465 AND wda.parent_delivery_detail_id = wdd2.delivery_detail_id
2466 AND wdd2.lpn_id = wlpn.lpn_id
2467 AND wlpn.lpn_context in ( 11,12)
2468 AND wlpn.subinventory_code = Nvl(p_sub, wlpn.subinventory_code) -- added NVL for packing workbench query (patchset J)
2469 AND wlpn.locator_id = Nvl(p_loc_id, wlpn.locator_id) -- added NVL for packing workbench query (patchset J)
2470 AND wlpn.organization_id = p_org_id
2471 -- AND wlpn.lpn_id = wdth.transfer_lpn_id Commented by Bug#4337112.
2472 AND NOT exists
2473 (
2477 wsh_delivery_details wdd1,
2474 SELECT 1
2475 FROM
2476 mtl_material_transactions_temp mmtt,
2478 wsh_delivery_assignments_v wda2
2479 WHERE wda2.delivery_id = p_delivery_id
2480 AND wda2.delivery_detail_id = wdd1.delivery_detail_id
2481 AND wdd1.organization_id = p_org_id
2482 AND wdd1.released_status = 'S'
2483 AND wdd1.move_order_line_id = mmtt.move_order_line_id
2484 AND wdd1.organization_id = mmtt.organization_id
2485 -- AND wdth.operation_plan_id = mmtt.operation_plan_id Commented by Bug#4337112.
2486 );
2487
2488
2489 EXCEPTION
2490 WHEN no_data_found THEN
2491 l_count := 0;
2492
2493 WHEN too_many_rows THEN
2494 l_count := 1;
2495
2496 END;
2497
2498 IF l_count > 0 THEN
2499
2500 l_is_delivery_consolidated := 'Y';
2501
2502 END IF;
2503
2504 RETURN l_is_delivery_consolidated;
2505
2506 END is_delivery_consolidated;
2507
2508
2509
2510 PROCEDURE create_staging_move
2511 (p_org_id IN NUMBER
2512 , p_user_id IN NUMBER
2513 , p_emp_id IN NUMBER
2514 , p_eqp_ins IN VARCHAR2
2515 , p_lpn_id IN NUMBER
2516 , x_return_status OUT nocopy VARCHAR2
2517 , x_msg_count OUT NOCOPY NUMBER
2518 , x_msg_data OUT NOCOPY VARCHAR2
2519 , p_calling_mode IN VARCHAR2
2520 , p_temp_id OUT NOCOPY NUMBER
2521 ) IS
2522
2523 l_from_sub VARCHAR2(60);
2524 l_from_loc NUMBER;
2525 l_task_type VARCHAR2(30);
2526 l_orig_sub VARCHAR2(30);
2527 l_orig_loc NUMBER;
2528 l_op_plan_id NUMBER;
2529
2530 l_temp_id NUMBER := 0;
2531
2532 l_period_id NUMBER;
2533 l_open_past_period BOOLEAN;
2534 l_item_id NUMBER;
2535
2536 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2537 BEGIN
2538
2539 SAVEPOINT sp_stg_move;
2540
2541 -- Since the forms-server and MWA server recycles database connections
2542 -- we need to always check for debug profiles values and see if they
2543 -- are different from the values with which it was initialized earlier. If
2544 -- different then reinitialize the debug variables
2545 g_trace_on := fnd_profile.value('INV_DEBUG_TRACE') ;
2546
2547 IF (g_trace_on = 1) THEN
2548
2549 IF (l_debug = 1) THEN
2550 mydebug('WMS_Staging_Move_Pvt: In Create Staging Move API: 10');
2551 mydebug('WMS_Staging_Move_Pvt: Initializing Variables: 11');
2552
2553 mydebug('p_org_id: '||p_org_id);
2554 mydebug('p_user_id: '||p_user_id);
2555 mydebug('p_emp_id: '||p_emp_id);
2556 mydebug('p_eqp_ins: '||p_eqp_ins);
2557 mydebug('p_lpn_id: '||p_lpn_id);
2558 mydebug('p_calling_mode: '||p_calling_mode);
2559 mydebug('p_temp_id: '||p_temp_id);
2560
2561 END IF;
2562
2563
2564 END IF;
2565
2566 x_return_status := 'S';
2567
2568 l_task_type:=7; --hard coded to be Staging Move task type
2569
2570
2571 IF (g_trace_on = 1) THEN
2572
2573 IF (l_debug = 1) THEN
2574 mydebug('WMS_Staging_Move_Pvt: Checking Acct period ID: 13');
2575 END IF;
2576
2577 END IF;
2578
2579
2580 invttmtx.tdatechk(org_id => p_org_id,
2581 transaction_date => sysdate,
2582 period_id => l_period_id,
2583 open_past_period => l_open_past_period);
2584
2585 IF l_period_id = -1 THEN
2586
2587 IF (g_trace_on = 1) THEN
2588
2589 IF (l_debug = 1) THEN
2590 mydebug('WMS_Staging_Move_Pvt: Period is invalid: 15');
2591 END IF;
2592
2593 END IF;
2594
2595 FND_MESSAGE.SET_NAME('INV', 'INV_NO_OPEN_PERIOD');
2596 FND_MSG_PUB.ADD;
2597 RAISE FND_API.G_exc_unexpected_error;
2598
2599 END IF;
2600
2601
2602 BEGIN
2603
2604 SELECT mmtt.transaction_temp_id
2605 INTO l_temp_id
2606 FROM
2607 wms_dispatched_tasks wdt,
2608 mtl_material_transactions_temp mmtt
2609 WHERE mmtt.organization_id = p_org_id
2610 AND mmtt.transfer_lpn_id = p_lpn_id
2611 AND mmtt.transaction_temp_id = wdt.transaction_temp_id
2612 AND ROWNUM = 1;
2613
2614
2615 EXCEPTION
2616 WHEN no_data_found THEN
2617 l_temp_id := 0;
2618 END;
2619
2620
2621 IF (l_temp_id > 0) THEN
2622
2623 IF (g_trace_on = 1) THEN
2624 IF (l_debug = 1) THEN
2625 mydebug('WMS_Staging_Move_Pvt: LPN already loaded: 29');
2626 END IF;
2627 END IF;
2628
2629 IF p_calling_mode = 'LOAD' THEN
2630 FND_MESSAGE.SET_NAME('WMS', 'WMS_LOADED_ERROR');
2631 FND_MSG_PUB.ADD;
2632 RAISE FND_API.G_exc_unexpected_error;
2633
2634 ELSE
2635 p_temp_id := l_temp_id;
2636 RETURN;
2637 END IF;
2638
2639 END IF;
2640
2641
2642 IF (g_trace_on = 1) THEN
2643
2647
2644 IF (l_debug = 1) THEN
2645 mydebug('WMS_Staging_Move_Pvt: Checking Mtl Status: 29.3');
2646 END IF;
2648 END IF;
2649
2650 IF inv_ui_item_sub_loc_lovs.vaildate_lpn_status
2651 (p_lpn_id => p_lpn_id,
2652 p_orgid => p_org_id,
2653 p_to_org_id => p_org_id,
2654 p_wms_installed => 'TRUE',
2655 p_transaction_type_id => 2) = 'N'
2656 OR
2657 inv_txn_validations.check_lpn_allocation
2658 (p_lpn_id => p_lpn_id,
2659 p_org_id => p_org_id,
2660 x_return_msg => x_msg_data) = 'N'
2661 OR
2662 inv_txn_validations.check_lpn_serial_allocation
2663 (p_lpn_id => p_lpn_id,
2664 p_org_id => p_org_id,
2665 x_return_msg => x_msg_data) = 'N' THEN
2666
2667 IF (g_trace_on = 1) THEN
2668 IF (l_debug = 1) THEN
2669 mydebug('WMS_Staging_Move_Pvt: Mtl Status Check Failed: 29.5');
2670 END IF;
2671 END IF;
2672
2673 FND_MESSAGE.SET_NAME('WMS', x_msg_data);
2674 FND_MSG_PUB.ADD;
2675 RAISE FND_API.G_exc_unexpected_error;
2676 END IF;
2677
2678 IF (g_trace_on = 1) THEN
2679 IF (l_debug = 1) THEN
2680 mydebug('WMS_Staging_Move_Pvt: Calculate Original Destination: 32');
2681 END IF;
2682 END IF;
2683
2684 BEGIN
2685 SELECT
2686 wdth.suggested_dest_subinventory,
2687 wdth.suggested_dest_locator_id,
2688 wdth.operation_plan_id,
2689 wlpn.subinventory_code,
2690 wlpn.locator_id
2691 INTO
2692 l_orig_sub,
2693 l_orig_loc,
2694 l_op_plan_id,
2695 l_from_sub,
2696 l_from_loc
2697 FROM
2698 wms_dispatched_tasks_history wdth,
2699 wms_license_plate_numbers wlpn
2700 WHERE wlpn.organization_id = p_org_id
2701 AND wlpn.outermost_lpn_id = p_lpn_id
2702 AND wdth.organization_id = p_org_id
2703 AND wdth.transfer_lpn_id = wlpn.lpn_id
2704 AND wdth.task_type = WMS_GLOBALS.G_WMS_TASK_TYPE_PICK --Bug5883610
2705 AND ROWNUM < 2;
2706
2707
2708 EXCEPTION
2709 WHEN no_data_found THEN
2710 IF (g_trace_on = 1) THEN
2711 IF (l_debug = 1) THEN
2712 mydebug('WMS_Staging_Move_Pvt: No corresponding rows found in WDTH: 35');
2713 END IF;
2714 END IF;
2715 --Fix for the bug #4157153.Added the following Block.
2716 --In case of Staging move for a splitted LPN, we will not have WDTH.
2717 BEGIN
2718 SELECT wlpn.subinventory_code, wlpn.locator_id
2719 INTO l_from_sub, l_from_loc
2720 FROM wms_license_plate_numbers wlpn
2721 WHERE wlpn.lpn_id = p_lpn_id; --Bug#4337112. Changed outermost_lpn_id to lpn_id
2722
2723 --Populate the suggested sub and loc
2724 l_orig_sub := l_from_sub;
2725 l_orig_loc := l_from_loc;
2726 IF (g_trace_on = 1 and l_debug = 1) THEN
2727 mydebug('WMS_Staging_Move_Pvt: For LPNs with no WDTH- l_from_sub:'||l_from_sub||',l_from_loc:'||l_from_loc);
2728 END IF;
2729 EXCEPTION
2730 WHEN no_data_found THEN
2731 FND_MESSAGE.SET_NAME('WMS','WMS_CONT_INVALID_LPN');
2732 FND_MSG_PUB.ADD;
2733 RAISE FND_API.G_EXC_ERROR;
2734 END; --End of Fix for bug #4157153.
2735 END;
2736
2737 SELECT wlc.inventory_item_id
2738 INTO l_item_id
2739 FROM
2740 wms_lpn_contents wlc,
2741 wms_license_plate_numbers wlpn
2742 WHERE wlc.parent_lpn_id = wlpn.lpn_id
2743 AND wlc.organization_id = wlpn.organization_id
2744 AND wlpn.outermost_lpn_id = p_lpn_id
2745 AND wlpn.organization_id = p_org_id
2746 AND ROWNUM = 1;
2747
2748 INSERT INTO MTL_MATERIAL_TRANSACTIONS_TEMP
2749 (TRANSACTION_HEADER_ID,
2750 TRANSACTION_TEMP_ID,
2751 PROCESS_FLAG,
2752 transaction_status,
2753 CREATION_DATE,
2754 CREATED_BY,
2755 LAST_UPDATE_DATE,
2756 LAST_UPDATED_BY,
2757 LAST_UPDATE_LOGIN,
2758 INVENTORY_ITEM_ID,
2759 ORGANIZATION_ID,
2760 SUBINVENTORY_CODE,
2761 LOCATOR_ID,
2762 transfer_organization,
2763 TRANSFER_SUBINVENTORY ,
2764 TRANSFER_TO_LOCATION,
2768 TRANSACTION_TYPE_ID,
2765 TRANSACTION_QUANTITY,
2766 PRIMARY_QUANTITY,
2767 TRANSACTION_UOM,
2769 TRANSACTION_ACTION_ID,
2770 TRANSACTION_SOURCE_TYPE_ID,
2771 TRANSACTION_DATE,
2772 acct_period_id,
2773 CONTENT_LPN_ID,
2774 posting_flag,
2775 operation_plan_id,
2776 wms_task_type,
2777 transfer_lpn_id)
2778 VALUES
2779 (mtl_material_transactions_s.nextval,
2780 mtl_material_transactions_s.NEXTVAL,
2781 'Y',
2782 2,
2783 sysdate,
2784 p_user_id,
2785 sysdate,
2786 p_user_id,
2787 p_user_id,
2788 l_item_id,-- inventory item id
2789 p_org_id,
2790 l_from_sub,
2791 l_from_loc,
2792 p_org_id,
2793 l_orig_sub,
2794 l_orig_loc,
2795 1,--trx qty
2796 1, --prim qty
2797 'X',--uom
2798 2,-- p_trx_type_id,
2799 2,-- p_trx_action_id,
2800 13,-- p_trx_src_type_id,
2801 sysdate, --tran date
2802 l_period_id,
2803 p_lpn_id,--content lpn id
2804 'Y',
2805 l_op_plan_id,
2806 l_task_type,
2807 p_lpn_id)
2808 returning transaction_temp_id INTO p_temp_id;
2809
2810
2811 INSERT INTO WMS_DISPATCHED_TASKS
2812 (TASK_ID ,
2813 TRANSACTION_TEMP_ID ,
2814 ORGANIZATION_ID ,
2815 USER_TASK_TYPE ,
2816 PERSON_ID ,
2817 EFFECTIVE_START_DATE ,
2818 EFFECTIVE_END_DATE ,
2819 EQUIPMENT_ID ,
2820 EQUIPMENT_INSTANCE ,
2821 PERSON_RESOURCE_ID ,
2822 MACHINE_RESOURCE_ID ,
2823 STATUS ,
2824 DISPATCHED_TIME ,
2825 LAST_UPDATE_DATE ,
2826 LAST_UPDATED_BY ,
2827 CREATION_DATE ,
2828 CREATED_BY ,
2829 task_type,
2830 suggested_dest_subinventory,
2831 suggested_dest_locator_id,
2832 operation_plan_id,
2833 TRANSFER_LPN_ID)
2834 VALUES(wms_dispatched_tasks_s.nextval,
2835 p_temp_id,
2836 p_org_id,
2837 2,
2838 p_emp_id,
2839 sysdate,
2840 sysdate,
2841 0,
2842 p_eqp_ins,
2843 0,
2844 0,
2845 4,
2846 sysdate,
2847 sysdate,
2848 p_user_id,
2849 sysdate,
2850 p_user_id,
2851 l_task_type,
2852 l_orig_sub,
2853 l_orig_loc,
2854 l_op_plan_id,
2855 p_lpn_id
2856 );
2857
2858
2859 IF (l_debug = 1) THEN
2860 mydebug('Update lpn_context to packing context - p_lpn_id: ' || p_lpn_id
2861 || ':' || wms_container_pvt.lpn_loaded_in_stage);
2862 END IF;
2863
2864 /* bug 3424353
2865 * change the staus of the inner ones also to packing
2866
2867 UPDATE wms_license_plate_numbers
2868 SET lpn_context = wms_container_pub.lpn_context_packing
2869 WHERE lpn_id = p_lpn_id;
2870 */
2871
2872 wms_container_pvt.Modify_LPN_Wrapper
2873 ( p_api_version => 1.0
2874 ,x_return_status => x_return_status
2875 ,x_msg_count => x_msg_count
2876 ,x_msg_data => x_msg_data
2877 ,p_caller => 'WMS_CONS_STG_MV' -- Staging move
2878 ,p_lpn_id => p_lpn_id
2879 ,p_lpn_context => wms_container_pvt.lpn_loaded_in_stage
2880 );
2881
2882 IF (l_debug = 1) THEN
2883 mydebug('After wms_container_pvt.Modify_LPN_Wrapper: x_return_status' ||x_return_status );
2884 END IF;
2885 -- MRANA - MDC */
2886 IF p_calling_mode = 'LOAD' THEN
2887 IF (l_debug = 1) THEN
2888 mydebug('Commit ' || p_calling_mode );
2889 END IF;
2890
2891 COMMIT;
2892 END IF;
2893
2894 EXCEPTION
2895
2896 WHEN FND_API.G_EXC_ERROR THEN
2897 ROLLBACK TO sp_stg_move;
2898 x_return_status:=FND_API.G_RET_STS_ERROR;
2899 fnd_msg_pub.count_and_get
2900 ( p_count => x_msg_count
2901 , p_data => x_msg_data
2902 );
2903 IF (g_trace_on = 1) THEN
2904 IF (l_debug = 1) THEN
2905 mydebug('create_staging_move: Error in create_staging_move API: ' || sqlerrm);
2906 END IF;
2907 END IF;
2908
2909
2910 WHEN OTHERS THEN
2911
2912 ROLLBACK TO sp_stg_move;
2913 x_return_status:=FND_API.G_RET_STS_UNEXP_ERROR;
2914 fnd_msg_pub.count_and_get
2915 ( p_count => x_msg_count
2916 , p_data => x_msg_data
2917 );
2918 IF (g_trace_on = 1) THEN
2919 IF (l_debug = 1) THEN
2920 mydebug('create_staging_move: Unexpected Error in create_staging_move API: ' || sqlerrm);
2921 END IF;
2922 END IF;
2923
2924
2925
2926 END create_staging_move;
2927
2928
2929 PROCEDURE mydebug(msg in varchar2)
2930 IS
2931
2932 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2933 BEGIN
2934
2935 inv_mobile_helper_functions.tracelog
2936 (p_err_msg => msg,
2937 p_module => 'wms_consolidation_pub',
2938 p_level => 4);
2939
2940 --dbms_output.put_line(msg);
2941
2942 null;
2943 END;
2944
2945 END wms_consolidation_pub;