[Home] [Help]
PACKAGE BODY: APPS.WMS_CATCH_WEIGHT_PVT
Source
1 PACKAGE BODY WMS_CATCH_WEIGHT_PVT AS
2 /* $Header: WMSVCWTB.pls 120.4.12010000.1 2008/07/28 18:37:46 appldev 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.4.12010000.1 2008/07/28 18:37:46 appldev ship $';
7
8 g_precision CONSTANT NUMBER := 5;
9
10 PROCEDURE print_debug( p_message VARCHAR2, p_level NUMBER ) IS
11 BEGIN
12 --dbms_output.put_line(p_message);
13 inv_log_util.trace(
14 p_message => p_message
15 , p_module => g_pkg_name
16 , p_level => p_level);
17 END print_debug;
18
19 PROCEDURE Get_Catch_Weight_Attributes (
20 p_api_version IN NUMBER
21 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
22 , x_return_status OUT NOCOPY VARCHAR2
23 , x_msg_count OUT NOCOPY NUMBER
24 , x_msg_data OUT NOCOPY VARCHAR2
25 , p_organization_id IN NUMBER
26 , p_inventory_item_id IN NUMBER
27 , p_quantity IN NUMBER := NULL
28 , p_uom_code IN VARCHAR2 := NULL
29 , x_tracking_quantity_ind OUT NOCOPY VARCHAR2
30 , x_ont_pricing_qty_source OUT NOCOPY VARCHAR2
31 , x_secondary_default_ind OUT NOCOPY VARCHAR2
32 , x_secondary_quantity OUT NOCOPY NUMBER
33 , x_secondary_uom_code OUT NOCOPY VARCHAR2
34 , x_uom_deviation_high OUT NOCOPY NUMBER
35 , x_uom_deviation_low OUT NOCOPY NUMBER
36 ) IS
37 l_api_name CONSTANT VARCHAR2(30) := 'Get_Catch_Weight_Attributes';
38 l_api_version CONSTANT NUMBER := 1.0;
39 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
40 l_progress VARCHAR2(10) := '0';
41
42 l_wms_org_flag BOOLEAN;
43 l_secondary_uom_code VARCHAR2(3);
44 l_result VARCHAR2(30);
45 BEGIN
46 -- Standard call to check for call compatibility.
47 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
48 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
49 fnd_msg_pub.ADD;
50 RAISE fnd_api.g_exc_unexpected_error;
51 END IF;
52
53 -- Initialize message list if p_init_msg_list is set to TRUE.
54 IF fnd_api.to_boolean(p_init_msg_list) THEN
55 fnd_msg_pub.initialize;
56 END IF;
57
58 -- Initialize API return status to success
59 x_return_status := fnd_api.g_ret_sts_success;
60
61 IF ( l_debug = 1 ) THEN
62 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
63 print_debug('orgid='||p_organization_id||' itemid='||p_inventory_item_id||' qty='||p_quantity||' uom='||p_uom_code, 4);
64 END IF;
65
66 -- Check if the organization is a WMS organization
67 l_wms_org_flag := wms_install.check_install (
68 x_return_status => x_return_status
69 , x_msg_count => x_msg_count
70 , x_msg_data => x_msg_data
71 , p_organization_id => p_organization_id );
72 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
73 IF ( l_debug = 1 ) THEN
74 print_debug('Call to wms_install.check_install failed:' ||x_msg_data, 1);
75 END IF;
76 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77 END IF;
78
79 l_progress := '100';
80 IF ( l_wms_org_flag ) THEN
81 l_progress := '200';
82 SELECT NVL(tracking_quantity_ind, G_TRACK_PRIMARY),
83 NVL(ont_pricing_qty_source, G_PRICE_PRIMARY),
84 secondary_default_ind,
85 secondary_uom_code,
86 dual_uom_deviation_high,
87 dual_uom_deviation_low
88 INTO x_tracking_quantity_ind,
89 x_ont_pricing_qty_source,
90 x_secondary_default_ind,
91 x_secondary_uom_code,
92 x_uom_deviation_high,
93 x_uom_deviation_low
94 FROM MTL_SYSTEM_ITEMS
95 WHERE organization_id = p_organization_id
96 AND inventory_item_id = p_inventory_item_id;
97
98 l_progress := '300';
99 IF ( p_quantity IS NOT NULL ) THEN
100 Get_Default_Secondary_Quantity (
101 p_api_version => 1.0
102 , x_return_status => x_return_status
103 , x_msg_count => x_msg_count
104 , x_msg_data => x_msg_data
105 , p_organization_id => p_organization_id
106 , p_inventory_item_id => p_inventory_item_id
107 , p_quantity => p_quantity
108 , p_uom_code => p_uom_code
109 , p_secondary_default_ind => x_secondary_default_ind
110 , x_ont_pricing_qty_source => x_ont_pricing_qty_source
111 , x_secondary_uom_code => x_secondary_uom_code
112 , x_secondary_quantity => x_secondary_quantity);
113
114 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
115 IF ( l_debug = 1 ) THEN
116 print_debug('Call to Get_Default_Secondary_Quantity failed', 1);
117 END IF;
118 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
119 END IF;
120 END IF;
121 ELSE -- not a wms org
122 IF ( l_debug = 1 ) THEN
123 print_debug('Not a WMS org return default values', 4);
124 END IF;
125 x_tracking_quantity_ind := G_TRACK_PRIMARY;
126 x_ont_pricing_qty_source := G_PRICE_PRIMARY;
127 x_secondary_default_ind := NULL;
128 x_secondary_quantity := NULL;
129 x_secondary_uom_code := NULL;
130 x_uom_deviation_high := NULL;
131 x_uom_deviation_low := NULL;
132 END IF;
133
134 IF ( l_debug = 1 ) THEN
135 print_debug(l_api_name || ' Exited ', 1);
136 print_debug('track_ind='||x_tracking_quantity_ind||' pricesrc='||x_ont_pricing_qty_source||' defaultind='||x_secondary_default_ind , 4);
137 print_debug('secqty='||x_secondary_quantity||' secuom='||x_secondary_uom_code||' devhigh='||x_uom_deviation_high||' devlow='||x_uom_deviation_low, 4);
138 END IF;
139
140 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
141 EXCEPTION
142 WHEN OTHERS THEN
143 IF (l_debug = 1) THEN
144 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
145 IF ( SQLCODE IS NOT NULL ) THEN
146 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
147 END IF;
148 END IF;
149 x_return_status := fnd_api.g_ret_sts_error;
150 fnd_message.set_name('WMS', 'WMS_GET_CWT_ATTR_FAIL');
151 fnd_msg_pub.ADD;
152 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
153 END Get_Catch_Weight_Attributes;
154
155
156 FUNCTION Get_Ont_Pricing_Qty_Source (
157 p_api_version IN NUMBER
158 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
159 , x_return_status OUT NOCOPY VARCHAR2
160 , x_msg_count OUT NOCOPY NUMBER
161 , x_msg_data OUT NOCOPY VARCHAR2
162 , p_organization_id IN NUMBER
163 , p_inventory_item_id IN NUMBER
164 ) RETURN VARCHAR2 IS
165 l_api_name CONSTANT VARCHAR2(30) := 'Get_Ont_Pricing_Qty_Source';
166 l_api_version CONSTANT NUMBER := 1.0;
167 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
168 l_progress VARCHAR2(10) := '0';
169
170 l_wms_org_flag BOOLEAN;
171 l_ont_pricing_qty_source VARCHAR(30);
172 BEGIN
173 -- Standard call to check for call compatibility.
174 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
175 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
176 fnd_msg_pub.ADD;
177 RAISE fnd_api.g_exc_unexpected_error;
178 END IF;
179
180 -- Initialize message list if p_init_msg_list is set to TRUE.
181 IF fnd_api.to_boolean(p_init_msg_list) THEN
182 fnd_msg_pub.initialize;
183 END IF;
184
185 -- Initialize API return status to success
186 x_return_status := fnd_api.g_ret_sts_success;
187
188 IF ( l_debug = 1 ) THEN
189 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
190 print_debug('orgid='||p_organization_id||' itemid='||p_inventory_item_id, 4);
191 END IF;
192
193 -- Check if the organization is a WMS organization
194 l_wms_org_flag := wms_install.check_install (
195 x_return_status => x_return_status
196 , x_msg_count => x_msg_count
197 , x_msg_data => x_msg_data
198 , p_organization_id => p_organization_id );
199 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
200 IF ( l_debug = 1 ) THEN
201 print_debug('Call to wms_install.check_install failed:' ||x_msg_data, 1);
202 END IF;
203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
204 END IF;
205
206 IF ( l_wms_org_flag ) THEN
207 SELECT NVL(ont_pricing_qty_source, G_PRICE_PRIMARY)
208 INTO l_ont_pricing_qty_source
209 FROM MTL_SYSTEM_ITEMS
210 WHERE organization_id = p_organization_id
211 AND inventory_item_id = p_inventory_item_id;
212 ELSE -- not a wms org
213 IF ( l_debug = 1 ) THEN
214 print_debug('Not a WMS org return default value', 4);
215 END IF;
216 l_ont_pricing_qty_source := G_PRICE_PRIMARY;
217 END IF;
218
219 IF ( l_debug = 1 ) THEN
220 print_debug(l_api_name||' Exited '||'ret='||l_ont_pricing_qty_source, 1);
221 END IF;
222
223 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
224
225 RETURN l_ont_pricing_qty_source;
226 EXCEPTION
227 WHEN OTHERS THEN
228 IF (l_debug = 1) THEN
229 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
230 IF ( SQLCODE IS NOT NULL ) THEN
231 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
232 END IF;
233 END IF;
234
235 x_return_status := fnd_api.g_ret_sts_error;
236 fnd_message.set_name('WMS', 'WMS_API_FAIL');
237 fnd_msg_pub.ADD;
238 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
239 END Get_Ont_Pricing_Qty_Source;
240
241
242 PROCEDURE Get_Default_Secondary_Quantity (
243 p_api_version IN NUMBER
244 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
245 , x_return_status OUT NOCOPY VARCHAR2
246 , x_msg_count OUT NOCOPY NUMBER
247 , x_msg_data OUT NOCOPY VARCHAR2
248 , p_organization_id IN NUMBER
249 , p_inventory_item_id IN NUMBER
250 , p_quantity IN NUMBER
251 , p_uom_code IN VARCHAR2
252 , p_secondary_default_ind IN VARCHAR2 := NULL
253 , x_ont_pricing_qty_source IN OUT NOCOPY VARCHAR2
254 , x_secondary_uom_code IN OUT NOCOPY VARCHAR2
255 , x_secondary_quantity OUT NOCOPY NUMBER
256 ) IS
257 l_api_name CONSTANT VARCHAR2(30) := 'Get_Default_Secondary_Quantity';
258 l_api_version CONSTANT NUMBER := 1.0;
259 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
260 l_progress VARCHAR2(10) := '0';
261
262 l_wms_org_flag BOOLEAN;
263 l_default_ind VARCHAR(30);
264 l_uom_code VARCHAR(3);
265 l_secondary_uom VARCHAR(3);
266 BEGIN
267 -- Standard call to check for call compatibility.
268 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
269 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
270 fnd_msg_pub.ADD;
271 RAISE fnd_api.g_exc_unexpected_error;
272 END IF;
273
274 -- Initialize message list if p_init_msg_list is set to TRUE.
275 IF fnd_api.to_boolean(p_init_msg_list) THEN
276 fnd_msg_pub.initialize;
277 END IF;
278
279 -- Initialize API return status to success
280 x_return_status := fnd_api.g_ret_sts_success;
281
282 IF ( l_debug = 1 ) THEN
283 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
284 print_debug('orgid='||p_organization_id||' itemid='||p_inventory_item_id||' qty='||p_quantity||' uom='||p_uom_code, 4);
285 print_debug('pricesrc='||x_ont_pricing_qty_source||' defaultind='||p_secondary_default_ind||' secuom='||x_secondary_uom_code, 4);
286 END IF;
287
288 l_progress := '000';
289 IF ( x_secondary_uom_code IS NULL OR x_ont_pricing_qty_source IS NULL OR
290 p_secondary_default_ind IS NULL OR p_uom_code IS NULL ) THEN
291 -- Check if the organization is a WMS organization
292 l_wms_org_flag := wms_install.check_install (
293 x_return_status => x_return_status
294 , x_msg_count => x_msg_count
295 , x_msg_data => x_msg_data
296 , p_organization_id => p_organization_id );
297 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
298 IF ( l_debug = 1 ) THEN
299 print_debug('Call to wms_install.check_install failed:' ||x_msg_data, 1);
300 END IF;
301 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302 END IF;
303
304 IF ( l_wms_org_flag ) THEN
305 SELECT ont_pricing_qty_source,
306 secondary_default_ind,
307 primary_uom_code,
308 secondary_uom_code
309 INTO x_ont_pricing_qty_source,
310 l_default_ind,
311 l_uom_code,
312 x_secondary_uom_code
313 FROM mtl_system_items
314 WHERE organization_id = p_organization_id
315 AND inventory_item_id = p_inventory_item_id;
316 ELSE -- not a wms org
317 IF ( l_debug = 1 ) THEN
318 print_debug('Not a WMS org return default value', 4);
319 END IF;
320 x_ont_pricing_qty_source := G_PRICE_PRIMARY;
321 l_default_ind := NULL;
322 l_uom_code := NULL;
323 x_secondary_uom_code := NULL;
324 END IF;
325 ELSE -- Use the values given by user to calculate the defaults
326 l_default_ind := p_secondary_default_ind;
327 END IF;
328
329 l_progress := '100';
330 -- If item is secondary priced and is not default restricted calculate the default
331 -- secondary value based off the secondary uom.
332 IF ( x_ont_pricing_qty_source = G_PRICE_SECONDARY AND
333 l_default_ind <> G_SECONDARY_NO_DEFAULT ) THEN
334 l_progress := '200';
335 IF ( x_secondary_uom_code IS NULL ) THEN
336 IF (l_debug = 1) THEN
337 print_debug('Secondary UOM is not defined for this secondary priced item', 1);
338 END IF;
339 fnd_message.set_name('WMS', 'WMS_SEC_UOM_UNDEF_ERROR');
340 fnd_msg_pub.ADD;
341 RAISE FND_API.G_EXC_ERROR;
342 END IF;
343
344 -- Use user specified uom if passed
345 IF ( p_uom_code IS NOT NULL ) THEN
346 l_uom_code := p_uom_code;
347 END IF;
348
349 l_progress := '300';
350 x_secondary_quantity := inv_convert.inv_um_convert(
351 p_inventory_item_id
352 , g_precision
353 , p_quantity
354 , l_uom_code
355 , x_secondary_uom_code
356 , NULL
357 , NULL );
358 IF ( x_secondary_quantity < 0 ) THEN
359 IF ( l_debug = 1 ) THEN
360 print_debug('Error converting to from '||p_uom_code||' to '||x_secondary_uom_code, 1);
361 END IF;
362 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
363 fnd_message.set_token('uom1', p_uom_code);
364 fnd_message.set_token('uom2', x_secondary_uom_code);
365 fnd_message.set_token('module', l_api_name);
366 fnd_msg_pub.ADD;
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369 l_progress := '400';
370 ELSE
371 x_secondary_quantity := NULL;
372 END IF;
373
374 IF ( l_debug = 1 ) THEN
375 print_debug(l_api_name||' Exited '||' priceind=' ||x_ont_pricing_qty_source, 1);
376 print_debug('secqty='||x_secondary_quantity||' secuom='||x_secondary_uom_code, 4);
377 END IF;
378
379 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
383 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
380 EXCEPTION
381 WHEN OTHERS THEN
382 IF (l_debug = 1) THEN
384 IF ( SQLCODE IS NOT NULL ) THEN
385 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
386 END IF;
387 END IF;
388
389 x_return_status := fnd_api.g_ret_sts_error;
390 fnd_message.set_name('WMS', 'WMS_CALC_SEC_QTY_FAIL');
391 fnd_msg_pub.ADD;
392 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
393 END Get_Default_Secondary_Quantity;
394
395
396 FUNCTION Check_Secondary_Qty_Tolerance (
397 p_api_version IN NUMBER
398 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
399 , x_return_status OUT NOCOPY VARCHAR2
400 , x_msg_count OUT NOCOPY NUMBER
401 , x_msg_data OUT NOCOPY VARCHAR2
402 , p_organization_id IN NUMBER
403 , p_inventory_item_id IN NUMBER
404 , p_quantity IN NUMBER
405 , p_uom_code IN VARCHAR2
406 , p_secondary_quantity IN NUMBER
407 , p_secondary_uom_code IN VARCHAR2 := NULL
408 , p_ont_pricing_qty_source IN VARCHAR2 := NULL
409 , p_uom_deviation_high IN NUMBER := NULL
410 , p_uom_deviation_low IN NUMBER := NULL
411 ) RETURN NUMBER IS
412 l_api_name CONSTANT VARCHAR2(30) := 'Check_Secondary_Qty_Tolerance';
413 l_api_version CONSTANT NUMBER := 1.0;
414 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
415 l_progress VARCHAR2(10) := '0';
416
417 l_tracking_quantity_ind VARCHAR2(30);
418 l_ont_pricing_qty_source VARCHAR2(30);
419 l_secondary_default_ind VARCHAR2(30);
420 l_secondary_quantity NUMBER;
421 l_secondary_uom_code VARCHAR2(3);
422 l_uom_deviation_high NUMBER;
423 l_uom_deviation_low NUMBER;
424 l_upper_qty_limit NUMBER;
425 l_lower_qty_limit NUMBER;
426 l_return NUMBER := 0;
427 l_uom_code VARCHAR2(3) := p_uom_code;
428 l_converted_qty NUMBER;
429 BEGIN
430 -- Standard call to check for call compatibility.
431 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
432 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
433 fnd_msg_pub.ADD;
434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
435 END IF;
436
437 -- Initialize message list if p_init_msg_list is set to TRUE.
438 IF fnd_api.to_boolean(p_init_msg_list) THEN
439 fnd_msg_pub.initialize;
440 END IF;
441
442 -- Initialize API return status to success
443 x_return_status := fnd_api.g_ret_sts_success;
444
445 IF ( l_debug = 1 ) THEN
446 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
447 print_debug('orgid='||p_organization_id||' itemid='||p_inventory_item_id||' qty='||p_quantity||' uom='||p_uom_code||' secqty='||p_secondary_quantity, 4);
448 print_debug('pricesrc='||p_ont_pricing_qty_source||' secuom='||p_secondary_uom_code||' devhigh='||p_uom_deviation_high||' devlow='||p_uom_deviation_low, 4);
449 END IF;
450
451 IF ( p_secondary_uom_code IS NULL OR p_ont_pricing_qty_source IS NULL OR
452 p_uom_deviation_high IS NULL OR p_uom_deviation_low IS NULL ) THEN
453 Get_Catch_Weight_Attributes (
454 p_api_version => 1.0
455 , x_return_status => x_return_status
456 , x_msg_count => x_msg_count
457 , x_msg_data => x_msg_data
458 , p_organization_id => p_organization_id
459 , p_inventory_item_id => p_inventory_item_id
460 , x_tracking_quantity_ind => l_tracking_quantity_ind
461 , x_ont_pricing_qty_source => l_ont_pricing_qty_source
462 , x_secondary_default_ind => l_secondary_default_ind
463 , x_secondary_quantity => l_secondary_quantity
464 , x_secondary_uom_code => l_secondary_uom_code
465 , x_uom_deviation_high => l_uom_deviation_high
466 , x_uom_deviation_low => l_uom_deviation_low );
467 ELSE
468 l_ont_pricing_qty_source := p_ont_pricing_qty_source;
469 l_secondary_uom_code := p_secondary_uom_code;
470 l_uom_deviation_high := p_uom_deviation_high;
471 l_uom_deviation_low := p_uom_deviation_low;
472 END IF;
473
474 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
475 IF ( l_debug = 1 ) THEN
476 print_debug('Call to Get_Catch_Weight_Attributes failed', 1);
477 END IF;
478 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
479 END IF;
480
481 l_progress := '100';
482 IF ( l_ont_pricing_qty_source = G_PRICE_SECONDARY AND
483 (l_uom_deviation_high IS NOT NULL OR l_uom_deviation_low IS NOT NULL) ) THEN
484 l_progress := '200';
485 l_converted_qty := inv_convert.inv_um_convert(
486 p_inventory_item_id
487 , 6
488 , p_quantity
489 , l_uom_code
490 , l_secondary_uom_code
491 , NULL
492 , NULL );
493 IF ( l_converted_qty < 0 ) THEN
494 IF ( l_debug = 1 ) THEN
495 print_debug('Error converting qty from '||l_uom_code||' to '||l_secondary_uom_code, 1);
496 END IF;
497 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
498 fnd_message.set_token('uom1', l_uom_code);
502 RAISE FND_API.G_EXC_ERROR;
499 fnd_message.set_token('uom2', l_secondary_uom_code);
500 fnd_message.set_token('module', l_api_name);
501 fnd_msg_pub.ADD;
503 END IF;
504
505 IF ( l_uom_deviation_high IS NOT NULL ) THEN
506 l_progress := '300';
507 --l_upper_qty_limit := p_quantity converted into catch weight uom* 1+DEVIATION_HIGH/100
508 l_upper_qty_limit := round(l_converted_qty*(1+(l_uom_deviation_high/100)), g_precision);
509
510 IF ( round(p_secondary_quantity, g_precision) > l_upper_qty_limit ) THEN
511 l_return := 1;
512 END IF;
513 END IF;
514
515 IF ( l_return = 0 AND l_uom_deviation_low IS NOT NULL ) THEN
516 l_progress := '400';
517 --l_lower_qty_limit := p_quantity converted into catch weight uom* 1-DEVIATION_LOW/100
518 l_lower_qty_limit := round(l_converted_qty*(1-(l_uom_deviation_low/100)), g_precision);
519
520 IF ( round(p_secondary_quantity, g_precision) < l_lower_qty_limit ) THEN
521 l_return := -1;
522 END IF;
523 END IF;
524 l_progress := '500';
525 END IF;
526
527 IF ( l_debug = 1 ) THEN
528 print_debug(l_api_name||' Exited '||' ret=' ||l_return, 1);
529 print_debug('uplim='||l_upper_qty_limit||' lowlim'||l_lower_qty_limit, 4);
530 END IF;
531
532 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
533
534 RETURN l_return;
535 EXCEPTION
536 WHEN OTHERS THEN
537 IF (l_debug = 1) THEN
538 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
539 IF ( SQLCODE IS NOT NULL ) THEN
540 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
541 END IF;
542 END IF;
543
544 x_return_status := fnd_api.g_ret_sts_error;
545 fnd_message.set_name('WMS', 'WMS_API_FAIL');
546 fnd_msg_pub.ADD;
547 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
548 END Check_Secondary_Qty_Tolerance;
549
550
551 PROCEDURE Update_Shipping_Secondary_Qty (
552 p_api_version IN NUMBER
553 , p_init_msg_list IN VARCHAR2
554 , p_commit IN VARCHAR2
555 , x_return_status OUT NOCOPY VARCHAR2
556 , x_msg_count OUT NOCOPY NUMBER
557 , x_msg_data OUT NOCOPY VARCHAR2
558 , p_delivery_detail_id IN NUMBER
559 , p_secondary_quantity IN NUMBER
560 , p_secondary_uom_code IN VARCHAR2 := NULL
561 ) IS
562 l_api_name CONSTANT VARCHAR2(30) := 'Update_Shipping_Secondary_Qty';
563 l_api_version CONSTANT NUMBER := 1.0;
564 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
565 l_progress VARCHAR2(10) := '0';
566
567 l_shipping_attr WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type;
568 l_shipping_in_rec WSH_INTERFACE_EXT_GRP.detailInRecType;
569 l_shipping_out_rec WSH_INTERFACE_EXT_GRP.detailOutRecType;
570
571 l_msg_details VARCHAR2(3000);
572 l_pricing_ind VARCHAR2(30);
573 l_tolerance NUMBER;
574 l_organization_id NUMBER;
575 l_inventory_item_id NUMBER;
576 l_primary_uom_code VARCHAR2(3) := NULL;
577 l_picked_quantity NUMBER;
578 l_requested_quantity_uom VARCHAR2(3);
579 l_secondary_quantity NUMBER;
580 l_secondary_uom_code VARCHAR(3);
581
582 BEGIN
583 SAVEPOINT UPDATE_SHIPPING_SECONDARY_QTY;
584
585 -- Standard call to check for call compatibility.
586 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
587 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
588 fnd_msg_pub.ADD;
589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
590 END IF;
591
592 -- Initialize message list if p_init_msg_list is set to TRUE.
593 IF fnd_api.to_boolean(p_init_msg_list) THEN
594 fnd_msg_pub.initialize;
595 END IF;
596
597 -- Initialize API return status to success
598 x_return_status := fnd_api.g_ret_sts_success;
599
600 IF ( l_debug = 1 ) THEN
601 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
602 print_debug('deldetid='||p_delivery_detail_id||' secqty='||p_secondary_quantity||' secuom='||p_secondary_uom_code, 4);
603 END IF;
604
605 BEGIN
606 SELECT organization_id, inventory_item_id, picked_quantity, requested_quantity_uom
607 INTO l_organization_id, l_inventory_item_id, l_picked_quantity, l_requested_quantity_uom
608 FROM wsh_delivery_details
609 WHERE delivery_detail_id = p_delivery_detail_id;
610
611 IF ( l_debug = 1 ) THEN
612 print_debug('got from WDD orgid='||l_organization_id||' itemid='||l_inventory_item_id||' pkdqty='||l_picked_quantity||' requom='||l_requested_quantity_uom, 4);
613 END IF;
614 EXCEPTION
615 WHEN OTHERS THEN
616 x_return_status := fnd_api.g_ret_sts_error;
617 fnd_message.set_name('WMS', 'WMS_MISSING_WDD_ERR');
618 fnd_msg_pub.ADD;
619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
620 END;
621
622 Get_Default_Secondary_Quantity (
623 p_api_version => 1.0
624 , x_return_status => x_return_status
625 , x_msg_count => x_msg_count
626 , x_msg_data => x_msg_data
627 , p_organization_id => l_organization_id
628 , p_inventory_item_id => l_inventory_item_id
629 , p_quantity => l_picked_quantity
633 , x_secondary_quantity => l_secondary_uom_code);
630 , p_uom_code => l_requested_quantity_uom
631 , x_ont_pricing_qty_source => l_pricing_ind
632 , x_secondary_uom_code => l_secondary_quantity
634
635 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
636 IF ( l_debug = 1 ) THEN
637 print_debug('Call to Get_Default_Secondary_Quantity failed', 1);
638 END IF;
639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
640 END IF;
641
642 IF ( l_pricing_ind = G_PRICE_SECONDARY ) THEN
643 l_progress := '100';
644 -- Item is catch weight enabled.
645 IF ( l_secondary_uom_code <> p_secondary_uom_code ) THEN
646 fnd_message.set_name('WMS', 'WMS_SEC_UOM_MISMATCH');
647 fnd_message.set_token('uom1', p_secondary_uom_code);
648 fnd_message.set_token('uom2', l_secondary_uom_code);
649 fnd_msg_pub.ADD;
650 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
651 END IF;
652
653 IF ( p_secondary_quantity = FND_API.G_MISS_NUM ) THEN
654 l_progress := '200';
655
656 -- User wishes to set secondary values to null
657 l_secondary_quantity := FND_API.G_MISS_NUM;
658 l_secondary_uom_code := FND_API.G_MISS_CHAR;
659 ELSIF ( p_secondary_quantity IS NOT NULL ) THEN
660 l_progress := '300';
661
662 -- Check to make sure that the secondary qty is within tolerance
663 l_tolerance := Check_Secondary_Qty_Tolerance (
664 p_api_version => 1.0
665 , x_return_status => x_return_status
666 , x_msg_count => x_msg_count
667 , x_msg_data => x_msg_data
668 , p_organization_id => l_organization_id
669 , p_inventory_item_id => l_inventory_item_id
670 , p_quantity => l_picked_quantity
671 , p_uom_code => l_requested_quantity_uom
672 , p_secondary_quantity => p_secondary_quantity );
673
674 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
675 IF ( l_debug = 1 ) THEN
676 print_debug('Check_Secondary_Qty_Tolerance failed ', 4);
677 END IF;
678 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
679 END IF;
680
681 l_progress := '400';
682 IF ( l_tolerance <> 0 ) THEN
683 IF ( l_debug = 1 ) THEN
684 print_debug('Secondary quantity out of tolerance', 4);
685 END IF;
686 fnd_message.set_name('WMS', 'WMS_CTWT_TOLERANCE_ERROR');
687 fnd_msg_pub.ADD;
688 RAISE FND_API.G_EXC_ERROR;
689 END IF;
690
691 -- User specified sec qty, do not need to use the default value.
692 l_secondary_quantity := p_secondary_quantity;
693 ELSIF ( l_secondary_quantity IS NULL ) THEN
694 -- cannot defualt secondary quantity error out
695 IF ( l_debug = 1 ) THEN
696 print_debug('Cannot default secondary quantity', 4);
697 END IF;
698 fnd_message.set_name('WMS','WMS_CTWT_DEFAULT_ERROR');
699 fnd_msg_pub.ADD;
700 RAISE FND_API.G_EXC_ERROR;
701 END IF;
702
703 -- If everything checks out, update wdd.picked_quantity2 with catch weight.
704 l_shipping_attr(1).delivery_detail_id := p_delivery_detail_id;
705 l_shipping_attr(1).picked_quantity2 := round(l_secondary_quantity, g_precision);
706 l_shipping_attr(1).requested_quantity_uom2 := l_secondary_uom_code;
707
708 l_shipping_in_rec.caller := 'WMS';
709 l_shipping_in_rec.action_code := 'UPDATE';
710
711 WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail (
712 p_api_version_number => 1.0
713 , p_init_msg_list => fnd_api.g_false
714 , p_commit => fnd_api.g_false
715 , x_return_status => x_return_status
716 , x_msg_count => x_msg_count
717 , x_msg_data => x_msg_data
718 , p_detail_info_tab => l_shipping_attr
719 , p_IN_rec => l_shipping_in_rec
720 , x_OUT_rec => l_shipping_out_rec );
721
722 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
723 --Get error messages from shipping
724 WSH_UTIL_CORE.get_messages('Y', x_msg_data, l_msg_details, x_msg_count);
725 IF x_msg_count > 1 then
726 x_msg_data := x_msg_data || l_msg_details;
727 ELSE
728 x_msg_data := x_msg_data;
729 END IF;
730
731 IF (l_debug = 1) THEN
732 print_debug('Error calling Create_Update_Delivery_Detail: '||x_msg_data, 9);
733 END IF;
734 FND_MESSAGE.SET_NAME('WMS','WMS_UPD_DELIVERY_ERROR' );
735 fnd_message.set_token('MSG1', x_msg_data);
736 FND_MSG_PUB.ADD;
737 RAISE FND_API.g_exc_unexpected_error;
738 END IF;
739
740 l_progress := '400';
741 END IF;
742
743 -- End of API body
744 IF fnd_api.to_boolean(p_commit) THEN
745 COMMIT WORK;
746 END IF;
747
748 IF ( l_debug = 1 ) THEN
749 print_debug(l_api_name || ' Exited ', 1);
750 END IF;
751
752 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
753 EXCEPTION
754 WHEN OTHERS THEN
755 IF (l_debug = 1) THEN
756 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
757 IF ( SQLCODE IS NOT NULL ) THEN
758 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
762 ROLLBACK TO UPDATE_SHIPPING_SECONDARY_QTY;
759 END IF;
760 END IF;
761
763 x_return_status := fnd_api.g_ret_sts_error;
764 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
765 fnd_msg_pub.ADD;
766 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
767 END Update_Shipping_Secondary_Qty;
768
769
770 PROCEDURE Update_Parent_Delivery_Sec_Qty (
771 p_api_version IN NUMBER
772 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
773 , p_commit IN VARCHAR2 := fnd_api.g_false
774 , x_return_status OUT NOCOPY VARCHAR2
775 , x_msg_count OUT NOCOPY NUMBER
776 , x_msg_data OUT NOCOPY VARCHAR2
777 , p_organization_id IN NUMBER
778 , p_parent_del_det_id IN NUMBER
779 , p_inventory_item_id IN NUMBER
780 , p_revision IN VARCHAR2 := NULL
781 , p_lot_number IN VARCHAR2 := NULL
782 , p_quantity IN NUMBER
783 , p_uom_code IN VARCHAR2
784 , p_secondary_quantity IN NUMBER
785 , p_secondary_uom_code IN VARCHAR2
786 ) IS
787 l_api_name CONSTANT VARCHAR2(30) := 'Update_Parent_Delivery_Sec_Qty';
788 l_api_version CONSTANT NUMBER := 1.0;
789 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
790 l_progress VARCHAR2(10) := '0';
791
792 CURSOR wdd_cur IS
793 SELECT wdd.delivery_detail_id, wdd.picked_quantity, wdd.requested_quantity_uom,
794 wdd.picked_quantity2, wdd.requested_quantity_uom2
795 FROM wsh_delivery_assignments_v wda, wsh_delivery_details wdd
796 WHERE wda.parent_delivery_detail_id = p_parent_del_det_id
797 AND wdd.delivery_detail_id = wda.delivery_detail_id
798 AND wdd.organization_id = p_organization_id
799 AND wdd.inventory_item_id = p_inventory_item_id
800 AND NVL(wdd.revision, '@') = NVL(p_revision, '@')
801 AND NVL(wdd.lot_number, '@') = NVL(p_lot_number, '@');
802
803 l_shipping_attr WSH_INTERFACE_EXT_GRP.delivery_details_Attr_tbl_Type;
804 l_shipping_in_rec WSH_INTERFACE_EXT_GRP.detailInRecType;
805 l_shipping_out_rec WSH_INTERFACE_EXT_GRP.detailOutRecType;
806 l_msg_details VARCHAR2(3000);
807 l_attr_counter NUMBER := 1;
808 l_del_det_id NUMBER;
809 l_line_quantity NUMBER;
810 l_total_quantity NUMBER := 0;
811
812 BEGIN
813 SAVEPOINT UPDATE_PARENT_DELIVERY_SEC_QTY;
814
815 -- Standard call to check for call compatibility.
816 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
817 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
818 fnd_msg_pub.ADD;
819 RAISE fnd_api.g_exc_unexpected_error;
820 END IF;
821
822 -- Initialize message list if p_init_msg_list is set to TRUE.
823 IF fnd_api.to_boolean(p_init_msg_list) THEN
824 fnd_msg_pub.initialize;
825 END IF;
826
827 -- Initialize API return status to success
828 x_return_status := fnd_api.g_ret_sts_success;
829
830 IF ( l_debug = 1 ) THEN
831 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
832 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);
833 print_debug('qty='||p_quantity||' uom='||p_uom_code||' secqty='||p_secondary_quantity||' secuom='||p_secondary_uom_code, 4);
834 END IF;
835
836 --Updating WDD with secondary quantity information
837 FOR wdd_rec IN wdd_cur LOOP
838 IF ( l_debug = 1 ) THEN
839 print_debug('Got from WDD deldetid='||wdd_rec.delivery_detail_id||' pkqty='||wdd_rec.picked_quantity||' requom='||wdd_rec.requested_quantity_uom, 4);
840 print_debug('pkqty2='||wdd_rec.picked_quantity2||' requom2='||wdd_rec.requested_quantity_uom2, 4);
841 END IF;
842 IF ( p_secondary_quantity IS NULL ) THEN
843 -- Caller wants to null out all secondary_quantity for item/lot
844 -- but only if they are not already null
845 IF ( wdd_rec.picked_quantity2 IS NOT NULL OR
846 wdd_rec.requested_quantity_uom2 IS NOT NULL ) THEN
847 l_shipping_attr(l_attr_counter).delivery_detail_id := wdd_rec.delivery_detail_id;
848 l_shipping_attr(l_attr_counter).picked_quantity2 := FND_API.G_MISS_NUM;
849 l_shipping_attr(l_attr_counter).requested_quantity_uom2 := FND_API.G_MISS_CHAR;
850 l_attr_counter := l_attr_counter + 1;
851 END IF;
852 ELSE
853 IF ( wdd_rec.requested_quantity_uom <> p_uom_code ) THEN
854 l_line_quantity := inv_convert.inv_um_convert(
855 p_inventory_item_id
856 , 6
857 , wdd_rec.picked_quantity
858 , wdd_rec.requested_quantity_uom
859 , p_uom_code
860 , NULL
861 , NULL );
862 IF ( l_line_quantity < 0 ) THEN
863 IF (l_debug = 1) THEN
864 print_debug('Error converting to picked qty from '||wdd_rec.requested_quantity_uom||' to '||p_uom_code, 1);
865 END IF;
866 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
867 fnd_message.set_token('uom1', wdd_rec.requested_quantity_uom);
868 fnd_message.set_token('uom2', p_uom_code);
869 fnd_message.set_token('module', l_api_name);
870 fnd_msg_pub.ADD;
871 RAISE FND_API.G_EXC_ERROR;
872 END IF;
876
873 ELSE
874 l_line_quantity := wdd_rec.picked_quantity;
875 END IF;
877 l_shipping_attr(l_attr_counter).delivery_detail_id := wdd_rec.delivery_detail_id;
878 l_shipping_attr(l_attr_counter).picked_quantity2 := round(p_secondary_quantity*(l_line_quantity/p_quantity), g_precision);
879 l_shipping_attr(l_attr_counter).requested_quantity_uom2 := p_secondary_uom_code;
880 l_attr_counter := l_attr_counter + 1;
881
882 l_total_quantity := l_total_quantity + l_line_quantity;
883 END IF;
884 END LOOP;
885
886 l_progress := '100';
887 -- Do sanity check to make sure the correct qty of items is being updated before callin shipping api
888 IF ( p_secondary_quantity IS NOT NULL AND round(p_quantity, g_precision) <> round(l_total_quantity, g_precision) ) THEN
889 IF (l_debug = 1) THEN
890 print_debug('p_quantity '||p_quantity||' does not match the sum quantity '||l_total_quantity, 9);
891 END IF;
892 FND_MESSAGE.SET_NAME('WMS','WMS_QTY_UPD_MISMATCH_ERR');
893 FND_MESSAGE.SET_TOKEN('QTY1', p_quantity);
894 FND_MESSAGE.SET_TOKEN('QTY2', l_total_quantity);
895 FND_MSG_PUB.ADD;
896 RAISE FND_API.G_EXC_ERROR;
897 END IF;
898
899 l_progress := '200';
900
901 IF ( l_shipping_attr.count > 0 ) THEN
902 l_shipping_in_rec.caller := 'WMS';
903 l_shipping_in_rec.action_code := 'UPDATE';
904
905 IF (l_debug = 1) THEN
906 print_debug('Calling Create_Update_Delivery_Detail count='||l_shipping_attr.count, 9);
907 END IF;
908
909 WSH_INTERFACE_EXT_GRP.Create_Update_Delivery_Detail (
910 p_api_version_number => 1.0
911 , p_init_msg_list => fnd_api.g_false
912 , p_commit => fnd_api.g_false
913 , x_return_status => x_return_status
914 , x_msg_count => x_msg_count
915 , x_msg_data => x_msg_data
916 , p_detail_info_tab => l_shipping_attr
917 , p_IN_rec => l_shipping_in_rec
918 , x_OUT_rec => l_shipping_out_rec );
919
920 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
921 --Get error messages from shipping
922 WSH_UTIL_CORE.get_messages('Y', x_msg_data, l_msg_details, x_msg_count);
923 IF x_msg_count > 1 then
924 x_msg_data := x_msg_data || l_msg_details;
925 ELSE
926 x_msg_data := x_msg_data;
927 END IF;
928
929 IF (l_debug = 1) THEN
930 print_debug('Error calling Create_Update_Delivery_Detail: '||x_msg_data, 9);
931 END IF;
932 FND_MESSAGE.SET_NAME('WMS','WMS_UPD_DELIVERY_ERROR' );
933 fnd_message.set_token('MSG1', x_msg_data);
934 FND_MSG_PUB.ADD;
935 RAISE FND_API.g_exc_unexpected_error;
936 END IF;
937 END IF;
938
939 l_progress := '300';
940 -- End of API body
941 IF fnd_api.to_boolean(p_commit) THEN
942 COMMIT WORK;
943 END IF;
944
945 IF ( l_debug = 1 ) THEN
946 print_debug( l_api_name || ' Exited ', 1);
947 END IF;
948
949 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
950 EXCEPTION
951 WHEN OTHERS THEN
952 IF (l_debug = 1) THEN
953 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
954 IF ( SQLCODE IS NOT NULL ) THEN
955 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
956 END IF;
957 END IF;
958
959 ROLLBACK TO UPDATE_PARENT_DELIVERY_SEC_QTY;
960 x_return_status := fnd_api.g_ret_sts_error;
961 fnd_message.set_name('WMS', 'WMS_API_FAIL');
962 fnd_msg_pub.ADD;
963 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
964 END Update_Parent_Delivery_Sec_Qty;
965
966 --
967 -- Procedure: Update_Delivery_Detail_Secondary_Quantity
968 -- Parameters:
969 -- Description:
970 --
971 PROCEDURE Update_LPN_Secondary_Quantity (
972 p_api_version IN NUMBER
973 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
974 , p_commit IN VARCHAR2 := fnd_api.g_false
975 , x_return_status OUT NOCOPY VARCHAR2
976 , x_msg_count OUT NOCOPY NUMBER
977 , x_msg_data OUT NOCOPY VARCHAR2
978 , p_record_source IN VARCHAR2
979 , p_organization_id IN NUMBER
980 , p_lpn_id IN NUMBER
981 , p_inventory_item_id IN NUMBER
982 , p_revision IN VARCHAR2 := NULL
983 , p_lot_number IN VARCHAR2 := NULL
984 , p_quantity IN NUMBER
985 , p_uom_code IN VARCHAR2
986 , p_secondary_quantity IN NUMBER
987 , p_secondary_uom_code IN VARCHAR2
988 ) IS
989 l_api_name CONSTANT VARCHAR2(30) := 'Update_LPN_Secondary_Quantity';
990 l_api_version CONSTANT NUMBER := 1.0;
991 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
992 l_progress VARCHAR2(10) := '0';
993
994 CURSOR mmtt_cur IS
995 SELECT rowid, transaction_temp_id, transaction_quantity, transaction_uom
996 FROM mtl_material_transactions_temp
997 WHERE organization_id = p_organization_id
998 AND inventory_item_id = p_inventory_item_id
999 AND NVL(revision, '@') = NVL(p_revision, '@')
1000 AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
1001 AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
1005 SELECT rowid, transaction_quantity
1002 AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id;
1003
1004 CURSOR mtlt_cur(p_trx_temp_id NUMBER) IS
1006 FROM mtl_transaction_lots_temp
1007 WHERE transaction_temp_id = p_trx_temp_id
1008 AND lot_number = p_lot_number;
1009
1010 l_del_det_id NUMBER;
1011 l_line_quantity NUMBER;
1012 l_total_quantity NUMBER := 0;
1013 BEGIN
1014 SAVEPOINT UPDATE_LPN_SECONDARY_QUANTITY;
1015
1016 -- Standard call to check for call compatibility.
1017 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1018 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1019 fnd_msg_pub.ADD;
1020 RAISE fnd_api.g_exc_unexpected_error;
1021 END IF;
1022
1023 -- Initialize message list if p_init_msg_list is set to TRUE.
1024 IF fnd_api.to_boolean(p_init_msg_list) THEN
1025 fnd_msg_pub.initialize;
1026 END IF;
1027
1028 -- Initialize API return status to success
1029 x_return_status := fnd_api.g_ret_sts_success;
1030
1031 IF ( l_debug = 1 ) THEN
1032 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
1033 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);
1034 print_debug('qty='||p_quantity||' uom='||p_uom_code||' secqty='||p_secondary_quantity||' secuom'||p_secondary_uom_code, 4);
1035 END IF;
1036
1037 IF ( p_record_source = 'WDD' OR p_record_source = 'wdd' ) THEN
1038 l_progress := '100';
1039 -- LPN is in staging, Update WSH_DELIVERY_DETAILS
1040 -- Need to retrieve the delviery_detail_id for the LPN, then pass to
1041 -- Other API from WDD processing
1042 BEGIN
1043 SELECT delivery_detail_id
1044 INTO l_del_det_id
1045 FROM wsh_delivery_details
1046 WHERE organization_id = p_organization_id
1047 AND lpn_id = p_lpn_id
1048 AND released_status = 'X'; -- For LPN reuse ER : 6845650
1049 EXCEPTION
1050 WHEN NO_DATA_FOUND THEN
1051 l_del_det_id := NULL;
1052 END;
1053
1054 l_progress := '200';
1055 IF ( l_del_det_id IS NOT NULL ) THEN
1056 Update_Parent_Delivery_Sec_Qty (
1057 p_api_version => 1.0
1058 , x_return_status => x_return_status
1059 , x_msg_count => x_msg_count
1060 , x_msg_data => x_msg_data
1061 , p_organization_id => p_organization_id
1062 , p_parent_del_det_id => l_del_det_id
1063 , p_inventory_item_id => p_inventory_item_id
1064 , p_revision => p_revision
1065 , p_lot_number => p_lot_number
1066 , p_quantity => p_quantity
1067 , p_uom_code => p_uom_code
1068 , p_secondary_quantity => p_secondary_quantity
1069 , p_secondary_uom_code => p_secondary_uom_code );
1070
1071 IF ( x_return_status <> fnd_api.g_ret_sts_success ) THEN
1072 RAISE fnd_api.g_exc_unexpected_error;
1073 END IF;
1074 END IF;
1075 ELSIF ( p_record_source = 'MMTT' OR p_record_source = 'mmtt' ) THEN
1076 l_progress := '300';
1077 -- This is assumed to be an update of LPNs that are before drop, update MMTT
1078 IF ( p_lot_number IS NULL ) THEN
1079 l_progress := '400';
1080 IF ( p_secondary_quantity IS NULL ) THEN
1081 -- Caller wants to null out all secondary_quantity for this item
1082 UPDATE mtl_material_transactions_temp
1083 SET secondary_transaction_quantity = NULL,
1084 secondary_uom_code = NULL
1085 WHERE organization_id = p_organization_id
1086 AND inventory_item_id = p_inventory_item_id
1087 AND NVL(revision, '@') = NVL(p_revision, '@')
1088 AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
1089 AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
1090 AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id;
1091 ELSE
1092 FOR mmtt_rec IN mmtt_cur LOOP
1093 IF ( l_debug = 1 ) THEN
1094 print_debug('Got from MMTT trxtempid='||mmtt_rec.transaction_temp_id||' trxqty='||mmtt_rec.transaction_quantity||' trxuom='||mmtt_rec.transaction_uom, 4);
1095 END IF;
1096
1097 IF ( mmtt_rec.transaction_uom <> p_uom_code ) THEN
1098 l_line_quantity := inv_convert.inv_um_convert(
1099 p_inventory_item_id
1100 , 6
1101 , mmtt_rec.transaction_quantity
1102 , mmtt_rec.transaction_uom
1103 , p_uom_code
1104 , NULL
1105 , NULL );
1106 IF ( l_line_quantity < 0 ) THEN
1107 IF ( l_debug = 1 ) THEN
1108 print_debug('Error converting to trx qty from '||mmtt_rec.transaction_uom||' to '||p_uom_code, 1);
1109 END IF;
1110 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
1111 fnd_message.set_token('uom1', mmtt_rec.transaction_uom);
1112 fnd_message.set_token('uom2', p_uom_code);
1113 fnd_message.set_token('module', l_api_name);
1114 fnd_msg_pub.ADD;
1115 RAISE FND_API.G_EXC_ERROR;
1116 END IF;
1117 ELSE
1118 l_line_quantity := mmtt_rec.transaction_quantity;
1119 END IF;
1120
1124 WHERE rowid = mmtt_rec.rowid;
1121 UPDATE mtl_material_transactions_temp
1122 SET secondary_transaction_quantity = round(p_secondary_quantity*(l_line_quantity/p_quantity), g_precision),
1123 secondary_uom_code = p_secondary_uom_code
1125
1126 --Add to the total primary quantity for sanity check at the end
1127 l_total_quantity := l_total_quantity + l_line_quantity;
1128 END LOOP;
1129 END IF;
1130 l_progress := '500';
1131 ELSE -- p_lot_number is not null
1132 l_progress := '600';
1133 IF ( p_secondary_quantity IS NULL ) THEN
1134 -- Caller wants to null out all secondary_quantity for this lot
1135 UPDATE mtl_transaction_lots_temp
1136 SET secondary_quantity = NULL,
1137 secondary_unit_of_measure = NULL
1138 WHERE lot_number = p_lot_number
1139 AND transaction_temp_id IN (
1140 SELECT transaction_temp_id
1141 FROM mtl_material_transactions_temp mmtt
1142 WHERE organization_id = p_organization_id
1143 AND inventory_item_id = p_inventory_item_id
1144 AND NVL(revision, '@') = NVL(p_revision, '@')
1145 AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
1146 AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
1147 AND NVL(content_lpn_id, transfer_lpn_id) = p_lpn_id );
1148 ELSE
1149 FOR mmtt_rec IN mmtt_cur LOOP
1150 IF ( l_debug = 1 ) THEN
1151 print_debug('Got from MMTT trxtempid='||mmtt_rec.transaction_temp_id||' trxqty='||mmtt_rec.transaction_quantity||' trxuom='||mmtt_rec.transaction_uom, 4);
1152 END IF;
1153
1154 FOR mtlt_rec IN mtlt_cur(mmtt_rec.transaction_temp_id) LOOP
1155 IF ( l_debug = 1 ) THEN
1156 print_debug('Got form MTLT lottrxqty='||mtlt_rec.transaction_quantity, 4);
1157 END IF;
1158
1159 IF ( mmtt_rec.transaction_uom <> p_uom_code ) THEN
1160 l_line_quantity := inv_convert.inv_um_convert(
1161 p_inventory_item_id
1162 , 6
1163 , mtlt_rec.transaction_quantity
1164 , mmtt_rec.transaction_uom
1165 , p_uom_code
1166 , NULL
1167 , NULL );
1168 IF ( l_line_quantity < 0 ) THEN
1169 IF ( l_debug = 1 ) THEN
1170 print_debug('Error converting to trx qty from '||mmtt_rec.transaction_uom||' to '||p_uom_code, 1);
1171 END IF;
1172 fnd_message.set_name('INV', 'INV_UOM_CONVERSION_ERROR');
1173 fnd_message.set_token('uom1', mmtt_rec.transaction_uom);
1174 fnd_message.set_token('uom2', p_uom_code);
1175 fnd_message.set_token('module', l_api_name);
1176 fnd_msg_pub.ADD;
1177 RAISE FND_API.G_EXC_ERROR;
1178 END IF;
1179 ELSE
1180 l_line_quantity := mtlt_rec.transaction_quantity;
1181 END IF;
1182
1183 UPDATE mtl_transaction_lots_temp
1184 SET secondary_quantity = round(p_secondary_quantity*(l_line_quantity/p_quantity), g_precision),
1185 secondary_unit_of_measure = p_secondary_uom_code
1186 WHERE rowid = mtlt_rec.rowid;
1187
1188 --Add to the total primary quantity for sanity check at the end
1189 l_total_quantity := l_total_quantity + l_line_quantity;
1190 END LOOP;
1191 END LOOP;
1192 END IF;
1193 END IF;
1194 l_progress := '700';
1195 -- Sanity check to make sure the correct quantity of items were updated
1196 IF(p_secondary_quantity IS NOT NULL AND round(p_quantity, g_precision) <> round(l_total_quantity, g_precision) ) THEN
1197 IF (l_debug = 1) THEN
1198 print_debug('the p_quantity '||p_quantity||' does not match the sum quantity '||l_total_quantity, 9);
1199 END IF;
1200 FND_MESSAGE.SET_NAME('WMS','WMS_QTY_UPD_MISMATCH_ERROR');
1201 FND_MESSAGE.SET_TOKEN('QTY1', p_quantity);
1202 FND_MESSAGE.SET_TOKEN('QTY2', l_total_quantity);
1203 FND_MSG_PUB.ADD;
1204 RAISE FND_API.G_EXC_ERROR;
1205 END IF;
1206 END IF;
1207
1208 l_progress := '800';
1209 -- End of API body
1210 IF fnd_api.to_boolean(p_commit) THEN
1211 COMMIT WORK;
1212 END IF;
1213
1214 IF ( l_debug = 1 ) THEN
1215 print_debug(l_api_name || ' Exited ', 1);
1216 END IF;
1217
1218 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1219 EXCEPTION
1220 WHEN OTHERS THEN
1221 IF (l_debug = 1) THEN
1222 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
1223 IF ( SQLCODE IS NOT NULL ) THEN
1224 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1225 END IF;
1226 END IF;
1227
1228 ROLLBACK TO UPDATE_LPN_SECONDARY_QUANTITY;
1229 x_return_status := fnd_api.g_ret_sts_error;
1230 fnd_message.set_name('WMS', 'WMS_OTHERS_ERROR_CALL'||l_progress);
1231 fnd_msg_pub.ADD;
1232 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1233 END Update_LPN_Secondary_Quantity;
1234
1235
1236 FUNCTION Check_LPN_Secondary_Quantity (
1237 p_api_version IN NUMBER
1241 , x_msg_data OUT NOCOPY VARCHAR2
1238 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
1239 , x_return_status OUT NOCOPY VARCHAR2
1240 , x_msg_count OUT NOCOPY NUMBER
1242 , p_organization_id IN NUMBER
1243 , p_outermost_lpn_id IN NUMBER
1244 ) RETURN NUMBER IS
1245 l_api_name CONSTANT VARCHAR2(30) := 'Check_LPN_Secondary_Quantity';
1246 l_api_version CONSTANT NUMBER := 1.0;
1247 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1248 l_progress VARCHAR2(10) := '0';
1249
1250 CURSOR wdd_nested_lpn_cur IS
1251 SELECT wlpn.lpn_id, wdd.delivery_detail_id
1252 FROM wms_license_plate_numbers wlpn,
1253 wsh_delivery_details wdd
1254 WHERE wlpn.organization_id = p_organization_id
1255 AND wlpn.outermost_lpn_id = p_outermost_lpn_id
1256 AND wdd.organization_id = wlpn.organization_id
1257 AND wdd.lpn_id = wlpn.lpn_id
1258 AND wdd.released_status = 'X'; -- For LPN reuse ER : 6845650
1259
1260 CURSOR wdd_item_cur (p_parent_delivery_detail_id NUMBER) IS
1261 SELECT distinct wdd.organization_id, wdd.inventory_item_id, msi.primary_uom_code, msi.secondary_uom_code
1262 FROM mtl_system_items msi,
1263 wsh_delivery_details wdd,
1264 wsh_delivery_assignments_v wda
1265 WHERE wda.parent_delivery_detail_id = p_parent_delivery_detail_id
1266 AND wdd.delivery_detail_id = wda.delivery_detail_id
1267 AND wdd.line_direction = 'O'
1268 AND wdd.picked_quantity2 IS NULL
1269 AND msi.organization_id = wdd.organization_id
1270 AND msi.inventory_item_id = wdd.inventory_item_id
1271 AND msi.ont_pricing_qty_source = G_PRICE_SECONDARY
1272 AND msi.secondary_default_ind = G_SECONDARY_DEFAULT
1273 ORDER BY msi.primary_uom_code, msi.secondary_uom_code;
1274
1275 CURSOR mmtt_item_cur IS
1276 SELECT distinct inventory_item_id, organization_id
1277 FROM mtl_material_transactions_temp
1278 WHERE organization_id = p_organization_id
1279 AND transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
1280 AND transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
1281 AND ( transfer_lpn_id = p_outermost_lpn_id OR content_lpn_id = p_outermost_lpn_id )
1282 AND ( secondary_transaction_quantity IS NULL OR secondary_uom_code IS NULL );
1283
1284 l_return NUMBER := G_CHECK_SUCCESS;
1285 l_lpn_context NUMBER;
1286 l_temp NUMBER := 0;
1287 l_prev_org_id NUMBER := -999;
1288 l_prev_item_id NUMBER := -999;
1289 l_prev_pri_uom VARCHAR2(3) := '@';
1290 l_prev_sec_uom VARCHAR2(3) := '@';
1291
1292 l_pricing_ind VARCHAR2(30);
1293 l_default_ind VARCHAR2(30);
1294 l_pri_uom VARCHAR2(3);
1295 l_sec_uom VARCHAR2(3);
1296 l_lot_control_code NUMBER;
1297 l_uom_conv_rate NUMBER;
1298 BEGIN
1299 -- Standard call to check for call compatibility.
1300 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1301 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
1302 fnd_msg_pub.ADD;
1303 RAISE fnd_api.g_exc_unexpected_error;
1304 END IF;
1305
1306 -- Initialize message list if p_init_msg_list is set to TRUE.
1307 IF fnd_api.to_boolean(p_init_msg_list) THEN
1308 fnd_msg_pub.initialize;
1309 END IF;
1310
1311 -- Initialize API return status to success
1312 x_return_status := fnd_api.g_ret_sts_success;
1313
1314 IF ( l_debug = 1 ) THEN
1315 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
1316 print_debug('orgid='||p_organization_id||' outerlpnid='||p_outermost_lpn_id, 4);
1317 END IF;
1318
1319 BEGIN
1320 SELECT lpn_context
1321 INTO l_lpn_context
1322 FROM wms_license_plate_numbers
1323 WHERE organization_id = p_organization_id
1324 AND lpn_id = p_outermost_lpn_id;
1325 EXCEPTION
1326 WHEN NO_DATA_FOUND THEN
1327 IF ( l_debug = 1 ) THEN
1328 print_debug('Error Could not find outermost lpn', 1);
1329 END IF;
1330 RAISE FND_API.G_EXC_ERROR;
1331 WHEN OTHERS THEN
1332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1333 END;
1334
1335 l_progress := '000';
1336 IF ( l_lpn_context = WMS_CONTAINER_PVT.LPN_CONTEXT_PICKED OR
1337 l_lpn_context = WMS_CONTAINER_PVT.LPN_LOADED_FOR_SHIPMENT ) THEN
1338 l_progress := '100';
1339 -- Records should be checked in WDD to see if any catch weight item is
1340 -- is missing secondary quantities (picked_quantity2)
1341 FOR wdd_nested_lpn_rec IN wdd_nested_lpn_cur LOOP
1342 IF ( l_debug = 1 ) THEN
1343 print_debug('lpnid='||wdd_nested_lpn_rec.lpn_id||' ddid='||wdd_nested_lpn_rec.delivery_detail_id, 1);
1344 END IF;
1345
1346 -- Check LPN that all catch weight enabled items that are not defaultable have sec qty.
1347 -- bug 4918256 only for WDD lines that are part of sales orders (line_direction = 'O')
1348 BEGIN
1349 SELECT 1 INTO l_temp FROM DUAL
1350 WHERE EXISTS (
1351 SELECT 1
1352 FROM mtl_system_items msi,
1353 wsh_delivery_details wdd,
1354 wsh_delivery_assignments_v wda
1355 WHERE wda.parent_delivery_detail_id = wdd_nested_lpn_rec.delivery_detail_id
1356 AND wdd.delivery_detail_id = wda.delivery_detail_id
1357 AND wdd.line_direction = 'O'
1358 AND wdd.picked_quantity2 IS NULL
1362 AND msi.secondary_default_ind = G_SECONDARY_NO_DEFAULT );
1359 AND msi.organization_id = wdd.organization_id
1360 AND msi.inventory_item_id = wdd.inventory_item_id
1361 AND msi.ont_pricing_qty_source = G_PRICE_SECONDARY
1363
1364 IF ( l_temp = 1 ) THEN
1365 IF ( l_debug = 1 ) THEN
1366 print_debug('Found lpn in wdd with catch weight item that require secondary qty: lpnid=' ||wdd_nested_lpn_rec.lpn_id, 1);
1367 END IF;
1368
1369 l_return := G_CHECK_ERROR;
1370 EXIT;
1371 END IF;
1372 EXCEPTION
1373 WHEN NO_DATA_FOUND THEN
1374 -- All the items in this lpn check out, continue
1375 NULL;
1376 WHEN OTHERS THEN
1377 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1378 END;
1379
1380 -- Check LPN that all catch weight enabled items that are defaultable and do not
1381 -- have sec qty defined have a valid uom conversion.
1382 FOR wdd_item_rec IN wdd_item_cur(wdd_nested_lpn_rec.delivery_detail_id) LOOP
1383 -- Check that there is a valid uom conversin between primary and secondary.
1384 IF ( wdd_item_rec.primary_uom_code <> l_prev_pri_uom OR
1385 wdd_item_rec.secondary_uom_code <> l_prev_sec_uom ) THEN
1386 -- Call UOM API to check that there is a valid conversion rate
1387 INV_CONVERT.inv_um_conversion (
1388 from_unit => wdd_item_rec.primary_uom_code
1389 , to_unit => wdd_item_rec.secondary_uom_code
1390 , item_id => wdd_item_rec.inventory_item_id
1391 , uom_rate => l_uom_conv_rate );
1392
1393 IF ( l_uom_conv_rate < 0 ) THEN
1394 -- no valid connection uom conversion between these two uoms
1395 l_return := G_CHECK_ERROR;
1396
1397 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
1398 fnd_msg_pub.ADD;
1399 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1400 ELSE
1401 -- there is a valid conversion, change status to warning
1402 l_return := G_CHECK_WARNING;
1403 l_prev_pri_uom := wdd_item_rec.primary_uom_code;
1404 l_prev_sec_uom := wdd_item_rec.secondary_uom_code;
1405 END IF;
1406 END IF;
1407 END LOOP;
1408
1409 -- Exit at any point when a catch weight cannot be resolved
1410 EXIT WHEN l_return = G_CHECK_ERROR;
1411 END LOOP;
1412 ELSIF ( l_lpn_context = WMS_CONTAINER_PVT.LPN_CONTEXT_INV OR
1413 l_lpn_context = WMS_CONTAINER_PVT.LPN_CONTEXT_PACKING OR
1414 l_lpn_context = WMS_CONTAINER_PVT.LPN_CONTEXT_PREGENERATED ) THEN
1415 l_progress := '600';
1416 -- Records should be checked in MMTT to see if any catch weight item is
1417 -- is missing secondary transaction quantities. Currently during picking
1418 -- LPNs can only be nested one level deep. This cursor makes that assumtion
1419 FOR mmtt_item_rec IN mmtt_item_cur LOOP
1420 BEGIN
1421 SELECT ont_pricing_qty_source, secondary_default_ind, primary_uom_code,
1422 secondary_uom_code, lot_control_code
1423 INTO l_pricing_ind, l_default_ind, l_pri_uom, l_sec_uom, l_lot_control_code
1424 FROM mtl_system_items
1425 WHERE organization_id = mmtt_item_rec.organization_id
1426 AND inventory_item_id = mmtt_item_rec.inventory_item_id;
1427 EXCEPTION
1428 WHEN NO_DATA_FOUND THEN
1429 IF ( l_debug = 1 ) THEN
1430 print_debug('Error: could not find item in MSI: orgid='||mmtt_item_rec.organization_id||' itemid='||mmtt_item_rec.inventory_item_id, 1);
1431 END IF;
1432 RAISE FND_API.G_EXC_ERROR;
1433 WHEN OTHERS THEN
1434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1435 END;
1436
1437 IF ( l_pricing_ind = G_PRICE_SECONDARY ) THEN
1438 -- If the item is not lot controlled, return to user that lpn still needs catch weight
1439 IF (l_lot_control_code = 1 ) THEN
1440 -- if item can defaulted check uom conversion to make sure it is valid
1441 IF ( l_default_ind = G_SECONDARY_DEFAULT ) THEN
1442 IF ( l_prev_pri_uom <> l_pri_uom OR l_prev_sec_uom <> l_sec_uom ) THEN
1443 -- Call UOM API to check that there is a valid conversion rate
1444 INV_CONVERT.inv_um_conversion (
1445 from_unit => l_pri_uom
1446 , to_unit => l_sec_uom
1447 , item_id => mmtt_item_rec.inventory_item_id
1448 , uom_rate => l_uom_conv_rate );
1449
1450 IF ( l_uom_conv_rate < 0 ) THEN
1451 -- no valid connection uom conversion between these two uoms
1452 l_return := G_CHECK_ERROR;
1453
1454 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
1455 fnd_msg_pub.ADD;
1456 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1457 ELSE
1458 -- there is a valid conversion, change status to warning
1459 l_return := G_CHECK_WARNING;
1460 l_prev_pri_uom := l_pri_uom;
1461 l_prev_sec_uom := l_sec_uom;
1462 END IF;
1463 END IF;
1464 ELSE
1465 IF ( l_debug = 1 ) THEN
1466 print_debug('Found catch weight item in mmtt with item that requires secondary qty: itemid=' ||mmtt_item_rec.inventory_item_id, 1);
1467 END IF;
1468
1472 BEGIN
1469 l_return := G_CHECK_ERROR;
1470 END IF;
1471 ELSE -- Lot controlled item need to check MTLT for sec qty for
1473 SELECT 1 INTO l_temp FROM DUAL
1474 WHERE EXISTS (
1475 SELECT 1
1476 FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
1477 WHERE mmtt.organization_id = mmtt_item_rec.organization_id
1478 AND mmtt.inventory_item_id = mmtt_item_rec.inventory_item_id
1479 AND mmtt.transaction_source_type_id = INV_GLOBALS.G_SourceType_SalesOrder
1480 AND mmtt.transaction_action_id = INV_GLOBALS.G_Action_Stgxfr
1481 AND ( mmtt.transfer_lpn_id = p_outermost_lpn_id OR mmtt.content_lpn_id = p_outermost_lpn_id )
1482 AND mtlt.transaction_temp_id = mmtt.transaction_temp_id
1483 AND ( mtlt.secondary_quantity IS NULL OR mtlt.secondary_unit_of_measure IS NULL) );
1484 EXCEPTION
1485 WHEN NO_DATA_FOUND THEN
1486 IF ( l_debug = 1 ) THEN
1487 print_debug('Found lot catch weight item in mtlt with item that requires secondary qty: itemid=' ||mmtt_item_rec.inventory_item_id, 1);
1488 END IF;
1489
1490 l_temp := 0;
1491 WHEN OTHERS THEN
1492 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1493 END;
1494
1495 -- If this lot item was found to not have catch weigth but can be defaulted
1496 -- Check UOM conversion to make sure it's valid
1497 IF ( l_temp = 0 AND l_default_ind = G_SECONDARY_DEFAULT ) THEN
1498 IF ( l_prev_pri_uom <> l_pri_uom OR l_prev_sec_uom <> l_sec_uom ) THEN
1499 -- Call UOM API to check that there is a valid conversion rate
1500 INV_CONVERT.inv_um_conversion (
1501 from_unit => l_pri_uom
1502 , to_unit => l_sec_uom
1503 , item_id => mmtt_item_rec.inventory_item_id
1504 , uom_rate => l_uom_conv_rate );
1505
1506 IF ( l_uom_conv_rate < 0 ) THEN
1507 -- no valid connection uom conversion between these two uoms
1508 l_return := G_CHECK_ERROR;
1509
1510 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
1511 fnd_msg_pub.ADD;
1512 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1513 ELSE
1514 -- there is a valid conversion, change status to warning
1515 l_return := G_CHECK_WARNING;
1516 l_prev_pri_uom := l_pri_uom;
1517 l_prev_sec_uom := l_sec_uom;
1518 END IF;
1519 END IF;
1520 ELSE
1521 IF ( l_debug = 1 ) THEN
1522 print_debug('Found catch weight item in mtlt with item that requires secondary qty: itemid=' ||mmtt_item_rec.inventory_item_id, 1);
1523 END IF;
1524
1525 l_return := G_CHECK_ERROR;
1526 END IF;
1527 END IF;
1528 END IF;
1529
1530 EXIT WHEN l_return = G_CHECK_ERROR;
1531 END LOOP;
1532 END IF;
1533
1534 -- if the return is a warning populate an appropreate
1535 -- message in the message stack
1536 IF ( l_return = G_CHECK_WARNING ) THEN
1537 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_WARNING');
1538 fnd_msg_pub.ADD;
1539 END IF;
1540
1541 IF ( l_debug = 1 ) THEN
1542 print_debug(l_api_name||' Exited '||'ret='||l_return, 1);
1543 END IF;
1544
1545 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1546
1547 RETURN l_return;
1548 EXCEPTION
1549 WHEN OTHERS THEN
1550 IF (l_debug = 1) THEN
1551 print_debug(l_api_name||' Error l_progress=' || l_progress, 1);
1552 IF ( SQLCODE IS NOT NULL ) THEN
1553 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
1554 END IF;
1555 END IF;
1556
1557 x_return_status := fnd_api.g_ret_sts_error;
1558 fnd_message.set_name('WMS', 'WMS_API_FAIL');
1559 fnd_msg_pub.ADD;
1560 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1561 END Check_LPN_Secondary_Quantity;
1562
1563
1564 PROCEDURE GET_OUTER_CATCH_WT_LPN
1565 (x_lpn_lov OUT NOCOPY t_genref,
1566 p_org_id IN NUMBER,
1567 p_lpn IN VARCHAR2,
1568 p_entry_type IN VARCHAR2)
1569 IS
1570 BEGIN
1571
1572 IF (UPPER(p_entry_type) = 'CT_WT_ALL') THEN
1573 OPEN x_lpn_lov FOR
1574 SELECT UNIQUE wlpn2.license_plate_number,
1575 wlpn.outermost_lpn_id outer_lpn_id, wlpn.lpn_context
1576 FROM wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2
1577 WHERE wlpn.outermost_lpn_id = wlpn2.lpn_id
1578 AND wlpn.lpn_context in (11,8) -- picked/loaded
1579 AND wlpn.organization_id = p_org_id
1580 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1581 AND EXISTS (
1582 SELECT msi.inventory_item_id
1583 FROM wms_lpn_contents wlc , mtl_system_items_b msi
1584 WHERE wlc.organization_id = msi.organization_id
1585 AND wlc.inventory_item_id = msi.inventory_item_id
1586 AND msi.ont_pricing_qty_source = 'S'
1587 AND msi.organization_id = p_org_id
1588 AND wlc.parent_lpn_id = wlpn.lpn_id)
1589 UNION
1590 SELECT UNIQUE wlpn.license_plate_number,
1591 nvl(mmtt.transfer_lpn_id,mmtt.content_lpn_id) outer_lpn,
1595 mtl_system_items_b msi
1592 wlpn.lpn_context
1593 FROM mtl_material_transactions_temp mmtt,
1594 wms_license_plate_numbers wlpn,
1596 WHERE mmtt.inventory_item_id = msi.inventory_item_id
1597 AND mmtt.organization_id = msi.organization_id
1598 AND mmtt.organization_id = p_org_id
1599 AND wlpn.lpn_id = nvl(mmtt.transfer_lpn_id, content_lpn_id)
1600 AND wlpn.lpn_context = 8 -- loaded
1601 AND msi.ont_pricing_qty_source = 'S'
1602 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1603 AND parent_line_id is null -- exclude bulk-picked tasks
1604 UNION
1605 SELECT UNIQUE wlpn.license_plate_number,
1606 transfer_lpn_id,
1607 wlpn.lpn_context
1608 FROM mtl_material_transactions_temp mmtt,
1609 wms_license_plate_numbers wlpn,
1610 mtl_system_items_b msi
1611 WHERE mmtt.inventory_item_id = msi.inventory_item_id
1612 AND mmtt.organization_id = msi.organization_id
1613 AND mmtt.organization_id = p_org_id
1614 AND wlpn.lpn_id = mmtt.transfer_lpn_id
1615 AND mmtt.transfer_lpn_id IS NOT NULL
1616 AND EXISTS (SELECT wlpn2.lpn_id
1617 FROM wms_license_plate_numbers wlpn2
1618 WHERE wlpn2.lpn_id = mmtt.content_lpn_id
1619 AND wlpn2.lpn_context = 8)
1620 AND msi.ont_pricing_qty_source = 'S'
1621 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1622 AND parent_line_id is null; -- exclude bulk-picked tasks
1623
1624 ELSE
1625 OPEN x_lpn_lov FOR
1626 SELECT UNIQUE wlpn2.license_plate_number,wlpn.outermost_lpn_id,
1627 wlpn2.lpn_context
1628 FROM wms_license_plate_numbers wlpn, wms_license_plate_numbers wlpn2
1629 WHERE wlpn.outermost_lpn_id = wlpn2.lpn_id
1630 AND wlpn.lpn_context in (11) -- picked
1631 AND wlpn.organization_id = p_org_id
1632 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1633 AND EXISTS (
1634 SELECT msi.inventory_item_id, wddl.lpn_id
1635 FROM mtl_system_items_b msi,
1636 wsh_delivery_details wddl,
1637 wsh_delivery_assignments_v wda,
1638 wsh_delivery_details wddit
1639 WHERE wddit.organization_id = msi.organization_id
1640 AND wddit.inventory_item_id = msi.inventory_item_id
1641 AND msi.ont_pricing_qty_source = 'S'
1642 AND wddl.lpn_id = wlpn.lpn_id
1643 AND wddl.released_status = 'X' -- For LPN reuse ER : 6845650
1644 AND msi.organization_id = p_org_id
1645 AND wddl.delivery_detail_id = wda.parent_delivery_detail_id
1646 AND wddit.delivery_detail_id = wda.delivery_detail_id
1647 AND wddit.picked_quantity2 is null )
1648 UNION
1649 SELECT UNIQUE wlpn.license_plate_number,
1650 nvl(mmtt.transfer_lpn_id,mmtt.content_lpn_id) outer_lpn,
1651 wlpn.lpn_context
1652 FROM mtl_material_transactions_temp mmtt, wms_license_plate_numbers wlpn,
1653 mtl_system_items_b msi, mtl_transaction_lots_temp mtlt
1654 WHERE mmtt.inventory_item_id = msi.inventory_item_id
1655 AND mmtt.organization_id = msi.organization_id
1656 AND mmtt.organization_id = p_org_id
1657 AND msi.ont_pricing_qty_source = 'S'
1658 AND wlpn.lpn_id = nvl(mmtt.transfer_lpn_id, content_lpn_id)
1659 AND wlpn.lpn_context = 8 -- loaded
1660 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1661 AND parent_line_id is null -- exclude bulk-picked tasks
1662 AND secondary_transaction_quantity is null -- catch.wt not enterd
1663 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1664 AND mtlt.secondary_quantity IS NULL
1665 UNION
1666 SELECT UNIQUE wlpn.license_plate_number,
1667 transfer_lpn_id, wlpn.lpn_context
1668 FROM mtl_material_transactions_temp mmtt,
1669 wms_license_plate_numbers wlpn,
1670 mtl_system_items_b msi,
1671 mtl_transaction_lots_temp mtlt
1672 WHERE mmtt.inventory_item_id = msi.inventory_item_id
1673 AND mmtt.organization_id = msi.organization_id
1674 AND mmtt.organization_id = p_org_id
1675 AND wlpn.lpn_id = mmtt.transfer_lpn_id
1676 AND wlpn.license_plate_number LIKE (p_lpn || '%')
1677 AND mmtt.transfer_lpn_id IS NOT NULL
1678 AND EXISTS (SELECT wlpn2.lpn_id
1679 FROM wms_license_plate_numbers wlpn2
1680 WHERE wlpn2.lpn_id = mmtt.content_lpn_id
1681 AND wlpn2.lpn_context = 8)
1682 AND mmtt.secondary_transaction_quantity is null -- catch.wt not enterd
1683 AND msi.ont_pricing_qty_source = 'S'
1684 AND parent_line_id is null -- exclude bulk-picked tasks
1685 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1686 AND mtlt.secondary_quantity IS NULL;
1687 END IF;
1688 END GET_OUTER_CATCH_WT_LPN;
1689
1690
1691 PROCEDURE GET_INNER_CATCH_WT_LPN
1692 (x_lpn_lov OUT NOCOPY t_genref,
1693 p_org_id IN NUMBER,
1694 p_outer_lpn_id IN NUMBER,
1695 p_entry_type IN VARCHAR2,
1696 p_lpn_context IN NUMBER,
1697 p_inner_lpn IN VARCHAR2)
1698 IS
1699 BEGIN
1700
1701 IF (UPPER(p_entry_type) = 'DIRECTSHIP') THEN
1702
1703 OPEN x_lpn_lov FOR
1704 SELECT owlpn.license_plate_number, owlpn.lpn_id
1705 FROM (
1709 AND EXISTS (
1706 SELECT wlpn.license_plate_number, wlpn.lpn_id
1707 FROM wms_license_plate_numbers wlpn
1708 WHERE outermost_lpn_id = p_outer_lpn_id
1710 SELECT 1
1711 FROM mtl_system_items_b msi, wms_lpn_contents wlc
1712 WHERE wlc.organization_id = wlpn.organization_id
1713 AND wlc.parent_lpn_id = wlpn.lpn_id
1714 AND msi.organization_id = wlc.organization_id
1715 AND msi.inventory_item_id = wlc.inventory_item_id
1716 AND msi.ont_pricing_qty_source = 'S'
1717 )
1718 ) owlpn
1719 WHERE owlpn.license_plate_number LIKE (p_inner_lpn || '%')
1720 AND not exists (SELECT 1 FROM WMS_DS_CT_WT_GTEMP gt
1721 WHERE nvl(gt.INNER_LPN_ID, gt.LPN_ID) = owlpn.lpn_id);
1722
1723 ELSIF (UPPER(p_entry_type) = 'CT_WT_ALL') THEN
1724
1725 OPEN x_lpn_lov FOR
1726 SELECT distinct wlpn.license_plate_number, lpn_id
1727 FROM wms_license_plate_numbers wlpn
1728 WHERE outermost_lpn_id = p_outer_lpn_id
1729 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%')
1730 AND EXISTS (
1731 SELECT msi.inventory_item_id
1732 FROM wms_lpn_contents wlc , mtl_system_items_b msi
1733 WHERE wlc.organization_id = msi.organization_id
1734 AND wlc.inventory_item_id = msi.inventory_item_id
1735 AND msi.ont_pricing_qty_source = 'S'
1736 AND wlc.parent_lpn_id = wlpn.lpn_id)
1737 UNION
1738 SELECT distinct license_plate_number, content_lpn_id
1739 FROM mtl_system_items msi, mtl_material_transactions_temp mmtt,
1740 wms_license_plate_numbers wlpn
1741 WHERE (mmtt.content_lpn_id = p_outer_lpn_id
1742 OR mmtt.transfer_lpn_id = p_outer_lpn_id )
1743 AND mmtt.organization_id = p_org_id
1744 AND mmtt.organization_id = msi.organization_id
1745 AND mmtt.inventory_item_id = msi.inventory_item_id
1746 AND msi.ont_pricing_qty_source = 'S'
1747 AND wlpn.lpn_id = mmtt.content_lpn_id
1748 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%');
1749 /*UNION
1750 SELECT distinct license_plate_number,
1751 transfer_lpn_id
1752 FROM mtl_system_items msi,
1753 mtl_material_transactions_temp mmtt,
1754 wms_license_plate_numbers wlpn
1755 WHERE (mmtt.content_lpn_id = p_outer_lpn_id
1756 OR mmtt.transfer_lpn_id = p_outer_lpn_id)
1757 AND mmtt.organization_id = p_org_id
1758 AND mmtt.organization_id = msi.organization_id
1759 AND mmtt.inventory_item_id = msi.inventory_item_id
1760 AND msi.ont_pricing_qty_source = 'S'
1761 AND wlpn.lpn_id = mmtt.transfer_lpn_id
1762 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%');
1763 */
1764 ELSE
1765 IF (p_lpn_context = 8 OR p_lpn_context = 1) THEN --Packing
1766 OPEN x_lpn_lov FOR
1767 SELECT distinct wlpn.license_plate_number, content_lpn_id
1768 FROM mtl_system_items msi
1769 ,mtl_material_transactions_temp mmtt
1770 ,wms_license_plate_numbers wlpn
1771 ,mtl_transaction_lots_temp mtlt
1772 WHERE mmtt.content_lpn_id = p_outer_lpn_id
1773 AND mmtt.organization_id = p_org_id
1774 AND mmtt.organization_id = msi.organization_id
1775 AND mmtt.inventory_item_id = msi.inventory_item_id
1776 AND msi.ont_pricing_qty_source = 'S'
1777 AND mmtt.secondary_transaction_quantity is null
1778 AND mmtt.content_lpn_id = wlpn.lpn_id
1779 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1780 AND mtlt.secondary_quantity IS NULL
1781 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%')
1782 UNION
1783 SELECT distinct wlpn.license_plate_number, content_lpn_id
1784 FROM mtl_system_items msi, mtl_material_transactions_temp mmtt
1785 ,wms_license_plate_numbers wlpn
1786 ,mtl_transaction_lots_temp mtlt
1787 WHERE mmtt.transfer_lpn_id = p_outer_lpn_id
1788 AND mmtt.organization_id = p_org_id
1789 AND mmtt.organization_id = msi.organization_id
1790 AND mmtt.inventory_item_id = msi.inventory_item_id
1791 AND msi.ont_pricing_qty_source = 'S'
1792 AND mmtt.secondary_transaction_quantity is null
1793 AND wlpn.lpn_id = mmtt.content_lpn_id
1794 AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+)
1795 AND wlpn.license_plate_number LIKE (p_inner_lpn || '%')
1796 AND mtlt.secondary_quantity is NULL;
1797
1798 ELSE
1799 OPEN x_lpn_lov FOR
1800 SELECT distinct outer.container_name, outer.lpn_id
1801 FROM wsh_delivery_Details inner,
1802 (SELECT wda.delivery_detail_id,
1803 wdd.inventory_item_id,
1804 wdd.lpn_id,wdd.container_name
1805 FROM wsh_delivery_details wdd,
1806 wsh_delivery_assignments_v wda
1810 WHERE outermost_lpn_id = p_outer_lpn_id)
1807 WHERE wdd.lpn_id in (
1808 SELECT wlpn.lpn_id
1809 FROM wms_license_plate_numbers wlpn
1811 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
1812 AND wda.parent_delivery_detail_id = wdd.delivery_detail_id
1813 AND wdd.organization_id = p_org_id
1814 AND wda.parent_delivery_detail_id is not null
1815 AND picked_quantity2 is null) outer
1816 WHERE inner.delivery_detail_id = outer.delivery_Detail_id
1817 AND outer.container_name LIKE (p_inner_lpn || '%')
1818 AND exists (SELECT msi.inventory_item_id
1819 FROM mtl_system_items_b msi
1820 WHERE msi.organization_id = inner.organization_id
1821 AND msi.organization_id = p_org_id
1822 AND msi.ont_pricing_qty_source = 'S'
1823 AND msi.inventory_item_id = inner.inventory_item_id);
1824 END IF;
1825 END IF;
1826 END GET_INNER_CATCH_WT_LPN;
1827
1828 PROCEDURE GET_CATCH_WT_ITEMS
1829 (x_item_lov OUT NOCOPY t_genref,
1830 p_org_id IN NUMBER,
1831 p_lpn_id IN NUMBER,
1832 p_entry_type IN VARCHAR2,
1833 p_lpn_context IN NUMBER,
1834 p_concat_item_segment IN VARCHAR2)
1835 IS
1836
1837 BEGIN
1838
1839 IF (UPPER(p_entry_type) = 'DIRECTSHIP') THEN
1840
1841 OPEN x_item_lov FOR
1842 SELECT DISTINCT msiv.concatenated_segments
1843 , msi.inventory_item_id
1844 , msi.description
1845 , NVL(msi.revision_qty_control_code, 1)
1846 , NVL(msi.lot_control_code, 1)
1847 , NVL(msi.serial_number_control_code, 1)
1848 , NVL(msi.restrict_subinventories_code, 2)
1849 , NVL(msi.restrict_locators_code, 2)
1850 , NVL(msi.location_control_code, 1)
1851 , msi.primary_uom_code
1852 , NVL(msi.inspection_required_flag, 2)
1853 , NVL(msi.shelf_life_code, 1)
1854 , NVL(msi.shelf_life_days, 0)
1855 , NVL(msi.allowed_units_lookup_code, 2)
1856 , NVL(msi.effectivity_control, 1)
1857 , '0'
1858 , '0'
1859 , '0'
1860 , '0'
1861 , '0'
1862 , '0'
1863 , ''
1864 , 'N'
1865 , msi.inventory_item_flag
1866 , 0
1867 --Bug No 3952081
1868 --Additional Fields for Process Convergence
1869 , NVL(msi.GRADE_CONTROL_FLAG,'N')
1870 , NVL(msi.DEFAULT_GRADE,'')
1871 , NVL(msi.EXPIRATION_ACTION_INTERVAL,0)
1872 , NVL(msi.EXPIRATION_ACTION_CODE,'')
1873 , NVL(msi.HOLD_DAYS,0)
1874 , NVL(msi.MATURITY_DAYS,0)
1875 , NVL(msi.RETEST_INTERVAL,0)
1876 , NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N')
1877 , NVL(msi.CHILD_LOT_FLAG,'N')
1878 , NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N')
1879 , NVL(msi.LOT_DIVISIBLE_FLAG,'Y')
1880 , NVL(msi.SECONDARY_UOM_CODE,'')
1881 , NVL(msi.SECONDARY_DEFAULT_IND,'')
1882 , NVL(msi.TRACKING_QUANTITY_IND,'P')
1883 , NVL(msi.DUAL_UOM_DEVIATION_HIGH,0)
1884 , NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
1885 FROM mtl_system_items_kfv msiv,
1886 wms_lpn_contents wlc,
1887 mtl_system_items msi
1888 WHERE wlc.parent_lpn_id = p_lpn_id
1889 AND wlc.organization_id = p_org_id
1890 AND msi.organization_id = wlc.organization_id
1891 AND msi.inventory_item_id = wlc.inventory_item_id
1892 AND msi.ont_pricing_qty_source = 'S'
1893 AND msiv.inventory_item_id = msi.inventory_item_id
1894 AND msiv.organization_id = msi.organization_id
1895 AND msiv.concatenated_segments LIKE (p_concat_item_segment || '%')
1896 AND not exists (SELECT 1 FROM WMS_DS_CT_WT_GTEMP gt
1897 WHERE gt.inventory_item_id = wlc.inventory_item_id
1898 AND gt.org_id = wlc.organization_id
1899 AND nvl(gt.INNER_LPN_ID, gt.LPN_ID) = wlc.parent_lpn_id);
1900
1901 ELSIF (UPPER(p_entry_type) = 'CT_WT_ALL') THEN
1902
1903 OPEN x_item_lov FOR
1904
1905 SELECT DISTINCT msi.concatenated_segments
1906 , msi.inventory_item_id
1907 , msi.description
1908 , NVL(msi.revision_qty_control_code, 1)
1909 , NVL(msi.lot_control_code, 1)
1910 , NVL(msi.serial_number_control_code, 1)
1911 , NVL(msi.restrict_subinventories_code, 2)
1912 , NVL(msi.restrict_locators_code, 2)
1913 , NVL(msi.location_control_code, 1)
1914 , msi.primary_uom_code
1915 , NVL(msi.inspection_required_flag, 2)
1916 , NVL(msi.shelf_life_code, 1)
1917 , NVL(msi.shelf_life_days, 0)
1918 , NVL(msi.allowed_units_lookup_code, 2)
1922 , '0'
1919 , NVL(msi.effectivity_control, 1)
1920 , '0'
1921 , '0'
1923 , '0'
1924 , '0'
1925 , '0'
1926 , ''
1927 , 'N'
1928 , msi.inventory_item_flag
1929 , 0
1930 --Bug No 3952081
1931 --Additional Fields for Process Convergence
1932 , NVL(msi.GRADE_CONTROL_FLAG,'N')
1933 , NVL(msi.DEFAULT_GRADE,'')
1934 , NVL(msi.EXPIRATION_ACTION_INTERVAL,0)
1935 , NVL(msi.EXPIRATION_ACTION_CODE,'')
1936 , NVL(msi.HOLD_DAYS,0)
1937 , NVL(msi.MATURITY_DAYS,0)
1938 , NVL(msi.RETEST_INTERVAL,0)
1939 , NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N')
1940 , NVL(msi.CHILD_LOT_FLAG,'N')
1941 , NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N')
1942 , NVL(msi.LOT_DIVISIBLE_FLAG,'Y')
1943 , NVL(msi.SECONDARY_UOM_CODE,'')
1944 , NVL(msi.SECONDARY_DEFAULT_IND,'')
1945 , NVL(msi.TRACKING_QUANTITY_IND,'P')
1946 , NVL(msi.DUAL_UOM_DEVIATION_HIGH,0)
1947 , NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
1948 FROM mtl_system_items_kfv msi, wms_lpn_contents wlc
1949 WHERE wlc.parent_lpn_id = p_lpn_id
1950 AND wlc.inventory_item_id = msi.inventory_item_id
1951 AND msi.ont_pricing_qty_source = 'S'
1952 AND wlc.organization_id = msi.organization_id
1953 AND msi.organization_id = p_org_id
1954 UNION
1955 SELECT DISTINCT msi.concatenated_segments
1956 , msi.inventory_item_id
1957 , msi.description
1958 , NVL(msi.revision_qty_control_code, 1)
1959 , NVL(msi.lot_control_code, 1)
1960 , NVL(msi.serial_number_control_code, 1)
1961 , NVL(msi.restrict_subinventories_code, 2)
1962 , NVL(msi.restrict_locators_code, 2)
1963 , NVL(msi.location_control_code, 1)
1964 , msi.primary_uom_code
1965 , NVL(msi.inspection_required_flag, 2)
1966 , NVL(msi.shelf_life_code, 1)
1967 , NVL(msi.shelf_life_days, 0)
1968 , NVL(msi.allowed_units_lookup_code, 2)
1969 , NVL(msi.effectivity_control, 1)
1970 , '0'
1971 , '0'
1972 , '0'
1973 , '0'
1974 , '0'
1975 , '0'
1976 , ''
1977 , 'N'
1978 , msi.inventory_item_flag
1979 , 0
1980 --Bug No 3952081
1981 --Additional Fields for Process Convergence
1982 , NVL(msi.GRADE_CONTROL_FLAG,'N')
1983 , NVL(msi.DEFAULT_GRADE,'')
1984 , NVL(msi.EXPIRATION_ACTION_INTERVAL,0)
1985 , NVL(msi.EXPIRATION_ACTION_CODE,'')
1986 , NVL(msi.HOLD_DAYS,0)
1987 , NVL(msi.MATURITY_DAYS,0)
1988 , NVL(msi.RETEST_INTERVAL,0)
1989 , NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N')
1990 , NVL(msi.CHILD_LOT_FLAG,'N')
1991 , NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N')
1992 , NVL(msi.LOT_DIVISIBLE_FLAG,'Y')
1993 , NVL(msi.SECONDARY_UOM_CODE,'')
1994 , NVL(msi.SECONDARY_DEFAULT_IND,'')
1995 , NVL(msi.TRACKING_QUANTITY_IND,'P')
1996 , NVL(msi.DUAL_UOM_DEVIATION_HIGH,0)
1997 , NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
1998 FROM mtl_system_items_kfv msi, mtl_material_transactions_temp mmtt
1999 WHERE (mmtt.content_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id = p_lpn_id)
2000 AND mmtt.organization_id = p_org_id
2001 AND mmtt.organization_id = msi.organization_id
2002 AND msi.ont_pricing_qty_source = 'S'
2003 AND mmtt.inventory_item_id = msi.inventory_item_id;
2004
2005 ELSE
2006
2007 IF (p_lpn_context = 8) THEN
2008
2009 OPEN x_item_lov FOR
2010
2011 select DISTINCT msi.concatenated_segments
2012 , msi.inventory_item_id
2013 , msi.description
2014 , NVL(msi.revision_qty_control_code, 1)
2015 , NVL(msi.lot_control_code, 1)
2016 , NVL(msi.serial_number_control_code, 1)
2017 , NVL(msi.restrict_subinventories_code, 2)
2018 , NVL(msi.restrict_locators_code, 2)
2019 , NVL(msi.location_control_code, 1)
2020 , msi.primary_uom_code
2021 , NVL(msi.inspection_required_flag, 2)
2022 , NVL(msi.shelf_life_code, 1)
2023 , NVL(msi.shelf_life_days, 0)
2024 , NVL(msi.allowed_units_lookup_code, 2)
2028 , '0'
2025 , NVL(msi.effectivity_control, 1)
2026 , '0'
2027 , '0'
2029 , '0'
2030 , '0'
2031 , '0'
2032 , ''
2033 , 'N'
2034 , msi.inventory_item_flag
2035 , 0
2036 --Bug No 3952081
2037 --Additional Fields for Process Convergence
2038 , NVL(msi.GRADE_CONTROL_FLAG,'N')
2039 , NVL(msi.DEFAULT_GRADE,'')
2040 , NVL(msi.EXPIRATION_ACTION_INTERVAL,0)
2041 , NVL(msi.EXPIRATION_ACTION_CODE,'')
2042 , NVL(msi.HOLD_DAYS,0)
2043 , NVL(msi.MATURITY_DAYS,0)
2044 , NVL(msi.RETEST_INTERVAL,0)
2045 , NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N')
2046 , NVL(msi.CHILD_LOT_FLAG,'N')
2047 , NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N')
2048 , NVL(msi.LOT_DIVISIBLE_FLAG,'Y')
2049 , NVL(msi.SECONDARY_UOM_CODE,'')
2050 , NVL(msi.SECONDARY_DEFAULT_IND,'')
2051 , NVL(msi.TRACKING_QUANTITY_IND,'P')
2052 , NVL(msi.DUAL_UOM_DEVIATION_HIGH,0)
2053 , NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
2054 from mtl_system_items_kfv msi, mtl_material_transactions_temp mmtt
2055 where (mmtt.content_lpn_id = p_lpn_id OR mmtt.transfer_lpn_id = p_lpn_id)
2056 and mmtt.organization_id = p_org_id
2057 and mmtt.organization_id = msi.organization_id
2058 and mmtt.inventory_item_id = msi.inventory_item_id
2059 AND msi.ont_pricing_qty_source = 'S'
2060 and mmtt.secondary_transaction_quantity is null;
2061
2062 ELSE
2063
2064 OPEN x_item_lov FOR
2065 SELECT DISTINCT msi.concatenated_segments
2066 , msi.inventory_item_id
2067 , msi.description
2068 , NVL(msi.revision_qty_control_code, 1)
2069 , NVL(msi.lot_control_code, 1)
2070 , NVL(msi.serial_number_control_code, 1)
2071 , NVL(msi.restrict_subinventories_code, 2)
2072 , NVL(msi.restrict_locators_code, 2)
2073 , NVL(msi.location_control_code, 1)
2074 , msi.primary_uom_code
2075 , NVL(msi.inspection_required_flag, 2)
2076 , NVL(msi.shelf_life_code, 1)
2077 , NVL(msi.shelf_life_days, 0)
2078 , NVL(msi.allowed_units_lookup_code, 2)
2079 , NVL(msi.effectivity_control, 1)
2080 , '0'
2081 , '0'
2082 , '0'
2083 , '0'
2084 , '0'
2085 , '0'
2086 , ''
2087 , 'N'
2088 , msi.inventory_item_flag
2089 , 0
2090 --Bug No 3952081
2091 --Additional Fields for Process Convergence
2092 , NVL(msi.GRADE_CONTROL_FLAG,'N')
2093 , NVL(msi.DEFAULT_GRADE,'')
2094 , NVL(msi.EXPIRATION_ACTION_INTERVAL,0)
2095 , NVL(msi.EXPIRATION_ACTION_CODE,'')
2096 , NVL(msi.HOLD_DAYS,0)
2097 , NVL(msi.MATURITY_DAYS,0)
2098 , NVL(msi.RETEST_INTERVAL,0)
2099 , NVL(msi.COPY_LOT_ATTRIBUTE_FLAG,'N')
2100 , NVL(msi.CHILD_LOT_FLAG,'N')
2101 , NVL(msi.CHILD_LOT_VALIDATION_FLAG,'N')
2102 , NVL(msi.LOT_DIVISIBLE_FLAG,'Y')
2103 , NVL(msi.SECONDARY_UOM_CODE,'')
2104 , NVL(msi.SECONDARY_DEFAULT_IND,'')
2105 , NVL(msi.TRACKING_QUANTITY_IND,'P')
2106 , NVL(msi.DUAL_UOM_DEVIATION_HIGH,0)
2107 , NVL(msi.DUAL_UOM_DEVIATION_LOW,0)
2108 FROM mtl_system_items_kfv msi, wsh_Delivery_Details wdd1
2109 WHERE wdd1.inventory_item_id = msi.inventory_item_id
2110 AND wdd1.organization_id = msi.organization_id
2111 AND msi.ont_pricing_qty_source = 'S'
2112 AND wdd1. picked_quantity2 is NULL
2113 AND wdd1.delivery_detail_id in
2114 (SELECT wda.delivery_detail_id
2115 FROM wsh_delivery_details wdd,
2116 wsh_delivery_assignments_v wda
2117 WHERE wdd.lpn_id= p_lpn_id
2118 AND wdd.released_status = 'X' -- For LPN reuse ER : 6845650
2119 AND wdd.delivery_detail_id = wda.parent_delivery_detail_id
2120 AND wdd.organization_id = p_org_id
2121 AND wda.parent_delivery_detail_id is not null)
2122 AND msi.organization_id = p_org_id;
2123 END IF;
2124 END IF;
2125
2126 END GET_CATCH_WT_ITEMS;
2127
2128 PROCEDURE SHOW_CT_WT_FOR_SPLIT (
2129 p_org_id IN NUMBER
2130 , p_from_lpn_id IN NUMBER
2131 , p_from_item_id IN NUMBER
2132 , p_from_item_revision IN VARCHAR2
2136 , x_return_status OUT NOCOPY VARCHAR2
2133 , p_from_item_lot_number IN VARCHAR2
2134 , p_to_lpn_id IN NUMBER
2135 , x_show_ct_wt OUT NOCOPY NUMBER
2137 , x_msg_data OUT NOCOPY VARCHAR2
2138 , x_msg_count OUT NOCOPY NUMBER
2139 ) IS
2140
2141 CURSOR itemCur(in_lpn_id NUMBER) IS
2142 SELECT picked_quantity2
2143 FROM wsh_delivery_details wdd,
2144 mtl_system_items_b msib
2145 WHERE wdd.inventory_item_id = p_from_item_id
2146 AND wdd.inventory_item_id = msib.inventory_item_id
2147 AND wdd.organization_id = p_org_id
2148 AND wdd.organization_id = msib.organization_id
2149 AND NVL(wdd.revision,'@@@') = NVL(NVL(p_from_item_revision,wdd.revision),'@@@')
2150 AND NVL(wdd.lot_number,'@@@') = NVL(p_from_item_lot_number,'@@@')
2151 AND wdd.delivery_detail_id in (SELECT wda.delivery_detail_id
2152 FROM wsh_delivery_assignments_v wda,
2153 wsh_delivery_details wdd1
2154 WHERE wdd1.lpn_id = in_lpn_id
2155 AND wdd1.released_status = 'X' -- For LPN reuse ER : 6845650
2156 AND wdd1.delivery_detail_id = wda.parent_delivery_Detail_id
2157 AND wdd1.organization_id = p_org_id);
2158
2159
2160
2161 l_from_sec_qty NUMBER := 0;
2162 l_to_sec_qty NUMBER := 0;
2163 l_from_item_pri_qty NUMBER := 0 ;
2164 l_to_item_picked_qty NUMBER := 0;
2165 l_api_name CONSTANT VARCHAR2(30) := 'SHOW_CT_WT_FOR_SPLIT';
2166 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2167 l_progress VARCHAR2(30) := '0';
2168 itemRec itemCur%ROWTYPE;
2169
2170
2171 BEGIN
2172
2173 --If p_to_lpn_id is NULL, THEN the context of to_lpn is 5 ELSE 11
2174 --Check to see if ct wt for from_lpn has been entered for that item in WDD
2175 --if YES, check to see
2176 -- if context of to_lpn = 5, then show_ct_wt
2177 -- if context of to_lpn = 11, then see if ct_wt has been entered in WDD for that item
2178 IF ( l_debug = 1 ) THEN
2179 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
2180 print_debug('p_org_id => ' || p_org_id||' fromlpn '||p_from_lpn_id||' fromItem =>'||p_from_item_id||' rev =>'||p_from_item_revision , 4);
2181 print_debug('p_from_lot_number => ' || p_from_item_lot_number||'p_to_lpn_id =>'||p_to_lpn_id, 4);
2182 END IF;
2183
2184 --add up all the picked_qty2 from wdd, even if 1 rec is null, then
2185 --the result will be null, and we should hide the ct wt entry
2186
2187 OPEN itemCur(p_from_lpn_id);
2188 LOOP
2189 FETCH itemCur into l_to_item_picked_qty;
2190 EXIT WHEN itemCur%NOTFOUND;
2191 print_debug(' inside 2 Loop l_to_item_pri_qty => '||l_to_item_picked_qty,4);
2192 l_from_item_pri_qty := l_from_item_pri_qty + l_to_item_picked_qty;
2193 print_debug(' inside 2 Loop l_from_item_pri_qty => '||l_from_item_pri_qty,4);
2194
2195 END LOOP;
2196 CLOSE itemCur;
2197
2198 l_progress := '10';
2199 print_debug(' out 2 Loop l_from_item_pri_qty => '||l_from_item_pri_qty,4);
2200 if (l_from_item_pri_qty IS NULL OR l_from_item_pri_qty = 0 ) THEN
2201 x_show_ct_wt := 0;
2202 x_return_status := fnd_api.g_ret_sts_success;
2203 RETURN;
2204 END IF;
2205
2206 l_progress := '20';
2207 if (p_to_lpn_id IS NULL) THEN
2208 x_show_ct_wt := 1 ;
2209 x_return_status := fnd_api.g_ret_sts_success;
2210 RETURN;
2211 END IF;
2212 IF (p_to_lpn_id IS NOT NULL) THEN
2213 OPEN itemCur(p_to_lpn_id);
2214 LOOP
2215 FETCH itemCur into l_to_item_picked_qty;
2216 EXIT WHEN itemCur%NOTFOUND;
2217
2218 l_to_sec_qty := l_to_sec_qty + l_to_item_picked_qty;
2219 print_debug('Inside Loop l_to_item_picked_qty => ' || l_to_item_picked_qty||' l_to_sec_qty=>'||l_to_sec_qty,4);
2220 END LOOP;
2221 CLOSE itemCur;
2222 l_progress := '30';
2223 print_debug('After Loop l_to_sec_qty => '||l_to_sec_qty,4);
2224
2225 if (l_to_sec_qty IS NULL) THEN
2226 x_show_ct_wt := 0 ;
2227 x_return_status := fnd_api.g_ret_sts_success;
2228 RETURN;
2229 END IF;
2230
2231 l_progress := '40';
2232 if (l_to_sec_qty = 0) THEN
2233 x_show_ct_wt := 1;
2234 x_return_status := fnd_api.g_ret_sts_success;
2235 RETURN;
2236 END IF;
2237 END IF;
2238 l_progress := '50';
2239
2240 --add up all the picked_qty2 from wdd, even if 1 rec is null, then
2241 --the result will be null, and we should hide the ct wt entry
2242
2243 OPEN itemCur(p_from_lpn_id);
2244 LOOP
2245 FETCH itemCur into l_to_item_picked_qty;
2246 EXIT WHEN itemCur%NOTFOUND;
2247 l_from_item_pri_qty := l_from_item_pri_qty + l_to_item_picked_qty;
2248 print_debug(' inside 2 Loop l_from_item_pri_qty => '||l_from_item_pri_qty,4);
2249
2250 END LOOP;
2251 CLOSE itemCur;
2252
2253 l_progress := '60';
2254 print_debug(' Before computing l_from_item_pri_qty => '||l_from_item_pri_qty||' l_to_sec_qty =>'||l_to_sec_qty,4);
2255
2256 IF (NVL(l_from_item_pri_qty,0) > 0 AND l_to_sec_qty >= 0 ) THEN
2260 END IF;
2257 x_show_ct_wt := 1;
2258 ELSE
2259 x_show_ct_wt := 0;
2261 l_progress := '70';
2262 x_return_status := fnd_api.g_ret_sts_success;
2263
2264 EXCEPTION
2265 WHEN OTHERS THEN
2266 IF (l_debug = 1) THEN
2267 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
2268 IF ( SQLCODE IS NOT NULL ) THEN
2269 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
2270 END IF;
2271 END IF;
2272 END SHOW_CT_WT_FOR_SPLIT;
2273
2274
2275 FUNCTION IS_CT_WT_SPLIT_VALID (
2276 p_org_id IN NUMBER
2277 , p_from_lpn_id IN NUMBER
2278 , p_from_item_id IN NUMBER
2279 , p_from_item_revision IN VARCHAR2
2280 , p_from_item_lot_number IN VARCHAR2
2281 , p_from_item_pri_qty IN NUMBER
2282 , p_from_item_pri_uom IN VARCHAR2
2283 , p_from_item_sec_qty IN NUMBER
2284 , x_return_status OUT NOCOPY VARCHAR2
2285 , x_msg_data OUT NOCOPY VARCHAR2
2286 , x_msg_count OUT NOCOPY NUMBER
2287 ) RETURN NUMBER IS
2288
2289 CURSOR itemCur IS
2290 SELECT sum(inv_convert.inv_um_convert(wdd.inventory_item_id,
2291 6,
2292 wdd.REQUESTED_QUANTITY,
2293 wdd.REQUESTED_QUANTITY_UOM,
2294 --msib.primary_uom_code,
2295 p_from_item_pri_uom,
2296 NULL,
2297 NULL)) requested_quantity,
2298 sum(picked_quantity2) picked_quantity2,
2299 msib.primary_uom_code
2300 FROM wsh_delivery_details wdd,
2301 mtl_system_items_b msib
2302 WHERE wdd.inventory_item_id = p_from_item_id
2303 AND wdd.inventory_item_id = msib.inventory_item_id
2304 AND wdd.organization_id = p_org_id
2305 AND wdd.organization_id = msib.organization_id
2306 AND NVL(wdd.revision,'@@@') = NVL(p_from_item_revision,'@@@')
2307 AND NVL(wdd.lot_number,'@@@') = NVL(p_from_item_lot_number,'@@@')
2308 AND wdd.delivery_detail_id in (SELECT wda.delivery_detail_id
2309 FROM wsh_delivery_assignments_v wda,
2310 wsh_delivery_Details wdd1
2311 WHERE wdd1.lpn_id = p_from_lpn_id
2312 AND wdd1.released_status = 'X' -- For LPN reuse ER : 6845650
2313 AND wdd1.delivery_detail_id = wda.parent_delivery_Detail_id
2314 AND wdd1.organization_id = p_org_id)
2315 GROUP BY wdd.inventory_item_id, wdd.revision, wdd.lot_number, msib.primary_uom_code;
2316
2317 l_progress VARCHAR2(20) := '0';
2318 l_api_name CONSTANT VARCHAR2(30) := 'IS_CT_WT_SPLIT_VALID';
2319 l_api_version CONSTANT NUMBER := 1.0;
2320 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2321 l_pri_qty NUMBER := 0;
2322 l_sec_Qty NUMBER := 0;
2323 l_pri_remaining_qty NUMBER := 0;
2324 l_sec_remaining_qty NUMBER := 0;
2325 l_pri_input_qty NUMBER := 0;
2326 L_PRI_QTY_DIFF NUMBER := 0;
2327 L_SEC_QTY_DIFF NUMBER := 0;
2328 RETURNTOLERANCE NUMBER := 0;
2329 first_time BOOLEAN := TRUE;
2330
2331 itemRec itemCur%ROWTYPE;
2332
2333 BEGIN
2334
2335 IF ( l_debug = 1 ) THEN
2336 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
2337 print_debug('p_org_id => ' || p_org_id||' fromlpn '||p_from_lpn_id||' fromItem =>'||p_from_item_id||' rev =>'||p_from_item_revision , 4);
2338 print_debug('p_from_lot_number => ' || p_from_item_lot_number||'pri uom =>'||p_from_item_pri_uom, 4);
2339 END IF;
2340
2341 l_progress := '10';
2342
2343 /**l_pri_input_qty := inv_convert.inv_um_convert(p_from_item_id,
2344 6,
2345 p_from_item_pri_qty,
2346 p_from_item_pri_uom,
2347 p_from_item_pri_uom,
2348 NULL,
2349 NULL);
2350 **/
2351 l_progress := '20';
2352
2353 OPEN itemCur;
2354 LOOP
2355 FETCH itemCur into itemRec;
2356 EXIT WHEN itemCur%NOTFOUND;
2357 l_pri_input_qty := p_from_item_pri_qty;
2358
2359 l_pri_qty_diff := itemRec.requested_quantity - l_pri_input_qty ;
2360 l_sec_qty_diff := itemRec.picked_quantity2 - p_from_item_sec_qty;
2361 IF ( l_debug = 1 ) THEN
2362 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);
2363 END IF;
2364
2365 END LOOP;
2366 CLOSE itemCur;
2367
2368 /*
2369
2370 FOR itemCur in itemRec LOOP
2371
2372 --l_pri_qty_diff := itemRec.requested_quantity - l_pri_input_qty ;
2373 l_pri_qty_diff := itemRec.requested_quantity - l_pri_input_qty ;
2374 l_sec_qty_diff := itemRec.picked_quantity2 - p_from_item_sec_qty;
2375 l_progress := '30';
2376
2377 END LOOP;
2378 */
2379
2383 **/
2380 IF (l_pri_qty_diff = 0) THEN
2381 /** This means, entire qty was split, there is no remaining qty
2382 ** so there is no need for further check..this is a valid split
2384 RETURN 1;
2385 END IF;
2386 l_progress := '40';
2387
2388 IF (l_pri_qty_diff < 0) THEN
2389 /** This condition should not occur, this is an error condition
2390 **/
2391 RETURN 0;
2392 END IF;
2393 l_progress := '50';
2394
2395 IF (l_pri_qty_diff > 0) THEN
2396 /**This is a valid condition, now we need to check the
2397 ** tolerance for the sec_qty_diff
2398 **/
2399
2400 l_progress := '60';
2401 returnTolerance := Check_Secondary_Qty_Tolerance (
2402 p_api_version => 1
2403 , p_init_msg_list => fnd_api.g_false
2404 , x_return_status => x_return_status
2405 , x_msg_count => x_msg_count
2406 , x_msg_data => x_msg_data
2407 , p_organization_id => p_org_id
2408 , p_inventory_item_id => p_from_item_id
2409 , p_quantity => l_pri_qty_diff
2410 , p_uom_code => p_from_item_pri_uom
2411 , p_secondary_quantity => l_sec_qty_diff);
2412
2413 l_progress := '70';
2414 if (returnTolerance = 0) THEN
2415 RETURN 1;
2416 else
2417 RETURN 0;
2418 end if;
2419 l_progress := '80';
2420 END IF;
2421 /**
2422 EXCEPTION
2423 WHEN OTHERS THEN
2424 IF (l_debug = 1) THEN
2425 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
2426 IF ( SQLCODE IS NOT NULL ) THEN
2427 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
2428 END IF;
2429 END IF;
2430 **/
2431
2432 END IS_CT_WT_SPLIT_VALID;
2433
2434 FUNCTION VALIDATE_CT_WT_FOR_DELIVERYNUM(
2435 p_api_version IN NUMBER
2436 , p_init_msg_list IN VARCHAR2 := fnd_api.g_false
2437 , x_return_status OUT NOCOPY VARCHAR2
2438 , x_msg_count OUT NOCOPY NUMBER
2439 , x_msg_data OUT NOCOPY VARCHAR2
2440 , p_org_id IN NUMBER
2441 , p_delivery_name IN VARCHAR2
2442 )RETURN NUMBER AS
2443
2444 CURSOR wddCur IS
2445 SELECT wdd.delivery_Detail_id,
2446 wdd.inventory_item_id,
2447 wdd.organization_id,
2448 wdd.picked_quantity,
2449 wdd.picked_quantity2,
2450 msi.primary_uom_code,
2451 msi.secondary_uom_code,
2452 msi.secondary_default_ind
2453 FROM wsh_delivery_Details wdd,
2454 mtl_system_items msi
2455 WHERE wdd.delivery_detail_id in
2456 (SELECT wda.delivery_detail_id
2457 FROM wsh_delivery_assignments_v wda,
2458 wsh_new_deliveries wnd
2459 WHERE wda.delivery_id = wnd.delivery_id
2460 AND wnd.name = p_delivery_name
2461 AND wda. PARENT_DELIVERY_DETAIL_ID is not NULL)
2462 AND picked_quantity2 is null
2463 AND wdd.inventory_item_id = msi.inventory_item_id
2464 AND wdd.inventory_item_id is not null
2465 AND wdd.organization_id = msi.organization_id
2466 AND msi.ont_pricing_qty_source = 'S';
2467
2468 l_progress VARCHAR2(20) := '0';
2469 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_CT_WT_FOR_DELIVERYNUM';
2470 l_api_version CONSTANT NUMBER := 1.0;
2471 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2472 l_uom_conv_rate NUMBER;
2473 l_return NUMBER := G_CHECK_SUCCESS;
2474
2475 BEGIN
2476
2477
2478 -- Standard call to check for call compatibility.
2479 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
2480 fnd_message.set_name('WMS', 'WMS_CONT_INCOMPATIBLE_API_CALL');
2481 fnd_msg_pub.ADD;
2482 RAISE fnd_api.g_exc_unexpected_error;
2483 END IF;
2484
2485 -- Initialize message list if p_init_msg_list is set to TRUE.
2486 IF fnd_api.to_boolean(p_init_msg_list) THEN
2487 fnd_msg_pub.initialize;
2488 END IF;
2489
2490 -- Initialize API return status to success
2491 x_return_status := fnd_api.g_ret_sts_success;
2492
2493
2494 IF ( l_debug = 1 ) THEN
2495 print_debug(l_api_name || ' Entered ' || g_pkg_version, 1);
2496 print_debug('p_org_id => '|| p_org_id||' p_delivery_number=>'||p_delivery_name , 4);
2497 END IF;
2498
2499 l_progress := '100';
2500
2501
2502 FOR item_rec IN wddCur LOOP
2503
2504 IF ( l_debug = 1 ) THEN
2505 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);
2506 END IF;
2507
2508 l_progress := '200';
2509
2510 IF (item_rec.secondary_default_ind = G_SECONDARY_DEFAULT AND item_rec.picked_quantity2 IS NULL) THEN
2511
2512 --call the defaulting logic and see if the return < 0
2513
2514 INV_CONVERT.inv_um_conversion (
2515 from_unit => item_rec.primary_uom_code
2516 , to_unit => item_rec.secondary_uom_code
2517 , item_id => item_rec.inventory_item_id
2518 , uom_rate => l_uom_conv_rate );
2519
2520 l_progress := '300';
2521
2522 IF ( l_debug = 1 ) THEN
2523 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);
2524 END IF;
2525
2526 IF ( l_uom_conv_rate < 0 ) THEN
2527 -- no valid connection uom conversion between these two uoms
2528 l_return := G_CHECK_ERROR;
2529 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
2530 fnd_msg_pub.ADD;
2531 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2532 ELSE
2533 -- there is a valid conversion, change status to warning
2534 l_return := G_CHECK_WARNING;
2535 END IF;
2536 ELSE
2537 IF (item_rec.secondary_default_ind = G_SECONDARY_NO_DEFAULT AND item_rec.picked_quantity2 IS NULL) THEN
2538 l_return := G_CHECK_ERROR;
2539 fnd_message.set_name('WMS', 'WMS_CTWT_DEFAULT_ERROR');
2540 fnd_msg_pub.ADD;
2541 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2542 END IF;
2543
2544 END IF;
2545
2546 l_progress := '400';
2547
2548 END LOOP;
2549
2550
2551 l_progress := '500';
2552
2553 RETURN l_return;
2554
2555 EXCEPTION
2556 WHEN OTHERS THEN
2557 IF (l_debug = 1) THEN
2558 print_debug(l_api_name ||' Error l_progress=' || l_progress, 1);
2559 IF ( SQLCODE IS NOT NULL ) THEN
2560 print_debug('SQL error: ' || SQLERRM(SQLCODE), 1);
2561 END IF;
2562 END IF;
2563 x_return_status := fnd_api.g_ret_sts_error;
2564 fnd_message.set_name('WMS', 'WMS_CALC_SEC_QTY_FAIL');
2565 fnd_msg_pub.ADD;
2566 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
2567 END VALIDATE_CT_WT_FOR_DELIVERYNUM;
2568
2569 END WMS_CATCH_WEIGHT_PVT;