[Home] [Help]
PACKAGE BODY: APPS.WMS_CATCH_WEIGHT_PVT
Source
1 PACKAGE BODY WMS_CATCH_WEIGHT_PVT AS
2 /* $Header: WMSVCWTB.pls 120.7.12020000.12 2013/05/23 17:35:21 sahmahes ship $ */
3
4 -- Global constant holding the package name
5 g_pkg_name CONSTANT VARCHAR2(30) := 'WMS_CATCH_WEIGHT_PVT';
6 g_pkg_version CONSTANT VARCHAR2(100) := '$Header: WMSVCWTB.pls 120.7.12020000.12 2013/05/23 17:35:21 sahmahes ship $';
7
8 g_precision CONSTANT NUMBER := 5;
9
10 TYPE tbl_num IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
11 TYPE tbl_v10 IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
12 TYPE tbl_v3 IS TABLE OF VARCHAR2(3) INDEX BY PLS_INTEGER;
13 TYPE tbl_v1 IS TABLE OF VARCHAR2(1) INDEX BY LONG;
14
15 g_src_line_id tbl_num;
16
17
18 PROCEDURE print_debug( p_message VARCHAR2, p_level NUMBER ) IS
19 BEGIN
20 --dbms_output.put_line(p_message);
21 inv_log_util.trace(
22 p_message => p_message
23 , p_module => g_pkg_name
24 , p_level => p_level);
25 END print_debug;
26
27 PROCEDURE Get_Catch_Weight_Attributes (
28 p_api_version IN NUMBER
29 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
30 , x_return_status OUT NOCOPY VARCHAR2
31 , x_msg_count OUT NOCOPY NUMBER
32 , x_msg_data OUT NOCOPY VARCHAR2
33 , p_organization_id IN NUMBER
34 , p_inventory_item_id IN NUMBER
35 , p_quantity IN NUMBER := NULL
36 , p_uom_code IN VARCHAR2 := NULL
37 , x_tracking_quantity_ind OUT NOCOPY VARCHAR2
38 , x_ont_pricing_qty_source OUT NOCOPY VARCHAR2
39 , x_secondary_default_ind OUT NOCOPY VARCHAR2
40 , x_secondary_quantity OUT NOCOPY NUMBER
41 , x_secondary_uom_code OUT NOCOPY VARCHAR2
42 , x_uom_deviation_high OUT NOCOPY NUMBER
43 , x_uom_deviation_low OUT NOCOPY NUMBER
44 ) IS
45 l_api_name CONSTANT VARCHAR2(30) := 'Get_Catch_Weight_Attributes';
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
48 l_progress VARCHAR2(10) := '0';
49
50 l_wms_org_flag BOOLEAN;
51 l_secondary_uom_code VARCHAR2(3);
52 l_result VARCHAR2(30);
53 BEGIN
54 -- Standard call to check for call compatibility.
55 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
56 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
57 fnd_msg_pub.ADD;
58 RAISE fnd_api.g_exc_unexpected_error;
59 END IF;
60
61 -- Initialize message list if p_init_msg_list is set to TRUE.
62 IF fnd_api.to_boolean(p_init_msg_list) THEN
63 fnd_msg_pub.initialize;
64 END IF;
65
66 -- Initialize API return status to success
67 x_return_status := fnd_api.g_ret_sts_success;
68
69 IF ( l_debug = 1 ) THEN
70 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
71 print_debug('orgid='||p_organization_id||' itemid='||p_inventory_item_id||' qty='||p_quantity||' uom='||p_uom_code, 4);
72 END IF;
73
74 -- Check if the organization is a WMS organization
75 l_wms_org_flag := wms_install.check_install (
76 x_return_status => x_return_status
77 , x_msg_count => x_msg_count
78 , x_msg_data => x_msg_data
79 , p_organization_id => p_organization_id );
80 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
81 IF ( l_debug = 1 ) THEN
82 print_debug('Call to wms_install.check_install failed:' ||x_msg_data, 1);
83 END IF;
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 l_progress := '100';
88 IF ( l_wms_org_flag ) THEN
89 l_progress := '200';
90 SELECT NVL(tracking_quantity_ind, G_TRACK_PRIMARY),
91 NVL(ont_pricing_qty_source, G_PRICE_PRIMARY),
92 secondary_default_ind,
93 secondary_uom_code,
94 dual_uom_deviation_high,
95 dual_uom_deviation_low
96 INTO x_tracking_quantity_ind,
97 x_ont_pricing_qty_source,
98 x_secondary_default_ind,
99 x_secondary_uom_code,
100 x_uom_deviation_high,
101 x_uom_deviation_low
102 FROM MTL_SYSTEM_ITEMS
103 WHERE organization_id = p_organization_id
104 AND inventory_item_id = p_inventory_item_id;
105
106 l_progress := '300';
107 IF ( p_quantity IS NOT NULL ) THEN
108 Get_Default_Secondary_Quantity (
109 p_api_version => 1.0
110 , x_return_status => x_return_status
111 , x_msg_count => x_msg_count
112 , x_msg_data => x_msg_data
113 , p_organization_id => p_organization_id
114 , p_inventory_item_id => p_inventory_item_id
115 , p_quantity => p_quantity
116 , p_uom_code => p_uom_code
117 , p_secondary_default_ind => x_secondary_default_ind
118 , x_ont_pricing_qty_source => x_ont_pricing_qty_source
119 , x_secondary_uom_code => x_secondary_uom_code
120 , x_secondary_quantity => x_secondary_quantity);
121
122 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
123 IF ( l_debug = 1 ) THEN
124 print_debug('Call to Get_Default_Secondary_Quantity failed', 1);
125 END IF;
126 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
127 END IF;
128 END IF;
129 ELSE -- not a wms org
130 IF ( l_debug = 1 ) THEN
131 print_debug('Not a WMS org return default values', 4);
132 END IF;
133 x_tracking_quantity_ind := G_TRACK_PRIMARY;
134 x_ont_pricing_qty_source := G_PRICE_PRIMARY;
135 x_secondary_default_ind := NULL;
136 x_secondary_quantity := NULL;
137 x_secondary_uom_code := NULL;
138 x_uom_deviation_high := NULL;
139 x_uom_deviation_low := NULL;
140 END IF;
141
142 IF ( l_debug = 1 ) THEN
143 print_debug(l_api_name || ' Exited ', 1);
144 print_debug('track_ind='||x_tracking_quantity_ind||' pricesrc='||x_ont_pricing_qty_source||' defaultind='||x_secondary_default_ind , 4);
145 print_debug('secqty='||x_secondary_quantity||' secuom='||x_secondary_uom_code||' devhigh='||x_uom_deviation_high||' devlow='||x_uom_deviation_low, 4);
146 END IF;
147
148 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
149 EXCEPTION
150 WHEN OTHERS THEN
151 IF (l_debug = 1) THEN
152 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
153 IF ( SQLCODE IS NOT NULL ) THEN
154 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
155 END IF;
156 END IF;
157 x_return_status := fnd_api.g_ret_sts_error;
158 fnd_message.set_name('WMS', 'WMS_GET_CWT_ATTR_FAIL');
159 fnd_msg_pub.ADD;
160 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
161 END Get_Catch_Weight_Attributes;
162
163
164 FUNCTION Get_Ont_Pricing_Qty_Source (
165 p_api_version IN NUMBER
166 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
167 , x_return_status OUT NOCOPY VARCHAR2
168 , x_msg_count OUT NOCOPY NUMBER
169 , x_msg_data OUT NOCOPY VARCHAR2
170 , p_organization_id IN NUMBER
171 , p_inventory_item_id IN NUMBER
172 ) RETURN VARCHAR2 IS
173 l_api_name CONSTANT VARCHAR2(30) := 'Get_Ont_Pricing_Qty_Source';
174 l_api_version CONSTANT NUMBER := 1.0;
175 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
176 l_progress VARCHAR2(10) := '0';
177
178 l_wms_org_flag BOOLEAN;
179 l_ont_pricing_qty_source VARCHAR(30);
180 BEGIN
181 -- Standard call to check for call compatibility.
182 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
183 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
184 fnd_msg_pub.ADD;
185 RAISE fnd_api.g_exc_unexpected_error;
186 END IF;
187
188 -- Initialize message list if p_init_msg_list is set to TRUE.
189 IF fnd_api.to_boolean(p_init_msg_list) THEN
190 fnd_msg_pub.initialize;
191 END IF;
192
193 -- Initialize API return status to success
194 x_return_status := fnd_api.g_ret_sts_success;
195
196 IF ( l_debug = 1 ) THEN
197 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
198 print_debug('orgid='||p_organization_id||' itemid='||p_inventory_item_id, 4);
199 END IF;
200
201 -- Check if the organization is a WMS organization
202 l_wms_org_flag := wms_install.check_install (
203 x_return_status => x_return_status
204 , x_msg_count => x_msg_count
205 , x_msg_data => x_msg_data
206 , p_organization_id => p_organization_id );
207 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
208 IF ( l_debug = 1 ) THEN
209 print_debug('Call to wms_install.check_install failed:' ||x_msg_data, 1);
210 END IF;
211 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
212 END IF;
213
214 IF ( l_wms_org_flag ) THEN
215 if (p_inventory_item_id is not null) then --BUG12851446
216 SELECT NVL(ont_pricing_qty_source, G_PRICE_PRIMARY)
217 INTO l_ont_pricing_qty_source
218 FROM MTL_SYSTEM_ITEMS
219 WHERE organization_id = p_organization_id
220 AND inventory_item_id = p_inventory_item_id;
221 else
222 IF ( l_debug = 1 ) THEN
223 print_debug('p_inventory_item_id is null so return default. Case of container txn.. Only LPNs involved', 4);
224 END IF;
225 l_ont_pricing_qty_source := G_PRICE_PRIMARY;
226 end if;
227 ELSE -- not a wms org
228 IF ( l_debug = 1 ) THEN
229 print_debug('Not a WMS org return default value', 4);
230 END IF;
231 l_ont_pricing_qty_source := G_PRICE_PRIMARY;
232 END IF;
233
234 IF ( l_debug = 1 ) THEN
235 print_debug(l_api_name||' Exited '||'ret='||l_ont_pricing_qty_source, 1);
236 END IF;
237
238 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
239
240 RETURN l_ont_pricing_qty_source;
241 EXCEPTION
242 WHEN OTHERS THEN
243 IF (l_debug = 1) THEN
244 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
245 IF ( SQLCODE IS NOT NULL ) THEN
246 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
247 END IF;
248 END IF;
249
250 x_return_status := fnd_api.g_ret_sts_error;
251 fnd_message.set_name('WMS', 'WMS_API_FAIL');
252 fnd_msg_pub.ADD;
253 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
254 END Get_Ont_Pricing_Qty_Source;
255
256
257 PROCEDURE Get_Default_Secondary_Quantity (
258 p_api_version IN NUMBER
259 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
260 , x_return_status OUT NOCOPY VARCHAR2
261 , x_msg_count OUT NOCOPY NUMBER
262 , x_msg_data OUT NOCOPY VARCHAR2
263 , p_organization_id IN NUMBER
264 , p_inventory_item_id IN NUMBER
265 , p_quantity IN NUMBER
266 , p_uom_code IN VARCHAR2
267 , p_secondary_default_ind IN VARCHAR2 := NULL
268 , x_ont_pricing_qty_source IN OUT NOCOPY VARCHAR2
269 , x_secondary_uom_code IN OUT NOCOPY VARCHAR2
270 , x_secondary_quantity OUT NOCOPY NUMBER
271 ) IS
272 l_api_name CONSTANT VARCHAR2(30) := 'Get_Default_Secondary_Quantity';
273 l_api_version CONSTANT NUMBER := 1.0;
274 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
275 l_progress VARCHAR2(10) := '0';
276
277 l_wms_org_flag BOOLEAN;
278 l_default_ind VARCHAR(30);
279 l_uom_code VARCHAR(3);
280 l_secondary_uom VARCHAR(3);
281 BEGIN
282 -- Standard call to check for call compatibility.
283 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
284 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
285 fnd_msg_pub.ADD;
286 RAISE fnd_api.g_exc_unexpected_error;
287 END IF;
288
289 -- Initialize message list if p_init_msg_list is set to TRUE.
290 IF fnd_api.to_boolean(p_init_msg_list) THEN
291 fnd_msg_pub.initialize;
292 END IF;
293
294 -- Initialize API return status to success
295 x_return_status := fnd_api.g_ret_sts_success;
296
297 IF ( l_debug = 1 ) THEN
298 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
299 print_debug('orgid='||p_organization_id||' itemid='||p_inventory_item_id||' qty='||p_quantity||' uom='||p_uom_code, 4);
300 print_debug('pricesrc='||x_ont_pricing_qty_source||' defaultind='||p_secondary_default_ind||' secuom='||x_secondary_uom_code, 4);
301 END IF;
302
303 l_progress := '000';
304 IF ( x_secondary_uom_code IS NULL OR x_ont_pricing_qty_source IS NULL OR
305 p_secondary_default_ind IS NULL OR p_uom_code IS NULL ) THEN
306 -- Check if the organization is a WMS organization
307 l_wms_org_flag := wms_install.check_install (
308 x_return_status => x_return_status
309 , x_msg_count => x_msg_count
310 , x_msg_data => x_msg_data
311 , p_organization_id => p_organization_id );
312 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
313 IF ( l_debug = 1 ) THEN
314 print_debug('Call to wms_install.check_install failed:' ||x_msg_data, 1);
315 END IF;
316 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
317 END IF;
318
319 IF ( l_wms_org_flag ) THEN
320 SELECT ont_pricing_qty_source,
321 secondary_default_ind,
322 primary_uom_code,
323 secondary_uom_code
324 INTO x_ont_pricing_qty_source,
325 l_default_ind,
326 l_uom_code,
327 x_secondary_uom_code
328 FROM mtl_system_items
329 WHERE organization_id = p_organization_id
330 AND inventory_item_id = p_inventory_item_id;
331 ELSE -- not a wms org
332 IF ( l_debug = 1 ) THEN
333 print_debug('Not a WMS org return default value', 4);
334 END IF;
335 x_ont_pricing_qty_source := G_PRICE_PRIMARY;
336 l_default_ind := NULL;
337 l_uom_code := NULL;
338 x_secondary_uom_code := NULL;
339 END IF;
340 ELSE -- Use the values given by user to calculate the defaults
341 l_default_ind := p_secondary_default_ind;
342 END IF;
343
344 l_progress := '100';
345 -- If item is secondary priced and is not default restricted calculate the default
346 -- secondary value based off the secondary uom.
347 IF ( x_ont_pricing_qty_source = G_PRICE_SECONDARY AND
348 l_default_ind <> G_SECONDARY_NO_DEFAULT ) THEN
349 l_progress := '200';
350 IF ( x_secondary_uom_code IS NULL ) THEN
351 IF (l_debug = 1) THEN
352 print_debug('Secondary UOM is not defined for this secondary priced item', 1);
353 END IF;
354 fnd_message.set_name('WMS', 'WMS_SEC_UOM_UNDEF_ERROR');
355 fnd_msg_pub.ADD;
356 RAISE FND_API.G_EXC_ERROR;
357 END IF;
358
359 -- Use user specified uom if passed
360 IF ( p_uom_code IS NOT NULL ) THEN
361 l_uom_code := p_uom_code;
362 END IF;
363
364 l_progress := '300';
365 x_secondary_quantity := inv_convert.inv_um_convert(
366 p_inventory_item_id
367 , g_precision
368 , p_quantity
369 , l_uom_code
370 , x_secondary_uom_code
371 , NULL
372 , NULL );
373 IF ( x_secondary_quantity < 0 ) THEN
374 IF ( l_debug = 1 ) THEN
375 print_debug('Error converting to from '||p_uom_code||' to '||x_secondary_uom_code, 1);
376 END IF;
377 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
378 fnd_message.set_token('uom1', p_uom_code);
379 fnd_message.set_token('uom2', x_secondary_uom_code);
380 fnd_message.set_token('module', l_api_name);
381 fnd_msg_pub.ADD;
382 RAISE FND_API.G_EXC_ERROR;
383 END IF;
384 l_progress := '400';
385 ELSE
386 x_secondary_quantity := NULL;
387 END IF;
388
389 IF ( l_debug = 1 ) THEN
390 print_debug(l_api_name||' Exited '||' priceind=' ||x_ont_pricing_qty_source, 1);
391 print_debug('secqty='||x_secondary_quantity||' secuom='||x_secondary_uom_code, 4);
392 END IF;
393
394 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
395 EXCEPTION
396 WHEN OTHERS THEN
397 IF (l_debug = 1) THEN
398 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
399 IF ( SQLCODE IS NOT NULL ) THEN
400 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
401 END IF;
402 END IF;
403
404 x_return_status := fnd_api.g_ret_sts_error;
405 fnd_message.set_name('WMS', 'WMS_CALC_SEC_QTY_FAIL');
406 fnd_msg_pub.ADD;
407 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
408 END Get_Default_Secondary_Quantity;
409
410
411 FUNCTION Check_Secondary_Qty_Tolerance (
412 p_api_version IN NUMBER
413 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
414 , x_return_status OUT NOCOPY VARCHAR2
415 , x_msg_count OUT NOCOPY NUMBER
416 , x_msg_data OUT NOCOPY VARCHAR2
417 , p_organization_id IN NUMBER
418 , p_inventory_item_id IN NUMBER
419 , p_quantity IN NUMBER
420 , p_uom_code IN VARCHAR2
421 , p_secondary_quantity IN NUMBER
422 , p_secondary_uom_code IN VARCHAR2 := NULL
423 , p_ont_pricing_qty_source IN VARCHAR2 := NULL
424 , p_uom_deviation_high IN NUMBER := NULL
425 , p_uom_deviation_low IN NUMBER := NULL
426 ) RETURN NUMBER IS
427 l_api_name CONSTANT VARCHAR2(30) := 'Check_Secondary_Qty_Tolerance';
428 l_api_version CONSTANT NUMBER := 1.0;
429 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
430 l_progress VARCHAR2(10) := '0';
431
432 l_tracking_quantity_ind VARCHAR2(30);
433 l_ont_pricing_qty_source VARCHAR2(30);
434 l_secondary_default_ind VARCHAR2(30);
435 l_secondary_quantity NUMBER;
436 l_secondary_uom_code VARCHAR2(3);
437 l_uom_deviation_high NUMBER;
438 l_uom_deviation_low NUMBER;
439 l_upper_qty_limit NUMBER;
440 l_lower_qty_limit NUMBER;
441 l_return NUMBER := 0;
442 l_uom_code VARCHAR2(3) := p_uom_code;
443 l_converted_qty NUMBER;
444 BEGIN
445 -- Standard call to check for call compatibility.
446 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
447 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
448 fnd_msg_pub.ADD;
449 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
450 END IF;
451
452 -- Initialize message list if p_init_msg_list is set to TRUE.
453 IF fnd_api.to_boolean(p_init_msg_list) THEN
454 fnd_msg_pub.initialize;
455 END IF;
456
457 -- Initialize API return status to success
458 x_return_status := fnd_api.g_ret_sts_success;
459
460 IF ( l_debug = 1 ) THEN
461 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
462 print_debug('orgid='||p_organization_id||' itemid='||p_inventory_item_id||' qty='||p_quantity||' uom='||p_uom_code||' secqty='||p_secondary_quantity, 4);
463 print_debug('pricesrc='||p_ont_pricing_qty_source||' secuom='||p_secondary_uom_code||' devhigh='||p_uom_deviation_high||' devlow='||p_uom_deviation_low, 4);
464 END IF;
465
466 IF ( p_secondary_uom_code IS NULL OR p_ont_pricing_qty_source IS NULL OR
467 p_uom_deviation_high IS NULL OR p_uom_deviation_low IS NULL ) THEN
468 Get_Catch_Weight_Attributes (
469 p_api_version => 1.0
470 , x_return_status => x_return_status
471 , x_msg_count => x_msg_count
472 , x_msg_data => x_msg_data
473 , p_organization_id => p_organization_id
474 , p_inventory_item_id => p_inventory_item_id
475 , x_tracking_quantity_ind => l_tracking_quantity_ind
476 , x_ont_pricing_qty_source => l_ont_pricing_qty_source
477 , x_secondary_default_ind => l_secondary_default_ind
478 , x_secondary_quantity => l_secondary_quantity
479 , x_secondary_uom_code => l_secondary_uom_code
480 , x_uom_deviation_high => l_uom_deviation_high
481 , x_uom_deviation_low => l_uom_deviation_low );
482 ELSE
483 l_ont_pricing_qty_source := p_ont_pricing_qty_source;
484 l_secondary_uom_code := p_secondary_uom_code;
485 l_uom_deviation_high := p_uom_deviation_high;
486 l_uom_deviation_low := p_uom_deviation_low;
487 END IF;
488
489 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
490 IF ( l_debug = 1 ) THEN
491 print_debug('Call to Get_Catch_Weight_Attributes failed', 1);
492 END IF;
493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
494 END IF;
495
496 l_progress := '100';
497 IF (l_uom_deviation_high IS NOT NULL OR l_uom_deviation_low IS NOT NULL) THEN
498 l_progress := '200';
499 l_converted_qty := inv_convert.inv_um_convert(
500 p_inventory_item_id
501 , 5 -- Updated precision as 5 for Bug 15877579
502 , p_quantity
503 , l_uom_code
504 , l_secondary_uom_code
505 , NULL
506 , NULL );
507 IF ( l_converted_qty < 0 ) THEN
508 IF ( l_debug = 1 ) THEN
509 print_debug('Error converting qty from '||l_uom_code||' to '||l_secondary_uom_code, 1);
510 END IF;
511 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
512 fnd_message.set_token('uom1', l_uom_code);
513 fnd_message.set_token('uom2', l_secondary_uom_code);
514 fnd_message.set_token('module', l_api_name);
515 fnd_msg_pub.ADD;
516 RAISE FND_API.G_EXC_ERROR;
517 END IF;
518
519 IF ( l_uom_deviation_high IS NOT NULL ) THEN
520 l_progress := '300';
521 --l_upper_qty_limit := p_quantity converted into catch weight uom* 1+DEVIATION_HIGH/100
522 l_upper_qty_limit := round(l_converted_qty*(1+(l_uom_deviation_high/100)), g_precision);
523
524 IF ( round(p_secondary_quantity, g_precision) > l_upper_qty_limit ) THEN
525 l_return := 1;
526 END IF;
527 END IF;
528
529 IF ( l_return = 0 AND l_uom_deviation_low IS NOT NULL ) THEN
530 l_progress := '400';
531 --l_lower_qty_limit := p_quantity converted into catch weight uom* 1-DEVIATION_LOW/100
532 l_lower_qty_limit := round(l_converted_qty*(1-(l_uom_deviation_low/100)), g_precision);
533
534 IF ( round(p_secondary_quantity, g_precision) < l_lower_qty_limit ) THEN
535 l_return := -1;
536 END IF;
537 END IF;
538 l_progress := '500';
539 END IF;
540
541 IF ( l_debug = 1 ) THEN
542 print_debug(l_api_name||' Exited '||' ret=' ||l_return, 1);
543 print_debug('uplim='||l_upper_qty_limit||' lowlim'||l_lower_qty_limit, 4);
544 END IF;
545
546 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
547
548 RETURN l_return;
549 EXCEPTION
550 WHEN OTHERS THEN
551 IF (l_debug = 1) THEN
552 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
553 IF ( SQLCODE IS NOT NULL ) THEN
554 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
555 END IF;
556 END IF;
557
558 x_return_status := fnd_api.g_ret_sts_error;
559 fnd_message.set_name('WMS', 'WMS_API_FAIL');
560 fnd_msg_pub.ADD;
561 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
562 END Check_Secondary_Qty_Tolerance;
563
564
565 PROCEDURE Update_Shipping_Secondary_Qty (
566 p_api_version IN NUMBER
567 , p_init_msg_list IN VARCHAR2
568 , p_commit IN VARCHAR2
569 , x_return_status OUT NOCOPY VARCHAR2
570 , x_msg_count OUT NOCOPY NUMBER
571 , x_msg_data OUT NOCOPY VARCHAR2
572 , p_delivery_detail_id IN NUMBER
573 , p_secondary_quantity IN NUMBER
574 , p_secondary_uom_code IN VARCHAR2 := NULL
575 ) IS
576 l_api_name CONSTANT VARCHAR2(30) := 'Update_Shipping_Secondary_Qty';
577 l_api_version CONSTANT NUMBER := 1.0;
578 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
579 l_progress VARCHAR2(10) := '0';
580
581 l_shipping_attr WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type;
582 l_shipping_in_rec WSH_INTERFACE_EXT_GRP.detailInRecType;
583 l_shipping_out_rec WSH_INTERFACE_EXT_GRP.detailOutRecType;
584
585 l_msg_details VARCHAR2(3000);
586 l_pricing_ind VARCHAR2(30);
587 l_tolerance NUMBER;
588 l_organization_id NUMBER;
589 l_inventory_item_id NUMBER;
590 l_primary_uom_code VARCHAR2(3) := NULL;
591 l_picked_quantity NUMBER;
592 l_requested_quantity_uom VARCHAR2(3);
593 l_secondary_quantity NUMBER;
594 l_secondary_uom_code VARCHAR(3);
595
596 BEGIN
597 SAVEPOINT UPDATE_SHIPPING_SECONDARY_QTY;
598
599 -- Standard call to check for call compatibility.
600 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
601 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
602 fnd_msg_pub.ADD;
603 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
604 END IF;
605
606 -- Initialize message list if p_init_msg_list is set to TRUE.
607 IF fnd_api.to_boolean(p_init_msg_list) THEN
608 fnd_msg_pub.initialize;
609 END IF;
610
611 -- Initialize API return status to success
612 x_return_status := fnd_api.g_ret_sts_success;
613
614 IF ( l_debug = 1 ) THEN
615 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
616 print_debug('deldetid='||p_delivery_detail_id||' secqty='||p_secondary_quantity||' secuom='||p_secondary_uom_code, 4);
617 END IF;
618
619 BEGIN
620 SELECT organization_id, inventory_item_id, picked_quantity, requested_quantity_uom
621 INTO l_organization_id, l_inventory_item_id, l_picked_quantity, l_requested_quantity_uom
622 FROM wsh_delivery_details
623 WHERE delivery_detail_id = p_delivery_detail_id;
624
625 IF ( l_debug = 1 ) THEN
626 print_debug('got from WDD orgid='||l_organization_id||' itemid='||l_inventory_item_id||' pkdqty='||l_picked_quantity||' requom='||l_requested_quantity_uom, 4);
627 END IF;
628 EXCEPTION
629 WHEN OTHERS THEN
630 x_return_status := fnd_api.g_ret_sts_error;
631 fnd_message.set_name('WMS', 'WMS_MISSING_WDD_ERR');
632 fnd_msg_pub.ADD;
633 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
634 END;
635
636 Get_Default_Secondary_Quantity (
637 p_api_version => 1.0
638 , x_return_status => x_return_status
639 , x_msg_count => x_msg_count
640 , x_msg_data => x_msg_data
641 , p_organization_id => l_organization_id
642 , p_inventory_item_id => l_inventory_item_id
643 , p_quantity => l_picked_quantity
644 , p_uom_code => l_requested_quantity_uom
645 , x_ont_pricing_qty_source => l_pricing_ind
646 , x_secondary_uom_code => l_secondary_uom_code
647 , x_secondary_quantity => l_secondary_quantity); -- 8655538
648
649 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
650 IF ( l_debug = 1 ) THEN
651 print_debug('Call to Get_Default_Secondary_Quantity failed', 1);
652 END IF;
653 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
654 END IF;
655
656 IF ( l_pricing_ind = G_PRICE_SECONDARY ) THEN
657 l_progress := '100';
658 -- Item is catch weight enabled.
659 IF ( l_secondary_uom_code <> p_secondary_uom_code ) THEN
660 fnd_message.set_name('WMS', 'WMS_SEC_UOM_MISMATCH');
661 fnd_message.set_token('uom1', p_secondary_uom_code);
662 fnd_message.set_token('uom2', l_secondary_uom_code);
663 fnd_msg_pub.ADD;
664 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
665 END IF;
666
667 IF ( p_secondary_quantity = FND_API.G_MISS_NUM ) THEN
668 l_progress := '200';
669
670 -- User wishes to set secondary values to null
671 l_secondary_quantity := FND_API.G_MISS_NUM;
672 l_secondary_uom_code := FND_API.G_MISS_CHAR;
673 ELSIF ( p_secondary_quantity IS NOT NULL ) THEN
674 l_progress := '300';
675
676 -- Check to make sure that the secondary qty is within tolerance
677 l_tolerance := Check_Secondary_Qty_Tolerance (
678 p_api_version => 1.0
679 , x_return_status => x_return_status
680 , x_msg_count => x_msg_count
681 , x_msg_data => x_msg_data
682 , p_organization_id => l_organization_id
683 , p_inventory_item_id => l_inventory_item_id
684 , p_quantity => l_picked_quantity
685 , p_uom_code => l_requested_quantity_uom
686 , p_secondary_quantity => p_secondary_quantity );
687
688 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
689 IF ( l_debug = 1 ) THEN
690 print_debug('Check_Secondary_Qty_Tolerance failed ', 4);
691 END IF;
692 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
693 END IF;
694
695 l_progress := '400';
696 IF ( l_tolerance <> 0 ) THEN
697 IF ( l_debug = 1 ) THEN
698 print_debug('Secondary quantity out of tolerance', 4);
699 END IF;
700 fnd_message.set_name('WMS', 'WMS_CTWT_TOLERANCE_ERROR');
701 fnd_msg_pub.ADD;
702 RAISE FND_API.G_EXC_ERROR;
703 END IF;
704
705 -- User specified sec qty, do not need to use the default value.
706 l_secondary_quantity := p_secondary_quantity;
707 ELSIF ( l_secondary_quantity IS NULL ) THEN
708 -- cannot defualt secondary quantity error out
709 IF ( l_debug = 1 ) THEN
710 print_debug('Cannot default secondary quantity', 4);
711 END IF;
712 fnd_message.set_name('WMS','WMS_CTWT_DEFAULT_ERROR');
713 fnd_msg_pub.ADD;
714 RAISE FND_API.G_EXC_ERROR;
715 END IF;
716
717 -- If everything checks out, update wdd.picked_quantity2 with catch weight.
718 l_shipping_attr(1).delivery_detail_id := p_delivery_detail_id;
719 l_shipping_attr(1).picked_quantity2 := round(l_secondary_quantity, g_precision);
720 l_shipping_attr(1).requested_quantity_uom2 := l_secondary_uom_code;
721
722 l_shipping_in_rec.caller := 'WMS';
723 l_shipping_in_rec.action_code := 'UPDATE';
724
725 WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail (
726 p_api_version_number => 1.0
727 , p_init_msg_list => fnd_api.g_false
728 , p_commit => fnd_api.g_false
729 , x_return_status => x_return_status
730 , x_msg_count => x_msg_count
731 , x_msg_data => x_msg_data
732 , p_detail_info_tab => l_shipping_attr
733 , p_IN_rec => l_shipping_in_rec
734 , x_OUT_rec => l_shipping_out_rec );
735
736 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
737 --Get error messages from shipping
738 WSH_UTIL_CORE.get_messages('Y', x_msg_data, l_msg_details, x_msg_count);
739 IF x_msg_count > 1 then
740 x_msg_data := x_msg_data || l_msg_details;
741 ELSE
742 x_msg_data := x_msg_data;
743 END IF;
744
745 IF (l_debug = 1) THEN
746 print_debug('Error calling Create_Update_Delivery_Detail: '||x_msg_data, 9);
747 END IF;
748 FND_MESSAGE.SET_NAME('WMS','WMS_UPD_DELIVERY_ERROR' );
749 fnd_message.set_token('MSG1', x_msg_data);
750 FND_MSG_PUB.ADD;
751 RAISE FND_API.g_exc_unexpected_error;
752 END IF;
753
754 l_progress := '400';
755 END IF;
756
757 -- End of API body
758 IF fnd_api.to_boolean(p_commit) THEN
759 COMMIT WORK;
760 END IF;
761
762 IF ( l_debug = 1 ) THEN
763 print_debug(l_api_name || ' Exited ', 1);
764 END IF;
765
766 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
767 EXCEPTION
768 WHEN OTHERS THEN
769 IF (l_debug = 1) THEN
770 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
771 IF ( SQLCODE IS NOT NULL ) THEN
772 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
773 END IF;
774 END IF;
775
776 ROLLBACK TO UPDATE_SHIPPING_SECONDARY_QTY;
777 x_return_status := fnd_api.g_ret_sts_error;
778 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
779 fnd_msg_pub.ADD;
780 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
781 END Update_Shipping_Secondary_Qty;
782
783
784 PROCEDURE Update_Parent_Delivery_Sec_Qty (
785 p_api_version IN NUMBER
786 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
787 , p_commit IN VARCHAR2 := fnd_api.g_false
788 , x_return_status OUT NOCOPY VARCHAR2
789 , x_msg_count OUT NOCOPY NUMBER
790 , x_msg_data OUT NOCOPY VARCHAR2
791 , p_organization_id IN NUMBER
792 , p_parent_del_det_id IN NUMBER
793 , p_inventory_item_id IN NUMBER
794 , p_revision IN VARCHAR2 := NULL
795 , p_lot_number IN VARCHAR2 := NULL
796 , p_quantity IN NUMBER
797 , p_uom_code IN VARCHAR2
798 , p_secondary_quantity IN NUMBER
799 , p_secondary_uom_code IN VARCHAR2
800 ) IS
801 l_api_name CONSTANT VARCHAR2(30) := 'Update_Parent_Delivery_Sec_Qty';
802 l_api_version CONSTANT NUMBER := 1.0;
803 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
804 l_progress VARCHAR2(10) := '0';
805
806 CURSOR wdd_cur IS
807 SELECT wdd.delivery_detail_id, wdd.picked_quantity, wdd.requested_quantity_uom,
808 wdd.picked_quantity2, wdd.requested_quantity_uom2
809 FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
810 WHERE wda.parent_delivery_detail_id = p_parent_del_det_id
811 AND wdd.delivery_detail_id = wda.delivery_detail_id
812 AND wdd.organization_id = p_organization_id
813 AND wdd.inventory_item_id = p_inventory_item_id
814 AND NVL(wdd.revision, '@') = NVL(p_revision, '@')
815 AND NVL(wdd.lot_number, '@') = NVL(p_lot_number, '@');
816
817 l_shipping_attr WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type;
818 l_shipping_in_rec WSH_INTERFACE_EXT_GRP.detailInRecType;
819 l_shipping_out_rec WSH_INTERFACE_EXT_GRP.detailOutRecType;
820 l_msg_details VARCHAR2(3000);
821 l_attr_counter NUMBER := 1;
822 l_del_det_id NUMBER;
823 l_line_quantity NUMBER;
824 l_total_quantity NUMBER := 0;
825
826 BEGIN
827 SAVEPOINT UPDATE_PARENT_DELIVERY_SEC_QTY;
828
829 -- Standard call to check for call compatibility.
830 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
831 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
832 fnd_msg_pub.ADD;
833 RAISE fnd_api.g_exc_unexpected_error;
834 END IF;
835
836 -- Initialize message list if p_init_msg_list is set to TRUE.
837 IF fnd_api.to_boolean(p_init_msg_list) THEN
838 fnd_msg_pub.initialize;
839 END IF;
840
841 -- Initialize API return status to success
842 x_return_status := fnd_api.g_ret_sts_success;
843
844 IF ( l_debug = 1 ) THEN
845 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
846 print_debug('orgid='||p_organization_id||' parddid='||p_parent_del_det_id||' itemid='||p_inventory_item_id||' rev='||p_revision||' lot='||p_lot_number, 4);
847 print_debug('qty='||p_quantity||' uom='||p_uom_code||' secqty='||p_secondary_quantity||' secuom='||p_secondary_uom_code, 4);
848 END IF;
849
850 --Updating WDD with secondary quantity information
851 FOR wdd_rec IN wdd_cur LOOP
852 IF ( l_debug = 1 ) THEN
853 print_debug('Got from WDD deldetid='||wdd_rec.delivery_detail_id||' pkqty='||wdd_rec.picked_quantity||' requom='||wdd_rec.requested_quantity_uom, 4);
854 print_debug('pkqty2='||wdd_rec.picked_quantity2||' requom2='||wdd_rec.requested_quantity_uom2, 4);
855 END IF;
856 IF ( p_secondary_quantity IS NULL ) THEN
857 -- Caller wants to null out all secondary_quantity for item/lot
858 -- but only if they are not already null
859 IF ( wdd_rec.picked_quantity2 IS NOT NULL OR
860 wdd_rec.requested_quantity_uom2 IS NOT NULL ) THEN
861 l_shipping_attr(l_attr_counter).delivery_detail_id := wdd_rec.delivery_detail_id;
862 l_shipping_attr(l_attr_counter).picked_quantity2 := FND_API.G_MISS_NUM;
863 l_shipping_attr(l_attr_counter).requested_quantity_uom2 := FND_API.G_MISS_CHAR;
864 l_attr_counter := l_attr_counter + 1;
865 END IF;
866 ELSE
867 IF ( wdd_rec.requested_quantity_uom <> p_uom_code ) THEN
868 l_line_quantity := inv_convert.inv_um_convert(
869 p_inventory_item_id
870 , 6
871 , wdd_rec.picked_quantity
872 , wdd_rec.requested_quantity_uom
873 , p_uom_code
874 , NULL
875 , NULL );
876 IF ( l_line_quantity < 0 ) THEN
877 IF (l_debug = 1) THEN
878 print_debug('Error converting to picked qty from '||wdd_rec.requested_quantity_uom||' to '||p_uom_code, 1);
879 END IF;
880 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
881 fnd_message.set_token('uom1', wdd_rec.requested_quantity_uom);
882 fnd_message.set_token('uom2', p_uom_code);
883 fnd_message.set_token('module', l_api_name);
884 fnd_msg_pub.ADD;
885 RAISE FND_API.G_EXC_ERROR;
886 END IF;
887 ELSE
888 l_line_quantity := wdd_rec.picked_quantity;
889 END IF;
890
891 l_shipping_attr(l_attr_counter).delivery_detail_id := wdd_rec.delivery_detail_id;
892 l_shipping_attr(l_attr_counter).picked_quantity2 := round(p_secondary_quantity*(l_line_quantity/p_quantity), g_precision);
893 l_shipping_attr(l_attr_counter).requested_quantity_uom2 := p_secondary_uom_code;
894 l_attr_counter := l_attr_counter + 1;
895
896 l_total_quantity := l_total_quantity + l_line_quantity;
897 END IF;
898 END LOOP;
899
900 l_progress := '100';
901 -- Do sanity check to make sure the correct qty of items is being updated before callin shipping api
902 IF ( p_secondary_quantity IS NOT NULL AND round(p_quantity, g_precision) <> round(l_total_quantity, g_precision) ) THEN
903 IF (l_debug = 1) THEN
904 print_debug('p_quantity '||p_quantity||' does not match the sum quantity '||l_total_quantity, 9);
905 END IF;
906 FND_MESSAGE.SET_NAME('WMS','WMS_QTY_UPD_MISMATCH_ERR');
907 FND_MESSAGE.SET_TOKEN('QTY1', p_quantity);
908 FND_MESSAGE.SET_TOKEN('QTY2', l_total_quantity);
909 FND_MSG_PUB.ADD;
910 RAISE FND_API.G_EXC_ERROR;
911 END IF;
912
913 l_progress := '200';
914
915 IF ( l_shipping_attr.count > 0 ) THEN
916 l_shipping_in_rec.caller := 'WMS';
917 l_shipping_in_rec.action_code := 'UPDATE';
918
919 IF (l_debug = 1) THEN
920 print_debug('Calling Create_Update_Delivery_Detail count='||l_shipping_attr.count, 9);
921 END IF;
922
923 WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail (
924 p_api_version_number => 1.0
925 , p_init_msg_list => fnd_api.g_false
926 , p_commit => fnd_api.g_false
927 , x_return_status => x_return_status
928 , x_msg_count => x_msg_count
929 , x_msg_data => x_msg_data
930 , p_detail_info_tab => l_shipping_attr
931 , p_IN_rec => l_shipping_in_rec
932 , x_OUT_rec => l_shipping_out_rec );
933
934 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
935 --Get error messages from shipping
936 WSH_UTIL_CORE.get_messages('Y', x_msg_data, l_msg_details, x_msg_count);
937 IF x_msg_count > 1 then
938 x_msg_data := x_msg_data || l_msg_details;
939 ELSE
940 x_msg_data := x_msg_data;
941 END IF;
942
943 IF (l_debug = 1) THEN
944 print_debug('Error calling Create_Update_Delivery_Detail: '||x_msg_data, 9);
945 END IF;
946 FND_MESSAGE.SET_NAME('WMS','WMS_UPD_DELIVERY_ERROR' );
947 fnd_message.set_token('MSG1', x_msg_data);
948 FND_MSG_PUB.ADD;
949 RAISE FND_API.g_exc_unexpected_error;
950 END IF;
951 END IF;
952
953 l_progress := '300';
954 -- End of API body
955 IF fnd_api.to_boolean(p_commit) THEN
956 COMMIT WORK;
957 END IF;
958
959 IF ( l_debug = 1 ) THEN
960 print_debug( l_api_name || ' Exited ', 1);
961 END IF;
962
963 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
964 EXCEPTION
965 WHEN OTHERS THEN
966 IF (l_debug = 1) THEN
967 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
968 IF ( SQLCODE IS NOT NULL ) THEN
969 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
970 END IF;
971 END IF;
972
973 ROLLBACK TO UPDATE_PARENT_DELIVERY_SEC_QTY;
974 x_return_status := fnd_api.g_ret_sts_error;
975 fnd_message.set_name('WMS', 'WMS_API_FAIL');
976 fnd_msg_pub.ADD;
977 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
978 END Update_Parent_Delivery_Sec_Qty;
979
980
981 FUNCTION wdd_src_line_id RETURN numset_t PIPELINED
982 IS
983 l_api_name VARCHAR2(30) := 'wdd_src_line_id';
984 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
985 l_count NUMBER;
986 BEGIN
987 l_count := g_src_line_id.COUNT;
988 FOR i IN 1..l_count LOOP
989 PIPE ROW(g_src_line_id(i));
990 END LOOP;
991 RETURN;
992 EXCEPTION
993 WHEN NO_DATA_NEEDED THEN
994 RETURN;
995 WHEN OTHERS THEN
996 IF (l_debug = 1) THEN
997 print_debug('Error occurred: ' || SQLERRM, 4);
998 END IF;
999 RETURN;
1000 END wdd_src_line_id;
1001
1002
1003 PROCEDURE process_ctwt_adj (
1004 x_return_status OUT NOCOPY VARCHAR2
1005 , x_msg_count OUT NOCOPY NUMBER
1006 , x_msg_data OUT NOCOPY VARCHAR2
1007 , p_ctwt_adj_type IN VARCHAR2
1008 , p_organization_id IN NUMBER
1009 , p_subinv_code IN VARCHAR2
1010 , p_locator_id IN NUMBER
1011 , p_lpn_id IN NUMBER
1012 , p_inventory_item_id IN NUMBER
1013 , p_revision IN VARCHAR2 := NULL
1014 , p_lot_number IN VARCHAR2 := NULL
1015 , p_pri_qty IN NUMBER
1016 , p_pri_uom_code IN VARCHAR2
1017 , p_sec_uom_code IN VARCHAR2
1018 ) IS
1019
1020 l_api_name CONSTANT VARCHAR2(30) := 'process_ctwt_adj';
1021 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1022
1023 l_txn_temp_id NUMBER;
1024 l_txn_hdr_id NUMBER;
1025 l_txn_type_id NUMBER;
1026 l_txn_action_id NUMBER;
1027 l_txn_src_typ_id NUMBER;
1028
1029 l_adj_account_id NUMBER;
1030 l_txn_date DATE;
1031 l_acct_period_id NUMBER;
1032
1033 l_loc_ctrl_code NUMBER;
1034 l_rev_ctrl_code NUMBER;
1035 l_lot_ctrl_code NUMBER;
1036 l_srl_ctrl_code NUMBER;
1037
1038 l_orig_lpn_ctx NUMBER;
1039 l_new_lpn_ctx NUMBER;
1040
1041 l_return_val NUMBER;
1042 l_message VARCHAR2(2000);
1043
1044 BEGIN
1045 x_return_status := fnd_api.g_ret_sts_success;
1046
1047 IF (l_debug = 1) THEN
1048 print_debug('Entered ' || l_api_name ||
1049 ': p_ctwt_adj_type: ' || p_ctwt_adj_type ||
1050 ', p_organization_id: ' || p_organization_id ||
1051 ', p_subinv_code: ' || p_subinv_code ||
1052 ', p_locator_id: ' || p_locator_id ||
1053 ', p_lpn_id: ' || p_lpn_id ||
1054 ', p_inventory_item_id: ' || p_inventory_item_id ||
1055 ', p_revision: ' || p_revision ||
1056 ', p_lot_number: ' || p_lot_number ||
1057 ', p_pri_qty: ' || p_pri_qty ||
1058 ', p_pri_uom_code: ' || p_pri_uom_code ||
1059 ', p_sec_uom_code: ' || p_sec_uom_code
1060 , 4);
1061 END IF;
1062
1063 IF p_pri_qty <= 0 THEN
1064 RETURN;
1065 END IF;
1066
1067 SAVEPOINT process_ctwt_sp;
1068
1069 SELECT mtl_material_transactions_s.NEXTVAL
1070 INTO l_txn_temp_id
1071 FROM dual;
1072
1073 SELECT mtl_material_transactions_s.NEXTVAL
1074 INTO l_txn_hdr_id
1075 FROM dual;
1076
1077 IF p_lpn_id IS NOT NULL THEN
1078 SELECT lpn_context
1079 INTO l_orig_lpn_ctx
1080 FROM wms_license_plate_numbers
1081 WHERE lpn_id = p_lpn_id;
1082 END IF;
1083
1084 IF p_ctwt_adj_type = 'ISSUE' THEN
1085 l_txn_type_id := 1009;
1086 l_txn_action_id := inv_globals.g_action_issue;
1087 ELSIF p_ctwt_adj_type = 'RECEIPT' THEN
1088 l_txn_type_id := 1008;
1089 l_txn_action_id := inv_globals.g_action_receipt;
1090 ELSE
1091 IF (l_debug = 1) THEN
1092 print_debug('Unknown adjustment type: ' || p_ctwt_adj_type, 4);
1093 END IF;
1094 RAISE fnd_api.g_exc_unexpected_error;
1095 END IF;
1096 l_txn_src_typ_id := inv_globals.g_sourcetype_inventory;
1097
1098 SELECT cat_wt_account
1099 INTO l_adj_account_id
1100 FROM mtl_parameters
1101 WHERE organization_id = p_organization_id;
1102
1103 l_txn_date := SYSDATE;
1104 l_acct_period_id := inv_txn_manager_grp.get_open_period( p_organization_id
1105 , l_txn_date
1106 , 0
1107 );
1108
1109 IF inv_cache.set_item_rec(p_organization_id,p_inventory_item_id) THEN
1110 l_loc_ctrl_code := inv_cache.item_rec.location_control_code;
1111 l_rev_ctrl_code := inv_cache.item_rec.revision_qty_control_code;
1112 l_lot_ctrl_code := inv_cache.item_rec.lot_control_code;
1113 l_srl_ctrl_code := inv_cache.item_rec.serial_number_control_code;
1114 END IF;
1115
1116 IF (l_debug = 1) THEN
1117 print_debug(' l_txn_temp_id: ' || l_txn_temp_id ||
1118 ', l_txn_hdr_id: ' || l_txn_hdr_id ||
1119 ', l_txn_type_id: ' || l_txn_type_id ||
1120 ', l_txn_action_id: ' || l_txn_action_id ||
1121 ', l_txn_src_typ_id: ' || l_txn_src_typ_id ||
1122 ', l_adj_account_id: ' || l_adj_account_id ||
1123 ', l_txn_date: ' || TO_CHAR(l_txn_date,'DD-MON-YYYY HH24:MI:SS') ||
1124 ', l_acct_period_id: ' || l_acct_period_id ||
1125 ', l_loc_ctrl_code: ' || l_loc_ctrl_code ||
1126 ', l_rev_ctrl_code: ' || l_rev_ctrl_code ||
1127 ', l_lot_ctrl_code: ' || l_lot_ctrl_code ||
1128 ', l_srl_ctrl_code: ' || l_srl_ctrl_code ||
1129 ', l_orig_lpn_ctx: ' || l_orig_lpn_ctx
1130 , 4);
1131 END IF;
1132
1133 INSERT INTO mtl_material_transactions_temp(
1134 transaction_header_id
1135 , transaction_temp_id
1136 , last_update_date
1137 , last_updated_by
1138 , creation_date
1139 , created_by
1140 , last_update_login
1141 , inventory_item_id
1142 , revision
1143 , organization_id
1144 , subinventory_code
1145 , locator_id
1146 , transaction_quantity
1147 , transaction_uom
1148 , primary_quantity
1149 , item_primary_uom_code
1150 , transaction_type_id
1151 , transaction_action_id
1152 , transaction_source_type_id
1153 , transaction_source_id
1154 , transaction_date
1155 , acct_period_id
1156 , distribution_account_id
1157 , item_location_control_code
1158 , item_revision_qty_control_code
1159 , item_lot_control_code
1160 , item_serial_control_code
1161 , posting_flag
1162 , process_flag
1163 , lpn_id
1164 , transfer_lpn_id
1165 , secondary_uom_code
1166 , secondary_transaction_quantity
1167 )
1168 VALUES ( l_txn_hdr_id -- TRANSACTION_HEADER_ID
1169 , l_txn_temp_id -- TRANSACTION_TEMP_ID
1170 , l_txn_date -- LAST_UPDATE_DATE
1171 , fnd_global.user_id -- LAST_UPDATED_BY
1172 , l_txn_date -- CREATION_DATE
1173 , fnd_global.user_id -- CREATED_BY
1174 , fnd_global.user_id -- LAST_UPDATE_LOGIN
1175 , p_inventory_item_id -- INVENTORY_ITEM_ID
1176 , p_revision -- REVISION
1177 , p_organization_id -- ORGANIZATION_ID
1178 , p_subinv_code -- SUBINVENTORY_CODE
1179 , p_locator_id -- LOCATOR_ID
1180 , p_pri_qty -- TRANSACTION_QUANTITY
1181 , p_pri_uom_code -- TRANSACTION_UOM
1182 , p_pri_qty -- PRIMARY_QUANTITY
1183 , p_pri_uom_code -- ITEM_PRIMARY_UOM_CODE
1184 , l_txn_type_id -- TRANSACTION_TYPE_ID
1185 , l_txn_action_id -- TRANSACTION_ACTION_ID
1186 , l_txn_src_typ_id -- TRANSACTION_SOURCE_TYPE_ID
1187 , l_adj_account_id -- TRANSACTION_SOURCE_ID
1188 , l_txn_date -- TRANSACTION_DATE
1189 , l_acct_period_id -- ACCT_PERIOD_ID
1190 , l_adj_account_id -- DISTRIBUTION_ACCOUNT_ID
1191 , l_loc_ctrl_code -- ITEM_LOCATION_CONTROL_CODE
1192 , l_rev_ctrl_code -- ITEM_REVISION_QTY_CONTROL_CODE
1193 , l_lot_ctrl_code -- ITEM_LOT_CONTROL_CODE
1194 , l_srl_ctrl_code -- ITEM_SERIAL_CONTROL_CODE
1195 , 'Y' -- POSTING_FLAG
1196 , 'Y' -- PROCESS_FLAG
1197 , DECODE ( p_ctwt_adj_type
1198 , 'ISSUE', p_lpn_id
1199 , 'RECEIPT', NULL
1200 , NULL
1201 ) -- LPN_ID
1202 , DECODE ( p_ctwt_adj_type
1203 , 'ISSUE', NULL
1204 , 'RECEIPT', p_lpn_id
1205 , NULL
1206 ) -- TRANSFER_LPN_ID
1207 , p_sec_uom_code -- SECONDARY_UOM_CODE
1208 , 0 -- SECONDARY_TRANSACTION_QUANTITY
1209 );
1210
1211 IF p_lot_number IS NOT NULL THEN
1212 INSERT INTO mtl_transaction_lots_temp(
1213 transaction_temp_id
1214 , last_update_date
1215 , last_updated_by
1216 , creation_date
1217 , created_by
1218 , last_update_login
1219 , transaction_quantity
1220 , primary_quantity
1221 , lot_number
1222 , secondary_quantity
1223 )
1224 VALUES ( l_txn_temp_id -- TRANSACTION_TEMP_ID
1225 , l_txn_date -- LAST_UPDATE_DATE
1226 , fnd_global.user_id -- LAST_UPDATED_BY
1227 , l_txn_date -- CREATION_DATE
1228 , fnd_global.user_id -- CREATED_BY
1229 , fnd_global.user_id -- LAST_UPDATE_LOGIN
1230 , p_pri_qty -- TRANSACTION_QUANTITY
1231 , p_pri_qty -- PRIMARY_QUANTITY
1232 , p_lot_number -- LOT_NUMBER
1233 , 0 -- SECONDARY_QUANTITY
1234 );
1235 END IF;
1236
1237 -- Call the TM
1238 l_return_val := inv_lpn_trx_pub.process_lpn_trx(
1239 p_trx_hdr_id => l_txn_hdr_id
1240 , x_proc_msg => l_message
1241 , p_proc_mode => 1);
1242
1243 IF (l_return_val = 0) THEN
1244 IF (l_debug = 1) THEN
1245 print_debug('Transaction processed successfully.', 4);
1246 END IF;
1247 ELSE
1248 IF (l_debug = 1) THEN
1249 print_debug('Error from TM: '|| l_message, 4);
1250 END IF;
1251 x_return_status := fnd_api.g_ret_sts_error;
1252 x_msg_data := l_message;
1253 RAISE fnd_api.g_exc_error;
1254 END IF;
1255
1256 IF p_lpn_id IS NOT NULL THEN
1257 SELECT lpn_context
1258 INTO l_new_lpn_ctx
1259 FROM wms_license_plate_numbers
1260 WHERE lpn_id = p_lpn_id;
1261
1262 IF (l_debug = 1) THEN
1263 print_debug('l_new_lpn_ctx: '|| l_new_lpn_ctx, 4);
1264 END IF;
1265
1266 IF l_new_lpn_ctx <> l_orig_lpn_ctx THEN
1267 UPDATE wms_license_plate_numbers
1268 SET lpn_context = l_orig_lpn_ctx
1269 WHERE lpn_id = p_lpn_id;
1270
1271 IF (l_debug = 1) THEN
1272 print_debug('Restore LPN context to: '|| l_orig_lpn_ctx, 4);
1273 END IF;
1274 END IF;
1275 END IF;
1276
1277 EXCEPTION
1278 WHEN fnd_api.g_exc_error THEN
1279 IF (l_debug = 1) THEN
1280 print_debug('fnd_api.g_exc_error', 1);
1281 END IF;
1282 ROLLBACK TO process_ctwt_sp;
1283
1284 WHEN OTHERS THEN
1285 IF (l_debug = 1) THEN
1286 print_debug('SQL error: ' || SQLERRM, 1);
1287 END IF;
1288
1289 ROLLBACK TO process_ctwt_sp;
1290 x_return_status := fnd_api.g_ret_sts_error;
1291 x_msg_data := SQLERRM;
1292
1293 END process_ctwt_adj;
1294
1295
1296
1297 PROCEDURE update_lpn_primary_quantity (
1298 p_api_version IN NUMBER
1299 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1300 , p_commit IN VARCHAR2 := fnd_api.g_false
1301 , x_return_status OUT NOCOPY VARCHAR2
1302 , x_msg_count OUT NOCOPY NUMBER
1303 , x_msg_data OUT NOCOPY VARCHAR2
1304 , p_record_source IN VARCHAR2
1305 , p_organization_id IN NUMBER
1306 , p_lpn_id IN NUMBER
1307 , p_inventory_item_id IN NUMBER
1308 , p_revision IN VARCHAR2 := NULL
1309 , p_lot_number IN VARCHAR2 := NULL
1310 , p_quantity IN NUMBER
1311 , p_uom_code IN VARCHAR2
1312 , p_secondary_quantity IN NUMBER
1313 , p_secondary_uom_code IN VARCHAR2
1314 , p_max_pri_residual IN NUMBER
1315 , p_ccnt_sec_residual IN VARCHAR2
1316 ) IS
1317
1318 l_api_name CONSTANT VARCHAR2(30) := 'update_lpn_primary_quantity';
1319 l_api_version CONSTANT NUMBER := 1.0;
1320 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1321
1322 CURSOR c_ds_lpn_qty IS
1323 SELECT SUM(wlc.primary_quantity)
1324 , SUM(wlc.secondary_quantity)
1325 FROM wms_license_plate_numbers wlpn
1326 , wms_lpn_contents wlc
1327 WHERE wlpn.lpn_id = p_lpn_id
1328 AND wlpn.lpn_context = 1
1329 AND wlc.parent_lpn_id = wlpn.lpn_id
1330 AND wlc.inventory_item_id = p_inventory_item_id
1331 AND NVL(wlc.revision,'@@@') = NVL(p_revision,'@@@')
1332 AND NVL(wlc.lot_number,'@@@') = NVL(p_lot_number,'@@@');
1333
1334 CURSOR c_ds_lpn_rsvs IS
1335 SELECT reservation_id
1336 FROM mtl_reservations
1337 WHERE demand_source_type_id IN (2,8)
1338 AND lpn_id = p_lpn_id
1339 AND organization_id = p_organization_id
1340 AND inventory_item_id = p_inventory_item_id
1341 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
1342 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
1343 AND NVL(staged_flag,'N') = 'N';
1344
1345 CURSOR c_stg_lpn_qty IS
1346 SELECT SUM(wlc.primary_quantity)
1347 , SUM(wlc.secondary_quantity)
1348 FROM wms_license_plate_numbers wlpn
1349 , wms_lpn_contents wlc
1350 WHERE wlpn.lpn_id = p_lpn_id
1351 AND wlpn.lpn_context = 11
1352 AND wlc.parent_lpn_id = wlpn.lpn_id
1353 AND wlc.inventory_item_id = p_inventory_item_id
1354 AND NVL(wlc.revision,'@@@') = NVL(p_revision,'@@@')
1355 AND NVL(wlc.lot_number,'@@@') = NVL(p_lot_number,'@@@');
1356
1357 CURSOR c_get_wdds IS
1358 SELECT wdd2.delivery_detail_id
1359 , wdd2.source_line_id
1360 , wdd2.picked_quantity
1361 , wdd2.shipped_quantity
1362 , DECODE( wdd2.requested_quantity_uom
1363 , msi.primary_uom_code, wdd2.picked_quantity
1364 , inv_convert.inv_um_convert( wdd2.inventory_item_id
1365 , wdd2.lot_number
1366 , wdd2.organization_id
1367 , NULL
1368 , wdd2.picked_quantity
1369 , wdd2.requested_quantity_uom
1370 , msi.primary_uom_code
1371 , NULL
1372 , NULL
1373 )
1374 ) primary_picked_qty
1375 , wdd2.requested_quantity_uom
1376 FROM wms_license_plate_numbers wlpn
1377 , wsh_delivery_details wdd1
1378 , wsh_delivery_assignments_v wda
1379 , wsh_delivery_details wdd2
1380 , mtl_system_items msi
1381 WHERE wlpn.lpn_id = p_lpn_id
1382 AND wlpn.lpn_context = 11
1383 AND wdd1.organization_id = p_organization_id
1384 AND wdd1.lpn_id = p_lpn_id
1385 AND wdd1.released_status = 'X'
1386 AND wda.parent_delivery_detail_id = wdd1.delivery_detail_id
1387 AND wdd2.delivery_detail_id = wda.delivery_detail_id
1388 AND wdd2.organization_id = p_organization_id
1389 AND wdd2.inventory_item_id = p_inventory_item_id
1390 AND NVL(wdd2.revision,'@@@') = NVL(p_revision,'@@@')
1391 AND NVL(wdd2.lot_number,'@@@') = NVL(p_lot_number,'@@@')
1392 AND msi.organization_id = wdd2.organization_id
1393 AND msi.inventory_item_id = wdd2.inventory_item_id;
1394
1395 CURSOR c_get_lpn_rsvs IS
1396 SELECT reservation_id
1397 FROM mtl_reservations
1398 WHERE demand_source_type_id IN (2,8)
1399 AND demand_source_line_id IN
1400 (SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
1401 AND lpn_id = p_lpn_id
1402 AND organization_id = p_organization_id
1403 AND inventory_item_id = p_inventory_item_id
1404 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
1405 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
1406 AND NVL(staged_flag,'N') = 'Y';
1407
1408 CURSOR c_lpn_lot_pri_qty IS
1409 SELECT SUM(mtlt.primary_quantity)
1410 , SUM(mtlt.secondary_quantity)
1411 FROM mtl_material_transactions_temp mmtt
1412 , mtl_transaction_lots_temp mtlt
1413 WHERE (mmtt.content_lpn_id = p_lpn_id
1414 OR mmtt.transfer_lpn_id = p_lpn_id)
1415 AND mmtt.inventory_item_id = p_inventory_item_id
1416 AND mmtt.parent_line_id IS NULL
1417 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1418 AND NVL(mmtt.revision,'@@@') = NVL(p_revision,'@@@')
1419 AND mtlt.lot_number = p_lot_number;
1420
1421 CURSOR c_lpn_pri_qty IS
1422 SELECT SUM(mmtt.primary_quantity)
1423 , SUM(mmtt.secondary_transaction_quantity)
1424 FROM mtl_material_transactions_temp mmtt
1425 WHERE (mmtt.content_lpn_id = p_lpn_id
1426 OR mmtt.transfer_lpn_id = p_lpn_id)
1427 AND mmtt.inventory_item_id = p_inventory_item_id
1428 AND mmtt.parent_line_id IS NULL
1429 AND NVL(mmtt.revision,'@@@') = NVL(p_revision,'@@@');
1430
1431 CURSOR c_lpn_mtlt IS
1432 SELECT mtlt.transaction_temp_id
1433 , mmtt.subinventory_code
1434 , mmtt.locator_id
1435 , mmtt.lpn_id
1436 , mmtt.content_lpn_id
1437 , mmtt.reservation_id
1438 , mmtt.move_order_line_id
1439 , mtlt.primary_quantity
1440 , mtlt.secondary_quantity
1441 FROM mtl_material_transactions_temp mmtt
1442 , mtl_transaction_lots_temp mtlt
1443 WHERE (mmtt.content_lpn_id = p_lpn_id
1444 OR mmtt.transfer_lpn_id = p_lpn_id)
1445 AND mmtt.inventory_item_id = p_inventory_item_id
1446 AND mmtt.parent_line_id IS NULL
1447 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
1448 AND NVL(mmtt.revision,'@@@') = NVL(p_revision,'@@@')
1449 AND mtlt.lot_number = p_lot_number;
1450
1451 CURSOR c_lpn_mmtt IS
1452 SELECT mmtt.transaction_temp_id
1453 , mmtt.subinventory_code
1454 , mmtt.locator_id
1455 , mmtt.lpn_id
1456 , mmtt.content_lpn_id
1457 , mmtt.reservation_id
1458 , mmtt.move_order_line_id
1459 , mmtt.primary_quantity
1460 , mmtt.secondary_transaction_quantity
1461 FROM mtl_material_transactions_temp mmtt
1462 WHERE (mmtt.content_lpn_id = p_lpn_id
1463 OR mmtt.transfer_lpn_id = p_lpn_id)
1464 AND mmtt.inventory_item_id = p_inventory_item_id
1465 AND mmtt.parent_line_id IS NULL
1466 AND NVL(mmtt.revision,'@@@') = NVL(p_revision,'@@@');
1467
1468 CURSOR c_get_mmtt_rsv(p_rsv_id IN NUMBER) IS
1469 SELECT reservation_quantity
1470 , reservation_uom_code
1471 , wms_catch_weight_pvt.get_uom_class(reservation_uom_code) rsv_uom_class
1472 , primary_reservation_quantity
1473 , detailed_quantity
1474 , wms_catch_weight_pvt.get_uom_class(secondary_uom_code) sec_uom_class
1475 FROM mtl_reservations
1476 WHERE reservation_id = p_rsv_id
1477 FOR UPDATE WAIT 5;
1478
1479 l_mmtt_rsv_rec c_get_mmtt_rsv%ROWTYPE;
1480
1481 CURSOR c_get_rsv_dtl(p_rsv_id IN NUMBER) IS
1482 SELECT primary_reservation_quantity
1483 , detailed_quantity
1484 FROM mtl_reservations
1485 WHERE reservation_id = p_rsv_id
1486 FOR UPDATE WAIT 5;
1487
1488 l_rsv_dtl_rec c_get_rsv_dtl%ROWTYPE;
1489
1490 CURSOR c_lot_rsv_qty(p_rsv_id IN NUMBER) IS
1491 SELECT NVL(SUM(mtlt.primary_quantity),0)
1492 FROM mtl_material_transactions_temp mmtt
1493 , mtl_transaction_lots_temp mtlt
1494 , mtl_lot_numbers mln
1495 , mtl_secondary_inventories msi
1496 , mtl_item_locations mil
1497 WHERE mmtt.reservation_id = p_rsv_id
1498 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1499 AND mln.organization_id = p_organization_id
1500 AND mln.inventory_item_id = p_inventory_item_id
1501 AND mln.lot_number = mtlt.lot_number
1502 AND NVL(mln.reservable_type,1) = 1
1503 AND NVL(mln.expiration_date,SYSDATE + 1) > SYSDATE
1504 AND msi.organization_id = mmtt.organization_id
1505 AND msi.secondary_inventory_name = mmtt.subinventory_code
1506 AND NVL(msi.reservable_type,1) = 1
1507 AND mil.organization_id = mmtt.organization_id
1508 AND mil.inventory_location_id = mmtt.locator_id
1509 AND NVL(mil.reservable_type,1) = 1;
1510
1511 CURSOR c_rsv_qty(p_rsv_id IN NUMBER) IS
1512 SELECT NVL(SUM(mmtt.primary_quantity),0)
1513 FROM mtl_material_transactions_temp mmtt
1514 , mtl_secondary_inventories msi
1515 , mtl_item_locations mil
1516 WHERE mmtt.reservation_id = p_rsv_id
1517 AND msi.organization_id = mmtt.organization_id
1518 AND msi.secondary_inventory_name = mmtt.subinventory_code
1519 AND NVL(msi.reservable_type,1) = 1
1520 AND mil.organization_id = mmtt.organization_id
1521 AND mil.inventory_location_id = mmtt.locator_id
1522 AND NVL(mil.reservable_type,1) = 1;
1523
1524 CURSOR c_onh_stat_rsv_qty(p_rsv_id IN NUMBER) IS
1525 SELECT NVL(SUM(mmtt.primary_quantity),0)
1526 FROM mtl_material_transactions_temp mmtt
1527 WHERE mmtt.reservation_id = p_rsv_id
1528 AND EXISTS ( SELECT 'x'
1529 FROM mtl_onhand_quantities_detail moqd
1530 , mtl_material_statuses mms
1531 WHERE moqd.organization_id = mmtt.organization_id
1532 AND moqd.subinventory_code = mmtt.subinventory_code
1533 AND moqd.locator_id = mmtt.locator_id
1534 AND moqd.inventory_item_id = mmtt.inventory_item_id
1535 AND NVL(moqd.revision,'@@@') = NVL(p_revision,'@@@')
1536 AND NVL(moqd.lot_number,'@@@') = NVL(p_lot_number,'@@@')
1537 AND NVL(moqd.lpn_id, -9999) = NVL(mmtt.lpn_id, -9999)
1538 AND moqd.status_id = mms.status_id
1539 AND NVL(mms.reservable_type, 1) = 1
1540 );
1541
1542
1543 l_onh_status BOOLEAN := FALSE;
1544 l_pr_uom_code VARCHAR2(3);
1545 l_lot_control_code NUMBER := 1;
1546 l_lot_divisible_flag VARCHAR2(1) := 'Y';
1547
1548 l_max_pri_residual NUMBER;
1549 l_primary_qty NUMBER;
1550 l_lpn_pri_qty NUMBER;
1551 l_lpn_sec_qty NUMBER;
1552 l_lpn_pck_qty NUMBER;
1553
1554 l_pri_qty_to_reduce NUMBER := 0;
1555 l_rem_pri_qty_decr NUMBER := 0;
1556 l_pri_qty_to_incr NUMBER := 0;
1557 l_rem_pri_qty_incr NUMBER := 0;
1558
1559 t_wdd_id tbl_num;
1560 t_temp_id tbl_num;
1561 t_subinv tbl_v10;
1562 t_loc_id tbl_num;
1563 t_lpn_id tbl_num;
1564 t_clpn_id tbl_num;
1565 t_rsv_id tbl_num;
1566 t_mol_id tbl_num;
1567 t_pck_qty tbl_num;
1568 t_shp_qty tbl_num;
1569 t_pri_qty tbl_num;
1570 t_req_uom tbl_v3;
1571 t_sec_qty tbl_num;
1572 t_proc_rsvs tbl_v1;
1573
1574 l_api_return_status VARCHAR2(1);
1575 l_wdd_qty_incr NUMBER;
1576 l_wdd_qty_decr NUMBER;
1577
1578 l_shipping_attr WSH_INTERFACE_EXT_GRP.delivery_details_attr_tbl_type;
1579 l_shipping_in_rec WSH_INTERFACE_EXT_GRP.detailinrectype;
1580 l_shipping_out_rec WSH_INTERFACE_EXT_GRP.detailoutrectype;
1581 l_wdd_index NUMBER;
1582 l_msg_details VARCHAR2(3000);
1583
1584 l_subinv_code VARCHAR2(10);
1585 l_sub_reservable NUMBER;
1586 l_locator_id NUMBER;
1587 l_lpn_rsv_pri_qty NUMBER;
1588 l_nonlpn_rsv_pri_qty NUMBER;
1589 l_rsv_rec inv_reservation_global.mtl_reservation_rec_type;
1590 l_original_serial_number inv_reservation_global.serial_number_tbl_type;
1591
1592 l_tree_mode INTEGER;
1593 l_rev_ctrl BOOLEAN;
1594 l_lot_ctrl BOOLEAN;
1595
1596 l_qoh NUMBER;
1597 l_rqoh NUMBER;
1598 l_qr NUMBER;
1599 l_qs NUMBER;
1600 l_att NUMBER;
1601 l_atr NUMBER;
1602 l_sqoh NUMBER;
1603 l_srqoh NUMBER;
1604 l_sqr NUMBER;
1605 l_sqs NUMBER;
1606 l_satt NUMBER;
1607 l_satr NUMBER;
1608
1609 l_sku_pri_incr NUMBER;
1610 l_sku_pri_decr NUMBER;
1611 l_pri_increase NUMBER;
1612 l_pri_decrease NUMBER;
1613
1614 l_tot_rsv_pri_qty NUMBER;
1615 l_tot_rsv_qty NUMBER;
1616
1617 BEGIN
1618 SAVEPOINT update_lpn_primary_qty_sp;
1619
1620 -- Standard call to check for call compatibility.
1621 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1622 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1623 fnd_msg_pub.ADD;
1624 RAISE fnd_api.g_exc_unexpected_error;
1625 END IF;
1626
1627 -- Initialize message list if p_init_msg_list is set to TRUE.
1628 IF fnd_api.to_boolean(p_init_msg_list) THEN
1629 fnd_msg_pub.initialize;
1630 END IF;
1631
1632 -- Initialize API return status to success
1633 x_return_status := fnd_api.g_ret_sts_success;
1634
1635 t_wdd_id.DELETE;
1636 g_src_line_id.DELETE;
1637 t_temp_id.DELETE;
1638 t_subinv.DELETE;
1639 t_loc_id.DELETE;
1640 t_lpn_id.DELETE;
1641 t_clpn_id.DELETE;
1642 t_rsv_id.DELETE;
1643 t_mol_id.DELETE;
1644 t_pck_qty.DELETE;
1645 t_shp_qty.DELETE;
1646 t_pri_qty.DELETE;
1647 t_req_uom.DELETE;
1648 t_sec_qty.DELETE;
1649 t_proc_rsvs.DELETE;
1650
1651 l_shipping_attr.DELETE;
1652
1653 IF ( l_debug = 1 ) THEN
1654 print_debug('Entered ' || l_api_name ||
1655 ': p_record_source=' || p_record_source ||
1656 ', p_organization_id=' || p_organization_id ||
1657 ', p_lpn_id=' || p_lpn_id ||
1658 ', p_inventory_item_id=' || p_inventory_item_id ||
1659 ', p_revision=' || p_revision ||
1660 ', p_lot_number=' || p_lot_number ||
1661 ', p_quantity=' || p_quantity ||
1662 ', p_uom_code=' || p_uom_code ||
1663 ', p_secondary_quantity=' || p_secondary_quantity ||
1664 ', p_secondary_uom_code=' || p_secondary_uom_code ||
1665 ', p_max_pri_residual=' || p_max_pri_residual ||
1666 ', p_ccnt_sec_residual=' || p_ccnt_sec_residual
1667 , 4);
1668 END IF;
1669
1670 IF ROUND(NVL(p_max_pri_residual,0),5) > 0 THEN
1671 l_max_pri_residual := ROUND(p_max_pri_residual,5);
1672 ELSE
1673 l_max_pri_residual := 0;
1674 END IF;
1675
1676 IF p_revision IS NOT NULL THEN
1677 l_rev_ctrl := TRUE;
1678 ELSE
1679 l_rev_ctrl := FALSE;
1680 END IF;
1681
1682 IF p_lot_number IS NOT NULL THEN
1683 l_lot_ctrl := TRUE;
1684 ELSE
1685 l_lot_ctrl := FALSE;
1686 END IF;
1687
1688 IF inv_cache.set_org_rec(p_organization_id) THEN
1689 IF inv_cache.org_rec.default_status_id IS NOT NULL THEN
1690 l_onh_status := TRUE;
1691 END IF;
1692 END IF;
1693
1694 IF (l_debug = 1) THEN
1695 IF l_onh_status THEN
1696 print_debug('Org has onhand status enabled.', 4);
1697 ELSE
1698 print_debug('Onhand status not enabled.', 4);
1699 END IF;
1700 END IF;
1701
1702 IF inv_cache.set_item_rec(p_organization_id,p_inventory_item_id) THEN
1703 l_pr_uom_code := inv_cache.item_rec.primary_uom_code;
1704
1705 l_lot_control_code := inv_cache.item_rec.lot_control_code;
1706 IF l_lot_control_code <> 1 THEN
1707 l_lot_divisible_flag := inv_cache.item_rec.lot_divisible_flag;
1708 END IF;
1709
1710 IF l_pr_uom_code = p_uom_code THEN
1711 l_primary_qty := ROUND(p_quantity,5);
1712 ELSE
1713 l_primary_qty := inv_convert.inv_um_convert(
1714 item_id => p_inventory_item_id
1715 , lot_number => p_lot_number
1716 , organization_id => p_organization_id
1717 , precision => NULL
1718 , from_quantity => p_quantity
1719 , from_unit => p_uom_code
1720 , to_unit => l_pr_uom_code
1721 , from_name => NULL
1722 , to_name => NULL
1723 );
1724 IF (l_primary_qty = -99999) THEN
1725 IF (l_debug = 1) THEN
1726 print_debug('UOM conversion error', 4);
1727 END IF;
1728 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
1729 fnd_msg_pub.ADD;
1730 RAISE fnd_api.g_exc_error;
1731 END IF;
1732 END IF;
1733 END IF;
1734
1735 IF (l_debug = 1) THEN
1736 print_debug('l_max_pri_residual: ' || l_max_pri_residual, 4);
1737 print_debug('l_primary_qty: ' || l_primary_qty, 4);
1738 print_debug('l_pr_uom_code: ' || l_pr_uom_code, 4);
1739 END IF;
1740
1741 IF ( UPPER(p_record_source) = 'DS' ) THEN --{
1742 -- Direct Ship
1743 OPEN c_ds_lpn_qty;
1744 FETCH c_ds_lpn_qty INTO l_lpn_pri_qty, l_lpn_sec_qty;
1745 CLOSE c_ds_lpn_qty;
1746
1747 IF (l_debug = 1) THEN
1748 print_debug('l_lpn_pri_qty: ' || l_lpn_pri_qty, 4);
1749 print_debug('l_lpn_sec_qty: ' || l_lpn_sec_qty, 4);
1750 END IF;
1751
1752 IF l_lpn_pri_qty <= 0 THEN
1753 IF (l_debug = 1) THEN
1754 print_debug('l_lpn_pri_qty is 0 or -ve, erroring out', 4);
1755 END IF;
1756 RAISE fnd_api.g_exc_unexpected_error;
1757 END IF;
1758
1759 IF l_lpn_sec_qty <> p_secondary_quantity THEN
1760 IF (l_debug = 1) THEN
1761 print_debug('l_lpn_sec_qty does not match passed-in secondary', 4);
1762 END IF;
1763 RAISE fnd_api.g_exc_unexpected_error;
1764 END IF;
1765
1766 IF l_lpn_pri_qty > l_primary_qty THEN
1767 -- Reduce WDD qty to match entered primary qty
1768 l_pri_qty_to_reduce := l_lpn_pri_qty - l_primary_qty;
1769 ELSIF l_lpn_pri_qty < l_primary_qty THEN
1770 -- Increase WDD qty to match entered primary qty
1771 l_pri_qty_to_incr := l_primary_qty - l_lpn_pri_qty;
1772 END IF;
1773
1774 IF (l_debug = 1) THEN
1775 print_debug('l_pri_qty_to_reduce: ' || l_pri_qty_to_reduce, 4);
1776 print_debug('l_pri_qty_to_incr: ' || l_pri_qty_to_incr, 4);
1777 END IF;
1778
1779 IF l_pri_qty_to_reduce > 0 OR l_pri_qty_to_incr > 0
1780 THEN --{ qty adjustment required
1781 BEGIN
1782 SELECT wlpn.subinventory_code
1783 , sub.reservable_type
1784 , wlpn.locator_id
1785 INTO l_subinv_code
1786 , l_sub_reservable
1787 , l_locator_id
1788 FROM wms_license_plate_numbers wlpn
1789 , mtl_secondary_inventories sub
1790 WHERE wlpn.lpn_id = p_lpn_id
1791 AND sub.organization_id = wlpn.organization_id
1792 AND sub.secondary_inventory_name = wlpn.subinventory_code;
1793 EXCEPTION
1794 WHEN OTHERS THEN
1795 IF (l_debug = 1) THEN
1796 print_debug('Error querying LPN sub/loc, sub reservable type: ' || SQLERRM, 4);
1797 END IF;
1798 RAISE FND_API.g_exc_unexpected_error;
1799 END;
1800
1801 IF (l_debug = 1) THEN
1802 print_debug('l_subinv_code: ' || l_subinv_code, 4);
1803 print_debug('l_sub_reservable: ' || l_sub_reservable, 4);
1804 print_debug('l_locator_id: ' || l_locator_id, 4);
1805 END IF;
1806
1807 -- Ct wt +ve adjustment
1808 IF l_pri_qty_to_incr > 0 THEN
1809 l_api_return_status := fnd_api.g_ret_sts_success;
1810 process_ctwt_adj(
1811 x_return_status => l_api_return_status
1812 , x_msg_count => x_msg_count
1813 , x_msg_data => x_msg_data
1814 , p_ctwt_adj_type => 'RECEIPT'
1815 , p_organization_id => p_organization_id
1816 , p_subinv_code => l_subinv_code
1817 , p_locator_id => l_locator_id
1818 , p_lpn_id => p_lpn_id
1819 , p_inventory_item_id => p_inventory_item_id
1820 , p_revision => p_revision
1821 , p_lot_number => p_lot_number
1822 , p_pri_qty => l_pri_qty_to_incr
1823 , p_pri_uom_code => l_pr_uom_code
1824 , p_sec_uom_code => p_secondary_uom_code
1825 );
1826 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1827 IF (l_debug = 1) THEN
1828 print_debug('Error status from process_ctwt_adj: '
1829 || l_api_return_status, 4);
1830 END IF;
1831 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1832 RAISE fnd_api.g_exc_error;
1833 ELSE
1834 RAISE fnd_api.g_exc_unexpected_error;
1835 END IF;
1836 END IF;
1837 END IF;
1838
1839 IF l_sub_reservable = 1 THEN --{
1840 BEGIN
1841 SELECT NVL(SUM(primary_reservation_quantity),0)
1842 INTO l_lpn_rsv_pri_qty
1843 FROM mtl_reservations
1844 WHERE demand_source_type_id IN (2,8)
1845 AND lpn_id = p_lpn_id
1846 AND organization_id = p_organization_id
1847 AND inventory_item_id = p_inventory_item_id
1848 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
1849 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
1850 AND NVL(staged_flag,'N') = 'N';
1851 EXCEPTION
1852 WHEN NO_DATA_FOUND THEN
1853 l_lpn_rsv_pri_qty := 0;
1854 WHEN OTHERS THEN
1855 IF (l_debug = 1) THEN
1856 print_debug('Error querying LPN rsv qty: ' || SQLERRM, 4);
1857 END IF;
1858 RAISE FND_API.g_exc_unexpected_error;
1859 END;
1860
1861 IF (l_debug = 1) THEN
1862 print_debug('l_lpn_rsv_pri_qty: ' || l_lpn_rsv_pri_qty, 4);
1863 END IF;
1864
1865 IF l_lpn_rsv_pri_qty > 0 THEN --{
1866 IF l_pri_qty_to_reduce > 0 THEN --{
1867 IF l_lpn_rsv_pri_qty <= l_pri_qty_to_reduce THEN --{
1868 IF (l_debug = 1) THEN
1869 print_debug('l_lpn_rsv_pri_qty <= l_pri_qty_to_reduce', 4);
1870 END IF;
1871 -- Delete reservations
1872 OPEN c_ds_lpn_rsvs;
1873 FETCH c_ds_lpn_rsvs BULK COLLECT INTO t_rsv_id;
1874 CLOSE c_ds_lpn_rsvs;
1875
1876 FOR i IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP
1877 l_rsv_rec.reservation_id := t_rsv_id(i);
1878
1879 IF (l_debug = 1) THEN
1880 print_debug('Calling delete_reservation for rsv ID: ' || t_rsv_id(i), 4);
1881 END IF;
1882
1883 l_api_return_status := fnd_api.g_ret_sts_success;
1884 inv_reservation_pvt.delete_reservation (
1885 p_api_version_number => 1.0
1886 , p_init_msg_lst => fnd_api.g_false
1887 , x_return_status => l_api_return_status
1888 , x_msg_count => x_msg_count
1889 , x_msg_data => x_msg_data
1890 , p_rsv_rec => l_rsv_rec
1891 , p_original_serial_number => l_original_serial_number
1892 , p_validation_flag => NULL
1893 );
1894 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
1895 IF (l_debug = 1) THEN
1896 print_debug('Error status from inv_reservation_pvt.delete_reservation: '
1897 || l_api_return_status, 4);
1898 END IF;
1899 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
1900 RAISE fnd_api.g_exc_error;
1901 ELSE
1902 RAISE fnd_api.g_exc_unexpected_error;
1903 END IF;
1904 END IF;
1905 END LOOP;
1906 --}
1907 ELSE
1908 IF (l_debug = 1) THEN
1909 print_debug('l_lpn_rsv_pri_qty > l_pri_qty_to_reduce', 4);
1910 END IF;
1911 -- Update reservations: reduce qty
1912 UPDATE mtl_reservations
1913 SET primary_reservation_quantity =
1914 (primary_reservation_quantity -
1915 ROUND(((primary_reservation_quantity * l_pri_qty_to_reduce)/l_lpn_rsv_pri_qty),5)
1916 )
1917 , reservation_quantity =
1918 (reservation_quantity -
1919 DECODE( reservation_uom_code
1920 , secondary_uom_code, 0
1921 , DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
1922 , wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
1923 , inv_convert.inv_um_convert(
1924 inventory_item_id
1925 , lot_number
1926 , organization_id
1927 , NULL
1928 , ROUND(((primary_reservation_quantity * l_pri_qty_to_reduce)/l_lpn_rsv_pri_qty),5)
1929 , primary_uom_code
1930 , reservation_uom_code
1931 , NULL
1932 , NULL
1933 )
1934 )
1935 )
1936 )
1937 WHERE demand_source_type_id IN (2,8)
1938 AND lpn_id = p_lpn_id
1939 AND organization_id = p_organization_id
1940 AND inventory_item_id = p_inventory_item_id
1941 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
1942 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
1943 AND NVL(staged_flag,'N') = 'N'
1944 RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
1945
1946 IF (l_debug = 1) THEN
1947 print_debug('Decreased the qty on following rsv IDs: ', 4);
1948 FOR j IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP
1949 print_debug(t_rsv_id(j), 4);
1950 END LOOP;
1951 END IF;
1952
1953 t_rsv_id.DELETE;
1954 END IF; -- end if/else l_lpn_rsv_pri_qty <= l_pri_qty_to_reduce
1955 END IF; --} end if l_pri_qty_to_reduce > 0
1956
1957 IF l_pri_qty_to_incr > 0 THEN --{
1958 IF (l_debug = 1) THEN
1959 print_debug('DS: l_pri_qty_to_incr > 0; ' || l_pri_qty_to_incr, 4);
1960 END IF;
1961 -- Update reservations: increase qty
1962 UPDATE mtl_reservations
1963 SET primary_reservation_quantity =
1964 (primary_reservation_quantity +
1965 ROUND(((primary_reservation_quantity * l_pri_qty_to_incr)/l_lpn_rsv_pri_qty),5)
1966 )
1967 , reservation_quantity =
1968 (reservation_quantity +
1969 DECODE( reservation_uom_code
1970 , secondary_uom_code, 0
1971 , DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
1972 , wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
1973 , inv_convert.inv_um_convert(
1974 inventory_item_id
1975 , lot_number
1976 , organization_id
1977 , NULL
1978 , ROUND(((primary_reservation_quantity * l_pri_qty_to_incr)/l_lpn_rsv_pri_qty),5)
1979 , primary_uom_code
1980 , reservation_uom_code
1981 , NULL
1982 , NULL
1983 )
1984 )
1985 )
1986 )
1987 WHERE demand_source_type_id IN (2,8)
1988 AND lpn_id = p_lpn_id
1989 AND organization_id = p_organization_id
1990 AND inventory_item_id = p_inventory_item_id
1991 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
1992 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
1993 AND NVL(staged_flag,'N') = 'N'
1994 RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
1995
1996 IF (l_debug = 1) THEN
1997 print_debug('Increased the qty on following rsv IDs: ', 4);
1998 FOR j IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP
1999 print_debug('t_rsv_id(j): ' || t_rsv_id(j), 4);
2000 END LOOP;
2001 END IF;
2002
2003 t_rsv_id.DELETE;
2004 END IF; --} end if l_pri_qty_to_incr > 0
2005 END IF; --} end if l_lpn_rsv_pri_qty > 0
2006 END IF; --} if sub is reservable
2007
2008 -- Ct wt -ve adjustment
2009 IF l_pri_qty_to_reduce > 0 THEN --{
2010 l_api_return_status := fnd_api.g_ret_sts_success;
2011 process_ctwt_adj(
2012 x_return_status => l_api_return_status
2013 , x_msg_count => x_msg_count
2014 , x_msg_data => x_msg_data
2015 , p_ctwt_adj_type => 'ISSUE'
2016 , p_organization_id => p_organization_id
2017 , p_subinv_code => l_subinv_code
2018 , p_locator_id => l_locator_id
2019 , p_lpn_id => p_lpn_id
2020 , p_inventory_item_id => p_inventory_item_id
2021 , p_revision => p_revision
2022 , p_lot_number => p_lot_number
2023 , p_pri_qty => l_pri_qty_to_reduce
2024 , p_pri_uom_code => l_pr_uom_code
2025 , p_sec_uom_code => p_secondary_uom_code
2026 );
2027 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2028 IF (l_debug = 1) THEN
2029 print_debug('Error status from process_ctwt_adj: '
2030 || l_api_return_status, 4);
2031 END IF;
2032 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
2033 RAISE fnd_api.g_exc_error;
2034 ELSE
2035 RAISE fnd_api.g_exc_unexpected_error;
2036 END IF;
2037 END IF;
2038 END IF; --} end if l_pri_qty_to_reduce > 0
2039 END IF; --} end if qty adjustment required
2040 --} end record source Direct Ship (DS)
2041 ELSIF ( UPPER(p_record_source) = 'WDD' ) THEN --{
2042 OPEN c_stg_lpn_qty;
2043 FETCH c_stg_lpn_qty INTO l_lpn_pri_qty, l_lpn_sec_qty;
2044 CLOSE c_stg_lpn_qty;
2045
2046 IF (l_debug = 1) THEN
2047 print_debug('l_lpn_pri_qty: ' || l_lpn_pri_qty, 4);
2048 print_debug('l_lpn_sec_qty: ' || l_lpn_sec_qty, 4);
2049 END IF;
2050
2051 IF l_lpn_pri_qty <= 0 THEN
2052 IF (l_debug = 1) THEN
2053 print_debug('l_lpn_pri_qty is 0 or -ve, erroring out', 4);
2054 END IF;
2055 RAISE fnd_api.g_exc_unexpected_error;
2056 END IF;
2057
2058 IF l_lpn_sec_qty <> p_secondary_quantity THEN
2059 IF (l_debug = 1) THEN
2060 print_debug('l_lpn_sec_qty does not match passed-in secondary', 4);
2061 END IF;
2062 RAISE fnd_api.g_exc_unexpected_error;
2063 END IF;
2064
2065 IF l_lpn_pri_qty > l_primary_qty THEN
2066 -- Reduce WDD qty to match entered primary qty
2067 l_pri_qty_to_reduce := l_lpn_pri_qty - l_primary_qty;
2068 ELSIF l_lpn_pri_qty < l_primary_qty THEN
2069 -- Increase WDD qty to match entered primary qty
2070 l_pri_qty_to_incr := l_primary_qty - l_lpn_pri_qty;
2071 END IF;
2072
2073 IF (l_debug = 1) THEN
2074 print_debug('l_pri_qty_to_reduce: ' || l_pri_qty_to_reduce, 4);
2075 print_debug('l_pri_qty_to_incr: ' || l_pri_qty_to_incr, 4);
2076 END IF;
2077
2078 IF l_pri_qty_to_reduce > 0 OR l_pri_qty_to_incr > 0
2079 THEN --{ qty adjustment required
2080 OPEN c_get_wdds;
2081 FETCH c_get_wdds BULK COLLECT
2082 INTO t_wdd_id, g_src_line_id, t_pck_qty, t_shp_qty, t_pri_qty, t_req_uom;
2083 CLOSE c_get_wdds;
2084
2085 BEGIN
2086 SELECT wlpn.subinventory_code
2087 , sub.reservable_type
2088 , wlpn.locator_id
2089 INTO l_subinv_code
2090 , l_sub_reservable
2091 , l_locator_id
2092 FROM wms_license_plate_numbers wlpn
2093 , mtl_secondary_inventories sub
2094 WHERE wlpn.lpn_id = p_lpn_id
2095 AND sub.organization_id = wlpn.organization_id
2096 AND sub.secondary_inventory_name = wlpn.subinventory_code;
2097 EXCEPTION
2098 WHEN OTHERS THEN
2099 IF (l_debug = 1) THEN
2100 print_debug('Error querying LPN sub/loc, sub reservable type: ' || SQLERRM, 4);
2101 END IF;
2102 RAISE FND_API.g_exc_unexpected_error;
2103 END;
2104
2105 -- Ct wt +ve adjustment
2106 IF l_pri_qty_to_incr > 0 THEN
2107 l_api_return_status := fnd_api.g_ret_sts_success;
2108 process_ctwt_adj(
2109 x_return_status => l_api_return_status
2110 , x_msg_count => x_msg_count
2111 , x_msg_data => x_msg_data
2112 , p_ctwt_adj_type => 'RECEIPT'
2113 , p_organization_id => p_organization_id
2114 , p_subinv_code => l_subinv_code
2115 , p_locator_id => l_locator_id
2116 , p_lpn_id => p_lpn_id
2117 , p_inventory_item_id => p_inventory_item_id
2118 , p_revision => p_revision
2119 , p_lot_number => p_lot_number
2120 , p_pri_qty => l_pri_qty_to_incr
2121 , p_pri_uom_code => l_pr_uom_code
2122 , p_sec_uom_code => p_secondary_uom_code
2123 );
2124 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2125 IF (l_debug = 1) THEN
2126 print_debug('Error status from process_ctwt_adj: '
2127 || l_api_return_status, 4);
2128 END IF;
2129 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
2130 RAISE fnd_api.g_exc_error;
2131 ELSE
2132 RAISE fnd_api.g_exc_unexpected_error;
2133 END IF;
2134 END IF;
2135 END IF;
2136
2137 -- Update WDDs
2138 l_wdd_index := 1;
2139 FOR i IN t_wdd_id.FIRST..t_wdd_id.LAST LOOP --{
2140 l_wdd_qty_incr := 0;
2141 l_wdd_qty_decr := 0;
2142 IF l_pri_qty_to_incr > 0 THEN
2143 l_pri_increase := ROUND(((t_pri_qty(i) * l_pri_qty_to_incr) / l_lpn_pri_qty),5);
2144 IF (l_debug = 1) THEN
2145 print_debug('l_pri_increase: ' || l_pri_increase, 4);
2146 END IF;
2147 IF ( t_req_uom(i) <> l_pr_uom_code ) THEN
2148 l_wdd_qty_incr := inv_convert.inv_um_convert(
2149 item_id => p_inventory_item_id
2150 , lot_number => p_lot_number
2151 , organization_id => p_organization_id
2152 , precision => NULL
2153 , from_quantity => l_pri_increase
2154 , from_unit => l_pr_uom_code
2155 , to_unit => t_req_uom(i)
2156 , from_name => NULL
2157 , to_name => NULL
2158 );
2159 IF (l_wdd_qty_incr = -99999) THEN
2160 IF (l_debug = 1) THEN
2161 print_debug('UOM conversion error', 4);
2162 END IF;
2163 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
2164 fnd_msg_pub.ADD;
2165 RAISE FND_API.G_EXC_ERROR;
2166 END IF;
2167 ELSE
2168 l_wdd_qty_incr := l_pri_increase;
2169 END IF;
2170 ELSIF l_pri_qty_to_reduce > 0 THEN
2171 l_pri_decrease := ROUND(((t_pri_qty(i) * l_pri_qty_to_reduce) / l_lpn_pri_qty),5);
2172 IF (l_debug = 1) THEN
2173 print_debug('l_pri_decrease: ' || l_pri_decrease, 4);
2174 END IF;
2175 IF ( t_req_uom(i) <> l_pr_uom_code ) THEN
2176 l_wdd_qty_decr := inv_convert.inv_um_convert(
2177 item_id => p_inventory_item_id
2178 , lot_number => p_lot_number
2179 , organization_id => p_organization_id
2180 , precision => NULL
2181 , from_quantity => l_pri_decrease
2182 , from_unit => l_pr_uom_code
2183 , to_unit => t_req_uom(i)
2184 , from_name => NULL
2185 , to_name => NULL
2186 );
2187 IF (l_wdd_qty_decr = -99999) THEN
2188 IF (l_debug = 1) THEN
2189 print_debug('UOM conversion error', 4);
2190 END IF;
2191 FND_MESSAGE.SET_NAME('INV','INV_NO_CONVERSION_ERR');
2192 fnd_msg_pub.ADD;
2193 RAISE FND_API.G_EXC_ERROR;
2194 END IF;
2195 ELSE
2196 l_wdd_qty_decr := l_pri_decrease;
2197 END IF;
2198 END IF;
2199
2200 IF (l_debug = 1) THEN
2201 print_debug('t_wdd_id(i): ' || t_wdd_id(i), 4);
2202 print_debug('g_src_line_id(i): ' || g_src_line_id(i), 4);
2203 print_debug('t_pck_qty(i): ' || t_pck_qty(i), 4);
2204 print_debug('t_shp_qty(i): ' || t_shp_qty(i), 4);
2205 print_debug('t_pri_qty(i): ' || t_pri_qty(i), 4);
2206 print_debug('t_req_uom(i): ' || t_req_uom(i), 4);
2207 print_debug('l_wdd_qty_incr: ' || l_wdd_qty_incr, 4);
2208 print_debug('l_wdd_qty_decr: ' || l_wdd_qty_decr, 4);
2209 END IF;
2210
2211 IF l_wdd_qty_incr > 0 OR l_wdd_qty_decr > 0 THEN
2212 l_shipping_attr(l_wdd_index).delivery_detail_id := t_wdd_id(i);
2213 l_shipping_attr(l_wdd_index).picked_quantity := t_pck_qty(i) + l_wdd_qty_incr - l_wdd_qty_decr;
2214 l_shipping_attr(l_wdd_index).shipped_quantity := t_shp_qty(i) + l_wdd_qty_incr - l_wdd_qty_decr;
2215 l_wdd_index := l_wdd_index + 1;
2216 END IF;
2217 END LOOP; --}
2218
2219 IF ( l_shipping_attr.count > 0 ) THEN --{
2220 l_shipping_in_rec.caller := 'WMS';
2221 l_shipping_in_rec.action_code := 'UPDATE';
2222
2223 IF (l_debug = 1) THEN
2224 print_debug('Calling create_update_delivery_detail count='||l_shipping_attr.count,4);
2225 END IF;
2226
2227 WSH_INTERFACE_EXT_GRP.create_update_delivery_detail(
2228 p_api_version_number => 1.0
2229 , p_init_msg_list => fnd_api.g_false
2230 , p_commit => fnd_api.g_false
2231 , x_return_status => x_return_status
2232 , x_msg_count => x_msg_count
2233 , x_msg_data => x_msg_data
2234 , p_detail_info_tab => l_shipping_attr
2235 , p_in_rec => l_shipping_in_rec
2236 , x_out_rec => l_shipping_out_rec
2237 );
2238
2239 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
2240 --Get error messages from shipping
2241 WSH_UTIL_CORE.get_messages('Y', x_msg_data, l_msg_details, x_msg_count);
2242 IF x_msg_count > 1 then
2243 x_msg_data := x_msg_data || l_msg_details;
2244 END IF;
2245
2246 IF (l_debug = 1) THEN
2247 print_debug('Error calling create_update_delivery_detail: '||x_msg_data, 4);
2248 END IF;
2249 FND_MESSAGE.set_name('WMS','WMS_UPD_DELIVERY_ERROR' );
2250 FND_MESSAGE.set_token('MSG1', x_msg_data);
2251 FND_MSG_PUB.add;
2252 RAISE FND_API.g_exc_unexpected_error;
2253 END IF;
2254 END IF; --}
2255
2256 IF (l_debug = 1) THEN
2257 print_debug('l_subinv_code: ' || l_subinv_code, 4);
2258 print_debug('l_sub_reservable: ' || l_sub_reservable, 4);
2259 print_debug('l_locator_id: ' || l_locator_id, 4);
2260 END IF;
2261
2262 IF l_sub_reservable = 1 THEN --{
2263 -- Start with LPN reservations
2264 BEGIN
2265 SELECT NVL(SUM(primary_reservation_quantity),0)
2266 INTO l_lpn_rsv_pri_qty
2267 FROM mtl_reservations
2268 WHERE demand_source_type_id IN (2,8)
2269 AND demand_source_line_id IN
2270 (SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
2271 AND lpn_id = p_lpn_id
2272 AND organization_id = p_organization_id
2273 AND inventory_item_id = p_inventory_item_id
2274 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
2275 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
2276 AND NVL(staged_flag,'N') = 'Y';
2277 EXCEPTION
2278 WHEN NO_DATA_FOUND THEN
2279 l_lpn_rsv_pri_qty := 0;
2280 WHEN OTHERS THEN
2281 IF (l_debug = 1) THEN
2282 print_debug('Error querying LPN rsv qty: ' || SQLERRM, 4);
2283 END IF;
2284 RAISE FND_API.g_exc_unexpected_error;
2285 END;
2286
2287 l_rem_pri_qty_decr := l_pri_qty_to_reduce;
2288 l_rem_pri_qty_incr := l_pri_qty_to_incr;
2289
2290 IF (l_debug = 1) THEN
2291 print_debug('l_lpn_rsv_pri_qty: ' || l_lpn_rsv_pri_qty, 4);
2292 print_debug('l_rem_pri_qty_decr: ' || l_rem_pri_qty_decr, 4);
2293 print_debug('l_rem_pri_qty_incr: ' || l_rem_pri_qty_incr, 4);
2294 END IF;
2295
2296 IF l_lpn_rsv_pri_qty > 0 THEN --{
2297 -- Need to increase or decrease LPN reservations first
2298 IF l_pri_qty_to_reduce > 0 THEN --{
2299 IF l_lpn_rsv_pri_qty <= l_pri_qty_to_reduce THEN --{
2300 IF (l_debug = 1) THEN
2301 print_debug('l_lpn_rsv_pri_qty <= l_pri_qty_to_reduce', 4);
2302 END IF;
2303 -- Delete reservations
2304 OPEN c_get_lpn_rsvs;
2305 FETCH c_get_lpn_rsvs BULK COLLECT INTO t_rsv_id;
2306 CLOSE c_get_lpn_rsvs;
2307
2308 FOR i IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP
2309 l_rsv_rec.reservation_id := t_rsv_id(i);
2310
2311 IF (l_debug = 1) THEN
2312 print_debug('Calling delete_reservation for rsv ID: ' || t_rsv_id(i), 4);
2313 END IF;
2314
2315 l_api_return_status := fnd_api.g_ret_sts_success;
2316 inv_reservation_pvt.delete_reservation (
2317 p_api_version_number => 1.0
2318 , p_init_msg_lst => fnd_api.g_false
2319 , x_return_status => l_api_return_status
2320 , x_msg_count => x_msg_count
2321 , x_msg_data => x_msg_data
2322 , p_rsv_rec => l_rsv_rec
2323 , p_original_serial_number => l_original_serial_number
2324 , p_validation_flag => NULL
2325 );
2326 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2327 IF (l_debug = 1) THEN
2328 print_debug('Error status from inv_reservation_pvt.delete_reservation: '
2329 || l_api_return_status, 4);
2330 END IF;
2331 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
2332 RAISE fnd_api.g_exc_error;
2333 ELSE
2334 RAISE fnd_api.g_exc_unexpected_error;
2335 END IF;
2336 END IF;
2337 END LOOP;
2338 l_rem_pri_qty_decr := l_rem_pri_qty_decr - l_lpn_rsv_pri_qty;
2339 --}
2340 ELSE
2341 IF (l_debug = 1) THEN
2342 print_debug('l_lpn_rsv_pri_qty > l_pri_qty_to_reduce', 4);
2343 END IF;
2344 -- Update reservations: reduce qty
2345 UPDATE mtl_reservations
2346 SET primary_reservation_quantity =
2347 (primary_reservation_quantity -
2348 ROUND(((primary_reservation_quantity * l_pri_qty_to_reduce)/l_lpn_rsv_pri_qty),5)
2349 )
2350 , reservation_quantity =
2351 (reservation_quantity -
2352 DECODE( reservation_uom_code
2353 , secondary_uom_code, 0
2354 , DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
2355 , wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
2356 , inv_convert.inv_um_convert(
2357 inventory_item_id
2358 , lot_number
2359 , organization_id
2360 , NULL
2361 , ROUND(((primary_reservation_quantity * l_pri_qty_to_reduce)/l_lpn_rsv_pri_qty),5)
2362 , primary_uom_code
2363 , reservation_uom_code
2364 , NULL
2365 , NULL
2366 )
2367 )
2368 )
2369 )
2370 WHERE demand_source_type_id IN (2,8)
2371 AND demand_source_line_id IN
2372 (SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
2373 AND lpn_id = p_lpn_id
2374 AND organization_id = p_organization_id
2375 AND inventory_item_id = p_inventory_item_id
2376 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
2377 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
2378 AND NVL(staged_flag,'N') = 'Y'
2379 RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
2380
2381 IF t_rsv_id.COUNT > 0 THEN
2382 IF (l_debug = 1) THEN
2383 print_debug('Decreased the qty on following rsv IDs: (count=' || t_rsv_id.COUNT || ')', 4);
2384 FOR j IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP
2385 print_debug(t_rsv_id(j), 4);
2386 END LOOP;
2387 END IF;
2388 t_rsv_id.DELETE;
2389 END IF;
2390 l_rem_pri_qty_decr := 0;
2391 END IF; -- end if/else l_lpn_rsv_pri_qty <= l_pri_qty_to_reduce
2392 END IF; --} end if l_pri_qty_to_reduce > 0
2393
2394 IF l_pri_qty_to_incr > 0 THEN --{
2395 IF (l_debug = 1) THEN
2396 print_debug('l_pri_qty_to_incr > 0 ::' || l_pri_qty_to_incr, 4);
2397 END IF;
2398 -- Update reservations: increase qty
2399 UPDATE mtl_reservations
2400 SET primary_reservation_quantity =
2401 (primary_reservation_quantity +
2402 ROUND(((primary_reservation_quantity * l_pri_qty_to_incr)/l_lpn_rsv_pri_qty),5)
2403 )
2404 , reservation_quantity =
2405 (reservation_quantity +
2406 DECODE( reservation_uom_code
2407 , secondary_uom_code, 0
2408 , DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
2409 , wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
2410 , inv_convert.inv_um_convert(
2411 inventory_item_id
2412 , lot_number
2413 , organization_id
2414 , NULL
2415 , ROUND(((primary_reservation_quantity * l_pri_qty_to_incr)/l_lpn_rsv_pri_qty),5)
2416 , primary_uom_code
2417 , reservation_uom_code
2418 , NULL
2419 , NULL
2420 )
2421 )
2422 )
2423 )
2424 WHERE demand_source_type_id IN (2,8)
2425 AND demand_source_line_id IN
2426 (SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
2427 AND lpn_id = p_lpn_id
2428 AND organization_id = p_organization_id
2429 AND inventory_item_id = p_inventory_item_id
2430 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
2431 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
2432 AND NVL(staged_flag,'N') = 'Y'
2433 RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
2434
2435 IF t_rsv_id.COUNT > 0 THEN
2436 IF (l_debug = 1) THEN
2437 print_debug('Increased the qty on following rsv IDs: (count=' || t_rsv_id.COUNT || ')', 4);
2438 FOR j IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP
2439 print_debug(t_rsv_id(j), 4);
2440 END LOOP;
2441 END IF;
2442 t_rsv_id.DELETE;
2443 END IF;
2444 l_rem_pri_qty_incr := 0;
2445 END IF; --} end if l_pri_qty_to_incr > 0
2446 END IF; --} end if l_lpn_rsv_pri_qty > 0
2447
2448 IF (l_debug = 1) THEN
2449 print_debug('Done with LPN reservations', 4);
2450 print_debug('l_pri_qty_to_reduce: ' || l_pri_qty_to_reduce, 4);
2451 print_debug('l_rem_pri_qty_decr: ' || l_rem_pri_qty_decr, 4);
2452 print_debug('l_pri_qty_to_incr: ' || l_pri_qty_to_incr, 4);
2453 print_debug('l_rem_pri_qty_incr: ' || l_rem_pri_qty_incr, 4);
2454 END IF;
2455
2456 -- Done with LPN reservations (if any), now process other reservations
2457
2458 BEGIN
2459 SELECT NVL(SUM(primary_reservation_quantity),0)
2460 INTO l_nonlpn_rsv_pri_qty
2461 FROM mtl_reservations
2462 WHERE demand_source_type_id IN (2,8)
2463 AND demand_source_line_id IN
2464 (SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
2465 AND organization_id = p_organization_id
2466 AND subinventory_code = l_subinv_code
2467 AND locator_id = l_locator_id
2468 AND lpn_id IS NULL
2469 AND inventory_item_id = p_inventory_item_id
2470 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
2471 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
2472 AND NVL(staged_flag,'N') = 'Y';
2473 EXCEPTION
2474 WHEN NO_DATA_FOUND THEN
2475 l_nonlpn_rsv_pri_qty := 0;
2476 WHEN OTHERS THEN
2477 IF (l_debug = 1) THEN
2478 print_debug('Error querying non-LPN rsv qty: ' || SQLERRM, 4);
2479 END IF;
2480 RAISE FND_API.g_exc_unexpected_error;
2481 END;
2482
2483 IF (l_debug = 1) THEN
2484 print_debug('l_nonlpn_rsv_pri_qty: ' || l_nonlpn_rsv_pri_qty, 4);
2485 END IF;
2486
2487 IF l_nonlpn_rsv_pri_qty > 0 THEN --{
2488 IF l_rem_pri_qty_decr > 0 THEN --{
2489 IF (l_debug = 1) THEN
2490 print_debug('Decrementing non-LPN reservations', 4);
2491 END IF;
2492 UPDATE mtl_reservations
2493 SET primary_reservation_quantity =
2494 (primary_reservation_quantity -
2495 ROUND(((primary_reservation_quantity * l_rem_pri_qty_decr)/l_nonlpn_rsv_pri_qty),5)
2496 )
2497 , reservation_quantity =
2498 (reservation_quantity -
2499 DECODE( reservation_uom_code
2500 , secondary_uom_code, 0
2501 , DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
2502 , wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
2503 , inv_convert.inv_um_convert(
2504 inventory_item_id
2505 , lot_number
2506 , organization_id
2507 , NULL
2508 , ROUND(((primary_reservation_quantity * l_rem_pri_qty_decr)/l_nonlpn_rsv_pri_qty),5)
2509 , primary_uom_code
2510 , reservation_uom_code
2511 , NULL
2512 , NULL
2513 )
2514 )
2515 )
2516 )
2517 WHERE demand_source_type_id IN (2,8)
2518 AND demand_source_line_id IN
2519 (SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
2520 AND organization_id = p_organization_id
2521 AND subinventory_code = l_subinv_code
2522 AND locator_id = l_locator_id
2523 AND lpn_id IS NULL
2524 AND inventory_item_id = p_inventory_item_id
2525 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
2526 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
2527 AND NVL(staged_flag,'N') = 'Y'
2528 RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
2529
2530 IF t_rsv_id.COUNT > 0 THEN
2531 IF (l_debug = 1) THEN
2532 print_debug('Decreased the qty on following rsv IDs: (count=' || t_rsv_id.COUNT || ')', 4);
2533 FOR j IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP
2534 print_debug(t_rsv_id(j), 4);
2535 END LOOP;
2536 END IF;
2537 t_rsv_id.DELETE;
2538 END IF;
2539 END IF; --} if -ve RSV qty changes required
2540
2541 IF l_rem_pri_qty_incr > 0 THEN --{
2542 IF (l_debug = 1) THEN
2543 print_debug('Incrementing non-LPN reservations', 4);
2544 END IF;
2545 UPDATE mtl_reservations
2546 SET primary_reservation_quantity =
2547 (primary_reservation_quantity +
2548 ROUND(((primary_reservation_quantity * l_rem_pri_qty_incr)/l_nonlpn_rsv_pri_qty),5)
2549 )
2550 , reservation_quantity =
2551 (reservation_quantity +
2552 DECODE( reservation_uom_code
2553 , secondary_uom_code, 0
2554 , DECODE( wms_catch_weight_pvt.get_uom_class(reservation_uom_code)
2555 , wms_catch_weight_pvt.get_uom_class(secondary_uom_code), 0
2556 , inv_convert.inv_um_convert(
2557 inventory_item_id
2558 , lot_number
2559 , organization_id
2560 , NULL
2561 , ROUND(((primary_reservation_quantity * l_rem_pri_qty_incr)/l_nonlpn_rsv_pri_qty),5)
2562 , primary_uom_code
2563 , reservation_uom_code
2564 , NULL
2565 , NULL
2566 )
2567 )
2568 )
2569 )
2570 WHERE demand_source_type_id IN (2,8)
2571 AND demand_source_line_id IN
2572 (SELECT * FROM TABLE(wms_catch_weight_pvt.wdd_src_line_id))
2573 AND organization_id = p_organization_id
2574 AND subinventory_code = l_subinv_code
2575 AND locator_id = l_locator_id
2576 AND lpn_id IS NULL
2577 AND inventory_item_id = p_inventory_item_id
2578 AND NVL(revision,'@@@') = NVL(p_revision,'@@@')
2579 AND NVL(lot_number,'@@@') = NVL(p_lot_number,'@@@')
2580 AND NVL(staged_flag,'N') = 'Y'
2581 RETURNING reservation_id BULK COLLECT INTO t_rsv_id;
2582
2583 IF t_rsv_id.COUNT > 0 THEN
2584 IF (l_debug = 1) THEN
2585 print_debug('Increased the qty on following rsv IDs: (count=' || t_rsv_id.COUNT || ')', 4);
2586 FOR j IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP
2587 print_debug(t_rsv_id(j), 4);
2588 END LOOP;
2589 END IF;
2590 t_rsv_id.DELETE;
2591 END IF;
2592
2593 END IF; --} if +ve RSV qty changes required
2594 END IF; --} end if l_nonlpn_rsv_pri_qty > 0
2595 IF (l_debug = 1) THEN
2596 print_debug('Done with non-LPN reservations', 4);
2597 END IF;
2598 END IF; --} if sub is reservable
2599
2600 -- Ct wt -ve adjustment
2601 IF l_pri_qty_to_reduce > 0 THEN --{
2602 l_api_return_status := fnd_api.g_ret_sts_success;
2603 process_ctwt_adj(
2604 x_return_status => l_api_return_status
2605 , x_msg_count => x_msg_count
2606 , x_msg_data => x_msg_data
2607 , p_ctwt_adj_type => 'ISSUE'
2608 , p_organization_id => p_organization_id
2609 , p_subinv_code => l_subinv_code
2610 , p_locator_id => l_locator_id
2611 , p_lpn_id => p_lpn_id
2612 , p_inventory_item_id => p_inventory_item_id
2613 , p_revision => p_revision
2614 , p_lot_number => p_lot_number
2615 , p_pri_qty => l_pri_qty_to_reduce
2616 , p_pri_uom_code => l_pr_uom_code
2617 , p_sec_uom_code => p_secondary_uom_code
2618 );
2619 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2620 IF (l_debug = 1) THEN
2621 print_debug('Error status from process_ctwt_adj: '
2622 || l_api_return_status, 4);
2623 END IF;
2624 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
2625 RAISE fnd_api.g_exc_error;
2626 ELSE
2627 RAISE fnd_api.g_exc_unexpected_error;
2628 END IF;
2629 END IF;
2630 END IF; --} end if l_pri_qty_to_reduce > 0
2631
2632 END IF; --} end if qty adjustment required
2633 --} end record source WDD
2634 ELSIF (UPPER(p_record_source) = 'MMTT') THEN --{
2635
2636 -- Update of loaded LPNs (before Pick Drop)
2637 IF p_lot_number IS NOT NULL THEN
2638 OPEN c_lpn_lot_pri_qty;
2639 FETCH c_lpn_lot_pri_qty INTO l_lpn_pri_qty, l_lpn_sec_qty;
2640 CLOSE c_lpn_lot_pri_qty;
2641 ELSE
2642 OPEN c_lpn_pri_qty;
2643 FETCH c_lpn_pri_qty INTO l_lpn_pri_qty, l_lpn_sec_qty;
2644 CLOSE c_lpn_pri_qty;
2645 END IF;
2646
2647 IF (l_debug = 1) THEN
2648 print_debug('l_lpn_pri_qty: ' || l_lpn_pri_qty, 4);
2649 print_debug('l_lpn_sec_qty: ' || l_lpn_sec_qty, 4);
2650 END IF;
2651
2652 IF l_lpn_pri_qty <= 0 THEN
2653 IF (l_debug = 1) THEN
2654 print_debug('l_lpn_pri_qty is 0 or -ve, erroring out', 4);
2655 END IF;
2656 RAISE fnd_api.g_exc_unexpected_error;
2657 END IF;
2658
2659 IF l_lpn_sec_qty <> p_secondary_quantity THEN
2660 IF (l_debug = 1) THEN
2661 print_debug('l_lpn_sec_qty does not match passed-in secondary', 4);
2662 END IF;
2663 RAISE fnd_api.g_exc_unexpected_error;
2664 END IF;
2665
2666 IF l_lpn_pri_qty > l_primary_qty THEN
2667 -- Reduce MMTT/MTLT qtys to match entered primary qty
2668 l_pri_qty_to_reduce := l_lpn_pri_qty - l_primary_qty;
2669 ELSIF l_lpn_pri_qty < l_primary_qty THEN
2670 -- Increase MMTT/MTLT qtys to match entered primary qty
2671 l_pri_qty_to_incr := l_primary_qty - l_lpn_pri_qty;
2672 END IF;
2673
2674 IF (l_debug = 1) THEN
2675 print_debug('l_pri_qty_to_reduce: ' || l_pri_qty_to_reduce, 4);
2676 print_debug('l_pri_qty_to_incr: ' || l_pri_qty_to_incr, 4);
2677 END IF;
2678
2679 IF l_pri_qty_to_reduce > 0 OR l_pri_qty_to_incr > 0
2680 OR l_max_pri_residual > 0 OR NVL(p_ccnt_sec_residual,'N') = 'Y'
2681 THEN --{ qty adjustment or cycle count check required
2682 IF p_lot_number IS NOT NULL THEN
2683 OPEN c_lpn_mtlt;
2684 FETCH c_lpn_mtlt BULK COLLECT
2685 INTO t_temp_id, t_subinv, t_loc_id, t_lpn_id, t_clpn_id, t_rsv_id, t_mol_id, t_pri_qty, t_sec_qty;
2686 CLOSE c_lpn_mtlt;
2687 ELSE
2688 OPEN c_lpn_mmtt;
2689 FETCH c_lpn_mmtt BULK COLLECT
2690 INTO t_temp_id, t_subinv, t_loc_id, t_lpn_id, t_clpn_id, t_rsv_id, t_mol_id, t_pri_qty, t_sec_qty;
2691 CLOSE c_lpn_mmtt;
2692 END IF;
2693
2694 IF t_temp_id.COUNT > 0 THEN
2695 IF (l_debug = 1) THEN
2696 print_debug('Temp IDs to process: ', 4);
2697 FOR k IN t_temp_id.FIRST..t_temp_id.LAST LOOP
2698 print_debug('t_temp_id(k): ' || t_temp_id(k), 4);
2699 print_debug('t_subinv(k): ' || t_subinv(k), 4);
2700 print_debug('t_loc_id(k): ' || t_loc_id(k), 4);
2701 print_debug('t_lpn_id(k): ' || t_lpn_id(k), 4);
2702 print_debug('t_clpn_id(k): ' || t_clpn_id(k), 4);
2703 print_debug('t_rsv_id(k): ' || t_rsv_id(k), 4);
2704 print_debug('t_mol_id(k): ' || t_mol_id(k), 4);
2705 print_debug('t_pri_qty(k): ' || t_pri_qty(k), 4);
2706 print_debug('t_sec_qty(k): ' || t_sec_qty(k), 4);
2707 END LOOP;
2708 END IF;
2709 ELSE
2710 IF (l_debug = 1) THEN
2711 print_debug('No temp IDs to process!', 4);
2712 END IF;
2713 RAISE fnd_api.g_exc_error;
2714 END IF;
2715
2716 IF l_pri_qty_to_reduce > 0 THEN --{
2717 IF (l_debug = 1) THEN
2718 print_debug('Reducing MMTT, MTLT, MOL and MR qty', 4);
2719 END IF;
2720
2721 FOR i IN t_temp_id.FIRST..t_temp_id.LAST LOOP --{
2722 l_sku_pri_decr := ROUND(((t_pri_qty(i) * l_pri_qty_to_reduce)/l_lpn_pri_qty),5);
2723
2724 IF (l_debug = 1) THEN
2725 print_debug('Processing:'||
2726 ' temp_id=' || t_temp_id(i) ||
2727 ', subinv=' || t_subinv(i) ||
2728 ', loc_id=' || t_loc_id(i) ||
2729 ', lpn_id=' || t_lpn_id(i) ||
2730 ', clpn_id=' || t_clpn_id(i) ||
2731 ', rsv_id=' || t_rsv_id(i) ||
2732 ', mol_id=' || t_mol_id(i) ||
2733 ', pri_qty=' || t_pri_qty(i) ||
2734 ', sec_qty=' || t_sec_qty(i) ||
2735 ', l_sku_pri_decr=' || l_sku_pri_decr
2736 , 4);
2737 END IF;
2738
2739 IF p_lot_number IS NOT NULL THEN
2740 UPDATE mtl_transaction_lots_temp
2741 SET primary_quantity = primary_quantity - l_sku_pri_decr
2742 WHERE transaction_temp_id = t_temp_id(i)
2743 AND lot_number = p_lot_number;
2744 END IF;
2745
2746 UPDATE mtl_material_transactions_temp
2747 SET primary_quantity = primary_quantity - l_sku_pri_decr
2748 WHERE transaction_temp_id = t_temp_id(i);
2749
2750 UPDATE mtl_txn_request_lines
2751 SET quantity_detailed = quantity_detailed -
2752 DECODE( uom_code
2753 , l_pr_uom_code, l_sku_pri_decr
2754 , inv_convert.inv_um_convert(
2755 p_inventory_item_id
2756 , NULL
2757 , l_sku_pri_decr
2758 , l_pr_uom_code
2759 , uom_code
2760 , NULL
2761 , NULL
2762 )
2763 )
2764 WHERE line_id = t_mol_id(i);
2765
2766 IF (t_clpn_id(i) IS NOT NULL)
2767 OR (l_lot_control_code <> 1 AND l_lot_divisible_flag = 'N')
2768 THEN --{
2769 l_api_return_status := fnd_api.g_ret_sts_success;
2770 process_ctwt_adj(
2771 x_return_status => l_api_return_status
2772 , x_msg_count => x_msg_count
2773 , x_msg_data => x_msg_data
2774 , p_ctwt_adj_type => 'ISSUE'
2775 , p_organization_id => p_organization_id
2776 , p_subinv_code => t_subinv(i)
2777 , p_locator_id => t_loc_id(i)
2778 , p_lpn_id => NVL(t_clpn_id(i),t_lpn_id(i))
2779 , p_inventory_item_id => p_inventory_item_id
2780 , p_revision => p_revision
2781 , p_lot_number => p_lot_number
2782 , p_pri_qty => l_sku_pri_decr
2783 , p_pri_uom_code => l_pr_uom_code
2784 , p_sec_uom_code => p_secondary_uom_code
2785 );
2786 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2787 IF (l_debug = 1) THEN
2788 print_debug('Error status from process_ctwt_adj: '
2789 || l_api_return_status, 4);
2790 END IF;
2791 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
2792 RAISE fnd_api.g_exc_error;
2793 ELSE
2794 RAISE fnd_api.g_exc_unexpected_error;
2795 END IF;
2796 END IF;
2797 END IF; --} end if content LPN or indiv lot
2798 END LOOP; --} end loop through MMTTs
2799
2800 FOR i IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP --{
2801 l_tot_rsv_pri_qty := 0;
2802 l_tot_rsv_qty := 0;
2803
2804 IF (l_debug = 1) THEN
2805 print_debug('Processing rsv ID: ' || t_rsv_id(i), 4);
2806 END IF;
2807
2808 IF t_rsv_id(i) IS NULL OR t_proc_rsvs.EXISTS(t_rsv_id(i)) THEN
2809 GOTO next_rsv;
2810 END IF;
2811
2812 BEGIN
2813 OPEN c_get_mmtt_rsv(t_rsv_id(i));
2814 FETCH c_get_mmtt_rsv INTO l_mmtt_rsv_rec;
2815 CLOSE c_get_mmtt_rsv;
2816 EXCEPTION
2817 WHEN OTHERS THEN
2818 IF (l_debug = 1) THEN
2819 print_debug('Error from c_get_mmtt_rsv: ' || SQLERRM, 4);
2820 END IF;
2821 IF c_get_mmtt_rsv%ISOPEN THEN
2822 CLOSE c_get_mmtt_rsv;
2823 END IF;
2824 GOTO next_rsv;
2825 END;
2826
2827 IF (l_debug = 1) THEN
2828 print_debug('Data from c_get_mmtt_rsv: ' ||
2829 ' rsv qty: ' || l_mmtt_rsv_rec.reservation_quantity ||
2830 ', rsv uom: ' || l_mmtt_rsv_rec.reservation_uom_code ||
2831 ', rsv uom class: ' || l_mmtt_rsv_rec.rsv_uom_class ||
2832 ', pri qty: ' || l_mmtt_rsv_rec.primary_reservation_quantity ||
2833 ', dtl qty: ' || l_mmtt_rsv_rec.detailed_quantity ||
2834 ', sec uom class: ' || l_mmtt_rsv_rec.sec_uom_class
2835 , 4);
2836 END IF;
2837
2838 IF l_onh_status THEN
2839 OPEN c_onh_stat_rsv_qty(t_rsv_id(i));
2840 FETCH c_onh_stat_rsv_qty INTO l_tot_rsv_pri_qty;
2841 CLOSE c_onh_stat_rsv_qty;
2842 ELSE
2843 IF p_lot_number IS NOT NULL THEN
2844 OPEN c_lot_rsv_qty(t_rsv_id(i));
2845 FETCH c_lot_rsv_qty INTO l_tot_rsv_pri_qty;
2846 CLOSE c_lot_rsv_qty;
2847 ELSE
2848 OPEN c_rsv_qty(t_rsv_id(i));
2849 FETCH c_rsv_qty INTO l_tot_rsv_pri_qty;
2850 CLOSE c_rsv_qty;
2851 END IF;
2852 END IF;
2853
2854 IF (l_debug = 1) THEN
2855 print_debug('l_tot_rsv_pri_qty: ' || l_tot_rsv_pri_qty, 4);
2856 END IF;
2857
2858 IF l_tot_rsv_pri_qty > 0 THEN --{
2859 IF (l_mmtt_rsv_rec.reservation_uom_code = l_pr_uom_code)
2860 OR (l_mmtt_rsv_rec.rsv_uom_class <> l_mmtt_rsv_rec.sec_uom_class)
2861 THEN --{
2862 IF l_mmtt_rsv_rec.reservation_uom_code = l_pr_uom_code THEN
2863 l_tot_rsv_qty := l_tot_rsv_pri_qty;
2864 ELSE
2865 l_tot_rsv_qty := inv_convert.inv_um_convert(
2866 item_id => p_inventory_item_id
2867 , precision => NULL
2868 , from_quantity => l_tot_rsv_pri_qty
2869 , from_unit => l_pr_uom_code
2870 , to_unit => l_mmtt_rsv_rec.reservation_uom_code
2871 , from_name => NULL
2872 , to_name => NULL
2873 );
2874 IF l_tot_rsv_qty < 0 THEN
2875 IF (l_debug = 1) THEN
2876 print_debug('UOM conversion error. l_tot_rsv_qty: ' || l_tot_rsv_qty, 4);
2877 END IF;
2878 GOTO next_rsv;
2879 END IF;
2880 END IF;
2881
2882 IF (l_debug = 1) THEN
2883 print_debug('l_tot_rsv_qty: ' || l_tot_rsv_qty, 4);
2884 print_debug('Updating reservation_quantity on MR', 4);
2885 END IF;
2886
2887 UPDATE mtl_reservations
2888 SET reservation_quantity = l_tot_rsv_qty
2889 WHERE reservation_id = t_rsv_id(i);
2890 END IF; --} end if MR uom is primary, or diff uom class than secondary
2891
2892 IF (l_debug = 1) THEN
2893 print_debug('Updating pri rsv qty and dtl qty on MR', 4);
2894 END IF;
2895
2896 UPDATE mtl_reservations
2897 SET primary_reservation_quantity = l_tot_rsv_pri_qty
2898 , detailed_quantity = l_tot_rsv_pri_qty
2899 WHERE reservation_id = t_rsv_id(i);
2900 END IF; --} end if l_tot_rsv_pri_qty > 0
2901
2902 t_proc_rsvs(t_rsv_id(i)) := 'Y';
2903
2904 <<next_rsv>>
2905 NULL;
2906 END LOOP; --} end loop through rsv IDs
2907 END IF; --} end if qty to be reduced
2908
2909 IF l_max_pri_residual > 0 OR l_pri_qty_to_incr > 0
2910 OR NVL(p_ccnt_sec_residual,'N') = 'Y'
2911 THEN
2912 --{ +ve adj or residual primary txns or cycle count check required
2913 IF (l_debug = 1) THEN
2914 print_debug('Processing max residual/MMTT qty increase/cycle count', 4);
2915 END IF;
2916 FOR j IN t_temp_id.FIRST..t_temp_id.LAST
2917 LOOP --{
2918 l_sku_pri_incr := 0;
2919 l_pri_increase := 0;
2920 inv_quantity_tree_pub.clear_quantity_cache;
2921 IF (t_lpn_id(j) IS NOT NULL)
2922 OR (t_clpn_id(j) IS NOT NULL)
2923 THEN
2924 l_tree_mode := inv_quantity_tree_pub.g_transaction_mode;
2925 ELSE
2926 l_tree_mode := inv_quantity_tree_pub.g_loose_only_mode;
2927 END IF;
2928
2929 IF (l_debug = 1) THEN
2930 print_debug('Processing:'||
2931 ' temp_id=' || t_temp_id(j) ||
2932 ', subinv=' || t_subinv(j) ||
2933 ', loc_id=' || t_loc_id(j) ||
2934 ', lpn_id=' || t_lpn_id(j) ||
2935 ', clpn_id=' || t_clpn_id(j) ||
2936 ', rsv_id=' || t_rsv_id(j) ||
2937 ', mol_id=' || t_mol_id(j) ||
2938 ', pri_qty=' || t_pri_qty(j) ||
2939 ', sec_qty=' || t_sec_qty(j)
2940 , 4);
2941 END IF;
2942
2943 l_api_return_status := fnd_api.g_ret_sts_success;
2944 inv_quantity_tree_pub.query_quantities(
2945 p_api_version_number => 1.0
2946 , p_init_msg_lst => fnd_api.g_false
2947 , x_return_status => l_api_return_status
2948 , x_msg_count => x_msg_count
2949 , x_msg_data => x_msg_data
2950 , p_organization_id => p_organization_id
2951 , p_inventory_item_id => p_inventory_item_id
2952 , p_tree_mode => l_tree_mode
2953 , p_is_revision_control => l_rev_ctrl
2954 , p_is_lot_control => l_lot_ctrl
2955 , p_is_serial_control => FALSE
2956 , p_demand_source_type_id => -9999
2957 , p_revision => p_revision
2958 , p_lot_number => p_lot_number
2959 , p_subinventory_code => t_subinv(j)
2960 , p_locator_id => t_loc_id(j)
2961 , x_qoh => l_qoh
2962 , x_rqoh => l_rqoh
2963 , x_qr => l_qr
2964 , x_qs => l_qs
2965 , x_att => l_att
2966 , x_atr => l_atr
2967 , x_sqoh => l_sqoh
2968 , x_srqoh => l_srqoh
2969 , x_sqr => l_sqr
2970 , x_sqs => l_sqs
2971 , x_satt => l_satt
2972 , x_satr => l_satr
2973 , p_lpn_id => NVL(t_clpn_id(j),t_lpn_id(j))
2974 , p_grade_code => NULL
2975 );
2976
2977 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
2978 IF (l_debug = 1) THEN
2979 print_debug('Error status from inv_quantity_tree_pub.query_quantities: '
2980 || l_api_return_status, 4);
2981 END IF;
2982 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
2983 RAISE fnd_api.g_exc_error;
2984 ELSE
2985 RAISE fnd_api.g_exc_unexpected_error;
2986 END IF;
2987 END IF;
2988
2989 IF (l_debug = 1) THEN
2990 print_debug('Qty tree return values:' ||
2991 ' l_qoh: ' || l_qoh ||
2992 ', l_rqoh: ' || l_rqoh ||
2993 ', l_qr: ' || l_qr ||
2994 ', l_qs: ' || l_qs ||
2995 ', l_att: ' || l_att ||
2996 ', l_atr: ' || l_atr ||
2997 ', l_sqoh: ' || l_sqoh ||
2998 ', l_srqoh: '|| l_srqoh ||
2999 ', l_sqr: ' || l_sqr ||
3000 ', l_sqs: ' || l_sqs ||
3001 ', l_satt: ' || l_satt ||
3002 ', l_satr: ' || l_satr
3003 , 4);
3004 END IF;
3005
3006 IF l_pri_qty_to_incr > 0 THEN
3007 --{ +ve catch weight adj required
3008 l_sku_pri_incr := ROUND(((t_pri_qty(j) * l_pri_qty_to_incr)/l_lpn_pri_qty),5);
3009
3010 IF (l_debug = 1) THEN
3011 print_debug('Qty to increase for this SKU: ' || l_sku_pri_incr, 4);
3012 END IF;
3013
3014 IF l_sku_pri_incr > 0 THEN --{
3015 IF l_att < l_sku_pri_incr THEN --{
3016 l_pri_increase := l_sku_pri_incr - GREATEST(l_att,0);
3017
3018 IF (l_debug = 1) THEN
3019 print_debug('Catch weight +ve adjustment: ' || l_pri_increase, 4);
3020 END IF;
3021
3022 l_api_return_status := fnd_api.g_ret_sts_success;
3023 process_ctwt_adj(
3024 x_return_status => l_api_return_status
3025 , x_msg_count => x_msg_count
3026 , x_msg_data => x_msg_data
3027 , p_ctwt_adj_type => 'RECEIPT'
3028 , p_organization_id => p_organization_id
3029 , p_subinv_code => t_subinv(j)
3030 , p_locator_id => t_loc_id(j)
3031 , p_lpn_id => NVL(t_clpn_id(j),t_lpn_id(j))
3032 , p_inventory_item_id => p_inventory_item_id
3033 , p_revision => p_revision
3034 , p_lot_number => p_lot_number
3035 , p_pri_qty => l_pri_increase
3036 , p_pri_uom_code => l_pr_uom_code
3037 , p_sec_uom_code => p_secondary_uom_code
3038 );
3039 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3040 IF (l_debug = 1) THEN
3041 print_debug('Error status from process_ctwt_adj: '
3042 || l_api_return_status, 4);
3043 END IF;
3044 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
3045 RAISE fnd_api.g_exc_error;
3046 ELSE
3047 RAISE fnd_api.g_exc_unexpected_error;
3048 END IF;
3049 END IF;
3050 END IF; --} end if l_att < l_sku_pri_incr
3051
3052 IF p_lot_number IS NOT NULL THEN
3053 UPDATE mtl_transaction_lots_temp
3054 SET primary_quantity = primary_quantity + l_sku_pri_incr
3055 WHERE transaction_temp_id = t_temp_id(j)
3056 AND lot_number = p_lot_number;
3057 IF (l_debug = 1) THEN
3058 print_debug('Updated MTLT: temp ID ' || t_temp_id(j)
3059 || ' lot ' || p_lot_number, 4);
3060 END IF;
3061 END IF;
3062
3063 UPDATE mtl_material_transactions_temp
3064 SET primary_quantity = primary_quantity + l_sku_pri_incr
3065 WHERE transaction_temp_id = t_temp_id(j);
3066
3067 UPDATE mtl_txn_request_lines
3068 SET quantity_detailed = quantity_detailed +
3069 DECODE( uom_code
3070 , l_pr_uom_code, l_sku_pri_incr
3071 , inv_convert.inv_um_convert(
3072 p_inventory_item_id
3073 , NULL
3074 , l_sku_pri_incr
3075 , l_pr_uom_code
3076 , uom_code
3077 , NULL
3078 , NULL
3079 )
3080 )
3081 WHERE line_id = t_mol_id(j);
3082
3083 IF (l_debug = 1) THEN
3084 print_debug('Updated MMTT: temp ID ' || t_temp_id(j) ||
3085 ', and MOL ID: ' || t_mol_id(j)
3086 , 4);
3087 END IF;
3088
3089 END IF; --} end if l_sku_pri_incr > 0
3090 END IF; --} end if l_pri_qty_to_incr > 0
3091
3092 IF l_max_pri_residual > 0 THEN --{
3093 --
3094 -- If the onhand secondary is being fully consumed, then check
3095 -- if there is a remaining primary qty left behind which is less
3096 -- than the threshold specified.
3097 --
3098 -- This is to make sure we do not leave onhand records like
3099 -- 0.00004 primary and 0.0 secondary. In this e.g., specifying
3100 -- a threshold value as 0.00004 would ensure that this
3101 -- fractional primary is issued out automatically. Such a
3102 -- record would never get allocated with fulfillment base
3103 -- as secondary, since the secondary onhand is already 0.
3104 --
3105 IF (l_sqoh = 0) AND (l_pri_increase = 0)
3106 AND ((l_att - l_sku_pri_incr) > 0)
3107 AND ((l_att - l_sku_pri_incr) <= l_max_pri_residual)
3108 THEN
3109 l_api_return_status := fnd_api.g_ret_sts_success;
3110 process_ctwt_adj(
3111 x_return_status => l_api_return_status
3112 , x_msg_count => x_msg_count
3113 , x_msg_data => x_msg_data
3114 , p_ctwt_adj_type => 'ISSUE'
3115 , p_organization_id => p_organization_id
3116 , p_subinv_code => t_subinv(j)
3117 , p_locator_id => t_loc_id(j)
3118 , p_lpn_id => NVL(t_clpn_id(j),t_lpn_id(j))
3119 , p_inventory_item_id => p_inventory_item_id
3120 , p_revision => p_revision
3121 , p_lot_number => p_lot_number
3122 , p_pri_qty => (l_att - l_sku_pri_incr)
3123 , p_pri_uom_code => l_pr_uom_code
3124 , p_sec_uom_code => p_secondary_uom_code
3125 );
3126 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3127 IF (l_debug = 1) THEN
3128 print_debug('Error status from process_ctwt_adj: '
3129 || l_api_return_status, 4);
3130 END IF;
3131 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
3132 RAISE fnd_api.g_exc_error;
3133 ELSE
3134 RAISE fnd_api.g_exc_unexpected_error;
3135 END IF;
3136 END IF;
3137 END IF;
3138
3139 END IF; --} end if l_max_pri_residual > 0
3140
3141 IF NVL(p_ccnt_sec_residual,'N') = 'Y' THEN --{
3142 --
3143 -- If the current transaction consumes the full primary
3144 -- but leaves behind some secondary, this will get issued
3145 -- out via residual transaction. In such case we should
3146 -- create a cycle count entry for this SKU, since the
3147 -- that secondary qty represents actual physical onhand
3148 -- for fulfillment base secondary
3149 --
3150 IF (l_sqoh > 0)
3151 AND ((l_att - l_sku_pri_incr) <= 0)
3152 THEN
3153 l_api_return_status := fnd_api.g_ret_sts_success;
3154 wms_cycle_pvt.create_unscheduled_counts
3155 ( p_api_version => 1.0
3156 , p_init_msg_list => fnd_api.g_false
3157 , p_commit => fnd_api.g_false
3158 , x_return_status => l_api_return_status
3159 , x_msg_count => x_msg_count
3160 , x_msg_data => x_msg_data
3161 , p_organization_id => p_organization_id
3162 , p_subinventory => t_subinv(j)
3163 , p_locator_id => t_loc_id(j)
3164 , p_inventory_item_id => p_inventory_item_id
3165 , p_lpn_id => t_lpn_id(j)
3166 , p_revision => p_revision
3167 );
3168
3169 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
3170 IF (l_debug = 1) THEN
3171 print_debug('Error status from wms_cycle_pvt.create_unscheduled_counts: '
3172 || l_api_return_status, 4);
3173 print_debug('Error msg: ' || x_msg_data, 4);
3174 END IF;
3175 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
3176 RAISE fnd_api.g_exc_error;
3177 ELSE
3178 RAISE fnd_api.g_exc_unexpected_error;
3179 END IF;
3180 END IF;
3181 END IF;
3182 END IF; --} end if p_ccnt_sec_residual is Y
3183 END LOOP; --} end loop through temp IDs
3184
3185 IF l_pri_qty_to_incr > 0 THEN --{ increment rsv detailed qty
3186 t_proc_rsvs.DELETE;
3187 FOR k IN t_rsv_id.FIRST..t_rsv_id.LAST LOOP --{
3188 l_tot_rsv_pri_qty := 0;
3189 l_tot_rsv_qty := 0;
3190
3191 IF (l_debug = 1) THEN
3192 print_debug('Processing rsv ID: ' || t_rsv_id(k), 4);
3193 END IF;
3194
3195 IF t_rsv_id(k) IS NULL OR t_proc_rsvs.EXISTS(t_rsv_id(k)) THEN
3196 GOTO skip_rsv;
3197 END IF;
3198
3199 BEGIN
3200 OPEN c_get_rsv_dtl(t_rsv_id(k));
3201 FETCH c_get_rsv_dtl INTO l_rsv_dtl_rec;
3202 CLOSE c_get_rsv_dtl;
3203 EXCEPTION
3204 WHEN OTHERS THEN
3205 IF (l_debug = 1) THEN
3206 print_debug('Error from c_get_rsv_dtl: ' || SQLERRM, 4);
3207 END IF;
3208 IF c_get_rsv_dtl%ISOPEN THEN
3209 CLOSE c_get_rsv_dtl;
3210 END IF;
3211 GOTO skip_rsv;
3212 END;
3213
3214 IF (l_debug = 1) THEN
3215 print_debug('Data from c_get_rsv_dtl: ' ||
3216 ', pri qty: ' || l_rsv_dtl_rec.primary_reservation_quantity ||
3217 ', dtl qty: ' || l_rsv_dtl_rec.detailed_quantity
3218 , 4);
3219 END IF;
3220
3221 IF l_onh_status THEN
3222 OPEN c_onh_stat_rsv_qty(t_rsv_id(k));
3223 FETCH c_onh_stat_rsv_qty INTO l_tot_rsv_pri_qty;
3224 CLOSE c_onh_stat_rsv_qty;
3225 ELSE
3226 IF p_lot_number IS NOT NULL THEN
3227 OPEN c_lot_rsv_qty(t_rsv_id(k));
3228 FETCH c_lot_rsv_qty INTO l_tot_rsv_pri_qty;
3229 CLOSE c_lot_rsv_qty;
3230 ELSE
3231 OPEN c_rsv_qty(t_rsv_id(k));
3232 FETCH c_rsv_qty INTO l_tot_rsv_pri_qty;
3233 CLOSE c_rsv_qty;
3234 END IF;
3235 END IF;
3236
3237 IF (l_debug = 1) THEN
3238 print_debug('l_tot_rsv_pri_qty: ' || l_tot_rsv_pri_qty, 4);
3239 END IF;
3240
3241 IF l_tot_rsv_pri_qty > 0
3242 AND l_tot_rsv_pri_qty > l_rsv_dtl_rec.detailed_quantity
3243 THEN
3244 IF (l_debug = 1) THEN
3245 print_debug('Updating dtl qty on MR', 4);
3246 END IF;
3247
3248 UPDATE mtl_reservations
3249 SET detailed_quantity = LEAST(primary_reservation_quantity,l_tot_rsv_pri_qty)
3250 WHERE reservation_id = t_rsv_id(k);
3251 END IF;
3252
3253 t_proc_rsvs(t_rsv_id(k)) := 'Y';
3254
3255 <<skip_rsv>>
3256 NULL;
3257 END LOOP; --} end loop through rsv IDs
3258 END IF; --} incr MR dtl qty if incr MMTT
3259 END IF; --} end if +ve adj or residual primary txns or cycle count check required
3260 END IF; --} end if qty adjustment or cycle count check required
3261 END IF; --} end if record source is MMTT
3262
3263 -- End of API body
3264 IF fnd_api.to_boolean(p_commit) THEN
3265 COMMIT WORK;
3266 END IF;
3267
3268 IF ( l_debug = 1 ) THEN
3269 print_debug(l_api_name || ' Exited ', 1);
3270 END IF;
3271
3272 EXCEPTION
3273 WHEN fnd_api.g_exc_error THEN
3274 ROLLBACK TO update_lpn_primary_qty_sp;
3275 x_return_status := fnd_api.g_ret_sts_error;
3276 fnd_msg_pub.count_and_get
3277 ( p_count => x_msg_count
3278 , p_data => x_msg_data
3279 , p_encoded => fnd_api.g_false
3280 );
3281 IF (l_debug = 1) THEN
3282 print_debug (x_msg_data, 4);
3283 END IF;
3284
3285 WHEN OTHERS THEN
3286 IF (l_debug = 1) THEN
3287 print_debug('Error: ' || SQLERRM, 1);
3288 END IF;
3289
3290 ROLLBACK TO update_lpn_primary_qty_sp;
3291 x_return_status := fnd_api.g_ret_sts_unexp_error;
3292 x_msg_data := SQLERRM;
3293
3294 END update_lpn_primary_quantity;
3295
3296
3297 --
3298 -- Procedure: Update_Delivery_Detail_Secondary_Quantity
3299 -- Parameters:
3300 -- Description:
3301 --
3302 PROCEDURE Update_LPN_Secondary_Quantity (
3303 p_api_version IN NUMBER
3304 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3305 , p_commit IN VARCHAR2 := fnd_api.g_false
3306 , x_return_status OUT NOCOPY VARCHAR2
3307 , x_msg_count OUT NOCOPY NUMBER
3308 , x_msg_data OUT NOCOPY VARCHAR2
3309 , p_record_source IN VARCHAR2
3310 , p_organization_id IN NUMBER
3311 , p_lpn_id IN NUMBER
3312 , p_inventory_item_id IN NUMBER
3313 , p_revision IN VARCHAR2 := NULL
3314 , p_lot_number IN VARCHAR2 := NULL
3315 , p_quantity IN NUMBER
3316 , p_uom_code IN VARCHAR2
3317 , p_secondary_quantity IN NUMBER
3318 , p_secondary_uom_code IN VARCHAR2
3319 ) IS
3320 l_api_name CONSTANT VARCHAR2(30) := 'Update_LPN_Secondary_Quantity';
3321 l_api_version CONSTANT NUMBER := 1.0;
3322 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3323 l_progress VARCHAR2(10) := '0';
3324
3325 CURSOR mmtt_cur IS
3326 SELECT rowid, transaction_temp_id, transaction_quantity, transaction_uom
3327 FROM mtl_material_transactions_temp
3328 WHERE organization_id = p_organization_id
3329 AND inventory_item_id = p_inventory_item_id
3330 AND NVL(revision, '@') = NVL(p_revision, '@')
3331 AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
3332 AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
3333 AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id;
3334
3335 CURSOR mtlt_cur(p_trx_temp_id NUMBER) IS
3336 SELECT rowid, transaction_quantity
3337 FROM mtl_transaction_lots_temp
3338 WHERE transaction_temp_id = p_trx_temp_id
3339 AND lot_number = p_lot_number;
3340
3341 l_del_det_id NUMBER;
3342 l_line_quantity NUMBER;
3343 l_total_quantity NUMBER := 0;
3344 BEGIN
3345 SAVEPOINT UPDATE_LPN_SECONDARY_QUANTITY;
3346
3347 -- Standard call to check for call compatibility.
3348 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3349 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
3350 fnd_msg_pub.ADD;
3351 RAISE fnd_api.g_exc_unexpected_error;
3352 END IF;
3353
3354 -- Initialize message list if p_init_msg_list is set to TRUE.
3355 IF fnd_api.to_boolean(p_init_msg_list) THEN
3356 fnd_msg_pub.initialize;
3357 END IF;
3358
3359 -- Initialize API return status to success
3360 x_return_status := fnd_api.g_ret_sts_success;
3361
3362 IF ( l_debug = 1 ) THEN
3363 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
3364 print_debug('recsrc='||p_record_source ||' orgid='||p_organization_id||' lpnid='||p_lpn_id||' itemid='||p_inventory_item_id||' rev='||p_revision||' lot='||p_lot_number, 4);
3365 print_debug('qty='||p_quantity||' uom='||p_uom_code||' secqty='||p_secondary_quantity||' secuom'||p_secondary_uom_code, 4);
3366 END IF;
3367
3368 IF ( p_record_source = 'WDD' OR p_record_source = 'wdd' ) THEN
3369 l_progress := '100';
3370 -- LPN is in staging, Update WSH_DELIVERY_DETAILS
3371 -- Need to retrieve the delviery_detail_id for the LPN, then pass to
3372 -- Other API from WDD processing
3373 BEGIN
3374 SELECT delivery_detail_id
3375 INTO l_del_det_id
3376 FROM wsh_delivery_details
3377 WHERE organization_id = p_organization_id
3378 AND lpn_id = p_lpn_id
3379 AND released_status = 'X'; -- For LPN reuse ER : 6845650
3380 EXCEPTION
3381 WHEN NO_DATA_FOUND THEN
3382 l_del_det_id := NULL;
3383 END;
3384
3385 l_progress := '200';
3386 IF ( l_del_det_id IS NOT NULL ) THEN
3387 Update_Parent_Delivery_Sec_Qty (
3388 p_api_version => 1.0
3389 , x_return_status => x_return_status
3390 , x_msg_count => x_msg_count
3391 , x_msg_data => x_msg_data
3392 , p_organization_id => p_organization_id
3393 , p_parent_del_det_id => l_del_det_id
3394 , p_inventory_item_id => p_inventory_item_id
3395 , p_revision => p_revision
3396 , p_lot_number => p_lot_number
3397 , p_quantity => p_quantity
3398 , p_uom_code => p_uom_code
3399 , p_secondary_quantity => p_secondary_quantity
3400 , p_secondary_uom_code => p_secondary_uom_code );
3401
3402 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
3403 RAISE fnd_api.g_exc_unexpected_error;
3404 END IF;
3405 END IF;
3406 ELSIF ( p_record_source = 'MMTT' OR p_record_source = 'mmtt' ) THEN
3407 l_progress := '300';
3408 -- This is assumed to be an update of LPNs that are before drop, update MMTT
3409 IF ( p_lot_number IS NULL ) THEN
3410 l_progress := '400';
3411 IF ( p_secondary_quantity IS NULL ) THEN
3412 -- Caller wants to null out all secondary_quantity for this item
3413 UPDATE mtl_material_transactions_temp
3414 SET secondary_transaction_quantity = NULL,
3415 secondary_uom_code = NULL
3416 WHERE organization_id = p_organization_id
3417 AND inventory_item_id = p_inventory_item_id
3418 AND NVL(revision, '@') = NVL(p_revision, '@')
3419 AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
3420 AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
3421 AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id;
3422 ELSE
3423 FOR mmtt_rec IN mmtt_cur LOOP
3424 IF ( l_debug = 1 ) THEN
3425 print_debug('Got from MMTT trxtempid='||mmtt_rec.transaction_temp_id||' trxqty='||mmtt_rec.transaction_quantity||' trxuom='||mmtt_rec.transaction_uom, 4);
3426 END IF;
3427
3428 IF ( mmtt_rec.transaction_uom <> p_uom_code ) THEN
3429 l_line_quantity := inv_convert.inv_um_convert(
3430 p_inventory_item_id
3431 , 6
3432 , mmtt_rec.transaction_quantity
3433 , mmtt_rec.transaction_uom
3434 , p_uom_code
3435 , NULL
3436 , NULL );
3437 IF ( l_line_quantity < 0 ) THEN
3438 IF ( l_debug = 1 ) THEN
3439 print_debug('Error converting to trx qty from '||mmtt_rec.transaction_uom||' to '||p_uom_code, 1);
3440 END IF;
3441 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
3442 fnd_message.set_token('uom1', mmtt_rec.transaction_uom);
3443 fnd_message.set_token('uom2', p_uom_code);
3444 fnd_message.set_token('module', l_api_name);
3445 fnd_msg_pub.ADD;
3446 RAISE FND_API.G_EXC_ERROR;
3447 END IF;
3448 ELSE
3449 l_line_quantity := mmtt_rec.transaction_quantity;
3450 END IF;
3451
3452 UPDATE mtl_material_transactions_temp
3453 SET secondary_transaction_quantity = round(p_secondary_quantity*(l_line_quantity/p_quantity), g_precision),
3454 secondary_uom_code = p_secondary_uom_code
3455 WHERE rowid = mmtt_rec.rowid;
3456
3457 --Add to the total primary quantity for sanity check at the end
3458 l_total_quantity := l_total_quantity + l_line_quantity;
3459 END LOOP;
3460 END IF;
3461 l_progress := '500';
3462 ELSE -- p_lot_number is not null
3463 l_progress := '600';
3464 IF ( p_secondary_quantity IS NULL ) THEN
3465 -- Caller wants to null out all secondary_quantity for this lot
3466 UPDATE mtl_transaction_lots_temp
3467 SET secondary_quantity = NULL,
3468 secondary_unit_of_measure = NULL
3469 WHERE lot_number = p_lot_number
3470 AND transaction_temp_id IN (
3471 SELECT transaction_temp_id
3472 FROM mtl_material_transactions_temp mmtt
3473 WHERE organization_id = p_organization_id
3474 AND inventory_item_id = p_inventory_item_id
3475 AND NVL(revision, '@') = NVL(p_revision, '@')
3476 AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
3477 AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
3478 AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id );
3479 ELSE
3480 FOR mmtt_rec IN mmtt_cur LOOP
3481 IF ( l_debug = 1 ) THEN
3482 print_debug('Got from MMTT trxtempid='||mmtt_rec.transaction_temp_id||' trxqty='||mmtt_rec.transaction_quantity||' trxuom='||mmtt_rec.transaction_uom, 4);
3483 END IF;
3484
3485 FOR mtlt_rec IN mtlt_cur(mmtt_rec.transaction_temp_id) LOOP
3486 IF ( l_debug = 1 ) THEN
3487 print_debug('Got form MTLT lottrxqty='||mtlt_rec.transaction_quantity, 4);
3488 END IF;
3489
3490 IF ( mmtt_rec.transaction_uom <> p_uom_code ) THEN
3491 l_line_quantity := inv_convert.inv_um_convert(
3492 p_inventory_item_id
3493 , 6
3494 , mtlt_rec.transaction_quantity
3495 , mmtt_rec.transaction_uom
3496 , p_uom_code
3497 , NULL
3498 , NULL );
3499 IF ( l_line_quantity < 0 ) THEN
3500 IF ( l_debug = 1 ) THEN
3501 print_debug('Error converting to trx qty from '||mmtt_rec.transaction_uom||' to '||p_uom_code, 1);
3502 END IF;
3503 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
3504 fnd_message.set_token('uom1', mmtt_rec.transaction_uom);
3505 fnd_message.set_token('uom2', p_uom_code);
3506 fnd_message.set_token('module', l_api_name);
3507 fnd_msg_pub.ADD;
3508 RAISE FND_API.G_EXC_ERROR;
3509 END IF;
3510 ELSE
3511 l_line_quantity := mtlt_rec.transaction_quantity;
3512 END IF;
3513
3514 UPDATE mtl_transaction_lots_temp
3515 SET secondary_quantity = round(p_secondary_quantity*(l_line_quantity/p_quantity), g_precision),
3516 secondary_unit_of_measure = p_secondary_uom_code
3517 WHERE rowid = mtlt_rec.rowid;
3518
3519 --Add to the total primary quantity for sanity check at the end
3520 l_total_quantity := l_total_quantity + l_line_quantity;
3521 END LOOP;
3522 END LOOP;
3523 END IF;
3524 END IF;
3525 l_progress := '700';
3526 -- Sanity check to make sure the correct quantity of items were updated
3527 IF(p_secondary_quantity IS NOT NULL AND round(p_quantity, g_precision) <> round(l_total_quantity, g_precision) ) THEN
3528 IF (l_debug = 1) THEN
3529 print_debug('the p_quantity '||p_quantity||' does not match the sum quantity '||l_total_quantity, 9);
3530 END IF;
3531 FND_MESSAGE.SET_NAME('WMS','WMS_QTY_UPD_MISMATCH_ERROR');
3532 FND_MESSAGE.SET_TOKEN('QTY1', p_quantity);
3533 FND_MESSAGE.SET_TOKEN('QTY2', l_total_quantity);
3534 FND_MSG_PUB.ADD;
3535 RAISE FND_API.G_EXC_ERROR;
3536 END IF;
3537 END IF;
3538
3539 l_progress := '800';
3540 -- End of API body
3541 IF fnd_api.to_boolean(p_commit) THEN
3542 COMMIT WORK;
3543 END IF;
3544
3545 IF ( l_debug = 1 ) THEN
3546 print_debug(l_api_name || ' Exited ', 1);
3547 END IF;
3548
3549 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3550 EXCEPTION
3551 WHEN OTHERS THEN
3552 IF (l_debug = 1) THEN
3553 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
3554 IF ( SQLCODE IS NOT NULL ) THEN
3555 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
3556 END IF;
3557 END IF;
3558
3559 ROLLBACK TO UPDATE_LPN_SECONDARY_QUANTITY;
3560 x_return_status := fnd_api.g_ret_sts_error;
3561 fnd_message.set_name('WMS', 'WMS_OTHERS_ERROR_CALL'||l_progress);
3562 fnd_msg_pub.ADD;
3563 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3564 END Update_LPN_Secondary_Quantity;
3565
3566
3567 FUNCTION Check_LPN_Secondary_Quantity (
3568 p_api_version IN NUMBER
3569 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3570 , x_return_status OUT NOCOPY VARCHAR2
3571 , x_msg_count OUT NOCOPY NUMBER
3572 , x_msg_data OUT NOCOPY VARCHAR2
3573 , p_organization_id IN NUMBER
3574 , p_outermost_lpn_id IN NUMBER
3575 ) RETURN NUMBER IS
3576 l_api_name CONSTANT VARCHAR2(30) := 'Check_LPN_Secondary_Quantity';
3577 l_api_version CONSTANT NUMBER := 1.0;
3578 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3579 l_progress VARCHAR2(10) := '0';
3580
3581 CURSOR wdd_nested_lpn_cur IS
3582 SELECT wlpn.lpn_id, wdd.delivery_detail_id
3583 FROM wms_license_plate_numbers wlpn,
3584 wsh_delivery_details wdd
3585 WHERE wlpn.organization_id = p_organization_id
3586 AND wlpn.outermost_lpn_id = p_outermost_lpn_id
3587 AND wdd.organization_id = wlpn.organization_id
3588 AND wdd.lpn_id = wlpn.lpn_id
3589 AND wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
3590
3591 CURSOR wdd_item_cur (p_parent_delivery_detail_id NUMBER) IS
3592 SELECT distinct wdd.organization_id, wdd.inventory_item_id, msi.primary_uom_code, msi.secondary_uom_code
3593 FROM mtl_system_items msi,
3594 wsh_delivery_details wdd,
3595 wsh_delivery_assignments_v wda
3596 WHERE wda.parent_delivery_detail_id = p_parent_delivery_detail_id
3597 AND wdd.delivery_detail_id = wda.delivery_detail_id
3598 AND wdd.line_direction = 'O'
3599 AND wdd.picked_quantity2 IS NULL
3600 AND msi.organization_id = wdd.organization_id
3601 AND msi.inventory_item_id = wdd.inventory_item_id
3602 AND msi.ont_pricing_qty_source = G_PRICE_SECONDARY
3603 AND msi.secondary_default_ind = G_SECONDARY_DEFAULT
3604 ORDER BY msi.primary_uom_code, msi.secondary_uom_code;
3605
3606 CURSOR mmtt_item_cur IS
3607 SELECT distinct inventory_item_id, organization_id
3608 FROM mtl_material_transactions_temp
3609 WHERE organization_id = p_organization_id
3610 AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
3611 AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
3612 AND ( transfer_lpn_id = p_outermost_lpn_id OR content_lpn_id = p_outermost_lpn_id )
3613 AND ( secondary_transaction_quantity IS NULL OR secondary_uom_code IS NULL );
3614
3615 l_return NUMBER := G_CHECK_SUCCESS;
3616 l_lpn_context NUMBER;
3617 l_temp NUMBER := 0;
3618 l_prev_org_id NUMBER := -999;
3619 l_prev_item_id NUMBER := -999;
3620 l_prev_pri_uom VARCHAR2(3) := '@';
3621 l_prev_sec_uom VARCHAR2(3) := '@';
3622
3623 l_pricing_ind VARCHAR2(30);
3624 l_default_ind VARCHAR2(30);
3625 l_pri_uom VARCHAR2(3);
3626 l_sec_uom VARCHAR2(3);
3627 l_lot_control_code NUMBER;
3628 l_uom_conv_rate NUMBER;
3629 BEGIN
3630 -- Standard call to check for call compatibility.
3631 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
3632 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
3633 fnd_msg_pub.ADD;
3634 RAISE fnd_api.g_exc_unexpected_error;
3635 END IF;
3636
3637 -- Initialize message list if p_init_msg_list is set to TRUE.
3638 IF fnd_api.to_boolean(p_init_msg_list) THEN
3639 fnd_msg_pub.initialize;
3640 END IF;
3641
3642 -- Initialize API return status to success
3643 x_return_status := fnd_api.g_ret_sts_success;
3644
3645 IF ( l_debug = 1 ) THEN
3646 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
3647 print_debug('orgid='||p_organization_id||' outerlpnid='||p_outermost_lpn_id, 4);
3648 END IF;
3649
3650 BEGIN
3651 SELECT lpn_context
3652 INTO l_lpn_context
3653 FROM wms_license_plate_numbers
3654 WHERE organization_id = p_organization_id
3655 AND lpn_id = p_outermost_lpn_id;
3656 EXCEPTION
3657 WHEN NO_DATA_FOUND THEN
3658 IF ( l_debug = 1 ) THEN
3659 print_debug('Error Could not find outermost lpn', 1);
3660 END IF;
3661 RAISE FND_API.G_EXC_ERROR;
3662 WHEN OTHERS THEN
3663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3664 END;
3665
3666 l_progress := '000';
3667 IF ( l_lpn_context = WMS_CONTAINER_PVT.LPN_CONTEXT_PICKED OR
3668 l_lpn_context = WMS_CONTAINER_PVT.LPN_LOADED_FOR_SHIPMENT ) THEN
3669 l_progress := '100';
3670 -- Records should be checked in WDD to see if any catch weight item is
3671 -- is missing secondary quantities (picked_quantity2)
3672 FOR wdd_nested_lpn_rec IN wdd_nested_lpn_cur LOOP
3673 IF ( l_debug = 1 ) THEN
3674 print_debug('lpnid='||wdd_nested_lpn_rec.lpn_id||' ddid='||wdd_nested_lpn_rec.delivery_detail_id, 1);
3675 END IF;
3676
3677 -- Check LPN that all catch weight enabled items that are not defaultable have sec qty.
3678 -- bug 4918256 only for WDD lines that are part of sales orders (line_direction = 'O')
3679 BEGIN
3680 SELECT 1 INTO l_temp FROM DUAL
3681 WHERE EXISTS (
3682 SELECT 1
3683 FROM mtl_system_items msi,
3684 wsh_delivery_details wdd,
3685 wsh_delivery_assignments_v wda
3686 WHERE wda.parent_delivery_detail_id = wdd_nested_lpn_rec.delivery_detail_id
3687 AND wdd.delivery_detail_id = wda.delivery_detail_id
3688 AND wdd.line_direction = 'O'
3689 AND wdd.picked_quantity2 IS NULL
3690 AND msi.organization_id = wdd.organization_id
3691 AND msi.inventory_item_id = wdd.inventory_item_id
3692 AND msi.ont_pricing_qty_source = G_PRICE_SECONDARY
3693 AND msi.secondary_default_ind = G_SECONDARY_NO_DEFAULT );
3694
3695 IF ( l_temp = 1 ) THEN
3696 IF ( l_debug = 1 ) THEN
3697 print_debug('Found lpn in wdd with catch weight item that require secondary qty: lpnid=' ||wdd_nested_lpn_rec.lpn_id, 1);
3698 END IF;
3699
3700 l_return := G_CHECK_ERROR;
3701 EXIT;
3702 END IF;
3703 EXCEPTION
3704 WHEN NO_DATA_FOUND THEN
3705 -- All the items in this lpn check out, continue
3706 NULL;
3707 WHEN OTHERS THEN
3708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3709 END;
3710
3711 -- Check LPN that all catch weight enabled items that are defaultable and do not
3712 -- have sec qty defined have a valid uom conversion.
3713 FOR wdd_item_rec IN wdd_item_cur(wdd_nested_lpn_rec.delivery_detail_id) LOOP
3714 -- Check that there is a valid uom conversin between primary and secondary.
3715 IF ( wdd_item_rec.primary_uom_code <> l_prev_pri_uom OR
3716 wdd_item_rec.secondary_uom_code <> l_prev_sec_uom ) THEN
3717 -- Call UOM API to check that there is a valid conversion rate
3718 INV_CONVERT.inv_um_conversion (
3719 from_unit => wdd_item_rec.primary_uom_code
3720 , to_unit => wdd_item_rec.secondary_uom_code
3721 , item_id => wdd_item_rec.inventory_item_id
3722 , uom_rate => l_uom_conv_rate );
3723
3724 IF ( l_uom_conv_rate < 0 ) THEN
3725 -- no valid connection uom conversion between these two uoms
3726 l_return := G_CHECK_ERROR;
3727
3728 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
3729 fnd_msg_pub.ADD;
3730 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3731 ELSE
3732 -- there is a valid conversion, change status to warning
3733 l_return := G_CHECK_WARNING;
3734 l_prev_pri_uom := wdd_item_rec.primary_uom_code;
3735 l_prev_sec_uom := wdd_item_rec.secondary_uom_code;
3736 END IF;
3737 END IF;
3738 END LOOP;
3739
3740 -- Exit at any point when a catch weight cannot be resolved
3741 EXIT WHEN l_return = G_CHECK_ERROR;
3742 END LOOP;
3743 ELSIF ( l_lpn_context = WMS_CONTAINER_PVT.LPN_CONTEXT_INV OR
3744 l_lpn_context = WMS_CONTAINER_PVT.LPN_CONTEXT_PACKING OR
3745 l_lpn_context = WMS_CONTAINER_PVT.LPN_CONTEXT_PREGENERATED ) THEN
3746 l_progress := '600';
3747 -- Records should be checked in MMTT to see if any catch weight item is
3748 -- is missing secondary transaction quantities. Currently during picking
3749 -- LPNs can only be nested one level deep. This cursor makes that assumtion
3750 FOR mmtt_item_rec IN mmtt_item_cur LOOP
3751 BEGIN
3752 SELECT ont_pricing_qty_source, secondary_default_ind, primary_uom_code,
3753 secondary_uom_code, lot_control_code
3754 INTO l_pricing_ind, l_default_ind, l_pri_uom, l_sec_uom, l_lot_control_code
3755 FROM mtl_system_items
3756 WHERE organization_id = mmtt_item_rec.organization_id
3757 AND inventory_item_id = mmtt_item_rec.inventory_item_id;
3758 EXCEPTION
3759 WHEN NO_DATA_FOUND THEN
3760 IF ( l_debug = 1 ) THEN
3761 print_debug('Error: could not find item in MSI: orgid='||mmtt_item_rec.organization_id||' itemid='||mmtt_item_rec.inventory_item_id, 1);
3762 END IF;
3763 RAISE FND_API.G_EXC_ERROR;
3764 WHEN OTHERS THEN
3765 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3766 END;
3767
3768 IF ( l_pricing_ind = G_PRICE_SECONDARY ) THEN
3769 -- If the item is not lot controlled, return to user that lpn still needs catch weight
3770 IF (l_lot_control_code = 1 ) THEN
3771 -- if item can defaulted check uom conversion to make sure it is valid
3772 IF ( l_default_ind = G_SECONDARY_DEFAULT ) THEN
3773 IF ( l_prev_pri_uom <> l_pri_uom OR l_prev_sec_uom <> l_sec_uom ) THEN
3774 -- Call UOM API to check that there is a valid conversion rate
3775 INV_CONVERT.inv_um_conversion (
3776 from_unit => l_pri_uom
3777 , to_unit => l_sec_uom
3778 , item_id => mmtt_item_rec.inventory_item_id
3779 , uom_rate => l_uom_conv_rate );
3780
3781 IF ( l_uom_conv_rate < 0 ) THEN
3782 -- no valid connection uom conversion between these two uoms
3783 l_return := G_CHECK_ERROR;
3784
3785 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
3786 fnd_msg_pub.ADD;
3787 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3788 ELSE
3789 -- there is a valid conversion, change status to warning
3790 l_return := G_CHECK_WARNING;
3791 l_prev_pri_uom := l_pri_uom;
3792 l_prev_sec_uom := l_sec_uom;
3793 END IF;
3794 END IF;
3795 ELSE
3796 IF ( l_debug = 1 ) THEN
3797 print_debug('Found catch weight item in mmtt with item that requires secondary qty: itemid=' ||mmtt_item_rec.inventory_item_id, 1);
3798 END IF;
3799
3800 l_return := G_CHECK_ERROR;
3801 END IF;
3802 ELSE -- Lot controlled item need to check MTLT for sec qty for
3803 BEGIN
3804 SELECT 1 INTO l_temp FROM DUAL
3805 WHERE EXISTS (
3806 SELECT 1
3807 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
3808 WHERE mmtt.organization_id = mmtt_item_rec.organization_id
3809 AND mmtt.inventory_item_id = mmtt_item_rec.inventory_item_id
3810 AND mmtt.transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
3811 AND mmtt.transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
3812 AND ( mmtt.transfer_lpn_id = p_outermost_lpn_id OR mmtt.content_lpn_id = p_outermost_lpn_id )
3813 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
3814 AND ( mtlt.secondary_quantity IS NULL OR mtlt.secondary_unit_of_measure IS NULL) );
3815 EXCEPTION
3816 WHEN NO_DATA_FOUND THEN
3817 IF ( l_debug = 1 ) THEN
3818 print_debug('Found lot catch weight item in mtlt with item that requires secondary qty: itemid=' ||mmtt_item_rec.inventory_item_id, 1);
3819 END IF;
3820
3821 l_temp := 0;
3822 WHEN OTHERS THEN
3823 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3824 END;
3825
3826 -- If this lot item was found to not have catch weigth but can be defaulted
3827 -- Check UOM conversion to make sure it's valid
3828 IF ( l_temp = 0 AND l_default_ind = G_SECONDARY_DEFAULT ) THEN
3829 IF ( l_prev_pri_uom <> l_pri_uom OR l_prev_sec_uom <> l_sec_uom ) THEN
3830 -- Call UOM API to check that there is a valid conversion rate
3831 INV_CONVERT.inv_um_conversion (
3832 from_unit => l_pri_uom
3833 , to_unit => l_sec_uom
3834 , item_id => mmtt_item_rec.inventory_item_id
3835 , uom_rate => l_uom_conv_rate );
3836
3837 IF ( l_uom_conv_rate < 0 ) THEN
3838 -- no valid connection uom conversion between these two uoms
3839 l_return := G_CHECK_ERROR;
3840
3841 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
3842 fnd_msg_pub.ADD;
3843 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3844 ELSE
3845 -- there is a valid conversion, change status to warning
3846 l_return := G_CHECK_WARNING;
3847 l_prev_pri_uom := l_pri_uom;
3848 l_prev_sec_uom := l_sec_uom;
3849 END IF;
3850 END IF;
3851 ELSE
3852 IF ( l_debug = 1 ) THEN
3853 print_debug('Found catch weight item in mtlt with item that requires secondary qty: itemid=' ||mmtt_item_rec.inventory_item_id, 1);
3854 END IF;
3855
3856 l_return := G_CHECK_ERROR;
3857 END IF;
3858 END IF;
3859 END IF;
3860
3861 EXIT WHEN l_return = G_CHECK_ERROR;
3862 END LOOP;
3863 END IF;
3864
3865 -- if the return is a warning populate an appropreate
3866 -- message in the message stack
3867 IF ( l_return = G_CHECK_WARNING ) THEN
3868 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_WARNING');
3869 fnd_msg_pub.ADD;
3870 END IF;
3871
3872 IF ( l_debug = 1 ) THEN
3873 print_debug(l_api_name||' Exited '||'ret='||l_return, 1);
3874 END IF;
3875
3876 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3877
3878 RETURN l_return;
3879 EXCEPTION
3880 WHEN OTHERS THEN
3881 IF (l_debug = 1) THEN
3882 print_debug(l_api_name||' Error l_progress=' || l_progress, 1);
3883 IF ( SQLCODE IS NOT NULL ) THEN
3884 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
3885 END IF;
3886 END IF;
3887
3888 x_return_status := fnd_api.g_ret_sts_error;
3889 fnd_message.set_name('WMS', 'WMS_API_FAIL');
3890 fnd_msg_pub.ADD;
3891 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3892 END Check_LPN_Secondary_Quantity;
3893
3894
3895 FUNCTION check_ds_lpn (
3896 p_lpn_id IN NUMBER
3897 , p_organization_id IN NUMBER
3898 , p_inventory_item_id IN NUMBER
3899 ) RETURN VARCHAR2 IS
3900
3901 CURSOR c_get_lpns (p_parent_lpn_id IN NUMBER) IS
3902 SELECT 'x'
3903 FROM wms_lpn_contents wlc, mtl_system_items msi
3904 WHERE wlc.parent_lpn_id IN ( SELECT lpn_id
3905 FROM wms_license_plate_numbers
3906 START WITH lpn_id = p_lpn_id
3907 CONNECT BY PRIOR lpn_id = parent_lpn_id)
3908 AND wlc.inventory_item_id = NVL(p_inventory_item_id,wlc.inventory_item_id)
3909 AND wlc.inventory_item_id = msi.inventory_item_id
3910 AND msi.organization_id = p_organization_id
3911 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
3912 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
3913 );
3914
3915 l_ret_val VARCHAR2(1);
3916 l_dummy VARCHAR2(1);
3917
3918 BEGIN
3919 l_ret_val := 'N';
3920
3921 OPEN c_get_lpns(p_lpn_id);
3922 FETCH c_get_lpns INTO l_dummy;
3923
3924 IF c_get_lpns%FOUND THEN
3925 l_ret_val := 'Y';
3926 END IF;
3927
3928 CLOSE c_get_lpns;
3929 RETURN l_ret_val;
3930
3931 EXCEPTION
3932 WHEN OTHERS THEN
3933 IF c_get_lpns%ISOPEN THEN
3934 CLOSE c_get_lpns;
3935 END IF;
3936 RETURN 'N';
3937 END check_ds_lpn;
3938
3939
3940 FUNCTION check_wsh_lpn (
3941 p_lpn_id IN NUMBER
3942 , p_organization_id IN NUMBER
3943 , p_inventory_item_id IN NUMBER
3944 , p_entry_type IN VARCHAR2
3945 ) RETURN VARCHAR2 IS
3946
3947 CURSOR c_get_all_wdd (p_lpn_wdd_id IN NUMBER) IS
3948 SELECT 'x'
3949 FROM wsh_delivery_details wdd,
3950 mtl_system_items msi
3951 WHERE wdd.delivery_detail_id IN (SELECT delivery_detail_id
3952 FROM wsh_delivery_assignments wda
3953 START WITH parent_delivery_detail_id = p_lpn_wdd_id
3954 CONNECT BY PRIOR delivery_detail_id = parent_delivery_detail_id)
3955 AND wdd.inventory_item_id = NVL(p_inventory_item_id,wdd.inventory_item_id)
3956 AND wdd.container_flag = 'N'
3957 AND msi.inventory_item_id = wdd.inventory_item_id
3958 AND msi.organization_id = p_organization_id
3959 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
3960 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
3961 );
3962
3963 CURSOR c_get_wdd_to_upd (p_lpn_wdd_id IN NUMBER) IS
3964 SELECT 'x'
3965 FROM wsh_delivery_details wdd
3966 WHERE wdd.delivery_detail_id IN (SELECT delivery_detail_id
3967 FROM wsh_delivery_assignments wda
3968 WHERE parent_delivery_detail_id IN
3969 ( SELECT parent_delivery_detail_id
3970 FROM wsh_delivery_assignments wda2
3971 START WITH wda2.parent_delivery_detail_id = p_lpn_wdd_id
3972 CONNECT BY PRIOR wda2.delivery_detail_id = wda2.parent_delivery_detail_id)
3973 AND EXISTS (SELECT 1
3974 FROM wsh_delivery_details wdd2,
3975 wms_license_plate_numbers wlpn
3976 WHERE wdd2.delivery_detail_id = wda.parent_delivery_detail_id
3977 AND wdd2.lpn_id = wlpn.lpn_id
3978 AND NVL(wlpn.CATCH_WEIGHT_FLAG,'N') = 'N') )
3979 AND wdd.inventory_item_id = NVL(p_inventory_item_id,inventory_item_id)
3980 AND wdd.container_flag = 'N'
3981 AND (EXISTS (SELECT 1 FROM mtl_system_items msi
3982 WHERE msi.inventory_item_id = wdd.inventory_item_id
3983 AND msi.organization_id = p_organization_id
3984 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
3985 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
3986 )
3987 )
3988 );
3989
3990 l_lpn_wdd_id NUMBER;
3991 l_dummy VARCHAR2(1);
3992 l_ret_val VARCHAR2(1);
3993
3994 BEGIN
3995 l_ret_val := 'N';
3996
3997 SELECT delivery_detail_id
3998 INTO l_lpn_wdd_id
3999 FROM wsh_delivery_details
4000 WHERE lpn_id = p_lpn_id
4001 AND organization_id = p_organization_id
4002 AND released_status = 'X';
4003
4004 IF upper(p_entry_type) = 'CT_WT_ALL' THEN
4005 OPEN c_get_all_wdd(l_lpn_wdd_id);
4006 FETCH c_get_all_wdd INTO l_dummy;
4007
4008 IF c_get_all_wdd%FOUND THEN
4009 l_ret_val := 'Y';
4010 END IF;
4011 CLOSE c_get_all_wdd;
4012 ELSE
4013 OPEN c_get_wdd_to_upd(l_lpn_wdd_id);
4014 FETCH c_get_wdd_to_upd INTO l_dummy;
4015
4016 IF c_get_wdd_to_upd%FOUND THEN
4017 l_ret_val := 'Y';
4018 END IF;
4019 CLOSE c_get_wdd_to_upd;
4020 END IF;
4021
4022 RETURN l_ret_val;
4023
4024 EXCEPTION
4025 WHEN OTHERS THEN
4026 IF c_get_all_wdd%ISOPEN THEN
4027 CLOSE c_get_all_wdd;
4028 END IF;
4029 IF c_get_wdd_to_upd%ISOPEN THEN
4030 CLOSE c_get_wdd_to_upd;
4031 END IF;
4032 RETURN 'N';
4033 END check_wsh_lpn;
4034
4035
4036 PROCEDURE get_outer_catch_wt_lpn
4037 ( x_lpn_lov OUT NOCOPY t_genref
4038 , p_org_id IN NUMBER
4039 , p_lpn IN VARCHAR2
4040 , p_entry_type IN VARCHAR2) IS
4041 BEGIN
4042 IF (UPPER(p_entry_type) = 'CT_WT_ALL') THEN
4043 OPEN x_lpn_lov FOR
4044 SELECT UNIQUE wlpn2.license_plate_number,
4045 wlpn.outermost_lpn_id outer_lpn_id,
4046 wlpn.lpn_context
4047 FROM wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2
4048 WHERE wlpn.outermost_lpn_id = wlpn2.lpn_id
4049 AND wlpn.lpn_context = 11
4050 AND wlpn.organization_id = p_org_id
4051 AND wlpn.license_plate_number LIKE (p_lpn || '%')
4052 AND EXISTS ( SELECT 'x'
4053 FROM wms_license_plate_numbers wlpn3
4054 , wms_lpn_contents wlc
4055 , mtl_system_items msi
4056 WHERE wlpn3.outermost_lpn_id = wlpn.outermost_lpn_id
4057 AND wlc.parent_lpn_id = wlpn3.lpn_id
4058 AND msi.inventory_item_id = wlc.inventory_item_id
4059 AND msi.organization_id = wlc.organization_id
4060 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
4061 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
4062 )
4063 )
4064 UNION
4065 SELECT UNIQUE wlpn.license_plate_number,
4066 mmtt.transfer_lpn_id,
4067 wlpn.lpn_context
4068 FROM wms_license_plate_numbers wlpn,
4069 mtl_material_transactions_temp mmtt,
4070 mtl_system_items_b msi
4071 WHERE mmtt.inventory_item_id = msi.inventory_item_id
4072 AND mmtt.organization_id = msi.organization_id
4073 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
4074 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
4075 )
4076 AND mmtt.organization_id = p_org_id
4077 AND mmtt.content_lpn_id IS NULL
4078 AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
4079 AND wlpn.lpn_id = mmtt.transfer_lpn_id
4080 AND wlpn.lpn_context = 8 -- loaded
4081 AND wlpn.license_plate_number LIKE (p_lpn || '%')
4082 UNION
4083 SELECT UNIQUE wlpn.license_plate_number,
4084 mmtt.transfer_lpn_id,
4085 wlpn.lpn_context
4086 FROM wms_license_plate_numbers wlpn,
4087 mtl_material_transactions_temp mmtt,
4088 mtl_system_items_b msi
4089 WHERE mmtt.inventory_item_id = msi.inventory_item_id
4090 AND mmtt.organization_id = msi.organization_id
4091 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
4092 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
4093 )
4094 AND EXISTS (SELECT wlpn2.lpn_id
4095 FROM wms_license_plate_numbers wlpn2
4096 WHERE wlpn2.lpn_id = mmtt.content_lpn_id
4097 AND wlpn2.lpn_context = 8)
4098 AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
4099 AND mmtt.organization_id = p_org_id
4100 AND wlpn.lpn_id = mmtt.transfer_lpn_id
4101 AND wlpn.license_plate_number LIKE (p_lpn || '%')
4102 UNION
4103 SELECT UNIQUE wlpn.license_plate_number,
4104 mmtt.transfer_lpn_id,
4105 wlpn.lpn_context
4106 FROM wms_license_plate_numbers wlpn,
4107 mtl_material_transactions_temp mmtt,
4108 mtl_material_transactions_temp mmtt2,
4109 mtl_system_items_b msi
4110 WHERE mmtt2.inventory_item_id = msi.inventory_item_id
4111 AND mmtt2.organization_id = msi.organization_id
4112 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
4113 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
4114 )
4115 AND mmtt2.transfer_lpn_id = mmtt.content_lpn_id
4116 AND mmtt2.parent_line_id IS NULL -- exclude bulk-picked tasks
4117 AND EXISTS (SELECT wlpn2.lpn_id
4118 FROM wms_license_plate_numbers wlpn2
4119 WHERE wlpn2.lpn_id = mmtt.content_lpn_id
4120 AND wlpn2.lpn_context = 8)
4121 AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
4122 AND mmtt.organization_id = p_org_id
4123 AND wlpn.lpn_id = mmtt.transfer_lpn_id
4124 AND wlpn.license_plate_number LIKE (p_lpn || '%');
4125 ELSE
4126 OPEN x_lpn_lov FOR
4127 SELECT UNIQUE wlpn2.license_plate_number,wlpn.outermost_lpn_id,
4128 wlpn2.lpn_context
4129 FROM wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2
4130 WHERE wlpn.outermost_lpn_id = wlpn2.lpn_id
4131 AND wlpn.lpn_context = 11 -- picked
4132 AND wlpn.organization_id = p_org_id
4133 AND wlpn.license_plate_number LIKE (p_lpn || '%')
4134 AND NVL(wlpn2.catch_weight_flag,'N') = 'N'
4135 AND EXISTS ( SELECT 'x'
4136 FROM wms_license_plate_numbers wlpn3
4137 , wms_lpn_contents wlc
4138 , mtl_system_items msi
4139 WHERE wlpn3.outermost_lpn_id = wlpn.outermost_lpn_id
4140 AND wlc.parent_lpn_id = wlpn3.lpn_id
4141 AND msi.inventory_item_id = wlc.inventory_item_id
4142 AND msi.organization_id = wlc.organization_id
4143 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
4144 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
4145 )
4146 )
4147 UNION
4148 SELECT UNIQUE wlpn.license_plate_number,
4149 mmtt.transfer_lpn_id outer_lpn,
4150 wlpn.lpn_context
4151 FROM wms_license_plate_numbers wlpn,
4152 mtl_material_transactions_temp mmtt,
4153 mtl_system_items_b msi
4154 WHERE mmtt.inventory_item_id = msi.inventory_item_id
4155 AND mmtt.organization_id = msi.organization_id
4156 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
4157 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
4158 )
4159 AND mmtt.organization_id = p_org_id
4160 AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
4161 AND mmtt.content_lpn_id IS NULL
4162 AND wlpn.lpn_id = mmtt.transfer_lpn_id
4163 AND wlpn.lpn_context = 8 -- loaded
4164 AND wlpn.license_plate_number LIKE (p_lpn || '%')
4165 AND NVL(wlpn.catch_weight_flag,'N') = 'N'
4166 UNION
4167 SELECT UNIQUE wlpn.license_plate_number,
4168 mmtt.transfer_lpn_id, wlpn.lpn_context
4169 FROM mtl_material_transactions_temp mmtt,
4170 mtl_system_items_b msi,
4171 wms_license_plate_numbers wlpn
4172 WHERE mmtt.inventory_item_id = msi.inventory_item_id
4173 AND mmtt.organization_id = msi.organization_id
4174 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
4175 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
4176 )
4177 AND EXISTS (SELECT wlpn2.lpn_id
4178 FROM wms_license_plate_numbers wlpn2
4179 WHERE wlpn2.lpn_id = mmtt.content_lpn_id
4180 AND wlpn2.lpn_context = 8)
4181 AND mmtt.organization_id = p_org_id
4182 AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
4183 AND mmtt.content_lpn_id IS NOT NULL
4184 AND wlpn.lpn_id = mmtt.transfer_lpn_id
4185 AND wlpn.license_plate_number LIKE (p_lpn || '%')
4186 AND NVL(wlpn.catch_weight_flag,'N') = 'N'
4187 UNION
4188 SELECT UNIQUE wlpn.license_plate_number,
4189 mmtt.transfer_lpn_id,
4190 wlpn.lpn_context
4191 FROM wms_license_plate_numbers wlpn,
4192 mtl_material_transactions_temp mmtt,
4193 mtl_material_transactions_temp mmtt2,
4194 mtl_system_items_b msi
4195 WHERE mmtt2.inventory_item_id = msi.inventory_item_id
4196 AND mmtt2.organization_id = msi.organization_id
4197 AND ( (msi.tracking_quantity_ind = 'PS' AND msi.secondary_default_ind <> 'F')
4198 OR (msi.tracking_quantity_ind = 'P' AND msi.ont_pricing_qty_source = 'S')
4199 )
4200 AND mmtt2.transfer_lpn_id = mmtt.content_lpn_id
4201 AND mmtt2.parent_line_id IS NULL -- exclude bulk-picked tasks
4202 AND EXISTS (SELECT wlpn2.lpn_id
4203 FROM wms_license_plate_numbers wlpn2
4204 WHERE wlpn2.lpn_id = mmtt.content_lpn_id
4205 AND wlpn2.lpn_context = 8)
4206 AND mmtt.parent_line_id IS NULL -- exclude bulk-picked tasks
4207 AND mmtt.organization_id = p_org_id
4208 AND wlpn.lpn_id = mmtt.transfer_lpn_id
4209 AND wlpn.license_plate_number LIKE (p_lpn || '%')
4210 AND NVL(wlpn.catch_weight_flag,'N') = 'N';
4211 END IF;
4212 END get_outer_catch_wt_lpn;
4213
4214
4215 PROCEDURE get_inner_catch_wt_lpn
4216 (x_lpn_lov OUT NOCOPY t_genref,
4217 p_org_id IN NUMBER,
4218 p_outer_lpn_id IN NUMBER,
4219 p_entry_type IN VARCHAR2,
4220 p_lpn_context IN NUMBER,
4221 p_inner_lpn IN VARCHAR2)
4222 IS
4223 BEGIN
4224
4225 IF (UPPER(p_entry_type) = 'DIRECTSHIP') THEN
4226
4227 OPEN x_lpn_lov FOR
4228 SELECT owlpn.license_plate_number, owlpn.lpn_id
4229 FROM (
4230 SELECT wlpn.license_plate_number, wlpn.lpn_id
4231 FROM wms_license_plate_numbers wlpn
4232 WHERE outermost_lpn_id = p_outer_lpn_id
4233 AND wms_catch_weight_pvt.check_ds_lpn( wlpn.lpn_id
4234 , p_org_id
4235 , NULL
4236 ) = 'Y'
4237 ) owlpn
4238 WHERE owlpn.license_plate_number LIKE (p_inner_lpn || '%')
4239 AND NOT EXISTS (SELECT 1 FROM wms_ds_ct_wt_gtemp gt
4240 WHERE NVL(gt.INNER_LPN_ID, gt.LPN_ID) = owlpn.lpn_id);
4241
4242 ELSIF (UPPER(p_entry_type) = 'CT_WT_ALL') THEN
4243
4244 OPEN x_lpn_lov FOR
4245 SELECT DISTINCT wlpn.license_plate_number, lpn_id
4246 FROM wms_license_plate_numbers wlpn
4247 WHERE outermost_lpn_id = p_outer_lpn_id
4248 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%')
4249 AND EXISTS (
4250 SELECT 1
4251 FROM wms_lpn_contents wlc
4252 WHERE wlc.parent_lpn_id = wlpn.lpn_id)
4253 AND wms_catch_weight_pvt.check_wsh_lpn( wlpn.lpn_id
4254 , p_org_id
4255 , NULL
4256 , p_entry_type
4257 ) = 'Y'
4258 UNION
4259 SELECT DISTINCT license_plate_number, content_lpn_id
4260 FROM mtl_system_items msi, mtl_material_transactions_temp mmtt,
4261 wms_license_plate_numbers wlpn
4262 WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
4263 AND mmtt.organization_id = p_org_id
4264 AND mmtt.parent_line_id IS NULL
4265 AND mmtt.organization_id = msi.organization_id
4266 AND mmtt.inventory_item_id = msi.inventory_item_id
4267 AND ( msi.ont_pricing_qty_source = 'S'
4268 OR NVL(mmtt.fulfillment_base,'P') = 'S')
4269 AND wlpn.lpn_id = mmtt.content_lpn_id
4270 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%');
4271 ELSE
4272 IF (p_lpn_context = 8) THEN --Packing
4273 OPEN x_lpn_lov FOR
4274 SELECT distinct wlpn.license_plate_number, mmtt.content_lpn_id
4275 FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
4276 ,wms_license_plate_numbers wlpn
4277 ,mtl_transaction_lots_temp mtlt
4278 WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
4279 AND mmtt.organization_id = p_org_id
4280 AND mmtt.parent_line_id IS NULL
4281 AND mmtt.organization_id = msi.organization_id
4282 AND mmtt.inventory_item_id = msi.inventory_item_id
4283 AND ( ( msi.ont_pricing_qty_source = 'S'
4284 AND NVL(mmtt.fulfillment_base,'P') = 'P'
4285 AND ( (mmtt.secondary_transaction_quantity IS NULL
4286 AND mtlt.secondary_quantity IS NULL)
4287 OR NVL(wlpn.catch_weight_flag,'N') = 'N') )
4288 OR ( NVL(mmtt.fulfillment_base,'P') = 'S'
4289 AND NVL(wlpn.catch_weight_flag,'N') = 'N') )
4290 AND wlpn.lpn_id = mmtt.content_lpn_id
4291 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
4292 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%');
4293 ELSE
4294 OPEN x_lpn_lov FOR
4295 SELECT wlpn.license_plate_number, wlpn.lpn_id
4296 FROM wms_license_plate_numbers wlpn
4297 WHERE wlpn.outermost_lpn_id = p_outer_lpn_id
4298 AND wlpn.lpn_id <> wlpn.outermost_lpn_id
4299 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%')
4300 AND wms_catch_weight_pvt.check_wsh_lpn( wlpn.lpn_id
4301 , p_org_id
4302 , NULL
4303 , p_entry_type
4304 ) = 'Y';
4305 END IF;
4306 END IF;
4307 END get_inner_catch_wt_lpn;
4308
4309
4310 PROCEDURE get_catch_wt_items
4311 (x_item_lov OUT NOCOPY t_genref,
4312 p_org_id IN NUMBER,
4313 p_lpn_id IN NUMBER,
4314 p_entry_type IN VARCHAR2,
4315 p_lpn_context IN NUMBER,
4316 p_concat_item_segment IN VARCHAR2)
4317 IS
4318 l_append varchar2(2):='';
4319 BEGIN
4320
4321 l_append := wms_deploy.get_item_suffix_for_lov(p_concat_item_segment);
4322
4323 IF (UPPER(p_entry_type) = 'DIRECTSHIP') THEN
4324
4325 OPEN x_item_lov FOR
4326 SELECT DISTINCT msiv.concatenated_segments
4327 , msiv.inventory_item_id
4328 , msiv.description
4329 , NVL(msiv.revision_qty_control_code, 1)
4330 , NVL(msiv.lot_control_code, 1)
4331 , NVL(msiv.serial_number_control_code, 1)
4332 , NVL(msiv.restrict_subinventories_code, 2)
4333 , NVL(msiv.restrict_locators_code, 2)
4334 , NVL(msiv.location_control_code, 1)
4335 , msiv.primary_uom_code
4336 , NVL(msiv.inspection_required_flag, 2)
4337 , NVL(msiv.shelf_life_code, 1)
4338 , NVL(msiv.shelf_life_days, 0)
4339 , NVL(msiv.allowed_units_lookup_code, 2)
4340 , NVL(msiv.effectivity_control, 1)
4341 , '0'
4342 , '0'
4343 , '0'
4344 , '0'
4345 , '0'
4346 , '0'
4347 , ''
4348 , 'N'
4349 , msiv.inventory_item_flag
4350 , 0
4351 , wms_deploy.get_item_client_name(msiv.inventory_item_id)
4352 --Bug No 3952081
4353 --Additional Fields for Process Convergence
4354 , NVL(msiv.grade_control_flag,'N')
4355 , NVL(msiv.default_grade,'')
4356 , NVL(msiv.expiration_action_interval,0)
4357 , NVL(msiv.expiration_action_code,'')
4358 , NVL(msiv.hold_days,0)
4359 , NVL(msiv.maturity_days,0)
4360 , NVL(msiv.retest_interval,0)
4361 , NVL(msiv.copy_lot_attribute_flag,'N')
4362 , NVL(msiv.child_lot_flag,'N')
4363 , NVL(msiv.child_lot_validation_flag,'N')
4364 , NVL(msiv.lot_divisible_flag,'Y')
4365 , NVL(msiv.secondary_uom_code,'')
4366 , NVL(msiv.secondary_default_ind,'')
4367 , NVL(msiv.tracking_quantity_ind,'P')
4368 , NVL(msiv.dual_uom_deviation_high,0)
4369 , NVL(msiv.dual_uom_deviation_low,0)
4370 FROM mtl_system_items_kfv msiv,
4371 wms_lpn_contents wlc
4372 WHERE wlc.parent_lpn_id = p_lpn_id
4373 AND wlc.organization_id = p_org_id
4374 AND wms_catch_weight_pvt.check_ds_lpn( p_lpn_id
4375 , p_org_id
4376 , wlc.inventory_item_id
4377 ) = 'Y'
4378 AND msiv.inventory_item_id = wlc.inventory_item_id
4379 AND msiv.organization_id = wlc.organization_id
4380 AND msiv.concatenated_segments LIKE (p_concat_item_segment || '%' || l_append)
4381 AND NOT EXISTS (SELECT 1 FROM wms_ds_ct_wt_gtemp gt
4382 WHERE gt.inventory_item_id = wlc.inventory_item_id
4383 AND gt.org_id = wlc.organization_id
4384 AND NVL(gt.inner_lpn_id, gt.lpn_id) = wlc.parent_lpn_id);
4385
4386 ELSIF (UPPER(p_entry_type) = 'CT_WT_ALL') THEN
4387
4388 OPEN x_item_lov FOR
4389
4390 SELECT DISTINCT msi.concatenated_segments
4391 , msi.inventory_item_id
4392 , msi.description
4393 , NVL(msi.revision_qty_control_code, 1)
4394 , NVL(msi.lot_control_code, 1)
4395 , NVL(msi.serial_number_control_code, 1)
4396 , NVL(msi.restrict_subinventories_code, 2)
4397 , NVL(msi.restrict_locators_code, 2)
4398 , NVL(msi.location_control_code, 1)
4399 , msi.primary_uom_code
4400 , NVL(msi.inspection_required_flag, 2)
4401 , NVL(msi.shelf_life_code, 1)
4402 , NVL(msi.shelf_life_days, 0)
4403 , NVL(msi.allowed_units_lookup_code, 2)
4404 , NVL(msi.effectivity_control, 1)
4405 , '0'
4406 , '0'
4407 , '0'
4408 , '0'
4409 , '0'
4410 , '0'
4411 , ''
4412 , 'N'
4413 , msi.inventory_item_flag
4414 , 0
4415 , wms_deploy.get_item_client_name(msi.inventory_item_id)
4416 --Bug No 3952081
4417 --Additional Fields for Process Convergence
4418 , NVL(msi.grade_control_flag,'N')
4419 , NVL(msi.default_grade,'')
4420 , NVL(msi.expiration_action_interval,0)
4421 , NVL(msi.expiration_action_code,'')
4422 , NVL(msi.hold_days,0)
4423 , NVL(msi.maturity_days,0)
4424 , NVL(msi.retest_interval,0)
4425 , NVL(msi.copy_lot_attribute_flag,'N')
4426 , NVL(msi.child_lot_flag,'N')
4427 , NVL(msi.child_lot_validation_flag,'N')
4428 , NVL(msi.lot_divisible_flag,'Y')
4429 , NVL(msi.secondary_uom_code,'')
4430 , NVL(msi.secondary_default_ind,'')
4431 , NVL(msi.tracking_quantity_ind,'P')
4432 , NVL(msi.dual_uom_deviation_high,0)
4433 , NVL(msi.dual_uom_deviation_low,0)
4434 FROM mtl_system_items_kfv msi, wms_lpn_contents wlc
4435 WHERE wlc.parent_lpn_id = p_lpn_id
4436 AND wlc.inventory_item_id = msi.inventory_item_id
4437 AND wlc.organization_id = msi.organization_id
4438 AND wms_catch_weight_pvt.check_wsh_lpn( p_lpn_id
4439 , p_org_id
4440 , wlc.inventory_item_id
4441 , p_entry_type
4442 ) = 'Y'
4443 UNION
4444 SELECT DISTINCT msi.concatenated_segments
4445 , msi.inventory_item_id
4446 , msi.description
4447 , NVL(msi.revision_qty_control_code, 1)
4448 , NVL(msi.lot_control_code, 1)
4449 , NVL(msi.serial_number_control_code, 1)
4450 , NVL(msi.restrict_subinventories_code, 2)
4451 , NVL(msi.restrict_locators_code, 2)
4452 , NVL(msi.location_control_code, 1)
4453 , msi.primary_uom_code
4454 , NVL(msi.inspection_required_flag, 2)
4455 , NVL(msi.shelf_life_code, 1)
4456 , NVL(msi.shelf_life_days, 0)
4457 , NVL(msi.allowed_units_lookup_code, 2)
4458 , NVL(msi.effectivity_control, 1)
4459 , '0'
4460 , '0'
4461 , '0'
4462 , '0'
4463 , '0'
4464 , '0'
4465 , ''
4466 , 'N'
4467 , msi.inventory_item_flag
4468 , 0
4469 , wms_deploy.get_item_client_name(msi.inventory_item_id)
4470 --Bug No 3952081
4471 --Additional Fields for Process Convergence
4472 , NVL(msi.grade_control_flag,'N')
4473 , NVL(msi.default_grade,'')
4474 , NVL(msi.expiration_action_interval,0)
4475 , NVL(msi.expiration_action_code,'')
4476 , NVL(msi.hold_days,0)
4477 , NVL(msi.maturity_days,0)
4478 , NVL(msi.retest_interval,0)
4479 , NVL(msi.copy_lot_attribute_flag,'N')
4480 , NVL(msi.child_lot_flag,'N')
4481 , NVL(msi.child_lot_validation_flag,'N')
4482 , NVL(msi.lot_divisible_flag,'Y')
4483 , NVL(msi.secondary_uom_code,'')
4484 , NVL(msi.secondary_default_ind,'')
4485 , NVL(msi.tracking_quantity_ind,'P')
4486 , NVL(msi.dual_uom_deviation_high,0)
4487 , NVL(msi.dual_uom_deviation_low,0)
4488 FROM mtl_system_items_kfv msi, mtl_material_transactions_temp mmtt
4489 WHERE (mmtt.content_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id = p_lpn_id)
4490 AND mmtt.organization_id = p_org_id
4491 AND mmtt.organization_id = msi.organization_id
4492 AND mmtt.inventory_item_id = msi.inventory_item_id
4493 AND mmtt.parent_line_id IS NULL
4494 AND ( msi.ont_pricing_qty_source = 'S'
4495 OR NVL(mmtt.fulfillment_base,'P') = 'S');
4496
4497 ELSE
4498
4499 IF (p_lpn_context = 8) THEN
4500
4501 OPEN x_item_lov FOR
4502
4503 SELECT DISTINCT msi.concatenated_segments
4504 , msi.inventory_item_id
4505 , msi.description
4506 , NVL(msi.revision_qty_control_code, 1)
4507 , NVL(msi.lot_control_code, 1)
4508 , NVL(msi.serial_number_control_code, 1)
4509 , NVL(msi.restrict_subinventories_code, 2)
4510 , NVL(msi.restrict_locators_code, 2)
4511 , NVL(msi.location_control_code, 1)
4512 , msi.primary_uom_code
4513 , NVL(msi.inspection_required_flag, 2)
4514 , NVL(msi.shelf_life_code, 1)
4515 , NVL(msi.shelf_life_days, 0)
4516 , NVL(msi.allowed_units_lookup_code, 2)
4517 , NVL(msi.effectivity_control, 1)
4518 , '0'
4519 , '0'
4520 , '0'
4521 , '0'
4522 , '0'
4523 , '0'
4524 , ''
4525 , 'N'
4526 , msi.inventory_item_flag
4527 , 0
4528 , wms_deploy.get_item_client_name(msi.inventory_item_id)
4529 --Bug No 3952081
4530 --Additional Fields for Process Convergence
4531 , NVL(msi.grade_control_flag,'N')
4532 , NVL(msi.default_grade,'')
4533 , NVL(msi.expiration_action_interval,0)
4534 , NVL(msi.expiration_action_code,'')
4535 , NVL(msi.hold_days,0)
4536 , NVL(msi.maturity_days,0)
4537 , NVL(msi.retest_interval,0)
4538 , NVL(msi.copy_lot_attribute_flag,'N')
4539 , NVL(msi.child_lot_flag,'N')
4540 , NVL(msi.child_lot_validation_flag,'N')
4541 , NVL(msi.lot_divisible_flag,'Y')
4542 , NVL(msi.secondary_uom_code,'')
4543 , NVL(msi.secondary_default_ind,'')
4544 , NVL(msi.tracking_quantity_ind,'P')
4545 , NVL(msi.dual_uom_deviation_high,0)
4546 , NVL(msi.dual_uom_deviation_low,0)
4547 FROM wms_license_plate_numbers wlpn,
4548 mtl_material_transactions_temp mmtt,
4549 mtl_system_items_kfv msi
4550 WHERE (mmtt.content_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id = p_lpn_id)
4551 AND mmtt.organization_id = p_org_id
4552 AND mmtt.organization_id = msi.organization_id
4553 AND mmtt.inventory_item_id = msi.inventory_item_id
4554 AND mmtt.parent_line_id IS NULL
4555 AND wlpn.lpn_id = p_lpn_id
4556 AND wlpn.organization_id = p_org_id
4557 AND ( ( msi.ont_pricing_qty_source = 'S'
4558 AND NVL(mmtt.fulfillment_base,'P') = 'P'
4559 AND mmtt.secondary_transaction_quantity IS NULL)
4560 OR ( NVL(mmtt.fulfillment_base,'P') = 'S'
4561 AND NVL(wlpn.catch_weight_flag,'N') = 'N') );
4562
4563 ELSE
4564
4565 OPEN x_item_lov FOR
4566 SELECT DISTINCT msi.concatenated_segments
4567 , msi.inventory_item_id
4568 , msi.description
4569 , NVL(msi.revision_qty_control_code, 1)
4570 , NVL(msi.lot_control_code, 1)
4571 , NVL(msi.serial_number_control_code, 1)
4572 , NVL(msi.restrict_subinventories_code, 2)
4573 , NVL(msi.restrict_locators_code, 2)
4574 , NVL(msi.location_control_code, 1)
4575 , msi.primary_uom_code
4576 , NVL(msi.inspection_required_flag, 2)
4577 , NVL(msi.shelf_life_code, 1)
4578 , NVL(msi.shelf_life_days, 0)
4579 , NVL(msi.allowed_units_lookup_code, 2)
4580 , NVL(msi.effectivity_control, 1)
4581 , '0'
4582 , '0'
4583 , '0'
4584 , '0'
4585 , '0'
4586 , '0'
4587 , ''
4588 , 'N'
4589 , msi.inventory_item_flag
4590 , 0
4591 , wms_deploy.get_item_client_name(msi.inventory_item_id)
4592 --Bug No 3952081
4593 --Additional Fields for Process Convergence
4594 , NVL(msi.grade_control_flag,'N')
4595 , NVL(msi.default_grade,'')
4596 , NVL(msi.expiration_action_interval,0)
4597 , NVL(msi.expiration_action_code,'')
4598 , NVL(msi.hold_days,0)
4599 , NVL(msi.maturity_days,0)
4600 , NVL(msi.retest_interval,0)
4601 , NVL(msi.copy_lot_attribute_flag,'N')
4602 , NVL(msi.child_lot_flag,'N')
4603 , NVL(msi.child_lot_validation_flag,'N')
4604 , NVL(msi.lot_divisible_flag,'Y')
4605 , NVL(msi.secondary_uom_code,'')
4606 , NVL(msi.secondary_default_ind,'')
4607 , NVL(msi.tracking_quantity_ind,'P')
4608 , NVL(msi.dual_uom_deviation_high,0)
4609 , NVL(msi.dual_uom_deviation_low,0)
4610 FROM wms_lpn_contents wlc,
4611 mtl_system_items_kfv msi
4612 WHERE wlc.parent_lpn_id = p_lpn_id
4613 AND wlc.organization_id = p_org_id
4614 AND msi.inventory_item_id = wlc.inventory_item_id
4615 AND msi.organization_id = wlc.organization_id
4616 AND wms_catch_weight_pvt.check_wsh_lpn( p_lpn_id
4617 , p_org_id
4618 , wlc.inventory_item_id
4619 , p_entry_type
4620 ) = 'Y';
4621 END IF;
4622 END IF;
4623
4624 END get_catch_wt_items;
4625
4626
4627 PROCEDURE SHOW_CT_WT_FOR_SPLIT (
4628 p_org_id IN NUMBER
4629 , p_from_lpn_id IN NUMBER
4630 , p_from_item_id IN NUMBER
4631 , p_from_item_revision IN VARCHAR2
4632 , p_from_item_lot_number IN VARCHAR2
4633 , p_to_lpn_id IN NUMBER
4634 , x_show_ct_wt OUT NOCOPY NUMBER
4635 , x_return_status OUT NOCOPY VARCHAR2
4636 , x_msg_data OUT NOCOPY VARCHAR2
4637 , x_msg_count OUT NOCOPY NUMBER
4638 ) IS
4639
4640 CURSOR itemCur(in_lpn_id NUMBER) IS
4641 SELECT picked_quantity2
4642 FROM wsh_delivery_details wdd,
4643 mtl_system_items_b msib
4644 WHERE wdd.inventory_item_id = p_from_item_id
4645 AND wdd.inventory_item_id = msib.inventory_item_id
4646 AND wdd.organization_id = p_org_id
4647 AND wdd.organization_id = msib.organization_id
4648 AND NVL(wdd.revision,'@@@') = NVL(NVL(p_from_item_revision,wdd.revision),'@@@')
4649 AND NVL(wdd.lot_number,'@@@') = NVL(p_from_item_lot_number,'@@@')
4650 AND wdd.delivery_detail_id in (SELECT wda.delivery_detail_id
4651 FROM wsh_delivery_assignments_v wda,
4652 wsh_delivery_details wdd1
4653 WHERE wdd1.lpn_id = in_lpn_id
4654 AND wdd1.released_status = 'X' -- For LPN reuse ER : 6845650
4655 AND wdd1.delivery_detail_id = wda.parent_delivery_Detail_id
4656 AND wdd1.organization_id = p_org_id);
4657
4658
4659
4660 l_from_sec_qty NUMBER := 0;
4661 l_to_sec_qty NUMBER := 0;
4662 l_from_item_pri_qty NUMBER := 0 ;
4663 l_to_item_picked_qty NUMBER := 0;
4664 l_api_name CONSTANT VARCHAR2(30) := 'SHOW_CT_WT_FOR_SPLIT';
4665 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4666 l_progress VARCHAR2(30) := '0';
4667 itemRec itemCur%ROWTYPE;
4668
4669
4670 BEGIN
4671
4672 --If p_to_lpn_id is NULL, THEN the context of to_lpn is 5 ELSE 11
4673 --Check to see if ct wt for from_lpn has been entered for that item in WDD
4674 --if YES, check to see
4675 -- if context of to_lpn = 5, then show_ct_wt
4676 -- if context of to_lpn = 11, then see if ct_wt has been entered in WDD for that item
4677 IF ( l_debug = 1 ) THEN
4678 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
4679 print_debug('p_org_id => ' || p_org_id||' fromlpn '||p_from_lpn_id||' fromItem =>'||p_from_item_id||' rev =>'||p_from_item_revision , 4);
4680 print_debug('p_from_lot_number => ' || p_from_item_lot_number||'p_to_lpn_id =>'||p_to_lpn_id, 4);
4681 END IF;
4682
4683 --add up all the picked_qty2 from wdd, even if 1 rec is null, then
4684 --the result will be null, and we should hide the ct wt entry
4685
4686 OPEN itemCur(p_from_lpn_id);
4687 LOOP
4688 FETCH itemCur into l_to_item_picked_qty;
4689 EXIT WHEN itemCur%NOTFOUND;
4690 print_debug(' inside 2 Loop l_to_item_pri_qty => '||l_to_item_picked_qty,4);
4691 l_from_item_pri_qty := l_from_item_pri_qty + l_to_item_picked_qty;
4692 print_debug(' inside 2 Loop l_from_item_pri_qty => '||l_from_item_pri_qty,4);
4693
4694 END LOOP;
4695 CLOSE itemCur;
4696
4697 l_progress := '10';
4698 print_debug(' out 2 Loop l_from_item_pri_qty => '||l_from_item_pri_qty,4);
4699 if (l_from_item_pri_qty IS NULL OR l_from_item_pri_qty = 0 ) THEN
4700 x_show_ct_wt := 0;
4701 x_return_status := fnd_api.g_ret_sts_success;
4702 RETURN;
4703 END IF;
4704
4705 l_progress := '20';
4706 if (p_to_lpn_id IS NULL) THEN
4707 x_show_ct_wt := 1 ;
4708 x_return_status := fnd_api.g_ret_sts_success;
4709 RETURN;
4710 END IF;
4711 IF (p_to_lpn_id IS NOT NULL) THEN
4712 OPEN itemCur(p_to_lpn_id);
4713 LOOP
4714 FETCH itemCur into l_to_item_picked_qty;
4715 EXIT WHEN itemCur%NOTFOUND;
4716
4717 l_to_sec_qty := l_to_sec_qty + l_to_item_picked_qty;
4718 print_debug('Inside Loop l_to_item_picked_qty => ' || l_to_item_picked_qty||' l_to_sec_qty=>'||l_to_sec_qty,4);
4719 END LOOP;
4720 CLOSE itemCur;
4721 l_progress := '30';
4722 print_debug('After Loop l_to_sec_qty => '||l_to_sec_qty,4);
4723
4724 if (l_to_sec_qty IS NULL) THEN
4725 x_show_ct_wt := 0 ;
4726 x_return_status := fnd_api.g_ret_sts_success;
4727 RETURN;
4728 END IF;
4729
4730 l_progress := '40';
4731 if (l_to_sec_qty = 0) THEN
4732 x_show_ct_wt := 1;
4733 x_return_status := fnd_api.g_ret_sts_success;
4734 RETURN;
4735 END IF;
4736 END IF;
4737 l_progress := '50';
4738
4739 --add up all the picked_qty2 from wdd, even if 1 rec is null, then
4740 --the result will be null, and we should hide the ct wt entry
4741
4742 OPEN itemCur(p_from_lpn_id);
4743 LOOP
4744 FETCH itemCur into l_to_item_picked_qty;
4745 EXIT WHEN itemCur%NOTFOUND;
4746 l_from_item_pri_qty := l_from_item_pri_qty + l_to_item_picked_qty;
4747 print_debug(' inside 2 Loop l_from_item_pri_qty => '||l_from_item_pri_qty,4);
4748
4749 END LOOP;
4750 CLOSE itemCur;
4751
4752 l_progress := '60';
4753 print_debug(' Before computing l_from_item_pri_qty => '||l_from_item_pri_qty||' l_to_sec_qty =>'||l_to_sec_qty,4);
4754
4755 IF (NVL(l_from_item_pri_qty,0) > 0 AND l_to_sec_qty >= 0 ) THEN
4756 x_show_ct_wt := 1;
4757 ELSE
4758 x_show_ct_wt := 0;
4759 END IF;
4760 l_progress := '70';
4761 x_return_status := fnd_api.g_ret_sts_success;
4762
4763 EXCEPTION
4764 WHEN OTHERS THEN
4765 IF (l_debug = 1) THEN
4766 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
4767 IF ( SQLCODE IS NOT NULL ) THEN
4768 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
4769 END IF;
4770 END IF;
4771 END SHOW_CT_WT_FOR_SPLIT;
4772
4773
4774 FUNCTION IS_CT_WT_SPLIT_VALID (
4775 p_org_id IN NUMBER
4776 , p_from_lpn_id IN NUMBER
4777 , p_from_item_id IN NUMBER
4778 , p_from_item_revision IN VARCHAR2
4779 , p_from_item_lot_number IN VARCHAR2
4780 , p_from_item_pri_qty IN NUMBER
4781 , p_from_item_pri_uom IN VARCHAR2
4782 , p_from_item_sec_qty IN NUMBER
4783 , x_return_status OUT NOCOPY VARCHAR2
4784 , x_msg_data OUT NOCOPY VARCHAR2
4785 , x_msg_count OUT NOCOPY NUMBER
4786 ) RETURN NUMBER IS
4787
4788 CURSOR itemCur IS
4789 SELECT sum(inv_convert.inv_um_convert(wdd.inventory_item_id,
4790 6,
4791 wdd.REQUESTED_QUANTITY,
4792 wdd.REQUESTED_QUANTITY_UOM,
4793 --msib.primary_uom_code,
4794 p_from_item_pri_uom,
4795 NULL,
4796 NULL)) requested_quantity,
4797 sum(picked_quantity2) picked_quantity2,
4798 msib.primary_uom_code
4799 FROM wsh_delivery_details wdd,
4800 mtl_system_items_b msib
4801 WHERE wdd.inventory_item_id = p_from_item_id
4802 AND wdd.inventory_item_id = msib.inventory_item_id
4803 AND wdd.organization_id = p_org_id
4804 AND wdd.organization_id = msib.organization_id
4805 AND NVL(wdd.revision,'@@@') = NVL(p_from_item_revision,'@@@')
4806 AND NVL(wdd.lot_number,'@@@') = NVL(p_from_item_lot_number,'@@@')
4807 AND wdd.delivery_detail_id in (SELECT wda.delivery_detail_id
4808 FROM wsh_delivery_assignments_v wda,
4809 wsh_delivery_Details wdd1
4810 WHERE wdd1.lpn_id = p_from_lpn_id
4811 AND wdd1.released_status = 'X' -- For LPN reuse ER : 6845650
4812 AND wdd1.delivery_detail_id = wda.parent_delivery_Detail_id
4813 AND wdd1.organization_id = p_org_id)
4814 GROUP BY wdd.inventory_item_id, wdd.revision, wdd.lot_number, msib.primary_uom_code;
4815
4816 l_progress VARCHAR2(20) := '0';
4817 l_api_name CONSTANT VARCHAR2(30) := 'IS_CT_WT_SPLIT_VALID';
4818 l_api_version CONSTANT NUMBER := 1.0;
4819 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4820 l_pri_qty NUMBER := 0;
4821 l_sec_Qty NUMBER := 0;
4822 l_pri_remaining_qty NUMBER := 0;
4823 l_sec_remaining_qty NUMBER := 0;
4824 l_pri_input_qty NUMBER := 0;
4825 L_PRI_QTY_DIFF NUMBER := 0;
4826 L_SEC_QTY_DIFF NUMBER := 0;
4827 RETURNTOLERANCE NUMBER := 0;
4828 first_time BOOLEAN := TRUE;
4829
4830 itemRec itemCur%ROWTYPE;
4831
4832 BEGIN
4833
4834 IF ( l_debug = 1 ) THEN
4835 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
4836 print_debug('p_org_id => ' || p_org_id||' fromlpn '||p_from_lpn_id||' fromItem =>'||p_from_item_id||' rev =>'||p_from_item_revision , 4);
4837 print_debug('p_from_lot_number => ' || p_from_item_lot_number||'pri uom =>'||p_from_item_pri_uom, 4);
4838 END IF;
4839
4840 l_progress := '10';
4841
4842 /**l_pri_input_qty := inv_convert.inv_um_convert(p_from_item_id,
4843 6,
4844 p_from_item_pri_qty,
4845 p_from_item_pri_uom,
4846 p_from_item_pri_uom,
4847 NULL,
4848 NULL);
4849 **/
4850 l_progress := '20';
4851
4852 OPEN itemCur;
4853 LOOP
4854 FETCH itemCur into itemRec;
4855 EXIT WHEN itemCur%NOTFOUND;
4856 l_pri_input_qty := p_from_item_pri_qty;
4857
4858 l_pri_qty_diff := itemRec.requested_quantity - l_pri_input_qty ;
4859 l_sec_qty_diff := itemRec.picked_quantity2 - p_from_item_sec_qty;
4860 IF ( l_debug = 1 ) THEN
4861 print_debug('Inside Loop l_pri_qty_diff=>'||l_pri_qty_diff||' l_sec_qty_diff =>'||l_sec_qty_diff||' l_pri_input_qty =>'||l_pri_input_qty||' p_from_item_sec_qty =>'||p_from_item_sec_qty, 4);
4862 END IF;
4863
4864 END LOOP;
4865 CLOSE itemCur;
4866
4867 /*
4868
4869 FOR itemCur in itemRec LOOP
4870
4871 --l_pri_qty_diff := itemRec.requested_quantity - l_pri_input_qty ;
4872 l_pri_qty_diff := itemRec.requested_quantity - l_pri_input_qty ;
4873 l_sec_qty_diff := itemRec.picked_quantity2 - p_from_item_sec_qty;
4874 l_progress := '30';
4875
4876 END LOOP;
4877 */
4878
4879 IF (l_pri_qty_diff = 0) THEN
4880 /** This means, entire qty was split, there is no remaining qty
4881 ** so there is no need for further check..this is a valid split
4882 **/
4883 RETURN 1;
4884 END IF;
4885 l_progress := '40';
4886
4887 IF (l_pri_qty_diff < 0) THEN
4888 /** This condition should not occur, this is an error condition
4889 **/
4890 RETURN 0;
4891 END IF;
4892 l_progress := '50';
4893
4894 IF (l_pri_qty_diff > 0) THEN
4895 /**This is a valid condition, now we need to check the
4896 ** tolerance for the sec_qty_diff
4897 **/
4898
4899 l_progress := '60';
4900 returnTolerance := Check_Secondary_Qty_Tolerance (
4901 p_api_version => 1
4902 , p_init_msg_list => fnd_api.g_false
4903 , x_return_status => x_return_status
4904 , x_msg_count => x_msg_count
4905 , x_msg_data => x_msg_data
4906 , p_organization_id => p_org_id
4907 , p_inventory_item_id => p_from_item_id
4908 , p_quantity => l_pri_qty_diff
4909 , p_uom_code => p_from_item_pri_uom
4910 , p_secondary_quantity => l_sec_qty_diff);
4911
4912 l_progress := '70';
4913 if (returnTolerance = 0) THEN
4914 RETURN 1;
4915 else
4916 RETURN 0;
4917 end if;
4918 l_progress := '80';
4919 END IF;
4920 /**
4921 EXCEPTION
4922 WHEN OTHERS THEN
4923 IF (l_debug = 1) THEN
4924 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
4925 IF ( SQLCODE IS NOT NULL ) THEN
4926 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
4927 END IF;
4928 END IF;
4929 **/
4930
4931 END IS_CT_WT_SPLIT_VALID;
4932
4933 FUNCTION VALIDATE_CT_WT_FOR_DELIVERYNUM(
4934 p_api_version IN NUMBER
4935 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
4936 , x_return_status OUT NOCOPY VARCHAR2
4937 , x_msg_count OUT NOCOPY NUMBER
4938 , x_msg_data OUT NOCOPY VARCHAR2
4939 , p_org_id IN NUMBER
4940 , p_delivery_name IN VARCHAR2
4941 )RETURN NUMBER AS
4942
4943 CURSOR wddCur IS
4944 SELECT wdd.delivery_Detail_id,
4945 wdd.inventory_item_id,
4946 wdd.organization_id,
4947 wdd.picked_quantity,
4948 wdd.picked_quantity2,
4949 msi.primary_uom_code,
4950 msi.secondary_uom_code,
4951 msi.secondary_default_ind
4952 FROM wsh_delivery_Details wdd,
4953 mtl_system_items msi
4954 WHERE wdd.delivery_detail_id in
4955 (SELECT wda.delivery_detail_id
4956 FROM wsh_delivery_assignments_v wda,
4957 wsh_new_deliveries wnd
4958 WHERE wda.delivery_id = wnd.delivery_id
4959 AND wnd.name = p_delivery_name
4960 AND wda. PARENT_DELIVERY_DETAIL_ID is not NULL)
4961 AND picked_quantity2 IS NULL
4962 AND wdd.inventory_item_id = msi.inventory_item_id
4963 AND wdd.inventory_item_id is not null
4964 AND wdd.organization_id = msi.organization_id
4965 AND msi.ont_pricing_qty_source = 'S';
4966
4967 l_progress VARCHAR2(20) := '0';
4968 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CT_WT_FOR_DELIVERYNUM';
4969 l_api_version CONSTANT NUMBER := 1.0;
4970 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
4971 l_uom_conv_rate NUMBER;
4972 l_return NUMBER := G_CHECK_SUCCESS;
4973
4974 BEGIN
4975
4976
4977 -- Standard call to check for call compatibility.
4978 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
4979 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
4980 fnd_msg_pub.ADD;
4981 RAISE fnd_api.g_exc_unexpected_error;
4982 END IF;
4983
4984 -- Initialize message list if p_init_msg_list is set to TRUE.
4985 IF fnd_api.to_boolean(p_init_msg_list) THEN
4986 fnd_msg_pub.initialize;
4987 END IF;
4988
4989 -- Initialize API return status to success
4990 x_return_status := fnd_api.g_ret_sts_success;
4991
4992
4993 IF ( l_debug = 1 ) THEN
4994 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
4995 print_debug('p_org_id => '|| p_org_id||' p_delivery_number=>'||p_delivery_name , 4);
4996 END IF;
4997
4998 l_progress := '100';
4999
5000
5001 FOR item_rec IN wddCur LOOP
5002
5003 IF ( l_debug = 1 ) THEN
5004 print_debug('p_org_id => ' || item_rec.organization_id||' p_inv_item_id => '||item_rec.inventory_item_id||' del_detail_id => '||item_rec.delivery_Detail_id , 4);
5005 END IF;
5006
5007 l_progress := '200';
5008
5009 IF (item_rec.secondary_default_ind = G_SECONDARY_DEFAULT AND item_rec.picked_quantity2 IS NULL) THEN
5010
5011 --call the defaulting logic and see if the return < 0
5012
5013 INV_CONVERT.inv_um_conversion (
5014 from_unit => item_rec.primary_uom_code
5015 , to_unit => item_rec.secondary_uom_code
5016 , item_id => item_rec.inventory_item_id
5017 , uom_rate => l_uom_conv_rate );
5018
5019 l_progress := '300';
5020
5021 IF ( l_debug = 1 ) THEN
5022 print_debug('from_unit => ' ||item_rec.primary_uom_code||' to_unit => '||item_rec.secondary_uom_code||' item_id => '||item_rec.inventory_item_id||' uom_rate =>'||l_uom_conv_rate , 4);
5023 END IF;
5024
5025 IF ( l_uom_conv_rate < 0 ) THEN
5026 -- no valid connection uom conversion between these two uoms
5027 l_return := G_CHECK_ERROR;
5028 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
5029 fnd_msg_pub.ADD;
5030 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
5031 ELSE
5032 -- there is a valid conversion, change status to warning
5033 l_return := G_CHECK_WARNING;
5034 END IF;
5035 ELSE
5036 IF (item_rec.secondary_default_ind = G_SECONDARY_NO_DEFAULT AND item_rec.picked_quantity2 IS NULL) THEN
5037 l_return := G_CHECK_ERROR;
5038 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
5039 fnd_msg_pub.ADD;
5040 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
5041 END IF;
5042
5043 END IF;
5044
5045 l_progress := '400';
5046
5047 END LOOP;
5048
5049
5050 l_progress := '500';
5051
5052 RETURN l_return;
5053
5054 EXCEPTION
5055 WHEN OTHERS THEN
5056 IF (l_debug = 1) THEN
5057 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
5058 IF ( SQLCODE IS NOT NULL ) THEN
5059 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
5060 END IF;
5061 END IF;
5062 x_return_status := fnd_api.g_ret_sts_error;
5063 fnd_message.set_name('WMS', 'WMS_CALC_SEC_QTY_FAIL');
5064 fnd_msg_pub.ADD;
5065 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
5066 END VALIDATE_CT_WT_FOR_DELIVERYNUM;
5067
5068
5069 PROCEDURE get_primary_uom_class(
5070 x_uoms OUT NOCOPY t_genref
5071 , p_organization_id IN NUMBER
5072 , p_inventory_item_id IN NUMBER
5073 , p_uom_code IN VARCHAR2
5074 ) IS
5075 l_code VARCHAR2(20) := p_uom_code;
5076 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5077 BEGIN
5078 IF ( l_debug = 1 ) THEN
5079 print_debug('Entered get_primary_uom_class:' ||
5080 ' p_organization_id: ' || p_organization_id ||
5081 ', p_inventory_item_id: ' || p_inventory_item_id ||
5082 ', p_uom_code: ' || p_uom_code
5083 , 4);
5084 END IF;
5085
5086 IF (INSTR(l_code,'(') > 0) THEN
5087 l_code := SUBSTR(p_uom_code,1,INSTR(p_uom_code,'(')-1);
5088 END IF;
5089
5090 IF ( l_debug = 1 ) THEN
5091 print_debug('l_code: ' || l_code, 4);
5092 END IF;
5093
5094 OPEN x_uoms FOR
5095 SELECT (inv_ui_item_lovs.get_conversion_rate(
5096 uom_code,
5097 p_organization_id,
5098 p_inventory_item_id)) uom_code_comp
5099 , unit_of_measure
5100 , description
5101 , uom_class
5102 FROM mtl_item_uoms_view
5103 WHERE organization_id = p_organization_id
5104 AND inventory_item_id = p_inventory_item_id
5105 AND uom_class IN (SELECT uom_class
5106 FROM mtl_system_items msi,
5107 mtl_units_of_measure muom
5108 WHERE msi.inventory_item_id = p_inventory_item_id
5109 AND msi.organization_id = p_organization_id
5110 AND muom.uom_code = msi.primary_uom_code)
5111 AND uom_code LIKE (l_code)
5112 ORDER BY inv_ui_item_lovs.conversion_order(
5113 inv_ui_item_lovs.get_conversion_rate(
5114 uom_code
5115 , p_organization_id
5116 , p_inventory_item_id)) asc
5117 , UPPER(uom_code);
5118 END get_primary_uom_class;
5119
5120
5121 FUNCTION get_uom_class(p_uom_code IN VARCHAR2)
5122 RETURN VARCHAR2 IS
5123 l_uom_class VARCHAR2(10);
5124 BEGIN
5125 SELECT uom_class
5126 INTO l_uom_class
5127 FROM mtl_units_of_measure_vl
5128 WHERE uom_code = p_uom_code;
5129
5130 RETURN l_uom_class;
5131 EXCEPTION
5132 WHEN OTHERS THEN
5133 RETURN NULL;
5134 END get_uom_class;
5135
5136
5137 PROCEDURE update_lpn_tare (
5138 x_return_status OUT NOCOPY VARCHAR2
5139 , x_msg_count OUT NOCOPY NUMBER
5140 , x_msg_data OUT NOCOPY VARCHAR2
5141 , p_lpn_id IN NUMBER
5142 , p_org_id IN NUMBER
5143 , p_tare_weight IN NUMBER
5144 , p_tare_uom_code IN VARCHAR2
5145 , p_update_tare_flag IN VARCHAR2
5146 ) IS
5147 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
5148 l_api_return_status VARCHAR2(1);
5149
5150 l_lpn_tbl wms_data_type_definitions_pub.lpntabletype;
5151 l_lpn_rec wms_data_type_definitions_pub.lpnrecordtype;
5152 l_curr_tare_wt NUMBER;
5153 l_curr_tare_wt_uom_code VARCHAR2(3);
5154 l_new_tare_wt NUMBER;
5155 l_new_tare_wt_uom_code VARCHAR2(3);
5156 l_converted_qty NUMBER;
5157 l_tare_weight NUMBER;
5158 l_inner_lpn_tare NUMBER := 0;
5159
5160 BEGIN
5161 x_return_status := fnd_api.g_ret_sts_success;
5162
5163 SAVEPOINT update_tare_sp;
5164
5165 IF (l_debug = 1) THEN
5166 print_debug('Entered update_lpn_tare:' ||
5167 ' p_lpn_id: ' || p_lpn_id ||
5168 ', p_org_id: ' || p_org_id ||
5169 ', p_tare_weight: ' || p_tare_weight ||
5170 ', p_tare_uom_code: ' || p_tare_uom_code ||
5171 ', p_update_tare_flag: ' || p_update_tare_flag
5172 , 4);
5173 END IF;
5174
5175 IF p_tare_weight < 0 THEN
5176 IF (l_debug = 1) THEN
5177 print_debug('Tare wgt < 0, return.', 4);
5178 END IF;
5179 RETURN;
5180 END IF;
5181
5182 l_tare_weight := p_tare_weight;
5183
5184 BEGIN
5185 SELECT NVL(tare_weight, 0), NVL(tare_weight_uom_code, '###')
5186 INTO l_curr_tare_wt, l_curr_tare_wt_uom_code
5187 FROM wms_license_plate_numbers
5188 WHERE lpn_id = p_lpn_id
5189 AND organization_id = p_org_id;
5190 EXCEPTION
5191 WHEN NO_DATA_FOUND THEN
5192 IF (l_debug = 1) THEN
5193 print_debug('Error querying LPN tare wt and tare UoM: ' || SQLERRM, 4);
5194 END IF;
5195 END;
5196
5197 IF (l_debug = 1) THEN
5198 print_debug('Inside update_lpn_tare l_curr_tare_wt : '|| l_curr_tare_wt ||', l_curr_tare_wt_uom_code: ' || l_curr_tare_wt_uom_code , 4);
5199 END IF;
5200
5201 IF(l_curr_tare_wt > 0) THEN
5202 BEGIN
5203 --The following query gets the tare_weight of the immediate child LPNs only and not the entire LPN hierarchy.
5204 --This is done intentionally as the child would already include the tare_weight of any of its children so we don't want to double count.
5205 SELECT NVL(SUM(DECODE(tare_weight_uom_code, NULL, 0 , inv_convert.inv_um_convert(
5206 item_id => NULL
5207 , precision => 5
5208 , from_quantity => NVL(tare_weight, 0)
5209 , from_unit => tare_weight_uom_code
5210 , to_unit => l_curr_tare_wt_uom_code
5211 , from_name => NULL
5212 , to_name => NULL))), 0)
5213 INTO l_inner_lpn_tare
5214 FROM wms_license_plate_numbers
5215 WHERE parent_lpn_id = p_lpn_id
5216 AND organization_id = p_org_id;
5217 EXCEPTION
5218 WHEN NO_DATA_FOUND THEN
5219 IF (l_debug = 1) THEN
5220 print_debug('No data found on inner LPNs ' || SQLERRM, 4);
5221 END IF;
5222 END;
5223 END IF;
5224
5225 IF(l_inner_lpn_tare > 0) THEN
5226 l_tare_weight := l_tare_weight + l_inner_lpn_tare;
5227 END IF;
5228
5229 IF (l_debug = 1) THEN
5230 print_debug('For current LPN, the inner LPNs have tare l_inner_lpn_tare '|| l_inner_lpn_tare ||', so adding that making l_tare_weight ' || l_tare_weight , 4);
5231 END IF;
5232
5233 IF(p_update_tare_flag = 'U') THEN
5234 IF(p_tare_uom_code = NVL(l_curr_tare_wt_uom_code, '###')) THEN
5235 l_new_tare_wt := l_tare_weight + NVL(l_curr_tare_wt,0);
5236 l_new_tare_wt_uom_code := l_curr_tare_wt_uom_code;
5237 IF (l_debug = 1) THEN
5238 print_debug('Inside update_lpn_tare for case where uom is same as curr LPN tare UoM', 4);
5239 END IF;
5240 ELSE
5241 l_converted_qty := inv_convert.inv_um_convert(
5242 item_id => NULL
5243 , precision => 5
5244 , from_quantity => l_tare_weight
5245 , from_unit => p_tare_uom_code
5246 , to_unit => l_curr_tare_wt_uom_code
5247 , from_name => NULL
5248 , to_name => NULL
5249 );
5250
5251 IF (l_debug = 1) THEN
5252 print_debug('Inside update_lpn_tare for case where uom is diff than curr LPN tare UoM', 4);
5253 print_debug('Inside update_lpn_tare l_converted_qty : '|| l_converted_qty ||', in l_curr_tare_wt_uom_code: ' || l_curr_tare_wt_uom_code , 4);
5254 END IF;
5255
5256 IF( l_converted_qty < 0)THEN
5257 l_converted_qty := 0;
5258 END IF;
5259 l_new_tare_wt := l_converted_qty + NVL(l_curr_tare_wt,0);
5260 l_new_tare_wt_uom_code := l_curr_tare_wt_uom_code;
5261 END IF;
5262 ELSE
5263 l_new_tare_wt := l_tare_weight;
5264 l_new_tare_wt_uom_code := p_tare_uom_code;
5265 END IF;
5266
5267 IF (l_debug = 1) THEN
5268 print_debug('Inside update_lpn_tare l_new_tare_wt: ' || l_new_tare_wt || ', l_new_tare_wt_uom_code: ' || l_new_tare_wt_uom_code , 4);
5269 END IF;
5270
5271 l_lpn_tbl.DELETE;
5272 l_lpn_rec.lpn_id := p_lpn_id;
5273 l_lpn_rec.organization_id := p_org_id;
5274 l_lpn_rec.tare_weight := l_new_tare_wt;
5275 l_lpn_rec.tare_weight_uom_code := l_new_tare_wt_uom_code;
5276 l_lpn_tbl(1) := l_lpn_rec;
5277
5278 l_api_return_status := fnd_api.g_ret_sts_success;
5279 wms_container_pvt.modify_lpns(
5280 p_api_version => 1.0
5281 , p_init_msg_list => fnd_api.g_true
5282 , p_commit => fnd_api.g_false
5283 , x_return_status => l_api_return_status
5284 , x_msg_count => x_msg_count
5285 , x_msg_data => x_msg_data
5286 , p_caller => NULL
5287 , p_lpn_table => l_lpn_tbl
5288 );
5289 IF l_api_return_status <> fnd_api.g_ret_sts_success THEN
5290 IF (l_debug = 1) THEN
5291 print_debug('Error status from wms_container_pvt.modify_lpns: '
5292 || l_api_return_status, 4);
5293 END IF;
5294 IF l_api_return_status = fnd_api.g_ret_sts_error THEN
5295 RAISE fnd_api.g_exc_error;
5296 ELSE
5297 RAISE fnd_api.g_exc_unexpected_error;
5298 END IF;
5299 END IF;
5300
5301 EXCEPTION
5302 WHEN fnd_api.g_exc_error THEN
5303 ROLLBACK TO update_tare_sp;
5304 x_return_status := fnd_api.g_ret_sts_error;
5305 fnd_msg_pub.count_and_get
5306 ( p_count => x_msg_count
5307 , p_data => x_msg_data
5308 , p_encoded => fnd_api.g_false
5309 );
5310 IF (l_debug = 1) THEN
5311 print_debug (x_msg_data, 4);
5312 END IF;
5313
5314 WHEN OTHERS THEN
5315 IF (l_debug = 1) THEN
5316 print_debug('Error: ' || SQLERRM, 1);
5317 END IF;
5318
5319 ROLLBACK TO update_tare_sp;
5320 x_return_status := fnd_api.g_ret_sts_unexp_error;
5321 x_msg_data := SQLERRM;
5322
5323 END update_lpn_tare;
5324
5325 END WMS_CATCH_WEIGHT_PVT;