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